使用过滤统计信息解决基数预估错误

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

基数预估是SQL Server里一颗隐藏的宝石。一般而言,基数预估指的是,在查询编译期间,查询优化器尝试找出在执行计划里从各个运算符平均返回的行数。这个估计用来驱动计划本身生成并选择正确的计划运算符——例如像Nested Loop, Merge Join,还是Hash Join的物理连接。当这些估计错误时,查询优化器就会选择错误的计划运算符,相信我——你的查询就会非常非常非常慢!

查询优化器使用称为统计信息对象作为基数预估。每次当你创建一个索引,SQL Server在下面也会创建一个统计对象。这个对象描述了那个索引的数据分布。另外,在查询执行时,SQL Server也能创建统计信息对象,在必须的时候(自动创建统计信息)。数据分布本身(复合索引键的第一列)被描述为所谓的直方图(Histogram)

直方图最痛苦之一就是最大只有200的步长。步长是对于你所给定列数据一部分的数据分布情况描述。你的表变得越大,你的直方图就越不准确,因为你有最大200的步长(直方图必须尽可能紧凑,它必须复核8kb的页)。

在复合索引键里其他列,SQL Server在统计信息对象里用所谓的密度向量(Density Vector)来保存,它是复合索引键唯一值是如何的情况描述(彼此结合在一起)。例如在某列里有3个不同值,那列的密度向量是0.33333(1/3)。

从SQL Server 2008开始,SQL Server支持所谓的过滤统计信息(Filtered Statistics)(和过滤索引对应)。使用过滤统计信息,你可以为数据的子集创建统计信息对象。对于那个数据子集,你也会有直方图和密度向量。如果在你的数据里有极端值,你可以对那个范围的数据创建过滤统计信息对象,当那个范围的数据被查询时,就可以让查询优化器更好的估计返回的行数。因此使用过滤统计信息,你就提高了基数预估的准确性,SQL Server就会给更好的执行计划性能。下面代码显示在SQL Server 2008及后续版本里如何创建过滤统计信息对象: 

1 CREATE STATISTICS Country_Austria ON Country(ID) 
2 WHERE Name = 'Austria' 
3 GO

 从上面代码可以看到,你用WHERE子句限制表数据的子集,那会通过新的过滤统计信息对象来描述这些数据。但也只有的你的查询也包含这个where条件,查询优化器才可以只用这个新的统计信息对象,就像这样:

1 SELECT SalesAmount FROM Country
2 INNER JOIN Orders ON Country.ID = Orders.ID
3 WHERE Name = 'Austria'
4 GO

如果在的查询里并不包含同样的WHERE子句,查询优化期在执行计划里访问的索引的统计信息还是原来默认的。如果你对刚才的查询启用9204的跟踪标记,你就可以看到在基数预估时,那个统计信息被查询优化器使用:

复制代码
 1 SELECT SalesAmount FROM Country
 2 INNER JOIN Orders ON Country.ID = Orders.ID
 3 WHERE Name = 'Austria'
 4 OPTION
 5 (
 6     RECOMPILE,-- Used to see the Statistics Output
 7     QUERYTRACEON 3604,-- Redirects the output to SSMS
 8     QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 9 )
10 GO
复制代码

查询本身也会编译(因为RECOMPLIE查询提示,即使查询计划已被缓存),因此在SSMS的消息窗,你就可以看到拿个统计信息被用做基数预估。

以过滤统计信息的简单介绍为基础,我想给你通过实例展示下,过滤统计信息是如何提高执行计划质量的。 

复制代码
 1 -- Create a new database
 2 CREATE DATABASE FilteredStatistics
 3 GO
 4  
 5 -- Use it
 6 USE FilteredStatistics
 7 GO
 8  
 9 -- Create a new table
10 CREATE TABLE Country
11 (
12 ID INT PRIMARY KEY, 
13 Name VARCHAR(100)
14 ) 
15 GO
16  
17 -- Create a new table
18 CREATE TABLE Orders
19 (
20 ID INT, 
21 SalesAmount DECIMAL(18, 2)
22 ) 
23 GO
复制代码

 我们在表上建立相应的索引:

复制代码
1 -- Create a Non-Clustered Index
2 CREATE NONCLUSTERED INDEX idx_Name ON Country(Name) 
3 GO
4  
5 -- Create a Clustered Index
6 CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount) 
7 GO
复制代码

最后往2个表里插入初始数据: 

复制代码
 1 -- Insert a few records into the Lookup Table
 2 INSERT INTO Country VALUES(0, 'Austria') 
 3 INSERT INTO Country VALUES(1, 'UK')
 4 INSERT INTO Country VALUES(2, 'France') 
 5 GO
 6  
 7 -- Insert uneven distributed order data
 8 INSERT INTO Orders VALUES(0, 0)
 9  
10 DECLARE @i INT = 1 
11  
12 WHILE @i <= 1000
13 BEGIN 
14 INSERT INTO Orders VALUES (1, @i) 
15 SET @i += 1
16 END
17 GO
复制代码

 为了保证所有的统计信息都已经是最新的,我用全扫描更新了统计信息:

1 -- Update the Statistics on both tables
2 UPDATE STATISTICS Country WITH FULLSCAN 
3 UPDATE STATISTICS Orders WITH FULLSCAN 
4 GO

