SqlServer 自动化分区方案

本文是我关于数据库分区的方案的一些想法，或许有些问题。仅供大家讨论。SqlServer (SqlServer 2005\SqlServer 2008)实现分区需要在企业版下进行. SqlServer的分区分为大致有以下个过程：1、创建文件组用以存放数据文件 2、创建文件组用户数据文件 3、创建分

　　本文是我关于数据库分区的方案的一些想法，或许有些问题。仅供大家讨论。SqlServer (SqlServer 2005\SqlServer 2008)实现分区需要在企业版下进行.

　　SqlServer的分区分为大致有以下个过程：1、创建文件组用以存放数据文件 2、创建文件组用户数据文件 3、创建分区函数 4、创建分区方案  5、在分区方案下创建表

　　本文是在SqlServer2012 下完成的。

　　过程：

      1、新建数据库，在属性中创建文件以及文件组。如下图：

　　

　　可以在下图中选择文件组、或者新建文件组用户存放上图中新建的文件：

     

　　

　　2、创建分区函数

<span>CREATE</span> PARTITION <span>FUNCTION</span> <span>[</span><span>partitionById</span><span>]</span>(<span>int</span><span>) 
</span><span>AS</span> RANGE <span>LEFT</span> <span>FOR</span> <span>VALUES</span> (<span>100</span>, <span>200</span>, <span>300</span>)

   3、创建分区方案

<span>CREATE</span> PARTITION SCHEME <span>[</span><span>partitionSchemeById</span><span>]</span> 
<span>AS</span> PARTITION <span>[</span><span>partitionById</span><span>]</span> --分区函数
<span>TO</span> (<span>[</span><span>FileGroup1</span><span>]</span>, <span>[</span><span>FileGroup2</span><span>]</span>,  <span>[</span><span>FileGroup3</span><span>]，[FileGroup4]</span>)

　　注意以上分区函数使用的是LEFT ,根据后面的值指明了数据库中如何存放。以上存放方式为：-∞，100],(100,200],(200,300],(300,+∞）.此分区方案是依据分区函数

<span>partitionById 创建的。那就是说以上Id的存储区间分别被放在</span>[FileGroup1], [FileGroup2],  [FileGroup3]，[FileGroup4]文件组的文件中。<br><br>4、依据分区方案创建表<br><br>

<span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Account</span><span>]</span><span>(
    </span><span>[</span><span>Id</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>Name</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>20</span>) <span>NULL</span><span>,
    </span><span>[</span><span>Password</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>20</span>) <span>NULL</span><span>,
    </span><span>[</span><span>CreateTime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>
) </span><span>ON</span> <span>partitionSchemeById(Id)</span>

　　注意：创建表的脚本中需要指明分区方案和分区依据列

　　查看某分区的数据：

<span>SELECT</span> <span>*</span> <span>FROM</span> 
<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Account</span><span>]</span>
<span>WHERE</span> $PARTITION.<span>[</span><span>partitionById</span><span>]</span>(Id)<span>=</span><span>1</span>

　　查询结果如下图：

 

至此，分区似乎已经结束了。但是看看后一个分区里的数据：Id>=400的全部放在了一个数据文件中。这样在有可能瓶颈就发生在了这个分区中。

如果数据不停的增长，希望分区也不断的自动增加。如：每天生成一个新的分区来存放分区新的数据。如到第二天时，新生成一个分区来存放(400，500 ]的数据。

这里我采用了Sql Job的方式来自动产生分区：

 

<span>DECLARE</span> <span>@maxValue</span> <span>INT</span><span>,
    </span><span>@secondMaxValue</span> <span>INT</span><span>,
    </span><span>@differ</span>    <span>INT</span><span>,
    </span><span>@fileGroupName</span> <span>VARCHAR</span>(<span>200</span><span>),
    </span><span>@fileNamePath</span>    <span>VARCHAR</span>(<span>200</span><span>),
    </span><span>@fileName</span>   <span>VARCHAR</span>(<span>200</span><span>),
    </span><span>@sql</span>        <span>NVARCHAR</span>(<span>1000</span><span>)


