影响MySQL查询性能的案例

当我们需要查询一条交易记录(trade_log) 中的全部交易详情(trade_detail) 时，可能会使用如下SQL

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

上面第一行是对 trade_log 的 id = 2 的这一条记录执行的查询，使用了主键索引，扫描行数 1 ；但是第二条没有使用 trade_detail 上的 tradeid索引，是不是感到有些奇怪。

在上面的执行计划里面，先是从 trade_log 里面去查询 id=2 的记录，然后再去匹配 trade_detail 。这里面 trade_log 称为 驱动表，trade_detail 称为 被驱动表，其执行流程如下所示：

那么上面第二条执行计划为什么没有走索引呢，仔细看你会发现上面 2 张表创建时所使用的字符集编码不同，一个是 utf8 一个是 utf8mb4 。utfutf8mb4 是 utf8 字符集的超集，当我们将 两张表的字段进行比较时，utf8 会转换为utf8mb4 （避免精度丢失）。

上图中的第 3步可以认为是执行如下操作（$L2.tradeid.value 是 utf8mb4 的字符值）：

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

隐式转换后的执行SQL 如下：

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

由此看来，执行的过程中对 trade_detail 的查询字段 tradeid 使用了函数，因此不走索引。但是当我们反过来查询时，也就是从一条 trade_detail 去关联对应的 trade_log 时，会是什么情况呢？

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

由上图可以看出，第二次查询使用到了 tradelog的 tradeid 索引了。当第一个执行计划找到 trade_detail 中 id=4 的记录后（R4），再去tradelog 中关联对应的记录时，执行的SQL 如下：

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

此时 等号右边的 value 值需要做隐式转换，并没有在索引字段上做函数操作，如下所示：

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

解决方案

对于字符集不同造成的索引不可用，可以使用如下 2 中方式去解决。

• 修改表的字符集编码。

mysql> alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null; 

• 手工字符编码转换。

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

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!