转载于：https://www.itzhai.com/articles/how-sql-works-understand-the-essence-of-tuning-by-the-execution-principle.html
1.Count
不同存储引擎count的区别：
- MylSAM引擎每张表中存放了一个meta信息，里面包含了row_count属性，内存和文件中各一份，内存的通过读取文件的进行初始化，如果查询带上了where条件那就无法使用这个属性了，还是得全表扫描
- InnoDB引擎执行count()的时候，需要把数据一行行从引擎里面取出来进行统计
下面我们主要介绍InnoDB中的count()
那么InnoDB为什么不像MyLSAM那样采取meta信息存储row_count呢？
因为MySQL为了实现事务的并发性能，需要MVCC支持，每个事务都会有自己的一个一致性视图（Read View），因此无法存取row_count属性，因为每个事务都有自己的快照，不太可能用一个共享字段row_count去存储数据数，因此count操作也是基于当前事务的快照的
因为执行Count操作是将数据读取到buffet pool进行统计，那么会影响到LFU的命中率吗？
不会，count加载的页面一直往旧子列表中插入，在旧子列表中淘汰，不会晋升到新子列表（防止缓冲污染），所以不会影响缓冲的命中率
1.1.Count(主键)
执行流程如下：
- 执行器请求存储引擎获取数据；
- 为了保证扫描数据量更少，存储引擎找到最小的那颗索引树获取所有记录，返回记录的id给到server。返回记录之前会进行MVCC及其可见性的判断，只返回当前事务可见的数据；
- server获取到记录之后，判断id如果不为空，则累加到结果记录中。
1.2.Count(1)
与Count（主键）的执行流程基本一致，区别在于，针对查询出的每一条记录，不会读取记录中的值，而是直接返回一个“1”进行累加
1.3.Count（字段）
与Count（主键）流程类似，只是Count只会统计非空的元素的个数，因此如果字段允许为空，这里会多一个判断的操作，如果字段没有添加索引，那么就会扫描聚簇索引，导致扫描的数据页会比较多，效率相对慢点
1.4.Count（*）
count(*)不会取记录的值，与count(1)类似。
执行效率对比：count(字段) < count(主键) < count(1)=Count（*）
2.Order By
2.1.如何跟踪执行优化
查看sql的执行流程，我们可以在当前session中开启optimizer_trace
SET optimizer_trace='enabled=on';
然后执行sql，执行完之后，就可以通过以下堆栈信息查看执行详情了：
SELECT * FROM information_schema.OPTIMIZER_TRACE\G;
以下是
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 100,2;
的执行结果，其中符合a=3的有8457条记录，针对order by重点关注以下属性：
"filesort_priority_queue_optimization": { // 是否启用优先级队列
"limit": 102, // 排序后需要取的行数，这里为 limit 100,2，也就是100+2=102
"rows_estimate": 24576, // 估计参与排序的行数
"row_size": 123, // 行大小
"memory_available": 32768, // 可用内存大小，即设置的sort buffer大小
"chosen": true // 是否启用优先级队列
},
...
"filesort_summary": {
"rows": 103, // 排序过程中会持有的行数
"examined_rows": 8457, // 参与排序的行数，InnoDB层返回的行数
"number_of_tmp_files": 0, // 外部排序时，使用的临时文件数量
"sort_buffer_size": 13496, // 内存排序使用的内存大小
"sort_mode": "sort_key, additional_fields" // 排序模式
}
2.1.1.排序模式
其中sort_mode(排序模式)包括如下几种：
-
sort_key，rowid：表明**排序缓冲区（sort buffer）**元组包含排序键值和原始表行的行id（主键id），排序后通过rowid进行回表查询，该算法称为：original filesort algorithm(回表排序算法)，由于需要回表随机IO查数据，因此效率不如单路排序
-
sort_key，additional_fields：即先回表查询完整记录，把排序需要查找的所有字段都放入sort buffer进行排序，表明**排序缓冲区（sort buffer）**包含包含排序键值和查询所需要的列，排序后无需回表，直接从缓冲区元组取的数据这种算法也称为modified filesort algorithm(不回表排序)
-
sort_key，packed_additional_fields：类似不回表排序，但是附加的列（如varchar）紧密打包在一起，而不是固定长度的编码（针对可变长列的特殊处理，见如何选择排序模式）
如何选择排序模式？
如何选择与max_length_for_sort_data这个属性有关，默认值为1024字节，该配置只是配置排序数据入sort buffer并采取排序模式的阈值，不代表sort buffer的最终大小，只用于控制排序模式，
- 如果查询列和排序列的查询列占用大小（列类型定义的大小，例如char型、int型等）超过该值，则会采用
sort_key, rowid模式（如果sort buffer都放不下了，则会多次排序并将排序结果放到排序临时文件，具体见下述排序算法）--双路排序
- 不超过，则会将所有列都放到sort buffer中，使用
sort_key, additional_fields或者sort_key, packed_additional_fields模式；--单路排序
- 如果查询记录过多，则会使用
sort_key, packed_additional_fields对可变列进行压缩（防止行数据过大）--单路排序
对于未使用的慢ORDER BY查询 filesort，请尝试将max_length_for_sort_data 系统变量降低 到适合触发双路排序并减少filesort. （官方原话：将此变量的值设置得太高的一个症状是高磁盘活动和低 CPU 活动的组合。），此技术仅适用于 MySQL 8.0.20 之前。从 8.0.20 开始， 由于优化器更改使其过时且无效，因此已弃用
将此变量的值设置得太高的一个症状是高磁盘活动和低 CPU 活动的组合，这句话怎么解释呢，意思并不是说要将max_length_for_sort_data配置尽可能调小，调小之后不是代表后续大概率都走单路排序了吗，单路排序不是性能更差了吗？那么我们是不是直接调大，来保证我们后续都走性能更好的双路排序就好了呢？这里我们不能只看max_length_for_sort_data这个配置，我们需要结合sort_buffer_size一起看，官方也说了，为了避免高磁盘活动和低 CPU 活动，假设我们调的很大，但是sort_buffer_size（每个请求的缓存）的值又很小，那我们岂不是会产生很多个filesort，因此最好的措施是保证sort_buffer_size足够大，max_length_for_sort_data尽量小于sort_buffer_size的值，但是又不能小到频繁触发单路排序。
此时我们可以监控磁盘（iostat -x 1 10）+CPU（top命令）的活动，也可以监控fileSort的指标：要监视合并遍数（合并临时文件），请检查 Sort_merge_passes 状态变量。
单路排序为将要排序的每一行创建了固定的缓冲区，varchar列的最大长度是 max_length_for_sort_data规定的值，而不是排序数据的实际大小。
针对变长字段（text、blob）进行排序时，会使用双路排序。当mysql不得不对text。blob列进行排序时，它只会使用前缀并忽略剩余的值，这是因为不得不分配固定大小的结构来容纳数据并且从外部存储中将前缀拷贝回结构中，可以使用max_sort_length定义前缀应该是多大
2.1.2.排序算法
基于参与排序的数据量的大小，会选择不同的排序算法
排序算法与排序的模式区别在于排序模式更侧重于排序时，是否采取回表（采取单路排序还是双路排序），而排序算法则指代的是对这些数据的排序采取哪种算法，具体的算法是由场景+sort buffer的大小决定的
-
如果排序结果很小，小于内存则会使用优先队列进行排序
- 例如只取最前面10条记录，则会通过优先队列进行排序，例如limit 1,10（只取最前面10条数据）
-
如果排序limit n,m；如果n太大了，也就是说可能需要取排序之后很后面的数据，那么会使用sort buffer使用快速排序
-
例如：表中a=1的数据有三条，但是需要取排序之后的第300个元素，也就是说需要排序的结果集可能会比较大，此时MySQL会分析开销之后选择一个比较合适的排序算法，这里最终放弃了优先级队列，转而使用sort buffer进行快速排序
如何衡量究竟是使用优先级队列还是内存快速排序？
一般来说，快速排序算法效率高于堆排序，但是堆排序实现的优先级队列，无需排序完所有的元素，就可以得到order by limit的结果。
MySQL源码中声明了快速排序速度是堆排序的3倍，在实际排序的时候，会根据待排序数量大小进行切换算法。如果数据量太大的时候，会转而使用快速排序。
select a, b, c, d from t20 force index(idx_abc) where a=1 order by d limit 300,2;
-
如果参与排序的数据sort buffer装不下了，那么我们会一批一批的在sort buffer进行快速排序，结果放到排序临时文件，然后再对所有的临时排序进行归并排序，得到最终结果
-
例如：如下，a=3的记录超过了sort buffer，我们要查找的数据是排序后1000行起，sort buffer装不下1000行数据了，最终MySQL选择使用sort buffer进行分批快排，把最终结果进行归并排序：
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;
查看和修改sort buffer的大小
我们看一下当前的sort buffer大小：
可以发现，这里默认配置了sort buffer大小为512k。
我们可以设置这个属性的大小：
SET GLOBAL sort_buffer_size = 32*1024;
或者
SET sort_buffer_size = 32*1024;
下面我们统一把sort buffer设置为32k
SET sort_buffer_size = 32*1024;
2.2.order by走索引避免排序
执行如下sql：
select a, b, c, d from t20 force index(idx_d) where d like 't%' order by d limit 2;
我们看一下执行计划：
发现Extra列为：Using index condition，也就是这里只走了索引。
执行流程如下图所示：
通过idx_d索引进行range_scan查找，扫描到4条记录，然后order by继续走索引，已经排好序，直接取前面两条，然后去聚集索引查询完整记录，返回最终需要的字段作为查询结果。这个过程只需要借助索引。
2.3.排序算法具体案例
2.4.排序模式具体案例
2.5.order by优化总结
- order by字段尽量使用固定长度的字段类型，因为排序字段不支持压缩
- order by字段如果需要可变长度，应尽量控制长度，道理同上
- 查询中尽量不使用select *，避免查询到的数据过多，导致order by时sort buffer不够导致外部排序，或者行大小超过
max_length_for_sort_data导致走了sort_key, rowid排序模式，使得需要回表产生更多的磁盘读
- 尝试给排序字段和相关匹配字段建立联合索引，能够用到覆盖索引是最好的。（可以避免回表以及排序避免使用filesort，能够让排序直接通过索引完成）
3.Join
MySQL在8.8.2 EXPLAIN Output Format. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/explain-output.html 中提到:
MySQL使用Nested Loop Join算法处理所有的关联查询
使用该算法，意味着这种执行模式：
- 从第一个表中读取一行，然后在第二个、第三个表中。。找到匹配的行，依次类推
- 处理完所有的关联的表后，MySQL将输出选定的列，如果列不在当前关联的索引树中，那么会回表查找完整记录
- 继续遍历，拿到下一行继续执行上述步骤
下面我们所讲到的都是Nested-Loop join算法的不同实现
多表Join：不管执行几个表的join，都是用的Nested-Loop Join实现的。如果有第三个join的表，那么会把前两个表的join结果集作为循环基础数据，在执行一次Nested-Loop Join，到第三个表中匹配数据，更多多表同理。
3.1.Join走索引（Index Nested-Loop Join）
3.1.1.Index Nested-Loop Join
首先执行如下sql：
//straight_join指定驱动表，只对inner join有效select * from t30 straight_join t31 on t30.a=t31.a;
查看执行计划：
从执行计划可以看到：
- t30作为驱动表，t31作为被驱动表
- 通过a字段去t31表查找数据时候用到了索引
该sql语句的执行流程如下图：
- 首先遍历t30的聚簇索引
- 针对t30的记录，找到a值并去t31的idx_a索引中查找是否存在记录
- 如果存在则通过索引idx_a回表查询拿到对应的数据
- 分别去t30和t31所需要的数据作为结果返回
由于这个过程中用到了idx_a索引，所以这种算法也称为：Index Nested-Loop(索引嵌套循环join)。其伪代码结构如下：
// A 为t30聚集索引
// B 为t31聚集索引
// BIndex 为t31 idx_a索引
void indexNestedLoopJoin(){
List result;
for(a in A) {
for(bi in BIndex) {
if (a satisfy condition bi) {
output <a, b>;
}
}
}
}
假设t30记录数为m，t31记录数为n，每一次查找索引树的复杂度为log2(n)，所以以上场景，总的复杂度为：m + m*2*log2(n)。
也就是说驱动表越小，复杂度越低，越能提高搜索效率。
3.1.2.Index Nested-Loop Join的优化
我们可以看到上述的流程。那么每一次从驱动表拿数据去被驱动表匹配数据拿到被驱动表的id后，通过id去回表查都使用的是磁盘的随机读写，效率比较低，那么是否有优化方法呢？
这个得从MySQL的MRR（Multi-Range Read）[链接]优化机制说起了
3.1.2.1.Multi-Range Read优化
我们执行以下代码，强制开启MMR功能：
set optimizer_switch="mrr_cost_based=off"
然后执行以下SQL，其中a是索引：
select * from t30 force index(idx_a) where a<=12 limit 10;
可以得到如下执行计划：
可以发现，Extra列提示用到了MRR优化。
这里为了演示走索引的场景，所以加了force index关键词。
正常不加force index的情况下，MySQL优化器会检查到这里即使走了索引还是需要回表查询，并且表中的数据量不多，那干脆就直接扫描全表，不走索引，效率更加高了。
如果没有MRR优化，那么流程是这样的：
- 在idx_a索引中找到a<10的记录；
- 取前面10条，拿着id去回表查找完整记录，这里回表查询是随机读，效率较差；
- 取到的结果通过net buffer返回给客户端。
使用了MRR优化之后，这个执行流程是这样的：
- 在idx_abc索引中找到a<10的记录；
- 取10条，把id放入
read rnd buffer;
read rnd buffer中的id排序；- 排序之后回表查询完整记录，id越多，排好序之后越有可能产生连续的id，去磁盘顺序读；
- 查询结果写入net buffer返回给客户端；
3.1.2.2.Batched Key Access
与Multi-Range Read的优化思路类似，MySQL也是通过把随机读改为顺序读，让Index Nested-Loop Join提升查询效率，这个算法称为Batched Key Access(BKA)[5]算法。
我们知道，默认情况下，是扫描驱动表，一行一行的去被驱动表匹配记录。这样就无法触发MRR优化了，为了能够触发MRR，于是BKA算法登场了。
在BKA算法中，驱动表通过使用join buffer批量在被驱动表的辅助索引中关联匹配数据，得到一批结果，一次性传递个数据库引擎的MRR接口，从而可以利用到MRR对磁盘读的优化。
为了启用这个算法，我们执行以下命令（BKA依赖于MRR）：
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
我们再次执行以下关联查询sql：
select * from t30 straight_join t31 on t30.a=t31.a;
我们可以得到如下的执行计划：
可以发现，这里用到了：Using join buffer(Batched Key Access)。
执行流程如下：
- 把驱动表的数据批量放入join buffer中；
- 在join buffer中批与被驱动表的辅助索引匹配结果，得到一个结果集；
- 把上一步的结果集批量提交给引擎的MRR接口；
- MRR接口处理同上一节，主要进行了磁盘顺序读的优化；
- 组合输出最终结果，可以看到，这里的结果与没有开启BKA优化的顺序有所不同，这里使用了t31被驱动表的id排序作为输出顺序，因为最后一步对被驱动表t31读取进行MRR优化的时候做了排序。
如果join条件没走索引，又会是什么情况呢，接下来我们尝试执行下对应的sql。
3.2.Join不走索引（Block Nested-Loop Join）
3.2.1.Block Nested-Loop Join（BNL）
我们执行以下sql：
select * from t30 straight_join t31 on t30.c=t31.c;
查看执行计划：
可以发现：
- t30作为驱动表，t31作为被驱动表；
- 通过c字段关联，去t31表查找数据的时候没有用到索引；
- join的过程中用到了join buffer，这里提示用到了Block Nested Loop Join；
该语句的执行流程如下：
- t30驱动表的数据分批（分块）存入join buffer，如果能够一次存入，这里会一次存入
- t31被驱动表中扫描记录，依次取出与join buffer中的记录对比，判断是否满足c相等的条件
- 满足条件的记录合并结果输出到net buffer中，最终传输给客户端
然后清空join buffer，存入下一批t30的数据，重复以上流程。
显然每批次的数据都需要扫描一遍被驱动表，批次越多，扫描越多，但是内存判断次数还是不变的，所以总批次越小，效率越高。所以和走索引的join一样，驱动表越小，复杂度越低，越能提高查询效率
3.2.2.BNL的问题
在 洞悉MySQL底层架构：游走在缓冲与磁盘之间 一文中，我们介绍了MySQL Buffer Pool的LRU算法，如下：
默认情况下，同一个数据页，在一秒中之后再次访问则会晋升到新子列表的MRU端
因此如果我们使用BNL算法，那么分批次扫描被驱动表的话，就会将被驱动表的数据放入新子列表。
因此对buffer pool的影响如下：
- 触发旧子列表的淘汰：如果join此时扫描到了一个很大的冷表（被驱动表），那么在join的这段时间内，会往旧子列表持续的写入数据页，淘汰队尾的数据页，并且会影响其他业务的数据页晋升到新子列表且可能淘汰其他的业务数据
- 触发新子列表的淘汰：因为BNL将数据分为了多个批次，如果每个批次扫描数据都超过了1s，则会将被驱动表的数据页升入新子列表，可能会触发其他业务的数据页提前从新子列表淘汰
具体什么场景页会放入buffer pool？
3.2.3.BNL问题解决方案
3.2.3.1.调大 JOIN_BUFFER_SIZE
**针对以上这种场景，为了避免影响buffer pool，最直接的办法就是增加join_buffer_size的值，以减少对被驱动表的扫描次数。**扫描次数少了，数据晋升速度会降低
3.2.3.2.把BNL转换为BKA（被驱动表join字段新增索引）
我们可以通过把join的条件加上索引，从而避免了BNL算法，转而使用BKA算法，这样也可以加快记录的匹配速度，以及从磁盘读取被驱动表记录的速度。
3.2.3.3.通过添加临时表
由于考虑到添加索引的开销，因此可以将被驱动表的数据放入临时表中，在临时表中添加索引的方式，以达成BKA的优化效果
3.2.3.4.使用HASH JOIN
一般情况下，MySQL的join实现都是以上介绍的各种nested-loop算法的实现，但是从MySQL 8.0.18[6]开始，我们可以使用hash join来实现表连续查询了。感兴趣可以进一步阅读这篇文章进行了解：[Hash join in MySQL 8 | MySQL Server Blog](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does.)
把驱动表满足条件的数据取出来，放入一个hash结构中，然后把被驱动表满足条件的数据取出来，一行一行的去hash结构中寻找匹配的数据，依次找到满足条件的所有记录。
3.3.各种Join
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
OUTER JOIN EXCLUDING INNER JOIN
3.3.Join使用总结
- 小表驱动大表：join优化的目标是尽可能减少join中Nested-Loop的循环次数，所以尽量让小表作为驱动表
- 关联字段尽量走索引，这样就可以用到index Nested-Loop Join了
- 如果有order by，尽量使用驱动表的字段作为order by，否则会使用using temporary
- 如果不可避免的需要使用BNL算法，为了减少被驱动表多次扫描对buffer pool的影响，那么可以尝试把join_buffer_size调大
- 为了进一步加快BNL算法的执行效率，我们可以给关联条件加上索引，转换为BKA算法；如果加索引成本较高，那么可以通过临时表添加索引来实现；
- 如果您使用的是MySQL 8.0.18，可以尝试使用hash join，如果是较低版本，也可以自己在程序中实现一个hash join。
Join什么时候会使用到join buffer
- 不使用索引字段进行连接，会将数据分块放到join buffer中进行比较
- 使用索引字段连接，并使用BKA进行优化，会将驱动表的数据全部放入join buffer
4.union
通过使用union可以把两个查询结果合并起来，注意：
union all不会去除重复的行，union则会去除重复读的行。
4.1、union all
执行下面sql：
(select id from t30 order by id desc limit 10) union all (select c from t31 order by id desc limit 10)
该sql执行计划如下图：
执行流程如下：
- 从t30表查询出结果，直接写出到net buffer，传回给客户端；
- 从t31表查询出结果，直接写出到net buffer，传回给客户端。
4.2、union
执行下面sql：
(select id from t30 order by id desc limit 10) union (select c from t31 order by id desc limit 10)
该sql执行计划如下图：
执行流程如下：
- 从t30查询出记录，写入到临时表；
- 从t30查询出记录，写入临时表，在临时表中通过唯一索引去重；
- 把临时表的数据通过net buffer返回给客户端。
5.group by
5.1.完全走索引
我们给t30加一个索引：
alter table t30 add index idx_c(c);
执行以下group bysql：
select c, count(*) from t30 group by c;
执行计划如下：
这里只用了索引，没有用到临时表，原因是idx_c索引本身就是按照c排序好的，那么直接扫描idx_c索引，可以直接统计到每一个c值有多少条记录，无需其他统计了
5.2.临时表
假设没有索引idx_c
select c, count(*) from t30 group by c order by null;
为了避免排序，所以我们这里添加了 order by null，表示不排序。
执行计划如下：
可以发现，这里用到了内存临时表。其执行流程如下：
- 扫描t30聚簇索引
- 建立一个临时表，以分组字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加
- 把累加后的临时表返回给客户端
5.3.临时表+排序
如果我们把上一步的order by null去掉，默认情况下，group by的结果是会通过c字段排序的。我们看看其执行计划：
可以发现，这里除了用到临时表，还用到了排序。
我们进一步看看其执行的OPTIMIZER_TRACE日志：
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table", // 创建中间临时表
"row_length": 13,
"key_length": 4,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 1290555
}
}
},
{
"filesort_information": [
{
"direction": "asc",
"table": "intermediate_tmp_table",
"field": "c"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)" // 由于没有 limit，不采用优先级队列排序
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 7,
"examined_rows": 7,
"number_of_tmp_files": 0,
"sort_buffer_size": 344,
"sort_mode": "<sort_key, rowid>" // rowid排序模式
}
}
]
通过执行栈日志可以看到，这里创建了临时表，由于没有limt限制条数，所以这里没有使用到优先级队列排序，这里采取的排序模式为sort_key, rowid。其执行流程如下：
- 扫描t30聚簇索引
- 建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加
- 将得到的临时表放入sort buffer进行排序，这里通过rowid进行排序（双路排序）
- 通过排序好的rowid回临时表查找需要的字段，返回给客户端。
临时表是存放在磁盘还是内存？
tmp_table_size 参数用于设置内存临时表的大小，如果临时表超过这个大小，那么会转为磁盘临时表：
可以通过以下sql设置当前session中的内存临时表大小：SET tmp_table_size = 102400;
5.5.直接排序
查看官方文档的 SELECT Statement[9]，可以发现SELECT后面可以使用许多修饰符来影响SQL的执行效果：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[into_option]
[FOR UPDATE | LOCK IN SHARE MODE]
into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}
这里我们重点关注下这两个：
SQL_BIG_RESULT：可以在包含group by 和distinct的SQL中使用，提醒优化器查询数据量很大，这个时候MySQL会直接选用磁盘临时表取代内存临时表，避免执行过程中发现内存不足才转为磁盘临时表。这个时候更倾向于使用排序取代二维临时表统计结果。后面我们会演示这样的案例；SQL_SMALL_RESULT：可以在包含group by 和distinct的SQL中使用，提醒优化器数据量很小，提醒优化器直接选用内存临时表，这样会通过临时表统计，而不是排序
接下来我们就通过例子来说明下使用了SQL_BIG_RESULT修饰符的SQL执行流程。
有如下SQL：
select SQL_BIG_RESULT c, count(*) from t30 group by c;
执行计划如下：
这里用到了排序，但是没有用到索引或者临时表，这里到了SQL_BIG_RESULT修饰符，告诉优化器group by的数据量很大，直接选用磁盘临时表，，但磁盘临时表存储效率不高，最终优化器使用数组排序的方式来完成这个查询。（当然，这个例子实际的结果集并不大，只是作为演示用）就是说可能还是会有临时表，但是数据量少，因此直接sort buffer排序就好了
执行流程如下：
- 扫描t30表，逐行将c字段放入sort buffer
- 在sort buffer对数据进行排序，得到一个排序好的c数组
- 遍历排序好的c数组，统计结果并输出
5.4.group by 优化建议
- 尽量让group by走索引，能很大程度上提高效率
- 如果group by不需要排序，则可以加上order by null，避免进行排序
- 如果group by的数据量很大，可以使用SQL_BIG_RESULT修饰符，提醒优化器应该直接使用排序算法得到group的结果
6.distinct
在大多数情况下，Distinct相当于Group by的一个特殊案例
select distinct a, b, c from t30;
select a, b, c from t30 group by a, b, c order by null;
这两个SQL的执行计划如下：
考虑到这种等效性，因此适用于group by的优化也适用于distinct
区别：distinct是在group by之后的每组中取出一条记录，distinct分组之后不进行排序
在一个join关联查询中，如果您只是查询驱动表的列，并且驱动表的该列申明了distinct关键字，那么优化器会进行优化，在被驱动表中查找匹配的第一行时，将停止继续扫描如下SQL：
explain select distinct t30.a from t30, t31 where t30.c=t31.c;
执行计划如下，可以发现Extra列中有一个distinct，该标识即标识用到了这种优化[10:1]：
7.子查询
子查询：可以嵌套在另一个查询(select insert update delete)内，子查询也可以嵌套在另一个子查询里面
7.1.子查询的使用方法
7.2.子查询的优化
子查询主要三种优化手段：
- Semijoin：半连接转换，把子查询sql自动转换为Semijoin
- Materialization，子查询物化；
- EXISTS策略，in转exists；
其中Semijoin只能用于IN，=，或者Exist的子查询中，无法用于NOT IN，<>，或者NOT EXISTS的子查询中
真的要尽量使用关联查询(Join)取代子查询吗？
在《高性能MySQL》[12]一书中，提到：优化子查询最重要的建议就是尽可能使用关联查询代替，但是，如果使用的是MySQL 5.6或者更新版本或者MariaDB，那么就可以直接忽略这个建议了。因为这些版本对子查询做了不少的优化，后面我们会重点介绍这些优化。
其中 5.6版本之后针对子查询的优化就包括上述所述的Semijoin、Materialization等。就拿IN查询举例，其在5.5版本之前都会将IN查询转化为Exist查询进行执行，那么5.6版本之后呢？可以继续看下文的MySQL的具体优化方案
in的效率真的这么慢吗？
在MySQL5.6之后是做了不少优化的，下面我们就逐个来介绍。
7.2.1.SENIJOIN
Semijoin，半连接：所谓半连接，指的是一张表在另一张表栈道匹配的记录之后，返回第一张表的记录，即便右边找到了几条匹配的记录，也只返回左边的第一条
半连接非常适用于查找两个表之间的数据是否具有关联关系，而不关注匹配的具体条数这种场景
半连接通常用于IN或EXIST语句的优化，因为只考虑查找的两个表之前是否存在匹配的记录，不考虑匹配的具体条数
7.2.2.1.优化场景
in关联子查询
优化场景：
统计有学生分数不及格的课程：
SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);
我们可以通过执行以下脚本，查看sql做了什么优化：
explain extended SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);show warnings\G;
得到如下执行执行计划，和SQL重写结果：
