SQL性能优化-算子与计划(更新中)

再来二十串！

已于 2023-04-25 23:31:19 修改

阅读量577

点赞数

分类专栏： SQL性能优化文章标签： sql 性能优化数据库

于 2023-04-09 19:00:40 首次发布

本文链接：https://blog.csdn.net/Carrot_L/article/details/130043706

版权

SQL性能优化专栏收录该内容

4 篇文章 1 订阅

订阅专栏

1. SQL查询计划

计划就是完成一个SQL语义描述的功能的一套完整方案。包括数据的扫描、连接、投影、排序等一系列操作步骤。
其中每一个步骤，都有一类算子来完成。一个个算子节点互相组合，通常是以树的形式，最终形成一个计划树，如下：

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=505947.09..505948.28 rows=100 width=45)
   ->  Streaming(type: LOCAL GATHER dop: 1/12)  (cost=505947.09..505948.28 rows=100 width=45)
         ->  Limit  (cost=505947.09..505947.51 rows=100 width=45)
               ->  HashAggregate  (cost=505947.09..507538.32 rows=377081 width=45)
                     Group By Key: l_orderkey
                     Filter: (sum((sum(l_quantity))) > 50::numeric)
                     ->  Streaming(type: LOCAL REDISTRIBUTE dop: 12/12)  (cost=351975.84..504376.87 rows=377081 width=45)
                           ->  HashAggregate  (cost=351975.84..488549.77 rows=377081 width=45)
                                 Group By Key: l_orderkey
                                 ->  Seq Scan on lineitem  (cost=0.00..170015.14 rows=59983266 width=13)
 Total runtime: 28391.243 ms
(12 rows)

在上面的例子中，共有7个算子，每一层->缩进都表示进入了下一个算子，观察每一个算子，最开始的是算子名称，之后括号内的则是算子的估算代价、估算行数、估算宽度。换行之后则可能有一些算子的细节信息，如过滤条件、输出投影等，不同explain的模式下有不同的输出。

通常实现一个SQL的计划有很多种，不同的计划，所用的时间、空间十分不一致，经常会是不同数量级的差异。
随着SQL的复杂程度增大，计划空间也迅速变大，通过数学方法选出来最优计划是一个NP问题。
数据库优化器会尽可能地通过各种方法来选出一个“最优计划”，目前常见的优化器有RBO、CBO、ABO等，CBO的递推方案也有自底向上和自顶向下等多种方法。
且同样的计划，在不同的环境上，表现也可能不尽相同，因此优化器内会有很多参数，来对结果进行调整。

因此对于优化器生成的计划，我们也很有调整的必要。

2. 常见算子

算子就是某一个操作的计算单元，算子的输入和输出都是元组，一个个算子以不同的顺序叠加就是计划。

2.1 扫描算子

扫描算子主要有顺序扫描、索引(Only)扫描、位图扫描、以及一些用的比较少的通用表表达式扫描、外表扫描、函数扫描、值扫描等。

在这里仅描述最常见的三种扫描算子。

2.1.1 顺序扫描(SeqScan)

    Query Plan
-----------------------
SeqScan on tb1 
    Output : a, b, c, d + 1
    Filter : a > 1

也可以叫做全表扫描，即将一整张表的全部数据，从头到尾进行遍历。每遍历一条元组，都会进行Where的计算与投影计算。

顺序扫描是最基础的扫描方式。

一般提升顺序扫描性能的直接方法有：

扩大shared_buffer：扩大buffer是万能的。
开启sync_scan同步扫描：在多个会话都在查询，且buffer比较小不足以装下整个表的情况下，可以提升buffer命中率，减小IO。

2.1.2 索引(Only)扫描(IndexScan、IndexOnlyScan)

    Query Plan
-----------------------
IndexScan on idx1 of tb1
    Output : a, b, c, d
    Index Cond: a = 5
    Filter : b > 1

索引扫描：先查找索引，找到目标行的位置，然后回表直接找到对应的完整的行。
索引Only扫描：当需要的行在索引中能全部找到时，仅需要查表，不需要回表。

索引扫描即借助索引进行扫描，根据是否需要回表，可以分为上述两种。

通常索引扫描通常用在选择率很小的查询之中。

另外索引扫描的一个很重要的特征是，它所扫描得到的数据必然是按照索引排好序的，因此可以基于此特征，省去一些需要排序的操作。

而IndexOnlyScan则又可以在一些不需要回表的场景，代替全表扫描，例如:select count(*)

2.1.3 位图扫描(BitmapScan)

    Query Plan
-----------------------
...

位图扫描算是基于索引扫描的一个变种，通过索引找到元组位置并记录到一个位图之中，之后根据位图在表中查找到元组。

