MySQL 索引不生效的情况

MySQL 索引不生效的 SQL 查询需要避免的情况

索引是提高 MySQL 查询性能的关键，但某些 SQL 写法会导致索引失效，从而影响查询效率。以下是需要避免的常见情况：

1. 使用 NOT、!= 或 <> 操作符

-- 索引可能失效
SELECT * FROM users WHERE status != 'active';

2. 使用 OR 连接条件（除非所有列都有索引）

-- 如果 age 或 name 中有一个没有索引，索引会失效
SELECT * FROM users WHERE age = 25 OR name = 'John';

3. 对索引列使用函数或运算

-- 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2023;
SELECT * FROM products WHERE price * 1.1 > 100;

4. 使用 LIKE 以通配符开头

-- 索引失效
SELECT * FROM users WHERE name LIKE '%ohn';-- 可以使用（索引有效）
SELECT * FROM users WHERE name LIKE 'Joh%';

5. 隐式类型转换

-- 如果 user_id 是字符串类型，索引会失效
SELECT * FROM users WHERE user_id = 12345;-- 应该写成
SELECT * FROM users WHERE user_id = '12345';

6. 使用 IN 或 NOT IN 时列表过大

-- 当列表非常大时，可能不会使用索引
SELECT * FROM users WHERE id IN (1,2,3,...,10000);

7. 多列索引未遵循最左前缀原则

-- 有联合索引 (col1, col2, col3)
-- 以下查询能使用索引
SELECT * FROM table WHERE col1 = 'a' AND col2 = 'b';-- 以下查询不能完全使用索引
SELECT * FROM table WHERE col2 = 'b' AND col3 = 'c';

8. 使用 IS NULL 或 IS NOT NULL

-- 索引可能失效
SELECT * FROM users WHERE phone IS NULL;

9. 使用 ORDER BY 与 WHERE 条件列不一致

-- 如果 name 有索引而 age 没有，排序会导致性能问题
SELECT * FROM users WHERE name LIKE 'A%' ORDER BY age;

10. 使用 DISTINCT、GROUP BY 或 UNION 不当

这些操作可能导致临时表创建，影响索引使用效率。

优化建议

1. 使用 EXPLAIN 分析查询执行计划
2. 合理设计索引，考虑查询模式
3. 避免全表扫描，限制返回数据量
4. 考虑使用覆盖索引（查询只使用索引列）

通过避免这些情况，可以确保 MySQL 查询能够有效利用索引，提高数据库性能。