以前的一次技術(shù)例會內(nèi)容，拿出來共享一下，大家有問題可以提出來，一起提高。

技術(shù)會議－ SQL Server Partitioning

V2※高捷

本月技術(shù)會議專題為數(shù)據(jù)庫分區(qū)（ SQL Server Partitioning ），主要講述為什么要分區(qū)，在什么情況下需要對數(shù)據(jù)進(jìn)行分區(qū)，如何進(jìn)行分區(qū)，分區(qū)表管理等內(nèi)容。

一、 摘要

◆    分區(qū)基礎(chǔ)知識

u  SQL Server2005 分區(qū)

u  技術(shù)討論

u  會議決議

二、 分區(qū)技術(shù)知識

1、  為什么要分區(qū)？

（1）       scale up VS scale out

首先需要理解 scale up 和 scale out 的含義， scale up （向上擴(kuò)展），即后端大型服務(wù)器以增加處理器等運(yùn)算資源進(jìn)行升級以獲得對應(yīng)用性能的要求，提高硬件來提高數(shù)據(jù)處理及提高性能的一種方式。而 scale out （向外擴(kuò)展）主要是指根據(jù)需求增加不同的服務(wù)器應(yīng)用，依靠多部服務(wù)器協(xié)同運(yùn)算，借負(fù)載平衡及容錯(cuò)等功能來提高運(yùn)算能力及可靠度的方式來提高數(shù)據(jù)處理和提高性能的一種方式。數(shù)據(jù)庫分區(qū)技術(shù)的應(yīng)用則是 scale out 的體現(xiàn)。在此需要注意的是 Scale Out 方案始終面臨著數(shù)據(jù)集中的問題，即拆分過的數(shù)據(jù)在服務(wù)器邏輯體系中仍然是各自相對集中的而非無限隨意拆分。如果大量的邏輯放在數(shù)據(jù)庫服務(wù)器一端，數(shù)據(jù)庫服務(wù)器將會使得系統(tǒng)失去 Scale out 的能力和可能。因此，要保證 Scale out 的能力就必須保證數(shù)據(jù)庫只處理實(shí)質(zhì)性的數(shù)據(jù)提交和不可避免的數(shù)據(jù)查詢，對于能夠避免的數(shù)據(jù)查詢和非實(shí)質(zhì)性數(shù)據(jù)提交都應(yīng)該想辦法予以避免。而具體的策略和方案相對沒有最優(yōu)的方法。

（2）       避免昂貴的硬件開銷

使用分區(qū)技術(shù)處理大容量數(shù)據(jù)表，可以讓我們不必為了性能而購買昂貴的新服務(wù)器或者提高服務(wù)器硬件性能來提高性能。

（3）       使數(shù)據(jù)在一個(gè)合適的 Level 上管理

使用分區(qū)技術(shù)后，我們在對數(shù)據(jù)庫進(jìn)行管理時(shí)，避免了面對 G 級別的數(shù)據(jù)量維護(hù)，只需要面對幾百兆或者幾十兆的數(shù)據(jù)，這樣使得我們面對數(shù)據(jù)庫管理時(shí)，處于一個(gè)合適的水平和級別，就能保證數(shù)據(jù)庫的高維護(hù)性，節(jié)約維護(hù)成本、資源

（4）       消除性能瓶頸，最小化維護(hù)成本

同樣使用分區(qū)技術(shù)后，客戶端對數(shù)據(jù)庫的操作也更容易更迅速，提高了數(shù)據(jù)庫的性能，對數(shù)據(jù)庫維護(hù)也相對簡單，比如說數(shù)據(jù)的備份、恢復(fù)等等。

2、  什么情況下使用數(shù)據(jù)庫分區(qū)

（1）       大數(shù)據(jù)量表（管理 / 索引創(chuàng)建 / 索引重建 / 備份與恢復(fù) / 擴(kuò)充 ）

面對大表，你首先遇到的一個(gè)管理的問題，因?yàn)閿?shù)據(jù)量太大，管理非常復(fù)雜和麻煩。其次是索引，在大數(shù)據(jù)量表進(jìn)行索引的建立、重建都會有可能因?yàn)槌瑫r(shí)而導(dǎo)致失敗。再次是表的維護(hù)，例如備份、恢復(fù)等有可能因?yàn)殚L時(shí)間的操作影響其他用戶的操作，最后還有一個(gè)表的擴(kuò)充的問題，比如說擴(kuò)充字段、鎖的升級。

