SQL运行内幕：从执行原理看调优的本质

相信大家看过无数的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 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(主键)执行流程如下：

  • 执行器请求存储引擎获取数据；
  • 为了保证扫描数据量更少，存储引擎找到最小的那颗索引树获取所有记录，返回记录的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 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：表明排序缓冲区元组包含排序键值和原始表行的行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，也就是这里只走了索引。

执行流程如下图所示：

通过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;

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"
}

发现这里是用到了优先级队列进行排序。排序模式是：sort_key, additional_fields，即先回表查询完整记录，把排序需要查找的所有字段都放入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、sort_key, additional_fields模式

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

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

2.4.2、<sort_key, packed_additional_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[3] 提到：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）[4]优化机制说起了。

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 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)。

执行流程如下：

  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、调大 join_buffer_size

针对以上这种场景，为了避免影响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[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.)

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

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

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。

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回临时表查找需要的字段，返回给客户端。

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

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的数据量很大，直接选用磁盘临时表，但磁盘临时表存储效率不高，最终优化器使用数组排序的方式来完成这个查询。（当然，这个例子实际的结果集并不大，只是作为演示用）

其执行结果如下：

  1. 扫描t30表，逐行的把c字段放入sort buffer；
  2. 在sort buffer中对c字段进行排序，得到一个排序好的c数组；
  3. 遍历这个排序好的c数组，统计结果并输出。

5.4、group by 优化建议

  • 尽量让group by走索引，能最大程度的提高效率；
  • 如果group by结果不需要排序，那么可以加上group by null，避免进行排序；
  • 如果group by的数据量很大，可以使用SQL_BIG_RESULT修饰符，提醒优化器应该使用排序算法得到group的结果。

6、distinct[10]

在大多数情况下，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分组之后不进行排序。

6.1、Extra中的distinct

在一个关联查询中，如果您只是查询驱动表的列，并且在驱动表的列中声明了distinct关键字，那么优化器会进行优化，在被驱动表中查找到匹配的第一行时，将停止继续扫描。如下SQL：

explain select distinct t30.a  from t30, t31 where t30.c=t30.c;

执行计划如下，可以发现Extra列中有一个distinct，该标识即标识用到了这种优化[10:1]：

7、子查询

首先，我们来明确几个概念：

子查询：可以是嵌套在另一个查询(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);

子查询的用法比较多，我们先来列举下有哪些子查询的使用方法。

7.1、子查询的使用方法

7.1.1、where中的子查询

7.1.1.1、比较运算符

可以使用比较运算法，例如=，>，<将子查询返回的单个值与where子句表达式进行比较，如

查找学生选择的编号最大的课程信息：

SELECT class.* FROM class WHERE class.class_num = ( SELECT MAX(class_num) FROM student_class );

7.1.1.2、in和not in

如果子查询返回多个值，则可以在WHERE子句中使用其他运算符，例如IN或NOT IN运算符。如

查找学生都选择了哪些课程：

SELECT class.* FROM class WHERE class.class_num IN ( SELECT DISTINCT class_num FROM student_class );

7.1.2、from子查询

在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。该表称为派生表或实例化子查询。如 查找最热门和最冷门的课程分别有多少人选择：

SELECT max(count), min(count) FROM (SELECT class_num, count(1) as count FROM student_class group by class_num) as t1;

7.1.3、关联子查询

前面的示例中，您注意到子查询是独立的。这意味着您可以将子查询作为独立查询执行。

与独立子查询不同，关联子查询是使用外部查询中的数据的子查询。换句话说，相关子查询取决于外部查询。对于外部查询中的每一行，对关联子查询进行一次评估。

下面是比较运算符中的一个关联子查询。

查找每门课程超过平均分的学生课程记录：

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可能都不一样。

7.1.3.1、exists和not exists

当子查询与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
)

7.2、子查询的优化

上面我们演示了子查询的各种用法，接下来，我们来讲一下子查询的优化[11]。

子查询主要由以下三种优化手段：

  • Semijoin，半连接转换，把子查询sql自动转换为semijion；
  • Materialization，子查询物化；
  • EXISTS策略，in转exists；

其中Semijoin只能用于IN，= ANY，或者EXISTS的子查询中，不能用于NOT IN，<> ALL，或者NOT EXISTS的子查询中。

下面我们做一下详细的介绍。

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

在《高性能MySQL》[12]一书中，提到：优化子查询最重要的建议就是尽可能使用关联查询代替，但是，如果使用的是MySQL 5.6或者更新版本或者MariaDB，那么就可以直接忽略这个建议了。因为这些版本对子查询做了不少的优化，后面我们会重点介绍这些优化。

in的效率真的这么慢吗？

在MySQL5.6之后是做了不少优化的，下面我们就逐个来介绍。

