全面了解MySQL九种索引失效场景
共 3181字，需浏览 7分钟
·
2023-05-13 06:22
MySQL索引是提高查询性能的重要工具，但是如果不合理使用或者不恰当的设计索引，就有可能出现索引失效的情况。本篇博客将介绍九种可能导致MySQL索引失效的场景，并给出相应的例子。
1.在索引列上进行函数操作
如果在索引列上进行函数操作，MySQL就无法使用该索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users WHERE YEAR(create_time) = 2022;
可以将查询条件改为不对索引列进行函数操作的形式：
SELECT * FROM users WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-12-31 23:59:59';
2.对索引列进行类型转换
如果对索引列进行类型转换，MySQL同样无法使用该索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users WHERE CAST(id AS CHAR) = '123';
可以将查询条件改为不对索引列进行类型转换的形式：
SELECT * FROM users WHERE id = 123;
3.使用LIKE操作符的通配符开头
如果使用LIKE操作符的通配符开头，MySQL无法使用索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users WHERE name LIKE '%john';
可以将查询条件改为不使用通配符开头的形式：
SELECT * FROM users WHERE name LIKE 'john%';
4.对NULL值进行查询
如果对NULL值进行查询，MySQL无法使用索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users WHERE name IS NULL;
可以将查询条件改为不对NULL值进行查询的形式：
SELECT * FROM users WHERE name = '';
5.使用OR连接多个条件
如果使用OR连接多个条件，MySQL无法使用索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users WHERE name = 'john' OR age = 18;
可以将查询条件改为使用UNION ALL连接多个子查询的形式：
SELECT * FROM users WHERE name = 'john'
UNION ALL
SELECT * FROM users WHERE age = 18;
6.在ORDER BY子句中对索引列
如果在ORDER BY子句中对索引列进行排序，MySQL可能无法使用该索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users WHERE age > 18 ORDER BY age;
可以将查询条件改为使用其他列进行排序的形式：
SELECT * FROM users WHERE age > 18 ORDER BY create_time;
7.在GROUP BY子句中对索引列进行分组
如果在GROUP BY子句中对索引列进行分组，MySQL可能无法使用该索引。例如，下面的查询将会导致索引失效：
SELECT name, COUNT(*) FROM users GROUP BY name;
可以将查询条件改为使用其他列进行分组的形式：
SELECT age, COUNT(*) FROM users GROUP BY age;
8.多表关联时使用非索引列进行连接
如果在多表关联时使用非索引列进行连接，MySQL可能无法使用索引。例如，下面的查询将会导致索引失效：
SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE orders.create_time > '2022-01-01';
可以将查询条件改为使用索引列进行连接的形式：
SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE orders.user_id > 0 AND orders.create_time > '2022-01-01';
9.最左匹配原则
对于索引列的顺序，最左匹配原则是MySQL使用索引的基本原则之一，要求查询条件中使用的索引列必须按照索引顺序的左侧列开始匹配。如果查询条件中的列没有按照索引列的顺序从左往右进行匹配，MySQL可能无法使用索引，导致索引失效。
如果使用的索引是(name, age)，而查询条件为WHERE age > 18 AND name = 'Tom'，由于age并不在索引列的最左侧，索引可能会失效，因此需要重新设计索引或者改变查询条件。为了遵守最左匹配原则，可以将索引改为(age, name)，这样就可以避免索引失效了。
综上所述，MySQL索引失效的场景有很多，开发者在设计和使用索引时需要注意避免上述情况的出现，从而提高查询性能和效率。
end
* 版权声明： 转载文章和图片均来自公开网络，版权归作者本人所有，推送文章除非无法确认，我们都会注明作者和来源。如果出处有误或侵犯到原作者权益，请与我们联系删除或授权事宜。
长按识别图中二维码
关注获取更多资讯
不点关注，我们哪来故事？