分页查询是在线业务中最常见的查询模式之一。对于小表来说，分页查询通常不会成为性能瓶颈；但在分布式数据库的大订单表场景下，分页查询面临着索引选择不稳定、回表代价高昂等一系列挑战。本文从实际线上案例出发，总结了 PolarDB-X 上大订单表分页查询的若干优化 insight。

需要说明的点：

文中的表结构和 SQL 均经过脱敏处理，不代表真实业务数据。

文中的优化思路基于 PolarDB-X 的分布式架构，部分思路也适用于其他分布式数据库。

图中省略了不影响理解的中间算子。

常见的订单表设计

PolarDB-X 的分区设计[1]中，订单表最常用的分区方式是一级 KEY 分区 + 二级 RANGE 分区：

一级 KEY 分区：以用户 ID 作为分区键，保证同一用户的数据落在同一个一级分区内，满足按用户维度查询的需求。

二级 RANGE 分区：以时间字段作为分区键，按日期划分。二级分区可以对接 TTL[2] 实现过期数据自动清理和分区自动滚动，也可以对接冷数据归档[3]将历史数据归档到 OSS，再通过冷热混合查询实现低成本的全量数据访问。

一个典型的订单表 DDL 如下（已脱敏）：

CREATE TABLE `t_order` (
    `id` bigint NOT NULL,
    `uid` bigint NOT NULL,
    `channel_id` int NOT NULL DEFAULT '0',
    `sub_id` bigint DEFAULT '0',
    `biz_type` tinyint NOT NULL,
    `product_id` bigint NOT NULL,
    `type` tinyint NOT NULL,
    `sys_type` tinyint NOT NULL DEFAULT '0',
    `state` tinyint NOT NULL,
    `order_price` decimal(32, 16) NOT NULL DEFAULT '0.0000000000000000',
    `order_qty` decimal(32, 16) NOT NULL DEFAULT '0.0000000000000000',
    `filled_qty` decimal(32, 16) NOT NULL DEFAULT '0.0000000000000000',
    `origin` tinyint NOT NULL,
    `client_order_id` varchar(64) DEFAULT NULL,
    `pnl` decimal(32, 16) NOT NULL DEFAULT '0.0000000000000000',
    -- ... 省略其他业务列 ...
    `pt` datetime(3) NOT NULL,
    `create_ts` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `update_ts` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`),
    KEY `idx_update_ts` (`update_ts`),
    KEY `idx_composite_1` (`uid`, `id`, `channel_id`, `product_id`, `biz_type`,
        `type`, `sys_type`, `client_order_id`, `origin`,
        `create_ts`, `update_ts`, `pt`, `sub_id`),
    KEY `idx_composite_2` (`uid`, `biz_type`, `id`, `channel_id`, `product_id`,
        `type`, `sys_type`, `client_order_id`,
        `create_ts`, `update_ts`, `pt`, `sub_id`),
    KEY `idx_uid_biz_prod` (`uid`, `biz_type`, `product_id`, `id`, `channel_id`,
        `type`, `sys_type`, `update_ts`, `pt`, `sub_id`,
        `origin`, `state`)
) ENGINE = InnoDB
PARTITION BY KEY(`uid`)
PARTITIONS 128
SUBPARTITION BY RANGE(TO_DAYS(`pt`))
(SUBPARTITION `p202308` VALUES LESS THAN (739129),
 SUBPARTITION `p202310` VALUES LESS THAN (739190),
 SUBPARTITION `p202311` VALUES LESS THAN (739220),
 SUBPARTITION `p202312` VALUES LESS THAN (739251),
 SUBPARTITION `p202401` VALUES LESS THAN (739282),
 SUBPARTITION `p202402` VALUES LESS THAN (739311),
 SUBPARTITION `p202403` VALUES LESS THAN (739342),
 SUBPARTITION `p202404` VALUES LESS THAN (739372),
 SUBPARTITION `p202405` VALUES LESS THAN (739403),
 SUBPARTITION `p202406` VALUES LESS THAN (739433),
 SUBPARTITION `p202407` VALUES LESS THAN (739464),
 SUBPARTITION `p202408` VALUES LESS THAN (739495),
 SUBPARTITION `p202409` VALUES LESS THAN (739525),
 SUBPARTITION `p202410` VALUES LESS THAN (739556),
 SUBPARTITION `p202411` VALUES LESS THAN (739586));

这张表有几个值得关注的设计特点：

宽表设计：列数多达 60+，单行数据量大，回表代价高。

多个覆盖索引：idx_composite_1、idx_composite_2 等索引包含了大量列，目的是覆盖常见查询模式，减少回表。

二级分区按月划分：每个一级分区下有十几个二级分区，随时间持续增长。

分页查询 Insight

对大订单表的分页查询，我们总结了以下五个关键 insight：

Insight	核心思想	解决的问题
过滤条件的分区裁剪	利用非分区键列与分区键的关联关系做分区裁剪	减少空扫分区
排序列的分区裁剪	利用排序列与分区键的大致单调关系做动态裁剪	避免长尾慢 SQL
索引有序性	将 OR/IN 条件拆分为多个有序子查询再归并	利用早停避免全表扫描
减少回表	覆盖索引 + 晚期物化 + 物理寻址优化	降低随机 IO
稳定性	索引选择需满足确定性方法论	防止选错索引导致雪崩

过滤条件的分区裁剪

用户经常查询最近修改过的订单详情，发起类似如下的查询：

SELECT * FROM t_order
WHERE uid = 12345678
  AND pt >= '2024-08-01 00:00:00.000'
  AND update_ts > '2024-11-13 14:20:00.000'
  AND update_ts < '2024-11-13 14:25:00.000'
  AND origin != 33
ORDER BY id DESC
LIMIT 0, 100;

对于小用户，uid 过滤后数据量不大，性能没有问题。但对于大租户，这条 SQL 会成为慢 SQL。原因在于：

pt >= '2024-08-01' 这个条件使得 SQL 需要扫描 p202408、p202409、p202410、p202411 四个二级分区。但 update_ts 的过滤条件限制为最近几天修改过的订单，而最近修改过的订单往往也是最近下单的订单，实际结果全部来自 p202411 分区，其余三个分区全是空扫。

空扫的代价很高：在大租户的分区上，空扫基本会把分区的数据都过一遍，反而成为长尾慢 SQL。所谓的过滤条件分区裁剪就是避免这种长尾。

排序列的分区裁剪

上一节讨论的是过滤条件中存在与分区键关联的列的情况。更常见的场景是：用户的 SQL 中只有 uid 作为过滤条件，没有任何时间相关的过滤条件，此时无法做 pt 的分区裁剪，SQL 会扫描对应一级分区下的所有二级分区。

SUBPARTITION `p202308` VALUES LESS THAN (739129),
SUBPARTITION `p202310` VALUES LESS THAN (739190),
...
SUBPARTITION `p202409` VALUES LESS THAN (739525),
SUBPARTITION `p202410` VALUES LESS THAN (739556),
SUBPARTITION `p202411` VALUES LESS THAN (739586))

假设p202410 分区已经满足了Top100，其余十几个分区满足条件的订单均不是Top100。

在分布式数据库中，逻辑 SQL 会被拆分成多条物理 SQL 分别下发到各个分区执行，最后做归并排序。问题在于：

分区	执行情况	耗时
p202410 / p202411	快速返回结果	毫秒级
p202308 ~ p202409	空扫一遍数据	秒级（长尾）

整条 SQL 的耗时取决于最慢的物理 SQL，即那些空扫的分区。

原有的归并逻辑：

所有分区的物理 SQL 都必须执行完成才能做归并排序，长尾分区拖慢了整体耗时。排序列的分区裁剪就是处理这样的长尾。

索引有序性

分页查询在行存上快的关键是早停：不能先过滤再排序取 TopK，而是直接利用索引有序性边过滤边取 TopK，拿到足够的行数就停止。

等值条件天然可以利用索引的有序性。但 OR 条件会破坏索引的有序性，导致无法早停。

SELECT * FROM t_swap
WHERE asset_a = 'TOKEN26' OR asset_b = 'TOKEN50'
ORDER BY id
LIMIT 100;

上述 SQL 中 asset_a 和 asset_b 分别有各自的索引，但 OR 条件拆开就可以索引的有序性来做 ORDER BY id 的早停。IN条件也是一种特殊的OR条件，也能做同样的处理。

SELECT * FROM (
    (SELECT * FROM t_swap WHERE asset_a = 'TOKEN26'
     ORDER BY id LIMIT 100)
    UNION
    (SELECT * FROM t_swap WHERE asset_b = 'TOKEN50'
     ORDER BY id LIMIT 100)
) t
ORDER BY id
LIMIT 100;

减少回表

对于宽表（60+ 列），SELECT * 的回表代价极高。回表的随机 IO 比顺序扫描慢几个数量级，是分页查询的主要性能瓶颈之一。

如果索引包含了查询需要的所有列，就不需要回表。上文中 idx_composite_1 等宽索引的设计目的正是如此。但覆盖索引的代价是索引本身占用大量存储空间，且写入时需要维护更多的索引。

当覆盖索引无法覆盖 SELECT * 时，可以使用晚期物化：先通过覆盖索引拿到满足条件的主键列表（只取 LIMIT 行），再用主键回表获取完整数据。

原始 SQL：

SELECT * FROM t_order
WHERE uid = 1
  AND channel_id = 0
  AND sub_id IN (0)
  AND id > 123456789
ORDER BY update_ts DESC
LIMIT 0, 20;

改写为晚期物化：

SELECT t_order0.*
FROM (
    SELECT id, uid, pt
    FROM t_order 
    WHERE uid = 1
      AND channel_id = 0
      AND sub_id IN (0)
      AND id > 123456789
    ORDER BY update_ts DESC
    LIMIT 20
) AS t3
INNER JOIN t_order AS t_order0
  ON t3.id = t_order0.id
  AND t3.uid = t_order0.uid
  AND t3.pt = t_order0.pt
ORDER BY t_order0.update_ts DESC
LIMIT 20;

内层查询通过覆盖索引 idx_composite_1 只取主键和分区键，避免了大量的随机回表 IO。外层查询只对 LIMIT 行做精确的主键回表，回表次数从全量扫描降低到最多 20 次。

除了 SQL 层面的优化，PolarDB-X 在存储引擎层面也做了索引回表的优化，包括 GPP（Guess Primarykey Pageno）和物理寻址优化[4][5]，通过减少回表时的 IO 放大和提升缓存命中率来加速回表操作。

稳定性

对大订单表来说，内存永远是不够的。一旦选错索引，大量数据被加载到 buffer pool 中，会污染缓存导致雪崩。分页查询的过滤条件千变万化，索引选择缺乏确定性，随时可能触发雪崩。

分页查询的索引设计有一套通用的方法论：

检查项	建议	原因
排序列索引化	必须	避免 Using filesort，这是分页慢的头号杀手
联合索引顺序	WHERE 等值列 → ORDER BY 列 → SELECT 列	符合 B+ 树最左匹配原则，利用索引排序
覆盖索引	尽量包含 SELECT 的所有字段	减少回表次数，尤其在 Offset 较大时
区分度高的列在前	WHERE 条件中区分度高的列放前面	快速缩小扫描范围
避免函数操作	不要对索引列做函数运算	会导致索引失效，触发全表扫描
隐式类型转换	确保参数类型与字段类型一致	字符串字段传数字会导致索引失效

如今用户的 SQL 索引优化通常会交给 AI，AI 会设计出满足上述方法论的索引。数据库只要能做到索引选择满足这套方法论，就可以保证稳定性。换言之，数据库的索引选择行为需要与 AI 推荐的索引设计方案保持一致，才能在生产环境中获得确定性的性能表现。

效果分析

为了验证上述各项优化的实际收益，下面列出用户的真实场景逐项对比。

对比项	某分布式数据库	PolarDB-X
SQL1（过滤条件的分区裁剪）	5秒	0.01秒
SQL2（排序列的分区裁剪）	3秒	0.05秒
SQL3（索引有序性）	10秒	0.001秒
SQL4（晚期物化）	10秒	0.6秒
索引选择稳定性	无保证	稳定
高峰QPS	4千（crash，升配无法缓解）	6万（可线性拓展）

总结

本文从实际线上案例出发，总结了 PolarDB-X大订单表分页查询的五个优化 insight。这些优化覆盖了从分区裁剪、索引利用、回表优化到稳定性保障的完整链路，核心思想可以归纳为：

利用数据的时间局部性：过滤条件与时间分区键存在联系，可以用于动态裁剪分区。

利用排序列的单调性：排序键与时间分区键存在联系，可以用于动态裁剪分区。

利用索引有序性：通过条件拆分，将扫描范围从全量降低到 LIMIT 行级别。

减少IO：通过覆盖索引、晚期物化、索引回表优化等手段，大幅降低随机IO。

保证确定性行为：索引选择需要满足通用方法论，避免因选错索引导致缓存污染和雪崩。

千亿行数据下的分页噩梦：PolarDB-X 如何在万级 QPS 场景中实现丝滑查询？

常见的订单表设计

分页查询 Insight

过滤条件的分区裁剪

排序列的分区裁剪

索引有序性

减少回表

稳定性

效果分析

总结

参考

PolarDB开源

热门文章

最新文章

相关电子书