再说一下表分区

再说一下表分区

网上表分区的文章成千上万，但是分区之后表数据的分布和流向都没有说

首先要说明的是表分区的分区不是指页面存储概念的分区，我用下面的图来表示

他们是没有关系的

---

正式开始

SQL脚本如下：

USE master
GO

--创建数据库
CREATE DATABASE [Test]
GO

USE [Test]
GO


--1.创建文件组
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_01]

ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_02]

ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_03]

--2.创建文件
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_01];

ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_02];

ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_03_data',FILENAME = N'E:\FG_TestUnique_Id_03_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_03];

创建分区函数和分区方案
我们创建了一个用于数据类型为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的分区函数，按照数值来划分
--文件组                  分区      取值范围
--[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
Fun_TestUnique_Id(INT) AS
RANGE LEFT
FOR VALUES(2,4)




--4.创建分区方案
CREATE PARTITION SCHEME
Sch_TestUnique_Id AS
PARTITION Fun_TestUnique_Id
TO([FG_TestUnique_Id_01],[FG_TestUnique_Id_02],[FG_TestUnique_Id_03])

建立分区表

--5.创建分区表
CREATE TABLE testPartionTable
(
  id INT  NOT NULL,
  itemno CHAR(20),
  itemname CHAR(40)
)ON Sch_TestUnique_Id([id])


INSERT INTO [dbo].[testPartionTable] ( [id], [itemno], [itemname] )
SELECT 1,'1','中国' UNION ALL
SELECT 2,'2','法国' UNION ALL
SELECT 3,'3','美国' UNION ALL
SELECT 4,'4','英国' UNION ALL
SELECT 5,'5','德国'

查看边界值点

--查看边界值点
select * from sys.partition_range_values
GO

 

查看表数据

SELECT * FROM [dbo].[testNonPartionTable]
GO

 

我们看一下当前数据库的情况

EXEC [sys].[sp_helpdb] @dbname = test -- sysname
GO

FG_TestUnique_Id_0X这三个文件组建立在三个ndf文件上,三个ndf文件都位于E盘

而fileid分别是3、4、5

我们看一下表的页面分配情况

CREATE TABLE DBCCResult (
PageFID NVARCHAR(200),
PagePID NVARCHAR(200),
IAMFID NVARCHAR(200),
IAMPID NVARCHAR(200),
ObjectID NVARCHAR(200),
IndexID NVARCHAR(200),
PartitionNumber NVARCHAR(200),
PartitionID NVARCHAR(200),
iam_chain_type NVARCHAR(200),
PageType NVARCHAR(200),
IndexLevel NVARCHAR(200),
NextPageFID NVARCHAR(200),
NextPagePID NVARCHAR(200),
PrevPageFID NVARCHAR(200),
PrevPagePID NVARCHAR(200)
)

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 

 

SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID('test'),
                                       OBJECT_ID('testPartionTable'), NULL,
                                       NULL, '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 OBJECT_ID('testPartionTable') AS 'OBJECTID'

 

先看FILEID为3的文件里面的数据页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,8,3)
GO

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (3:8)


BUFFER:


BUF @0x03DFDE90

bpage = 0x16EEE000                   bhash = 0x00000000                   bpageno = (3:8)
bdbid = 11                           breferences = 0                      bUse1 = 28337
bstat = 0x3c0000b                    blog = 0x212121bb                    bnext = 0x00000000

PAGE HEADER:


Page @0x16EEE000

