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

发布于: 21 小时前
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 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条记录的idd字段,放入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

更详细的介绍,可以参考:

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] ...
}

这里我们重点关注下这两个:

  • 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[^13]

在大多数情况下,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,该标识即标识用到了这种优化[^13]:

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、子查询的优化

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

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

  • Semijoin,半连接转换,把子查询sql自动转换为semijion;

  • Materialization,子查询物化;

  • EXISTS策略,in转exists;

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

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

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

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

in的效率真的这么慢吗?

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

7.2.1、Semijoin

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

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

半连接通常用于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子句要求子查询仅从studentclass表返回唯一的classnum。在这种情况下,查询会自动优化为使用半联接。

如果使用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做了什么优化: