MySQL分页查询优化案例

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

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

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