sqlserver数据存储.docx
《sqlserver数据存储.docx》由会员分享，可在线阅读，更多相关《sqlserver数据存储.docx（35页珍藏版）》请在冰豆网上搜索。
sqlserver数据存储
概述
最近要分享一个课件就重新把这块知识整理了一遍出来，篇幅有点长，想要理解的透彻还是要上机实践。
正文
聚集索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--创建测试数据库
CREATEDATABASEIxdata
GO
USE[Ixdata]
GO
---创建测试表
CREATETABLEOrders
（IDINTPRIMARYKEYIDENTITY（1,1）,
NAMECHAR（80）NOTNULL,
IDATEDATETIMENOTNULLDEFAULT（GETDATE（））
）;
GO
---插入1000条测试数据
DECLARE@IDINT=1
WHILE（@ID<=1000）
BEGIN
INSERTINTOOrders（NAME）VALUES（'商品'+CONVERT（NVARCHAR（20）,@ID））
SET@ID=@ID+1
END
GO
SELECT*FROMOrders
GO
分析新创建的表的页的信息
1
2
3
4
5
6
7
8
---显示跟踪标志的状态
DBCCTRACESTATUS
---开启跟踪标志
DBCCTRACEON（3604,2588）
--DBCCTRACEOFF（3604,2588）
---获取对象的数据页，结构：
数据库、对象、显示
DBCCIND（Ixdata,Orders,-1）
/*
1：
显示所有分页的信息，包括IAM分页,数据分页,所有存在的LOB分页和行溢出页,索引分页
-1：
显示所有IAM、数据分页、及指定对象上全部索引的索引分页.
-2：
显示指定对象的所有IAM分页
0:
显示所有IAM、数据分页.
*/
DBCCIND的表结构
还可以通过另一种方法来测试：
1
2
3
4
5
6
7
selectso.name,so.object_id,sp.index_id,internals.total_pages,internals.used_pages,internals.data_pages,first_iam_page,
first_page,root_page
fromsys.objectsso
innerjoinsys.partitionssponso.object_id=sp.object_id
innerjoinsys.allocation_unitssaonsa.container_id=sp.hobt_id
innerjoinsys.system_internals_allocation_unitsinternalsoninternals.container_id=sa.container_id
whereso.object_id=object_id（'orders'）
最后三个字段分别是IAM页，根页，和第一个数据页；它们分别用16进制来表示，拿first_iam_page来分析，首先将编码从右往左一个字节接着一个字节反过来排行（0X代表16进制），结果就是0X,0001,00000050;前两个字节代表文件组号，最后4个字节代表页号。
16进制的0001转换成10进制就是1；16进制的00000050转换成10进制就是5*16的1次方=5*16=80，所以第一个数据页是4*16+15=79，根页是5*16+9=89 结果和前面的查询出来的结果是一样的。
从表格的otal_pages,used_pages,data_pages得到的结果也和前面查询出来的结果是一致的，总分配了17个页，使用了15个页包括13个数据页+1个IAM页+1个索引页。
手绘一张当前表格的聚集索引体系结构图：
分析索引页
1
2
---DBCCpage的格式为（数据库，文件id,页号，显示）
DBCCpage（Ixdata,1,89,3）
分析结果89页下面的子页总共有13页，每页80条记录，89索引页记录了每页的的键值的最小值，第一页就是id为1-80,第二页81-160，所以当你要找ID为150的数据的时候直接就可以去第90页里面找了。
PAGEHEADER
分析数据页
通过这些数据我们基本上可以知道90页的基本情况了，包括它的字段长度，上一页、下一页，还有该页的所以记录（这里没有截图出来）.
插入20万条记录分析索引结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
--插入20万条记录分析索引结构
DECLARE@IDINT=1
WHILE（@ID<=200000）
BEGIN
INSERTINTOOrders（NAME）VALUES（'商品'+CONVERT（NVARCHAR（20）,@ID））
SET@ID=@ID+1
END
CREATETABLEPage
（
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumberTINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR（30）,
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID TINYINT,
PrevPagePID INT
）;
GO
INSERTINTOPageEXEC（'DBCCIND（Ixdata,Orders,-1）'）
---查询索引页
SELECT [PageFID]
,[PagePID]
,[IAMFID]
,[IAMPID]
,[ObjectID]
,[IndexID]
,[PartitionNumber]
,[PartitionID]
,[iam_chain_type]
,[PageType]
,[IndexLevel]
,[NextPageFID]
,[NextPagePID]
,[PrevPageFID]
,[PrevPagePID]
FROM[Ixdata].[dbo].[Page]
WHEREPageType=2
go
selectso.name,so.object_id,sp.index_id,internals.total_pages,internals.used_pages,internals.data_pages,first_iam_page,
first_page,root_page
fromsys.objectsso
innerjoinsys.partitionssponso.object_id=sp.object_id
innerjoinsys.allocation_unitssaonsa.container_id=sp.hobt_id
innerjoinsys.system_internals_allocation_unitsinternalsoninternals.container_id=sa.container_id
whereso.object_id=object_id（'orders'）
通过两种方法查询到的索引页的数量是一样的，下面的这种计算方法是2524-2513-1（IAM页）=10，其中807页是root_page页它在第二级，其它的是中间级索引页页就是第一级；页可以通过下面的16进制计算出来，IAM=5*16=80，ROOT_PAGE=3*16*16+2*16+7=807
再分析89页
1
2
---DBCCpage的格式为（数据库，文件id,页号，显示）
DBCCpage（Ixdata,1,89,3）
查询结果总共有269行，页就是269个数据页，orders表总共插入了201000条记录，一个页面存80条记录，就需要2513个页面和上面查询到的data_page是一样的。
每个索引页存储269个数据页面就需要（‘select2513*1.0/269’除不尽加1）10个索引页，查询最后一个索引页2698发现它还没分页共存储了361条记录，总共8*269+361=2513
手绘存储结构
手绘的有点难看，但是意思差不多表达出来了。
大型对象（LOB）列
根据聚集索引中的数据类型，每个聚集索引结构将有一个或多个分配单元，将在这些单元中存储和管理特定分区的相关数据。
每个聚集索引的每个分区中至少有一个IN_ROW_DATA分配单元。
如果聚集索引包含大型对象（LOB）列，则它的每个分区中还会有一个LOB_DATA分配单元。
如果聚集索引包含的变量长度列超过8,060字节的行大小限制，则它的每个分区中还会有一个ROW_OVERFLOW_DATA分配单元。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
---创建测试表
CREATETABLEOrderslob
（IDINTPRIMARYKEYIDENTITY（1,1）,
NAMECHAR（80）NOTNULL,
ProductNVARCHAR（MAX）NOTNULL,
IDATEDATETIMENOTNULLDEFAULT（GETDATE（））
）;
GO
---插入1000条测试数据
DECLARE@IDINT=1
WHILE（@ID<=1000）
BEGIN
INSERTINTOOrderslob（NAME,Product）VALUES（CONVERT（NVARCHAR（20）,@ID）+'商品',REPLICATE（@ID,2））
SET@ID=@ID+1
END
--REPLICATE（@ID,200）
GO
DBCCIND（Ixdata,Orderslob,1）
1
2
--查看2719数据页的信息
DBCCpage（Ixdata,1,2719,1）
结果记录了每一条记录的偏移量。
每个人在自己的电脑上面测试页面id会不一样，但是反应的结果是一样的。
本篇文章是关于堆的存储结构。
堆是不含聚集索引的表（所以只有非聚集索引的表也是堆）。
堆的sys.partitions中具有一行，对于堆使用的每个分区，都有 index_id =0。
默认情况下，一个堆有一个分区。
当堆有多个分区时，每个分区有一个堆结构，其中包含该特定分区的数据。
例如，如果一个堆有四个分区，则有四个堆结构；每个分区有一个堆结构。
根据堆中的数据类型，每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。
每个堆中的每个分区至少有一个IN_ROW_DATA分配单元。
如果堆包含大型对象（LOB）列，则该堆的每个分区还将有一个LOB_DATA分配单元。
如果堆包含超过8,060字节行大小限制的可变长度列，则该堆的每个分区还将有一个ROW_OVERFLOW_DATA分配单元。
有关分配单元的详细信息，
sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列IAM页的第一页。
SQLServer使用IAM页在堆中移动。
堆内的数据页和行没有任何特定的顺序，也不链接在一起。
数据页之间唯一的逻辑连接是记录在IAM页内的信息。
正文
堆结构
可以通过扫描IAM页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。
因为IAM按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件进行。
使用IAM页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。
页面的组成
一个SQL数据页面=标头+数据行+剩余空间+行偏移表（如果表中存在大数据类型字段）+溢出表（如果存在）
行偏移
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
---测试数据
CREATETABLETheap
（IDINTIDENTITY（1,1）NOTNULL,
NAMENVARCHAR（MAX）NOTNULL,
IDATEDATETIMEDEFAULT（GETDATE（））NOTNULL
）
GO
---插入1000条测试数据
DECLARE@IDINT=1
WHILE（@ID<=1000）
BEGIN
INSERTINTOTheap（NAME）VALUES（（@ID））
SET@ID=@ID+1
END
GO
SELECT*FROMTheap
---开启跟踪标志
DBCCTRACEON（3604,2588）
--DBCCTRACEOFF（3604,2588）
---获取对象的数据页，结构：
数据库、对象、显示
DBCCIND（Ixdata,Theap,-1）
SELECT*FROMsys.system_internals_allocation_unitsWHEREcontainer_id=72057594039566336
分析114页
1
DBCCpage（Ixdata,1,114,3）
整个数据页有四部分组成
1.页面在内存中的映射信息（BUFFER:
）
2.页头部分（PAGEHEADER）：
记录了页号、页类型、记录数，LSN及其他信息，在上一章已经讲过
3.数据部分（DATA）：
以16进制格式存储行记录（从第96个字节开始）
4.行偏移部分（OFFSETTABLE）：
以倒序的顺序记录了行记录的指针位置，这个使用2的显示方式比较明显看出
看看一行记录在页面中是怎样记录的
00000000:
300010000100000076ff740164a40000†0…….v.t.d…
00000010:
0300b8010019003100†††††††††††††††††…….1.
1字节：
30>00110000；右边第一位开始是0位，第4位和第5位是1，由于在2008中nullbitmap总是存在的，所以只考虑第五位，即存在变长字段。
1字节：
00；状态位B在SQLServer2005/2008中未启用，所以为00
2字节：
1000;这两个字节是表示定长列的字节数，反过来排0010=1*16=16个字节，表中的定长列ID（4个字节）+IDATE（8个字节）+4个字节（默认加的）=16个字节
N个字节：
0100000076ff740164a40000；这N个字节是定长字段的内容，总共12个字节
2个字节：
0300；表中的字段数，由于表中只有3个字段所以用0300表示
1个字节：
b8>10111000;这个字节表示主要是判断对应的字段是否允许为空1代表允许为空，前三个字段都不允许为空，而且表只有三个字段所以不用看后面。
2个字节：
0100；这个字段表示变长列的个数，根据刚才说的方法倒过来0001=1个字段，表中页只有NAME字段是变长字段。
2个字节*变长字段的个数：
1900；由于表中只有一个变长字段，所以只有两个字节，表示第一个变长列的终止位置=25
N个字节：
变长字段的内容，3100转换成字符刚好是‘1’
在线16进制转字符：
查询
1
2
3
4
5
6
7
8
9
10
11
SELECT[ID]
,[NAME]
,[IDATE]
FROM[Ixdata].[dbo].[Theap]
WHERENAME='1'
SELECT[ID]
,[NAME]
,[IDATE]
FROM[Ixdata].[dbo].[Theap]
WHERENAME='900'
分析查询可以看出无论你查询的是’1′还是’900′,都是扫描一次，逻辑读取4次，因为存在4个页，用ID去查也是一样.
行溢出
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATETABLETheapover
（IDINTIDENTITY（1,1）NOTNULL,
NAMEVARCHAR（5000）NOTNULL,
NAME1VARCHAR（5000）NOTNULL,
IDATEDATETIMEDEFAULT（GETDATE（））NOTNULL
）
GO
---插入1000条测试数据
DECLARE@IDINT=1
WHILE（@ID<=1000）
BEGIN
INSERTINTOTheapover（NAME,NAME1）VALUES（REPLICATE（1,5000）,REPLICATE（2,5000））
SET@ID=@ID+1
END
GO
SELECT*FROMTheapover
ORDERBYID
GO
DBCCIND（Ixdata,Theapover,-1）
SELECT*FROMsys.system_internals_allocation_unitsWHEREcontainer_id=72057594039828480
总共插入了1000条记录，一行占一页再加上两个IAM页刚好2002页，
存在两个IAM页，分别是3281和3283页，还有一个比较特殊的页3280页，3280页是溢出数据里面的根页，等一下看一下这页的数据。
分析IAM页
1
DBCCpage（Ixdata,1,3283,3）
分析溢出页
1
DBCCpage（Ixdata,1,3282,3）
注意:
不是堆页和溢出页就只能一一对应，由于当前表中堆页容纳不下两条记录所以就导致了堆页和溢出页一样，当堆页可以存多条记录的时候就会出现一个堆页对应多个溢出页。
测试查询
1
2
3
4
5
6
SELECT [ID]
,[NAME]
,[NAME1]
,[IDATE]
FROM[Ixdata].[dbo].[Theapover]
whereID=500
当我继续往堆表里插入数据直到表超过4G的时候会有新的IAM页生成，而且IAM页之间存在链关系（数据页）。
查询发现新生成的3135IAM页种的数据页的行溢出指向的是新生成的511256IAM页的溢出页，这样的话IAM页之间的链关系对查询效率貌似没有什么改善的好处。
1.IAM用于查找分配给heap的所有数据页信息，IAM页中记录了所有的页面的页id。
2.对于大多数较小的heap表来说，仅需要一个IAM页就可以管理其页面。
3.若heap表大于4GB或包含LOB数据类型的话，则会包含多个IAM页面。
4.当查询要获取heap表的所有记录时，SQLServer使用IAM页来扫描heap表
总结
堆表的页是没有规律的不存在页链，所以导致堆表的查询效率很差,当查询一个10万条记录的堆表逻辑读取就需要10万次，如果堆表的数据量很大需要多次进行物理读获取页面的时候对于IO的消耗是非常大的，建议表都应该建聚集索引。
概述
非聚集索引与聚集索引具有相同的B树结构，它们之间的显著差别在于以下两点：
∙基础表的数据行不按非聚集键的顺序排序和存储。
∙非聚集索引的叶层是由索引页而不是由数据页组成。
既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。
非聚集索引中的每个索引行都包含非聚集键值和行定位符。
此定位符指向聚集索引或堆中包含该键值的数据行。
非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述：
∙如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。
该指针由文件标识符（ID）、页码和页上的行数生成。
整个指针称为行ID（RID）。
∙如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。
如果聚集索引不是唯一的索引，SQLServer将添加在内部生成的值（称为唯一值）以使所有重复键唯一。
此四字节的值对于用户不可见。
仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。
SQLServer通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。
对于索引使用的每个分区，非聚集索引在 index_id >0的sys.partitions中都有对应的一行。
默认情况下，一个非聚集索引有单个分区。
如果一个非聚集索引有多个分区，则每个分区都有一个包含该特定分区的索引行的B树结构。
例如，如果一个非聚集索引有四个分区，那么就有四个B树结构，每个分区中一个。
根据非聚集索引中数据类型的不同，每个非聚集索引结构会有一个或多个分配单元，在其中存储和管理特定分区的数据。
每个非聚集索引至少有一个针对每个分区的IN_ROW_DATA分配单元（存储索引B树页）。
如果非聚集索引包含大型对象（LOB）列，则还有一个针对每个分区的LOB_DATA分配单元。
此外，如果非聚集索引包含的可变长度列超过8,060字节行大小限制，则还有一个针对每个分区的ROW_OVERFLOW_DATA分配单元。
有关分配单元的详细信息，请参阅表组织和索引组织。
B树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位。
要很好的理解这篇文章的内容之前需要先阅
升级会员 