SQLSERVER聚集索引与非聚集索引的再次研究（上）

概述http://www.cnblogs.com/lyhabc/p/3196479.html 上篇主要说聚集索引 下篇的地址：SQLSERVER聚集索引与非聚集索引的再次研究（下） 由于本人还是SQLSERVER菜鸟一枚，加上一些实验的逻辑严谨性， 单写《SQLSERVER聚集索引与非聚集索引的再次研究（上）》就用了12个小时，两篇文章加起来最起码写了20个小时， 本人非常非常用心的努力完成这两篇文章

http://www.cnblogs.com/lyhabc/p/3196479.HTML

上篇主要说聚集索引

下篇的地址：SQLSERVER聚集索引与非聚集索引的再次研究（下）

由于本人还是sqlSERVER菜鸟一枚，加上一些实验的逻辑严谨性， 单写《sqlSERVER聚集索引与非聚集索引的再次研究（上）》就用了12个小时，两篇文章加起来最起码写了20个小时， 本人非常非常用心的努力完成这两篇文章，希望各位看官给点意见o(∩_∩)o

 

为了搞清楚索引内部工作原理和结构，真是千头万绪，这篇文章只是作为参考，里面的观点不一定正确

有一些问题，msdn里，网上的文章里，博客园里都有提到，但是这些问题的答案是正确的吗？其实有时候我自己都想知道答案

比如，画聚集索引的图，有一些人用表格来表示，但是他们正确吗？

以前知道聚集索引 非聚集索引是B树 二叉树结构，又知道执行计划图标很像二叉树很传神，但是还是觉得很抽象

这篇文章写完以后还是比较抽象但是最起码比以前清晰一些了

有很多问题不知道为什么，但是MSDN就是这样说的，既然说得这麽模糊不如自己做一下实验，验证一下MSDN的内容吧o(∩_∩)o

--------------------------------------------华丽的分割线---------------------------------------------

 先来看一下索引的结构，文章里面的一些结构图都是自己画的一些草图，本人自认画得非常烂，希望各位看官谅解o(∩_∩)o

 

 

 

----------------------------------------------华丽的分割线---------------------------------------------------------

先创建一个表，保存DBCC IND的结果

 1 CREATE table DBCCResult ( 2 PageFID NVARCHAR(200), 3 PagePID  4 IAMFID  5 IAMPID  6 ObjectID  7 IndexID  8 PartitionNumber  9 PartitionID 10 iam_chain_type 11 PageType 12 IndexLevel 13 NextPageFID 14 NextPagePID 15 PrevPageFID 16 PrevPagePID 200)17 )

创建一个聚集索引表

1 --只有聚集索引2 table Department(3 DepartmentID int IDENTITY(1,1) NOT NulL PRIMARY KEY,128)">4 name 200) NulL,128)">5 Groupname 6 Company 300),128)">7 ModifIEdDate datetime NulL DEFAulT (getdate())8 )

插入10W条记录

INSERT INTO Department(name,[Company],groupname) VALUES('销售部',0)">中国你好有限公司XX分公司销售组')GO 100000

将DBCC IND的结果放入DBCCRESulT表

INTO DBCCResult EXEC (DBCC IND(pratice,Department,-1) ')

查询Department表中的页面情况

先说明一下：

PageType  分页类型： 1：数据页面；2：索引页面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM页面

IndexID    索引ID：   0 代表堆,1 代表聚集索引,2-250 代表非聚集索引 ，大于250就是text或image字段

红色框部分都是需要关注的

第一个：IAM页不是只有堆表才有也不只是维护堆表中的数据页的连续，有索引的表都有，所以IAM页不只维护数据页，也维护索引页的连续，在下篇说到非聚集索引的时候

我会给出MSDN的解释和IAM页在聚集索引表，非聚集索引表中的情况

第二个：每个数据页的IndexID都是1，不是说数据页变成了索引页，而是说现在数据页已经属于聚集索引的一部分，不在堆里了

第三个：每个数据页的IndexLevel都是0，就是说数据页在聚集索引的最下层

第四个:索引页和数据页,前一页和后一页是首尾相连的,但是数据页和索引页不是首尾相连的，也就是说没有一个数据页的[PrevPagePID]指向14464页或3528页

那么在上面的聚集索引图片中为什麽会说索引页指向数据页呢？叶子节点就是数据页呢？

数据页的index level是0，那么就是说聚集索引的叶子节点就是数据页

 上面索引页的结构

现在来看一下索引页里都有什么，运行下面的SQL语句

DBCC TRACEON(3604,128)">-1) 2 GO 3 4 DBCC PAGE(pratice3527,0)">3) 5 6 7 8 3528,128)"> 9 10 11 14464,128)">12 GO

 您们应该看到ChildPageID，所以上面我的图为什麽会这样画的原因，索引页连接着数据页，而且一个索引页指向多个数据页

 DepartmentID是主键列，从1开始自增，那么从下图可以看出主键列数据是从最左边的索引节点（不是叶子节点）开始排序