这样可以解决索引扫描中随机读带来的不必要性能损耗的问题，但也有一个新问题，若数据量还是比较大，则位图所需要的内存空间也会很大，因此此时会将位图按照页为单位进行构造，仅记录哪些页上有目标元组，扫上来页面后，进行一次ReCheck操作，进行二次筛选。

同时位图的构造流程可以通过多个索引来同时构造，例如 where a > 5 and b < 6，两个条件对应两个不同的索引，那么可以分别构造两个位图，然后两个位图做and操作即可。

位图扫描基于顺序扫描和索引扫描之间，既不需要像顺序扫描一样扫描全部数据，也不会有索引扫描的随机读进而放大IO的情况，但其输出也是无序的，适用于选择率中等的场景。

2.1.4 扫描算子的调整与选择

根据上面的几个算子的特点描述可知，在选择扫描算子的时候，可以参考以下几个因素：

是否有可用索引
没有索引，或者索引键在SQL中并没有能使用到的地方，那就只能走顺序扫描，且没有调整空间。
查询所用列
若查询所用到的列在某个索引中能够全部找到，那么可以考虑使用IndexOnlyScan。
选择率
通常根据where条件的选择率来进行选择。若经过where过滤后，剩余的数据非常少，则可以选择索引扫描；若剩余数据中等，则可以选择位图扫描；若剩余数据很多，则选择顺序扫描。
其中少、中、多的边界并非一个绝对值，且与Buffer命中率、IO性能是很相关的，进而与实际的行宽度、硬件性能等都有关系。因此这里有一些参数可用于对这个边界进行调整：

random_page_cost : 随机扫描的每页的代价因子，越小越倾向于选择索引扫描。

seq_page_cost : 顺序扫描一个页面的代价因子。

index_page_cost : 每条索引元组的CPU内计算的代价。
…
索引键是否用于连接条件
主要可以考虑可以走参数化路径以及归并排序，见下连接算子。
索引键是否可用于分组操作
若SQL中存在例如group by, distinct等需要分组来完成的操作，若分组键可以在索引中全部找到，则可以考虑使用索引扫描，进而在分组操作中使用排序分组的算法，见下聚集算子。
索引键是否可用于排序操作
主要是利用索引扫描的有序性，规避掉排序操作，见下排序算子。
是否在limit场景可以用于规避掉启动代价
见下Limit算子。

2.2 连接算子

2.2.1 循环嵌套连接(NestLoop Join)

...

循环嵌套连接，即最朴素的连接方法，双重for循环。
其中计划中第一个算子表示驱动表，第二个算子表示被驱动表。计算方法便是，驱动表中每取一行，都会遍历被驱动表进行连接。由参数enable_nestloop进行开关控制。
被驱动表的数据通常可能会被临时物化，因此常见一个物化算子，这样可以减少被驱动表的条件过滤、可见性判断过滤等。

这种算法的优势是适用范围最大，启动快，但时间复杂度高，为O(n^2)，适合数据量小的场景。

...

但在有这么一种场景，若连接条件上有可用索引，则可以通过这个索引进行一些算法的优化。其计算过程为：在驱动表中获取一行数据，将这行数据中的值放入连接条件中，于是连接条件便可以作为一个查询条件，去索引中直接查询另一个表对应的数据，查出来便可以直接进行连接。这个参数化连接条件进而进行索引扫描的算法也被称为：参数化路径。

区分是不是参数化路径的一个显著特征就是看连接条件有没有下推到被驱动表中，例如上述计划。通过参数enable_nestloop_indexscan可以控制参数化路径的开启。

由于对于被驱动表的扫描是精确的索引扫描，因此其时间复杂度直接降低到 O(nlogn)，但是也引入了索引扫描的随机读的缺点，因此两个表的连接数据尽量应该一一连接，而不应该多多连接。

对于循环嵌套来说，除了内表物化时之外，不存在启动代价，因此也可以参与limit的一些优化，详见下limit算子。

2.2.2 归并连接(MergeJoin)

...

归并排序，即对两个有序的数组进行连接，且有序是按照连接键有序的。

单纯的归并操作可以说时间复杂度是o(m + n)的，且空间复杂度也可以认为是o(1)的。
但是归并的前提是有序，因此在mergejoin之下的两个表经常带着Sort算子进行排序，或者是index的扫描自带有序属性。

当然，归并连接的输入是有序的，输出也是有序的。

2.2.3 哈希连接(HashJoin)

...

哈希连接，即用连接的表中的一张构造出一个哈希表，然后遍历另外一张表，在哈希表中寻找可连接的数据并进行连接。

哈希表的插入和查找我们暂且认为其时间复杂度为o(1)，那么整个哈希连接的时间复杂度便是o(m + n)。

