分享15个Mysql索引失效的场景

当进行MySQL查询时，优秀的索引设计可以提高查询性能，但如果失效了，索引将不再发挥任何作用，反而会导致性能下降甚至全表扫描。接下来，我们将介绍MySQL索引失效的15种场景以及如何解决它们。

1. 对索引列做函数操作

如果在查询条件中对索引列使用了函数操作，如下所示：

SELECT * FROM user WHERE YEAR(created_at) = '2021';

虽然函数不一定导致索引失效，但是在MySQL中，大多数函数都会导致索引失效。因此建议在查询中避免对索引列进行任何函数操作。

2. 对索引列进行隐式类型转换

如果查询条件中对索引列进行了隐式类型转换，如下所示：

SELECT * FROM user WHERE created_at = '2021-01-01';

由于MySQL使用了类型转换，所以无法使用索引。因此建议在查询中尽量使用列原本的数据类型，以避免类型转换导致索引失效。

3. 使用LIKE查询时以通配符开头

如果在LIKE查询中将通配符放在开头，如下所示：

SELECT * FROM user WHERE name LIKE '%John';

由于MySQL无法使用索引中的模糊匹配，因此可以考虑使用全文本搜索来优化查询。

4. 使用!= 查询或 NOT IN 查询

如果查询条件使用了!=或NOT IN，如下所示：

SELECT * FROM user WHERE age != 18;

或

SELECT * FROM user WHERE age NOT IN(18,20);

这将迫使MySQL扫描整个表，而不使用索引。

5. 对列进行NULL值检查

如果查询条件使用了IS NULL 或者IS NOT NULL，如下所示：

SELECT * FROM user WHERE name IS NULL;

或

SELECT * FROM user WHERE name IS NOT NULL;

由于NULL值在索引中没有明确的值，因此MySQL无法使用索引，只能进行全表扫描。

6. 不同类型的列进行比较

如果在查询条件中比较了不同类型的列，如下所示：

SELECT * FROM user WHERE age = '18';

因为此时MySQL需要进行类型转换，因此无法使用索引进行查询。

7. 使用表达式或函数索引

如果创建的索引是通过某些函数或表达式来实现的，如下所示：

CREATE INDEX ix_age ON user(age + 1);

虽然这可能会使您的索引更加灵活，但它也可能导致索引失效，因为MySQL无法在查询中重写带有这些函数或表达式的查询。

8. 连接条件使用OR

如果在连接条件中使用了OR，如下所示：

SELECT * FROM user JOIN orders ON user.id = orders.user_id OR user.name = 'John';

如果其中任何一个条件无法使用索引进行查询，则MySQL将扫描整个表。

9. 查询语句中存在子查询

如果查询语句中存在子查询，如下所示：

SELECT * FROM user WHERE age IN(SELECT MAX(age) FROM user);

MySQL无法使用这种情况下的索引。

10. 多个范围条件查询

如果您在查询中有多个范围条件，如下所示：

SELECT * FROM user WHERE age BETWEEN 18 AND 25 AND salary BETWEEN 3000 AND 5000;

在这种情况下，MySQL只能使用一个范围条件，无法同时使用多个范围条件。

11. 使用排序和分组

如果在查询中使用排序和分组，如下所示：

SELECT age, COUNT(*) as num FROM user WHERE gender = 'male' GROUP BY age HAVING num > 10 ORDER BY age DESC;

虽然这在某些情况下是必要的，但是对于大型表，使用排序和分组将导致MySQL无法使用索引。

12. 多表查询

如果在多表查询中，其中一个表没有使用索引，则MySQL将扫描整个表，从而降低性能。

13. 多列索引顺序不正确

如果您为多列创建了索引，但顺序不正确，则MySQL无法使用该索引，如下所示：

CREATE INDEX ix_name_age ON user(age, name);

在这里，MySQL只能使用单列索引。

14. 过多的索引

虽然索引可以提高查询性能，但如果创建了太多索引，则会使查询性能下降，因为它们需要额外的存储和更新。

15. 数据分布不均匀

如果数据的分布不是均匀的，则MySQL将无法利用索引的优势。例如，在一个表中，如果只有少数记录有特定的标记或类型，而大多数记录没有，则使用这个标记或类型的查询将无法使用索引。这种情况下，可以考虑使用全文本搜索或其他技术来优化查询。

以上就是15个MySQL索引失效的场景了，在实际开发中我们需要根据具体情况来优化索引，以加快查询速度。