《Microsoft Sql server 2008 Internals》读书笔记订阅地址:
http://www.cnblogs.com/downmoon/category/230397.html/rss
《Microsoft Sql server 2008 Internals》索引目录:
《Microsoft Sql server 2008 Internal》读书笔记--目录索引
前面我们主要了解Inserting/Deleting Rows 时SQL Server的内部存储机制,这一篇我们来看看Update Rows的内部机制。
■Update Rows
SQL Server更新行有多种方式,自动并隐性地为特定的操作选择最快的更新策略,在决定策略时,SQL Server评估将要影响行的数量,行是如何(通过查询或索引恢复、或者索引本身)被访问的,索引键是否发生变化等。更新在两种情况下发生:一是在原始行 改变一列值到新值。二是delete紧跟着一个insert。Update能被查询处理器或存储引擎管理。
■Moving Rows
如果表的一行必须被移动到一个新的位置会发生什么?在SQL Server 2008中,在一个可变长度列被更新到一个新的更大的(原页面不再适应的)长度时,或在一个聚集索引或非聚集索引列(因为行被索引键逻辑排序而)发生改变 时,这种情况发生。例如,在lastname列上有一个聚集索引,一个lastname值为abbot的行被存储在表的开始部分,如果这个值被更新为 Zappa,这个行不得不移动到表的末尾部分。
在前一章中,我们了解索引的结构,并知道非聚集索引的叶级包含一个行定位器,即书签,对应于 表中的每一个单独的行。如果表有一个聚集索引,行定位器就是这个表的索引键。如果聚集索引键被更新时,每一个非聚集索引(除了筛选非聚集索引可能例外)都 得跟着修改。当你决定在哪一列建立聚集索引时,记得这一点。在一个不易更改的列建产聚集索引是一个很great的想法,比如标识列。
如 果一行因为它不适应原页面而被移动时,它仍然有相同的行定位器(换句话说,聚集索引键没有改变),没有非聚集索引需要被修改,即使表被移动到一个新物理位 置(文件组或分区架构)。非聚集索引被更新除非聚集索引键发生变化,而移动一个表的行并不改变它的索引键。
在索引内幕的讨论中,你也注意 到如果一个表是Heap,存储在非聚集索引的行定位器实际上是行的物理位置。在SQL Server 2008中,如果一个Heap的一行移动到一个新页,该行保留一个指向原位置的转向指针。这个非聚集索引并不需要被改变,仍然指向原位置,从原位置,直接 指向新位置。在这种情况下,如果表移动到一个新位置,非聚集索引被更新,当Heap的所有记录的物理位置必须被改变,从而使非聚集索引的前一个行定位器失 效。
这段非常拗口,我们看一个例子,
go
DROP TABLE bigrows;
go
CREATE TABLE bigrows
(
a int IDENTITY ,
b varchar ( 1600 ),
c varchar ( 1600 )
);
go
INSERT INTO bigrows
VALUES ( REPLICATE ( ' a ' , 1600 ), '' );
INSERT INTO bigrows
VALUES ( REPLICATE ( ' b ' , 1600 ), '' );
INSERT INTO bigrows
VALUES ( REPLICATE ( ' c ' , 1600 ), '' );
INSERT INTO bigrows
VALUES ( REPLICATE ( ' d ' , 1600 ), '' );
INSERT INTO bigrows
VALUES ( REPLICATE ( ' e ' , 1600 ), '' );
go
建立一个表来存放页面数据
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DROP TABLE sp_tablepages;
go
CREATE TABLE sp_tablepages
(
PageFID tinyint ,
PagePID int ,
IAMFID tinyint ,
IAMPID int ,
ObjectID int ,
IndexID tinyint ,
PartitionNumber tinyint ,
PartitionID bigint ,
iam_chain_type varchar ( 30 ),
PageType tinyint ,
IndexLevel tinyint ,
NextPageFID tinyint ,
NextPagePID int ,
PrevPageFID tinyint ,
PrevPagePID int ,
CONSTRAINT sp_tablepages_PK
PRIMARY KEY (PageFID, PagePID)
);
go
查看结果:
INSERT INTO sp_tablepages
EXEC ( ' DBCC IND (Testdb, bigrows, -1) ' );
SELECT PageFID, PagePID
FROM sp_tablepages
WHERE PageType = 1 ;
go
PageFID PagePID
1 248
我们看到。这些行在一个PageID为248的页面中。
现在我们来更新一行
SET c = REPLICATE ( ' x ' , 1600 )
WHERE a = 3 ;
go
再 来看结果:
PageFID PagePID
1 248
1 254
很显然,增加了一个页面,因为原页面不够存放a=3的这行新值。
我们再来看DBCC的输出:
go
-- Be sure to enter YOUR PagePID:
DBCC PAGE(testdb, 1 , 248 , 1 );
go
结果如图,你也可以使用以下命令直接查看结果:
go
-- Be sure to enter YOUR PagePID:
DBCC PAGE(testdb, 1 , 254 , 1 );
go
■Managing Forwarding Pointers
转发指针允许你在一个Heap中修改数据而不用担心非聚集索引发生激烈的变化,如果一个已经转向的行需要再次被移动,原转向指针被更新以指向新的位置。另 外,如果转向行收缩到足够适应原页面,该记录可能移动到原来的位置,如果原页面还有空间。此时,转向指针将消除。
在SQL Server的未来一个版本中,可能包含一些机制,如在一个Heap中执行物理识别,以除去转向指针。注意转向指针仅存在于Heap中。重新组织表的 Alter Table选项并不影响Heap。可以在一个Heap中整理一个非聚集索引,而不是更新表自身。目前,当一个转向指向被创建时,它永远在那儿不会移动, 除了几个例外:
第一个例外是刚才提到的,行被收缩返回到原始位置。
第二个例外,是整个数据库发生收缩,当一个文件被 收缩时书签实际上被重排了。收缩进程实际上并不生成转发指针。而那些对于收缩进程移动的页面,所包含的任何转向的行或存根都有有效地“反转向”了。
第三个例外,如果转向行被删除或一个聚集索引被创建而不再是一个Heap。
如果要获取一个表中的转向记录的数量,你可以使用 sys.dm_db_index_physical_stats函数。请看MSDN:
http://msdn.microsoft.com/zh-cn/library/ms188917.aspx
■Update in place
在 SQL Server 2008中,原地更新一个行时主要依据规则而不是意外。(updating a row in place is the rule rather than the exception),这意味着行准确在呆在相同的页面的相同位置,仅仅受影响的字节发生改变。此外,日志为每个原地更新操作包含了一个单独的记录,除非 表有一个更新触发器或被标记为用于复制。在这些情况下,更新将在原地发生,但日志不包含索引键列被更新时的Delete+Insert操作。
在某些情况下当一个行不能被原地更新时,“非原地更新”的代价是最小的,因为非聚集索引的存储方式和转向的使用。事实上,也可以对仍保留在原始页的行进行非 原地更新。如果一个Heap被更新,并且页面有足够的空间,或一个有聚集索引的表被更新(而同时聚集键没有变化的情况下),更新在原地发生。如果聚集键发 生改变但是行不需要移动时,也可以在原地更新。比如,如果在包含连续键值(Able,Becker和Charlie)的lastname列有一个聚集索 引,你可能更新Becker为Baker,此时,行还在原来的位置,即使聚集索引键改变,SQL会执行一个原地更新。另外一方面,如果你更新“Able” 为“Buchner”,更新不能在原地发生。
■Update not in place
如果正在更新聚集键时更新不能在原地发生,更新以“Delete+Insert”方式实行,在某些情况下,你可能得到一个混合更新:一些行被原地更新,一些不在。如果你更新索引键,SQL建立一个需要实行(Delete+inert)的行列表。列表在内存中,在不大的情况下,如果有必要可能会写入Tempdb 中,这个列表按键值和操作(Delete+Insert)排序。如果键值被改变的索引是不惟一的,Delete+Insert步骤被应用到表。如果是惟一 的,则执行单个的Update操作。
本文主要介绍了更新行时数据页的变化及转向指针(Forwarding pointer)的一些基本机制。下文将介绍表级和索引级数据修改时索引的维护和整理。