彙總篇:http://www.cnblogs.com/dunitian/p/4822808.html#tsql 第一次引入文件組的概念:http://www.cnblogs.com/dunitian/p/5276431.html 上次說了其他的解決方案(http://www.cnblogs.com/ ...
彙總篇:http://www.cnblogs.com/dunitian/p/4822808.html#tsql
第一次引入文件組的概念:http://www.cnblogs.com/dunitian/p/5276431.html
上次說了其他的解決方案(http://www.cnblogs.com/dunitian/p/6041745.html),就是沒有說水平分庫,這次好好說下。
上次共用的第一份大數據,這次正好來演示一下水平分庫
1.模擬部分數據
2.創建索引後,發現可以根據日期來分組
按數據量大致分一下
步入正軌
---------------------------------------------------------------------
GUI方法:
3.0創建文件組
添加文件到文件組
命令操作:
alter database BigData_TestInfo_PartialData add filegroup Info
alter database BigData_TestInfo_PartialData add file(name=N'TestInfo2006',filename=N'G:\SQLData\BigData_TestInfo2006.ndf') to filegroup Info
註意:BigData_TestInfo2006.ndf是資料庫自己創建的,不需要自己手動創建(有些同志手動創建了,然後報錯。。。。呃,有點哭笑不得了)
查詢看看:select * from sys.filegroups
水平分區走起:一般就幾步,1.創建分區函數 2.創建分區方案 3.創建分區表
GUI方法
分區函數
分區方案
上一張圖有些人可能不懂,用PPT畫張概念圖:
創建腳本
系統生成腳本:
use [BigData_TestInfo_PartialData] go begin transaction create partition function [CreatedatePartitionFun](varchar(10)) as range right for values(N'2006-01-01', N'2007-01-01', N'2009-01-01', N'2012-01-01') create partition scheme [CreatedatePartitionScheme] as partition [CreatedatePartitionFun] TO ([Info], [Info], [Info], [Info], [primary]) alter table [dbo].[Info] drop constraint [PK__Info__3214EC07B2FE10C8] alter table [dbo].[Info] add primary key nonclustered ( [Id] asc )with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] set ansi_padding on create clustered index [ClusteredIndex_on_CreatedatePartitionScheme_636193166313125124] on [dbo].[Info] ( [CreateDate] )with (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [CreatedatePartitionScheme]([CreateDate]) drop index [ClusteredIndex_on_CreatedatePartitionScheme_636193166313125124] on [dbo].[Info] commit transaction go
命令方式創建(根據上面生成的命令逆推)
創建分區函數和架構(方案)
create partition function CreatedatePartitionFun(varchar(10)) as range right for values(N'2006-01-01', N'2007-01-01', N'2009-01-01', N'2012-01-01')
create partition scheme CreatedatePartitionScheme as partition [CreatedatePartitionFun] TO ([Info], [Info], [Info], [Info], [primary])
創建分區表
尚未創建表的情況
已經創建了表(基本上都是這種情況)
主要就兩步,把主鍵變為非聚集索引+創建分區聚集索引
alter table Info drop constraint PK__Info__3214EC064B338648
alter table Info add constraint PK_Info_Id primary key nonclustered (Id asc)
create clustered index IX_Info_CreateDate on Info(CreateDate) on CreatedatePartitionScheme(CreateDate)
測試:基本上是均勻分散在各個文件中,生產環境的時候可以把這些文件放各個磁碟
參考文章:
http://www.cnblogs.com/gaizai/p/3582024.html
http://www.cnblogs.com/lyhabc/p/3480917.html
http://www.cnblogs.com/libingql/p/4087598.html
http://www.cnblogs.com/CareySon/p/3252670.html
http://database.51cto.com/art/201009/225448.htm
http://www.cnblogs.com/knowledgesea/p/3696912.html
http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html
http://www.cnblogs.com/lykbk/p/erererert343243434388773437878.html