问题

为什么MySQL是小表驱动大表，为什么能提高查询性能？

答案

核心概念

小表驱动大表：在多表JOIN时，让记录数少的表（小表）作为驱动表（外层循环），记录数多的表（大表）作为被驱动表（内层循环），并确保被驱动表的关联字段有索引。

这一策略的核心在于：减少总的数据访问次数和利用索引加速查询。

嵌套循环连接原理

Simple Nested-Loop Join（简单嵌套循环）

执行逻辑：

// 小表驱动大表
for (rowA in 小表A) {  // 外层循环：1000次
    for (rowB in 大表B) {  // 内层循环：每次遍历10000行
        if (match(rowA, rowB)) {
            output(rowA, rowB);
        }
    }
}
// 总扫描次数 = 1000 + 1000 × 10000 = 10,001,000

// 大表驱动小表
for (rowB in 大表B) {  // 外层循环：10000次
    for (rowA in 小表A) {  // 内层循环：每次遍历1000行
        if (match(rowA, rowB)) {
            output(rowA, rowB);
        }
    }
}
// 总扫描次数 = 10000 + 10000 × 1000 = 10,010,000

结论：在简单嵌套循环中，小表驱动大表可以略微减少扫描次数。

问题：这种方式效率极低，MySQL实际不会使用（除非被驱动表没有索引）。

Index Nested-Loop Join（索引嵌套循环）

关键前提：被驱动表的关联字段有索引

执行逻辑：

// 小表驱动大表（被驱动表有索引）
for (rowA in 小表A) {  // 外层循环：1000次
    rowB = 大表B.indexSearch(rowA.key);  // 索引查找：O(log n)
    if (rowB != null) {
        output(rowA, rowB);
    }
}
// 成本 = 1000（扫描小表） + 1000 × 3（索引树高度） = 4000

// 大表驱动小表（被驱动表有索引）
for (rowB in 大表B) {  // 外层循环：10000次
    rowA = 小表A.indexSearch(rowB.key);  // 索引查找
    if (rowA != null) {
        output(rowB, rowA);
    }
}
// 成本 = 10000（扫描大表） + 10000 × 3（索引树高度） = 40000

性能差距：10倍（4000 vs 40000）

结论：小表驱动大表可以显著减少索引查找次数。

为什么能提高查询性能

1. 减少外层循环次数

原理：驱动表决定了外层循环的执行次数，次数越少越好。

示例：

-- users: 100万行
-- orders: 10万行
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

小表驱动（orders → users）：

外层循环：10万次（遍历orders）
内层查找：10万次索引查找（在users中通过主键查找）
总成本：200000

大表驱动（users → orders）：

外层循环：100万次（遍历users）
内层查找：100万次索引查找（在orders中通过user_id查找）
总成本：2000000

性能提升：10倍

2. 更高效地利用索引

被驱动表的索引至关重要：

-- 假设orders.user_id有索引
SELECT * FROM orders o
INNER JOIN users u ON u.id = o.user_id;

索引查找复杂度：O(log n) + 回表

• B+树查找：树高（通常3-4层）
• 回表：1次IO（如果是聚簇索引则不需要）

无索引的灾难：

成本 = 10万（扫描orders） + 10万 × 100万（全表扫描users）
     = 1000亿次操作（完全不可接受）

3. 减少内存占用（Join Buffer）

Block Nested-Loop Join：当被驱动表无索引时，MySQL使用Join Buffer优化。

-- orders无索引的情况
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Join Buffer工作原理：

1. 从驱动表读取一批数据到Join Buffer（默认256KB）
2. 扫描被驱动表，与Buffer中的数据匹配
3. 重复直到处理完驱动表所有数据

小表驱动的优势：

• 如果驱动表（小表）能完全放入Join Buffer，只需扫描1次被驱动表
• 如果驱动表（大表）需要分批次，被驱动表需要扫描N次

示例：

小表驱动：
- 小表10万行,可以放入Join Buffer
- 大表扫描1次
- 成本：10万 + 100万 = 110万

大表驱动：
- 大表100万行,需要分100批放入Join Buffer
- 小表扫描100次
- 成本：100万 + 100 × 10万 = 1100万

性能差距：10倍

4. 更好的缓存局部性

原理：小表更容易完全加载到内存（InnoDB Buffer Pool），后续访问都是内存操作。

-- 小表orders可能完全在内存中
SELECT * FROM orders o
INNER JOIN users u ON u.id = o.user_id;

