MySQL 索引
一、索引概述
什么是索引
可以把一张没有索引的表想象成一本没有目录的字典：想找"赢"字，只能一页一页从头翻到尾（全表扫描）。而索引就是字典的目录——先查目录找到页码，直接翻到那一页，速度天差地别。
简单说：索引是帮助 MySQL 高效获取数据的数据结构。它用额外的存储空间和写入时的维护成本，换取查询速度的大幅提升。
索引的优缺点
优点：
- 大幅提高查询速度，这是最核心的价值。
- 通过唯一索引保证数据唯一性。
- 加速排序（
ORDER BY）和分组（GROUP BY）。
缺点：
- 占磁盘空间：索引本身也是数据，也要存文件。
- 拖慢写入：
INSERT、UPDATE、DELETE时不仅要改数据，还要同步维护索引结构。
结论：索引不是越多越好，只给经常查询的字段建索引。
二、索引结构
MySQL 索引底层主要有两种数据结构：B+ 树和哈希。
1. B+ 树（InnoDB 默认，绝对主流）
B+ 树是一种"矮胖"的多叉平衡树：
[50] ← 根节点（只存键值，不存数据）
/ \
[20, 35] [65, 80] ← 非叶子节点（只存键值，做导航）
/ | \ / | \
[..][..][..][..][..][..] ← 叶子节点（存数据，且用链表相连）
←——————— 双向链表 ———————→特点：
- 非叶子节点只存键值，不存数据，所以一个节点能存更多键，树就很"矮"（通常 3~4 层就能存上千万条数据），查找只需 3~4 次磁盘 IO。
- 叶子节点存数据，并且所有叶子节点用双向链表串起来。所以范围查询（
WHERE id > 20 AND id < 80）特别快——找到起点后顺着链表扫过去就行。 - 所有数据查询都要从根走到叶子，查询效率稳定。
为什么不用二叉树 / B 树 / 哈希？
- 二叉树：数据量大时树很高，磁盘 IO 次数多；遇到自增主键还会退化成链表。
- B 树：非叶子节点也存数据，一个节点能装的键变少，树更高；且叶子节点之间没有链表，范围查询效率低。
- 哈希：等值查询虽快，但不支持范围查询和排序。
2. 哈希（Hash）
- 对索引列做一次哈希计算，直接定位到数据位置。
- 等值查询（
=、IN）速度极快，理论上 O(1)。 - 缺点：不支持范围查询、不支持排序、不支持模糊查询，哈希冲突时性能下降。
- Memory 引擎默认支持哈希索引；InnoDB 有自适应哈希索引（内部自动建立，用户不可控）。
3. 聚簇索引 vs 非聚簇索引（重要）
这是 InnoDB 里最容易被问到的概念：
- 聚簇索引（Clustered Index）：索引的叶子节点直接存放整行数据。InnoDB 的主键索引就是聚簇索引，一张表只有一个。按主键查数据，一次搞定。
- 非聚簇索引（二级索引 / 辅助索引）：叶子节点不存整行数据，只存索引列的值 + 主键值。用普通索引查到主键后，还要再查一次主键索引才能拿到完整数据，这个过程叫回表。
二级索引 [name='张三'] → 拿到主键 id=5 → 回表查主键索引 → 拿到整行数据覆盖索引：如果查询的字段在二级索引里全都有（比如 SELECT name FROM t WHERE name='张三'），就不需要回表了，效率更高。这是常见的优化手段。
三、索引分类
按功能逻辑分
| 类型 | 说明 |
|---|---|
| 主键索引 | 建表时的 PRIMARY KEY，唯一且非空，一张表只能有一个，InnoDB 中是聚簇索引 |
| 唯一索引 | UNIQUE，值不能重复，但允许 NULL，一张表可以有多个 |
| 普通索引 | 最常见的索引，没有任何限制，只为加速查询 |
| 全文索引 | FULLTEXT，用于文章内容的关键词搜索，替代低效的 LIKE '%xx%' |
按字段数量分
- 单列索引：只包含一个字段。
- 联合索引（组合索引）：包含多个字段，如
(name, age)。
联合索引必须理解最左前缀原则：索引按字段定义顺序排列，查询必须从最左边的字段开始用，索引才生效。
sql
-- 建了联合索引 (name, age, city)
WHERE name = '张三' -- ✅ 生效
WHERE name = '张三' AND age = 20 -- ✅ 生效
WHERE age = 20 -- ❌ 不生效（跳过了最左的 name）
WHERE name = '张三' AND city = '北京' -- ⚠️ name 生效，city 用不上（中间断了）类比：查字典按"拼音 + 笔画"排目录，你得先按拼音找，笔画才有意义。
按存储形式分
- 聚簇索引（主键索引）
- 非聚簇索引（二级索引，含回表 / 覆盖索引）
四、索引语法
创建索引
建表时创建：
sql
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
name VARCHAR(50) NOT NULL,
email VARCHAR(100),
id_card VARCHAR(18),
UNIQUE KEY uk_email (email), -- 唯一索引
KEY idx_name (name), -- 普通索引
KEY idx_name_card (name, id_card) -- 联合索引
) ENGINE = InnoDB;建表后追加：
sql
-- 方式一：CREATE INDEX
CREATE INDEX idx_name ON user(name);
CREATE UNIQUE INDEX uk_email ON user(email);
-- 方式二：ALTER TABLE
ALTER TABLE user ADD INDEX idx_name (name);
ALTER TABLE user ADD UNIQUE KEY uk_email (email);
ALTER TABLE user ADD PRIMARY KEY (id); -- 添加主键查看索引
sql
SHOW INDEX FROM user;删除索引
sql
DROP INDEX idx_name ON user;
-- 或
ALTER TABLE user DROP INDEX idx_name;验证索引是否生效：EXPLAIN
在 SQL 前加 EXPLAIN，看 key 列是否用到了索引、rows 列扫描了多少行：
sql
EXPLAIN SELECT * FROM user WHERE name = '张三';五、索引失效的常见场景（避坑）
建了索引不等于会用上，以下写法容易导致索引失效，退化为全表扫描：
- 对索引列做运算或函数操作：
WHERE YEAR(create_time) = 2026 - 模糊查询以
%开头：WHERE name LIKE '%三'（LIKE '张%'可以用索引） - 违反最左前缀原则（联合索引）
- 隐式类型转换：字段是 VARCHAR，查询时写数字不加引号
WHERE phone = 13800000000 - 使用
OR连接非索引列：WHERE name='张三' OR age=20（age 无索引则整体失效） - 数据量太少或区分度太低：优化器认为全表扫描更快时会主动放弃索引，这是正常行为
六、一句话总结
- 索引 = 字典目录，用空间和写入成本换查询速度。
- InnoDB 默认用 B+ 树；主键索引是聚簇索引，普通索引查询涉及回表，能用覆盖索引更好。
- 联合索引牢记最左前缀原则；写 SQL 时避开索引失效的常见写法，用
EXPLAIN验证。