9年Java开发，SQL优化做了无数轮。最崩溃的场景是：明明建了索引，EXPLAIN也显示走了，但就是慢。今天聊四个让索引“失效”的经典陷阱，每个都是我线上真实踩过的。
一、隐式转换：MySQL“悄悄”把类型转了，索引就废了
现象：明明给字段建了索引，查询还是全表扫描
sql
-- 表结构
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`phone` varchar(20) NOT NULL, -- 手机号，varchar类型
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_phone` (`phone`)
);
-- 错误查询：用数字去匹配varchar字段
EXPLAIN SELECT * FROM user WHERE phone = 13800138000; -- 数字
EXPLAIN结果：
type: ALL（全表扫描）Extra: Using where
为什么？——MySQL隐式转换规则
当varchar字段和数字比较时，MySQL会把varchar转成数字再比较：
sql
-- 实际执行时变成了
SELECT * FROM user WHERE CAST(phone AS signed) = 13800138000;
函数操作导致索引失效。
解决方案
sql
-- ✅ 正确：用字符串匹配
SELECT * FROM user WHERE phone = '13800138000';
-- ✅ 反过来：如果字段是int，传字符串没问题（字符串会转数字）
SELECT * FROM user WHERE age = '18'; -- age是int，没问题
口诀： 字符串字段用字符串匹配，数字字段用数字匹配，类型要对齐。
二、函数操作：对索引列做计算，索引直接失效
场景1：对索引列使用函数
sql
-- 查询某一天的数据
EXPLAIN SELECT * FROM `order` WHERE DATE(create_time) = '2024-01-15';
问题： DATE(create_time) 对create_time列做了函数计算，索引失效。
解决方案：
sql
-- ✅ 改为范围查询
SELECT * FROM `order`
WHERE create_time >= '2024-01-15 00:00:00'
AND create_time < '2024-01-16 00:00:00';
场景2：对索引列做运算
sql
-- ❌ 错误：对索引列做运算
SELECT * FROM product WHERE price + 10 > 100;
-- ✅ 正确：把运算移到等号右边
SELECT * FROM product WHERE price > 90;
场景3：使用LENGTH等函数
sql
-- ❌ 错误
SELECT * FROM user WHERE LENGTH(name) > 5;
-- ✅ 正确：加一个冗余字段name_length，建索引
ALTER TABLE user ADD COLUMN name_length INT, ADD KEY idx_name_length(name_length);
-- 写入时计算好长度
常见的“索引杀手”函数：
| 函数 | 替代方案 |
|---|---|
DATE(create_time) | 范围查询 >= 和 < |
YEAR(create_time) | 范围查询或冗余字段 |
SUBSTRING(name,1,3) | 考虑ES或冗余字段 |
CONCAT(first_name, ' ', last_name) | 冗余字段 |
CAST(phone AS SIGNED) | 改参数类型 |
三、不等号（!=、<>）：索引可以用，但基本是“全表扫描”效果
现象：加了索引，但查询还是很慢
sql
EXPLAIN SELECT * FROM `order` WHERE status != 'PAID';
EXPLAIN结果：
type: ALL或rangerows: 表中大部分数据
为什么？——不等号会扫描大量数据
如果status字段90%都是PAID，查询!= PAID意味着要返回10%的数据。MySQL优化器认为：
- 走索引再回表，成本更高
- 不如直接全表扫描
解决方案
sql
-- 方案1：改成IN或等值查询
SELECT * FROM `order` WHERE status IN ('PENDING', 'CANCELED', 'REFUND');
-- 方案2：分区表，把不等于的单独分区
-- 方案3：用两个索引合并（MySQL 5.6+ 索引合并）
-- 方案4：接受全表扫描，但加覆盖索引
CREATE INDEX idx_status_cover ON `order`(status, id, amount); -- 覆盖索引，避免回表
记住：不等于查询基本等于索引失效，尽量改成等值或IN。
四、OR条件：左右两边都能用索引才行
现象：OR两边只有一个有索引，整个查询不走索引
sql
-- user_id有索引，name没有索引
EXPLAIN SELECT * FROM `order` WHERE user_id = 123 OR name = '张三';
EXPLAIN结果：
type: ALL（全表扫描）
为什么？——MySQL需要对两个条件的结果做OR合并
如果name没有索引，MySQL需要全表扫描来找到name='张三'的数据，然后和user_id=123的结果合并。既然已经全表扫描了，前面索引的优势就没了。
解决方案
sql
-- 方案1：给OR两边都建索引
CREATE INDEX idx_name ON `order`(name); -- 两边都有索引
-- 方案2：用UNION代替OR
SELECT * FROM `order` WHERE user_id = 123
UNION
SELECT * FROM `order` WHERE name = '张三';
-- 方案3：改成IN（如果OR的值是固定的）
SELECT * FROM `order` WHERE user_id IN (123, 456);
五、最左前缀原则：索引顺序错了，白建
场景：复合索引 (a, b, c)
sql
CREATE INDEX idx_a_b_c ON table(a, b, c);
哪些查询能走索引？
| 查询条件 | 是否能走索引 | 说明 |
|---|---|---|
WHERE a = 1 | ✅ 能 | 用到了a |
WHERE a = 1 AND b = 2 | ✅ 能 | 用到了a,b |
WHERE a = 1 AND b = 2 AND c = 3 | ✅ 能 | 用到了全部 |
WHERE a = 1 AND c = 3 | ⚠️ 部分 | 只用到了a，c用不到 |
WHERE b = 2 | ❌ 不能 | 没用到最左列a |
WHERE b = 2 AND c = 3 | ❌ 不能 | 没用到a |
WHERE a = 1 ORDER BY b | ✅ 能 | 索引排序 |
常见误区
sql
-- 误区1：范围查询右边的列用不到索引
WHERE a = 1 AND b > 10 AND c = 3
-- b用了索引，c用不到（范围查询右边的列失效）
-- 误区2：跳过了中间的列
WHERE a = 1 AND c = 3
-- 只有a用索引，c用不到
-- 误区3：顺序不对
WHERE b = 2 AND a = 1
-- MySQL优化器会自动调整顺序，实际能走到a,b
设计原则
把区分度高的列放左边，范围查询的列放右边。
sql
-- 区分度：status（只有几种值）vs user_id（几百万种）
-- ✅ 正确：区分度高的放左边
INDEX idx_user_status (user_id, status)
-- ❌ 错误：区分度低的放左边
INDEX idx_status_user (status, user_id)
六、LIKE查询：%放前面，索引失效
场景
sql
-- ❌ 以%开头，索引失效
SELECT * FROM user WHERE name LIKE '%张三%';
-- ✅ 以%结尾，索引有效
SELECT * FROM user WHERE name LIKE '张三%';
-- ❌ 中间有%，索引失效
SELECT * FROM user WHERE name LIKE '张%三';
为什么？——B+Tree从左到右匹配
%开头，MySQL不知道从哪个节点开始找，只能全表扫描。
解决方案
sql
-- 方案1：改成后缀匹配
WHERE name LIKE '张三%'
-- 方案2：用ES（Elasticsearch）做全文检索
-- 方案3：用MySQL全文索引（不推荐，功能弱）
ALTER TABLE user ADD FULLTEXT(name);
SELECT * FROM user WHERE MATCH(name) AGAINST('张三');
七、NULL值：索引列允许NULL，查询可能不走索引
场景
sql
-- 表结构：age允许NULL
CREATE TABLE user (age INT, KEY idx_age(age));
-- 查询NOT NULL走索引
SELECT * FROM user WHERE age IS NOT NULL; -- 可能不走
-- 查询NULL走索引
SELECT * FROM user WHERE age IS NULL; -- 可能走
-- 但用!=或<>可能不走
SELECT * FROM user WHERE age != 18; -- 可能全表扫描
最佳实践
sql
-- 设计时：索引列尽量设置NOT NULL DEFAULT
ALTER TABLE user MODIFY age INT NOT NULL DEFAULT 0;
-- 查询时：避免使用IS NULL / IS NOT NULL
-- 如果字段允许NULL，查询条件要考虑好
八、索引失效总结速查表
| 场景 | 失效原因 | 解决方案 |
|---|---|---|
phone = 13800138000 | 隐式类型转换 | 用字符串 '13800138000' |
DATE(create_time) = '2024-01-01' | 对索引列用函数 | 改为范围查询 |
price + 10 > 100 | 对索引列做运算 | 移到等号右边 |
status != 'PAID' | 不等号扫描大量数据 | 改IN或用覆盖索引 |
user_id = 123 OR name = '张三' | OR右边无索引 | UNION或两边都建索引 |
LIKE '%张三%' | %在开头 | 改成 '张三%' 或用ES |
age IS NOT NULL | NULL值处理 | 字段设NOT NULL |
a=1 AND c=3（复合索引a,b,c） | 跳过中间列 | 调整索引顺序 |
a=1 AND b>10 AND c=3 | 范围查询右边列失效 | 把范围放最后 |
九、EXPLAIN看懂这几个字段就够了
sql
EXPLAIN SELECT * FROM user WHERE phone = '13800138000';
| 字段 | 好 | 坏 | 说明 |
|---|---|---|---|
type | ref/range/const | ALL/index | ALL=全表扫描 |
key | 有索引名 | NULL | NULL=没用索引 |
rows | 越小越好 | 大 | 扫描的行数 |
Extra | Using index | Using filesort/Using temporary | Using index=覆盖索引 |
十、一句话避坑口诀
text
隐式转换索引废，函数运算全表扫。
不等号、OR条件坑，LIKE百分放前面。
复合索引左到右，范围查询右边倒。
NULL值要设默认，EXPLAIN先看再跑。
十一、互动一下
你遇到过最离谱的索引失效是什么？
有没有一个SQL优化了一整天，最后发现是类型写错了？
评论区聊聊👇
下期预告： 避坑7——事务隔离级别“我以为读到了最新数据”（脏读、不可重复读、幻读、MVCC原理）
我是小李，9年Java，产假中持续输出。点个赞，收藏防丢❤️
