MySQL索引失效

MySQL索引失效的情况

1）创建了组合索引，但查询条件未遵守最左匹配原则
2）在索引列上进行计算、函数、类型转换等操作
3）以%开头的LIKE查询比如LIKE '%abc';
4）查询条件中使用OR，且OR的前后条件中有一个列没有索引，涉及的索引都不会被使用到
5）IN的取值范围较大时会导致索引失效，走全表扫描(NOT IN和IN的失效场景相同)
6）发生隐式转换

违反最左前缀原则

假设有联合索引

1
create index idx_abc on user(a, b, c);

有效地情况：

1
2
3
a
a,b
a,b,c

无效的情况：

1
2
3
b
c
b,c

对索引列做了运算或函数

如：

1
2
select * from user where age + 1 = 20;
select * from user where year(birthday) = 1990;

索引中存的是原始值，一旦对列进行运算，MySQL必须对每一行算一遍再比较，就相当于把有序结构打乱了，无法使用索引。

以%开头的LIKE查询

如：

1
select * from user where name like '%abc';

B+Tree只能从左往右匹配
%abc没有前缀，所以无法使用索引
abc%有前缀，可以使用索引

OR条件中有一个列没有索引

如：

1
select * from user where name = '张三' or age = 20;

如果name列有索引，age列没有索引，那么这个查询就无法使用索引
解决方式：

  • 给age列也创建索引
  • 或改成UNION ALL的形式

IN的取值范围较大

如：

1
select * from user where id in (1, 2, 3, ..., 1000);

如果IN的取值范围较大，MySQL可能会认为使用索引不如全表扫描效率高，从而导致索引失效。

隐式转换

隐式转换规则：

  1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
  2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
  3. 两个参数都是整数，按照整数来比较，不做类型转换
  4. 十六进制的值和非数字做比较时，会被当做二进制串
  5. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
  6. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
  7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

如：

1
SELECT * FROM `test1` WHERE num2 = 10000;

其中num2是字符串类型
左边是字符串类型'10000'，转浮点数为10000是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，'10000'转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的。