MySQL 多字段查询索引失效问题分析与解决方案

索引失效的常见原因

当进行多字段查询时，索引可能会失效的主要原因包括：

1. 未遵循最左前缀原则：联合索引中未从最左列开始使用
2. 使用了范围查询：在联合索引中使用了范围查询(>, <, BETWEEN等)后，后续列的索引会失效
3. 使用了函数或表达式：对索引列使用了函数或运算
4. 使用了OR条件：除非OR两侧的列都有索引
5. 数据类型不匹配：如字符串列与数字比较
6. 使用了NOT、!=、<>操作符
7. LIKE以通配符开头：如LIKE '%abc'

解决方案

1. 合理设计联合索引

-- 好的联合索引设计
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 查询能使用索引的情况
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM users WHERE last_name = 'Smith';

2. 避免索引列上的计算和函数

-- 不好的写法(索引失效)
SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 好的写法(能使用索引)
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

3. 处理范围查询

-- 范围查询后索引失效
SELECT * FROM users WHERE age > 30 AND last_name = 'Smith';

-- 解决方案1: 调整索引顺序(如果业务允许)
CREATE INDEX idx_lastname_age ON users(last_name, age);

-- 解决方案2: 使用索引合并
SELECT * FROM users WHERE last_name = 'Smith' AND age > 30;

4. 使用索引提示

-- 强制使用特定索引
SELECT * FROM users FORCE INDEX(idx_name_age) WHERE first_name = 'John' AND age > 25;

5. 使用覆盖索引

-- 只查询索引包含的列，避免回表
SELECT last_name, first_name FROM users WHERE last_name = 'Smith';

6. 处理OR条件

-- 不好的写法(除非name和age都有独立索引)
SELECT * FROM users WHERE name = 'John' OR age = 30;

-- 好的写法
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE age = 30;

高级优化技巧

1. 使用索引条件下推(ICP)：MySQL 5.6+支持
2. 使用MRR优化：减少随机IO
3. 使用BKA连接算法：批量键访问
4. 考虑使用复合索引的列顺序：高选择性列在前

诊断工具

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE last_name = 'Smith' AND age > 30;

-- 查看索引使用情况
SHOW INDEX FROM users;

-- 开启优化器跟踪(MySQL 5.6+)
SET optimizer_trace="enabled=on";
SELECT * FROM users WHERE ...;
SELECT * FROM information_schema.optimizer_trace;
SET optimizer_trace="enabled=off";

通过合理设计索引、编写优化查询语句和使用适当的查询技术，可以显著提高多字段查询的性能并避免索引失效问题。