背景：Task任务，需要将最近一天的数据查询出来，然后同步到kafka中。
一次慢查询的优化
环境：mysql5.7.26
表数据总量：4千万数据
单日数据量：1百万数据
表索引：date_user_id_idx（date、user_id）
主键索引：id
需求：需要将60天的数据归到到历史表，需要将昨天的数据同步给kafka。
sql：select * from tb_temp where date = 20220919
V1升级（分批次查询）
优化方案：限制每次查询的数量，分多次查出。
此时我们一般会使用分页查询。
-
一般最常用的就是
offset+limit偏移量进行查询。但是会带来深分页的性能损耗，是不推荐使用这种方案的。 -
另一种方案就是使用
cursor游标的方式，每次查询数据后得到一个游标id，然后代入到sql条件中。
select * from tb_temp where date = 20220919 and id<=xxx order by id desc limit 5000
思考点：为什么要专门引入
order by id desc
需要注意的时候，因为select * from tb_temp where date = 20220919命中的是date_user_id_idx索引。联合索引的规则：先根据date排序、然后根据user_id排序。所以这条只查询date返回的主键id是无序的。而游标分页需要一个有序的游标id，我们既然要借助主键id作为游标id，所以需要专门引入order by id desc。
思考点：为什么引入
order by id desc后，sql执行计划从date_user_id_idx变为主键索引
如果命中date_user_id_idx二级索引的话，我们得到的二级索引下的id是无序的。那么会带来回表查询+文件排序（100w数据）的性能损耗。那么在mysql看来直接走id主键索引性能会更优。
V2升级（limit底层原理）
而我们采用的正是V1版本的升级方案（游标法），但代码执行过程依旧会存在两种慢sql。
慢sql-1（归档历史数据）：select * from tb_temp where date = 20220723 and id<=9223372036854775807（因为第一次的的时候给框架默认游标是Long.MAX） order by id desc limit 5000
慢sql-2（同步昨日数据）：select * from tb_temp where date = 20220921 and id<=12344566（“临界数据的id”） order by id desc limit 5000
limit 200执行原理：
- 在表中拉取到200条数据后，终止；
- 扫描完where条件规定的返回后，终止；
当查询出的行数无法满足limit的限制时，mysql需要将where范围内的数据全部扫描完，流程才会被结束。
那么我们可以看到：
慢sql1（归档历史数据）：由于是第一次查询所以框架给的游标id是默认值也就是Long的最大值。而0723的数据分布在聚簇索引树最左侧，本次查询就会在聚簇索引树的最右侧发起查询。期间会涉及到大量的IO操作，导致我们查询很慢。但是（归档历史数据）的sql第二次查询的时候：select * from tb_temp where date = 20220723 and id<=111122002 order by id desc limit 5000已经确定了范围，就会导致查询非常快速。
慢sql1（同步昨日数据）：20220921数据分布在聚簇索引树的最右侧，所以开始的时候查询效率很高，但是到了20220921数据的边界处时，只查询到了288条数据，没有满足5000条数据的终止条件，且我们并没有给出终止条件（date不是终止条件，而是筛选条件）所以依旧会一路去左查询满足条件的数据，直到遍历完全表数据。
优化方案：
mysql的id是自增主键，逻辑上我们认为date和id是有关系的。所以需要查询当天的第一笔或者最后一笔id，作为终止limit的条件。
以同步昨日数据为例：
-- 先查询上一天的最后一笔数据的lastId
select id from tb_temp where date = 20220920 order by id desc limit 1;
- 再将这笔id代入到下面sql中
elect * from tb_temp where date = 20220921 and id<=12344566 and id>lastId order by id desc limit 5000
彩蛋：
为什么要查询上一天最后一笔数据的id，而不是当天的第一笔id，其实也和上面讲的有关系。
如果查询20220921的第一笔id的话（select id from tb_temp where date = 20220921 order by id limit 1）那么mysql会从聚簇索引树的最左侧开始寻找（而实际上这笔数据位于最右侧，从而带来很多无用的性能开销）。
网友评论