MySQL分页查询优化案例-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

MySQL分页查询优化案例

简介: 一个简单但还是比较典型的分页查询优化

12月25日,在查看某客户监控时,发现了客户执行了超过60s的分页查询,执行情况如下:

image

这是一个很简单、典型的分页查询SQL,khome_space.regdate列上有索引,表有三百多万行数据。而查询的偏移量太大,所以导致分页查询过于耗时。随后在自己的本地环境模拟了下这个场景,以我自己本地100万数据行的t_operater_record表为例,来优化一下查询。

模拟出客户的分页查询操作,查看对应的执行计划:

mysql> explain
    -> select t.id,t.operationer,t.operater_method,t.operater_param,t.num,t.updated_time
    -> from t_operater_record t
    -> order by t.updated_time desc
    -> limit 950000,50;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996580 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+

客户的regdate列有索引,我的updated_time列也有索引。通过执行计划来看,确实不太理想,本地执行这个SQL耗时6.79s。

image

对SQL进行改写,优化后SQL的执行计划:

mysql> explain
    -> SELECT t.id,t.operationer,t.operater_method,t.operater_param,t.num,t.updated_time
    -> FROM t_operater_record t
    -> JOIN ( SELECT id
    -> FROM `t_operater_record`
    -> ORDER BY updated_time DESC
    -> limit 950000,50) a on t.id=a.id;
+----+-------------+-------------------+------------+--------+---------------+----------------+---------+------+--------+----------+-------------+
| id | select_type | table             | partitions | type   | possible_keys | key            | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------+------------+--------+---------------+----------------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2>        | NULL       | ALL    | NULL          | NULL           | NULL    | NULL | 950050 |   100.00 | NULL        |
|  1 | PRIMARY     | t                 | NULL       | eq_ref | PRIMARY       | PRIMARY        | 8       | a.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | t_operater_record | NULL       | index  | NULL          | i_updated_time | 5       | NULL | 950050 |   100.00 | Using index |
+----+-------------+-------------------+------------+--------+---------------+----------------+---------+------+--------+----------+-------------+

执行计划实际上是很优的。虽然从执行计划来看,子查询a这一步的rows值为950050,但是extra显示using index,表示这一步只访问了索引i_updated_time,没有回表操作,而扫描索引的操作是很快的;之后再以子查询返回的结果集为驱动表,与原表关联取完整数据。

这个SQL执行时间0.38s,执行时间大大提升。

image

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章