浅谈mysql哪些情况会导致索引失效

下面有一些培训教学机构的口诀和我个人的一些总结：
为了讲解以下索引内容，我们先建立一个临时的表 test02

CREATE TABLE `sys_user` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `name` varchar(64) DEFAULT NULL COMMENT '名字',
  `age` int(64) DEFAULT NULL COMMENT '年龄',
  `pos` varchar(64) DEFAULT NULL COMMENT '职位',
  PRIMARY KEY (`id`),
  KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

这个表有四个字段 主键 、名字、年龄、职位

下面我们来讲解第一个口诀：
1.全值匹配我最爱
2.最佳左前缀法则（重要）
全值匹配意思就是联立的复合索引的顺序和个数要和检索的条件顺序和个数相同。
最佳左前缀法则是指，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
下面我们给这个表建立一个复合索引

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

以下是我们的检索语句：

SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

我们通过在检索语句前面加关键字 EXLAIN，可以知道是否使用的索引

（1）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
（2）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ;
（3）EXPLAIN SELECT * FROM sys_user WHERE name='小明'  AND pos ='java';

通过展示的结果我们可以知道，第一个复合索引的三个字段我们都用了，第二个复合索引我们只用到两个字段，第三个复合索引我们只用到一个字段。三个语句我们都用到索引，显然第一种是最优的。

我们再看看哪种情况会失效：

（4）EXPLAIN SELECT * FROM sys_user WHERE age = 22;
（5）EXPLAIN SELECT * FROM sys_user WHERE pos ='java';
（6）EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos ='java';

以上三种情况都变成了全表扫描，原因是违反了最左左前缀原则，因为复合索引最左边的是name，当检索条件name没在前面索引将失效，第一种情况满足了全值匹配，第二种满足了两个字段name和age，第三种因为只满足了name，所以索引只用到name。

3.不在索引列上做任何操作（计算、函数（自动或手动）类型转换），会使索引失效转为全表扫描

（7）EXPLAIN SELECT * FROM sys_user WHERE  LEFT(name,1)='小明';

第七种情况失效是因为索引列做了计算或者函数的操作，导致了全表扫描。

4.存储引擎不能使用索引中范围条件右边的列
可能大家关看上面的文字不知道是什么意思，下面我们执行一下查询语句就清楚了

（8）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age < 22 AND pos ='java';

从上图我们可以知道type变成了范围级别，也就是说age<22之后的pos字段的索引失效了。

5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致），减少select * 的使用
这个就是字面意思，查询具体的字段比查询*效率更高，下面我们坐一下对比

（9）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';
（10）EXPLAIN SELECT name,age,pos FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';

6.mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
(!= 或者<>)通常会匹配到大量数据，当使用索引的花销大于全表扫描时，mysql则会放弃使用索引而选择全表扫描

（11）EXPLAIN SELECT * FROM sys_user WHERE name !='小明'

结果显示索引失效导致了全表扫描

7.is null，is not null 也无法使用索引
is null，is not null 通常会匹配到大量数据，当使用索引的花销大于全表扫描时，mysql则会放弃使用索引而选择全表扫描

（12）EXPLAIN SELECT * FROM sys_user WHERE name is not null

8.like以通配符开头（'%abc…'）mysql索引会失效变成全表扫描的操作，（%写右边则可以避免索引失效，如果业务实在需要'%abc…%'则可以用覆盖索引避免索引失效）

（13）EXPLAIN SELECT * FROM sys_user WHERE name like '%明%'
（14）EXPLAIN SELECT * FROM sys_user WHERE name like '明%'
（15）EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

从上面的结果，第一种索引失效，第二种只写右边的%则可以避免索引失效，第三种如果业务实在需要‘%abc…%'这种sql，则可以用覆盖索引解决索引失效的问题

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

（16）EXPLAIN SELECT * FROM sys_user WHERE name=222;

因为检索字符串是必须加单引号，上面用用了222是int类型，mysql在检索的时候会判断name是varchar的类型会将222转换为'222'进行检索，索引列发生了类型转换，故索引失效。

10.少用or,用它连接时会索引失效

（16）EXPLAIN SELECT * FROM sys_user WHERE name='小明' or age = 22;

到此这篇关于浅谈mysql哪些情况会导致索引失效的文章就介绍到这了,更多相关mysql 索引失效内容请搜索潘少俊衡以前的文章或继续浏览下面的相关文章希望大家以后多多支持潘少俊衡！