SQL Server 第二章 第十节 执行计划Word文档下载推荐.docx
- 文档编号:8538873
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:30
- 大小:1.26MB
SQL Server 第二章 第十节 执行计划Word文档下载推荐.docx
《SQL Server 第二章 第十节 执行计划Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《SQL Server 第二章 第十节 执行计划Word文档下载推荐.docx(30页珍藏版)》请在冰点文库上搜索。
RowCountSpool
9.流聚合:
StreamAggregate
10.排序:
Sort
11.合并联接:
MergeJoin
12.合并间隔:
MergeInterval
13.拆分、折叠:
Split,Collapse
1.1.1.2断言:
Assert运算符是一个物理运算符。
在执行计划中,如果为中文版图形化执行计划,被称为“断言”,在英文版及非图形化执行计划中显示为Assert。
其图标为:
Assert
运算符用于验证条件。
例如,验证引用完整性或确保标量子查询返回一行。
对于每个输入行,Assert
运算符都要计算执行计划的
Argument
列中的表达式。
如果此表达式的值为NULL,则通过
Assert
运算符传递该行,并且查询执行将继续。
如果此表达式的值非空,则将产生相应的错误。
1.1.1.3断言与Check约束:
先来看看这段代码,在服务器执行时,先创建测试环境,使用TempDB是不错的选择:
USEtempdb
GO
IFOBJECT_ID('
TableAssert'
)ISNOTNULL
DROPTABLETableAssert
CREATETABLETableAssert(
IDINTEGER
GenderCHAR
(1)
)
ALTERTABLETableAssertADDCONSTRAINTck_Gender_M_FCHECK(GenderIN('
M'
'
F'
))
选中下面代码,不要执行,选择“显示估计的执行计划”,如图:
代码如下:
INSERTINTOTableAssert(ID,Gender)
VALUES(1,'
X'
)
从上图可见有一个操作符叫“断言(Assert)”,那么这个里面是什么东西呢?
把鼠标移到这个操作符上面可以看到下图:
注意上面的解释:
用于验证指定的条件是否存在,这个解释很直观,并且看谓词部分,说明了实际验证的内容,判断Gender字段的插入值是否属于F/M两种,如果不是则返回NULL。
断言操作符会针对验证返回值进行处理,如果验证返回NULL,则返回错误信息,也就是如果你直接执行INSERT语句就可以看到报错:
1.1.1.4断言与外键约束:
下面来看个关于外键约束的例子:
usetempdb
go
ALTERTABLETableAssertADDID_GendersINT
TableFOREIGN'
DROPTABLETableFOREIGN
CREATETABLETableFOREIGN(IDIntegerPRIMARYKEY,GenderCHAR
(1))
INSERTINTOTableFOREIGN(ID,Gender)VALUES(1,'
INSERTINTOTableFOREIGN(ID,Gender)VALUES(2,'
INSERTINTOTableFOREIGN(ID,Gender)VALUES(3,'
N'
ALTERTABLETableAssertADDCONSTRAINTfk_Tab2FOREIGNKEY(ID_Genders)REFERENCESTableFOREIGN(ID)
同样,我们使用估计执行计划测试一下INSERT语句:
语句如下:
INSERTINTOTableAssert(ID,ID_Genders,Gender)VALUES(1,4,'
这次我们使用另外一个工具:
SET
SHOWPLAN_TEXT
ON
按这种方式执行:
SETSHOWPLAN_TEXTON
会看到两个结果,第一个是语句,不用关,我们看第二个结果:
1.|--Assert(WHERE:
(CASEWHENNOT[Pass1009]AND[Expr1008]ISNULLTHEN(0)ELSENULLEND))
2.|--NestedLoops(LeftSemiJoin,PASSTHRU:
([tempdb].[dbo].[TableAssert].[ID_Genders]ISNULL),OUTERREFERENCES:
([tempdb].[dbo].[TableAssert].[ID_Genders]),DEFINE:
([Expr1008]=[PROBEVALUE]))
3.|--Assert(WHERE:
(CASEWHEN[tempdb].[dbo].[TableAssert].[Gender]<
>
'
AND[tempdb].[dbo].[TableAssert].[Gender]<
THEN(0)ELSENULLEND))
4.||--TableInsert(OBJECT:
([tempdb].[dbo].[TableAssert]),SET:
([tempdb].[dbo].[TableAssert].[ID]=[@1],[tempdb].[dbo].[TableAssert].[ID_Genders]=[@2],[tempdb].[dbo].[TableAssert].[Gender]=[Expr1004]),DEFINE:
([Expr1004]=CONVERT_IMPLICIT(char
(1),[@3],0)))
5.|--ClusteredIndexSeek(OBJECT:
([tempdb].[dbo].[TableFOREIGN].[PK__TableFOR__3214EC27173876EA]),SEEK:
([tempdb].[dbo].[TableFOREIGN].[ID]=[tempdb].[dbo].[TableAssert].[ID_Genders])ORDEREDFORWARD)
这个结果内容较多可能不直观,读者可以执行测试看结果。
可以看到里面有两次Assert,自下而上地阅读,第一个Assert(也就是下面那个,针对于图形化界面而言是右边那个,因为图形化执行计划是从右到左地阅读)是前面用于CHECK约束的,如果返回0则继续运行语句,否则返回错误。
对于第二个Assert用于检测两表关联的结果,其中“[Expr1008]ISNULL”(注意[Expr1008]不是固定的,根据每台机器可能返回不同值,在本人机器上的SQL2008/2012分别执行都得到不同的[Expr]值),我们需要知道[Expr1008]是什么,内容中有DEFINE:
([Expr1008]=[PROBEVALUE]),这就是表关联的结果。
如果INSERT语句中ID_Gender的值已经存在与TableFOREIGN,那么这个Probe(探测器)会返回关联值。
否则返回NULL。
所以这个“断言”是检查TableForeign中的值,如果没有找到INSERT中传入的值,断言会返回一个异常。
如果ID_Genders的值为NULL,那么SQLServer不能返回异常,而是返回“0”并继续运行语句。
如果运行上面的INSERT语句,SQLServer会返回异常,因为值为’X’,违反了check约束:
但是如果把X换成F再运行,还是会报错,因为违反了外键约束:
但是当把4换成NULL或1或2或3之后,再运行插入语句,就不会产生异常:
1.1.1.5断言与子查询:
断言操作符同样可以用于检查子查询,对于标量子查询不能返回多个值,但是有时候写法和数据的变动会引发多值错误。
此时断言扮演着校验标量子查询是否返回一个值的角色。
下面来看看这两个语句:
INSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssert),'
用上面的方法查看一下执行计划:
INSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssert),'
观察语句大概可以知道发生什么情况,第一个insert会成功(除非你已经修改过里面的数据),因为VALUES中的SELECT部分只返回一个值,但是第二个INSERT由于VALUES中的SELECT有两个值(第一个INSERT加入的),所以会报错。
结果如下:
([Expr1013]))
2.|--ComputeScalar(DEFINE:
([Expr1013]=CASEWHEN[tempdb].[dbo].[TableAssert].[Gender]<
AND[tempdb].[dbo].[TableAssert].[Gender]<
3.|--TableInsert(OBJECT:
([tempdb].[dbo].[TableAssert]),SET:
([tempdb].[dbo].[TableAssert].[ID]=[Expr1009],[tempdb].[dbo].[TableAssert].[Gender]=[Expr1010],[tempdb].[dbo].[TableAssert].[ID_Genders]=NULL))
4.|--Top(TOPEXPRESSION:
(
(1)))
5.|--ComputeScalar(DEFINE:
([Expr1009]=[Expr1012],[Expr1010]='
6.|--NestedLoops(LeftOuterJoin)
7.|--ConstantScan
8.|--Assert(WHERE:
(CASEWHEN[Expr1011]>
(1)THEN(0)ELSENULLEND))
9.|--StreamAggregate(DEFINE:
([Expr1011]=Count(*),[Expr1012]=ANY([tempdb].[dbo].[TableAssert].[ID])))
10.|--TableScan(OBJECT:
([tempdb].[dbo].[TableAssert]))
注意最内层的Assert:
可以看到SQLServer创建一个StreamAggregate(流汇聚,可从预估执行计划中看到其解释,后续会专门介绍)去计算子查询会返回多少数据,然后把这个值传递给断言用于检测。
作为已经商业化二十几年的产品,其核心(查询优化器)已经经过了很多年的积累和改进,高版本的SQLServer(如2008R2及以上版本,这个没有绝对标准),会对语句和表结构的当前情况来判断是否需要使用“断言,Assert”操作符。
比如:
INSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssertWHEREID=1),'
INSERTINTOTableAssert(ID,Gender)VALUES((SELECTTOP1IDFROMTableAssert),'
)
先不执行,开启估计执行计划再看图形化界面,可以看到如下结果:
因为优化器检测到第二个语句里面包含了TOP1,仅返回一行数据,所以没有必要引入断言来检测。
1.1.1.6总结:
到这里为止,对这个操作符的介绍已经完毕,下一篇会介绍串联操作符。
对于这个断言操作符,我们需要知道它是用来“验证”某些条件,但是每个操作符的引入都必将带来一定的开销,可是这些操作符的引入又是必须的,因为需要它们完成一些任务。
如果需要改进,不妨先看看它是用来检验什么,比如上面提到的子查询,可以通过使用TOP1、添加唯一约束等方式来减少这种校验。
但是所有改进都应该做充分的测试和论证。
1.1.2执行计划操作符详解——串联(Concatenation)
1.1.2.1前言:
本文开始讲述另外一个操作符串联(Concatenation),读者可以根据这个词(中英文均可)先幻想一下是干嘛的。
其实还是挺直观,就是把东西连起来,那么下面我们来看看到底连什么?
怎么连?
什么时候连?
1.1.2.2简介:
串联操作符既是物理操作符,也是逻辑操作符,在中文版SQLServer的图形化执行计划中称为“串联”,在其他格式及英文版本中称为“Concatenation”。
其图标为:
,它扫描多个输入并返回每个扫描的行。
通常用于实现T-SQL中的UNIONALL。
它可以有多个输入,但只有一个输出,就如多个集合UNIONALL一样,最终返回一个结果集,注意这里一直使用“集合/集”,关系数据库是基于集合论的,所以使用关系数据库时要以集合的思维去考虑问题。
在执行计划中的每个操作符,都要实现三个方法/函数:
Init()、GetNext()和Close()。
前面说了,串联操作符是其中一种可以接受多个输入的操作符,这些输入会在Init()方法中处理。
在Init()方法中,串联初始化然后建立所需的数据结构。
然后在运行GetNext()方法读取输入集中的第一行及后续行,直到把输入集合里面的所有数据读取完毕为止。
1.1.2.3环境搭建:
下面创建一个测试表并循环插入10000行数据。
TEST'
'
U'
DROPTABLETEST
CREATETABLETest(
IDINTIdentity(1,1)PRIMARYKEY
NomeVARCHAR(250)DEFAULTNewID()
SETNOCOUNTON
INSERTINTOTestDEFAULT
VALUES
GO10000
1.1.2.4串联演示:
前面提到,串联主要用于实现T-SQL的UNIONALL,那么现在就来看看UNIONALL的情况:
开启实际执行计划并运行下面语句:
SELECT*FROMTEST
UNIONALL
执行计划如下:
如果使用SETSHOWPLAN_TEXTON来查看的话可以看到如下结果:
这个图的含义是把4个“ClusteredIndexScan”的结果塞到一个结果集,然后调用Init()和GetNext()方法去遍历这些数据,然后输出。
另外需要说明的是这个操作符是根据T-SQL中结果集的出现顺序来处理的,为了证明这个想法,我们来改写一下语句:
WHEREID<
100
WHEREIDBETWEEN101AND1000
WHEREIDBETWEEN1001AND5000
WHEREID>
5001
然后看看输出:
对比一下参数可得每个ClusteredIndexSeek的顺序和语句的出现顺序是一致的。
另外读者可能留意到每行最后的ORDEREDFORWARD,其含义是扫描索引的顺序是按照聚集索引的顺序并向前扫描。
1.1.2.5总结:
本文主要演示了串联操作符的情况,并且主要以T-SQL中的UNIONALL来触发。
由于目前没有任何资料显示是否仅UNIONALL才会使用,所以这里也不做绝对的判断,读者只需要知道这个操作符的含义、常见情景即可。
另外读者可以使用UNION来检查执行计划,实际上UNION是不用串联的,因为它本质上需要去重,所以使用不同的操作符来实现,比如MergeJoin,在后续再介绍。
1.1.3执行计划操作符详解——计算标量(ComputeScalar)
1.1.3.1前言:
第三个常见的操作符计算标量(ComputeScalar)。
这个操作符的名字比较直观——进行一个标量计算并返回计算值。
官方说明:
ComputeScalar运算符通过对表达式求值来生成计算标量值。
该值可以返回给用户、在查询中的其他位置引用或二者皆可。
例如,在筛选谓词或联接谓词中就会出现二者皆可的情况。
该操作符的图标为:
,它既是一个逻辑操作符,也是一个物理操作符。
这个操作符可能不容易引起用户注意,因为一般我们看执行计划是因为语句有问题,而有问题的语句又通常是比较复杂或混乱的,这些语句生成的执行计划往往也非常复杂。
相对于整个执行计划来说,这个操作符通常是比较小开销的。
但是这个操作符之所以重要或常见,是因为它通常是由于游标处理或其他一些大范围查找引起的,这些操作可能在CPU存在压力时变得雪上加霜。
1.1.3.2演示:
使用TempDB做测试是一个不错的选择,简单重启一下SQL服务即可清空过去的操作,不过如果你发现重启后还在,那不妨检查一下是否建到Model数据库或者设置为启动时运行。
下面代码在TempDB中创建一个表,插入10000行数据后,循环100次进行数据检查:
CREATETABLEtest(IDIntIdentity(1,1)PRIMARYKEY,
NameVarChar(250)DEFAULTNewID())
INSERTINTOtestDEFAULTVALUES
GO10000--循环插入行数据
--下面代码循环100次,判断是否存在某个ID
DECLARE@IInt
SET@I=0
WHILE@I<
100
BEGIN
IFEXISTS(SELECTIDFROMtestWHEREID=@I)
BEGIN
PRINT'
存在这个ID'
END
SET@I=@I+1;
END
看一下图形化执行计划:
截图中红框部分表明使用了计算标量操作符,使用前面的方法,检查文本化执行计划:
END
可以看到执行计划使用计算标量操作符来检查嵌套循环(NestedLoop)是否返回了值,也就是说用于实现IFEXISTS操作。
如果使用Profiler来抓取信息,记住一下CPU开销:
下面改写一下语句来避免这个操作符:
DECLARE@IInt,@VarInt
SELECT@Var=IDFROMtestWHEREID=@I
IF@@ROWCOUNT>
0
再看看图形化执行计划:
及Profiler信息:
如果再检查文本化执行计划就可以看到只有一个操作符:
1.|--ClusteredIndexSeek(OBJECT:
([tempdb].[dbo].[test].[PK__test__3214EC27D8827737]),SEEK:
([tempdb].[dbo].[test].[ID]=[@I])ORDEREDFORWARD)
对比Profiler中的数据,没有使用计算标量的执行计划消耗更少的CPU和运行时间去完成结果,这里主要是演示计算标量,所以不对写法做更深入的研究。
但是从写法上看,使用了@@rowcount函数替代IFEXISTS,有时候会有一定的帮助,当然,并不是绝对的。
如果你觉得是数据量的原因,不妨再看看下面的脚本:
DECLARE@TabTABLE(IDSmallIntPRIMARYKEY)
SELECT'
A'
+'
-'
B'
FROM@Tab
然后看看图形化执行计划:
和文本化执行计划:
1.|--ComputeScalar(DEFINE:
([Expr1002]='
A
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server 第二章 第十节 执行计划 第二 执行 计划
![提示](https://static.bingdoc.com/images/bang_tan.gif)