如何选择合适的字段来创建索引？

在 MySQL 中选择合适的字段来创建索引对于提升数据库查询性能至关重要。以下是一些选择索引字段的原则和方法：

基于查询条件

• 频繁作为查询过滤条件的字段：如果某个字段经常出现在WHERE子句中，将其作为索引字段是个不错的选择。例如，在一个电商系统的订单表中，经常会根据用户 ID 查询订单信息，那么user_id字段就适合创建索引。

-- 查询用户ID为123的所有订单
SELECT * FROM orders WHERE user_id = 123;

• 参与连接操作的字段：当进行多表连接查询时，连接条件所涉及的字段应该创建索引。比如，在订单表和用户表进行连接查询时，通常会根据用户 ID 进行连接，此时订单表和用户表的user_id字段都应该创建索引。

-- 连接订单表和用户表，查询用户ID为123的订单信息
SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.user_id = 123;

基于字段数据特性

• 高基数性字段：基数指的是字段中不同值的数量。高基数性字段意味着该字段有很多不同的值，这样的字段适合创建索引。例如，用户表中的email字段，每个用户的邮箱地址通常是唯一的，具有很高的基数，创建索引可以显著提高查询效率。

-- 根据邮箱地址查询用户信息
SELECT * FROM users WHERE email = 'example@example.com';

• 字段长度：较短的字段创建索引时，索引文件占用的空间更小，查找速度也更快。因此，如果有多个字段都适合创建索引，优先选择长度较短的字段。例如，使用整数类型的user_id创建索引通常比使用较长的字符串类型的username创建索引性能更好。

基于排序和分组操作

• 经常用于排序的字段：如果某个字段经常出现在ORDER BY子句中，为该字段创建索引可以避免 MySQL 进行额外的文件排序操作，提高排序效率。例如，在新闻列表页面，经常需要按照发布时间对新闻进行排序，那么publish_time字段就适合创建索引。

-- 按照发布时间降序查询新闻列表
SELECT * FROM news ORDER BY publish_time DESC;

• 经常用于分组的字段：当某个字段经常用于GROUP BY操作时，为其创建索引可以加快分组的速度。例如，在统计不同部门的员工数量时，department_id字段适合创建索引。

 

-- 统计不同部门的员工数量
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

避免过度索引

• 不选择低基数性字段：如果一个字段的取值范围非常有限，例如性别字段，只有 “男” 和 “女” 两个值，为这样的字段创建索引可能不会带来明显的性能提升，反而会增加索引维护的开销。
• 避免为很少使用的字段创建索引：创建索引需要占用额外的存储空间，并且会影响数据插入、更新和删除的性能。因此，不要为那些很少在查询条件、连接条件、排序和分组操作中使用的字段创建索引。