MySQL / 系统课时

读懂多表 JOIN 的驱动顺序

读懂多表 JOIN 的驱动顺序

多表 JOIN 的关键不是“先写哪张表”,而是优化器最终让哪张表先产出行、后续表又被探测多少次。本课用可运行的三表查询读懂 JOIN order,并把表规模、过滤率和索引组合成一个可估算的成本模型。

学习目标

  • 从 EXPLAIN FORMAT=TREE 识别多表 JOIN 的实际驱动顺序
  • 用过滤后行数和单次探测成本估算不同顺序的放大效应
  • 判断什么时候应通过索引、过滤条件或统计信息改善驱动表选择

驱动顺序决定循环规模

在典型 Nested Loop 执行中,第一张被访问的表是驱动端。它每输出一行,执行器都会继续访问下一层;如果第二层又匹配多行,第三层的探测次数会继续被放大。粗略估算可写成:下一层探测次数 ≈ 上一层输出行数 × 平均匹配行数。

因此,不能只看原始表有多少行。更重要的是经过过滤后还能留下多少行,以及这些行访问下一张表时能否使用高选择性的索引。一个百万行表如果能先通过唯一条件缩到 1 行,反而可能是理想驱动表。

三表 JOIN 驱动顺序与循环放大效应

用一条三表查询观察计划

下面创建客户、订单和订单明细三张表。查询只找一个活跃客户最近状态为 PAID 的订单明细;customers.email 是唯一索引,orders(customer_id, status) 和 order_items.order_id 都可用于后续查找。

sql
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id BIGINT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  active TINYINT NOT NULL,
  UNIQUE KEY uk_customers_email (email)
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  KEY idx_orders_customer_status (customer_id, status)
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  sku VARCHAR(50) NOT NULL,
  KEY idx_items_order (order_id)
);

INSERT INTO customers VALUES
(1, 'a@example.com', 1),
(2, 'b@example.com', 1);

INSERT INTO orders VALUES
(101, 1, 'PAID'),
(102, 1, 'NEW'),
(103, 2, 'PAID');

INSERT INTO order_items VALUES
(1001, 101, 'SKU-A'),
(1002, 101, 'SKU-B'),
(1003, 103, 'SKU-C');

EXPLAIN FORMAT=TREE
SELECT oi.sku
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
JOIN order_items AS oi
  ON oi.order_id = o.id
WHERE c.email = 'a@example.com'
  AND c.active = 1
  AND o.status = 'PAID';

预期输出中应出现三层访问关系:先通过 uk_customers_email 定位 customers,再用 idx_orders_customer_status 查 orders,最后用 idx_items_order 查 order_items。不同 MySQL 8.0 小版本的文字和成本数字可能不同。

text
-> Nested loop inner join
    -> Nested loop inner join
        -> Filter: (c.active = 1)
            -> Index lookup on c using uk_customers_email (email='a@example.com')
        -> Index lookup on o using idx_orders_customer_status (customer_id=c.id, status='PAID')
    -> Index lookup on oi using idx_items_order (order_id=o.id)

把表规模换成过滤后行数

假设三张表原始规模分别是客户 100 万、订单 5000 万、明细 2 亿。若 email 唯一条件把客户先缩到 1 行,那么后续只需探测这个客户的订单,再探测这些订单的明细。此时“客户很大”并不重要,因为真正进入 JOIN 循环的是 1 行。

反过来,如果先从 orders.status='PAID' 开始,而 PAID 占全部订单的 70%,驱动端可能先产生数千万行。即使后面每次客户查找都是主键命中,总探测次数仍会非常大。判断顺序时应同时看估算行数 rows、过滤比例 filtered、索引访问方式和每层实际输出。

发现顺序不理想时怎么改

优先修正能改变成本估算或单次探测成本的因素,而不是一上来强制 JOIN 顺序。常见动作是给高频连接键补合适索引、让高选择性过滤尽早可用,并通过 ANALYZE TABLE 刷新统计信息。

sql
ANALYZE TABLE customers, orders, order_items;

EXPLAIN FORMAT=TREE
SELECT oi.sku
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
JOIN order_items AS oi
  ON oi.order_id = o.id
WHERE c.email = 'a@example.com'
  AND c.active = 1
  AND o.status = 'PAID';

预期结果是三张表的统计信息被更新,随后再次查看计划;若数据分布和索引与示例一致,驱动顺序仍应从高选择性的 customers.email 查找开始。

text
Table        Op       Msg_type  Msg_text
customers    analyze  status    OK
orders       analyze  status    OK
order_items  analyze  status    OK

只有在你确认统计信息可靠、索引设计合理,而优化器仍持续选择明显更差的顺序时,才考虑 STRAIGHT_JOIN 这类强制手段。强制顺序会把当前数据分布假设写死在 SQL 中,数据规模变化后可能从“修复”变成新的性能问题。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

读懂多表 JOIN 的驱动顺序

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通