sql server列存储 sqlserver列式存储

转载

云端小悟空 2023-08-01 23:57:07

文章标签 sql server列存储 SQL Server 数据 文章分类 SQL Server 数据库

SQL Server 列存储索引强化

SQL Server 列存储索引强化... 1

1. 概述... 1

2.背景... 2

2.1 索引存储... 2

2.2 缓存和I/O.. 2

2.3 Batch处理方式... 2

3 聚集索引... 3

3.1 提高索引创建... 4

3.2 采样的支持... 4

3.3 BookMark的支持... 4

3.4 其他加强... 4

4 更新处理... 4

4.1 随机插入... 6

4.2 批量插入... 6

4.3 删除和更新... 6

4.4 对查询处理的影响... 6

5 查询处理和优化... 6

5.1 混合执行模式... 7

5.2 Hash Join. 7

5.2.1 spilling. 7

5.3 Bitmap过滤... 7

6 归档压缩... 7

7 性能测试... 8

7.1 Batch模式性能... 8

7.2 存储需求... 9

7.3 删除性能... 9

7.4 批量和随机插入... 9

参考：... 9

1. 概述

之前SQL Server只有2个存储组织方式，heaps和Btree，都是基于行的。SQL Server 2012之后加入了新的存储方式是，列存储压缩方式。还加入了新的查询处理方式，batch处理。

在SQL Server 2012中有不少限制，会在之后的版本中被修正：
1.列存储索引可更新。
2.可以当主存储方式用，比如聚集索引。
3.可以进一步压缩，减少使用空间。
4.batch处理方式得到很大的扩展和加强。

2.背景

2.1 索引存储

sql server列存储 sqlserver列式存储_SQL

如图演示了列存储索引是如何创建和保存的。注意在列存储索引中数据是不排序的。在一个column segment也没有排序。如图中显示，segment和目录会被以blob的方式保存每个column segment和目录都以独立的blob保存。一个blob可能分布在不同的磁盘page上，但是是有blob机制自动控制的。

目录保存了segment位置的信息，所以所有segment包含了一个列和任何相关的目录信息可以被简单的定位。目录也包含了额外的元数据。

2.2 缓存和I/O

Column segment和目录根据需要会被加载到内存中。但是并不会被保存在buffer pool中，有一个新的cache用来缓存large objects。每个对象连续的被保存在内存页中没有空隙，提高扫描性能。为了提高I/O性能预读可以再segment中进行，也可以再segment间进行。

2.3 Batch处理方式

SQL Server传统使用行模式，新的查询操作有些引入了batch模式。

sql server列存储 sqlserver列式存储_Server_02

一个batch通常是由几千行组成，如图，每个列被保存在连续的元素长度固定的向量上。Batch的执行方式效率很高，比如执行col1<5过滤，只需要扫描col1的向量比较执行，并且设置在qualifying row向量上的bit位。

SQL Server 2012只对一些操作符支持了batch模式，如Hash join的batch实现有2个操作，一个是build，一个是实际的join操作。在build阶段，多线程并发创建一个hash table，每个线程只处理一个子集，一旦build完成，多线程并发probe表，每个线程处理一部分probe输入。因为join的输入不会预先分配，所以会导致数据倾斜问题使几个线程负荷不均。

Cpu在hash join上的减少很显著，行模式每行要用600个指令，但是batch模式只需要85个指令。

优化器会决定使用行模式还是batch模式。Batch模式通常被用在数据密集计算，行模式通常使用在小输入，计算在树上完成，或者不支持batch操作的地方。

3 聚集索引

在SQL Server 2012中，列存储只能以secondary 索引方式存在，之后的版本SQL Server会取消这个限制。并且允许列存储来组织表数据。

3.1 提高索引创建

SQL Server列存储使用另一种目录来编码。在经常出现的值上，使用32bit对应到目录上。SQL Server使用2种目录，一个是全局目录和整个列相关，一个是局部目录，只和当前row group相关。

修改了创建索引的过程，现在过程分为2步：
1.采集每个列的数据，然后决定是否要为这个列创建一个全局的目录，并选一些数据放入到全局目录上。
2.使用第一步生成的全局目录来创建一个索引。

列存储的创建过程是很耗内存的，因此会在创建之前先保留好内存，根据保留的内存和每个线程需要的内存决定线程个数。初始的内存评估是不准确的，因为内存分配和数据有关很难准确评估。为了解决这个问题，使用动态的决定活动的线程个数来解决，build过程会监控内存的消耗来决定活动线程个数。

3.2 采样的支持

SQL Server的优化器使用数据分布的统计信息来优化查询。统计信息是一个直方图，由随机采样活得。非聚集的列存储索引不需要支持采样，因为统计信息可以通过基表活得。

统计信息的采样有2种方式：
1. 不太准确的，对io，cpu要求不高。
2. 准确的，io，cpu开销较大。

