数据库优化——分区存储

最近遇到很头疼的事情，服务器数据越来越大，网页读取经常超时，数据库经过几次优化（索引、程序修改等）刚开始优化后还行，运行一阶段又出现重复问题，主要数据库存在联合查表比较多，所以这一次打算利用分区存储来测试优化，刚开始使用分区存储走了不少弯路，因为服务器数据库版本问题，不能执行分区（只有sql企业版本可以执行），所以跨服务器测试无法执行，只能再本地两台机器搭建测试，在数据库导出（数据结构问题TimeStamp）、数据库复制中（ 找不到sqlServerAgent 服务，修复及权限配置后还是没有）步步存在问题，最后找到另外一个客户服务器测试，在测试中也同样是花费了不少时间，本来都是整个数据库备份还原，再把分区的表删除，重建结构，分组，导入数据，后来测试发现只要修改索引即可，可以节约很多时间，具体看一下过程。

将实现过程列出如下：

--select*from RepositoryChange
--数据库备份还原为MedDB_New1
--1、数据处理
--select count(*) from SaleDetails
--delete from SaleDetails
--update ProductExt set RepositoryChangeID=Null where RepositoryChangeID!=Null
--delete RepositoryExt
--delete from RepositoryChange
--有现成的数据库,步骤1,6.1,6.2可以忽略

--2、创建文件组
alter database MedDB_New1
add filegroup f_fg1

alter database MedDB_New1
add filegroup f_fg2


--3、创建文件
alter database MedDB_New1
add file
(
    name = f_fg1_1,
    filename = 'D:\MedData\fg1_1.ndf',
    size = 1MB
)
to filegroup f_fg1

alter database MedDB_New1
add file
(
name = f_fg2_1,
filename = 'E:\MedData\fg2_1.ndf',
size = 1MB
)
to filegroup f_fg2

--4、建立分区函数，以时间分区
CREATE partition function partFuncForExample(datetime)
as Range Right for Values('2015-01-01')
--把分区函数建立好以后，我们再来建立分区方案。目的是为了把分区函数产生的分区映射到文件数据组里。
--分区函数是告诉数据库如何分区数据，而分区方案是告诉数据库如何把已分区的数据存到哪个文件组里。
--5、建立分区方案
CREATE partition Scheme PartSchForExample
as partition partFuncForExample
to (f_fg1,f_fg2)

--6、建立分区表
--6.1创建表,下面是把已经存在的表改为分区表,其实分区表说到底就是对聚集索引的分区,
--所以只要重建主键索引就可以了
CREATE TABLE [dbo].[RepositoryChange](
    [ID] [uniqueidentifier] NOT NULL,
    [ShopID] [uniqueidentifier] NOT NULL,
    [ProductID] [uniqueidentifier] NOT NULL,
    [SerialNO] [nvarchar](50) NULL,
    [ChangeAmount] [int] NOT NULL,
    [RealAmount] [int] NOT NULL,
    [ChangeReasonID] [int] NOT NULL,
    [InPrice] [money] NULL,
    [OutPrice] [money] NULL,
    [DistributorID] [uniqueidentifier] NULL,
    [ManufactorID] [uniqueidentifier] NULL,
    [Description] [nvarchar](500) NULL,
    [RepositoryID] [uniqueidentifier] NULL,
    [CubeID] [uniqueidentifier] NULL,
    [CreatedBy] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [SeriesNo] [nvarchar](50) NULL,
    [Currency] [money] NULL,
    [ManufactedDate] [date] NULL,
    [InvalidDate] [date] NULL,
    [VerifiedBy1] [uniqueidentifier] NULL,
    [VerifiedBy1Date] [datetime] NULL,
    [VerifiedBy2] [uniqueidentifier] NULL,
    [VerifiedBy2Date] [datetime] NULL,
    [InvoiceNumber] [nvarchar](50) NULL,
    [InvoiceDate] [datetime] NULL,
    [IsValid] [int] NOT NULL,
    [Timestamp] [timestamp] NOT NULL,
    [ArrivalNum] [int] NULL,
    [CheckResult] [int] NOT NULL,
    [InvalidReason] [nvarchar](500) NULL,
     --constraint pk_ID_CreatedDate   --主键约束
    -- primary key(ID,[CreatedDate])  
    ) on PartSchForExample([CreatedDate]) --分区字段

--6.2添加数据或数据导入
insert into MedDB_New1.dbo.RepositoryChange(ID,ShopID,ProductID,SerialNO,ChangeAmount,RealAmount,ChangeReasonID,InPrice,OutPrice,DistributorID,ManufactorID,[Description],RepositoryID,
CubeID,CreatedBy,CreatedDate,SeriesNo,Currency,ManufactedDate,InvalidDate,VerifiedBy1,VerifiedBy1Date,VerifiedBy2,VerifiedBy2Date,
InvoiceNumber,InvoiceDate,IsValid, ArrivalNum,CheckResult,InvalidReason)
select ID,ShopID,ProductID,SerialNO,ChangeAmount,RealAmount,ChangeReasonID,InPrice,OutPrice,DistributorID,ManufactorID,[Description],RepositoryID,
CubeID,CreatedBy,CreatedDate,SeriesNo,Currency,ManufactedDate,InvalidDate,VerifiedBy1,VerifiedBy1Date,VerifiedBy2,VerifiedBy2Date,
InvoiceNumber,InvoiceDate,IsValid, ArrivalNum,CheckResult,InvalidReason  
from MedDB.dbo.RepositoryChange;

      
--6.3现在需要把原表按照一个分区架构来分区
--6.3.1如果这么删除,会报错,因为这个索引正用于 PRIMARY KEY 约束的强制执行
drop index PK_RepositoryChange on dbo.RepositoryChange


--6.5.2重建聚集索引,操作失败，因为表'RepositoryChange'上已存在名称为'PK_RepositoryChange'的索引或统计信息。
create clustered index PK_RepositoryChange on RepositoryChange(ID,CreatedDate)
on PartSchForExample(CreatedDate)


--6.3.3正确的方法：首先需要删除主键约束,应该这么写才是对的
alter table dbo.RepositoryChange
drop constraint PK_RepositoryChange

--删除其他表外键约束
alter table dbo.SaleDetails
drop constraint FK_SaleDetails_RepositoryChange
alter table dbo.RepositoryExt
drop constraint FK_RepositoryExt_RepositoryChange
alter table dbo.ProductExt
drop constraint FK_ProductExt_RepositoryChange


--6.3.4再次建立主键约束,指定创建聚集索引,同时指定分区架构
alter table dbo.RepositoryChange
add constraint PK_RepositoryChange primary key clustered (ID,CreatedDate)
on PartSchForExample(CreatedDate)

--7.显示每条数据所属分区号,从1开始计算
--8、查询数据所在分区
select *,
       --$partition函数,后面是分区函数名称,列名称
       $partition.partFuncForExample(CreatedDate) as partition
from RepositoryChange

 

参考链接：http://www.myexception.cn/sql-server/1397279.html

http://blog.jobbole.com/80395/