下面给出一份「看完就能落地」的 MySQL 索引学习全景文章，覆盖
①索引概述 → ②索引结构 → ③索引分类 → ④索引语法 → ⑤SQL 性能分析 → ⑥索引使用实战 → ⑦设计原则与踩坑，
并穿插「随手笔记 + 可运行代码 + 完整案例」，一条线学透。

---

1. 索引概述（是什么 & 为什么）

定义：索引是排好序的、帮助快速检索数据的数据结构，存储在磁盘，由存储引擎层实现。

• 类比：字典音序表、书籍目录。
• 代价：占用磁盘空间、降低 DML 速度（需要同步维护）。
• 权衡：OLTP 场景“读 >> 写”，合理索引 ROI 极高。

代码感受 10 万行差距

-- 建表时故意不加索引
CREATE TABLE t_user(
  id INT PRIMARY KEY,          -- 只有主键索引
  name VARCHAR(50),
  age  INT
);
INSERT INTO t_user SELECT * FROM t_user;  -- 重复插入到约 10 万行

-- 无索引全表扫描
mysql> SELECT * FROM t_user WHERE age=30;
+------+------+------+
| id   | name | age  |
+------+------+------+
... 1000 行
-- 耗时 0.12 s

-- 对 age 建普通索引
ALTER TABLE t_user ADD INDEX idx_age(age);

-- 同样语句
-- 耗时 0.001 s（120 倍差距）

结论：索引是“空间换时间”的典型工程实践。

---

2. 索引结构（底层长什么样）

2.1 主流结构对比

结构	特点	引擎支持	适用场景
B+Tree（多路平衡树）	有序、范围查询友好、扇出高、IO 少	InnoDB/MyISAM 默认	95% 场景
Hash	仅 = 查询、O(1)、无序	Memory 引擎显式支持	等值热点缓存
R-Tree	空间索引	MyISAM	GIS
Full-text	倒排	InnoDB≥5.6	大文本关键词

2.2 为什么选 B+Tree

• 高度低：1 000 万行仅 3-4 层 → 3-4 次 IO。
• 叶子双向链表：范围扫描无需回溯。
• 非叶子节点只存键 → 一个页（16 KB）可存 1 200+ 键，扇出大。

手绘 3 层 B+Tree 示意图（文字版）

 [10 |  20 |  30  |  40  |  50 ]          ← 根（非叶子）
  /      |      |    |     \
[1-9][10-19][20-29][30-39][40-50]  ← 叶子（含行/主键）
↑→ 双向指针 →↑

---

3. 索引分类（面试常考）

3.1. 按逻辑

• 主键索引（PRIMARY）
• 唯一索引（UNIQUE）
• 普通索引（KEY / INDEX）
• 全文索引（FULLTEXT）
• 组合索引（复合索引）
• 前缀索引（对 TEXT/VARCHAR 前 N 字符）

3.2. 按物理

• 聚簇索引（Clustered）：叶子=整行数据，InnoDB 必有，只能有一个。
• 二级索引（Secondary）：叶子=主键值，需要回表。

3.3. 按数据结构

B+Tree、Hash、R-Tree、Full-text。

---

4. 索引语法（速查表）

-- 1. 建表时一起建
CREATE TABLE article(
  id INT PRIMARY KEY,                       -- 聚簇
  user_id INT NOT NULL,
  title VARCHAR(200),
  content TEXT,
  UNIQUE uk_user_title(user_id,title),      -- 组合唯一
  INDEX idx_user(user_id),                  -- 普通
  FULLTEXT ft_content(content)              -- 全文
) ENGINE=InnoDB;

-- 2. 事后加
ALTER TABLE article ADD INDEX idx_ctime(create_time);
CREATE INDEX idx_title ON article(title(20)); -- 前缀

-- 3. 查看
SHOW INDEX FROM article\G

-- 4. 删除
DROP INDEX idx_user ON article;
ALTER TABLE article DROP INDEX uk_user_title;

---

5. SQL 性能分析工具箱

5.1 执行计划 EXPLAIN（最常用）

EXPLAIN SELECT * FROM article WHERE user_id=123\G

关键字段

• type：ALL（全表） < index < range < ref < eq_ref < const
• key：实际用到的索引
• rows：预估扫描行数
• Extra：Using index（覆盖）、Using filesort（需排序）、Using temporary（临时表）

