欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SQLSERVER聚集索引与非聚集索引的再次研究(上)

程序员文章站 2022-06-11 14:11:51
...

SQLSERVER聚集索引与非聚集索引的再次研究(上) 上篇主要说聚集索引 下篇的地址:SQLSERVER聚集索引与非聚集索引的再次研究(下) 由于本人还是SQLSERVER菜鸟一枚,加上一些实验的逻辑严谨性, 单写《SQLSERVER聚集索引与非聚集索引的再次研究(上)》就用

SQLSERVER聚集索引与非聚集索引的再次研究(上)

上篇主要说聚集索引

下篇的地址:SQLSERVER聚集索引与非聚集索引的再次研究(下)

由于本人还是SQLSERVER菜鸟一枚,加上一些实验的逻辑严谨性,

单写《SQLSERVER聚集索引与非聚集索引的再次研究(上)》就用了12个小时,两篇文章加起来最起码写了20个小时,

本人非常非常用心的努力完成这两篇文章,希望各位看官给点意见o(∩_∩)o

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

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

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

创建一个聚集索引表

1 --只有聚集索引
2 CREATE TABLE Department(
3     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
4     Name NVARCHAR(200) NOT NULL,
5     GroupName NVARCHAR(200) NOT NULL,
6     Company NVARCHAR(300),
7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
8 )

插入10W条记录

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

将DBCC IND的结果放入DBCCRESULT表

1 INSERT 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字段

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

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

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

上面索引页的结构

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

 1 DBCC TRACEON(3604,-1)
 2 GO
 3 
 4 DBCC PAGE([pratice],1,3527,3)
 5 GO
 6 
 7 
 8 DBCC PAGE([pratice],1,3528,3)
 9 GO
10 
11 DBCC PAGE([pratice],1,14464,3)
12 GO

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

这里有个问题:为什麽根节点只有两行???是不是根节点只作连接作用,所以只有两行 ,不过这个问题我也不清楚

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

而这个KeyHashValue我们就叫做键,也就是key-value中的key

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

这里要分两种情况:(1)聚集索引查找和(2)聚集索引扫描

(1)聚集索引查找

SQLSERVER聚集索引与非聚集索引的再次研究(上)

放大一下索引页

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER首先把每个数据页的头一条记录里的DepartmentID的值加上一定范围值hash出一个key值,然后放在KeyHashValue列里

当我要找DepartmentID为110的那条记录里的GroupName和Company的值的时候,首先SQLSERVER根据where DepartmentID=110

将110加上一个范围值hash出一个值,这个值就是KeyHashValue的值,找到KeyHashValue=(f000ff86397c)的那条记录

然后到数据页13791里找出DepartmentID为110的那条记录里的GroupName和Company的值

其实这里的算法应该跟hash join是一样的,但是实际具体怎麽算的?本人就不清楚了,大家可以看一下hash join的原理

个人感觉在SQLSERVER里 key-value hash桶用途很广泛,执行计划、 hash join、 聚集索引都用到了

证明:这里我可以证明一下SQLSERVER聚集索引查找记录的流程

先到索引页里找到键值为KeyHashValue=XXX的那条记录,然后再到数据页里把实际数据读出来

运行下面的SQL语句,看一下SQLSERVER申请的锁就知道了

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 --只有聚集索引
 2 CREATE TABLE Department2(
 3     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 4     Name NVARCHAR(200) NOT NULL,
 5     GroupName NVARCHAR(200) NOT NULL,
 6     Company NVARCHAR(300),
 7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
 8 )
 9 
10 INSERT INTO Department2(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组')
11 GO 100000
12 
13 
14 SELECT * FROM Department2
15 
16 --先清空[DBCCResult]表里的记录
17 --TRUNCATE TABLE [dbo].[DBCCResult]
18 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department2,-1) ')
19 
20 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
21 
22 DBCC PAGE([pratice],1,14471,3)
23 GO
24 
25 DBCC PAGE([pratice],1,4375,3)
26 GO
27 DBCC PAGE([pratice],1,4376,3)
28 GO
View Code

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 USE [pratice]
 2 GO
 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 4 GO
 5 --以下查询使用了聚集索引查找 ctrl+l
 6 BEGIN TRAN
 7 SELECT GroupName FROM [dbo].[Department2]  WHERE DepartmentID IN(32641,361,32281) 
 8 
 9 --COMMIT TRAN