m_pageId = (3:8)                     m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 82     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043301888                                 
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 68                         m_slotCnt = 2                        m_freeCnt = 7950
m_freeData = 238                     m_reservedCnt = 0                    m_lsn = (41:289:25)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (3:2) = ALLOCATED                SGAM (3:3) = ALLOCATED               
PFS (3:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (3:6) = CHANGED
ML (3:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A70C060

00000000:   10004400 01000000 31202020 20202020 †..D.....1                
00000010:   20202020 20202020 20202020 d6d0b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 1                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 中国                                                           

Slot 1 Offset 0xa7 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A70C0A7

00000000:   10004400 02000000 32202020 20202020 †..D.....2                
00000010:   20202020 20202020 20202020 b7a8b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 1 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 1 Column 1 Offset 0x8 Length 20

itemno = 2                           

Slot 1 Column 2 Offset 0x1c Length 40

itemname = 法国                                                           


DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

这个页面是属于testPartionTable表

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 1                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 中国                                                           

Slot 1 Offset 0xa7 Length 71

              

Slot 1 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 1 Column 1 Offset 0x8 Length 20

itemno = 2                           

Slot 1 Column 2 Offset 0x1c Length 40

itemname = 法国         

FILEID为3的文件里面的数据页面里存放了id为1和id为2的这两条记录

 

看FILEID为4的文件里面的数据页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,8,3)
GO

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (4:8)


BUFFER:


BUF @0x03E6777C

bpage = 0x19A78000                   bhash = 0x00000000                   bpageno = (4:8)
bdbid = 11                           breferences = 0                      bUse1 = 28520
bstat = 0x3c0000b                    blog = 0x212121bb                    bnext = 0x00000000

PAGE HEADER:


Page @0x19A78000

m_pageId = (4:8)                     m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 83     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043367424                                 
Metadata: PartitionId = 72057594038386688                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 68                         m_slotCnt = 2                        m_freeCnt = 7950
m_freeData = 238                     m_reservedCnt = 0                    m_lsn = (41:289:49)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (4:2) = ALLOCATED                SGAM (4:3) = ALLOCATED               
PFS (4:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (4:6) = CHANGED
ML (4:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C060

00000000:   10004400 03000000 33202020 20202020 †..D.....3                
00000010:   20202020 20202020 20202020 c3c0b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 0 Column 0 Offset 0x4 Length 4

id = 3                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 3                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 美国                                                           

Slot 1 Offset 0xa7 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C0A7

00000000:   10004400 04000000 34202020 20202020 †..D.....4                
00000010:   20202020 20202020 20202020 d3a2b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 1 Column 0 Offset 0x4 Length 4

id = 4                               

Slot 1 Column 1 Offset 0x8 Length 20

itemno = 4                           

Slot 1 Column 2 Offset 0x1c Length 40

itemname = 英国                                                           


DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 这个页面是属于testPartionTable表

Slot 0 Offset 0x60 Length 71


Slot 0 Column 0 Offset 0x4 Length 4

id = 3                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 3                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 美国                                                           

Slot 1 Offset 0xa7 Length 71


Slot 1 Column 0 Offset 0x4 Length 4

id = 4                               

Slot 1 Column 1 Offset 0x8 Length 20

itemno = 4                           

Slot 1 Column 2 Offset 0x1c Length 40

itemname = 英国         

FILEID为4的文件里面的数据页面里存放了id为3和id为4的这两条记录

 

看FILEID为5的文件里面的数据页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,8,3)
GO

 这个页面是属于testPartionTable表

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (5:8)


BUFFER:


BUF @0x03E7B0FC

bpage = 0x1A2A8000                   bhash = 0x00000000                   bpageno = (5:8)
bdbid = 11                           breferences = 0                      bUse1 = 28674
bstat = 0x3c0000b                    blog = 0x212121bb                    bnext = 0x00000000

PAGE HEADER:


Page @0x1A2A8000

m_pageId = (5:8)                     m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 84     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043432960                                 
Metadata: PartitionId = 72057594038452224                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 68                         m_slotCnt = 1                        m_freeCnt = 8023
m_freeData = 167                     m_reservedCnt = 0                    m_lsn = (41:326:23)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (5:2) = ALLOCATED                SGAM (5:3) = ALLOCATED               
PFS (5:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (5:6) = CHANGED
ML (5:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C060

00000000:   10004400 05000000 35202020 20202020 †..D.....5                
00000010:   20202020 20202020 20202020 b5c2b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 0 Column 0 Offset 0x4 Length 4

id = 5                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 5                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 德国                                                           


DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

Slot 0 Offset 0x60 Length 71


Slot 0 Column 0 Offset 0x4 Length 4

id = 5                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 5                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 德国

FILEID为5的文件里面的数据页面里存放了id为5这条记录

 

再看我们刚才建立的分区函数，和各个ndf里的数据页面存储的记录条数

创建分区函数
我们创建了一个用于数据类型为int的分区函数，按照数值来划分
文件组                  分区      取值范围
[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
Fun_TestUnique_Id(INT) AS
RANGE LEFT
FOR VALUES(2,4)          

 

当执行select * from testPartionTable的时候，就需要跨这三个ndf文件来读取记录

IO一定会有所影响，所以一般应用都是按照月份、性别等来进行分区，确保查询数据的时候不要跨多个文件组

---

如果表没有分区是怎样的？
SQL脚本如下，建立testNonPartionTable表：

USE [Test]
GO
CREATE TABLE testNonPartionTable
(
  id INT  NOT NULL,
  itemno CHAR(20),
  itemname CHAR(40)
)


INSERT INTO [dbo].[testNonPartionTable] ( [id], [itemno], [itemname] )
SELECT 1,'1','中国' UNION ALL
SELECT 2,'2','法国' UNION ALL
SELECT 3,'3','美国' UNION ALL
SELECT 4,'4','英国' UNION ALL
SELECT 5,'5','德国'

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testNonPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,1,47,3)
GO

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:47)


BUFFER:


BUF @0x03E83C38

bpage = 0x1763C000                   bhash = 0x00000000                   bpageno = (1:47)
bdbid = 11                           breferences = 0                      bUse1 = 29165
bstat = 0x3c0000b                    blog = 0xca2159bb                    bnext = 0x00000000

PAGE HEADER:


Page @0x1763C000

m_pageId = (1:47)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 86     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043564032                                 
Metadata: PartitionId = 72057594038583296                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 68                         m_slotCnt = 5                        m_freeCnt = 7731
m_freeData = 451                     m_reservedCnt = 0                    m_lsn = (41:355:23)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C060

00000000:   10004400 01000000 31202020 20202020 †..D.....1                
00000010:   20202020 20202020 20202020 d6d0b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 1                           

Slot 0 Column 2 Offset 0x1c Length 40

itemname = 中国                                                           

Slot 1 Offset 0xa7 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C0A7

00000000:   10004400 02000000 32202020 20202020 †..D.....2                
00000010:   20202020 20202020 20202020 b7a8b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 1 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 1 Column 1 Offset 0x8 Length 20

itemno = 2                           

Slot 1 Column 2 Offset 0x1c Length 40

itemname = 法国                                                           

Slot 2 Offset 0xee Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C0EE

00000000:   10004400 03000000 33202020 20202020 †..D.....3                
00000010:   20202020 20202020 20202020 c3c0b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 2 Column 0 Offset 0x4 Length 4

id = 3                               

Slot 2 Column 1 Offset 0x8 Length 20

itemno = 3                           

Slot 2 Column 2 Offset 0x1c Length 40

itemname = 美国                                                           

Slot 3 Offset 0x135 Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C135

00000000:   10004400 04000000 34202020 20202020 †..D.....4                
00000010:   20202020 20202020 20202020 d3a2b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 3 Column 0 Offset 0x4 Length 4

id = 4                               

Slot 3 Column 1 Offset 0x8 Length 20

itemno = 4                           

Slot 3 Column 2 Offset 0x1c Length 40

itemname = 英国                                                           

Slot 4 Offset 0x17c Length 71

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A37C17C

00000000:   10004400 05000000 35202020 20202020 †..D.....5                
00000010:   20202020 20202020 20202020 b5c2b9fa †            ....         
00000020:   20202020 20202020 20202020 20202020 †                         
00000030:   20202020 20202020 20202020 20202020 †                         
00000040:   20202020 0300f8††††††††††††††††††††††    ...                  

Slot 4 Column 0 Offset 0x4 Length 4

id = 5                               

Slot 4 Column 1 Offset 0x8 Length 20

itemno = 5                           

Slot 4 Column 2 Offset 0x1c Length 40

itemname = 德国                                                           


DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

itemname = 中国                                                           

Slot 1 Offset 0xa7 Length 71


Slot 1 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 1 Column 1 Offset 0x8 Length 20

itemno = 2                           

Slot 1 Column 2 Offset 0x1c Length 40

itemname = 法国                                                           

Slot 2 Offset 0xee Length 71


Slot 2 Column 0 Offset 0x4 Length 4

id = 3                               

Slot 2 Column 1 Offset 0x8 Length 20

itemno = 3                           

Slot 2 Column 2 Offset 0x1c Length 40

itemname = 美国                                                           

Slot 3 Offset 0x135 Length 71


Slot 3 Column 0 Offset 0x4 Length 4

id = 4                               

Slot 3 Column 1 Offset 0x8 Length 20

itemno = 4                           

Slot 3 Column 2 Offset 0x1c Length 40

itemname = 英国                                                           

Slot 4 Offset 0x17c Length 71

             

Slot 4 Column 0 Offset 0x4 Length 4

id = 5                               

Slot 4 Column 1 Offset 0x8 Length 20

itemno = 5                           

Slot 4 Column 2 Offset 0x1c Length 40

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.创建分区表
CREATE TABLE testPartionTable
(
  id INT  NOT NULL,
  itemname NVARCHAR(4000)
)ON Sch_TestUnique_Id([id])


INSERT INTO [dbo].[testPartionTable] ( [id], [itemname] )
SELECT 1,'中国'+REPLICATE('a',3500) UNION ALL
SELECT 2,'法国'+REPLICATE('a',3500) UNION ALL
SELECT 3,'美国'+REPLICATE('a',3500) UNION ALL
SELECT 4,'英国'+REPLICATE('a',3500) UNION ALL
SELECT 5,'德国'+REPLICATE('a',3500)

SELECT * FROM [dbo].[testPartionTable]
GO

--查看边界值点
select * from sys.partition_range_values
GO

 

这个表有一个特点：就是一条记录占用一个数据页面

创建聚集索引，聚集索引字段创建在分区字段id上

--创建聚集索引
CREATE CLUSTERED INDEX cix_id ON testPartionTable(id ASC)

我们看一下，创建聚集索引之后，表的页面的分配情况

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] 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的聚集索引页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,15,3)
GO

 

fileid为4的聚集索引页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,15,3)
GO

 

