SQL Server 列存储索引强化

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: SQL Server 列存储索引强化 SQL Server 列存储索引强化... 1 1. 概述... 1 2.背景... 2 2.1 索引存储... 2 2.2 缓存和I/O.. 2 2.

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个存储组织方式,heapsBtree,都是基于行的。SQL Server 2012之后加入了新的存储方式是,列存储压缩方式。还加入了新的查询处理方式,batch处理。

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

2.背景

2.1 索引存储

如图演示了列存储索引是如何创建和保存的。注意在列存储索引中数据是不排序的。在一个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模式。

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

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

Cpuhash 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. 不太准确的,对iocpu要求不高。
2. 准确的,iocpu开销较大。

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

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

3.3 BookMark的支持

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

3.4 其他加强

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

4 更新处理

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

SQL Server使用了2个组件来自持列存储可更新:delete bitmapdelta stores

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

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

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

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

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

Tuple Mover读取一个关闭的delta store,开始创建相关的压缩segment,当创建完新的segment会被设置为可见,相关的delta store就不可见了。Tuple Moverdelta 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操作则为被分为insertdelete 2个部分。

4.4 对查询处理的影响

Delta storedelete 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 2012Batch处理之应用在大量查询的数据仓库的场景。batch处理图形比较呆板,join循序是根据基数评估确定。当内存无法满足要求是,会使用行模式执行。

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

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

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 joinspill
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的选择度。

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

6 归档压缩

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

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

7 性能测试

7.1 Batch模式性能

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

压缩率

对一下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

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,需要使用239sdelete在列存储中比较快是因为,只要在dlete bitmap上插入row_group_id,row_number,而btree则要做实际的删除动作,会生成很多log。若在列存储上删除>>10%建议重建列存储索引。

7.4 批量和随机插入

对于列存储索引,bulk insert16核设别上16个并发,可以达到600GB/h,如果做随机插入,每次一行,在48个物理线程的设别上插入393W,需要花费2216秒,每秒2944行。

参考:

Enhancements to SQL Server Column Stores

 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
25天前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
39 2
|
14天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
19天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
18天前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
19天前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
19天前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
2月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
23天前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤
|
25天前
|
索引
SQL_创建和管理索引
SQL_创建和管理索引
24 0
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")