索引创建原则

一、索引的基本类型

在实际开发中，常用的索引类型包括：

• 主键索引：表中必须且唯一的索引，默认使用主键字段创建，用于唯一标识数据行。
• 唯一索引：字段值唯一但非主键，可避免重复数据，同时支持高效查询。
• 复合索引（联合索引）：根据业务场景创建的多字段索引，遵循最左前缀原则，适用于多条件查询场景。

---

二、索引创建核心原则

1. 优先为数据量大、查询频繁的表创建索引 小表查询性能本身较高，创建索引收益不大；大表且查询频繁的场景，索引能显著降低全表扫描带来的IO开销。

2. 为常作为查询条件、排序、分组的字段创建索引

   • WHERE条件字段：索引能快速过滤数据，减少扫描行数。
   • ORDER BY/GROUP BY字段：索引可直接利用有序性避免文件排序，降低CPU消耗。

3. 选择字段内容区分度高的列作为索引 区分度越高，索引过滤效果越好，例如用户ID、手机号等字段；低区分度字段（如性别、状态）创建索引收益极低，甚至可能被优化器忽略。

4. 内容较长的字段使用前缀索引 对TEXT、VARCHAR等长文本字段，可通过前缀索引截取前N个字符创建索引，减少索引占用空间，同时保证查询效率。

5. 优先创建复合索引而非多个单值索引 多条件查询场景下，复合索引能一次覆盖多个条件，避免多次回表；需遵循最左前缀原则，将查询频率高、区分度高的字段放在前面。

6. 控制索引数量，避免过度索引 索引会占用额外存储空间，且会降低INSERT/UPDATE/DELETE的写入性能，需平衡查询和写入性能，仅创建必要索引。

7. 索引列尽量避免NULL值，使用NOT NULL约束 NULL值会导致索引失效或查询效率降低，创建表时应使用NOT NULL约束，或设置默认值避免NULL。

---

面试相关问题

1. 问：创建索引时需要遵循哪些原则？ 答：核心原则包括：为数据量大、查询频繁的表创建索引；优先选择查询条件、排序分组字段；选择区分度高的列；长字段使用前缀索引；优先复合索引；控制索引数量；索引列避免NULL值。

2. 问：为什么低区分度的字段不适合创建索引？ 答：低区分度字段（如性别）过滤效果差，索引无法有效减少扫描行数，优化器可能仍选择全表扫描，反而增加索引维护开销。

3. 问：复合索引的最左前缀原则是什么？创建时需要注意什么？ 答：最左前缀原则是指查询条件需包含复合索引的最左字段，才能命中索引。创建时应将查询频率高、区分度高的字段放在前面，覆盖常用查询场景。

4. 问：为什么要控制索引数量？ 答：索引会占用磁盘空间，且在数据写入时需要同步更新索引，降低INSERT/UPDATE/DELETE性能，因此需避免过度索引，仅创建必要索引。

5. 问：NULL值对索引有什么影响？如何避免？ 答：NULL值会导致索引失效或查询效率降低，创建表时应使用NOT NULL约束，并设置合理默认值，避免字段存储NULL。