总结mysql索引失效的N种情况

mysql中，索引是存储引擎实现的，不同的存储引擎索引的工作方式不一样，由于mysql默认的存储引擎为InnoDB，所以下面都是基于InnoDB的例子。

1、查询条件中有or

创建一个user 表和一个job表，建表语句如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行如下sql

explain SELECT name,age,address FROM user where name = '光头强' or age=9

上例sql中name字段上有索引index_name，下面来看下执行计划：

image.png

type为All，仍然是走的全表扫描。

2、like查询是以'%'开头

仍然用1中的user表执行如下sql

explain SELECT name,age,address FROM user where name like '%头强'

上例sql中name上有索引index_name，下面来看下执行计划：

image.png

type为All，仍然是走的全表扫描。但是如果想让以‘%’开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列：

explain SELECT name FROM user where name like '%头强'

image.png

如上，执行计划里的key用到了index_name。

3、对查询的列上有运算或者函数的

仍然用1中的user表执行如下sql

explain SELECT name,age,address FROM user where substr(name,-2)='头强'

上例sql中name上有索引index_name，下面来看下执行计划：

image.png

再比如：

explain SELECT name,age,address FROM user where age+1=12

上例中age有索引index_age，执行计划如下：

image.png

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

仍然用1中的user表执行如下sql

explain SELECT name,age,address FROM user where name = 10

上例sql中name上有索引index_name，下面来看下执行计划：

image.png

同样，没有用到索引走的全表扫描。
这里面的原因其实跟例3是一样的，首先mysql有个类型转换规则就是将“字符转成数字”，所以以上sql就等价于这样：

explain SELECT name,age,address FROM user where cast(name as signed)= 10

很明显，name字段上有函数。所以跟例3一样的原因。不会走索引。

5、左连接查询或者右连接查询查询关联的字段编码格式不一样

首先同样建两张表，一个user，一个job，建表语句如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注意user 表的name字段编码是utf8mb4，而job表的name字段编码为gbk。执行左外连接查询：

EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

执行计划如下：

image.png

b表仍然走的全表扫描。注意如果是用覆盖索引的话，那么b表就会走索引了。

6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引

这个举例还用user表和job表，只不过我们往表里面少插入一些数据，建表如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('光头强', 12, '狗熊岭', 1);
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('熊大', 9, '狗熊岭2', 2);

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`job`(`id`, `userId`, `job`, `name`) VALUES (1, 1, 'java', '光头强');
INSERT INTO `test`.`job`(`id`, `userId`, `job`, `name`) VALUES (2, 2, 'php', '熊大');

还是执行上面的查询：

EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

执行计划如下：

image.png

此时，由于要查询b.name，mysql需要回表，mysql认为走全表扫描会快一些，所以即使b表的name有索引，也不会走。

7、连接查询中，按照优化器顺序的第一张表不会走索引

这个可能听起来有点懵逼，举个栗子，仍然建两张表，建表语句如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('光头强', 12, '狗熊岭', 1);
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('熊大', 9, '狗熊岭2', 2);

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据的insert语句就不写了，要多插入一些，否则下面演示会失效，因为数据少mysql认为走全表扫面快一些，就怎么都用不到索引了。
执行查询：

EXPLAIN select a.name,a.age,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

此时a表user上的name是有索引的，b表name无索引，看下执行计划

image.png

a表，b表都是全表扫描，而建表语句不变，把左外连接改为右外连接

EXPLAIN select a.name,a.age,b.name,b.job
from
user a
right JOIN job b
ON a.name =b.name

执行计划如下：

image.png

可以看到，a表用到了索引，同样，将右连接改为内连接再看下

EXPLAIN select a.name,a.age,b.name,b.job
from
user a
inner JOIN job b
ON a.name =b.name

执行计划如下：

image.png

同样，a表也是用到了索引，从上面三个连接查询来看只有左外连接a表没有用到索引的，这就是因为由于是左外连接，所以优化器的执行顺序是a表、b表，也就是说首先全表扫描a表，再根据a表的name查询b表的值，所以a表无法用到索引。用段伪代码解释下：

//mysql代码
select a.name,a.age,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name
//相当于执行以下循环
List<Map<String,Object>> resultA=select a.name,a.age from user a
for(Map<String,Object> map: resultA){
       List<Map<String,Object>> resultB=select b.name,b.job from job b where b.name=map.get("name")
}

从这段伪代码可以看到a表没有where语句，所以根本无法用到name上的索引，而b表此时name上若无索引的话，导致两个表都是全表扫描，所以一般这种连接查询，A表关联B表，要在将优化器顺序的第二张表上关联的字段上加索引，而第一张表则无需加索引，无用的索引也会影响性能。而第三个例子中内连接就不一样了，如果是内连接优化器本身就会根据索引情况，连接表的大小等去选择执行顺序了，所以上例中的内连接执行顺序是b、a，这样仍然可以用到a的索引。

8、如果查询中没有用到联合索引的第一个字段，则不会走索引

建立job表如下：

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`,`job`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行查询

explain SELECT * from job where job='java'

执行计划如下：

image.png

走的是全表扫描。

最后编辑于：2018.12.24 17:28:29

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 215,384评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,845评论 3赞 391
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 161,148评论 0赞 351
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,640评论 1赞 290
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,731评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,712评论 1赞 294
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,703评论 3赞 415
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,473评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,915评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,227评论 2赞 331
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,384评论 1赞 345
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,063评论 5赞 340
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,706评论 3赞 324
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,302评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,531评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,321评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,248评论 2赞 352

总结mysql索引失效的N种情况

1、查询条件中有or

2、like查询是以'%'开头

3、对查询的列上有运算或者函数的

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5、左连接查询或者右连接查询查询关联的字段编码格式不一样

6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引

7、连接查询中，按照优化器顺序的第一张表不会走索引

8、如果查询中没有用到联合索引的第一个字段，则不会走索引

推荐阅读更多精彩内容