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

---

案例一：条件字段函数操作

• 假设，现在已经记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂，你的 SQL 语句可能会这么写：

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

• 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
• 由于在 t_modified 字段加了 month() 函数操作，导致了全索引扫描。为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法，优化器就能按照我们预期的，用上 t_modified 索引的快速定位能力了。

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

• 优化器在个问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行

---

案例二：隐式类型转换

• 来看另一个经常让程序员掉坑里的例子

select * from tradelog where tradeid=110717;

• 交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
• 对于优化器来说，这个语句相当于：

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

• 在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

---

案例三：隐式字符编码转换

• 如果要查询 id=2 的交易的所有操作步骤信息，SQL 语句 explain 结果如下，其中 tradelog 用的是 utf8mb4 字符集，而 trade_detail 用的是 utf8 字符集
  
• 也就是说，实际上这个语句等同于下面这个写法：

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

• 这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。

---

笔记来源于《极客时间：MySQL实战45讲：为什么这些 SQL 语句逻辑相同，性能却差异巨大？》