10 
11 USE [pratice] --要查询申请锁的数据库
12 GO
13 SELECT
14 [request_session_id],
15 c.[program_name],
16 DB_NAME(c.[dbid]) AS dbname,
17 [resource_type],
18 [request_status],
19 [request_mode],
20 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
21 p.[index_id]
22 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
23 ON a.[resource_associated_entity_id]=p.[hobt_id]
24 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
25 WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
26 ORDER BY [request_session_id],[resource_type]
View Code

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

(2)聚集索引扫描

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 --只有聚集索引
 2 CREATE TABLE Department2(
 3     DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 4     Name NVARCHAR(200) NOT NULL,
 5     GroupName NVARCHAR(200) NOT NULL,
 6     Company NVARCHAR(300),
 7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
 8 )
 9 
10 DECLARE @i INT
11 SET @i=1
12 WHILE @i  100000 
13     BEGIN
14         INSERT  INTO Department3 ( name, [Company], groupname )
15         VALUES  ( '销售部', '中国你好有限公司XX分公司'+CAST(@i AS VARCHAR(200)), '销售组'+CAST(@i AS VARCHAR(200)) )
16         SET @i = @i + 1
17     END
18 
19 
20 SELECT * FROM Department2
View Code

证明:

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 USE [pratice]
 2 GO
 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 4 GO
 5 --以下查询使用了聚集索引查找 ctrl+l
 6 BEGIN TRAN
 7 SELECT * FROM [dbo].[Department2]  WHERE [GroupName] ='销售组83421'
 8 
 9 --COMMIT TRAN
10 
11 USE [pratice] --要查询申请锁的数据库
12 GO
13 SELECT
14 [request_session_id],
15 c.[program_name],
16 DB_NAME(c.[dbid]) AS dbname,
17 [resource_type],
18 [request_status],
19 [request_mode],
20 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
21 p.[index_id]
22 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
23 ON a.[resource_associated_entity_id]=p.[hobt_id]
24 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
25 WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
26 ORDER BY [request_session_id],[resource_type]
View Code

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

再看一下聚集索引查找的流程

SQLSERVER首先把每个数据页的头一条记录里的DepartmentID的值加上一定范围值hash出一个key值,然后放在KeyHashValue列里

当我要找DepartmentID为110的那条记录里的GroupName和Company的值的时候,首先SQLSERVER根据where DepartmentID=110

将110加上一个范围值hash出一个值,这个值就是KeyHashValue的值,找到KeyHashValue=(f000ff86397c)的那条记录

然后到数据页13791里找出DepartmentID为110的那条记录里的GroupName和Company的值

因为[GroupName]列不是索引列,所以根本找不到KeyHashValue值,所以这里只能使用扫描所有数据页的方法来找出记录,除非找到那条记录

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

那个键锁我估计是当SQLSERVER找到那条记录之后,需要在

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

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

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

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

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

这里有一个问题:没有主键但是有聚集索引,索引页的列数不一样,会多了一列,而这个列(uniquifier)的作用在下面会讲到

这里创建Department3表

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 --只有聚集索引
 2 CREATE TABLE Department3(
 3     DepartmentID int IDENTITY(1,1) NOT NULL ,
 4     Name NVARCHAR(200) NOT NULL,
 5     GroupName NVARCHAR(200) NOT NULL,
 6     Company NVARCHAR(300),
 7     ModifiedDate datetime NOT NULL  DEFAULT (getdate())
 8 )
 9 
