《Microsoft Sql server 2008 Internals》读书笔记--第八章The Query Optimizer(10)

lomg

　　《Microsoft Sql server 2008 Internal》读书笔记--目录索引
　　今天我们继续学习三个有关计划更新的概念：Halloween Protection、Split/Sort/Collapse和Merge
　　■Halloween Protection（Halloween保护，万圣节面具？呵呵）
　　Halloween保护描述了关系数据库中用于保证更新计划正确性的一种功能。这个功能的需求来自于更新计划的一次单纯实现中所发生的情况。执行一次更新的一种简单方法是使一个运算符遍历一棵B＋树索引，并更新满足筛选条件的每一个值。只要指定一个值给常量或没有应用到筛选器中的某个值，这种方法的效果很好。万一不小心，则迭代器可能会发现已经在前面扫描过程中处理过的行，因为原来的更新将行移动到了指针的前面循环访问B＋树。
　　不是每个查询都需要担心这一问题，但是这却是查询计划在某些情况下会出现的一个问题。对这一问题的一般保护方法是将所有行扫描到一个缓冲中，接下来处理缓冲中的行。在sQLserver中，这通常是使用Spool或sort运算符实现的，其中每种运算符都具有在查询树中产生输出行给下一个运算符之前读取所有输入行的某种保障。SQL Server还能够使用某种特殊形式的计算标量运算符在某些限定情况下提供Halloween保护，但是显示计划没有公共信息说明这种情况正在发生（除计划中有一个额外的计算标量之外）。在所有情况下，副本防止同一行被查看两次。
　　■拆分/排序/折叠（Split/Sort/Collapse）
　　SQL　Server包含一种被称为”拆分/排序/折叠”的物理优化，以使大范围更新计划更有效．该功能检查批处理中所有要被更改的更改行并确定这些更改对某个索引的净效应。这样避免了不必要的更改，从而降低完成查询的I/O需求。这种更改还允许进行一种单一线性传递，将更改应用到每个索引上，这比一系列随机I/O操作更有效．下面我们看一个实例：



/****************** Split/Sort/Collapse 演示 *************/
/************ 3W@live.cn by 邀月 *******************/
----生成表
CREATE TABLE update5(col1 INT PRIMARY KEY);
----插入数据
INSERT INTO update5(col1) VALUES (1), (2), (3);
----查看执行计划
UPDATE update5 SET col1=col1+1;
　　

http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.sn2.livefilestore.com/y1pEZ_0q-I-o93qTedKWRx_QrdcSwedMxzCzYVkQhTm3jNYmNgX2b2jCuoq7UqsiksxbR2NpFJe90Ripq6cKpKxkA/2012-4-7%2010-39-46.png?psid=1
　　
　　该查询正在修改一个聚集索引，该索引具有值为1、2和3的3行。查询之后，这3行的值改为2、3和4。事实上不一定修改这3行，很可能是只删除1并插入4来对该查询进行修改。对于本例来说，可以避免修改一行，但是对于大型表来说，这种节省可能很实用。
　　这种优化通过一种被称为“操作列”（Action Column）的内部列实现．其中包含一个值表示每一行是否是INSERT, UPDATE, DELETE或MERGE。更新运算符使用这种操作列确定应该对该索引应用怎样的修改。虽然显示计划（ShowPlan）根据提交查询的不同为该更新运算符显示不同的名称，但是在系统内部它们是同一个运算符并且通过操作列进行修改。遗憾的是，您不可以查看该列的值，因为该列只是查询处理器中的一种结构。
　　操作列也可以被查询处理器用于帮助确定应用到某个索引上的净更改（net change）。也可以被”拆分/排序/折叠”逻辑用于确定对该索引要进行的下一步更改。现在让我们大致看一下每一步骤中的情况。在拆分之前，行数据如下所示。
　　

Action	OldValue	New Vaue
Update	1	2
Update	2	3
Update	3	4

　　
　　拆分将每条Update转换为一条Delete和一条Insert，拆分后的效果如下：
　　