每个文件中的聚集索引页面都各自为政，都只管他自己的文件里的数据页面，而别的文件里的数据页面他是不管的

 

我们看一下数据页面，这里我只显示有用的信息，数据页面的其他没用信息我都删掉了

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,12,3)
GO

PAGE: (3:12)


        
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 1                               

Slot 0 Column 2 Offset 0x11 Length 7004

itemname = 中国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,14,3)
GO

PAGE: (3:14)


      
            
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 2                               

Slot 0 Column 2 Offset 0x11 Length 7004

itemname = 法国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,12,3)
GO

PAGE: (4:12)


         
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 3                               

Slot 0 Column 2 Offset 0x11 Length 7004

itemname = 美国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,14,3)
GO

PAGE: (4:14)

         
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 4                               

Slot 0 Column 2 Offset 0x11 Length 7004

itemname = 英国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,11,3)
GO

PAGE: (5:11)
    
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 5                               

Slot 0 Column 2 Offset 0x11 Length 7004

itemname = 德国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

从上面的结果得出：

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

按照了分区函数来分

--我们创建了一个用于数据类型为int的分区函数，按照数值来划分
--文件组                  分区      取值范围
--[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
Fun_TestUnique_Id(INT) AS
RANGE LEFT
FOR VALUES(2,4)

而且聚集索引页面保存的id值也是按照分区函数来分的

fileid3/分区号1：id值为1和2

fileid4/分区号2：id值为3和4

那么这时候可以说聚集索引和数据都按照分区函数来划分，是索引对齐的

 

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

聚集索引建立在非分区列

我们drop掉testPartionTable表，重新建立testPartionTable表

CREATE TABLE testPartionTable
(
  id INT  NOT NULL,
  itemno CHAR(20),
  itemname NVARCHAR(4000)
)ON Sch_TestUnique_Id([id])





INSERT INTO [dbo].[testPartionTable] ( [id],[itemno], [itemname] )
SELECT 1,'5','中国'+REPLICATE('a',3500) UNION ALL
SELECT 2,'6','法国'+REPLICATE('a',3500) UNION ALL
SELECT 3,'3','美国'+REPLICATE('a',3500) UNION ALL
SELECT 4,'1','英国'+REPLICATE('a',3500) UNION ALL
SELECT 5,'4','德国'+REPLICATE('a',3500) UNION ALL
SELECT 6,'2','日本'+REPLICATE('a',3500)

SELECT * FROM [dbo].[testPartionTable]
GO

--查看边界值点
select * from sys.partition_range_values
GO

创建聚集索引之前testPartionTable表页面分配情况

这次我们将聚集索引创建在非分区字段itemno上

--创建聚集索引
CREATE CLUSTERED INDEX cix_id ON testPartionTable([itemno] ASC)
GO

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

建立聚集索引之后，页面会重新分配

这个在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文件里数据页有首尾相连标记

刚才：

现在：

 

这里可以反映出：一个文件/文件组里的数据页多于一个才会出现聚集索引页面

 

我们看一下 聚集索引页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,23,3)
GO

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,23,3)
GO

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,23,3)
GO

 

