两个 JOIN 列都有索引，MySQL 还是跑了 38 秒

场景：订单明细查询从 200ms 飙升到 38 秒，orders（500 万行）JOIN order_items（2000 万行），两个 JOIN 列都有索引路径：EXPLAIN → 驱动表 rows 异常 → 统计信息过旧 → STRAIGHT_JOIN / ANALYZE TABLE

上篇讲了 GROUP BY 查询性能骤降的根因分析——临时表从内存搬到磁盘。这次我们来看另一种情况：索引没失效，驱动表选错了。

SELECT o.order_no, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 1
  AND oi.created_at >= '2026-01-01'
ORDER BY oi.id DESC
LIMIT 50;

——两个 JOIN 列都有索引，跑了 38 秒。

不是数据量大，是优化器选错了驱动表。

【现象】从 200ms 到 38 秒：一个 JOIN 的离奇退化

业务背景

几个小时后端收到了几十个告警——订单查询接口耗时从平稳的 200ms 突然飙升到 38 秒。

表结构：

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(12,2),
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_name VARCHAR(128) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2),
    created_at DATETIME NOT NULL,
    INDEX idx_order_id (order_id),
    INDEX idx_created_at (created_at),
    INDEX idx_order_id_created (order_id, created_at)
) ENGINE=InnoDB;

orders 约 500 万行，order_items 约 2000 万行。两个表的 JOIN 列（orders.id = order_items.order_id）都走了索引。

EXPLAIN 关键信号

第一眼：两个表都用上了索引（没有 type=ALL），但驱动表（orders）的 rows 是 450 万。

关键信号：驱动表 rows = 450 万，远大于被驱动表 rows = 1 — 驱动表比被驱动表还大。

MySQL 版本说明：以下分析基于 MySQL 8.0.32。8.0 优化器对 JOIN 的估算优于 5.7（有 hash join、更好的 cost 模型），但统计信息过旧依然能让它"瞎"选。

【解析】优化器不是选错索引——是选错了驱动表

数据分布拆解

慢的根因不在索引结构，在数据分布：

orders:         5,000,000 行
  status=1:     4,500,000 行 (90%)
  status=0:       500,000 行 (10%)

order_items:   20,000,000 行
  一个月范围:     ~800,000 行 (4%)
  六个月范围:   ~4,000,000 行 (20%)

优化器的视角： - orders 做驱动表：status=1 过滤后剩 450 万行 → 每行去 order_items 做 ref JOIN（type=ref, 1 行/次） - order_items 做驱动表：created_at >= '2026-01-01'（半年 ~400 万行）→ 每行去 orders 做 ref JOIN（type=ref, 1 行/次）

优化器选了更"小"的那个驱动表——但这是错的。

优化器在哪里算错了

执行计划的两个候选：

方案 A（优化器选的）:
  orders (idx_status, rows=4500K)
    └─→ order_items (idx_order_id, rows=1)          × 4500K 次 ref lookup

方案 B（实际最优）:
  order_items (idx_created_at, range, rows=4000K)
    └─→ orders (PRIMARY, rows=1)                    × 4000K 次 ref lookup

优化器以为方案 B 更贵——4000K 次 ref lookup > 4500K 次（4500K 个订单各查 1 行明细）。

但有两个隐藏成本优化器没算进去：

① ORDER BY + LIMIT 的截断效应 SQL 中有 ORDER BY oi.id DESC LIMIT 50。如果从 order_items 驱动，MySQL 可以走 idx_created_at 的索引顺序，每找到一条满足 created_at >= '2026-01-01' 的行就去 orders 查 status——找到 50 条就停。扫描行数远小于 400 万。

但从 orders 驱动时，MySQL 必须先扫完所有 status=1 的 450 万行订单，把结果写入临时表，再排序取前 50——450 万行的中间结果集全部生成完了才开始排序。

② 统计信息过旧 SHOW TABLE STATUS 显示 orders 的 rows=5,000,000（实际已增长到 800 万）。order_items 显示 rows=20,000,000（实际已增长到 5000 万）。优化器的 cost 估算是基于 500 万的，多出来的 60% 成本它完全不知道。

EXPLAIN FORMAT=JSON 成本拆解

用 EXPLAIN FORMAT=JSON 能看到优化器给 orders 方案的 cost 评分——prefix_cost 约 49 万。看起来不高对吧？但这只是嵌套循环的成本，Using filesort 的 450 万行文件排序完全是隐藏账单。优化器选了它，是因为它看不见排序成本。

