On December 25, when viewing the monitoring of a customer, it was found that the customer had executed page by page queries for more than 60s. The execution status is as follows:

This is a very simple and typical page query SQL. khome_space.regdate column has an index, and the table has more than three million rows of data. The offset of the query is too large, so the paging query is too time-consuming. Then I simulated this scenario in my own local environment, and optimized the query by taking the T operator record table of my own local 1 million data rows as an example.

Simulate the paging query operation of the customer, and view the corresponding execution plan:

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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+

The customer's regdate column has an index, and so does my updated time column. According to the execution plan, it is not ideal. It takes 6.79s to execute the SQL locally.

Rewrite SQL, and optimize the execution plan of 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 |
+----+-------------+-------------------+------------+--------+---------------+----------------+---------+------+--------+----------+-------------+

The execution plan is actually excellent. Although from the perspective of execution plan, the rows value of sub query a is 950050, but extra displays using index, which means that this step only accesses the index I ﹐ updated ﹐ time, and there is no table returning operation, while the operation of scanning index is very fast; then, the result set returned by sub query is used as the driving table to associate with the original table to get the complete data.

The execution time of this SQL is 0.38s, which greatly improves the execution time.