聚集索引页面告诉我们：虽然我们在itemno字段上建立聚集索引，但是SQLSERVER在聚集索引页面里

还是以id为聚集索引键来建立聚集索引，直白一点来说就是SQLSERVER会在id列上建立聚集索引，按照id字段来进行排序

无论你在非分区列的那个列上建立聚集索引，SQLSERVER都只会在分区列上建立聚集索引（可能有点绕口o(∩_∩)o ）

INSERT INTO [dbo].[testPartionTable] ( [id],[itemno], [itemname] )
SELECT 1,'5','中国'+REPLICATE('a',3500) UNION ALL
SELECT 2,'6','法国'+REPLICATE('a',3500) UNION ALL
SELECT 3,'3','美国'+REPLICATE('a',3500) UNION ALL
SELECT 4,'1','英国'+REPLICATE('a',3500) UNION ALL
SELECT 5,'4','德国'+REPLICATE('a',3500) UNION ALL
SELECT 6,'2','日本'+REPLICATE('a',3500)

--创建聚集索引
CREATE CLUSTERED INDEX cix_id ON testPartionTable([itemno] ASC)
GO

 

我们看一下数据页面

PAGE: (3:20)
                     
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 20

itemno = 5                           

Slot 0 Column 2 Offset 0x18 Length 4