</span><span>SET</span> <span>@fileGroupName</span><span>=</span><span>'</span><span>FileGroup</span><span>'</span><span>+</span><span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>varchar</span>, <span>GETDATE</span>(), <span>120</span> ),<span>'</span><span>-</span><span>'</span>,<span>''</span>),<span>'</span> <span>'</span>,<span>''</span>),<span>'</span><span>:</span><span>'</span>,<span>''</span><span>) 
</span><span>PRINT</span> <span>@fileGroupName</span>
<span>SET</span> <span>@sql</span><span>=</span><span>'</span><span>ALTER DATABASE [Test] ADD FILEGROUP </span><span>'</span><span>+</span><span>@fileGroupName</span>
<span>PRINT</span> <span>@sql</span>
<span>EXEC</span>(<span>@sql</span><span>)

</span><span>SET</span> <span>@fileNamePath</span><span>=</span><span>'</span><span>C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLINSTANCE\MSSQL\DATA\</span><span>'</span><span>+</span><span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>varchar</span>, <span>GETDATE</span>(), <span>120</span> ),<span>'</span><span>-</span><span>'</span>,<span>''</span>),<span>'</span> <span>'</span>,<span>''</span>),<span>'</span><span>:</span><span>'</span>,<span>''</span>) <span>+</span><span>'</span><span>.NDF</span><span>'</span>
<span>SET</span> <span>@fileName</span><span>=</span>N<span>'</span><span>File</span><span>'</span><span>+</span><span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>varchar</span>, <span>GETDATE</span>(), <span>120</span> ),<span>'</span><span>-</span><span>'</span>,<span>''</span>),<span>'</span> <span>'</span>,<span>''</span>),<span>'</span><span>:</span><span>'</span>,<span>''</span><span>) 

</span><span>SET</span> <span>@sql</span><span>=</span><span>'</span><span>ALTER DATABASE [Test] ADD FILE (NAME=</span><span>'''</span><span>+</span><span>@fileName</span><span>+</span><span>'''</span><span>,FILENAME=N</span><span>'''</span><span>+</span><span>@fileNamePath</span><span>+</span><span>'''</span><span>) TO FILEGROUP</span><span>'</span><span>+</span><span>'</span>    <span>'</span><span>+</span><span>@fileGroupName</span>
<span>PRINT</span> <span>@sql</span>
<span>PRINT</span> <span>1</span>
<span>EXEC</span>(<span>@sql</span><span>)
</span><span>PRINT</span> <span>2</span>

<span>--</span><span>修改分区方案，用一个新的文件组用于存放下一新增的数据</span>
<span>SET</span> <span>@sql</span><span>=</span><span>'</span><span>ALTER PARTITION SCHEME [partitionSchemeById] NEXT USED</span><span>'</span><span>+</span><span>'</span>    <span>'</span><span>+</span><span>@fileGroupName</span>
<span>EXEC</span>(<span>@sql</span><span>)
  </span><span>--</span><span>分区架构</span>
<span>PRINT</span> <span>3</span> 
<span>SELECT</span> <span>@maxValue</span> <span>=</span><span>CONVERT</span>(<span>INT</span>,<span>MAX</span><span>(value))
</span><span>FROM</span><span> SYS.PARTITION_RANGE_VALUES PRV

</span><span>SELECT</span> <span>@secondMaxValue</span> <span>=</span> <span>CONVERT</span>(<span>INT</span>,<span>MIN</span><span>(value))
</span><span>FROM</span><span> 
(
    </span><span>SELECT</span> <span>TOP</span> <span>2</span> <span>*</span> <span>FROM</span> SYS.PARTITION_RANGE_VALUES <span>ORDER</span> <span>BY</span> VALUE <span>DESC</span><span>
)
 PRV 

</span><span>SET</span> <span>@differ</span><span>=</span><span>@maxValue</span> <span>-</span> <span>@secondMaxValue</span> 


<span>ALTER</span> PARTITION <span>FUNCTION</span> partitionById()  <span>--</span><span>分区函数</span>
SPLIT RANGE (<span>@maxValue+</span><span>@differ</span>) 

这样在计划里指定每天什么时候运行，下图：

 

参考：http://www.cnblogs.com/lyhabc/articles/2623685.html

<br><br>