分区的表和索引
11.5 第4部分：分区的表和索引
http://book.csdn.net/bookfiles/1051/100105131404.shtml
现在将开始深入研究一些令人敬畏的新特性的细节，以应当会令人非常激动的特性作为开始：新的分区的表和索引。首先将介绍为什么需要使用这个新特性，以及应当如何使用它。还会发现更多关于分区表和索引是什么，以及更多关于如何使用它们的信息。
11.5.1 使用分区的表和索引的理由
将表进行分区是一种将一个表分布到多个分区上的方式，而且这样做时，每个分区可以位于独立的文件组中。这样做有多个理由，如下面所介绍的。
1. 数据加载速度更快且更加容易
如果数据库有着大量的数据需要加载，则可能会希望考虑使用分区表。大量的数据并不是指特定数量的数据，而是指加载操作所花费的时间超出生产周期的可接受程度。
分区表允许将数据加载到一个不被活跃数据使用的空表中，因此对并发活跃操作的影响较小。显然这将会对I/O子系统产生影响，但是如果在不同的物理磁盘上有着独立的文件组，则即使这样，对总体系统性能的影响也会很小。
一旦数据加载到新的表，可以执行一个转换以将新的表加入到活跃数据。这个转换是一个简单的元数据改动，执行起来非常快。因此分区表是加载大量数据的好的方式，而且对接触表中其他数据的用户影响有限。
2. 数据删除或存档的速度更快且更加容易
基于相同的原因，分区表还有助于删除或存档数据。如果数据在增加或删除数据的自然边界上进行分区，则数据被认为是对齐的。当数据对齐之后，删除或存档数据就像从当前分区中将一个表切换出去并在有空时将它卸载或存档那样简单。
在存档部分有一个条件，经常会希望将旧的数据移动到更便宜的或不同的存储器上。转换操作的速度非常快是因为所做的就是修改元数据，并没有移动任何数据。为了将数据从它所处的文件组移动到更便宜的存储器上的档案文件组，不能够仅仅做一些转换，必须实际移动数据。然而，这个移动是发生在分区不附加在现有分区表的时候，因此尽管这个移动会花费相当长的时间，但仍然不会对执行在活跃数据上的任何查询产生大的影响。
3. 更快的查询
能够得到更快的查询的机会让人非常感兴趣。Query Optimizer在查询一个分区表时，能够排除掉它已经知道不会持有任何结果的分区的查询。这被称作分区排除。这只有在分区的表或索引根据查询进行对齐时才会有效。这里的对齐，是指数据在分区中的分布方式必须同查询中的搜索子句匹配。当了解如何创建一个表分区的细节时，会看到关于这一点的更多细节。
4. 滑动窗口
滑动窗口基本上同前面描述的数据删除/存档的场景相同，即加入新的数据，然后删除或存档旧的数据。它有几分类似于将新数据窗口滑动到当前分区表，然后将旧数据窗口滑动出分区表。
11.5.2 分区的先决条件
在因分区的表而感到激动之前，应当记住分区只能用在SQL Server 2005 Enterprise版本中。同时还对使用的一些硬件提出了期望，尤其是存储系统，当然这些只是隐式的期望，还是可以将数据存放在任何地方。只不过不能够得到像使用(专用于不同分区的多个磁盘组)的大型企业级存储系统那样的性能好处。
11.5.3 创建分区的表
在第一次决定创建分区的表时，可能会被分区函数、左边范围对右边范围、分区模式、如何实际创建可用分区等文档搞得非常迷惑。下面的部分将自始至终地介绍这个过程。如果已经阅读了本章的全部内容，并且希望实现下面的例子，应当通过运行createtables.sql和loadnames.sql来删除并重建表。下面在people表中加载200 000行：
EXEC usp_loopPeopleInsert 100000, 10000
-- inserts 100,000 couples and prints progress every 10,000 rows.
GO
EXEC sp_spacedused people
GO
name rows reserved data index_size unused
---------------------------------------------------------------------
people 199998 28936 KB 28880 KB 8 KB 48 KB
1. 从现有表创建分区的表
下面将从一个现有的表出发，将它转换成分区的表，但是仍将所有分区保存在相同的文件组中，以避免对数据进行物理移动。
分区函数决定数据如何在分区之间进行拆分。第一步为创建分区函数。下面的分区函数代码根据DOB域将People表拆分到多个分区中：
-- Range partition function for the People table,
-- every 10 years from 2006 - 110 = 1896,
-- start the first partition at everything before 1 Jan 1900
-- 1890-1900, 1900-1910, 1910 - 1920, 1920-1930, 1930-1940, 1940-1950,
-- 1950-1960,1960-1970, 1970-1980, 1980-1990, 1990-2000, 2000 onwards
CREATE PARTITION FUNCTION [peopleRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('01/01/1900', '01/01/1910', '01/01/1920', '
01/01/1930', '01/01/1940', '01/01/1950', '01/01/1960', '01/01/1970', '
01/01/1980', '01/01/1990', '01/01/2000', '01/01/2005', '01/01/2006'
);
GO
这个例子是基于本章11.1节中的示例数据库、表和存储过程的。用来创建这个例子的文件可以在www.wrox.com或www.tupwk.com.cn/downpage/上找到。
大部分代码都简单易懂，但是有一些新的语法需要进行解释，即range right for values。它的作用是决定如何将边界条件应用到实际范围值上。在本例中，第一个范围边界是01/01/1990。函数如何处理同该值匹配的数据？它属于哪个范围呢？Range Right告诉函数将匹配的数据放在数据的右侧(较高的值，或在范围边界以上)，而Range Left则告诉函数将匹配的数据放在数据的左侧(较低的值，或在范围边界以下)。
注意：
这里需要加以注意的是当使用datatime域时，匹配的是精确的日期和时间。在本例中，可以不用担心一些接近于边界的值的精确放置可能会进入到下一个分区。在一个实际执行系统中，需要关心的是分区精确地持有希望的值。在那种情况下，需要对为范围边界指定的精确的日期和时间常量特别加以注意。
第二个步骤是创建分区模式，如下：
CREATE PARTITION SCHEME [peoplePS1]
AS PARTITION [peopleRangePF1]
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY]);
GO
这里使用的句法非常简单。主要需要加以留心的是要创建的分区的精确数字。分区函数创建13个边界，因此需要为要加入的数据提供13+1=14个分区。
由于是对一个现有的表进行分区，而且不希望移动数据，所以会将所有的数据保存在现有的文件组中。
接下来要创建一个使用分区模式来进行分区的聚簇索引，以将数据“重建”到正确的分区：
CREATE CLUSTERED INDEX cix_People ON people(dob,personID)
ON peoplePS1(dob)
聚簇索引键是dob和personID，因为dob是分区键，而personID会使得每个索引记录唯一(参见11.2.3节中的聚簇索引)。使用和底层表相同的分区模式创建的索引被称作对齐索引。这不是一个要求，但是这是在一个已经分区的表上创建索引时默认的。
最后，可以检查系统元数据以确认分区的正确性。为了完成这个检查，可以使用一些新的语法来判断数据所在的分区：$partition。下面是使用它来查看每个分区上有多少行的例子：
- this will tell us how many entries there are in each partition
SELECT $partition.PeopleRangePF1(dob) [Partition Number], count(*) AS total
FROM people
GROUP BY $partition.PeopleRangePF1(dob)
ORDER BY $partition.PeopleRangePF1(dob)
Partition Number total
---------------- -----------
1 5379
2 18240
3 18206
4 18259
5 18147
6 18075
7 18295
8 18131
9 18313
10 18242
11 18091
12 8987
13 1810
14 1823
使用这个语法，可以看到每个分区上的行数，从而确认数据分布符合您的计划。
2. 将新数据切换到分区的表
既然表已经进行了分区，需要一种方式来离线增加新的数据并将新的数据切换到表分区中。第一步是创建一个表来持有所有要加载的新数据。这个表需要有着同分区的表相同的布局：
-- Create a new People table to hold any births after 09/20/2007
-- MUST BE IDENTICAL to People
CREATE TABLE newPeople (
[personID] [uniqueidentifier] NULL DEFAULT (newsequentialid()),
[firstName] [varchar](80) NOT NULL,
[lastName] [varchar](80) NOT NULL,
[DOB] [datetime] NOT NULL,
[DOD] [datetime] NULL,
[sex] [char](1) NOT NULL
) on [PRIMARY] -- Must be on the same filegroup as the target partition!
之后，需要创建usp_birthInsert和usp_loopBirthInsert存储过程的改进版本来将行插入到newPeople表中，而不是插入到People表。在本例中，调用新的过程usp_birthInsert和usp_loopNewBirthInsert。
插入到新表的速度会很快，因为这是一个空表，而且没有会降低插入速度的索引。将一些数据插入到表中，几百或几千行就可以了。通过运行usp_loopNewBirthInsert来完成插入。修改参数将会决定插入的行数。
EXEC usp_loopNewBirthInsert 50000, 10000
GO
接下来，创建一个索引来同分区的索引相匹配：
-- create the index on the new table.
CREATE CLUSTERED INDEX cix_newPeople ON newPeople(dob,personID)
还有一件需要做的事情是在新表上创建一个约束检查来确保数据和将要设置的分区函数边界相匹配：
-- Before we do the switch,
-- create a check constraint on the source table
-- to enforce the integrity of the data in the partition
ALTER TABLE newPeople
ADD CONSTRAINT [CK_DOB_DateRange]
CHECK ([DOB] >= '09/20/2007');
GO
现在可以对活动分区进行修改，为希望加载的新的数据集做准备。第一步是修改分区模式，从而指定新的分区的位置。这个位置必须与新表在同一个文件组中。在本例中，这个条件很容易满足，因为所有的表都在Primary文件组中。在一个有着多个文件组的实际执行系统中，这将是一个可用的空文件组。关于使用多文件组的细节，参见稍后的“对实际执行系统进行分区”。
-- alter the partition scheme to ready a new empty partition
ALTER PARTITION SCHEME PeoplePS1
NEXT USED [PRIMARY];
GO
下一步是在分区函数中创建新的分区边界。这是通过使用ALTER PARTITION FUNCTION语句来完成的。在本例中，有一个range right函数，新的数据是为2007-9-20提供的，而且希望所有的新数据都位于新的分区，因此使用RANGE RIGHT，将边界指定为2007-9-20。
-- Split the newest partition at Sept 20, 2007
-- This is a RANGE RIGHT function,
-- so anything on 09/20 goes into the NEW partition,
-- anything BEFORE 09/20 goes into the OLD partition
-- The new partition this creates goes to the new partition
-- we prepared in the alter scheme above
ALTER PARTITION FUNCTION PeopleRangePF1()
SPLIT RANGE ('09/20/2007');
GO
下一步是将新数据切换到分区中，但是在进行切换之前，检查一下在newPeople表中已经有多少人了。
EXEC sp_spaceused newPeople
GO
name rows reserved data index_size unused
-----------------------------------------------------------------------
newPeople 99998 14472 KB 14416 KB 8 KB 48 KB
现在可以应用切换，并将所有这些人移动到新的分区中。
-- Now switch in the data from the new table
ALTER TABLE newPeople
SWITCH -- No partition here as we are switching in a NON partitioned table
TO People PARTITION 15; -- but need to add the partition here !
GO
切换的运行速度非常快。现在检查一下每个表中有多少人。首先，查看newPeople表中有多少人：
EXEC sp_spaceused newPeople
GO
name rows reserved data index_size unused
-----------------------------------------------------------------------
newPeople 0 0 KB 0 KB 0 KB 0 KB
与预期的一样，该表是空的。而所有的99 998人的记录都在分区表的新分区中：
-- this will tell us how many entries there are in each partition
SELECT $partition.PeopleRangePF1(dob) [Partition Number], count(*) AS total
FROM people
GROUP BY $partition.PeopleRangePF1(dob)
ORDER BY $partition.PeopleRangePF1(dob)
Partition Number total
---------------- -----------
1 5571
2 18381
3 18245
4 18139
5 17970
6 18106
7 17934
8 18360
9 18153
10 18297
11 18168
12 9051
13 1852
14 1771
15 99998
-- We now have a 15th partition with all the new people in it
查询这个表分区，可以找到第15个分区，有99 998人的记录位于其中。
3. 从分区的表中删除数据
当表现新的一天、一周、一个月的数据的新数据集加入到表中之后，还需要将旧的数据移出，要么删除这些数据，要么将它们存档到别的位置。这个过程的第一步是创建一个新的表来放置数据。这应当是一个空表，而且有着与分区的表相同的结构，同时这个表应当和要删除的分区位于相同的文件组中。与前面一样，还需要创建相同的索引结构。
-- Step one, we need a table to put the data into
CREATE TABLE oldPeople (
[personID] [uniqueidentifier] NULL DEFAULT (newsequentialid()),
[firstName] [varchar](80) NOT NULL,
[lastName] [varchar](80) NOT NULL,
[DOB] [datetime] NOT NULL,
[DOD] [datetime] NULL,
[sex] [char](1) NOT NULL
) ON [PRIMARY]
-- Must be on the same filegroup as the source partition!
-- we need a clustered index on DOB to match the partition
CREATE CLUSTERED INDEX cix_oldPeople ON oldPeople(dob, personID)
GO
注意，在这个表上不需要进行约束检查，因为是将数据从分区中删除，而不是移入到其中。
既然已经有了与分区的表和索引相匹配的表和聚簇索引，可以执行切换来移出一组数据。这仍然是通过ALTER TABLE SWITCH语句来完成的，但是要注意现在使用的句法发生了一点变化，必须指定分区编号。
-- now go switch out the partition
ALTER TABLE People
SWITCH partition 1 -- which partition are we removing
TO OldPeople
-- No partition here as we are switching to a NON partitioned table
GO
现在，再次检查每个分区中有多少行。
-- this will tell us how many entries there are in each partition
SELECT $partition.PeopleRangePF1(dob) [Partition Number], count(*) AS total
FROM people
GROUP BY $partition.PeopleRangePF1(dob)
ORDER BY $partition.PeopleRangePF1(dob)
Partition Number total
---------------- -----------
2 18381
3 18245
4 18139
5 17970
6 18106
7 17934
8 18360
9 18153
10 18297
11 18168
12 9051
13 1852
14 1771
15 99998
-- Notice that partition 1 has gone!
-- lets see how many people are now in the oldPeople table
EXEC sp_spaceused oldPeople
GO
name rows reserved data index_size unused
-----------------------------------------------------------------------
oldPeople 5571 904 KB 784 KB 16 KB 104 KB
可以看到编号为1的分区不再是分区的表中的一部分，该分区中的行现在位于oldPeople表中。
还要执行一点整理工作，即修改分区函数来合并不需要的旧的范围。这是通过另一个ALTER PARTITION功能语句来完成的，在该语句中，可以指定不再需要的旧的边界。
-- next we can merge the first partition
ALTER PARTITION FUNCTION PeopleRangePF1()
MERGE RANGE (’01/01/1900’);
GO
-- now check the partition layout after the merge
-- this will tell us how many entries there are in each partition?
select $partition.PeopleRangePF1(dob) [Partition Number], count(*) as total
from people
group by $partition.PeopleRangePF1(dob)
order by $partition.PeopleRangePF1(dob)
/*
Partition Number total
---------------- -----------
Partition Number total
---------------- -----------
1 18381
2 18245
3 18139
4 17970
5 18106
6 17934
7 18360
8 18153
9 18297
10 18168
11 9051
12 1852
13 1771
14 99998
*/
-- Now we only have partitions 1-14 again
以上内容相当简单，但是还没有彻底完成。虽然已经将旧的分区数据从表分区中删除了，但是它仍然位于相同的文件组中，消耗着最为昂贵的存储。希望这些数据位于较廉价的用于存档的存储，或者位于准备进行备份或删除的位置。
为了实现这一点，需要将数据从一个文件组中物理移动到另一个。有很多方法可以用来完成这件工作，但是速度最快且最有效的方法是使用一个select into。最后还需要完成以下三步。
(1) 修改默认文件组为存档文件组。
(2) select into到一个新的表中。
(3) 将默认文件组修改回来。
下面是完成以上工作的代码：
-- Once its slid out, we have to physically move the data
-- to a different filegroup for archiving storage
-- Change the default filegroup to be cheapDisks
-- This is so that the SELECT INTO creates the new table on cheapDisks
ALTER DATABASE people ADD FILEGROUP cheapDisks
GO
ALTER DATABASE people ADD FILE (NAME = cheapdisk1, FILENAME = 'D:\cheapdisk.ndf')
TO FILEGROUP cheapDisks
GO
ALTER DATABASE people MODIFY FILEGROUP cheapDisks DEFAULT
GO
-- Move the data!
SELECT * INTO archivePeople
FROM oldPeople
-- alter the default filegroup back again!
ALTER DATABASE People MODIFY FILEGROUP [primary] DEFAULT
GO
-- you can now drop the oldPeople table
DROP TABLE oldPeople
最后，查看系统元数据来观察新的表所处的位置。下面是可用于此目的的查询，同时还显示了简化后的结果，只显示我们所关心的对象：
-- Lets check which filegroup all our objects are on
SELECT OBJECT_NAME(i.object_id) AS ObjectName, i.name AS IndexName, f.name
as filegroupName
FROM sys.indexes AS i INNER JOIN sys.filegroups AS f ON i.data_space_id =
f.data_space_id
WHERE i.object_id > 100
-- Returns
ObjectName IndexName filegroupName
---------- --------- -------------
boysnames NULL PRIMARY
girlsnames NULL PRIMARY
lastnames NULL PRIMARY
newPeople cix_newPeople PRIMARY
archivePeople NULL cheapDisks
可以看到archivePeople现在位于cheapDisks文件组中。
4. 对实际执行系统进行分区
迄今为止，介绍的场景都是用来显示如何实现一个滑动窗口的场景，但是它们掩盖了一些细节，这些细节是实际执行系统当中的关键点。在实际执行系统上的一个主要问题是将会有着多个文件组与不同的物理存储相匹配。
这一点所产生的变化发生在创建和修改分区模式时。因此，不像之前所看到的例子，这里将会使用这样的代码来创建分区模式：
CREATE PARTITION SCHEME [PeoplePS1]
AS PARTITION [PeopleRangePF1]
TO ([FileGroup1], [FileGroup2], [FileGroup3] );
GO
每个分区会分到不同的文件组中。
当创建文件组时，需要确保有着足够的空间，不仅用于活动的表，而且还包括用于新的数据以及移动到存档存储之前的或要进行删除的旧的数据的空间，因此所需要的文件组的数目为活动分区的数目再加上两个，一个用于新的数据，另一个用于旧的数据。
另一种物理布局可能会让多个分区共享同一个文件组。布置物理磁盘的精确细节超出了本章的范围，但是在第6章中大略谈到过。
5. 分区和DTA
如果提出要求，则DTA将会提供分区建议。为了实现这个目的，需要修改Tuning Options标签页中的调校选项。在标题为Partitioning strategy to employ的组中，将默认设置No Partitioning修改为full partitioning。还需要决定调校的查询的类型。使用INSERT、DELETE或SWITCH查询没有好处，因为DTA对它们不感兴趣。我们希望对不利于非分区的表的SELECT语句进行调校。DTA对INSERT、DELETE和SWITCH语句不感兴趣的原因是它无法通过增加索引来提高它们的性能，因此它忽略这些语句，并只关注于它可以调校的语句：SELECT。
如果表足够大，而且查询能够从分区中受益，DTA将会提供脚本来创建分区函数、分区模式以及聚簇索引。下面是一个来自另一个数据库的DTA分区建议的例子。底层表有着57 000 000行，使用1.8 GB数据空间和3.6 GB索引空间。
CREATE PARTITION FUNCTION [_dta_pf__2533](int) AS RANGE LEFT FOR VALUES
(103863552,196930103, 203421423, 246065168, 269171113, 269702979,
270375078, 273695583,276447808, 280951053, 298459732, 298855583, 299375843,
299810346, 301474640)
CREATE PARTITION SCHEME [_dta_ps__8258] AS PARTITION [_dta_pf__2533] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY])
CREATE NONCLUSTERED INDEX [_dta_index_myTable_6_741577680_23810504_K1_K2_K3] ON
[dbo].[myTable]
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,
ONLINE =OFF) ON [_dta_ps__8258]([Colx])
