MySQL 笔记整理（18） --为什么这些SQL语句逻辑相同，性能却差异巨大？

笔记记录自林晓斌（丁奇）老师的《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这个字段的索引示意图如下:

　　如果你的查询条件是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执行结果为：

　　这个结果表明：

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

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

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的字符集，避免了被驱动表上字符编码的转换。

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

原文：https://www.cnblogs.com/dogtwo0214/p/10732402.html