SQL SERVER 表分区技术

当一个数据库表的数据达到千万级别的时候，这时候磁盘的I/O就成了性能的瓶颈，因此如果能提高磁盘的I/O能力，也就相应的提高了数据库的效率。SQL Server推出了表分区技术。

表分区就是把一个数据库表文件拆分成多个文件，并放到不同的文件组甚至是不同的磁盘中，但是在外界访问时看到的还是一个数据库表，这样不同的磁盘不同的文件组可以提高并发访问效率，而对于开发技术人员来说面对还只是那一张表，和单表没有区别。

表分区带来的优点：

1、提高I/O效率，插入、查询的效率都大大的得到提升，提高性能，这也是主要的优点。

2、提高容灾能力，当一个分区数据出现问题时，不会影响其他分区内容。

3、备份管理更方便，可以单独对分区文件进行备份。

表分区的实现也比较简单，主要分为三点：

1、创建分区函数。

2、创建分区架构。

3、创建分区表。

第一步，创建分区函数：

CREATE PARTITION FUNCTION [fnPartition](INT) AS RANGE LEFT FOR VALUES (1, 2)

　　

[fnPartition]是方法名，后边是分区依据列的类型，然后有一个RANGE这里可以写left和right，这个值代表临界值归属那边，VALUES就是分区的临界值。例子中写的left，那么如果插入的数据中，分区依据列的值为1或者小于1的，那么就归属到第一个数据文件中，值为2的归属到第二个文件中，大于2的就属于第三个数据文件。这里的1、2可以是任何数据，时间也可以，自己规定好即可。

第二步，创建分区架构：

在创建分区架构之前我们要先创建分区的各个数据文件。创建方法如下：

ALTER DATABASE Test ADD FILEGROUP fg1

　　

然后我们再把数据文件放到文件组中：

ALTER DATABASE Test ADD FILE 
(NAME=N'fg1',FILENAME=N'F:\Test\fg1.ndf',SIZE=5MB,FILEGROWTH=5MB)
TO FILEGROUP fg1

　　

我们建立三个文件和文件组，这里不再重复上述代码，下面就要开始创建分区架构了：

CREATE PARTITION SCHEME [SchemaForParirion] AS PARTITION [fnPartition] TO ([fg1], [fg2], [fg3])

　　

[SchemaForParirion]是架构名称，[fnPartition]是我们前边创建的分区函数，fg1、2、3是分区存储的三个数据文件。

第三步，创建分区表：

CREATE TABLE [dbo].[TestTable](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[ProvinceId] [INT] NOT NULL,
[Data] [NVARCHAR](50) NULL
)ON SchemaForParirion(ProvinceId)

　　

新建数据库表，我们都会，但是这里需要注意，要添加一个指定该表为分区表的操作，ON SchemaForParirion(ProvinceId)，这个SchemaForParirion是我们刚刚定义的分区架构，参数为分区列。

这里还有一点需要注意：如果在表中创建主键或唯一索引，则分区依据列必须为该列。所以我们如果不是用主键或者索引来进行分区的话，在建表的时候就要主意不要给表设置主键或索引。当然索引能够提高查询效率，具体情况还需要具体考虑。

还有几个方法需要记录一下：

1、查看分区依据列的指定值所在的分区，例如，查询省Id是2的存储在第几个分区，语句如下：

SELECT  $partition.fnPartition(2)

　　

结果为：2.就是说省Id为2的数据在存储的时候是存储到了第二个数据库文件中。

2、查看分区表中，每个非空分区存在的行数：

SELECT  $partition.fnPartition(ProvinceId) AS Num ,
        COUNT(*) AS recordCount
FROM    TestTable
GROUP BY $partition.fnPartition(ProvinceId)

　　

结果显示三个ProvinceId各个数据库文件的数据量，都是1千万。

3、查看指定分区中的数据记录，查询省Id为1的数据：

SELECT  *
FROM    TestTable
WHERE   $partition.fnPartition(ProvinceId) = 1

　　

好了下边该看表分区的成果了。TestTable表进行了分区处理，数据按照省Id（ProvinceId）等于1、2、3分别存储到了三个文件里面，每个文件里1千万数据，一共3千万。这里直接写插入语句就行，SQL Server会自己判断存储文件是哪个。TestTable_1表的结构和TestTable一样，没有做分区处理，存储了3千万的数据，省Id都是123。接下来我们看一下查询结果对比，结果只做参考，查询语句如下：

SELECT TOP 10000 * FROM dbo.TestTable where ProvinceId = 1 AND Id>0
SELECT TOP 10000 * FROM dbo.TestTable where ProvinceId = 2 AND Id>10000000
SELECT TOP 10000 * FROM dbo.TestTable where ProvinceId = 3 AND Id>20000000

　　

从TestTable_1表中读取数据的语句如下：

SELECT TOP 10000 * FROM dbo.TestTable_1 WHERE ProvinceId = 123 AND Id>0
SELECT TOP 10000 * FROM dbo.TestTable_1 WHERE ProvinceId = 123 AND Id>10000000
SELECT TOP 10000 * FROM dbo.TestTable_1 WHERE ProvinceId = 123 AND Id>20000000

　　

虽然语句的对比性不是很强烈，但是我们可以看到，分区表的读取速度确实是比普通表的要快。所以对于单表大数据的问题，我们可以尝试着使用这种方式来处理。
————————————————
版权声明：本文为CSDN博主「二月十六」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sinat_28984567/article/details/79840051