点击工具栏的显示包含实际的执行计划。我们来执行下列的查询:

复制代码
 1 SELECT SalesAmount FROM Country
 2 INNER JOIN Orders ON Country.ID = Orders.ID
 3 WHERE Name = 'UK'
 4 OPTION
 5 (
 6 RECOMPILE,-- Used to see the Statistics Output
 7     QUERYTRACEON 3604,-- Redirects the output to SSMS
 8     QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 9 )
10 GO
复制代码

从执行计划里可以看到,基数预估出现了大问题。

SQL Server 估计行数是501,聚集索引查找运算符的实际行数是1000。SQL Server这里使用idx_ID_SalesAmount统计信息对象的密度向量来做那个估计:密度向量是0.5(在那列我们只有2个不同值),因此估计行数是501(1001 * 0.5)。

当你用Austria参数值执行同样的查询,SQL Server又一次估计行数是501,但是查询本身值返回1行……当其他运算符使用这些估计做运算时,这个行为在执行计划里会有巨大的副作用。例如,Sort和Hash运算符根据这些估计作为内存授予需要的大小。如果低估,你的查询会涌向TempDb,如果高估,你就在浪费内存,当你有大量的并发查询是,就会导致竞争问题(查询内存的最大数量是有资源管理器限制的……)

你可以使用过滤统计信息来帮助这些特殊场景。这个会给SQL Server关于数据本身分布的更多信息,也会在基数预估里得到帮助。对于那个特殊场景,我创建2个不同的过滤统计信息,对于每个国家我都创建各自的过滤统计信息对象: 

复制代码
1 -- Fix the problem by creating Filtered Statistics Objects
2 CREATE STATISTICS Country_UK ON Country(ID) 
3 WHERE Name = 'UK'
4  
5 CREATE STATISTICS Country_Austria ON Country(ID) 
6 WHERE Name = 'Austria' 
7 GO
复制代码

 现在当你重新执行查询时,最后你会看到基数预估是正确的:

 当你在你表上上创建了过滤统计信息时,你也要注意维护。从整个表本身——如果有20%的数据改变时,SQL Server会自动更新统计信息!!! 假设你有10000行的表,你在表的子集上创建了过滤统计信息,就定子集行数是500条。在这个情况下,当指定列有2000行改变时,SQL Server会更新过滤统计信息对象。因此你要更新过滤统计信息对象里4倍的数据,才会使统计信息失效然后它被更新(在过滤统计信息区间外,没有数据发生改变)。这是很糟糕的情况,当你使用过滤统计信息时,要记住这个。

希望这篇文章给你过滤统计信息的很好概述,对于给出的查询,你知道如何使用过滤统计信息帮助SQL Server提高基数预估。



本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4618818.html,如需转载请自行联系原作者

相关实践学习
使用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
相关文章
|
6月前
|
对象存储
统计数组中的重复数据的数量
这篇文章总结了5种统计数组中重复数据数量的方法。方法1和4使用for循环和对象存储计数;方法2和5利用`reduce`函数,其中方法5是最简写形式;方法3是特定场景下的应用,针对特定值计数。所有方法最终都返回一个对象,键为数组元素,值为出现次数。
|
数据挖掘 数据处理
人人都会点数据分析 | 了解统计指标与异常值的简单处理
人人都会点数据分析 | 了解统计指标与异常值的简单处理
113 0
|
iOS开发 索引
LeetCode--1773. 统计匹配检索规则的物品数量
给你一个数组 items ,其中 items[i] = [typei, colori, namei] ,描述第 i 件物品的类型、颜色以及名称。 另给你一条由两个字符串 ruleKey 和 ruleValue 表示的检索规则。 如果第 i 件物品能满足下述条件之一,则认为该物品与给定的检索规则 匹配 : ruleKey == "type" 且 ruleValue == typei 。 ruleKey == "color" 且 ruleValue == colori 。 ruleKey == "name" 且 ruleValue == namei 。 统计并返回 匹配检索规则的物品数量 。
78 0
LeetCode 1773. 统计匹配检索规则的物品数量
给你一个数组 items ,其中 items[i] = [typei, colori, namei] ,描述第 i 件物品的类型、颜色以及名称。
93 0
|
算法 前端开发
统计圆内格点数目
🎈每天进行一道算法题目练习,今天的题目是“统计圆内格点数目”。
192 0
|
算法 前端开发
【前端算法】独一无二的出现次数,统计次数加去重
给你一个整数数组 arr,请你帮忙统计数组中每个数的出现次数。 如果每个数的出现次数都是独一无二的,就返回 true;否则返回 false。
103 0
使用正则表达式统计VS2017项目的代码量(统计源码总行数)
使用正则表达式统计VS2017项目的代码量(统计源码总行数)
376 0
使用正则表达式统计VS2017项目的代码量(统计源码总行数)
|
BI
利用时间维度统计分页
在进行数据统计的时候,不能简单的通过数据表数据条数进行分页。这时,可以通过时间维度进行分页。 //以时间作为查询条件进行用户统计查询 $start_date = request('date')['start'] ? : ''; $end_date = request('date')['end'].
865 0