【数据库】MySQL 100万条记录全表遍历---金山面试题

mysql开发中文博客:https://imysql.cn/

今天做一个业务需求的逻辑处理,需要对MySQL全表进行遍历,数据规模百万级别,为方便描述,这张表就用 a 来代替吧

结合自己的思路和一些资料,在这里记录一下方案的进化史

 

一、直接 select * from a ; 肯定不用多说了,估计还没拿完,内存就爆了,对这种数量级的表不现实。

二、分页式循环遍历

select * from a limit 0,1000;

select * from a limit 1000,1000。

在代码中,循环地使用这种模式的sql去遍历表,虽然可以实现,但显然这种方式是没法用到索引,越往后遍历性能会越低。

三、带索引链式遍历,由于where条件中的id字段有索引,对于比较靠后的数据,查询性能将会轻松提升很多

select * from a where id > 0 order by id limit 10000;

假设上一次查询得到的结果集中,最后一条数据的id为10001
select * from a where id > 10001 order by id limit 10000;

 

那初步的优化方案就很容易想到了,id有索引,而且是自增的,就从id=1开始遍历,结果集以id升序排列,然后根据结果集最后一条数据的id,继续下一次遍历,依此类推,直到遍历完成

缺点:下次遍历必须知道上次遍历的最后一个id的值,也就是前后遍历形成了一个依赖链,这将导致无法实现遍历行为之间的并行操作。

 

四、id分区多线程遍历

可以尝试进行ID的区间划分,有了ID的分区各个分区的查询就不存在必要的关联性了。

比如,第一个线程遍历id区间为1-10000的数据,第二个线程遍历id区间为10001-20000的数据,依此类推,直到遍历到最大id。这样就可以实现多个线程并行操作,性能也许会大大提升。

找到表中最大最小id,确定id区间
select min(id),max(id) from a ;


多线程遍历,每个线程每批次拿10000条记录,从min(id)开始,到max(id)结束
线程1:
select * from a where id >= 1 and id <= 10000;

线程2:
select * from a where id >= 100001 and id <=20000;

但不能说方案四一定比方案三好,要根据实际情况进行选择。方案四需要考虑ID的区间大小和SQL并行查询的线程数(数据库压力)。

 而对于存储过程或游标之类,个人认为移植性和通用性不是很好,就没研究那方面的。

先写到这里,后续有什么好的方案,再补上!

参考:

  MYSQL全表遍历性能优化

原文:https://www.cnblogs.com/dengguangxue/p/12196937.html

posted on 2022-10-04 01:25  bdy  阅读(62)  评论(0编辑  收藏  举报

导航