跳到主要内容

创建日期：2026-09-08 ｜ 最近更新：2026-09-08 本机 MySQL 8.4.0 实测；下方 EXPLAIN 均来自 shop 真实数据（300 顾客 / 5000 订单 / 8000 明细）。

MySQL 深潜 3：索引与 EXPLAIN——慢查询的答案都在这

索引是 MySQL 性能的第一杠杆：加对索引，一条查询从全表扫变成「直取几行」。这篇教你两件事：索引到底怎么工作（够用的直觉） + 用 EXPLAIN 看一条 SQL 有没有吃到索引。会看 EXPLAIN，你就超过大多数「只会写 SELECT」的人。

1. 索引的直觉：书的目录 & 数据的排序​

  • 没有索引：MySQL 一行行翻整张表（全表扫描）找你要的数据；
  • 有索引：像查字典，按**有序结构（B+ 树）**二分跳着找，几下命中。

核心认知：普通索引本质是「把某一列排好序的目录」，目录里存着指向真实行的指针。 所以：

  • 索引帮「按这列查」和「按这列排序/去重」；
  • 主键本身就是索引（聚簇索引，数据和索引在一起）；
  • 代价：每次写（INSERT/UPDATE/DELETE）都要同步维护索引 → 索引不是越多越好。

2. 怎么加索引（先会动手）​

-- 单列
CREATE INDEX idx_amount ON orders (amount);
-- 复合索引（列顺序重要，见 §4）
CREATE INDEX idx_status_cust ON orders (status, customer_id);
-- 唯一索引（保证不重复 + 加速查询）
CREATE UNIQUE INDEX uk_email ON customers (email);
-- 建表时：KEY idx_city (city)  同理
-- 删索引
DROP INDEX idx_amount ON orders;

加索引不用动业务代码，是最「性价比高」的优化手段。

3. EXPLAIN：让 MySQL 告诉你它怎么执行​

在 SELECT 前面加 EXPLAIN，看执行计划。只需要盯四个字段：type、key、rows、Extra。

实测 ① 命中索引：按外键列查​

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
type: ref
key:  idx_customer
rows: 16          ← 预计只碰 16 行
Extra: NULL

type=ref、key=idx_customer → 用了索引，扫 16 行。

实测 ② 没索引：全表扫（慢查询的根源）​

EXPLAIN SELECT * FROM orders WHERE amount > 4000;    -- 此刻 amount 还没有索引
type: ALL
key:  NULL
rows: 5000        ← 全表 5000 行一格格翻
Extra: Using where

type=ALL = 全表扫描。给它加索引后再看：

ALTER TABLE orders ADD KEY idx_amount (amount);
EXPLAIN SELECT * FROM orders WHERE amount > 4000;
type: range
key:  idx_amount
rows: 1
Extra: Using index condition    ← 用上索引，范围读

同样一条 SQL，从翻 5000 行 → 只读 1 行。这就是索引的威力。

type 好坏大致排序：const/ref（好）→ range（还行）→ ALL（全表，要警惕）。看到 ALL + rows 巨大，基本就是该加索引的信号。

实测 ③ 索引被「用不上」的经典情况​

EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2025-02-01';   -- created_at 本有索引
type: ALL
key:  NULL
rows: 5000

对索引列套了函数（DATE(col)），索引就失效 → 全表扫。正确姿势：改成范围：WHERE created_at >= '2025-02-01' AND created_at < '2025-02-02'。

实测 ④ EXPLAIN FORMAT=TREE：看整棵执行树​

EXPLAIN FORMAT=TREE
SELECT customer_id, COUNT(*) FROM orders WHERE status='paid' GROUP BY customer_id;
-> Table scan on <temporary>                                  ← 结果先放临时表
   -> Aggregate using temporary table
      -> Index lookup on orders using idx_status (status='paid')

一眼看懂：先走 idx_status 索引取出 paid 的行，再分组聚合（用临时表）。比格子版更直观，8.0 起可用。

4. 复合索引：最左前缀法则（高频面试 + 高频踩坑）​

建 (status, customer_id) 复合索引，等于同时拥有：

  • 能帮 WHERE status=…；
  • 能帮 WHERE status=… AND customer_id=…（从最左两列用起）；
  • 帮不了只查 customer_id 的查询（没用最左列 status）——这叫最左前缀法则。
-- 能用 (status, customer_id)
SELECT * FROM orders WHERE status='paid' AND customer_id=5;
-- 用不上这个复合索引（缺最左列 status）
SELECT * FROM orders WHERE customer_id=5;

设计建议：把「等值筛选最频繁」的列放最左；范围/排序列放后面。

5. 一张「要不要建索引」的决策卡​

场景建议
WHERE 高频且选择性好（city、user_id、status）建索引
查询列（SELECT 只取这些列，能覆盖）复合/覆盖索引，免回表
连接键（JOIN ... ON）两边都要有索引
排序 ORDER BY / 去重 DISTINCT建索引帮排序
低选择性列（如 boolean 只有两值）单独建意义小，复合索引里放前面配合用
写多读少的表 / 每列都建别乱建，维护成本 > 收益
函数/表达式包着列建了也常失效

复合索引的选择性经验：单独命中行太多（如 status 就 4 个值）时收益有限，通常和 customer_id 这类高选择性列组合成复合索引。

6. 索引失效的「五宗罪」（自查清单）​

  1. 对索引列用函数：DATE(col)= / LOWER(col)=；
  2. 隐式类型转换：WHERE varchar_col = 123（数字）→ 隐式转函数，失效；
  3. 模糊搜索前导通配：LIKE '%xx%'（前缀 LIKE 'xx%' 可以走）；
  4. 复合索引没从最左列用起；
  5. OR 连接的条件里有一个没索引列（可能退化为全表）。

遇到慢查询流程：EXPLAIN 看 type/key/rows → 命中 ALL 就找该不该加索引 → 确认没踩上面五条 → 加复合索引/改写法。

动手（用 shop）​

  1. EXPLAIN SELECT * FROM order_items WHERE product_id=5，看 type/key（有 idx_product）；
  2. 造一条 amount 大范围查询对比加索引前后 rows；
  3. 对 orders(customer_id) 试一次函数包列，观察退化成 ALL；
  4. 建 (status, customer_id) 复合索引，分别 EXPLAIN 两条 SQL 体会最左前缀。

自测​

  1. type=ALL 意味着什么？看到它第一反应是？
  2. 为什么对索引列用 DATE() 会失效？正确写法？
  3. 复合索引 (a,b) 能帮哪些查询？帮不了哪种？
  4. 索引的代价是什么？为什么不能每列都建？
  5. key 和 rows 分别说明什么？

下一篇：事务与隔离级别。