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

89788

                        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倍。