公众号后台回复“学习+JavaSE”，即可免费获得学习资料
前言：本公众号将推出系列性的知识分享，以专题或者模块的方式，向读者分享学习体会和心得。所以想要学习的朋友可以点击关注，一起学习。
我们在建立一张表的trade_detail，用于记录交易的操作细节，为了便于查看操作的细节，我们王两张表中插入一些数据。
mysql> 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*/
我们一起来看下这个结果：
第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描一行；
第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。
在这个执行计划里，是从tradelog表中取tradeid字段，再去trade_detail表里查询匹配字段。因此，我们把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。
接下来，我们看下这个explain结果表示的执行流程：
图中：
第1步，是根据id在tradelog表里找到L2这一行；
第2步，是从L2中取出tradeid字段的值；
第3步，是根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配。
进行到这里，你会发现第3步不符合我们的预期。因为表trade_detail里tradeid字段上是有索引的，我们本来是希望通过使用tradeid索引能够快速定位到等值的行。但，这里并没有。
大家可能也会想到原因那就是字符集不一样。一个是utf8，一个是utf8mb4。但是为什么不一样的字符集，就会导致用不上索引呢？
问题是出在了第三步，如果单独把这一步改成SQL语句的话，那就是：
select * from trade_detail where tradeid=$L2.tradeid.value;
其中，$L2.tradeid.value的字符集是utf8mb4。参照前面的例子，字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。
因此， 在执行上面这个语句的时候，需要将被驱动数据表里的字段一个个地转换成utf8mb4，再跟L2做比较。
也就是说，实际上这个语句等同于下面这个写法：
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
CONVERT()函数，在这里的意思是把输入的字符串转成utf8mb4字符集。
这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。
到这里，你终于明确了，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。
作为对比验证，我给你提另外一个需求，“查找trade_detail表里id=4的操作，对应的操作者是谁”，再来看下这个语句和它的执行计划。
mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
这个语句里trade_detail 表成了驱动表，但是explain结果的第二行显示，这次的查询操作用上了被驱动表tradelog里的索引(tradeid)，扫描行数是1。
这也是两个tradeid字段的join操作，为什么这次能用上被驱动表的tradeid索引呢？我们来分析一下。
假设驱动表trade_detail里id=4的行记为R4，那么在连接的时候（图5的第3步），被驱动表tradelog上执行的就是类似这样的SQL 语句：
select operator from tradelog where traideid =$R4.tradeid.value;
这时候$R4.tradeid.value的字符集是utf8, 按照字符集转换规则，要转成utf8mb4，所以这个过程就被改写成：
select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);
你看，这里的CONVERT函数是加在输入参数上的，这样就可以用上被驱动表的traideid索引。
理解了原理以后，就可以用来指导操作了。如果要优化语句
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
的执行过程，有两种做法：
比较常见的优化方法是，把trade_detail表上的tradeid字段的字符集也改成utf8mb4，这样就没有字符集转换的问题了。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大， 或者业务上暂时不能做这个DDL的话，那就只能采用修改SQL语句的方法了。
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
这里，我主动把 l.tradeid转成utf8，就避免了被驱动表上的字符编码转换，从explain结果可以看到，这次索引走对了。
好了，今天的介绍到这里就结束了，大家有问题了可以私信我，大家共同学习，一起进步哈！
喜欢的同学可以点击“在看”，并且关注ITwords微信公众号，第一时间获取更新内容。您的转发和点赞将是我原创的动力，感谢您的支持。
扫描二维码
获取更多精彩
ITwords
往期回顾：
