mysql的深度分页问题

随风飘的云
53 阅读11分钟

深度分页是指 查询大页码数据（如 LIMIT 100000, 10，查询第 10001~100010 行数据）时，MySQL 性能急剧下降的场景。核心矛盾是：InnoDB 需扫描大量无关数据后丢弃，导致 I/O 和 CPU 开销激增。结合 InnoDB 索引结构和查询原理，下面从「问题本质→解决方案→场景选型→避坑要点」展开说明。

一、先搞懂：深度分页为什么慢？

以常见的分页 SQL 为例（user 表，主键 id，二级索引 idx_age，按 age 排序分页）：

-- 慢查询：查询第 10001 页，每页 10 条（跳过前 100000 行）
SELECT id, username, age FROM user WHERE age > 20 ORDER BY age LIMIT 100000, 10;

慢查询的核心原因（InnoDB 执行流程）：

  1. 索引扫描 + 回表：因查询字段（username）不在二级索引 idx_age 中，InnoDB 需先通过 idx_age 扫描前 100000+10 条满足 age>20 的记录（获取主键 id）；
  2. 丢弃无效数据：根据主键 id 回表查询完整数据后，丢弃前 100000 条，仅返回最后 10 条；
  3. 关键瓶颈：扫描大量无关数据（100010 条）+ 频繁回表（100010 次随机 I/O），导致性能暴跌（页码越大，扫描行数越多，越慢）。

补充：无索引的情况更糟

若 age 无索引，InnoDB 会进行 全表扫描（扫描整张表的所有行），再排序、跳过前 100000 行，性能会差一个数量级。

二、深度分页的 5 种解决方案（按优先级排序）

方案 1：基于「主键 / 有序索引」的游标分页（最优，推荐优先使用）

核心原理：

利用 InnoDB 索引的 有序性，通过「上一页的最后一个索引值」替代 OFFSET，直接定位到目标数据的起始位置，避免扫描前 N 行。本质是「用索引值定位」替代「跳过 N 行」，扫描行数 = 每页大小（如 10 行），性能恒定高效。

适用场景：
  • 主键自增（INT AUTO_INCREMENT）或有序索引（如时间戳 create_time）；
  • 不需要跳页（仅支持「上一页 / 下一页」，如 APP 列表下拉加载）；
  • 排序字段是唯一索引或有序字段（避免重复 / 遗漏数据）。
实现示例（按主键 id 分页）：
-- 第 1 页（初始页，无上次主键值）
SELECT id, username, age FROM user WHERE age > 20 ORDER BY id LIMIT 10;
-- 假设最后一条数据的 id=100010（记录此值作为游标）

-- 第 2 页（用上次的 id 作为条件，直接定位）
SELECT id, username, age FROM user WHERE age > 20 AND id > 100010 ORDER BY id LIMIT 10;
-- 记录新的最后 id=100020，以此类推...
按二级索引 create_time 分页（需保证有序）：
-- 第 1 页
SELECT id, username, create_time FROM user WHERE status=1 ORDER BY create_time DESC LIMIT 10;
-- 记录最后一条的 create_time='2025-11-12 10:00:00'，id=100010（避免时间重复导致漏数据）

-- 第 2 页（用时间+主键双重条件，防止时间相同的记录遗漏）
SELECT id, username, create_time FROM user 
WHERE status=1 AND create_time <= '2025-11-12 10:00:00' AND id < 100010 
ORDER BY create_time DESC LIMIT 10;
优点：
  • 性能极致（扫描行数 = 每页大小，无无效扫描）；
  • 无 OFFSET 带来的性能损耗，支持无限深度分页。
缺点：
  • 不支持跳页（如直接跳转到第 100 页）；
  • 排序字段必须有序且唯一（或配合主键确保唯一性）。

方案 2：索引覆盖 + 延迟关联（支持跳页，次优）

核心原理：
  1. 索引覆盖：先通过「包含排序字段 + 主键」的联合索引，查询目标页的主键 id（无需回表，因索引已包含所需字段）；
  2. 延迟关联：用查询到的主键 id 关联原表，获取完整数据（仅回表 10 次，而非 100010 次）。核心是「减少回表次数」，将扫描 + 回表的开销从 offset+size 降至 size。
适用场景：
  • 需要跳页（如后台管理系统的分页查询）；
  • 可创建「排序字段 + 筛选字段 + 主键」的联合索引。