执行计划逐字段拆解

字段	值	解读
`o.type`	ref	用索引等值查找，不差
`o.key`	idx_status	索引选对了
`o.rows`	4500K	驱动表预计扫描 450 万行——这是第一个红旗
`o.Extra`	Using where	有额外的 WHERE 过滤（status=1 已经在索引里做了）
`oi.type`	ref	被驱动表用索引等值查找，正常
`oi.key`	idx_order_id	JOIN 列走索引
`oi.rows`	1	每次 JOIN 扫描 1 行

type=ref — 索引等值查找。比 type=ALL（全表扫描）好，但不如 type=eq_ref（主键或唯一索引查找）。type=ref 表示找到多条匹配行的可能性。

看到 orders.rows 比 order_items.rows 两个数量级大，就说明驱动表可能选错了。

5.7 vs 8.0

版本	行为差异
MySQL 5.7	没有 hash join，对 ORDER BY + LIMIT 的结合场景 cost 估算更差。驱动表选错后几乎只能 STRAIGHT_JOIN
MySQL 8.0	有 hash join + 更好的 cost 模型，但统计信息过旧时依然会出现同款问题

8.0 的基于哈希的 join 在 OLTP 场景中不会自动启用（小表驱动 + 索引 JOIN 时优化器优先选 NLJ）。真正的改进是 8.0 能更准确评估 LIMIT 的截断效果——前提是统计信息准确。

【转折】第一次优化：加索引——EXPLAIN 好看了，但没用

为什么 EXPLAIN 漂亮了还是慢？

当时的第一反应：给 order_items 加一个覆盖索引。

ALTER TABLE order_items ADD INDEX idx_cvr_oid_ct
    (order_id, created_at, product_name, quantity);

新的 EXPLAIN：

Extra 多了 Using index（覆盖索引，免回表），rows 没变，驱动表还是 orders。

看到这个 EXPLAIN 的时候我是满意的——全绿，没有 Using temporary，没有 Using filesort。覆盖索引，教科书式的"正确"方案。

跑了一下：35 秒。

我以为我看错了。又跑了一次——35 秒。明明 Extra 比之前还漂亮，为什么只快了 3 秒？

这个时候才意识到：问题不是索引——是驱动表选错了。 加索引优化的是被驱动表的访问效率（让 order_items 的 JOIN 更快），但在 orders 驱动 450 万行这个前提下，被驱动表再快也没用——瓶颈在驱动表太大，不在 JOIN 时的单次查找速度。

【重构】第二次：告诉 MySQL 真实数据量

方案 A：ANALYZE TABLE 更新统计信息

ANALYZE TABLE orders;
ANALYZE TABLE order_items;

更新后优化器重新计算 cost，自动选了正确的驱动表。

指标	优化前	优化后
驱动表	orders（450 万行）	order_items（range 扫描）
耗时	38 秒	0.3 秒
排序方式	文件排序（450 万行）	索引顺序取前 50
扫描行数	~450 万	~8 万（+LIMIT 截断）

为什么 ANALYZE TABLE 有效？

ANALYZE TABLE 重新计算表的基数（cardinality）和行数估算。当数据量从 500 万增长到 800 万，而统计信息还停在 500 万时，优化器在用一个错误的基数做决策。更新后 rows 回到真实值，cost 估算才可靠。

方案 B：STRAIGHT_JOIN 强制驱动表（紧急止血）

SELECT o.order_no, oi.product_name, oi.quantity
FROM orders o
STRAIGHT_JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 1
  AND oi.created_at >= '2026-01-01'
ORDER BY oi.id DESC
LIMIT 50;

STRAIGHT_JOIN 强制 MySQL 以左表为驱动表、右表为被驱动表。上面这段 SQL 把 STRAIGHT_JOIN 加在 orders 和 order_items 之间，就是强制执行 orders_items 驱动、orders 被驱动的 JOIN 顺序。

效果：同样 0.3 秒。

使用限制： - STRAIGHT_JOIN 只适用于知道哪个表更小的场景。如果数据分布后续变化，硬编码可能反而更差 - 长期方案应当是 ANALYZE TABLE 或者设置 innodb_stats_auto_recalc = ON - STRAIGHT_JOIN 在 MySQL 8.0.32 中已验证有效，8.0 后续版本行为一致

