子查询与排序分页优化

开篇：那些"看起来简单"的 SQL 坑

有一类 SQL 问题特别阴险：它们写起来很简单，跑起来也快------在开发环境。等上了生产，数据量从几百条涨到几百万条，突然就炸了。

子查询、ORDER BY、分页查询都属于这一类。它们的语法初学者就会写，但藏在背后的性能陷阱，不踩一次很难意识到。

举个真实案例：一个后台管理系统，订单列表页翻到第 1000 页时突然卡死。原因是一条 LIMIT 999990, 10 的分页查询------MySQL 要先把前 999990 条记录全部查出来然后扔掉，只返回最后 10 条。当数据量达到百万级别，这个操作要花好几秒。

本文就来逐个拆解这些"看起来简单"的坑，以及对应的优化手段。

一、子查询优化

1.1 子查询为什么慢

子查询就是在一个 SELECT 里面嵌套另一个 SELECT。它语法直观，写起来很自然------但在 MySQL 中，子查询的执行效率往往不高。原因主要有三个：

临时表开销：MySQL 在执行子查询时，会为内层查询创建一个临时表存放结果，外层查询再从这个临时表中取数据。查询完毕后还要销毁临时表。这个"建表→查询→销毁"的过程消耗额外的 CPU 和 IO 资源。
临时表没有索引：不管是内存临时表还是磁盘临时表，都不会有索引。所以外层查询在临时表中查找数据时，只能全表扫描。
结果集越大越慢：如果子查询返回的结果集很大（比如几万行），临时表就越大，扫描开销也越大。

1.2 用 JOIN 替代子查询

在大多数情况下，子查询可以改写成 JOIN，性能会好很多。因为 JOIN 不需要创建临时表，而且可以利用索引。

改写前（子查询）：

-- 查询下过订单的用户
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE status = 'PAID'
);

改写后（JOIN）：

SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'PAID';

JOIN 版本可以直接利用 orders.user_id 和 users.id 上的索引，不需要创建临时表。

1.3 EXISTS vs IN：选哪个

EXISTS 和 IN 都能实现子查询的功能，但执行逻辑不同，适用场景也不同。

IN 的执行逻辑：先执行子查询，得到一个结果集（比如一组 ID），然后外层查询逐行检查某个字段是否在这个结果集中。

SELECT * FROM employees
WHERE dept_id IN (
    SELECT id FROM departments WHERE location = 'Beijing'
);

EXISTS 的执行逻辑：对外层查询的每一行，执行一次子查询（带关联条件），只要子查询返回至少一行，就认为匹配。

SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.location = 'Beijing' AND d.id = e.dept_id
);

如何选择？

场景	推荐	原因
外表大，子查询结果集小	IN	子查询只执行一次，结果集小，查找快
外表小，子查询涉及的表大	EXISTS	外层行数少，子查询执行次数少
子查询结果集非常大	EXISTS	避免生成大的临时结果集

简单记忆：外大用 IN，外小用 EXISTS。但在实际中，MySQL 优化器有时会自动做转换，所以最靠谱的办法还是用 EXPLAIN 对比两种写法的执行计划。

二、ORDER BY 优化

2.1 两种排序方式

MySQL 支持两种排序方式：Index 排序和 Filesort 排序。

Index 排序：如果 ORDER BY 的字段恰好是索引的一部分，而且查询条件也能走这个索引，那么数据本身就是有序的，MySQL 直接按索引顺序读取即可，不需要额外排序。就像翻一本按字母排好序的通讯录------本来就是有序的，直接读就行。

Filesort 排序：如果没法利用索引的有序性，MySQL 就要把数据取出来，放到 sort_buffer 中进行排序。如果 sort_buffer 装不下，还要写磁盘临时文件做归并排序。就像你拿到一堆乱序的扑克牌，要自己手动排序。

在 EXPLAIN 的 Extra 中看到 Using filesort 就说明走了第二种。这不一定是坏事（小数据量下 filesort 很快），但在大数据量下需要关注。

2.2 Filesort 的两种算法

当 MySQL 需要做 Filesort 时，内部有两种算法：

全字段排序（单路排序）：把查询需要的所有字段都放进 sort_buffer，排序完直接返回。只需要一次回表。

rowid 排序（双路排序）：只把排序字段和主键放进 sort_buffer，排序后再根据主键回表取完整数据。需要两次回表。

MySQL 怎么选？看参数 max_length_for_sort_data（默认 1024 字节）：

如果单行数据长度 < 这个值，用全字段排序（省回表）
如果单行数据长度 > 这个值，用 rowid 排序（省内存）

这也是为什么**不要 SELECT ***------字段越多，单行越长，越容易触发 rowid 排序，多一次回表。

2.3 如何让 ORDER BY 走索引

最理想的情况是让 ORDER BY 直接走索引，完全避免 Filesort。关键原则：

原则 1：WHERE 和 ORDER BY 用同一个索引

