企业官网建设流程全解析

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

场景与目标

场景：数据库表有time时间字段，并建了以time为最左前缀的索引（单列索引，或time为第一列的联合索引），需要按时间及其他业务条件分批查询，且要避免慢查询。

对数据库表的使用方式：按时间顺序把这些数据分批读取并处理，要求——

• 不重不漏：每行恰好被处理一次
• 每页查询代价有上界：不随总数据量和已处理深度增长
• 断点续跑（常见诉求）：处理进度可持久化，重启后从断点继续

本文方案：用time字段做分页游标——上一页的末端时间就是下一页的起点，把查询拆成小段 range scan，每页只扫描本页时间区间内的行。

需要注意的问题

把time当游标，直觉做法是"取一页、把页末端时间当下一页起点"循环下去。但有六个坑，任踩一个就会重复、漏数据或死循环：

其中 1、2 是方案设计要解决的核心问题，3 ~ 6 是使用方必须满足的前提。另有两个使用边界：本方案只支持顺序推进（不支持按页码随机跳转）、不计算总数/总页数。

核心原理

用时间字段做"游标"，把查询拆成小段 range scan：

1. 第一轮只查time一个字段，走time索引，取LIMIT page_size条，代价极低
2. 外层MAX聚合直接得到page_end_time，只回 1 行（选型见"第一轮取游标方案对比"一章）
3. 第二轮用time范围条件约束扫描区间，附加业务过滤条件，取实际数据
4. 后续页按 ② 各情况的规则推进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_time：start_time之后已无任何数据（前提：区间静态无新增），直接终止

② 第二轮：取数据并推进游标

时间范围仅用于约束索引扫描区间，附加其他业务过滤条件。取数与推进一体，按情况处理：

情况 A：page_end_time != start_time

SELECT*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

情况 B：page_end_time == start_time

SELECT*FROMtWHEREtime=:start_timeAND{其他业务条件};

=走索引 ref 访问，该秒数据 ≤ 10000 条，一把全取，不排序。

本轮已处理区间为单点[start_time, start_time]（右闭），推进游标到该时间点的下一格
（秒精度+1s，毫秒精度+1ms）：

start_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非降序——游标单调推进、情况 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)联合游标是特定场景的可选增强
（见"边界场景处理"），不是本方案的依赖。

数据量估算

情况 A 第二轮行数上界的证明：第一轮按time升序LIMIT page_size，
返回的即候选区间内time最小的page_size行；升序序列中，time值严格
小于page_end_time的行必然全部排在第page_size行之前，故
[start_time, page_end_time)内的总行数（未筛选）≤page_size − 1。
这正是第二轮敢不加LIMIT的依据；同时说明"区间静态"前提是承重墙——
若第一、二轮之间有新记录落入当前区间，该上界即失效。

避免慢查询的关键点

边界场景处理

并发写入的滞后要求：“只写当前时刻"并不自动满足"新记录落在游标之后”。
反例：无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;

四种方案都围绕这个内层展开。

候选方案

方案 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 NULL
  （time NOT NULL保证NULL只来自空集）

方案 1（次优）：应用取结果集最后一条

直觉写法：SQL 原样返回page_size行，应用代码取最后一行的time。
正确性与方案 2 相同（升序末位即最大值），索引扫描量也相同。不如方案 2 之处：

1. 应用拿到page_size行却只用 1 个值：日志/调试要面对上千个时间值，
   网络传输与客户端内存白费
2. "最后一条 = 最大时间"的等价关系压在应用代码里，SQL 本身不自解释

方案 3（否决）：LIMIT :page_size - 1, 1只取第 N 条

把内层改为ORDER BY time ASC LIMIT :page_size - 1, 1，直接返回第page_size
行：同样只回 1 行，索引扫描量与方案 2 相同，且无派生表物化。否决理由：

1. 空结果语义二义：返回空既可能是"区间内无数据"，也可能是
   “剩余 1 ~page_size − 1条、不足一页”——后者page_end_time应取实际最大值，
   须再补一次MAX查询兜底。方案 1/2 中空结果只有一种含义，判断简单可靠
2. 可读性：page_size − 1的 offset 是魔法数字，读者须推演
   “跳过前 N−1 条、取第 N 条”，边界值稍错即引入重复或遗漏且不易察觉

方案 4（红线，否决）：去掉 LIMIT 查全区间 MAX

SELECTMAX(time)FROMtWHEREtime>=:start_timeANDtime<:end_time;

这不是"第一页的端点"，而是整个剩余区间的端点，一旦这么写：

• page_end_time一步跳到区间末端，情况A第二轮[start_time, page_end_time)
  一次扫出全部剩余行——"每页扫描行数有上限 / O(log n + k)"的核心性质被破坏，
  等价于不分页
• 情况B（page_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条全部落在该时间点。
此时两个问题必须分别回答：

1. 本轮怎么取数（第二轮 SQL）
2. 下一轮从哪开始（游标推进）

为什么不能沿用情况A的范围查询

情况A第二轮条件是time >= :start_time AND time < :page_end_time。
情况B时page_end_time == start_time，区间[start_time, start_time)是空集，
照抄情况A一条数据也查不出来。所以第二轮必须换条件：要么"等于"，要么"单位区间"。

候选方案

方案 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_size=50）：不推进时迭代 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)用+1s，DATETIME(3)用+1ms。单位大于字段精度会漏数据，
小于字段精度会产生空轮（不影响正确性，影响效率）。

方案 3（次优）：范围查询[start_time, start_time + 1 单位)+ 推进

SELECT*FROMtWHEREtime>=:start_timeANDtime<:start_time+1单位AND{其他业务条件}ORDERBYtimeASC;

正确性与方案 2 等价（区间恰好覆盖该时间点），但不如方案 2：

1. 索引路径：范围条件走range 扫描且通常要带ORDER BY time保证顺序；
   等值条件是 ref 定位，路径更直接、免排序
2. 精度耦合：把"一个时间点"表达为"长度为 1 个精度单位的区间"，隐含要求
   推进单位 = 字段最小精度，写错单位即漏/重；方案 2 的取数条件（等于）本身
   不涉及单位，风险只集中在推进这一处
3. 可读性：time = :start_time直白表达"取该时间点全部"，无需读者自行
   推演"区间 [t, t+1) 恰好覆盖一个秒精度的点"

结论

情况B采用“等于这个时间查询（本轮取数） + 游标推进 1 个时间精度单位（下一轮起点）”：

keyset 翻页方案对比

(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_len=16，两列均进入区间）——这是
  实现层面的隐式约定而非显式契约；若要摆脱该依赖，须显式建(time, id)
  联合索引，多一份索引维护成本
• 依赖隐含后缀意味着索引形态演化即退化：实测把idx(time)改建为
  (time, biz_status)后（MariaDB 10.0.36），keyset 的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：验证表 DDL，time单列索引 + 自增主键，每次运行前 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不在最左列）：第一轮内层退化为全索引扫描 +
  filesort（LIMIT提前终止失效）、情况 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 翻页方案对比"）