MySQL数据库—索引
一、索引基础
1. 什么是索引？
索引是一种数据结构，它通过对表中数据按照特定规则排序，帮助数据库快速定位和查询数据，本质上类似于字典的目录：先查目录找到页码，再翻到对应页找具体内容。
2. 为什么要使用索引？
- 核心目的：提升查询效率，在海量数据下避免全表扫描。
- ** trade-off**：查询效率提升的同时，会降低写入（INSERT/UPDATE/DELETE）效率，因为索引需要同步维护。
3. 索引的数据结构选型
不同数据结构的特性决定了其适用场景，MySQL 最终选择了B + 树作为索引的底层数据结构：
表格
| 数据结构 | 时间复杂度 | 优点 | 缺点 | MySQL 支持 |
|---|---|---|---|---|
| HASH | O(1) | 等值查询极快 | 不支持范围查询 | ❌ 不默认使用 |
| 二叉搜索树 | O(logN) | 结构简单 | 树高不可控，磁盘 I/O 次数多 | ❌ 不使用 |
| N 叉树 | O(logN) | 降低树高，减少 I/O | 节点数据冗余 | ⚠️ 过渡方案 |
| B + 树 | O(logN) | 支持范围查询，I/O 次数少，性能稳定 | 结构相对复杂 | ✅InnoDB 默认使用 |
二、B + 树与 MySQL 存储结构
1. B + 树核心特性
- 非叶子节点：只存储索引键和子节点指针，不存储真实数据，因此单个节点可以存储更多索引，大幅降低树高。
- 叶子节点：包含完整数据记录，且通过双向链表连接，天然支持范围查询和排序。
- 与 B 树的区别：
- B + 树数据只存在于叶子节点，B 树所有节点都存数据。
- B + 树叶子节点链表化，范围查询更高效。
- 相同树高下，B + 树能存储更多数据，查询路径更短。
2. MySQL 的页（Page）
- 页是 InnoDB 与磁盘交互的最小单位，默认大小为16KB。
- 数据和索引都以页为单位存储，利用局部性原理，一次 I/O 加载一页数据到内存，提升后续查询效率。
- 页结构：页头部（元信息）→ 数据行 → 页尾部（校验信息）。
3. B + 树在 MySQL 中的应用
- 每个 B + 树节点就是一个页，根节点、非叶子节点、叶子节点共同构成完整索引树。
- 聚簇索引（主键索引）的叶子节点直接存储完整行数据，辅助索引的叶子节点存储主键值，需要回表查询完整数据。
三、索引分类与创建
1. 索引分类
表格
| 索引类型 | 定义 | 特点 |
|---|---|---|
| 主键索引 (PRIMARY KEY) | 唯一标识表中记录，非空且唯一 | 一张表只能有一个，InnoDB 中是聚簇索引 |
| 唯一索引 (UNIQUE) | 列值必须唯一，允许一个 NULL | 业务字段有唯一性要求时使用 |
| 普通索引 (INDEX) | 最基础的索引类型 | 用于提升高频查询列的查询速度 |
| 复合索引 | 多个列共同组成的索引 | 遵循最左前缀原则，高效支持多条件查询 |
| 覆盖索引 | 索引包含查询所需的所有列 | 避免回表，查询效率极高 |
2. 自动创建索引
- 创建
PRIMARY KEY、UNIQUE、FOREIGN KEY约束时，MySQL 会自动创建对应索引。 - 未指定主键时，MySQL 会自动选择一个非空唯一列作为主键，若无则生成隐藏
ROW_ID作为主键。
3. 手动创建索引
3.1 主键索引
sql
-- 建表时指定 CREATE TABLE student ( id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20) NOT NULL ); -- 建表后添加 ALTER TABLE student ADD PRIMARY KEY (id);3.2 唯一索引
sql
-- 建表时指定 CREATE TABLE student ( id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20) UNIQUE ); -- 建表后添加 ALTER TABLE student ADD UNIQUE INDEX idx_name (name);3.3 普通 / 复合索引
sql
-- 建表时指定 CREATE TABLE student ( id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20), class_id bigint, INDEX idx_name_class (name, class_id) -- 复合索引 ); -- 建表后添加 CREATE INDEX idx_sno ON student (sno); ALTER TABLE student ADD INDEX idx_name (name);4. 删除索引
sql
-- 删除主键索引（需先移除 AUTO_INCREMENT） ALTER TABLE student MODIFY id bigint; ALTER TABLE student DROP PRIMARY KEY; -- 删除其他索引 ALTER TABLE student DROP INDEX idx_name;四、索引使用与优化
1. 索引生效规则
- 最左前缀原则：复合索引会按照创建顺序排序，查询时必须包含索引的最左列才能生效。
- 例：
idx(a,b,c)支持WHERE a=?、WHERE a=? AND b=?、WHERE a=? AND b=? AND c=?，不支持WHERE b=?。
- 例：
- 覆盖索引：查询的列都在索引中，无需回表查询，速度最快。
- 回表查询：通过辅助索引找到主键后，再通过主键索引查询完整行数据。
2. 索引失效场景
- 使用
LIKE '%xxx'（以通配符开头）。 - 对索引列进行函数 / 运算操作（如
WHERE YEAR(create_time) = 2024）。 - 类型隐式转换（如
WHERE id = '123'，id 是数字类型）。 - 复合索引未遵循最左前缀原则。
- 数据量过小，优化器选择全表扫描。
3. 查看与分析索引
sql
-- 查看表中所有索引 SHOW INDEX FROM student; -- 查看 SQL 执行计划，判断是否使用索引 EXPLAIN SELECT * FROM student WHERE name = '张三';type字段：ref/range表示使用了索引，ALL表示全表扫描。key字段：显示实际使用的索引名称。
五、最佳实践总结
- 选择合适的列创建索引：高频查询列、
WHERE条件列、JOIN关联列。 - 优先使用复合索引：替代多个单列索引，减少索引数量，提升查询效率。
- 避免过度索引：索引越多，写入性能越差。
- 使用覆盖索引：将查询所需列都包含在索引中，避免回表。
- 定期维护索引：大数据量下，考虑使用
OPTIMIZE TABLE重建索引，减少碎片。 - 谨慎使用
SELECT *：只查询需要的列，更容易命中覆盖索引。