相信大家看过无数的MySQL调优经验贴了，会告诉你各种调优手段，如：



• 避免 select *；

• join字段走索引；

• 慎用in和not in，用exists取代in；

• 避免在where子句中对字段进行函数操作；

• 尽量避免更新聚集索引；

• group by如果不需要排序，手动加上 order by null；

• join选择小表作为驱动表；

• order by字段尽量走索引...



其中有些手段也许跟随者MySQL版本的升级过时了。我们真的需要背这些调优手段吗？我觉得是没有必要的，在掌握MySQL存储架构和SQL执行原理的情况下，我们就很自然的明白，为什么要提议这么优化了，甚至能够发现别人提的不太合理的优化手段。



在 洞悉MySQL底层架构：游走在缓冲与磁盘之间 这篇文章中，我们已经介绍了MySQL的存储架构，详细对你在MySQL存储、索引、缓冲、IO相关的调优经验中有了一定的其实。



本文，我们重点讲解常用的SQL的执行原理，从执行原理，以及MySQL内部对SQL的优化机制，来分析SQL要如何调优，理解为什么要这样...那样...那样...调优。





如果没有特别说明，本文以MySQL5.7版本作为讲解和演示。



阅读完本文，您将了解到：



• COUNT： MyISAM和InnoDB存储引擎处理count的区别是什么？

• COUNT： count为何性能差？

• COUNT： count有哪些书写方式，怎么count统计会快点？

• ORDER BY： order by语句有哪些排序模式，以及每种排序模式的优缺点？

• ORDER BY： order by语句会用到哪些排序算法，在什么场景下会选择哪种排序算法

• ORDER BY： 如何查看和分析sql的order by优化手段(执行计划 + OPTIMIZER_TRACE日志)

• ORDER BY： 如何优化order by语句的执行效率？(思想：减小行查询大小，尽量走索引，能够走覆盖索引最佳，可适当增加sort buffer内存大小)

• JOIN： join走索引的情况下是如何执行的？

• JOIN： join不走索引的情况下是如何执行的？

• JOIN： MySQL对Index Nested-Loop Join做了什么优化？(MMR，BKA)

• JOIN： BNL算法对缓存会产生什么影响？有什么优化策略？

• JOIN： 有哪些常用的join语句？

• JOIN： 针对join语句，有哪些优化手段？

• UNION： union语句执行原理是怎样的？

• UNION： union是如何去重的？

• GROUP BY： group by完全走索引的情况下执行计划如何？

• GROUP BY： 什么情况下group by会用到临时表？什么情况下会用到临时表+排序？

• GROUP BY： 对group by有什么优化建议？

• DISTINCT： distinct关键词执行原理是什么？

• 子查询： 有哪些常见的子查询使用方式？

• 子查询： 常见的子查询优化有哪些？

• 子查询： 真的要尽量使用关联查询取代子查询吗？

• 子查询：in 的效率真的这么慢吗？

• 子查询： MySQL 5.6之后对子查询做了哪些优化？(SEMIJOIN，Materializatioin，Exists优化策略)

• 子查询： Semijoin有哪些优化策略，其中Materializatioin策略有什么执行方式，为何要有这两种执行方式？

• 子查询： 除了in转Exists这种优化优化，MariaDB中的exists转in优化措施有什么作用？





1、count



存储引擎的区别



• MyISAM引擎每张表中存放了一个meta信息，里面包含了row_count属性，内存和文件中各有一份，内存的count变量值通过读取文件中的count值来进行初始化。[^1]但是如果带有where条件，还是必须得进行表扫描



• InnoDB引擎执行count()的时候，需要把数据一行行从引擎里面取出来进行统计。



下面我们介绍InnoDB中的count()。

count中的一致性视图



InnoDB中为何不像MyISAM那样维护一个row_count变量呢？



前面 洞悉MySQL底层架构：游走在缓冲与磁盘之间 一文我们了解到，InnoDB为了实现事务，是需要MVCC支持的。MVCC的关键是一致性视图。一个事务开启瞬间，所有活跃的事务(未提交)构成了一个视图数组，InnoDB就是通过这个视图数组来判断行数据是否需要undo到指定的版本。



如下图，假设执行count的时候，一致性视图得到当前事务能够取到的最大事务ID DATATRXID=1002，那么行记录中事务ID超过1002都都要通过undo log进行版本回退，最终才能得出最终哪些行记录是当前事务需要统计的：





row1是其他事务新插入的记录，当前事务不应该算进去。所以最终得出，当前事务应该统计row2，row3。



执行count会影响其他页面buffer pool的命中率吗？