-- 假设有联合索引 (status, create_time)
SELECT * FROM orders
WHERE status = 'PAID'
ORDER BY create_time;
-- status 做等值匹配，create_time 做排序
-- 在索引中，相同 status 的记录已经按 create_time 有序
-- 可以直接走索引排序！

原则 2：ORDER BY 字段的顺序要和索引中的顺序一致

-- 联合索引 (a, b, c)
ORDER BY a, b, c    -- 可以走索引
ORDER BY a, b       -- 可以走索引（最左前缀）
ORDER BY b, c       -- 不走索引（跳过了 a）
ORDER BY a, c       -- 不走索引（跳过了 b）

原则 3：不要混合 ASC 和 DESC（MySQL 8.0 之前）

ORDER BY a ASC, b DESC  -- MySQL 5.x 不走索引
-- MySQL 8.0+ 支持降序索引，可以走索引

2.4 排序优化建议总结

在 WHERE 子句和 ORDER BY 子句中使用索引，优先用联合索引同时覆盖两者
如果 WHERE 和 ORDER BY 是同一个列，用单列索引就行；如果不同列，用联合索引
不要 SELECT *，减少排序的数据量
当范围条件和 ORDER BY 字段冲突时（只能给一个加索引），优先看哪个的过滤效果更好------如果 WHERE 能过滤掉 90% 的数据，优先给 WHERE 加索引

三、GROUP BY 优化

GROUP BY 的优化原则和 ORDER BY 非常相似，因为 MySQL 在执行 GROUP BY 时，默认会先排序再分组。

3.1 核心优化建议

给 GROUP BY 字段加索引：遵循最左前缀法则。即使没有 WHERE 条件，GROUP BY 也可以利用索引。
WHERE 优于 HAVING：能在 WHERE 中过滤的条件，不要放到 HAVING 中。WHERE 在分组前过滤，HAVING 在分组后过滤------提前过滤意味着参与分组的数据更少。

-- 不好：先分组再过滤
SELECT city, COUNT(*) FROM users
GROUP BY city
HAVING city != 'Unknown';

-- 好：先过滤再分组
SELECT city, COUNT(*) FROM users
WHERE city != 'Unknown'
GROUP BY city;

控制结果集大小：包含 ORDER BY、GROUP BY、DISTINCT 的查询，WHERE 条件过滤后的数据最好控制在 1000 行以内，否则 SQL 容易变慢。
能不排序就不排序：如果业务不关心分组后的顺序，可以加 ORDER BY NULL 禁止 MySQL 自动排序。

四、深度分页：百万级数据第 N 页

4.1 问题：LIMIT offset 为什么越来越慢

这是 MySQL 中最经典的性能坑之一。看这两条 SQL：

SELECT * FROM orders ORDER BY id LIMIT 10;       -- 几乎秒出
SELECT * FROM orders ORDER BY id LIMIT 999990, 10; -- 可能要好几秒

为什么差距这么大？因为 MySQL 的 LIMIT 实现方式是：先查出 offset + count 行，再丢掉前 offset 行。

也就是说，LIMIT 999990, 10 会先查出 1000000 行，然后扔掉前 999990 行，只返回最后 10 行。那前面 999990 行的查询、排序、回表工作全白做了。

这就像你去图书馆找第 10000 本书------管理员从第 1 本开始数，数到第 10000 本才拿给你。前面 9999 本完全是浪费。

4.2 方案一：延迟关联（Deferred Join）

核心思想：先用子查询在索引上快速定位到目标页的主键 ID，再用这些 ID 去取完整数据。

-- 原始（慢）
SELECT * FROM orders
WHERE name = 'Hollis'
ORDER BY id
LIMIT 1000000, 10;

-- 优化（延迟关联）
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE name = 'Hollis'
    ORDER BY id
    LIMIT 1000000, 10
) AS t ON o.id = t.id;

为什么快了？因为子查询 SELECT id FROM orders WHERE name='Hollis' ORDER BY id 走的是覆盖索引------如果 name 有索引，查询 id 不需要回表。只有最终确定了 10 个 ID 之后，才回表取完整数据。

4.3 方案二：基于 ID 的范围查询

如果主键是自增的，可以记住上一页的最大 ID，下一页直接从这个 ID 之后开始查：

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;
-- 假设第一页最大 ID 是 100

-- 第二页（传统分页）
SELECT * FROM orders ORDER BY id LIMIT 10, 10;

-- 第二页（基于 ID，更高效）
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

WHERE id > 100 直接通过主键索引定位到第 101 条记录，跳过了前面所有数据，效率极高。

但这个方案有局限：

要求 ID 连续且自增（如果有删除导致 ID 不连续，翻页可能会跳过数据）
只支持"上一页/下一页"这种连续翻页，不支持直接跳到第 N 页

4.4 方案三：子查询定位起始 ID

结合方案一和方案二的思路：

SELECT * FROM orders
WHERE name = 'Hollis'
  AND id >= (
    SELECT id FROM orders
    WHERE name = 'Hollis'
    ORDER BY id
    LIMIT 1000000, 1
  )
ORDER BY id
LIMIT 10;