性能优化的采样扫描使用集群采样，即，一组row groups会被随机选择，行的扫描取决于采样率。没有被选中的row groups不会被读取。从btree和heap上采样也使用聚集采样的方式。

第二种方式是真实的随机行级别的采样。扫描所有的列并且随机选择一些行的子集。这样采样会比btree和heap上采样要准确。列存储的聚集采样只是用来帮助目录的创建，不会被用于查询优化。

3.3 BookMark的支持

在SQL Server BookMark是一个术语唯一的标示一行。在SQL Server中任何索引都可以被bookmark定位。最多的应用是在删除操作上，删除的时候会先收集bookmark然后在实际删除行之前先删除bookmark。因为列存储索引没有key来唯一标示一行，所以我们使用唯一标示row group中一行的tupleid和row group id组合来唯一标示使用。

3.4 其他加强

SQL Server 2012对某些列不支持，后续版本会对这部分类型支持，对于字符串，以后支持保存短字符串的值，而不是转为32bit的id。

4 更新处理

列存储对读取性能提升很大，但是直接对数据修改花费也很大。列存储的设计目的是数据仓库的事实表，通常有大量的insert，删除和更新很少。老的数据定期删除可以通过分区切换实现，高性能的常规插入和批量插入比较关键。

SQL Server使用了2个组件来自持列存储可更新：delete bitmap和delta stores。

每个列存储索引都有一个关联的delete bitmap，在扫描disqualify rows的时候会去查询bitmap过滤被删除的行。在内存和磁盘上bitmap结构是不同的，在内存中是bitmap，但是在磁盘上则是btree。

插入的或者修改的会被插入到delta store，delta store是btree行模式存储。Delta store对列存储扫描是透明的。

有了这些组件，列存储的增删改就可以实现了：
insert：直接把新行插入到delta store，因为是btree所以可以高效的完成
delete：若行被删除会把包含一个rowid的记录保存到保存了delete bitmap的btree中。如果行在delta store比较简单可以直接从btree中删除。
update：update操作会被分为delete和insert
merge：Merge操作也会被分为delete，insert或者update操作。

sql server列存储 sqlserver列式存储_sql server列存储_03

Delta store和对应的列存储有的列是一样的。唯一键是rowid由系统生成。

列存储可能有多个delta store，当有行要插入的时候，会创建新的delta store。Delta store到达上限之后就会关闭。SQL Server会自动检查关闭的delta store并把它们转化为列存储的方式。处理这个的任务被称为Tuple Mover,定期的在后台运行，不会堵塞读，但是会堵塞删除。

Tuple Mover读取一个关闭的delta store，开始创建相关的压缩segment，当创建完新的segment会被设置为可见，相关的delta store就不可见了。Tuple Mover等delta store上的扫描都完成之后就会删除这个delta store。

4.1 随机插入

通常非批量插入，在这里都称为随机插入。插入到列存储对查询处理是透明的。有内部的访问方法层负责delta store的处理。

4.2 批量插入

大的bulk insert不会把行直接插入到delta store而是直接把一批行转化为列存储。这个操作会缓存行，直到累计的行数满足可以转化为列存储的条件，并把结果segment和目录写入到磁盘。这个很有效减少IO的需求。

当bulk insert完成的时候，必须压缩并且关闭数据，bulk insert API中的batch_size是指一次bulk insert的行数。为了提高压缩效率，建议把batch size设置为1MB。

虽然有时候batch size很大但是无法满足压缩row group的条件，SQL Server会自动的把这些行放入delta store。

4.3 删除和更新

对于删除操作，会直接把删除行的rowid写入到delete bitmap 上。Update操作则为被分为insert和delete 2个部分。

4.4 对查询处理的影响

Delta store和delete bitmap的控制是由访问方法层处理的，对查询处理层透明。

在扫描列存储时，发现rowid 存在在delete bitmap那么这个行就会被跳过，这个操作也是在访问方法层处理，对查询处理器透明。

The process of segment elimination during scans (by checking segment metadata containing the minimum and maximum values in columns) does not need to consult the deleted bitmap. The interval between the minimum and maximum values within a column cannot grow when rows are deleted. Therefore, the original minimum and maximum values computed during column store segment creation can safely be used for segment elimination even after deletes.

有大量的delete会减少io的性能，因为delete bitmap 会变大。

并发扫描delta store是为每个delta分配一个线程，因为一个delta store并发扫描会显得delta store 太小。Delta store的扫描比列存储扫描慢，因为delta store是列存储会读入不必要的列。

5 查询处理和优化

在SQL Server 2012中Batch处理之应用在大量查询的数据仓库的场景。batch处理图形比较呆板，join循序是根据基数评估确定。当内存无法满足要求是，会使用行模式执行。

