索引失效的几种情况

1.概述

索引失效的几种情况：

1.违反最左前缀法则的。

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

2.使用范围查询的情况，右边的列失效

3.不要在索引列上进行运算操作， 索引将失效。

4.字符串不加单引号,造成索引失效。

2.实例

2.1 准备环境

CREATE TABLE `im_user_nok` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `nickname` varchar(200) NOT NULL DEFAULT '' COMMENT '昵称',

  `phone` varchar(15) CHARACTER SET utf8 DEFAULT '' COMMENT '手机',

  `head` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '头像',

  `pwd` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT '密码',

  `name` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '真实姓名',

  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1启用，0禁止',

  PRIMARY KEY (`id`),

  KEY `key_name_phone_nickname` (`nickname`,`phone`,`name`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4

2.2 避免索引失效

组合索引(nickname,phone,name)

1) 全值匹配

对索引中所有列都指定具体值。

-- 全值匹配

explain select * from im_user_nok where nickname = '美美' and phone = '18569548877' and name = '张三';

ken_len = 4 * N + 2;-- nickname varchar(200)==802 -- phone varchar(15)  ==48

-- name varchar(10) ==32

2) 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

explain select * from im_user_nok where nickname = '美美' ;

违反最左前缀法则 ， 索引失效：

explain select * from im_user_nok where name = '张三';

explain select * from im_user_nok where phone = '18569548877' and name = '张三';

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

explain select * from im_user_nok where nickname = '美美' and name = '张三';

3) 范围查询右边的列

-- 使用范围查询的情况，右边的列失效 

explain select * from im_user_nok where nickname = '美美' and phone > '18569548877' and name = '张三';

根据前面的两个字段nickname ， phone 查询是走索引的， 但是最后一个条件name 没有用到索引。

4) 禁止列运算

-- 不要在索引列上进行运算操作， 索引将失效。

explain select * from im_user_nok where substring(nickname,3,2) = '美美';

5) 字符串不加单引号

造成索引失效。

-- 字符串不加单引号,造成索引失效。

eexplain select * from im_user_nok where nickname = 0;

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

补充：

key_len的计算公式：

varchar()：表字符集长度*列长度+2(变长)+1(null) 若是列被定义为"not null",则不需要加1

char()：表字符集长度*列长度+1(null)  若是列被定义为"not null",则不需要加1

常用的字符集：

utf8mb4：4个字节

utf8：3个字节

gbk：2个字节

latin：1个字节

示例：

表的字符集选用utf8mb4,计算

varchar(10),not null  --->  key_len=4*10+2=42

char(10),null         --->  key_len=4*10+1=41

最后

以上就是魔幻毛豆为你收集整理的索引失效的几种情况的全部内容，希望文章能够帮你解决索引失效的几种情况所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。