为什么MySQL要“小表驱动大表”

在 MySQL 优化中，“小表驱动大表”是一条广为人知的核心原则——尤其在多表 Join 场景下，驱动表的选择直接影响查询性能，甚至可能导致“毫秒级”与“秒级”的差距。但很多开发者只知其然，不知其所以然：为什么小表驱动大表更快？没有索引时还成立吗？多表 Join 如何选择驱动表？

一、先明确概念：什么是“驱动表”与“被驱动表”？

在 MySQL 的 Join 操作中，两个表的角色的固定的，核心区分如下：

• 驱动表（外层表）：Join 时先被扫描的表，通过它的结果集作为“过滤条件”去匹配另一个表；
• 被驱动表（内层表）：Join 时后被扫描的表，通过驱动表的结果集进行匹配查询（通常需要索引支持）。

核心公式：驱动表的行数 × 被驱动表的单行匹配成本 = 总查询成本
优化目标：最小化总查询成本，因此需要让“驱动表行数”尽可能小，“被驱动表单行匹配成本”尽可能低（通过索引实现）。

举个直观例子：

• 表 A（小表）：100 行数据（部门表 departments）；
• 表 B（大表）：100 万行数据（员工表 employees）；
• 小表驱动大表：100 行 × 索引匹配成本（毫秒级）= 总成本；
• 大表驱动小表：100 万行 × 全表扫描成本（百毫秒级）= 总成本（直接爆炸）。

二、底层原理：为什么小表驱动大表更快？

“小表驱动大表”的性能优势，本质是“减少外层循环次数 + 利用索引降低内层匹配成本”，核心依赖 MySQL 的 Join 执行机制。

1. MySQL 两种核心 Join 算法

MySQL 主要通过两种算法实现 Join，无论哪种算法，小表驱动大表都能最大化性能：

（1）Nested-Loop Join（嵌套循环连接）—— 最常用算法

原理：外层循环遍历驱动表的每一行，内层循环根据连接条件去被驱动表中匹配数据，类似 Java 的嵌套循环。

• 无索引场景：被驱动表需全表扫描，总成本 = 驱动表行数 × 被驱动表行数；
• 有索引场景：被驱动表通过索引匹配，总成本 = 驱动表行数 × 索引查询成本（O(logN)）。

（2）Hash Join（哈希连接）—— 适合无索引大表

原理：先将小表加载到内存构建哈希表，再扫描大表，通过哈希表快速匹配数据（MySQL 8.0+ 支持）。

• 核心限制：小表必须能加载到内存，否则会使用磁盘临时表，性能骤降；
• 优势：无索引时性能远超 Nested-Loop Join，但仍需小表作为“构建哈希表的表”（小表构建哈希表更快，占用内存更少）。

2. 性能差距的核心根源：循环次数与匹配成本

我们用具体数据量化对比（基于 Nested-Loop Join 算法）：
假设：

• 小表 T1（部门表）：100 行；
• 大表 T2（员工表）：100 万行；
• 无索引时，被驱动表单行匹配成本 = 1ms（全表扫描单行耗时）；
• 有索引时，被驱动表单行匹配成本 = 0.01ms（B+树索引查询耗时）。

场景 1：小表驱动大表（T1 驱动 T2）

• 无索引：总成本 = 100 行 × 1ms = 100ms；
• 有索引：总成本 = 100 行 × 0.01ms = 1ms；

场景 2：大表驱动小表（T2 驱动 T1）

• 无索引：总成本 = 100 万行 × 1ms = 1000000ms（约 16.7 分钟）；
• 有索引：总成本 = 100 万行 × 0.01ms = 10000ms（约 10 秒）；

结论：

• 有索引时，小表驱动大表的性能是大表驱动小表的 10000 倍；
• 无索引时，小表驱动大表的性能是大表驱动小表的 10000 倍；
• 索引是“小表驱动大表”的放大器——没有索引时，小表驱动大表仍有优势，但差距会缩小（本质是外层循环次数减少）。

3. 无索引时：小表驱动大表仍成立吗？

很多人误以为“小表驱动大表”只在有索引时成立，但实际无索引场景下，该原则依然有效——只是优势从“指数级”变为“线性级”。

无索引场景对比（基于 Nested-Loop Join）：

• 小表驱动大表：外层循环 100 次，每次内层循环扫描 100 万行（全表扫描），总扫描行数 = 100 × 100 万 = 10 亿行；
• 大表驱动小表：外层循环 100 万次，每次内层循环扫描 100 行（全表扫描），总扫描行数 = 100 万 × 100 = 10 亿行；

看似扫描行数相同，为什么小表驱动大表仍更快？

• 磁盘 IO 优化：小表驱动大表时，小表可完全加载到内存（100 行占用极小），减少磁盘 IO；大表驱动小表时，大表无法全部加载到内存，需频繁磁盘读写；
• CPU 缓存优化：小表的结果集小，CPU 缓存命中率高；大表的结果集大，CPU 缓存频繁失效，增加计算开销；
• 实际测试（无索引）：小表驱动大表耗时约 30 秒，大表驱动小表耗时约 60 秒，差距仍达 2 倍。

