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

SQL语句使用不当，相同逻辑会导致不同性能

1. 条件字段函数操作

对字段做了函数计算，就用不上索引了，这是MySQL的规定。

1.1 原理

假设有一个交易系统
- ```
 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+树，只不过是需要从头到尾遍历这个树，而不是在树上直接执行快速搜索。树索引只能加速等值查询和范围查询。对于需要对索引字段执行函数运算的这种场景无能为力。
- 所以优化器决定放弃走树搜索功能。

2 隐式类型转换

sql语句
```
mysql> select * from tradelog where tradeid=110717;
```
- 交易编号tradid这个字段有索引，但是explain却显示这条语句走了全局扫描。
原因在于tradeid的字段类型是varch(32)，但输入的参数确是整数，所以需要类型转换。

2.1 类型转换的规则

看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注入

2.2 为什么又数据类型转换，就要走全索引扫描

回到上面的SQL语句：
- 对于优化器来说，这个SQL语句相当于
```
mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;
```
- 即对tradid做了函数操作，优化器放弃走书搜索功能。

3 隐式字符编码转换

假设有另一个表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; /*语句Q1*/

慢查询结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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字符集，就又对索引字段做函数操作。
连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

1. 转换驱动表和被驱动表

”查找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索引。

2. 对第一个查询sql的优化

把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4，这样就没有字符集转换的问题了。
```
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
```
如果数据量比较大，或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。
```
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
```