MySql的索引失效的场景

1、全表扫描操作：

SELECT COUNT(*) FROM table;

统计表中所有行的数量时，即使表有索引，也可能触发全表扫描，因为索引不能直接提供行计数。
TRUNCATE TABLE;、DROP TABLE; 或 ALTER TABLE…; 等DDL操作通常不涉及索引。

2、索引列使用计算或函数：

SELECT * FROM table WHERE indexed_column / 1 = value;

对索引列进行数学运算（如除法、乘法等）或应用函数（如TO_CHAR(), UPPER(), LOWER()等），可能导致索引失效，因为索引存储的是原始数据值，而非经过计算或函数处理后的值。

3、LIKE查询条件不匹配索引：

SELECT * FROM table WHERE indexed_column LIKE '***%';

以通配符开头的查询（如’%abc’）通常无法利用索引。但以固定字符串开头的模糊查询（如’abc%'）有时可以利用索引前缀。

4、联合索引未按最左前缀原则使用：

CREATE INDEX idx ON table(a, b, c);

对于一个多列索引，查询仅使用索引的一部分（如只查询b和c列），或者查询顺序与索引列顺序不一致（如先查询c再查询a），可能导致索引失效。

5、索引列参与排序但无谓值筛选：

SELECT * FROM table ORDER BY indexed_column;

当仅对索引列进行排序而无其他筛选条件时，可能无法利用索引进行排序优化。

6、索引列被隐式转换：

SELECT * FROM table WHERE indexed_column = 'value' AND indexed_column IS NOT NULL;

如果查询中对索引列进行了隐式类型转换（如将数值型索引列与字符串比较），可能导致索引失效。

7、索引列使用OR连接多个条件：

SELECT * FROM table WHERE indexed_column = value1 OR indexed_column = value2;

除非索引支持IN列表查询，否则使用OR连接多个索引列条件可能导致索引失效。

8、索引列在IN子句中包含大量值：

SELECT * FROM table WHERE indexed_column IN (value1, value2, ..., valueN);

当IN子句包含过多值时，查询优化器可能判断使用索引不如全表扫描划算，从而放弃使用索引。

9、索引列用于NOT操作：

SELECT * FROM table WHERE NOT indexed_column = value;

对索引列应用逻辑非操作（如NOT、!=、<>）可能导致索引失效，除非索引支持倒序扫描。

10、索引列用于JOIN条件但数据分布不均：

SELECT * FROM table1 JOIN table2 ON indexed_column = foreign_key;

如果关联表的数据分布极不均匀，即使使用了索引，也可能因数据倾斜导致索引效果不佳。

11、索引列数据过于分散：

SELECT * FROM table WHERE indexed_column BETWEEN low_value AND high_value;

如果查询条件覆盖了索引列的大部分值（如查询范围过大），可能导致索引效果减弱或失效。

12、查询结果集过大：

SELECT * FROM table WHERE indexed_column = value;

当查询结果集预计远大于工作集（如全表的一定比例）时，查询优化器可能认为全表扫描更高效，从而不使用索引。

13、临时表或派生表操作：

CREATE TEMPORARY TABLE AS SELECT ...;

创建临时表或派生表的操作通常不保留原表的索引，后续查询可能无法利用索引。

14、索引未被维护或统计信息过期：

ANALYZE TABLE table;

如果表数据发生显著变化后未重新收集统计信息，或索引长期未进行维护，可能导致查询优化器对索引效用的评估不准确，从而不使用索引。

15、不等于比较

select * from t_user where id_no <> '1002';

查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效

16、is not null

select * from t_user where id_no is not null;

查询条件使用is null时正常走索引，使用is not null时，不走索引。