7.2.1、Semijoin

Semijoin[13]，半连接，所谓半连接，指的是一张表在另一张表栈道匹配的记录之后，返回第一张表的记录。即使右边找到了几条匹配的记录，也最终返回左边的一条。

所以，半连接非常适用于查找两个表之间是否存在匹配的记录，而不关注匹配了多少条记录这种场景。

半连接通常用于IN或者EXISTS语句的优化。

7.2.1.1、优化场景

上面我们讲到：接非常适用于查找两个表之间是否存在匹配的记录，而不关注匹配了多少条记录这种场景。

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有哪些优化策略。

7.2.1.2、Semijoin优化策略

MySQL支持5中Semijoin优化策略，下面逐一介绍。

7.2.1.2.1、FirstMatch

在内部表寻找与外部表匹配的记录，一旦找到第一条，则停止继续匹配。

案例 - 统计有学生分数不及格的课程：

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);

执行计划：

执行流程，图比较大，请大家放大观看：

  1. 扫描class表，把class表分批放入join buffer中，分批处理；
  2. 在批次中依次取出每一条记录，在student_class表中扫描查找符合条件的记录，如果找到，则立刻返回，并从该条匹配的class记录取出查询字段返回；
  3. 依次继续扫描遍历。

您也可以去MariaDB官网，查看官方的FirstMatch Strategy[14]解释。

7.2.1.2.2、Duplicate Weedout

将Semijoin作为一个常规的inner join，然后通过使用一个临时表去重。

具体演示案例，参考MariaDB官网：DuplicateWeedout Strategy[15]，以下是官网例子的图示：

可以看到，灰色区域为临时表，通过临时表唯一索引进行去重。

7.2.1.2.3、LooseScan

把内部表的数据基于索引进行分组，取每组第一条数据进行匹配。

具体演示案例，参考MariaDB官网：LooseScan Strategy[16]，以下是官网例子的图示：

7.2.1.4、Materialization[17]

如果子查询是独立的(非关联子查询)，则优化器可以选择将独立子查询产生的结果存储到一张物化临时表中。

为了触发这个优化，我们需要往表里面添加多点数据，好让优化器认为这个优化是有价值的。

我们执行以下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%';

执行流程如下：

  1. 执行子查询：通过where条件从student_class 表中找出符合条件的记录，把所有记录放入物化临时表；
  2. 通过where条件从class表中找出符合条件的记录，与物化临时表进行join操作。

物化表的唯一索引

MySQL会报物化子查询所有查询字段组成一个唯一索引，用于去重。如上面图示，灰色连线的两条记录冲突去重了。

join操作可以从两个方向执行：

  • 从物化表关联class表，也就是说，扫描物化表，去与class表记录进行匹配，这种我们称为Materialize-scan；
  • 从class表关联物化表，也就是，扫描class表，去物化表中查找匹配记录，这种我们称为Materialize-lookup，这个时候，我们用到了物化表的唯一索引进行查找，效率会很快。

下面我们介绍下这两种执行方式。

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%';

执行计划如下：

可以发现：

  • t2表的select_type为MATERIALIZED，这意味着id=2这个查询结果将存储在物化临时表中。并把该查询的所有字段作为临时表的唯一索引，防止插入重复记录；
  • id=1的查询接收一个subquery2的表名，这个表正式我们从id=2的查询得到的物化表。
  • id=1的查询首先扫描t1表，依次拿到t1表的每一条记录，去subquery2执行eq_ref，这里用到了auto_key，得到匹配的记录。

也就是说，优化器选择了对t1(class)表进行全表扫描，然后去物化表进行所以等值查找，最终得到结果。

执行模型如下图所示：

原则：小表驱动大表，关联字段被驱动表添加索引

如果子查询查出来的物化表很小，而外部表很大，并且关联字段是外部表的索引字段，那么优化器会选择扫描物化表去关联外部表，也就是Materialize-scan，下面演示这个场景。

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表索引查询。

7.2.2、Materialization

优化器使用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语句导致外部每一行记录都会执行一次子查询，严重降低了效率。

7.2.3、EXISTS策略

考虑以下的子查询：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL“从外到内”来评估查询。也就是说，它首先获取外部表达式outer_expr的值，然后运行子查询并获取其产生的结果集用于比较。

7.2.3.1、condition push down 条件下推

如果我们可以把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子查询，这样就可以用上非关联资产性的物化优化策略了。

详细可以阅读：EXISTS-to-IN Optimization[20]

7.2.4、总结

