SQL SERVER 表及索引分区

SQL SERVER 表及索引分区

表分区应该是处理海量数据一个最好的方法。名义上一个表，但分散存储于不同的物理文件上，这跟小表并没有多大区别。

不过，分区表的主键如何处理却不得不谨慎。

分区表的依据列最好就是主键。比如用户表，UserId是自动增长的标识列，主键，又以之作为划分分区的依据，每50万一个分区。在这里，主键与分区依据列完美结合，护发去头皮。 但有时候主键不一定是分区依据列。比如说，用户设置表。用户表与用户设置表是一对多的关系，一个用户有好多设置，即每个用户会有若干条设置记录。这种情况，UserId应该是分区依据列，但显然不能是主键，因为不唯一。

通常情况下，主键默认是聚集索引。而聚集索引是数据的一部分，就是说，聚集索引和表数据肯定是同一个文件组。如果主键不是分区依据列，又是聚集索引，这个表还怎么分区？我就试过，辛辛苦苦将一个巨大表分好区，此时尚未建主键，后来将主键建到一个独立的文件组，发现表数据也全部挪到该独立文件组，之前的分区努力化为泡影。

我的感觉是，这种情况下，主键不能定为聚集索引，应该做成非聚集索引。

除了依据列，其他索引不要建立在这个分区上（即所谓对齐），全部指定独立文件组，甚至自己分区。否则，系统会偷偷给你搭售依据列。

--------------------------------------------------------------------------------------- （以下内容抄自SQL SERVER 2008 联机帮助）对聚集索引进行分区 对聚集索引进行分区时，聚集键必须包含分区依据列。对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下 SQL Server 将在聚集索引键列表中添加分区依据列。如果聚集索引是唯一的，则必须明确指定聚集索引键包含分区依据列。

对非聚集索引进行分区 对唯一的非聚集索引进行分区时，索引键必须包含分区依据列。对非唯一的非聚集索引进行分区时，默认情况下 SQL Server 将分区依据列添加为索引的非键（包含性）列，以确保索引与基表对齐。如果索引中已经存在分区依据列，SQL Server 将不会向索引中添加分区依据列。

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

CREATE TABLE [dbo].[User_News_Log]( [LogId] [int] IDENTITY(1,1) NOT NULL, [WebUserId] [int] NOT NULL, [Code] [varchar](20) NOT NULL, [Content] [varchar](700) NOT NULL, [CreateDate] [smalldatetime] NOT NULL,--非聚集索引主键 CONSTRAINT [PK_user_news] PRIMARY KEY NonCLUSTERED ( [LogId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserNewsIndex] ) --分区表 ON UserNewsSCM(CreateDate)--页级压缩 WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 5) ); GO

也由此可知，想将现有未分区表转为分区表，有个方法就是将聚集索引建到分区上。运行完毕，你会欣喜地发现，表已经分区了。

附：一个现有巨大表分区、压缩、主键再分区的例子

use [Compare] go------------------------------------------------------------------- -- 数据 -------------------------------------------------------------------

--文件组 ALTER DATABASE [Compare] ADD FILEGROUP [Compare0] GO ALTER DATABASE [Compare] ADD FILEGROUP [Compare1] GO ALTER DATABASE [Compare] ADD FILEGROUP [Compare2] GO-- 文件 ALTER DATABASE [Compare] ADD FILE (NAME = N'Compare0',FILENAME = N'C:/Compare/Compare0.ndf',SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [Compare0] GO ALTER DATABASE [Compare] ADD FILE (NAME = N'Compare1',FILENAME = N'C:/Compare/Compare1.ndf',SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [Compare1] GO ALTER DATABASE [Compare] ADD FILE (NAME = N'Compare2',FILENAME = N'C:/Compare/Compare2.ndf',SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [Compare2] GO-- 分区函数 CREATE PARTITION FUNCTION ComparePFN(INT) AS RANGE LEFT FOR VALUES (50000,100000); GO --分区方案 CREATE PARTITION SCHEME CompareSCM AS PARTITION ComparePFN TO ( [Compare0] ,[Compare1] ,[Compare2] ); GO --将聚集索引建到分区方案，数据自动跟着转移 CREATE CLUSTERED INDEX [cix_cp_wk] ON [dbo].[CatchMirror] ( [WebSiteKeyWordId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON CompareSCM(WebSiteKeyWordId) GO----------------------------------------------------------------- -- 行压缩 ----------------------------------------------------------------- ALTER TABLE [CatchMirror] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = ROW ON PARTITIONS(1 TO 3) ); GO------------------------------------------------------------------- -- 分区索引（主键） ------------------------------------------------------------------- ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex0] GO ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex1] GO ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex2] GO ALTER DATABASE [Compare] ADD FILE (NAME = N'CompareIndex0',FILENAME = N'C:/Compare/CompareIndex0.ndf',SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [CompareIndex0] GO ALTER DATABASE [Compare] ADD FILE (NAME = N'CompareIndex1',FILENAME = N'C:/Compare/CompareIndex1.ndf',SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [CompareIndex1] GO ALTER DATABASE [Compare] ADD FILE (NAME = N'CompareIndex2',FILENAME = N'C:/Compare/CompareIndex2.ndf',SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [CompareIndex2] GO CREATE PARTITION FUNCTION CompareIndexPFN(INT) AS RANGE LEFT FOR VALUES (200000000,400000000); GO CREATE PARTITION SCHEME CompareIndexSCM AS PARTITION CompareIndexPFN TO ( [CompareIndex0] ,[CompareIndex1] ,[CompareIndex2] ); GO --主键，非聚集，分区，并启用行压缩 ALTER TABLE [dbo].[CatchMirror] ADD CONSTRAINT [PK_CatchMirror] PRIMARY KEY NONCLUSTERED ( [CatchMirrorId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,DATA_COMPRESSION = ROW ) ON CompareIndexSCM(CatchMirrorId) GO 后记：应用分区对性能的改善是显著的。未分区和压缩前，服务器硬盘队列达到30以上；分区和压缩后，硬盘队列10以下，足足改善了3倍。补充一句：如果表已经应用了页级压缩，聚集索引就不要再应用行压缩，否则整个表又变成行压缩。 2、拆分分区： 拆分分区，除非分区方案里 有多余的文件组，否则要先添加文件组，然后修改分区方案，再修改分区函数。 ALTER PARTITION SCHEME UserSCM NEXT USED [user5]; ALTER PARTITION FUNCTION UserPFN() SPLIT RANGE('5') 3、分区依据列为字符型 -- 分区函数 CREATE PARTITION FUNCTION UserPFN(VARCHAR(50)) AS RANGE LEFT FOR VALUES ( '1','4','7','a','c','e','h','k','o','r','u','x','z'); GO 4、分区依据列为日期型 -- 分区函数 CREATE PARTITION FUNCTION UserPFN(SMALLDATETIME) AS RANGE LEFT FOR VALUES ('2008-01-01','2009-01-01','2010-01-01','2011-01-01','2012-01-01'); GO

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。