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

2025-07-21
福建
本文字数：3069 字
阅读完需：约 10 分钟

在 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 结果：

第一行说明优化器会在 tradelog 找到id=2的行，这个步骤用上了主键索引；第二行key=null，说明没有用上 tradeid 索引，进行了全表扫描。

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

该语句的执行流程：

首先根据 id 在 tradelog 表找到 L2 这一行；
从 L2 取出 tradeid 字段的值；
根据 tradeid 到 trade_detail 表查找条件匹配的行，这个过程是通过遍历主键索引的方式。

我们本来是希望在 trade_detail 表里用索引，但最终并没有。

究其原因，通常是因为两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询时用不上关联字段的索引。那么为什么字符集不同就用不上索引呢？

如果把流程的最后一步单独改成 SQL 语句，那就是：

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

复制代码

其中，$L2.tradeid.value 的字符集是 utf8mb4。由于 utf8mb4 是 utf8 的超集，因此这两个类型的字符串作比较时，MySQL 内部会先把 utf8 字符串转换成 utf8mb4，再做比较，等同于：

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

复制代码

CONVERT 在这里表示字符集转换。而这条语句就相当于案例一的堆索引字段做函数操作，优化器会放弃走树搜索。

此时可以看出，字符集不同只是条件，连接过程要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

作为对比验证，换一下驱动表和被驱动表：

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

复制代码

其 explain 结果：

从结果可以看出，当 trade_detail 变成驱动表，此时用上了被驱动表 tradelog 里的索引。

假设驱动表 trade_detail 里id=4的行记为 R4，如果我们类似分析该语句的最后一步，其类似：

select operator from tradelog  where traideid =$R4.tradeid.value;

复制代码

又由于有字符串转换，进一步的，该语句可以写为：

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

复制代码

此时可以发现，函数是加在输入参数上的，这样是可以用上被驱动表的 tradeid 索引的。

理解了原理，如果想要优化语句：

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

复制代码

有两种做法：

把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4；
如果不支持修改字符集，可以修改 SQL 语句为

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

复制代码

最后总结：三个案例其实都想说明同一件事，就是对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器会放弃走树搜索。

文章转载自：叁沐
原文链接：https://www.cnblogs.com/san-mu/p/18990993
体验地址：http://www.jnpfsoft.com/?from=001YH

发布于: 刚刚阅读数: 3

电子尖叫食人鱼

关注

还未添加个人签名 2025-04-01 加入

还未添加个人简介

发布

暂无评论

创作场景

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

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

案例二：隐式类型转换

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

电子尖叫食人鱼

评论