01、列存儲(chǔ)的特點(diǎn)

02、列存儲(chǔ)的物理實(shí)現(xiàn)

03、列存儲(chǔ)索引

04、列存儲(chǔ)索引的空間使用

傳統(tǒng)的存儲(chǔ)數(shù)據(jù)的方式是逐行存儲(chǔ)（Row Store），每一個(gè)Page存儲(chǔ)多行數(shù)據(jù)，而列存儲(chǔ)（Column Store）把數(shù)據(jù)表中的每一列單獨(dú)存儲(chǔ)在Page集合中，這意味著，Page集合中存儲(chǔ)的是某一列的數(shù)據(jù)，而不是一行的所有列的數(shù)據(jù)。

列存儲(chǔ)索引適合于數(shù)據(jù)倉(cāng)庫(kù)中，主要執(zhí)行大容量數(shù)據(jù)加載和只讀查詢(xún)，與傳統(tǒng)面向行的存儲(chǔ)方式相比，使用列存儲(chǔ)索引存儲(chǔ)可最多提高 10 倍查詢(xún)性能 ，與使用非壓縮數(shù)據(jù)大小相比，可提供多達(dá) 7 倍數(shù)據(jù)壓縮率 。列存儲(chǔ)索引使用用“批處理執(zhí)行模式”的模式，這與行存儲(chǔ)使用的逐行數(shù)據(jù)讀取模式對(duì)比，性能大幅提升。

列存儲(chǔ)索引主要在下面三個(gè)特性上提升查詢(xún)的性能：

行存儲(chǔ)使用逐行處理模式，每次只處理一行數(shù)據(jù)；而列存儲(chǔ)索引使用批處理模式，每次處理一批數(shù)據(jù)行。

行存儲(chǔ)是逐行存儲(chǔ)（Row Store），每一個(gè)Page存儲(chǔ)多行數(shù)據(jù)，而列存儲(chǔ)（Column Store）把數(shù)據(jù)表中的每一列單獨(dú)存儲(chǔ)在Page集合中，這意味著，Page集合中存儲(chǔ)的是某一列的數(shù)據(jù)，而不是一行中所有列的數(shù)據(jù)。在讀取數(shù)據(jù)時(shí)，行存儲(chǔ)把一行的所有列都加載到內(nèi)存，即使有些列根本不會(huì)用到；而列存儲(chǔ)只把需要的列加載到內(nèi)存中，不需要的列不會(huì)被加載到內(nèi)存中。

列存儲(chǔ)索引自動(dòng)對(duì)數(shù)據(jù)進(jìn)行壓縮處理，由于同一行的數(shù)據(jù)具有很高的相似性，壓縮率很高，數(shù)據(jù)讀取更快速。

一般情況下，數(shù)據(jù)倉(cāng)庫(kù)的查詢(xún)語(yǔ)句只會(huì)查詢(xún)少數(shù)幾個(gè)列的數(shù)據(jù)，其他列的數(shù)據(jù)不需要加載到內(nèi)存中，這就使得列存儲(chǔ)特別適合用于數(shù)據(jù)倉(cāng)庫(kù)中。

01、列存儲(chǔ)的特點(diǎn)

為什么列存儲(chǔ)能夠大幅度提高數(shù)據(jù)的查詢(xún)性能呢？要回答這個(gè)問(wèn)題，首先必須明白SQL Server引擎是怎樣讀取數(shù)據(jù)的。在讀取數(shù)據(jù)時(shí)，SQL Server每次都把所需數(shù)據(jù)所在的整個(gè)Page讀取到內(nèi)存中，Page是數(shù)據(jù)讀取的最小單位。如果采用行存儲(chǔ)，每一個(gè)Page都存儲(chǔ)所有列的數(shù)據(jù)，每行的Size決定了單個(gè)Page能夠存儲(chǔ)的數(shù)據(jù)行數(shù)量。

我們可以粗略計(jì)算一下，如果一個(gè)數(shù)據(jù)行有10列，每列的平均Size是10B，一行的Size是100B，那么單個(gè)Page最多存儲(chǔ)80行（8060B/100B）；如果采用列存儲(chǔ)模式，那么單個(gè)Page可以存儲(chǔ)806行（8060B/10B）。就單個(gè)Page存儲(chǔ)的數(shù)據(jù)行數(shù)量而言，列存儲(chǔ)是行存儲(chǔ)的10倍，SQL Server引擎把一個(gè)Page讀取到內(nèi)存中，能夠獲取的數(shù)據(jù)行數(shù)量成10倍增加。

因此，采用列存儲(chǔ)模式時(shí)，每一個(gè)Page能夠存儲(chǔ)更多的數(shù)據(jù)行。在加載列存儲(chǔ)數(shù)據(jù)時(shí)，SQL Server只需要消耗少量的IO，就能把某一列的全部數(shù)據(jù)加載到緩存中。當(dāng)從列很多的大表中讀取幾個(gè)列時(shí)，相比傳統(tǒng)的行存儲(chǔ)（Row Store）模式，列存儲(chǔ)（Column Store）能夠成千上萬(wàn)倍地提高數(shù)據(jù)的讀取速度和查詢(xún)性能。

