SqlServer2008R2 分區管理

标簽（空格分隔）： .Net

• SqlServer2008R2 分區管理
  • 分區的背景
  • 分區的優劣
  • 分區操作
    • 建立檔案與檔案組
    • 建立分區函數與分區方案
    • 映射資料
    • 檢視分區
      • 參看每個分區的總資料
      • 檢視某個分區的流水
      • 檢視一筆資料屬于哪個分區
    • 删除分區

分區的背景

當資料量太大時，可以通過資料分區來提升效率

分區的優劣

優勢

• 提供性能：這個是大多人數分區的目的，把一個表分部到不同的硬碟或其他存儲媒體中，提高并發，會大大提升查詢速度。
• 提高穩定性：當一個分區出了問題，不會影響其他分區，僅僅是目前壞的分區不可用。
• 便于管理：把一個大表分成若幹個小表，則備份和恢複的時候不再需要備份整個表，可以單獨備份分區。
• 存檔：将一些不太常用的資料，單獨存放。如：将1年前的資料記錄分到一個專門的存檔伺服器存放。

劣勢

• 跨分區的查詢資料處理不好速度會減慢

分區操作

建立檔案與檔案組

一個檔案隻可以對應一個檔案組，一個檔案組可以對應多個檔案，最終資料是相對于檔案組操作，這樣資料操作更簡單，而一個檔案組中包含多個檔案的話，資料插入是按初始比例插入

查詢效率：單一檔案組多分區檔案 > 多檔案組多分區檔案 >單一檔案組單一分區檔案 > 預設

參考别人連結：https://www.cnblogs.com/knowledgesea/p/3696912.html

文法：alter database <資料庫名> add filegroup <檔案組名>
alter database FenQuTest add filegroup Group1
alter database FenQuTest add filegroup Group2
alter database FenQuTest add filegroup Group3

文法：alter database <資料庫名稱> add file <資料辨別> to filegroup <檔案組名稱>

--<資料辨別> （name:檔案名，fliename:實體路徑檔案名，size:檔案初始大小kb/mb/gb/tb，filegrowth:檔案自動增量kb/mb/gb/tb/%,maxsize:檔案可以增加到的最大大小kb/mb/gb/tb/unlimited）

alter database FenQuTest add file (name=N'g1',filename=N'C:\fq\g1.ndf',size=5Mb,filegrowth=5mb) to filegroup Group1
alter database FenQuTest add file (name=N'g2',filename=N'C:\fq\g2.ndf',size=5Mb,filegrowth=5mb) to filegroup Group2 
alter database FenQuTest add file (name=N'g3',filename=N'C:\fq\g3.ndf',size=5Mb,filegrowth=5mb) to filegroup Group3
           

建立分區函數與分區方案

分區函數： 以時間（datetime）分割來建立分區方案，等于分割資料的資料歸右邊分區（RANGE RIGHT）

CREATE PARTITION FUNCTION [OrderInfo_SubareaFun](datetime) AS RANGE RIGHT FOR VALUES (
N'2017-07-01T00:00:00',N'2017-08-01T00:00:00', N'2017-09-01T00:00:00')
           

分區方案

CREATE PARTITION SCHEME [OrderInfo_SubareaSCHEME] AS PARTITION [OrderInfo_SubareaFun] TO ([PRIMARY],[Group1],[Group2],[Group3])   
           

映射資料

映射資料：確定表中不含聚集索引

–删除表中的聚集索引

ALTER TABLE [dbo].[Test] DROP CONSTRAINT [PK__Test_Id]

ALTER TABLE [dbo].[Test] ADD CONSTRAINT [PK__Test_Id] 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]

--映射資料
CREATE CLUSTERED INDEX [ClusteredIndex_on_Test_SubareaPlan] ON [dbo].[Test] 
(
    [CreateOn]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [OrderInfo_SubareaSCHEME]([CreateOn])
           

檢視分區

參看每個分區的總資料

SELECT $PARTITION.[OrderInfo_SubareaFun](CreateOn) AS Partition, 
COUNT(*) AS [COUNT]    
FROM dbo.Test    
GROUP BY $PARTITION.[OrderInfo_SubareaFun](CreateOn)    
ORDER BY Partition ;
           

檢視某個分區的流水

SELECT * 
FROM dbo.Test 
WHERE $PARTITION.[OrderInfo_SubareaFun](CreateOn) =3
           

檢視一筆資料屬于哪個分區

select $partition.[OrderInfo_SubareaFun]('2017-9-1') 
           

删除分區

alter partition function OrderInfo_SubareaFun()

merge range(N’1500000’)