在此，需要特別講述一下有關(guān)索引的問題，在 OLTP （ On-Line Transaction Processing ）和 DSS （ Decision Support Systems ）系統(tǒng)中，索引在上述兩種系統(tǒng)中的應(yīng)用是不同，在 OLTP 系統(tǒng)中，可能我們需要很少的索引，而在 DSS 系統(tǒng)中，我們肯定需要大量的索引，同時(shí)我們在 OLTP 系統(tǒng)中對索引的重建和碎片整理需要經(jīng)常性的去處理，而對于 DSS 系統(tǒng)，對于索引我們原則上只需要一次即可。

在備份與恢復(fù)的層面上分析， OLTP 是一個(gè)需要經(jīng)常備份、存放很多關(guān)鍵數(shù)據(jù)的、需要保持高可用性的比較小型的 VLDB( VERY LARGE DATE BASE ) 的系統(tǒng)，而 DSS 是一個(gè)不需要經(jīng)常備份、數(shù)據(jù)也非關(guān)鍵數(shù)據(jù)但需要保高可用的大型 VLDB 系統(tǒng)。

（2）       不同的訪問模式

一種訪問模式主要指聯(lián)機(jī)事務(wù)處理的方式，比如一些插入、更新、刪除等內(nèi)容。另一種訪問模式主要是對數(shù)據(jù)庫的查詢、分析等操作，這些主要是一些 SELECT 的操作。

3、  分區(qū)策略（ Partitioning Strategies ）

（1）       垂直分區(qū)

垂直分區(qū)將一個(gè)表分為多個(gè)表，每個(gè)表包含較少的列。垂直分區(qū)包括兩種類型，即規(guī)范化和行拆分：

規(guī)范化是標(biāo)準(zhǔn)的數(shù)據(jù)庫進(jìn)程，它刪除表中的多余列，并將這些列放置在通過主鍵和外鍵關(guān)系鏈接到主表的輔助表中。

行拆分將原始表垂直分成多個(gè)只包含較少列的表。拆分表內(nèi)的每個(gè)邏輯行都與其他表內(nèi)由 UNIQUE KEY 列（在所有已分區(qū)表中都相同）標(biāo)識的相同邏輯行相匹配。例如，聯(lián)接每個(gè)拆分表內(nèi)具有 ID 712 的行將重新創(chuàng)建原始行。

應(yīng)該慎用垂直分區(qū)，因?yàn)榉治龆鄠€(gè)分區(qū)中的數(shù)據(jù)時(shí)需要聯(lián)接表的查詢。如果分區(qū)過大，垂直分區(qū)還可能會影響性能。

（2）       水平分區(qū)

水平分區(qū)將表分為多個(gè)表。這樣，每個(gè)表包含的列數(shù)相同，但是行更少。例如，可以將一個(gè)包含十億行的表水平分區(qū)成 12 個(gè)表，每個(gè)小表表示特定年份內(nèi)一個(gè)月的數(shù)據(jù)。任何需要特定月的數(shù)據(jù)的查詢都只引用相應(yīng)的表。

具體如何將表進(jìn)行水平分區(qū)取決于如何分析數(shù)據(jù)。您應(yīng)將表進(jìn)行分區(qū)，以便查詢引用的表盡可能少。否則，查詢時(shí)需要使用過多的 UNION 查詢來邏輯合并表，這會影響查詢性能。

4、  垂直分區(qū)案例

某個(gè)表存在記錄行為 1,600,000 rows 。此表有 47 個(gè)列， 4600Bytes/Row ，由于 SQL Server 本身系統(tǒng)限制一條記錄不能超過 8060Bytes ，所以我們一行記錄需要一頁，每條記錄浪費(fèi) 3460Bytes ，這樣計(jì)算后整個(gè)表空間占用約 12G 。表內(nèi)容如下所示：

先對其進(jìn)行垂直分區(qū)，假設(shè)此表為一個(gè)主鍵，我們將其分為三個(gè)表，分區(qū)標(biāo)準(zhǔn)如下表所示：

結(jié)果：節(jié)省了 5G 的空間，同時(shí)提高了性能。

另外在進(jìn)行垂直分區(qū)的時(shí)候還需要注意一下幾點(diǎn)：

1 優(yōu)化行的尺寸

   因?yàn)?/span> SQL Server 在對數(shù)據(jù)進(jìn)行檢索的時(shí)候，是通過頁來取得的，這就要求我們盡量讓更多的記錄在一個(gè)頁上，才能保證更多的行在緩存中，這樣就保證了數(shù)據(jù)庫在進(jìn)行 I/O 操作時(shí)，提高了性能。

    另外，從鎖的方面分析，我們將列分區(qū)后，在進(jìn)行操作時(shí)，列鎖定的是沒有分區(qū)表的幾個(gè)字段，而不是所有的字段，這就保證了另外兩個(gè)表不被鎖限制，也就降低了行鎖對數(shù)據(jù)庫并發(fā)用戶的影響。

