通常会有一些起点巨大的分页查询,效率低下,例如:
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获取数据。