相信大家看过无数的MySQL调优经验贴了，会告诉你各种调优手段，如：
其中有些手段也许跟随者MySQL版本的升级过时了。我们真的需要背这些调优手段吗？我觉得是没有必要的，在掌握MySQL存储架构和SQL执行原理的情况下，我们就很自然的明白，为什么要提议这么优化了，甚至能够发现别人提的不太合理的优化手段。
在 洞悉MySQL底层架构：游走在缓冲与磁盘之间 这篇文章中，我们已经介绍了MySQL的存储架构，详细对你在MySQL存储、索引、缓冲、IO相关的调优经验中有了一定的其实。
本文，我们重点讲解常用的SQL的执行原理，从执行原理，以及MySQL内部对SQL的优化机制，来分析SQL要如何调优，理解为什么要这样...那样...那样...调优。
如果没有特别说明，本文以MySQL5.7版本作为讲解和演示。
阅读完本文，您将了解到：
存储引擎的区别
MyISAM引擎每张表中存放了一个meta信息，里面包含了row_count属性，内存和文件中各有一份，内存的count变量值通过读取文件中的count值来进行初始化。[1]但是如果带有where条件，还是必须得进行表扫描
InnoDB引擎执行count()的时候，需要把数据一行行从引擎里面取出来进行统计。
下面我们介绍InnoDB中的count()。
InnoDB中为何不像MyISAM那样维护一个row_count变量呢？
前面 洞悉MySQL底层架构：游走在缓冲与磁盘之间 一文我们了解到，InnoDB为了实现事务，是需要MVCC支持的。MVCC的关键是一致性视图。一个事务开启瞬间，所有活跃的事务(未提交)构成了一个视图数组，InnoDB就是通过这个视图数组来判断行数据是否需要undo到指定的版本。
如下图，假设执行count的时候，一致性视图得到当前事务能够取到的最大事务ID DATA_TRX_ID=1002，那么行记录中事务ID超过1002都都要通过undo log进行版本回退，最终才能得出最终哪些行记录是当前事务需要统计的：
row1是其他事务新插入的记录，当前事务不应该算进去。所以最终得出，当前事务应该统计row2，row3。
执行count会影响其他页面buffer pool的命中率吗？
我们知道buffer pool中的LRU算法是是经过改进的，默认情况下，旧子列表(old区)占3/8，count加载的页面一直往旧子列表中插入，在旧子列表中淘汰，不会晋升到新子列表中。所以不会影响其他页面buffer pool的命中率。
count(主键)执行流程如下：
count(1)与count(主键)执行流程基本一致，区别在于，针对查询出的每一条记录，不会取记录中的值，而是直接返回一个"1"用于统计累加。统计了所有的行。
与count(主键)类似，会筛选非空的字段进行统计。如果字段没有添加索引，那么会扫描聚集索引树，导致扫描的数据页会比较多，效率相对慢点。
count(*)不会取记录的值，与count(1)类似。
执行效率对比：count(字段) < count(主键) < count(1)
以下是我们本节作为演示例子的表，假设我们有如下表：
索引如下：
对应的idx_d索引结构如下(这里我们做了一些夸张的手法，让一个页数据变小，为了展现在索引树中的查找流程)：
为了方便分析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" // 排序模式
}
其中 sort_mode有如下几种形式：
sort_key, rowid：表明排序缓冲区元组包含排序键值和原始表行的行id，排序后需要使用行id进行回表，这种算法也称为original filesort algorithm(回表排序算法)；sort_key, additional_fields：表明排序缓冲区元组包含排序键值和查询所需要的列，排序后直接从缓冲区元组取数据，无需回表，这种算法也称为modified filesort algorithm(不回表排序)；sort_key, packed_additional_fields：类似上一种形式，但是附加的列(如varchar类型)紧密地打包在一起，而不是使用固定长度的编码。选择哪种排序模式，与max_length_for_sort_data这个属性有关，这个属性默认值大小为1024字节：
sort_key, rowid模式；sort_key, additional_fields或者sort_key, packed_additional_fields模式；sort_key, packed_additional_fields对可变列进行压缩。基于参与排序的数据量的不同，可以选择不同的排序算法：
如果排序取的结果很小，小于内存，那么会使用优先级队列进行堆排序；
例如，以下只取了前面10条记录，会通过优先级队列进行排序：
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;
如果排序limit n, m，n太大了，也就是说需要取排序很后面的数据，那么会使用sort buffer进行快速排序：
如下，表中a=1的数据又三条，但是由于需要limit到很后面的记录，MySQL会对比优先级队列排序和快速排序的开销，选择一个比较合适的排序算法，这里最终放弃了优先级队列，转而使用sort buffer进行快速排序：
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;
执行如下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继续走索引，已经排好序，直接取前面两条，然后去聚集索引查询完整记录，返回最终需要的字段作为查询结果。这个过程只需要借助索引。
如何查看和修改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;
如果排序取的结果很小，并且小于sort buffer，那么会使用优先级队列进行堆排序；
例如，以下只取了前面10条记录：
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;
a=3的总记录数：8520。查看执行计划：
发现这里where条件用到了索引，order by limit用到了排序。我们进一步看看执行的optimizer_trace日志：
"filesort_priority_queue_optimization": {
"limit": 10,
"rows_estimate": 27033,
"row_size": 123,
"memory_available": 32768,
"chosen": true // 使用优先级队列进行排序
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 11,
"examined_rows": 8520,
"number_of_tmp_files": 0,
"sort_buffer_size": 1448,
"sort_mode": "sort_key, additional_fields"
}
发现这里是用到了优先级队列进行排序。排序模式是：sort_key, additional_fields，即先回表查询完整记录，把排序需要查找的所有字段都放入sort buffer进行排序。
所以这个执行流程如下图所示：
如果排序limit n, m，n太大了，也就是说需要取排序很后面的数据，那么会使用sort buffer进行快速排序。MySQL会对比优先级队列排序和归并排序的开销，选择一个比较合适的排序算法。
如何衡量究竟是使用优先级队列还是内存快速排序？
一般来说，快速排序算法效率高于堆排序，但是堆排序实现的优先级队列，无需排序完所有的元素，就可以得到order by limit的结果。
MySQL源码中声明了快速排序速度是堆排序的3倍，在实际排序的时候，会根据待排序数量大小进行切换算法。如果数据量太大的时候，会转而使用快速排序。
有如下SQL：
select a, b, c, d from t20 force index(idx_abc) where a=1 order by d limit 300,2;
我们把sort buffer设置为32k：
SET sort_buffer_size = 32*1024;
其中a=1的记录有3条。查看执行计划：
可以发现，这里where条件用到了索引，order by limit 用到了排序。我们进一步看看执行的optimizer_trace日志：
"filesort_priority_queue_optimization": {
"limit": 302,
"rows_estimate": 27033,
"row_size": 123,
"memory_available": 32768,
"strip_additional_fields": {
"row_size": 57,
"sort_merge_cost": 33783,
"priority_queue_cost": 61158,
"chosen": false // 对比发现快速排序开销成本比优先级队列更低，这里不适用优先级队列
}
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 3,
"examined_rows": 3,
"number_of_tmp_files": 0,
"sort_buffer_size": 32720,
"sort_mode": "<sort_key, packed_additional_fields>"
}
可以发现这里最终放弃了优先级队列，转而使用sort buffer进行快速排序。
所以这个执行流程如下图所示：
sort buffer中；快速排序；当参与排序的数据太多，一次性放不进去sort buffer的时候，那么我们会一批一批的给sort buffer进行内存排序，结果放入排序临时文件，最终使对所有排好序的临时文件进行归并排序，得到最终的结果。
有如下sql：
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;
其中a=3的记录有8520条。执行计划如下：
可以发现，这里where用到了索引，order by limit用到了排序。进一步查看执行的optimizer_trace日志：
"filesort_priority_queue_optimization": {
"limit": 1010,
"rows_estimate": 27033,
"row_size": 123,
"memory_available": 32768,
"strip_additional_fields": {
"row_size": 57,
"chosen": false,
"cause": "not_enough_space" // sort buffer空间不够，无法使用优先级队列进行排序了
}
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 8520,
"examined_rows": 8520,
"number_of_tmp_files": 24, // 用到了24个外部文件进行排序
"sort_buffer_size": 32720,
"sort_mode": "<sort_key, packed_additional_fields>"
}
我们可以看到，由于limit 1000，要返回排序后1000行以后的记录，显然sort buffer已经不能支撑这么大的优先级队列了，所以转而使用sort buffer内存排序，而这里需要在sort buffer中分批执行快速排序，得到多个排序好的外部临时文件，最终执行归并排序。（外部临时文件的位置由tmpdir参数指定）
其流程如下图所示：
sort_key, additional_fields，排序缓冲区元组包含排序键值和查询所需要的列（先回表取需要的数据，存入排序缓冲区中），排序后直接从缓冲区元组取数据，无需再次回表。
上面 2.3.1、2.3.2节的例子都是这种排序模式，就不继续举例了。
sort_key, packed_additional_fields：类似上一种形式，但是附加的列(如varchar类型)紧密地打包在一起，而不是使用固定长度的编码。
上面2.3.3节的例子就是这种排序模式，由于参与排序的总记录大小太大了，因此需要对附加列进行紧密地打包操作，以节省内存。
前面我们提到，选择哪种排序模式，与max_length_for_sort_data[2]这个属性有关，max_length_for_sort_data规定了排序行的最大大小，这个属性默认值大小为1024字节：
也就是说如果查询列和排序列占用的大小小于这个值，这个时候会走sort_key, additional_fields或者sort_key, packed_additional_fields算法，否则，那么会转而使用sort_key, rowid模式。
现在我们特意把这个值设置小一点，模拟sort_key, rowid模式：
SET max_length_for_sort_data = 100;
这个时候执行sql：
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;
这个时候再查看sql执行的optimizer_trace日志：
"filesort_priority_queue_optimization": {
"limit": 10,
"rows_estimate": 27033,
"row_size": 49,
"memory_available": 32768,
"chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 11,
"examined_rows": 8520,
"number_of_tmp_files": 0,
"sort_buffer_size": 632,
"sort_mode": "<sort_key, rowid>"
}
可以发现这个时候切换到了sort_key, rowid模式，在这个模式下，执行流程如下：
id和d字段，放入sort buffer中进行堆排序；可以发现，正因为行记录太大了，所以sort buffer中只存了需要排序的字段和主键id，以时间换取空间，最终排序完成，再次从聚集索引中查找到所有需要的字段返回给客户端，很明显，这里多了一次回表操作的磁盘读，整体效率上是稍微低一点的。
根据以上的介绍，我们可以总结出以下的order by语句的相关优化手段：
max_length_for_sort_data导致走了sort_key, rowid排序模式，使得产生了更多的磁盘读，影响性能；为了演示join，接下来我们需要用到这两个表：
CREATE TABLE `t30` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;
CREATE TABLE `t31` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`f` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY idx_a(a)
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into t30(a,b,c) values(1, 1, 1),(12,2,2),(3,3,3),(11, 12, 31),(15,1,32),(33,33,43),(5,13,14),(4,13,14),(16,13,14),(10,13,14);
insert into t31(a,f,c) values(1, 1, 1),(21,2,2),(3,3,3),(12, 1, 1),(31,20,2),(4,10,3),(2,23,24),(22,23,24),(5,23,24),(20,23,24);
在MySQL官方文档中 8.8.2 EXPLAIN Output Format[3] 提到：MySQL使用Nested-Loop Loin算法处理所有的关联查询。使用这种算法，意味着这种执行模式：
下面我们所讲到的都是Nested-Loop Join算法的不同实现。
多表join：不管多少个表join，都是用的Nested-Loop Join实现的。如果有第三个join的表，那么会把前两个表的join结果集作为循环基础数据，在执行一次Nested-Loop Join，到第三个表中匹配数据，更多多表同理。
我们执行以下sql：
select * from t30 straight_join t31 on t30.a=t31.a;
查看执行计划：
可以发现：
该sql语句的执行流程如下图：
由于这个过程中用到了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)。
也就是说驱动表越小，复杂度越低，越能提高搜索效率。
我们可以发现，以上流程，每次从驱动表取一条数据，然后去被驱动表关联取数，表现为磁盘的随记读，效率是比较低低，有没有优化的方法呢？
这个就得从MySQL的MRR（Multi-Range Read）[4]优化机制说起了。
我们执行以下代码，强制开启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优化，那么流程是这样的：
使用了MRR优化之后，这个执行流程是这样的：
read rnd buffer;read rnd buffer中的id排序；与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条件没走索引，又会是什么情况呢，接下来我们尝试执行下对应的sql。
我们执行以下sql：
select * from t30 straight_join t31 on t30.c=t31.c;
查看执行计划：
可以发现：
该语句的执行流程如下图：
然后清空join buffer，存入下一批t30的数据，重复以上流程。
显然，每批数据都需要扫描一遍被驱动表，批次越多，扫描越多，但是内存判断总次数是不变的。所以总批次越小，越高效。所以，跟上一个算法一样，驱动表越小，复杂度越低，越能提高搜索效率。
在 洞悉MySQL底层架构：游走在缓冲与磁盘之间 一文中，我们介绍了MySQL Buffer Pool的LRU算法，如下：
默认情况下，同一个数据页，在一秒钟之后再次访问，那么就会晋升到新子列表(young区)。
恰巧，如果我们用到了BNL算法，那么分批执行的话，就会重复扫描被驱动表去匹配每一个批次了。
考虑以下两种会影响buffer pool的场景：
针对以上这种场景，为了避免影响buffer pool，最直接的办法就是增加join_buffer_size的值，以减少对被驱动表的扫描次数。
我们可以通过把join的条件加上索引，从而避免了BNL算法，转而使用BKA算法，这样也可以加快记录的匹配速度，以及从磁盘读取被驱动表记录的速度。
有时候，被驱动表很大，但是关联查询又很少使用，直接给关联字段加索引太浪费空间了，这个时候就可以通过把被驱动表的数据放入临时表，在零时表中添加索引的方式，以达成3.2.3.2的优化效果。
什么是hash join呢，简单来说就是这样的一种模型：
把驱动表满足条件的数据取出来，放入一个hash结构中，然后把被驱动表满足条件的数据取出来，一行一行的去hash结构中寻找匹配的数据，依次找到满足条件的所有记录。
一般情况下，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.)
我们在平时工作中，会遇到各种各样的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
更详细的介绍，可以参考：
通过使用union可以把两个查询结果合并起来，注意：
union all不会去除重复的行，union则会去除重复读的行。
执行下面sql：
(select id from t30 order by id desc limit 10) union all (select c from t31 order by id desc limit 10)
该sql执行计划如下图：
执行流程如下：
执行下面sql：
(select id from t30 order by id desc limit 10) union (select c from t31 order by id desc limit 10)
该sql执行计划如下图：
执行流程如下：
我们给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值有多少条记录，无需做其他的统计了。
现在我们把刚刚的idx_c索引给删掉，执行以下sql：
select c, count(*) from t30 group by c order by null;
为了避免排序，所以我们这里添加了 order by null，表示不排序。
执行计划如下：
可以发现，这里用到了内存临时表。其执行流程如下：
如果我们把上一步的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排序模式
}
}
]
通过日志也可以发现，这里用到了中间临时表，由于没有limit限制条数，这里没有用到优先级队列排序，这里的排序模式为sort_key, rowid。其执行流程如下：
临时表是存放在磁盘还是内存？
tmp_table_size 参数用于设置内存临时表的大小，如果临时表超过这个大小，那么会转为磁盘临时表：
可以通过以下sql设置当前session中的内存临时表大小：SET tmp_table_size = 102400;
查看官方文档的 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的数据量很大，直接选用磁盘临时表，但磁盘临时表存储效率不高，最终优化器使用数组排序的方式来完成这个查询。（当然，这个例子实际的结果集并不大，只是作为演示用）
其执行结果如下：
group by null，避免进行排序；SQL_BIG_RESULT修饰符，提醒优化器应该使用排序算法得到group的结果。在大多数情况下，DISTINCT可以考虑为GROUP BY的一个特殊案例，如下两个SQL是等效的：
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分组之后不进行排序。
在一个关联查询中，如果您只是查询驱动表的列，并且在驱动表的列中声明了distinct关键字，那么优化器会进行优化，在被驱动表中查找到匹配的第一行时，将停止继续扫描。如下SQL：
explain select distinct t30.a from t30, t31 where t30.c=t30.c;
执行计划如下，可以发现Extra列中有一个distinct，该标识即标识用到了这种优化[10:1]：
首先，我们来明确几个概念：
子查询：可以是嵌套在另一个查询(select insert update delete)内，子查询也可以是嵌套在另一个子查询里面。
MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在使用表达式的任何地方使用。
接下来我们使用以下表格来演示各种子查询：
create table class (
id bigint not null auto_increment,
class_num varchar(10) comment ‘课程编号‘,
class_name varchar(100) comment ‘课程名称‘,
pass_score integer comment ‘课程及格分数‘,
primary key (id)
) comment ‘课程‘;
create table student_class (
id bigint not null auto_increment,
student_name varchar(100) comment ‘学生姓名‘,
class_num varchar(10) comment ‘课程编号‘,
score integer comment ‘课程得分‘,
primary key (id)
) comment ‘学生选修课程信息‘;
insert into class(class_num, class_name, pass_score) values (‘C001‘,‘语文‘, 60),(‘C002‘,‘数学‘, 70),(‘C003‘, ‘英文‘, 60),(‘C004‘, ‘体育‘, 80),(‘C005‘, ‘音乐‘, 60),(‘C006‘, ‘美术‘, 70);
insert into student_class(student_name, class_num, score) values(‘James‘, ‘C001‘, 80),(‘Talor‘, ‘C005‘, 75),(‘Kate‘, ‘C002‘, 65),(‘David‘, ‘C006‘, 82),(‘Ann‘, ‘C004‘, 88),(‘Jan‘, ‘C003‘, 70),(‘James‘, ‘C002‘, 97), (‘Kate‘, ‘C005‘, 90), (‘Jan‘, ‘C005‘, 86), (‘Talor‘, ‘C006‘, 92);
子查询的用法比较多，我们先来列举下有哪些子查询的使用方法。
可以使用比较运算法，例如=，>，<将子查询返回的单个值与where子句表达式进行比较，如
查找学生选择的编号最大的课程信息：
SELECT class.* FROM class WHERE class.class_num = ( SELECT MAX(class_num) FROM student_class );
如果子查询返回多个值，则可以在WHERE子句中使用其他运算符，例如IN或NOT IN运算符。如
查找学生都选择了哪些课程：
SELECT class.* FROM class WHERE class.class_num IN ( SELECT DISTINCT class_num FROM student_class );
在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。该表称为派生表或实例化子查询。如 查找最热门和最冷门的课程分别有多少人选择：
SELECT max(count), min(count) FROM (SELECT class_num, count(1) as count FROM student_class group by class_num) as t1;
前面的示例中，您注意到子查询是独立的。这意味着您可以将子查询作为独立查询执行。
与独立子查询不同，关联子查询是使用外部查询中的数据的子查询。换句话说，相关子查询取决于外部查询。对于外部查询中的每一行，对关联子查询进行一次评估。
下面是比较运算符中的一个关联子查询。
查找每门课程超过平均分的学生课程记录：
SELECT t1.* FROM student_class t1 WHERE t1.score > ( SELECT AVG(score) FROM student_class t2 WHERE t1.class_num = t2.class_num);
关联子查询中，针对每一个外部记录，都需要执行一次子查询，因为每一条外部记录的class_num可能都不一样。
当子查询与EXISTS或NOT EXISTS运算符一起使用时，子查询将返回布尔值TRUE或FALSE。
查找所有学生总分大于100分的课程：
select * from class t1
where exists(
select sum(score) as total_score from student_class t2
where t2.class_num=t1.class_num group by t2.class_num having total_score > 100
)
上面我们演示了子查询的各种用法，接下来，我们来讲一下子查询的优化[11]。
子查询主要由以下三种优化手段：
其中Semijoin只能用于IN，= ANY，或者EXISTS的子查询中，不能用于NOT IN，<> ALL，或者NOT EXISTS的子查询中。
下面我们做一下详细的介绍。
真的要尽量使用关联查询取代子查询吗？
在《高性能MySQL》[12]一书中，提到：优化子查询最重要的建议就是尽可能使用关联查询代替，但是，如果使用的是MySQL 5.6或者更新版本或者MariaDB，那么就可以直接忽略这个建议了。因为这些版本对子查询做了不少的优化，后面我们会重点介绍这些优化。
in的效率真的这么慢吗？
在MySQL5.6之后是做了不少优化的，下面我们就逐个来介绍。
Semijoin[13]，半连接，所谓半连接，指的是一张表在另一张表栈道匹配的记录之后，返回第一张表的记录。即使右边找到了几条匹配的记录，也最终返回左边的一条。
所以，半连接非常适用于查找两个表之间是否存在匹配的记录，而不关注匹配了多少条记录这种场景。
半连接通常用于IN或者EXISTS语句的优化。
上面我们讲到：接非常适用于查找两个表之间是否存在匹配的记录，而不关注匹配了多少条记录这种场景。
in关联子查询
这种场景，如果使用in来实现，可能会是这样：
SELECT class_num, class_name
FROM class
WHERE class_num IN
(SELECT class_num FROM student_class where condition);
在这里，优化器可以识别出IN子句要求子查询仅从student_class表返回唯一的class_num。在这种情况下，查询会自动优化为使用半联接。
如果使用exists来实现，可能会是这样：
SELECT class_num, class_name
FROM class
WHERE EXISTS
(SELECT * FROM student_class WHERE class.class_num = student_class.class_num);
优化案例
统计有学生分数不及格的课程：
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重写结果：
从这个SQL重写结果中，可以看出，最终子查询变为了semi join语句：
/* select#1 */ select `test`.`t1`.`class_num` AS `class_num`,`test`.`t1`.`class_name` AS `class_name`
from `test`.`class` `t1`
semi join (`test`.`student_class` `t2`) where ((`test`.`t2`.`class_num` = `test`.`t1`.`class_num`) and (`test`.`t2`.`score` < `test`.`t1`.`pass_score`))
而执行计划中，我们看Extra列：
Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
Using join buffer这项是在join关联查询的时候会用到，前面讲join语句的时候已经介绍过了，现在我们重点看一下FirstMatch(t1)这个优化项。
FirstMatch(t1)是Semijoin优化策略中的一种。下面我们详细介绍下Semijoin有哪些优化策略。
MySQL支持5中Semijoin优化策略，下面逐一介绍。
在内部表寻找与外部表匹配的记录，一旦找到第一条，则停止继续匹配。
案例 - 统计有学生分数不及格的课程：
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);
执行计划：
执行流程，图比较大，请大家放大观看：
您也可以去MariaDB官网，查看官方的FirstMatch Strategy[14]解释。
将Semijoin作为一个常规的inner join，然后通过使用一个临时表去重。
具体演示案例，参考MariaDB官网：DuplicateWeedout Strategy[15]，以下是官网例子的图示：
可以看到，灰色区域为临时表，通过临时表唯一索引进行去重。
把内部表的数据基于索引进行分组，取每组第一条数据进行匹配。
具体演示案例，参考MariaDB官网：LooseScan Strategy[16]，以下是官网例子的图示：
如果子查询是独立的(非关联子查询)，则优化器可以选择将独立子查询产生的结果存储到一张物化临时表中。
为了触发这个优化，我们需要往表里面添加多点数据，好让优化器认为这个优化是有价值的。
我们执行以下SQL：
select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like ‘C%‘;
执行流程如下：
物化表的唯一索引
MySQL会报物化子查询所有查询字段组成一个唯一索引，用于去重。如上面图示，灰色连线的两条记录冲突去重了。
join操作可以从两个方向执行：
扫描物化表，去与class表记录进行匹配，这种我们称为Materialize-scan；物化表中查找匹配记录，这种我们称为Materialize-lookup，这个时候，我们用到了物化表的唯一索引进行查找，效率会很快。下面我们介绍下这两种执行方式。
还是以上面的sql为例：
select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like ‘C%‘;
执行计划如下：
可以发现：
subquery2的表名，这个表正式我们从id=2的查询得到的物化表。subquery2执行eq_ref，这里用到了auto_key，得到匹配的记录。也就是说，优化器选择了对t1(class)表进行全表扫描，然后去物化表进行所以等值查找，最终得到结果。
执行模型如下图所示：
原则：小表驱动大表，关联字段被驱动表添加索引
如果子查询查出来的物化表很小，而外部表很大，并且关联字段是外部表的索引字段，那么优化器会选择扫描物化表去关联外部表，也就是Materialize-scan，下面演示这个场景。
现在我们尝试给class表添加class_num唯一索引：
alter table class add unique uk_class_num(class_num);
并且在class中插入更多的数据。然后执行同样的sql，得到以下执行计划：
可以发现，这个时候id=1的查询是选择了subquery2，也就是物化表进行扫描，扫描结果逐行去t1表(class)进行eq_ref匹配，匹配过程中用到了t1表的索引。
这里的执行流程正好与上面的相反，选择了从class表关联物化表。
现在，我问大家：Materialization策略什么时候会选择从外部表关联内部表？相信大家心里应该有答案了。
执行模型如下：
原则：小表驱动大表，关联字段被驱动表添加索引
现在留给大家另一个问题：以上例子中，这两种Materialization的开销分别是多少(从行读和行写的角度统计)
答案：
Materialize-lookup：40次读student_class表，40次写物化临时表，42次读外部表，40次lookup检索物化临时表；
Materialize-scan：15次读student_class表，15次写物化临时表，15次扫描物化临时表，执行15次class表索引查询。
优化器使用Materialization(物化)来实现更加有效的子查询处理。物化针对非关联子查询进行优化。
物化通过把子查询结果存储为临时表（通常在内存中）来加快查询的执行速度。MySQL在第一次获取子查询结果时，会将结果物化为临时表。随后如果再次需要子查询的结果，则直接从临时表中读取。
优化器可以使用哈希索引为临时表建立索引，以使查找更加高效，并且通过索引来消除重复项，让表保持更小。
子查询物化的临时表在可能的情况下存储在内存中，如果表太大，则会退回到磁盘上进行存储。
如果未开启物化优化，那么优化器有时会将非关联子查询重写为关联子查询。
可以通过以下命令查询优化开关(Switchable Optimizations[18])状态：
SELECT @@optimizer_switch\G;
也就是说，如下的in独立子查询语句：
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
会重写为exists关联子查询语句：
SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
开启了物化开关之后，独立子查询避免了这样的重写，使得子查询只会查询一次，而不是重写为exists语句导致外部每一行记录都会执行一次子查询，严重降低了效率。
考虑以下的子查询：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
MySQL“从外到内”来评估查询。也就是说，它首先获取外部表达式outer_expr的值，然后运行子查询并获取其产生的结果集用于比较。
如果我们可以把outer_expr下推到子查询中进行条件判断，如下：
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
这样就能够减少子查询的行数了。相比于直接用IN来说，这样就可以加快SQL的执行效率了。
而涉及到NULL值的处理，相对就比较复杂，由于篇幅所限，这里作为延伸学习，感兴趣的朋友可以进一步阅读：
8.2.2.3 Optimizing Subqueries with the EXISTS Strategy[19]
延伸：
除了让关联的in子查询转为exists进行优化之外。在MariaDB 10.0.2版本中，引入了另一种相反的优化措施：可以让exists子查询转换为非关联in子查询，这样就可以用上非关联资产性的物化优化策略了。
总结一下子查询的优化方式：
condition push down把条件下推到exists子查询中，减少子查询的结果集，从而达到优化的目的。limit的用法：
limit [offset], [rows]
其中 offset表示偏移量，rows表示需要返回的行数。
offset limit 表中的剩余数据
_||_ __||__ __||__
| | | | |
RRRRRR RRRRRRRR RRR...
|______|
||
结果集
MySQL进行表扫描，读取到第 offset + rows条数据之后，丢弃前面offset条记录，返回剩余的rows条记录。
比如以下sql：
select * from t30 order by id limit 10000, 10;
这样总共会扫描10010条。
如果查询的offset很大，避免直接使用offset，而是通过id到聚集索引中检索查找。
select * from t30 where id > 10000 limit 10;
当然，这也是会有问题的，如果id中间产生了非连续的记录，这样定位就不准确了。写到这里，篇幅有点长了，最后这个问题留给大家思考，感兴趣的朋友可以进一步思考探讨与延伸。
这篇文章的内容就差不多介绍到这里了，能够阅读到这里的朋友真的是很有耐心，为你点个赞。
本文为arthinking基于相关技术资料和官方文档撰写而成，确保内容的准确性，如果你发现了有何错漏之处，烦请高抬贵手帮忙指正，万分感激。
大家可以关注我的博客：itzhai.com 获取更多文章，我将持续更新后端相关技术，涉及JVM、Java基础、架构设计、网络编程、数据结构、数据库、算法、并发编程、分布式系统等相关内容。
如果您觉得读完本文有所收获的话，可以关注我的账号，或者点赞吧，码字不易，您的支持就是我写作的最大动力，再次感谢！
关注我的公众号，及时获取最新的文章。
更多文章
本文作者： arthinking
版权声明：
BY-NC-SA许可协议：创作不易，如需转载，请务必附加上博客链接，谢谢！
https://zhuanlan.zhihu.com/p/54378839. Retrieved from https://zhuanlan.zhihu.com/p/54378839 ??
8.2.1.14 ORDER BY Optimization. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html ??
8.8.2 EXPLAIN Output Format. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/explain-output.html ??
Batched Key Access: a Significant Speed-up for Join Queries. Retrieved from https://conferences.oreilly.com/mysql2008/public/schedule/detail/582 ??
Batched Key Access Joins. Retrieved from http://underpop.online.fr/m/mysql/manual/mysql-optimization-bka-optimization.html ??
[Hash join in MySQL 8. MySQL Server Blog. Retrieved from https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does) ??
MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS. Retrieved from https://www.guru99.com/joins.html ??
How the SQL join actually works?. Retrieved from https://stackoverflow.com/questions/34149582/how-the-sql-join-actually-works ??
13.2.9 SELECT Statement. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/select.html ??
8.2.1.18 DISTINCT Optimization. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/distinct-optimization.html ?? ??
Subquery Optimizer Hints. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-subquery ??
高性能MySQL第3版[M]. 电子工业出版社, 2013-5:239. ??
8.2.2.1 Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/semijoins.html ??
FirstMatch Strategy. Retrieved from https://mariadb.com/kb/en/firstmatch-strategy/ ??
DuplicateWeedout Strategy. Retrieved from https://mariadb.com/kb/en/duplicateweedout-strategy/ ??
LooseScan Strategy. Retrieved from https://mariadb.com/kb/en/loosescan-strategy/ ??
Semi-join Materialization Strategy. Retrieved from https://mariadb.com/kb/en/semi-join-materialization-strategy/ ??
Switchable Optimizations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html ??
8.2.2.3 Optimizing Subqueries with the EXISTS Strategy. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html ??
EXISTS-to-IN Optimization. Retrieved from https://mariadb.com/kb/en/exists-to-in-optimization/ ??
原文：https://www.cnblogs.com/arthinking/p/13205303.html