总结一下子查询的优化方式：

  • 首先优先使用Semijoin来进行优化，消除子查询，通常选用FirstMatch策略来做表连接；
  • 如果不可以使用Semijoin进行优化，并且当前子查询是非关联子查询，则会物化子查询，避免多次查询，同时这一步的优化会遵循选用小表作为驱动表的原则，尽量走索引字段关联，分为两种执行方式：Materialize-lookup，Materialization-scan。通常会选用哈希索引为物化临时表提高检索效率；
  • 如果子查询不能物化，那就只能考虑Exists优化策略了，通过condition push down把条件下推到exists子查询中，减少子查询的结果集，从而达到优化的目的。

8、limit offset, rows

limit的用法：

limit [offset], [rows]

其中 offset表示偏移量，rows表示需要返回的行数。

offset  limit  表中的剩余数据
 _||_   __||__   __||__
|    | |      | |
RRRRRR RRRRRRRR RRR...
       |______|
          ||
         结果集

8.1、执行原理

MySQL进行表扫描，读取到第 offset + rows条数据之后，丢弃前面offset条记录，返回剩余的rows条记录。

比如以下sql：

select * from t30 order by id limit 10000, 10;

这样总共会扫描10010条。

8.2、优化手段

如果查询的offset很大，避免直接使用offset，而是通过id到聚集索引中检索查找。

  1. 利用自增索引，如：
select * from t30 where id > 10000 limit 10;

当然，这也是会有问题的，如果id中间产生了非连续的记录，这样定位就不准确了。写到这里，篇幅有点长了，最后这个问题留给大家思考，感兴趣的朋友可以进一步思考探讨与延伸。

这篇文章的内容就差不多介绍到这里了，能够阅读到这里的朋友真的是很有耐心，为你点个赞。

本文为arthinking基于相关技术资料和官方文档撰写而成，确保内容的准确性，如果你发现了有何错漏之处，烦请高抬贵手帮忙指正，万分感激。

大家可以关注我的博客：itzhai.com 获取更多文章，我将持续更新后端相关技术，涉及JVM、Java基础、架构设计、网络编程、数据结构、数据库、算法、并发编程、分布式系统等相关内容。

如果您觉得读完本文有所收获的话，可以关注我的账号，或者点赞吧，码字不易，您的支持就是我写作的最大动力，再次感谢！

关注我的公众号，及时获取最新的文章。

更多文章

  • 关注公众号进入会话窗口获取
  • JVM系列专题：公众号发送 JVM

本文作者： arthinking

博客链接： https://www.itzhai.com/database/how-sql-works-understand-the-essence-of-tuning-by-the-execution-principle.html

SQL运行内幕：从执行原理看调优的本质

版权声明： 版权归作者所有，未经许可不得转载，侵权必究！联系作者请加公众号。

References

  1. https://zhuanlan.zhihu.com/p/54378839. Retrieved from https://zhuanlan.zhihu.com/p/54378839 ↩︎

  2. 8.2.1.14 ORDER BY Optimization. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html ↩︎

  3. 8.8.2 EXPLAIN Output Format. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/explain-output.html ↩︎

  4. Batched Key Access: a Significant Speed-up for Join Queries. Retrieved from https://conferences.oreilly.com/mysql2008/public/schedule/detail/582 ↩︎

  5. Batched Key Access Joins. Retrieved from http://underpop.online.fr/m/mysql/manual/mysql-optimization-bka-optimization.html ↩︎

  6. [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) ↩︎

  7. MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS. Retrieved from https://www.guru99.com/joins.html ↩︎

  8. How the SQL join actually works?. Retrieved from https://stackoverflow.com/questions/34149582/how-the-sql-join-actually-works ↩︎

  9. 13.2.9 SELECT Statement. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/select.html ↩︎

  10. 8.2.1.18 DISTINCT Optimization. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/distinct-optimization.html ↩︎ ↩︎

  11. Subquery Optimizer Hints. Retrieved from https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-subquery ↩︎

  12. 高性能MySQL第3版[M]. 电子工业出版社, 2013-5:239. ↩︎

  13. 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 ↩︎

  14. FirstMatch Strategy. Retrieved from https://mariadb.com/kb/en/firstmatch-strategy/ ↩︎

  15. DuplicateWeedout Strategy. Retrieved from https://mariadb.com/kb/en/duplicateweedout-strategy/ ↩︎

  16. LooseScan Strategy. Retrieved from https://mariadb.com/kb/en/loosescan-strategy/ ↩︎

  17. Semi-join Materialization Strategy. Retrieved from https://mariadb.com/kb/en/semi-join-materialization-strategy/ ↩︎

  18. Switchable Optimizations. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html ↩︎

  19. 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 ↩︎

  20. EXISTS-to-IN Optimization. Retrieved from https://mariadb.com/kb/en/exists-to-in-optimization/ ↩︎

posted @ 2020-06-28 22:13  IT宅  阅读(3590)  评论(13)  编辑  收藏  举报