这里有个问题：为什麽根节点只有两行？？？是不是根节点只作连接作用，所以只有两行 ？？

答：其实我们在建立索引的时候，会有一个默认选项：PAD_INDEX

PAD_INDEX 选项只作用于 非叶级索引页

如果PAD_INDEX未指定，则默认会为非叶级页留出一行空间

在非叶级页上的行数永远不会小于两行

聚集索引页里主键列DepartmentID上一行与下一行相差120条记录,一个数据页刚好容纳120条记录

KeyHashValue根据主键列的第一个字段而生成的，就算两个表完全一样，这个hash出来的KeyHashValue都不会一样

我创建了一个一模一样的表Department2，看到hash出来的值都不一样

 

------------------------------------------------------------华丽的分割线------------------------------------------------------

聚集索引怎麽找记录的？？？

这里要分两种情况：（1）聚集索引查找  （2）聚集索引扫描

（1）聚集索引查找

 放大一下索引页

 

 

sqlSERVER聚集索引查找记录的流程

先用二分查找法找到实际的数据页面，然后再到数据页里把实际数据读出来

这里还有一个keyhashvalue用来锁定数据行资源， 运行下面的SQL语句，看一下sqlSERVER申请的锁就知道了

 下面实验我在Department2表里做的，表数据和表结构和Department1一模一样

  VIEw Code

 下面这个证明代码在《sqlSERVER企业级平台管理实践》里找的

  VIEw Code

 

 

需要记住的是，B树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页面，数据库把页面载入到内存，这里算一个读I/O

对真个页面进行扫描，由于在内存中查找的一个页面里的一条记录很快，因此通常忽略这部分扫描所用的时间

二分查找只是个缩小查找范围的查找方法，本身作用于的对象是一个个的数据页面，要找到具体某条记录还需要读取整个页面到内存进行查找

 

 

（2）聚集索引扫描

先drop掉Department2表，然后重新创建Department2表

  VIEw Code

证明：

  VIEw Code

上图“以下查询使用了聚集索引查找”，由于本人写sql代码的时候没有修改上面注释，大家可以不用理会

为什麽会有一个键锁，那么多的页锁，在徐海蔚老师的《sqlSERVER企业级平台管理实践》的书本里第361页说到

因为在有聚集索引的表格上，数据是直接存放在索引的最底层（叶子节点），所以要扫描整个表格里的数据，就要把整个聚集索引

扫描一遍。在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别

 

sqlSERVER不会停止扫描数据页，所以才看到上图有那么多的页面上加了页锁，sqlSERVER需要逐个数据页逐个数据页去扫描就像堆表的全表扫描那样。

那个键锁是当sqlSERVER找到那条记录之后，需要在

记录的所在页面（即是索引页指向那个记录的数据页的那一行）加上一个键锁，以防止别人删除索引页的那一行记录

但是聚集索引扫描是不是一定比聚集索引查找要差呢？这个不一定，要看实际情况o(∩_∩)o

那么非聚集索引扫描是不是跟聚集索引扫描一样，所要用的时间和资源与表扫描没有什么差别呢？？？

大家可以看一下《sqlSERVER聚集索引与非聚集索引的再次研究（下）》本人做的一个小实验

实验证明了《sqlSERVER企业级平台管理实践》里第363页说到的内容

索引扫描表明sqlSERVER正在扫描一个非聚集索引。由于非聚集索引上一般只会有一小部分字段，所以这里虽然也是扫描，但是

代价会比整表扫描要小很多

 

 ------------------------------------------------华丽的分割线--------------------------------------------------------------------

 这里有一个问题：没有主键但是有聚集索引，索引页的列数不一样，会多了一列，而这个列（uniquifIEr）的作用在下面会讲到

这里创建Department3表

  VIEw Code

 可以看到只有聚集索引没有主键的表会比主键表多了一列uniquifIEr列，这个列的作用会在创建Department5表的时候讲到

