针对 MySQL 大表深分页 LIMIT 1000000, 10 性能差的问题，最推荐通过覆盖索引延迟关联或业务游标方案优化。适用场景为读多写少的列表查询，风险边界在于主键 ID 必须连续或业务允许断号。

命令速用版

以下 SQL 片段可直接替换原有分页语句，需根据实际表名和索引调整。

-- 原慢查询
SELECT * FROM table_name LIMIT 1000000, 10;

-- 优化方案 1：覆盖索引延迟关联
SELECT t1.* FROM table_name t1
INNER JOIN (SELECT id FROM table_name LIMIT 1000000, 10) t2 ON t1.id = t2.id;

-- 优化方案 2：业务游标（需记录上一页最大 ID）
SELECT * FROM table_name WHERE id > 1000000 ORDER BY id ASC LIMIT 10;

为什么会这样

MySQL 执行 LIMIT offset, N 时需要扫描 offset+N 行数据，然后丢弃前 offset 行。

当 offset 值很大时，数据库引擎必须读取大量索引记录并回表查询完整行数据，即使最终只返回 10 行。这种机制导致磁盘 I/O 和 CPU 消耗随偏移量线性增长。官方文档指出，高偏移量 LIMIT 查询效率低是因为需要访问许多行后才返回所需结果。

分步处理

按顺序执行以下步骤，每步完成后检查执行计划。

步骤 1：确认当前执行计划

使用 EXPLAIN 查看原始查询的扫描行数，确认 type 是否为 ALL 或 index，rows 是否远大于 10。

EXPLAIN SELECT * FROM table_name LIMIT 1000000, 10;

步骤 2：实施覆盖索引优化

如果查询只需要主键或已有覆盖索引，先查 ID 再回表。确保 id 列有索引。

SELECT t1.* FROM table_name t1
INNER JOIN (SELECT id FROM table_name LIMIT 1000000, 10) t2 ON t1.id = t2.id;

检查点：子查询仅扫描索引树，不回表，主查询仅回表 10 次。

步骤 3：实施业务游标优化

如果业务允许，记录上一页最后一条数据的 ID，下一页直接基于 ID 查询。

SELECT * FROM table_name WHERE id > last_seen_id ORDER BY id ASC LIMIT 10;

检查点：WHERE 条件利用索引范围扫描，rows 接近 10。

怎么验证是否生效

通过执行计划 rows 字段和实际耗时判断优化效果。

检查命令：

EXPLAIN SELECT ... (优化后的 SQL);

判断标准：

• EXPLAIN 输出中 rows 值从 1000010 降至 10 或略高。
• type 字段显示为 range 或 ref，而非 ALL。
• 实际执行时间从秒级降至毫秒级。

如果没有开启慢查询日志，可在客户端查看执行时间反馈。

常见坑

• ID 不连续：如果主键中间有大量删除空洞，基于 ID 的游标分页可能会跳过部分数据或分页数量不准。
• 排序不一致：使用业务游标时，ORDER BY 必须与索引顺序一致，否则无法利用索引范围扫描。
• 事务一致性：深分页优化后，如果在分页过程中数据发生变更，可能导致数据重复或遗漏，需在业务层评估一致性要求。
• 覆盖索引失效：延迟关联方案要求子查询字段必须完全覆盖索引，如果 SELECT 了非索引列，优化可能失效。

常见问题

为什么不能直接用 SQL_CACHE 缓存结果？

查询缓存依赖表数据不变，大表频繁写入会导致缓存频繁失效，且 MySQL 8.0 已移除查询缓存功能。

必须先 COUNT 总页数再分页吗？

不需要，COUNT 全表在大表上同样性能差，建议前端改用“下一页”按钮而非页码跳转。

业务游标方案支持跳转到指定页吗？

不支持，业务游标只能顺序翻页，如需跳页需结合离线索引或搜索引擎方案。

参考来源

• MySQL Reference Manual, "LIMIT Optimization", https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html