id = 1                               

Slot 0 Column 3 Offset 0x25 Length 7004

itemname = 中国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (3:22)
                     
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 20

itemno = 6                           

Slot 0 Column 2 Offset 0x18 Length 4

id = 2                               

Slot 0 Column 3 Offset 0x25 Length 7004

itemname = 法国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (4:20)
                      
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 20

itemno = 1                           

Slot 0 Column 2 Offset 0x18 Length 4

id = 4                               

Slot 0 Column 3 Offset 0x25 Length 7004

itemname = 英国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (4:22)
                     
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 20

itemno = 3                           

Slot 0 Column 2 Offset 0x18 Length 4

id = 3                               

Slot 0 Column 3 Offset 0x25 Length 7004

itemname = 美国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (5:20)
                       
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 20

itemno = 2                           

Slot 0 Column 2 Offset 0x18 Length 4

id = 6                               

Slot 0 Column 3 Offset 0x25 Length 7004

itemname = 日本aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (5:22)
                       
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 20

itemno = 4                           

Slot 0 Column 2 Offset 0x18 Length 4

id = 5                               

Slot 0 Column 3 Offset 0x25 Length 7004

itemname = 德国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

注意：我这里为了节省篇幅，将数据页面的内容进行了删减

 

fileid3/分区号1：id值为1和2

fileid4/分区号2：id值为3和4

fileid5/分区号3：id值为5和6

 

那么就是说，对于聚集索引表来说，无论聚集索引建立在分区列还是非分区列，都会索引对齐

---

非聚集索引表

非聚集索引建立在分区列

我们drop掉数据库test，重新建立数据库test

USE master
GO
DROP DATABASE [Test]
GO

SQL脚本都跟刚才一样的

USE master
GO

--创建数据库
CREATE DATABASE [Test]
GO

USE [Test]
GO


--1.创建文件组
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_01]

ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_02]

ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_03]

--2.创建文件
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_01];

ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_02];

ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_03_data',FILENAME = N'E:\FG_TestUnique_Id_03_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_03];


