MySQL索引的字段选择

一、是什么？

索引字段选择是指在MySQL中为数据库表的哪些列（字段）创建索引，以优化查询性能。索引相当于数据的"目录"，通过特定数据结构（如B+树）快速定位数据。选择哪些字段建索引直接影响查询效率、写入速度和存储空间。

二、解决什么问题

1. 查询性能瓶颈：避免全表扫描（时间复杂度O(n)），将查询复杂度降至O(log n)。
2. 排序/分组效率：加速ORDER BY、GROUP BY操作。
3. 连接查询优化：提升多表JOIN时的匹配速度。
4. 覆盖索引场景：直接通过索引返回数据，避免回表查询。

三、核心选择原则

1. 高选择性原则

   • 选择性 = 不同值数量 / 总行数
   • 选择性 > 15% 的列适合建索引（如用户ID、手机号）。
   • 低选择性列（如性别、状态标志）通常不适合单独建索引。

2. 高频查询条件

   • 频繁出现在WHERE、JOIN ON、ORDER BY中的字段优先。
   • 示例：WHERE user_id=100 或 ORDER BY create_time。

3. 最左前缀原则（复合索引）

4. 短字段优先

   • 更小的数据类型（如INT优于VARCHAR(255)）减少索引大小。
   • 前缀索引：对长文本使用INDEX(email(10))。

5. 避免冗余索引

   • INDEX(a) 和 INDEX(a,b) 同时存在时，后者可覆盖前者。

四、应用场景

场景	推荐索引策略	说明
主键查询	自动创建主键索引	PRIMARY KEY 默认聚簇索引
等值查询	单列索引	WHERE status=1
范围查询+排序	复合索引（范围字段在前）	WHERE age>20 ORDER BY score
多条件查询	复合索引（按条件频率排序）	WHERE dept='IT' AND salary>5000
覆盖索引	包含查询所需所有字段的复合索引	避免回表，极大提升速度

五、Java示例

// 通过Spring Data JPA定义索引
@Entity
@Table(indexes = {
    @Index(name = "idx_user_email", columnList = "email"), // 单列索引
    @Index(name = "idx_dept_salary", columnList = "department, salary DESC") // 复合索引
})
public class Employee {
    @Id
    private Long id;
    
    private String email;      // 高选择性字段
    private String department; // 高频查询字段
    private Integer salary;    // 范围查询字段
    private Integer age;       // 低选择性字段（通常不单独索引）
}

六、重要注意事项

1. 写入代价：
   • 每次INSERT/UPDATE/DELETE需更新索引，写频繁的表需谨慎添加索引。
2. 索引失效场景：
   • 对索引列使用函数：WHERE UPPER(name)='ALICE'
   • 类型不匹配：字符串列用数字查询 WHERE phone=13800138000
   • OR条件未全覆盖：WHERE a=1 OR b=2（需分别为a、b建索引）
3. 监控优化：
   • 使用EXPLAIN分析查询执行计划
   • 监控慢查询日志：slow_query_log=ON
4. 空间限制：
   • InnoDB索引最大长度767字节（UTF8mb4下约191字符）

七、总结

• 黄金法则：优先为高选择性且高频查询的字段建索引。
• 复合索引：按查询条件顺序遵循最左前缀原则。
• 权衡取舍：在查询加速与写入开销/存储成本间取得平衡。
• 持续优化：随着业务变化定期审查索引有效性，删除冗余索引。

📊 最终决策流程：