標簽：style blog http java color 使用
一般情况下，我们建立數據庫表时，表数据都存放在一个文件里。
但是如果是分區表的話，表數據就會按照你指定的規則分放到不同的文件裏，把一個大的數據文件拆分爲多個小文件，還可以把這些小文件放在不同的磁盤下由多個cpu進行處理。這樣文件的大小隨著拆分而減小，還得到硬件系統的加強，自然對我們操作數據是大大有利的。
所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦，因为表分区会对數據庫产生不必要的开销，除啦性能还会增加实现对象的管理费用和复杂性。
先跟著做一個分區表（分爲11個分區），去除神秘的面紗，然後咱們再逐一擊破各個要點要害。
分区是要把一个表数据拆分为若干子集合，也就是把把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或这多个文件组，由于多个文件组可以提高數據庫的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，所以创建时建议分区跟文件组个数相同。
1.創建文件組
可以点击數據庫属性在文件组里面添加
T-sql語法：
alter database <數據庫名> add filegroup <文件組名>
---创建數據庫文件组 alter database testSplit add filegroup ByIdGroup1 alter database testSplit add filegroup ByIdGroup2 alter database testSplit add filegroup ByIdGroup3 alter database testSplit add filegroup ByIdGroup4 alter database testSplit add filegroup ByIdGroup5 alter database testSplit add filegroup ByIdGroup6 alter database testSplit add filegroup ByIdGroup7 alter database testSplit add filegroup ByIdGroup8 alter database testSplit add filegroup ByIdGroup9 alter database testSplit add filegroup ByIdGroup10
2.創建數據文件到文件組裏面
可以点击數據庫属性在文件里面添加
T-sql語法：
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 testSplit add file (name=N‘ById1‘,filename=N‘J:\Work\數據庫\data\ById1.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup1 alter database testSplit add file (name=N‘ById2‘,filename=N‘J:\Work\數據庫\data\ById2.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup2 alter database testSplit add file (name=N‘ById3‘,filename=N‘J:\Work\數據庫\data\ById3.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup3 alter database testSplit add file (name=N‘ById4‘,filename=N‘J:\Work\數據庫\data\ById4.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup4 alter database testSplit add file (name=N‘ById5‘,filename=N‘J:\Work\數據庫\data\ById5.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup5 alter database testSplit add file (name=N‘ById6‘,filename=N‘J:\Work\數據庫\data\ById6.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup6 alter database testSplit add file (name=N‘ById7‘,filename=N‘J:\Work\數據庫\data\ById7.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup7 alter database testSplit add file (name=N‘ById8‘,filename=N‘J:\Work\數據庫\data\ById8.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup8 alter database testSplit add file (name=N‘ById9‘,filename=N‘J:\Work\數據庫\data\ById9.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup9 alter database testSplit add file (name=N‘ById10‘,filename=N‘J:\Work\數據庫\data\ById10.ndf‘,size=5Mb,filegrowth=5mb) to filegroup ByIdGroup10
执行完成后，右键數據庫看文件组跟文件里面是不是多出来啦这些文件组跟文件。
3.使用向導創建分區表
右键到要分区的表--- >> 存储 --- >> 创建分区 --- >>显示向导视图 --- >> 下一步 --- >> 下一步。。
這裏舉例說下選擇列的意思：
假如你選擇的是int類型的列：那麽你的分區可以指定爲1--100W是一個分區，100W--200W是一個分區....
假如你選擇的是datatime類型：那麽你的分區可以指定爲：2014-01-01--2014-01-31一個分區，2014-02-01--2014-02-28一個分區...
根据这样的列数据规则划分，那么在那个区间的数据，在插入數據庫时就被指向那个分区存储下来。
我这里选用orderid int类型 --- >> 下一步 --- >>
左邊界右邊界：就是把臨界值劃分給上一個分區還是下一個分區。一個小于號，一個小于等于號。
然後下一步下一步最後你會得到分區函數和分區方案。
USE [testSplit] GO BEGIN TRANSACTION --創建分區函數 CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N‘1000000‘, N‘2000000‘, N‘3000000‘, N‘4000000‘, N‘5000000‘, N‘6000000‘, N‘7000000‘, N‘8000000‘, N‘9000000‘, N‘10000000‘) --創建分區方案 CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10]) --創建分區索引 CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] ( [OrderId] )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId]) --刪除分區索引 DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF ) COMMIT TRANSACTION
執行上面向導生成的語句。分區完成。。
4.秀一下速度。
首先我在表中插入啦1千萬行數據。給表分啦11個分區。前十個分區裏面一個是100W條數據。。
說兩句：
可見反常現象，掃描次數跟邏輯讀取次數都是無分區表的2倍之多，但查詢速度卻是快啦不少啊。這就是分區的神奇之處啊，所以要相信這世界一切皆有可能。
1.分區函數
指定分依據區列（依據列唯一），分區數據範圍規則，分區數量，然後將數據映射到一組分區上。
創建語法：
create partition function 分區函數名(<分區列類型>) as range [left/right] for values (每個分區的邊界值,....)
--創建分區函數 CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N‘1000000‘, N‘2000000‘, N‘3000000‘, N‘4000000‘, N‘5000000‘, N‘6000000‘, N‘7000000‘, N‘8000000‘, N‘9000000‘, N‘10000000‘)
然而，分區函數只定義了分區的方法，此方法具體用在哪個表的那一列上，則需要在創建表或索引是指定。
刪除語法：
--刪除分區語法 drop partition function <分區函數名>
--删除分区函数 bgPartitionFun drop partition function bgPartitionFun
需要注意的是，只有沒有應用到分區方案中的分區函數才能被刪除。
2.分區方案
指定分區對應的文件組。
創建語法：
--創建分區方案语法 create partition scheme <分區方案名稱> as partition <分區函數名称> [all]to (文件組名称,....)
--創建分區方案,所有分区在一个组里面 CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1])
分區函數必須關聯分區方案才能有效，然而分區方案指定的文件組數量必須與分區數量一致，哪怕多個分區存放在一個文件組中。
刪除語法：
--刪除分區方案語法 drop partition scheme<分區方案名稱>
--删除分区方案 bgPartitionSchema drop partition scheme bgPartitionSchema1
只有沒有分區表，或索引使用該分區方案是，才能對其刪除。
3.分區表
創建語法：
--創建分區表語法 create table <表名> ( <列定義> )on<分區方案名>(分區列名)
--創建分區表 create table BigOrder ( OrderId int identity, orderNum varchar(30) not null, OrderStatus int not null default 0, OrderPayStatus int not null default 0, UserId varchar(40) not null, CreateDate datetime null default getdate(), Mark nvarchar(300) null )on bgPartitionSchema(OrderId)
如果在表中創建主鍵或唯一索引，則分區依據列必須爲該列。
4.分區索引
創建語法：
--創建分區索引语法 create <索引分類> index <索引名稱> on <表名>(列名) on <分區方案名>(分區依據列名)
--創建分區索引 CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] ( [OrderId] )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])
使用分區索引查詢，可以避免多個cpu操作多個磁盤時産生的沖突。
這裏的語法，我就不寫啦，自己看語句分析吧。簡單的很。。
1.查看分區依據列的指定值所在的分區
--查詢分區依據列爲10000014的數據在哪個分區上 select $partition.bgPartitionFun(2000000) --返回值是2，表示此值存在第2個分區
2.查看分區表中，每個非空分區存在的行數
--查看分區表中，每個非空分區存在的行數 select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount from bigorder group by $partition.bgPartitionFun(orderid)
3.查看指定分區中的數據記錄
---查看指定分區中的數據記錄 select * from bigorder where $partition.bgPartitionFun(orderid)=2
結果：數據從1000001開始到200W結束
1.拆分分區
在分區函數中新增一個邊界值，即可將一個分區變爲2個。
--分區拆分 alter partition function bgPartitionFun() split range(N‘1500000‘) --將第二個分區拆爲2個分區
注意：如果分區函數已經指定了分區方案，則分區數需要和分區方案中指定的文件組個數保持對應一致。
2.合並分區
與拆分分區相反，去除一個邊界值即可。
--合並分區 alter partition function bgPartitionFun() merge range(N‘1500000‘) --將第二第三分區合並
3.分區中的數據移動
你或許會遇到這樣的需求，將普通表數據複制到分區表中，或者將分區表中的數據複制到普通表中。
那麽移動數據這兩個表，則必須滿足下面的要求。
1.創建表時指定文件組
--創建表 create table <表名> ( <列定義> )on <文件組名>
2.從分區表中複制數據到普通表
--將bigorder分區表中的第一分區數據複制到普通表中 alter table bigorder switch partition 1 to <普通表名>
3.從普通標中複制數據到分區表中
這裏要注意的是要先將分區表中的索引刪除，即便普通表中存在跟分區表中相同的索引。
--將普通表中的數據複制到bigorder分區表中的第一分區 alter table <普通表名> switch to bigorder partition 1
分區視圖是先建立带有字段约束的相同表,而约束不同,例如,第一个表的id约束为0--100W,第二表为101万到200万.....依次类推。
创建完一系列的表之后，用union all 连接起来创建一个视图，这个视图就形成啦分区视同。
很简单的，这里我主要是说分区表，就不说分區視圖啦。。
剛一看，不明白爲什麽有兩個反對，反思中。。。
如果你點啦反對，麻煩你把理由給留下來，我給提供技術支持，thanks。。
SQL Server表分区详解,码迷,mamicode.com
標簽：style blog http java color 使用
原文地址：http://www.cnblogs.com/knowledgesea/p/3696912.html
