MySQL索引失效的场景

MySQL索引是提高数据库查询性能的重要机制，但在某些情况下，索引可能会失效，导致查询无法使用索引而进行全表扫描。以下是MySQL索引失效的主要情况：

1. 查询条件导致索引失效

使用了不等于操作符（!= 或 <>）

当在WHERE子句中使用不等于操作符时，MySQL通常不会使用索引。

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

使用了IS NULL或IS NOT NULL

对索引列使用IS NULL或IS NOT NULL可能导致索引失效。

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

使用了函数或表达式

在索引列上使用函数或表达式会导致索引失效。

-- 索引失效，因为在索引列上使用了函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM users WHERE YEAR(create_time) = 2023;

使用了LIKE操作符且以通配符开头

当使用LIKE操作符进行模糊查询，且通配符（%）在开头时，索引会失效。

-- 索引失效，因为通配符在开头
SELECT * FROM users WHERE name LIKE '%john%';
SELECT * FROM users WHERE name LIKE '%john';

-- 索引有效，因为通配符不在开头
SELECT * FROM users WHERE name LIKE 'john%';

使用了OR连接条件

当WHERE子句中使用OR连接多个条件，且这些条件涉及不同的索引列时，索引可能失效。

-- 索引可能失效
SELECT * FROM users WHERE name = 'John' OR age = 30;

数据类型不匹配

当查询条件中的值与索引列的数据类型不匹配时，索引可能失效。

-- 假设id是整型，索引可能失效
SELECT * FROM users WHERE id = '123';

对索引列进行计算

在索引列上进行计算会导致索引失效。

-- 索引失效，因为在索引列上进行了计算
SELECT * FROM orders WHERE price + 10 > 100;

使用了NOT IN或NOT EXISTS

使用NOT IN或NOT EXISTS可能导致索引失效。

-- 索引可能失效
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE users.id = orders.user_id);

2. 索引设计导致失效

复合索引未遵循最左前缀原则

对于复合索引（多列索引），如果查询条件没有遵循最左前缀原则，索引可能失效。

-- 假设有复合索引(name, age)
-- 索引有效，使用了最左前缀
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name = 'John' AND age = 30;

-- 索引失效，没有使用最左前缀
SELECT * FROM users WHERE age = 30;

使用了ORDER BY但未使用索引列或顺序与索引不一致

当使用ORDER BY排序时，如果排序列不是索引列，或者排序顺序与索引顺序不一致，索引可能失效。

-- 假设有索引(name)
-- 索引可能失效，因为排序方向与索引不同
SELECT * FROM users ORDER BY name DESC;

-- 索引可能失效，因为排序列不是索引列
SELECT * FROM users ORDER BY age;

使用了DISTINCT但未使用索引列

当使用DISTINCT去重时，如果去重的列不是索引列，索引可能失效。

-- 索引可能失效，因为去重的列不是索引列
SELECT DISTINCT age FROM users;

使用了GROUP BY但未使用索引列或未遵循最左前缀原则

当使用GROUP BY分组时，如果分组的列不是索引列，或者对于复合索引没有遵循最左前缀原则，索引可能失效。

-- 假设有复合索引(name, age)
-- 索引有效，使用了最左前缀
SELECT name, COUNT(*) FROM users GROUP BY name;
SELECT name, age, COUNT(*) FROM users GROUP BY name, age;

-- 索引失效，没有使用最左前缀
SELECT age, COUNT(*) FROM users GROUP BY age;

使用了JOIN但连接条件未使用索引

当使用JOIN进行表连接时，如果连接条件没有使用索引，索引可能失效。

-- 索引可能失效，如果users.id或orders.user_id没有索引
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

3. 数据特性导致失效

索引列存在大量重复值

当索引列存在大量重复值时，MySQL可能认为使用索引效率不高，而选择全表扫描。

-- 假设status只有少量几个值，且大部分记录的status相同
-- 索引可能失效
SELECT * FROM users WHERE status = 'active';

表数据量过小

当表数据量过小时，MySQL可能认为全表扫描比使用索引更高效。

-- 对于小表，索引可能失效
SELECT * FROM small_table WHERE id = 1;

4. 优化器选择导致失效

查询优化器选择全表扫描

在某些情况下，即使可以使用索引，MySQL查询优化器也可能认为全表扫描更高效，特别是当表数据量很小或者需要返回大部分数据时。

-- 当返回的数据比例较大时，优化器可能选择全表扫描
SELECT * FROM users WHERE status = 'active';

使用了FORCE INDEX但索引不适用

当使用FORCE INDEX强制使用某个索引，但该索引不适用于当前查询时，MySQL可能会忽略该索引。

-- 索引可能失效，因为强制使用的索引不适用于查询
SELECT * FROM users FORCE INDEX (idx_name) WHERE age = 30;

使用了索引提示但索引不适用

类似于FORCE INDEX，使用USE INDEX或IGNORE INDEX也可能导致索引失效。

-- 索引可能失效，因为提示使用的索引不适用于查询
SELECT * FROM users USE INDEX (idx_name) WHERE age = 30;

使用了子查询且优化器无法优化

当使用子查询时，如果MySQL优化器无法将其优化为连接查询，索引可能失效。

-- 索引可能失效，因为优化器无法优化子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

使用了LIMIT但返回结果比例较大

当使用LIMIT限制返回结果，但实际需要扫描的数据比例较大时，索引可能失效。

-- 索引可能失效，因为需要扫描的数据比例较大
SELECT * FROM users WHERE status = 'active' LIMIT 1000;

如何避免索引失效

为了避免索引失效，可以采取以下措施：

1. 避免在索引列上使用函数或表达式：尽量在应用层处理数据，而不是在SQL中使用函数。

2. 避免使用不等于操作符：尽量使用等于操作符，或者使用其他方式替代不等于操作符。

3. 避免使用LIKE以通配符开头：尽量使用LIKE以固定值开头，或者使用全文索引替代。

4. 遵循复合索引的最左前缀原则：确保查询条件使用复合索引的最左列。

5. 确保数据类型匹配：确保查询条件中的值与索引列的数据类型匹配。

6. 避免在索引列上进行计算：尽量在应用层进行计算，而不是在SQL中。

7. 使用EXPLAIN分析查询执行计划：使用EXPLAIN命令分析查询的执行计划，查看是否使用了索引。

8. 合理设计索引：根据查询需求设计合理的索引，避免创建过多或过少的索引。

9. 定期维护索引：定期使用ANALYZE TABLE更新表的统计信息，帮助查询优化器选择合适的索引。

10. 考虑使用覆盖索引：设计索引使其包含查询所需的所有列，避免回表操作。

参考文档

• MySQL官方文档关于索引的使用：https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
• MySQL官方文档关于优化器如何使用索引：https://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html
• MySQL官方文档关于EXPLAIN的使用：https://dev.mysql.com/doc/refman/8.0/en/explain.html
• Percona关于MySQL索引失效的博客：https://www.percona.com/blog/2019/11/22/when-does-mysql-use-an-index-for-where-conditions/
• DigitalOcean关于MySQL索引优化的教程：https://www.digitalocean.com/community/tutorials/how-to-optimize-queries-and-tables-in-mysql