我们知道buffer pool中的LRU算法是是经过改进的，默认情况下，旧子列表(old区)占3/8，count加载的页面一直往旧子列表中插入，在旧子列表中淘汰，不会晋升到新子列表中。所以不会影响其他页面buffer pool的命中率。

count(主键)



count(主键)执行流程如下：



• 执行器请求存储引擎获取数据；

• 为了保证扫描数据量更少，存储引擎找到最小的那颗索引树获取所有记录，返回记录的id给到server。返回记录之前会进行MVCC及其可见性的判断，只返回当前事务可见的数据；

• server获取到记录之后，判断id如果不为空，则累加到结果记录中。





count(1)

count(1)与count(主键)执行流程基本一致，区别在于，针对查询出的每一条记录，不会取记录中的值，而是直接返回一个"1"用于统计累加。统计了所有的行。

count(字段)

与count(主键)类似，会筛选非空的字段进行统计。如果字段没有添加索引，那么会扫描聚集索引树，导致扫描的数据页会比较多，效率相对慢点。

count(*)

count(*)不会取记录的值，与count(1)类似。



执行效率对比：count(字段) < count(主键) < count(1)

2、order by

以下是我们本节作为演示例子的表，假设我们有如下表：



索引如下：



对应的idx_d索引结构如下(这里我们做了一些夸张的手法，让一个页数据变小，为了展现在索引树中的查找流程)：





2.1、如何跟踪执行优化

为了方便分析sql的执行流程，我们可以在当前session中开启 optimizer_trace:

SET optimizer_trace='enabled=on';

然后执行sql，执行完之后，就可以通过以下堆栈信息查看执行详情了：

SELECT * FROM informationschema.OPTIMIZERTRACE\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：表明排序缓冲区元组包含排序键值和原始表行的行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 buffer中，使用sort_key, additional_fields或者sort_key, packed_additional_fields模式；

• 如果查询的记录太多，那么会使用sort_key, packed_additional_fields对可变列进行压缩。

2.1.2、排序算法

基于参与排序的数据量的不同，可以选择不同的排序算法：



• 如果排序取的结果很小，小于内存，那么会使用优先级队列进行堆排序；



例如，以下只取了前面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;

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，也就是这里只走了索引。



执行流程如下图所示：



通过idxd索引进行rangescan查找，扫描到4条记录，然后order by继续走索引，已经排好序，直接取前面两条，然后去聚集索引查询完整记录，返回最终需要的字段作为查询结果。这个过程只需要借助索引。





如何查看和修改sort buffer大小？



我们看一下当前的sort buffer大小：



可以发现，这里默认配置了sort buffer大小为512k。



我们可以设置这个属性的大小：

SET GLOBAL sortbuffersize = 32*1024;

或者

SET sortbuffersize = 32*1024;



下面我们统一把sort buffer设置为32k

SET sort_buffer_size = 32*1024; 

2.3、排序算法案例

2.3.1、使用优先级队列进行堆排序



如果排序取的结果很小，并且小于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"
}



发现这里是用到了优先级队列进行排序。排序模式是：sortkey, additionalfields，即先回表查询完整记录，把排序需要查找的所有字段都放入sort buffer进行排序。



所以这个执行流程如下图所示：



1. 通过where条件a=3扫描到8520条记录；

2. 回表查找记录；

3. 把8520条记录中需要的字段放入sort buffer中；

4. 在sort buffer中进行堆排序；

5. 在排序好的结果中取limit 10前10条，写入net buffer，准备发送给客户端。





2.3.2、内部快速排序



如果排序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进行快速排序。



所以这个执行流程如下图所示：



1. 通过where条件a=1扫描到3条记录；

2. 回表查找记录；

3. 把3条记录中需要的字段放入sort buffer中；

4. 在sort buffer中进行快速排序；

5. 在排序好的结果中取limit 300, 2第300、301条记录，写入net buffer，准备发送给客户端。





2.3.3、外部归并排序

当参与排序的数据太多，一次性放不进去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参数指定）



其流程如下图所示：



2.4、排序模式案例

2.4.1、sortkey, additionalfields模式

sort_key, additional_fields，排序缓冲区元组包含排序键值和查询所需要的列（先回表取需要的数据，存入排序缓冲区中），排序后直接从缓冲区元组取数据，无需再次回表。



上面 2.3.1、2.3.2节的例子都是这种排序模式，就不继续举例了。

2.4.2、<sortkey, packedadditional_fields>模式

sort_key, packed_additional_fields：类似上一种形式，但是附加的列(如varchar类型)紧密地打包在一起，而不是使用固定长度的编码。



上面2.3.3节的例子就是这种排序模式，由于参与排序的总记录大小太大了，因此需要对附加列进行紧密地打包操作，以节省内存。

