海量数据分页查询效率低？一文解析阿里云AnalyticDB深分页优化方案

阿里云瑶池数据库 2025-05-13

213

1. 背景

在企业的实际应用中，深分页是一种常见的高成本查询场景，通常指用户需要从海量数据结果集中获取靠后页码（如1000页之后）的数据，随之而来的是I/O开销激增、查询延迟显著上升等性能问题。比如在电商商品查找场景中，通常按销量、评分等排序，使用户能快速找到优质商品。为避免一次性加载过多数据导致卡顿，应用端常采用分页展示。

常规做法是按照所需列进行排序后利用 limit + offset 实现，这样的查询称为分页查询，其中 limit 为每页需要展示的数据条数，offset 为每页数据的起始偏移。若系统每页展示 100 条数据，那么查询第 1 页的 SQL 为：

SELECT * FROM t_order ORDER BY id LIMIT 0, 100

查询第 10001 页的 SQL 为：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 100

1.1 什么是深分页问题？

当请求的分页较深时，全局排序开销和频繁的回表开销会导致数据库性能急剧下降，这样的问题称为深分页问题，比如上面例子中查询第 10001 页的数据。

在云原生数据仓库AnalyticDB MySQL（以下简称ADB）这样的分布式数据库下会更复杂。为了减少不同节点间 shuffle 的数据量，往往每个 worker (存储数据的节点) 会先在本地做一次 TopN 计算，再由单节点对每个 worker 的执行结果进行聚合，排序后产生最终的结果集返回。

所以为了保证最终的正确性，需要对 SQL 语句进行改写交给每个 worker 执行。例如，查询第 10001 页，每个 worker 收到的查询为：

SELECT * FROM t_order ORDER BY id LIMIT 0, 1000100

原本只要 100 条数据，最后却要由单节点对 1000100 × worker数条数据进行全局排序，排序的数据量随分页深度线性增长，性能也会急剧下降。同时不落盘的情况下排序完全基于内存进行，极大增加了 OOM 风险。

1.2 为什么在AnalyticDB使用深分页？

▶︎ 1.2.1 数据导出

在做数据导出时，一次性读取过多数据容易造成客户端不稳定，所以往往会通过分页查询的方式分批拉取结果，这也是在 ADB 深分页的绝大多数场景。

由于在分布式环境下，直接使用 limit offset 的方式并不会保证数据以确定的顺序被处理，意味着同一页数据每次查询时可能会返回不同的结果，所以需要通过显式增加 order by 来保证数据导出时的不重不漏。

▶︎ 1.2.2 全量结果按需（分页）展示

查询的全量结果暂存在ADB服务端，通过分页的方式按需展示。

▶︎ 1.2.3 业务报表并发控制

多个用户并发查询同一张报表时，以往实现的是每个请求都独立发起查询，这样不仅会增加集群负载，还可能导致数据一致性问题。

通过本优化，可以达到一段时间窗口内全局一个query的效果，显著提升查询性能和集群稳定性。

2. AnalyticDB深分页解决方案

2.1 整体方案介绍

从上述场景和用户痛点出发，ADB对深分页进行专项优化，解决深分页查询的性能问题。主要思想是基于快照缓存。首次查询时会生成并缓存查询去掉 limit offset 后的结果集的快照，在元数据表中维护缓存的相关信息，通过 id 标识，以下称为 paging_id。

后续每次分页请求会通过 SQL 计算出 paging_id，找到对应快照数据，批量拉取结果。使用深分页优化后并不保证缓存数据与原始数据的强一致性。

2.2 关键链路

▶︎ 2.2.1 内部转换逻辑

a. ADB 接收到带深分页优化 hint 的分页查询，解析并进行必要检查（如是否包含 limit offset），若不符合要求则报错。

b. 解析分页SQL，去掉 limit offset 后根据 pattern 计算哈希生成 paging_id，检查元数据表中是否存在对应分页缓存。若无缓存，则进入“创建分页缓存模式”；若有，则进入“查询分页缓存模式”。

c. 创建分页缓存模式：服务端内部会执行分页SQL 去掉外层 limit offset 的部分，并生成数据快照缓存下来，整个过程异步执行，产生的缓存文件会同步到cache file meta表中。

d. 查询分页缓存模式：根据 paging_id 查找分页缓存元数据，检查通过后定位对应缓存文件及所在节点，下发任务读取数据。若数据仍在准备中，查询将进入阻塞。

▶︎ 2.2.2 缓存自动清理逻辑

根据过期时间淘汰：统计的是最后一次被访问的时间间隔，默认为 10 分钟，当某个分页缓存在该时间间隔内没有被访问，则会被自动删除。

▶︎ 2.2.3 设置缓存有效时间

分页缓存支持设置缓存有效时间。超过有效时间后，缓存失效，后续同一Pattern的查询会重新访问数据库查询数据，并更新缓存表，通常应用在报表并发控制场景中。

2.3 优化来源分析

深分页的代价根源是结果集全局排序，ADB 针对这一点进行了优化，以下面简单的例子进行分析：

假设要读取 1-100 页数据，每页有 100 条记录。

普通分页查询

每次都需要在每个存储数据的节点上查询排在最前面的 offset + 100 条数据，然后由单节点负责收集，将节点数 × (offset + 100) 条数据聚合起来进行一次全局排序，取最终的从 offset 开始的 100 条数据。这个过程中每个worker都要对本地存储的数据进行多次排序计算【如上图红色虚线框所示】，且最后全局排序的数据量与offset线性相关【如上图红色实线框所示】。

开启深分页优化

创建分页缓存阶段会在每个节点上查询分页SQL 去掉 limit offset 部分的所有数据，后续分页查询会根据 limit 和 offset 按一致的确定顺序读取缓存数据。【数据导出场景示意图如上所示】

相比普通分页查询，开启深分页优化后，对于数据导出场景，会优化掉不必要的 order by，执行效率和资源消耗会大幅降低；对于全量结果按需展示场景，也只存在一次全局排序，且每个分片只查询了一次。

3. 深分页优化方案收益（部分结果）

基于TPCH 100G对数据导出场景的优化收益进行评估，测试集群包含6个worker，导出数据 100w 条，每页 10w 条数据。

-- 使用普通分页
select * from lineitem order by l_orderkey,l_linenumber limit 0,100000


-- 开启深分页优化
/*paging_cache_enabled=true*/
select * from lineitem limit 0,100000