导致MySQL索引失效的场景(随时补充)

平时开发种很少注意索引的使用,但在数据量大的时候还是很有必要的,然而索引失效的场景有很多,有时也很难发现问题所在,以下总结一些MySQL索引失效的场景,后续随时补充!

1.对索引字段进行了计算或函数处理。例如，如果你有一个索引字段age，但是你在查询时使用了WHERE age + 1 > 18这样的表达式，那么索引将不会被命中，因为MySQL无法通过索引来评估这个表达式。

CREATE TABLE test (id INT, age INT);
INSERT INTO test VALUES(1, 18), (2, 19);
SELECT * FROM test WHERE age + 1 > 18;

2.在索引字段上使用了LIKE关键字，并且通配符%没有出现在字符串的开始位置。例如，如果你有一个索引字段name，并且执行WHERE name LIKE '%John'这样的查询，那么索引将不会被命中，因为在这种情况下MySQL无法使用索引。

CREATE TABLE test (id INT, name VARCHAR(50));
INSERT INTO test VALUES(1, 'John'), (2, 'Jane');
SELECT * FROM test WHERE name LIKE '%John';

3.对索引字段使用了不等于操作符<>或!=。例如，如果你有一个索引字段active，并且执行WHERE active != 1这样的查询，那么索引将不会被命中 

CREATE TABLE test (id INT, active TINYINT);
INSERT INTO test VALUES(1, 1), (2, 0);
SELECT * FROM test WHERE active != 1;

4.对索引字段使用了IS NULL或IS NOT NULL操作。例如，如果你有一个索引字段email，并且执行WHERE email IS NULL这样的查询，那么索引将不会被命中。

CREATE TABLE test (id INT, email VARCHAR(50));
INSERT INTO test VALUES(1, NULL), (2, 'test@example.com');
SELECT * FROM test WHERE email IS NULL;

5.使用了全表扫描的操作，如COUNT(*)，DELETE FROM table等，这时也会导致索引未命中。

6.当对一个包含多个列的复合索引进行部分匹配时（即只在其中一部分列上做条件限制），如果所使用的列顺序与创建复合索引的顺序不同，则可能无法利用到该复合索引。

CREATE TABLE t (a INT,b INT,c INT,d INT,key idx(a,b,c));
INSERT INTO t VALUES(1,2,3,4),(5,6,7,8);
 
SELECT * FROM t WHERE b=2; -- 能够命中复合索引idx
SELECT * FROM t WHERE c=3; -- 不能命中复合索引idx
 
SELECT * FROM t WHERE b=2 AND c=3; -- 命中复合索引idx
SELECT * FROM t WHERE c=3 AND b=2; -- 不能命中复合索引idx

7.修改表结构或者删除/添加字段会导致已有索引失效。

8.更新统计信息或者优化表等操作可能会使索引失效。

9.如果MySQL认为全部扫描比使用索引更快，可能会选择放弃使用索引，这种情况一般发生在数据量非常小的表上。

10.索引页满了也可能导致索引失效。

11.使用UNION、INTERSECT 或 EXCEPT 操作符连接的结果集大小超过了所有合并结果集大小总和的 50%，则 MySQL 将改用临时表存储结果，索引可能不再有效。

 12.分组语句中包含多个列，而分组依据的列没有出现在WHERE子句中时，MySQL优化器会默认对所有的行进行排序和分组，这可能会使得索引无法生效。

CREATE TABLE t(a INT,b INT);
INSERT INTO t VALUES(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
 
SELECT a,SUM(b) FROM t GROUP BY a HAVING SUM(b)>1;  -- 索引能被使用
SELECT a,SUM(b) FROM t GROUP BY a HAVING b>1;       -- 索引不能被使用

 13.当在一个多表连接查询中，若使用了带有子查询或者派生表的FROM子句时，MySQL会在内部生成一张临时表来完成这个查询，此时外部的索引就可能无法被使用。

CREATE TABLE parent (id INT PRIMARY KEY, value INT);
CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id));
 
INSERT INTO parent VALUES(1,100),(2,200);
INSERT INTO child VALUES(1,1),(2,1),(3,2),(4,2);
 
SELECT p.id 
FROM parent AS p 
JOIN (SELECT parent_id, AVG(value) as avg_value 
      FROM child 
      GROUP BY parent_id) AS c ON p.id = c.parent_id 
WHERE p.value > c.avg_value; -- 索引无法被使用