mysql索引使用原则

一、联合索引的使用原则

联合索引在设计数据库时扮演着至关重要的角色。以下是关于联合索引使用的核心原则：

1. 最左前缀法则：联合索引的查询必须从最左侧列开始，无法跳过中间的列。如果跳跃使用，那么后续列将无法充分利用索引。例如，在索引(a,b,c)中，如果只使用b或c，则索引的优势将大打折扣^[1][4][5]^。

2. 范围查询的限制：在联合索引中使用范围查询（如大于小于符号）时，其右侧所有列的索引将失效。为了解决这个问题，建议采用包含等于符号的范围查询^[1][5]^。

3. 选择性优先原则：在设计组合索引时，应将选择性较高的列放在前面。选择性是指某个字段不同值的数量与总记录数的比值，选择性越高的字段越适合放在前面，如性别和年龄字段^[5][6]^。组合索引的字段数建议不超过五个，避免冗余^[5]^。

二、字段操作注意事项

在实际数据库操作中，对字段的操作也会影响索引的使用效率：

1. 禁止字段运算和函数：在WHERE条件中对索引列进行运算或使用函数，会导致索引失效。例如，`WHERE YEAR(create_time)=2025`这样的操作^[1][5][7]^。

2. 字符串必须加引号：当字符串类型的字段条件值没有加引号时，可能触发隐式类型转换，导致索引失效^[1][5]^。

3. 模糊查询的限制：头部模糊匹配（如LIKE '%abc'）无法使用索引，而尾部模糊匹配（如LIKE 'abc%'）则可以正常利用索引^[1][5]^。

三、索引设计原则

设计数据库索引时，应遵循以下原则：

1. 高频查询优先索引：为经常出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的字段建立索引^[2][6]^。单表的索引总数建议不超过五个，避免影响写入性能^[5][7]^。

2. 主键与覆盖索引：显式定义一个与业务无关的自增主键（建议使用INT/BIGINT类型），并利用覆盖索引减少回表查询的次数（避免SELECT 操作）^[5][8]^。

3. 避免冗余和低效索引：不要在频繁更新的字段、数据量小的表以及低区分度的字段上建立索引^[5][7]^。

四、验证与维护

为了确保索引的使用效率，需要进行验证和维护：

1. 使用EXPLAIN分析执行计划：关注`type`列是否为`ref/range`，同时避免`Extra`列出现`Using filesort`或`Using temporary`的情况^[5][8]^。

2. 监控索引选择性：通过特定公式判断字段的选择性，选择性越接近1的字段越适合建立索引^[5][6]^。

3. 类型匹配与避免隐式转换：在进行JOIN操作时，确保字段类型严格一致，避免隐式转换导致全表扫描^[5][8]^。

最佳实践示例：

创建选择性优先的联合索引：`ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);`^[4][5]^。

利用覆盖索引优化查询：`SELECT order_id, total_price FROM orders WHERE status=1 AND created_at>'2025-05-01';` 这个查询可以完全通过idx_status_created索引完成，前提是索引字段的顺序与查询条件的顺序相匹配，并且返回的结果仅包含在索引中^[4][5]^。