簡(jiǎn)介

首先介紹列存儲(chǔ)的概念： 傳統(tǒng)的數(shù)據(jù)庫(kù)存儲(chǔ)是行存儲(chǔ)。對(duì)于SQL Server來說，每個(gè)page是8K；往page里面塞數(shù)據(jù)，假設(shè)該表每條數(shù)據(jù)長(zhǎng)度是500字節(jié)，那么這個(gè)page 先塞第一條數(shù)據(jù)，然后再塞第二條數(shù)據(jù)，大概能塞 8K/500=16條數(shù)據(jù)。注意這里每一條數(shù)據(jù)都是包括所有字段(column)的。如圖所示，下面是若干個(gè)page，每個(gè)page塞滿了一行一行的數(shù)據(jù)。行存儲(chǔ)示意圖。

接下來介紹列存儲(chǔ)，是一個(gè)column一個(gè)column塞，而且在SQL Server里，是向row group或者segment塞。列存儲(chǔ)示意圖

如圖，該表有5個(gè)字段(column)，每個(gè)紅色柱體是一個(gè)Segment；每5個(gè)segment組成一個(gè)row group。Segment只包含一個(gè)column的數(shù)據(jù)，而row group包含所有column的數(shù)據(jù)。

每個(gè)Segment最多包含1百萬條該column的數(shù)據(jù)；從性能上說，1百萬條能達(dá)到性能最優(yōu)(數(shù)量越大，壓縮比越大)，數(shù)量越少，該segment的性能越差。

列存儲(chǔ)對(duì)比行存儲(chǔ)有什么好處： 行存儲(chǔ)適合OLTP系統(tǒng)，就是多用戶使用，很多update/insert/delete，SQL 語句處理的對(duì)象都是幾條數(shù)據(jù)或者幾百條數(shù)據(jù)(數(shù)據(jù)量不大)；

列存儲(chǔ)適合數(shù)據(jù)倉(cāng)庫(kù)，用戶數(shù)很少，數(shù)據(jù)量巨大，數(shù)據(jù)變化少(除了ETL)。在SQL Server中，ColumnStore index能把大量的數(shù)據(jù)壓縮到1/10，從而減少IO，CPU和memory的使用，從而帶來性能的飛躍；ColumnStore 除了壓縮，還使用了Batch mode，segment eliminate等技術(shù)，對(duì)性能有很大提升。

2. ColumnStore Index 適用的場(chǎng)景

和其他技術(shù)一樣，不能適合所有場(chǎng)景；如果選用的場(chǎng)景不適合，反而會(huì)帶來性能的急劇下降。

(1) 用星性/雪花模型建模的數(shù)據(jù)倉(cāng)庫(kù)

(2) 該表(或者分區(qū))的記錄數(shù)要大于1百萬

(3) 大部分SQL 語句是報(bào)表類的語句，就是range scan 而不是 seek。

(4) 該表的數(shù)據(jù)很少進(jìn)行update/delete，大量的insert是可以的。

(5) 該表不能有varchar(max), nvarchar(max), or varbinary(max) 數(shù)據(jù)類型

(6) 對(duì)于OLTP數(shù)據(jù)庫(kù)，在某些特定的場(chǎng)景下也可以使用columnstore index ：real-time operational analytics

3. Column Store的物理結(jié)構(gòu)

ColumnStore Index 除了Row group 還包括 DeltaStore。假設(shè)該表有1105萬條記錄，每個(gè)rowgroup容納100萬條，那么總共有11個(gè)row group，還有5萬記錄放在Deltastore里面。

DeltaStore是用來存放不夠數(shù)量(這里是100萬)的數(shù)據(jù)，行存儲(chǔ)，沒有壓縮；而rowgroup都是列存儲(chǔ)，而且壓縮了。

隨著insert 數(shù)據(jù)增多，Deltastore的數(shù)量增加，如果數(shù)量增加到100萬，該Deltastore 會(huì)停止接收數(shù)據(jù)，變成row group，也就是 列存儲(chǔ)，壓縮；如果還有數(shù)據(jù)insert，會(huì)生成新的Deltastore。

Columnstore Index 的組成部分除了 Row Group，Delta store，還有 Delted Bitmap.

