什么是表分區(qū)一般情況下，我們建立數(shù)據(jù)庫表時，表數(shù)據(jù)都存放在一個文件里。 但是如果是分區(qū)表的話，表數(shù)據(jù)就會按照你指定的規(guī)則分放到不同的文件里,，把一個大的數(shù)據(jù)文件拆分為多個小文件，還可以把這些小文件放在不同的磁盤下由多個cpu進(jìn)行處理,。這樣文件的大小隨著拆分而減小,，還得到硬件系統(tǒng)的加強(qiáng)，自然對我們操作數(shù)據(jù)是大大有利的,。 所以大數(shù)據(jù)量的數(shù)據(jù)表,，對分區(qū)的需要還是必要的，因?yàn)樗梢蕴岣遱elect效率,，還可以對歷史數(shù)據(jù)經(jīng)行區(qū)分存檔等,。但是數(shù)據(jù)量少的數(shù)據(jù)就不要湊這個熱鬧啦，因?yàn)楸矸謪^(qū)會對數(shù)據(jù)庫產(chǎn)生不必要的開銷,，除啦性能還會增加實(shí)現(xiàn)對象的管理費(fèi)用和復(fù)雜性,。 跟著做，分區(qū)如此簡單先跟著做一個分區(qū)表（分為11個分區(qū)）,，去除神秘的面紗,，然后咱們再逐一擊破各個要點(diǎn)要害,。 分區(qū)是要把一個表數(shù)據(jù)拆分為若干子集合，也就是把把一個數(shù)據(jù)文件拆分到多個數(shù)據(jù)文件中,，然而這些文件的存放可以依托一個文件組或這多個文件組,，由于多個文件組可以提高數(shù)據(jù)庫的訪問并發(fā)量，還可以把不同的分區(qū)配置到不同的磁盤中提高效率,，所以創(chuàng)建時建議分區(qū)跟文件組個數(shù)相同,。 1.創(chuàng)建文件組 可以點(diǎn)擊數(shù)據(jù)庫屬性在文件組里面添加 T-sql語法： alter database <數(shù)據(jù)庫名> add filegroup <文件組名>  ---創(chuàng)建數(shù)據(jù)庫文件組 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.創(chuàng)建數(shù)據(jù)文件到文件組里面 可以點(diǎn)擊數(shù)據(jù)庫屬性在文件里面添加 T-sql語法： alter database <數(shù)據(jù)庫名稱> add file <數(shù)據(jù)標(biāo)識> to filegroup <文件組名稱> --<數(shù)據(jù)標(biāo)識> （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\數(shù)據(jù)庫\data\ById1.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup1 alter database testSplit add file (name=N'ById2',filename=N'J:\Work\數(shù)據(jù)庫\data\ById2.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup2 alter database testSplit add file (name=N'ById3',filename=N'J:\Work\數(shù)據(jù)庫\data\ById3.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup3 alter database testSplit add file (name=N'ById4',filename=N'J:\Work\數(shù)據(jù)庫\data\ById4.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup4 alter database testSplit add file (name=N'ById5',filename=N'J:\Work\數(shù)據(jù)庫\data\ById5.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup5 alter database testSplit add file (name=N'ById6',filename=N'J:\Work\數(shù)據(jù)庫\data\ById6.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup6 alter database testSplit add file (name=N'ById7',filename=N'J:\Work\數(shù)據(jù)庫\data\ById7.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup7 alter database testSplit add file (name=N'ById8',filename=N'J:\Work\數(shù)據(jù)庫\data\ById8.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup8 alter database testSplit add file (name=N'ById9',filename=N'J:\Work\數(shù)據(jù)庫\data\ById9.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup9 alter database testSplit add file (name=N'ById10',filename=N'J:\Work\數(shù)據(jù)庫\data\ById10.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup10 執(zhí)行完成后，右鍵數(shù)據(jù)庫看文件組跟文件里面是不是多出來啦這些文件組跟文件,。 3.使用向?qū)?chuàng)建分區(qū)表 右鍵到要分區(qū)的表--- >> 存儲 --- >> 創(chuàng)建分區(qū) --- >>顯示向?qū)б晥D --- >> 下一步 --- >> 下一步,。。
這里舉例說下選擇列的意思： 假如你選擇的是int類型的列：那么你的分區(qū)可以指定為1--100W是一個分區(qū),，100W--200W是一個分區(qū).... 假如你選擇的是datatime類型：那么你的分區(qū)可以指定為：2014-01-01--2014-01-31一個分區(qū),，2014-02-01--2014-02-28一個分區(qū)... 根據(jù)這樣的列數(shù)據(jù)規(guī)則劃分，那么在那個區(qū)間的數(shù)據(jù),，在插入數(shù)據(jù)庫時就被指向那個分區(qū)存儲下來,。
我這里選用orderid int類型 --- >> 下一步 --- >>
左邊界右邊界：就是把臨界值劃分給上一個分區(qū)還是下一個分區(qū),。一個小于號,，一個小于等于號。 然后下一步下一步最后你會得到分區(qū)函數(shù)和分區(qū)方案,。 USE [testSplit] GO BEGIN TRANSACTION --創(chuàng)建分區(qū)函數(shù) 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') --創(chuàng)建分區(qū)方案 CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10]) --創(chuàng)建分區(qū)索引 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]) --刪除分區(qū)索引 DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF ) COMMIT TRANSACTION 執(zhí)行上面向?qū)傻恼Z句,。分區(qū)完成。,。 4.秀一下速度,。 首先我在表中插入啦1千萬行數(shù)據(jù)。給表分啦11個分區(qū),。前十個分區(qū)里面一個是100W條數(shù)據(jù),。。
說兩句： 可見反?，F(xiàn)象,，掃描次數(shù)跟邏輯讀取次數(shù)都是無分區(qū)表的2倍之多，但查詢速度卻是快啦不少啊,。這就是分區(qū)的神奇之處啊,，所以要相信這世界一切皆有可能。 分區(qū)函數(shù),，分區(qū)方案,，分區(qū)表,，分區(qū)索引1.分區(qū)函數(shù) 指定分依據(jù)區(qū)列（依據(jù)列唯一），分區(qū)數(shù)據(jù)范圍規(guī)則,，分區(qū)數(shù)量,，然后將數(shù)據(jù)映射到一組分區(qū)上。 創(chuàng)建語法： create partition function 分區(qū)函數(shù)名(<分區(qū)列類型>) as range [left/right] for values (每個分區(qū)的邊界值,....) --創(chuàng)建分區(qū)函數(shù) 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') 然而,，分區(qū)函數(shù)只定義了分區(qū)的方法,，此方法具體用在哪個表的那一列上，則需要在創(chuàng)建表或索引是指定,。 刪除語法： --刪除分區(qū)語法 drop partition function <分區(qū)函數(shù)名> --刪除分區(qū)函數(shù) bgPartitionFun drop partition function bgPartitionFun 需要注意的是,，只有沒有應(yīng)用到分區(qū)方案中的分區(qū)函數(shù)才能被刪除。 2.分區(qū)方案 指定分區(qū)對應(yīng)的文件組,。 創(chuàng)建語法： --創(chuàng)建分區(qū)方案語法 create partition scheme <分區(qū)方案名稱> as partition <分區(qū)函數(shù)名稱> [all]to (文件組名稱,....) --創(chuàng)建分區(qū)方案,所有分區(qū)在一個組里面 CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1]) 分區(qū)函數(shù)必須關(guān)聯(lián)分區(qū)方案才能有效,，然而分區(qū)方案指定的文件組數(shù)量必須與分區(qū)數(shù)量一致，哪怕多個分區(qū)存放在一個文件組中,。 刪除語法： --刪除分區(qū)方案語法 drop partition scheme<分區(qū)方案名稱> --刪除分區(qū)方案 bgPartitionSchema drop partition scheme bgPartitionSchema1 只有沒有分區(qū)表,，或索引使用該分區(qū)方案是，才能對其刪除,。 3.分區(qū)表 創(chuàng)建語法： --創(chuàng)建分區(qū)表語法 create table <表名> ( <列定義> )on<分區(qū)方案名>(分區(qū)列名)  --創(chuàng)建分區(qū)表 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) 如果在表中創(chuàng)建主鍵或唯一索引,，則分區(qū)依據(jù)列必須為該列。 4.分區(qū)索引 創(chuàng)建語法： --創(chuàng)建分區(qū)索引語法 create <索引分類> index <索引名稱> on <表名>(列名) on <分區(qū)方案名>(分區(qū)依據(jù)列名) --創(chuàng)建分區(qū)索引 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]) 使用分區(qū)索引查詢,，可以避免多個cpu操作多個磁盤時產(chǎn)生的沖突,。 分區(qū)表明細(xì)信息這里的語法，我就不寫啦,，自己看語句分析吧,。簡單的很。,。 1.查看分區(qū)依據(jù)列的指定值所在的分區(qū) --查詢分區(qū)依據(jù)列為10000014的數(shù)據(jù)在哪個分區(qū)上 select $partition.bgPartitionFun(2000000) --返回值是2,，表示此值存在第2個分區(qū) 2.查看分區(qū)表中，每個非空分區(qū)存在的行數(shù) --查看分區(qū)表中,，每個非空分區(qū)存在的行數(shù) select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount from bigorder group by $partition.bgPartitionFun(orderid)
3.查看指定分區(qū)中的數(shù)據(jù)記錄 ---查看指定分區(qū)中的數(shù)據(jù)記錄 select * from bigorder where $partition.bgPartitionFun(orderid)=2 結(jié)果：數(shù)據(jù)從1000001開始到200W結(jié)束 分區(qū)的拆分與合并以及數(shù)據(jù)移動1.拆分分區(qū) 在分區(qū)函數(shù)中新增一個邊界值,，即可將一個分區(qū)變?yōu)?個。 --分區(qū)拆分 alter partition function bgPartitionFun() split range(N'1500000') --將第二個分區(qū)拆為2個分區(qū) 注意：如果分區(qū)函數(shù)已經(jīng)指定了分區(qū)方案,，則分區(qū)數(shù)需要和分區(qū)方案中指定的文件組個數(shù)保持對應(yīng)一致,。 2.合并分區(qū) 與拆分分區(qū)相反，去除一個邊界值即可,。 --合并分區(qū) alter partition function bgPartitionFun() merge range(N'1500000') --將第二第三分區(qū)合并 3.分區(qū)中的數(shù)據(jù)移動 你或許會遇到這樣的需求,，將普通表數(shù)據(jù)復(fù)制到分區(qū)表中，或者將分區(qū)表中的數(shù)據(jù)復(fù)制到普通表中,。 那么移動數(shù)據(jù)這兩個表,，則必須滿足下面的要求,。
1.創(chuàng)建表時指定文件組 --創(chuàng)建表 create table <表名> ( <列定義> )on <文件組名> 2.從分區(qū)表中復(fù)制數(shù)據(jù)到普通表 --將bigorder分區(qū)表中的第一分區(qū)數(shù)據(jù)復(fù)制到普通表中 alter table bigorder switch partition 1 to <普通表名> 3.從普通標(biāo)中復(fù)制數(shù)據(jù)到分區(qū)表中 這里要注意的是要先將分區(qū)表中的索引刪除，即便普通表中存在跟分區(qū)表中相同的索引,。 --將普通表中的數(shù)據(jù)復(fù)制到bigorder分區(qū)表中的第一分區(qū) alter table <普通表名> switch to bigorder partition 1 分區(qū)視圖分區(qū)視圖是先建立帶有字段約束的相同表,而約束不同,例如,第一個表的id約束為0--100W,第二表為101萬到200萬.....依次類推,。 創(chuàng)建完一系列的表之后，用union all 連接起來創(chuàng)建一個視圖,，這個視圖就形成啦分區(qū)視同,。 很簡單的，這里我主要是說分區(qū)表,，就不說分區(qū)視圖啦,。。 查看數(shù)據(jù)庫分區(qū)信息SELECT OBJECT_NAME(p.object_id) AS ObjectName, i.name AS IndexName, p.index_id AS IndexID, ds.name AS PartitionScheme, p.partition_number AS PartitionNumber, fg.name AS FileGroupName, prv_left.value AS LowerBoundaryValue, prv_right.value AS UpperBoundaryValue, CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS Range, p.rows AS Rows FROM sys.partitions AS p JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0 UNION ALL SELECT OBJECT_NAME(p.object_id) AS ObjectName, i.name AS IndexName, p.index_id AS IndexID, NULL AS PartitionScheme, p.partition_number AS PartitionNumber, fg.name AS FileGroupName, NULL AS LowerBoundaryValue, NULL AS UpperBoundaryValue, NULL AS Boundary, p.rows AS Rows FROM sys.partitions AS p JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id JOIN sys.filegroups AS fg ON fg.data_space_id = i.data_space_id WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0 ORDER BY ObjectName, IndexID, PartitionNumber
|
|
|
