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

技术分享 8个月前 (07-21) 0 999+

关注

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

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

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语句改为基于字段本身的范围查询：

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

还有其他的情况，索引可能也会失效，比如：

• select * from tradelog where id+1=10000，失效；

• select * from tradelog where id=10000-1，可以。

案例二：隐式类型转换

有一条SQL语句：

select * from tradelog where tradeid=110717; 

explain结果显示这条语句需要走全表扫描，这是因为tradeid字段类型是varchar(32)，而输入的参数是整型，所以需要做类型转换。

那么，数据类型转换规则是什么？为什么有数据类型转换，就需要走全索引扫描？

来看一个简单的比较select "10">9：

• 如果规则是将字符串转成数字，那么结果应该是1；

• 如果规则是将数字转成字符串，那么结果应该是0。

验证结果：

所以在MySQL中，字符串和数字做比较的话，规则是将字符串转成数字。

这时再看案例给出的语句，能发现对于优化器来说，语句等同于：

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

而根据案例一，这条语句会索引失效。

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

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

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

这时，有一条SQL语句：

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

其explain结果：