④ MySQL 是如何基于各种规则去优化执行计划的

对应第 97 ~ 99 讲 · SQL 等价改写 · 子查询执行与物化 · semi join 半连接

总览 ① 数据访问方式 ② 多表关联原理 ③ 成本优化 ④ 规则优化 ⑤ EXPLAIN 字段 ⑥ Extra 与调优 架构与实践篇 01~20 实战与高可用篇 109~130
第一类 · SQL 语句的等价改写（第 97 讲） 第二类 · 子查询的执行与优化（第 98 讲） 第三类 · semi join 半连接（第 99 讲） 工程忠告 · 互联网公司的 SQL 编写原则（能简单就简单） 同样作用于子查询 IN 子查询的终极致优 与其等优化器救，不如一开始就写简单 数据量反转时 你写的 SQL 可能按字面执行并不高效 查询优化器 · 规则优化 执行前对 SQL 做一系列等价改写 更优的执行计划 语义不变 · 方便索引与数据页查找 规则 1 · 删除无关紧要的括号 清理多余嵌套 · 让语句结构更清晰 规则 2 · 常量传播 / 常量替换 i = 5 and j > i → i = 5 and j > 5 x = y and y = k and k = 3 → x = 3 and y = 3 and k = 3 规则 3 · 删除恒成立的无意义条件 b = b and a = a → 直接删除 规则 4 · 等值推导 → 提前查常量替换 join on t1.x1=t2.x1 and t1.id=1 → 先查出 t1 中 id=1 那行 把 t1 相关字段全部替换成该行的常量值，关联直接用常量匹配 标量子查询 · 拆成两步执行 x1 = (select x1 from t2 where id=xxx) 先执行子查询拿到常量 → 外层变成普通单表查询 相关子查询 · 依赖外层字段 → 性能低下 x1 = (select x1 from t2 where t1.x2=t2.x2) 外层每行都要执行一次子查询 → 遍历式执行，尽量避免 IN 子查询 → 物化表（materialization） 子查询结果集写入物化表：小 → memory 引擎 · 大 → 磁盘 B+ 树 关键：物化表会建立索引 → 后续查找不用裸扫中间结果 反向优化 · 物化表 500 行 vs 外层表 10 万行 改为全表扫物化表，每个值去外层表的索引里查 → 方向反转 IN 子查询在内核被改写为半连接 select * from t1 where x1 in (select x2 from t2 where x3=xxx) ≈ select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx semi join 的语义 t1 只要在 t2 中存在匹配行即保留 与 IN + 子查询语义完全等价 注意 MySQL 并无 semi join 语法 内核概念 · 有适用场景限制 能单表查询 就不要写多表关联 能多表关联 就尽量不要写子查询 复杂计算逻辑 放 Java 系统内存里做 SQL 简单 + 索引合适 数据库性能通常就不是问题 图例 优化器改写规则 性能陷阱 最佳实践 概念注解

改写规则的本质

  • • 各种改写看着琐碎，本质都是一件事：优化语义清晰度
  • • 常量传播让"能提前算出来的"提前算出来
  • • 等值推导 + 常量替换：join 条件里引入常量后直接物化那一行
  • • 改写后的 SQL 更方便在索引和数据页里执行查找

相关子查询为什么是性能黑洞

  • • 子查询的 WHERE 依赖外层表的字段值
  • • 外层每一条数据都要执行一遍子查询
  • • 相当于隐藏的嵌套循环，行数一大就失控
  • • 写 SQL 时优先考虑改写成 JOIN 或分步查询

物化 + 半连接：IN 子查询的两级优化

  • • 第一级：子查询结果物化成带索引的临时表
  • • 第二级：谁小扫谁 —— 物化表小就反过来驱动外层
  • • 终极形态：整个查询改写为 semi join 半连接
  • • semi join 只判断"存在匹配"，不关心匹配几行
  • • 与 IN 子查询语义完全等价，由内核自动选择

④ 规则优化 · MySQL 执行计划知识图谱 · 依据第 97 ~ 99 讲整理