-----------------------------------------------华丽的分割线-------------------------------------------------------

 下面说一下，复合主键或者聚集索引建立在多个字段上，KeyHashValue只会根据第一个字段生成hash key

当你查询的时候where 后面的字段不包含创建聚集索引时的第一个字段或者复合主键的第一个字段就会聚集索引扫描而不是聚集索引查找

 创建Department4表

  VIEw Code

 

SELECT * FROM dbo].Department4] WHERE name=销售部6' 聚集索引扫描 因为name不是复合主键中的第一个字段销售部241' AND DepartmentID]=241 聚集索引查找3 WHERE 241 聚集索引查找

 

 

在建立聚集索引的时候在多个字段上建立聚集索引是没有任何意义的

因为聚集索引查找是根据建立索引的第一个字段来查找，索引扫描的时候会到数据页里扫描 ，而聚集索引的每一行只是一个数据页的范围值从而不能直接定位到要找的那条记录

所以只需要在数据表的一个字段上建立聚集索引就可以了，而究竟要在哪一个字段上建立聚集索引大家一定好好斟酌，本人建议那一个字段在order by中经常要排序的

因为数据页都已经按照聚集索引的第一个字段排好序的了

而不像非聚集索引的索引页跟数据表的记录一一对应，扫描的时候扫描索引页的每一行

大家可以对比一下聚集索引和非聚集索引页的结构

聚集索引页的结构

非聚集索引页的结构

非聚集索引页面的结构会在sqlSERVER聚集索引与非聚集索引的再次研究（下）里讲到

 ---------------------------------------------------------华丽的分割线-----------------------------------------------------

 由于主键不允许重复值，那么就在表上创建一个不唯一的聚集索引，有人说在重复值很多的列上建立聚集索引没有意义

创建Department5表  在Company字段上建立聚集索引，Company字段的值全部都是"中国你好有限公司XX分公司"

  VIEw Code

 

  VIEw Code

 

在Department3表的时候讲到列（uniquifIEr），为什麽有主键的表没有这个列，而聚集索引的表有这个列，原因在于

主键列不能有重复值，必须是唯一的，而聚集索引允许有重复值，所以聚集索引需要增加列（uniquifIEr）来区分重复值

而且可以看到这里uniquifIEr列是没有规律的，不像Department表每隔120行记录在索引页里标记一行

看一下执行计划和执行结果

SET STATISTICS TIME ONDepartment5241 4 sql Server 分析和编译时间: 5 cpu 时间 = 0 毫秒，占用时间 0 毫秒。 7 (1 行受影响) 8 9 sql Server 执行时间:10 cpu 时间 0 毫秒。

销售部106106 聚集索引扫描3 sql Server 执行时间:4 cpu 时间 0 毫秒。

至于应不应该在重复值很多的列上建立聚集索引我这里也不敢妄下判断，因为实际环境和这里的测试环境不一样

在MSDN中的解释：http://msdn.microsoft.com/zh-cn/library/ms177484(v=SQL.105).aspx

如果聚集索引不是唯一的索引，sql Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见

还有一个，看一下叶子节点中的数据页，在每个数据页的每行记录中都有

Slot 101 Column 0 Offset 0x1d Length 4

UNIQUIFIER = 206 

因为需要标记索引列中的唯一，所以需要在每行记录中增加一列UNIQUIFIER ，但是这一列在select * 表中数据的时候是select不出来的

还有人说UNIQUIFIER 是一个可变长度的字段，但是Length 4已经说明了是一个占用4字节的字段

  VIEw Code

还有增加了UNIQUIFIER 列之后，无论索引页和数据页都会有所增加，性能有所损耗

下面截图右边的是数据页pageID：14517中的数据，左边的是聚集索引页面

至于性能损耗多少，可以看一下宋大侠这篇文章：

从性能的角度谈SQL Server聚集索引键的选择

----------------------------------------------------华丽的分割线-------------------------------------------------------

 堆表中的数据页之间[PrevPagePID],[NextPagePID]是否会首尾相连

堆表

聚集索引表

 -----------------------------------------------------华丽的分割线-------------------------------------------------

聚集索引有一个特点，就是当表记录太少的时候，可能一个数据页面就能容纳下表的所有记录，那么这时候由于可能只有一个页面不足以构成一棵B树

创建Department6表,然后插入9条记录

