索引是基于原始列值构建的

数据库索引详解：原理、类型与最佳实践

在日常开发中，数据库性能往往成为系统瓶颈，而“索引（Index）”则是优化查询性能最重要的手段之一。本文将从概念、原理、类型到实践，系统性地介绍数据库索引，帮助你真正理解它，而不是“只会加索引”。

---

一、什么是数据库索引？

数据库索引是一种用于提高查询效率的数据结构，类似于书籍的目录。

• 没有索引：全表扫描（Full Table Scan）
• 有索引：通过索引快速定位数据

👉 类比：

• 数据表 = 一本书
• 索引 = 目录
• 查询 = 查某一页内容

如果没有目录，你只能一页一页翻；有了目录，可以快速定位。

---

二、索引的底层原理

大多数关系型数据库（如 MySQL、PostgreSQL）默认使用：

1. B+ 树（B+ Tree）

索引底层通常是 B+ 树结构，其特点：

• 多路平衡树（非二叉树）
• 所有数据存储在叶子节点
• 叶子节点之间通过链表连接（方便范围查询）

为什么不用红黑树 / 哈希？

结构	适合场景	缺点
哈希	等值查询	不支持范围查询
红黑树	内存结构	层级深，不适合磁盘
B+ 树	数据库索引	⭐ 最优选择

👉 B+ 树的优势：

• IO 次数少（树高度低）
• 支持范围查询
• 支持排序（ORDER BY）

---

三、索引的分类

1. 按结构分类

（1）B+ 树索引（默认）

• 最常见
• 支持范围查询、排序

（2）哈希索引

• 基于哈希表
• 仅支持 = 查询

👉 常见于：

• Memory 引擎
• Redis（类似思想）

---

2. 按逻辑分类

（1）主键索引（Primary Key）

• 唯一且不能为空
• 一张表只能有一个
• InnoDB 中是聚簇索引

（2）唯一索引（Unique Index）

• 不允许重复
• 可以为空（视数据库而定）

（3）普通索引（Index）

• 最基本的索引
• 允许重复

（4）联合索引（Composite Index）

多个字段组成一个索引：

CREATE INDEX idx_user_name_age ON user(name, age);

👉 使用规则：

• 遵循最左前缀原则

---

四、聚簇索引 vs 非聚簇索引

1. 聚簇索引（Clustered Index）

• 数据和索引存储在一起
• 主键索引就是聚簇索引

👉 优点：

• 查询快（少一次回表）

👉 缺点：

• 插入可能导致页分裂

---

2. 非聚簇索引（Secondary Index）

• 索引和数据分开存储
• 叶子节点存储的是主键

👉 查询过程：

1. 先查索引
2. 再根据主键回表查询数据

👉 这一步叫：回表（Back to Table）

---

五、索引的核心原则

1. 最左前缀原则

对于联合索引 (a, b, c)：

查询条件	是否使用索引
a	✅
a, b	✅
a, b, c	✅
b	❌
c	❌

---

2. 覆盖索引（Covering Index）

如果查询的字段都在索引中：

SELECT name, age FROM user WHERE name = 'Tom';

👉 如果 (name, age) 有索引：

• 不需要回表
• 性能更高

---

3. 索引下推（Index Condition Pushdown）

数据库会尽量在索引层过滤数据，减少回表次数。

---

六、什么时候该建索引？

✅ 适合建索引的场景

• WHERE 条件字段
• JOIN 关联字段
• ORDER BY / GROUP BY 字段
• 高频查询字段

---

❌ 不适合建索引的场景

• 数据量很小（如几百行）
• 频繁更新字段（写入成本高）
• 低选择性字段（如性别）

---

七、索引的代价

索引不是免费的，它有成本：

1. 空间开销

• 索引会占用额外存储空间

2. 写入开销

• INSERT / UPDATE / DELETE 都需要维护索引

3. 维护成本

• 索引过多会拖慢写入性能

👉 原则：查询优化 vs 写入性能的权衡

---

八、常见索引失效场景

1. 使用函数

WHERE YEAR(create_time) = 2024;

👉 索引失效
原因：对列使用函数（如 YEAR()）会导致数据库无法直接利用该列上的索引进行查找。因为索引是基于原始列值构建的，而函数会改变列值的形态，使得优化器无法将查询条件与索引结构匹配。

---

2. 隐式类型转换

WHERE id = '123';

👉 可能导致索引失效
原因：当列的数据类型与查询值类型不一致时（例如 id 是整型，但传入的是字符串 '123'），数据库可能进行隐式类型转换。这种转换通常发生在列上（即把 id 转为字符串比较），从而破坏了索引的使用条件，导致全表扫描。

---

3. LIKE 以 % 开头

WHERE name LIKE '%Tom';

👉 无法使用索引
原因：B+树索引依赖前缀匹配。如果 LIKE 模式以通配符 % 开头，数据库无法确定从索引的哪个位置开始查找，因此不能利用索引的有序性，只能进行全表扫描。只有 LIKE 'Tom%' 这类前缀匹配才能有效使用索引。

---

4. OR 条件不合理

WHERE a = 1 OR b = 2;

👉 可能导致索引失效
原因：如果 a 和 b 分别有单独的索引，但没有联合索引或合适的覆盖索引，数据库优化器可能认为分别使用两个索引再合并结果的成本高于全表扫描，从而放弃使用索引。只有当 OR 的每个子句都能高效使用索引（如各自有独立索引且选择性高），或者使用了“索引合并”（Index Merge）策略时，才可能有效利用索引。

---

九、索引优化实战建议

1. 建立合理的联合索引

优先把：

• 区分度高的字段放前面

---

2. 避免重复索引

(a, b) 已存在，就不需要单独建 (a)

---

3. 使用 EXPLAIN 分析

EXPLAIN SELECT * FROM user WHERE name = 'Tom';

关注：

• type（访问类型）
• key（使用的索引）
• rows（扫描行数）

---

4. 控制索引数量

👉 一张表建议：

• 3~5 个核心索引
• 避免“索引泛滥”

---

十、总结

数据库索引的本质是：

用空间换时间，用复杂结构换查询效率

核心要点回顾：

• 索引底层通常是 B+ 树
• 聚簇索引 vs 非聚簇索引要理解
• 联合索引要遵循最左前缀原则
• 覆盖索引是性能优化关键
• 索引并非越多越好

---

最后一条建议

如果你只记住一句话：

“不是所有查询都需要索引，但高频查询一定要有合适的索引。”