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

笼中人 發表於 2025-7-20 18:51:00

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

在MySQL中，有很多看上去逻辑相同，但性能差异巨大的SQL语句。对这些语句使用不当的话，就会不经意导致整个数据库压力变大。本文选择了三个这样的案例。
<h3 id="案例一条件字段函数操作">案例一：条件字段函数操作</h3>
假设现在维护了一个交易系统，其中交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段。建表语句如下：
<pre><code class="language-sql">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;
</code></pre>
目前的需求是，统计发生在所有年份中7月份的交易记录总数。可能会这样写SQL语句：
<pre><code class="language-sql">select count(*) from tradelog where month(t_modified)=7;
</code></pre>
但这可能需要执行很久，因为如果对字段做了函数计算，就用不上索引了。为什么呢？
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250718100200405-2138020560.png" width="40%"></div>
上面是t_modified索引示意图。如果SQL语句的条件是<code>where t_modified='2018-7-1'</code>，引擎会按照绿色箭头的路线快速定位到结果。但如果要计算month()函数，传入7这个数字，并不知道如何在树中查找。也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
但优化器并不是要放弃使用这个索引。在该案例中，优化器可以选择遍历主键索引，也可以选择遍历t_modified索引，对比索引大小后会选择遍历t_modified索引。
用explain命令查看执行结果：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250718100230059-688802373.png" width="90%"></div>
<ul>
<li>
<code>key=t_modified</code>，表示用上了索引；
</li>
<li>
<code>rows=100335</code>，已知测试数据共10万行，表示这条语句扫描了整个索引的所有值。
</li>
</ul>
为了让优化器能按照预期用上t_modified索引的快速定位能力，需要把SQL语句改为基于字段本身的范围查询：
<pre><code class="language-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');
</code></pre>
还有其他的情况，索引可能也会失效，比如：
<ul>
<li>
<code>select * from tradelog where id+1=10000</code>，失效；
</li>
<li>
<code>select * from tradelog where id=10000-1</code>，可以。
</li>
</ul>
<h3 id="案例二隐式类型转换">案例二：隐式类型转换</h3>
有一条SQL语句：
<pre><code class="language-sql">select * from tradelog where tradeid=110717;
</code></pre>
explain结果显示这条语句需要走全表扫描，这是因为tradeid字段类型是varchar(32)，而输入的参数是整型，所以需要做类型转换。
那么，数据类型转换规则是什么？为什么有数据类型转换，就需要走全索引扫描？
来看一个简单的比较<code>select "10">9</code>：
<ul>
<li>
如果规则是将字符串转成数字，那么结果应该是1；
</li>
<li>
如果规则是将数字转成字符串，那么结果应该是0。
</li>
</ul>
验证结果：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250718100313289-556874183.png" width="30%"></div>
所以在MySQL中，字符串和数字做比较的话，规则是将字符串转成数字。
这时再看案例给出的语句，能发现对于优化器来说，语句等同于：
<pre><code class="language-sql">select * from tradelog where CAST(tradid AS signed int) = 110717;
</code></pre>
而根据案例一，这条语句会索引失效。
<h3 id="案例三隐式字符编码转换">案例三：隐式字符编码转换</h3>
假设系统里还有另外一个表trade_detail用于记录交易的操作细节。为了便于分析，在表中插入一些数据：
<pre><code class="language-sql">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');
</code></pre>
这时，有一条SQL语句：
<pre><code class="language-sql">select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
</code></pre>
其explain结果：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250718100354093-87620615.png" width="90%"></div>
第一行说明优化器会在tradelog找到<code>id=2</code>的行，这个步骤用上了主键索引；第二行<code>key=null</code>，说明没有用上tradeid索引，进行了全表扫描。
在该执行计划中，是从tradelog表取tradeid字段，再去trade_detail表里查询匹配字段，因此把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。
该语句的执行流程：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250718100416838-1976047191.png" width="40%"></div>
<ul>
<li>
首先根据id在tradelog表找到L2这一行；
</li>
<li>
从L2取出tradeid字段的值；
</li>
<li>
根据tradeid到trade_detail表查找条件匹配的行，这个过程是通过遍历主键索引的方式。
</li>
</ul>
我们本来是希望在trade_detail表里用索引，但最终并没有。
究其原因，通常是因为两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询时用不上关联字段的索引。那么为什么字符集不同就用不上索引呢？
如果把流程的最后一步单独改成SQL语句，那就是：
<pre><code class="language-sql">select * from trade_detail where tradeid=$L2.tradeid.value;
</code></pre>
其中，$L2.tradeid.value的字符集是utf8mb4。由于utf8mb4是utf8的超集，因此这两个类型的字符串作比较时，MySQL内部会先把utf8字符串转换成utf8mb4，再做比较，等同于：
<pre><code class="language-sql">select * from trade_detailwhere CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
</code></pre>
CONVERT在这里表示字符集转换。而这条语句就相当于案例一的堆索引字段做函数操作，优化器会放弃走树搜索。
此时可以看出，字符集不同只是条件，连接过程要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。
 
作为对比验证，换一下驱动表和被驱动表：
<pre><code class="language-sql">select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
</code></pre>
其explain结果：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250718100455095-289043476.png" width="90%"></div>
从结果可以看出，当trade_detail变成驱动表，此时用上了被驱动表tradelog里的索引。
假设驱动表trade_detail里<code>id=4</code>的行记为R4，如果我们类似分析该语句的最后一步，其类似：
<pre><code class="language-sql">select operator from tradelogwhere traideid =$R4.tradeid.value;
</code></pre>
又由于有字符串转换，进一步的，该语句可以写为：
<pre><code class="language-sql">select operator from tradelogwhere traideid =CONVERT($R4.tradeid.value USING utf8mb4);
</code></pre>
此时可以发现，函数是加在输入参数上的，这样是可以用上被驱动表的tradeid索引的。
 
理解了原理，如果想要优化语句：
<pre><code class="language-sql">select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
</code></pre>
有两种做法：
<ul>
<li>
把trade_detail表上的tradeid字段的字符集也改成utf8mb4；
</li>
<li>
如果不支持修改字符集，可以修改SQL语句为
<pre><code class="language-sql">select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
</code></pre>
</li>
</ul>
最后总结：三个案例其实都想说明同一件事，就是对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器会放弃走树搜索。 
来源：https://www.cnblogs.com/san-mu/p/18990993

頁: [1]

琼殿技术论坛's Archiver

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