在MySQL中深分页查询如何通过延迟关联利用索引优化-mysql教程-PHP中文网

延迟关联必须用INNER JOIN而非IN，因IN子查询易触发嵌套循环、放弃物化、退化为全表扫描；而INNER JOIN配合主键等值条件可使优化器稳定选择eq_ref访问类型，确保覆盖索引生效与精准回表。

在mysql中深分页查询如何通过延迟关联利用索引优化

延迟关联为什么必须用 INNER JOIN 而不是 IN

很多人把 SELECT * FROM t WHERE id IN (SELECT id FROM t ORDER BY create_time LIMIT 100000, 20) 当作延迟关联，结果 EXPLAIN 一看还是 type: ALL 或 Using filesort。这不是写法“差不多”，而是根本没走优化路径。

MySQL 优化器对 IN 子查询的处理倾向是嵌套循环（Nested Loop），尤其当子查询结果集不可预估时，它可能放弃物化、不复用索引，甚至退化成全表扫描。而 INNER JOIN 明确表达了连接语义，配合主键等值条件，能让优化器更可靠地选择 eq_ref 访问类型。

正确写法必须是 JOIN (...) AS tmp ON t.id = tmp.id 或 USING(id)
禁止在子查询里查非主键字段，比如 SELECT id, create_time —— 一旦带了 create_time，就失去覆盖索引优势，必然回表
若业务要求排序字段也出现在最终结果中，应确保该字段已包含在联合索引末尾（如 (status, create_time, id)），而非靠外层再取

联合索引字段顺序不能错：WHERE + ORDER BY + id 缺一不可

在 MySQL 5.7 及更早版本中，延迟关联能否生效，几乎完全取决于联合索引是否严格按 (过滤字段, 排序字段, 主键) 定义。漏掉任意一项，子查询就会触发 Using filesort 或全索引扫描。

例如查询 WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20：

建 INDEX(status, create_time) → 不行：缺少 id，子查询仍需回表取主键
建 INDEX(status, id, create_time) → 不行：排序字段 create_time 不在索引前缀连续位置，无法跳过排序
建 INDEX(status, create_time, id) → 正确：WHERE 过滤、ORDER BY 排序、SELECT id 全部命中索引叶子节点

注意 create_time 的升降序必须和 ORDER BY 一致；如果写 ORDER BY create_time DESC，索引也得是 (status, create_time DESC, id)（MySQL 8.0+ 支持，5.7 建议统一用升序索引 + 应用层翻转）。

EXPLAIN 必须验证的三个信号

改完 SQL 后别急着上线，先跑 EXPLAIN 看这三项是否全部满足：

win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录，或需要将该能力封装成可公开复用的 Codex skill。

下载

子查询那一行的 type 是 range 或 index，不是 ALL —— 表示 WHERE 条件走了索引
子查询的 Extra 字段含 Using index，且没有 Using filesort —— 表示排序直接用了索引有序性
外层主表的 type 是 eq_ref（主键 JOIN）或 ref（唯一索引 JOIN），不是 ALL 或 index —— 表示回表是精准的 20 次，不是扫全表

只要其中任一条件不成立，性能就不会有实质提升。常见干扰项包括：WHERE 中用了函数（DATE(create_time)）、参数类型不匹配（传字符串 '1' 给 TINYINT 字段）、或索引字段允许 NULL 导致排序不稳定。

MySQL 5.7 必须加 STRAIGHT_JOIN 强制执行顺序

MySQL 5.7 的优化器不会自动将子查询下推到索引扫描阶段，尤其当表上有多个索引时，它可能错误选择驱动表，导致子查询被物化成临时表，彻底失效。

稳妥做法是在 JOIN 前显式加 STRAIGHT_JOIN，强制子查询作为驱动表先行执行：

SELECT STRAIGHT_JOIN t.* 
FROM orders t 
INNER JOIN (
  SELECT id FROM orders 
  WHERE status = 1 
  ORDER BY create_time DESC 
  LIMIT 1000000, 10
) AS tmp ON t.id = tmp.id;

注意：STRAIGHT_JOIN 必须放在 SELECT 关键字后，且主表 t 必须在 JOIN 左侧；否则优化器仍可能重排。生产环境若用存储过程拼接 SQL，更要避免用 CONCAT + EXECUTE，否则慢日志里看不到真实语句，DBA 无法定位问题。