Mysql索引失效的常见场景

在 MySQL 中，索引是提高查询性能的重要机制，但某些情况下索引可能会失效，导致查询性能下降。以下是一些常见的索引失效场景及其原因：

一、索引失效的常见场景

1. 使用函数或表达式

如果在查询条件中对索引列使用了函数或表达式，MySQL 通常会放弃使用索引。

示例：

SELECT * FROM users WHERE YEAR(birthdate) = 1990;

原因： YEAR(birthdate) 是一个函数，MySQL 无法直接利用 birthdate 列上的索引进行优化。

解决方案： 避免在查询条件中对索引列使用函数。可以通过以下方式优化：

SELECT * FROM users WHERE birthdate BETWEEN '1990-01-01' AND '1990-12-31';

2. 隐式类型转换

当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL 会进行隐式类型转换，导致索引失效。

示例：

SELECT * FROM users WHERE age = '25'; -- age 是 INT 类型

原因： '25' 是字符串类型，而 age 列是 INT 类型。MySQL 在比较时会进行类型转换，导致索引失效。

解决方案： 确保查询条件中的数据类型与索引列的数据类型一致：

SELECT * FROM users WHERE age = 25;

3. 使用 OR 逻辑

在查询条件中使用 OR 时，MySQL 可能无法有效利用索引。

示例：

SELECT * FROM users WHERE age = 25 OR name = 'Alice';

原因： OR 逻辑可能导致 MySQL 无法同时利用 age 和 name 列上的索引。

解决方案： 可以将查询拆分为两个子查询，并使用 UNION 合并结果：

SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE name = 'Alice';

4. 模糊查询（LIKE）

如果模糊查询的模式以通配符（% 或 _）开头，索引通常会失效。

示例：

SELECT * FROM users WHERE name LIKE '%Alice%';

原因： 以通配符开头的模式无法利用索引进行快速查找。

解决方案： 如果可能，尽量将通配符放在模式的末尾：

SELECT * FROM users WHERE name LIKE 'Alice%';

5. 联合索引的非最左匹配

在联合索引中，如果查询条件没有使用索引的最左列，索引可能会失效。

示例： 假设有一个联合索引 (age, name)：

SELECT * FROM users WHERE name = 'Alice';

原因： 查询条件没有使用联合索引的最左列 age，导致索引失效。

解决方案： 确保查询条件使用联合索引的最左列：

SELECT * FROM users WHERE age = 25 AND name = 'Alice';

6. 索引列上有计算或表达式

如果在查询条件中对索引列进行了计算或表达式操作，索引可能会失效。

示例：

SELECT * FROM users WHERE age + 1 = 26;

原因： age + 1 是一个表达式，MySQL 无法直接利用 age 列上的索引。

解决方案： 将表达式改写为可以直接利用索引的形式：

SELECT * FROM users WHERE age = 25;

7. 数据量过小

如果表中的数据量过小，MySQL 可能会认为全表扫描比索引扫描更高效，从而放弃使用索引。

示例：

SELECT * FROM users WHERE age = 25;

原因： 如果 users 表的数据量非常小，MySQL 认为全表扫描的开销小于索引扫描。

解决方案： 可以通过 ANALYZE TABLE 优化表的统计信息，或者调整 MySQL 的配置参数（如 innodb_stats_auto_recalc）。

8. 索引列上有隐式类型转换

如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL 会进行隐式类型转换，导致索引失效。

示例：

SELECT * FROM users WHERE age = '25'; -- age 是 INT 类型

原因： '25' 是字符串类型，而 age 列是 INT 类型。MySQL 在比较时会进行类型转换，导致索引失效。

解决方案： 确保查询条件中的数据类型与索引列的数据类型一致：

SELECT * FROM users WHERE age = 25;

9. 使用 NOT 或 <>

在查询条件中使用 NOT 或 <> 时，索引可能会失效。

示例：

SELECT * FROM users WHERE age <> 25;

原因： <> 操作符可能导致 MySQL 无法有效利用索引。

解决方案： 尽量避免使用 NOT 或 <>，或者通过其他方式优化查询逻辑。

10. 索引列上有 IS NULL 或 IS NOT NULL

在查询条件中使用 IS NULL 或 IS NOT NULL 时，索引可能会失效。

示例：

SELECT * FROM users WHERE age IS NULL;

原因： IS NULL 或 IS NOT NULL 操作符可能导致 MySQL 无法有效利用索引。

解决方案： 如果可能，尽量避免使用 IS NULL 或 IS NOT NULL，或者通过其他方式优化查询逻辑。

11. 索引列上有 IN 或 NOT IN

在查询条件中使用 IN 或 NOT IN 时，索引可能会失效。

示例：

SELECT * FROM users WHERE age IN (25, 30);

原因： IN 或 NOT IN 操作符可能导致 MySQL 无法有效利用索引。

解决方案： 可以将查询拆分为多个子查询，并使用 UNION 合并结果：

SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE age = 30;

12. 索引列上有 BETWEEN 或 NOT BETWEEN

在查询条件中使用 BETWEEN 或 NOT BETWEEN 时，索引可能会失效。

示例：

SELECT * FROM users WHERE age BETWEEN 25 AND 30;

原因： BETWEEN 或 NOT BETWEEN 操作符可能导致 MySQL 无法有效利用索引。

解决方案： 确保查询条件中的范围是连续的，避免断断续续的范围。

13. 索引列上有 ORDER BY 或 GROUP BY

在查询中使用 ORDER BY 或 GROUP BY 时，索引可能会失效。

示例：

SELECT * FROM users ORDER BY age;

原因： 如果查询中没有使用索引列进行排序或分组，MySQL 可能会放弃使用索引。

解决方案： 确保查询中使用索引列进行排序或分组。

14. 索引列上有 DISTINCT

在查询中使用 DISTINCT 时，索引可能会失效。

示例：

SELECT DISTINCT age FROM users;

原因： DISTINCT 操作可能导致 MySQL 无法有效利用索引。

解决方案： 尽量避免使用 DISTINCT，或者通过其他方式优化查询逻辑。

15. 索引列上有 JOIN

在查询中使用 JOIN 时，索引可能会失效。

示例：

SELECT users.name, orders.order_id
FROM users
JOIN orders ON