在 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 语句可能会这么写:
mysql> select count(*) from tradelog where month(t_modified)=7;
由于 t_modified 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。
原因是如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定。
现在你已经学过了 InnoDB 的索引结构了,可以再追问一句为什么?为什么条件是 where t_modified='2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?
下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。
如果你的 SQL 语句条件用的是 where t_modified='2018-7-1’的话,引擎就会按照上面绿色箭头的路线,快速定位到 t_modified='2018-7-1’需要的结果。
实际上,B+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。
但是,如果计算 month() 函数的话,你会看到传入 7 的时候,在树的第一层就不知道该怎么办了。
也就是说,对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
需要注意的是,优化器并不是要放弃使用这个索引。
在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。
接下来,我们使用 explain 命令,查看一下这条 SQL 语句的执行结果。
key="t_modified"表示的是,使用了 t_modified 这个索引;我在测试表数据中插入了 10 万行数据,rows=100335,说明这条语句扫描了整个索引的所有值;Extra 字段的 Using index,表示的是使用了覆盖索引。
也就是说,由于在 t_modified 字段加了 month() 函数操作,导致了全索引扫描。为了能够用上索引的快速定位能力,我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法,优化器就能按照我们预期的,用上 t_modified 索引的快速定位能力了。
mysql>