一次索引失效，让查询从毫秒级暴跌到秒级！这些坑你踩过几个？

测试环境准备

-- 创建测试表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT,
    status TINYINT,
    created_at DATETIME,
    INDEX idx_name (name),
    INDEX idx_email (email),
    INDEX idx_age (age),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_name_age (name, age)
);

-- 插入50万测试数据
DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 500000 DO
        INSERT INTO users (name, email, age, status, created_at) 
        VALUES (CONCAT('用户', i), CONCAT('user', i, '@email.com'), 
                FLOOR(RAND() * 100), i % 2, NOW() - INTERVAL FLOOR(RAND() * 365) DAY);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insert_test_data();

索引失效的八大场景

1. 最左前缀原则失效

❌ 错误写法

-- 联合索引 idx_name_age (name, age)
EXPLAIN SELECT * FROM users WHERE age = 25;

执行计划： type: ALL（全表扫描）

✅ 正确写法

-- 使用联合索引的第一个字段
EXPLAIN SELECT * FROM users WHERE name = '用户1000';
-- 同时使用两个字段
EXPLAIN SELECT * FROM users WHERE name = '用户1000' AND age = 25;

执行计划： type: ref（索引扫描）

要点：联合索引像电话簿，必须先找姓才能找名

2. 在索引列上运算

❌ 错误写法

-- 在索引列上使用函数
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 对索引列进行运算
EXPLAIN SELECT * FROM users WHERE age + 1 = 30;

✅ 正确写法

-- 使用范围查询取代函数
EXPLAIN SELECT * FROM users 
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
-- 调整运算顺序
EXPLAIN SELECT * FROM users WHERE age = 29;

性能对比：错误写法耗时 1200ms vs 正确写法 15ms

3. 使用前导通配符

❌ 错误写法

-- 前导%导致索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '%用户%';
EXPLAIN SELECT * FROM users WHERE email LIKE '%abc%';

✅ 正确写法

-- 非前导%可以使用索引
EXPLAIN SELECT * FROM users WHERE name LIKE '用户%';
-- 使用全文索引解决模糊查询
ALTER TABLE users ADD FULLTEXT(name);
EXPLAIN SELECT * FROM users WHERE MATCH(name) AGAINST('用户');

要点：LIKE '张%' ✅ 能用索引，LIKE '%张' ❌ 不能

4. 隐式类型转换

❌ 错误写法

-- email是字符串类型，用数字查询导致类型转换
EXPLAIN SELECT * FROM users WHERE email = 123456;
-- 字符串与数字比较
EXPLAIN SELECT * FROM users WHERE age = '25';

✅ 正确写法

-- 保持类型一致
EXPLAIN SELECT * FROM users WHERE email = 'user1000@email.com';
EXPLAIN SELECT * FROM users WHERE age = 25;

诊断方法

-- 查看执行计划中的Extra字段
EXPLAIN SELECT * FROM users WHERE email = 123456;
-- 如果看到Using where; Using index，说明发生了类型转换

5. OR条件使用不当

❌ 错误写法

-- OR条件中有一个字段无索引
EXPLAIN SELECT * FROM users WHERE name = '用户1000' OR email = 'user1000@email.com';
-- 假设name有索引，但other_field无索引

✅ 正确写法

-- 使用UNION替代OR
EXPLAIN 
SELECT * FROM users WHERE name = '用户1000'
UNION
SELECT * FROM users WHERE email = 'user1000@email.com';

-- 为所有OR条件字段建立索引
ALTER TABLE users ADD INDEX idx_email (email);

6. 不等于(!= / <>)查询

❌ 错误写法

-- 不等于查询无法使用索引
EXPLAIN SELECT * FROM users WHERE status != 1;
EXPLAIN SELECT * FROM users WHERE age <> 50;

✅ 正确写法

-- 改写为范围查询
EXPLAIN SELECT * FROM users WHERE status = 0;
-- 使用IN取代不等于
EXPLAIN SELECT * FROM users WHERE status IN (0, 2, 3);
-- 组合使用等于和范围查询
EXPLAIN SELECT * FROM users WHERE status = 0 OR status > 2;

7. ORDER BY使用不当

❌ 错误写法

-- ORDER BY字段与WHERE字段无关联
EXPLAIN SELECT * FROM users WHERE name = '用户1000' ORDER BY created_at;
-- 排序方向不一致
EXPLAIN SELECT * FROM users WHERE name LIKE '用户%' ORDER BY name ASC, age DESC;

✅ 正确写法

-- 为ORDER BY字段建立复合索引
ALTER TABLE users ADD INDEX idx_name_created_at (name, created_at);
EXPLAIN SELECT * FROM users WHERE name = '用户1000' ORDER BY created_at;

-- 排序方向一致
EXPLAIN SELECT * FROM users WHERE name LIKE '用户%' ORDER BY name ASC, age ASC;

8. 数据分布与索引选择

❌ 忽视数据分布

-- 当status=1的数据超过30%时，MySQL可能选择全表扫描
EXPLAIN SELECT * FROM users WHERE status = 1;

✅ 针对性优化

-- 对于低区分度字段，思考其他优化方式
-- 使用覆盖索引
EXPLAIN SELECT id, status FROM users WHERE status = 1;
-- 强制使用索引
EXPLAIN SELECT * FROM users FORCE INDEX(idx_status) WHERE status = 1;

性能对比测试

-- 场景：查询姓张的30岁用户
-- ❌ 索引失效写法
SELECT * FROM users WHERE age = 30 AND name LIKE '%张%';
-- 执行时间：850ms

-- ✅ 正确写法
SELECT * FROM users WHERE name LIKE '张%' AND age = 30;
-- 执行时间：12ms

-- 性能提升：70倍！

实用诊断技巧

1. 查看执行计划

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '用户1000';
-- 关注：type、key、rows、Extra

2. 索引使用分析

-- 查看索引使用情况
SHOW INDEX FROM users;
-- 查看索引选择性
SELECT COUNT(DISTINCT name)/COUNT(*) AS selectivity FROM users;

3. 强制使用索引

-- 当优化器选择不当时
SELECT * FROM users FORCE INDEX(idx_name) WHERE name LIKE '张%';

总结

场景	错误写法	正确写法	性能提升
最左前缀	WHERE age=25	WHERE name='张' AND age=25	50倍
列运算	WHERE age+1=30	WHERE age=29	80倍
前导%	LIKE '%张'	LIKE '张%'	60倍
类型转换	WHERE email=123	WHERE email='123'	40倍

记住这些规则，避免索引失效：

1. ✅ 遵循最左前缀原则
2. ✅ 避免在索引列上运算
3. ✅ LIKE查询避免前导%
4. ✅ 保持数据类型一致
5. ✅ 谨慎使用OR条件
6. ✅ 避免使用不等于
7. ✅ 合理设计ORDER BY
8. ✅ 思考数据分布特征

掌握这些技巧，让你的查询性能飞起来！