mysql通过“延迟关联”进行limit分页查询优化的一个实例
来源:互联网 发布:域名注册使用godaddy 编辑:程序博客网 时间:2024/06/04 18:35
最近在生产上遇见一个分页查询特别慢的问题,数据量大概有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倍。
第一步,找到执行慢的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倍。
0 0
- mysql通过“延迟关联”进行limit分页查询优化的一个实例
- 优化limit分页(延迟关联)
- mysql limit 分页查询优化
- 一个 JDBC 实现对 mysql 进行分页查询的 实例
- Mysql分页&关联查询优化
- mysql limit分页查询优化写法
- mysql limit分页查询优化写法
- mysql limit分页查询优化写法
- MySQL limit分页查询优化写法
- Mysql limit 分页机制和优化实例
- Mysql limit 分页机制和优化实例
- MySQL关联查询优化实例
- 如何优化LIMIT,OFFSET进行的分页?
- jsp+mysql分页技巧:巧用limit 进行分页查询
- mysql分页limit优化
- MySQL 优化Limit分页
- mysql分页limit 优化
- mysql 分页limit 优化
- listview的优化性能 还有二次采样
- 设计模式-观察者模式简单实例
- MDK Keil编译器编译窗口的设置
- 设计模式六大原则 - (3):依赖倒置原则
- cocos2d-js
- mysql通过“延迟关联”进行limit分页查询优化的一个实例
- Nodejs创建https服务器(Windows 7)
- Codevs1155 金明的预算方案 ——2006年NOIP全国联赛提高组 变种经典背包dp
- HOG 学习
- 自定义Editext控制输入字数限制的文本框Editext
- boosting和adaboost区别
- jQuery笔记--动画篇
- 设计模式六大原则 - (4):接口隔离原则
- Android二維碼掃描