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

 

《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

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

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

 

 

上节主要学习了聚集健的依赖（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 TestDb
go

IF OBJECTPROPERTY(object_id('Clustered_Dupes'), 'IsUserTable') IS NOT NULL
    DROP TABLE Clustered_Dupes;
go

CREATE TABLE Clustered_Dupes
(
    Col1    CHAR(5)    NOT NULL,
    Col2    INT        NOT NULL,
    Col3    CHAR(3)    NULL,
    Col4    CHAR(6)    NOT NULL
);
go
CREATE CLUSTERED INDEX Cl_dupes_col1
ON Clustered_Dupes(col1);
go
SELECT indid, keycnt, name
FROM sysindexes
WHERE id = OBJECT_ID ('Clustered_Dupes');
go

第二列KeyCnt，显示了一个索引的键值数量。这个结果是;

indid    keycnt    name
1    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 index
ALTER 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;
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
--TRUNCATE TABLE sp_tablepages;
INSERT sp_tablepages
EXEC ('DBCC IND (IndexInternals, Employee, 1)');
go

SELECT IndexLevel
    , PageFID
    , PagePID
    , PrevPageFID
    , PrevPagePID
    , NextPageFID
    , NextPagePID
FROM sp_tablepages
ORDER 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)
go
DBCC 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)时被用到。为了更好地理解非聚集索引是如何被使用的，我们需要继续了解非聚集索引是如何被存储，以及它怎样找到数据行。这正是下一节将要了 解的内容。快两点了，打个盹先。