索引与查询性能

这一课你将能做到

1. 亲手给 10 万行的表建索引，用 EXPLAIN 对比建前建后的执行计划差异
2. 建联合索引并用实验验证「最左前缀原则」，说出三类索引失效写法
3. 说出 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 ✗