主要是和普通的索引進行對比:
/* *******************
準備數據
***************** */
select * into ColumnStoreTest from northwind..orders
declare @i int
set @i = 12
while ( @i > 0 )
begin
insert into ColumnStoreTest
select * from ColumnStoreTest
union all
select * from ColumnStoreTest
set @i = @i - 1
end
-- 順帶提一下,因為 into 會把 identity 也寫進去,為了方便 我就把ColumnStoreTest 的 identity 給散掉了
@i 用12 可能數據量有點多,可以自己調整
/* *************************
創建columnstrore index
*********************** */
create index idx_CustomerID on ColumnStoreTest(CustomerID,Freight)
create columnstore index csidx_CustomerID on ColumnStoreTest(CustomerID,Freight)
?
這個是使用第一個索引測試產生的結果
? SQL Server 分析和編譯時間:
? CPU 時間 = 0 毫秒,占用時間 = 5 毫秒。
( 89 行受影響)
表 ' ColumnStoreTest ' 。掃描計數 5 ,邏輯讀取 7352 次,物理讀取 0 次,預讀 32 次,lob 邏輯讀取 0 次,lob 物理讀取 0 次,lob 預讀 0 次。
( 6 行受影響)
( 1 行受影響)
SQL Server 執行時間:
CPU 時間 = 1529 毫秒,占用時間 = 544 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
執行計劃也沒什么特別的就是 普通的索引掃描
select CustomerID, sum (Freight) from ColumnStoreTest group by CustomerID
| -- Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [globalagg1006]=(0) THEN NULL ELSE [globalagg1008] END))
| -- Stream Aggregate(GROUP BY:([Northwind].[dbo].[ColumnStoreTest].[CustomerID]) DEFINE:([globalagg1006]=SUM([partialagg1005]), [globalagg1008]=SUM([partialagg1007])))
| -- Parallelism(Gather Streams, ORDER BY:([Northwind].[dbo].[ColumnStoreTest].[CustomerID] ASC))
| -- Stream Aggregate(GROUP BY:([Northwind].[dbo].[ColumnStoreTest].[CustomerID]) DEFINE:([partialagg1005]=COUNT_BIG([Northwind].[dbo].[ColumnStoreTest].[Freight]), [partialagg1007]=SUM([Northwind].[dbo].[ColumnStoreTest].[Freight])))
| -- Index Scan(OBJECT:([Northwind].[dbo].[ColumnStoreTest].[idx_CustomerID]), ORDERED FORWARD)
?
SQL Server 分析和編譯時間:
CPU 時間 = 16 毫秒,占用時間 = 93 毫秒。
( 89 行受影響)
表 ' ColumnStoreTest ' 。掃描計數 4 ,邏輯讀取 34 次,物理讀取 2 次,預讀 18 次,lob 邏輯讀取 0 次,lob 物理讀取 0 次,lob 預讀 0 次。
表 ' Worktable ' 。掃描計數 0 ,邏輯讀取 0 次,物理讀取 0 次,預讀 0 次,lob 邏輯讀取 0 次,lob 物理讀取 0 次,lob 預讀 0 次。
( 7 行受影響)
( 1 行受影響)
SQL Server 執行時間:
CPU 時間 = 63 毫秒,占用時間 = 281 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
select CustomerID, sum (Freight) from ColumnStoreTest group by CustomerID
| -- Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [globalagg1006]=(0) THEN NULL ELSE [globalagg1008] END))
| -- Stream Aggregate(GROUP BY:([Northwind].[dbo].[ColumnStoreTest].[CustomerID]) DEFINE:([globalagg1006]=SUM([partialagg1005]), [globalagg1008]=SUM([partialagg1007])))
| -- Sort(ORDER BY:([Northwind].[dbo].[ColumnStoreTest].[CustomerID] ASC))
| -- Parallelism(Gather Streams)
| -- Hash Match(Partial Aggregate, HASH:([Northwind].[dbo].[ColumnStoreTest].[CustomerID]), RESIDUAL:([Northwind].[dbo].[ColumnStoreTest].[CustomerID] = [Northwind].[dbo].[ColumnStoreTest].[CustomerID]) DEFINE:([partialagg1005]=COUNT_BIG([Northwind].[dbo].[ColumnStoreTest].[Freight]), [partialagg1007]=SUM([Northwind].[dbo].[ColumnStoreTest].[Freight])))
| -- Index Scan(OBJECT:([Northwind].[dbo].[ColumnStoreTest].[csidx_CustomerID]))
可以從這2個結果中看出,邏輯讀的數量columnstore index 明顯比 普通索引的少,這也就是 columnstore 索引的優勢
但是如果是普通的select * from where 這類語句那columnstore index 還有優勢嘛?
是不是和 oracle的bitmapindex 一樣在 or 語句中 也很有優勢呢?
在columnstore index 狀況下的執行計劃沒有一點優勢:
因為大家對非聚集索引比較了解,我也就不發非聚集索引在這種狀況下的執行計劃了。
select * from ColumnStoreTest where customerid = ' VINET ' or customerid = ' TOMSP '
| -- Parallelism(Gather Streams)
| -- Table Scan(OBJECT:([Northwind].[dbo].[ColumnStoreTest]), WHERE:([Northwind].[dbo].[ColumnStoreTest].[CustomerID]=N'TOMSP' OR [Northwind].[dbo].[ColumnStoreTest].[CustomerID]=N'VINET'))
都已經是表掃描了其實也沒什么好說的了。
上面的例子是再選擇性低的情況下的執行計劃。
那么如果選擇性高又會怎么樣呢?
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 16 毫秒,占用時間 = 28 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
( 1 行受影響)
表 ' ColumnStoreTest ' 。掃描計數 1 ,邏輯讀取 12 次,物理讀取 0 次,預讀 2 次,lob 邏輯讀取 0 次,lob 物理讀取 0 次,lob 預讀 0 次。
( 4 行受影響)
( 1 行受影響)
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 86 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SELECT * FROM [ ColumnStoreTest ] WHERE [ orderid ] = @1
| -- Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| -- Index Scan(OBJECT:([Northwind].[dbo].[ColumnStoreTest].[csidx_orderID]), WHERE:([Northwind].[dbo].[ColumnStoreTest].[OrderID]=(10248)))
| -- RID Lookup(OBJECT:([Northwind].[dbo].[ColumnStoreTest]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒,占用時間 = 9 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
( 1 行受影響)
表 ' ColumnStoreTest ' 。掃描計數 1 ,邏輯讀取 3 次,物理讀取 0 次,預讀 0 次,lob 邏輯讀取 0 次,lob 物理讀取 0 次,lob 預讀 0 次。
( 4 行受影響)
( 1 行受影響)
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 92 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SQL Server 執行時間:
CPU 時間 = 0 毫秒,占用時間 = 0 毫秒。
SELECT * FROM [ ColumnStoreTest ] WHERE [ orderid ] = @1
| -- Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| -- Index Seek(OBJECT:([Northwind].[dbo].[ColumnStoreTest].[idx_orderid]), SEEK:([Northwind].[dbo].[ColumnStoreTest].[OrderID]=(10248)) ORDERED FORWARD)
| -- RID Lookup(OBJECT:([Northwind].[dbo].[ColumnStoreTest]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
csidx_orderid 是columnstore index
idx_orderid 是非聚集索引
仔細比較邏輯讀,就能看出,在高選擇性,傳統索引是比較又優勢的。
關于or,理論上來說是columnstore index 比非聚集索引又優勢。
因為我相信,columnstore index 是和bitmap index 相同原理的。
如果對bitmap index 不太了解可以參考:《
expert oracle database architecture》中的相關章節
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
