假如现在有一个交易系统，在这个交易系统中有一个表，这个表中记录着每次交易的相关信息，这个表的名字是tradelog，在这个表中有交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段，这个表的建表语句如下。
CREATE TABLE `tradelog` (`id` int(11) NOT NULL,`tradeid` varchar(32) 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;
假设现在在这个表中已经有了从2016年初到2018年底的交易数据，现在我们有一个需求，就是统计出这个表中发生在七月份的交易的数量，我们可以这么来写sql语句。
select count(*) from tradelog where month(t_modified)=7;
因为我们已经在t_modified字段上建立了索引，所以我们以为这条语句会很快执行完，但是真正执行这条语句时我们会发现这条语句会在执行很长时间后，才返回结果。<br />既然我们已经在字段上建立索引了，这条sql语句为什么还会执行的这么慢呢？这是因为在这个语句中我们对建立了索引的字段使用了函数，那么为什么对建立了索引的字段使用函数会导致sql语句执行变慢呢？<br />这是因为对建立了索引的字段使用函数会导致无法使用索引树来加快sql语句的执行过程，比如如果sql语句中的查询条件是where t_motified='2018-7-1'，就可以通过t_motified索引树来降低扫描的行数，如果sql语句中的查询条件改为where month(t_motified)=7，由于t_motified索引树中的叶子节点并不是对于月份是有序的，所以我们没办法只扫描特定的某些叶子节点，想要找出所有满足查询条件的数据行必须要在索引树上做全表扫描才能找出来。所以我们经常说对字段使用函数就走不了索引了。<br />那么会在哪个索引树上做全表扫描呢？会在主键索引树上做全表扫描，还是在t_motified字段的索引树上做全表扫描呢？取决于两棵索引树的大小，Mysql会优先在占用磁盘空间更小的索引树上做全表扫描。<br />然后我们看一下这条语句的explain执行计划，看看我们分析的对不对。<br /><br />key的值是t_motified，说明在这个sql语句的执行过程中使用了t_motified索引，前面没有提到在交易表中插入了10w条数据，而row的值是100035，这就说明在这个sql语句的执行过程中进行了全表扫描，extra字段的值中包含了using index，这说明在这个sql语句的执行过程中使用了覆盖索引，没有进行回表。<br />也就是说，由于我们对t_motified字段使用了函数，从而只能通过对索引树进行全表扫描来找出所有满足查询条件的数据行。那么我们可以怎么改写上面的sql语句，从而能够使用上索引树上叶子节点的有序性快速定位到满足查询条件的叶子节点进而减少需要扫描的行数呢？<br />我们可以把上面的sql语句改写成基于t_motified字段的范围查询，范围查询是能够使用上索引树上叶子节点的有序性的，那么我们就可以把sql语句改写成如下。
select count(*) from tradelog where
-> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
-> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
-> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
由于对字段进行了函数操作，所以Mysql就不能从索引树上快速定位到所有满足查询条件的叶子节点，而只能通过对索引树进行全表扫描来找出所有满足查询条件的数据行。
但是即使是不会影响索引树有序性的函数，Mysql也不会走索引，比如select from tradelog where id+1=10000，虽然加1操作并不会影响索引树的有序性，但是Mysql在执行这个sql语句时也不会根据索引树的有序性直接定位到id=9999的叶子节点，仍然是通过全表扫描来找出满足id+1=10000的数据行，那么我们可以怎么改上面的语句，从而能够快速定位到满足查询条件的数据行呢？把上面的sql语句改成select from tradelog where id=10000-1就可以了，Mysql会先计算出10000-1的结果，然后接下来的执行过程就和select * from tradelog where id=9999一样了。
加1操作算是对字段进行函数操作吗？加1操作和month()函数的本质是一样的，都是对字段的值进行操作，只不过month函数做的是截取字段值中的部分字符，加1操作做的是数值运算。
下面让我们来看一下这条sql语句。
select * from tradelog where tradeid=110717;
在交易编号tradeid字段上我们已经建立了索引呀，为什么这个sql语句的explain计划中显示的是执行这条sql语句需要进行全表扫描呢？
这是因为tradeid字段的数据类型是varchar(32)，但是在sql语句中的值是一个整数，所以在判断是否满足查询条件之前，要先进行数据类型的转换，那么在Mysql是把整数转换成字符串与tradeid字段的值进行比较的呢？还是把tradeid字段的值转换成整数再与sql语句中的值进行比较的呢？
为什么进行了数据类型的转换，就走不了索引了呢？
有一个很简单的确定是谁转换成谁的方法，我们只需要去看select “10”>9的结果。
如果是把整数值转换成字符串，那么select语句的执行结果就是1；如果是把字符串的值转换成整数，那么select语句的执行结果就是0，那么我们就到Mysql中验证一下，验证结果如图所示。
从图中我们可以看到，select “10”>9语句的执行结果是1，所以在Mysql中，如果比较的是字符串和数字的大小，那么是把字符串转换成数字再比较的。
我们再回到这个sql语句。
select * from tradelog where tradeid=110717;
所以Mysql会先将tradeid的值从字符串转换为整数值，再110717进行比较，也就是下面的sql语句描述的逻辑。
select * from tradelog where CAST(tradid AS signed int) = 110717;
而在这条sql语句中，对建立了索引的字段进行了函数操作，所以这条sql语句的执行过程也是不走索引的。
那么我们在执行下面这条sql语句的过程中会进行全表扫描吗？在建表时我们给id字段设置的是整数数据类型。
select * from tradelog where id="83126";
Mysql在执行这个sql语句的时候会先将”83126”字符串转换为数字83126，然后按照sql语句select * from tradelog where id=83126来执行，也就是说，执行这个语句的过程就相当于执行的是：
select * from tradelog where id=int("83126")
假如在这个交易系统中还有另外一张数据表，trade_detail表，用来记录每次交易会经过哪些步骤的表，现在我们向交易日志tradelog表和交易详情trade_detail表中插入一些数据。
CREATE TABLE `trade_detail` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`trade_step` int(11) DEFAULT NULL, /*操作步骤*/
`step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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');
这时，如果要查询id=2的交易会经过哪些步骤，sql语句可以这么写：
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
这个sql语句的explain计划如下。
explain执行计划中记录的是优化器最后选取的方案，在这个explain计划的第一行中记录的是执行器会先在交易日志tradelog表中直接定位到id=2的数据行，所以扫描行数是1，并且在这个过程中使用的是主键索引。
在这个explain执行计划的第二行中的key字段的值是null，说明没有使用交易详情trade_details表上的tradeid索引，并且trade_details表中一共有11行数据，而rows字段的值是11，所以是在主键索引树上进行了全表扫描。explain执行计划的第二行对应的是什么过程？对应着select from trade_details where tradeid=取出的值;语句的执行过程。
那么这个sql语句的执行流程是什么样的呢？
Mysql先在tradelog表的主键索引树上定位到id=2的叶子节点，取出这个叶子节点中存放的数据行，再从数据行中取出tradeid字段的值。接下来就是拿着取出的tradeid字段的值到trade_details表中找匹配的数据行，接下来的过程就相当于执行的是select from trade_details where tradeid=取出的值;语句。从explain执行计划的第二行中我们能够看到Mysql是通过遍历主键索引树上的所有叶子节点来找出所有满足查询条件的数据行的。
但是这并不符合我们的预期，因为我们在trade_details表的tradeid字段上建立了索引，我们希望能够使用tradeid索引快速定位出满足查询条件的数据行，那么为什么在explain执行计划的第二阶段没有使用索引来加速呢？
这是因为两张表使用的字符集不同，tradelog表使用的字符集是utf8mb4，而trade_details表使用的字符集是utf8，字符集是什么？对两张字符集不同的表做关联查询是用不了关联字段上建立的索引的，那么为什么两张字符集不同的表在做关联查询时就用不了关联字段上建立的索引呢？
不符合我们预期的是explain执行计划的第二行，而explain执行计划的第二行中记录的是select * from trade_details where tradeid=取到的值;语句的执行过程的相关信息，并且取到的值的字符集是utf8mb4，结合前面的两个例子，我们可以推测出不能使用的原因，一定是因为在关联字段上发生了字符集类型的转换，也就是说在关联字段上进行了函数操作，所以用不了关联字段上建立的索引。
那么utf8和utf8mb64字符集是谁转换成谁呢？在Mysql中的设置是把utf8字符集转换成utf8mb64字符集，这是因为
因此，在执行上面的sql语句的时候，会从主键索引树上将查询字段的值一个个取出来，并且把这些字符串使用的字符集都转换成utf8mb64字符集，再与取到的值进行比较。
那么我们就可以把上面的sql语句改写成这个写法，它们的执行过程是一样的。
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
所以，做关联查询的表字符集不同只是表面上的原因，本质上的原因还是由于对建立了索引的字段进行了函数操作。
现在我们再考虑一个新的需求，找出trade_details表中id=4的操作，这个操作的操作者是谁，那么这个sql语句是什么样子的呢？
select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
这个sql语句的explain执行计划是什么样的呢？
在这个语句里trade_details表成了驱动表，什么是驱动表？先在哪张表上查询数据哪张表就是驱动表，但是从explain执行计划的第二行我们可以看到使用了被驱动表中的索引，并且扫描的行数是1，那么为什么这里就能够使用索引呢？
假设驱动表trade_detail中id=4的数据行记为R4，那么在被驱动表tradelog上执行的就是：
select operator from tradelog where traideid =$R4.tradeid.value;
$R4.tradeid.value字符串使用的字符集是utf8，按照字符集转换规则，Mysql会先把utf8字符集转换为utf8mb64字符集，那么就相当于执行的是：
select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);
从这个sql语句中我们能够看到，函数操作是加在从R4数据行取出的值上的，而不是加在建立了索引的字段上，所以可以正常使用索引。
所以，如果想对select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;语句进行优化的话，有以下办法。
最简单的方法肯定是把trade_detail表的tradeid字段使用的字符集改为utf8mb64，那么就不会存在字符集转换的问题了。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
如果能够修改字段使用的字符集，这种方法是最好的，但是如果表中的数据非常多，或者是由于业务原因暂时不能做这个DDL（改变字段使用的字符集，也属于DDL的一种），什么是DDL？更改表的结构的行为，都属于DDL。那么就只能采用修改sql语句的办法了。
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
我们可以在sql语句中把从tradelog表中取出的tradeid的值从utf8mb64字符集转换为utf8字符集，这样trade_detail表中的tradeid的值和从tradelog表中取出的tradeid的值就不存在类型不一致的问题了，也就不会出现类型转换问题了。
从explain执行计划我们可以看到，这个sql语句按照我们期望使用了索引。
若有收获，就点个赞吧
0 人点赞
