欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 笔记整理(18) --为什么这些SQL语句逻辑相同,性能却差异巨大?

程序员文章站 2022-04-19 15:35:02
笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》 (本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除) 18) --为什么这些SQL语句逻辑相同,性能却差异巨大? 本篇我们以三个例子来记录。 案例一:条件字段函数操作 一个交易系统中有这样一个交易记录表,假设现在已经记录了从16年年初到1 ......

笔记记录自林晓斌(丁奇)老师的《mysql实战45讲》

(本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除)

18) --为什么这些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;

  一个交易系统中有这样一个交易记录表,假设现在已经记录了从16年年初到18年年底的全部数据,需要统计发生在所有年份中7月份的交易记录总数,你可能会这么写查询语句:

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

  由于查询条件中的t_modified字段上有索引,你就很放心的执行了,但是实际上执行地很慢。如果你接着这个问题查一查会发现,如果对字段做了函数计算,就用不上索引了,这是mysql的规定。那么,为什么呢?

  我们前面介绍过了,mysql是按照b+树的数据结构来存放索引的,实际上t_modified这个字段的索引示意图如下:

  

MySQL 笔记整理(18) --为什么这些SQL语句逻辑相同,性能却差异巨大?

  如果你的查询条件是where t_modified = '2018-7-1',那么引擎就会按照上面所示的方式快速定位到这条记录,而如果你使用了month函数来计算的话,在这个索引树的第一层引擎就不知道应该怎么去寻找了。

  实际上,b+树提供这个快速定位能力,来源于同一层兄弟节点的有序性。

  因此,对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。需要注意的是,优化器并不是要放弃使用这个索引。在这个例子中,放弃了树的搜索功能,优化器可以遍历主键索引,也可以遍历索引t_modified。优化器对比索引大小之后,发现t_modified更小,遍历这个索引更快,因此最终还是会选择索引t_modified。

  上面这个例子对索引字段使用了函数操作破坏所引值的有序性,因此查询变慢,那么我们应该怎么优化一下呢?问题的关键是使用上索引,我们可以把语句改成基于字段本身的范围查询,例如:

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');

  当然,如果你的系统上线更早,或者后面又插入了之后年份的数据,你就需要把其他年份补齐了。

  month()函数破坏了有序性因此导致查询变慢,但实际上,mysql的优化器确实有“偷懒”行为。例如

select * from tradlog where id+1 = 10000; 

  虽然没有改变有序性,但是优化器还是不能利用索引快速定位到id=9999这一行。你需要手动改动查询条件为

id = 10000-1才行。

案例二:隐式类型转换

  我们还用刚才那个交易记录表举例,来看看这条sql语句:

mysql> select * from tradelog where tradeid=110717;

  交易编号tradeid这个字段本来就有索引,但是explain的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid的字段类型是varchar(32),而输入的参数确实整型,索引需要做类型转换。那么现在这里就有两个问题了:

  1. 数据类型的转换规则是什么?
  2. 为什么有数据类型转换,就需要走全表索引扫描?

  先来看第一个问题,你可能会说,数据库里类型这么多,这种数据类型规则更多,我记不住怎么办呢?有一个简单地方法,看看select "10" > 9的结果:

  1. 如果规则是“将字符串转成数字”,那么就是数字比较,结果应该是1;
  2. 如果规则是“将数字转成字符串”,那么久做字符串比较,结果应该是0;

  实际上上面这个查询返回的结果是1,即“将字符串转成数字”。这时,我们再来看看案例二刚开始的查询语句

mysql> select * from tradelog where tradeid=110717;

  对于优化器来说,这个语句就相当于

mysql> select * from tradelog where cast(tradeid 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');

  此时如果需要查询id=2(tradeid = 'aaaaaaab')的交易的所有操作步骤信息,sql语句可以这么写:

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

  这条语句的explain执行结果为:

MySQL 笔记整理(18) --为什么这些SQL语句逻辑相同,性能却差异巨大?

  这个结果表明:

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

  这个执行结果里,是从tradelog表中取tradeid字段,再去trade_detail表里查询匹配字段,因此,我们把tradelog称为驱动表,把trade_detail称为被驱动表,把tradeid称为关联字段。接下来我们来看看explain结果表示的执行流程:

MySQL 笔记整理(18) --为什么这些SQL语句逻辑相同,性能却差异巨大?

  1. 根据id在tradelog中找到l2这一行记录。
  2. 从l2中取出tradeid字段的值。
  3. 根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=null表示的就是,这个过程通过遍历主键索引的方式,一个一个地判断tradeid的值是否匹配。

  到这里你会发现,第三步中与我们期望的执行结果不符,因为trade_detail字段上是有索引的,我们本来是希望通过使用tradeid索引来快速定位的。这时候如果你去问dba同学,他可能会告诉你,因为这两个表的字符集不同,一个是utf8,另一个是utf8mb4,所以做表连接查询的时候用不上关联字段的索引。但是如果你再追问一下,为什么字符集不同就用不上索引了呢?

  如果说刚才的执行结果问题是出在第三步,那么如果单独把这一步改成sql语句的话,那就是:

mysql>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; 

  这就触发了我们在案例一中的那种情况:对索引字段做函数操作,优化器会放弃走树搜索功能。

  到这里,你应该明白了,字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。

  那么这个语句我们应该怎么去优化呢,一般有两种作法:

  1. 直接把trade_detail的表的字符集也改成utf8mb4,这样就没有字符集转换的问题了。
  2. 如果业务上不允许进行ddl的话,那就只能修改sql语句了,你可以尝试这么写:
    mysql> select d.* from tradelog l , trade_detail d where d.tradeid=convert(l.tradeid using utf8) and l.id=2; 
    

      

  这里我们主动改变了l.tradeid的字符集,避免了被驱动表上字符编码的转换。

  今天这个三个例子,其实是在说同一件事。即:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。