前几天一个小伙伴说面试可能挂了，他说面试官问他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 值，或改用其他标识。
你学废了了吗？