内存命中的优势：

• 内存访问：~100ns
• 磁盘IO：~10ms（相差10万倍）

实际案例分析

案例1：等值JOIN

场景：

-- departments: 10个部门
-- employees: 100万员工
SELECT d.name, e.name
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id;

优化器选择：departments作为驱动表

执行过程：

1. 全表扫描departments（10行，完全在内存）
2. 对每个dept.id，在employees.dept_id索引中查找匹配行
3. 总IO：10（扫描部门） + 10（索引查找） ≈ 20次

如果选错（employees驱动）：

1. 全表扫描employees（100万行，大量磁盘IO）
2. 对每个emp.dept_id，在departments.id主键索引中查找
3. 总IO：100万 + 100万 ≈ 200万次

性能差距：10万倍

案例2：带WHERE条件的JOIN

场景：

-- orders: 100万行
-- users: 10万行
SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';  -- 过滤后只有1000行

优化器选择：orders作为驱动表（虽然原始行数多，但过滤后变成小表）

关键：不是看表的原始大小，而是看WHERE过滤后的结果集大小。

执行成本：

成本 = 扫描orders索引(假设status有索引) + 回表 + 索引查找users
     = 1000 + 1000 + 1000
     = 3000

如果选错（users驱动，10万行）：

成本 = 扫描users + 索引查找orders + 过滤status
     ≈ 100000 + 100000 + 100000
     = 300000

性能差距：100倍

特殊场景：什么时候不适用？

1. 被驱动表没有索引

如果被驱动表的关联字段没有索引，小表驱动大表的优势大幅降低：

-- orders.user_id没有索引
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

成本计算（小表驱动）：

成本 = 10万（扫描users） + 10万 × 100万（全表扫描orders）
     = 1000亿次操作

解决方案：

1. 优先：创建索引 CREATE INDEX idx_user_id ON orders(user_id);
2. 如果无法创建索引，考虑应用层处理（分批查询、缓存等）

2. 数据分布严重倾斜

-- orders中90%的订单属于1个用户
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

此时即使users是小表，但大部分JOIN操作集中在1个用户，可能导致：

• 索引热点
• 锁竞争（高并发场景）

优化方案：

• 针对热点数据单独优化（缓存、分片）
• 考虑分批处理

优化建议

1. 确保被驱动表有索引

-- 检查索引
SHOW INDEX FROM orders;

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 验证索引使用
EXPLAIN SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 查看key列，确认使用了idx_user_id

2. 利用WHERE条件过滤驱动表

-- 优化前
SELECT * FROM large_table t1
INNER JOIN small_table t2 ON t1.id = t2.t1_id;

-- 优化后：先过滤large_table
SELECT * FROM large_table t1
INNER JOIN small_table t2 ON t1.id = t2.t1_id
WHERE t1.status = 'active'  -- 大幅减少驱动表行数
  AND t1.create_time > '2024-01-01';

3. 使用覆盖索引避免回表

-- 创建覆盖索引
CREATE INDEX idx_user_id_status_amount
ON orders(user_id, status, amount);

-- 查询只需要索引中的字段
SELECT o.user_id, o.status, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 避免回表，性能大幅提升

4. 调整Join Buffer大小（无索引时）

-- 查看当前Join Buffer大小
SHOW VARIABLES LIKE 'join_buffer_size';

-- 调整大小（单位：字节，默认256KB）
SET SESSION join_buffer_size = 2097152;  -- 2MB

-- 或在配置文件中永久设置
[mysqld]
join_buffer_size = 2M

验证优化效果

-- 查看执行计划
EXPLAIN FORMAT=TREE
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.s_id\G

-- 查看实际执行成本
EXPLAIN FORMAT=JSON
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.s_id\G

-- 查看实际执行时间
SET profiling = 1;
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.s_id;
SHOW PROFILES;

面试总结

简洁版回答：

小表驱动大表的原理：

• 驱动表决定外层循环次数，小表可减少循环次数
• 被驱动表通过索引快速查找，避免全表扫描
• 减少总的数据访问量和IO次数

性能提升原因：

1. 减少外层循环：1000次 vs 100万次
2. 高效索引查找：O(log n) vs O(n)
3. Join Buffer优化：小表更容易完整加载
4. 缓存友好：小表更容易驻留内存

关键前提：

• 被驱动表的关联字段必须有索引
• “小表”是指WHERE过滤后的结果集，不是原始表大小

性能差距：在合适场景下，可达10倍甚至100倍以上。