数据库索引详解：原理、设计原则与应用场景

一、什么是索引？

索引（Index） 是数据库中一种提高数据查询效率的数据结构。它的核心思想就类似于书籍的目录------当你需要查找某个内容时，可以快速定位到该内容所在的页码，而不是从第一页开始翻。

在数据库中，索引的底层通常使用 B+ 树 或 哈希表 结构来加速数据的定位。最常见的是 B+ 树索引，因为它支持范围查询和有序遍历，适用于大多数业务场景。

二、索引的设计原则（解释+原因）

编号

原则

原因说明

1

数据量大、查询频繁的表建立索引

避免全表扫描，大大提升查询性能。小表不需要索引，因为扫描成本本身就低。

2

WHERE、ORDER BY、GROUP BY字段上建立索引

这些操作需要频繁遍历或排序，若无索引只能全表扫描或排序，效率低。

3

选择区分度高的列建立索引

区分度高 = 唯一性强，可以快速过滤掉大量无关行，提高查询效率。

4

字符串字段可使用前缀索引

长字符串全字段建索引浪费空间、性能差，前缀索引节省空间，牺牲一点查询准确率。

5

优先考虑联合索引（覆盖索引）

减少回表操作，同时避免多个单列索引带来的"索引失效"或"最左前缀限制"问题。

6

控制索引数量

索引越多，增删改操作的代价越高（需维护多个索引结构），影响写入性能。

7

NOT NULL优化索引判断

有 NULL 的列不利于优化器准确评估索引使用价值，NOT NULL 会更利于索引使用。

三、哪些字段适合建立索引？

适合建立索引的字段：

WHERE条件中经常使用的字段

例如：用户表中的 user_id、订单表中的 order_no

JOIN操作中用作关联条件的字段

如：外键字段 user_id, product_id

ORDER BY / GROUP BY中出现频繁的字段

如：文章表中的 created_at、评论表中的 article_id

区分度高的字段（例如唯一ID）

如：email、身份证号、手机号

经常作为分页条件的字段

如：按 id 或 created_at 分页查询

联合索引覆盖查询所需字段

如：创建 index(user_id, status) 可以用于 where user_id = ? and status = ? 的查询

四、哪些字段不适合建立索引？

不适合建立索引的字段：

频繁变更的字段

每次更新都会引起索引的维护，如频繁变动的 last_login_time

区分度低的字段

如：gender、is_deleted、status 等只有几个值，索引无法有效过滤数据

不参与查询/排序/分组的字段

比如备注类字段 description, comment

很少查询的冷数据表字段

如历史归档表的字段，使用频率低，没必要增加索引开销

长文本字段

如：text, blob, json，这些字段即使能索引，性能也不会好，建议避免索引。

五、小结

类型

举例

是否适合索引

高频查询字段

user_id、order_no

适合

状态字段

status（0/1/2）

通常不适合（除非与其他字段联合）

排序字段

created_at、updated_at

适合

备注类字段

description、comment

不适合

外键字段

product_id、user_id

适合

低区分度字段

gender、type（枚举类）

不适合（可用于联合索引）