Action	Value
Delete	1
Insert	2
Delete	2
Insert	3
Delete	3
Insert	4

　　
　　按（Action,Value）进行排序，Delete在Insert之前，排序后的结果：
　　
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.sn2.livefilestore.com/y1pGqoPZb9j48pwo-d0VUBZKwmp1SbuFsOQgK0epaU-74HYbIwHaHkErvLjkAfT4loVzE7pZEYLvEoYVJAKJoztzw/2012-4-7%2012-20-54.png?psid=1
　　Collapse运算符在（Delete，Insert）对中查找相同的值并将它们删除。在这个示例中，Collapse运算符将值为2和3的行用Update替换了Delete和Insert行，Update减少了必要的B+树维护操作的数量，同时，存储引擎能够不记录B+树更新的相同值（但是锁仍然用于更正）。最后，折叠结果如下：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.sn2.livefilestore.com/y1pEj3dNUkaXBWR3Kxug39pHSN4Ing1OmACDBugCpbF9XjM9GJFj5UEoBFt7Dn1a93axJnDESNcbmd0NWdRyuKU2A/2012-4-7%2013-54-15.png?psid=1
　　结果是需要对索引进行净更改。从技术上来说，每个索引都包含主键索引或堆的行标识符，甚至索引中不存在的行也会被更新，从而使引用适应Heap或聚集索引。日志流量也会在标准更新路径中减少，并且也可以获得I/O排序的优先权。
　　虽然“拆分/排序/折叠”逻辑是一种性能优化，但是它也会帮助在修改唯一索引（例如主键）时避免失败故障。如果原始计划在没有“拆分/排序/折叠”的情况下被执行，则它会试图将值为1的行修改为2。这可能会与索引中值为2的现有行相冲突。虽然这对于该查询来说可以通过向后循环行的方式避免，但是有时不能通过特地选择一种扫描顺序的方式来避免这一问题。“拆分/排序/折叠”允许SQL Server在不返回错误的情况下支持该示例之类的查询。
　　■Merge
　　SQL Server 2008　引入了一种被称为Merge的新型更新操作。Merge是其他更新操作的混合，用于对表执行条件更改。该操作的业务价值在于它可以将多个T-SQL操作折叠成一个查询。这样会简化必须编写修改表的代码、提高性能并在实际上有助于对大型表（可能太大以至于有效地进行多步操作太慢可失去意义）的操作。
　　看到其他更新操作如何被处理后，你可能己经知道Merge实际上不是对其他操作中使用的操作列技术的一种复杂扩展。与其他查询一样，元数据被扫描、筛选和修改。但是，在Mergeg运算中，要被修改的行与目标资源相联接以决定对每一行所应执行的操作。基于这一join，每一行的操作列将会被修改以通知Stream Update操作对每一行要进行的操作。
　　在下例中，对一个现有表使用新数据进行更新，这些新数据中可能有一些已经在表中存在。因此，Merge用于确定不存在的行。



/****************** Merge 演示 ***************************/
/************ 3W@live.cn by 邀月 *******************/
CREATE Table AnimalsInMyYard(sightingdate Date,animal Nvarchar(200));
GO
INSERT INTO AnimalsInMyYard(sightingdate,animal) VALUES ('2011-04-06',N'哈士奇');
INSERT INTO AnimalsInMyYard(sightingdate,animal) VALUES ('2011-04-05',N'狐狸');
INSERT INTO AnimalsInMyYard(sightingdate,animal) VALUES ('2011-04-06',N'狼');
GO
CREATE Table NewSighting(sightingdate Date,animal Nvarchar(200));
GO
INSERT INTO AnimalsInMyYard(sightingdate,animal) VALUES ('2011-04-08',N'兔子');
INSERT INTO AnimalsInMyYard(sightingdate,animal) VALUES ('2011-04-06',N'哈士奇');
INSERT INTO AnimalsInMyYard(sightingdate,animal) VALUES ('2011-04-09',N'狼');
GO
----插入还没有看过的，否则不操作
MERGE AnimalsInMyYard A USING NewSighting N
ON (A.sightingdate = N.sightingdate AND A.Animal = N.Animal)
WHEN NOT MATCHED
THEN INSERT (sightingdate, Animal) VALUES (sightingdate, Animal);
　　Merge计划变得稍微有点庞大，我们分两部分来看，第一部分如下图：（初始连接以查找已存在的行）
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.sn2.livefilestore.com/y1pSmVZINd_ce_87rpges2W2RmfdLKSkjlbC2wAJ-vlCa6qECo2LUgwHWZnDJP9Ul-LVq4MKhUHfDFhruMSiEKgvg/2012-4-7%2014-32-46.png?psid=1
　　首先读取源表NewSighting，同时查询处理器探测目标表AnimalsInMyYard，查看该行是否己经存在。 Left outer Join隐含的Compute scalar仅仅用于添加一列，如果该值匹配则值为1,如果没有与源表行相匹配的行（由于Left Outer Join的工作原理）则返回Null值。联接上的Compute scalar会生成Action 列：
　　[Action1008] = Scalar Operator(ForceOrder(CASE WHEN [TrgPrb1006] IS NOT NULL THEN NULL ELSE (4) END))
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.sn2.livefilestore.com/y1pRKmK9oAcH8Wl8-vA4wFudne8fS_fm3LzKJapzxaILClIieGSofYbMijGFgCgoMDM2aHvLYbj_YHmwmG3dHzjVA/2012-4-7%2014-33-04.png?psid=1
　　第二部分：Merge计划：更新、Halloween保护池、行筛选器
　　在该计划的上半部分，筛选器删除有一次null操作的行（谓词：[Action1008] IS NOT NULL)，因为该Merge语句只有一项操作（可能在一条Merge语句中有多项操作）。导出查询命令的脚本能够提供Halloween保护，这表示它会在视图将值写回AnimalsInMyYard表之前使用输入中的所有行。Merge实际上只是一项更新操作，但是显示计划输出已经被更改，以避免出现混淆。
　　
　　小结：本文学习了三个有关计划更新的概念：Halloween Protection、Split/Sort/Collapse和Merge，下节将继续学习Wide Update Plans。