之后的SQL Server版本，Batch处理有更强的能力。会考虑执行计划中的迭代器的batch执行，不管输入是不是使用batch执行的，不管数据的组织方式是列还是行。并且join的循序不再固定。Batch操作支持所有的join类型，union all，标量聚集。

sql server列存储 sqlserver列式存储_Server_04

如图计划是混合模式的，红色框框是行模式处理。

5.1 混合执行模式

在SQL Server 行和batch之间转化由计划规定，转化只有在必要的情况下才会发生。后续版本的SQL Server会有全新的模式来处理行和batch之间的转化。

5.2 Hash Join

一开始只有inner join支持，现在已经支持所有join类型。

必须要有足够的内存来创建hash表，如果没有就会从batch模式转化为row模式可以支持低内存情况下运行，通过把数据spilling到磁盘来降低内存使用。

提高了bitmap过滤的实现。

5.2.1 spilling

以简单的join为例，hash join的spill：
Build阶段:在build之前数据通过hash函数被分为若干个buckets。当决定spill的，选择一个bucket然后标记为spilling，创建一个新的临时文件，并把bucket数据写入到文件中并释放内存。
Probe阶段：在处理probe时，不对输入的行进行分区但是在lookup hash表之前先回去检查相关的bucket是否在内存中，如果不在内存中，则写入存probe记录的临时文件中，之后留下一对对的build-probe，为每对都应用相同的算法只是以文件输入。

Spilling的时候就个简单的规则，总是spilling行较多的。

5.3 Bitmap过滤

下一个版本中会修改bitmap过滤生产的和在计划中的位置，在SQL Server 2012中优化器会决定bitmap过滤的准确位置：
好处：不需要花时间来移动bitmap。
坏处：限制bitmap过滤了其他好处。

在每个join下面加个bitmap 过滤被证明是不可行的，是因为会导致逻辑计划空间爆炸，为了避免这个，hash join操作会保存bitmap和它创建的评估的选择度，再是现实把选择度信息转给子操作，可以让他来调整cost来计算bitmap的选择度。

Bitmap有2种:
简单的bitmap:就是hash 表，由一个int值为索引的bit数组
修改过的bitmap:就是bloom filter，优化了cpu cache 的使用。

6 归档压缩

某些数据，越老使用的就越少，所以这种情况下可以使用列存储的归档压缩。

归档压缩是每个表或者分区的选项。为了能够简单的扩展到磁盘，归档压缩由流压缩实现，透明的序列化和反序列化。使用LZ77压缩算法。

7 性能测试

7.1 Batch模式性能

SQL Server 2012 使用batch模式来处理从列存储索引上过来的数据。有些操作可以减少40倍左右的cpu。性能测试使用TPC-DS数据库。

压缩率

sql server列存储 sqlserver列式存储_Server_05

对一下SQL进行测试：

Q_count:

select count(*) from store_sales

Q_outer:

select item.i_brand_id brand_id, item.i_brand brand,

　　sum(ss_ext_sales_price) ext_price

from item left outer join store_sales

　　on (store_sales.ss_item_sk = item.i_item_sk)

where item.i_manufact_id = 128

group by item.i_brand_id, item.i_brand

order by ext_price desc, brand_id

Q_union_all:

select d.d_date_sk, count (*)

from (select ss_sold_date_sk as date_sk,

　　　　　　ss_quantity as quantity

　　　　from store_sales

　　　　union all

　　　　select ws_sold_date_sk as date_sk,

　　　　　　ws_quantity as quantity

　　　　from web_sales) t, date_dim d

where t.date_sk = d.d_date_sk

and d.d_weekend = 'Y'

group by d.d_date_sk;

Q_count_in:

-- Here, store_study_group contains a

-- set of 100 IDs of interesting stores.

select count(*) from store_sales where ss_store_sk in (select s_store_sk from store_study_group);

Q_not_in:-- bad_ticket_numbers contains a set of ticket numbers
-- with known data errors that we want to ignore.
select ss_store_sk, d_moy, sum(ss_sales_price)
from store_sales, date_dim
where ss_sold_date_sk = d_date_sk and d_year = 2002
　　and ss_ticket_number not in (select * from bad_ticket_numbers)
group by ss_store_sk, d_moy

sql server列存储 sqlserver列式存储_数据_06

7.2 存储需求

列存储方式，store_sales表要13.2GB空间，大概46byte一样。在Btree下聚集索引35.7GB，非聚集索引7.7GB，一共43.5GB大概151byte一行。

7.3 删除性能

删除性能通过以下语句来测试：
delete from Purchase where MediaId % 20 = 1;

在一个4核的设备上，数据在磁盘汇总，delete语句在57s后完成，在相同的设备上，使用btree，需要使用239s。delete在列存储中比较快是因为，只要在dlete bitmap上插入row_group_id,row_number，而btree则要做实际的删除动作，会生成很多log。若在列存储上删除>>10%建议重建列存储索引。