实现示例（优化前文慢查询）：
-- 步骤 1：创建联合索引（包含筛选字段 age、排序字段 age、主键 id）
CREATE INDEX idx_age_id ON user(age, id); -- 覆盖索引，无需回表查 id

-- 步骤 2：延迟关联查询（先查 id，再关联原表）
SELECT u.id, u.username, u.age 
FROM user u
-- 子查询：通过覆盖索引快速获取目标页的 id（仅扫描 100010 行索引，无回表）
JOIN (
  SELECT id FROM user WHERE age > 20 ORDER BY age LIMIT 100000, 10
) AS t ON u.id = t.id;
关键优化点：
  • 联合索引必须包含「筛选字段（WHERE 条件）+ 排序字段（ORDER BY）+ 主键（id）」，确保子查询是「索引覆盖查询」（EXPLAIN 显示 type=range，Extra=Using index）；
  • 子查询仅返回主键 id，数据量小，关联效率高。
优点：
  • 支持跳页，兼容传统分页场景；
  • 性能比原生 LIMIT offset, size 提升 10~100 倍（取决于 offset 大小）。
缺点：
  • 需维护额外的联合索引（增加写入开销）；
  • offset 极大时（如 LIMIT 1000000, 10），仍需扫描大量索引行（但无回表开销，比原生 SQL 快）。

方案 3：预计算分页锚点（适合超大数据量 + 固定跳页）

核心原理：

提前计算并存储「分页锚点」（如每 100 页的最后一个主键 / 索引值），查询时直接通过锚点定位到目标页的起始位置，避免 OFFSET 扫描。

适用场景：
  • 超大数据量（千万级 +），需要支持跳页（如报表系统）；
  • 数据更新频率低（锚点无需频繁更新）。
实现步骤：

  1. 创建锚点表：存储分页维度、页码区间、锚点值；

    CREATE TABLE page_anchor (
  id INT PRIMARY KEY AUTO_INCREMENT,
  table_name VARCHAR(50) NOT NULL, -- 目标表名（如 user）
  filter_condition VARCHAR(100) NOT NULL, -- 筛选条件（如 "age>20"）
  page_step INT NOT NULL, -- 锚点间隔（如 100 页一个锚点）
  anchor_value BIGINT NOT NULL, -- 锚点值（主键 id 或索引值）
  anchor_page INT NOT NULL, -- 锚点对应的页码（如 100 页）
  UNIQUE KEY uk_table_filter_step (table_name, filter_condition, page_step)
);

  2. 定时更新锚点：通过定时任务（如 cron）计算锚点值并插入锚点表；

    -- 示例：计算 user 表 age>20 条件下，每 100 页的锚点（id）
INSERT INTO page_anchor (table_name, filter_condition, page_step, anchor_value, anchor_page)
SELECT 'user', 'age>20', 100, id, 100 
FROM user WHERE age>20 ORDER BY id LIMIT 9999, 1; -- 第 100 页的最后一个 id

  3. 查询时使用锚点：

    -- 需求：查询第 150 页（page=150，size=10）
-- 步骤 1：查询第 100 页的锚点值（anchor_value=10000）
SELECT anchor_value FROM page_anchor WHERE table_name='user' AND filter_condition='age>20' AND anchor_page=100;

-- 步骤 2：通过锚点定位第 150 页（偏移 50 页）
SELECT id, username, age FROM user 
WHERE age>20 AND id > 10000 
ORDER BY id LIMIT 50*10, 10; -- 仅偏移 500 行，而非 14990 行
优点：
  • 支持超大数据量跳页，性能接近游标分页；
  • 锚点可复用，降低重复计算开销。
缺点：
  • 需维护锚点表和定时任务，增加系统复杂度；
  • 数据更新频繁时，锚点易失效（需频繁重建）。

方案 4：分库分表（水平拆分，大数据量终极方案）

核心原理：

当单表数据量超千万级，即使优化索引，深度分页性能仍会瓶颈，此时需将表按「主键范围」或「哈希」水平拆分（如拆分为 user_01~user_10 10 张表），查询时：

  1. 按拆分规则定位到目标数据所在的分表；
  2. 在分表内执行分页查询（分表数据量小，OFFSET 开销可接受）。
适用场景：
  • 单表数据量超千万级，且需要支持跳页；
  • 高并发读写场景（分库分表同时提升并发性能）。
实现示例（按主键范围分表）：

  • 分表规则：user_01（id 11000000）、user_02（id 10000012000000）...；

  • 分页查询第 10001 页（id 100001~100010）：

    -- 定位分表：id 100001~100010 属于 user_01
