在平常的程序开发中不避免要进行多表关联查询虽然行业普遍规范是要求尽量少用 JOIN 的但在一些后台管理功能、报表功能中还是需要进行多表关联查询。在一些入行不久的研发同学中可能直接上手就写 JOIN 语句而不去关注是否写得正确今天就来分享一些在日常开发中使用 JOIN时一些很容易被忽视但一旦踩上就会让你抓狂的问题JOIN 的驱动表选错了性能差几十倍都是轻的。说起来有点讽刺可能很多人写了好几年 SQLJOIN 连接查询也写了无数条但从来没有思考过一个问题MySQL 到底是先扫哪张表驱动表是怎么决定的如果它选错了我该怎么办这种忽视在数据量小的时候没什么感觉但等到生产环境某张表涨到千万级你就会发现同一条 SQL原来只要 50 毫秒现在要 8 秒而且你改来改去索引都没用。今天这篇文章我就来手把手带你搞清楚 JOIN 驱动表这件事。下面这张图是本文的核心先收藏再看。图1JOIN 驱动表排查流程图— SQL 变慢后按这张图走一遍90% 的 JOIN 慢查询问题都能定位到。先收藏再看。一、从一个真实例子说起下面来看一条 SQL缩减是之前做一个内容平台项目里使用到。当时有一个接口功能是查询某个分类下的文章列表并带上作者信息SQL 大概是这样的SELECT a.id, a.title, a.content, u.nickname, u.avatar FROM articles a JOIN users u ON a.author_id u.id WHERE a.category_id 12 ORDER BY a.created_at DESC LIMIT 20;这条 SQL 在测试环境跑得很好响应 30 毫秒以内。但某天上线后运营反映文章列表页加载很慢有时候要 6 秒才出来。DBA 拉出慢查询日志经过一翻查找最后确定是这条 SQL 引起的在高峰期执行时间飙到了 5~8 秒。当时的研发第一反应就说是不是索引的问题于时赶紧EXPLAIN一下这条 SQL EXPLAIN SELECT a.id, a.title, a.content, u.nickname, u.avatar FROM articles a JOIN users u ON a.author_id u.id WHERE a.category_id 12 ORDER BY a.created_at DESC LIMIT 20;输出结果如下---------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | ALL | PRIMARY | NULL | NULL | NULL | 1200000 | NULL | | 2 | SIMPLE | a | ref | idx_author_cat | idx... | 8 | u.id | 320 | Using where | ----------------------------------------------------------------------------------------------------------大家看这个输出结果有什么问题结合在上一篇文章中介绍的 EXPLAIN 的使用你能发现问题吗来看下users表排在第一行它是驱动表。而users表有120 万条用户数据type 是ALL也就是全表扫描。这个输出表时MySQL 先全扫了 120 万用户再对每个用户去articles表里找文章。研发瞬间晕是不是搞反了他原以为是用articles来当驱动表去关联。articles加了WHERE category_id 12过滤后只有 300 多条理论上应该是先从 articles 拿这 300 条再去 users 找对应用户信息才对。这个例子说明了驱动表选错了而且错得很离谱。二、驱动表是什么MySQL 怎么选在讲怎么优化 JOIN连表 之前我们需要先把 JOIN连表 原理搞清楚这样你以后看到问题一眼就能判断并能自行解决。2.1 什么是驱动表JOIN 查询不是两张表“同时扫描”实际上是有先后顺序的在MySQL 的内部是有一个嵌套循环Nested Loop来处理这件事的for each row in 驱动表外层循环{ for each row in 被驱动表内层循环{ if 满足 JOIN 条件 → 输出到结果集 } }驱动表就是外层循环那张表它的每一行都会触发一次对被驱动表的查找。被驱动表是内层循环每次被从驱动表过来的一行“驱动”着去查。所以驱动表的行数决定了外层循环跑多少次被驱动表每次被查最好能走索引否则就是每次都全表扫2.2 MySQL 怎么决定谁是驱动表这个是由 MySQL 优化器根据代价模型Cost Model来决定简单来说它会估算两种顺序的总成本然后选便宜的那一个。看到这里你可能心理在想那这个代价模型有那些影响因素呢根据 MySQL 官方的资料影响这个决策的核心因素有三个因素解释过滤后的行数WHERE 条件过滤后哪张表剩的行少哪张表更适合当驱动表被驱动表是否有索引被驱动表有索引每次查找是 O(log N)没索引就是全表扫统计信息的准确性优化器基于采样估算如果统计信息过期可能会选错优化的核心原则小表驱动大表。这里的“小”不是指表的总行数而是指过滤后实际要循环的行数。2.3 用行数来验证一下差距上面提到了代价模型其中涉及到行数估算下面还是拿刚才的例子来演示让你感受一下这个过程。方案 Ausers120万驱动articles300条有索引外层循环120 万次 每次内层查找走索引约 log(500万) ≈ 23 次 I/O 总 I/O 估算120万 × 23 ≈ 2760 万次方案 Barticles300条驱动users120万走主键索引外层循环300 次 每次内层查找走主键索引约 log(120万) ≈ 20 次 I/O 总 I/O 估算300 × 20 ≈ 6000 次差了 4600 倍。这可不是玄学这可是实实在在的数学。所以优化器在统计信息准确的情况下一定会选方案 B可是它选了方案 A这说明它的判断出问题了。那到底是那里有问题三、在 EXPLAIN 里如何看驱动表从上面的分析可以看到寻找驱动表对于 JOIN 连接很重要若你没办法一眼就看出来可以借助 EXPLAIN 执行计划来看下面来看下它没有那么神乎。看的规则很简单EXPLAIN 输出结果中id相同时从上到下第一张表就是驱动表。----------------- | id | table | type | ----------------- | 1 | users | ALL | ← 驱动表外层循环 | 1 | articles | ref | ← 被驱动表内层循环 -----------------除了看驱动表同时还需要关注Extra列那里有一个信号要特别注意Using join buffer (hash join)或Using join buffer (Block Nested Loop)若这个提示出现在被驱动表那行表明被驱动表没有索引可用MySQL 不得不把驱动表的数据缓存到 join buffer 里然后批量比对。这个信号通常说明两件事被驱动表的连接列上没有索引这个 JOIN 的效率有优化空间图2NLJ 嵌套循环原理— 左边是大表驱动小表错右边是小表驱动大表对。循环次数的差异决定了性能的天壤之别。四、为什么优化器会选错回到上面的问题在2个方案对比中明显有一个小表当驱动表应该选择但为什么 MySQL 优化器却选错了驱动表是不是 MySQL 傻了当然不是。出现 MySQL 选错驱动表通常来有以下几种常见的原因原因 1记录行数统计信息过期MySQL 优化器依赖表的统计信息来估算行数。users表如果最近刚做过大批量写入统计信息还没更新优化器可能还以为users只有几万行。验证方法-- 查看 users 表的统计信息 SHOW TABLE STATUS LIKE users\G -- 或者 SELECT table_name, table_rows, data_length, update_time FROM information_schema.tables WHERE table_name users;如果table_rows和实际相差很大说明统计信息过期了。修复方法ANALYZE TABLE users; ANALYZE TABLE articles;执行完再 EXPLAIN 看看通常优化器就会选对了。原因 2连接列缺索引这是一个比较常见的问题很多人在建表初期可能没有考虑到后期会使用到多表关联从而没有建立相关索引回顾下你是否也有做这种情况。来演示个例子比如articles.author_id没有索引-- articles 表 CREATE TABLE articles ( id BIGINT PRIMARY KEY, author_id BIGINT, -- 没有索引 category_id INT, title VARCHAR(200), ... );这时候不管谁当驱动表被驱动表都得全表扫。但如果articles当驱动表300 行 × 全表扫 120 万users 灾难。这种情况下给author_id加索引是最重要的事ALTER TABLE articles ADD INDEX idx_author_id (author_id);加完索引再 EXPLAIN你会看到articles上来当驱动表users走主键索引——一切都对了。原因 3WHERE 过滤条件没有用上有时候优化器估算的过滤率和实际偏差很大比如-- articles 里 category_id 12 的文章只有 300 条 -- 但优化器估算有 50000 条因为采样不均匀 WHERE a.category_id 12优化器觉得articles过滤后还剩 50000 条users只有 120 万于是选了users当驱动表因为它觉得 articles 的结果集不够小。这种情况可以用 MySQL 8.0 的直方图来辅助优化器-- 为 category_id 建立直方图帮助优化器更准确地估算值分布 ANALYZE TABLE articles UPDATE HISTOGRAM ON category_id WITH 256 BUCKETS;直方图告诉优化器每个category_id的值分布情况优化器就能更准确地估算过滤后的行数。五、常见的几种典型驱动表问题好了上面对 JOIN 驱动表的知识进行讲解也给出了一些示例这里我梳理了过往项目的实际经验总结了几种常遇到的问题你可以对照着看是否也有做这种情况问题一大表被当驱动表特征EXPLAIN 第一行是中行数多的大表type 是 ALL 或 index示例-- 错误情况 | 1 | users | ALL | 1200000 行 | ← 大表在前全表扫 | 1 | articles | ref | 15 行 |修复通常是统计信息过期执行ANALYZE TABLE后重新观察问题二被驱动表无索引特征EXPLAIN 里看到Using join buffer (hash join)或Using join buffer (Block Nested Loop)示例| 1 | articles | ref | 300 行 | | 1 | orders | ALL | 5000000 行 | Using join buffer (hash join)修复在orders表的连接列上加索引-- 假设连接条件是 articles.id orders.article_id ALTER TABLE orders ADD INDEX idx_article_id (article_id);问题三过滤条件没有配套索引特征EXPLAIN 里articles的 type 是 ALL但WHERE category_id 12应该能过滤大量数据修复在过滤列加索引ALTER TABLE articles ADD INDEX idx_category_id (category_id); -- 或者加组合索引过滤连接排序一起覆盖 ALTER TABLE articles ADD INDEX idx_cat_author_created (category_id, author_id, created_at);问题四强制指定驱动顺序STRAIGHT_JOIN如果分析之后你确认 MySQL 的选择是错的但一时没法改表结构比如加索引需要走审批流程可以用STRAIGHT_JOIN强制指定连接顺序-- STRAIGHT_JOIN强制 FROM 后第一张表作为驱动表 SELECT a.id, a.title, u.nickname FROM articles a STRAIGHT_JOIN users u ON a.author_id u.id WHERE a.category_id 12 ORDER BY a.created_at DESC LIMIT 20;注意STRAIGHT_JOIN是应急手段不是长期方案。因为它绕过了优化器的判断一旦数据分布变了原来正确的顺序可能就不对了。加完索引或更新完统计信息后记得把它去掉。图3优化前后对比— 上面是优化前users 驱动6 秒下面是三步修复后articles 驱动48 毫秒。ANALYZE TABLE 组合索引效果立竿见影。六、实操一个完整的排查与优化案例我们回到开头那个 articles users 的例子完整走一遍排查流程。第一步定位慢 SQL-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 或者用 performance_schema 查当前在跑的慢 SQL SELECT digest_text, avg_timer_wait/1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10;第二步EXPLAIN 确认驱动表问题EXPLAIN SELECT a.id, a.title, a.content, u.nickname, u.avatar FROM articles a JOIN users u ON a.author_id u.id WHERE a.category_id 12 ORDER BY a.created_at DESC LIMIT 20;看到users排第一type 是 ALL120 万行——确认是驱动表选错。第三步刷新统计信息ANALYZE TABLE articles; ANALYZE TABLE users;第四步再次 EXPLAINEXPLAIN SELECT ...同上;这次结果------------------------------------------------------------------------------------------------ | id | table | type | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------ | 1 | a | ref | idx_cat_author_created | 4 | const | 312 | Using where; Using index | | 1 | u | eq_ref | PRIMARY | 8 | a.author_id | 1 | NULL | ------------------------------------------------------------------------------------------------articles 上来了312 行做外层循环users 走主键索引每次找 1 行。执行时间从 6 秒 →48 毫秒。第五步补充组合索引可选但推荐如果articles表的查询模式比较固定按 category 排序可以加一个覆盖索引ALTER TABLE articles ADD INDEX idx_cat_created_cover (category_id, created_at DESC, author_id, id, title);这样过滤、排序、连接列都在索引里不需要回表Extra 会显示Using index性能还能再提升一截。七、三种 JOIN 算法的横向对比MySQL 8.0 里实际上有三种 JOIN 算法搞清楚它们你就能理解 Extra 里各种提示的含义算法触发条件EXPLAIN Extra性能NLJ嵌套循环被驱动表有索引无特殊提示✅ 最好Hash JoinMySQL 8.0.18无索引等值连接Using join buffer (hash join)⚠️ 中等BNL块嵌套循环 8.0.20无索引Using join buffer (Block Nested Loop)❌ 最差8.0.20 起废弃NLJ 是最优的。它利用被驱动表上的索引外层每循环一次内层只需要几次 I/O。Hash Join虽然没有 NLJ 快但比老的 BNL 强多了特别是连接列没有索引的情况下它是当前8.0.20的兜底方案。如果你在 EXPLAIN 里看到Using join buffer (hash join)说明被驱动表没有合适的索引——这是一个给连接列加索引的信号。图4MySQL 8.0 三种 JOIN 算法— 从左到右性能递减。Using join buffer (hash join)出现在 Extra 里就是提醒你该给被驱动表加索引了。八、JOIN 优化总结与速查表一句话总结 JOIN 的相关知识点核心知识点一句话总结驱动表外层循环那张表行数决定循环次数被驱动表内层循环那张表必须有索引才高效选择原则过滤后行数少的表当驱动表小表驱动大表EXPLAIN 读法id 相同时第一行驱动表Using join buffer被驱动表无索引需要加索引的信号NLJ最优算法前提是被驱动表有索引Hash Join8.0.18 的兜底算法无索引时使用STRAIGHT_JOIN强制指定驱动顺序应急用不推荐长期使用ANALYZE TABLE刷新统计信息解决优化器估算偏差直方图解决值分布不均匀导致的估算偏差速查表帮你快速定位问题场景EXPLAIN 特征解决方案大表被选为驱动表第一行 rows 很大typeALL执行ANALYZE TABLE刷新统计信息被驱动表无索引Extra 显示Using join buffer给被驱动表的连接列加索引过滤条件没有索引驱动表 rows 估算偏大给 WHERE 条件列加索引或建直方图优化器持续选错刷新统计后仍然不对临时用STRAIGHT_JOIN同时查根因多 JOIN 性能差多个Using join buffer逐个分析从最外层驱动表开始修值分布不均匀统计信息准确但仍选错使用直方图ANALYZE TABLE ... UPDATE HISTOGRAM ON col写在最后我在做代码 Review 时经常看到这样的情况一个 JOIN 写了两三层索引也加了但偏偏慢改来改去摸不到头绪。很多时候根因不是索引本身而是驱动表搞反了。优化器做了一个基于统计信息的判断但那个统计信息不准或者没有足够的信息比如缺了直方图导致它做了一个在你看来不可思议的选择。理解驱动表记住三个动作EXPLAIN 看第一行确认谁是驱动表检查 Extra 有没有Using join buffer有的话给被驱动表加索引统计信息过期就执行ANALYZE TABLE90% 的问题到这里就解了写在最后驱动表选错这件事不是 MySQL 的 bug而是它在用不完整的信息做决策。你给它更准确的信息索引统计信息直方图它就会做出正确的判断。从这个角度来看学会看 EXPLAIN就是学会跟 MySQL 的优化器沟通。下期预告说完了 JOIN 的执行顺序下一篇聊另一个高频踩坑NOT IN遇到 NULL 值结果集莫名其妙变空——一个很多人踩了之后还没搞清楚原因的经典问题。记得星标别错过。