Columnstore index 刪除記錄并不是物理刪除，而是邏輯刪除，在 Delted Bitmap加一個(gè)標(biāo)記； Delted Bitmap會(huì)記錄整個(gè)表被刪除的記錄；SQL Server對(duì)該表做query的時(shí)候，除了查詢r(jià)ow group，Delta store(row store)還要查詢 Delted Bitmap，把三者的結(jié)果Union才是最后的結(jié)果。

那么ColumnStore Index什么時(shí)候做物理刪除？ 對(duì)index 進(jìn)行rebuild或者reorganize的時(shí)候。

對(duì)Columnstore index進(jìn)行update，并不是物理update，而是delete該條記錄然后insert一條新的記錄。

ColumnStore Index 結(jié)構(gòu)小結(jié)：

(1) 包括Row Group(compressed，列存儲(chǔ))，Delta Store (也叫Delta Row group，行存儲(chǔ))，Deleted Bitmap (存儲(chǔ)被刪除的記錄的信息)。

(2) 從SQL Server2016開始，一個(gè)表創(chuàng)建了ColumnStore index后，還可以創(chuàng)建傳統(tǒng)的行存儲(chǔ)的索引----non clustered index(NCI)。

(3)ColumnStore index本身不排序的，所以查詢某一條記錄都需要 全表掃描(full table scan)；不排序這個(gè)特性對(duì)于insert是利好，performance很好；對(duì)于delete/update不好，特別表比較大的時(shí)候。 因?yàn)閐elete/update某一條記錄，需要先找到它，而查找的代價(jià)對(duì)于ColumnStore Index 比較大。

(4) 對(duì)于上面第三點(diǎn)，要快速的找到某一條或幾條數(shù)據(jù)，可以在ColumnStore index基礎(chǔ)上再創(chuàng)建傳統(tǒng)的行存儲(chǔ)的索引----non clustered index(NCI)。

(5) 在ColumnStore index基礎(chǔ)上再創(chuàng)建傳統(tǒng)的行存儲(chǔ)的索引----non clustered index(NCI)，好處不僅是performance，還能給這個(gè)表加上 唯一性約束、主鍵約束和外鍵約束等約束。當(dāng)然，這些都只能在SQL2016或之后的版本才能實(shí)現(xiàn)。

4. 如何發(fā)現(xiàn)某些表適合創(chuàng)建ColumnStore index。

首先它比較適合于數(shù)據(jù)倉(cāng)庫(kù)，但數(shù)據(jù)倉(cāng)庫(kù)的每個(gè)表都能創(chuàng)建ColumnStore Index嗎？另外OLTP環(huán)境可以使用CCI嗎？

另外可以用 DMV sys.dm_db_index_operational_stats來查看某個(gè)表的過往操作：

(1) 如果50%以上的操作是range scan，而不是seek

(2) update/delete的操作少于10%

那么這個(gè)表很適合創(chuàng)建cluster columnstore index(CCI)；當(dāng)然還有一個(gè)前提，該表足夠大，至少1百萬條記錄，越大越好。

如何估計(jì)某個(gè)表創(chuàng)建clustered columnstore index 之后的壓縮率？ 在SQL 2019中，可以使用sp_estimate_data_compression_savings 來預(yù)估壓縮率。

該sp在數(shù)據(jù)庫(kù)中執(zhí)行以下操作：

?創(chuàng)建臨時(shí)表 T

?把該表的數(shù)據(jù)進(jìn)行采樣，載入一部分?jǐn)?shù)據(jù)到T

?查看T的大小

?對(duì)T 進(jìn)行列存儲(chǔ)壓縮，查看壓縮后的大小

5. 快速的裝載數(shù)據(jù)到已經(jīng)創(chuàng)建 CCI的表中

(1) 裝載外部文件

bulk insert tableA FROM 'c:\temp\fileA.csv'

csv文件的數(shù)據(jù)并行的裝載到多個(gè)Row Group和多個(gè)Delta Store 中；超過102400條記錄的數(shù)據(jù)進(jìn)入Row Group，列壓縮；沒有超過102400條記錄的數(shù)據(jù)進(jìn)入 Delta Store (Delta Row group)。

因?yàn)镽ow Group提供了高的壓縮比，所以裝載數(shù)據(jù)產(chǎn)生的日志也會(huì)少很多；

SQL Server會(huì)自動(dòng)的使用并行操作，同時(shí)向多個(gè)Row Group裝載數(shù)據(jù)。

(2) 從其他表裝載數(shù)據(jù)

Insert into select * from

