Mysql 索引上做计算为什么会导致索引失效

一、数据准备

创建 employee 表

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_hire_time` (`hire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

employee 表中 name 字段为 varchar 类型, age 字段为 int 类型,hire_time 为 timestamp 类型,同时针对字段 name、age、hire_time 分别创建了 3 个单列索引 idx_name、idx_age、idx_hire_time ,现在我们为 employee 表插入几条数据

二、SQL 执行计划分析

数据准备好了之后我们就可以开始分析了

下面先看两条 Sql,分析一下这些 Sql 会不会走索引

select * from employee where name = '1';
select * from employee where age = 2;

我们很自然的可以想到,name 是字符串类型,传递的参数也是字符串,age 是数字类型,传递的也是数字,传递的参数类型与索引字段类型完全匹配,所以它们一定是会走索引的

通过 explain 关键字看一下执行计划

果然上面两个 Sql 是能使用到索引的,那么继续看一下这条 Sql

select * from employee where hire_time = '20220513193801';

hire_time 字段的类型是 timestamp,而我们传递的参数是字符串 '20220513193801',参数类型和索引字段类型并不匹配,那么这样的情况会使用到索引吗?

在 Mysql 中如果字段类型和参数类型不一致,并且字段类型和参数类型中存在字符串,那么 Mysql 都会把字符串转换成其它类型,从而保证字段和参数的类型一致,例如上面的 Sql 就会把字符串 '20220513193801' 转换成时间类型,然后再根据条件进行筛选过滤

通过查询可以推断出字符串 '20220513193801' 会被转成时间格式 2022-05-13 19:38:01,然后再根据条件去数据库中查找符合条件的数据记录

转换只发生在参数上,并没有在索引字段上进行 显示/隐式 的转换,所以索引不会失效

继续分析一下下面的两条 Sql,它们会用到索引吗?

select * from employee where age = '1';
select * from employee where name = 0;

我们大多数情况下都会认为 age 是数值类型,传递的参数是字符串 '1', name 是字符串类型,传递的参数却是数值类型的 123,索引字段类型和参数类型不匹配,所以它们不会走索引,真的是这样吗,我们可以使用 explain 验证一下

第一条 Sql 走了索引,而第二条 Sql 却没有走索引,为什么呢,下面我们就来分析一下

先看第一条 Sql

select * from employee where age = '1';

前面我们提到过,当有数据库字段类型和传递的参数类型不一致的时候,如果包含字符串,Mysql 默认会将字符串转成可以让 = 号两边匹配的上的类型

age 是数值类型, '1' 是字符串类型,那么字符串 '1' 会转成数字类型,这样 = 号两边的类型就相符了,如果将字符串 '1' 转成数值类型,转换之后的数值类型的值是多少呢?

• 如果是数值类型的字符串,那么转成数值类型之后的值就是对应的数值本身

例如 '6' 转换成 6、'12' 转换成 12

• 如果字符串不是以数字开头,转成数值类型之后,数值类型的值为 0 

例如 'x13x' 转换成 0、'abc' 转换成 0 

• 如果字符串是以数字开头,转成数值类型的时候会将开头的数值截取下来,直到截取到非字符串为止,截取下来的数字就是转换后的结果

例如 '1a4' 转换成 1、'16xx14' 转换成 16

age 字段是数值类型,虽然传递的参数是字符串类型,但是转换只发生在参数上,基于 age 字段构建的索引 B+ 树并没有发生任何变化,只需要将参数对应的字符串转化成数字类型的值,然后使用数字类型的值去 age 索引树上查找即可,索引并不会失效

继续看第二条 Sql

select * from employee where name = 123;

前面已经分析了,如果包含字符串的参数类型和字段类型不匹配,Mysql 会将字符串转换成其它类型,以保证字段类型和参数类型相一致

name 字段是字符串, 123 是数值类型,所以这一条 Sql 会将 employee 表中 name 列的所有值先转成数值类型,然后再进行匹配过滤

可以验证一下

上面的 Sql 为什么能筛选到 5 条记录呢,那是因为 Mysql 会将 name 列的所有值先转成数值类型

