学习笔记（九）——数据库存储结构：页、聚集索引、非聚集索引

SQL Server用8KB 的页来存储数据，并且在SQL Server里磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入所有数据页。页有不同的类型，像数据页，GAM，SGAM等。先理解下数据页结构。

SQL Server把数据记录存在数据页（Data Page）里。数据记录是堆表里、聚集索引里叶子节点的行。

数据页由3个部分组成。页头（标头），数据区（数据行和可用空间）及行偏移数组。

我们来执行下列的命令：

DBCC IND(<span style="color: #800000;">‘</span><span style="color: #800000;">InternalStorageFormat</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">Customers</span><span style="color: #800000;">‘</span>,-<span style="color: #800080;">1</span>)

结果如下

可以看到有2条记录，一条记录为页面类型（PageType）为10的页和一条记录为页面类型（PageType）为1的页。页面类型（PageType）10是IAM页，页面类型（PageType）1是数据页，它的页ID是79.

2、聚集索引与费聚集索引的对比

非聚集索引与聚集索引具有相同的树结构，它们之间的显著差别在于以下两点：

          基础表的数据行不按非聚集键的顺序排序和存储。

          非聚集索引的叶层是由索引页而不是由数据页组成。

 3、聚集索引：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个。

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

4、非聚集索引：非聚集索引指定表的逻辑顺序，数据存储的一个位置，索引存储在另一个位置，索引中包含指向数据的指针，可以有多个。如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引。

5、创建聚集索引查询

 查询表如下：

然后在DateKey列建立聚集索引：

<span style="color: #000000;">CREATE CLUSTERED
INDEX Index_Key  ON DimDate(DateKey)</span>

  执行结果如下

DROP INDEX Dimdate.Index_Key    --<span style="color: #000000;">删除索引
    
　　CREATE CLUSTERED
　　INDEX Index_Key ON DimDate(DateKey)    </span>--再在重建列聚集索引

　再执行查询语句：

　　select top 3 * from Person

留意到同样的语句，返回已经改变。可以聚集索引是表的顺序，会影响到top语句。

6、创建非聚集索引

<span style="color: #000000;">USE [pratice] 
GO
CREATE TABLE Department8(
    DepartmentID </span><span style="color: #0000ff;">int</span> IDENTITY(<span style="color: #800080;">1</span>,<span style="color: #800080;">1</span><span style="color: #000000;">) NOT NULL ,
    Name NVARCHAR(</span><span style="color: #800080;">200</span><span style="color: #000000;">) NOT NULL,
    GroupName NVARCHAR(</span><span style="color: #800080;">200</span><span style="color: #000000;">) NOT NULL,
    Company NVARCHAR(</span><span style="color: #800080;">300</span><span style="color: #000000;">),
    ModifiedDate datetime NOT NULL  DEFAULT (getdate())
)
CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department8](Name,[GroupName])
DECLARE @i INT
SET @i</span>=<span style="color: #800080;">1</span><span style="color: #000000;">
WHILE @i </span>< <span style="color: #800080;">100</span><span style="color: #000000;">
    BEGIN
        INSERT  INTO Department8 ( name, [Company], groupname )
        VALUES  ( </span><span style="color: #800000;">‘</span><span style="color: #800000;">销售部</span><span style="color: #800000;">‘</span>+CAST(@i AS VARCHAR(<span style="color: #800080;">200</span>)), <span style="color: #800000;">‘</span><span style="color: #800000;">中国你好有限公司XX分公司</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">销售组</span><span style="color: #800000;">‘</span>+CAST(@i AS VARCHAR(<span style="color: #800080;">200</span><span style="color: #000000;">)) )
        SET @i </span>= @i + <span style="color: #800080;">1</span><span style="color: #000000;">
    END
    
SELECT </span>*<span style="color: #000000;"> FROM [dbo].[Department8]
</span>--<span style="color: #000000;">TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC (</span><span style="color: #800000;">‘</span><span style="color: #800000;">DBCC IND(pratice,Department8,-1) </span><span style="color: #800000;">‘</span><span style="color: #000000;">)
SELECT </span>* FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

学习笔记（九）——数据库存储结构：页、聚集索引、非聚集索引

标签：键值   mod   技术分享   into   数据区   删除   主键   存储结构   intern