與“裝載外部文件”很類似，超過100k條記錄的數(shù)據(jù)進(jìn)入Row Group，列壓縮；沒有超過100k條記錄的數(shù)據(jù)進(jìn)入 Delta Store (Delta Row group)。

不過SQL Server不會(huì)自動(dòng)的使用并行操作，要使用tablock才能觸發(fā)并行。

insert into ccitest with (TABLOCK) select * from dbo.FactResellerSalesXL (能并行)

(3) 使用SSIS 來裝載數(shù)據(jù)

從SQL2016開始，有一個(gè)新的參數(shù) AutoAdjustBufferSize，它能根據(jù)batchsize來自動(dòng)調(diào)整 buffer size，對(duì)性能有極大提升。

6. CCI 的性能

CCI 除了壓縮比大帶來的CPU/memory/IO的減少，還使用了 Batch mode，segment eliminate和 并行來提升性能。

(1) Batch Mode

Batch mode 從字面上，就是批處理，就是一次處理幾百條數(shù)據(jù)，而不是一條一條處理數(shù)據(jù)；

比較適合于大數(shù)據(jù)量的數(shù)據(jù)倉(cāng)庫(kù)。

Batch mode 是從SQL Server 2012開始和ColumnStore Index 一起使用的；在SQL 2019之前，Batch mode只能在列存儲(chǔ)中使用，從SQL 2019開始，batch mode也能在row store使用。

對(duì)于 SQL 語句： selectProductKey,OrderQuantityfromFactResellerSalesXL_CCI where OrderQuantity<3

batch mode會(huì)在內(nèi)存中 占用64K大小的內(nèi)存，形成上圖中的vector，先scan 該表把64k的數(shù)據(jù)放到vector中，然后用predictte來過濾(OrderQuantity<3)。符合過濾條件的，會(huì)在memory當(dāng)中的bitmap打上標(biāo)記；bitmap在上圖的最左邊。

每次處理64k大小的數(shù)據(jù)。

在SQL 2016之前，很多函數(shù)不支持 batch mode，包括sum，avg，min,rank等；還有distinct，left join,group by, order by等也不被支持。 總之一句話，要用Columnstore index ，要用batch mode，不要選SQL 2012/2014，要選SQL 2016/2017/2019

(2) Segment elimination and column elimination

Segment elimination 也叫 Rowgroup elimination

從上圖可知，如果表SalesTable創(chuàng)建了ColumnStore Index，SQL Server會(huì)自動(dòng)把不需要的字段(column)，不需要的rowgroup過濾掉。

CCI如何過濾Row group/segment？請(qǐng)看下面的元數(shù)據(jù)：

SELECT segment_id, object_name(p.object_id), s.column_id, s.min_data_id, s.max_data_id FROM sys.column_store_segments s, sys.partitions p

where p.object_id = object_id('FactResellerSalesXL_CCI') and

p.hobt_id = s.hobt_id and column_id = 2

從上圖可以看到，該表的CCI的第二個(gè)字段(column)有12個(gè)segment，每個(gè)segment都記錄了最大值，和最小值，這樣sql語句查詢的時(shí)候，很容易過濾不需要的segment。

另外執(zhí)行 下面的語句：

set statistics IO ON

set statistics TIME ON

SELECT Productkey, OrderQuantity as curqty,

Sum (OrderQuantity) OVER (ORDER BY ProductKey) AS TotalQuantity

FROM FactResellerSalesXL_CCI WHERE orderdatekey in ( 20060301,20060401)

正因?yàn)榈诙€(gè)字段(orderdatekey)的元數(shù)據(jù)存儲(chǔ)了最大值/最小值，所以上面的SQL 直接skip了 7個(gè)segment，只在另外的5個(gè)segment中查找數(shù)據(jù)

(3) Aggregate Pushdown

從SQL2016 開始，對(duì)于Select SUM(sales) from 這樣的語句，當(dāng)表非常大的時(shí)候，Aggregate Pushdown 特性可以極大提高性能。

簡(jiǎn)單的來說，對(duì)于sum/avg/group by/max/count這樣·的函數(shù)，表有數(shù)以億計(jì)的記錄，SQL Server 會(huì)自動(dòng)的把處理大量數(shù)據(jù)的工作放在執(zhí)行計(jì)劃的第一步，也就是最接近存儲(chǔ)的地方，這樣傳送給執(zhí)行計(jì)劃的下一步的數(shù)據(jù)會(huì)大大減少。

該特性自動(dòng)進(jìn)行，不用任何調(diào)整。

