做了这么多年大数据平台开发我一直觉得”查询结果分页”是OLAP场景里最容易被低估的一个坑。很多从业务系统转过来的同学习惯了MySQL里那种LIMIT 10 OFFSET 20000的写法顺手就搬到了Hive、ClickHouse或者Doris上结果要么慢到怀疑人生要么直接把查询节点的内存打满然后对着报错日志一脸懵。这篇文章我就把OLAP里查询结果分页这件事彻底讲透底层为什么慢、主流引擎各自的坑在哪、有哪些能真正落地的分页方案以及我在实际项目里踩过的那些坑和对应的排查思路。这文章不是给你背概念的是给你抄作业的。适合正在做数据平台、报表系统、自助分析工具或者刚接触大数据OLAP查询的工程师。不管你是用Hive、Spark SQL、ClickHouse还是Doris只要涉及”大结果集翻页”这个需求下面这些内容基本都能对上号。1. 先说清楚OLAP分页为什么让你脑壳疼1.1 你以为的”下一页”和大数据里的”下一页”不是一回事做业务系统的时候分页查询的模型很简单查一张表按某个字段排序然后跳过前面的记录取后面的。底层数据库有索引OFFSET跳过的那些记录走索引也能快速定位。你翻到第100页数据库也不用真的把前990条数据读出来再扔掉。但OLAP场景完全是另一套逻辑。OLAP引擎比如ClickHouse、Doris、Hive它们的设计目标是海量数据的聚合分析表动辄几亿几十亿行数据分布在几十上百个节点上。你想要一个”按某个指标排序后的整体结果”这个顺序在分布式环境下本身就是被”硬算”出来的——得先把所有相关数据扫描出来全部汇聚到某个节点做全局排序然后才能谈”截取一段”。所以你翻页翻得不深还好说一旦深了比如OFFSET 100000引擎要做的事情是把满足查询条件的所有数据全部排好序然后从前到后数出整整10万条再丢掉最后才把你想要的20条返回。前面那10万条虽然不返回给前端但它们被扫描了、被排序了、被遍历了一分钱成本都不会少。我把这个情况类比成”去图书馆找书”业务系统里翻页是你知道书在哪个书架哪一层直接走过去抽出来OLAP深分页是管理员为了帮你找某一页把整个图书馆的书全部搬出来按字母排一遍序再从第一本数到你想要的位置。这中间浪费的人力物力全得你买单。1.2 OLAP引擎的底层特性决定了OFFSET是毒药OLAP引擎普遍有几个共性每一个都踩在OFFSET分页的死穴上。第一全量扫描是常态。OLAP表基本是列式存储为了分析性能很少为某个查询条件单独建索引。即使你只需要返回20条记录引擎也得把分区内所有满足条件的列数据读一遍做过滤、聚合、排序之后才能确定”这20条到底是哪20条”。注意这一步的成本是固定的不因为你只要20条就变少而是取决于符合条件的总数据量。第二全局排序代价高。分布式场景下想要一个全局有序的结果就得把所有节点的数据发送到一个协调节点做一次归并或整体排序。你翻第3页和翻第300页前面这个排序和网络传输过程完全一样。可以说每次翻页引擎都在重复执行一次同样的”全量排序”工作没有任何增量复用的可能。第三内存和临时文件是隐形成本。ClickHouse这类MPP引擎在执行ORDER BY ... LIMIT ... OFFSET ...时需要把排序结果放在内存或临时文件里。OFFSET越大引擎在计数时需要遍历的元素就越多某些实现下内存占用也跟着涨。我就见过同事在一个5亿行的Kafka明细表上做深分页明明只是翻到第2000页结果ClickHouse节点内存被撑爆直接OOM重启。所以说OLAP分页慢不是某一种引擎做得不好而是这一类引擎的设计哲学和OFFSET分页天然冲突。你没法改变引擎的底层逻辑只能改变你分页的方式。这也是这篇文章后面所有方案的核心出发点不再依赖OFFSET去跳过数据而是让每次查询只扫描你真正需要的那一小部分数据。2. 主流OLAP引擎的分页能力逐一盘点2.1 Hive与Spark SQL经典批处理引擎的分页困境先说说大家最熟悉的Hive。早期版本的Hive只支持LIMIT N不支持LIMIT N OFFSET M。你想翻页没门要么全取回来自己在应用层切要么只能用WHERE row_number() OVER (...)这种曲折的方式。后来Hive 2.x开始支持LIMIT M, N和LIMIT N OFFSET M但性能现实很骨感。因为Hive本身是跑MapReduce或Tez的批处理框架一次查询要经过完整的任务调度。你写个OFFSET进去Hive并不会神奇地跳过前面M条它依然要做全表扫描、全局排序最后在Reduce阶段把前M条数据丢弃。翻页越深丢得越多浪费越大。Spark SQL的情况稍微好一点因为得益于内存计算速度比Hive快。而且Spark 3.4之后对OFFSET有专门的物理计划优化能避免不必要的shuffle某种程度上缓解了深分页的性能问题。但注意这个优化只针对”OFFSET较小”的场景有效。你让我翻到第500万条Spark照样逃不过全局排序。我实测过在Spark 3.4上翻一个5亿行的事实表OFFSET超过50万之后查询耗时基本呈线性上涨因为每次翻页都是重新扫描、重新排序、重新跳过。这两种引擎更适合的定位是”离线的批量计算”而不是”在线的高频翻页查询”。如果你确实要在Hive或Spark SQL上做分页我的建议是别频繁翻页尽量一次性把结果集控制在一个合理规模比如通过分区过滤、字段裁剪、预聚合把数据量压到万级以内再用普通LIMIT OFFSET或者干脆导出到MySQL再做分页。2.2 ClickHouse与DorisMPP引擎的内存红线ClickHouse社区版单表查询能力极强也是很多公司做OLAP报表的首选。它支持标准的LIMIT N OFFSET M语法小OFFSET下很快毫秒级返回都没问题。但问题恰恰出在”深分页”这三个字上。我拆过ClickHouse的执行计划ORDER BY ... LIMIT ... OFFSET ...在底层会先构建一个排好序的Block然后从最前面开始计数。OFFSET大意味着它要在内存中维护更多的临时数据。如果你还开启了max_bytes_before_external_sort来避免内存溢出那数据会落盘到临时文件速度直接掉一个量级。另一个隐藏问题ClickHouse对查询内存有max_memory_usage限制深分页的查询很容易触顶然后抛异常告诉你”Memory limit exceeded”。这个报错我部门至少每两周出现一次基本都来自线上报表系统的深翻页。Doris和StarRocks在分页机制上更依赖MPP架构支持LIMIT OFFSET同时它们的执行引擎会对OFFSET做分区级别的并行优化。但Doris社区版对单查询的内存限制同样严格大OFFSET时某个BE节点内存被打满的情况我也遇到过。StarRocks在LIMIT OFFSET场景下的表现相对好一些因为它有全局并行扫描加执行翻页覆盖的数据如果恰好能走分区裁剪性能会意外地好。用MPP引擎做分页我总结了一个经验阈值OFFSET小于1万老老实实用LIMIT OFFSET性能完全可接受OFFSET超过1万就得换方案了。这个阈值不是严格标准但它在ClickHouse和Doris上表现很稳定大家可以当参考。2.3 Presto/Trino查询编排器的深分页代价Presto和Trino的定位是交互式查询引擎它的典型用法是”快速跑一条复杂的分析SQL”而不是给前端提供高频翻页接口。官方文档里也明确说OFFSET操作是非常低效的因为它需要等待所有行都从Worker节点传输到Coordinator节点然后在Coordinator上执行排序、过滤、跳过。整个过程中的网络开销会随OFFSET线性增长。我过去在Presto上试过做一个明细查询的分页数据量4000万行翻到第50页就已经明显卡顿了。后面改成键集分页下面会讲每次查询扫描的行数从几百万降到几百行查询耗时几乎恒定在200毫秒以内。这个对比非常直观也说明了一个道理不是Presto不行而是分页写法不对给它再大的集群都白搭。如果你所在公司用Presto/Trino作为统一查询引擎下面这些建议值得保留第一能走分区过滤就一定带上分区字段第二排序字段尽量选高基数的唯一键避免大量重复值导致排序不稳定第三用键集分页代替OFFSET这是Presto社区公认的最佳实践。3. 三种能落地的OLAP分页方案3.1 LIMIT OFFSET的“能用但别浪”场景先给LIMIT OFFSET一个公正的评价它不是完全不能用而是要用在合适的场景里。什么时候适合用第一OFFSET数值很小一般不超过1万。比如用户刚进系统的首页看前几页数据或者你只需要”前100条热点数据”这种场景OFFSET几乎可以忽略不计。第二查询条件已经做了强过滤比如按天分区每天的数据量只有几千行。这种情况下即使翻到第200页实际扫描的数据也很少性能完全兜得住。第三结果集已经被落到了MySQL/PostgreSQL这类OLTP库那分页就是它们的强项放心用。什么时候千万别用我总结了三类一是OFFSET超过10万的深翻页二是数据量过亿分区过滤后还有千万级数据三是高频接口每秒钟几十上百次的翻页请求。这三类场景下OFFSET分页轻则拖垮接口响应时间重则打爆引擎内存。我自己现在定了一个规矩任何新建的分页查询只要看到SQL里有OFFSET就多问一句”这个OFFSET最大会到多少”。如果业务上真的允许用户翻几百页那就默认OFFSET方案不通过直接转键集分页。这个习惯帮我挡掉了很多晚上的告警电话。3.2 键集分页Keyset Pagination的正确姿势这是我在OLAP场景下最推荐也是用得最多的方案。核心思路就一句话不跳数据而是记住“上一页最后一个位置”下一页从这个位置继续往后取。因为不再需要跳过前面的记录每次查询只需要扫描目标行附近的数据成本几乎恒定。你可以把键集分页想象成”续读”你上次看到第100节记住页脚标着143页下次直接翻到143页开始看而不是从第1页慢慢数过来。这正好绕开了OLAP引擎最怕的”全量排序跳过”过程。具体怎么做假设你有一个订单事实表orders按order_id排序分页每页20条。普通写法SELECT order_id, user_id, amount, order_time FROM orders WHERE dt 2024-06-01 ORDER BY order_id LIMIT 20 OFFSET 0;这是第一页没毛病。第二页不要再传OFFSET20了而是把第一页最后一条记录的order_id作为参数传进来SELECT order_id, user_id, amount, order_time FROM orders WHERE dt 2024-06-01 AND order_id 上一页最后一条order_id ORDER BY order_id LIMIT 20;这样一来引擎只需要从索引或分区内定位到order_id xxx的第一个位置然后顺序往后扫20条完事。查询耗时跟翻到第几页几乎没关系。如果排序字段有多个比如业务要求ORDER BY amount DESC, order_id ASC键集条件需要变成复合条件WHERE amount 上一个amount OR (amount 上一个amount AND order_id 上一个order_id) ORDER BY amount DESC, order_id ASC LIMIT 20;这个写法要特别注意条件里的比较方向必须和排序方向完全对应。amount DESC所以第二页要取的是amount更小的记录如果amount相同用order_id做二次排序就顺着排序方向走。一旦方向写反你会在下一页面看到上一页已经出现过的数据大概率被业务方投诉。键集分页有一个硬性前提排序字段必须是唯一的或者至少是”字段组合后唯一”。比如ORDER BY amount本身不够因为存在大量金额相同的记录无法确认”上一页边界”到底属于谁。解决办法是把唯一键比如order_id加进排序字段保证每个位置都是确定性的。这个方案的缺点也很明确第一你不能直接跳到第186页只能一页一页往后翻。第二上一页到下一页之间如果有新增数据排序位置会发生偏移可能造成少量数据重复或漏读。对于大多数报表和查询场景这完全可以接受。如果你要的是”绝对不重不漏”且”支持任意跳页”那请继续看下面的物化方案。3.3 结果集物化用空间换时间的“伪分页”还有一个思路思路特别简单既然OLAP引擎不适合高频深翻页那我干脆把结果集先算出来、存起来再来翻页。这就是物化方案。实操上有两种常见做法。第一种是落临时表。对一个大查询执行一次把结果写入一张临时表或者新表后续分页查询直接查这张表。我常用Hive SQL来实现CREATE TABLE tmp_query_result AS SELECT user_id, amount, order_time FROM orders WHERE dt 2024-06-01 AND amount 1000 ORDER BY amount DESC;然后分页就变成对这张小表的常规查询可以用LIMIT OFFSET也可以继续用键集怎么方便怎么来。临时表本身带有主键和索引的话性能甚至和单机数据库没区别。这个方案特别适合那种”查询条件复杂、结果集规模可控百万级以内、前端可能要反复翻页”的场景。第二种是落Redis或内存缓存。查询结果集不大时几十万条以内把主键列表或者完整JSON存到Redis的List结构前端每次翻页就是在Redis上做一个LRANGE性能快到飞起。我做过一个线上自助报表工具复杂查询聚集了十几万条结果第一次查询把ID列表写入Redis之后的每次翻页都走Redis List接口P99从800毫秒降到5毫秒。代价是缓存一致性要处理数据变更时得清缓存或做TTL过期。物化方案最大的优势是总页数可以精确计算也支持任意跳页。业务方如果要”跳转到第50页”这种操作它是唯一合适的方案。我个人的习惯是优先键集分页做下一页业务方明确需要跳页和总页数才用物化方案。因为物化需要额外的存储和缓存管理成本查询的实时性也会打折扣不适合数据实时更新的场景。提示物化结果集一定记得设置时效性或手动清理机制。我见过有人物化了一张全表的大结果表放在Hive里忘清理结果占了几十个T的存储被运维同事找上门。用完就删或者加分区按天管理别手软。4. 结合真实项目从方案选型到上线踩坑4.1 评估与选型的主要参考指标每次接到分页需求我不会直接上手写SQL而是先过一遍下面这张评估表。这个问题清单看起来很基础但能让你少走很多弯路。评估维度关键问题影响数据规模参与查询的明细/聚合结果有多少行决定是否需要用物化方案翻页深度用户会翻到第几页是否允许任意跳页OFFSET超过1万基本要换方案查询频率接口调用频率是多少并发多少影响是否需要缓存层实时性要求数据更新后秒级、分钟级还是小时级可见物化方案的缓存时效必须覆盖排序字段是否稳定唯一是否支持索引定位决定键集分页是否可行是否必须显示总页数业务方要显示“共356页”吗键集分页做不到必须物化或近似值有一次做账单查询需求业务方在PRD里写了个”分页展示”我一看数据源是ClickHouse里十亿级的账单流水本能觉得不对。拿评估表去对翻页深度不定但用户确实会搜一个人的全部账单翻个几十页排序字段是账单ID唯一稳定实时性要求分钟级不要总页数。最终选了键集分页上线后稳定运行没有一例深分页超时。如果当时用OFFSET方案十亿级数据深翻页ClickHouse内存早就爆了。4.2 游标式分页接口的完整落地步骤实际开发中我不建议把键集逻辑直接暴露给前端。更好的做法是在接口层做成游标Cursor分页前端只记住一个不透明的字符串cursor后端负责解析它转换成查询条件。下面是完整的落地步骤这个流程我在几个项目里都用过基本可以照抄。第一步确定排序与游标字段。选一个唯一且稳定的字段组合比如order_id或(create_time, order_id)。这个字段同时是数据库里的排序键能支撑高效的定位查询。第二步定义游标编码格式。我常用JSON串加Base64编码。一个经验是别把游标搞太复杂只包含必要的定位信息就行。{ last_order_id: 202406010012345, last_create_time: 2024-06-01 12:30:00 }Base64编码后作为前端拿到的next_cursor。第三步写后端SQL模板。以ClickHouse为例第一页的SQLSELECT order_id, user_id, amount, create_time FROM orders WHERE dt {partition_date} ORDER BY create_time DESC, order_id DESC LIMIT {page_size};后端拿到游标后解析出上一页的边界值拼接第二页SQLSELECT order_id, user_id, amount, create_time FROM orders WHERE dt {partition_date} AND (create_time {last_create_time} OR (create_time {last_create_time} AND order_id {last_order_id})) ORDER BY create_time DESC, order_id DESC LIMIT {page_size};注意这里create_time DESC时第二页应该是更早的时间所以比较符号要用。方向要对齐不然就乱了。第四步设计返回体。一个典型的游标分页响应长这样{ data: [], next_cursor: eyJsYXN0X29yZGVyX2lkIjo..., has_more: true }has_more的判断方式是查询时多取一条看有没有第page_size 1条记录。有就说明还有下一页但不返回这一条。第五步全链路压测。压测的重点不是功能而是确认深翻页的耗时是否稳定。我习惯把用户从头翻到第50页的过程完整走一遍看每一页的P99延迟。键集分页理想的曲线应该是平直的不能越翻越慢。如果出现越翻越慢多半是游标里的定位字段没走索引或者排序方向写错导致引擎扫描范围没有收窄。4.3 优化前后的实测效果对比这里放一个真实的压力测试对比数据来自我之前团队维护的ClickHouse集群表是订单明细表共6亿行按天44个分区查询条件限定在某一天的数据单分区约3000万行。每页20条连续翻30页。OFFSET方案的平均查询耗时从第1页的120毫秒到第30页的3.8秒呈明显上升趋势第30页的查询扫描行数约为3600万行内存峰值接近2GB。键集分页方案从第1页到第30页耗时始终稳定在180到240毫秒之间扫描行数约为每页20到40行内存占用基本可以忽略。这就是数学上的差距一个和OFFSET成正比一个和页大小成正比。这个对比非常直观也印证了那句话方案选对了集群都不用扩。还有一个需要注意的细节如果业务方坚持要做”总页数”键集分页给不了只能用物化方案或统计近似值。我一般先问清楚这个总页数是精确值还是可以近似。很多报表场景下前端只是想展示一个”共X页”的文本用查询结果集大小除以页大小估算就能满足没必要精确到行。5. 常见问题与排查技巧实录5.1 深分页内存溢出OOM怎么排查接触过OLAP引擎的人一定会遇到这个问题。我归纳了四条排查路径按优先级排序。第一看OFFSET的大小。如果你写的是LIMIT 20 OFFSET 10000000那OOM几乎必然发生。排查时第一时间把OFFSET降到1万以内如果问题消失就确认是深分页造成的。对策是换键集分页。第二看查询里有没有全局排序。ORDER BY字段特别多、排序值过大会让中间结果膨胀。ClickHouse中可以通过配置max_bytes_before_external_sort把排序数据落盘减轻内存压力但耗时增加。Doris可以通过限制并行度来降低单节点内存峰值。这些是治标治本是减少排序数据量。第三看分区裁剪是否生效。我见过很多查询SQL写成WHERE date 2024-01-01 AND date 2024-12-31结果把一整年的分区全扫了。如果是查半年数据分区裁剪生效后数据和内存占用直接减半。第四检查引擎的内存限额配置。ClickHouse的max_memory_usage、Doris的exec_mem_limit都可能卡得太紧。有些查询本身是健康的中等量级查询但因为默认限额太小而OOM。这种情况调大限额就可以但千万别无脑调大否则一个异常查询就能拖垮整个节点。5.2 分页结果出现重复或缺失这个问题在分布式OLAP里太典型了尤其在两个场景下。场景一排序字段不唯一。前面讲过ORDER BY amount如果amount有大量重复两次查询之间如果数据发生了微小变动翻页时的边界判断就会错位结果就是某些行在上一页和下一页都出现或都没出现。解法是排序时加上唯一键比如ORDER BY amount DESC, order_id ASC。场景二上游数据在两次查询之间发生了变化。比如你翻到第3页这时候实时ETL又灌入了新数据排序位置整体往后移了就会造成重复或漏读。这其实是无解的除非你用快照读或者物化结果。我在做实时数仓分页时一般会告诉产品经理”结果集是实时变动的如果要求精确翻页需要额外做物化快照。”大多数产品经理都接受这个解释因为他们也能理解实时数据和精确翻页本身就是冲突的。排查时拿到重复的情况第一件事就是对比两次查询的排序键是否一致以及两次查询之间数据有没有变动。方法是在返回体里加一个数据指纹比如首条和末条的主键前后比对问题能快速定位。5.3 高并发下分页接口的稳定性优化分页接口本身查询量小但架不住并发高。一旦某个报表上线全班同事都点开翻QPS瞬间上来再轻快的查询也会让引擎喘不过气。我有几个稳定的优化手段。第一加重结果缓存层。如果数据源更新频率不高比如小时级可以把第一页、第二页这种热门页结果缓存到Redis前端翻页时直接命中完全不查OLAP引擎。我用Caffeine和Redis都做过效果都很好。第二限制单用户翻页深度。业务上90%的人只翻前几页那就从产品上限制最多翻50页再往后提示”请细化筛选条件”。这不是敷衍而是合理地引导用户。50页以后的数据用户注意力已经下降真正有价值的概率很低为了那2%的极端行为让系统承担全部压力不划算。第三所有分页查询强制带上分区裁剪和字段裁剪。分区裁剪是最有效的性能杀器字段裁剪则是让每次扫描传回节点数据量变少。前端要20个字段绝不select * 回50个字段。列式存储虽然只读需要的列但多一个字段就是多一份IO和网络传输积少成多。第四设置查询超时和熔断。ClickHouse、Doris都支持query timeout。接口层再设置一个兜底超时超过3秒直接返回”请缩小查询范围”避免慢查询拖垮后端线程池。这是稳定性工程的基本素养宁可主动失败不要被动拖死。5.4 前端无限滚动的接口设计最后提一个我在对接前端时经常被问到的点现在很多报表页面做的是”无限滚动”前端不断往下拉触发加载更多。这种交互模式天然适合游标分页。前端只需要维护一个next_cursor变量每次滚动到底部就带这个游标请求下一页。后端判断has_more为false就不再返回游标前端也就停下请求。整个交互不需要页码不需要总条数体验非常顺滑。我做的数据门户大屏和报表系统都采用了这套设计前端拉取几十页数据后端耗时始终稳定。这里有个小坑值得提醒游标一定要做有效期判断比如游标里带上生成时间戳超过30分钟就过期。因为游标本质是”上一页边界位置的快照”如果缓存太久或数据更新太频繁旧游标拿到新数据上会定位到错误位置。过期就让前端回到第一页重新开始干净利落。我个人在实际操作中的体会是分页本身是个小功能但它的方案选择直接影响整个报表链路的稳定性。很多时候业务方提“分页查询”不是真要你实现一个完整的翻页模型而是要解决“快速浏览大结果集”这个交互问题。你带着这个视角去做方案不用纠结是不是一定要给总页数、一定要支持跳页很多架构决策会清晰很多。希望这篇内容能帮你避开一些我当年踩过的坑也欢迎在实际项目里遇到具体情况时再一起交流具体参数和方案细节。