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

HigginCui
116 阅读4分钟

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

有一个交易表tradelog，包含交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段。

mysql> CREATE TABLE `tradelog` (
    `id` int(11) NOT NULL,
    `tradeid` varchar(32) DEFAULT NULL comment "交易流水号",
    `operator` int(11) DEFAULT NULL comment "交易员id",
    `t_modified` datetime DEFAULT NULL comment "交易时间",
    PRIMARY KEY (`id`),
    KEY `tradeid` (`tradeid`),
    KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这个表记录了2016-2018年的所有数据，现在要统计所有年份7月份的总交易记录数。

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

虽然t_modified加了索引，但是这个语句执行时间很久。 原因：

  • 如果是where t_modified ="2018-7-1"的话，引擎会走图中绿色的箭头，快速定位到t_modified="2017-7-1"的记录，因为B+树同一层兄弟节点是有序的。
  • 使用month()函数，会破坏索引值的有序性，所以优化器可能会放弃走搜索树功能。
  • 优化器可能会选择走主键索引或者普通索引t_modified，优化器对比索引大小后认为索引t_modified更小，最终还是选择遍历这个索引。

image.png

explain命令查看执行计划，(测试时总共加了10W行数据)： image.png

  • key的值是t_modified说明使用了t_modified这个索引。
  • rows的值是10335,说明扫描了整个索引的所有值
  • Extra字段的Using index说明使用了覆盖索引

总结：t_midified字段加上了month()函数操作，直接导致了全索引扫描。优化如下：

--改成基本字段本身的范围查询
 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');

案例二：隐式类型转换

查看这个SQL：

-- `tradeid` varchar(32) 为字符格式
select * from tradelog where tradeid=110717;

结果：tradeid字段有索引，预期走索引查询，但是实际是全表查询。 原因：类型转换规则，字符串和数字比较时，将表中字符串转换成数字，实际执行语句如下，导致对索引字段做函数操作，进而导致优化器放弃走树搜索功能。

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

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

系统中新增2个表：

  1. 表trade_detail:记录交易的操作细节
  2. 表tradelog:交易，两个表基于tradeid来关联
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;

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;

要查询id=2的交易的所有操作步骤信息，SQL如下：

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

执行计划如下：

image.png

  • 第一行：使用了主键索引key=PRIMARY，rows=1，表示主键索引只扫描了1行
  • 第二行：key=NULL，表示没有用上trade_detail上的tradeid索引，而是用了全表扫描！

语句的执行流程：

image.png

  1. 根据id在tradelog表找到L2这一行；
  2. 从L2中取出tradeid字段的值；
  3. 根据tradeid值到trade_detail表中查找条件匹配的行。问题就在这一步，预期走索引tradeid，但是实际是通过遍历主键索引的方式一个个判断tradeid是否匹配。

分析：trade_detail的字符集是utf8，tradelog的字符集是utf8mb4，通过tradelog的tradeid去关联查询trade_detail的tradeid，而字符集utf8mb4是utf8的超集，当这两个类型的字段在做比较时，MySQL内部会将utf8字符串转成utf8mb4字符集，在做比较，导致优化器放弃走搜索树功能。

上述步骤三的实际SQL如下：

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

convert()函数：把输入的字符串转成utfmb4字符集。

结论：tradelog为驱动表，trade_detail为被驱动表，tradeid为关联字段，驱动表和被驱动表的字符集不同，连接过程中要求在被驱动表的索引字段加上函数转换操作，直接导致对被驱动表做全表扫描。

对比

将tradelog改为被驱动表，trade_detail改为驱动表

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

执行计划如下： image.png 改变驱动和被驱动关系后，发现第二行key=tradeid，使用了索引查询，扫描行数为1。

实际执行逻辑如下：（tradelog的tradeid本身就是utf8mb4格式。）

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

优化手段

  1. 直接将trade_detail的字符集也改成utf8mb4
  2. 如果数据量过大，可能业务上不能直接执行这个DDL，可以通过修改SQL的手段：
-- 主动把l.tradeid转成utf8，避免被驱动表上的字符编码转换。
select d.* from tradelog l ,trade_detail d where d.tradeid = CONVERT(l.tradeid USING utf8) and l.id = 2;

小结

三个例子，主要核心其实是一件事：对索引函数操作，可能会破坏索引值的有序性，导致优化器决定放弃走搜索树功能。

avatar
文章
阅读
粉丝