mysql通过“延迟关联”进行limit分页查询优化的一个实例

转载自：
https://blog.csdn.net/iteye_1914/article/details/82675158

最近在生产上遇见一个分页查询特别慢的问题，数据量大概有200万的样子，翻到最后一页性能很低，差不多得有4秒的样子才能出来整个页面，需要进行查询优化。
第一步，找到执行慢的sql，如下：
SELECT
shotel_id as hotelId,
mroom_type_id as mroomTypeId,
available_date as availableDate,
result_status as resultStatus,
create_time as createTime,
operate_time as operateTime
FROM autofs_ivr
ORDER BY shotel_id
LIMIT 1983424, 20

explain一下：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE autofs_ivr ALL None None None None 1875402 Using file sort

观察可见，type为all，走了全表扫描，extra是using file sort，不是索引覆盖。
其中select语句选中的列除了shotel_id剩余均不在order by的列里面，而shotel_id列上面有一个索引，所以这个sql并没有走索引覆盖，每次根据二级索引查询到一条记录，都要再走一遍主键索引去表里找出所需要的其他列，速度自然慢。

有什么办法可以优化一下这个limit分页查询呢？《高性能mysql》当中提到了一种“延迟关联”技术，可以优化这句sql，优化后的语句如下：
SELECT
shotel_id as hotelId,
mroom_type_id as mroomTypeId,
available_date as availableDate,
result_status as resultStatus,
operate_time as operateTime,
create_time as createTime
FROM autofs_ivr
inner join(
select id
from autofs_ivr
ORDER BY shotel_id
LIMIT 1983424, 20
) as lim using(id));

explain结果如下：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ALL None None None None 20
1 PRIMARY autofs_ivr eq_ref PRIMARY PRIMARY 4 lim.id 1
2 DERIVED autofs_ivr index None ix_sh_mr 124 None 1875402 Using index

子查询中，使用索引覆盖技术，查出20条记录，再通过主键和表本身做关联，即使走了全表扫描，访问记录也不过20条，查询时间降为400毫秒，提升速度10倍。