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 列上有一个二级索引，那么查询的过程是这样的：在 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 中获取数据。

原文链接