MySQL数据库—索引

一、索引基础

1. 什么是索引？

索引是一种数据结构，它通过对表中数据按照特定规则排序，帮助数据库快速定位和查询数据，本质上类似于字典的目录：先查目录找到页码，再翻到对应页找具体内容。

2. 为什么要使用索引？

• 核心目的：提升查询效率，在海量数据下避免全表扫描。
• ** trade-off**：查询效率提升的同时，会降低写入（INSERT/UPDATE/DELETE）效率，因为索引需要同步维护。

3. 索引的数据结构选型

不同数据结构的特性决定了其适用场景，MySQL 最终选择了B + 树作为索引的底层数据结构：

表格

二、B + 树与 MySQL 存储结构

1. B + 树核心特性

• 非叶子节点：只存储索引键和子节点指针，不存储真实数据，因此单个节点可以存储更多索引，大幅降低树高。
• 叶子节点：包含完整数据记录，且通过双向链表连接，天然支持范围查询和排序。
• 与 B 树的区别：
  1. B + 树数据只存在于叶子节点，B 树所有节点都存数据。
  2. B + 树叶子节点链表化，范围查询更高效。
  3. 相同树高下，B + 树能存储更多数据，查询路径更短。

2. MySQL 的页（Page）

• 页是 InnoDB 与磁盘交互的最小单位，默认大小为16KB。
• 数据和索引都以页为单位存储，利用局部性原理，一次 I/O 加载一页数据到内存，提升后续查询效率。
• 页结构：页头部（元信息）→ 数据行 → 页尾部（校验信息）。

3. B + 树在 MySQL 中的应用

• 每个 B + 树节点就是一个页，根节点、非叶子节点、叶子节点共同构成完整索引树。
• 聚簇索引（主键索引）的叶子节点直接存储完整行数据，辅助索引的叶子节点存储主键值，需要回表查询完整数据。

三、索引分类与创建

1. 索引分类

表格

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字段：显示实际使用的索引名称。

五、最佳实践总结

1. 选择合适的列创建索引：高频查询列、WHERE条件列、JOIN关联列。
2. 优先使用复合索引：替代多个单列索引，减少索引数量，提升查询效率。
3. 避免过度索引：索引越多，写入性能越差。
4. 使用覆盖索引：将查询所需列都包含在索引中，避免回表。
5. 定期维护索引：大数据量下，考虑使用OPTIMIZE TABLE重建索引，减少碎片。
6. 谨慎使用SELECT *：只查询需要的列，更容易命中覆盖索引。