SQL 语句分页优化是否正确？

[mao_siyu (毛巳煜)]

【TiDB 版本】：

v3.0.5

【前置条件】：

150万数据，分页查询，每页1万条

【优化前】：

MySQL [pressure_table1]> EXPLAIN ANALYZE
    -> SELECT
    ->     code1,
    ->     code2,
    ->     code3,
    ->     normal1,
    ->     date1
    -> FROM
    ->     dc_sale
    -> WHERE
    ->     Is_Del = '0'
    -> ORDER BY id
    -> LIMIT 740000, 10000;
+--------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
| id                       | count      | task | operator info                                                                                                                                                                                                                                                                                       | execution info                                                                     | memory               |
+--------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
| Projection_8             | 10000.00   | root | pressure_table1.dc_sale.code1, pressure_table1.dc_sale.code2, pressure_table1.dc_sale.code3, pressure_table1.dc_sale.normal1, pressure_table1.dc_sale.date1 | time:5.455002597s, loops:11, rows:10000                                            | N/A                  |
| └─TopN_11                | 10000.00   | root | pressure_table1.dc_sale.id:asc, offset:740000, count:10000                                                                                                                                                                                                                    | time:5.454709343s, loops:11, rows:10000                                            | 145.3629379272461 MB |
|   └─TableReader_21       | 956849.86  | root | data:Selection_20                                                                                                                                                                                                                                                                                   | time:1.069426628s, loops:1019, rows:1041178                                        | 53.48866653442383 MB |
|     └─Selection_20       | 956849.86  | cop  | eq(pressure_table1.dc_sale.Is_Del, "0")                                                                                                                                                                                                                                       | proc max:538ms, min:58ms, p80:442ms, p95:538ms, rows:1041178, iters:1519, tasks:11 | N/A                  |
|       └─TableScan_19     | 1477850.00 | cop  | table:dc_sale, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                 | proc max:530ms, min:55ms, p80:431ms, p95:530ms, rows:1503640, iters:1519, tasks:11 | N/A                  |
+--------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
5 rows in set (5.46 sec)

MySQL [pressure_table1]>

【优化后】：

MySQL [pressure_table1]> EXPLAIN ANALYZE
    -> SELECT
    -> *
    -> FROM
    -> (
    ->     SELECT
    ->     Is_Del,
    ->     code1,
    ->     code2,
    ->     code3,
    ->     normal1,
    ->     date1,
    ->     ROW_NUMBER ( ) OVER ( ORDER BY id ) AS row_num
    ->     FROM
    ->     dc_sale
    ->     WHERE
    ->     Is_Del = '0'
    ->    ) t
    -> WHERE
    -> t.row_num > 740000
    -> LIMIT 10000;
+--------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
| id                             | count      | task | operator info                                                                                                                                                                                                                                                                                                                                                                                               | execution info                                                                     | memory               |
+--------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
| Projection_13                  | 10000.00   | root | pressure_table1.dc_sale.Is_Del, pressure_table1.dc_sale.code1, pressure_table1.dc_sale.code2, pressure_table1.dc_sale.code3, pressure_table1.dc_sale.normal1, pressure_table1.dc_sale.date1, row_num                                          | time:2.2110195s, loops:11, rows:10000                                              | N/A                  |
| └─Limit_16                     | 10000.00   | root | offset:0, count:10000                                                                                                                                                                                                                                                                                                                                                                                       | time:2.210741782s, loops:11, rows:10000                                            | N/A                  |
|   └─Selection_17               | 10000.00   | root | gt(row_num, 740000)                                                                                                                                                                                                                                                                                                                                                                                         | time:2.210717323s, loops:10, rows:10000                                            | N/A                  |
|     └─Window_18                | 10000.00   | root | row_number() over(order by pressure_table1.dc_sale.id asc)                                                                                                                                                                                                                                                                                                                            | time:2.165769184s, loops:733, rows:750592                                          | N/A                  |
|       └─Projection_23          | 956849.86  | root | pressure_table1.dc_sale.code1, pressure_table1.dc_sale.code2, pressure_table1.dc_sale.code3, pressure_table1.dc_sale.normal1, pressure_table1.dc_sale.date1, pressure_table1.dc_sale.Is_Del, pressure_table1.dc_sale.id | time:2.050503838s, loops:1018, rows:1041178                                        | N/A                  |
|         └─IndexLookUp_22       | 956849.86  | root |                                                                                                                                                                                                                                                                                                                                                                                                             | time:2.103801808s, loops:1018, rows:1041178                                        | 32.93011474609375 MB |
|           ├─IndexScan_19       | 1477850.00 | cop  | table:dc_sale, index:id, range:[NULL,+inf], keep order:true                                                                                                                                                                                                                                                                                                                           | proc max:515ms, min:371ms, p80:515ms, p95:515ms, rows:1503640, iters:1478, tasks:2 | N/A                  |
|           └─Selection_21       | 956849.86  | cop  | eq(pressure_table1.dc_sale.Is_Del, "0")                                                                                                                                                                                                                                                                                                                                               | proc max:70ms, min:0s, p80:41ms, p95:59ms, rows:1041178, iters:4654, tasks:710     | N/A                  |
|             └─TableScan_20     | 1477850.00 | cop  | table:dc_sale, keep order:false                                                                                                                                                                                                                                                                                                                                                            | proc max:70ms, min:0s, p80:41ms, p95:58ms, rows:1503640, iters:4654, tasks:710     | N/A                  |
+--------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
9 rows in set (2.22 sec)

MySQL [pressure_table1]>

感觉优化前后的结果 差距不大，我的语句是不是写的有问题？

[yilong (yi888long)]

是指执行时间吗？ 看起来从9s到5s了，是需要多少时间满足要求？

[mao_siyu (毛巳煜)]

https://www.jianshu.com/p/7a2fd8c2af25?from=timeline

我根据这篇文章对SQL做了调整

大概明白了，分页之所以慢是因为, 慢在了offset上，从上面的查询计划中可以看出来，优化前的语句，消耗了两次内存，第一次是在扫表，而第二次是执行 offset, 消耗的内存更大

而优化后的语句，是只做了一次的全表扫然后排序，之后的操作只是对结果数据进行截取，并且只消耗了一次内存

[yilong (yi888long)]