mysql通过“延迟关联”进行limit分页查询优化的一个实例
mysql通过“延迟关联”进行limit分页查询优化的一个实例最近在生产上遇见一个分页查询特别慢的问题，数据量大概有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分页查询呢？下载延迟关联技术，可以优化这句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倍。
页:
[1]