性能调优8:分组聚合 - group by

聚合实际上对数据做分组统计,SQL Server使用两种操作符来实现聚合,流聚合(Stream Aggregation)和哈希聚合(Hash aggration)。流聚合是非阻塞性的,具有流的特性,流聚合操作符;边处理数据,边输出聚合的结果。而哈希聚合是阻塞性的,只要处理完所有的数据,才会输出聚合的结果。

一,流聚合

流聚合要求输入的数据集在group by 即分组列上是有序的,也就是说,流聚合需要排序。分组列的位置和顺序不会影响聚合的结果,因此分组列的排序是任意的。对于索引上的流聚合,由于数据是已经排序的,使用流聚合算法没有排序操作的开销。

流聚合算法是:第一个被读取的数据会创建第一个分组,后续读入的数据都会先和当前的分组匹配,如果匹配,把该行放入到当前的分组中;如果不匹配,创建新的分组,直到所有数据行都处理完成为止,最终对各个分组计算聚合值。

二,哈希聚合

在执行计划中,哈希聚合使用的物理操作符是:Hash Match(Aggregate),实际上,Hash Join也是使用Hash Match作为物理操作符。哈希聚合不需要排序,但是需要授予内存来创建Hash表。优化器倾向于使用哈希聚合来对无序的大表进行聚合操作,哈希聚合的算法:

  • 对于每一个输入行,在group by列上计算哈希值,
  • 检查该行是否映射到hash表中,如果不存在于现有的哈希表,那么把该行插入到哈希表中,创建新的分组;如果存在于现有的哈希表中,把该行插入到现有的分组中。
  • 计算哈希表中的数据,作为最终的结果输出。

哈希聚合使用Hash表来存储各个分组的数据,最后并行计算各个分组中的数据。由于数据是无序的,任何数据行都有可能属于任意一个分组,因此,哈希聚合直到处理完所有的数据行才会输出结果。

Hash聚合在创建哈希表时,需要向系统申请授予内存,当授予内存不足时,需要把哈希表的一部分哈希桶溢出到硬盘的workfiles中。这和Hash Join的内存使用和溢出相同。

三,列存储索引

列存储索引适合于数据仓库中,主要执行大容量数据加载和只读查询,与传统面向行的存储方式相比,使用列存储索引存储可最多提高 10 倍查询性能 ,与使用非压缩数据大小相比,可提供多达 7 倍数据压缩率 。列存储索引使用用“批处理执行模式”的模式,这与行存储使用的逐行数据读取模式对比,性能大幅提升。

列存储索引主要在下面三个特性上提升查询的性能:

  • 行存储使用逐行处理模式,每次只处理一行数据;而列存储索引使用批处理模式,每次处理一批数据行。
  • 行存储是逐行存储(Row Store),每一个Page存储多行数据,而列存储(Column Store)把数据表中的每一列单独存储在Page集合中,这意味着,Page集合中存储的是某一列的数据,而不是一行中所有列的数据。在读取数据时,行存储把一行的所有列都加载到内存,即使有些列根本不会用到;而列存储只把需要的列加载到内存中,不需要的列不会被加载到内存中。
  • 列存储索引自动对数据进行压缩处理,由于同一行的数据具有很高的相似性,压缩率很高,数据读取更快速。

一般情况下,数据仓库的查询语句只会查询少数几个列的数据,其他列的数据不需要加载到内存中,这就使得列存储特别适合用于数据仓库中对星型连接(Star- Join)进行聚合查询,所谓星型连接(Star-Join)的聚合查询是指对一个大表(Large Table)和多个小表(Little Table)进行连接,并对Large Table 进行聚合查询。在数据库仓库中,是指事实表和维度表的连接。在大表上创建列存储索引,SQL Server 引擎将充分使用批处理模式(Batch processing mode)来执行星型查询,获取更高的查询性能。

 

 

 

 

 

参考文档:

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
posted @ 2019-01-15 08:07  悦光阴  阅读(2572)  评论(0编辑  收藏  举报