SELECT id, username, age FROM user_01 WHERE age>20 ORDER BY id LIMIT 100000, 10;
优点：
  • 彻底解决单表数据量过大的问题，分页性能稳定；
  • 支持高并发和无限深度分页。
缺点：
  • 系统复杂度高（需处理分表路由、分布式事务等）；
  • 需依赖中间件（如 Sharding-JDBC）简化分表操作。

方案 5：禁用深度跳页（业务妥协，最简单）

核心思路：

从业务层面限制「深度跳页」（如仅允许查询前 100 页），引导用户通过「筛选条件缩小范围」（如按时间、地区筛选）后再分页，从根源上避免 OFFSET 过大的问题。

适用场景：
  • 大部分 C 端产品（如 APP、网站），用户极少需要查询 100 页以后的数据；
  • 业务允许用户通过筛选条件缩小查询范围。
实现示例：
-- 限制最大 offset（如仅允许 offset < 10000）
SELECT id, username, age FROM user WHERE age>20 ORDER BY age LIMIT 10000, 10;
-- 业务层判断：若用户输入页码 > 1000（1000*10=10000），返回「请缩小筛选范围」
优点：
  • 无需修改数据库，实现成本极低；
  • 引导用户规范查询行为，减少无效请求。
缺点：
  • 不支持深度跳页，灵活性差；
  • 不适用于 B 端管理系统、报表系统等场景。

三、常见坑与避坑要点

坑 1：用非有序字段排序（如 UUID 主键、随机字符串）

  • 问题：UUID 是随机值，索引无序，LIMIT offset, size 无法利用索引有序性，导致全索引扫描；
  • 解决：排序字段必须是「有序索引」（如自增主键、时间戳、连续数字字段）。

坑 2：联合索引顺序错误，导致索引失效

  • 问题：联合索引需遵循「最左前缀原则」，若筛选字段不在索引最左侧，索引失效；

    -- 错误：筛选字段 age 在索引第 2 位，索引失效
CREATE INDEX idx_id_age ON user(id, age);
SELECT id FROM user WHERE age>20 ORDER BY age LIMIT 100000, 10; -- 无法使用 idx_id_age

-- 正确：筛选字段 age 在索引第 1 位
CREATE INDEX idx_age_id ON user(age, id); -- 可使用索引

  • 解决：联合索引顺序 =「筛选字段（WHERE）→ 排序字段（ORDER BY）→ 主键（id）」。

坑 3：忽略排序字段的重复值，导致数据遗漏

  • 问题：若排序字段（如 create_time）有重复值，LIMIT offset, size 可能跳过重复值对应的记录；

  • 解决：排序时增加主键（唯一值），确保排序唯一：

    -- 正确：create_time + id 双重排序，避免重复
SELECT id, username FROM user ORDER BY create_time DESC, id DESC LIMIT 100000, 10;

坑 4：过度依赖 OFFSET，未做索引优化

  • 问题：直接使用 LIMIT 100000, 10 且无覆盖索引，导致大量回表；
  • 解决：优先使用「索引覆盖 + 延迟关联」优化，减少回表开销。

四、方案选型对比表（快速决策）

方案支持跳页性能实现复杂度适用场景
游标分页（主键 / 索引）否最优低C 端 APP 下拉加载（无跳页需求）
索引覆盖 + 延迟关联是次优中B 端管理系统（需跳页，数据量百万级）
预计算分页锚点是优中高报表系统（超大数据量，低更新频率）
分库分表是最优高单表千万级 +，高并发读写
禁用深度跳页否无极低C 端产品（用户无深度分页需求）

五、总结

MySQL 深度分页的核心优化思路是：减少扫描行数 + 避免无效回表，优先选择「无 OFFSET」的方案（游标分页），其次是「优化 OFFSET 扫描」的方案（索引覆盖 + 延迟关联），超大数据量则需考虑分库分表。

实际开发中：

  1. 若无需跳页（C 端下拉加载），直接用「游标分页」（性能最优，实现最简单）；
  2. 若需跳页（B 端管理系统），先尝试「索引覆盖 + 延迟关联」（无需改造业务，仅需加索引）；
  3. 单表数据量超千万级，且需频繁跳页，再考虑「分库分表」或「预计算锚点」。

避免过度设计：大多数场景下，「索引覆盖 + 延迟关联」已能满足需求，无需直接上分库分表

avatar
文章
阅读
粉丝