《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)。

另外两个区别是:一个索引行没有使用TagBFSize行头部值(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, 12343)--参数3表示Grid格式,这里复习一下。哈,考试内容啊。

 结果如下:
邀月工作室

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

DBCC PAGE (IndexInternals, 12353)
go 

结果:
邀月工作室

 继续:

DBCC PAGE (IndexInternals, 116163);
go

 

 邀月工作室

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

 

posted @ 2010-02-08 01:43  邀月  阅读(2206)  评论(8编辑  收藏  举报