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........) 操作的时候,整个数据库都炸了