USE ] 3 table Department6 5 ( 6 DepartmentID INT 7 name 8 Groupname 9 Company 10 ModifIEdDate DATETIME DEFAulT ( GETDATE() ),128)">11 CONSTRAINT PK_Department6_1KEY CLUSTERED12 ( name ASC,DepartmentID ASC )13 WITH ( PAD_INDEX = OFF,STATISTICS_norECOmpuTE OFF,128)">14 ALLOW_ROW_LOCKS ON,ALLOW_PAGE_LOCKS ON ) ON PRIMARY15 ) 16 17 18 DECLARE @i INT19 @i120 WHILE @i < 1021 BEGIN22 INSERT INTO Department6 ( name,0)">],groupname )23 VALUES ( '+CAST(AS VARCHAR(200)),0)">' )24 + 25 END

只有一个数据页和一个IAM页

插入更多记录

1000004 5 6 7 8 END

因为数据页开始多起来了，这时候聚集索引会组织会一棵有层次结构的B树

IndexLevel为2  表示这棵B树有3层

0：叶子节点层

1：非叶子节点层

2：root层

PageType 分页类型： 1：数据页面；2：索引页面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM页面

IndexID 索引ID： 0 代表堆,2-250 代表非聚集索引 ，大于250就是text或image字段

---------------------------------------------华丽的分割线---------------------------------------------------

大家再看一下Department2表的那部分，究竟数据页的排序顺序跟主键DepartmentID的排序顺序有没有关系呢？

先创建Department7表，插入1000条记录

  VIEw Code TruncATE table [dbo].[DBCCResult]3 4 DBCCResultORDER BY PageTypeDESC

根据数据页的首尾连接顺序，我画了一下草图

看一下索引页13791

  VIEw Code

再画一下草图

对比一下数据页的首尾连接顺序那张图，不知道大家看出规律没有

所以我把聚集索引结构图画成下面这个样子

为什麽聚集索引只能按照第一个字段生成key？为什麽数据页只能按照第一个字段来排序？

其实这个跟数据页排序有关的，大家再仔细看下面两张图

聚集索引页里根据第一个字段排列好这些数据页的第一个字段的范围值，数据页根据这个范围值首尾相连一一排序好

如果聚集索引按多个字段来排序，那么数据页根本排不了，多个字段又升序，又降序？？那怎么排序啊？只能按照一个字段来排序

聚集索引查找的时候，使用order by为什麽这么快，因为数据已经根据索引第一个字段排好序了，例子中的字段就是DepartmentID

而只有非聚集索引的表order by的时候就需要排一下序了，因为表中没有聚集索引，数据页没有预先按照一定顺序来排序

详细可以看一下非聚集索引的结构：sqlSERVER聚集索引与非聚集索引的再次研究（下）

---------------------------------------------华丽的分割线--------------------------------------------------------

问题：为什么一个表只能建立一个聚集索引

其实大家看一下我上面画的聚集索引结构图和非聚集索引结构图就知道了

因为如果一个表有聚集索引，那么他的数据页跟索引页有非常强的联系，数据页跟主键第一个字段排好序了，例子中就是“DepartmentID”

如果你再建一个聚集索引，你叫sqlSERVER应该按哪个字段来排序？排序方式是按照你原来的那个聚集索引的DepartmentID列来排序还是

按照你新建的那个聚集索引的第一个字段来排序？？

多个聚集索引，数据页都按不同的字段顺序排序，来建立双向链表，那数据表不就乱套了？？？

但是如果一个表中只有非聚集索引，非聚集索引里的索引页的每一行会有一个指针值指向数据页，数据页依然是堆，没有任何顺序可言

所以你可以在一个表上建立多个非聚集索引也没问题

至于表里面只有非聚集索引表结构是怎样的，大家可以看一下本系列的《sqlSERVER聚集索引与非聚集索引的再次研究（下）》

到时大家就会更加清楚了o(∩_∩)o

----------------------------------------------华丽的分割线----------------------------------------------------------

还有一个问题没有解决：

为什麽根节点只有两行？？？是不是根节点只作连接作用，所以只有两行 ？

 

聚集索引就说到这里了,有些地方有可能不对,希望大家强烈拍砖o(∩_∩)o

也希望给个推荐o(∩_∩)o

---------------------------------------------------------------------------

2013-7-21补充

为什麽根节点只有两行？？其实根节点不只有两行的

由于出现二层索引节点需要插入大量数据，如果数据很少的话索引节点只有一层，并且不能用主键，只能创建聚集索引

根据宋大侠说的，当页拆分的时候，根节点就会增加记录，我这里提供一下脚本

 