5.2 优化器 trace（看选索引过程）

SET optimizer_trace="enabled=on";
SELECT ...;                 -- 你的SQL
SELECT * FROM information_schema.optimizer_trace\G
SET optimizer_trace="enabled=off";

5.3 会话级 profiling（简单耗时）

SET profiling=1;
SELECT ...;
SHOW PROFILE FOR QUERY 1;

5.4 官方 Performance Schema（生产推荐）

UPDATE performance_schema.setup_consumers
SET enabled='YES' WHERE name LIKE '%statements%';
-- 查询最耗时 SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e9 AS avg_ms
FROM events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5\G

---

6. 索引使用实战（正反面案例）

案例 1 覆盖索引减少回表

需求：订单表 2 000 万行，常按 user_id 查订单号。

-- 表结构
CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  user_id INT,
  order_no VARCHAR(32),
  amount DECIMAL(10,2),
  status TINYINT,
  create_time DATETIME
);

-- 最初只有主键，查询
SELECT order_no FROM orders WHERE user_id=12345;
-- type=ALL  2 s

-- 建组合索引并覆盖
ALTER TABLE orders ADD INDEX idx_user_no(user_id, order_no);
-- 同样语句 type=ref  Extra=Using index  10 ms

Explain 对比

key: idx_user_no
rows: 80
Extra: Using index   -- 无需回表

案例 2 最左前缀失效

ALTER TABLE orders ADD INDEX idx_u_s(user_id, status);

-- 能用到
SELECT * FROM orders WHERE user_id=123;
SELECT * FROM orders WHERE user_id=123 AND status=1;

-- 用不到
SELECT * FROM orders WHERE status=1;      -- 跳过最左列

案例 3 范围列后断组合索引

INDEX idx_range(user_id, create_time, order_no)

-- 只用前两列
SELECT * FROM orders
WHERE user_id=123
  AND create_time BETWEEN '2025-01-01' AND '2025-01-31'
  AND order_no='N123';   -- 第三列失效

案例 4 隐式转换导致索引失效

-- order_no 是 varchar，但用数字
SELECT * FROM orders WHERE order_no=200601019999;  
-- 会隐式 CAST(col AS DECIMAL) → 函数包裹 → 索引失效
-- 改为字符串
SELECT * FROM orders WHERE order_no='200601019999';

案例 5 索引下推（ICP）

Extra 出现 Using index condition 代表引擎层先过滤，减少回表。

INDEX idx_user_status(user_id, status)

SELECT * FROM orders
WHERE user_id BETWEEN 10000 AND 20000
  AND status=1;

---

7.索引设计原则（checklist）

1. 数据量 > 1 000 行 & 查询频次 > 更新频次 → 值得建。
2. 选择性公式：SELECT COUNT(DISTINCT col)/COUNT(*) > 0.2 越高越好。
3. 联合索引把选择性高的放最左；范围列放最后。
4. 覆盖索引优先：让 Extra 出现 Using index。
5. 控制总数：单表建议 ≤ 6 个二级索引；过多会拖慢写入。
6. 长文本用前缀索引：ALTER TABLE tb ADD INDEX idx_url(url(64));
7. 业务唯一列一定加 UNIQUE，既保数据又送索引。
8. 禁止对“性别/状态”这类低选择度列单独建索引。
9. 线上大表加索引用 pt-online-schema-change 或 gh-ost，避免锁表。
10. 发布前 EXPLAIN 所有新 SQL，拒绝 ALL 和 Using filesort。

---

8. 小结脑图（文字版）

MySQL 索引
├─ 概述：排好序的快速查找结构，空间换时间
├─ 结构：B+Tree 为主，Hash/R-Tree/Full-text 为辅
├─ 分类：主键/唯一/普通/组合/前缀/全文/聚簇/二级
├─ 语法：CREATE/ALTER/DROP + SHOW INDEX
├─ 性能分析：EXPLAIN → trace → PFS
├─ 实战：覆盖/最左/范围/隐转/ICP
└─ 设计：高选择性、最左、覆盖、控制个数、在线工具

把本文 SQL 全部跑一遍，你会得到：

• 120 倍提速的直观体验
• 一份属于自己的 EXPLAIN 清单
• 不再怕“索引失效”面试追问。

Happy indexing!