MySQL 实战宝典（七）：MySQL 连接查询、嵌套子查询与性能优化原理

摘要：在关系型数据库开发中，SQL 语句的编写质量直接决定了系统的性能上限。本文将深入剖析 MySQL 中最核心的两种查询方式——连接查询（JOIN）与嵌套子查询的底层执行原理，并揭秘 MySQL 优化器是如何基于 成本模型（Cost Model） 进行决策的。

---

一、连接查询：打破数据的孤岛

连接查询是关系型数据库的灵魂，它允许我们跨越表与表的边界检索数据。

1.1 全局概览 (MindMap)

首先，通过一张思维导图快速理清连接查询的分类与核心逻辑。

1.2 四大连接模式详解

假设我们有两张表：employees (员工) 和 departments (部门)。

(1) INNER JOIN (内连接)

这是最严格的匹配，只有当“员工”找到了“部门”，且“部门”里有该“员工”时才展示。

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

(2) LEFT JOIN (左外连接)

以左表为尊。展示所有员工，哪怕他没有部门（部门字段显示 NULL）。

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

(3) RIGHT JOIN (右外连接)

以右表为尊。展示所有部门，哪怕部门里没有员工。

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

(4) FULL OUTER JOIN (全连接)

MySQL 原生不支持 FULL JOIN，但我们可以通过 UNION “拼”出来。注意 UNION 会自动去重。

-- 既要所有员工，也要所有部门
SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

---

二、连接查询的底层原理与性能关键

理解原理是优化的前提。MySQL 使用嵌套循环连接 (Nested-Loop Join, NLJ) 算法来执行连接。

2.1 驱动表与被驱动表

• 驱动表 (Driver Table)：外层循环的表。MySQL 优化器会选择结果集更小的表作为驱动表。
• 被驱动表 (Driven Table)：内层循环的表。

2.2 执行流程时序图

下图展示了 LEFT JOIN 的执行过程（假设 Table A 是驱动表）：

2.3 ON 与 WHERE 的本质区别

这是开发者最容易混淆的地方，特别是在外连接中：

• ON 子句：决定了被驱动表的记录是否被“匹配”。如果外连接中 ON 不匹配，驱动表的记录依然会被保留（被驱动表补 NULL）。
• WHERE 子句：在连接操作完成后，对整个结果集进行“清洗”。如果不满足，整行记录（包括驱动表记录）都会被丢弃。

最佳实践：

• 涉及两表连接关系的条件，必须写在 ON 中。
• 只涉及单表数据的筛选，建议写在 WHERE 中（虽然优化器会自动优化，但这样语义更清晰）。

---

三、嵌套子查询 (Nested Subqueries) 性能陷阱

子查询虽然写法直观，但在 MySQL 早期版本（5.5及之前）性能极差，虽然 5.6+ 引入了 Materialization（物化）等优化，但仍需谨慎。

3.1 IN 子查询的陷阱

-- 找出有下属部门的经理
SELECT * FROM managers m 
WHERE m.dept_id IN (SELECT id FROM departments);

传统误区：我们以为 MySQL 会先执行子查询拿到 ID 列表，再查外层。
实际情况：在旧版本或特定场景下，MySQL 可能会把它改写为相关子查询，即 EXISTS 模式：

SELECT * FROM managers m 
WHERE EXISTS (SELECT 1 FROM departments d WHERE m.dept_id = d.id);

这意味着，外表 managers 有多少条记录，子查询就要执行多少次！如果外表很大，性能直接崩塌。

3.2 优化策略：子查询转 JOIN

绝大多数情况下，将子查询改写为 JOIN 是提升性能的捷径。

优化代码示例：

-- 优化后：使用 JOIN 代替 IN
SELECT m.* 
FROM managers m
JOIN departments d ON m.dept_id = d.id;

此时，MySQL 优化器可以自由选择谁做驱动表，利用索引进行高效匹配。

---

四、MySQL 性能优化的核心：成本模型 (Cost Model)

为什么有时 MySQL 选错索引？为什么它不选我们认为更快的方案？因为 MySQL 是基于成本（Cost-Based） 的优化器。

4.1 成本的构成

MySQL 计算一条查询的成本公式大致如下：
$$TotalCost=CPUCost+I/OCost$$

• I/O 成本：将数据页从磁盘加载到内存。默认成本常数 1.0。
• CPU 成本：读取记录、检测条件、排序等。默认成本常数 0.2。

4.2 优化器决策流程

4.3 调节成本常数 (高级调优)

MySQL 将这些“定价标准”存在了系统表中，DBA 可以根据硬件情况进行修改。

(1) mysql.server_cost (Server 层成本)

主要控制 CPU 相关的逻辑操作成本。

成本名称	默认值	含义	调优建议
disk_temptable_create_cost	40.0	创建磁盘临时表	调大该值，迫使优化器尽量避免使用磁盘临时表
row_evaluate_cost	0.2	检测记录是否符合条件	调大该值，会让优化器更倾向于使用索引
key_compare_cost	0.1	键值比较 (用于排序)	调大该值，可减少 FileSort，倾向索引排序

(2) mysql.engine_cost (存储引擎层成本)

主要控制 I/O 操作成本。

• io_block_read_cost (默认 1.0): 从磁盘读取一页的成本。
• memory_block_read_cost (默认 1.0): 从内存读取一页的成本。

调优场景：
如果你使用的是高性能 NVMe SSD，磁盘 I/O 极快，可以适当调低 io_block_read_cost，告诉优化器：“读磁盘没那么贵，放心读”，这可能会让 MySQL 更愿意选择某些基于全表扫描但能避免回表的方案。

---

• 总结与建议：

1. 连接查询优先：在大部分场景下，JOIN 的性能优于复杂的嵌套子查询，尤其是当被驱动表上有索引时。
2. 小表驱动大表：这是由 NLJ 算法决定的铁律。虽然优化器会自动选择，但复杂的 SQL 可能会迷惑优化器，开发者需心中有数。
3. 索引是关键：连接条件（ON 字段）必须要有索引！否则就是 $M\times N$ 的笛卡尔积式扫描，系统会瞬间卡死。
4. 理解成本：MySQL 不会“猜”哪个快，它是“算”出来的。理解 I/O 和 CPU 成本，有助于我们理解 EXPLAIN 的结果，甚至通过调整 Cost Constants 进行深度调优。