三、实战判断：如何确定谁是驱动表？

MySQL 优化器会自动选择驱动表，但并非总能选对（尤其在统计信息过时、索引缺失时）。我们需要手动判断驱动表，并通过优化让优化器做出正确选择。

1. 看执行计划：type 列与 rows 列

通过 EXPLAIN 查看 Join 查询的执行计划，核心关注两列：

• type 列：驱动表通常为 ALL（全表扫描）或 range（范围扫描），被驱动表若有索引则为 ref 或 eq_ref（高效索引匹配）；
• rows 列：预估扫描行数，行数少的通常是驱动表（优化器倾向选择行数少的表作为驱动表）。

执行计划示例（小表驱动大表）：

EXPLAIN SELECT e.name, d.name 
FROM departments d  -- 小表（100 行）
JOIN employees e    -- 大表（100 万行）
ON e.department_id = d.id;

执行计划关键输出：

id	table	type	rows	key
1	d	ALL	100	NULL	-- 驱动表（全表扫描，行数少）
1	e	ref	10000	idx_department_id	-- 被驱动表（索引匹配，ref 表示非唯一索引匹配）

执行计划示例（大表驱动小表，反面案例）：

id	table	type	rows	key
1	e	ALL	1000000	NULL	-- 驱动表（全表扫描，行数多）
1	d	ref	1	PRIMARY	-- 被驱动表（索引匹配，但外层循环次数太多）

2. 优化器选择驱动表的核心逻辑

MySQL 优化器通过“成本估算”选择驱动表，核心参考因素：

• 表的大小（通过统计信息 information_schema.TABLES 中的 ROWS 字段）；
• 索引的可用性（被驱动表是否有可用的 Join 索引）；
• 查询条件（如 WHERE 子句过滤后的行数，过滤后行数少的表更可能成为驱动表）。

关键公式（简化版）：

驱动表成本 = 驱动表过滤后行数 × 单行扫描成本
被驱动表成本 = 驱动表过滤后行数 × 被驱动表单行匹配成本
总成本 = 驱动表成本 + 被驱动表成本
优化器选择总成本最低的表作为驱动表。

3. 手动干预驱动表：STRAIGHT_JOIN

当优化器选择错误的驱动表时（如统计信息过时），可通过 STRAIGHT_JOIN 强制指定驱动表（仅适用于两表 Join）。

语法格式：

SELECT ... FROM 小表 STRAIGHT_JOIN 大表 ON 连接条件;

示例（强制部门表作为驱动表）：

-- 强制 departments 作为驱动表，employees 作为被驱动表
SELECT e.name, d.name 
FROM departments d 
STRAIGHT_JOIN employees e 
ON e.department_id = d.id 
WHERE d.id IN (1, 3, 5);

注意：STRAIGHT_JOIN 是“双刃剑”——仅当明确知道优化器选择错误时使用，否则可能因手动干预导致更差的性能（如小表过滤后行数反而比大表多）。

四、多表 Join 场景：如何选择驱动表？

当 Join 超过 2 表时，“小表驱动大表”的原则依然适用，但需扩展为“最外层表必须是过滤后行数最少的表”。

1. 三表 Join 核心原则

• 优先选择“过滤后行数最少”的表作为最外层驱动表；
• 确保中间表与被驱动表之间有可用的 Join 索引；
• 避免多层全表扫描（即每个被驱动表都需有索引支持）。

示例（三表 Join 优化）：

表结构：

• T1（用户表 users）：10 万行，过滤后 100 行（WHERE status = 1）；
• T2（订单表 orders）：100 万行，Join 字段 user_id 有索引；
• T3（订单详情表 order_items）：500 万行，Join 字段 order_id 有索引；

优化前（错误驱动表）：

-- 错误：以订单表（T2）为驱动表，过滤后行数 10 万行
SELECT u.name, oi.product_name 
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
WHERE u.status = 1;

优化后（正确驱动表）：

-- 正确：以用户表（T1）为驱动表，过滤后行数 100 行
SELECT u.name, oi.product_name 
FROM users u
JOIN orders o ON u.id = o.user_id  -- o.user_id 有索引
JOIN order_items oi ON o.id = oi.order_id  -- oi.order_id 有索引
WHERE u.status = 1;

执行计划预期：

• 驱动表：users（type=ALL，rows=100）；
• 中间表：orders（type=ref，rows=10）；
• 被驱动表：order_items（type=ref，rows=5）；
• 总成本：100 ×（10 × 5）= 5000 次索引匹配，性能极佳。

2. 多表 Join 避坑点

• 避免“大表作为最外层驱动表”：即使中间表有索引，外层循环次数过多仍会导致性能崩溃；
• 确保每个 Join 都有索引：中间表与被驱动表的 Join 字段必须有索引，否则会触发多层全表扫描；
• 优先过滤再 Join：在驱动表中通过 WHERE 子句过滤数据，减少驱动表行数（核心优化手段）。

