背景

有时候我们经常遇到明明语句写的很简单，而且也应用了索引字段，但是却执行了好久,一脸懵逼
举个例子
create table order_log
(
id INT(10) auto_increment
primary key,
id_str VARCHAR(32) null,
order_number VARCHAR(32) not null
);
create index order_log_id_str_index
on order_log (id_str);

我在order_log里面插入了1W条数据数据大致如下

函数

假设我想查询出Id 拼接字符串等于1W的结果集
select * from order_log where concat(id,'W') = '1W' ;
但是当查看执行计划的时候却傻眼了,发现并没有应用索引

追究其原因是B+索引树因为叶子节点存储的是具体的数值，当通过函数传入到索引树的时候，优化器认为索引值的有序性所以不知道该如何查找了,因此优化器就决定放弃走树搜索功能

隐式转换

假设我想查询id_str=‘3’的数据

select * from order_log where id_str = ‘3’
查看执行计划发现没有问题，应用了索引，扫描行数1

但是我稍微做了一点点修改,我把字符串的id_str的数值应用的int类型

select * from order_log where id_str = 3
查看执行计划发现竟然扫描了oder_log_id_str_idenx的所有行

原因和函数执行一样mysql内部会存在隐式转换，在我的db里,mysql的规则是默认把字符串转换成int，所以对于mysql优化器来说实际上翻译的结果是
explain select * from order_log where CAST(id_str AS signed int) = 3;
所以实际上是应用了cast函数,虽然优化器放弃使用树索引，但是它评估还是选择应用了索引全扫描

隐式字符编码转换

假设现在存在两张表order_log,order_log_detail,其中order_log_detail通过log_Id和order_log关联，唯一的区别是

select orderLog.*
from order_log orderLog ,
order_log_detail orderLogDetail
where orderLog.id_str = orderLogDetail.order_log_id
and orderLogDetail.order_log_id = '2';

查看执行计划发现,发现order_log的索引并没有生效

原因是也是存在隐式字符串编码转换
where orderLog.id_str = orderLogDetail.order_log_id
相当于
where convert(orderLog.id_str using utf8mb4) = orderLogDetail.order_log_id
因为utf8mb4 是utf8的超集所以会优先转换索引列orderLog.id_str

优化

把两个表的字符集改成一样的
如果表太大暂时无法修改，则修改sql语句
where orderLog.id_str = convert(orderLogDetail.order_log_id using utf8)
强行转换成字符集一样的也可以避免

总结

对于优化器如果想通过B+树快速搜索定位数据,不可以对索引字段做操作,那样优化器是无法使用索引树查询,我们可以通过让条件应用函数
例如
1.select * from order_log where id_str = 3 ===> select * from order_log where id_str = cast(3 as char(32))
2.where orderLog.id_str = orderLogDetail.order_log_id ===> where orderLog.id_str = convert(orderLogDetail.order_log_id using utf8)