开发者日记

外观

从 18s 到 120ms：JOIN 调优实战 ​

问题背景：多表关联为什么突然变慢 ​

业务从单表查询演进到订单、用户、商品、履约等多表关联后，SQL 性能往往不是线性下降，而是被执行算法放大。MySQL 在处理 JOIN 时，常见路径包括 Index Nested-Loop Join（索引嵌套循环，简称 NLJ）、Block Nested-Loop Join（块嵌套循环，简称 BNL），以及 MySQL 8.0.18 之后引入的 Hash Join。很多慢查询表面上看是“关联表太多”，本质却是：被驱动表没有可用索引、驱动表选错、Join Buffer 太小，或优化器在统计信息过期时选择了错误的访问路径。

下面用一个可复现的电商订单场景，演示如何识别 JOIN 算法、如何改写 SQL，以及如何用索引把一次 18 秒的多表关联优化到约 120ms。

三种 JOIN 算法的选型对比 ​

NLJ 适合被驱动表的关联字段有高选择性索引的场景。执行过程类似两层循环：外层读取驱动表一行，内层通过索引到被驱动表做一次精准查找。如果驱动表有 N 行，被驱动表索引访问成本接近常数，整体成本通常是 N 次索引树查找。

BNL 常见于被驱动表关联字段没有索引的情况。MySQL 会把驱动表的一批记录放入 Join Buffer，再扫描被驱动表，并在内存中比较。它减少了内层表的重复扫描次数，但仍然可能产生大范围扫描，尤其在大表之间关联时容易出现扫描行数暴涨。

Hash Join 则更适合等值连接且无法有效利用索引的场景。它会为较小输入构建哈希表，再探测另一张表。MySQL 8.0.18 起，优化器在满足条件时可以选择 Hash Join；8.0.20 对 BNL 做了进一步替换，传统执行计划中的 Block Nested Loop 字样在部分场景会由 Hash Join 承担。

NLJ驱动行 + 索引探测BNL批量缓存 + 扫描比较Hash Join构建哈希表 + 探测适合：被驱动表关联字段有索引风险：大表全扫描Buffer 命中率差适合：等值连接索引缺失或代价更高

初始化测试表与数据 ​

以下 SQL 可直接在 MySQL 8.0 中运行。为了突出 JOIN 问题，订单明细表先不创建外键关联索引，这也是很多线上表在历史迭代中常见的状态。

sql
-- 用户表：模拟 10 万用户
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  user_name VARCHAR(64) NOT NULL,
  city VARCHAR(32) NOT NULL,
  create_time DATETIME NOT NULL,
  KEY idx_city_create (city, create_time)
) ENGINE=InnoDB;\n
-- 订单主表：模拟 50 万订单
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  order_status TINYINT NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  create_time DATETIME NOT NULL,
  KEY idx_user_id (user_id),
  KEY idx_status_create (order_status, create_time)
) ENGINE=InnoDB;\n
-- 订单明细表：初始缺少 order_id 索引，故意制造 BNL/Hash Join
CREATE TABLE order_items (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  sku_id BIGINT NOT NULL,
  sku_name VARCHAR(128) NOT NULL,
  quantity INT NOT NULL,
  item_amount DECIMAL(12,2) NOT NULL
) ENGINE=InnoDB;\n
-- 用递归 CTE 造数，MySQL 8.0 可执行
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100000
)
INSERT INTO users(id, user_name, city, create_time)
SELECT
  n,
  CONCAT('user_', n),
  ELT(1 + n % 5, '北京', '上海', '广州', '深圳', '杭州'),
  DATE_SUB(NOW(), INTERVAL n MINUTE)
FROM seq;\n
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 500000
)
INSERT INTO orders(id, user_id, order_no, order_status, total_amount, create_time)
SELECT
  n,
  1 + n % 100000,
  CONCAT('NO', LPAD(n, 10, '0')),
  IF(n % 7 = 0, 0, 1),
  ROUND(50 + RAND() * 500, 2),
  DATE_SUB(NOW(), INTERVAL n MINUTE)
