『Mysql』汇总Mysql索引失效的常见场景-阿里云开发者社区

『Mysql』汇总Mysql索引失效的常见场景

2022-07-22 211

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群版 2核4GB 100GB

云数据库 RDS MySQL，高可用版 2核4GB 50GB

简介： 📣读完这篇文章里你能收获到- 列举引起Mysql索引失效的多种实际场景- 索引使用的误区及设计的建议

请添加图片描述
📣读完这篇文章里你能收获到

列举引起Mysql索引失效的多种实际场景
索引使用的误区及设计的建议

请添加图片描述

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

一、总结Mysql索引失效的12种情况

查询条件中有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 * from product where ProductId ='rm226' or ProductDescription = '2221'

上例sql中name字段上有索引index_name，下面来看下执行计划：
在这里插入图片描述

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

方案：两个字段使用索引

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

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

explain
select * from product where ProductId like'%226'

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

在这里插入图片描述

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

explain
select ProductId from product where ProductId like'%226'

在这里插入图片描述

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

方案：覆盖索引

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

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

explain
select * from product where substr(ProductId,-2)='226'

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

请添加图片描述

再比如：

explain
select * from product where ProductId+1='226'

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

请添加图片描述

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

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

explain
select * from product where ProductId=1226

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

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

explain
select * from product where cast(ProductId as signed)=1226

很明显，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;
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;

注意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

执行计划如下：
请添加图片描述

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

执行计划如下：

此时，由于要查询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无索引，看下执行计划
在这里插入图片描述
a表，b表都是全表扫描，而建表语句不变，把左外连接改为右外连接

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

执行计划如下：

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

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

执行计划如下：

在这里插入图片描述
同样，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.如果查询中没有用到联合索引的第一个字段，则不会走索引

执行查询

explain
select * from product where ProductDescription = '22'

执行计划如下：

在这里插入图片描述

9.联合索引Or失效

执行查询

explain
select * from product where ProductName ='22' or ProductDescription = '22'

10.联合索引范围查询 > 部分索引失效
联合索引的第一个字段作范围查询时会导致失效

执行查询

explain
select * from product where
  ProductName ='22' 
 and ProductDescription = '22'
 and ProductId > '22'

11.用is null和is not null没注意字段不为空失效

执行查询

explain
select * from product where
  ProductUrl is null

如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
如果字段允许为空，则is null走 ref 类型的索引，而is not null失效

12.使用了select *

执行查询

explain
select * from product

方案：覆盖索引

explain
select * from product where Id in (select Id from product)

请添加图片描述

二、索引使用误区

说是误区，其实就是大家经常误解的点，然而并不会导致失效

1.使用not in会导致索引失效

explain
select * from product where
  Id not in (100,102);

2.使用不等于号会导致索引失效

explain
select * from product where
  Id != 100;

3.order by索引字段顺序不当导致索引失效

explain
select * from product where
  Id != 100 order by ProductId;

请添加图片描述

三. 索引设计的几个建议

优先使用唯一索引，能够快速定位
为常用查询字段建索引
为排序、分组和联合查询字段建索引
一张表的索引数量不超过5个
表数据量少，可以不用建索引
尽量使用占用空间小的字段建索引
用idx_或unx_等前缀命名索引，方面查找
删除没用的索引，因为它会占一定空间

『Mysql』汇总Mysql索引失效的常见场景

一、总结Mysql索引失效的12种情况

二、索引使用误区

三. 索引设计的几个建议

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像