2 使用方法

   首先考慮將經(jīng)常關(guān)聯(lián)的邏輯列進(jìn)行分組，也就說將同一類屬性，經(jīng)常放在一起進(jìn)行查詢劃分為一個(gè)分組放到一個(gè)表中，從而減少表與表之間的交流和關(guān)聯(lián)；其次要考慮到那些列是只讀的，那些是 OLTP 環(huán)境下的列，這樣可以避免數(shù)據(jù)檢索時(shí)的重復(fù)性，提高性能。

5、  水平分區(qū)

水平分區(qū)創(chuàng)造出了更多的可管理的塊，同時(shí)減少的 DBA 對表的維護(hù)的影響，例如表索引管理、備份恢復(fù)等。另外，他減少的鎖對數(shù)據(jù)操作的影響，使得我們在對部分?jǐn)?shù)據(jù)進(jìn)行操作時(shí)，鎖僅僅在某一個(gè)部分進(jìn)行，減少了鎖操作對數(shù)據(jù)庫資源的占用，從而提高了數(shù)據(jù)處理的效率。

三、 SQL Server2005 分區(qū)實(shí)現(xiàn)

1、  SQL Server2005 分區(qū)表和索引概念

物理分區(qū)，具有標(biāo)準(zhǔn)表和索引相關(guān)的所有的屬性和功能

大型表或索引經(jīng)過分區(qū)后更容易管理，因?yàn)榉謪^(qū)后可以更快速有效地管理和訪問數(shù)據(jù)子集，同時(shí)維護(hù)數(shù)據(jù)集合的完整性。通過分區(qū)，從 OLTP 向 OLAP 系統(tǒng)加載數(shù)據(jù)操作只需要幾秒鐘，同時(shí)由于對數(shù)據(jù)子集執(zhí)行的維護(hù)操作只是針對所需數(shù)據(jù)而不是整個(gè)表，因此效率也得到了很大的提高。

已分區(qū)表和已分區(qū)索引的數(shù)據(jù)劃分為分布于一個(gè)數(shù)據(jù)庫中多個(gè)文件組的單元，數(shù)據(jù)俺水平方式分區(qū)，因此多組行映射到單個(gè)的分區(qū)中。單個(gè)索引或表的所有分區(qū)都必須位于同一個(gè)數(shù)據(jù)庫中。

在 SQL Server2005 中，數(shù)據(jù)庫中所有表和索引都視為已分區(qū)表和索引，即使這些表和索引只包含一個(gè)分區(qū)。

2、  分區(qū)設(shè)計(jì)

（1）       創(chuàng)建分區(qū)函數(shù)；

分區(qū)函數(shù)：分區(qū)函數(shù)定義如何根據(jù)某些列的值將表或索引的行映射到一組分區(qū)。（一張表最多有 1000 個(gè)分區(qū)）

（2）       創(chuàng)建分區(qū)方案；

分區(qū)方案：分區(qū)方案將分區(qū)函數(shù)指定的每個(gè)分區(qū)映射到文件組。（一個(gè)分區(qū)方案只能使用一個(gè)分區(qū)函數(shù)，但是，一個(gè)分區(qū)函數(shù)可以參與到多個(gè)分區(qū)方案）

（3）       在創(chuàng)建表和索引的時(shí)候，指定表或索引的分區(qū)方案。

3、  分區(qū)實(shí)現(xiàn)

（1）       創(chuàng)建分區(qū)函數(shù)

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

例句：

CREATE PARTITION FUNCTION myRangePF1( DATETIME ) AS RANGE RIGHT FOR VALUES ( '2007-12-21' , '2007-12-22' , '2007-12-23' , '2007-12-24' , '2007-12-25' , '2007-12-26' )

說明：指定當(dāng)間隔值由 數(shù)據(jù)庫引擎 按升序從左到右排序時(shí)， boundary_value [ ,...n ] 屬于每個(gè)邊界值間隔的哪一側(cè)（左側(cè)還是右側(cè)）。如果未指定，則默認(rèn)值為 LEFT 。

（2）       創(chuàng)建分區(qū)方案

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

            例句：

            CREATE PARTITION SCHEME mySchemePS1 AS PARTITION [myRangePF1] TO ( [PRIMARY], [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7])

說明：如果創(chuàng)建的文件組少于要分區(qū)的指定的文件組，創(chuàng)建語句將執(zhí)行失敗，如果創(chuàng)建的文件組大于要分區(qū)指定的文件組，那么剩余的文件組將會做為下一次分區(qū)指定時(shí)使用，所以多出來的文件組也不會存在數(shù)據(jù)。

（3）       增加分區(qū)

ALTER PARTITION SCHEME partition_scheme_name

NEXT USED [ filegroup_name ] [ ; ]

例句：

ALTER PARTITION SCHEME MyRangePS1

NEXT USED test5fg;

