Mysql导致B+Tree索引失效的场景汇总
在MySQL中使用B+Tree索引时，以下查询场景可能导致无法使用索引而触发全表扫描（Full Table Scan）。理解这些场景有助于优化索引设计和查询语句。
一、导致索引失效的常见场景
1.1、不符合最左前缀原则
联合索引（如INDEX(a, b, c)）要求查询条件必须从最左列开始，且连续。
-- 有效使用索引的条件：
WHERE a = 1; -- ✅使用索引a
WHERE a = 1 AND b = 2; -- ✅使用索引a,b
WHERE a = 1 AND b = 2 AND c = 3;-- ✅使用索引a,b,c
-- 索引失效的条件：
WHERE b = 2; -- ❌未指定a，无法使用索引
WHERE a = 1 AND c = 3; -- ❌跳过b，只能用到a的索引
WHERE b = 2 AND c = 3; -- ❌未指定a，无法使用索引
解决方法：调整查询条件顺序或重建联合索引。
1.2、对索引列使用函数或表达式
对索引列进行运算或函数处理后，索引无法匹配原始值。
-- 索引失效：
WHERE YEAR(create_time) = 2023; -- ❌ 函数操作
WHERE amount * 2 > 100; -- ❌ 表达式计算
WHERE UPPER(name) = 'JOHN'; -- ❌ 函数操作
-- 有效写法：
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'; -- ✅
WHERE amount > 100 / 2; -- ✅ 将计算移到右侧
1.3、隐式类型转换
查询条件与索引列类型不匹配，触发隐式转换。
-- 假设user_id是字符串类型（VARCHAR）：
WHERE user_id = 10086; -- ❌ 数字转字符串，索引失效
-- 有效写法：
WHERE user_id = '10086'; -- ✅ 类型一致
1.4、使用OR连接非索引列
若OR的一侧字段无索引，优化器可能选择全表扫描。
-- 假设age字段无索引：
WHERE name = 'Alice' OR age = 25; -- ❌ 若age无索引，触发全表扫描
-- 解决方法：为age单独建索引或使用UNION优化：
SELECT * FROM users WHERE name = 'Alice'
UNION
SELECT * FROM users WHERE age = 25;
1.5、范围查询后的列无法使用索引
联合索引中，若某一列使用范围查询（>、<、BETWEEN），其后的列无法继续使用索引。
-- 索引(a, b, c)：
WHERE a = 1 AND b > 10 AND c = 3; -- ❌ 只能用到a和b的索引，c无法使用
-- 解决方法：调整索引顺序（如`INDEX(a, c, b)`）或拆分查询。
1.6、使用!=或<>操作符
不等于操作符通常无法有效利用索引。
WHERE status != 'deleted'; -- ❌ 可能触发全表扫描
1.7、LIKE以通配符开头
前缀模糊查询（如LIKE '%abc'）无法利用索引。
WHERE title LIKE '%system'; -- ❌ 全表扫描
WHERE title LIKE 'system%'; -- ✅ 使用索引（后缀匹配）
1.8、数据量过少时优化器放弃索引
表中数据量极少（如几十行），优化器可能认为全表扫描更快。
解决方法：强制使用索引（慎用）：
SELECT * FROM users FORCE INDEX(index_name) WHERE ...;
二、如何验证索引是否生效
使用EXPLAIN分析查询计划，关注以下字段：
- type：若为ALL，表示全表扫描；ref或range表示使用索引。
- key：显示实际使用的索引名称。
- rows：预估扫描的行数（越小越好）。
示例：
EXPLAIN SELECT * FROM users WHERE name = 'John';
三、总结
- 不符合最左前缀：调整联合索引顺序或拆分索引。
- 索引列参与计算/函数：重写查询条件，避免对索引列操作。
- 隐式类型转换：确保查询条件与索引列类型一致。
- OR连接非索引列：改用UNION或为相关字段添加索引。
- 范围查询后的列失效：将等值查询列放在联合索引的前部。
- 不使用!=和<>
- like不以通配符开头
- 定期使用EXPLAIN分析慢查询，调整索引策略。
慢查询相关内容可以查看我的另一篇文章：《MySQL慢查询与查询优化器》
全部评论