【学习计划】《MySQL实战45讲》18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

---

案例说明

1. 条件字段函数操作

示例代码：

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

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。需要注意的是，优化器并不是要放弃使用这个索引。放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。由于在 t_modified 字段加了 month() 函数操作，导致了全索引扫描。

优化后代码：

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');

2. 隐式类型转换

示例代码：

mysql> select * from tradelog where tradeid=110717;

相当于

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

查看数据类型转换规则的简单方法。
看 select “10” > 9 的结果：

• 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
• 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

3. 隐式字符编码转换

示例代码：

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

相当于

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

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

优化方法：

• 比较常见的优化方法是，把 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;