2.4.3、<sort_key, rowid>模式

前面我们提到，选择哪种排序模式，与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模式，在这个模式下，执行流程如下：



1. where条件a=3扫描到8520条记录；

2. 回表查找记录；

3. 找到这8520条记录的id和d字段，放入sort buffer中进行堆排序；

4. 排序完成后，取前面10条；

5. 取这10条的id回表查询需要的a，b，c，d字段值；

6. 依次返回结果给到客户端。





可以发现，正因为行记录太大了，所以sort buffer中只存了需要排序的字段和主键id，以时间换取空间，最终排序完成，再次从聚集索引中查找到所有需要的字段返回给客户端，很明显，这里多了一次回表操作的磁盘读，整体效率上是稍微低一点的。

2.5、order by优化总结

根据以上的介绍，我们可以总结出以下的order by语句的相关优化手段：



• order by字段尽量使用固定长度的字段类型，因为排序字段不支持压缩；

• order by字段如果需要用可变长度，应尽量控制长度，道理同上；

• 查询中尽量不用用select *，避免查询过多，导致order by的时候sort buffer内存不够导致外部排序，或者行大小超过了max_length_for_sort_data导致走了sort_key, rowid排序模式，使得产生了更多的磁盘读，影响性能；

• 尝试给排序字段和相关条件加上联合索引，能够用到覆盖索引最佳。

3、join

为了演示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[^7] 提到：MySQL使用Nested-Loop Loin算法处理所有的关联查询。使用这种算法，意味着这种执行模式：



• 从第一个表中读取一行，然后在第二个表、第三个表...中找到匹配的行，以此类推；

• 处理完所有关联的表后，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：

select * from t30 straight_join t31 on t30.a=t31.a;



查看执行计划：



可以发现：



• t30作为驱动表，t31作为被驱动表；

• 通过a字段关联，去t31表查找数据的时候用到了索引。



该sql语句的执行流程如下图：



1. 首先遍历t30聚集索引；

2. 针对每个t30的记录，找到a的值，去t31的idx_a索引中找是否存在记录；

3. 如果存在则拿到t30对应索引记录的id回表查找完整记录；

4. 分别取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的优化

我们可以发现，以上流程，每次从驱动表取一条数据，然后去被驱动表关联取数，表现为磁盘的随记读，效率是比较低低，有没有优化的方法呢？



这个就得从MySQL的MRR（Multi-Range Read）[^5]优化机制说起了。

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优化，那么流程是这样的：



1. 在idx_a索引中找到a<10的记录；

2. 取前面10条，拿着id去回表查找完整记录，这里回表查询是随机读，效率较差；

3. 取到的结果通过net buffer返回给客户端。





使用了MRR优化之后，这个执行流程是这样的：



1. 在idx_abc索引中找到a<10的记录；

2. 取10条，把id放入read rnd buffer;

3. read rnd buffer中的id排序；

4. 排序之后回表查询完整记录，id越多，排好序之后越有可能产生连续的id，去磁盘顺序读；

5. 查询结果写入net buffer返回给客户端；





3.1.2.2、Batched Key Acces

与Multi-Range Read的优化思路类似，MySQL也是通过把随机读改为顺序读，让Index Nested-Loop Join提升查询效率，这个算法称为Batched Key Access(BKA)[^6]算法。



我们知道，默认情况下，是扫描驱动表，一行一行的去被驱动表匹配记录。这样就无法触发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)。



执行流程如下：



1. 把驱动表的数据批量放入join buffer中；

2. 在join buffer中批与被驱动表的辅助索引匹配结果，得到一个结果集；

3. 把上一步的结果集批量提交给引擎的MRR接口；

4. MRR接口处理同上一节，主要进行了磁盘顺序读的优化；

5. 组合输出最终结果，可以看到，这里的结果与没有开启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；



该语句的执行流程如下图：

1. t30驱动表中的数据分批(分块)存入join buffer，如果一次可以全部存入，则这里会一次性存入；

2. t31被驱动表中扫描记录，依次取出与join buffer中的记录对比(内存中对比，快)，判断是否满足c相等的条件；

3. 满足条件的记录合并结果输出到net buffer中，最终传输给客户端。



然后清空join buffer，存入下一批t30的数据，重复以上流程。



显然，每批数据都需要扫描一遍被驱动表，批次越多，扫描越多，但是内存判断总次数是不变的。所以总批次越小，越高效。所以，跟上一个算法一样，驱动表越小，复杂度越低，越能提高搜索效率。

3.2.2、BNL问题

在 洞悉MySQL底层架构：游走在缓冲与磁盘之间 一文中，我们介绍了MySQL Buffer Pool的LRU算法，如下：