--3.创建分区函数
--我们创建了一个用于数据类型为int的分区函数，按照数值来划分
--文件组                  分区      取值范围
--[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
Fun_TestUnique_Id(INT) AS
RANGE LEFT
FOR VALUES(2,4)




--4.创建分区方案
CREATE PARTITION SCHEME
Sch_TestUnique_Id AS
PARTITION Fun_TestUnique_Id
TO([FG_TestUnique_Id_01],[FG_TestUnique_Id_02],[FG_TestUnique_Id_03])

--5.创建分区表
--DROP TABLE testPartionTable
CREATE TABLE testPartionTable
(
  id INT  NOT NULL,
  itemno CHAR(20),
  itemname NVARCHAR(4000)
)ON Sch_TestUnique_Id([id])





INSERT INTO [dbo].[testPartionTable] ( [id],[itemno], [itemname] )
SELECT 1,'5','中国'+REPLICATE('a',3500) UNION ALL
SELECT 2,'6','法国'+REPLICATE('a',3500) UNION ALL
SELECT 3,'3','美国'+REPLICATE('a',3500) UNION ALL
SELECT 4,'1','英国'+REPLICATE('a',3500) UNION ALL
SELECT 5,'4','德国'+REPLICATE('a',3500) UNION ALL
SELECT 6,'2','日本'+REPLICATE('a',3500)

SELECT * FROM [dbo].[testPartionTable]
GO

--查看边界值点
select * from sys.partition_range_values
GO

CREATE TABLE DBCCResult (
PageFID NVARCHAR(200),
PagePID NVARCHAR(200),
IAMFID NVARCHAR(200),
IAMPID NVARCHAR(200),
ObjectID NVARCHAR(200),
IndexID NVARCHAR(200),
PartitionNumber NVARCHAR(200),
PartitionID NVARCHAR(200),
iam_chain_type NVARCHAR(200),
PageType NVARCHAR(200),
IndexLevel NVARCHAR(200),
NextPageFID NVARCHAR(200),
NextPagePID NVARCHAR(200),
PrevPageFID NVARCHAR(200),
PrevPagePID NVARCHAR(200)
)

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

在id字段上建立非聚集索引

--创建非聚集索引
CREATE  INDEX cix_id ON testPartionTable([id] ASC)
GO

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

 

我们比较一下创建非聚集索引之前和之后的图片

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

放大左下角的图片

可以看到只是在每个文件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

 

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

我们看一下非聚集索引页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,12,3)
GO

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,12,3)
GO

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,12,3)
GO

每个文件中的非聚集索引页面都各自为政，都只管他自己的文件里的数据页面，而别的文件里的数据页面他是不管的

这里HEAP RID（key）只会指向本文件里的数据页面，不会指向其他文件的数据页面，因为如果指向其他文件的数据页面的话

那么就不用每个文件都有一个非聚集索引页面12了

 

我们看一下数据页面，这里我只显示有用的信息，数据页面的其他没用信息我都删掉了

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,8,3)
GO

PAGE: (3:8)       

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 5                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 中国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,10,3)
GO

PAGE: (3:10)         

Slot 0 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 6                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 法国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,8,3)
GO

PAGE: (4:8)         

Slot 0 Column 0 Offset 0x4 Length 4

id = 3                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 3                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 美国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,10,3)
GO

PAGE: (4:10)      

Slot 0 Column 0 Offset 0x4 Length 4

id = 4                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 1                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 英国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,8,3)
GO

PAGE: (5:8)        

Slot 0 Column 0 Offset 0x4 Length 4

id = 5                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 4                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 德国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,10,3)
GO

PAGE: (5:10)        

Slot 0 Column 0 Offset 0x4 Length 4

id = 6                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 2                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 日本aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

fileid3/分区号1：id值为1和2

fileid4/分区号2：id值为3和4

fileid5/分区号3：id值为5和6

 

非聚集索引和数据都按照分区函数来划分，是索引对齐的

---

非聚集索引建立在非分区列

我们drop掉testPartionTable表，重新建立testPartionTable表

