假设现在有一个交易系统，涉及的表有交易记录表tradelog和交易详情表trade_detail，表结构如下：

  2. mysql> CREATE TABLE `tradelog` (
  3.   `id` int(11) NOT NULL,
  4.   --交易流水号
  5.   `tradeid` varchar(32) DEFAULT NULL,
  6.   --交易员id
  7.   `operator` int(11) DEFAULT NULL,
  8.   --交易时间
  9.   `t_modified` datetime DEFAULT NULL,
  10.   PRIMARY KEY (`id`),
  11.   KEY `tradeid` (`tradeid`),
  12.   KEY `t_modified` (`t_modified`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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');

条件字段使用函数

现在记录了从2016年初到2018年底的所有数据，当我们要查询发送在所有年份中7月份的交易记录总数，SQL如下：

select count(*) from tradelog where month(t_modified)=7;

由于t_modified字段上有索引，是不是会认为这条SQL会执行很快，但实际上是执行了很长时间才返回结果。
当时当我们修改为where t_modified='2018-07-01'的时候，就查询的很快。

我们都知道MySQL的索引存储结构是B+树，索引存储情况大致如下
image.png
节点上边数字为month()函数对应的值，当使用month函数时，传入的值为7，在树的第一层就不知道该怎么办了，如果使用where t_modified='2018-07-01'，引擎就会按照上面蓝色箭头的路线快速定位到t_modified=’2018-07-01’需要的结果。

但并不是优化器放弃了使用这个索引，而是放弃了树的搜索功能，优化器可以选择遍历主键索引，也可以选择遍历t_modified索引树，优化器对索引大小比较后发现，索引t_modified更小，遍历这个索引比遍历主键索引来的更快，因此最终还是会选择索引t_modified，只是全索引扫描而已，使用explain分析语句可知：

image.png
可以看到key=t_modified，表示使用了索引t_modified，可以改写SQL为指定日期查询。

mysql> 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> select * from tradelog where tradeid=110717;

上边这个SQL使用了tradeid字段作为条件，并且该字段有索引，通过explain命令查看发现这个SQL会扫描全表：
image.png
我们发现tradeid字段的类型为字段类型为varchar(32)，但是传入的参数却是整型，因此需要做类型转换，那是字符串转整型还是整型转字符串呢？我们可以使用简单的测试来证明这个问题
执行select 10>'9';如果结果为1，表示字符串会转换为整型，如果为0，则表示整型会转换为字符串。
image.png
结果是返回1，表示字符串会转换为整型，在MySQL中，字符串和数字做比较的话，是将字符串转换成数字，select * from tradelog where tradeid=110717这个语句就相当于select * from tradelog where CAST(tradid AS signed int) = 110717;其实底层还是条件字段使用了函数

但是当我们执行如下SQL，还是会走索引，select * from tradelog where id='111'，因为是字符串发生转换，这里是输入参数转换，不涉及字段的类型转换，因此会走索引。

隐式字符编码转换

上边我们已经创建了交易详情表，两张表的字符集一个是utf8mb4，一个是utf8，当我们需要查询id=2的交易的所有操作步骤信息时，我们可以使用SQL：

select td.* from tradelog tl,trade_detail td where td.tradeid=tl.tradeid and tl.id=2

image.png
可以看到tradelog表使用了索引，而trade_detail表没有使用索引，上边的这个执行计划里， 是从tradelog表中取tradeid字段，再去trade_detail表里查询字段，因此我们把tradelog称为驱动表，trade_detail被称为被驱动表，通过tradeid关联。

  • 第一步，根据id所在的tradelog表找到id=2这一行
  • 第二步，从id=2这一行取出tradeid字段的值
  • 第三步，根据tradeid值得到trade_detail表中查找条件匹配的行，explain的结果里面第二行的key=NULL表示的就是这个过程是通过遍历主键索引的方式，一个一个地判断traceid的值是否匹配。

疑惑：为什么trade_detail的tradeid字段上有索引而却不用，答案就是字符集不同，一个是utf8，一个是utf8mb4，由于utf8mb4是utf8的超集，因此utf8会像超集utf8mb4转换，因此上边这个语句在执行的时候，需要将被驱动表（trace_detail）的字段一个个地转换成utf8mb4，再跟tradelog做比较，实际上底层还是使用了函数。

/*CPMVERT函数在这里的意思是把输入的字符串转换成utf8mb4的字符集*/
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

但是如果不是被驱动表发生字符集转换，而是驱动表，就没有问题，比如执行如下SQL

select td.* from tradelog tl,trade_detail td where td.tradeid=tl.tradeid and td.id=2

这个SQL底层是将输入参数做了函数转换，因此会走索引

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

优化方案：

  • 统一字符集
  • 修改SQL，如果数据量过大，或者不能做这个DDL。就只能修改SQL
    • 比如主动将tracelog转成utf8，避免被驱动表上的字符集编码转换。
    • image.png