默认情况下，同一个数据页，在一秒钟之后再次访问，那么就会晋升到新子列表(young区)。



恰巧，如果我们用到了BNL算法，那么分批执行的话，就会重复扫描被驱动表去匹配每一个批次了。



考虑以下两种会影响buffer pool的场景

• 如果这个时候join扫描了一个很大的冷表，那么在join这段期间，会持续的往旧子列表(old区)写数据页，淘汰队尾的数据页，这会影响其他业务数据页晋升到新子列表，因为很可能在一秒内，其他业务数据就从旧子列表中被淘汰掉了；

• 而如果这个时候BNL算法把驱动表分为了多个批次，每个批次扫描匹配被驱动表，都超过1秒钟，那么这个时候，被驱动表的数据页就会被晋升到新子列表，这个时候也会把其他业务的数据页提前从新子列表中淘汰掉。

3.2.3、BNL问题解决方案

3.2.3.1、调大 joinbuffersize

针对以上这种场景，为了避免影响buffer pool，最直接的办法就是增加join_buffer_size的值，以减少对被驱动表的扫描次数。

3.2.3.2、把BNL转换为BKA

我们可以通过把join的条件加上索引，从而避免了BNL算法，转而使用BKA算法，这样也可以加快记录的匹配速度，以及从磁盘读取被驱动表记录的速度。

3.2.3.3、通过添加临时表

有时候，被驱动表很大，但是关联查询又很少使用，直接给关联字段加索引太浪费空间了，这个时候就可以通过把被驱动表的数据放入临时表，在临时表中添加索引的方式，以达成3.2.3.2的优化效果。

3.2.3.4、使用hash join

什么是hash join呢，简单来说就是这样的一种模型：



把驱动表满足条件的数据取出来，放入一个hash结构中，然后把被驱动表满足条件的数据取出来，一行一行的去hash结构中寻找匹配的数据，依次找到满足条件的所有记录。



一般情况下，MySQL的join实现都是以上介绍的各种nested-loop算法的实现，但是从MySQL 8.0.18[^8]开始，我们可以使用hash join来实现表连续查询了。感兴趣可以进一步阅读这篇文章进行了解：Hash join in MySQL 8 | MySQL Server Blog

3.3、各种join

我们在平时工作中，会遇到各种各样的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





更详细的介绍，可以参考：



• MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS[^10]

• How the SQL join actually works?[^11]

3.3、join使用总结

• join优化的目标是尽可能减少join中Nested-Loop的循环次数，所以请让小表做驱动表；

• 关联字段尽量走索引，这样就可以用到Index Nested-Loop Join了；

• 如果有order by，请使用驱动表的字段作为order by，否则会使用 using temporary；

• 如果不可避免要用到BNL算法，为了减少被驱动表多次扫描导致的对Buffer Pool利用率的影响，那么可以尝试把 joinbuffersize调大；

• 为了进一步加快BNL算法的执行效率，我们可以给关联条件加上索引，转换为BKA算法；如果加索引成本较高，那么可以通过临时表添加索引来实现；

• 如果您使用的是MySQL 8.0.18，可以尝试使用hash join，如果是较低版本，也可以自己在程序中实现一个hash join。

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执行计划如下图：

执行流程如下：



1. 从t30表查询出结果，直接写出到net buffer，传回给客户端；

2. 从331表查询出结果，直接写出到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执行计划如下图：



执行流程如下：



1. 从t30查询出记录，写入到临时表；

2. 从t30查询出记录，写入临时表，在临时表中通过唯一索引去重；

3. 把临时表的数据通过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索引给删掉，执行以下sql：

select c, count(*) from t30 group by c order by null;



为了避免排序，所以我们这里添加了 order by null，表示不排序。



执行计划如下：



可以发现，这里用到了内存临时表。其执行流程如下：



1. 扫描t30聚集索引；

2. 建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加；

3. 把最后累加得到的临时表返回给客户端。





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排序模式
    }
  }
]



通过日志也可以发现，这里用到了中间临时表，由于没有limit限制条数，这里没有用到优先级队列排序，这里的排序模式为sort_key, rowid。其执行流程如下：



1. 扫描t30聚集索引；

2. 建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加；

3. 把得到的临时表放入sort buffer进行排序，这里通过rowid进行排序；

4. 通过排序好的rowid回临时表查找需要的字段，返回给客户端。





临时表是存放在磁盘还是内存？

tmptablesize 参数用于设置内存临时表的大小，如果临时表超过这个大小，那么会转为磁盘临时表：



可以通过以下sql设置当前session中的内存临时表大小：

SET tmptablesize = 102400;

5.5、直接排序

查看官方文档的 SELECT Statement[^12]，可以发现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] ...
}