MySQL 索引失效了吧？

前几天一个小伙伴说面试可能挂了，引失他说面试官问他MySQL 索引失效的引失原因可能有哪些时，他脑袋一懵，引失竟然啥都没说出来，引失面试官的引失笑容给他留下了深刻的印象。

MySQL 索引在优化查询性能中扮演着关键角色，引失尤其是引失有联查或者数据量大的情况，但是引失，一些操作或写法会导致索引失效，引失索引失效，引失等于无用功了。引失

下面说一下 MySQL 索引失效的引失几种常见情况。

使用函数或表达式操作索引列

如果在查询条件中对索引列使用了函数或表达式，引失MySQL 无法利用索引。引失

例如下面的引失语句，本来name列是有索引的

复制SELECT * FROM users WHERE LEFT(name, 3) = Tom;1.

但是，LEFT(name, 3) 对 name 列进行了函数操作，这就导致 MySQL 无法直接使用索引。

解决方法

第一种就是避免在索引列上使用函数，改为使用 like查询，注意是后面加 %

复制SELECT * FROM users WHERE name LIKE Tom%;1.

第一种方式是香港云服务器针对普通索引来说的，还有一种解决方法，那就是直接加前缀索引，例如

复制CREATE INDEX idx_name_prefix ON users (LEFT(name, 3));1.

前缀索引，一般用于长文本列（用户名、地址）、减少索引存储空间、提高索引创建和查询性能等场景。

查询条件中使用隐式类型转换

如果索引列和查询条件的数据类型不一致，MySQL 会进行隐式类型转换，导致索引失效。

例如：

复制SELECT * FROM users WHERE phone_number = 1234567890;1.

如果 phone_number 列是 VARCHAR 类型，而查询条件中的值是数字类型，MySQL 会将 phone_number 转换为数字类型，导致索引失效。

解决方法 

确保查询条件的数据类型与列类型一致：

复制SELECT * FROM users WHERE phone_number = 1234567890;1.

使用不等操作符

当查询条件中使用 <> 或 NOT IN，MySQL 无法高效利用索引。

例如

复制SELECT * FROM users WHERE age <> 30;1.

不等操作符会使查询范围不连续，MySQL 通常会选择全表扫描。

解决方法

如果可以的话，建议尝试调整查询逻辑，例如

复制-- 优化方案1：IN 列表 SELECT * FROM users WHERE status IN (2, 3, 4); -- 优化方案2：逻辑重构 SELECT * FROM users WHERE status > 1 AND status < 5;1.2.3.4.5.

使用 OR 条件且未对所有列加索引

当 OR 条件连接的多个列中，并非所有列都有索引时，索引会失效。网站模板

例如下面语句，只要name和age列有至少一个没有加索引，索引都不会被命中

复制SELECT * FROM users WHERE name = Tom OR age = 30;1.

name 列有索引，而 age 列没有索引，会导致全表扫描。

解决方法

确保 OR 条件的每一列都加上索引，或者改用 UNION或UNION ALL

复制SELECT * FROM users WHERE name = Tom UNION SELECT * FROM users WHERE age = 30;1.2.3.

且 UNION和 UNION ALL也是有差别的，UNION去重，性能略低，UNION ALL不去重，性能更高

索引列在范围查询后再用其他条件筛选

在范围查询（如 <、>、BETWEEN、LIKE）后再对其他列筛选时，其他列的索引可能失效。

例如：

复制SELECT * FROM users WHERE age > 30 AND name = Tom;1.

MySQL 使用联合索引时，范围查询会中断索引的使用，后续的 name = Tom 条件无法利用索引。

解决方法 调整索引顺序或逻辑，确保查询优化。

设置联合索引时，建议将区分度最高的列放在最左侧，使用下列方法可以评估各个列的区分度大小

复制SELECT COUNT(DISTINCT username) / COUNT(*) AS username_selectivity, COUNT(DISTINCT age) / COUNT(*) AS age_selectivity FROM users;1.2.3.4.

索引列的前缀未完全匹配

对于多列联合索引，必须遵循最左前缀匹配规则，云南idc服务商否则索引会部分或完全失效。

例如：

下面的语句，没有遵守最左前缀原则，导致索引失效

复制CREATE INDEX idx_name_age ON users (name, age); SELECT * FROM users WHERE age = 30; -- 索引失效1.2.

查询条件未包含索引的第一列 name，无法触发联合索引。

解决方法

确保遵循最左前缀匹配规则：

复制SELECT * FROM users WHERE name = Tom AND age = 30;1.

LIKE 模式中通配符使用不当

前面也提到了这种情况，如果 LIKE 查询以 % 开头，索引会失效。

例如：

复制SELECT * FROM users WHERE name LIKE %Tom;1.

以 % 开头无法通过索引定位记录。

解决方法

尽量避免以 % 开头，改为：

复制SELECT * FROM users WHERE name LIKE Tom%;1.

但，模糊查询本身性能就不高，所以，如果有需要like查询的情况，可以分析一下是否需要全文索引，也就是 FULLTEXT。

我之前修改过一个老系统中特别慢的接口，数据量够大，而且在很多字段用了 like查询，改用全文索引后，性能提升非常明显，10倍以上。

复制-- 创建全文索引 CREATE FULLTEXT INDEX idx_fulltext ON users (username); -- 全文搜索 SELECT * FROM users WHERE MATCH(username) AGAINST(张* IN BOOLEAN MODE);1.2.3.4.5.6.

查询结果集太小，优化器选择全表扫描

MySQL 的查询优化器会根据查询成本决定是否使用索引。如果查询结果集较小，MySQL 可能选择全表扫描。

例如：

复制SELECT * FROM users WHERE is_active = 1;1.

如果 is_active = 1 的记录占比非常高，MySQL 会认为全表扫描更高效。

解决方法

这种情况下，索引可能无法优化查询性能。

索引统计信息不准确

MySQL 会根据统计信息决定索引使用与否。如果统计信息不准确，可能导致索引失效。

索引的统计信息过期，影响查询优化器的决策。

解决方法

定期执行 ANALYZE TABLE，调整 innodb_stats_persistent，以保持统计信息准确。 手动更新统计信息：

复制ANALYZE TABLE users;1.

IS NULL 和 IS NOT NULL 操作

在某些 MySQL 版本中，IS NULL 和 IS NOT NULL 条件会导致索引失效。

例如：

复制SELECT * FROM users WHERE age IS NOT NULL;1.

MySQL 无法直接通过索引处理 NULL 值。

解决方法

尽量避免大量 NULL 值，或改用其他标识。

你学废了了吗？