MySQL 大数据量分页查询优化-基于时间游标的分页方案

MySQL 大数据量分页查询优化——基于时间游标的分页方案场景与目标场景数据库表有time时间字段并建了以time为最左前缀的索引单列索引或time为第一列的联合索引需要按时间及其他业务条件分批查询且要避免慢查询。对数据库表的使用方式按时间顺序把这些数据分批读取并处理要求——不重不漏每行恰好被处理一次每页查询代价有上界不随总数据量和已处理深度增长断点续跑常见诉求处理进度可持久化重启后从断点继续本文方案用time字段做分页游标——上一页的末端时间就是下一页的起点把查询拆成小段 range scan每页只扫描本页时间区间内的行。需要注意的问题把time当游标直觉做法是取一页、把页末端时间当下一页起点循环下去。但有六个坑任踩一个就会重复、漏数据或死循环#问题后果方案的应对1同一时间值的行数可能超过页大小该时间点跨在两页边界上区间切页要么重复要么遗漏处理不当还会死循环情况 B单点全取 推进 1 个精度单位见完整流程②、“情况B方案对比”2业务过滤条件把整段数据滤空页内查不出数据游标失去推进依据——死循环或误判结束而漏数据第一轮定界不带业务条件见完整流程①、“第一轮取游标方案对比”3time字段可空time :start_time永远匹配不到 NULL 行这些行被静默跳过且无任何报错使用前提见前提与限制4查询期间区间内有并发写入不重不漏所依赖的区间静态性失效只写当前时刻并不自动安全使用前提与滞后要求见前提与限制、“边界场景处理”5同一时间值内部的行序不稳定下游若依赖该顺序会出错使用前提见前提与限制6没有确定的终止条件无限循环使用前提见前提与限制其中 1、2 是方案设计要解决的核心问题3 ~ 6 是使用方必须满足的前提。另有两个使用边界本方案只支持顺序推进不支持按页码随机跳转、不计算总数/总页数。核心原理用时间字段做游标把查询拆成小段 range scan第一轮只查time一个字段走time索引取LIMIT page_size条代价极低外层MAX聚合直接得到page_end_time只回 1 行选型见第一轮取游标方案对比一章第二轮用time范围条件约束扫描区间附加业务过滤条件取实际数据后续页按 ② 各情况的规则推进start_time右开区间推进到端点、单点全取推进到下一格循环往复为什么快每轮第二轮扫描行数 ≈ 该时间区间内实际数据量不随总数据量增长而变慢time最左前缀索引保证 range scan 起始点精准完整流程初始化start_time 查询起始时间 end_time 查询终止时间全局上限可选 page_size 每页游标条数如 1000循环体① 第一轮取游标SELECTMAX(time)FROM(SELECTtimeFROMtWHEREtime:start_timeANDtime:end_time-- 可选无上界时省略此行ORDERBYtimeASCLIMIT:page_size)ASpage;内层子查询圈定候选区间内time最小的page_size行走time索引纯覆盖扫描外层MAX聚合只返回 1 行 1 列即page_end_time升序 LIMIT page_size下MAX(time)恒等于最后一条的 time两种写法语义完全等价选型见第一轮取游标方案对比一章只回 1 行的收益应用侧日志/调试不必面对page_size个时间值传输与客户端内存开销最小内层ORDER BY与LIMIT均不可省ORDER BY保证取的是最小的page_size条去掉后LIMIT变成任意抽样MAX无意义LIMIT是分页的承重墙去掉即退化为全区间MAX见对比章方案 4定界不带业务条件这是问题 2 的解——业务条件可能把整段滤空若游标推进依赖带条件的查询结果游标将失去推进依据第一轮只按time定界推进永远有保障结果为空时聚合查询永远返回 1 行空集时值为NULL——time NOT NULL且time :start_time天然滤掉 NULL 行故NULL只可能来自空集page_end_time IS NULL即[:start_time, :end_time)区间内无数据不执行第二轮有end_time置start_time end_time循环结束无end_timestart_time之后已无任何数据前提区间静态无新增直接终止② 第二轮取数据并推进游标时间范围仅用于约束索引扫描区间附加其他业务过滤条件。取数与推进一体按情况处理情况 Apage_end_time ! start_timeSELECT*FROMtWHEREtime:start_timeANDtime:page_end_timeAND{其他业务条件}ORDERBYtimeASC;ORDER BY time引导优化器选择time索引range scan 的输出顺序天然满足排序无需 filesort。注意ORDER BY本身不强制访问路径——优化器按成本选择若业务条件上存在选择性更好的索引可能改走该索引结果仍正确仅扫描路径不同必须钉死路径时使用FROM t FORCE INDEX(idx_time)。本轮已处理区间为[start_time, page_end_time)右开推进游标start_time page_end_time情况 Bpage_end_time start_timeSELECT*FROMtWHEREtime:start_timeAND{其他业务条件};走索引 ref 访问该秒数据 ≤ 10000 条一把全取不排序。本轮已处理区间为单点[start_time, start_time]右闭推进游标到该时间点的下一格秒精度1s毫秒精度1msstart_time start_time 1 个时间精度单位情况 B 不可推进到page_end_time它等于start_time否则游标原地踏步会无限循环并重复输出同一时间点的数据。这是问题 1 的解同一时间点数据量≥page_size时整点全取、一次越过而不是让该点横跨页边界。回到 ①直到start_time end_time或第一轮返回NULL时终止。整个循环可用伪代码概括游标 start_time 循环: page_end_time 第一轮: MAX(time) FROM (升序 LIMIT page_size 的候选页) 若 page_end_time IS NULL: # 区间内无数据 结束 若 page_end_time ! 游标: # 情况 A 第二轮: time ∈ [游标, page_end_time) 业务条件 游标 page_end_time # 已处理区间右开 否则: # 情况 B: 该时间点行数 ≥ page_size 第二轮: time 游标 业务条件 游标 游标 1 个时间精度单位 # 已处理区间右闭 若有 end_time 且 游标 end_time: 结束前提与限制项说明time字段位于索引最左前缀单列索引或time为第一列的联合索引方案基础见下注time字段NOT NULL可空时time :start_time永远匹配不到 NULL 行这些行被静默跳过且无任何报错一秒内数据量 ≤ 10000硬上限start_time page_end_time时一把全取的内存/网络保障查询执行期间所处理的时间区间不会产生新记录分页期间该区间数据静态是不重不漏的基础若存在并发写入须保证新记录的time均落在游标已推进位置之后下游处理不依赖同一时间值内的行序情况 B 不排序情况 A/B 见完整流程②整体仍按time非降序见下注查询有确定的终止时间防止无限循环只支持顺序推进不支持按页码随机跳转游标分页的固有形态下一页起点由上一页末端决定不计算总数/总页数方案只负责按时间区间取数需要总数须另行统计注不要求time单调递增。游标按time值范围切分与行的插入顺序无关已用乱序插入数据实测验证不重不漏。注输出整体按time非降序——游标单调推进、情况 A 带ORDER BY time、情况 B 整批为同一时间值需要稳定的只是同一时间值内部的行序加ORDER BY id。注索引不要求单列idx(time)与(time, 其他字段)均可第一轮按time前缀有序扫描——ORDER BY time免 filesort、LIMIT提前终止、仅查time为覆盖扫描情况 B 的对最左前缀仍是 ref 访问联合索引第二列恰为业务条件字段时业务条件可纳入索引访问range/ref 命中两列或经 ICP 在索引内过滤比单列索引更优。time不在最左列如(status, time)不支持第一轮无法按time有序扫描、LIMIT失去提前终止情况 B 的等值定位退化为全表扫描。注查询与排序不依赖主键——排序键只有time游标推进只用time值。同一时间值内部的行序未定义idx(time)与(time, x)两种索引下该内部顺序不同均不影响正确性。ORDER BY id、(time, id)联合游标是特定场景的可选增强见边界场景处理不是本方案的依赖。数据量估算场景扫描行数说明第一轮取游标page_size如 1000只查time字段纯索引覆盖外层MAX聚合只回 1 行服务端派生表物化 ≤page_size行第二轮正常页该时间区间内实际行数≤page_size− 1可证明见下第二轮同秒页≤ 10000索引 ref 访问一把全取每页总代价O(log n k)k 为区间内行数不随总表大小退化情况 A 第二轮行数上界的证明第一轮按time升序LIMIT page_size返回的即候选区间内time最小的page_size行升序序列中time值严格小于page_end_time的行必然全部排在第page_size行之前故[start_time, page_end_time)内的总行数未筛选≤page_size − 1。这正是第二轮敢不加LIMIT的依据同时说明区间静态前提是承重墙——若第一、二轮之间有新记录落入当前区间该上界即失效。避免慢查询的关键点手段作用第一轮只查time单字段最小化回表纯索引覆盖第一轮外层MAX聚合只回 1 行最小化传输/日志/客户端内存LIMIT page_size限制游标扫描行数防止游标查询本身变慢第二轮时间范围约束把扫描区间压到最小page_end_time ! start_time时加ORDER BY time引导走time索引 range scan、免 filesort需强制时用FORCE INDEXpage_end_time start_time时用ref 访问不走 range不依赖排序每页扫描行数有上限不会因数据增长导致单页查询变慢边界场景处理场景处理第一轮返回NULL区间内无数据有end_time时置start_time end_time结束无上界时直接终止情况 B同一时间点数据 ≥page_size全取该时间点后游标 1 个精度单位见 ② 情况 B不可推进到自身一秒内数据量超过 10000时间字段精度升级为毫秒DATETIME(3)逻辑不变需要顺序稳定指同一时间值内的行序情况 B 加ORDER BY id整体time非降序天然成立需要严格不重不漏游标改为(time, id)联合用time ? OR (time ? AND id ?)其他业务条件选择性极低考虑联合索引(time, 高选择性字段)查询期间区间内可能新增写入违反前提须保证新记录time始终落在游标已推进位置之后见下注只写当前时刻并不自动满足否则改用一致性快照或(time, id)联合游标并发写入的滞后要求“只写当前时刻并不自动满足新记录落在游标之后”。反例无end_time的扫描追到当前秒 T 时情况 B 全取秒 T 后游标推进到 T 的下一格同一秒内随后写入的行time T已落后于游标被漏掉且第一轮查空后循环直接终止。安全条件是游标始终滞后写入前沿至少 1 个精度单位只扫历史区间或让扫描上界与写入时刻之间留出足够的滞后窗口。第一轮取游标方案对比第一轮的职责是算游标候选区间内time最小的page_size行中最大的time即第page_size行的time记为page_end_time。应用侧只需要这一个值但 SQL 有四种写法正确性、代价与空结果语义各不相同。公共内层候选页SELECTtimeFROMtWHEREtime:start_timeANDtime:end_time-- 可选无上界时省略此行ORDERBYtimeASCLIMIT:page_size;四种方案都围绕这个内层展开。候选方案方案第一轮写法应用拿到服务端代价空结果语义结论1公共内层应用取结果集最后一条page_size行纯覆盖索引扫描结果集为空可用次优2公共内层外包MAX(time)1 行覆盖索引扫描 派生表物化 聚合值为NULL采用3公共内层改为LIMIT :page_size - 1, 11 行覆盖索引扫描跳到第 N 条空 剩余不足一页否决4去掉LIMITSELECT MAX(time) FROM t WHERE ...1 行全区间扫描值为NULL否决错误方案 2采用外层 MAX 聚合SELECTMAX(time)FROM(SELECTtimeFROMtWHEREtime:start_timeANDtime:end_time-- 可选无上界时省略此行ORDERBYtimeASCLIMIT:page_size)ASpage;语义升序序列的最后一条 页内最大值MAX(time)与取最后一条的 time恒等方案性质不重不漏、情况B触发判断、第二轮 ≤page_size − 1上界完全不变收益结果集 1 行 1 列——应用日志/调试不必面对page_size个时间值传输与客户端内存开销最小MAX把取这页的最大时间的意图显式写进 SQL自解释“取最后一条”需要读者自行推出升序末位 最大值代价内层含LIMIT派生表无法 merge 进外层MySQL 优化器规则须物化 ≤page_size行到内存临时表再聚合页 1000 量级无感知兼容性MySQL 5.x / 8.0、MariaDB 均支持派生表必须带别名AS page注意空结果判断从结果集为空改为page_end_time IS NULLtime NOT NULL保证NULL只来自空集方案 1次优应用取结果集最后一条直觉写法SQL 原样返回page_size行应用代码取最后一行的time。正确性与方案 2 相同升序末位即最大值索引扫描量也相同。不如方案 2 之处应用拿到page_size行却只用 1 个值日志/调试要面对上千个时间值网络传输与客户端内存白费最后一条 最大时间的等价关系压在应用代码里SQL 本身不自解释方案 3否决LIMIT :page_size - 1, 1只取第 N 条把内层改为ORDER BY time ASC LIMIT :page_size - 1, 1直接返回第page_size行同样只回 1 行索引扫描量与方案 2 相同且无派生表物化。否决理由空结果语义二义返回空既可能是区间内无数据也可能是“剩余 1 ~page_size − 1条、不足一页”——后者page_end_time应取实际最大值须再补一次MAX查询兜底。方案 1/2 中空结果只有一种含义判断简单可靠可读性page_size − 1的 offset 是魔法数字读者须推演“跳过前 N−1 条、取第 N 条”边界值稍错即引入重复或遗漏且不易察觉方案 4红线否决去掉 LIMIT 查全区间 MAXSELECTMAX(time)FROMtWHEREtime:start_timeANDtime:end_time;这不是第一页的端点而是整个剩余区间的端点一旦这么写page_end_time一步跳到区间末端情况A第二轮[start_time, page_end_time)一次扫出全部剩余行——每页扫描行数有上限 / O(log n k)的核心性质被破坏等价于不分页情况Bpage_end_time start_time只有全区间数据挤在同一个时间点时才可能触发正常永远走不到密集秒保护失效方案 1/2/3 的全部性质含第二轮 ≤page_size − 1的证明都建立在page_end_time是第page_size行的 time之上去掉LIMIT前提即不成立结论第一轮采用方案 2内层升序 LIMIT page_size圈定候选页外层MAX聚合只回 1 行。ORDER BY与LIMIT是两根都不可省的承重柱——前者保证取的是“最小的page_size条”后者保证分页性质本身。情况B方案对比第一轮查到的page_size条时间数据全部相同时page_end_time start_time说明该时间点数据量 ≥page_size第二轮为什么要用“等于这个时间查询 游标推进 1 个时间精度单位”而不是别的做法。对应 ② 情况 B取数据并推进游标。问题定义第一轮取游标SELECTMAX(time)FROM(SELECTtimeFROMtWHEREtime:start_timeANDtime:end_time-- 可选无上界时省略此行ORDERBYtimeASCLIMIT:page_size)ASpage;返回的page_end_time start_time升序 start_time页内最大值等于区间起点意味着返回的page_size条全部落在该时间点。此时两个问题必须分别回答本轮怎么取数第二轮 SQL下一轮从哪开始游标推进为什么不能沿用情况A的范围查询情况A第二轮条件是time :start_time AND time :page_end_time。情况B时page_end_time start_time区间[start_time, start_time)是空集照抄情况A一条数据也查不出来。所以第二轮必须换条件要么等于要么单位区间。候选方案方案第二轮取数游标推进正确性索引路径结论1等于time :start_timestart_time page_end_time未推进死循环 重复输出ref否决2等于time :start_timestart_time 1 个时间精度单位不重不漏ref最优免排序采用3范围time :start_time AND time :start_time 1 单位同方案 2不重不漏range 需ORDER BY正确但次优方案 1否决只用等于查询游标不推进直觉“第二轮用等于查询已经把该时间点全部取完了游标照旧推进到page_end_time即可。”错误page_end_time start_time推进start_time page_end_time后游标原地不动下一轮取游标查询time :start_time LIMIT :page_size取到的还是同一批数据page_end_time start_time再次成立再次进入情况B无限循环且每轮都重复输出同一时间点的全部数据实测863 行的表、单秒 130 条、page_size50不推进时迭代 3000 次保护上限内已输出约 39 万行重复数据仍不终止。结论等于查询与游标推进是互补的两半不是二选一等于查询负责本轮取数1 推进负责跳过已取的时间点。只有等于查询而不推进 死循环。方案 2采用等于查询 游标推进 1 个时间精度单位-- 第二轮取数SELECT*FROMtWHEREtime:start_timeAND{其他业务条件};-- 推进该轮已处理区间为单点 [start_time, start_time]右闭 start_time start_time 1 个时间精度单位为什么正确不漏等于查询已取完该时间点的全部行下一轮从start_time 1 单位起该点已无未处理数据不重下一轮time start_time 1 单位不会再命中该点推进量恰好越过已处理区间的右端点右闭区间 → 推进到右端之外这是游标分页的统一不变式推进位置 已处理区间右端开或右端 1 格闭为什么最优time 常量走索引ref 等值定位直接命中该点全部行无需排序该点数据 ≤ 10000 条为前提等值条件不引入时间单位概念不存在精度换算精度匹配要求1的单位必须等于time字段的最小精度——DATETIME(0)用1sDATETIME(3)用1ms。单位大于字段精度会漏数据小于字段精度会产生空轮不影响正确性影响效率。方案 3次优范围查询[start_time, start_time 1 单位) 推进SELECT*FROMtWHEREtime:start_timeANDtime:start_time1单位AND{其他业务条件}ORDERBYtimeASC;正确性与方案 2 等价区间恰好覆盖该时间点但不如方案 2索引路径范围条件走range 扫描且通常要带ORDER BY time保证顺序等值条件是 ref 定位路径更直接、免排序精度耦合把一个时间点表达为长度为 1 个精度单位的区间隐含要求推进单位 字段最小精度写错单位即漏/重方案 2 的取数条件等于本身不涉及单位风险只集中在推进这一处可读性time :start_time直白表达取该时间点全部无需读者自行推演区间 [t, t1) 恰好覆盖一个秒精度的点结论情况B采用“等于这个时间查询本轮取数 游标推进 1 个时间精度单位下一轮起点”关注点由谁解决只取一半的后果本轮取数等于查询time :start_timeref 最快、免排序不推进 → 死循环 重复输出方案1跳过已处理时间点游标1 个精度单位越过右闭区间右端换成范围查询 → 正确但次优方案3keyset 翻页方案对比(time, id)keyset 是另一种游标分页游标为(last_time, last_id)二元组按(time, id)全序取页SELECT*FROMtWHERE(time:last_timeOR(time:last_timeANDid:last_id))AND{其他业务条件}ORDERBYtime,idLIMIT:page_size;两者同属游标分页都避开OFFSET总扫描量同级查询效率不是本质差异朴素 keyset 把过滤条件放进定位查询单条查询扫描量随过滤命中率退化但该问题可以移植本方案的两轮定界技巧解决——第一轮不带业务条件按(time, id)取page_size行定界第二轮限定元组区间取数单条查询同样有上界且(time, id)全序无并列连情况 B 都不存在。本质差异在索引依赖、游标形态与批次语义。本方案的优势1. 不依赖(time, id)序核心差异本方案的排序与游标只用time值不需要(time, id)序——既不需要显式创建(time, id)联合索引也不依赖单列idx(time)在 InnoDB 中实际对应(time, 主键)这一实现细节keyset 走单列idx(time)靠的是 InnoDB 二级索引隐含主键后缀 优化器对扩展索引的识别MariaDB 10.0.36 实测 key_len16两列均进入区间——这是实现层面的隐式约定而非显式契约若要摆脱该依赖须显式建(time, id)联合索引多一份索引维护成本依赖隐含后缀意味着索引形态演化即退化实测把idx(time)改建为(time, biz_status)后MariaDB 10.0.36keyset 的ORDER BY time, id出现 filesort仅当过滤列恰为等值条件时例外本方案在同一次实测中仍是range 访问、免 filesort且多了索引内过滤。主键定义变更组合主键、换主键列对本方案无影响。退化表现为变慢而非报错本方案只要求time在索引最左前缀见前提与限制注time之后加列、换列均不影响正确性与执行路径。若time被移出最左列两个方案都不可用2. 游标是单值 time断点续跑只需持久化一个时间值按时间段重放天然幂等游标本身可读可监控“处理到 12:00:00”。keyset 需原子保存(time, id)一对id 对运维不可读。3. 批次按时间区间对齐每轮输出一个完整[start_time, page_end_time)区间或单个时间点与按时间窗对齐的下游聚合、按时间段重跑吻合。keyset 的页边界是行数边界一页可跨多个时间值、一个时间值可跨多页无法回答这批数据属于哪个时间段。4. 谓词简单本方案两轮均为单列区间/等值谓词。keyset 的定位谓词是元组 OR 形式若再借两轮定界第二轮须两个元组区间相交谓词复杂度进一步上升。keyset 的优势如实列出(time, id)全序、无并列不存在同一时间点行数 ≥page_size的情况B无需单点全取无一秒内数据量 ≤ 10000前提无需精度升级DATETIME(3)每页恰好page_size行本方案情况 A ≤page_size − 1朴素形态每页一条 SQL本方案两条。实现负担各在一处本方案须正确处理情况 A/B 推进——漏写情况 B 推进会死循环实测复现游标推进到表尾时剩余行全落在同一时间点page_end_time start_time使第二轮空区间查询永远返回 0 行且游标原地踏步即情况B方案对比章方案 1 的失败模式keyset 的负担在元组谓词与索引依赖并发同秒写入略宽容游标未离开秒 T 期间同秒后插入id 更大的行仍可见本方案情况 B 全取 1s 后同秒后到的写入立即落后于游标选型结论只维护time最左前缀索引、需要单值断点续跑、按时间窗对齐批处理 →本方案已有显式(time, id)索引且索引形态不会演化、追求无并列分支的推进逻辑 →keyset验证配套验证脚本两个文件create_table.sql验证表 DDLtime单列索引 自增主键每次运行前 DROP 重建time_cursor_paging_verify.py乱序插入数据逐场景与全量基准查询比对断言集合一致、无重复、按time非降序实测环境本地 MariaDB 10.0.36。数据为乱序插入 863 行覆盖连续秒、密集秒单秒 130/80 条超过页大小触发情况B、空时间段、整秒被业务条件滤空、非整除页大小等场景page_size覆盖 100000 / 50 / 7 / 3 / 1。实测结果15 个场景全部通过与全量基准集合完全一致不重不漏。另外验证了第一轮两种取游标写法的等价性外层MAX聚合与取结果集最后一条各跑一遍 15 个场景全部通过且各场景页数逐场景一致全范围/页50 均为 20 页、密集秒单点均 1 页、页1 均 100 页。索引形态补充实测20 万行表、EXPLAIN联合索引(time, biz_status)第一轮内层走索引有序扫描且覆盖LIMIT提前终止情况 A 为 range 访问、业务条件纳入索引key_len含第二列行数估算低于单列索引情况 B 为 ref 且命中两列。均无 filesort反例(biz_status, time)time不在最左列第一轮内层退化为全索引扫描 filesortLIMIT提前终止失效、情况 B 等值查询退化为全表扫描成本模型不成立数百行小表上情况 A 无论单列还是联合索引都可能被优化器选全表扫描 filesort属表规模成本现象大表无 hint 自然走索引需钉死路径时用FORCE INDEX小结时间游标分页每页扫描行数有上限第一轮page_size、情况 A 第二轮≤page_size − 1、情况 B 单点全取 ≤ 10000不随总数据量和页码退化正确性的关键是游标推进规则情况 A 推进到page_end_time已处理区间右开情况 B 单点全取后推进 1 个精度单位右闭第一轮取游标用升序LIMIT 外层MAX只回 1 行内层ORDER BY与LIMIT都不可省正确性依赖区间静态前提并发写入须满足游标滞后于写入前沿见边界场景处理与(time, id)keyset 翻页总扫描量同级、定界技巧可互通本质差异在索引依赖本方案不依赖(time, id)序无需显式联合索引也不依赖单列索引隐含的主键后缀索引形态演化不退化keyset 全序无并列、无单点数据量上限见keyset 翻页方案对比