FROM seq;\n
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000000
)
INSERT INTO order_items(id, order_id, sku_id, sku_name, quantity, item_amount)
SELECT
  n,
  1 + n % 500000,
  1 + n % 20000,
  CONCAT('SKU_', 1 + n % 20000),
  1 + n % 3,
  ROUND(20 + RAND() * 200, 2)
FROM seq;\n```

## 慢查询复现：过滤条件强但关联索引缺失

需求是查询上海用户最近 30 天内已完成订单及其商品明细。第一版 SQL 如下：

```sql
SELECT
  o.id AS order_id,
  o.order_no,
  u.user_name,
  oi.sku_name,
  oi.quantity,
  oi.item_amount
FROM users AS u
JOIN orders AS o
  ON o.user_id = u.id
JOIN order_items AS oi
  ON oi.order_id = o.id
WHERE u.city = '上海'
  AND o.order_status = 1
  AND o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY o.create_time DESC
LIMIT 100;

使用 EXPLAIN 观察：

sql
EXPLAIN FORMAT=TRADITIONAL
SELECT ... ;

重点关注三列：type、key、rows 和 Extra。如果 order_items 这一行出现 type=ALL、key=NULL，并且 Extra 中出现 Using where; Using join buffer (Block Nested Loop)，说明明细表关联字段缺少索引，MySQL 只能缓存外层记录后扫描明细表。在 MySQL 8.0.20 以后，也可能看到 Hash Join 相关信息，此时可通过 EXPLAIN ANALYZE 查看实际耗时与行数。

sql
EXPLAIN ANALYZE
SELECT ... ;

典型问题是：优化器预估外层只有少量订单，但由于 order_items.order_id 无索引，每一批外层记录都要与百万级明细表进行比较。即使最终 LIMIT 100，数据库也无法在关联前安全地随意截断，因为不完成匹配就无法确认哪些行满足 JOIN 条件。

索引改写：让高频路径走 NLJ ​

多表关联最稳定的优化方式，仍然是让小结果集驱动大表，并让被驱动表通过索引完成精准探测。这里给明细表补充关联索引：

sql
ALTER TABLE order_items
ADD INDEX idx_order_id (order_id);

如果明细表经常按订单查询商品，并且还需要避免回表，可以建立覆盖索引：

sql
ALTER TABLE order_items
ADD INDEX idx_order_cover (
  order_id, sku_name, quantity, item_amount
);

订单表的过滤与排序也要考虑。原条件是 order_status = 1 AND create_time >= ?，并按 create_time DESC 排序，已有 idx_status_create(order_status, create_time) 可以同时服务过滤和排序。用户表通过主键关联，users.id 本身是聚簇索引，不需要额外创建 (id, city) 这类冗余索引。

再次查看执行计划时，理想形态应接近：

  • users 通过 idx_city_create 或其他城市相关索引缩小用户范围；
  • orders 通过 idx_user_id 或 idx_status_create 获得订单；
  • order_items 通过 idx_order_id 或覆盖索引进行 ref 访问；
  • Extra 中不再出现大表的 Block Nested Loop；
  • rows 估算值显著下降。

SQL 改写：先收窄再关联 ​

仅靠加索引并不总能解决问题。当业务过滤主要落在订单表，而用户表只是补充名称时，可以用“先过滤订单、再关联维度表”的写法，降低优化器选错驱动表的概率。

sql
SELECT
  o.id AS order_id,
  o.order_no,
  u.user_name,
  item.sku_name,
  item.quantity,
  item.item_amount
FROM (
  SELECT
    id,
    user_id,
    order_no,
    create_time
  FROM orders
  WHERE order_status = 1
    AND create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  ORDER BY create_time DESC
  LIMIT 100
) AS o
JOIN users AS u
  ON u.id = o.user_id
JOIN order_items AS item
  ON item.order_id = o.id
ORDER BY o.create_time DESC;

