MySQL JOIN 优化详解

我刚工作的时候有次上线了个新功能结果有个 JOIN 查询慢得要命用户投诉电话被打爆。DBA 帮我一看执行计划发现驱动表选错了扫描了 2000 万行。从那以后我每次写 JOIN 查询都会用EXPLAIN看看执行计划避免线上事故。今天咱们就来彻底搞懂 MySQL 的 JOIN 优化看完这篇你就能写出高性能的 JOIN 查询了。JOIN 的两种算法MySQL 的 JOIN 有两种算法Nested Loop Join嵌套循环连接和Hash Join哈希连接MySQL 8.0。1. Nested Loop JoinNLJ默认NLJ 的思路很简单双层循环。for each row in t1 { -- 外层循环驱动表 for each row in t2 { -- 内层循环被驱动表 if (join_condition) { output row } } } **问题**如果 t1 有 1000 行t2 有 10000 行那内层循环要执行 1000 × 10000 1000 万次 **优化**给被驱动表的关联字段加索引这样内层循环不需要全表扫描变成 **Index Nested Loop JoinINLJ**。 ### 2. Hash JoinHJMySQL 8.0 Hash Join 的思路先给被驱动表建哈希表再遍历驱动表去匹配。– 第 1 步给 t2 建哈希表按关联字段hash_table build_hash_table(t2, join_key)– 第 2 步遍历 t1去哈希表匹配for each row in t1 {if (hash_table.contains(t1.join_key)) {output row}}优势不需要索引适合大表关联。劣势只能做等值关联不能做非等值关联、、BETWEEN。驱动表 vs 被驱动表JOIN 查询有两个表驱动表Driver Table和被驱动表Driven Table。驱动表外层循环全表扫描或者用索引被驱动表内层循环用索引匹配关键驱动表选错了性能会差 10 倍怎么选驱动表原则小表驱动大表驱动表的数据量少。-- 假设 users 表有 1000 行orders 表有 1000000 行SELECT*FROMusers uJOINorders oONu.ido.user_id;优化器会选择users 作为驱动表1000 行orders 作为被驱动表用user_id索引匹配。为什么因为外层循环的次数决定了内层循环要执行多少次。如果驱动表小内层循环执行次数就少。验证一下EXPLAINSELECT*FROMusers uJOINorders oONu.ido.user_id;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | ALL | PRIMARY | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | o | ref | idx_user_id | idx_user_id | 5 | db.u.id | 10 | | ----------------------------------------------------------------------------------------------解读第 1 行table uusers 是驱动表type ALL全表扫描因为要遍历所有行第 2 行table oorders 是被驱动表type ref用索引匹配ref db.u.id关联字段总扫描行数1000驱动表 1000 × 10被驱动表平均每次匹配 10 行 11000 行。如果驱动表选反了orders 是驱动表总扫描行数1000000驱动表 1000000 × 1被驱动表假设 users 每张表只有 1 行匹配 2000000 行。性能差距2000000 / 11000 ≈ 181 倍优化 JOIN 的实战步骤咱们来个实际案例假设要查所有用户的订单SQL 很慢。第 1 步看执行计划EXPLAINSELECT*FROMusers uJOINorders oONu.ido.user_idWHEREu.age20;输出-------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | range | idx_age | idx_age | 5 | NULL | 500000 | Using where | | 1 | SIMPLE | o | ref | idx_user_id | idx_user_id | 5 | db.u.id | 10 | | --------------------------------------------------------------------------------------------------------问题驱动表uusers扫了 500000 行rows 500000被驱动表oorders平均每次匹配 10 行总匹配 500000 × 10 5000000 行总扫描行数500000 5000000 5500000 行第 2 步优化驱动表的选择问题WHERE u.age 20过滤后还有 500000 行驱动表太大了。方案如果orders表过滤后行数更少可以换个写法让优化器选择orders作为驱动表。-- 假设我们要查 2024 年下的订单 的用户信息EXPLAINSELECT*FROMusers uJOINorders oONu.ido.user_idWHEREo.created_at2024-01-01;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | SIMPLE | o | range | idx_created | idx_created | 6 | NULL | 1000 | | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | db.o.user_id | 1 | | ----------------------------------------------------------------------------------------------优化效果驱动表变成了oorders只扫 1000 行被驱动表uusers平均每次匹配 1 行总匹配 1000 × 1 1000 行总扫描行数1000 1000 2000 行从 5500000 降到 20002750 倍提升第 3 步确保被驱动表的关联字段有索引如果被驱动表的关联字段没索引会导致Block Nested Loop JoinBNLJ性能极差。-- 假设 orders.user_id 没有索引EXPLAINSELECT*FROMusers uJOINorders oONu.ido.user_id;输出------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 1000000 | Using join buffer (Block Nested Loop) | -------------------------------------------------------------------------------------------------------------------问题Extra Using join buffer (Block Nested Loop)用了 BNLJ 算法BNLJ 会把驱动表的数据放到join buffer然后全表扫描被驱动表匹配如果被驱动表很大join buffer放不下会分多次全表扫描性能炸裂解决方案给被驱动表的关联字段加索引。CREATEINDEXidx_user_idONorders(user_id);常见 JOIN 类型及优化1. INNER JOIN内连接SELECT*FROMusers uINNERJOINorders oONu.ido.user_id;优化给被驱动表的关联字段加索引让小表驱动大表优化器会自动选但你可以用STRAIGHT_JOIN强制顺序-- 强制驱动表顺序u 是驱动表o 是被驱动表SELECT*FROMusers u STRAIGHT_JOIN orders oONu.ido.user_id;2. LEFT JOIN左连接SELECT*FROMusers uLEFTJOINorders oONu.ido.user_id;特点LEFT JOIN 的左表一定是驱动表因为要保留左表的所有行。优化给右表被驱动表的关联字段加索引如果右表太大考虑用子查询过滤后再 JOIN-- 优化前右表太大SELECT*FROMusers uLEFTJOINorders oONu.ido.user_id;-- 优化后先过滤右表SELECT*FROMusers uLEFTJOIN(SELECT*FROMordersWHEREcreated_at2024-01-01)oONu.ido.user_id;3. RIGHT JOIN右连接SELECT*FROMusers uRIGHTJOINorders oONu.ido.user_id;特点RIGHT JOIN 的右表一定是驱动表。建议尽量用 LEFT JOIN 代替 RIGHT JOIN更符合阅读习惯。4. 多表 JOIN2 张表SELECT*FROMt1JOINt2ONt1.idt2.t1_idJOINt3ONt2.idt3.t2_id;优化给所有被驱动表的关联字段加索引调整 JOIN 顺序让小表驱动大表如果优化器选错了驱动表用STRAIGHT_JOIN强制顺序SELECT*FROMt1 STRAIGHT_JOIN t2ONt1.idt2.t1_id STRAIGHT_JOIN t3ONt2.idt3.t2_id;实战建议1. 给被驱动表的关联字段加索引最重要这是最重要的建议。如果被驱动表的关联字段没索引会导致 BNLJ性能极差。-- 检查关联字段有没有索引EXPLAINSELECT*FROMusers uJOINorders oONu.ido.user_id;-- 如果被驱动表 type ALL说明没走索引2. 让小表驱动大表如果优化器选错了驱动表用STRAIGHT_JOIN强制顺序。-- 强制驱动表顺序SELECT*FROMsmall_table STRAIGHT_JOIN big_tableON...;3. 先过滤再 JOIN如果某张表可以先过滤WHERE 条件先过滤再 JOIN减少驱动表的数据量。-- 优化前先 JOIN 再过滤SELECT*FROMusers uJOINorders oONu.ido.user_idWHEREo.created_at2024-01-01;-- 优化后先过滤再 JOINSELECT*FROMusers uJOIN(SELECT*FROMordersWHEREcreated_at2024-01-01)oONu.ido.user_id;4. 避免 SELECT ***SELECT *** 会查所有字段增加网络传输和内存消耗而且无法用覆盖索引。建议只查需要的字段。-- 优化前SELECT*FROMusers uJOINorders oONu.ido.user_id;-- 优化后SELECTu.id,u.name,o.id,o.amountFROMusers uJOINorders oONu.ido.user_id;5. 用 EXPLAIN 检查执行计划每次写完 JOIN 查询都用EXPLAIN看看执行计划确保被驱动表的关联字段走了索引type ref或eq_ref驱动表的数据量尽量小rows尽量小Extra里没有Using join buffer (Block Nested Loop)总结MySQL 的 JOIN 有两种算法Nested Loop Join默认和 Hash JoinMySQL 8.0JOIN 查询有驱动表外层循环和被驱动表内层循环要让小表驱动大表优化 JOIN 的步骤看执行计划 → 确保被驱动表的关联字段有索引 → 让小表驱动大表 → 先过滤再 JOIN常见 JOIN 类型INNER JOIN、LEFT JOIN、RIGHT JOIN、多表 JOIN实战建议给被驱动表的关联字段加索引、让小表驱动大表、先过滤再 JOIN、避免 SELECT *、用 EXPLAIN 检查执行计划如果你能把 JOIN 的两种算法、驱动表的选择、优化步骤讲清楚面试官绝对觉得你是高级开发。实战代码都在我本地跑过你可以放心复制。如果有问题欢迎评论区交流