MySQL / 系统课时

为 JOIN 条件设计连接索引

为 JOIN 条件设计连接索引

JOIN 慢往往不是“连接本身很贵”,而是被驱动表在每一轮连接中要扫描太多记录。本课用一个固定访问顺序的实验,把连接键、过滤条件和复合索引对应起来,并用 EXPLAIN 验证被驱动表是否能够进行小范围索引查找。

学习目标

  • 根据连接条件和实际查询模式判断复合索引候选列
  • 理解连接键与等值过滤条件同时命中复合索引时的访问方式
  • 用 EXPLAIN 检查 type、key、ref、key_len 与估算扫描行数是否符合预期

先从连接访问方式出发

以常见的 Nested Loop Join 心智模型理解最直接:MySQL 先从一侧取得一批行,再拿每一行的连接键去另一侧查匹配记录。真正需要优化的是“每次去被驱动表时,要读多少行”,而不是机械规定某一列必须作为索引首列。

如果驱动表是 customers,连接条件是 o.customer_id = c.id,同时还有 o.status = 'PAID',那么探测 orders 时,c.id 和常量 PAID 都已经可用。对于 (customer_id, status) 与 (status, customer_id),当前查询同时对两列做等值匹配时,两种索引都可以利用完整组合键完成查找。因此,不能仅根据“驱动表先提供客户 ID”就断言前者一定更优;索引顺序还应结合其他查询的左前缀使用方式、列的选择性和索引复用范围判断。

JOIN 被驱动表索引访问路径

把连接键和过滤条件放进同一索引

下面创建一个最小实验。为了稳定观察 customers 驱动、orders 被驱动的路径,查询使用 STRAIGHT_JOIN 固定连接顺序,并用 FORCE INDEX 指定本课正在验证的索引;这些限定主要用于教学观察,生产查询不应仅因实验结果就长期保留。

sql
CREATE DATABASE IF NOT EXISTS join_index_lab;
USE join_index_lab;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    region VARCHAR(16) NOT NULL,
    KEY idx_customers_region_id (region, id)
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(16) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    KEY idx_orders_customer_status (customer_id, status)
);

INSERT INTO customers (id, region) VALUES
(1, 'EAST'), (2, 'EAST'), (3, 'WEST');

INSERT INTO orders (id, customer_id, status, amount) VALUES
(101, 1, 'PAID', 99.00),
(102, 1, 'NEW', 35.00),
(103, 1, 'PAID', 42.00),
(104, 2, 'CANCELLED', 18.00),
(105, 2, 'PAID', 66.00),
(106, 3, 'PAID', 88.00);

EXPLAIN
SELECT STRAIGHT_JOIN c.id, o.id, o.amount
FROM customers AS c
JOIN orders AS o FORCE INDEX (idx_orders_customer_status)
  ON o.customer_id = c.id
WHERE c.region = 'EAST'
  AND o.status = 'PAID';

预期结果:

text
重点观察 EXPLAIN 中 orders 对应的一行:
- key 命中 idx_orders_customer_status
- type 通常为 ref 等索引等值访问类型
- ref 会反映连接列 c.id 与常量条件对索引列的使用
- key_len 通常能够反映两个索引列都参与访问
- rows 是优化器估算值,会随数据分布和统计信息变化

这里最重要的不是某个固定 rows 数字,而是 orders 不再对每个客户做无界的大范围扫描。连接值与状态条件都能参与索引查找时,每轮探测需要读取的候选记录会更少。

索引列顺序要服从复用方式

当前查询同时对 customer_id 和 status 使用等值条件,因此 (customer_id, status) 与 (status, customer_id) 都可能完整利用这两个条件。真正决定顺序的,应是这个索引还要服务哪些查询。

索引 适合的复用模式 主要考虑
(customer_id, status) 还存在只按 customer_id 查找的查询 customer_id 可作为左前缀继续使用
(status, customer_id) 还存在先按 status 筛选的查询 status 可作为左前缀继续使用
(customer_id) 只需要按连接键查找 无法同时利用 status 缩小索引访问范围

因此,联合索引设计不是“被驱动表连接键永远第一”,而是比较完整查询条件、左前缀复用、数据选择性、写入维护成本后做取舍。当前查询若只有这两个等值条件,单凭连接方向不足以证明某一种列顺序更优。

如果被驱动表还有稳定的时间范围过滤,例如 created_at >= ...,可以进一步评估 (customer_id, status, created_at) 或其他顺序。但进入范围条件后,后续列通常不能继续以同样方式缩小索引区间,最终仍应以真实执行计划和扫描量验证,而不是把所有条件机械塞进一个索引。

用真实计划复核优化是否成立

实验中的 STRAIGHT_JOIN 和 FORCE INDEX 只是把观察对象固定下来。回到真实 SQL 时应先移除它们,再检查优化器最终选择的连接顺序和索引。如果优化器改成 orders 驱动,那么最合适的索引设计也可能随之变化。

复核时重点看四件事:被驱动表的 key 是否命中预期索引,type 是否从 ALL 等大范围访问收敛为 ref、eq_ref 或合理的 range,rows 是否显著下降,以及 Extra 是否暴露额外排序、临时表或残余过滤。只有真实查询在目标数据分布下减少了实际读取量,这个 JOIN 索引设计才算成立。

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

目录与阅读设置

阅读设置

17

记录一个想法

为 JOIN 条件设计连接索引

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

登录哪都通