如何分辨驱动表和被驱动表
如何分辨驱动表和被驱动表
1. 通过EXPLAIN执行计划识别
1.1 查看EXPLAIN输出顺序
-- 示例查询
EXPLAIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';
-- 可能的EXPLAIN输出：
-- +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
-- | 1 | SIMPLE | u | NULL | ref | idx_status | idx_status | 52 | const | 500 | 100.00 | Using where |
-- | 1 | SIMPLE | o | NULL | ref | idx_user_id | idx_user_id | 4 | test.u.id | 10 | 100.00 | NULL |
-- +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
识别要点：
- 第一行：
table: u→ users表是驱动表（先被访问） - 第二行：
table: o→ orders表是被驱动表（后被访问）
1.2 复杂JOIN的识别
-- 三表JOIN示例
EXPLAIN
SELECT u.name, o.order_date, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
WHERE u.status = 'premium';
-- EXPLAIN输出顺序：
-- 1. users (驱动表)
-- 2. orders (被users驱动)
-- 3. products (被orders驱动)
2. 通过执行计划的访问方式识别
2.1 驱动表的特征
-- 驱动表通常：
-- 1. 是第一个被访问的表
-- 2. 使用全表扫描(ALL)、索引扫描(index)或索引查找(ref)
-- 3. 不依赖其他表的值
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- 如果users表在第一行，且type不是ref（不依赖其他表），则是驱动表
2.2 被驱动表的特征
-- 被驱动表通常：
-- 1. 在驱动表之后被访问
-- 2. 使用ref、eq_ref等需要关联值的访问类型
-- 3. ref列显示依赖于前一个表的列
-- 示例：
-- | table | type | ref |
-- | o | ref | test.u.id| ← 依赖于u表的id值，是被驱动表
3. 通过ref列识别依赖关系
3.1 ref列详解
EXPLAIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 关键观察ref列：
-- users表：ref列可能是const（常量）或NULL（不依赖其他表）→ 驱动表
-- orders表：ref列是test.u.id（依赖u表的id）→ 被驱动表
3.2 不同JOIN类型的ref表现
-- LEFT JOIN示例
EXPLAIN
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 输出：
-- users表：ref=NULL或const → 驱动表
-- orders表：ref=test.u.id → 被驱动表
-- RIGHT JOIN示例
EXPLAIN
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 输出：
-- orders表：ref=NULL或const → 驱动表
-- users表：ref=test.o.user_id → 被驱动表
4. 实际案例演示
4.1 简单两表JOIN
-- 创建测试表
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
INDEX idx_dept_id (dept_id)
);
-- 插入测试数据
INSERT INTO departments VALUES (1, 'IT'), (2, 'HR'), (3, 'Finance');
INSERT INTO employees VALUES
(1, 'Alice', 1), (2, 'Bob', 1), (3, 'Charlie', 2), (4, 'David', 3);
-- 分析查询
EXPLAIN
SELECT d.name, e.name
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id;
-- EXPLAIN输出：
-- +----+-------------+-------+------------+------+---------------+-----------+---------+-----------+------+----------+-------+
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- +----+-------------+-------+------------+------+---------------+-----------+---------+-----------+------+----------+-------+
-- | 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
-- | 1 | SIMPLE | e | NULL | ref | idx_dept_id | idx_dept_id | 5 | test.d.id | 1 | 100.00 | NULL |
-- +----+-------------+-------+------------+------+---------------+-----------+---------+-----------+------+----------+-------+
-- 识别结果：
-- departments表：第一行，ref=NULL → 驱动表
-- employees表：第二行，ref=test.d.id → 被驱动表
4.2 WHERE条件影响驱动表选择
-- 添加WHERE条件
EXPLAIN
SELECT d.name, e.name
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id
WHERE d.name = 'IT';
-- EXPLAIN输出：
-- +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
-- | 1 | SIMPLE | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
-- | 1 | SIMPLE | e | NULL | ref | idx_dept_id | idx_dept_id | 5 | const | 2 | 100.00 | Using where |
-- +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
-- 识别结果：
-- departments表：type=const → 驱动表
-- employees表：ref=const → 被驱动表
5. 复杂查询中的识别
5.1 子查询中的JOIN
EXPLAIN
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u
WHERE u.status = 'active';
-- 这里orders表在子查询中，相对于子查询来说，u.id是常量
-- 所以orders表在子查询中是"被驱动表"
5.2 UNION中的JOIN
EXPLAIN
SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
UNION
SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.registration_date > '2023-01-01';
-- 会显示两个独立的查询计划，每个都需要识别驱动表和被驱动表
6. 特殊情况识别
6.1 使用STRAIGHT_JOIN强制顺序
-- 强制驱动表顺序
EXPLAIN
SELECT STRAIGHT_JOIN u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 这时users表一定是驱动表，orders表一定是被驱动表
-- 不管优化器如何计算成本
6.2 使用JOIN顺序提示
-- MySQL 8.0+提示
EXPLAIN
SELECT /*+ JOIN_ORDER(orders, users) */ u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;
-- orders表会作为驱动表，users表作为被驱动表
7. 快速识别技巧
7.1 一句话法则
"EXPLAIN输出中，第一行的表是驱动表，后续行的表是被驱动表"
7.2 关键列检查法
- 看table列：确定表的访问顺序
- 看ref列：
- 显示NULL或const → 驱动表
- 显示其他表的列 → 被驱动表
- 看type列：
- ALL、index、range等 → 通常是驱动表
- ref、eq_ref等 → 通常是被驱动表
7.3 实用查询模板
-- 通用分析模板
EXPLAIN FORMAT=TRADITIONAL
[your query here];
-- 或者使用详细格式
EXPLAIN FORMAT=JSON
[your query here];
8. 验证识别结果
8.1 通过实际执行验证
-- 开启查询日志或性能监控
SET profiling = 1;
[your query];
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
8.2 使用optimizer_trace（MySQL）
-- 查看优化器决策过程
SET optimizer_trace="enabled=on";
[your query];
SELECT * FROM information_schema.optimizer_trace;
通过以上方法，可以准确识别任何JOIN查询中的驱动表和被驱动表，这对于SQL性能优化和索引设计具有重要意义。
浙公网安备 33010602011771号