------------------------------------------------------------------DROP table [dbo].[Department] 6 7 DepartmentID int 8 name 9 Groupname 10 Company 11 ModifIEdDate 12 )13 14 CLUSTERED INDEX CL_DepartmentID ON Department(DepartmentID ASC)15 16 17 30000019 20 INTO Department(21 VALUES ( @i,0)">200)) )323 END24 25 DROP table Department26 FROM Department27 28 29 30 31 32 33 DESC

大家可以测试一下

插入数据的时候最好是根节点前的记录

附上宋大侠的文章：

T-SQL查询高级—SQL Server索引中的碎片和填充因子

根节点的记录行数在下面两种情况下会有所变化

（1）向表中插入数据或更新表中数据并产生碎片的时候

（2）碎片很多然后alter index REORGANIZE 重组索引的时候

ALTER INDEX tableForTestCIXDepartment] REORGANIZE

 ------------------------------------------------------------------------------

2013-8-24  补充：

关于我在文中说的

至于应不应该在重复值很多的列上建立聚集索引我这里也不敢妄下判断，因为实际环境和这里的测试环境不一样

 

今天看了一下《sqlSERVER企业级平台管理实践》，书里面第437页是这样说的

要慎重选择索引的第一个字段，最好选择一个重复记录最少的字段。这是因为索引上的统计信息只保存第一个字段的数据直方图。如果选择一个

重复数据很多的字段，这个索引的可选度就比较低了，会影响索引的价值

 

所以，为什麽建立聚集索引的时候，只能在一个字段上建立索引，并且这个字段最好不要重复，从数据直方图上也能解释这个原因

------------------------------------------------------------------------------------------

2013-9-15 补充：

如何查看聚集索引页面的内容，使用DBCC PAGE的时候使用1这个格式就可以了

聚集索引页中有三条记录，而且三条记录的Record Type = INDEX_RECORD

37397,255)">GO

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 2 3 PAGE: (1:37397) 4 5 6 BUFFER: 9 BUF @0x03E5DC3C11 bpage 0x1A8D8000 bhash 0x00000000 bpageno = (12 bdbID 5 breferences 0 bUse1 648713 bstat 0x3c0000b blog 0x212159bb bnext 0x0000000014 15 PAGE header:18 Page @0x1A8D800019 20 m_pageID 37397) m_headerVersion 1 m_type 221 m_typeFlagBits 0x0 m_level 1 m_flagBits 0x022 m_objID (AllocUnitID.idobj) 549 m_indexID (AllocUnitID.idind) 256 23 Metadata: AllocUnitID 72057594073907200 24 Metadata: PartitionID 72057594061717504 Metadata: IndexID 25 Metadata: ObjectID 1543676547 m_prevPage 0:0) m_nextPage 0)26 pminlen 11 m_slotCnt 3 m_freeCnt 805727 m_freeData 129 m_reservedCnt 0 m_lsn 3046:261:41)28 m_xactReserved 0 m_xdesID 0) m_ghostRecCnt 029 m_tornBits 0 30 31 Allocation Status33 GAM (2) = ALLOCATED SGAM (3) = ALLOCATED 34 PFS (32352) 0x60 MIXED_EXT ALLOCATED 0_PCT_FulL DIFF (6) = CHANGED35 ML (7) NOT MIN_LOGGED 36 37 DATA:38 39 40 Slot 0,Offset 0x60,Length 11,DumpStyle BYTE41 42 Record Type = INDEX_RECORD Record Attributes = 43 Memory Dump @0x0A3BC06044 45 00000000: 06010000 00283d00 000100†††††††††††††.....(=.... 46 47 Slot 0x6b,128)">48 49 Record Type 50 Memory @0x0A3BC06B51 52 06950100 00929100 000100†††††††††††††........... 53 54 Slot 2,0)">0x76,128)">55 56 Record Type 57 Memory @0x0A3BC07658 59 06290300 002f0000 000100†††††††††††††.).../..... 60 61 OFFSET table:62 63 Row - Offset 64 2 (0x2) - 118 (0x76) 65 1 (0x1) 107 (0x6b) 66 0 (0x0) 96 (0x60) 67 68 69 DBCC 输出了错误信息，请与系统管理员联系。

总结

以上是内存溢出为你收集整理的SQLSERVER聚集索引与非聚集索引的再次研究（上）全部内容，希望文章能够帮你解决SQLSERVER聚集索引与非聚集索引的再次研究（上）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。