7. CCI 的維護(hù)。

和其他傳統(tǒng)的row store index一樣，CCI也會(huì)有碎片(fragment)。

有兩類碎片：

1.對(duì)于CCI，只有一個(gè)Delta Store是正常的，如果有多個(gè)Delta Store(超過10個(gè)以上)那就是碎片化，需要進(jìn)行維護(hù)

2. 對(duì)于列存儲(chǔ)的Row Group，刪除數(shù)據(jù)只是邏輯刪除，沒有物理刪除；如果刪除的數(shù)據(jù)占該RowGroup中超過10%，那就是碎片化，需要進(jìn)行維護(hù)。

從SQL 2016開始，使用 Alter Index on

(1) self merge

當(dāng)Row group中的邏輯刪除記錄數(shù)占到10%以上，就會(huì)使用self merge物理刪除這些記錄；

(2)merge

兩個(gè)Row group的記錄數(shù)加起來都不到1百萬，那么merge操作會(huì)把這兩個(gè)RG 合并成一個(gè)

8. Columnstore 和 In-Memory OLTP的結(jié)合

首先解釋什么是 Real-time Operational Analytics。

之前介紹的傳統(tǒng)的數(shù)據(jù)倉(cāng)庫(kù)，適合使用 CCI；對(duì)比傳統(tǒng)的數(shù)據(jù)倉(cāng)庫(kù)，現(xiàn)在有一些混合型的應(yīng)用場(chǎng)景，就是既有OLTP，也有數(shù)據(jù)倉(cāng)庫(kù)的查詢。這種場(chǎng)景，就是直接在OLTP的數(shù)據(jù)庫(kù)上跑一些報(bào)表的大SQL，好處如下：因?yàn)闆]有專門的數(shù)據(jù)倉(cāng)庫(kù)，節(jié)約了硬件；

沒有經(jīng)過ETL，OLTP的數(shù)據(jù)一般是最新的，而傳統(tǒng)的數(shù)據(jù)倉(cāng)庫(kù)往往經(jīng)過ETL，數(shù)據(jù)往往不是最新的。

因?yàn)闆]有ETL，Stage 數(shù)據(jù)庫(kù)的硬件，ETL的維護(hù)、軟件的成本都節(jié)省了。

缺點(diǎn)也很明顯：不能像傳統(tǒng)數(shù)據(jù)倉(cāng)庫(kù)，有多個(gè)數(shù)據(jù)源

沒有經(jīng)過ETL，數(shù)據(jù)的結(jié)構(gòu)不能像傳統(tǒng)數(shù)據(jù)庫(kù)那樣實(shí)現(xiàn)星形/雪花建模

同時(shí)在一個(gè)數(shù)據(jù)庫(kù)上跑OLTP和報(bào)表SQL，互相影響性能

前面介紹了，Real-time Operational Analytics可以使用disk based table(磁盤表)，也可以使用in memory table;如果使用前者，就可以使用noclustered columnstore index；如果使用in memory table,必須使用 clustered columnstore index。下面介紹后者

首先該表是 in-memory OLTP table,表上可以創(chuàng)建hash index 或range index，這些都是傳統(tǒng)的row store(行存儲(chǔ))，只是把這些都搬到內(nèi)存當(dāng)中；圖中最下面是columnstore index(列存儲(chǔ))，也是放著memory當(dāng)中。

上圖告訴我們，數(shù)據(jù)實(shí)現(xiàn)了冗余，in memory table存儲(chǔ)了數(shù)據(jù)，而內(nèi)存中的columnstore index也存儲(chǔ)了數(shù)據(jù)，而且是數(shù)據(jù)、索引放在一起。每當(dāng)有數(shù)據(jù)insert，先對(duì)in memory table的hot 部分(尾部)進(jìn)行insert，當(dāng)這一部分的記錄數(shù)達(dá)到1百萬，這些數(shù)據(jù)會(huì)轉(zhuǎn)移到columnstore index 當(dāng)中。

當(dāng)SQL 語句是OLTP類型，in memory table 可以很好的處理，效率非常高，具體原理要參考in memory OLTP特性。

當(dāng)SQL 語句是報(bào)表類語句，columnstore index可以高效處理。

從上圖看，該場(chǎng)景需要比較多的memory。

總結(jié)

以上是生活随笔為你收集整理的predicate 列存储索引扫描_ColumnStore index （列存储索引）解析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。