数据库学习（五）——MySQL索引
一、MySQL索引
MySQL 中的 索引（Index） 是提升查询效率的关键机制。索引在表中某些列上建立一个快速查找的数据结构，能够显著减少数据的扫描量，从而提升查询、排序、分组等操作的性能。
作用：
| 功能 | 说明 |
|---|---|
| 提高查询效率 | 加快 WHERE 条件过滤，减少全表扫描 |
| 加速排序与分组 | 支持 ORDER BY、GROUP BY 等操作 |
| 强制唯一约束 | PRIMARY KEY 和 UNIQUE 索引可防止重复数据插入 |
| 加速关联查询 | 联表查询时加快 JOIN 条件字段的匹配速度 |
| 提高部分更新效率 | 支持 UPDATE/DELETE 时更快定位记录 |
二、MySQL 索引类型（逻辑分类）
| 索引类型 | 说明 |
|---|---|
| 主键索引（PRIMARY） | 唯一且不能为空，每张表最多一个，InnoDB 中作为聚簇索引（数据和索引在一起） |
| 唯一索引（UNIQUE） | 字段值必须唯一，但可为 NULL |
| 普通索引（INDEX） | 最基本的索引类型，无唯一性约束 |
| 组合索引（Composite） | 多列组成的索引，支持“最左前缀原则” |
| 全文索引（FULLTEXT） | 用于大文本匹配（如博客、文章搜索），支持 MATCH AGAINST 查询方式 |
| 空间索引（SPATIAL） | 支持几何数据类型，MyISAM/InnoDB 支持部分实现 |
三、MySQL 索引底层结构
| 类型 | 底层结构 | 应用场景 | 特点 |
|---|---|---|---|
| B+ 树索引 | B+ 树 | 默认索引结构（InnoDB） | 高效范围查询、按序遍历、主键聚簇、高页利用率 |
| 哈希索引 | 哈希表 | MEMORY引擎默认索引 | 仅适合等值查询，无法进行范围或排序查询 |
| 全文索引 | 倒排索引 | MyISAM / InnoDB | 用于全文搜索（匹配文本中的单词） |
| R 树索引 | R-Tree | MyISAM 的 GIS 空间索引 | 支持多维空间数据（地理数据）查询 |
1. B+ 树索引（最常见）
数据结构特性：
- 是多路平衡查找树，所有数据只存在叶子节点；
- 叶子节点按键值递增顺序通过双向链表连接，便于范围查询；
- 非叶子节点仅保存索引字段，不保存数据。
应用场景：
- 主键索引（聚簇索引）；
- 二级索引（辅助索引）；
- 联合索引；
- 最常用于
InnoDB存储引擎中。
特点：
| 特点 | 说明 |
|---|---|
| 支持排序、范围 | 因为索引是有序的，可以高效地做 BETWEEN、ORDER BY 等 |
| 树高较小 | 一个节点可存上百个 key，通常只需 2~3 次磁盘 IO 查找 |
| 叶子节点数据结构 | 聚簇索引存整行记录，辅助索引存主键值 |
2. 哈希索引（Hash Index）
数据结构特性：
- 以 哈希函数计算键值对应的位置；
- 适合
=、IN查询； - 无序，不支持范围查询 和 排序。
注意事项：
- 常用于
MEMORY引擎； - 哈希冲突会降低性能；
InnoDB支持 自适应哈希索引（Adaptive Hash Index），由 B+ 树自动生成部分热点哈希。
3. 倒排索引（Inverted Index）
数据结构：
- 类似搜索引擎，每个单词对应一个文档列表；
- 本质是
Hash + List。
应用场景：
FULLTEXT全文索引；- 支持
MATCH ... AGAINST查询； - 适合大文本字段的关键词检索。
示例：
"mysql" → [doc1, doc4]
"index" → [doc2, doc5]
4. R 树索引（空间索引）
数据结构：
R-Tree是多维空间数据的索引结构；- 用于二维或多维空间的矩形、坐标数据。
应用场景：
- 地理信息系统（GIS）；
MyISAM引擎下的空间索引（InnoDB 从 5.7+ 支持）；- 常配合
SPATIAL INDEX使用。
四、最左前缀原则（组合索引的查询优化关键）
在 MySQL 的 B+ 树联合索引（联合主键或普通索引） 中，有一个非常重要的优化原则——最左前缀原则（Leftmost Prefix Rule）
定义：
最左前缀原则是指：在使用联合索引时，查询条件必须从最左边的列开始连续使用，索引才能被有效使用。
示例说明
假设有一个联合索引：
CREATE INDEX idx_user ON users (name, age, city);
这个索引的结构顺序是：(name, age, city)。
可以使用索引的查询：
| 查询语句 | 能否用索引 | 原因 |
|---|---|---|
SELECT * FROM users WHERE name = 'Alice' | ✅ | 使用了第一列 name |
SELECT * FROM users WHERE name = 'Alice' AND age=30 | ✅ | 使用了前两列 (name, age) |
SELECT * FROM users WHERE name = 'Alice' AND age=30 AND city='BJ' | ✅ | 使用了全列，最优 |
SELECT * FROM users WHERE name LIKE 'A%' | ✅（前缀） | 使用了 name 前缀查询 |
无法使用索引的查询：
| 查询语句 | 能否用索引 | 原因 |
|---|---|---|
SELECT * FROM users WHERE age = 30 | ❌ | 没有使用最左的 name 字段 |
SELECT * FROM users WHERE city = 'BJ' | ❌ | 跳过了前两个字段 |
SELECT * FROM users WHERE name = 'Alice' AND city='BJ' | ✅（部分） | 中间缺了 age，只使用了 name 的索引 |
原因：索引是按顺序构建的
联合索引底层是 B+ 树，树结构是按照创建顺序建立的：
(name) → (name, age) → (name, age, city)
一旦中间字段缺失，MySQL 无法精准定位后续字段的范围。
应用建议：
- 构建联合索引时，字段顺序很重要，要把过滤性强、常用作查询条件的列放前面。
- 在写 SQL 时，尽量保证
WHERE子句 从左到右连续使用索引列。 - 尽量避免跳列、只用尾列等情况，会导致索引失效。
五、常见的索引使用建议
| 建议 | 原因说明 |
|---|---|
| 针对高频查询字段添加索引 | 提升检索性能 |
WHERE、JOIN、ORDER BY 字段优先考虑索引 | 能显著减少扫描量 |
| 控制单张表索引数量（建议不超过 5~6 个） | 索引越多，写入性能越差，维护开销越大 |
| 使用覆盖索引（索引包含所有查询字段） | 避免回表，进一步提升查询效率 |
| 避免在索引字段上使用函数或计算 | 如 WHERE YEAR(birth)=1990 会导致索引失效 |
避免使用 %前缀通配 | 如 LIKE '%abc' 无法使用索引；但 LIKE 'abc%' 可用 |
六、索引失效的常见原因
| 原因 | 示例 SQL |
|---|---|
| 在索引列上使用函数或计算 | WHERE YEAR(date_col) = 2020 |
| 隐式类型转换 | WHERE phone = 123456（列为 VARCHAR） |
使用 OR 且部分列无索引 | WHERE a=1 OR b=2 |
LIKE 模糊匹配 %前缀 | WHERE name LIKE '%abc' |
| 组合索引未遵循最左前缀原则 | WHERE age = 20（索引为 name, age） |
七、索引相关操作
-- 创建普通索引
CREATE INDEX idx_name ON users(name);-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);-- 创建组合索引
CREATE INDEX idx_name_age ON users(name, age);-- 删除索引
DROP INDEX idx_name ON users;-- 查看表索引
SHOW INDEX FROM users;
八、使用工具分析索引效果
| 工具或命令 | 功能 |
|---|---|
EXPLAIN SELECT ... | 查看是否使用索引、访问方式 |
SHOW INDEX FROM ... | 查看索引结构 |
pt-duplicate-key-checker | 查找重复或冗余索引 |
ANALYZE TABLE | 让优化器重新评估索引统计信息 |
九、索引的使用场景
| 场景 | 是否应建索引 |
|---|---|
| 高频查询的字段（如用户ID、邮箱、手机号） | ✅ |
经常作为 JOIN 条件的字段 | ✅ |
| 查询结果非常少（高选择性字段） | ✅ |
| 表中数据很少、几百条以内 | ❌ |
| 写操作远多于读操作（如日志表） | ❌ |
| 经常更新的字段，且更新频率高 | 慎用 |