SQL Server优化器特性-位图过滤(Bitmap)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:SQL Server优化器特性-位图过滤(Bitmap)一直以来,由于SQL Server中没有位图索引使得面对一些场景,从业人员在索引选择上受限,饱受诟病.其实熟悉SQL Server的朋友应该知道,SQL Server虽然没有位图索引,但在特定环境下还是会采用位图(Bitmap)过滤的,这次就为大家介绍下SQL Server的位图过滤.
原文: SQL Server优化器特性-位图过滤(Bitmap)

一直以来,由于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
View Code

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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
24天前
|
SQL 存储 数据库
SQL学习一:ACID四个特性,CURD基本操作,常用关键字,常用聚合函数,五个约束,综合题
这篇文章是关于SQL基础知识的全面介绍,包括ACID特性、CURD操作、常用关键字、聚合函数、约束以及索引的创建和使用,并通过综合题目来巩固学习。
26 1
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
98 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
58 6
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
306 1
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
431 0
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
273 3
|
4月前
|
SQL 算法 数据库
SQL优化器原理 - Join重排
保证等价性:不同的Join顺序可能产生相同的结果集,但执行成本可能不同。因此,在重排Join顺序时,必须确保结果集的等价性。
|
4月前
|
SQL 算法 数据库
SQL优化器原理 - Join重排。
保证等价性:不同的Join顺序可能产生相同的结果集,但执行成本可能不同。因此,在重排Join顺序时,必须确保结果集的等价性。