MySQL 索引失效情况

本文整理了 MySQL InnoDB 存储引擎中，常见导致索引失效的原因。

MySQL 是否使用索引通常取决于优化器估算的结果，如果计算结果认为全表扫描比使用索引还快，那通常就使用全表扫描。

以下两大类查询语句会导致查询就算走索引也需要对结果进行处理，最终优化器不会使用索引进行查询：

1. 违反最左匹配原则
2. 索引列上面做操作

违反最左匹配原则

《最左匹配原则》

如：建立索引为 (a,b) 的联合索引，那么只查 where b = 2 则不生效。

LIKE 语句的前导模糊查询不能使用索引

LIKE 语句也遵守最左匹配原则。

select * from doc where title like '%XX'；   --不能使用索引
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决。

在索引列上面做操作

例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：

select * from doc where YEAR(create_time) <= '2016';

强制类型转换会全表扫描

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引：

select * from user where phone=13800001234

对于优化器，相当于对索引字段做函数操作：

select * from user where  CAST(phone AS signed int)=13800001234

可以优化为：

select * from user where phone='13800001234';

隐式字符编码转换

对于查询中字段字符集编码不同，也会等同使用 CONVERT 函数进行编码转换，导致无法使用索引。

对于以下语句，tradelog 称为驱动表，trade_detail 称为被驱动表，tradeid 称为关联字段。

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

假定：

• 驱动表 tradelog 的 tradeid 字段为 utf8mb4
• 被驱动表 trade_detail 的 tradeid 字段为 utf8

那么，查询时就需要将 utf8 转换为 utf8mb4，两表关联查询的字段字符集不同，会导致索引失效。查询相当于：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

违反字段加函数的规则，不能走索引。

如果反过来，驱动表 tradelog 的 tradeid 字段为 utf8，被驱动表 trade_detail 的 tradeid 字段为 utf8mb4，相当于函数加在非字段上，可以使用索引。

负向条件查询常不能使用索引

负向条件有：!=、<>、not in、not exists、not like 等。

例如下面SQL语句：

select * from doc where status != 1 and status != 2;

可以优化为 in 查询：

select * from doc where status in (0,3,4);

由于回表，以及数据量的问题，MySQL 认为走全表查询的代价反而更低，因此最终采用了全表查询。

IN 使索引失效的情况

IN 的条件过多，会导致索引失效，走索引扫描。

OR 使索引失效的情况

如果 or 前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引都不会被用到。

IS NULL、IS NOT NULL 使用索引的条件

NULL 值占比较小的情况下，是可以使用索引的。

本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值。

引用

1. SQL IN 一定走索引吗？
2. MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！