MySQL 索引

总结：

索引是 MySQL 性能的生命线，80% 的慢 SQL 都能靠正确的索引解决。

一、索引是什么？解决什么问题？

索引是一种加速数据检索的数据结构，让查询：

从全表扫描 O(n)
变为树查找 O(log n)

📌 本质：空间换时间

二、MySQL 索引的底层结构（核心）

1️⃣ 为什么是 B+Tree？

InnoDB 使用 B+Tree 作为默认索引结构：

优势：

所有数据在叶子节点
非叶子节点只存键 -> 树更矮
叶子节点通过双向链表相连 -> 范围查询极快

📌 Hash 索引不适合：

不支持范围
不支持排序
不支持前缀匹配

2️⃣ 聚簇索引 vs 二级索引（必考）

🔹 聚簇索引（Clustered Index）

主键索引
叶子节点存整行数据
表数据物理顺序 = 主键顺序

PRIMARY KEY(id)

📌 一张表只有一个聚簇索引

🔹 二级索引（Secondary Index）

非主键索引
叶子节点存：索引列 + 主键值
查询需回表

KEY idx_name(name)

3️⃣ 回表（重要）

流程：

通过二级索引找到主键
再到主键 B+Tree 查询行数据

📌 回表 = 额外 IO

三、索引类型大全

类型	说明
PRIMARY	主键索引
UNIQUE	唯一索引
NORMAL	普通索引
FULLTEXT	全文索引
SPATIAL	空间索引

四、联合索引 & 最左前缀

KEY idx_a_b_c (a, b, c)

可用场景：

WHERE 条件	是否使用索引
a=1	✅
a=1 AND b=2	✅
a=1 AND b=2 AND c=3	✅
b=2	❌
a=1 AND c=3	✅（c 不能再继续）

📌 必须从最左列开始

五、索引的典型使用场景

WHERE 精确匹配

WHERE id = 10

JOIN 条件

ON order.user_id = user.id

ORDER BY / GROUP BY

ORDER BY create_time DESC

覆盖索引（极致优化）

SELECT id, name FROM user WHERE name='Tom';

Extra：

Using index

六、索引何时会失效？（必考）

❌ 1. 对索引列做函数/运算

WHERE DATE(create_time) = '2024-01-01'

❌ 2. 隐式类型转换

WHERE phone = 13800138000  -- phone 是 VARCHAR

❌ 3. 左模糊查询

LIKE '%abc'

❌ 4. OR 混用不同索引

WHERE a=1 OR b=2

❌ 5. NOT IN / != / <>

WHERE status != 1

七、索引下推（ICP，MySQL 5.6+）

SELECT * FROM user
WHERE name='Tom' AND age=20;

如果只有 name 有索引：

以前：回表后再判断 age
现在：在索引层直接过滤 age

EXPLAIN：

Using index condition

📌 减少回表次数

八、索引与锁的关系（运维必懂）

⚠️ 没有索引的 UPDATE / DELETE：

全表扫描
全表 Next-Key Lock
阻塞所有写操作

✅ 有索引：

精确行锁
并发安全

九、索引设计最佳实践（经验总结）

✅ 主键设计

BIGINT UNSIGNED
单调递增（减少页分裂）

✅ 索引数量

不宜过多（写放大）
常用：3～5 个

✅ 联合索引顺序

区分度高的放前面
等值条件在前，范围在后

✅ 避免重复索引

KEY idx_a(a),
KEY idx_a_b(a,b)  -- idx_a 可删

十、EXPLAIN 如何判断索引好不好？

重点看：

字段	理想值
type	const / ref / range
key	命中索引
rows	很小
Extra	Using index / ICP

十一、常见索引问题

Q：为什么主键建议自增？

A：减少 B+Tree 页分裂，提高写入性能。

Q：索引越多越好吗？

A：否，影响 INSERT/UPDATE 性能。

Q：覆盖索引一定快吗？

A：是（避免回表）。

Q：索引能提高 INSERT 吗？

A：不能，反而变慢。

Q：什么时候用全文索引？

A：全文搜索，不适合 LIKE ‘%xxx%’

十二、索引相关 SQL 速查

-- 查看索引
SHOW INDEX FROM table;

-- 添加索引
ALTER TABLE t ADD INDEX idx_a(a);

-- 删除索引
ALTER TABLE t DROP INDEX idx_a;

-- 查看是否走索引
EXPLAIN SELECT ...

🔚 总结

索引不是“建了就快”，而是“建对才快”。