一、概述
列存儲索引是SQL Server 2012中為提高數據查詢的性能而引入的一個新特性，顧名思義，數據以列的方式存儲在頁中，不同于聚集索引、非聚集索引及堆表等以行為單位的方式存儲。因為它并不要求存儲的列必須唯一，因此它可以通過壓縮將重復的列合并，從而減少查詢時的磁盤IO，提高效率。
為了分析列存儲索引，我們先看看B樹或堆中的數據的存儲方式，如下圖，在page1上，數據是按照行的方式存儲數據的，假設一行有10列，那么在該頁上，實際的存儲也會以每行10列的方式存儲，如下圖中的C1到C10。
假設我們執行select c1,c2 from table時，數據庫會讀取整個page1，顯然，從C3到C10并不是我們想要的數據，但因為數據庫每次讀的最小單位是一頁，因此這些不得不都加載到內存中。如果數據頁多時，必然要消耗更過的IO和內存。
如果是列存儲索引，數據按列的方式存儲在一個頁面中，如下圖，page1中只存儲表中C1列，page2只存儲c2列，以此類推，page10存儲c10列。
假設我們執行select c1,c2 from table時，結果會怎樣呢？數據庫只會讀page1和page2，至于page3到page10因為沒有對應的數據，數據庫不會去讀這些頁，也不會加載到內存中，相比行存儲而言，減少了磁盤IO和優化了內存的使用。
下文做了一個技術驗證，用來分析列存儲索引的查詢性能。
思路：做兩張一模一樣的分區表（分區表可以更好的展示效果），含1000000行數據，然后給其中一張表（sales2）建立聚集索引，另一張表（sales）建列存儲索引，最后來對比這兩張表的查詢性能。
二、創建表
先做兩張相同的表，創建的語句如下：
create partition function pf (date) as range left for values
('20110712', '20110713', '20110714', '20110715', '20110716');
go
create partition scheme ps as partition pf all to ([PRIMARY]);
go
create table sales (
[id] int not null identity (1,1),
[date] date not null,
itemid smallint not null,
price money not null,
quantity numeric(18,4) not null)
on ps([date]);
go
declare @i int = 0;
begin transaction;
while @i < 1000000
begin
declare @date date = dateadd(day, @i /250000.00, '20110712');
insert into sales2 ([date], itemid, price, quantity)
values (@date, rand()*10000, rand()*100 + 100, rand()* 10.000+1);
set @i += 1;
if @i % 10000 = 0
begin
raiserror (N'Inserted %d', 0, 1, @i);
commit;
begin tran;
end
end
commit;
GO
三、查詢含聚集鍵的表
CREATE CLUSTERED INDEX Clu_sales2_index ON sales2(date,price,quantity) on ps([date])；
查看表的存儲信息
select * from sys.system_internals_partitions p
where p.object_id = object_id('sales2');
select au.* from sys.system_internals_allocation_units au
join sys.system_internals_partitions p
on p.partition_id = au.container_id
where p.object_id = object_id('sales2');
GO
該表一共有6個分區，其中只有4個分區有數據，每個分區250000行，已使用1089頁，。
(2) 執行查詢語句 （注意清掉緩存）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT COUNT(*),SUM(price*quantity) FROM sales2 WHERE date='20110713';
GO
我們可以看到，在這個查詢中，一共有1089次邏輯讀（等于該表每個分區中的已使用頁數），CPU時間為62毫秒，占用時間為261毫秒。
備注：CPU時間，執行語句的時間；
占用時間，從磁盤讀取數據開始到完全處理使用的時間。
四、查詢含列存儲索引的表
(1) 創建表sales的列存儲索引
create columnstore index cs_sales_price on sales ([date], price, quantity) on ps([date]);
查看表的存儲信息：
select * from sys.system_internals_partitions p
where p.object_id = object_id('sales')
and index_id = 2;
select au.* from sys.system_internals_allocation_units au
join sys.system_internals_partitions p
on p.partition_id = au.container_id
where p.object_id = object_id('sales')
and index_id = 2;
GO
在建有列存儲索引后，表的行數并沒有改變，每個分區依然還是250000行，但頁面數明顯減少，且頁的類型由原來的IN_ROW_DATA變成了LOB_DATA。
(2) 執行查詢語句
select count(*), sum(price*quantity) from sales where date = '20110713'
在這個查詢中，一共有363次邏輯讀（等于該表每個分區），CPU時間為93毫秒，占用時間為191毫秒。
總結
從兩次查詢的結果來看，無論是邏輯讀的次數和占用時間，在列存儲索引的表中執行查詢明顯要快于聚集索引的表。
而且，從兩種表的存儲結構中可以看到，列存儲索引占用的頁面數量較聚集索引的少，這也印證了列存儲索引的壓縮功能。
備注：通過兩次查詢，我們看到兩者的CPU時間差距不是很大，相反聚集索引占用的時間更小，考慮到列存儲實際上是壓縮存儲,我認為在一張小表或者簡單的表中,對列存儲索引差查詢或許會占用多的CPU時間，因為查詢時需要解壓（我沒有具體驗證過），因此列存儲索引在小表中的優勢主要體現在IO和空間上，實際上列存儲索引的對象往往是含有大數據量的表，數據量越大，其優勢體現越明顯。
說明：準確的說本文并不是原創，文章是從如下地址翻譯過來，然后結合自己的實踐，增加了一些自己的理解。
http://rusanu.com/2011/07/13/how-to-update-a-table-with-a-columnstore-index/
文章列表
留言列表
