前言: 很多很多地方对于语句的优化,一般比较靠谱的回复即使——把执行计划发出来看看。当然那些只看语句就说如何如何改代码,我一直都是拒绝的,因为这种算是纯蒙。根据本人经验,大量的性能问题单纯从语句来看很难发现瓶颈,同一个语句,由于环境的不同,差距非常大,所以比较合适的还是分析执行计划。 那么对于执行计划,一般使用图形化执行计划就差不多了,但是用过的人也有一些疑惑,里面的图标(称为操作符)并不非常直观。所以从本文开始,会整理一些不怎么常见但由比较重要的操作符并进行解释,对于那些表扫描、索引扫描、聚集索引扫描、索引查找、聚集索引查找这些非常常见的操作符,暂时不打算介绍。 只有了解一些重要且常见的操作符,才能对语句进行准确有效的性能分析和优化。 本系列文章预计包含下面操作符: - 断言:Assert (英文版本图形化界面的名字,中文版本中XML格式的执行计划和TEXT格式的执行计划的名字。下同)
- 串联:Concatenation
- 计算标量:Compute Scalar
- 键查找:Key Lookup
- 假脱机:Spools
- 表假脱机:Lazy Spool
- 索引假脱机:Index Spool
- 行计数假脱机:Row CountSpool
- 流聚合:Stream Aggregate
- 排序:Sort
- 合并联接:Merge Join
- 合并间隔:Merge Interval
- 拆分、折叠:Split,Collapse
接下来从断言开始介绍。原文出处:http://blog.csdn.net/dba_huangzj/article/details/50261747
断言:
Assert运算符是一个物理运算符。在执行计划中,如果为中文版图形化执行计划,被称为“断言”,在英文版及非图形化执行计划中显示为Assert。 其图标为: Assert 运算符用于验证条件。例如,验证引用完整性或确保标量子查询返回一行。对于每个输入行,Assert 运算符都要计算执行计划的 Argument 列中的表达式。如果此表达式的值为 NULL,则通过 Assert 运算符传递该行,并且查询执行将继续。如果此表达式的值非空,则将产生相应的错误。
断言与Check约束: 先来看看这段代码,在服务器执行时,先创建测试环境,使用TempDB是不错的选择:
- USE tempdb
- GO
-
- IF OBJECT_ID('TableAssert') IS NOT NULL
- DROP TABLE TableAssert
- GO
-
- CREATE TABLE TableAssert (
- ID INTEGER
- ,Gender CHAR(1)
- )
- GO
-
- ALTER TABLE TableAssert ADD CONSTRAINT ck_Gender_M_F CHECK (Gender IN ('M','F'))
- GO
选中下面代码,不要执行,选择“显示估计的执行计划”,如图:
代码如下: - INSERT INTO TableAssert(ID ,Gender )
- VALUES (1,'X')
- GO
从上图可见有一个操作符叫“断言(Assert)”,那么这个里面是什么东西呢?把鼠标移到这个操作符上面可以看到下图:
注意上面的解释:用于验证指定的条件是否存在,这个解释很直观,并且看谓词部分,说明了实际验证的内容,判断Gender字段的插入值是否属于F/M两种,如果不是则返回NULL。 断言操作符会针对验证返回值进行处理,如果验证返回NULL,则返回错误信息,也就是如果你直接执行INSERT语句就可以看到报错: 原文出处:http://blog.csdn.net/dba_huangzj/article/details/50261747
断言与外键约束:
下面来看个关于外键约束的例子: - use tempdb
- go
- ALTER TABLE TableAssert ADD ID_Genders INT
- GO
- IF OBJECT_ID('TableFOREIGN') IS NOT NULL
- DROP TABLE TableFOREIGN
- GO
- CREATE TABLE TableFOREIGN(ID Integer PRIMARY KEY, Gender CHAR(1))
- GO
- INSERT INTO TableFOREIGN(ID, Gender) VALUES(1, 'F')
- INSERT INTO TableFOREIGN(ID, Gender) VALUES(2, 'M')
- INSERT INTO TableFOREIGN(ID, Gender) VALUES(3, 'N')
- GO
- ALTER TABLE TableAssert ADD CONSTRAINT fk_Tab2 FOREIGN KEY (ID_Genders) REFERENCES TableFOREIGN(ID)
- GO
同样,我们使用估计执行计划测试一下INSERT语句:
语句如下: - INSERT INTO TableAssert(ID, ID_Genders, Gender) VALUES(1, 4, 'X')
这次我们使用另外一个工具:SET SHOWPLAN_TEXT ON 按这种方式执行: - SET SHOWPLAN_TEXT ON
- GO
- INSERT INTO TableAssert(ID, ID_Genders, Gender) VALUES(1, 4, 'X')
会看到两个结果,第一个是语句,不用关,我们看第二个结果: - |--Assert(WHERE:(CASE WHEN NOT [Pass1009] AND [Expr1008] IS NULL THEN (0) ELSE NULL END))
- |--Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[TableAssert].[ID_Genders] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[TableAssert].[ID_Genders]), DEFINE:([Expr1008] = [PROBE VALUE]))
- |--Assert(WHERE:(CASE WHEN [tempdb].[dbo].[TableAssert].[Gender]<>'F' AND [tempdb].[dbo].[TableAssert].[Gender]<>'M' THEN (0) ELSE NULL END))
- | |--Table Insert(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)))
- |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TableFOREIGN].[PK__TableFOR__3214EC27173876EA]), SEEK:([tempdb].[dbo].[TableFOREIGN].[ID]=[tempdb].[dbo].[TableAssert].[ID_Genders]) ORDERED FORWARD)
这个结果内容较多可能不直观,读者可以执行测试看结果。 可以看到里面有两次Assert,自下而上地阅读,第一个Assert(也就是下面那个,针对于图形化界面而言是右边那个,因为图形化执行计划是从右到左地阅读)是前面用于CHECK约束的,如果返回0则继续运行语句,否则返回错误。 对于第二个Assert用于检测两表关联的结果,其中“[Expr1008] IS NULL”(注意[Expr1008]不是固定的,根据每台机器可能返回不同值,在本人机器上的SQL 2008/2012分别执行都得到不同的[Expr]值),我们需要知道[Expr1008]是什么,内容中有DEFINE:([Expr1008] = [PROBE VALUE]),这就是表关联的结果。如果INSERT语句中ID_Gender的值已经存在与TableFOREIGN,那么这个Probe(探测器)会返回关联值。否则返回NULL。所以这个“断言”是检查TableForeign中的值,如果没有找到INSERT中传入的值,断言会返回一个异常。 如果ID_Genders的值为NULL,那么SQL Server不能返回异常,而是返回“0”并继续运行语句。如果运行上面的INSERT语句,SQL Server会返回异常,因为值为’X’,违反了check约束:
但是如果把X换成F再运行,还是会报错,因为违反了外键约束: 但是当把4换成NULL或1或2或3之后,再运行插入语句,就不会产生异常: 原文出处:http://blog.csdn.net/dba_huangzj/article/details/50261747
断言与子查询:
断言操作符同样可以用于检查子查询,对于标量子查询不能返回多个值,但是有时候写法和数据的变动会引发多值错误。此时断言扮演着校验标量子查询是否返回一个值的角色。 下面来看看这两个语句: - INSERT INTO TableAssert(ID, Gender) VALUES((SELECT ID FROM TableAssert), 'F')
- INSERT INTO TableAssert(ID, Gender) VALUES((SELECT ID FROM TableAssert), 'F')
用上面的方法查看一下执行计划: - SET SHOWPLAN_TEXT ON
- GO
- INSERT INTO TableAssert(ID,Gender) VALUES((SELECT ID FROM TableAssert), 'F')
- INSERT INTO TableAssert(ID,Gender) VALUES((SELECT ID FROM TableAssert), 'F')
观察语句大概可以知道发生什么情况,第一个insert会成功(除非你已经修改过里面的数据),因为VALUES中的SELECT部分只返回一个值,但是第二个INSERT由于VALUES中的SELECT有两个值(第一个INSERT加入的),所以会报错。 结果如下: - |--Assert(WHERE:([Expr1013]))
- |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN[tempdb].[dbo].[TableAssert].[Gender]<>'F' AND[tempdb].[dbo].[TableAssert].[Gender]<>'M' THEN (0) ELSE NULL END))
- |--Table Insert(OBJECT:([tempdb].[dbo].[TableAssert]),SET:([tempdb].[dbo].[TableAssert].[ID] =[Expr1009],[tempdb].[dbo].[TableAssert].[Gender] =[Expr1010],[tempdb].[dbo].[TableAssert].[ID_Genders] = NULL))
- |--Top(TOP EXPRESSION:((1)))
- |--ComputeScalar(DEFINE:([Expr1009]=[Expr1012], [Expr1010]='F'))
- |--Nested Loops(LeftOuter Join)
- |--ConstantScan
- |--Assert(WHERE:(CASE WHEN [Expr1011]>(1) THEN (0) ELSE NULL END))
- |--StreamAggregate(DEFINE:([Expr1011]=Count(*),[Expr1012]=ANY([tempdb].[dbo].[TableAssert].[ID])))
- |--Table Scan(OBJECT:([tempdb].[dbo].[TableAssert]))
注意最内层的Assert:
可以看到SQL Server创建一个StreamAggregate(流汇聚,可从预估执行计划中看到其解释,后续会专门介绍)去计算子查询会返回多少数据,然后把这个值传递给断言用于检测。 作为已经商业化二十几年的产品,其核心(查询优化器)已经经过了很多年的积累和改进,高版本的SQL Server(如2008 R2及以上版本,这个没有绝对标准),会对语句和表结构的当前情况来判断是否需要使用“断言,Assert”操作符。比如: - INSERT INTO TableAssert(ID, Gender) VALUES((SELECT ID FROM TableAssert WHERE ID = 1), 'F')
- INSERT INTO TableAssert(ID, Gender) VALUES((SELECT TOP 1 ID FROM TableAssert), 'F')
原文出处:http://blog.csdn.net/dba_huangzj/article/details/50261747
先不执行,开启估计执行计划再看图形化界面,可以看到如下结果:
因为优化器检测到第二个语句里面包含了TOP 1,仅返回一行数据,所以没有必要引入断言来检测。
总结:
到这里为止,对这个操作符的介绍已经完毕,下一篇会介绍串联操作符。对于这个断言操作符,我们需要知道它是用来“验证”某些条件,但是每个操作符的引入都必将带来一定的开销,可是这些操作符的引入又是必须的,因为需要它们完成一些任务。如果需要改进,不妨先看看它是用来检验什么,比如上面提到的子查询,可以通过使用TOP 1、添加唯一约束等方式来减少这种校验。但是所有改进都应该做充分的测试和论证。 原文出处:http://blog.csdn.net/dba_huangzj/article/details/50261747
|