MYSQL索引失效简介

什么是索引失效，它会带来什么问题

        索引失效可以简单理解为，在SQL语句执行时没有使用到索引列对数据库中的数据页进行查询。

        索引失效会导致查询性能显著下降，并可能引发一系列问题。

        如：全表扫描、系统资源消耗激增、并发性能恶化等。

索引失效场景描述：

1、索引列参与运算或函数处理

场景：对索引列进行运算（如加减、函数转换等）。

SELECT * FROM users WHERE YEAR(create_time) = 2023;  -- create_time有索引

原因：
        索引存储的是原始值，运算后的值无法直接匹配索引。
解决方法：
        改写为范围查询：

SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

2、隐式类型转换

场景：索引列与查询值类型不一致（如字符串列用数字查询）。

SELECT * FROM users WHERE user_id = 123;  -- user_id是VARCHAR类型

原因：
        MySQL会将列值隐式转换为数字，导致索引失效。
解决方法：
        保持类型一致：

SELECT * FROM users WHERE user_id = '123';

3、OR连接非索引列

场景：OR连接的条件中有列未建立索引。

SELECT * FROM users WHERE a = 1 OR b = 2;  -- 仅a有索引，b无索引

原因：
        优化器可能选择全表扫描而非索引合并。
解决方法：

• 为b列添加索引；

• 拆分为UNION查询：

SELECT * FROM users WHERE a = 1 
UNION 
SELECT * FROM users WHERE b = 2;

4、LIKE以通配符开头

场景：模糊查询的通配符（%）在开头。

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

原因：
        B-Tree索引无法匹配开头不确定的值。
解决方法：

• 改用全文索引（如MATCH AGAINST）；

• 调整查询为后缀匹配：LIKE 'John%'。

5、违反最左前缀原则

场景：联合索引未按最左列作为查询起点。
 

CREATE INDEX idx_a_b_c ON table(a, b, c);
SELECT * FROM table WHERE b = 2 AND c = 3;  -- 未包含a列

原因：
        联合索引按(a, b, c)顺序构建，跳过a列无法使用索引。
解决方法：

• 查询条件包含联合索引的最左列；

• 调整索引顺序或新建索引。

6、数据量小或低选择性

场景：

• 表数据量极小（如几百行）；

• 索引列重复值过多（如性别列）。

原因：

        优化器认为全表扫描更快。
解决方法：

• 对低选择性列谨慎建索引；

• 使用FORCE INDEX强制使用索引（需验证效果）。

-- 建议使用索引 idx_age
EXPLAIN SELECT * FROM users
USE INDEX (idx_age)
WHERE age > 30;

-- 强制使用索引 idx_age
EXPLAIN SELECT * FROM users
FORCE INDEX (idx_age)
WHERE age > 30;

7、使用范围查询后的列失效

场景：联合索引中，范围查询（>、<、BETWEEN）后的列无法使用索引。

CREATE INDEX idx_a_b ON table(a, b);
SELECT * FROM table WHERE a > 100 AND b = 5;  -- b列无法利用索引

原因：
        范围查询后的索引列停止匹配。
解决方法：
        调整索引顺序（如(b, a)），或拆分查询条件。

8、使用否定条件（NOT、!=）

场景：

SELECT * FROM users WHERE status != 'active';  -- status有索引

原因：
        否定条件需扫描大部分数据，优化器可能放弃索引。
解决方法：