一直以来,由于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相关的培训或是优化,架构等方面的需求可以联系我.为了小蓝天,为了家要更拼些.
