JOIN操作中驱动表和被驱动表的选择逻辑

JOIN操作中驱动表和被驱动表的选择逻辑

1. 基本概念

驱动表（Outer Table/Driving Table）

• 在Nested Loop Join中首先被扫描的表
• 作为外层循环的表
• 每一行都会触发对被驱动表的查找

被驱动表（Inner Table/Driven Table）

• 在Nested Loop Join中作为内层循环的表
• 对于驱动表的每一行都要被扫描查找
• 索引主要优化这个表的访问效率

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

2.1 成本基础选择（Cost-Based Selection）

数据库优化器使用成本模型来决定哪个表作为驱动表：

-- 伪代码：优化器成本计算
total_cost = outer_table_scan_cost + (outer_table_rows * inner_table_access_cost)

-- 选择总成本最小的方案作为执行计划

2.2 选择因子分析

表大小（Cardinality）

-- 假设有两个表：
-- users表：10,000行
-- orders表：1,000,000行

-- 方案1：users作为驱动表
-- 成本 = 10,000 + (10,000 * log₂(1,000,000)) ≈ 10,000 + 10,000 * 20 = 210,000

-- 方案2：orders作为驱动表
-- 成本 = 1,000,000 + (1,000,000 * log₂(10,000)) ≈ 1,000,000 + 1,000,000 * 13.3 = 14,300,000

-- 优化器会选择方案1：users作为驱动表

过滤条件（Filter Conditions）

-- 查询示例
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'premium'  -- 假设只有100个premium用户
AND o.order_date > '2023-01-01'; -- 假设过滤后orders表剩500,000行

-- 优化器会考虑WHERE条件的影响：
-- users表过滤后：100行
-- orders表过滤后：500,000行

-- 方案1：过滤后的users作为驱动表
-- 成本 = 100 + (100 * log₂(500,000)) ≈ 100 + 100 * 18.9 = 1,990

-- 方案2：过滤后的orders作为驱动表
-- 成本 = 500,000 + (500,000 * log₂(100)) ≈ 500,000 + 500,000 * 6.6 = 3,800,000

-- 优化器会选择users作为驱动表

3. 详细的成本计算模型

3.1 扫描成本计算

全表扫描成本

-- 简化的成本计算公式
table_scan_cost = number_of_blocks * block_io_cost

-- 实际考虑因素：
-- 1. 数据页数量
-- 2. 缓冲池命中率
-- 3. 磁盘I/O成本
-- 4. CPU处理成本

索引扫描成本

-- 索引访问成本
index_access_cost = index_height + 1 data_page_access

-- 例如：B+树高度为3的索引
-- 成本 = 3 (索引页访问) + 1 (数据页访问) = 4次I/O

3.2 连接成本计算

-- Nested Loop Join总成本
total_cost = outer_scan_cost + (outer_rows * inner_access_cost_per_row)

-- 具体示例：
-- users表：1000行，10个数据页
-- orders表：100000行，1000个数据页，user_id上有索引（高度3）

-- users驱动，orders被驱动：
-- 成本 = 10 (users全表扫描) + (1000 * 4) (orders索引访问) = 4,010

-- orders驱动，users被驱动：
-- 成本 = 1000 (orders全表扫描) + (100000 * 4) (users索引访问) = 401,000

-- 优化器选择第一种方案

4. 优化器决策过程

4.1 统计信息的作用

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'users';
SHOW INDEX FROM users;

-- 关键统计信息：
-- 1. 表行数（Rows）
-- 2. 平均行长度
-- 3. 索引基数（Cardinality）
-- 4. 数据页数量

4.2 选择性估算

-- WHERE条件选择性计算
-- 选择性 = 满足条件的行数 / 总行数

-- 示例：
-- users表总行数：10,000
-- status = 'active' 的行数：2,000
-- 选择性 = 2,000 / 10,000 = 0.2

-- 优化器估算过滤后的行数 = 10,000 * 0.2 = 2,000行

5. 实际案例分析

5.1 简单JOIN选择

-- 表结构
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
); -- 10行

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    INDEX idx_dept_id (dept_id)
); -- 10,000行

-- 查询
SELECT d.name, e.name
FROM departments d
JOIN employees e ON d.id = e.dept_id;

-- 优化器分析：
-- 1. departments表小（10行）→ 适合作为驱动表
-- 2. employees表大（10,000行）但有索引 → 适合作为被驱动表
-- 3. 总成本 = 1 + (10 * 4) = 41

5.2 复杂条件下的选择

-- 查询
SELECT u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.registration_date > '2023-01-01'  -- 假设过滤后剩500行
AND o.status = 'completed';               -- 假设过滤后剩30,000行

-- 表统计：
-- users表：100,000行
-- orders表：1,000,000行，user_id上有索引

-- 方案1：过滤后users作为驱动表（500行）
-- 成本 = 扫描500行users + (500 * 4 orders访问) = 2,500

-- 方案2：过滤后orders作为驱动表（30,000行）
-- 成本 = 扫描30,000行orders + (30,000 * 4 users访问) = 150,000

-- 优化器选择方案1

6. 影响选择的关键因素

6.1 索引的存在和质量

-- 有高质量索引的情况
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 这个复合索引可以让被驱动表的访问更加高效
-- 优化器更倾向于选择没有这个索引的表作为驱动表

6.2 数据分布和选择性

-- 数据分布不均匀的情况
-- users表中，大部分用户是普通用户，少数是VIP用户

SELECT u.username, o.order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.user_type = 'VIP'; -- 假设只有100个VIP用户

-- 即使orders表很大，但由于users表过滤后很小
-- 优化器会选择users作为驱动表

6.3 排序和分组需求

-- 需要排序的查询
SELECT u.username, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY order_count DESC;

-- 如果users表有基于username的索引
-- 优化器可能选择有利于排序的表作为驱动表

7. 人工优化策略

7.1 使用STRAIGHT_JOIN强制顺序

-- 强制users作为驱动表
SELECT STRAIGHT_JOIN u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 查看执行计划确认
EXPLAIN SELECT STRAIGHT_JOIN u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

7.2 优化器提示

-- MySQL 8.0+ 的优化器提示
SELECT /*+ USE_INDEX(orders idx_user_id) */ 
       u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 指定连接顺序
SELECT /*+ JOIN_ORDER(users, orders) */ 
       u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

8. 监控和调试

8.1 查看执行计划

-- 分析JOIN顺序
EXPLAIN FORMAT=TRADITIONAL
SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 关注：
-- 1. tables列的顺序（实际JOIN顺序）
-- 2. key列（使用的索引）
-- 3. rows列（估算扫描行数）
-- 4. Extra列（特殊操作）

8.2 性能分析

-- 使用性能模式监控
SELECT 
    digest_text,
    count_star,
    avg_timer_wait/1000000000 as avg_time_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%JOIN%'
ORDER BY avg_timer_wait DESC;

总结

优化器选择驱动表的核心逻辑是最小化总体执行成本：

1. 估算各表经过WHERE过滤后的行数
2. 计算不同驱动表选择下的总成本
3. 选择总成本最小的方案
4. 考虑索引、数据分布、排序需求等因素

理解这个逻辑有助于：

• 编写更高效的SQL查询
• 设计合适的索引策略
• 在必要时通过提示干预优化器选择