索引
数据库索引：教学、使用场景与最佳实践指南
索引的本质与作用
**索引（Index）**是关系型数据库中用于提升查询性能的核心机制，作用等同于一本书的目录 —— 可以快速定位目标信息的位置。
- 没有索引时： 查询需逐行扫描表，效率低下（尤其在百万级或更大数据规模下）。
- 有索引时： 查询可通过索引结构（如 B+ 树）直接定位数据，I/O 成本显著减少。
🔍 索引是空间换时间的经典案例：它额外占用存储空间，但大幅减少查询时间。
常见索引类型（以 MySQL InnoDB 为例）
1. B+ 树索引（最常用）
- 存储结构：根节点 + 中间节点 + 叶子节点
- 所有数据均保存在叶子节点，有序，便于范围查询和二分查找
- 每次查找平均只需 3\~4 次磁盘 I/O，适用于绝大多数查询场景
2. 全文索引
- 适合模糊文本搜索，如博客、新闻内容字段（MyISAM 支持更早，InnoDB 需 5.6+）
3. R 树索引
- 用于地理空间数据（GIS 数据），如地图坐标查找
本文聚焦于 B+ 树索引 —— 实际使用中最常见、最具代表性的索引类型。
InnoDB 索引结构与术语理解
1. 聚集索引（Clustered Index）
- 每张表有且只有一个，即主键索引
- 表数据直接存储在 B+ 树的叶子节点中，叶子节点即为整行数据
2. 非聚集索引（Secondary Index）
- 用户手动创建的普通索引
- 叶子节点只存储主键值 + 索引列
- 查询时需通过主键回表获取完整行数据（称为回表查询）
索引使用场景实操讲解
示例：查询学生姓名为“林震南”的记录
无索引时的执行计划：
EXPLAIN SELECT * FROM tb_student WHERE stuname='林震南'\G
输出显示 type: ALL（全表扫描），查询慢且低效。
创建索引：
CREATE INDEX idx_student_name ON tb_student(stuname);
再次执行 SQL：
EXPLAIN SELECT * FROM tb_student WHERE stuname='林震南'\G
此时 type: ref，扫描行数为 1，查询效率显著提升。
使用前缀索引：
CREATE INDEX idx_student_name_1 ON tb_student(stuname(1));
此索引仅使用“姓”进行索引，扫描行数增加至 2（因为两个姓“林”的学生），牺牲部分查询精度以节省空间。
使用 EXPLAIN 解读 SQL 性能
EXPLAIN 是分析 SQL 性能的核心工具。
| 字段名 | 含义 |
|---|---|
id |
查询步骤 ID |
select_type |
查询类型（如 SIMPLE、PRIMARY） |
table |
当前查询的表名 |
type |
访问类型，性能从好到差为：const > eq_ref > ref > range > index > ALL |
possible_keys |
可能使用的索引 |
key |
实际使用的索引 |
rows |
预计扫描的行数 |
Extra |
额外信息，如是否使用了临时表、排序 |
索引删除方式
两种方式均可删除已存在索引：
ALTER TABLE tb_student DROP INDEX idx_student_name;
-- 或
DROP INDEX idx_student_name ON tb_student;
索引优化的高级用法
1. 复合索引（联合索引）
- 例：
CREATE INDEX idx_name_age ON tb_student(name, age); - 遵循最左前缀原则，可用于
(name)、(name, age)的查询，不能用于只查age
2. 索引覆盖
- 当查询所用字段都包含在索引中，不需要回表
- 示例：只查询索引字段
name和age，不涉及其他列
3. 函数索引（MySQL 5.7+ 支持）
- 如：
CREATE INDEX idx_lower_name ON tb_student ((LOWER(name))); - 注意：索引中用到的表达式必须和查询语句保持一致
最佳实践与设计原则
✅ 应该索引的情况
- WHERE 子句中频繁使用的字段
- JOIN 条件的字段
- ORDER BY 或 GROUP BY 中使用的字段
- 高基数字段（值分布广，不重复）
❌ 不适合索引的情况
- 表数据量小，查询快，建索引反而浪费
- 经常变动的列（UPDATE/INSERT 频繁）
- 重复值多的字段（如性别、状态等）
📌 总结设计原则
- WHERE 子句、JOIN 子句优先考虑加索引
- 基数越大，索引效率越高
- 前缀索引节省空间但降低精度
- 索引数量不宜过多，写操作成本增加
- 主键字段宜使用短类型（如 INT）
- 避免在索引列使用函数、LIKE '%xxx%' 模糊查询会导致索引失效
索引失效的常见原因
| 场景 | 说明 |
|---|---|
使用 LIKE '%前缀%' |
索引失效，全表扫描 |
| 索引列参与计算 | 如 WHERE age + 1 = 20 |
| 数据类型不匹配 | 字符串用引号，数字不加引号 |
| OR 连接的多个条件中有未加索引的字段 | 整体无法使用索引 |
结语：学会用好索引，是数据库性能优化的核心能力
索引设计是一门艺术，涉及性能、空间、维护成本的权衡。作为开发者，应掌握索引的类型、机制、优化方法，并善于使用 EXPLAIN 工具进行调优。熟练掌握这些技巧，将极大提高数据库应用的响应速度与可扩展性。
💡 记住：索引能救命，也可能拖垮系统。设计之前，请三思！