MySQL 亿级数据分页与慢查询排查优化全景实战

1. 致命隐患：为什么 LIMIT 1000000, 20 会拖垮数据库？#

在很多电商、日志系统或内容平台中，当数据规模达到数千万乃至上亿时，常见的深度分页查询往往是导致数据库 CPU 飙升至 100% 的首要元凶：

sql
SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY created_at DESC 
LIMIT 1000000, 20;

许多初级开发者误以为：“我只要最后 20 条数据，MySQL 应该很快返回才对”。 但在 InnoDB 存储引擎的底层，这个语句的实际执行代价是：

先根据二级索引定位并扫描 1,000,020 条 索引记录；

触发 1,000,020 次回表操作（Bookmark Lookup），根据主键从聚簇索引中读取整行完整数据；

将前面的 1,000,000 条数据白白丢弃，最终仅返回最后 20 条！

海量的回表产生了极其恐怖的随机 I/O，直接将磁盘读取带宽吃满。

---

2. 深度分页的三大主流优化架构方案#

方案一：子查询延迟关联（Deferred Join，最推荐）#

原理：先让二级索引在只检索主键 ID 的情况下走覆盖索引（无需回表），拿到 20 个目标 ID 后，再与原表进行主键关联。

sql
-- 优化后：耗时从 3.8s 暴降至 0.03s
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE status = 'PAID' 
    ORDER BY created_at DESC 
    LIMIT 1000000, 20
) AS lim ON o.id = lim.id;

方案二：游标分页法（Seek Method / Keyset Paging）#

如果业务场景允许（例如移动端无限滚动瀑布流、日志连续翻页），可以抛弃 OFFSET，改为记录上一页最后一条记录的自增 ID 或时间戳：

sql
SELECT * FROM orders 
WHERE status = 'PAID' AND id < 9845210 
ORDER BY id DESC 
LIMIT 20;

此方式直接利用 B+ 树索引的精准范围扫描（Range Scan），时间复杂度恒定为 $O(\log N)$，翻到第 1 亿页也仅需 1 毫秒！

---

3. EXPLAIN 执行计划关键字段速查#

使用 EXPLAIN 分析慢 SQL 时，重点关注 type 与 Extra 列：

type 访问效率等级（从优到劣）：

system > const > eq_ref > ref > range > index > ALL (全表扫描必须优化)

Extra 核心标志：

* Using index：利用覆盖索引，无需回表，极致高效； * Using filesort：无法利用索引排序，内存/磁盘排序，高并发下需警惕； * Using temporary：使用了临时表存储中间结果，必须重点排查。

在日常排查时，可借助站内的 [SQL 美化与格式化工具](/tools/sql) 对超长复杂嵌套查询进行语法缩进与多方言高亮整理，大幅提升排查效率。