SQL Server 数据库分区分表（水平分表）详细步骤(2)-木庄网络博客

当前第2页返回上一页

图4 文件管理界面

在文件管理界面中，点击箭头①所示的“添加”选项，添加新的文件，在新添加的箭头②所示的区域，根据实际需求，填写对应的文件属性值，填写完成后点击“确定”。其中，一个文件组中可以添加多个文件，即“文件组”属性的值是可以重复的。

3.2.4 定义分区表

在SQL Server 2012 Management Studio的界面中，找到目标数据库下的“表”菜单，右键点击，选择“新建数据库表”，打开新建数据库表界面，新建一个分区表。如图5所示：

图5 新建数据库表

3.2.5 添加分区函数和分区架构

完成新建分区表后，我们就可以在分区表上添加分区函数和分区架构了。右键点击分区表，选择“存储”，然后选择“创建分区”，开始添加分区函数和分区架构，如图6所示：

图6 新建分区界面

点击“下一步”，如图7所示：

图7 选择分区列

在图7所示的界面中，勾选分区列，勾选完成后，选择“下一步”，如图8所示：

图8 填写分区函数

在图8所示的界面填写分区函数名称，填写完成后点击“下一步”，如图9所示：

图9 填写分区架构

在图9所示的界面中填写需要创建的分区架构的名称，填写完成后点击“下一步”，如图10所示：

图10 指定文件组

如图10所示，按照图示箭头步骤，一步步设置文件组参数。首先选择分区边界值划分在左边界分区还是右边界分区，然后进行第二步，设置分区所属文件组，再设置分区边界值（该值要与分区表的分区字段类型对应），最后点击“预计存储空间(E)”对其他参数进行自动填充。设置完成后点击“下一步”，如图11所示：

图11 脚本设置

在图11所示的界面中，根据实际需求完成最后的设置(一般不做设置)，然后点击“完成”，在下一个界面中再次点击“完成”，然后等待数据库执行操作，最后关闭界面。

分区完成后，右键点击分区表，选择“属性”，然后选择“存储”，打开如图12所示界面：

图12 表分区查看

从图12中可以看到数据库表已经完成分区。

3.3 动态添加分割点

要完成动态的向分区函数中添加分割点的功能，首先我们来理一下思路：首先我们要向文件组中添加一个新的文件或者直接添加一个新的文件组，添加完成后，需要修改分区架构，来告知数据库新分的分区数据存储到那个文件或者文件组中，最后在分区函数中添加新的分割点，完成动态添加分区分割点的功能。

根据这个思路，我编写的了一个存储过程，用于动态的添加分割点：

CREATE proc[dbo].[Demo_FileGroup_Add]
as
declare
@file_name varchar(20),--要添加的文件名称
@add_sql nvarchar(max)--在文件组下添加新文件的SQL语句

begin  

set @file_name='DemoFile'+left((convert(varchar,(DATEADD(yy, DATEDIFF(yy,0,(DATEADD(YY,1,GETDATE()))), 0)),120)),4)--动态拼接文件名
set @add_sql='
ALTER DATABASE Demo ADD FILE (
NAME='+@file_name+',
FILENAME=''D:\ProgramFiles\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'+@file_name+'.mdf'',
SIZE=5MB,
FILEGROWTH=5MB)
TO FILEGROUP DemoFileGroup'
--select @add_sql
Exec sp_executesql@add_sql--执行向文件组中添加文件的SQL语句

alter partitionscheme DemoPartitionSchemenext used 'DemoFileGroup' --告知数据库新建立的分区放在哪个文件组（修改分区架构）

alter partitionfunction DemoPartitionFunction() split range (CONVERT(VARCHAR,DATEADD(yy, DATEDIFF(yy,0,(DATEADD(YY,1,GETDATE()))), 0),120))--在分区函数中添加分割点

end

注意：在执行该存储过程之前，一定要确保文件组中没有即将添加的文件，并且在分区函数中，没有要添加的分割点，否则会报错，存储过程不能执行。

4、测试数据

4.1 添加测试数据

4.1.1 新建测试表

新建一个未分区的TestTable表，其表结构与分区表DemoTable表结构完全一致，代码如下：

CREATE TABLE[dbo].[TestTable](
 [demo_id][bigint],
 [demo_date][datetime2](0),
 [demo_desc][varchar](50)
)

4.1.2 编写T-SQL添加测试数据

T-SQL语句如下：

declare
@num bigint, --id
@test_date datetime2(0),--时间
@test_desc varchar(300),--描述
@count int--计数器
begin
set @num= 0 --设置初始id
set @test_date= '2015-01-01 00:00:00'--设置初始日期
set @test_desc='屈贾谊于长沙，无非明主；窜梁鸿于海曲，岂乏明时？'
while (@test_date<'2019-01-01 00:00:00') --设置日期上限
begin
     set@count = 0
     while(@count<10)--每个时间点添加10条数据
        begin
            insertinto dbo.DemoTable values(@num,@test_date,@test_desc+CAST(@count as varchar)) --添加数据
            set@count=@count+1 --计数自增
            set@num = @num +1 --id自增
        end
     set@test_date = DATEADD(MI,1,@test_date) --每一个时间点添加完10条数据后，时间自增1分
end
end

修改T-SQL语句中insert部分的表明，分别向两张表中添加测试数据，添加21038400行数据，结果如下：

图13 分区表插入数据耗时统计

图14 分区表存储信息

图15 普通表插入数据耗时统计

图16 普通表存储信息

从图13与图15中可以看出，同样插入21038400行数据，分区表耗时3740秒，普通表耗时3920秒，分区表快4.6%。考虑到运行环境对测试数据的影响，在此我们先对此数据不做评价，毕竟4.6%不是很明显。

而从图14与图16的对比中可以看出，分区表的索引空间为0.125MB，而普通表的索引空间为0.008MB。那么为什么分区表的索引空间要比普通表的索引空间大呢？其实这个问题个人理解大致是：创建分区表就是将数据存储在不同的文件中，然后数据库会根据分区函数和分区架构，以分区列为索引列，创建分区索引来管理数据存放的位置，所以分区表的索引空间理所当然会比普通标表的索引空间大。

默认情况下，分区表中创建的索引使用与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。

在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样SQL Server 不会将索引与表对齐。

在已分区的表上创建索引（分区索引）时，应该注意以下事项：

l 唯一索引

建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。

l 非唯一索引

对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。

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

5、补充说明

5.1 分区分表理解

分区分表分为垂直分区分表和水平分区分表，根据我自己查阅资料，总结如下：

垂直分区分表是在SQL Server 2005之前大量使用的，垂直分表相对很少见到和用到，因为这可能是数据库设计上的问题了。如果数据库中一张表有部分字段几乎从不不更改但经常查询，而部分字段的数据频繁更改，这种设计放到同一个表中就不合理了，相互影响太大了。在已存在改情况的表的时候，可以考虑按列拆分表，即垂直拆分。拆分完成后，通过分表之间设置外键关联，然后创建视图和触发器等对表进行增、删、改、查操作。

而水平分区分表是SQL Server2005之后被大量使用的。其原理就是将逻辑上的一个表，在物理上拆分，将数据存储在不同的文件组中，这个我们前边已经讲过了，这里就不在赘述。