索引是MySQL性能优化的核武器，但错误的使用姿势会让它沦为烧火棍。本文通过真实案例场景，深度解析8种让索引失效的高危操作，每个开发者都该刻进DNA！

一、左模糊查询：LIKE '%abc' 的致命陷阱

场景复现

用户表users有100万数据，phone字段建立普通索引。执行查询：

SELECT * FROM users WHERE phone LIKE '%1234'; 

耗时1.8秒，执行计划显示type=ALL（全表扫描）

致命原因

左模糊匹配使B+树无法确定搜索起点，索引失效

优化方案

-- 改用右模糊（索引生效）
SELECT * FROM users WHERE phone LIKE '1234%'; 
-- 耗时0.02秒，type=range

二、IS NULL判断：你以为的捷径其实是弯路

场景复现

订单表orders的coupon_id字段允许NULL且建有索引，查询未使用优惠券的订单：

SELECT * FROM orders WHERE coupon_id IS NULL;

执行计划显示key=null，未走索引

本质原因

B+树不存储NULL值的具体数据，需要通过全表扫描定位

优化策略

-- 添加默认值（如0表示无优惠券）
ALTER TABLE orders MODIFY coupon_id BIGINT NOT NULL DEFAULT 0;
-- 建立普通索引后查询
SELECT * FROM orders WHERE coupon_id = 0;

三、隐式类型转换：看不见的性能杀手

典型案例

用户表phone字段为varchar类型，但查询时使用数字：

SELECT * FROM users WHERE phone = 13800138000; -- 触发隐式转换

执行计划显示type=ALL，索引失效

底层原理

MySQL将phone字段转换为数字进行比较，等同于：

SELECT * FROM users WHERE CAST(phone AS UNSIGNED) = 13800138000;

正确姿势

SELECT * FROM users WHERE phone = '13800138000'; -- 使用字符串匹配

四、对索引列使用函数：聪明反被聪明误

事故现场

订单表created_at为datetime类型并建立索引，按日期查询：

SELECT * FROM orders WHERE DATE(created_at) = '2023-08-01';

导致全表扫描

破局之道

SELECT * FROM orders 
WHERE created_at BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59';

五、OR连接非索引字段：组合拳打空自己

错误示例

-- name有索引，age无索引
SELECT * FROM employees 
WHERE name = '张三' OR age > 30; 

实际执行时MySQL选择全表扫描

优化方案

-- 拆分成UNION查询
SELECT * FROM employees WHERE name = '张三'
UNION ALL
SELECT * FROM employees WHERE age > 30 AND name != '张三';

六、不符合最左前缀：复合索引的致命缺陷

复合索引（a,b,c）的失效场景：

WHERE b = 1 AND c = 2 --  缺少最左a字段
WHERE a = 1 AND c = 2 --  跳过了b字段

黄金法则

复合索引的查询条件必须包含最左连续字段，如同开保险箱需要按顺序转动密码盘

七、范围查询后的索引失效

典型陷阱

WHERE a > 100 AND b = 10 -- 索引(a,b)中b列无法生效

范围查询后的索引列如同断掉的桥，无法继续使用

解决方案

调整索引顺序为(b,a)或拆分成多个查询

八、不当使用不等于操作符

危险操作

SELECT * FROM products WHERE status != '下架';

当status有索引时，!=操作可能导致全表扫描

替代方案

SELECT * FROM products 
WHERE status IN ('在售', '预售', '缺货');

避坑总结表

索引优化的本质是让查询路径可预测。掌握这8大禁忌，配合EXPLAIN执行计划分析，你将真正掌控索引这把利剑。建议收藏本文，在每次编写SQL时对照检查，让性能优化成为肌肉记忆！

如果本文解决了你曾遇到的性能痛点，请点赞让更多开发者看到。你有过哪些索引翻车经历？欢迎在评论区分享交流！