CREATE TABLE testPartionTable
(
  id INT  NOT NULL,
  itemno CHAR(20),
  itemname NVARCHAR(4000)
)ON Sch_TestUnique_Id([id])





INSERT INTO [dbo].[testPartionTable] ( [id],[itemno], [itemname] )
SELECT 1,'5','中国'+REPLICATE('a',3500) UNION ALL
SELECT 2,'6','法国'+REPLICATE('a',3500) UNION ALL
SELECT 3,'3','美国'+REPLICATE('a',3500) UNION ALL
SELECT 4,'1','英国'+REPLICATE('a',3500) UNION ALL
SELECT 5,'4','德国'+REPLICATE('a',3500) UNION ALL
SELECT 6,'2','日本'+REPLICATE('a',3500)

SELECT * FROM [dbo].[testPartionTable]
GO

--查看边界值点
select * from sys.partition_range_values
GO

创建非聚集索引之前testPartionTable表页面分配情况

这次我们将非聚集索引创建在非分区字段itemno上

--创建非聚集索引
CREATE  INDEX ix_id ON testPartionTable([itemno] ASC)
GO

--TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(test,testPartionTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

这里跟聚集索引不同，原来的数据页面不会重新分配

从上图可以得出:

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

 

我们看一下非聚集索引页面

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,3,12,3)
GO

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,4,12,3)
GO

DBCC TRACEON(3604,-1)
GO
DBCC PAGE(test,5,12,3)
GO

 

 

非聚集索引页面告诉我们：非聚集索引里面会包含分区依据列，但是索引键还是不变还是itemno

 

我们看一下数据页面

PAGE: (3:8)         

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 5                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 中国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (3:10)        

Slot 0 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 6                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 法国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (4:8)          

Slot 0 Column 0 Offset 0x4 Length 4

id = 3                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 3                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 美国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (4:10)       

Slot 0 Column 0 Offset 0x4 Length 4

id = 4                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 1                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 英国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (5:8)          

Slot 0 Column 0 Offset 0x4 Length 4

id = 5                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 4                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 德国aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PAGE: (5:10)        

Slot 0 Column 0 Offset 0x4 Length 4

id = 6                               

Slot 0 Column 1 Offset 0x8 Length 20

itemno = 2                           

Slot 0 Column 2 Offset 0x23 Length 7004

itemname = 日本aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

 

fileid3/分区号1：id值为1和2

fileid4/分区号2：id值为3和4

fileid5/分区号3：id值为5和6

对于非聚集索引表来说，无论非聚集索引建立在分区列还是非分区列，都会索引对齐

---

补两张图

表分区下的聚集索引

 

可以看到每个分区都有两个数据页和一个聚集索引页，而主文件组/fileid1里是没有任何表数据的

 

表分区下的非聚集索引

可以看到每个分区都有两个数据页和一个非聚集索引页，而主文件组/fileid1里是没有任何表数据的

这里不知道是不是Intelnals Viewer的BUG，就算是非聚集索引都会显示为聚集索引

USE [GPOSDB]
GO
CREATE TABLE NONCLUSTEREDtalbe(id int,NAME CHAR(20))
INSERT INTO NONCLUSTEREDtalbe
SELECT 1,'nin' UNION ALL
SELECT 2,'nin' UNION ALL
SELECT 3,'nin' UNION ALL
SELECT 4,'nin' 

CREATE NONCLUSTERED INDEX ix_NONCLUSTEREDtalbe ON NONCLUSTEREDtalbe(id ASC)

---

总结

 

缺点：这里不但数据分布在多个文件里，连聚集索引页面和非聚集索引页面都分布在多个文件里

如果是聚集索引/非聚集索引查找也需要到多个文件里去查找

因为在表分区了之后多个文件组之间/多个ndf文件之间，数据页面与数据页面之间已经没有联系了

必须到每个ndf文件里的聚集索引页面/非聚集索引页面去查找，直到找到所需的数据为止

 

所以，在使用表分区的时候一定要做好分区字段的选择，避免select * from 表 不加where 分区字段=

造成的扫描所有分区

 

无论是索引页还是数据页，将一个页面在每个分区里都保存一份这就是分区，表分区没有什么神秘的o(∩_∩)o 

看一下插入和查询的执行计划