MySQL 索引选择策略：单字段、组合及唯一性判断

单字段索引的局限性

索引的核心是存储引擎用于快速定位记录的数据结构。但在实际查询中，许多开发者习惯在 WHERE 子句中使用计算表达式，这往往导致索引失效。

核心规则： 对于单字段索引，必须在查询条件中直接使用字段名，禁止对字段进行运算或函数处理。

-- ❌ 错误写法：无法命中索引，执行类型变为 ALL（全表扫描）
mysql> explain select * from city where fid + 1 = 2;

-- ✅ 正确写法：直接匹配字段值，可命中 index_1
mysql> explain select * from city where fid = 1;

即使 fid + 1 = 2 逻辑上等价于 fid = 1，MySQL 优化器也无法自动将左侧的表达式转换为索引查找，从而导致全表扫描。

组合索引解决 OR 查询性能问题

当查询条件包含多个字段的 OR 关系（如 name='xxx' OR id=1）时，如果在不同字段分别建立普通索引，MySQL 依然可能退化为全表扫描或多次扫描合并结果，效率低下。

第 1 步：验证多字段单索引的表现

分别在 fid 和 name 字段建立独立索引后查询：

explain select * from city where name = '青浦区' or fid = 1;

现象： possible_keys 虽然显示两个索引可用，但实际执行类型为 ALL。MySQL 会先通过 name 索引全表扫描匹配，再通过 fid 索引全表扫描匹配，最后合并结果。数据量大时开销极高。

第 2 步：创建组合索引提升性能

为了解决上述问题，应将涉及的条件字段建立为联合索引（组合索引）。这样 MySQL 可以在一次索引扫描中同时匹配这两个条件。

-- 假设已建立 (fid, name) 的组合索引 index_3
explain select * from city where name = '青浦区' or fid = 1;

结果： 执行类型变为 index，Extra 信息显示 Using where; Using index。系统只需两次基于索引的遍历即可完成匹配，显著提升了查询速度。需要注意的是，组合索引需遵循最左前缀法则以确保护所有相关字段都能被有效利用。

唯一索引与普通索引：读写性能深度对比

很多开发者认为只有业务要求绝对唯一的字段才需要使用唯一索引。实际上，从性能角度分析，两者在读操作上差别微乎其微，但在写操作上存在显著差异。

读取性能对比

假设通过主键或唯一值查询订单（order_id = 52355096）：

• 唯一索引： 在 B-Tree 中查找到目标值后，由于知道只有一条记录，直接返回结果。
• 普通索引： 查找到目标值后，仍需向下继续扫描，直到发现下一条数据的值不等于该值为止，才能确认“没有重复项”，然后返回结果。

结论： 尽管步骤略有不同，但由于相邻数据在索引树中物理位置接近，两者的查询耗时几乎相同，读性能无明显区别。

写入性能对比

在更新操作（如 update ... where order_id = ...）中，差异变得明显：

• 普通索引： 允许使用 Change Buffer。如果修改的数据不在内存缓冲区（Buffer Pool），变更会被缓存到磁盘的 Change Buffer 中，后续空闲时再合并，减少随机 I/O。
• 唯一索引： 不支持 使用 Change Buffer。因为必须保证数据唯一性，任何修改前都必须先将相关数据页加载到内存中进行完整性校验，相当于强制进行了一次随机 IO 读取。

结论： 高并发写操作场景下，唯一索引的性能开销远大于普通索引。

总结与最佳实践

综合以上分析，在 MySQL 索引选型时建议遵循以下原则：

1. 书写规范： 避免在索引列上使用表达式或函数，保持条件的纯粹性。
2. 复合查询： 当单个字段的单独索引无法满足性能需求时（特别是 OR 查询），优先考虑建立组合索引以复用单次索引遍历。
3. 类型选择： 除非有严格的物理层面唯一性约束需求，否则推荐使用普通索引。业务层面的唯一性约束应在代码应用层进行校验和控制，以获得更高的写入性能。

以上就是 MySQL 中各种索引选择与优化的详细内容，更多关于 MySQL 索引调优的资料请关注本站其它相关文章！