MySQL 千万级大表深分页与索引调优实战

在你的系统刚上线时，页面点哪里都是秒开。随着业务的发展，订单表（orders）的数据量不知不觉突破了千万。

某天，一个运营小姐姐在后台管理系统中，将订单列表翻到了第 10 万页，然后系统卡死了，紧接着监控面板上 MySQL 的 CPU 使用率飙升到了 100%。

DBA 发来了一张愤怒的截图，上面赫然写着你代码里生成的 SQL：

SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 10;

这就是臭名昭著的 “深分页（Deep Paging）灾难”。

1. 为什么 LIMIT 大了会慢得离谱？

很多人以为 LIMIT 1000000, 10 的意思是：MySQL 直接跳到第 100 万行，然后往后读 10 行。 这是完全错误的。

在 InnoDB 引擎中，由于数据是基于 B+ 树存储的，MySQL 并没有一个神奇的指针能直接指向第 100 万行。它的真实执行逻辑是：

根据二级索引（如 status, create_time）的 B+ 树，扫描并取出 1,000,010 条记录的主键 ID。
拿着这 1,000,010 个主键 ID，回到聚簇索引（主键索引树）中进行回表（回表查询），查出这 100 多万条记录的完整数据（也就是 SELECT *）。
终于拿到了 100 多万条完整数据，MySQL 将前 100 万条直接丢弃，只把最后 10 条返回给你。

大量的无用回表，产生了海量的随机磁盘 I/O 和内存开销，直接压垮了数据库。

2. 终极解法：如何干掉深分页？

在 2026 年的架构设计中，面对千万级的大表分页，我们绝不使用 LIMIT offset, size。主要有以下两种标准解法：

方案一：子查询（延迟关联）—— 适合必须跳页的管理后台

既然慢在“前 100 万条数据无用的回表”，那我们就不让它回表！我们先通过子查询，在**纯索引（覆盖索引）**的层面上，极快地定位到那 10 条需要的数据的主键 ID，然后再拿这 10 个 ID 去回表。

优化后的 SQL：

SELECT t1.*
FROM orders t1
INNER JOIN (
    -- 这一步因为只查 ID，且用到了 (status, create_time) 的联合索引，触发了索引覆盖（Using index），没有任何回表，速度极快！
    SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 10
) t2 ON t1.id = t2.id;

这种写法叫做 延迟关联（Deferred Join），可以将深分页的耗时从几十秒瞬间降到几百毫秒级别。

方案二：游标分页（Cursor Paging）—— 适合移动端/瀑布流滚动

如果你的场景是类似抖音、小红书的下拉刷新（瀑布流），用户是不需要输入“跳转到第 10 万页”这种脑残操作的。他们只能一页一页往下刷。

既然如此，我们每次查询时，把上一页最后一条记录的标志（如 id 或 create_time）传给后端。

游标 SQL：

-- 假设上一页最后一条记录的 id 是 9527000
SELECT * FROM orders
WHERE status = 1 AND id < 9527000
ORDER BY id DESC
LIMIT 10;

由于条件是 id < 9527000，MySQL 可以利用主键索引直接精准定位到底层叶子节点的位置，无论你往下滑了多少页，查询耗时永远是稳定的 O(1)，不到 10 毫秒！

(注意：如果用 create_time 做游标，由于时间可能重复，通常需要用 create_time + id 作为联合游标)

3. 大表索引调优的两把尖刀

除了深分页，我们在建索引时还经常踩坑。在 2026 年的 MySQL 8.x 环境下，你必须深刻理解两个核心概念：

尖刀一：最左前缀法则与联合索引

如果你建了一个联合索引 INDEX (a, b, c)，它就相当于一个以 a 为首字母，b 为中字母，c 为尾字母的字典。

WHERE a=1 AND b=2 AND c=3：完美走索引。
WHERE a=1 AND c=3：a 能走索引，但因为跳过了 b，c 走不了索引（会退化为范围过滤）。
WHERE b=2：完全不走索引，因为你没有给 a。

架构准则：在设计表结构时，把区分度最高（唯一值最多）、最常作为等值查询条件的列，放在联合索引的最左边。

尖刀二：索引下推 (Index Condition Pushdown, ICP)

在以前的 MySQL 版本中，如果执行 WHERE name = '张' AND age LIKE '%18%'，即使有 (name, age) 联合索引。引擎也会先通过 name 把所有姓张的 ID 找出来，然后老老实实回表查出整行数据，然后在 Server 层再用 LIKE 过滤 age。

而在现代 MySQL 中，默认开启了 ICP（Using index condition）。存储引擎在扫描索引树时，如果发现索引里本来就包含了 age 字段，它会直接在索引层面把不符合 LIKE 的记录过滤掉，大幅减少无用的回表操作。

总结

千万级大表并不可怕，可怕的是我们用对付百条数据的思维去写 SQL。

废弃大 Offset：在 C 端禁用跨页跳转，强制使用游标（Cursor）模式。在 B 端管理后台，使用延迟关联（覆盖索引查 ID + Join 回表）优化深分页。
消灭无用回表：合理建立联合索引，利用最左前缀和覆盖索引，把所有的过滤动作全部逼停在索引树上。

做到了这些，你的 MySQL 依然能在一堆 NoSQL 的围剿中，稳稳地充当着业务系统的绝对核心定海神针。