MySQL 18 为什么这些SQL语句逻辑相同，性能却差异巨大？

在MySQL中，有很多看上去逻辑相同，但性能差异巨大的SQL语句。对这些语句使用不当的话，就会不经意导致整个数据库压力变大。本文选择了三个这样的案例。
案例一：条件字段函数操作

假设现在维护了一个交易系统，其中交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段。建表语句如下：

1. mysql> CREATE TABLE `tradelog` (
2.   `id` int(11) NOT NULL,
3.   `tradeid` varchar(32) DEFAULT NULL,
4.   `operator` int(11) DEFAULT NULL,
5.   `t_modified` datetime DEFAULT NULL,
6.   PRIMARY KEY (`id`),
7.   KEY `tradeid` (`tradeid`),
8.   KEY `t_modified` (`t_modified`)
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

目前的需求是，统计发生在所有年份中7月份的交易记录总数。可能会这样写SQL语句：

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

复制代码

但这可能需要执行很久，因为如果对字段做了函数计算，就用不上索引了。为什么呢？

上面是t_modified索引示意图。如果SQL语句的条件是where t_modified='2018-7-1'，引擎会按照绿色箭头的路线快速定位到结果。但如果要计算month()函数，传入7这个数字，并不知道如何在树中查找。也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
但优化器并不是要放弃使用这个索引。在该案例中，优化器可以选择遍历主键索引，也可以选择遍历t_modified索引，对比索引大小后会选择遍历t_modified索引。
用explain命令查看执行结果：

• key=t_modified，表示用上了索引；
  
• rows=100335，已知测试数据共10万行，表示这条语句扫描了整个索引的所有值。
  

为了让优化器能按照预期用上t_modified索引的快速定位能力，需要把SQL语句改为基于字段本身的范围查询：

1. mysql> select count(*) from tradelog where
2.     -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
3.     -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
4.     -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

复制代码

而根据案例一，这条语句会索引失效。
案例三：隐式字符编码转换

假设系统里还有另外一个表trade_detail用于记录交易的操作细节。为了便于分析，在表中插入一些数据：

1. select * from tradelog where tradeid=110717;

复制代码

这时，有一条SQL语句：

1. select * from tradelog where CAST(tradid AS signed int) = 110717;

复制代码

其explain结果：

第一行说明优化器会在tradelog找到id=2的行，这个步骤用上了主键索引；第二行key=null，说明没有用上tradeid索引，进行了全表扫描。
在该执行计划中，是从tradelog表取tradeid字段，再去trade_detail表里查询匹配字段，因此把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。
该语句的执行流程：

• 首先根据id在tradelog表找到L2这一行；
  
• 从L2取出tradeid字段的值；
  
• 根据tradeid到trade_detail表查找条件匹配的行，这个过程是通过遍历主键索引的方式。
  

我们本来是希望在trade_detail表里用索引，但最终并没有。
究其原因，通常是因为两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询时用不上关联字段的索引。那么为什么字符集不同就用不上索引呢？
如果把流程的最后一步单独改成SQL语句，那就是：

1. mysql> CREATE TABLE `trade_detail` (
2.   `id` int(11) NOT NULL,
3.   `tradeid` varchar(32) DEFAULT NULL,
4.   `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
5.   `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
6.   PRIMARY KEY (`id`),
7.   KEY `tradeid` (`tradeid`)
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10. insert into tradelog values(1, 'aaaaaaaa', 1000, now());
11. insert into tradelog values(2, 'aaaaaaab', 1000, now());
12. insert into tradelog values(3, 'aaaaaaac', 1000, now());
14. insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
15. insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
16. insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
17. insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
18. insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
19. insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
20. insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
21. insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
22. insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
23. insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
24. insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

复制代码

其中，$L2.tradeid.value的字符集是utf8mb4。由于utf8mb4是utf8的超集，因此这两个类型的字符串作比较时，MySQL内部会先把utf8字符串转换成utf8mb4，再做比较，等同于：

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

复制代码

CONVERT在这里表示字符集转换。而这条语句就相当于案例一的堆索引字段做函数操作，优化器会放弃走树搜索。
此时可以看出，字符集不同只是条件，连接过程要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

作为对比验证，换一下驱动表和被驱动表：

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

复制代码

其explain结果：

从结果可以看出，当trade_detail变成驱动表，此时用上了被驱动表tradelog里的索引。
假设驱动表trade_detail里id=4的行记为R4，如果我们类似分析该语句的最后一步，其类似：

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

复制代码

又由于有字符串转换，进一步的，该语句可以写为：

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

复制代码

此时可以发现，函数是加在输入参数上的，这样是可以用上被驱动表的tradeid索引的。

理解了原理，如果想要优化语句：

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

复制代码

有两种做法：

• 把trade_detail表上的tradeid字段的字符集也改成utf8mb4；
  
• 如果不支持修改字符集，可以修改SQL语句为
  1. select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

  复制代码

最后总结：三个案例其实都想说明同一件事，就是对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器会放弃走树搜索。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！