MySQL索引失效分析

2021-05-10 15:31:16 浏览数 (1)

为了演示索引失效的问题，首先来建一张表：

代码语言：javascript复制
create table staffs(
   id int primary key auto_increment,
   name varchar(100) not null default '' comment '姓名',
   age int not null default 0 comment '年龄',
   pos varchar(100) not null default '' comment '职位',
   add_time timestamp not null default current_timestamp comment '入职时间'
) charset utf8 comment '员工表';

insert into staffs(name, age, pos, add_time) values('张三', 22, 'manager', now());
insert into staffs(name, age, pos, add_time) values('july', 23, 'dev', now());
insert into staffs(name, age, pos, add_time) values('tom', 23, 'dev', now());
insert into staffs(name, age, pos, add_time) values('2000', 23, 'dev', now());

alter table staffs add index idx_staffs_nameAgePos(name, age, pos);

索引问题大概可以分为以下十种情况：

  • 最好的情况就是全值匹配
  • 最佳左前缀法则
  • 不在索引列上做任何操作(计算、函数、类型转换)，这些操作会导致索引失效
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引(查询列和索引列一致)，避免select *
  • MySQL中使用不等于(!= 或者 <>)的时候会导致索引失效
  • is null，is not null也无法使用索引
  • like以通配符开头('�c')会导致索引失效
  • 字符串不加单引号索引会失效
  • 少用or，用它来连接时索引会失效

下面就详细说说这十种情况。

1. 全值匹配：

在staffs表建表时我们建立了一个联合索引，如下：

联合索引

可以看到，一楼是name，二楼是age，三楼是pos。

依次查看下面语句的执行计划：**

代码语言：javascript复制
explain select  * from staffs where name = 'july';
explain select  * from staffs where name = 'july' and age = 23;
explain select  * from staffs where name = 'july' and age = 23 and pos = 'dev';

执行结果如下：

结果

第三种情况，就是全值匹配。即我们建立的索引一楼是name，二楼是age，三楼是pos，查询的条件也是先name再age最后pos，从上面图中第三条语句的执行计划可以看出，使用三个const，效率是很高的。

2. 最佳左前缀法则：

再执行下面的语句，看结果：

代码语言：javascript复制
explain select  * from staffs where age = 23 and pos = 'dev';
explain select  * from staffs where pos = 'dev';
explain select  * from staffs where name = 'july';
explain select  * from staffs where name = 'july' and pos = 'dev';

结果

可以发现，第一第二条语句的索引是失效了，而第三和第四条是用到了索引的。第一第二条没有用到name，即把一楼楼梯拆了，所以二楼三楼也就用不到了；第三条用到了一楼，所以没问题；第四条用到了一楼和三楼，但是中间的二楼没了，不能直接跳到三楼，所以也只能用到一楼，会部分失效。这就是最佳左前缀法则，即一楼一定不能少，带头大哥不能死，否则就会导致索引全部失效，中间兄弟不能断，否则会导致索引部分失效。

那么如果是这样的语句能不能用到索引呢？

代码语言：javascript复制
explain select  * from staffs where pos= 'dev' and age = 23 and name= 'july';

看结果：

执行计划

可以看到，三个索引都用到了。我们建立的索引是name，age，pos，用的时候反过来了，但是这个并不影响，带头大哥没死，中间兄弟没断，经过MySQL的优化器，就会自动进行调整，以达到最优。

如果是这样呢？

代码语言：javascript复制
explain select  * from staffs where name = 'july' and pos > 'dev' and age = 23;

执行计划

可以看到，key len还是608，说明还是三个都用到了。为什么？不是说范围之后全失效吗？别忘了，优化器会把age条件放到前面去，pos条件放到最后，所以三个都可以用上。

3. 不在索引列做任何操作：

执行下面的语句：

代码语言：javascript复制
explain select * from staffs where name = 'july';
explain select * from staffs where left(name,4) = 'july';

第一条语句是用name查询，第二条语句是在name列上包了一个函数，即查询name列左边四位等于july的记录。查看执行计划如下：

