526互联

mysql大批量删除数据，工作记录

发布时间 2023-10-28 15:14:44作者: bitterteaer

在10万记录的三张关系表中删除相对应的数据，三张表为层级嵌套关系

优化查询

目的：减少mysql数据库慢查询导致的长时间占据锁的情况出现
优化方向：将慢sql改为多次执行的快sql，避免长时间占用mysql资源情况出现

多表查询会用到的几种方式

连表查询

SELECT event_data.id FROM event_data
LEFT JOIN tracking_event
ON tracking_event.id = event_data.tracking_event_id
LEFT JOIN tracking_visitor
ON tracking_event.tto_clid = tracking_visitor.tto_clid
WHERE tracking_visitor.d_value REGEXP '"matomo": "449"'
AND event_data.d_key != "449"
LIMIT 100

嵌套子查询

SELECT event_data.id
FROM event_data
WHERE event_data.tracking_event_id in (
	SELECT tracking_event.id 
	FROM tracking_event
	WHERE tracking_event.tto_clid in (
		SELECT tracking_visitor.tto_clid 
		FROM tracking_visitor
		WHERE tracking_visitor.d_value 
		REGEXP '"matomo": "449"'
	)
)
LIMIT 100

连表查询和嵌套子查询在数据量大的情况下没有合理设置外键和索引都会很慢

将所有sql操作变为简单sql，分批执行，在程序中缓存结果，空间换时间

  • 查询tracking_visitor表中符合的数据的id，将其缓存到一个变量
  • 利用第一点缓存的数据查询第二章表tracking_event的数据，以此避免嵌套子查询和连表查询
  • 类似1、2点操作，将tracking_event的id缓存到一个变量，再查询event_data的数据
  • 现在已经拿到了三张表需要删除的数据了再分别删除即可

上述查询中都要加limit，分批执行

其他思路

1、将示例id在tracking_visitor表中取出来，作为索引
2、建立中间表，从头到尾读取数据，符合的保留到新的临时表，不符合的删除，最后交换两张表以此达到大批量删除数据
删除大表的多行数据时，会超出innod block table size的限制，最小化的减少锁表的时间的方案是：

  • 选择不需要删除的数据，并把它们存在一张相同结构的空表里
  • 重命名原始表，并给新表命名为原始表的原始表名
  • 删掉原始表

参考链接：https://blog.csdn.net/jike11231/article/details/126551510