MySQL 利用延迟关联优化分页查询

通常会有一些起点巨大的分页查询，效率低下，例如：

select * from tab 
where create_date>'2018-12-20 13:20:10' and create_date<'2019-05-01 14:00:00' 
order by id 
limit 328000,10;

假设tab是一个数据量非常大的表，且满足条件create_date>'2018-12-20 13:20:10' and create_date<'2019-05-01 14:00:00'的数据量在百万以上，如果create_date列上有一个二级索引，那么需要在二级索引上每遍历一个符合条件的索引行，都需要回表一次。取得所有符合条件的数据行后，再根据id进行排序，需要filesort操作。
所以即使在create_date列上存在索引，对于上述SQL由于回表的开销，最终优化器一般都会选择全表扫描。而全表扫描的代价就是查询效率非常慢。

针对这一类分页查询，可以利用延迟关联的手法进行优化，改写如下：

select * from 
tab a,
(select id from tab where create_date>'2018-12-20 13:20:10' and create_date<'2019-05-01 14:00:00' order by id limit 328000,10) b
where a.id=b.id;

改写后快的原因在于，子查询b可以通过create_date索引走覆盖索引，这个覆盖索引一定比tab表小很多。另外，在上述SQL以外的情况，如果能利用覆盖索引同时避免排序，那么就能在获取足够数量的数据时及时停止扫描。子查询b查出符合条件的主键id，然后与a关联，这时b得到的id通过a的id主键索引，能够唯一确定一行数据，高效地从tab获取数据。