在SqlServer2005上进行表分区经验分享(转贴)

kittywei 2012-04-10

展开全文

最近因为一个项目数据量太大，导致经常查询数据超时；因此决定对其进行表分区，以下是这几天来研究的成果和经验，与大家共享之。

前提描述：有这么一个Log表，数据量大概是两千多万条，只保留六个月以内的数据。表里有列LogTime，记录日志的时间。

分析：对Log分成6个区，分别为PG1，PG2，PG3...PG6；每个区只存储一个月份的数据，以LogTime为分界点(推荐这6个区分别放在不同的物理硬盘上)

          步骤：
                 1.为数据库[TestDB]添加6个文件组及文件组逻辑名称，设定文件组存放的位置。我们暂时设定为PG1,G2…PG6，如果我们的数据库存储着今年1-6月份的数据，则我们的PG1就存放1月份的数据，PG2就存放2月份的数据，以此类推，PG6就存放6月份的数据。
              Alter DATABASE [TestDB]
              Add FILEGROUP [PG1]
              Alter DATABASE [TestDB]
              Add FILE( NAME = N'PG1', FILENAME = N'E:\Database\PG1.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ) To FILEGROUP [PG1]
上面只列出创建PG1的代码，依次创建PG2,PG3...PG6

               2.创建分区函数:
              Create Partition Function [Data Partition Range](DateTime)
                    AS Range Right For Values ('20070201 00:00:00.000','20070301 00:00:00.000’,'20070401 00:00:00.000','20070501 00:00:00.000','2007-06-01 00:00:00.000')
这里分区函数只有5个分界值，是相对应于之前的6个分区的。

               3.创建分区方案
                  Create Partition Scheme [Data Partition Scheme]
                  As Partition [Data Partition Range]
                  To ([PG1],[PG2],[PG3],[PG4],[PG5],[PG6])

               4.创建与Log表结构相同的表，暂名为Log1，需应用分区方案。
              CREATE TABLE [dbo].[Log1](
                   [logTime] [datetime] NULL,
                   ...
                   ...
                   ) ON [Data Partition Scheme](LogTime)

5.将Log表的数据迁移到Log1
Insert Into Log1 Select * From Log

6.为Log1表创建分区索引，这里创建索引的时间可能是比较久的，因为本身数据量很大
CREATE CLUSTERED INDEX [IX_LogTime] ON [dbo].[Log1]( [logTime] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Data Partition Scheme]([LogTime])