子查询先定位到偏移位置的那条记录的 ID，然后主查询直接从这个 ID 开始取 10 条。同样利用了覆盖索引减少回表。

4.5 方案四：使用搜索引擎

如果分页需求是基于文本搜索的（如商品名搜索），可以把数据同步到 Elasticsearch。ES 的 search_after 机制天然支持高效的深度分页。

不过要注意，ES 本身也有深度分页的问题（from + size 模式下同样会变慢），需要配合 search_after 或 scroll API 使用。

4.6 LIMIT + ORDER BY 的数据重复问题

一个容易踩的坑：在分页查询中，同一条记录可能出现在多个页中。

原因是 MySQL 官方文档明确说明：如果 ORDER BY 的列存在重复值，服务器可以自由地以任何顺序返回这些行。也就是说，排序结果是不稳定的。

-- 可能每次执行结果不同
SELECT * FROM orders ORDER BY status LIMIT 10;

解决办法：在 ORDER BY 中加入一个唯一字段（通常是主键 ID），保证排序结果的确定性：

-- 稳定的分页查询
SELECT * FROM orders ORDER BY status, id LIMIT 10;

五、常见面试题精选

Q1：MySQL 的深度分页如何优化？

四种主流方案：

延迟关联：用子查询先在索引上定位 ID，再回表取数据。适用于不能改变分页方式的场景。
游标分页：记住上一页最大 ID，下一页用 WHERE id > last_max_id。适用于连续翻页的场景（如无限滚动列表）。
子查询定位起始 ID：先定位到偏移位置的 ID，再从该 ID 开始取数据。
搜索引擎：数据同步到 ES，利用 search_after 实现高效分页。

Q2：EXISTS 和 IN 有什么区别？怎么选？

两者的核心区别在于执行逻辑：

IN：先执行子查询得到结果集，再和外表逐行匹配。适合子查询结果集小的场景。
EXISTS：对外表每一行执行一次子查询，只要返回至少一行就算匹配。适合外表数据量小的场景。

选择口诀：外表大用 IN，外表小用 EXISTS。

Q3：ORDER BY 是怎么实现的？

两种方式：

索引排序：如果 ORDER BY 字段在索引中且查询条件符合，直接利用索引有序性读取，零排序开销。
Filesort：无法走索引时，在 sort_buffer 中排序。数据量小时在内存完成；数据量大时用磁盘临时文件做归并排序。Filesort 内部又分全字段排序（一次回表）和 rowid 排序（两次回表），MySQL 根据行长度自动选择。

Q4：LIMIT 0,100 和 LIMIT 10000000,100 一样吗？

完全不一样。MySQL 的 LIMIT 实现是"先查出 offset+count 行，再丢弃前 offset 行"。所以 LIMIT 10000000,100 要先查出 1000 万行再扔掉 999 万行，极慢。而 LIMIT 0,100 只需要查 100 行。这就是深度分页问题，需要用延迟关联或游标分页来优化。

小结

本文覆盖了三类"看起来简单但坑很深"的 SQL 问题：

子查询：优先用 JOIN 替代。如果必须用子查询，IN 和 EXISTS 按"外大用 IN，外小用 EXISTS"的原则选择。
ORDER BY：让 WHERE + ORDER BY 走同一个联合索引是最佳方案。看到 EXPLAIN 中的 Using filesort 就要评估是否需要优化。不要 SELECT *，减少排序数据量。
深度分页：LIMIT offset, count 中 offset 越大越慢。用延迟关联（先查 ID 再回表）或游标分页（WHERE id > last_id）来优化。排序字段有重复值时记得加唯一字段保证稳定性。

这三类问题的共同特点是：开发环境数据量小时看不出问题，上生产后数据量一大就暴露。所以养成好习惯------写完 SQL 后先用 EXPLAIN 检查一下，防患于未然。

附录：ON 和 WHERE 的区别

在 JOIN 查询中，经常有人搞混 ON 和 WHERE 的作用。它们看起来都是"过滤条件"，但生效时机完全不同：

ON 子句：在 JOIN 阶段生效，决定两张表如何匹配
WHERE 子句：在 JOIN 完成后生效，对合并后的结果集进行过滤

在 INNER JOIN 中，条件写在 ON 里还是 WHERE 里，结果相同。但在 LEFT JOIN 中，差别很大：

-- 条件在 ON 中：返回所有员工，IT 部门匹配上的显示部门信息，其他显示 NULL
SELECT * FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id
    AND departments.name = 'IT';

-- 条件在 WHERE 中：先做 LEFT JOIN，再过滤，只返回 IT 部门的员工
SELECT * FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id
WHERE departments.name = 'IT';

第一种写法返回全部员工（LEFT JOIN 的语义），非 IT 部门的员工对应的部门列为 NULL。第二种写法因为 WHERE 过滤掉了 departments.name 不是 'IT' 的行（包括 NULL），实际效果等同于 INNER JOIN。

简单记忆：ON 管"怎么连"，WHERE 管"怎么筛"。