逻辑相同，性能差异巨大的SQL语句

• 1. 条件字段函数操作
• • 1.1 原理
• 2 隐式类型转换
• • 2.1 类型转换的规则
  • 2.2 为什么又数据类型转换，就要走全索引扫描
• 3 隐式字符编码转换
• • 慢查询结果
  • 1. 转换驱动表和被驱动表
  • 2. 对第一个查询sql的优化

假设有一个交易系统

•  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;
  

• 其中记录了从年初到年末所有的数据。那如果我们查7月这一个月的数据，可能会这样写sql

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

• 因为t_modified上面加了索引，所以这条语句可以走索引，返回结果，但是执行结果却不一样，这条语句查询了很久。

• 但如果将条件改为where t_modified = ‘2020-7-1’，这时候就可以用上索引，结果返回很快。

为什么会造成上述情况？

• t_modified索引示意图，方框下面数字就是month()函数对应的值.

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4JOl5fSq-1654087278668)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220112152438.png)]

• 实际上month()破坏了B+树提供的快速定位功能，因为这个功能来源于同一层通敌节点的有序性。

• month函数是server层的，与innodb无关。 优化器也是server层的，选择索引是优化器的事，与innodb无关。 实际上还是会使用tmodify索引对应的b+树，只不过是需要从头到尾遍历这个树，而不是在树上直接执行快速搜索。 树索引只能加速等值查询和范围查询。对于需要对索引字段执行函数运算的这种场景无能为力。

• 所以优化器决定放弃走树搜索功能。

sql语句

mysql> select * from tradelog where tradeid=110717;

• 交易编号tradid这个字段有索引，但是explain却显示这条语句走了全局扫描。

原因在于tradeid的字段类型是varch(32)，但输入的参数确是整数，所以需要类型转换。

看select “10” > 9的结果

1. 如果规则是"将字符串转成数字"，那么就是做数字比较，结果为1.
2. 如果规则是"将数字转成字符串"，那么就是做字符串比较，结果为0.

结果为：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-raWLwNXI-1654087278669)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220112155257.png)]

在MySQL中，字符串和数字做比较的话，是将字符串转成数字

• 对于隐形转换就要注意字段是字符串类型的。比如name(varchar) select * from table name=0 就可以查询出数据。这样就会造成SQL注入

回到上面的SQL语句：

• 对于优化器来说，这个SQL语句相当于

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

• 即对tradid做了函数操作，优化器放弃走书搜索功能。

假设有另一个表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这样写

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; 

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wMNNcAcF-1654087278669)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220112160353.png)]

1. 第一行显示优化器会先在tradelog 上查到id = 2的行，这个步骤用了主键索引，rows表示只扫描了一行。
2. 第二行key = null，表示没有在trade_detail上的tradeid索引，进行了全表扫描。

这个执行语句是

1. 从tradelog表取出tradeid。
2. 去trade_detail表里查询匹配的trade_id。

所以把tradelog称为驱动表，把trade_detail称为被驱动表，把trade_id称为关联字段。

执行流程:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TnBT5OEi-1654087278670)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220112160909.png)]

1. 根据ID在tradelog找到L2
2. 从L2取出tradeid字段的值
3. 根据tradeid值到trade_detail表中查找条件匹配的行，explain的结果里面第二行的key= null表示的就是，这过程是通过遍历主键索引的方式，一个一个的判断tradeid是否匹配。

为什么trade_detail表中的tradeid字段索引没有生效？

• 两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

为什么字符集不同就用不上索引呢？

• 第三步的SQL

  select * from trade_detail where tradeid=$L2.tradeid.value; 
  

• 其中$L2.tradeid.value的字符集是utf8mb4。

• 字符集utf8mb4是utf8的超集，所以当这两个类型的字符串做比较的时候，MySQL内部的操作时，先把utf8转成utf8mb4字符集，在比较。

• utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

• 因此载执行的时候，需要将被驱动数据表里的字段一个个转成utf8mb5，再和L2做比较。

因此，实际上这个语句等同于下面语法

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

• cnvert()函数，在这里就是把输入的字符串转成utf8mb4字符集，就又对索引字段做函数操作。

连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

”查找trade_detail表里id=4的操作，对应的操作人是谁“

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dOcZAMmm-1654087278670)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220112164259.png)]

• 在上面即查询的SQL类似于

  select operator from tradelog  where traideid =$R4.tradeid.value; 
  

• convert函数是加载输入参数上的，这样就可以用上被驱动表的tradeid索引。