本文主要是介绍验证mysql的where条件字段上加函数，无法使用索引。，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
我们都听说过，写sql语句时，如果where条件里面，对字段使用了函数，就无法使用索引，只能走全表扫描。这句话是正确的吗？mysql真的是这样设计的吗？下面通过一些案例来验证一下。
1 初始化表
准备两张表, tradelog和tradedetail。
tradelog有交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)字段。
tradedetail有交易流水号(tradeid)、交易步骤(trade_step)、交易信息(trade_info)等字段。
建表语句如下：
CREATE TABLE `tradelog` (`id` int(11) NOT NULL,`tradeid` varchar(64) DEFAULT NULL,`operator` int(11) DEFAULT NULL,`t_modified` datetime DEFAULT NULL,PRIMARY KEY (`id`),KEY `tradeid` (`tradeid`),KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `trade_detail` (`id` int(11) NOT NULL,`tradeid` varchar(64) DEFAULT NULL,`trade_step` int(11) DEFAULT NULL, /*操作步骤*/`step_info` varchar(64) DEFAULT NULL, /*步骤信息*/PRIMARY KEY (`id`),KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
案例一 条件字段函数操作
向表里插入一些测试数据
这里使用存储过程来插入数据，sql代码如下：
DELIMITER //
CREATE PROCEDURE InsertRandomData()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 100 DOINSERT INTO tradelog (id, tradeid, operator, t_modified) VALUES (i, UUID(), FLOOR(1 + RAND() * 100), TIMESTAMPADD(DAY, FLOOR(RAND() * (365*4)), '2016-1-1'));SET i = i + 1;END WHILE;
END //
DELIMITER ;插入了100条，随机生成的测试数据，t_modified（交易日期）的范围是2016-1-1到2019-12-31。
生成的数据类似这样:
现在有一个要求，查询2016-1-1到2019-12-31的交易数据中，月份为7月份的交易日志。
这条sql语句可以这样写:
select * from tradelog where month(t_modified) = 7;
通过explain查看该语句的执行情况：
explain select * from tradelog where month(t_modified) = 7;
可以看到，这里key=null，没有用到索引。而rows=100，我一共插入了100行数据，说明走了全表扫描。而 t_modified字段是有索引的，mysql放弃了使用索引搜索树的功能，选择全表扫描。这是因为t_modified上加了一个month函数。
t_modified索引对应的B+树大概长这样：
mysql如果是拿到没有函数的字段， t_modified，就会依次比较节点关键字大小，选择走哪个分支。
但是，现在mysql拿到的是month( t_modified)，这就导致了mysql不知道该怎么办了，只能走全索引扫描，把行记录一行一行取出来，然后将字段 t_modified的值取出来，用函数截取出月份。
如何优化这条sql呢？
可以使用硬编码，把7月份表示处理，因为7月份就是从7-1到7-31，前面加个年份。sql语句如下：
select * from tradelog where
(t_modified >= '2016-7-1' and t_modified <= '2016-7-31') or
(t_modified >= '2017-7-1' and t_modified <= '2017-7-31') or
(t_modified >= '2018-7-1' and t_modified <= '2018-7-31') or
(t_modified >= '2019-7-1' and t_modified <= '2019-7-31');
查询结果是一样的
使用explain，查看sql底层执行情况：
explain select * from tradelog where
(t_modified >= '2016-7-1' and t_modified <= '2016-7-31') or
(t_modified >= '2017-7-1' and t_modified <= '2017-7-31') or
(t_modified >= '2018-7-1' and t_modified <= '2018-7-31') or
(t_modified >= '2019-7-1' and t_modified <= '2019-7-31');
可以看到这里key = t_modified，表示使用了索引，rows=7，只扫描了7行，执行效率大大提高。
除了给字段显示的加上函数，使用运算表达式也不可以。查看下面这条sql：
select * from tradelog where id + 1 = 100;
这条代码本质上是查询id=99的这一行数据
使用explain查看，该sql底层执行情况：
explain select * from tradelog where id + 1 = 100;
可以看到，id虽然是主键索引，但这条sql还是没有使用索引（key=null），而总共行数据就100行，这条sql就扫描了100行，说明进行了全表扫描。
所以在where条件里，对索引字段进行表达式运算，也不会走索引搜索树功能。
案例二 隐式类型转换
重新创建表，向表里添加一些数据
这里使用存储过程来插入数据，sql代码如下：
DELIMITER ;;
CREATE PROCEDURE idata()
BEGINDECLARE i INT;SET i = 1;WHILE(i < 10000)DOINSERT INTO tradelog VALUES(i,CONVERT(i,CHAR),i,NOW());SET i = i + 1;END WHILE;
END;;
DELIMITER ;CALL idata();
向tradelog表中插入了一万条数据，数据类似这样：
使用以下sql语句，查询tradeid为976的数据
select * from tradelog where tradeid = 976;
使用explain函数，查看执行情况
explain select * from tradelog where tradeid = 976;
关注key和rows两列。
key=null，说明此次查询没有使用索引，rows=9956, 扫描行数几乎达到了一万行，说明走的还是全表扫描。
但是建表时，tradeid是有索引的，为什么没有走索引搜索树呢？
这是因为tradeid是varchar类型，而我写的参数是int类型，发生了类型转换。
那到底是字符串类型转int类型，还是int类型转字符串类型呢？
可以通过以下sql语句进行判断：
selec '10' > 9;
如果返回的是1，说明是先把字符串‘10’转为数字10，然后比较10和9的大小，10比9大，所以结果是1。字符串转到int的类型转换
如果返回的是0，说明是先把9变成字符串‘9’，然后比较‘10’和‘9’的大小，第一位的‘9’大于‘1’，所以返回0。int到字符串类型转换。
执行后的结果：
结果为1，说明是把字符串转为了整数类型。
我们再来看这条sql语句：
explain select * from tradelog where tradeid = 976;
等于号左边是字符串类型，右边是整数类。所以mysql在执行这条sql语句的时候会把tradeid隐式转换成整数类型，真正执行时是下面这样:
select * from tradelog where CAST(tradeid AS INT)= 976;
这里对索引字段加了函数，触发了前面所说的原则，索引字段加函数，优化器会放弃走索引搜索树的功能，执行全索引扫描，全表扫描。
案例三 隐式字符编码转换
重新创建表，向表里面添加一些数据
insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());
insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');
这时候，如果要查询tradelog，id=2这一行的所有交易的详细信息，可以执行下面这句sql：
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
我们用explain来查看一下这条sql语句的执行过程
explain select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
explain会出现两行，主要关注key和rows字段：
- 第一行，显示优化器会先在tradelog表上，查到id=2的这一行（table那一列使用的l），这个步骤用上了主键索引（key那一列是PRIMARY），扫描行数是1（rows那一列是1）
- 第二行，优化器会在trade_detail表上，根据d.tradeid=l.tradeid，查询满足条件的行（table那一列的值是d）。在两张表上，tradeid都是由索引的。但是第二行的key=null，说明没有使用到索引，扫描行数是11，说明是全表扫描
我们重点关注一下第二行，在底层是这么执行的，为什么没有用到索引。
在这个执行计划里，是从tradelog里面取出tradeid，然后拿着这个tradeid去trade_detail里面匹配满足条件的行。所以tradelog是驱动表，trade_detail是被驱动表。tradeid是关联字段。
用图来表示执行流程:
根据图中可以，得到以下执行步骤：
- 第1步，根据id=2在tradelog表中拿到L2这一行。
- 第2步，从L2中取出tradeid这个字段的值。
- 第3步，根据tradeid值到trade_detail中查找满足条件的行。从前面explain，key=null可知，这个查找不会利用主键索引树的搜索功能，而是全索引扫描，通过遍历主键索引树的方式，一行一行判断tradeid是否满足。
从步骤分析上看，发现第3步不符合我们的预期，因为tradeid上是有字段的，在查询满足条件的行数，应该走tradeid索引对应B+树的查询算法，可以很快定位到满足条件的行。但是这里并没有，是为什么呢？
这是因为这两个表使用了不同的字符集，tradelog使用了utf8mb4字符集，trade_detail使用了utf8字符集。
为什么使用不同字符据就用不到索引呢？
我们将第3步单独拿出来，简化一下sql：
select * from trade_detail where tradeid = $L2.tradeid.value;
其中，$L2.tradeid.value的字符集是utf8mb4，tradeid 的字符集是utf8。
utf8mb4是utf8字符集的超集，执行上面比较操作时，会将utf8隐式转换到utf8mb4。
为什么是utf8转utf8mb4呢？
因为utf8mb4是utf8的超集，在进行隐式字符编码转换时，都是子类型转到父类型，因为父类型表示的范围更大一些，在发生类型转换时不会截断信息。这是程序语言的设计，为了避免在数据转换过程中由于截断导致数据错误，所以都是按数据长度增加的方向进行转换的。
所以，实际上，上面那条sql语句，在底层执行的时候是这样的：
select * from trade_detail where CONVERT(tradeid USING utf8mb4) = $L2.tradeid.value;
需要将trade_detail表里面的tradeid字段转为utf8mb4，再去和tradelog表里的tradeid字段比较。这里对字段使用了函数，就触发了mysql的设计，如果索引字段上有函数，优化器会放弃走搜索树的功能。
作为对比，查询trade_detail表中id=4这一行对应的交易员是谁？
可以执行以下sql语句：
select l.operator from tradelog l, trade_detail d where l.tradeid = d.tradeid and d.id =4;
使用explain查看该语句的执行情况：
explain select l.operator from tradelog l, trade_detail d where l.tradeid = d.tradeid and d.id =4;
与前面分析过程一样，这是这里第二行，key=tradeid，用到了索引，所以扫描行数rows只有1行，执行效率大大提高。这是因为第二行的执行步骤，在底层本质上是这样的：
select operator from tradelog where tradeid = $R4.tradeid.value;
$R4.tradeid.value就是trade_detail表中id=4的这一行。trade_detail的字符集是utf8，会进行隐式字符编码转换，变成tradelog的utf8mb4，所以真正执行的是：
select operator from tradelog where tradeid = CONVERT($R4.tradeid.value USING utf8mb4);
可以看到，tradelog的索引字段上没有使用函数，所以mysql就可以利用该索引的搜索树功能，走B+树的查询算法，很快定位到满足条件的行，避免全表扫描。
对于一开始，查询tradelog表id=2这一行的交易详细信息，的sql语句：
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
就可以优化了。优化方式有两种:
一、使用DDL语句，修改trade_detail表字符集，修改为utf8mb4。这样就可以避免在执行过程中发生隐式字符编码转换。
对应的sql语句如下：
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
再使用explain查看上面查询的语句：
explain select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
第二次就用到了索引。
使用DDL修改字符集的好处就是可以彻底解决这两张表，字符集隐式转换的问题，缺点是如果当前的业务量较大，使用DDL语句，会给表加上MDL写锁，导致所有对表的增删改查语句都被阻塞，业务就会停滞。所以还有第二种解决办法
二、修改sql语句，手动进行强制字符编码转换，将tradelog的tradeid从utf8mb4强转为utf8字符编码，避免了执行时，mysql进行隐式字符编码转换，将小类型转为大类型。
（这里为了不让第一次解决办法影响，我将trade_detail的字符集改回了utf8）
修改后的sql语句如下：
select d.* from tradelog l, trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
explain 查看该语句：
explain select d.* from tradelog l, trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
依然用到了索引。
修改sql语句的解决办法就是对其它并发业务的影响最小，但是无法彻底解决这两种表字符集不一致的问题。
这篇关于验证mysql的where条件字段上加函数，无法使用索引。的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