五、索引优化：让“小表驱动大表”性能翻倍

“小表驱动大表”的性能上限，取决于被驱动表的索引质量——没有索引时，小表驱动大表的优势会大幅缩水；有高效索引时，性能会呈指数级提升。

1. 被驱动表必须创建“Join 字段索引”

Join 操作中，被驱动表的 Join 字段（如 e.department_id）必须创建索引，否则会触发全表扫描。

推荐索引类型：

• 等值 Join（如 e.department_id = d.id）：创建普通 B+树索引（INDEX idx_department_id (department_id)）；
• 范围 Join（如 e.create_time > o.create_time）：创建联合索引（INDEX idx_create_time_user_id (create_time, user_id)）；
• 多字段 Join（如 e.department_id = d.id AND e.status = 1）：创建联合索引（INDEX idx_department_id_status (department_id, status)），将 Join 字段放在前面。

索引优化示例：

-- 被驱动表（employees）创建索引
CREATE INDEX idx_department_id ON employees (department_id);

-- 多字段 Join 优化（联合索引）
CREATE INDEX idx_department_id_status ON employees (department_id, status);

2. 避免“索引失效”陷阱

即使创建了索引，以下情况会导致索引失效，让“小表驱动大表”失效：

• Join 字段类型不匹配（如 VARCHAR 与 INT 比较）；
• 被驱动表 Join 字段使用函数（如 DATE(e.create_time) = o.create_time）；
• 模糊查询（如 e.name LIKE '%张%'）导致索引失效；
• 联合索引字段顺序错误（如索引 (a, b)，查询条件仅用 b）。

反例（索引失效）：

-- 错误：Join 字段类型不匹配（d.id 是 INT，e.department_id 是 VARCHAR）
SELECT e.name, d.name 
FROM departments d
JOIN employees e ON e.department_id = d.id;  -- 索引失效，全表扫描

-- 错误：被驱动表 Join 字段使用函数
SELECT e.name, d.name 
FROM departments d
JOIN employees e ON DATE(e.create_time) = d.create_time;  -- 索引失效

六、实战案例：从“10 秒”到“10 毫秒”的优化

场景描述：

• 表 A（orders 订单表）：100 万行，字段 id（主键）、user_id、status；
• 表 B（users 用户表）：10 万行，字段 id（主键）、name、status；
• 需求：查询所有“已支付”订单的用户名称（status = 2）；
• 初始查询（大表驱动小表，耗时 10.2 秒）：

SELECT o.id, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 2;

优化步骤：

1. 分析执行计划：

   • 驱动表：orders（type=ALL，rows=1000000），全表扫描；
   • 被驱动表：users（type=ref，rows=1），索引匹配；
   • 问题：驱动表行数过多（100 万行），导致总成本过高。

2. 优化驱动表：过滤后行数减少：
   在驱动表（orders）的 status 字段创建索引，减少过滤后行数：

   CREATE INDEX idx_status_user_id ON orders (status, user_id);

   过滤后，驱动表行数从 100 万行降至 1 万行（假设 1% 订单已支付）。

3. 切换驱动表：小表驱动大表：
   优化后，优化器自动选择 users 作为驱动表（过滤后行数少），orders 作为被驱动表：

   SELECT o.id, u.name 
   FROM users u
   JOIN orders o ON u.id = o.user_id
   WHERE o.status = 2;

4. 最终性能：耗时从 10.2 秒降至 0.01 秒（10 毫秒），性能提升 1020 倍。

六、总结：“小表驱动大表”核心原则与实践要点

核心原则：

1. 驱动表选择：优先选择“过滤后行数最少”的表作为驱动表，而非物理大小最小的表；
2. 索引优先：被驱动表的 Join 字段必须创建索引，否则“小表驱动大表”优势大幅缩水；
3. 先过滤后 Join：通过 WHERE 子句减少驱动表行数（最有效的优化手段）；
4. 多表 Join：最外层表必须是过滤后行数最少的表，确保每层 Join 都有索引支持。

实践要点：

• 用 EXPLAIN 验证驱动表：关注 type 和 rows 列，确保驱动表是过滤后行数最少的表；
• 强制驱动表：优化器选择错误时，用 STRAIGHT_JOIN 手动指定驱动表；
• 索引优化：被驱动表创建 Join 字段索引，避免索引失效；
• 避免过度 Join：多表 Join 尽量控制在 3 表以内，超过 3 表可考虑分拆查询。

“小表驱动大表”不是教条，而是基于 MySQL 执行机制的理性选择——它的核心是“最小化总查询成本”。掌握这一原则，并结合索引优化、执行计划分析，就能在大多数 Join 场景下实现性能翻倍，避开 MySQL 性能陷阱。

除非注明，否则均为李锋镝的博客原创文章，转载必须以链接形式标明本文链接

本文链接：https://www.lifengdi.com/zhong-jian-jian/4558