說明：修改分區(qū)方案后，需要修改分區(qū)函數(shù)。

ALTER PARTITION FUNCTION partition_function_name()

{  SPLIT RANGE ( boundary_value )

  | MERGE RANGE ( boundary_value ) } [ ; ]

例句：

ALTER PARTITION FUNCTION myRangePF1 ()

SPLIP RANGE ( 100);

（4）       合并分區(qū)

ALTER PARTITION FUNCTION myRangePF1 ()

MERGE RANGE (100);

（5）       創(chuàng)建分區(qū)表

CREATE TABLE PatitionTable( col1 int , col2 char ( 10))

ON MyRangePS1( col1);

（6）       創(chuàng)建分區(qū)索引

CREATE INDEX ix_Col2 ON PartitionTable( col2)

ON myRangePS1( col1);

CREATE INDEX ix_Col2 ON PartitionTable( col2)

說明：如果是根據(jù)分區(qū)依據(jù)列來創(chuàng)建索引，則不需要增加 On 后的內(nèi)容。

（7）       分區(qū)信息查看

※ 使用 $PARTITION 函數(shù)

※ 訪問已分區(qū)表的分區(qū)子集中的所有行。

SELECT $PARTITION . myRangePF1( col1), count (*)

FROM PartitionTable

GROUP BY $Partition . myRangePF1( col1)

※確定包含特定分區(qū)鍵值的行位于哪個(gè)分區(qū)中？

SELECT $PARTITION . myRangePF1( col1)

4、  分區(qū) DEMO

示例數(shù)據(jù)為 SQL Server2005 自帶的 AdventureWorks 數(shù)據(jù)庫，在這個(gè)數(shù)據(jù)庫中有兩個(gè)表 TransactionHistory （交易歷史信息表）和 TransactionHistoryArchive （交易歷史歸檔表）， TransactionHistory 主要維護(hù)年度最新事務(wù)信息，而 TransactionHistoryArchive 保存歷史的事務(wù)信息。

TransactionHistory 設(shè)定為 12 個(gè)分區(qū)，存放了 03 年 9 月份到 04 年八月份的數(shù)據(jù)， TransactionHistoryArchive 分為 2 個(gè)區(qū)，存放了 03 年 9 月份之前和之后的數(shù)據(jù)，分區(qū)字段為 TransactionDate 。

每個(gè)月開始， TransactionHistory 當(dāng)前最早的一個(gè)月的數(shù)據(jù)將被切換到 TransactionHistoryArchive 表中。需要注意的是，在這個(gè)操作中，如果不使用分區(qū)，而是使用導(dǎo)入導(dǎo)出或 INSERT 等語句進(jìn)行數(shù)據(jù)的切換，是非常耗費(fèi)資源和時(shí)間的，而采用分區(qū)，則避免了這個(gè)問題，因?yàn)樵谡嬲牟僮髦校?/span> SQL Server 并不是真正的將數(shù)據(jù)進(jìn)行了遷移，而只是將源數(shù)據(jù)進(jìn)行了切換，就是說數(shù)據(jù)的指針或者說數(shù)據(jù)資源表位置進(jìn)行了修改，所以表分區(qū)之間的數(shù)據(jù)切換是瞬間的事情。

實(shí)現(xiàn)腳本：

ALTER TABLE [Production]. [TransactionHistory]

SWITCH PARTITION 1

TO [Production]. [TransactionHistoryArchive] PARTITION 2;

實(shí)現(xiàn)：見 SQL 腳本。

5、  條碼物流系統(tǒng)分區(qū)應(yīng)用介紹

目前部門所規(guī)劃的 5 大產(chǎn)品之一條碼物流系統(tǒng)在開發(fā)中使用到了數(shù)據(jù)庫分區(qū)技術(shù)來改善數(shù)據(jù)庫性能，目前應(yīng)用只是輪胎狀態(tài)表。

因?yàn)檩喬顟B(tài)表貫穿于整個(gè)條碼物流系統(tǒng)的各個(gè)工序和環(huán)節(jié)，也存放著大量的生產(chǎn)、質(zhì)檢等數(shù)據(jù)，數(shù)據(jù)量非常巨大，在沒有進(jìn)行分區(qū)時(shí)整個(gè)表的檢索經(jīng)常出現(xiàn)檢索超時(shí)和客戶端死機(jī)的情況。

輪胎狀態(tài)表的分區(qū)主要依據(jù)于輪胎胎號，將 08 年之前的信息，按年分區(qū)，而 08 年之后的數(shù)據(jù)按照每 10 周進(jìn)行分區(qū)，目前應(yīng)用良好，對整個(gè)現(xiàn)場系統(tǒng)的應(yīng)用和 WEB 系統(tǒng)的查詢性能都有了很大的改善。