《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(3)

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internals》读书笔记--目录索引

 

上节主要学习了聚集健的依赖（The Dependancy on the Clustering key）和建立非聚集索引的聚集键的三个基本要点：聚集键应该是惟一的、窄的、静态的。本节我们继续深入学习物理的索引结构(physical  Index Structures),这一部分分为两类：一、聚集索引的物理结构。二、 非聚集索引的物理结构。

今天我们先来看第一部分： 聚集索引的物理结构

索引页(index pages)的结构和前面学过的数据页(data pages) 非常类似。惟一区别是前者存的是索引记录，而后者存的是数据记录。像SQL Server中大多数其它类型的页一样，索引页也是固定的8K大小或8192字节。索引页也有一个96字节的Header,在每个页的末端都有一个两字节的偏移数组，用以指示每行在页中的偏移量。一个非聚集的索引拥有全部三类分配单元，分别是IN_ROW_DATA，ROW_OCERFLOW_DATA和LOB_DATA。每一个索引在目录视图sys.indexes中对应一行。index_id是1(聚集索引）或2-250,256-1005（非聚集索引)。正如data pages一样，251-255是系统保留值。

索引行格式 （Index Row Format）

索引行(Index rows)和数据行(Data rows)的结构非常类似。但有两个主要的区别：一、一个索引行不能有稀疏列(Sparse columns)。一个稀疏列可以用于索引，但不能作为主键，并且在创建索引时不能定义为稀疏列。二、如果一个聚集索引被创建时未定义成惟一(unique),那么重复键值包含一个唯一标志(uniquifier)。

另外两个区别是：一个索引行没有使用TagB或FSize行头部值(row header value) ,作为FSize字段（本来是行末尾的固定长度部分）的替代，页头部(page header)Pminlen的值被用于解码一个索引行。Pminlen Value表明行末尾的固定长度部分的偏移量。如果索引行没有可变长度列或可空列，那就是行的末尾。仅当索引行在字段Ncol有可空列，同时空位图(null bitmap)也被设置（这句翻译不好，原文是Only if thd index row has nulable columns are the field called Ncol and the null bitmap both present)。Ncol字段包括一个表明索引行有多少列的值，这个值被用于决定在null bitmap中有多少bits。数据行有一个Ncol字段和空位图（决定是否任何列允许为空），而索引行只有一个空位图和一个Ncol字段（决定索引的任何列是否允许Nulls,如下表：

Information

Mnemonic

Size

Status Bits A

TagA
Bits1 through3

1 byte

Fixed-length data

Fdata

pminlen-1

Number of columns

Ncol

2 bytes

NULL bitmap(1 byte for each column in table;

1 indicates that the corresponding column is NULL)

Nullbits

Ceiling (Ncol / 8)

Number of variable-length columns
only Present if >0

VarCount

2 bytes

Variable column offset array;Only present if VarCount>0

VarOffset

2 * VarCount

Variable-length data;if any

VarData

 

 聚集索引结构(Clustered Index Structures)

聚集索引的叶级是数据本身。当一个聚集索引被创建时，这些数据被物理复制并基于聚集健排序。聚集索引的行结构和Heap的行结构完全相同，除了以下一种情况：

当一个聚集健没有被用"UNIQUE" 定义时。在这种情况下，SQL　Server必须确保内部惟一性。为此，每个重复行需要一个附加的惟一值。

带Uniquifier限定符聚集索引行(Clustered Index Rows with  uniquifier)

正如前面所了解的，当没有使用UNIQUE属性时，SQL Server会增加一个4字节的惟一标志符以确保每一个nonunique健值的惟一。因为聚集键被用于识别非聚集索引(书签查询)所引用的基本行，这就需要一个惟一的方式去指向聚集索引的每一行。

SQL Server仅仅在必须的时候增加唯一标志（uniquifier),也就是说，当重复键被加到表里的时候。我们看一个例子：

Use TestDbgoIF OBJECTPROPERTY(object_id('Clustered_Dupes'), 'IsUserTable') IS NOT NULL DROP TABLE Clustered_Dupes;goCREATE TABLE Clustered_Dupes( Col1 CHAR(5) NOT NULL, Col2 INT NOT NULL, Col3 CHAR(3) NULL, Col4 CHAR(6) NOT NULL);goCREATE CLUSTERED INDEX Cl_dupes_col1ON Clustered_Dupes(col1);goSELECT indid, keycnt, nameFROM sysindexesWHERE id = OBJECT_ID ('Clustered_Dupes');go
第二列KeyCnt，显示了一个索引的键值数量。这个结果是;
indid keycnt name1 2 C1_depes_Col1
这个值是2（需要注意这是在兼容性视图sysindexes,而不是在目录视图sys.Indexes中）。如果使用"UNIQUE"属性，这个值应该是1。因为在一个非惟一的健上创建一个聚集索引是不被推荐的，原因是这样SQL Server为保证行惟一将不得不浪费时间和空间。这个已经在前面讨论过了。

