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

最新推荐文章于 2022-09-20 11:53:07 发布

sxlxwyr

最新推荐文章于 2022-09-20 11:53:07 发布

阅读量171

点赞数

分类专栏： MySQL

原文链接：https://time.geekbang.org/column/article/74059

版权

MySQL 专栏收录该内容

48 篇文章 3 订阅

订阅专栏

对一些语句使用不当的话，就会不经意间导致整个数据库的压力变大。

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

假设一个交易系统，包含交易流水号（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月份的交易记录总数，你可能会这么写：

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

由于t_modified字段上有索引，所以就很放心这个语句，但是结果却发现执行了特别久才返回了结果。

这是因为如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

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

用explain命令：

key="t_modified"表示使用了t_modified这个索引。Extra字段的Using index表示的是使用了覆盖索引。

可以使用下面的方法，能够用上索引的快速定位能力：

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; 所以需要改成where id = 10000 -1才可以。

案例二：隐式类型转换

SQL语句：

select * from tradelog where tradeid=110717;

tradeid这个字段上本来就有索引，但是explain的结构显示没有使用索引。因为tradeid的字段类型是varchar(32),而输入的参数类型却是整型，所以需要做类型转换。

MySQL的转换规则：字符串和数字做比较的话，是将字符串转换成数字。

所以对于优化器来说，这个语句相当于：

select * from tradelog where cast(tradeid as signed int) = 110717;

这也就变成案例1的情况了，优化器放弃走树搜索功能。

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

假设系统里还有另外一个表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');

这时候要查询id=2的交易的所有操作步骤信息，SQL语句可以这么写：

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

explain结果：

分析：

第一行显示优化器会在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描了一行；
第二行key=null,表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。

这个执行计划里，是从tradelog表中取tradeid字段，再去trade_detail表里查询匹配字段。因此，我们把tradelog称为驱动表，trade_detail称为被驱动表，tradeid称为关联字段。

执行流程：

图中：

第一步，根据id在tradelog里找到L2这一行；
第二步，从L2中取出tradeid字段的值；
第三步，根据tradeid值到trade_detail表中查询匹配的行。explain结果里面的第二行的key=null，表示的就是这个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配。

这是因为：这两个表的字符集不同，一个是utf8,一个是utf8mb64，所以做表连接查询的时候用不上关联字段的索引。

单独把第三步拿出来，那就是：

select * from trade_detail where tradeid=$L2.tradeid.value;

其中，$L2.tradeid.value的字符集是utf8mb64。

字符集utf8mb64是utf8的超集。类似在程序设计语言里面做自动类型转换的时候，为了避免数据载转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

因此，在执行上面这个语句的时候，需要将被驱动数据表的字段一个个地转换成utf8mb64，再跟L2做比较。

也就是说，实际上这个语句等同于下面这个写法：

select * from trade_detail where convert(tradeid using utf8mb64)=$L2.tradeid.value;

convert()函数，在这里的意思是把输入的字符转转换成utf8mb64字符集。这又是案例一的情况了。

字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数，是直接导致被驱动表做全表扫描的原因。

可以考虑类似如下方式的优化：

比较常见的做法是，把trade_detail表上的tradeid字段的字符集也改成utf8mb64 ：alter table trade_detail modify tradeid varchar(32) character set utf8mb64 default null;
如果能修改字段字符集的话，是最好的。如果数据量较大，或者业务上暂时不能做这个DDL的话，只能修改SQL语句了，如下：select d.* from tradelog l, trade_detail d where d.tradeid=convert(l.tradeid using utf8) and l.id=2;

explain结果：