这个改写的关键不是“子查询一定更快”，而是先在具备 idx_status_create 的订单表上完成过滤、排序和分页，只让 100 条订单参与后续关联。用户表和明细表都变成被小结果集驱动的表，配合主键和 order_id 索引，访问成本非常可控。

需要注意：如果业务语义是“每个订单必须有明细”，当前 INNER JOIN 没问题；如果要保留没有明细的订单，应改为 LEFT JOIN。不要为了执行计划好看而随意改变连接语义。

Hash Join 什么时候值得保留 ​

看到 Hash Join 不一定要立刻消除。对于等值连接，如果其中一张表经过过滤后结果集较小，而另一张表虽然很大但没有合适索引，Hash Join 往往比 BNL 更高效。例如做月度统计时，需要把临时表中的一批订单 ID 与明细表匹配，但临时查询无法长期依赖固定索引，Hash Join 可能就是合理选择。

可以通过以下参数观察或控制内存行为：

sql
-- 查看 Join Buffer 大小
SHOW VARIABLES LIKE 'join_buffer_size';

-- 会话级临时调整，不建议全局盲目调大
SET SESSION join_buffer_size = 4 * 1024 * 1024;

如果 Hash Build 输入超过内存限制，MySQL 可能落盘执行，此时性能会明显下降。线上不要简单把 join_buffer_size 调到几百 MB，因为该内存可能按会话、按连接分配，突发流量下会放大内存压力。更优先的手段仍然是缩小驱动结果集、补充正确索引，或通过离线汇总减少实时 JOIN 范围。

执行计划验证流程 ​

捕获慢 SQLEXPLAIN 看访问路径补索引或改写实际耗时验证确认扫描行数、回表次数、排序与磁盘落盘情况

避坑总结：JOIN 调优中最容易踩的 5 个坑 ​

第一，不要只看 LIMIT 100 就以为 JOIN 很快。常见现象是 SQL 最后只返回 100 行，但执行计划里大表 rows 达到百万级。原因是 JOIN、WHERE、ORDER BY 的执行顺序决定了数据库必须先找到满足条件的记录。修正方法是把强过滤条件收敛到具备复合索引的驱动表，必要时用派生表先分页再关联。

第二，关联字段只建“看起来像外键”的字段不够，必须确认真实使用了索引。典型错误是 ON DATE(oi.create_time)=DATE(o.create_time) 或 ON oi.order_id + 0 = o.id，函数、隐式类型转换、字符集不一致都会让索引失效，执行计划表现为 key=NULL、type=ALL。修正方法是保持关联列类型、字符集、排序规则一致，并改成裸字段等值关联。

第三，复合索引顺序不能只按 WHERE 条件堆列。比如查询同时有 order_status、create_time，还按 create_time DESC 排序，索引应优先考虑等值列在前、范围排序列在后，即 (order_status, create_time)。如果建成 (create_time, order_status)，MySQL 可能扫描大量时间范围记录后再过滤状态，甚至出现 Using filesort。

第四，不要盲目调大 join_buffer_size。很多 BNL 或 Hash Join 落盘问题，根因是被驱动表缺索引或驱动结果集过大。把 Buffer 调大只能缓解一部分比较成本，却可能在高并发下造成内存飙升。正确做法是先用 EXPLAIN ANALYZE 看实际行数和耗时，再通过索引、SQL 改写、汇总表缩小数据集。

第五，统计信息过期会让优化器选错驱动表。现象是测试库走索引很快，线上却选择全表扫描，或者小表反而被大表驱动。可在大批量数据导入或字段分布明显变化后执行：

sql
ANALYZE TABLE users, orders, order_items;

JOIN 调优没有固定的“NLJ 一定优于 Hash Join”。核心判断标准是：谁经过过滤后最小，谁适合做驱动；被驱动表能否用索引精准探测；无法用索引时 Hash Join 的构建输入是否足够小。沿着执行计划而不是经验参数去优化，多表关联才能从偶发快、变成稳定快。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。