聚集索引的非叶级(The Non-Leaf Level(s)of a Clustered Index)

 当一个B-Tree被创建时，为了导航到索引的叶级，它包含了叶级的数据行。非叶级的每一个行(针对叶级的每一个页(Page))有一个项(entry)，这个项包含了一个索引键值和一个6字节的指针，指向这个页。在此情况下，页指针由一个2字节的FileID和一个4字节的PageNumberlnTheFile组成。SQL Server并不需要一个8字节的RID，因为slot Number不需要被存储。这个项(entry)的索引键部分总是显示能被用于指针页的最小值(mininum value)。注意，这并不是实际的最低值(Lowest value)。仅仅是页最低的可能值。（当一个页上的具有最低值的行被删除时，这个级上方的索引行并没有被更新）

分析一个聚集索引的结构(Analyzing a Clustered Index Structure)

我们先看一个例子：

CREATE TABLE Employee(EmployeeID Int NOT NULL Identity,LastName nchar(30) NOT NULL, -- created as fixed width to make our row size exactly 400 bytes (to simplify the math/visualization)FirstName nchar(29) NOT NULL,MiddleInitial nchar(1) NULL,SSN char(11) NOT NULL,OtherColums char(258) NOT NULL DEFAULT 'Junk')go
注意我们每行刚好400字节，换句话说，每个数据页(8096/400=20.24)可以放20行数据。这里有个原因是IN_ROW数据行不能跨页。

在上面的定义中，这个表是一个Heap ,我们给它加一个聚集索引

-- Add the clustered indexALTER TABLE Employee ADD CONSTRAINT EmployeePK PRIMARY KEY CLUSTERED (EmployeeID)ON [PRIMARY]go

我们使用前面介绍过的DMV来查看索引的存储状态：
-------------------------------------------------------------------------------- Analyze the Employee Table's Clustered Index------------------------------------------------------------------------------SELECT index_depth AS D , index_level AS L , record_count AS 'Count' , page_count AS PgCnt , avg_page_space_used_in_percent AS 'PgPercentFull' , min_record_size_in_bytes AS 'MinLen' , max_record_size_in_bytes AS 'MaxLen' , avg_record_size_in_bytes AS 'AvgLen'FROM sys.dm_db_index_physical_stats (DB_ID ('IndexInternals') , OBJECT_ID ('IndexInternals.dbo.Employee') , 1 , NULL , 'DETAILED');go
结果：

 从上图中我们看到：正如我们所期望的那样，80000行数据的表一个4000页的叶级。从Minlen我们看到，非叶级的行长度只有11字节。这个结构很容易被细分成:4字节对应EmployeeID,6个字节对应页指针，每行仅增加一个字节的开销(overhead)。为什么呢？因为我们的索引行只包含一个固定宽度的列，并且这些列中没有一个允许空值(Null),因此，不需要在索引页放置一个空位图。此外，你可以在结果中看到，在Level 1仅仅只有7页数据，回忆以前我们介绍的B-Tree存储结构，我们再看看以上数据的存储结构：

 我们可能通过DBCC IND命令进一步了解索引页的存储明细。

IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL DROP TABLE sp_tablepages;goCREATE 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--TRUNCATE TABLE sp_tablepages;INSERT sp_tablepagesEXEC ('DBCC IND (IndexInternals, Employee, 1)');goSELECT IndexLevel , PageFID , PagePID , PrevPageFID , PrevPagePID , NextPageFID , NextPagePIDFROM sp_tablepagesORDER BY IndexLevel DESC, PrevPagePID;GO

 部分结果;

 注意上图中黄色部分，页数是完全连续的，这是因为表创建时我们在空表中建立了聚集索引，然而从Root往下，就不一定连续，因为索引被从叶级到Root按照(对应的每一个叶级)排序。这很重要，想像我们查询：
Select e.* from dbo.Employee as e where e.EmployeeID=27682

 为了找到所有EmployeeID=27682的行，（记住，这是聚集索引键值）SQL Server开始从Root页开始沿着叶级向下浏览，root Page我们已经知道是234 (IndexLevel最高2）,我们进·一步用DBCC Page命令分析：
DBCC TRACEON (3604)goDBCC PAGE (IndexInternals, 1, 234, 3)--参数3表示Grid格式，这里复习一下。哈，考试内容啊。

 结果如下：

根据结果，我们大致判断一下27682应该在24881与37321之间,也就是在第三页与第四页之间，为了精确定位，我们不得不沿FileID1的第三页的子页(ChildPageID)235,命令如下：

DBCC PAGE (IndexInternals, 1, 235, 3)go

结果：

 继续：

 DBCC PAGE (IndexInternals, 1, 1616, 3);go 

 

 小结：通过预览一个行结构，我们复习了两样东西，索引内幕和进程。并通过一个聚集索引值查找一个行。这个方法在从一个非聚集索引（检索已经聚集的表的数据时）执行一个书签查找(bookmark lookup)时被用到。为了更好地理解非聚集索引是如何被使用的，我们需要继续了解非聚集索引是如何被存储，以及它怎样找到数据行。这正是下一节将要了解的内容。快两点了，打个盹先。

 

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]