导致索引失效的情况汇总（持续更新）

目录

前言

一、select *

二、在索引列上计算或者索引列用函数

三、隐式转换

四、模糊查询，like左%

五、or关键字

六、in关键字

---

---

前言

有时建了索引但是实际的执行计划却没有走索引，到底是什么导致索引失效？读完并理解本篇文章你必将有所收获。

---

创建表order_id

create table order_test(
 id int PRIMARY KEY,
 name VARCHAR(10),
 age int,
 order_id int,
 key inx_test_od(order_id),
 key inx_test_naa(name,age)
)


insert into order_test values(1,'a',10,1);
insert into order_test values(2,'ea',9,6);
insert into order_test values(3,'d',8,4);
insert into order_test values(4,'c',11,7);
insert into order_test values(5,'e',19,8);
insert into order_test values(6,'f',2,1);
insert into order_test values(7,'g',8,12);
insert into order_test values(8,'h',19,43);
insert into order_test values(10,'i',2,32);
insert into order_test values(14,'j',8,23);
insert into order_test values(9,'k',19,5);
insert into order_test values(11,'l',2,7);
insert into order_test values(12,'m',8,0);

一、select *

很多公司有自己的开发规范，里面肯定会有一条禁止使用select *

并不是说使用select *一定会造成全表扫描，但是很大几率会造成回表，导致没办法索引覆盖。

优化：查询具体的列

SELECT id,username FROM user

理由：

1. 节省资源、减少网络开销。

2. 可能用到覆盖索引，减少回表，提高查询效率。

二、在索引列上计算或者索引列用函数

explain 
select * from order_test where order_id +2 =5;

可以看到在索引列上计算会导致索引失效。

下面证明下，避免写select *的重要性：

explain 
select id ,order_id from order_test where order_id +2 =5;

 可以看到同一个语句，虽然也在索引列上做了计算，但是查找某列，这样就产生索引覆盖，不用回表，只需要扫描二级索引。

函数运算不过多赘述，有一种解决方法就是连着函数一起建索引，但是这样效率不高。

三、隐式转换

varchar类型的字段，在查询的时候不加单引号的话，就会进行隐式转换，导致索引失效，转向全表扫描。

实验前准备：

insert into order_test values(13,'123',8,0);
insert into order_test values(16,'123a',8,0);
insert into order_test values(17,'a123a',8,0);

数字类型转字符串类型：

先看正常情况

explain 
select * from order_test where name ='123';

发生隐式转换的情况

explain 
select * from order_test where name = 123;

 发现索引失效了，没有走索引。

原因：字符串’123‘、’123a‘（只要开头是数字就不会报错）转成数字类型都=123，

        数字类型123转成字符串，mysql就不知道转成哪个字符串，所以只能全表扫描找到符合条件的数据。

select * from order_test where name = 123;

 

字符串类型转数字类型：

EXPLAIN 
select * from order_test where order_id = 5;

explain 
select * from order_test where order_id ='5a';

explain 
select * from order_test where order_id ='5';

上三个sql都走索引。

四、模糊查询，like左%

explain 
select * from order_test where name like '123%';

%在右：匹配的是首字母，所以可以在B+树上进行有序查找，查找首字母符合要去的数据。

explain 
select * from order_test where name like '%123';

EXPLAIN
select * from order_test where name like '%123%';

%在左：匹配的是字符串尾部的数据，尾部字母是没有顺序的，所以无法按照索引顺序查找，索引失效。

 两个%和%在左相同。

EXPLAIN
select NAME from order_test where name like '%123%';

 避免select *，select name后可以全索引树扫描。

五、or关键字

or关键字的情况可以看这篇文章，详细写了or作为连接条件的各种情况。

where子句中使用 or 来连接条件的各种情况-CSDN博客

六、in关键字

explain 
select * from order_test where name in ('d','f','h');

explain 
select * from order_test where name in ('d','f');

explain 
select * from order_test where name in ('d');

可以看到in查找的数据不要太多，太多会导致索引失效，或者说优化器会选择全表扫描。主键索引也是相同的道理。

explain 
select * from order_test where id in (2,3,4,5);

explain 
select * from order_test where id in (2,3,4,5,6,7,8,9,10);