索引是 MySQL 性能优化的核心利器，合理使用索引能让查询速度提升数十倍，滥用则会拖慢写入性能。本文围绕图片中的知识点，从优缺点、设计原则、创建 / 删除、测试验证等维度，带你彻底掌握 MySQL 索引。

---

一、索引核心基础

1. 什么是索引？

索引是帮助 MySQL 高效获取数据的排好序的数据结构，本质是「以空间换时间」：通过额外存储索引结构，大幅减少查询时的磁盘 I/O 次数。MySQL 最常用的索引结构是 B+ 树（图片第 9 点），它的优势：

• 多路平衡查找树，层级少（3-4 层即可支撑千万级数据）
• 叶子节点存储完整数据，非叶子节点仅存索引键
• 叶子节点形成双向链表，支持范围查询

---

2. 索引的优缺点

维度	内容
优点	1. 大幅提升查询速度，避免全表扫描2. 加速排序、分组、去重操作3. 唯一索引可保证数据唯一性
缺点	1. 占用额外磁盘空间2. 降低写入性能（插入 / 更新 / 删除需维护索引）3. 索引需要维护，增加数据库开销

---

3. 索引设计原则

1. 最左前缀原则：联合索引按「左到右」顺序匹配，查询条件必须包含最左列
2. 选择性优先：优先给区分度高（重复值少）的字段加索引（如手机号 > 性别）
3. 避免过度索引：不是索引越多越好，冗余索引会拖慢写入
4. 覆盖索引优化：让索引包含查询所需所有字段，避免回表
5. 避免索引失效：不在索引列上做计算、函数、模糊查询（%xxx）
6. 小表大字段不加索引：小表全表扫描更快，大字段索引占用空间大

---

4. 索引分类

分类维度	类型	说明
数据结构	B+ 树索引、哈希索引、全文索引	InnoDB 默认 B+ 树
逻辑分类	普通索引、唯一索引、主键索引、联合索引、前缀索引	主键索引是特殊的唯一索引
物理分类	聚集索引（InnoDB 主键）、非聚集索引	InnoDB 主键为聚集索引，数据与索引共存

---

二、索引的创建、查看与删除

准备测试表

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    phone VARCHAR(11) NOT NULL,
    class VARCHAR(10) NOT NULL
) ENGINE=InnoDB;

-- 插入10000条测试数据（用于性能测试）
INSERT INTO student (name, age, phone, class)
SELECT 
    CONCAT('用户', FLOOR(RAND()*10000)),
    FLOOR(RAND()*100),
    CONCAT('138', FLOOR(RAND()*100000000)),
    CONCAT('班级', FLOOR(RAND()*10))
FROM information_schema.tables1, information_schema.tables2
LIMIT 10000;

5. 创建索引的方法

（1）创建表时创建索引

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引（自动创建）
    name VARCHAR(20) NOT NULL,
    phone VARCHAR(11) NOT NULL UNIQUE, -- 唯一索引
    age INT NOT NULL,
    INDEX idx_age (age), -- 普通索引
    INDEX idx_name_age (name, age) -- 联合索引
);

（2）表已存在时创建索引

-- 1. CREATE INDEX 语法（推荐）
CREATE INDEX idx_phone ON student(phone); -- 普通索引
CREATE UNIQUE INDEX idx_phone_unique ON student(phone); -- 唯一索引
CREATE INDEX idx_name_age ON student(name, age); -- 联合索引
CREATE INDEX idx_name_prefix ON student(name(5)); -- 前缀索引（针对长字符串）

-- 2. ALTER TABLE 语法
ALTER TABLE student ADD INDEX idx_age(age);
ALTER TABLE student ADD UNIQUE INDEX idx_phone(phone);

6. 查看索引

-- 查看表的所有索引
SHOW INDEX FROM student;
-- 或
SHOW INDEXES FROM student;

关键字段说明：

• Key_name：索引名（PRIMARY 为主键索引）
• Column_name：索引对应的字段
• Non_unique：0 为唯一索引，1 为普通索引
• Seq_in_index：联合索引中的顺序（1 为最左列）

---

7. 索引测试（验证索引效果）

测试一：不同查询的索引命中情况

（1）查询主键字段（自动命中主键索引）

EXPLAIN SELECT * FROM student WHERE id = 100;

EXPLAIN 结果：type 为 const，key 为 PRIMARY，命中主键索引，性能极高。

