Mysql 小表驱动大表原则

关注公众号【九逸编码】了解更多优质内容。

核心定义：在多表连接（JOIN）操作中，应优先选择数据量更小或结果集更小的表作为驱动表（Outer Table），去探测或访问数据量更大的表（被驱动表），以最小化整体查询的计算复杂度与资源消耗。

一、驱动表概念的本质理解

1.1 什么是驱动表？

驱动表（Driving Table）是表连接操作中首先被访问的表，它决定了外层循环的次数。在嵌套循环连接（Nested Loop Join）中，驱动表的每一行都会去被驱动表中查找匹配的行。

1.2 数据库如何执行表连接？

以MySQL的Nested Loop Join为例：

-- 伪代码展示连接过程
for each row in driver_table {
    for each row in driven_table {
        if (rows satisfy join condition) {
            send to client
        }
    }
}

1.3 为什么驱动表选择如此重要？

• 性能影响：驱动表行数决定外层循环次数
• I/O成本：驱动表通常需要全表扫描或全索引扫描
• 内存占用：驱动表数据可能被缓存

二、驱动表选择的本质

请注意：驱动表本质不是物理大小，而是“结果集大小”

⚠️ 常见误区：认为“小表”仅指物理行数少。

✅ 正确认知：驱动表应是经过 WHERE 条件过滤后结果集更小的表。

示例：

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'       -- 过滤后只剩 1000 人
  AND o.create_time > NOW() - INTERVAL 1 DAY; -- 订单每天百万级

• 虽然 users 表物理上可能小于 orders，但若 WHERE 后 orders 的结果集更小（比如只查昨天的订单），则可能 orders 更适合作为驱动表。
• 但通常 users.status='active' 过滤性强，结果集更小 → 更适合做驱动表。

🔍 结论：

驱动表的选择应基于 “过滤后的基数（Cardinality）”，而非原始表大小。
优化器通过统计信息（如 ANALYZE TABLE）估算各表的过滤后行数，从而决策驱动顺序。

二、小表驱动大表的底层原理

2.1 磁盘I/O优化

• 小表特性：数据量小，可以更快加载到内存
• 大表策略：只需按需读取索引和数据页

2.2 缓存利用率

数据库缓冲池（Buffer Pool）的工作机制：

1. 小表数据可以完全缓存在内存中
2. 大表通过索引访问只需缓存热点数据页
3. 减少缓冲池的置换频率

2.3 索引利用效率

• 被驱动表必须建立索引（连接字段）
• 大表的索引层级通常更深，需要高效利用

三、不同连接类型的驱动表选择

3.1 嵌套循环连接（Nested Loop Join）

• 最佳场景：小表驱动 + 被驱动表有索引
• 性能公式：Cost = 驱动表扫描成本 + 驱动表行数 × 单次被驱动表查找成本

3.2 哈希连接（Hash Join）

• 驱动表用于构建哈希表
• 小表构建哈希表内存消耗更低
• 大表作为探测端效率更高

3.3 排序合并连接（Merge Join）

• 双表都需要排序
• 驱动表选择影响较小
• 但小表排序成本更低

四、实战案例分析

案例1：

-- 用户表(10万行) 订单表(1000万行)
-- 反例：大表驱动
SELECT * FROM orders JOIN users ON orders.user_id = users.id 
WHERE users.vip_level > 3; -- 执行时间：2.8s

-- 正例：小表驱动
SELECT * FROM users JOIN orders ON users.id = orders.user_id
WHERE users.vip_level > 3; -- 执行时间：0.4s

优化效果：性能提升7倍

案例2：

-- 用户表(100万) 好友关系表(1亿)
-- 通过子查询实现小表驱动
SELECT u.* FROM users u
WHERE EXISTS (
    SELECT 1 FROM friendships f 
    WHERE f.user_id = u.id AND f.create_time > '2023-01-01'
);

五、如何准确识别驱动表

5.1 使用EXPLAIN分析

使用 EXPLAIN 分析执行计划是必备技能。

