SQL Server分区表应用场景

背景：

    《SQL Server 大数据管理——表分区》介绍创建分区的基础知识；《SQL Server 自动化管理——分区表自动化管理》介绍如何自动化管理分区；《SQL Server 大数据管理——数据归档（主文件备份）》、《SQL Server 大数据管理——数据归档(段落备份)》介绍了分区在数据归档中的应用。但对什么样的表可以做分区，如何选择分区字段，分区对性能上有哪些影响等问题都未提及。这些问题是非常复杂的，想要详尽彻底说明白，不是一两篇博文能够说清楚的，本文将抛砖引玉，简述分区的几种场景，以供参考。

一.    如何确定需要分区的表

        表是否需要做分区，首要条件是数据表数据足够大。定义“大”表是一个很难的问题，一般根据表记录数或表记录存储空间大小，这个“大”表会因服务器性能（包括硬盘读写速度，带宽）等而不同，有些服务器可能100万行数据即为大表，有些可能千万级数据为大表。

       其次就是针对大表，需要做某些操作，如数据归档，前面已经有两文叙述该方面的应用；或者存在某种性能问题，而除分区外，其他优化措施都不能明显起作用，或者有这样那样的制约，本文将给出两个应用场景。

       确定需要做分区的表，接下来就是如何选择分区字段的问题。这个问题需要结合实际应用场景说明，打算在下文各应用场景中涉及。

二.    分区多少问题

        一个大表，需要分多少个分区合适，可遵循一个规则，即每个分区的数据不再是大数据即可。这个和确定大表是同样的难题，需要日常管理中去发现，或者进行实验，这里无法给出量化标准，请根据实际情况确定。

三.     解决写瓶颈问题

        每块硬盘写的速度是固定，而数据量会随着用户量等的增加而成倍数级，甚至指数级增加，当单块硬盘写的速度达到不到数据量增长的需求时，就会出现写瓶颈。对于该类问题，现在比较流行的处理方式是分布式存储，使用多服务器的多块硬盘同时进行写操作。如果数据量级还没有到非使用分布式存储不可的情况下，其实SQL Server也提供了同服务器的伪分布式存储方案，即表分区。对于写密集型表（如交易明细表），将分区文件分布在不同的硬盘上，如果能做到数据被随机写到N个分区中，最大可以将写提升接近N倍。如下脚本创建分区文件分布在两快硬盘上的分区表trade_log_new和不分区表trade_log_new_no

USE [test]
GO
 
alter database test
    addfilegroup tradelog_0
alter database test
    addfilegroup tradelog_1
 
alter database test
add file(name=N'tradelog_0',filename='D:\DB\test_partition\tradelog_0.ndf'
    ,size=5mb, filegrowth=5mb)
to filegroup tradelog_0
alter database test
add file(name=N'tradelog_1',filename='E:\database\tradelog_1.ndf'
    ,size=5mb, filegrowth=5mb)
to filegroup tradelog_1
 
create partition function F_Tradelog(int)
as range right for values(1)
 
create partition scheme P_Tradelog
as partition [F_Tradelog]
to(tradelog_0,tradelog_1)
 
CREATE TABLE [dbo].[tradelog_new](
    [ID] [int] IDENTITY(1,1)NOTNULL,
    [productID] [int] NULL,
    [tradedate] [datetime] NULL
    ,partitionId as id%2 PERSISTED
)on P_Tradelog(partitionId)
 
CREATE TABLE [dbo].[tradelog_new_no](
    [ID] [int] IDENTITY(1,1)NOTNULL,
    [productID] [int] NULL,
    [tradedate] [datetime] NULL
    ,partitionId as id%2 PERSISTED
)
GO

分别使用如下脚本，开两个进行程，向两个表中插入200万行记录

Tradelog_new插入数据脚本

DECLARE @max ASINT=1000000
declare @rc int=0
WHILE @rc  <= @max
BEGIN
    INSERT INTO tradelog_new(productID,tradedate)values(@rc,getdate())
    SET @rc = @rc +1;
END

Tradelog_new_no插入数据脚本

DECLARE @max ASINT=1000000
declare @rc int=0
WHILE @rc  <= @max
BEGIN
    INSERT INTO tradelog_new_no(productID,tradedate)values(@rc,getdate())
    SET @rc = @rc +1;
END

 

四.     解决并行问题

有些大表，需要频繁发生DML操作，如用户表，会插入新增用户，更新、查找已有用户信息，对于该类表，可以采用分区，以减少DML操作的相互影响，提高性能。因为各分区间的DML操作，不相互影响。下面给出脚本来验证这一结论。

新建两个查询窗口，在其中一个查询窗口逐次执行下面三个代码片段

--代码片段1
begintran
    insert into tradelog(productID,tradedate)values(5,'2018-04-30')
--代码片段2
begintran
    update tradelog set productID =4
    where tradedate>'2018-04-30'and tradedate<'2018-05-01'
--代码片段3
begintran
    delete top(1)from tradelog
    where tradedate>'2018-04-30'and tradedate<'2018-05-01'

在另外一个查询窗口执行如下代码：

select * from tradelog
where tradedate>'2018-4-27'and tradedate<'2018-4-30'
 
update tradelog set productID =4
    where tradedate>'2018-05-01'and tradedate<'2018-05-01'
 
insert into tradelog(productID,tradedate)
values(1,'2018-05-01')
 
delete top(1)from tradelog
where tradedate>'2018-05-01'

本文为测试方便，沿用《 SQL Server自动化管理——分区表自动化管理》创建tradelog表，如需亲测，请打开原文，将其创建的表的脚本拷去创建即可。