（2）查询带索引字段（命中普通索引）

-- 先给phone加索引：CREATE INDEX idx_phone ON student(phone);
EXPLAIN SELECT * FROM student WHERE phone = '13812345678';

EXPLAIN 结果：type 为 ref，key 为 idx_phone，命中索引，避免全表扫描。

（3）查询不带索引字段（全表扫描）

EXPLAIN SELECT * FROM student WHERE class = '班级1';

EXPLAIN 结果：type 为 ALL，key 为 NULL，执行全表扫描，性能差（数据量越大越明显）。

（4）结论

• 主键、带索引字段查询：命中索引，速度快
• 无索引字段查询：全表扫描，速度慢
• 可通过 EXPLAIN 分析索引命中情况，优化查询

---

8. 删除索引

（1）删除语法

-- 方法一：DROP INDEX 语法（推荐）
DROP INDEX 索引名 ON 表名;

-- 方法二：ALTER TABLE 语法
ALTER TABLE 表名 DROP INDEX 索引名;

（2）示例

-- 方法一：删除普通索引
DROP INDEX idx_age ON student;
-- 删除唯一索引
DROP INDEX idx_phone ON student;

-- 方法二：删除联合索引
ALTER TABLE student DROP INDEX idx_name_age;

注意：主键索引无法直接删除，需先删除主键约束。

---

三、索引的关键问题

9. 索引结构选择 B+ 树（核心原理）

为什么 MySQL 选择 B+ 树作为默认索引结构，而非二叉树、B 树、哈希表？

1. 二叉树：极端情况退化为链表，查询效率 O (n)
2. B 树：非叶子节点存储数据，层级多，范围查询效率低
3. 哈希表：仅支持等值查询，不支持范围查询、排序
4. B+ 树：
   • 层级少，磁盘 I/O 次数少
   • 叶子节点双向链表，完美支持范围查询
   • 非叶子节点仅存索引键，内存占用少，缓存命中率高

---

10. 什么情况下不适用索引？（避坑指南）

以下场景加索引不仅无效，还会拖慢性能：

1. 数据量小的表：全表扫描比走索引更快
2. 重复值多的字段：如性别（男 / 女）、状态（0/1），区分度极低
3. 频繁更新的字段：每次更新都要维护索引，写入性能骤降
4. 长文本字段：如 TEXT、LONGTEXT，索引占用空间大，维护成本高
5. 索引列参与计算 / 函数：如 WHERE YEAR(create_time) = 2024（索引失效）
6. 模糊查询以 % 开头：如 WHERE name LIKE '%张三'（索引失效）
7. OR 条件未全加索引：如 WHERE id=1 OR age=20（age 无索引则全表扫描）
8. IS NULL/IS NOT NULL：部分场景下索引失效，需具体分析

---

四、综合实战：索引优化示例

1. 联合索引的最左前缀测试

-- 创建联合索引：name, age, class
CREATE INDEX idx_name_age_class ON student(name, age, class);

-- 1. 命中索引（包含最左列name）
EXPLAIN SELECT * FROM student WHERE name = '用户1' AND age = 20;
-- 2. 命中索引（仅最左列name）
EXPLAIN SELECT * FROM student WHERE name = '用户1';
-- 3. 索引失效（不包含最左列name）
EXPLAIN SELECT * FROM student WHERE age = 20 AND class = '班级1';

2. 覆盖索引优化（避免回表）

-- 普通查询：需回表查询数据
EXPLAIN SELECT * FROM student WHERE name = '用户1';
-- 覆盖索引：索引包含所有查询字段，无需回表
EXPLAIN SELECT id, name, age FROM student WHERE name = '用户1';

优化效果：覆盖索引 type 为 ref，Extra 为 Using index，性能提升明显。

---

五、核心总结（面试速记）

1. 索引本质：B+ 树结构，以空间换时间，加速查询
2. 优缺点：提升查询速度，降低写入性能
3. 设计原则：最左前缀、选择性优先、避免过度索引
4. 核心操作：CREATE INDEX 创建、SHOW INDEX 查看、DROP INDEX 删除
5. 验证方法：EXPLAIN 分析索引命中情况
6. 避坑关键：区分度低、频繁更新、长字段不加索引，避免索引失效
7. 结构选择：B+ 树是最优选择，支持范围查询、层级少