masyarakat Belajar Perpustakaan Alatan Masa lapang
cari
Melayu
Rumah > pangkalan data > tutorial mysql > SqlServer 自动化分区方案

SqlServer 自动化分区方案

WBOY
Lepaskan： 2016-06-07 15:40:11
asal
1207 orang telah melayarinya

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

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

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

　　本文是在SqlServer2012 下完成的。

　　过程：

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

　　SqlServer 自动化分区方案

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

     SqlServer 自动化分区方案

　　

　　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>)
Salin selepas log masuk

   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>)
Salin selepas log masuk

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

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

<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>
Salin selepas log masuk

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

　　查看某分区的数据：

<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>
Salin selepas log masuk

　　查询结果如下图：

SqlServer 自动化分区方案

 

至此，分区似乎已经结束了。但是看看后一个分区里的数据：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>)
Salin selepas log masuk

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

SqlServer 自动化分区方案

 

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

<br><br>
Salin selepas log masuk
Label berkaitan：
sqlserver kira-kira Pembahagian nombor rancangan automasi
sumber：php.cn
Artikel sebelumnya：EntityFramework 多数据库链接,MySql,SqlServer,Oracel等 Artikel seterusnya：MySQL中进行树状所有子节点的查询
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Artikel terbaru oleh pengarang
Isu terkini
SQL Server: Menggunakan ungkapan CASE WHEN dengan klausa WHERE Saya mempunyai projek SpringBoot baharu dengan SQLServer dan saya perlu menggantikan perta...
daripada 2024-04-03 14:46:55
0
1
351
Tidak dapat menyambung ke SQL Server dalam Laravel Saya tidak boleh menyambung ke pelayan sql dalam Laravel. Saya telah memberikan nilai konf...
daripada 2024-03-31 13:08:48
0
1
395
Topik-topik yang berkaitan
Lagi>
Cadangan popular
Tutorial Popular
Lagi>
Tutorial berkaitan
Cadangan popular
Kursus terkini
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan