MySQL分页查询优化案例

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

MySQL分页查询优化案例

这是一个很简单、典型的分页查询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。

MySQL分页查询优化案例

对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,执行时间大大提升。

MySQL分页查询优化案例

上一篇:explain的type列解析


下一篇:创业公司做数据分析(四)ELK日志系统