EXPLAIN 
SELECT * FROM small_table s JOIN large_table l ON s.id = l.s_id;

关注字段：

• id：相同则为同级，数字小的先执行（驱动表）。
• table：顺序通常表示执行顺序（驱动表在前）。
• type：驱动表可用 ALL（全表），被驱动表应为 ref / eq_ref（索引查找）。
• rows：预估扫描行数，越小越好。
• Extra：出现 Using join buffer 表示 NLJ；Using index 表示覆盖索引。

✅ 理想情况：

• 驱动表 rows 小
• 被驱动表 type 为 ref 或 eq_ref
• 无 Using filesort 或 Using temporary

5.2 MySQL执行计划解读

EXPLAIN SELECT * FROM small JOIN large ON small.id = large.id;

• 第一行出现的表通常是驱动表
• "Using join buffer"表示使用了连接缓冲

5.3 强制指定驱动表的方法

-- MySQL STRAIGHT_JOIN语法
SELECT * FROM small STRAIGHT_JOIN large ON small.id = large.id;

-- Oracle LEADING提示
SELECT /*+ LEADING(small) */ * FROM small JOIN large ON small.id = large.id;

六、特殊场景的应对策略

6.1 大表过滤后变小的情况

-- 虽然orders是大表，但过滤后数据量很小
SELECT * FROM 
    (SELECT * FROM orders WHERE create_date = CURDATE()) AS small_orders
JOIN 
    products ON small_orders.product_id = products.id;

6.2 多表连接的驱动顺序

复杂查询的驱动顺序策略：

1. 优先选择过滤后行数最少的表
2. 其次选择连接字段有唯一索引的表
3. 最后考虑表的大小因素

6.3 分布式数据库的特殊性

• 数据分片影响驱动表选择
• 需要考虑网络传输成本
• 可能优先选择本地分片作为驱动表

七、高级优化技巧

7.1 索引覆盖优化

索引的关键作用：即使小表驱动大表，若被驱动表无索引，仍可能导致全表扫描，性能崩溃

-- 使驱动表查询使用覆盖索引
SELECT * FROM 
    (SELECT id FROM small_table WHERE status = 1) AS driver
JOIN 
    large_table ON driver.id = large_table.id;

-- 推荐：小表 users 驱动大表 orders
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.department = 'sales';

-- 必须确保：orders.user_id 上有索引！
CREATE INDEX idx_orders_user_id ON orders(user_id);

📌 索引原则：

被驱动表的连接字段必须建立高效索引，否则“小表驱动”优势荡然无存。

7.2 物化视图技术

-- 创建小表的物化视图
CREATE MATERIALIZED VIEW small_view AS 
SELECT * FROM original_table WHERE condition;

-- 使用物化视图作为驱动表
SELECT * FROM small_view JOIN large_table ON small_view.id = large_table.id;

7.3 批处理优化

-- 分批次处理大表连接
DECLARE @batch_size INT = 1000;
DECLARE @i INT = 0;

WHILE EXISTS (SELECT 1 FROM large_table WHERE id > @i)
BEGIN
    SELECT * FROM small_table s
    JOIN large_table l ON s.id = l.id
    WHERE l.id > @i AND l.id <= @i + @batch_size;
    
    SET @i = @i + @batch_size;
END

八、实际优化策略与技巧

九、总结与最佳实践

1. 基本原则：始终优先考虑小表驱动大表
2. 验证方法：通过EXPLAIN确认实际驱动表
3. 必要准备：确保被驱动表连接字段有索引
4. 例外处理：当大表有高选择性过滤时灵活调整
5. 监控调整：定期检查执行计划，必要时使用HINT

总结：原则的本质与演进

更多内容关注公众号【九逸编码】，定期会分享技术文章和常用工具使用的案例，目前更多的内容正在整理，后续会持续发出来的。

往期回顾
rsync + sersync 实现文件实时同步的完整配置详解【一】
rsyncd.conf、confxml.xml 完整 配置文件详解【二】