[原创]SQL2008分区表的应用策略

由于公司项目上要使用分区表来对表进行分区管理。。。

首先创建表结构，其中我们用AIR_FROM_DATETIME字段来进行分区。。。

CREATE TABLE [dbo].[BA_CACHE_AIR_RESULT]( [AIR_ID] [nvarchar](50) NOT NULL, [TRIPINDEX] [nvarchar](50) NULL, [AIR_AGENT_ID] [varchar](36) NULL, [AIR_FROM_AIRPORT_CODE] [nvarchar](10) NULL, [AIR_TO_AIRPORT_CODE] [nvarchar](10) NULL, [AIR_FROM_AIRPORT_NAME] [nvarchar](50) NULL, [AIR_TO_AIRPORT_NAME] [nvarchar](50) NULL, [AIR_FROM_CITY_NAME] [nvarchar](50) NULL, [AIR_TO_CITY_NAME] [nvarchar](50) NULL, [AIR_FROM_DATETIME] [datetime] NULL, [AIR_TO_DATETIME] [datetime] NULL, [AIR_AIRFARE_PRICE] [decimal](18, 2) NULL, [AIR_ADULT_DISCOUNT] [decimal](18, 2) NULL, [AIR_CHILD_DISCOUNT] [decimal](18, 2) NULL, [AIR_BABY_DISCOUNT] [decimal](18, 2) NULL, [AIR_ADULT_DISCOUNT_ORDER] [tinyint] NULL, [AIR_TAX_FEE] [nvarchar](40) NULL, [AIR_OIL_FEE] [nvarchar](40) NULL, [AIR_AIRLINES_INFO] [nvarchar](50) NULL, [AIR_FLIGHT_CODE] [nvarchar](50) NULL, [AIR_FLIGHT_TYPE] [nvarchar](50) NULL, [AIR_CABIN_CODE] [nvarchar](50) NULL, [AIR_CABIN_NAME] [nvarchar](50) NULL, [AIR_AIRPLANE_INFO] [nvarchar](1000) NULL, [AIR_TUIPIAO_DESC] [nvarchar](1000) NULL, [AIR_GENGGAI_DESC] [nvarchar](1000) NULL, [AIR_QIANZHUAN_DESC] [nvarchar](1000) NULL, [AIR_INSURANCE_FEE] [decimal](18, 0) NULL, [AIR_TICKET_COUNT] [nvarchar](10) NULL, [AIR_LEG_TYPE] [nvarchar](1) NULL, [AIR_TRANSIT_AIRPORT_CODE] [nvarchar](50) NULL, [AIR_TRANSIT_AIRPORT_NAME] [nvarchar](50) NULL, [AIR_TRANSIT_CITY_NAME] [nvarchar](50) NULL, [AIR_TRANSIT_TO_DATETIME] [nvarchar](50) NULL, [AIR_TRANSIT_FROM_DATETIME] [nvarchar](50) NULL, [AIR_TRANSIT_AIRLINES_INFO] [nvarchar](50) NULL, [AIR_TRANSIT_FLIGHT_CODE] [nvarchar](50) NULL, [AIR_TRANSIT_AIRPLANE_INFO] [nvarchar](1000) NULL, [AIR_TRANSIT_TAX_FEE] [nvarchar](20) NULL, [AIR_TRANSIT_OIL_FEE] [nvarchar](20) NULL, [AIR_FROM_CITY_CODE] [nvarchar](10) NULL, [AIR_TO_CITY_CODE] [nvarchar](10) NULL, [AIR_AFTER_DISCOUNT_ADULT_PRICE] [decimal](18, 2) NULL, [AIR_AFTER_DISCOUNT_CHILD_PRICE] [decimal](18, 2) NULL, [AIR_AFTER_DISCOUNT_BABY_PRICE] [decimal](18, 2) NULL, [AIR_AIRLINE_RULE_ID] [varchar](5) NULL, [AIR_TOURCODE_ID] [varchar](20) NULL, [AIR_TJ] [nvarchar](1) NULL, [AIR_FAREBASIS] [nvarchar](100) NULL, [AIR_CODESHARE] [varchar](5) NULL, [AIR_CREATETIME] [datetime] NULL, [AIR_OFTEN] [int] NULL, CONSTRAINT [PK_AIR_ID] PRIMARY KEY NONCLUSTERED ( [AIR_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD DEFAULT ('false') FOR [AIR_CODESHARE] GO ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD DEFAULT (getdate()) FOR [AIR_CREATETIME] GO

表结构创建好了后，创建三个非聚集索引，如果应用了分区表的话，最好是不要使用聚集索引的。如果在其中一个分区使用了，则数据又会回到那个分区上。。。分区表就没作用了。。

ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID --创建主键，但不设为聚集索引 ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED ( AIR_ID ASC ) ON [PRIMARY] IF EXISTS( SELECT * FROM SYSINDEXES WHERE name='IX_001' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT') ) BEGIN DROP INDEX IX_001 ON BA_CACHE_AIR_RESULT END CREATE NONCLUSTERED INDEX [IX_001] ON [dbo].[BA_CACHE_AIR_RESULT] ( [AIR_FROM_CITY_CODE] ASC, [AIR_TO_CITY_CODE] ASC, [AIR_FROM_DATETIME] 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 = ON) IF EXISTS(SELECT * FROM SYSINDEXES WHERE name='IX_002' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT')) BEGIN DROP INDEX IX_002 ON BA_CACHE_AIR_RESULT END /****** Object: Index [IX_002] Script Date: 03/09/2011 19:03:52 ******/ CREATE NONCLUSTERED INDEX [IX_002] ON [dbo].[BA_CACHE_AIR_RESULT] ( [AIR_AGENT_ID] ASC, [AIR_FLIGHT_CODE] ASC, [AIR_TRANSIT_FROM_DATETIME] ASC, [AIR_FROM_CITY_CODE] ASC, [AIR_TO_CITY_CODE] 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 = ON) GO

接着我们来创建分区方案

CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO ([CITY04], [CITY05], [CITY06], [CITY07], [CITY07], [CITY07], [CITY07], [CITY08], [CITY08], [CITY08], [CITY08], [CITY01], [CITY02], [CITY03], [PRIMARY]) GO

接着创建分区函数

CREATE PARTITION FUNCTION [FromCityCodePartitionFunction](datetime) AS RANGE LEFT FOR VALUES (N'2011-03-15T00:00:00.000', N'2011-03-20T00:00:00.000', N'2011-03-25T00:00:00.000', N'2011-03-30T00:00:00.000', N'2011-04-05T00:00:00.000', N'2011-04-10T00:00:00.000', N'2011-04-15T00:00:00.000', N'2011-04-20T00:00:00.000', N'2011-04-25T00:00:00.000', N'2011-04-30T00:00:00.000', N'2011-05-01T00:00:00.000', N'2011-05-03T00:00:00.000', N'2011-05-05T00:00:00.000', N'2011-05-07T00:00:00.000') GO

到这里部分哥们可能要问了：这些CITY04，CITY05等是什么？这些呢是数据库的文件组啦。。上图

如果还不清楚的话，哥就把数据库的创建脚本也贴上。。。

CREATE DATABASE [Demo] ON PRIMARY ( NAME = N'Demo', FILENAME = N'D:/EMS_DATA/Demo.mdf' , SIZE = 234880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY01] ( NAME = N'CITY01', FILENAME = N'E:/AirData/CITY01.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY02] ( NAME = N'CITY02', FILENAME = N'E:/AirData/CITY02.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY03] ( NAME = N'CITY03', FILENAME = N'F:/AirData/CITY03.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY04] ( NAME = N'CITY04', FILENAME = N'F:/AirData/CITY04.ndf' , SIZE = 333824KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY05] ( NAME = N'CITY05', FILENAME = N'D:/AirData/CITY05.ndf' , SIZE = 330880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY06] ( NAME = N'CITY06', FILENAME = N'D:/AirData/CITY06.ndf' , SIZE = 330176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY07] ( NAME = N'CITY07', FILENAME = N'D:/AirData/CITY07.ndf' , SIZE = 981120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [CITY08] ( NAME = N'CITY08', FILENAME = N'D:/AirData/CITY08.ndf' , SIZE = 651840KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = Demo_log', FILENAME = N'D:/EMS_DATA/Demo.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

好了,到这里我们就开始创建分区了。。。

BEGIN TRANSACTION CREATE PARTITION FUNCTION [FromCityCodePartitionFunction](datetime) AS RANGE LEFT FOR VALUES ( N'2011-03-01T00:00:00', N'2011-03-05T00:00:00', N'2011-03-10T00:00:00', N'2011-03-15T00:00:00', N'2011-03-20T00:00:00', N'2011-03-25T00:00:00', N'2011-03-30T00:00:00', N'2011-04-05T00:00:00', N'2011-04-10T00:00:00', N'2011-04-15T00:00:00', N'2011-04-20T00:00:00', N'2011-04-25T00:00:00', N'2011-04-30T00:00:00') CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO ( [CITY01], [CITY01], [CITY01], [CITY01], [CITY02], [CITY02], [CITY02], [CITY02], [CITY03], [CITY03], [CITY03], [CITY03], [CITY03], [CITY04], [CITY04], [CITY04], [PRIMARY]) ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID --创建主键，但不设为聚集索引 ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED ( AIR_ID ASC ) ON [PRIMARY] CREATE CLUSTERED INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT] ( [AIR_FROM_DATETIME] )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FromCityCodePartition]([AIR_FROM_DATETIME]) DROP INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT] WITH ( ONLINE = OFF ) COMMIT TRANSACTION

等待这个执行完毕后。。

我们就通过这个SQL语句来查看各分区的情况了。。。

SELECT PARTITION = $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME) ,ROWS = COUNT(*) ,MINVAL = MIN(AIR_FROM_DATETIME) ,MAXVAL = MAX(AIR_FROM_DATETIME) FROM DBO.BA_CACHE_AIR_RESULT GROUP BY $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME) ORDER BY PARTITION GO

大功告成。。。