MySQL 索引失效了吧

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

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

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

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

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

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

SELECT * FROM users WHERE LEFT(name, 3) = 'Tom';

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

解决方法

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

  SELECT * FROM users WHERE name LIKE 'Tom%';

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

CREATE INDEX idx_name_prefix ON users (LEFT(name, 3));

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

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

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

例如

SELECT * FROM users WHERE phone_number = 1234567890;

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

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

SELECT * FROM users WHERE phone_number = '1234567890';

使用不等操作符

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

例如

SELECT * FROM users WHERE age <> 30;

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

解决方法

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

-- 优化方案1：IN 列表
SELECT * FROM users WHERE status IN (2, 3, 4);

-- 优化方案2：逻辑重构
SELECT * FROM users WHERE status > 1 AND status < 5;

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

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

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

SELECT * FROM users WHERE name = 'Tom' OR age = 30;

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

解决方法

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

SELECT * FROM users WHERE name = 'Tom'
UNION
SELECT * FROM users WHERE age = 30;

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

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

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

例如

SELECT * FROM users WHERE age > 30 AND name = 'Tom';

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

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

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

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

索引列的前缀未完全匹配

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

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

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

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

解决方法

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

SELECT * FROM users WHERE name = 'Tom' AND age = 30;

LIKE 模式中通配符使用不当

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

例如

SELECT * FROM users WHERE name LIKE '%Tom';

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

解决方法

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

SELECT * FROM users WHERE name LIKE 'Tom%';

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

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

-- 创建全文索引
CREATE FULLTEXT INDEX idx_fulltext ON users (username);

-- 全文搜索
SELECT * FROM users 
WHERE MATCH(username) AGAINST('张*' IN BOOLEAN MODE);

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

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

例如

SELECT * FROM users WHERE is_active = 1;

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

解决方法

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

索引统计信息不准确

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

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

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

ANALYZE TABLE users;

IS NULL 和 IS NOT NULL 操作

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

例如

SELECT * FROM users WHERE age IS NOT NULL;

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

解决方法

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

你学废了了吗？

  推荐阅读：

1. MySQL探秘之旅：绕不开的数据库事务
   
2. 百度二面，MySQL 怎么做权重搜索？
   
3. 后端程序员必备：15个MySQL表设计的经验准则
   

4. mysql主库更新后，从库都读到最新值了，主库还有可能读到旧值吗？
   

5. 来看看一位阿里P9+的年薪和资产。