|
关于SQLServer表分区，这里有篇文章写的很好，我就不多废话了，转载原地址：?http://www.cnblogs.com/lyhabc/p/3350121.html
网上表分区的文章成千上万，但是分区之后表数据的分布和流向都没有说
首先要说明的是表分区的分区不是指页面存储概念的分区，我用下面的图来表示
他们是没有关系的
正式开始
SQL脚本如下：
1 USE master
2 GO
3
4 --创建数据库
5 CREATE DATABASE [Test]
6 7
8 USE 9 10
11
12 1.创建文件组
13 ALTER 14 ADD FILEGROUP FG_TestUnique_Id_0115
16 17 FG_TestUnique_Id_0218
19 20 FG_TestUnique_Id_0321
22 2.创建文件
23 24 ADD FILE
25 (NAME = N'FG_TestUnique_Id_01_data',FILENAME E:FG_TestUnique_Id_01_data.ndf= 1MB,FILEGROWTH = 1MB )
26 TO FILEGROUP ];
27
28 29 30 (NAME FG_TestUnique_Id_02_dataE:FG_TestUnique_Id_02_data.ndf31 32
33 34 35 (NAME FG_TestUnique_Id_03_dataE:FG_TestUnique_Id_03_data.ndf36 ];
创建分区函数和分区方案
我们创建了一个用于数据类型为int的分区函数，按照数值来划分
文件组????????????????????????????分区????? ?取值范围
[FG_TestUnique_Id_01]??? 1??????? (小于2,2]? --包括2
[FG_TestUnique_Id_02]??? 2??????? [3,4]
[FG_TestUnique_Id_03]??? 3??????? (4,大于4)? --不包括4
3.创建分区函数
我们创建了一个用于数据类型为int的分区函数，按照数值来划分
3 文件组 分区 取值范围
[FG_TestUnique_Id_01] 1 (小于2,2]--包括2
[FG_TestUnique_Id_02] 2 [3,4]
[FG_TestUnique_Id_03] 3 (4,大于4) --不包括4
CREATE PARTITION FUNCTION
9 Fun_TestUnique_Id(INT) AS
10 RANGE LEFT
11 FOR VALUES(2,4)
12
13
14
4.创建分区方案
CREATE PARTITION SCHEME
18 Sch_TestUnique_Id PARTITION Fun_TestUnique_Id
TO(],])
建立分区表
5.创建分区表
TABLE testPartionTable
(
4 id INT NOT NULL, 5 itemno CHAR(20),128); line-height:1.5!important"> 6 itemname 40 7 )ON Sch_TestUnique_Id(id 8
9
10 INSERT INTO dbo].testPartionTable] ( itemnoitemname )
SELECT 1,0); line-height:1.5!important">1中国' UNION ALL
2法国3,0); line-height:1.5!important">3美国4,0); line-height:1.5!important">4英国15 5,0); line-height:1.5!important">5德国'
查看边界值点
1 查看边界值点
2 select * from sys.partition_range_values
3 GO
?
查看表数据
FROM testNonPartionTable
我们看一下当前数据库的情况
EXEC syssp_helpdb] @dbname = test sysname
FG_TestUnique_Id_0X这三个文件组建立在三个ndf文件上,三个ndf文件都位于E盘
而fileid分别是3、4、5
我们看一下表的页面分配情况
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 TRUNCATE TABLE [dbo].[DBCCResult]
INTO DBCCResult EXEC (DBCC IND(test,testPartionTable,-1) 'DBCCResultORDER BY PageTypeDESC
?
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(test3 OBJECT_ID('),4 NULL,0); line-height:1.5!important">detailed')
从上面两个图我们可以得知
-----------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
分区号1~3对应的文件组名和ndf文件名分别是：
分区号1 (PartitionNumber1)-》文件组FG_TestUnique_Id_01-》E:FG_TestUnique_Id_01_data.ndf
分区号2 (PartitionNumber2)-》文件组FG_TestUnique_Id_02-》E:FG_TestUnique_Id_02_data.ndf
分区号3 (PartitionNumber3)-》文件组FG_TestUnique_Id_03-》E:FG_TestUnique_Id_03_data.ndf
表中只有一个数据页面8 和一个IAM页面9
但是每个ndf文件里面却都存储了一份数据页面8 和一份IAM页面9
而且每个ndf文件里面 数据页面存储的内容都不一样，虽然页面编号一样，都是8
数据页面存储的内容
我们来看一下每个ndf文件里面的数据页面都存储了些什么内容？
我们先来看一下testPartionTable表的objectID
SELECT ') AS OBJECTID'
先看FILEID为3的文件里面的数据页面
DBCC TRACEON(3604,128); line-height:1.5!important">-1DBCC PAGE(test,0); line-height:1.5!important">8,0); line-height:1.5!important">34 GO
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
2
3 PAGE: (3:8 4
5
BUFFER:
9 BUF @0x03DFDE90
11 bpage = 0x16EEE000 bhash 0x00000000 bpageno = (12 bdbid 11 breferences 0 bUse1 28337
13 bstat 0x3c0000b blog 0x212121bb bnext 0x00000000
PAGE HEADER:
16
17
18 Page @0x16EEE000
19
20 m_pageId 8) m_headerVersion 1 m_type 1
21 m_typeFlagBits 0x4 m_level 0 m_flagBits 0x8000
22 m_objId (AllocUnitId.idObj) 82 m_indexId (AllocUnitId.idInd) 256
23 Metadata: AllocUnitId 72057594043301888
24 Metadata: PartitionId 72057594038321152 Metadata: IndexId 0
25 Metadata: ObjectId 2073058421 m_prevPage 0:0) m_nextPage 026 pminlen 68 m_slotCnt 2 m_freeCnt 7950
27 m_freeData 238 m_reservedCnt 0 m_lsn 41:289:2528 m_xactReserved 0 m_xdesId 0) m_ghostRecCnt 29 m_tornBits 0
30
Allocation Status
33 GAM (2) = ALLOCATED SGAM (3) ALLOCATED
34 PFS (1) 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (6) CHANGED
35 ML (7) NOT MIN_LOGGED
36
37 Slot 0 Offset 0x60 Length 71
38
39 Record Type = PRIMARY_RECORD Record Attributes NULL_BITMAP
40 Memory Dump @0x0A70C060
41
42 00000000: 10004400 01000000 31202020 20202020 ?..D.....1
43 00000010: 20202020 20202020 d6d0b9fa ? ....
44 00000020: ?
45 00000030: 46 00000040: 0300f8?????????????????????? ...
47
48 Slot 0 Column 0x4 Length 4
49
50 id 1
51
52 Slot 1 Offset 0x8 Length 20
53
54 itemno 1
55
56 Slot 2 Offset 0x1c Length 40
57
58 itemname 中国
59
60 Slot 0xa7 Length 61
62 Record Type 63 Memory @0x0A70C0A7
64
65 02000000 32202020 2
66 b7a8b9fa ? ....
67 68 69 70
71 Slot 1 72
73 id 2
74
75 Slot 76
77 itemno 2
78
79 Slot 80
81 itemname 法国
82
83
84 DBCC 输出了错误信息，请与系统管理员联系。
这个页面是属于testPartionTable表
1 Slot 3 id 5 Slot 6
7 itemno 9 Slot 11 itemname 13 Slot 15
17 Slot 19 id 20
21 Slot 22
23 itemno 24
25 Slot 26
27 itemname = 法国
FILEID为3的文件里面的数据页面里存放了id为1和id为2的这两条记录
看FILEID为4的文件里面的数据页面
4:@0x03E6777C
0x19A78000 bhash 28520
@0x19A78000
83 m_indexId (AllocUnitId.idInd) 72057594043367424
72057594038386688 Metadata: IndexId 49@0x0A37C060
03000000 33202020 3
c3c0b9fa ? ....
3
3
美国
@0x0A37C0A7
04000000 34202020 4
d3a2b9fa ? ....
4
4
英国
?这个页面是属于testPartionTable表
4 Slot 6 id 8 Slot 10 itemno 12 Slot 14 itemname 16 Slot 18
19 Slot 21 id 23 Slot 25 itemno 27 Slot 28
29 itemname = 英国
FILEID为4的文件里面的数据页面里存放了id为3和id为4的这两条记录
看FILEID为5的文件里面的数据页面
5:@0x03E7B0FC
0x1A2A8000 bhash 28674
@0x1A2A8000
84 m_indexId (AllocUnitId.idInd) 72057594043432960
72057594038452224 Metadata: IndexId 1 m_freeCnt 8023
167 m_reservedCnt 326:2305000000 35202020 5
b5c2b9fa ? ....
5
5
德国
60
61
= 德国
FILEID为5的文件里面的数据页面里存放了id为5这条记录
再看我们刚才建立的分区函数，和各个ndf里的数据页面存储的记录条数
创建分区函数
我们创建了一个用于数据类型为int的分区函数，按照数值来划分
文件组 分区 取值范围
4 ] 1 (小于2,0); line-height:1.5!important">2]包括2
5 2 3,4 6 3 (不包括4
4)
当执行select * from testPartionTable的时候，就需要跨这三个ndf文件来读取记录
IO一定会有所影响，所以一般应用都是按照月份、性别等来进行分区，确保查询数据的时候不要跨多个文件组
如果表没有分区是怎样的？
SQL脚本如下，建立testNonPartionTable表：
testNonPartionTable
5 id 6 itemno 7 itemname '
21
47,51); font-family:'Courier New'!important">
1 2
3 PAGE: (1:47 4
5
6 7
8
9 BUF @0x03E83C38
10
11 bpage 0x1763C000 bhash 12 bdbid 29165
13 bstat 0xca2159bb bnext 14
15 16
17
18 Page @0x1763C000
19
20 m_pageId 47) m_headerVersion 21 m_typeFlagBits 22 m_objId (AllocUnitId.idObj) 86 m_indexId (AllocUnitId.idInd) 23 Metadata: AllocUnitId 72057594043564032
24 Metadata: PartitionId 72057594038583296 Metadata: IndexId 25 Metadata: ObjectId 2105058535 m_prevPage 26 pminlen 5 m_freeCnt 7731
27 m_freeData 451 m_reservedCnt 355: 28 m_xactReserved 29 m_tornBits 30
31 32
33 GAM ( ALLOCATED
34 PFS ( 35 ML ( 36
37 Slot 38
39 Record Type 40 Memory 41
42 43 44 45 46 47
48 Slot 49
50 id 51
52 Slot 53
54 itemno 55
56 Slot 57
58 itemname 59
60 Slot 61
62 Record Type 63 Memory 64
65 66 67 68 69 70
71 Slot 72
73 id 74
75 Slot 76
77 itemno 78
79 Slot 80
81 itemname 82
83 Slot 0xee Length 84
85 Record Type 86 Memory @0x0A37C0EE
87
88 89 90 91 92 93
94 Slot 2 95
96 id 97
98 Slot 99
100 itemno 101
102 Slot 103
104 itemname 105
106 Slot 3 Offset 0x135 Length 107
108 Record Type 109 Memory @0x0A37C135
110
111 112 113 114 115 116
117 Slot 3 118
119 id 120
121 Slot 122
123 itemno 124
125 Slot 126
127 itemname 128
129 Slot 4 Offset 0x17c Length 130
131 Record Type 132 Memory @0x0A37C17C
133
134 135 136 137 138 139
140 Slot 4 141
142 id 143
144 Slot 145
146 itemno 147
148 Slot 149
150 itemname 151
152
153 1 itemname 3 Slot 6 Slot 8 id 10 Slot 12 itemno 14 Slot 16 itemname 18 Slot 23 id 27 itemno 29 Slot 31 itemname 33 Slot 34
35
36 Slot 37
38 id 39
40 Slot 42 itemno 43
44 Slot 45
46 itemname 50
54 id 58 itemno 62 itemname = 德国
五条记录都在同一个数据页面
参考文章：
http://www.cnblogs.com/zhijianliutang/archive/2012/10/28/2743722.html
如有不对的地方，欢迎大家拍砖o(∩_∩)o
----------------------------------------------------------------
2013-10-19 晚上补充
我们用Internal Viewer来查看TEST数据库
在Internal Viewer查看到TEST数据库是分区的，十分形象
也能够看到那3个pageid为8的数据页面
我们进入第4个文件的数据页面，即是：E:FG_TestUnique_Id_02_data.ndf里的数据页面
可以看到每个数据文件都会有GAM、SGAM、DCM、BCM、PFS页面
另外的两个数据页面我就不打开来看了
关于GAM、SGAM、DCM、BCM、PFS这些页面的作用可以参考下面文章：
SQL Server 2008 连载之存储结构之DCM、BCM
SQL Server 2008连载之存储结构之GAM、SGAM
SQL Server 2008连载之存储结构之PFS结构
关于索引对齐
大家可以先看一下我之前写的文章，看一下数据页面之间是怎麽关联的，先了解一下
SQLSERVER聚集索引与非聚集索引的再次研究（上）
SQLSERVER聚集索引与非聚集索引的再次研究（下）
http://msdn.microsoft.com/zh-cn/library/ms345146(v=sql.90).aspx
MSDN中的解释
索引分区
除了对表的数据集进行分区之外，还可以对索引进行分区。使用相同的函数对表及其索引进行分区通常可以优化性能。
当索引和表按照相同的顺序使用相同的分区函数和列时，表和索引将对齐。如果在已经分区的表中建立索引，
SQL Server 会自动将新索引与该表的分区架构对齐，除非该索引的分区明显不同。当表及其索引对齐后，
SQL Server 则可以更有效地将分区移入和移出分区表，因为所有相关的数据和索引都使用相同的算法进行划分。
如果定义表和索引时不仅使用了相同的分区函数，还使用了相同的分区架构，则这些表和索引将被认为是按存储位置对齐。
按存储位置对齐的一个优点是，相同边界内的所有数据都位于相同的物理磁盘上。在这种情况下，
可以单独在某个时间段内执行备份操作，还可以根据数据的变化在备份频率和备份类型方面改变您的策略。
如果连接或收集了相同文件或文件组中的表和索引，则可以发现更多的好处。SQL Server 可以通过在多个分区中并行操作来获益。
在按存储位置对齐和多 CPU 的情况下，每个处理器都可以直接处理特定的文件或文件组，而不会与数据访问产生任何冲突，
因为所有需要的数据都位于同一个磁盘上。这样，可以并行运行多个进程，而不会相互干扰。
?
建立索引：是否分区？
默认情况下，分区表中创建的索引也使用相同的分区架构和分区列。
如果属于这种情况，索引将与表对齐。尽管未作要求，但将表与其索引对齐可以使管理工作更容易进行，
对于滑动窗口方案尤其如此。
例如，要创建唯一的索引，分区列必须是一个关键列；这将确保对相应的分区进行验证，以保证索引的唯一性。
因此，如果需要在一列上对表进行分区，而必须在另一个列上创建唯一的索引，这些表和索引将无法对齐。
在这种情况下，可以在唯一的列（如果是多列的唯一键，则可以是任一关键列）中对索引进行分区，或者根本就不进行分区。
请注意，在分区表中移入和移出数据时，必须删除和创建此索引。
注意：如果您打算使用现有数据加载表并立即在其中添加索引，则通常可以通过以下方式获得更好的性能：
先加载到未分区、未建立索引的表中，然后在加载数据后创建分区索引。
通过为分区架构定义群集索引，可以在加载数据后更有效地为表分区。
这也是为现有表分区的不错方法。要创建与未分区表相同的表并创建与已分区群集索引相同的群集索引，
请用一个文件组目标位置替换创建表中的 ON 子句。然后，在加载数据之后为分区架构创建群集索引。
索引对齐，简单来讲，因为索引也可以创建在不同的文件组中，那里创建索引的时候也可以根据
分区架构和用来分区的列来创建索引，这样索引数据和表数据都放在同一个文件组中，叫索引对齐
?------------------------------------------------------------------------------
聚集索引表
聚集索引建立在分区列
我们drop掉testPartionTable表，重新建立testPartionTable表
5 itemname 4000 6 )'+REPLICATE(a3500) 3500GO
这个表有一个特点：就是一条记录占用一个数据页面
创建聚集索引，聚集索引字段创建在分区字段id上
创建聚集索引
CLUSTERED INDEX cix_id ON testPartionTable(id ASC)
我们看一下，创建聚集索引之后，表的页面的分配情况
3
DESC
上图中，红色框的列都需要注意的
分区号1/fileid3 (PartitionNumber1)-》文件组FG_TestUnique_Id_01-》E:FG_TestUnique_Id_01_data.ndf
分区号2/fileid4 (PartitionNumber2)-》文件组FG_TestUnique_Id_02-》E:FG_TestUnique_Id_02_data.ndf
分区号3/fileid5 (PartitionNumber3)-》文件组FG_TestUnique_Id_03-》E:FG_TestUnique_Id_03_data.ndf
从上图可以得出：
fileid3/分区号1：聚集索引页15，IAM页13，数据页12，数据页14
fileid4/分区号2：聚集索引页15，IAM页13，数据页12，数据页14
fileid5/分区号3：IAM页12，数据页11
?fileid ? ? ? ? ?PartitionID
fileid3：72057594038583296
fileid4：72057594038648832
fileid5：72057594038714368
PartitionID指的是：表的分区ID，如果一张表没有使用表分区技术，每张表本来默认会有一个分区
如果使用了表分区技术，那么，每个分区都会有一个分区ID（PartitionID）
页面12既是IAM页面也是数据页面
不同的是：一个页面12在fileid5文件里作为IAM页面，另一个页面12在fileid4文件里作为数据页面
可以看到fileid3文件和fileid4文件里的数据页面是首尾相连的，都标记了与那个文件的哪个页面进行相连
唯独fileid5文件里面没有聚集索引页面,可能因为只有一个数据页11,所以没有聚集索引页面
而且也看不到fileid5文件里数据页有首尾相连标记
?
-------------------------------------------------------------------
----------------------------------------------------------------------
页面指向
文件4里数据页面12-》文件4里数据页面14
文件3里数据页面12-》文件3里数据页面14
可以看到三个文件组之间或者三个ndf文件之间，数据页面与数据页面之间已经没有联系了，
只有大家都在同一个ndf文件里才能首尾相连，才有联系
我们看一下聚集索引页面
fileid为3的聚集索引页面
15,sans-serif; font-size:14px; line-height:25px">
fileid为4的聚集索引页面
每个文件中的聚集索引页面都各自为政，都只管他自己的文件里的数据页面，而别的文件里的数据页面他是不管的
我们看一下数据页面，这里我只显示有用的信息，数据页面的其他没用信息我都删掉了
12,128); line-height:1.5!important"> 1 PAGE: (12 4
5 UNIQUIFIER = NULL]
7 Slot 9 id 11 Slot 0x11 Length 7004
13 itemname 中国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
DBCC 输出了错误信息，请与系统管理员联系。
14,0); line-height:1.5!important">14 4
5
6 UNIQUIFIER 10 id 法国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4
美国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
3
4 UNIQUIFIER 12 itemname 英国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
11,0); line-height:1.5!important">11 2
3 UNIQUIFIER 7 id 德国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
从上面的结果得出：
fileid3：数据页12存放的 id值为1
fileid3：数据页14存放的 id值为2
fileid4：数据页12存放的 id值为3
fileid4：数据页14存放的 id值为4
fileid5：数据页11存放的 id值为5
fileid3/分区号1：id值为1和2
fileid4/分区号2：id值为3和4
fileid5/分区号3：id值为5
按照了分区函数来分
7 8 Fun_TestUnique_Id( 9 RANGE 4)
而且聚集索引页面保存的id值也是按照分区函数来分的
那么这时候可以说聚集索引和数据都按照分区函数来划分，是索引对齐的
-----------------------------------------------------------------------------------------------------
聚集索引建立在非分区列
3 id 4 itemno 66,0); line-height:1.5!important">日本25
创建聚集索引之前testPartionTable表页面分配情况
这次我们将聚集索引创建在非分区字段itemno上
ON testPartionTable(ASC
建立聚集索引之后，页面会重新分配
这个在SQLSERVER聚集索引与非聚集索引的再次研究（下）里已经讲过了
从上图可以得出:
fileid3/分区号1：聚集索引页23，IAM页21，数据页20，数据页22
fileid4/分区号2：聚集索引页23，IAM页21，数据页20，数据页22
fileid5/分区号3：聚集索引页23，IAM页21，数据页20，数据页22
这里跟刚才不一样的是：多了聚集索引页23
fileid5/分区号3：聚集索引页23，IAM页21，数据页20，数据页22
fileid5/分区号3（刚才）：IAM页12，数据页11
而且可以看到fileid5文件里数据页有首尾相连标记
刚才：
现在：
这里可以反映出：一个文件/文件组里的数据页多于一个才会出现聚集索引页面
我们看一下 聚集索引页面
23,sans-serif; font-size:14px; line-height:25px">
?
聚集索引页面告诉我们：虽然我们在itemno字段上建立聚集索引，但是SQLSERVER在聚集索引页面里
还是以id为聚集索引键来建立聚集索引，直白一点来说就是SQLSERVER会在id列上建立聚集索引，按照id字段来进行排序
无论你在非分区列的那个列上建立聚集索引，SQLSERVER都只会在分区列上建立聚集索引（可能有点绕口o(∩_∩)o ）
GO
我们看一下数据页面
2
0x18 Length 11 id 0x25 Length 15 itemname = 中国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
226
= 法国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2
= 英国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
= 美国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2
6
= 日本aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
= 德国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
注意：我这里为了节省篇幅，将数据页面的内容进行了删减
fileid5/分区号3：id值为5和6
那么就是说，对于聚集索引表来说，无论聚集索引建立在分区列还是非分区列，都会索引对齐
非聚集索引表
非聚集索引建立在分区列
我们drop掉数据库test，重新建立数据库test
DROP GO
SQL脚本都跟刚才一样的
39 40 41 42 43 44 46 47 Fun_TestUnique_Id(48 RANGE 49 50
52
54 55 56 Sch_TestUnique_Id 57 58 60 DROP TABLE testPartionTable
62 63 64 id 65 itemno 66 itemname 67 )68
69
71
73 74 75 76 77 78 79 81 82 85 86
DESC
在id字段上建立非聚集索引
创建非聚集索引
CREATE
我们比较一下创建非聚集索引之前和之后的图片
-----------------------------------------------------------------------------------------------------------------------
放大左下角的图片
可以看到只是在每个文件fileid3、fileid4、fileid5里新建了非聚集索引页面12和IAM页面13，其他什么都没有改变
fileid3/分区号1：非聚集索引页12，IAM页13，IAM页9，数据页8，数据页10
fileid4/分区号2：非聚集索引页12，IAM页13，IAM页9，数据页8，数据页10
fileid5/分区号3：非聚集索引页12，IAM页13，IAM页9，数据页8，数据页10
?----------------------------------------------------------------------------
我们看一下非聚集索引页面
每个文件中的非聚集索引页面都各自为政，都只管他自己的文件里的数据页面，而别的文件里的数据页面他是不管的
这里HEAP RID（key）只会指向本文件里的数据页面，不会指向其他文件的数据页面，因为如果指向其他文件的数据页面的话
那么就不用每个文件都有一个非聚集索引页面12了
我们看一下数据页面，这里我只显示有用的信息，数据页面的其他没用信息我都删掉了
GO
)
5 id 9 itemno 0x23 Length
10,0); line-height:1.5!important">10 )
)
)
非聚集索引和数据都按照分区函数来划分，是索引对齐的
非聚集索引建立在非分区列
我们drop掉testPartionTable表，重新建立testPartionTable表
创建非聚集索引之前testPartionTable表页面分配情况
这次我们将非聚集索引创建在非分区字段itemno上
INDEX ix_id
这里跟聚集索引不同，原来的数据页面不会重新分配
我们看一下非聚集索引页面
非聚集索引页面告诉我们：虽然我们在itemno字段上建立非聚集索引，但是SQLSERVER在非聚集索引页面里,
还是以id为非聚集索引键来建立非聚集索引，直白一点来说就是SQLSERVER会在id列上建立非聚集索引，
按照id字段来进行排序，这个情况跟刚才聚集索引建立在非分区列上是一样的，这里不多说了
我们看一下数据页面
)
对于非聚集索引表来说，无论非聚集索引建立在分区列还是非分区列，都会索引对齐
补两张图
表分区下的聚集索引
?
可以看到每个分区都有两个数据页和一个聚集索引页，而主文件组/fileid1里是没有任何表数据的
表分区下的非聚集索引
可以看到每个分区都有两个数据页和一个非聚集索引页，而主文件组/fileid1里是没有任何表数据的
这里不知道是不是Intelnals Viewer的BUG，就算是非聚集索引都会显示为聚集索引
GPOSDB TABLE NONCLUSTEREDtalbe(id int,NAME ))
INTO NONCLUSTEREDtalbe
nin'
NONCLUSTERED INDEX ix_NONCLUSTEREDtalbe ON NONCLUSTEREDtalbe(id ASC)
总结
缺点：这里不但数据分布在多个文件里，连聚集索引页面和非聚集索引页面都分布在多个文件里
如果是聚集索引/非聚集索引查找也需要到多个文件里去查找
因为在表分区了之后多个文件组之间/多个ndf文件之间，数据页面与数据页面之间已经没有联系了
必须到每个ndf文件里的聚集索引页面/非聚集索引页面去查找，直到找到所需的数据为止
所以，在使用表分区的时候一定要做好分区字段的选择，避免select * from 表 不加where 分区字段=
造成的扫描所有分区
无论是索引页还是数据页，将一个页面在每个分区里都保存一份这就是分区，表分区没有什么神秘的o(∩_∩)o?
看一下插入和查询的执行计划
转载原地址：?http://www.cnblogs.com/lyhabc/p/3350121.html
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