'0'、'alis'、'dani'、'jean'、'owen' 这些字符串都会转成 int 类型的 0 

'1' 转成 1

'2' 转成 2

'3' 转成 3

所以上面的 Sql 能匹配到 5 条记录

接下来分析一下,为什么 name 列不能用到索引 idx_name

先看一下基于 name 字段建立的正常索引树是什么样子的 (黄色的是主键 id)

条件是数字类型的 123 ,而基于 name 字段的索引树是字符串类型的,假如你想要利用索引去搜寻,必然要先将索引树上的字符串转成数字类型,然后再根据数字类型的参数 123 去整颗索引树上进行查找比对,找出符合条件的数据,否则数字类型的 123 怎么可能去字符串类型的索引树上去搜寻结果呢

基于上面的要求,我们就来转换一下索引树

'0'、'alis'、'dani'、'jean'、'owen' 这些字符串都会转成 int 类型的 0 

'1' 转成 1

'2' 转成 2

'3' 转成 3

那么原先的索引树就会变成如下这个样子(黄色的是主键 id)

转换的过程会存在以下的问题

1、完全转换一颗这样的 B+ 树是十分消耗性能的,因为如果表数据过大的情况下,索引树也是很庞大的

2、转换后的 B+ 树有可能不是一颗符合规范的 B+ 树,例如上图转换过后的 B+ 树,叶子节点的 key 并不是有序的(key  0<-->1<-->2<-->3<-->0<-->0<-->0<-->0 并不是有序的),既然不是一颗合格的 B+ 树,那么你就不能利用 B+ 树的特性去快速查找了,如果你想把它变成一颗合格的 B+ 树,你又要做很多额外的操作,那么这个成本开销是很大的,显然不现实

3、如果其它的 Sql 也用到了 idx_name 这个索引,转换后的 B+ 树就会影响到其它使用了该索引的 Sql

所以 Mysql 底层认为,与其这样还不如全表扫描呢,这样效率反而更高一些,Mysql 根据自身维护的一套成本计算规则认定全表扫描的成本更低,所以输出执行计划的时候 type 列的值就是 ALL

那么既然 Mysql 认为全表扫描成本更低,而你又认为使用索引的成本更低,如果你强制使用索引(force index),会怎么样呢?

即使你想强制使用 idx_name 索引,Mysql 也不会这么做

同理,在索引字段上显示的使用函数,或者对索引字段进行运算也会使索引失效

三、总结

1、在索引字段对应的参数上 显式、隐式转换不会影响到索引树的结构,索引不会失效

2、对索引字段进行显示、隐式转换都会让索引失效,因为你想利用索引的话必然要先改变索引树的结构,这是一个很高的成本,与其是这样,还不如全表扫描

3、为了避免出现各种意想不到的问题,一定要保证索引字段的类型和条件中参数的类型一致

四、题外话

分享一个生产环境发生的慢 SQL 问题

我们生产环境有一张集团总商户表,里面有一个索引字段 first_agent_id,类型是 int 类型

前端在传递参数的时候没有对输入框做任何限制,既可以输入数字也可以输入字符串

我们后台在实体类中定义了一个字段 private String firstAgentId,用来接收前台传递的参数

当前端输入数字 123456 的时候,可以通过实体类进行转换,变成字符串类型,Sql 中的条件变成了 first_agent_id = '123456' ,由于 fisrt_agent_id 是 int 类型,参数是字符串 '123456',所以字符串会先转换成 int 类型,也就等价于 first_agent_id = 123456,索引不会失效,查找性能也是比较高的

问题就出在,当前端输入的是一些杂乱的字符串,例如  'afsdfsd ' 的时候,Sql 中的条件变成了 first_agent_id = 'afsdfsd ' ,字符串 'afsdfsd ' 转换成 int 类型的 0, Sql 的条件就变成了 first_agent_id = 0,而我们这张集团商户表中存在 130 万条 first_agent_id = 0 的过期商户,这 130 万过期商户就很无辜的被查出来了,后面使用 in(商户号1,商户号2........) 操作的时候,整个数据库都炸了