mysql索引失效的常见原因和如何⽤好索引本篇主要介绍的是索引失效的常见原因和如何⽤好索引，跟上⼀篇正好承上启下，给有需要的朋友⼀个参考。
本⽂将从以下⼏个⽅便进⾏讲解：
1.索引失效常见原因：
2.索引失效常见误区：
3.索引设计的⼏个建议：
准备⼯作
查看当前 mysql 的版本：
select VERSION();
查出当前版本为：8.0.21
创建⼀张表 test1
CREATE TABLE `test1` (
`id` bigint NOT NULL,
`code` varchar(30) NOT NULL,
`age` int NOT NULL,
`name` varchar(30) NOT NULL,
`height` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`) USING BTREE,
KEY `idx_height` (`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插⼊两条数据：
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (1, '001', '张飞', 18,'7');
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (2, '002', '关⽻', 19,'8');
查询⼀下：
select * from test1;
结果：
此外建⽴了两个索引：idx_code_age_name( code , age , name )联合索引和 idx_height(height )普通索引。
⼀. 索引失效常见原因
1.不满⾜最左前缀原则
第1种情况：
where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、name 。
where code='001' and age=18 and name='张飞' ;
结果：
从图中标红的地⽅可以看出已经⾛了联合索引idx_code_name_age，并且索引的长度是 188， 188 = 30 * 3 + 2 + 30 * 3 + 2 + 4 ，索引是使⽤充分的，索引使⽤效率最佳。
有些朋友可能会问：索引长度为什么是这样计算的？
答：请参考《 explain | 索引优化的这把绝世好剑，你真的会⽤吗？》，⾥⾯给出了⾮常详细的讲解。
第2种情况：
where 条件后的字段包含了联合索引的所有索引字段，顺序是不按照： code 、 age 、name。
执⾏sql如下：
explain select * from test1
size19where code='001' and name='张飞' and age=18;
结果：
从上图中看出执⾏结果跟第⼀种情况⼀样。
注意：这种情况⽐较特殊，在查询之前mysql会⾃动优化顺序。
第3种情况：
where 条件后的字段包含了联合索引中的： code 字段
执⾏sql如下：
explain select * from test1
where code='001';
结果：
从上图看出也⾛了索引，但是索引长度有所变化，现在变成了 92 ， 92 = 30*3 + 2 ，只⽤到了⼀个索引字段code，索引使⽤不充分。
第4种情况：
where 条件后的字段包含了联合索引中的： age 字段
执⾏sql如下：
html代码怎么写大佬教程explain select * from test1
where age=18;
结果：
从上图中看变成了全表扫描，所有的索引都失效了。
第5种情况：
sourceforge下载太慢where 条件后的字段包含了联合索引中的： name 字段
执⾏sql如下：
explain select * from test1
where name='张飞';
结果：从上图中看变成了全表扫描，所有的索引都失效了。
第6种情况：
where 条件后的字段包含了联合索引中的： code 和 age 字段
where code='001' and age=18;
结果：
从上图中看出还是⾛了索引，但是索引长度变成了： 96 ， 96 = 30*3 + 2 + 4 ，只⽤到了两个索引字段code和age，索引使⽤也不充分。第7种情况：python2022激活码免费
where 条件后的字段包含了联合索引中的： code 和 name 字段
执⾏sql如下：
explain select * from test1
where code='001' and name='张飞';
结果：
从上图中看出⾛的索引长度跟第1种情况⼀样，长度也是 92 。也就是说只⽤到了⼀个索引字段 code ，⽽ age 字段的索引失效了。
第8种情况：
where 条件后的字段包含了联合索引中的： age 和 name 字段
执⾏sql如下：
explain select * from test1
where age=18 and name='张飞';
结果：
从上图中看出变成了全表扫描，所有的索引都失效了。
⼩结：
1. code
2. code
3. 如果中间出现断层，如： code、name ，只会⾛第⼀个索引code，从断层后的索引都会失效。
4. name name
2.范围索引列没有放在最后
where 条件后的字段 age ⽤了⼤于等于，具体sql如下：
EXPLAIN select * from test1
where code='001' and age>18 and name='张飞' ;
结果：
从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只⽤到了两个索引字段 code 和age ，⽽ name 字段的索引失效了。
如果范围查询的语句放到最后：
EXPLAIN select * from test1
where code='001' and name='张飞' and age>18 ;
结果：
什么⿁？怎么索引长度还是： 96 ？
这是⼀个⾮常经典的错误
范围查询放最后是指创建联合索引的字段顺序，现在的顺序是：
再执⾏上⾯的sql，结果：
从上图中看出索引长度变成： 188 ，索引使⽤充分了。
回过头再执⾏刚开始的那条sql：
EXPLAIN select * from test1
where code='001' and age>18 and name='张飞';
结果：
什么？
索引长度也是： 188 。
注意：范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常⾛到索引。
3.使⽤了select *
其实在《阿⾥巴巴开发⼿册》中也明确说了，禁⽌使⽤select * ，这是为什么呢？
EXPLAIN select * from test1
结果：
从上图中看出⾛了全表扫描。
那么如果查询的是索引列：
EXPLAIN select code,age,name from test1
结果：
从图中可以看出这种情况⾛了全索引扫描，⽐全表扫描效率更⾼。update in
其实这⾥⽤到了：覆盖索引。
如果 select 的列都是索引列，则被称为覆盖索引。
如果 select 的列不只包含索引列，则需要回表，即回到表中再查询出其他列，效率相当更低⼀些。 select * ⼤概率需要查询⾮索引列，需要回表，因此要少⽤。
当然，本⽂中很多⽰例都使⽤了 select * ，主要是我表中只有两条数据，为了⽅便演⽰，正常业务代码中是要杜绝这种写法的。
4.索引列上有计算
执⾏sql如下：
explain select * from test1
where height+1 =7;
结果：从上图中可以看出变成全表扫描了，由此可见在索引列上有计算，索引会失效。
5.索引列上使⽤了函数
如果在索引列加某个函数，具体sql如下：
explain select * from test1
where SUBSTR(height,1,1)=8;
结果：从上图中可以看出变成全表扫描了，由此可见在索引列上加了函数，索引也会失效。
6.字符类型没加引号
废话不多说直接上sql：
结果：
从图中看出⾛的全表扫描，索引失效了。
为什么索引会失效呢？
这⾥有些朋友可能会有点懵。
答： name 字段是字符类型，⽽等于号右边的是数字类型，类型不匹配导致索引丢失。
所以在使⽤字符类型字段做判断时，⼀定要加上单引号。
类型不匹配导致索引丢失问题，是我们平时⼯作中⾮常容易忽视的问题，⼀定要引起⾜够的重视7.⽤is null和is not null没注意字段是否允许为空前⾯创建的test1表中height字段是⾮空的。
查询sql如下：
explain select * from test1
where height is null;
explain select * from test1
where height is not null;
结果都是：从上图中看出都是全表扫描，索引都失效了。
如果height字段改成允许为空的呢？
上⾯第⼀条sql执⾏结果：
从上图中看出⾛了 ref 类型的索引。
上⾯第⼆条sql执⾏结果：
从上图中看出⾛了 range 类型的索引。
mysql面试题常问
⼩结
1. 如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
2. 如果字段允许为空，则is null⾛ ref 类型的索引，⽽is not null⾛ range 类型的索引。
8.like查询左边有%
like查询主要有三种情况：
like '%a'
like 'a%'
like '%a%'
先看看第⼀种情况：
explain select * from test1
where code like '%001';
结果：
从上图看出⾛的全表扫描，索引失效了。
再看看第⼆种情况：
explain select * from test1
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论