基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶(hù)、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

項(xiàng)目地址：https://github.com/YunaiV/ruoyi-vue-pro

視頻教程：https://doc.iocoder.cn/video/

02、列存儲(chǔ)的物理實(shí)現(xiàn)

數(shù)據(jù)表（堆，B-Tree）以行存儲(chǔ)模式存儲(chǔ)數(shù)據(jù)，而列存儲(chǔ)索引以列存儲(chǔ)模式存儲(chǔ)數(shù)據(jù)，行存儲(chǔ)和列存儲(chǔ)的示例圖：

1，列存儲(chǔ)的優(yōu)點(diǎn)

對(duì)于列存儲(chǔ)，列C1…C6 存儲(chǔ)在不同的Page組中，列存儲(chǔ)的有點(diǎn)是：

列存儲(chǔ)是把每一列都單獨(dú)存儲(chǔ)在Pages集合中，對(duì)于行存儲(chǔ)，哪怕只從數(shù)據(jù)表中選擇（select）一列，SQL Server引擎都把整個(gè)數(shù)據(jù)行所在的Page讀取到內(nèi)存中，而使用列存儲(chǔ)索引，僅僅需要把select子句指定的列讀取到內(nèi)存，不需要的列不會(huì)被讀?。灰虼?，如果一個(gè)查詢(xún)請(qǐng)求只需要從少量的幾個(gè)列中獲得數(shù)據(jù)，列存儲(chǔ)能夠大幅度提高查詢(xún)性能；

由于單個(gè)數(shù)據(jù)列的數(shù)據(jù)冗余度更高，因此同一列的數(shù)據(jù)更容易被壓縮存儲(chǔ)，單個(gè)Page存儲(chǔ)更多的數(shù)據(jù)；

緩存命中率提高，這是因?yàn)橥涣械臄?shù)據(jù)被高度壓縮，常用的Page被頻繁訪問(wèn)而變得異?；钴S，Buffer Manager把活躍的數(shù)據(jù)頁(yè)緩存到內(nèi)存中，不常用的Page被換出（Page Out）。

更高級(jí)的查詢(xún)執(zhí)行技術(shù)，列存儲(chǔ)模式讀取數(shù)據(jù)使用的是批處理模式（Batch Processing Mode），相對(duì)于傳統(tǒng)的行處理技術(shù)，查詢(xún)性能更高。

2，列存儲(chǔ)模式的物理實(shí)現(xiàn)

SQL Server引擎分三步實(shí)現(xiàn)列存儲(chǔ)：

step1，列存儲(chǔ)索引先把數(shù)據(jù)表的所有數(shù)據(jù)行分組，每個(gè)分組也稱(chēng)作行組（Row Groups）。

step2，在每個(gè)行組中，每列的所有數(shù)據(jù)行構(gòu)成一個(gè)列段（Column Segment），簡(jiǎn)稱(chēng)段。

step3，對(duì)每個(gè)段進(jìn)行壓縮處理和編碼，每個(gè)段都單獨(dú)存儲(chǔ)在列存儲(chǔ)索引中。

3，編碼和壓縮

列存儲(chǔ)使用兩種編碼類(lèi)型：基于字典（dictionary based）和基于值（value based），使用Vertipaq壓縮數(shù)據(jù)。

字典編碼是把唯一值編入字典，每一個(gè)唯一值都匹配一個(gè)序號(hào)，而序號(hào)用于索引字典，通過(guò)存儲(chǔ)序號(hào)來(lái)壓縮數(shù)據(jù)。如果數(shù)據(jù)表中存在大量的重復(fù)值，那么使用字典編碼壓縮率高。

值編碼用于整數(shù)類(lèi)型，或小數(shù)類(lèi)型，編碼的原理是把Value的范圍按照比例縮小或增大，并使用一個(gè)指數(shù)（exponent）來(lái)表示比例。如果整數(shù)（integer） 或小數(shù)（decimal）的值分布集中，那么使用基于值（value-based）編碼方法進(jìn)行壓縮非常高效。

列存儲(chǔ)索引的物理存儲(chǔ)如下圖所示：

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶(hù)、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

項(xiàng)目地址：https://github.com/YunaiV/yudao-cloud

視頻教程：https://doc.iocoder.cn/video/

03、列存儲(chǔ)索引

SQL Server 2012開(kāi)始引入列存儲(chǔ)模式，用戶(hù)通過(guò)創(chuàng)建列存儲(chǔ)索引（Column Store Index）來(lái)體驗(yàn)列存儲(chǔ)模式帶來(lái)的性能提升。而列存儲(chǔ)模式非常適用于星型連接（Star- Join）類(lèi)型的聚合查詢(xún)，所謂星型連接（Star-Join）的聚合查詢(xún)是指對(duì)一個(gè)大表（Large Table）和多個(gè)小表（Little Table）進(jìn)行連接，并對(duì)Large Table 進(jìn)行聚合查詢(xún)。在數(shù)據(jù)庫(kù)倉(cāng)庫(kù)中，是指事實(shí)表和維度表的連接。

