MySQL 8.0 优化大表 LIMIT OFFSET 分页性能的核心是避免扫描并丢弃大量偏移行，推荐优先使用覆盖索引配合延迟关联，或改用基于游标（Cursor-based）的条件分页。适用场景为单表数据量超过百万级且偏移量较大时，风险在于游标分页不支持随机跳页。
先说结论：大偏移量分页性能下降是因 MySQL 需扫描 offset+count 行后丢弃前 offset 行，优化本质是减少无效扫描。
- 先定位：通过慢查询日志或 EXPLAIN 确认扫描行数是否远大于返回行数。
- 先做：优先建立覆盖索引，或改写 SQL 使用延迟关联（Deferred Join）。
- 再验证：对比优化前后 EXPLAIN 的 rows 字段及实际执行时间。
快速处理思路
针对深分页问题，直接改写 SQL 结构比调整参数更有效，以下是两种常用改写方案。
方案一：延迟关联（适用场景：必须使用 OFFSET 且无法修改业务逻辑）
先通过覆盖索引查出主键，再回表查询完整数据。
示例：SELECT * FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY order_date LIMIT 200000, 10) tmp ON o.id = tmp.id;
方案二：游标分页（适用场景：无限滚动加载，允许连续翻页）
基于上一页最后一条记录的值（如 ID 或时间戳）获取下一页数据。
示例：SELECT * FROM orders WHERE id > last_seen_id ORDER BY id ASC LIMIT 10;
为什么会这样
MySQL 执行 LIMIT offset, count 时需要遍历并丢弃前 offset 行数据，导致时间复杂度随偏移量线性增长。
即使使用索引，若查询字段未覆盖索引，仍需回表获取数据行。当 offset 值极大（例如 100 万）时，MySQL 需读取前 100 万 +count 行数据，I/O 和 CPU 开销集中在无效扫描上，导致响应时间显著增加。
分步处理
按以下步骤逐步优化，每步完成后需验证执行计划变化。
步骤 1：检查执行计划
操作动作：使用 EXPLAIN 分析原 SQL。
验证结果：关注 rows 字段，若远大于 limit count 值，则存在优化空间。
风险边界：确保测试环境与生产数据量级接近，避免误判。
步骤 2：建立覆盖索引
操作动作：为 ORDER BY 和 WHERE 字段建立复合索引，确保 SELECT 字段均在索引中。
验证结果：EXPLAIN 的 Extra 列出现 Using index。
风险边界：索引过多会影响写入性能，需评估写操作频率。
步骤 3：改写 SQL 为延迟关联
操作动作：将原查询拆分为子查询查主键，外层关联查详情。
验证结果：子查询仅扫描索引，外层通过主键快速定位。
风险边界：子查询语法需符合 MySQL 版本规范，8.0 支持良好。
怎么验证是否生效
通过执行计划扫描行数和实际耗时双重验证优化效果。
检查命令：EXPLAIN [原 SQL] 与 EXPLAIN [优化后 SQL] 对比 rows 列。
状态判断：优化后 rows 应接近 limit count 值，而非 offset+count 总和。
页面表现：接口响应时间应显著降低，CPU 使用率回落。
常见坑
分页优化过程中容易忽略排序一致性和空值处理问题。
1. 缺少 ORDER BY：无排序分页导致每次执行返回顺序可能不同，引发数据重复或遗漏。
2. 排序字段无索引：ORDER BY 字段未建立索引会导致文件排序，抵消分页优化效果。
3. 动态拼接字段：直接在 SQL 中拼接 ORDER BY 字段名容易引发 SQL 注入，应通过白名单校验。
4. NULL 值影响：排序字段含 NULL 值可能导致顺序漂移，需明确 NULL 排序规则。
常见问题
窗口函数 ROW_NUMBER 能替代 LIMIT 分页吗？
不建议替代，窗口函数必须先计算全部行的序号再过滤，性能通常更差。
子查询分页和 JOIN 分页哪个更好？
JOIN 分页通常更好，子查询可能在某些版本优化不佳，JOIN 能更明确利用索引。
MySQL 8.0 有新参数能直接解决吗？
8.0.21 引入 prefer_ordering_index 参数可调整优化器对排序索引的偏好，但不能根本解决深分页扫描问题。
参考来源
1. 博客园 - MySQL 的 limit 优化 2 - jock_javaEE
2. 知乎 - mysql 中 LIMIT 分页怎么优化？
3. 博客文章 - MySQL 8.0 Limit 原理详解与大分页优化实战
4. 博客文章 - MySQL 8.0 中 LIMIT 优化新特性使用场景及最佳实践
5. 博客文章 - 如何解决 MySQL 的深度分页问题？