索引
索引概述
索引是提升查询性能的关键数据结构，类似书籍目录。MySQL InnoDB 使用 B+ 树作为索引结构（高度3-4层，可存百万~十亿级数据，查询仅需3-4次I/O）。
核心类型
| 类型 |
说明 |
| 聚集索引（主键索引） |
主键上的索引，数据按主键排序存储，叶子节点存整行数据。一表只能有一个。 |
| 非聚集索引（二级索引） |
自定义索引，叶子节点存主键值。需"回表"查完整数据。 |
执行计划（EXPLAIN）
EXPLAIN SELECT * FROM tb_student WHERE stu_name = '林震南'\G
关键字段解读
| 字段 |
说明 |
type |
访问类型：ALL(全表扫描最差) → const(常量级最优) |
key |
实际使用的索引，NULL=未用索引 |
possible_keys |
可选索引列表 |
key_len |
索引长度，越短越好 |
rows |
预估扫描行数，越少越好 |
Extra |
额外信息 |
Extra 常见值
| 值 |
含义 |
Using index |
索引覆盖，无需回表 |
Using where |
列未被索引覆盖，筛选非前导列 |
Using filesort |
无法利用索引排序，需额外排序 |
Using temporary |
需要临时表（分组/排序） |
索引操作
-- 创建索引
CREATE INDEX idx_student_name ON tb_student(stu_name);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_student_no ON tb_student(stu_no);
-- 前缀索引（节省空间，浪费时间）
CREATE INDEX idx_student_name ON tb_student(stu_name(1));
-- 删除索引
ALTER TABLE tb_student DROP INDEX idx_student_name;
DROP INDEX idx_student_name ON tb_student;
-- 查看索引
SHOW INDEX FROM tb_student;
索引设计原则
| 原则 |
说明 |
| ✅ 适合 查询频繁、更新少 的列 |
索引有维护成本 |
| ✅ 主键、外键、WHERE 条件列 优先建索引 |
区分度高的列效果更好 |
| ✅ 前缀匹配 查询可利用索引 |
LIKE '杨%' 可用，LIKE '%杨%' 不可用 |
| ✅ 多列索引 注意列顺序 |
查询从左到右匹配 |
| ❌ 区分度低 的列不建索引 |
如性别（只有0/1） |
| ❌ 数据量小 的表不建 |
全表扫描可能更快 |
索引失效场景
| 场景 |
示例 |
使用 LIKE '%xx%' |
WHERE name LIKE '%林%' ❌ |
使用 OR 混合条件 |
WHERE col1 = ? OR col2 = ?（除非两列都有索引） |
| 数据类型转换 |
WHERE id = '123'（字符串存数字） |
| 函数/运算 |
WHERE YEAR(birth) = 1990 ❌ |
NOT IN / != |
通常无法利用索引 |
总结
- 索引用 空间换时间，加快查询但增加写操作开销
EXPLAIN 是调优必备工具，关注 type、key、rows- 聚集索引存完整数据，非聚集索引存主键（需回表）
- 设计原则：查询频繁 + 区分度高 + 更新不频繁
