一直以來,由于SQL Server中沒有位圖索引使得面對一些場景,從業(yè)人員在索引選擇上受限,飽受詬病.其實熟悉SQL Server的朋友應(yīng)該知道,SQL Server雖然沒有位圖索引,但在特定環(huán)境下還是會采用位圖(Bitmap)過濾的,這次就為大家介紹下SQL Server的位圖過濾.
概念:關(guān)于位圖索引的概念我就不做過多介紹了,感興趣的朋友可以看下wikipedia
http://en.wikipedia.org/wiki/Bitmap_index
優(yōu)勢:在重復(fù)率高,數(shù)據(jù)很少被更新的場景中(如一年之內(nèi)的年齡,汽車車型等)過濾高效.
?
SQL Server的位圖過濾采用的布隆過濾(bloom filter)方式,這里我簡單說下布隆過濾的實現(xiàn)方式.
實現(xiàn)方式:通過構(gòu)建一個長度X的位數(shù)組(bit array)(所有位為0),將要匹配的集合通過哈希函數(shù)映射到位數(shù)組中的相應(yīng)點中(相應(yīng)位為1),當判斷一個值是否存在時找bit array中對應(yīng)位是否為1就可以了.這個過程由SQL Server內(nèi)部自己完成.
如圖1-1所示,我將需要匹配的集合{神仙?,妖怪?,謝謝!}映射到bit array中,當有一條新記錄{悟空..}我判斷他是否在我的集合中,只需判斷相應(yīng)的位是否是1就可以了,圖中可以看出{悟空..}并不是所有位都為1,所以悟空并不在我的集合中.
?????????????????????? ? ? ? ? ? 圖1-1
具體到SQL Server中是如何實現(xiàn)的呢?我們還是通過一個實例來看.
測試環(huán)境腳本
USE
AdventureWorks
GO
SELECT
p.ProductID
+
(a.
number
*
1000
)
AS
ProductID,
p.Name
+
CONVERT
(
VARCHAR
, (a.
number
*
1000
))
AS
Name,
p.ProductNumber
+
'
-
'
+
CONVERT
(
VARCHAR
, (a.
number
*
1000
))
AS
ProductNumber,
p.MakeFlag,
p.FinishedGoodsFlag,
p.Color,
p.SafetyStockLevel,
p.ReorderPoint,
p.StandardCost,
p.ListPrice,
p.Size,
p.SizeUnitMeasureCode,
p.WeightUnitMeasureCode,
p.Weight,
p.DaysToManufacture,
p.ProductLine,
p.Class,
p.Style,
p.ProductSubcategoryID,
p.ProductModelID,
p.SellStartDate,
p.SellEndDate,
p.DiscontinuedDate
INTO
T1
FROM
Production.Product
AS
p
CROSS
JOIN
master..spt_values
AS
a
WHERE
a.type
=
'
p
'
AND
a.
number
BETWEEN
1
AND
50
GO
SELECT
ROW_NUMBER()
OVER
(
ORDER
BY
x.TransactionDate,
(
SELECT
NEWID
())
)
AS
TransactionID,
p1.ProductID,
x.TransactionDate,
x.Quantity,
CONVERT
(
MONEY
, p1.ListPrice
*
x.Quantity
*
RAND
(CHECKSUM(
NEWID
()))
*
2
)
AS
ActualCost
INTO
T2
FROM
(
SELECT
p.ProductID,
p.ListPrice,
CASE
WHEN
p.productid
%
26
=
0
THEN
26
WHEN
p.productid
%
25
=
0
THEN
25
WHEN
p.productid
%
24
=
0
THEN
24
WHEN
p.productid
%
23
=
0
THEN
23
WHEN
p.productid
%
22
=
0
THEN
22
WHEN
p.productid
%
21
=
0
THEN
21
WHEN
p.productid
%
20
=
0
THEN
20
WHEN
p.productid
%
19
=
0
THEN
19
WHEN
p.productid
%
18
=
0
THEN
18
WHEN
p.productid
%
17
=
0
THEN
17
WHEN
p.productid
%
16
=
0
THEN
16
WHEN
p.productid
%
15
=
0
THEN
15
WHEN
p.productid
%
14
=
0
THEN
14
WHEN
p.productid
%
13
=
0
THEN
13
WHEN
p.productid
%
12
=
0
THEN
12
WHEN
p.productid
%
11
=
0
THEN
11
WHEN
p.productid
%
10
=
0
THEN
10
WHEN
p.productid
%
9
=
0
THEN
9
WHEN
p.productid
%
8
=
0
THEN
8
WHEN
p.productid
%
7
=
0
THEN
7
WHEN
p.productid
%
6
=
0
THEN
6
WHEN
p.productid
%
5
=
0
THEN
5
WHEN
p.productid
%
4
=
0
THEN
4
WHEN
p.productid
%
3
=
0
THEN
3
WHEN
p.productid
%
2
=
0
THEN
2
ELSE
1
END
AS
ProductGroup
FROM
bigproduct p
)
AS
p1
CROSS
APPLY
(
SELECT
transactionDate,
CONVERT
(
INT
, (
RAND
(CHECKSUM(
NEWID
()))
*
100
)
+
1
)
AS
Quantity
FROM
(
SELECT
DATEADD
(dd,
number
,
'
20050101
'
)
AS
transactionDate,
NTILE(p1.ProductGroup)
OVER
(
ORDER
BY
number
)
AS
groupRange
FROM
master..spt_values
WHERE
type
=
'
p
'
)
AS
z
WHERE
z.groupRange
%
2
=
1
)
AS
x
實例Code
select
*
from
t1
inner
join
t2
on
t1.productid
=
t2.ProductID
where
t1.ProductID
<
1510
執(zhí)行計劃如圖1-2所示,再掃描t2表時實際上通過t1表的匹配結(jié)果集生成bit array(bitmap1008)進行過濾,從而使得20多萬的數(shù)據(jù)可以高效過濾,進而提升語句的整體效率.
?
???????????????????????????????????????????????????????????? 圖1-2
也許有人會說,既然Bitmap過濾如此強悍為什么這個運算符在日常執(zhí)行計劃中并不常見呢?的確SQL Server在Bitmap過濾上有限制.只有在并行hash join,merge join的情形中才會使用這個技術(shù)(實際串行計劃hash join中也有可能采用,但不顯示).
其實位圖過濾(位圖索引)的應(yīng)用場景我感覺還是不少的,由于 SQL Server沒有位圖索引,針對優(yōu)化器自身使用的Bitmap 過濾又有種種限制,這個限制了這個優(yōu)秀算法的使用空間,為此我還專門給微軟SQL Server團隊提了建議,建議放寬/可控bitmap過濾的使用.
注: 關(guān)于位圖索引的使用,大家可以參考oracle中的技術(shù)文檔
http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html
?? 關(guān)于布隆過濾器的使用可以參考wikipedia
http://en.wikipedia.org/wiki/Bloom_filter
?
也許大家有疑問既然SQL Server中Bitmap這么不容易出現(xiàn),那對我們調(diào)優(yōu)還有什么幫助呢?
這個給大家講個我們實際生產(chǎn)過程中的應(yīng)用.在我寫的 SQL Server優(yōu)化技巧之SQL Server中的"MapReduce" 博客中,不少朋友對我調(diào)整的那個系統(tǒng)參數(shù)興趣極大:),這里大概講下相關(guān)的調(diào)整過程.
背景:雙11活動中,公司網(wǎng)站訪問量明顯增加,發(fā)現(xiàn)某臺數(shù)據(jù)庫實例資源消耗上升明顯.通過DMV捕獲其中消耗資源的語句發(fā)現(xiàn)資源大多被個別高并發(fā)的語句消耗.
語句執(zhí)行計劃截圖圖2-1
????????????????????????????? 圖2-1
可以看出絕大多數(shù)消耗被Sort占據(jù).
由于Sort是典型的計算密集型操作,消耗CPU的同時消耗大量內(nèi)存.
在沒有溢出到tempdb的sort采用的算法是快速排序,內(nèi)存消耗將至少是排序結(jié)果集的200%以上,本例中單條查詢的內(nèi)存消耗在600MB以上,高并發(fā),加上語句執(zhí)行周期長(2s以上)使得單條語句長期占用內(nèi)存,影響B(tài)uffer Pool的穩(wěn)定,進而影響吞吐.同時帶來不好的用戶體驗.
通過對語句實際分析,發(fā)現(xiàn)如果采用并行執(zhí)行,優(yōu)化器是可以利用Bitmap過濾,進而改善整體查詢.
語句執(zhí)行計劃截圖圖2-2
????????????????????????? 圖2-2
可以看出在并行執(zhí)行計劃中由于采用了Bitmap過濾,使得并行響應(yīng)時間縮短為不到0.3s,同時CPU時間縮短為1s并且內(nèi)存的消耗由600MB+減少至不到300M,這樣減少資源使用的同時也提升了用戶體驗,并且由于響應(yīng)時間不到0.3s使得查詢內(nèi)存的占用時間明顯縮短,保證了Buffer Pool的穩(wěn)定,進而確保吞吐基本穩(wěn)定.
?
調(diào)整方案的抉擇
實際上要優(yōu)化器針對某些查詢使用并行執(zhí)行計劃,我們是有幾種方案供選擇的
Plan Guide, Trace Flag , cost threshold for parallelism
?
由于當時的語句是個復(fù)雜的拼串語句,在query cache中發(fā)現(xiàn)針對相關(guān)語句存在不少不同的query_hash,此時如果使得Plan Guide調(diào)整復(fù)雜,不確定因素多,因此未采用.
?
針對特定的語句采用Trace flag(8649)對特定語句調(diào)整其實是最具針對性的,但是考慮到代碼中實際上是需要研發(fā)同事參與的,在特定的時間窗口(雙11)能不給別人找事兒就是運維人員最主要的出發(fā)點(同時也是運維人員價值的側(cè)面直觀體現(xiàn)).
?
因此決定采用并行閾值,使系統(tǒng)自動出發(fā)并行,并調(diào)整合適的并行度.調(diào)整并行閾值時我當時并未采用一般的二分法進行定位調(diào)整,考慮到并行閾值調(diào)整是實例級調(diào)整,會清空plan cache,影響很大,多一次調(diào)整就多一次性能抖動(甚至多一次意外).這時在一個時間段內(nèi)我對實例的高消耗,出鏡率高的查詢進行采樣,分別統(tǒng)計他們的subtree cost,進而大概確定了最小影響的閾值區(qū)間,并進行調(diào)整.由于本人人品不錯:),一次調(diào)整就OK了.
之后CPU下降明顯,訪問量繼續(xù)升高.
?
結(jié)語:無論是日常,還是特殊時段的運維,都需要我們確保頭腦冷靜的同時依靠自己掌握的知識選擇最合理的解決方案.
?
/*******************************************************************/
再次奉上我兒子小藍天的靚照.
小寶貝出生了,壓力增加,動力更強了,哪些朋友如果有SQL Server相關(guān)的培訓(xùn)或是優(yōu)化,架構(gòu)等方面的需求可以聯(lián)系我.為了小藍天,為了家要更拼些.
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