但是哈希连接需要使用其中一个表而构造出一个哈希表，相对于其他的连接方案，要消耗额外的空间资源，因此在表的选择上，通常会选择数据量较小的表进行构造哈希表。

哈希连接是最常用的。

2.2.4 连接算子的选择

在不考虑索引时，简单的可以直接按照数据量大小进行选择。与扫描算子类似，数据量比较小时选择NestLoop，中等的时候选择MergeJoin，数据量大的时候选择HashJoin。
需要注意的是我们在这里说的表指的是用于连接的两个数据集，即经过扫描、过滤之后的数据，而非简单理解的表。

当有索引的时候，可以考虑能否使用 参数化路径 、索引扫描 + 归并连接 进行优化。

另外还有一些与连接相关的优化器概念，可以在SQL层的角度上进行思考：

子查询消除
谓词下推

2.3 聚集算子

2.3.1 直接聚集(Plain Aggregate)

...

直接聚集。

2.3.2 排序聚集(Sort Group Aggregate)

...

排序后聚集。
利用索引的有序规避掉排序

2.3.3 哈希聚集(Hash Aggregate)

...

万用的聚集算法

2.3.4 聚集算子的选择

PlainAgg处理不带分组的聚集，这个没有什么好分的。
有序无序

2.4 排序算子

内排
外排
top k

2.5 Limit算子

limit算子通常没有什么好调的，其最大的差别在于是否可以采用某些算子的特征来规避掉一些带有物化属性算子的‘获取并卡住所有数据’的行为，即启动代价。常见的是利用索引扫描的有序性来取代排序操作。

例如对于以下三种计划

Table: tb1(a int, b int, c int)； row 1000W，distinct a row 300W
Index：idx1 on tb1(a)
SQL: select sum(b) from tb1 group by a;

        Plan1
----------------------
HashAgg
    -> SeqScan on tb1

        Plan2
----------------------
GroupAgg
    -> Sort
        -> SeqScan on tb1

        Plan3
----------------------
GroupAgg
    -> IndexScan on idx1 of tb1

不加Limit时，通常会选用HashAgg的方案，且这种方案会比GroupAgg快上非常多，因为GroupAgg无论下层采用排序还是索引扫描，其代价都非常重。

但加了limit之后就变了。

Table: tb1(a int, b int, c int)； row 1000W，distinct a row 300W
Index：idx1 on tb1(a)
SQL: select sum(b) from tb1 group by a limit 10;

        Plan1
----------------------
Limit 
    -> HashAgg
        -> SeqScan on tb1

        Plan2
----------------------
Limit 
    -> GroupAgg
        -> Sort
            -> SeqScan on tb1

        Plan3
----------------------
Limit 
    -> GroupAgg
        -> IndexScan on idx1 of tb1

方案1里需要扫描所有数据并做哈希；方案2里的排序操作也需要扫描所有的数据。可这两种都存在“读取并卡住所有数据”的情况，但我们limit需要的仅仅是前10条，做了非常多的无用功。

但方案3内没有上述情况，仅需要精准的扫描前10个分组的数据即可，会变得非常快。

当然对于前两种方案，也并非无药可救。openGauss与PG都是用的是堆表进行存储，而堆表数据是无序的，同时limit的语义并没有对返回的值有某些方面的约束，因此我们可以随便选数据进行返回，其实包括上述三个计划，其返回值都并不是完全一样的。因此在某些方案下，可以将limit下推进聚集之中，例如仅对10个分组做哈希，采用类似top k排序算法仅排前10个分组出来。不过虽然这些方案下对聚集的计算是进行了剪枝，但无论如何，全表扫描都是逃不掉的（且这个特性目前og、pg都还没有）。

上述这个情况在CBO中通常使用“启动代价”的机制来描述，存在启动代价的算子通常有排序、哈希两个操作，例如：sort、hashjoin、hashagg、mergejoin + sort、groupagg + sort、unique等算子，当然严格来说索引扫描也有启动代价，但很小，暂且忽略不计。
但对于启动代价的计算，误差往往会很大，因此对于这种场景通常可以进行一些调整尝试，一旦成功，就可能是好几个数量级的提升。

再来二十串！

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
SQL性能优化-算子与计划(更新中)

这样可以解决索引扫描中随机读带来的不必要性能损耗的问题，但也有一个新问题，若数据量还是比较大，则位图所需要的内存空间也会很大，因此此时会将位图按照页为单位进行构造，仅记录哪些页上有目标元组，扫上来页面后，进行一次ReCheck操作，进行二次筛选。但加了Limit情况就变了，采用HashAgg需要扫描所有数据并做哈希，采用GroupAgg+Sort的方案也需要扫描所有的数据并排序，但是采用GroupAgg+IndexScan的方案时我们仅需要精准的扫描前10个分组的数据即可。
复制链接

扫一扫