在前端页面显示，为了防止一次性展现全量数据，通过高低翻页或指定页码的形式查看局部数据，就像翻书一样，这就利用了 MySQL 的分页查问。
一、MySQL 的深分页
查问偏移量过大的分页会导致数据库获取数据性能低下，以如下 SQL 为例：
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
这句 SQL 会使得 MySQL 在无奈利用索引的状况下跳过 1000000 条记录后，再获取 10 条记录，其性能可想而知。这种查问偏移量过大的场景咱们称为深分页。
MySQL 的深分页会带来性能降落等问题，而这个问题在分布式数据库场景下，会变得更加简单。
二、分布式数据库的深分页
弹性数据库 JED 能够简略了解成分布式的 MySQL 数据库，这里以 JED 为例，介绍下大多数分布式数据库是如何做分页查问的。
2.1 弹性数据库的分页实现
以下图的例子，咱们来介绍多分片数据库如何执行分页查问。t\_order 表以 id 作为主键以 t\_col1 作为分片键，数据分布如下：
为了获取 t_order 表第 2 条之后的两条数据，执行 SQL：
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
再将返回的后果集在内存排序后，取第 2 条之后的两条数据{(id : 4, t\_col1 : “a”)，(id : 5, t\_col1 : “b”)} 返回用户。
2.2 深分页存在的问题
因为分布式场景下，分页语句会被放大。而这个问题，在执行深分页 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 危险。
三、VtDriver 的深分页优化
3.1 SQL 下推
VtDriver 对查问条件中带有分片键，仅落至繁多分片的查问进行进一步优化。落至单分片查问的申请并不需要改写 SQL 也能够保障记录的正确性，因而在此种状况下，VtDriver 并未进行 SQL 改写，从而达到节俭资源的成果。
3.2 流式解决
利用侧被动开启流式查问性能。开启流式查问后，采纳流式解决 + 归并排序的形式来防止内存的适量占用。因为 SQL 改写不可避免的占用了额定的带宽，但并不会导致内存暴涨。与直觉不同，大多数人认为 VtDriver 会将 1000010 * 2 记录全副加载至内存，进而占用大量内存而导致内存溢出。但因为每个后果集的记录是有序的，因而 VtDriver 每次比拟仅获取各个分片的以后后果集记录，驻留在内存中的记录仅为以后路由到的分片的后果集的以后游标指向而已。对于自身即有序的待排序对象，采纳归并排序，将会进一步升高性能损耗。
3.3 深分页主动转为流式查问
针对深度分页，VtDriver 提供了依据深度分页临界值，主动开启流式查问的形式。
利用可通过 deepPaginationThreshold 参数，设置深度分页临界值。比方 limit N,M，当 N >deepPaginationThreshold 设置的值时，会转为流式查问。
四、深分页的优化倡议
能够看到，即使 VtDriver 对于深分页进行了优化，然而深分页的应用场景还是会给利用带来了很大的压力。用户通过优化 SQL 才能够从根本上解决问题。
4.1 范畴查问
当能够保障 ID 的连续性时，用户依据 ID 范畴进行分页是比拟好的解决方案：
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
或通过记录上次查问后果的最初一条记录的 ID 进行下一页的查问：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10
4.2 子查问
把查问条件，转移回到主键索引。因为子查问中只获取主键列对应的值，能够肯定水平上升高利用 OOM 危险。
改写后的 SQL 为（id 为表 t_order 的主键）：
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;
数据量过大时，客户端仍有 OOM 危险，倡议把子查问仅作为应急过渡计划。
作者：京东批发 金越
起源：京东云开发者社区 转载请注明起源