sql 类型转换导致索引失效

1. 简介

在我学习Mysql大数据分页的时候，在测试学习的时候，select * from amj_devinfo where devcho = 77 limit 20000, 10;因为，字段devcho 的类型是varchar 而我传入的参数是int 整型；所以，导致索引失效；既然，发现了问题，就学习一下由于sql类型转换导致索引失效的相关问题，及其背后的知识点；

2.相关结论

本文，先给出结论，然后，再通过测试来查看这个结论是否正确；
结论： 索引字段不能进行函数操作，但是，索引字段的参数是可以使用函数操作的；

2.1 建表语句

create table table1(    
id int NOT NULL PRIMARY KEY AUTO_INCREMENT, 
col1 int, col2 varchar(10),
key(col1),key(col2)
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4;
复制代码

我们对col1和col2建立了索引；直接上sql；

2.2 相关测试

EXPLAIN select id, col2 from table1 where col2 = '2';
复制代码

这个，类型type = ref 没什么问题，说明，是通过索引树确定了col2= ‘2’的位置，只是结果不止一个而已；

EXPLAIN select id, col2 from table1 where col2 = 2;
复制代码

这个时候，将’2’改成为2，explain分析结果为：

首先：说明一点，这也是用上索引的。但是，它是基于索引树的遍历；不是通过索引树而去定位col2 = ‘2’这个位置；我们都知道，基于索引树的遍历和全表扫描基本上也没啥区别了；所以，索引失效指的就是这个。以上是，索引字段为字符串，参数为整型时，导致索引失效；那么，如果索引字段为整型，参数为字符串呢？

EXPLAIN select id, col1 from table1 where col1 = '2';
复制代码

经过，测试，发现没有什么问题；为什么就没问题呢？ 回到之前给出的结论：索引字段不能进行函数操作，但是，索引字段的参数是可以使用函数操作的。所以，我们可以推理一下，索引字段col1为整型，参数为字符串；Mysql将参数的字符串，强制类型转换为整型,所以，索引不会失效；而上面的col2索引字段为整型，而参数为字符串，Mysql将索引字段转为整型；所以，导致索引失效；
验证
select "10" > 9;
如果，答案为1 说明是将”10″ 变成 10
如果，答案为0 说明是将9变成”9″;
结果为1；
结论：当字符串与整型发生冲突时，会将字符串转为整型；
这也验证了：索引字段不能进行函数操作，但是，索引字段的参数是可以使用函数操作的；
补充：
其实，只要对索引字段有了函数操作，不管多么简单，都是会导致索引失效的；

explain select id, col1 from table1 where col1 + 1 = 3;
复制代码

索引也是失效的；

3.隐式字符编码转换

其实，隐式字符编码转换也算是对索引字段进行了函数操作；只是，它比较隐蔽；需要两张表字符编码类型不同；
建表语句：

 CREATE TABLE `order_error_log` (
  `id` varchar(32) NOT NULL,
  `order_id` varchar(32) DEFAULT NULL COMMENT '订单id',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单日志表';


CREATE TABLE `order` (
  `id` varchar(32) NOT NULL,
  `create_user` varchar(32) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

复制代码

对如下sql进行分析：

explain select o.* from order_error_log oe INNER JOIN `order` o on  o.id = oe.order_id where oe.id = "11";
复制代码

分析结果如下：

其实，本来我们是希望这条sql语句这么执行：

1. 根据oe.id在order_error_log中，找到id=”11″这条语句,找到对应的order_id；
2. 然后，在order 这个表上，根据id索引索引树，找到o.id = oe.order_id的相关语句，找出o.* 相关的内容；

这个时候，两个sql查询都应该是主键索引；但是，现在的执行过程如下：

1. 根据oe.id在order_error_log中，找到id=”11″这条语句,找到对应的order_id；
2. 然后，在order 这个表上，全表扫描，找到o.id = oe.order_id的相关语句，找出o.* 相关的内容；

这不是我们所希望的，原因分析：因为oe.order_id 字段类型是utf8mb4,而o.id的类型是utf-8;因为，utf8mb4是utf8的超集，所以，类型转换时，为了保证精度不损失，所以，是将unt8转换为utf8mb4;所以，针对o.id = oe.order_id这个条件，因为，查找的是o.* ;所以，会把o.id这个字段转换为utf8mb4，这时候，因为，对索引字段进行了函数操作，会导致索引无法使用；即在o这张表上，用不上主键索引；
解决方案：
因为，索引字段进行了类型转换导致无法使用索引，所以，我们只需要改变发生冲突的索引字段即可；如将order_error_log的order_id 这个字段改成utf-8;

alter table order_error_log change order_id order_id varchar(32) character set utf8;
复制代码

再进行sql语句分析：符合预期