10 CREATE CLUSTERED INDEX CL_DepartmentID ON [dbo].[Department3]([DepartmentID])
11 
12 DECLARE @i INT
13 SET @i=1
14 WHILE @i  100000 
15     BEGIN
16         INSERT  INTO Department3 ( name, [Company], groupname )
17         VALUES  ( '销售部', '中国你好有限公司XX分公司'+CAST(@i AS VARCHAR(200)), '销售组'+CAST(@i AS VARCHAR(200)) )
18         SET @i = @i + 1
19     END
20 
21 
22 SELECT * FROM Department3
23 
24 --TRUNCATE TABLE [dbo].[DBCCResult]
25 
26 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department3,-1) ')
27 
28 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
29 
30 DBCC PAGE([pratice],1,13861,3)
31 GO
View Code

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

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

创建Department4表

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 --只有聚集索引
 2 CREATE TABLE Department4  --包含复合主键DepartmentID 和Name
 3 (
 4   DepartmentID INT IDENTITY(1, 1) NOT NULL ,
 5   Name NVARCHAR(200) NOT NULL ,
 6   GroupName NVARCHAR(200) NOT NULL ,
 7   Company NVARCHAR(300) ,
 8   ModifiedDate DATETIME NOT NULL DEFAULT ( GETDATE() ) ,
 9   CONSTRAINT [PK_Department4_1] PRIMARY KEY CLUSTERED
10     ( DepartmentID ASC,
11       Name ASC )
12     WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
13            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
14 ) ON [PRIMARY]
15 
16 
17 DECLARE @i INT
18 SET @i=1
19 WHILE @i  100000 
20     BEGIN
21         INSERT  INTO Department4 ( name, [Company], groupname )
22         VALUES  ( '销售部'+CAST(@i AS VARCHAR(200)), '中国你好有限公司XX分公司', '销售组' )
23         SET @i = @i + 1
24     END
25 
26 
27 SELECT * FROM [dbo].[Department4]
28 
29 
30 
31 --TRUNCATE TABLE [dbo].[DBCCResult]
32 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department4,-1) ')
33 
34 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
35 
36 DBCC PAGE([pratice],1,7102,3)
37 GO
View Code

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

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

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

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

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

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

聚集索引页的结构

SQLSERVER聚集索引与非聚集索引的再次研究(上)

非聚集索引页的结构

SQLSERVER聚集索引与非聚集索引的再次研究(上)

非聚集索引页面的结构会在SQLSERVER聚集索引与非聚集索引的再次研究(下)里讲到

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

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

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

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

 1 --只有聚集索引
 2 USE [pratice]
 3 GO
 4 CREATE TABLE Department5
 5 (
 6   DepartmentID INT IDENTITY(1, 1) NOT NULL ,
 7   Name NVARCHAR(200) NOT NULL ,
 8   GroupName NVARCHAR(200) NOT NULL ,
 9   Company NVARCHAR(300) ,
10   ModifiedDate DATETIME NOT NULL  DEFAULT ( GETDATE() ) 
11 )
12 
13 CREATE CLUSTERED INDEX CL_Company ON [dbo].[Department5]([Company] ASC)
14 
15 --DROP TABLE [dbo].[Department5]
16 
17 DECLARE @i INT
18 SET @i=1
19 WHILE @i  10000
20     BEGIN
21         INSERT  INTO Department5 ( name, [Company], groupname )
22         VALUES  ( '销售部'+CAST(@i AS VARCHAR(200)), '中国你好有限公司XX分公司', '销售组' )
23         SET @i = @i + 1
24     END
View Code

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

1 --TRUNCATE TABLE [dbo].[DBCCResult]
2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department5,-1) ')
3 
4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
5 
6 DBCC PAGE([pratice],1,14516,3)
7 GO
View Code

SQLSERVER聚集索引与非聚集索引的再次研究(上)

在Department3表的时候讲到列(uniquifier),为什麽有主键的表没有这个列,而聚集索引的表有这个列,原因在于

主键列不能有重复值,必须是唯一的,而聚集索引允许有重复值,所以聚集索引需要增加列(uniquifier)来区分重复值

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

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

 1 SET STATISTICS TIME ON
 2 SELECT * FROM [dbo].[Department5] WHERE [Company]='中国你好有限公司XX分公司' AND [DepartmentID]=241 
 3 
 4 SQL Server 分析和编译时间: 
 5    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 6 
 7 (1 行受影响)
 8 
 9 SQL Server 执行时间:
10    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQLSERVER聚集索引与非聚集索引的再次研究(上)

1 SET STATISTICS TIME ON
2 SELECT * FROM [dbo].[Department5] WHERE name='销售部106' AND [DepartmentID]=106  --聚集索引扫描
3 SQL Server 执行时间:
4    CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQLSERVER聚集索引与非聚集索引的再次研究(上)

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

在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字节的字段

SQLSERVER聚集索引与非聚集索引的再次研究(上)SQLSERVER聚集索引与非聚集索引的再次研究(上)

   1 PAGE: (1:14517)
   2 
   3 
   4 BUFFER:
   5 
   6 
   7 BUF @0x03EC9D64
   8 
   9 bpage = 0x1A096000                   bhash = 0x00000000                   bpageno = (1:14517)
  10 bdbid = 5                            breferences = 0                      bUse1 = 4722
  11 bstat = 0x3c00009                    blog = 0x32159                       bnext = 0x00000000
  12 
  13 PAGE HEADER:
  14 
  15 
  16 Page @0x1A096000
  17 
  18 m_pageId = (1:14517)                 m_headerVersion = 1                  m_type = 1
  19 m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x200
  20 m_objId (AllocUnitId.idObj) = 317    m_indexId (AllocUnitId.idInd) = 256  
  21 Metadata: AllocUnitId = 72057594058702848                                 
  22 Metadata: PartitionId = 72057594049462272                                 Metadata: IndexId = 1
  23 Metadata: ObjectId = 1022626686      m_prevPage = (1:14514)               m_nextPage = (1:14518)
  24 pminlen = 16                         m_slotCnt = 102                      m_freeCnt = 38
  25 m_freeData = 7950                    m_reservedCnt = 0                    m_lsn = (2568:5252:8)
  26 m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
  27 m_tornBits = -1007571449             
  28 
  29 Allocation Status
  30 
  31 GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
  32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                      DIFF (1:6) = NOT CHANGED
  33 ML (1:7) = NOT MIN_LOGGED            
  34 

  35 Slot 0 Offset 0x60 Length 77
  36 
  37 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
  38 
  39 Memory Dump @0x0A64C060
  40 
  41 00000000:   30001000 6a000000 d42e7c01 fea10000 †0...j.....|.....         
  42 00000010:   0600c004 0021003b 0047004d 00690000 †.....!.;.G.M.i..         
  43 00000020:   002d4efd 56604f7d 59096750 966c51f8 †.-N.V`O}Y.gP.lQ.         
  44 00000030:   53580058 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
  45 00000040:   90310030 00360000 952e55c4 7e††††††††.1.0.6....U.~            
  46 
  47 Slot 0 Column 0 Offset 0x1d Length 4
  48 
  49 UNIQUIFIER = 105                     
  50 
  51 Slot 0 Column 1 Offset 0x21 Length 26
  52 
  53 Company = 中国你好有限公司XX分公司   
  54 
  55 Slot 0 Column 2 Offset 0x4 Length 4
  56 
  57 DepartmentID = 106                   
  58 
  59 Slot 0 Column 3 Offset 0x3b Length 12
  60 
  61 Name = 销售部106                     
  62 
  63 Slot 0 Column 4 Offset 0x47 Length 6
  64 
  65 GroupName = 销售组                   
  66 
  67 Slot 0 Column 5 Offset 0x8 Length 8
  68 
  69 ModifiedDate = 07 17 2013 11:04PM    
  70 
  71 Slot 1 Offset 0xad Length 77
  72 
  73 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
  74 
  75 Memory Dump @0x0A64C0AD
  76 
  77 00000000:   30001000 6b000000 d42e7c01 fea10000 †0...k.....|.....         
  78 00000010:   0600c004 0021003b 0047004d 006a0000 †.....!.;.G.M.j..         
  79 00000020:   002d4efd 56604f7d 59096750 966c51f8 †.-N.V`O}Y.gP.lQ.         
  80 00000030:   53580058 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
  81 00000040:   90310030 00370000 952e55c4 7e††††††††.1.0.7....U.~            
  82 
  83 Slot 1 Column 0 Offset 0x1d Length 4
  84 
  85 UNIQUIFIER = 106                     
  86 
  87 Slot 1 Column 1 Offset 0x21 Length 26
  88 
  89 Company = 中国你好有限公司XX分公司   
  90 
  91 Slot 1 Column 2 Offset 0x4 Length 4
  92 
  93 DepartmentID = 107                   
  94 
  95 Slot 1 Column 3 Offset 0x3b Length 12
  96 
  97 Name = 销售部107                     
  98 
  99 Slot 1 Column 4 Offset 0x47 Length 6
 100 
 101 GroupName = 销售组                   
 102 
 103 Slot 1 Column 5 Offset 0x8 Length 8
 104 
 105 ModifiedDate = 07 17 2013 11:04PM    
 106 
 107 Slot 2 Offset 0xfa Length 77
 108 
 109 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
 110 
 111 Memory Dump @0x0A64C0FA
 112 
 113 00000000:   30001000 6c000000 d42e7c01 fea10000 †0...l.....|.....         
 114 00000010:   0600c004 0021003b 0047004d 006b0000 †.....!.;.G.M.k..         
 115 00000020:   002d4efd 56604f7d 59096750 966c51f8 †.-N.V`O}Y.gP.lQ.         
 116 00000030:   53580058 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
 117 00000040:   90310030 00380000 952e55c4 7e††††††††.1.0.8....U.~            
 118 
 119 Slot 2 Column 0 Offset 0x1d Length 4
 120 
 121 UNIQUIFIER = 107                     
 122 
 123 Slot 2 Column 1 Offset 0x21 Length 26
 124 
 125 Company = 中国你好有限公司XX分公司   
 126 
 127 Slot 2 Column 2 Offset 0x4 Length 4
 128 
 129 DepartmentID = 108                   
 130 
 131 Slot 2 Column 3 Offset 0x3b Length 12
 132 
 133 Name = 销售部108                     
 134 
 135 Slot 2 Column 4 Offset 0x47 Length 6
 136 
 137 GroupName = 销售组                   
 138 
 139 Slot 2 Column 5 Offset 0x8 Length 8
 140 
 141 ModifiedDate = 07 17 2013 11:04PM    
 142 
 143 Slot 3 Offset 0x147 Length 77
 144 
 145 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
 146 
 147 Memory Dump @0x0A64C147
 148 
 149 00000000:   30001000 6d000000 d42e7c01 fea10000 †0...m.....|.....         
 150 00000010:   0600c004 0021003b 0047004d 006c0000 †.....!.;.G.M.l..         
 151 00000020:   002d4efd 56604f7d 59096750 966c51f8 †.-N.V`O}Y.gP.lQ.         
 152 00000030:   53580058 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
 153 00000040:   90310030 00390000 952e55c4 7e††††††††.1.0.9....U.~            
 154 
 155 Slot 3 Column 0 Offset 0x1d Length 4
 156 
 157 UNIQUIFIER = 108                     
 158 
 159 Slot 3 Column 1 Offset 0x21 Length 26
 160 
 161 Company = 中国你好有限公司XX分公司   
 162 
 163 Slot 3 Column 2 Offset 0x4 Length 4
 164 
 165 DepartmentID = 109                   
 166 
 167 Slot 3 Column 3 Offset 0x3b Length 12
 168 
 169 Name = 销售部109                     
 170 
 171 Slot 3 Column 4 Offset 0x47 Length 6
 172 
 173 GroupName = 销售组                   
 174 
 175 Slot 3 Column 5 Offset 0x8 Length 8
 176 
 177 ModifiedDate = 07 17 2013 11:04PM    
 178 
 179 Slot 4 Offset 0x194 Length 77
 180 
 181 Record Typ