索引与查询性能
何时建索引、EXPLAIN 入门。
25 分钟
索引与查询性能
这一课你将能做到
- 亲手给 10 万行的表建索引，用 EXPLAIN 对比建前建后的执行计划差异
- 建联合索引并用实验验证「最左前缀原则」，说出三类索引失效写法
- 说出 B+ 树、聚簇索引、回表、覆盖索引这几个高频面试词的准确含义
前置：第一课建库脚本已跑（本课会把 student 表撑到 10 万行，结束给复原提示）。本课在 sql_practice 执行。
为什么要学
数据到 10 万行，没有索引的查询就是整表逐行翻。接口慢、面试问索引、DBA 让你加索引——这一课把三件事一次打通：什么时候该建索引、怎么建联合索引、怎么用 EXPLAIN 判断查询走没走索引。EXPLAIN 不是 DBA 专属，后端工程师上线前自己跑一遍，是最便宜的性能保险。
先建立图像
没有索引：整表扫描 有索引：B+ 树目录
┌──────────────┐ 根节点 → 分支 → 叶子
│ row1 ✗ │ 3~4 层就能定位 10 万行中的 1 行
│ row2 ✗ │ 叶子层还是双向链表 → 范围查询顺着扫
│ … │
│ row99999 ✓ │ ← 翻完才知道 索引列有序 + 树结构 = 二分定位
└──────────────┘
两个必须建立的概念：
- 聚簇索引：InnoDB 的主键索引，叶子节点直接存整行数据——表本身就是按主键组织的一棵 B+ 树
- 二级索引：其他索引，叶子存的是「索引列 + 主键值」，查到之后还要拿主键回聚簇索引取整行，这一步叫回表
跟着做
步骤 1：把 student 表撑到 10 万行
用 MySQL 8 的递归 CTE 造数，一段 SQL 搞定：
SET SESSION cte_max_recursion_depth = 1000000; -- 默认只允许递归 1000 层
INSERT INTO student (name, email, city, vip, balance)
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT CONCAT('user', n),
CONCAT('user', n, '@example.com'),
ELT(n % 3 + 1, '深圳', '杭州', '北京'),
n % 2,
ROUND(RAND() * 200, 2)
FROM seq;
SELECT COUNT(*) FROM student; -- 100007
步骤 2：先看没有索引时查询长什么样
EXPLAIN SELECT * FROM student WHERE city = '深圳' AND vip = 1;
重点看输出里这几列（格式化展示，数值是近似值）：
type: ALL ← 全表扫描！
key: NULL ← 没用任何索引
rows: 57000 ← 预估要扫的行数
Extra: Using where
type 的好坏梯度（从差到好）：ALL < index < range < ref < eq_ref < const。线上查询出现 ALL 且行数大，基本就是要优化的信号。
步骤 3：建索引，再看一次
CREATE INDEX idx_student_city ON student(city);
EXPLAIN SELECT * FROM student WHERE city = '深圳' AND vip = 1;
-- type: ref, key: idx_student_city, rows: ~34000
从「扫 5.7 万」到「按索引直接定位深圳的 3.4 万行」。但 vip 的过滤仍发生在取回数据之后——这正好引出联合索引。
步骤 4：联合索引与最左前缀原则
CREATE INDEX idx_student_city_vip ON student(city, vip);
DROP INDEX idx_student_city ON student; -- 联合索引能覆盖它，单列的留着纯属浪费
-- 场景 A：等值查询两列 → 完整命中
EXPLAIN SELECT * FROM student WHERE city = '深圳' AND vip = 1;
-- type: ref, key: idx_student_city_vip, ref: const,const, rows: ~17000
-- 场景 B：只有 city（联合索引的最左列）→ 也能用
EXPLAIN SELECT * FROM student WHERE city = '深圳';
-- key: idx_student_city_vip ✓
-- 场景 C：只有 vip（跳过最左列）→ 索引用不上
EXPLAIN SELECT * FROM student WHERE vip = 1;
-- type: ALL, key: NULL ✗