实战篇---18. 为什么这些SQL语句逻辑相同，性能却差异巨大？

在 MySQL 中，有很多看上去逻辑相同，但性能却差异巨大的 SQL 语句。对这些语句使用不当的话，就会不经意间导致整个数据库的压力变大。本文挑选了三个这样的案例和你分享。希望再遇到相似的问题时，你可以做到举一反三、快速解决问题。

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

假设你现在维护了一个交易系统，其中交易记录表 tradelog 包含交易流水号（ tradeid ）、交易员id （ operator ）、交易时间（ t_modified ）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

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

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

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

由于 t_modified 字段上有索引，于是你就很放心地在生产库中执行了这条语句，但却发现执行了特别久，才返回了结果。如果你问 DBA 同事为什么会出现这样的情况，他大概会告诉你：如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。

现在你已经学过了 InnoDB 的索引结构了，可以再追问一句为什么？为什么条件是 where t_modified='2018-7-1’ 的时候可以用上索引，而改成 where month(t_modified)=7 的时候就不行了？

下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。

图1 t_modified 索引示意图

如果你的 SQL 语句条件用的是 where t_modified='2018-7-1’ 的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified='2018-7-1’ 需要的结果。

实际上， B+ 树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。但是，如果计算 month() 函数的话，你会看到传入 7 的时候，在树的第一层就不知道该怎么办了。也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引t_modified ，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified 。

接下来，我们使用 explain 命令，查看一下这条 SQL 语句的执行结果。

图2 explain 结果

key=”t_modified” 表示的是，使用了 t_modified 这个索引；我在测试表数据中插入了 10 万行数据， rows=100335 ，说明这条语句扫描了整个索引的所有值； Extra 字段的 Using index ，表示的是使用了覆盖索引。