数据库深分页介绍及优化方案

采查询偏移量过大的分页会导致数据库获取数据性能低下，以如下SQL为例：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

SELECT * FROM t_order ORDER BY id LIMIT 2, 2

假如只是简单的把SQL下推到每个分片的MySQL实例执行，再在内存中对返回结果进行聚合排序处理，会是什么效果呢？

分片1返回结果 {(id : 4, t_col1 : "a"), (id : 10, t_col1 : "a")};

分片2返回结果 {(id : 7, t_col1 : "b"), (id : 8, t_col1 : "b")}；

内存排序计算后，将结果{(id : 4, t_col1 : "a")，(id : 7, t_col1 : "b")}返回，显然这是一个错误的结果。为了得到正确的结果，需要每个分片都获取前4条（2+2）数据，之后在内存中进行排序后分页。因此，每个分片执行的SQL改写为：

SELECT * FROM t_order ORDER BY id LIMIT 0, 4

由于分布式场景下，分页语句会被放大。而这个问题，在执行深分页SQL时（查询偏移量过大），更加严重。深分页会导致数据库性能急剧下降，并且占用大量的CPU、内存资源用于聚合排序运算。

当执行以下SQL，获取1000000之后的10条数据：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

在多分片场景下，为了保证数据的正确性，SQL会改写为：

SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010

将改写后的SQL发送至每一个分片执行，并将结果集返回，对结果集汇总处理后，把排序后的10条记录返回给用户。可以发现原SQL仅需要传输10条记录至客户端，而改写之后的SQL则会传输1000010 * 2的记录至客户端，这将极大增大了OOM风险。

【 SQL的下推 】

VtDriver对查询条件中带有分片键，仅落至单一分片的查询进行进一步优化。落至单分片查询的请求并不需要改写SQL也可以保证记录的正确性，因此在此种情况下，VtDriver并未进行SQL改写，从而达到节省资源的效果。

【 流式处理 】

可以看到，即便VtDriver对于深分页进行了优化，但是深分页的使用场景还是会给应用带来了很大的压力。用户通过优化SQL才可以从根本上解决问题。

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id

SELECT * FROM t_order WHERE id > 100000 LIMIT 10

SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;