总结mysql索引失效的N种情况
摘自:https://www.jianshu.com/p/3ccca0444432
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,下面来看下执行计划:
![](http://upload-images.jianshu.io/upload_images/8826822-51fe6b58ee7b8291.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
type为All,仍然是走的全表扫描。
2、like查询是以'%'开头
仍然用1中的user表执行如下sql
explain SELECT name,age,address FROM user where name like '%头强'
上例sql中name上有索引index_name,下面来看下执行计划:
![](http://upload-images.jianshu.io/upload_images/8826822-3050444c238456e4.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
type为All,仍然是走的全表扫描。 但是如果想让以‘%’开头仍然使用索引,则需要使用覆盖索引,即只查询带索引字段的列:
explain SELECT name FROM user where name like '%头强'
![](http://upload-images.jianshu.io/upload_images/8826822-2635a5a316c9aef9.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
如上,执行计划里的key用到了index_name。
3、对查询的列上有运算或者函数的
仍然用1中的user表执行如下sql
explain SELECT name,age,address FROM user where substr(name,-2)='头强'
上例sql中name上有索引index_name,下面来看下执行计划:
![](http://upload-images.jianshu.io/upload_images/8826822-595fb0ee19513446.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
再比如:
explain SELECT name,age,address FROM user where age+1=12
上例中age有索引index_age,执行计划如下:
![](http://upload-images.jianshu.io/upload_images/8826822-bf2fffebb1723e10.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
仍然用1中的user表执行如下sql
explain SELECT name,age,address FROM user where name = 10
上例sql中name上有索引index_name,下面来看下执行计划:
![](http://upload-images.jianshu.io/upload_images/8826822-0c9ded1b98b70b61.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
同样,没有用到索引走的全表扫描。
这里面的原因其实跟例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
执行计划如下:
![](http://upload-images.jianshu.io/upload_images/8826822-d2564f7aad558f47.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
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
执行计划如下:
![](http://upload-images.jianshu.io/upload_images/8826822-08255e7f8797fad9.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
此时,由于要查询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无索引,看下执行计划
![](http://upload-images.jianshu.io/upload_images/8826822-e08f1e2f31d3dd7e.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
a表,b表都是全表扫描,而建表语句不变,把左外连接改为右外连接
EXPLAIN select a.name,a.age,b.name,b.job
from
user a
right JOIN job b
ON a.name =b.name
执行计划如下:
![](http://upload-images.jianshu.io/upload_images/8826822-c5d358ab585c7565.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
可以看到,a表用到了索引,同样,将右连接改为内连接再看下
EXPLAIN select a.name,a.age,b.name,b.job
from
user a
inner JOIN job b
ON a.name =b.name
执行计划如下:
![](http://upload-images.jianshu.io/upload_images/8826822-2bae7266b95ada49.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
同样,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'
执行计划如下:
![](http://upload-images.jianshu.io/upload_images/8826822-6f7161105e86bf4d.png?imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)
走的是全表扫描。
作者:catalina_
链接:https://www.jianshu.com/p/3ccca0444432
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。