执行计划

可以看到，第一句是用到了索引的，但是第二句没有，因为第二句中索引列使用了函数。所以索引列上少计算。

4. 存储引擎不能使用索引中范围条件右边的列：

这个是啥意思？请看案例，执行下面两条sql：

代码语言：javascript复制
explain select  * from staffs where name = 'july' and age = 23 and pos = 'dev';
explain select  * from staffs where name = 'july' and age > 23 and pos = 'dev';

执行计划

第一条语句毫无疑问，全值匹配，最佳情况。第二条，带头大哥没死，中间兄弟没断，索引列上没计算，但是age不是常量，给的是一个范围，结果执行计划看到的是range。这种情况，name索引用到了，age也用到了，但不是精确检索，而是一个范围，最后的pos就没有用到，所以结论就是范围之后全失效。

5. 尽量使用覆盖索引(查询列和索引列一致)，避免select *：

查看以下两句的执行计划：**

代码语言：javascript复制
explain select  * from staffs where name = 'july' and age = 23 and pos = 'dev';
explain select  name, age, pos from staffs where name = 'july' and age = 23 and pos = 'dev';

执行计划

可以看到，如果查询字段和索引列完全一致，或者在索引列的范围内，比如select name, age，那么extra中是有using index的，这个效率是高于select *的。

6. MySQL中使用不等于(!= 或者 <>)的时候会导致索引失效：

查看下面语句的执行计划：

代码语言：javascript复制
explain select  * from staffs where name != 'july' and age = 23 and pos = 'dev';
explain select  * from staffs where name <> 'july' and age = 23 and pos = 'dev';

执行计划

可以看到，使用了!=或者<>确实导致索引失效了。

7. is null，is not null也无法使用索引：

查看下面语句的执行计划：

代码语言：javascript复制
explain select  * from staffs where name is null and age = 23 and pos = 'dev';
explain select  * from staffs where name is not null and age = 23 and pos = 'dev';

执行计划

is null的情况是最糟糕的，所以我们数据列如果经常用来当查询条件的话，最好设置默认值，而不能让它为null。

8. like以通配符开头('�c')会导致索引失效：

查看如下语句的执行计划：

代码语言：javascript复制
explain select  * from staffs where name like '%july';
explain select  * from staffs where name like 'july%';
explain select  * from staffs where name like '%july%';

结果如下：

执行计划

根据结果可以发现，只要左边出现了百分号，那么索引就失效了。所以百分like加右边。但是有些情况必须得百分号写左边，那么怎么解决索引失效的问题呢？一般我们会采用覆盖索引来解决。比如上面这种情况，不要select *，像下面这样就行了：

代码语言：javascript复制
explain select  name from staffs where name like '%july%';
explain select  age from staffs where name like '%july%';
explain select  age,pos from staffs where name like '%july%';
explain select  name,age,pos from staffs where name like '%july%';

执行计划

可以看到，全部都是用到了索引的。

like也是表示范围，但是如果是百分号写右边，这种范围和大于小于是不一样的，百分号写右边的like，后面的字段索引也是不会失效的。

9. 字符串不加单引号索引会失效：

查看如下语句执行计划：

代码语言：javascript复制
explain select  * from staffs where name = '2000';
explain select  * from staffs where name = 2000;

执行计划

可以发现，没加单引号，就会导致索引失效的。varchar类型的，没加单引号，存在类型转换，从而索引失效。

10. 少用or，用它来连接时索引会失效：

查看下面语句的执行计划：

代码语言：javascript复制
explain select  * from staffs where name = '2000' or name = 'july';

执行计划

可以看到，用了or以后，索引失效了。

总结：

全值匹配我最爱， 最左前缀要遵守； 带头大哥不能死， 中间兄弟不能断； 索引列上少计算， 范围之后全失效； 模糊百分写最右， 覆盖索引不写星； 不等空值还有或， 索引失效要少用； 字符引号不可丢， 牢记以上就无忧。

mysql null select 索引

0 人点赞