主要是和普通的索引進(jìn)行對(duì)比：
/*
*******************
準(zhǔn)備數(shù)據(jù)
*****************
*/
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
--
順帶提一下，因?yàn)?into 會(huì)把 identity 也寫進(jìn)去，為了方便 我就把ColumnStoreTest 的 identity 給散掉了
@i 用12 可能數(shù)據(jù)量有點(diǎn)多，可以自己調(diào)整
/*
*************************
創(chuàng)建columnstrore index
***********************
*/
create
index
idx_CustomerID
on
ColumnStoreTest(CustomerID,Freight)
create
columnstore
index
csidx_CustomerID
on
ColumnStoreTest(CustomerID,Freight)
?
這個(gè)是使用第一個(gè)索引測(cè)試產(chǎn)生的結(jié)果
? SQL Server 分析和編譯時(shí)間:
? CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 5 毫秒。
(
89
行受影響)
表
'
ColumnStoreTest
'
。掃描計(jì)數(shù)
5
，邏輯讀取
7352
次，物理讀取
0
次，預(yù)讀
32
次，lob 邏輯讀取
0
次，lob 物理讀取
0
次，lob 預(yù)讀
0
次。
(
6
行受影響)
(
1
行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
1529
毫秒，占用時(shí)間
=
544
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
執(zhí)行計(jì)劃也沒(méi)什么特別的就是 普通的索引掃描
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 分析和編譯時(shí)間:
CPU 時(shí)間
=
16
毫秒，占用時(shí)間
=
93
毫秒。
(
89
行受影響)
表
'
ColumnStoreTest
'
。掃描計(jì)數(shù)
4
，邏輯讀取
34
次，物理讀取
2
次，預(yù)讀
18
次，lob 邏輯讀取
0
次，lob 物理讀取
0
次，lob 預(yù)讀
0
次。
表
'
Worktable
'
。掃描計(jì)數(shù)
0
，邏輯讀取
0
次，物理讀取
0
次，預(yù)讀
0
次，lob 邏輯讀取
0
次，lob 物理讀取
0
次，lob 預(yù)讀
0
次。
(
7
行受影響)
(
1
行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
63
毫秒，占用時(shí)間
=
281
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
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個(gè)結(jié)果中看出，邏輯讀的數(shù)量columnstore index 明顯比 普通索引的少，這也就是 columnstore 索引的優(yōu)勢(shì)
但是如果是普通的select * from where 這類語(yǔ)句那columnstore index 還有優(yōu)勢(shì)嘛？
是不是和 oracle的bitmapindex 一樣在 or 語(yǔ)句中 也很有優(yōu)勢(shì)呢？
在columnstore
index
狀況下的執(zhí)行計(jì)劃沒(méi)有一點(diǎn)優(yōu)勢(shì)：
因?yàn)榇蠹覍?duì)非聚集索引比較了解，我也就不發(fā)非聚集索引在這種狀況下的執(zhí)行計(jì)劃了。
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'))
都已經(jīng)是表掃描了其實(shí)也沒(méi)什么好說(shuō)的了。
上面的例子是再選擇性低的情況下的執(zhí)行計(jì)劃。
那么如果選擇性高又會(huì)怎么樣呢？
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間
=
16
毫秒，占用時(shí)間
=
28
毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
(
1
行受影響)
表
'
ColumnStoreTest
'
。掃描計(jì)數(shù)
1
，邏輯讀取
12
次，物理讀取
0
次，預(yù)讀
2
次，lob 邏輯讀取
0
次，lob 物理讀取
0
次，lob 預(yù)讀
0
次。
(
4
行受影響)
(
1
行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
86
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
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 分析和編譯時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
9
毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
(
1
行受影響)
表
'
ColumnStoreTest
'
。掃描計(jì)數(shù)
1
，邏輯讀取
3
次，物理讀取
0
次，預(yù)讀
0
次，lob 邏輯讀取
0
次，lob 物理讀取
0
次，lob 預(yù)讀
0
次。
(
4
行受影響)
(
1
行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
92
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
0
毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間
=
0
毫秒，占用時(shí)間
=
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 是非聚集索引
仔細(xì)比較邏輯讀，就能看出，在高選擇性，傳統(tǒng)索引是比較又優(yōu)勢(shì)的。
關(guān)于or，理論上來(lái)說(shuō)是columnstore index 比非聚集索引又優(yōu)勢(shì)。
因?yàn)槲蚁嘈牛琧olumnstore index 是和bitmap index 相同原理的。
如果對(duì)bitmap index 不太了解可以參考：《
expert oracle database architecture》中的相關(guān)章節(jié)
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
