[大数据]Mysql单表查询时候的索引情况：索引是否失效

介绍

使用命令 explain 对sql进行分析

例如：

explain select * from test;

结果如下：

关键信息是这个type的值

type常用值介绍：

• ALL：表示进行了全表查询
• index：index表示走了索引
• range：只检索给定范围的行，使用一个索引来选择行
• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
• const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

更多信息请参考：https://www.cnblogs.com/xuanzhi201111/p/4175635.html

1. 单表实验

0. 环境介绍

• window10操作系统
• mysql8.0 64位
• 数据库引擎：InnerDB
• 使用test表做演示操作，表中有三列
  • id：主键，int类型，自增
  • name：varchar类型，有索引，允许为null
  • age：int类型，有索引，允许为null

1. like关键字

1. 不带通配符

explain select * from test t where t.name like '张三';

可以看到，使用了range：只检索给定范围的行，使用一个索引来选择行。相当于走了索引

2. 使用前置 _

explain select * from test t where t.name like '_张三';

可以看到，使用了全表扫描，索引失效

3. 使用后置_

explain select * from test t where t.name like '张三_';

可以看到，type是range，索引生效

4. 使用前置%

explain select * from test t where t.name like '%张三';

使用前置%进行模糊查询，索引失效，type是all，进行了全表扫描

5. 使用后置%

explain select * from test t where t.name like '张三%';

type是range，索引有效，没有进行全表扫描

6. 补充内容：instr

网上说instr可以避免like的索引失效，是不是这样呢？

-- 网上说使用instr(列名, '查找字符串') > 0效率更好
select * from test t where instr(t.name, '李四') > 0;
-- 可以代替
select * from test t where t.name like '%李四%';

看一下这个语句的分析吧：

explain select * from test t where instr(t.name, '李四') > 0;

结果是：

也是全表扫描，索引依然没有用，效率能比like高到哪里去呢？

总结一下就是：

使用like进行模糊查询，在mysql8.0，InnerDB引擎下

1. 不带通配符，或后置通配符索引有效
2. 前置通配符，导致索引失效

2. = 进行选择

1. 无需类型转换

1. 主键

   explain select * from test t where t.id = 1;
   

? 可以看到，主键type是const，其实就是只查询了一行，直接找到

1. varchar类型

   explain select * from test t where t.name = '张三';
   

ref类型，直接找到

1. int类型

   explain select * from test t where t.age = 1;
   

ref类型，直接找到

2. 需要类型转换

varchar列，给int值

explain select * from test t where t.name = 1;

全表扫描

int列，给char值

explain select * from test t where t.age = '张三';

直接找到，不需要，无需索引

3. 运算符

1. 普通算术运算符

>, <, <=, >=, between and, and, 少量or

其他运算符也大差不差，这里只是用 > 运算符做演示

explain select * from test t where t.age > 1;

普通运算符，range类型，索引生效

2. <> 运算符

1. 在普通索引列，没有null值时

explain select * from test t where t.age <> 1;

all类型，索引失效，所以网上说的 <> 运算符导致索引失效时真的。

网上说，可以使用，下方sql语句替换 <> 运算符，使索引生效，是真的吗？

explain select * from test t where t.age < 1 or t.age > 1;

请看结果：

没有用，依旧是全表扫描

2. 普通索引列，有null值时

explain select * from test t where t.age <> 1 ;

可以看到，索引没有失效，不会和null那部分数据进行比较，但是有值的都是会进行比较的。

name列一样。

3. 在主键上

explain select * from test t where t.id <> 10;

可以看到，虽然是range类型，但是仍然扫描了所有的行，但是不能算是索引失效吧，也能算是索引失效。

4. 总结：

可以看到，<> 会对所有非null的数据进行扫描，但是会过滤掉null值不扫描；没有null值的时候也算是全表扫描。有null值的时候，也不算是全表扫描。

如果把<> 操作说成会使索引失效也不能算错吧。

3. is null

explain select * from test t where t.name is null;

可以看到，类型为 ref，索引生效，没有全表扫描

int类型的age与varcahr类型的name结果一致，不在展示age的结果

4. is not null

explain select * from test t where t.age is not null;

可以看到，type为all，进行了全表扫描，is not null 导致索引失效。

这里的rows不一样是因为，在测试前我在表里增加了一些null数据

varchar类型的name与int类型的age结果一致，不在展示name的结果

5. in 和 or

1. in

explain select * from test t where t.age in (1,2,3,4,5,6);

可以看到，当in值比较少的时候，in 可以走索引，索引不失效。

varchar类型的name与 int类型的age结果一致，不再进行展示

当索引值比较多的时候：

explain select * from test t where t.age in (1,2,3,4,5,6,7);

索引失效，进行全表扫描

2. or操作，与in相似

6个or条件的时候，索引不失效，具体个数应该跟数据量有关系，因为这时候数据总数是14条

explain select * from test t 
where t.age = 1 or t.age = 2  or t.age = 3 
or t.age = 4 or t.age = 5 or t.age = 6
;

超过6个的时候，索引失效

explain select * from test t 
where t.age = 1 or t.age = 2  or t.age = 3 
or t.age = 4 or t.age = 5 or t.age = 6
or t.age = 7
;

网上有说 in 实际上就是一系列的 or 条件的说法，还是有一定道理的，至少从这个结果上看是这样的。

4. where中的其他操作

1. 列字段进行运算

例如：

explain select * from test t where t.id + 1 = 2;

全表扫描

2. 在where条件中使用函数

例如：

explain select * from test t where ifnull(t.age, 0) = 0;

结果；

进行了全表扫描，索引失效

4. 总结

1. like进行模糊查询的时候，前置通配符会导致索引失效，后置通配符则不会
2. 使用instr函数简单查找字符串，索引依然失效
3. = 可以直接找到结果，但是当等号列需要进行类型转换的时候，可能发生全表扫描，[例如](#2. 需要类型转换)
4. 普通算数运算符不会导致索引失效
5. <> 运算符，在没有null值的时候，会进行全表扫描，有null的时候不会扫描null值的数据
6. 使用or替换<> 依然会进行全表扫描，若or条件为true，必全表扫描
7. is null 不会导致索引失效，会扫描全部非null的数据，但是 is not null 会导致索引失效
8. in 和 or 在比较少的时候不会导致索引失效（个数跟具体的数据量有关系）
9. in 的值超过一定数量，或连续or超过一定数量，会进行全表扫描，索引失效
10. 在where条件中对列字段进行运算，会索引失效，进行全表扫描
11. 在where条件中使用函数时，可能导致索引失效

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