后端写 SQL 一半的慢查都出在 JOIN 和索引上。本文用 Venn 图文字版讲 INNER/LEFT/RIGHT/FULL JOIN 区别、联合索引最左前缀原则、EXPLAIN 的 type/key/rows/Extra 4 列怎么看、覆盖索引、为什么不能 SELECT *。
校验环境
MySQL 8.0.39,InnoDB 引擎,字符集 utf8mb4。所有 SQL 在 10 万行的订单表 + 1 万行的用户表上实跑,EXPLAIN 输出真实。
准备两张表(后面所有 JOIN 和索引示例用):
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
phone VARCHAR(20) NOT NULL UNIQUE,
nickname VARCHAR(64) NOT NULL,
level TINYINT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_level_created (level, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
amount INT NOT NULL, -- 分,避免浮点数
status TINYINT NOT NULL, -- 1=待支付 2=已支付 3=取消 4=退款
paid_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_created (user_id, created_at),
INDEX idx_status_paid (status, paid_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
第一部分:4 种 JOIN 用 Venn 图文字版讲清
下面 4 条 SQL 的结果差异,用「用户表(左) 和 订单表(右) ON users.id = orders.user_id」说明。
1. INNER JOIN = 交集(两边都有才保留)
SELECT u.nickname, o.amount, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
结果:只保留「有下单记录的用户」×「该用户的订单」。一个用户如果没有任何订单,不会出现在结果里; 一条订单如果 user_id 在 users 表里找不到(脏数据),也不会出现。
2. LEFT JOIN = 左表全保留,右表能匹配就填,匹配不到填 NULL
SELECT u.nickname, SUM(o.amount) AS pay_total
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id AND o.status = 2 -- ← 注意:status 条件写 ON 里
GROUP BY u.id;
结果:所有用户都出现,无论他有没有订单。
- 有已支付订单的用户:pay_total = 对应金额之和
- 没有已支付订单的用户:pay_total = NULL(你想让他显示 0?套
COALESCE(SUM(o.amount), 0))
🔥 LEFT JOIN 最常见坑:条件写 ON 还是 WHERE 完全两码事
-- A 写法:status = 2 写 ON
SELECT u.nickname, o.amount
FROM users u LEFT JOIN orders o
ON o.user_id = u.id AND o.status = 2;
-- 结果:所有用户都保留,没支付2订单的用户 amount=NULL
-- B 写法:status = 2 写 WHERE
SELECT u.nickname, o.amount
FROM users u LEFT JOIN orders o
ON o.user_id = u.id
WHERE o.status = 2;
-- 结果:只有「支付成功的订单」存在的那几行出现,和 INNER JOIN 结果一样!
-- 因为 WHERE 在 JOIN 之后执行,NULL = 2 的判断是 NULL(非真),那些行被排除了。
原则:**想过滤右表的条件、且想让左表没匹配的行保留为 NULL,就必须写在 ON 里。**写 WHERE 会把 LEFT JOIN 变成 INNER JOIN 效果。
3. RIGHT JOIN = 右表全保留,左表匹配不到填 NULL
和 LEFT JOIN 对称,A RIGHT JOIN B 等价 B LEFT JOIN A。实际项目中很少有人写 RIGHT JOIN(读起来绕),全改成 LEFT JOIN 就行。
4. FULL OUTER JOIN = 并集(两边都保留,不匹配那侧 NULL)
MySQL 没有原生 FULL JOIN 语法,用 LEFT + RIGHT 的结果 UNION 模拟(UNION 会去重):
SELECT u.nickname, o.amount
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.nickname, o.amount
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;
第二部分:InnoDB B+Tree 索引 + 最左前缀原则
主键索引(Primary Key)和二级索引(Secondary Index)的叶子节点存什么
InnoDB 是「索引组织表」:
- 主键索引(聚簇索引) 叶子页 = 完整一整行数据。按主键物理排序。
- 二级索引(普通/唯一/联合索引) 叶子页 =「索引列的值 + 主键值」。想拿到其他列必须回表(再查一次主键索引)。
这就是为什么「覆盖索引」能快:如果 SELECT 的列全在二级索引里,就不用回表了。
联合索引最左前缀原则:一个索引多种用法
INDEX(a, b, c) 建了这 1 个联合索引,实际上下面 3 种 WHERE 都能用得上:
| WHERE 条件 | 能不能用 (a,b,c) 索引? | 实际用到了哪些列? |
|---|---|---|
a = 1 | ✅ | a 列 |
a = 1 AND b = 2 | ✅ | a, b 列 |
a = 1 AND b = 2 AND c = 3 | ✅ | a, b, c 列 |
b = 2 AND c = 3 | ❌ | 一根毛都用不到(跳过了最左的 a,索引树找不到入口) |
a = 1 AND c = 3 | ⚠️ | 只用 a 列,扫到 a=1 的一堆记录后逐个过滤 c=3(不能跳到 c) |
a = 1 AND b > 2 AND c = 3 | ⚠️ | 用 a 列 + b 的范围,c 列用不到(b 是范围查询,之后的列跳过) |
让中间列索引用不上的坏兄弟:> < >= <= BETWEEN LIKE 'xxx%'(前缀匹配) —— 只要中间某列用了这些,后面的列就退出索引。
列顺序是建联合索引时唯一要关心的事,等值过滤列放前面,范围放后面,高选择性(区分度大)的放前面。
第三部分:EXPLAIN 只看 4 列就够
执行:EXPLAIN SELECT ... 会出一张 10+ 列的表。90% 的慢查询定位只看 4 列:type、key、rows、Extra。
type:联接类型(从好到坏)
| type | 含义 | 合格线 |
|---|---|---|
| const / eq_ref | 主键/唯一键精准查一行 | 🌟 |
| ref | 二级索引等值匹配 | ✅ |
| range | 索引范围扫描(IN / BETWEEN / > / <) | ✅ |
| index | 扫描整棵二级索引树(比 ALL 好一点,但还是全扫) | ⚠️ 看数据量 |
| ALL | 全表扫(主键树整棵扫) | ❌ 必须优化 |
你的慢查询 type 是 ALL?先看是不是没加索引,或者 WHERE 写法让索引失效了。
key:MySQL 最终选中的索引
possible_keys = 候选有哪些;key = 优化器最后选的那条。
如果 possible_keys 里有你建的索引,但 key 却是 NULL → 要么数据量太少 MySQL 觉得全扫更快,要么统计信息不准(ANALYZE TABLE t; 刷新一下)。
如果你觉得该走 A 索引但走了 B,可以 FORCE INDEX(idx_xxx) 强行走(只建议临时排查用,不要留到生产代码)。
rows:预估要扫多少行
是个估算值(不是真实值),但量级很有参考。 rows = 100000,实际只返回 10 行?说明索引没选对,扫了 10 万行才过滤出 10 条,典型「Using where」。
Extra:一些非常关键的附加信息
| Extra 内容 | 含义 | 是否危险 |
|---|---|---|
| Using where | Server 层在收到存储引擎返回的行后再做 WHERE 过滤 | ⚠️ 配合 rows 大就是危险 |
| Using index | 覆盖索引!不需要回表,这是好东西 | ✅ |
| Using index condition | ICP(索引下推),MySQL 5.6+ 自动用 | ✅ |
| Using filesort | ORDER BY 没用到索引,在内存或磁盘排一遍序 | ❌ 一定要看 |
| Using temporary | GROUP BY / DISTINCT 用了临时表 | ❌ 一定要看 |
例子:
Extra: Using where; Using index;
→ Using where 说明 WHERE 有一部分不能用索引(Server 层又过滤了一遍),但同时 Using index 说明用了覆盖索引,总体还 OK。
Extra: Using where; Using filesort;
→ WHERE 没完全走索引,ORDER BY 也没走(额外排序了),典型需要优化的慢查询。
第四部分:覆盖索引 + 不要 SELECT *
有联合索引 INDEX(user_id, created_at)。下面这条 SQL 能走覆盖索引吗?
-- 语句 1:不回表 ✅
SELECT user_id, created_at
FROM orders
WHERE user_id = 123;
-- 叶子节点直接就有 (user_id, created_at, id),SELECT 的两列都在里面,Extra = Using index
-- 语句 2:需要回表 ⚠️
SELECT *
FROM orders
WHERE user_id = 123;
-- SELECT * 要 amount / status / paid_at,这些不在二级索引里,
-- 每条都得拿叶子里的主键 id 再查一次聚簇索引 = 回表
结论:
- 尽量写具体的列名,不写
SELECT *。真要所有列也不是不能写,但你要知道它破坏了覆盖索引的可能。 - 如果某条高频查询就是要「user_id + amount」,可以直接建
INDEX(user_id, amount)让它变成覆盖索引。
常见问题 FAQ
Q1. LIKE '%xxx'(前缀有通配符)能走索引吗?
不能。B+Tree 是按前缀有序的,你第一位就是 %,不知道从哪个节点下钻,只能全扫。 业务如果需要「后缀匹配」「模糊搜任意位置」,请走专用搜索引擎(Elasticsearch / MeiliSearch),不要在 MySQL 里硬扛。
Q2. OR 两边的列都有索引,为什么 EXPLAIN 显示没用到?
MySQL 5.7+ 有 Index Merge 优化,部分情况会同时用两个索引然后合并。但大多数场景 OR 会让优化器直接选全扫。
改法:把 WHERE a = 1 OR b = 2 改写成两个 SELECT UNION 各走各的索引。
Q3. 建了索引 (status, paid_at),为什么 WHERE paid_at > '2026-08-01' 不走它?
最左前缀原则:你没给最左列 status 限定,status 从 1 到 4 所有子树都要扫描,MySQL 觉得不如全扫快。
如果你这条查询就是经常要按 paid_at 查且不带 status,单独建 INDEX(paid_at)。
Q4. 字符串列存手机号,用 WHERE phone = 13800000000(数字)为什么不走 phone 的 UNIQUE 索引?
类型隐式转换:phone 是 VARCHAR,右边常量是数字。MySQL 把 phone 转成数字再比较,索引上的值就不是原来的顺序了,没法用。
改:WHERE phone = '13800000000'(字符串常量)。
更正记录
- 2026-08-09:首版发布,MySQL 8.0.39 实跑。