生产建议：如果是紧急止血，先用 STRAIGHT_JOIN 恢复。然后在低峰期 ANALYZE TABLE 更新统计信息，再逐步移除 STRAIGHT_JOIN。两者结合使用——一个堵住眼前的坑，一个防止同一个坑下次换个地方又出现。

为什么统计信息会过时？

MySQL InnoDB 的统计信息默认通过 innodb_stats_auto_recalc（默认 ON）自动重算，但触发条件是表中 10% 的数据行发生变化。

当 order_items 从 2000 万增长到 3000 万之间时没触发 10% 阈值（自动统计信息重算不是实时的，可能有最多数秒延迟）。如果表上同时还有大量写入，自动统计可能无法跟上变化节奏——导致优化器用的估算行数和实际严重偏离。

什么时候该主动 ANALYZE TABLE：

信号	操作
`SHOW TABLE STATUS` 中 `rows` 与实际偏差 > 30%	`ANALYZE TABLE`
大表写入量突然翻倍	写入稳定后 `ANALYZE TABLE`
升级 MySQL 大版本后	`ANALYZE TABLE`
SQL 因执行计划突变从毫秒变成秒级	先 `ANALYZE TABLE` 再排查其他原因

【路径】🔍 诊断驱动表选错的 3 步决策树

看到 EXPLAIN 输出时，按这个顺序判断驱动表是否选对：

Step 1 — 看驱动表的 rows

EXPLAIN 结果中第一个 table 就是驱动表。如果它的 rows > 被驱动表的 rows（且相差 10 倍以上）→ 危险信号。

❌ orders: rows=4500K > order_items: rows=1 → 驱动表太大

Step 2 — 看 filtered

filtered — 从存储引擎读取的行中，经过 WHERE 条件过滤后剩余行数的百分比。filtered=100% 表示存储引擎读了多少行就返回了多少行（WHERE 由索引完全过滤，没有额外过滤）。在诊断驱动表时更关键的指标是 rows × filtered%——这才是驱动表实际需要参与 JOIN 的行数估算：

把 filtered 和 rows 结合看：实际 JOIN 行数 ≈ rows × (filtered / 100)
如果驱动表的 rows × filtered% 仍然远大于被驱动表的 rows，选错概率就很高

驱动表 rows	filtered	结论
大	高 (>50%)	❌ 大概率选错
大	低 (<10%)	✅ 虽大但过滤好
小	任何值	✅ 合理

Step 3 — 核对统计信息

SHOW TABLE STATUS LIKE 'orders';
SHOW TABLE STATUS LIKE 'order_items';

比较 rows 字段与实际数据量。偏差 > 30% → ANALYZE TABLE。

记住：EXPLAIN 的 rows 是优化器的估算，不是精确值。但两张表的 rows 相对关系如果异常，就已经是值得深挖的信号。

【标记】🗝 在你的项目里搜这类慢 JOIN

1. 慢查询日志

# 搜 JOIN + 执行时间 > 5 秒的查询
grep -i "join" mysql-slow.log | grep -E "Query_time: [5-9]|[1-9][0-9]"

# 或使用 mysqldumpslow 汇总
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log | grep -i join

2. 查 performance_schema（MySQL 8.0）

SELECT digest_text, count_star,
       ROUND(avg_timer_wait / 1000000000, 2) as avg_ms,
       ROUND(sum_rows_examined / count_star) as avg_rows,
       first_seen, last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%JOIN%'
  AND avg_timer_wait / 1000000000 > 5000
ORDER BY avg_timer_wait DESC
LIMIT 20;

3. 核对统计信息一致性

SELECT TABLE_NAME, TABLE_ROWS,
       (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS total_mb,
       UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME IN ('orders', 'order_items');

TABLE_ROWS 是估算值，但如果它和实际数据量严重不符（比如 TABLE_ROWS 500 万但实际有 800 万行），就是统计信息需要更新的信号。

4. 代码仓库里搜

# 搜 Java 项目中的 JOIN 查询（没有合适索引的）
grep -r "JOIN" src/main/java/ --include="*.java" | \
  grep -v "INDEX" | head -20

# 搜 XML Mapper 中的 JOIN
grep -r "join" src/main/resources/ --include="*.xml" | \
  grep -i "select" | head -20