深入剖析MySQL优化器如何选择索引与JOIN顺序

阿澈

发布时间：2026-10-13 | 5人浏览过

原创

本文通过具体案例解析MySQL优化器在查询执行计划中的决策逻辑。详细阐述Range Analysis阶段如何评估记录数，以及优化器如何通过穷举Left-Deep Tree来寻找最优JOIN顺序和访问方式。对比正常数据分布与极端倾斜情况下的执行差异，揭示基于统计信息预估可能导致的Bad Case，帮助开发者理解底层机制以优化SQL性能。

1. 测试SQL与可能的执行路径

为了探究MySQL优化器的行为，我们准备以下包含两张表的关联查询：

EXPLAIN
SELECT *
FROM employee AS A, department AS B
WHERE A.LastName = 'zhou'
  AND B.DepartmentID = A.DepartmentID
  AND B.DepartmentName = 'TBX';

针对上述SQL，优化器面临的主要选择包括：

JOIN顺序：先扫描A表后B表（A→B），或先扫描B表后A表（B→A）。
A表访问方式：全表扫描、使用索引IND_L_D（匹配LastName）、或使用索引IND_DID（通过连接条件匹配DepartmentID）。
B表访问方式：全表扫描、使用索引IND_D（匹配DepartmentID）、或使用索引IND_DN（匹配DepartmentName）。

2. MySQL优化器的决策流程

MySQL优化器的核心工作包括查询重写、常量表检测、范围分析（Range Analysis）、JOIN优化（顺序与访问方式选择）以及计划细化。本案例重点解析从Range Analysis到JOIN优化的过程。

第1步：Range Analysis（范围分析）

此阶段主要评估所有可能的Range条件和Index Merge成本。虽然等值查询不是典型的Range范围，但优化器仍会将其视为一种特殊的Range进行成本估算，以确定命中的大致记录数（Found Records）。

在本案例中：

A表：A.LastName = 'zhou'，预计命中记录数为 51。
B表：B.DepartmentName = 'TBX'，预计命中记录数为 1。

这些数值后续将用于Ref访问方式的成本修正。注意：InnoDB每次调用相关接口进行采样可能消耗性能，而MariaDB等数据库通过直方图统计信息来避免此类操作。

第2步：确定搜索策略与初始排序

MySQL优化器通过枚举所有的Left-Deep树（即左深树结构）来搜索最优执行计划。由于两表JOIN的搜索空间极小，优化器直接退化为穷举搜索。

初始排序规则：优化器根据Range Analysis得到的Found Records对表进行排序，记录数少的表优先考虑作为驱动表。因此，初始候选顺序为：B → A。

第3步：穷举JOIN顺序与访问方式

场景一：优先尝试顺序 B → A

1. 确定B表（第一个表）的访问方式

作为第一个表，B无法利用来自A表的列作为Ref条件，只能基于自身条件选择索引。
- 候选索引：IND_DN (匹配'DBX') vs 全表扫描。
- 成本比较：IND_DN的IO成本估算较低（约1.0），优于全表扫描（约2.0）。
- 结果：B表使用Ref访问方式，采用索引IND_DN，预估总成本约为 1.2。

2. 确定A表（第二个表）的访问方式

A表加入JOIN后，可以利用已确定的B表结果。
- 候选索引：IND_L_D (仅用A自身条件) vs IND_DID (通过B.DepartmentID关联)。
- IND_L_D评估：
Range Analysis显示命中文档约51条。Ref方式下，IO成本需经过Seek修正（Worst Seek修正），最终成本约为 25.2。
- IND_DID评估：
这是基于连接的索引查找。已知B表结果仅有1条记录，因此只需查找对应的一条DepartmentID。
根据索引基数（Cardinality）估算，该值的出现频率极低，FOUND_RECORD估算为1×1=1。
IO成本仅为1，加上基础开销，总成本约为 1.2。
- 比较结论：IND_DID的成本（1.2）远低于IND_L_D（25.2）。优化器选择使用IND_DID。

场景一总成本：B表(1.2) + A表(1.2) = 2.4。

场景二：尝试顺序 A → B

1. 确定A表（第一个表）的访问方式

A作为驱动表，不能引用B表字段。只能选择IND_L_D。
- 成本估算：与场景一中计算的IND_L_D成本一致，约为 25.2。

2. 剪枝判断

当前A表的访问成本（25.2）已经远超之前找到的最优解（2.4）。优化器立即剪枝，不再继续计算B表的访问成本。

第4步：最终执行计划确认

通过对比，优化器判定顺序 B → A 且 A表使用索引 IND_DID 为最优方案，总成本最低。

3. 测试环境数据背景

本案例基于MySQL 5.1.48及InnoDB引擎构建。数据构造如下：

department表：生成随机部门，其中DepartmentName='TBX'的记录极少（1条），DepartmentID=27760对应特定部门。
employee表：生成随机员工，其中LastName='zhou'的记录较多（51条），且这部分员工的DepartmentID均集中在27760。

4. 数据倾斜带来的Bad Case分析

优化器高度依赖统计信息进行成本预估。当数据分布严重倾斜时，基于基数的估算可能与实际情况偏差巨大，导致次优甚至错误的执行计划。

重构数据后的现象

我们将数据分布调整为：'zhou'对应的员工减少至1人，但'TBX'部门的员工增加至1010人（其中10人名字是TBX，大量是同部门其他名字的员工）。

预期正确逻辑：
如果先查B表（DepartmentName='TBX'），结果应包含大量同部门但不匹配Lastname的人，效率较低。但如果先看A表，'zhou'只有1人，顺着他的ID去B表找'TBX'，效率极高。

实际执行计划（MySQL错误判断）

| id | select_type | table | type | possible_keys | key   | ref             | rows |
|----|-------------|-------|------|---------------|-------|-----------------|------|
|  1 | SIMPLE      | A     | ref  | IND_L_D       | IND_L_D| const           |    1 |
|  1 | SIMPLE      | B     | ref  | IND_D         | IND_D  | test.A.DepartmentID|  1010 |

问题分析：

优化器依然认为先查A表（命中1条）更优，因为它的统计信息显示‘zhou’非常稀少。
然而，A表中这唯一记录的DepartmentID是27760，对应B表中有高达1010条记录。
随后B表使用IND_D索引查找，虽然找到了这些记录，但需要逐一过滤DepartmentName='TBX'这一条件（Extra: Using where）。

更优方案应为：

先查B表，利用IND_DN索引快速定位‘TBX’相关的少量高价值记录，再与A表关联。但在本例的数据分布下，优化器被‘zhou’的低卡片数误导，选择了先扫描A表的次优路径。

以上就是MySQL优化器如何选择索引和JOIN顺序的详细内容，更多关于MySQL执行计划优化的资料请关注本站其它相关文章！