MySQL 笔记整理（18） --为什么这些SQL语句逻辑相同，性能却差异巨大？
笔记记录自林晓斌（丁奇）老师的《MySQL实战45讲》
（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除）
18） --为什么这些SQL语句逻辑相同，性能却差异巨大？
本篇我们以三个例子来记录。
案例一：条件字段函数操作
1
2
3
4
5
6
7
8
9
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月份的交易记录总数，你可能会这么写查询语句：
1
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。
上面这个例子对索引字段使用了函数操作破坏所引值的有序性，因此查询变慢，那么我们应该怎么优化一下呢？问题的关键是使用上索引，我们可以把语句改成基于字段本身的范围查询，例如：
1
2
3
4
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的优化器确实有“偷懒”行为。例如
1
select * from tradlog where id+1 = 10000;
虽然没有改变有序性，但是优化器还是不能利用索引快速定位到id=9999这一行。你需要手动改动查询条件为
id = 10000-1才行。
案例二：隐式类型转换
我们还用刚才那个交易记录表举例，来看看这条SQL语句：
1
mysql> select * from tradelog where tradeid=110717;
交易编号tradeid这个字段本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid的字段类型是varchar(32),而输入的参数确实整型，索引需要做类型转换。那么现在这里就有两个问题了：
数据类型的转换规则是什么？
为什么有数据类型转换，就需要走全表索引扫描？
先来看第一个问题，你可能会说，数据库里类型这么多，这种数据类型规则更多，我记不住怎么办呢？有一个简单地方法，看看select "10" > 9的结果：
如果规则是“将字符串转成数字”，那么就是数字比较，结果应该是1；
如果规则是“将数字转成字符串”，那么久做字符串比较，结果应该是0；
实际上上面这个查询返回的结果是1，即“将字符串转成数字”。这时，我们再来看看案例二刚开始的查询语句
1
mysql> select * from tradelog where tradeid=110717;
对于优化器来说，这个语句就相当于
1
mysql> select * from tradelog where CAST(tradeid AS signed int) = 110717;
因此优化器放弃了走树搜索的功能。
案例三: 隐式字符编码转换
假设系统里还有另一个表trade_detail用于记录交易的操作细节。为了方便量化分析和复现，我们准备一些数据，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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语句可以这么写：
1
mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
这条语句的explain执行结果为：
这个结果表明：
第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描了1行。
第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。
这个执行结果里，是从tradelog表中取tradeid字段，再去trade_detail表里查询匹配字段，因此，我们把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。接下来我们来看看explain结果表示的执行流程：
根据id在tradelog中找到L2这一行记录。
从L2中取出tradeid字段的值。
根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是，这个过程通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配。
到这里你会发现，第三步中与我们期望的执行结果不符，因为trade_detail字段上是有索引的，我们本来是希望通过使用tradeid索引来快速定位的。这时候如果你去问DBA同学，他可能会告诉你，因为这两个表的字符集不同，一个是utf8，另一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。但是如果你再追问一下，为什么字符集不同就用不上索引了呢？
如果说刚才的执行结果问题是出在第三步，那么如果单独把这一步改成SQL语句的话，那就是：
1
mysql>select * from trade_detail where tradeid = $L2.tradeid.value;
其中，$L2.tradeid.value的字符集是utf8mb4。
参照前面的例子，你肯定想到了，字符集utf8mb4是utf8的超集，所以当这两个类型的字符串是在做比较的时候，MySQL内部的操作是，先把utf8转化成utf8mb4字符集，再做比较。也就是说，实际上这个语句等同于下面这个写法：
1
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
这就触发了我们在案例一中的那种情况：对索引字段做函数操作，优化器会放弃走树搜索功能。
到这里，你应该明白了，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。
那么这个语句我们应该怎么去优化呢，一般有两种作法：
直接把trade_detail的表的字符集也改成utf8mb4,这样就没有字符集转换的问题了。
如果业务上不允许进行DDL的话，那就只能修改SQL语句了，你可以尝试这么写：
1
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