在大表上創(chuàng)建列存儲(chǔ)索引，SQL Server 引擎將充分使用批處理模式（Batch processing mode）來(lái)執(zhí)行星型查詢(xún)，獲取更高的查詢(xún)性能。

典型的Star- Join的聚合查詢(xún)類(lèi)似于下面的示例腳本：

selectlt.Grouping_Columns,
AggregationFunction(bt.Columns)
fromdbo.LittleTableltwith(nolock)
innerjoindbo.BitTablebtwith(nolock)
onlt.Int_Col1=bt.Int_col1
where....
groupbylt.Grouping_Columns

在SQL Server 2012中，只能創(chuàng)建非聚集的列存儲(chǔ)索引，由于列存儲(chǔ)索引的每一列都有獨(dú)立的存儲(chǔ)空間（Page Set），因此，列存儲(chǔ)索引會(huì)包含數(shù)據(jù)表的所有列，這樣，每一個(gè)數(shù)據(jù)列都會(huì)被索引到。但是，并不是每一列都能獲得的相同的性能提升，這是因?yàn)椋写鎯?chǔ)使用的壓縮算法對(duì)于具有大量重復(fù)值的字符或數(shù)值的數(shù)據(jù)，壓縮效率更高。對(duì)于列存儲(chǔ)索引而言，查詢(xún)性能的提升很大程度上依賴(lài)列數(shù)據(jù)的高度壓縮，這會(huì)大幅減少存儲(chǔ)該列數(shù)據(jù)所占用的數(shù)據(jù)頁(yè)（Data Page），進(jìn)而大幅減少把數(shù)據(jù)加載到內(nèi)存所耗費(fèi)的內(nèi)存和時(shí)間。

CREATE[NONCLUSTERED]COLUMNSTOREINDEXindex_name
ONschema_name.table_name(column[,...n])
[WITH(DROP_EXISTING={ON|OFF}|MAXDOP=max_degree_of_parallelism)]
[ONpartition_scheme_name(column_name)|filegroup_name]

一旦表上創(chuàng)建了非聚集的列存儲(chǔ)索引，基礎(chǔ)表就變成只讀的（read-only），不能對(duì)基礎(chǔ)表做任何更新（insert，update，delete 或merge）操作，如果需要修改數(shù)據(jù)，那么，首先要禁用列存儲(chǔ)索引，然后更新數(shù)據(jù)，最后重建列存儲(chǔ)索引：

ALTERINDEXmycolumnstoreindexONmytableDISABLE;
--updatemytable--
ALTERINDEXmycolumnstoreindexonmytableREBUILD

由于創(chuàng)建或重建列存儲(chǔ)索引是IO密集型資源，十分耗費(fèi)內(nèi)存資源，因此必須在系統(tǒng)空閑的情況下，更新數(shù)據(jù)。

04、列存儲(chǔ)索引的空間使用

列存儲(chǔ)索引首先把數(shù)據(jù)分組，然后每個(gè)行組中的每個(gè)列構(gòu)成一個(gè)段（Segment），每段都是單獨(dú)存儲(chǔ)的，列存儲(chǔ)索引占用的存儲(chǔ)空間的大小是由所有段占用的硬盤(pán)空間的加和。

系統(tǒng)視圖：sys.column_store_segments 提供每個(gè)段的數(shù)據(jù)信息，每個(gè)段都是每個(gè)行組中的一列的數(shù)據(jù)的集合，例如，如果一個(gè)列存儲(chǔ)索引分為10個(gè)行組，每個(gè)行組有15個(gè)數(shù)據(jù)列，那么，該視圖將返回150個(gè)段。

selecti.object_id
,object_name(i.object_id)asobject_name
,i.nameasindex_name
,i.type_descasindex_type
,col_name(i.object_id,ic.column_id)asindex_column_name
,sum(s.row_count)asrow_count
,sum(s.on_disk_size)/1024/1024ason_disk_size_mb
fromsys.column_store_segmentss
innerjoinsys.partitionsp
ons.partition_id=p.partition_id
innerjoinsys.indexesi
onp.object_id=i.object_id
andp.index_id=i.index_id
innerjoinsys.index_columnsic
oni.object_id=ic.object_id
andi.index_id=ic.index_id
ands.column_id=ic.index_column_id
groupbyi.object_id
,i.index_id
,i.name
,i.type_desc
,ic.column_id
orderbyi.object_id
,i.name
,index_column_name

可以看出，列存儲(chǔ)索引中每個(gè)段占用的硬盤(pán)空間是很少的，加載到內(nèi)存所需要耗費(fèi)的時(shí)間，IO次數(shù)和內(nèi)存資源也是很少的，再配上性能更高的批處理模式，所以，列存儲(chǔ)能夠大幅度提高數(shù)據(jù)的查詢(xún)性能，特別是對(duì)星型聚合的查詢(xún)。
責(zé)任編輯：彭菁