从单表查询到多表 JOINMySQL 执行计划背后的秘密摘要DBA 丢给你一个慢查询你除了加索引还能做什么本文从const到all逐层拆解 MySQL 的 6 种单表访问方法再深入到连接查询的底层——笛卡尔积、嵌套循环连接、Join Buffer以及ON与WHERE的微妙差异。读完之后你不仅能看懂EXPLAIN更能预判一条 SQL 到底会怎么跑。一、一条 SQL 的旅程从语法树到执行计划写 SQL 时我们总以为数据库应该这么执行先查 A 表再查 B 表最后拼在一起。但实际上你看到的 SQL 只是声明式语法——你告诉 MySQL “我想要什么”MySQL 自己决定怎么去拿。用户 SQL 语句 | v 查询解析器 - 语法树 | v 查询优化器 - 生成执行计划Execution Plan | 决定用哪个索引访问方法是什么连接顺序是什么 v 执行引擎 - 调用存储引擎接口 | v 返回结果集执行计划的核心就是两个问题单表怎么查- 访问方法access method多表怎么连- 连接算法join algorithm本文先回答第一个问题再回答第二个。二、单表访问的六种交通工具设计 MySQL 的大叔给单表查询的访问方法起了 6 个名字我们用一个更通俗的类比来理解把查询比作从西安钟楼到大雁塔不同的访问方法就像不同的交通工具。访问方法速度适用场景类比const极快主键/唯一索引等值匹配坐火箭ref很快普通二级索引等值匹配坐高铁ref_or_null很快普通二级索引等值匹配 NULL高铁 Plusrange中等索引列范围匹配开车兜风index较慢遍历二级索引全表坐公交all最慢全表扫描步行实战技巧执行EXPLAIN SELECT ...时type列就显示访问方法。优化 SQL 的首要目标就是尽量把ALL变成RANGE把RANGE变成REF或CONST。2.1 const坐火箭通过主键或唯一二级索引与常数进行等值比较最多只匹配一条记录。-- 主键等值查询SELECT*FROMsingle_tableWHEREid1438;-- 唯一二级索引等值查询SELECT*FROMsingle_tableWHEREkey23841;const 执行过程 B 树根节点 | v 逐层二分查找 -------------- | id 1438 | - 叶子节点聚簇索引 | 完整记录 | -------------- | v 直接返回记录最多 1 条B 树矮胖的结构决定了查找只需 3~4 次页面内比较代价极小。例外唯一索引列值为NULL时因为NULL可以有多个退化为refSELECT*FROMsingle_tableWHEREkey2ISNULL;-- type ref不是 const2.2 ref坐高铁通过普通二级索引与常数进行等值比较可能匹配多条记录然后回表。SELECT*FROMsingle_tableWHEREkey1abc;ref 执行过程 idx_key1 B 树 聚簇索引 B 树 -------------- -------------- | key1abc | -- 回表 -- | id x | | id x | | 完整记录 | -------------- -------------- | key1abc | -- 回表 -- | id y | | id y | | 完整记录 | -------------- --------------联合索引的 ref 用法只要最左边连续的列是等值匹配就能用ref。-- 能用 ref最左连续等值WHEREkey_part1god like;WHEREkey_part1aANDkey_part2b;-- 不能用 refkey_part2 不是最左列或不是全部等值WHEREkey_part1aANDkey_part2b;2.3 ref_or_null高铁 Plus在ref的基础上还要找出值为NULL的记录。SELECT*FROMsingle_tableWHEREkey1abcORkey1ISNULL;ref_or_null 执行过程 在 idx_key1 B 树中找两个范围 -------------------------------------- | ① 先找 key1 IS NULL 的连续记录 | | NULL 值在 B 树最左侧 | -------------------------------------- | ② 再找 key1 abc 的连续记录 | | 等值匹配 | -------------------------------------- | v 合并两个结果集的主键统一回表2.4 range开车兜风索引列匹配一个或多个范围区间。SELECT*FROMsingle_tableWHEREkey2IN(1438,6328)OR(key238ANDkey279);数轴上的区间示意 key2 的取值范围 ----------------------------------- 38 79 1438 6328 | | | | -------- --------- [38, 79] 两个单点 连续区间 结果取并集 - key2 ∈ {1438, 6328} ∪ [38, 79]能触发 range 的操作符,,IN,IS NULL,IS NOT NULL,,,,,BETWEEN,!,LIKE prefix%。复杂条件的区间提取当 WHERE 中有 AND/OR 组合时MySQL 会取区间交集AND或并集OR。-- AND - 取交集WHEREkey2100ANDkey2200-- 合并为 key2 200-- OR - 取并集WHEREkey2100ORkey2200-- 合并为 key2 100关键规则如果用不到索引的条件和用到索引的条件用OR连接整条都会退化为全表扫描WHEREkey2100ORcommon_fieldabc-- common_field 没有索引优化器会把 key2 100 也-- 当成需要全表扫描的条件最终 type ALL2.5 index坐公交当查询列表和 WHERE 条件中的列全部包含在某个二级索引中时MySQL 可以直接遍历二级索引的全部记录而不需要回表。SELECTkey_part1,key_part2,key_part3FROMsingle_tableWHEREkey_part2abc;index 执行过程 直接遍历 idx_key_part B 树的叶子节点 -------------- -------------- -------------- | key1, k2, k3 | -- | key1, k2, k3 | -- | key1, k2, k3 | | a, abc, x | | b, abc, y | | c, def, z | -------------- -------------- -------------- 匹配 匹配 不匹配 保留 保留 跳过为什么遍历二级索引比全表扫描快因为二级索引记录只包含索引列和主键比聚簇索引的完整记录小得多同样 16KB 的页能装更多记录遍历的页数更少。2.6 all步行最朴素的执行方式——直接扫描聚簇索引的全部叶子节点逐条对比 WHERE 条件。当没有任何索引可以使用时MySQL 就会选择这种方式。SELECT*FROMsingle_tableWHEREcommon_fieldabc;-- common_field 没有索引只能全表扫描all 执行过程 从聚簇索引的第一个叶子节点开始 页1: [R1][R2][R3][R4][R5] -- 页2: [R6][R7][R8][R9][R10] -- ... 逐条判断 common_field abc三、索引合并多条索引齐上阵前面说一般情况下只能利用单个二级索引但 MySQL 还留了一手索引合并Index Merge在特定场景下可以同时使用多个索引。3.1 Intersection 合并取交集当 WHERE 条件用AND连接多个索引列时可以从多个二级索引分别查出主键集合取交集后再回表。SELECT*FROMsingle_tableWHEREkey1aANDkey3b;Intersection 索引合并过程 idx_key1 B 树 idx_key3 B 树 ---------- ---------- | key1a | | key3b | | id1 | | id3 | | id3 | | id5 | | id5 | | id7 | ---------- ---------- | | | 主键取交集{1,3,5} ∩ {3,5,7} {3,5} | | ----------- ----------- | | v v 回表查 id3, id5什么情况下能用 Intersection 合并二级索引列都是等值匹配联合索引的每个列也必须等值匹配主键列可以是范围匹配为什么这么苛刻因为只有等值匹配时从每个二级索引查出的主键值集合是按主键排好序的这样取交集只需 O(n) 的双指针算法。如果顺序乱了就得先排序代价太高。按有序主键值回表取记录有个专业名词Rowid Ordered RetrievalROR。3.2 Union 合并取并集当 WHERE 条件用OR连接多个索引列时可以从多个二级索引分别查出主键集合取并集后再回表。SELECT*FROMsingle_tableWHEREkey1aORkey3b;Union 索引合并过程 idx_key1: {id1, id3} idx_key3: {id3, id5} | | | 主键取并集: {1,3} ∪ {3,5} {1,3,5} | | ----------- ------- | | v v 回表查 id1,3,5使用条件与 Intersection 类似二级索引列必须等值匹配主键可范围匹配。或者某部分用 Intersection 合并后再与其他结果集取 Union。3.3 Sort-Union 合并先排序再合并如果OR两边的条件涉及范围匹配查出的主键值集合就不是有序的了SELECT*FROMsingle_tableWHEREkey1aORkey3z;这时 MySQL 会先分别对两个主键集合排序然后再做 Union 合并。这就是 Sort-Union。为什么没有 Sort-Intersection因为 Intersection 的目的是减少回表记录数如果还要先对大量记录排序排序成本可能比回表还高得不偿失。索引合并的替代方案如果你发现 SQL 频繁触发 Intersection 索引合并不妨考虑建一个联合索引-- 原来有两个独立索引 idx_key1 和 idx_key3WHEREkey1aANDkey3b;-- 优化建联合索引直接命中无需合并ALTERTABLEsingle_tableADDINDEXidx_key1_key3(key1,key3);四、连接的本质笛卡尔积与过滤4.1 连接的本质连接JOIN的本质非常简单把各个连接表中的记录都取出来依次匹配符合条件的组合加入结果集。假设有 t1 和 t2 两张表SELECT*FROMt1,t2;t1 表: t2 表: -------- -------- | m1 | n1 | | m2 | n2 | -------- -------- | 1 | a | | 2 | b | | 2 | b | | 3 | c | | 3 | c | | 4 | d | -------- -------- 笛卡尔积结果3 × 3 9 行 ---------------- | m1 | n1 | m2 | n2 | ---------------- | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | | 1 | a | 4 | d | | 2 | b | 4 | d | | 3 | c | 4 | d | ----------------没有任何过滤条件时这就是笛卡尔积。3 个 100 行的表连接会产生 100 万行记录所以连接时必须有过滤条件。4.2 内连接 vs 外连接连接类型特点驱动表可互换内连接INNER JOIN驱动表在被驱动表中找不到匹配记录不加入结果集✅ 可以互换左外连接LEFT JOIN驱动表记录即使在被驱动表中无匹配也加入结果集被驱动表字段填 NULL❌ 左边固定是驱动表右外连接RIGHT JOIN同上但右边是驱动表❌ 右边固定是驱动表内连接t1 INNER JOIN t2 ON t1.m1 t2.m2 t1 t2 结果 1,a 2,b 2,b 匹配 2,b ✅ 2,b ---- 3,c 3,c 匹配 3,c ✅ 3,c 4,d 无匹配 ❌ 左外连接t1 LEFT JOIN t2 ON t1.m1 t2.m2 t1 t2 结果 1,a 2,b 无匹配但保留 1,a NULL,NULL ✅ 2,b ---- 3,c 2,b 匹配 2,b ✅ 3,c 4,d 3,c 匹配 3,c ✅4.3 ON 与 WHERE 的分工这是连接查询中最容易搞混的知识点SELECT*FROMt1LEFTJOINt2ONt1.m1t2.m2WHEREt2.n2d;子句作用对内连接对外连接ON连接条件等价于 WHERE仅决定被驱动表记录是否匹配不匹配时驱动表记录仍保留WHERE全局过滤过滤所有记录过滤所有记录包括外连接补 NULL 的记录LEFT JOIN 的执行逻辑 1. 用 ON 条件匹配 t1 和 t2 的记录 - 匹配成功组合成一行 - 匹配失败t1 记录保留t2 字段填 NULL 2. 用 WHERE 条件过滤上一步的结果 - 包括过滤掉 t2.n2 IS NULL 的行如果需要的话推荐写法只涉及单表的过滤条件 → 放在WHERE中涉及两表的连接条件 → 放在ON中五、连接算法嵌套循环与 Join Buffer5.1 嵌套循环连接NLJMySQL 执行连接查询的核心算法是嵌套循环连接Nested-Loop Join伪代码 for each row in 驱动表 { -- 驱动表只扫描 1 次 for each row in 被驱动表 { -- 被驱动表扫描 N 次N 驱动表结果行数 if 满足连接条件: 加入结果集 } }实际执行示例 SELECT * FROM t1, t2 WHERE t1.m1 1 AND t1.m1 t2.m2 AND t2.n2 d; 步骤1扫描驱动表 t1 t1 中满足 t1.m1 1 的记录 -------- | 2 | b | -- 第1条驱动记录 | 3 | c | -- 第2条驱动记录 -------- 步骤2用每条驱动记录去被驱动表 t2 中查找匹配 当 t1.m1 2 时t2 的查询变为 SELECT * FROM t2 WHERE t2.m2 2 AND t2.n2 d; -- 结果m22, n2b ✅ 当 t1.m1 3 时t2 的查询变为 SELECT * FROM t2 WHERE t2.m2 3 AND t2.n2 d; -- 结果m23, n2c ✅关键结论驱动表只访问1 次被驱动表访问N 次N 驱动表过滤后的记录数所以被驱动表的查询效率直接决定整个连接查询的效率5.2 被驱动表的索引加速既然被驱动表要被访问多次为它建立索引就至关重要。SELECT*FROMt1,t2WHEREt1.m1t2.m2;如果t2.m2是主键或唯一索引那么每次访问被驱动表都是const级别。MySQL 把这种在连接中对被驱动表使用主键/唯一索引等值查找的访问方法称为eq_ref。EXPLAIN 输出中的 type 列 -------------------------------- | id | select_type | table | type | -------------------------------- | 1 | SIMPLE | t1 | ALL | - 驱动表全表扫描 | 1 | SIMPLE | t2 | eq_ref | - 被驱动表用主键等值匹配 --------------------------------如果t2.m2是普通二级索引则 type 为ref。5.3 基于块的嵌套循环连接BNL当被驱动表没有索引且数据量很大时每次访问被驱动表都是全表扫描I/O 代价极高。MySQL 引入了Join Buffer来优化Join Buffer 工作机制 驱动表结果集假设 1000 条 | v --------------------------- | Join Buffer内存块 | | 容量 join_buffer_size | | 默认 256KB | | | | 装入一批驱动表记录比如100条| --------------------------- | v 扫描被驱动表只扫描 1 次 | v 被驱动表的每条记录与 Buffer 中所有记录做匹配 全部在内存中完成无需重复读盘效果对比场景无 Join Buffer有 Join Buffer驱动表 1000 条被驱动表读 1000 次被驱动表读 1 次被驱动表 100 万行1000 × 100万 行扫描1 × 100万 行扫描Join Buffer 配置-- 查看当前大小默认 256KBSHOWVARIABLESLIKEjoin_buffer_size;-- 临时调大会话级SETSESSIONjoin_buffer_size1048576;-- 1MB注意Join Buffer 中只放查询列表和过滤条件涉及的列所以再次强调——不要用SELECT *只选需要的列可以让 Buffer 装下更多记录。六、连接优化实战6.1 小表驱动大表由于被驱动表会被扫描多次选择结果集更小的表作为驱动表能显著减少整体扫描量。-- 假设 orders 表有 100 万条users 表有 1 万条-- 如果 WHERE 过滤后 orders 只剩 100 条users 剩 5000 条-- 优化前大表驱动小表SELECT*FROMusers uLEFTJOINorders oONu.ido.user_id;-- users 结果 5000 条 → orders 被扫描 5000 次-- 优化后小表驱动大表SELECT*FROMorders oLEFTJOINusers uONo.user_idu.id;-- orders 结果 100 条 → users 被扫描 100 次对于内连接MySQL 优化器会自动选择小表作为驱动表。但对于外连接驱动表是固定的LEFT JOIN 左边就是驱动表写 SQL 时要特别注意。6.2 为被驱动表建索引连接查询中被驱动表的连接列ON 条件中的列上必须有索引否则每次访问都是全表扫描。-- 慢查询SELECT*FROMorders oJOINusers uONo.user_idu.id;-- 如果 orders.user_id 没有索引orders 作为被驱动表时就是灾难-- 优化ALTERTABLEordersADDINDEXidx_user_id(user_id);6.3 调大 join_buffer_size当被驱动表确实无法建索引时比如临时表、复杂条件可以调大 Join Buffer-- 查看当前值SHOWVARIABLESLIKEjoin_buffer_size;-- 建议根据内存情况适当调大但不宜超过 1~4MB-- 过大的 Join Buffer 可能导致内存碎片和线程切换开销七、总结EXPLAIN 速查手册------------------------------------------------------------- | type | 速度 | 含义 | ------------------------------------------------------------- | const | 最快 | 主键/唯一索引等值匹配最多1条 | | eq_ref | 最快 | 连接中被驱动表用主键/唯一索引等值匹配 | | ref | 很快 | 普通二级索引等值匹配 | | ref_or_null | 很快 | 二级索引等值匹配 NULL | | range | 中等 | 索引范围扫描 | | index | 较慢 | 遍历整个二级索引覆盖索引 | | ALL | 最慢 | 全表扫描 | -------------------------------------------------------------优化优先级消灭 ALL给 WHERE 条件列加索引消灭 index检查是否 SELECT * 导致无法覆盖索引争取 const/ref确保等值查询命中主键或普通索引连接优化确保被驱动表的连接列有索引小表驱动大表最后的手段调大 join_buffer_sizeMySQL 查询执行全景图 单表查询 多表连接 | | v v -------- ----------- | const | 等值主键/唯一 | eq_ref | | ref | 等值普通索引 | ref | - 被驱动表有索引 | range | 范围匹配 | range | | index | 遍历二级索引 | index | | ALL | 全表扫描 | ALL/BNL | - 被驱动表无索引 -------- ----------- | | ---------------------------- | 索引合并Index Merge - IntersectionAND - UnionOR - Sort-Union范围OR延伸阅读MySQL 官方文档EXPLAIN Output Format《高性能 MySQL》第 5 章创建高性能的索引 从单表查询到多表 JOIN：MySQL 执行计划背后的秘密
从单表查询到多表 JOINMySQL 执行计划背后的秘密摘要DBA 丢给你一个慢查询你除了加索引还能做什么本文从const到all逐层拆解 MySQL 的 6 种单表访问方法再深入到连接查询的底层——笛卡尔积、嵌套循环连接、Join Buffer以及ON与WHERE的微妙差异。读完之后你不仅能看懂EXPLAIN更能预判一条 SQL 到底会怎么跑。一、一条 SQL 的旅程从语法树到执行计划写 SQL 时我们总以为数据库应该这么执行先查 A 表再查 B 表最后拼在一起。但实际上你看到的 SQL 只是声明式语法——你告诉 MySQL “我想要什么”MySQL 自己决定怎么去拿。用户 SQL 语句 | v 查询解析器 - 语法树 | v 查询优化器 - 生成执行计划Execution Plan | 决定用哪个索引访问方法是什么连接顺序是什么 v 执行引擎 - 调用存储引擎接口 | v 返回结果集执行计划的核心就是两个问题单表怎么查- 访问方法access method多表怎么连- 连接算法join algorithm本文先回答第一个问题再回答第二个。二、单表访问的六种交通工具设计 MySQL 的大叔给单表查询的访问方法起了 6 个名字我们用一个更通俗的类比来理解把查询比作从西安钟楼到大雁塔不同的访问方法就像不同的交通工具。访问方法速度适用场景类比const极快主键/唯一索引等值匹配坐火箭ref很快普通二级索引等值匹配坐高铁ref_or_null很快普通二级索引等值匹配 NULL高铁 Plusrange中等索引列范围匹配开车兜风index较慢遍历二级索引全表坐公交all最慢全表扫描步行实战技巧执行EXPLAIN SELECT ...时type列就显示访问方法。优化 SQL 的首要目标就是尽量把ALL变成RANGE把RANGE变成REF或CONST。2.1 const坐火箭通过主键或唯一二级索引与常数进行等值比较最多只匹配一条记录。-- 主键等值查询SELECT*FROMsingle_tableWHEREid1438;-- 唯一二级索引等值查询SELECT*FROMsingle_tableWHEREkey23841;const 执行过程 B 树根节点 | v 逐层二分查找 -------------- | id 1438 | - 叶子节点聚簇索引 | 完整记录 | -------------- | v 直接返回记录最多 1 条B 树矮胖的结构决定了查找只需 3~4 次页面内比较代价极小。例外唯一索引列值为NULL时因为NULL可以有多个退化为refSELECT*FROMsingle_tableWHEREkey2ISNULL;-- type ref不是 const2.2 ref坐高铁通过普通二级索引与常数进行等值比较可能匹配多条记录然后回表。SELECT*FROMsingle_tableWHEREkey1abc;ref 执行过程 idx_key1 B 树 聚簇索引 B 树 -------------- -------------- | key1abc | -- 回表 -- | id x | | id x | | 完整记录 | -------------- -------------- | key1abc | -- 回表 -- | id y | | id y | | 完整记录 | -------------- --------------联合索引的 ref 用法只要最左边连续的列是等值匹配就能用ref。-- 能用 ref最左连续等值WHEREkey_part1god like;WHEREkey_part1aANDkey_part2b;-- 不能用 refkey_part2 不是最左列或不是全部等值WHEREkey_part1aANDkey_part2b;2.3 ref_or_null高铁 Plus在ref的基础上还要找出值为NULL的记录。SELECT*FROMsingle_tableWHEREkey1abcORkey1ISNULL;ref_or_null 执行过程 在 idx_key1 B 树中找两个范围 -------------------------------------- | ① 先找 key1 IS NULL 的连续记录 | | NULL 值在 B 树最左侧 | -------------------------------------- | ② 再找 key1 abc 的连续记录 | | 等值匹配 | -------------------------------------- | v 合并两个结果集的主键统一回表2.4 range开车兜风索引列匹配一个或多个范围区间。SELECT*FROMsingle_tableWHEREkey2IN(1438,6328)OR(key238ANDkey279);数轴上的区间示意 key2 的取值范围 ----------------------------------- 38 79 1438 6328 | | | | -------- --------- [38, 79] 两个单点 连续区间 结果取并集 - key2 ∈ {1438, 6328} ∪ [38, 79]能触发 range 的操作符,,IN,IS NULL,IS NOT NULL,,,,,BETWEEN,!,LIKE prefix%。复杂条件的区间提取当 WHERE 中有 AND/OR 组合时MySQL 会取区间交集AND或并集OR。-- AND - 取交集WHEREkey2100ANDkey2200-- 合并为 key2 200-- OR - 取并集WHEREkey2100ORkey2200-- 合并为 key2 100关键规则如果用不到索引的条件和用到索引的条件用OR连接整条都会退化为全表扫描WHEREkey2100ORcommon_fieldabc-- common_field 没有索引优化器会把 key2 100 也-- 当成需要全表扫描的条件最终 type ALL2.5 index坐公交当查询列表和 WHERE 条件中的列全部包含在某个二级索引中时MySQL 可以直接遍历二级索引的全部记录而不需要回表。SELECTkey_part1,key_part2,key_part3FROMsingle_tableWHEREkey_part2abc;index 执行过程 直接遍历 idx_key_part B 树的叶子节点 -------------- -------------- -------------- | key1, k2, k3 | -- | key1, k2, k3 | -- | key1, k2, k3 | | a, abc, x | | b, abc, y | | c, def, z | -------------- -------------- -------------- 匹配 匹配 不匹配 保留 保留 跳过为什么遍历二级索引比全表扫描快因为二级索引记录只包含索引列和主键比聚簇索引的完整记录小得多同样 16KB 的页能装更多记录遍历的页数更少。2.6 all步行最朴素的执行方式——直接扫描聚簇索引的全部叶子节点逐条对比 WHERE 条件。当没有任何索引可以使用时MySQL 就会选择这种方式。SELECT*FROMsingle_tableWHEREcommon_fieldabc;-- common_field 没有索引只能全表扫描all 执行过程 从聚簇索引的第一个叶子节点开始 页1: [R1][R2][R3][R4][R5] -- 页2: [R6][R7][R8][R9][R10] -- ... 逐条判断 common_field abc三、索引合并多条索引齐上阵前面说一般情况下只能利用单个二级索引但 MySQL 还留了一手索引合并Index Merge在特定场景下可以同时使用多个索引。3.1 Intersection 合并取交集当 WHERE 条件用AND连接多个索引列时可以从多个二级索引分别查出主键集合取交集后再回表。SELECT*FROMsingle_tableWHEREkey1aANDkey3b;Intersection 索引合并过程 idx_key1 B 树 idx_key3 B 树 ---------- ---------- | key1a | | key3b | | id1 | | id3 | | id3 | | id5 | | id5 | | id7 | ---------- ---------- | | | 主键取交集{1,3,5} ∩ {3,5,7} {3,5} | | ----------- ----------- | | v v 回表查 id3, id5什么情况下能用 Intersection 合并二级索引列都是等值匹配联合索引的每个列也必须等值匹配主键列可以是范围匹配为什么这么苛刻因为只有等值匹配时从每个二级索引查出的主键值集合是按主键排好序的这样取交集只需 O(n) 的双指针算法。如果顺序乱了就得先排序代价太高。按有序主键值回表取记录有个专业名词Rowid Ordered RetrievalROR。3.2 Union 合并取并集当 WHERE 条件用OR连接多个索引列时可以从多个二级索引分别查出主键集合取并集后再回表。SELECT*FROMsingle_tableWHEREkey1aORkey3b;Union 索引合并过程 idx_key1: {id1, id3} idx_key3: {id3, id5} | | | 主键取并集: {1,3} ∪ {3,5} {1,3,5} | | ----------- ------- | | v v 回表查 id1,3,5使用条件与 Intersection 类似二级索引列必须等值匹配主键可范围匹配。或者某部分用 Intersection 合并后再与其他结果集取 Union。3.3 Sort-Union 合并先排序再合并如果OR两边的条件涉及范围匹配查出的主键值集合就不是有序的了SELECT*FROMsingle_tableWHEREkey1aORkey3z;这时 MySQL 会先分别对两个主键集合排序然后再做 Union 合并。这就是 Sort-Union。为什么没有 Sort-Intersection因为 Intersection 的目的是减少回表记录数如果还要先对大量记录排序排序成本可能比回表还高得不偿失。索引合并的替代方案如果你发现 SQL 频繁触发 Intersection 索引合并不妨考虑建一个联合索引-- 原来有两个独立索引 idx_key1 和 idx_key3WHEREkey1aANDkey3b;-- 优化建联合索引直接命中无需合并ALTERTABLEsingle_tableADDINDEXidx_key1_key3(key1,key3);四、连接的本质笛卡尔积与过滤4.1 连接的本质连接JOIN的本质非常简单把各个连接表中的记录都取出来依次匹配符合条件的组合加入结果集。假设有 t1 和 t2 两张表SELECT*FROMt1,t2;t1 表: t2 表: -------- -------- | m1 | n1 | | m2 | n2 | -------- -------- | 1 | a | | 2 | b | | 2 | b | | 3 | c | | 3 | c | | 4 | d | -------- -------- 笛卡尔积结果3 × 3 9 行 ---------------- | m1 | n1 | m2 | n2 | ---------------- | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | | 1 | a | 4 | d | | 2 | b | 4 | d | | 3 | c | 4 | d | ----------------没有任何过滤条件时这就是笛卡尔积。3 个 100 行的表连接会产生 100 万行记录所以连接时必须有过滤条件。4.2 内连接 vs 外连接连接类型特点驱动表可互换内连接INNER JOIN驱动表在被驱动表中找不到匹配记录不加入结果集✅ 可以互换左外连接LEFT JOIN驱动表记录即使在被驱动表中无匹配也加入结果集被驱动表字段填 NULL❌ 左边固定是驱动表右外连接RIGHT JOIN同上但右边是驱动表❌ 右边固定是驱动表内连接t1 INNER JOIN t2 ON t1.m1 t2.m2 t1 t2 结果 1,a 2,b 2,b 匹配 2,b ✅ 2,b ---- 3,c 3,c 匹配 3,c ✅ 3,c 4,d 无匹配 ❌ 左外连接t1 LEFT JOIN t2 ON t1.m1 t2.m2 t1 t2 结果 1,a 2,b 无匹配但保留 1,a NULL,NULL ✅ 2,b ---- 3,c 2,b 匹配 2,b ✅ 3,c 4,d 3,c 匹配 3,c ✅4.3 ON 与 WHERE 的分工这是连接查询中最容易搞混的知识点SELECT*FROMt1LEFTJOINt2ONt1.m1t2.m2WHEREt2.n2d;子句作用对内连接对外连接ON连接条件等价于 WHERE仅决定被驱动表记录是否匹配不匹配时驱动表记录仍保留WHERE全局过滤过滤所有记录过滤所有记录包括外连接补 NULL 的记录LEFT JOIN 的执行逻辑 1. 用 ON 条件匹配 t1 和 t2 的记录 - 匹配成功组合成一行 - 匹配失败t1 记录保留t2 字段填 NULL 2. 用 WHERE 条件过滤上一步的结果 - 包括过滤掉 t2.n2 IS NULL 的行如果需要的话推荐写法只涉及单表的过滤条件 → 放在WHERE中涉及两表的连接条件 → 放在ON中五、连接算法嵌套循环与 Join Buffer5.1 嵌套循环连接NLJMySQL 执行连接查询的核心算法是嵌套循环连接Nested-Loop Join伪代码 for each row in 驱动表 { -- 驱动表只扫描 1 次 for each row in 被驱动表 { -- 被驱动表扫描 N 次N 驱动表结果行数 if 满足连接条件: 加入结果集 } }实际执行示例 SELECT * FROM t1, t2 WHERE t1.m1 1 AND t1.m1 t2.m2 AND t2.n2 d; 步骤1扫描驱动表 t1 t1 中满足 t1.m1 1 的记录 -------- | 2 | b | -- 第1条驱动记录 | 3 | c | -- 第2条驱动记录 -------- 步骤2用每条驱动记录去被驱动表 t2 中查找匹配 当 t1.m1 2 时t2 的查询变为 SELECT * FROM t2 WHERE t2.m2 2 AND t2.n2 d; -- 结果m22, n2b ✅ 当 t1.m1 3 时t2 的查询变为 SELECT * FROM t2 WHERE t2.m2 3 AND t2.n2 d; -- 结果m23, n2c ✅关键结论驱动表只访问1 次被驱动表访问N 次N 驱动表过滤后的记录数所以被驱动表的查询效率直接决定整个连接查询的效率5.2 被驱动表的索引加速既然被驱动表要被访问多次为它建立索引就至关重要。SELECT*FROMt1,t2WHEREt1.m1t2.m2;如果t2.m2是主键或唯一索引那么每次访问被驱动表都是const级别。MySQL 把这种在连接中对被驱动表使用主键/唯一索引等值查找的访问方法称为eq_ref。EXPLAIN 输出中的 type 列 -------------------------------- | id | select_type | table | type | -------------------------------- | 1 | SIMPLE | t1 | ALL | - 驱动表全表扫描 | 1 | SIMPLE | t2 | eq_ref | - 被驱动表用主键等值匹配 --------------------------------如果t2.m2是普通二级索引则 type 为ref。5.3 基于块的嵌套循环连接BNL当被驱动表没有索引且数据量很大时每次访问被驱动表都是全表扫描I/O 代价极高。MySQL 引入了Join Buffer来优化Join Buffer 工作机制 驱动表结果集假设 1000 条 | v --------------------------- | Join Buffer内存块 | | 容量 join_buffer_size | | 默认 256KB | | | | 装入一批驱动表记录比如100条| --------------------------- | v 扫描被驱动表只扫描 1 次 | v 被驱动表的每条记录与 Buffer 中所有记录做匹配 全部在内存中完成无需重复读盘效果对比场景无 Join Buffer有 Join Buffer驱动表 1000 条被驱动表读 1000 次被驱动表读 1 次被驱动表 100 万行1000 × 100万 行扫描1 × 100万 行扫描Join Buffer 配置-- 查看当前大小默认 256KBSHOWVARIABLESLIKEjoin_buffer_size;-- 临时调大会话级SETSESSIONjoin_buffer_size1048576;-- 1MB注意Join Buffer 中只放查询列表和过滤条件涉及的列所以再次强调——不要用SELECT *只选需要的列可以让 Buffer 装下更多记录。六、连接优化实战6.1 小表驱动大表由于被驱动表会被扫描多次选择结果集更小的表作为驱动表能显著减少整体扫描量。-- 假设 orders 表有 100 万条users 表有 1 万条-- 如果 WHERE 过滤后 orders 只剩 100 条users 剩 5000 条-- 优化前大表驱动小表SELECT*FROMusers uLEFTJOINorders oONu.ido.user_id;-- users 结果 5000 条 → orders 被扫描 5000 次-- 优化后小表驱动大表SELECT*FROMorders oLEFTJOINusers uONo.user_idu.id;-- orders 结果 100 条 → users 被扫描 100 次对于内连接MySQL 优化器会自动选择小表作为驱动表。但对于外连接驱动表是固定的LEFT JOIN 左边就是驱动表写 SQL 时要特别注意。6.2 为被驱动表建索引连接查询中被驱动表的连接列ON 条件中的列上必须有索引否则每次访问都是全表扫描。-- 慢查询SELECT*FROMorders oJOINusers uONo.user_idu.id;-- 如果 orders.user_id 没有索引orders 作为被驱动表时就是灾难-- 优化ALTERTABLEordersADDINDEXidx_user_id(user_id);6.3 调大 join_buffer_size当被驱动表确实无法建索引时比如临时表、复杂条件可以调大 Join Buffer-- 查看当前值SHOWVARIABLESLIKEjoin_buffer_size;-- 建议根据内存情况适当调大但不宜超过 1~4MB-- 过大的 Join Buffer 可能导致内存碎片和线程切换开销七、总结EXPLAIN 速查手册------------------------------------------------------------- | type | 速度 | 含义 | ------------------------------------------------------------- | const | 最快 | 主键/唯一索引等值匹配最多1条 | | eq_ref | 最快 | 连接中被驱动表用主键/唯一索引等值匹配 | | ref | 很快 | 普通二级索引等值匹配 | | ref_or_null | 很快 | 二级索引等值匹配 NULL | | range | 中等 | 索引范围扫描 | | index | 较慢 | 遍历整个二级索引覆盖索引 | | ALL | 最慢 | 全表扫描 | -------------------------------------------------------------优化优先级消灭 ALL给 WHERE 条件列加索引消灭 index检查是否 SELECT * 导致无法覆盖索引争取 const/ref确保等值查询命中主键或普通索引连接优化确保被驱动表的连接列有索引小表驱动大表最后的手段调大 join_buffer_sizeMySQL 查询执行全景图 单表查询 多表连接 | | v v -------- ----------- | const | 等值主键/唯一 | eq_ref | | ref | 等值普通索引 | ref | - 被驱动表有索引 | range | 范围匹配 | range | | index | 遍历二级索引 | index | | ALL | 全表扫描 | ALL/BNL | - 被驱动表无索引 -------- ----------- | | ---------------------------- | 索引合并Index Merge - IntersectionAND - UnionOR - Sort-Union范围OR延伸阅读MySQL 官方文档EXPLAIN Output Format《高性能 MySQL》第 5 章创建高性能的索引