一直以來,由于SQL Server中沒有位圖索引使得面對一些場景,從業人員在索引選擇上受限,飽受詬病.其實熟悉SQL Server的朋友應該知道,SQL Server雖然沒有位圖索引,但在特定環境下還是會采用位圖(Bitmap)過濾的,這次就為大家介紹下SQL Server的位圖過濾.
概念:關于位圖索引的概念我就不做過多介紹了,感興趣的朋友可以看下wikipedia
http://en.wikipedia.org/wiki/Bitmap_index
優勢:在重復率高,數據很少被更新的場景中(如一年之內的年齡,汽車車型等)過濾高效.
?
SQL Server的位圖過濾采用的布隆過濾(bloom filter)方式,這里我簡單說下布隆過濾的實現方式.
實現方式:通過構建一個長度X的位數組(bit array)(所有位為0),將要匹配的集合通過哈希函數映射到位數組中的相應點中(相應位為1),當判斷一個值是否存在時找bit array中對應位是否為1就可以了.這個過程由SQL Server內部自己完成.
如圖1-1所示,我將需要匹配的集合{神仙?,妖怪?,謝謝!}映射到bit array中,當有一條新記錄{悟空..}我判斷他是否在我的集合中,只需判斷相應的位是否是1就可以了,圖中可以看出{悟空..}并不是所有位都為1,所以悟空并不在我的集合中.
?????????????????????? ? ? ? ? ? 圖1-1
具體到SQL Server中是如何實現的呢?我們還是通過一個實例來看.
測試環境腳本
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
執行計劃如圖1-2所示,再掃描t2表時實際上通過t1表的匹配結果集生成bit array(bitmap1008)進行過濾,從而使得20多萬的數據可以高效過濾,進而提升語句的整體效率.
?
???????????????????????????????????????????????????????????? 圖1-2
也許有人會說,既然Bitmap過濾如此強悍為什么這個運算符在日常執行計劃中并不常見呢?的確SQL Server在Bitmap過濾上有限制.只有在并行hash join,merge join的情形中才會使用這個技術(實際串行計劃hash join中也有可能采用,但不顯示).
其實位圖過濾(位圖索引)的應用場景我感覺還是不少的,由于 SQL Server沒有位圖索引,針對優化器自身使用的Bitmap 過濾又有種種限制,這個限制了這個優秀算法的使用空間,為此我還專門給微軟SQL Server團隊提了建議,建議放寬/可控bitmap過濾的使用.
注: 關于位圖索引的使用,大家可以參考oracle中的技術文檔
http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html
?? 關于布隆過濾器的使用可以參考wikipedia
http://en.wikipedia.org/wiki/Bloom_filter
?
也許大家有疑問既然SQL Server中Bitmap這么不容易出現,那對我們調優還有什么幫助呢?
這個給大家講個我們實際生產過程中的應用.在我寫的 SQL Server優化技巧之SQL Server中的"MapReduce" 博客中,不少朋友對我調整的那個系統參數興趣極大:),這里大概講下相關的調整過程.
背景:雙11活動中,公司網站訪問量明顯增加,發現某臺數據庫實例資源消耗上升明顯.通過DMV捕獲其中消耗資源的語句發現資源大多被個別高并發的語句消耗.
語句執行計劃截圖圖2-1
????????????????????????????? 圖2-1
可以看出絕大多數消耗被Sort占據.
由于Sort是典型的計算密集型操作,消耗CPU的同時消耗大量內存.
在沒有溢出到tempdb的sort采用的算法是快速排序,內存消耗將至少是排序結果集的200%以上,本例中單條查詢的內存消耗在600MB以上,高并發,加上語句執行周期長(2s以上)使得單條語句長期占用內存,影響Buffer Pool的穩定,進而影響吞吐.同時帶來不好的用戶體驗.
通過對語句實際分析,發現如果采用并行執行,優化器是可以利用Bitmap過濾,進而改善整體查詢.
語句執行計劃截圖圖2-2
????????????????????????? 圖2-2
可以看出在并行執行計劃中由于采用了Bitmap過濾,使得并行響應時間縮短為不到0.3s,同時CPU時間縮短為1s并且內存的消耗由600MB+減少至不到300M,這樣減少資源使用的同時也提升了用戶體驗,并且由于響應時間不到0.3s使得查詢內存的占用時間明顯縮短,保證了Buffer Pool的穩定,進而確保吞吐基本穩定.
?
調整方案的抉擇
實際上要優化器針對某些查詢使用并行執行計劃,我們是有幾種方案供選擇的
Plan Guide, Trace Flag , cost threshold for parallelism
?
由于當時的語句是個復雜的拼串語句,在query cache中發現針對相關語句存在不少不同的query_hash,此時如果使得Plan Guide調整復雜,不確定因素多,因此未采用.
?
針對特定的語句采用Trace flag(8649)對特定語句調整其實是最具針對性的,但是考慮到代碼中實際上是需要研發同事參與的,在特定的時間窗口(雙11)能不給別人找事兒就是運維人員最主要的出發點(同時也是運維人員價值的側面直觀體現).
?
因此決定采用并行閾值,使系統自動出發并行,并調整合適的并行度.調整并行閾值時我當時并未采用一般的二分法進行定位調整,考慮到并行閾值調整是實例級調整,會清空plan cache,影響很大,多一次調整就多一次性能抖動(甚至多一次意外).這時在一個時間段內我對實例的高消耗,出鏡率高的查詢進行采樣,分別統計他們的subtree cost,進而大概確定了最小影響的閾值區間,并進行調整.由于本人人品不錯:),一次調整就OK了.
之后CPU下降明顯,訪問量繼續升高.
?
結語:無論是日常,還是特殊時段的運維,都需要我們確保頭腦冷靜的同時依靠自己掌握的知識選擇最合理的解決方案.
?
/*******************************************************************/
再次奉上我兒子小藍天的靚照.
小寶貝出生了,壓力增加,動力更強了,哪些朋友如果有SQL Server相關的培訓或是優化,架構等方面的需求可以聯系我.為了小藍天,為了家要更拼些.
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
