PfLearn | 数据库索引创建规则

数据库索引创建规则原创

2024-09-19 16:21 19

## 数据库索引创建规则

1. **主键和外键**：
   - 表的主键和外键必须有索引，以保证数据的唯一性和加速连接操作。
2. **数据量**：
   - 数据量超过 300 的表应该考虑创建索引，以提高查询性能。
3. **连接字段**：
   - 经常与其他表进行连接的字段（通常是外键）应建立索引，以加快连接操作。
4. **查询条件字段**：
   - 经常出现在 `WHERE` 子句中的字段，特别是大表中的字段，应该建立索引。
5. **选择性高的字段**：
   - 索引应建立在选择性高的字段上，即字段中有大量不同值的列。
6. **字段大小**：
   - 应该建立在小字段上，避免在大的文本字段（如 `TEXT`、`BLOB`）上创建索引。
7. **复合索引**：
   - 复合索引的建立需要仔细分析：
     - 选择性较好的字段应放在前面。
     - 复合索引中的字段应经常同时出现在 `WHERE` 子句中。
     - 复合索引包含的字段不宜超过 3 个。
     - 如果已有单字段索引，再添加复合索引时，需考虑是否有必要。
8. **频繁数据操作的表**：
   - 对于频繁进行数据操作的表，不要建立过多的索引，以避免影响数据操作性能。
9. **删除无用索引**：
   - 定期删除不再使用的索引，以减少对执行计划的负面影响。

------

## 一般选择在这些列上创建索引

1. **搜索查询列**：
   - 在经常需要搜索查询的列上创建索引，以加快搜索速度。
2. **主键列**：
   - 在主键列上创建索引，确保唯一性并组织表中数据的排列结构。
3. **连接列**：
   - 在经常用于连接的列（外键）上创建索引，以加快连接速度。
4. **范围搜索列**：
   - 在经常需要根据范围进行搜索的列上创建索引，利用索引的排序特性加快查询速度。
5. **排序列**：
   - 在经常需要排序的列上创建索引，利用索引的排序特性提高排序速度。
6. ** `WHERE` 子句列**：
   - 在经常使用在 `WHERE` 子句中的列上创建索引，以提高条件判断速度。
7. **关键字列**：
   - 为经常出现在 `ORDER BY`、`GROUP BY`、`DISTINCT` 后面的字段建立索引。

------

## 一般不选择在这些列上创建索引

1. **少使用的列**：
   - 对于在查询中很少使用的列不创建索引，以避免不必要的维护成本。
2. **大量相同取值的字段**：
   - 不在取值重复的字段上创建索引，例如性别字段，因为索引无法显著提高检索速度。
3. **大文本或图像字段**：
   - 对于 `TEXT`、`IMAGE`、`BIT` 数据类型的列通常不创建索引。
4. **修改性能优先的情况**：
   - 当修改性能远大于检索性能时，不创建索引，以避免索引增加对写操作的影响。

------

## 创建索引时需要注意的地方

1. **限制索引数目**：
   - 对于更新操作频繁的表，索引数目一般不超过 3 个，最多不超过 5 个，以避免过多索引影响更新操作。
2. **避免索引增长字段**：
   - 对于取值朝一个方向增长的字段（如日期字段），避免建立索引，尤其是复合索引的前面部分。
3. **复合索引字段排序**：
   - 对复合索引中的字段，按照在查询条件中出现的频度进行排序，将应用频度高的字段放在前面。
4. **删除无用索引**：
   - 定期删除不再使用或很少被使用的索引，减少索引对更新操作的影响。

------

这些原则和注意事项有助于设计高效的索引，优化数据库性能。

擦擦擦擦 2天前

大豆i啊街道回复