MS SQL Server：分區表、分區索引 詳解

来源:http://www.cnblogs.com/shouce/archive/2016/03/31/5339810.html
-Advertisement-
Play Games

1. 分區表簡介使用分區表的主要目的，是為了改善大型表以及具有各種訪問模式的表的可伸縮性和可管理性。 大型表：數據量巨大的表。 訪問模式：因目的不同，需訪問的不同的數據行集，每種目的的訪問可以稱之為一種訪問模式。分區一方面可以將數據分為更小、更易管理的部分，為提高性能起到一定的作用；另一方面，對 ...


1. 分區表簡介
使用分區表的主要目的，是為了改善大型表以及具有各種訪問模式的表的可伸縮性和可管理性。

        大型表：數據量巨大的表。
        訪問模式：因目的不同，需訪問的不同的數據行集，每種目的的訪問可以稱之為一種訪問模式。

分區一方面可以將數據分為更小、更易管理的部分，為提高性能起到一定的作用；另一方面，對於如果具有多個CPU的系統，分區可以是對錶的操作通過並行的方式進行，這對於提升性能是非常有幫助的。

註意：只能在 SQL Server Enterprise Edition 中創建分區函數。只有 SQL Server Enterprise Edition 支持分區。
2. 創建分區表或分區索引的步驟
可以分為以下步驟：
1. 確定分區列和分區數
2. 確定是否使用多個文件組
3. 創建分區函數
4. 創建分區架構（Schema）
5. 創建分區表
6. 創建分區索引

下麵詳細描述的創建分區表、分區索引的步驟。
2.1. 確定分區列和分區數
在開始做分區操作之前，首先要確定待分區表的訪問模式，該模式決定了什麼列適合做分區鍵。例如，對於銷售數據，一般會先根據日期把數據範圍限定在一個範圍內，然後在這個基礎上做進一步的查詢，這樣，就可以把日期作為分區列。

確定了分區列之後，需要進一步確定分區數，亦即分區表中需要包含多少數據，每個分區的數據應該限定在哪個範圍。

2.2. 確定是否使用多個文件組
為了有助於優化性能和維護，應該使用文件組分離數據。一般情況下，如果經常對分區的整個數據集操作，則文件組數最好與分區數相同，並且這些文件組通常應該位於不同的磁碟上，再配合多個CPU，則SQL Server 可以並行處理多個分區，從而大大縮短處理大量複雜報表和分析的總體時間。

2.3. 創建分區函數
分區函數用於定義分區的邊界條件，創建分區函數的語法如下：
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
[ ; ]

參數說明：
        partition_function_name
是分區函數的名稱。分區函數名稱在資料庫內必須唯一，並且符合標識符的規則。

        input_parameter_type
是用於分區的列的數據類型。當用作分區列時，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名數據類型或 CLR 用戶定義數據類型外，所有數據類型均有效。

實際列（也稱為分區列）是在 CREATE TABLE 或 CREATE INDEX 語句中指定的。

        boundary_value
為使用 partition_function_name 的已分區表或索引的每個分區指定邊界值。如果 boundary_value 為空，則分區函數使用 partition_function_name 將整個表或索引映射到單個分區。只能使用 CREATE TABLE 或 CREATE INDEX 語句中指定的一個分區列。

boundary_value 是可以引用變數的常量表達式。這包括用戶定義類型變數，或函數以及用戶定義函數。它不能引用 Transact-SQL 表達式。boundary_value 必須與 input_parameter_type 中提供的數據類型相匹配或者可隱式轉換為該數據類型，並且如果該值的大小和小數位數與 input_parameter_type 中相應的值的大小和小數位數不匹配，則在隱式轉換過程中該值不能被截斷。

註意：
如果 boundary_value 包含 datetime 或 smalldatetime 文字值，則為這些文字值在計算時假設 us_english 是會話語言。不推薦使用此行為。要確保分區函數定義對於所有會話語言都具有預期的行為，建議使用對於所有語言設置都以相同方式進行解釋的常量，例如 yyyymmdd 格式；或者將文字值顯式轉換為特定樣式。有關詳細信息，請參閱編寫國際化 Transact-SQL 語句。若要確定伺服器的語言會話，請運行 SELECT @@LANGUAGE。

        ...n
指定 boundary_value 提供的值的數目，不能超過 999。所創建的分區數等於 n + 1。不必按順序列出各值。如果值未按順序列出，則 Microsoft SQL Server 2005 資料庫引擎 將對它們進行排序，創建函數並返回一個警告，說明未按順序提供值。如果 n 包括任何重覆的值，則資料庫引擎將返回錯誤。

        LEFT | RIGHT
指定當間隔值由 資料庫引擎 按升序從左到右排序時，boundary_value [ ,...n ] 屬於每個邊界值間隔的哪一側（左側還是右側）。如果未指定，則預設值為 LEFT。

創建分區函數示例：
CREATE PARTITION FUNCTION PF_Left(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO

CREATE PARTITION FUNCTION PF_Right(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO

PF_Left 和 PF_Right 分區函數的區分：
分區函數        分區1        分區2        分區3
PF_Left        <= 10         > 10 and <= 20        > 20
PF_Right        < 10        >= 10 and < 20        >= 20

2.4. 創建分區架構（Schema）
創建分區函數後，必須將其與分區架構（Schema）相關聯，以便將分區定向至特定的文件組。定義分區架構師，即使多個 分區位於同一個文件組中，也必須為每個分區指定一個文件組。

創建分區架構的語法如下：
GOCREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

參數：
        partition_scheme_name
分區方案的名稱。分區方案名稱在資料庫中必須是唯一的，並且符合標識符規則。

        partition_function_name
使用分區方案的分區函數的名稱。分區函數所創建的分區將映射到在分區方案中指定的文件組。partition_function_name 必須已經存在於資料庫中。

        ALL
指定所有分區都映射到在 file_group_name 中提供的文件組，或映射到主文件組（如果指定了 [PRIMARY]。如果指定了 ALL，則只能指定一個 file_group_name。

        file_group_name | [ PRIMARY ] [ ,...n]
指定用來持有由 partition_function_name 指定的分區的文件組的名稱。file_group_name 必須已經存在於資料庫中。

如果指定了 [PRIMARY]，則分區將存儲於主文件組中。如果指定了 ALL，則只能指定一個 file_group_name。分區分配到文件組的順序是從分區 1 開始，按文件組在 [,...n] 中列出的順序進行分配。在 [,...n] 中，可以多次指定同一個 file_group_name。如果 n 不足以擁有在 partition_function_name 中指定的分區數，則 CREATE PARTITION SCHEME 將失敗，並返回錯誤。

如果 partition_function_name 生成的分區數少於文件組數，則第一個未分配的文件組將標記為 NEXT USED，並且出現顯示命名 NEXT USED 文件組的信息。如果指定了 ALL，則單獨的 file_group_name 將為該 partition_function_name 保持它的 NEXT USED 屬性。如果在 ALTER PARTITION FUNCTION 語句中創建了一個分區，則 NEXT USED 文件組將再接收一個分區。若要再創建一個未分配的文件組來擁有新的分區，請使用 ALTER PARTITION SCHEME。

在 file_group_name[ 1,...n] 中指定主文件組時，必須像在 [PRIMARY] 中那樣分隔 PRIMARY，因為它是關鍵字。

創建分區架構示例：
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);
GO

2.5. 創建分區表
定義了分區函數（邏輯結構）和 分區架構（物理結構）後，既可以創建分區表來利用它們。分區表定義應使用的分區架構，而分區架構又定義其使用的分區函數。要將這三者結合起來，必須指定應用於分區函數的 列 。範圍分區始終只映射到表中的一列。

CREATE TABLE 語法如下：
CREATE TABLE
    [ database_name . [ schema_name ] . | schema_name . ] table_name
        ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] )
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup
        | "default" } ]
    [ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]

示例如下：
CREATE TABLE myRangePT1
(
        ID        int not null,
        AGE int,
        PRIMARY KEY (ID)
) ON myRangePS1(myRangePF1)
GO


2.6. 創建分區索引
索引對於提高查詢性能非常有效，因此，一般應該考慮應該考慮為分區表建立索引，為分區表建立索引與為普通表建立索引的語法一直，但是，其行為與普通索引有所差異。

預設情況下，分區表中創建的索引使用與分區表相同分區架構和分區列，這樣，索引將於表對齊。將表與其索引對齊，可以使管理工作更容易進行，對於滑動視窗方案尤其如此。若要啟動分區切換，表的所有索引都必須對齊。

在創建索引時，也可以指定不同的分區方案（Schema）或單獨的文件組（FileGroup）來存儲索引，這樣SQL Server 不會將索引與表對齊。

在已分區的表上創建索引（分區索引）時，應該註意以下事項：
        唯一索引
建立唯一索引（聚集或者非聚集）時，分區列必須出現在索引列中。此限制將使SQL Server只調查單個分區，並確保表中 寵物的新鍵值。如果分區依據列不可能包含在唯一鍵中，則必須使用DML觸發器，而不是強制實現唯一性。

        非唯一索引
對非唯一的聚集索引進行分區時，如果未在聚集鍵中明確指定分區依據列，預設情況下SQL Server 將在聚集索引列中添加分區依據列。
對非唯一的非聚集索引進行分區時，預設情況下SQL Server 將分區依據列添加為索引的包含性列，以確保索引與基表對齊，若果索引中已經存在分區依據列，SQL Server 將不會像索引中添加分區依據列。

3. 分區操作
分區適用於可以縮放的大型表，所以隨著時間和環境的變化，就會產生對分區的拆分、合併、移動的需求。
3.1. 拆分與合併分區
        通過拆分或合併邊界值更改分區函數。通過執行 ALTER PARTITION FUNCTION，可以將使用分區函數的任何表或索引的某個分區拆分為兩個分區，也可以將兩個分區合併為一個分區。
       
        註意：多個表或索引可以使用同一分區函數。ALTER PARTITION FUNCTION 在單個事務中影響所有這些表或索引。
       
ALTER PARTITION FUNCTION 語法如下：
ALTER PARTITION FUNCTION partition_function_name()
{
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value )
} [ ; ]

參數說明：
        partition_function_name
要修改的分區函數的名稱。

        SPLIT RANGE ( boundary_value )
在分區函數中添加一個分區。boundary_value 確定新分區的範圍，因此它必須不同於分區函數的現有邊界範圍。根據 boundary_value，Microsoft SQL Server 2005 資料庫引擎 將某個現有範圍拆分為兩個範圍。在這兩個範圍中，新 boundary_value 所在的範圍被視為是新分區。

重要提示：
文件組必須處於聯機狀態，並且必須由使用此分區函數的分區方案標記為 NEXT USED，以保存新分區。在 CREATE PARTITION SCHEME 語句中，將把文件組分配給分區。如果 CREATE PARTITION SCHEME 語句分配了多餘的文件組（在 CREATE PARTITION FUNCTION 語句中創建的分區數少於用於保存它們的文件組），則存在未分配的文件組，分區方案將把其中的某個文件組標記為 NEXT USED。該文件組將保存新的分區。如果分區方案未將任何文件組標記為 NEXT USED，則必須使用 ALTER PARTITION SCHEME 添加一個文件組或指定一個現有文件組來保存新分區。可以指定已保存分區的文件組來保存附加分區。由於一個分區函數可以參與多個分區方案，因此所有使用分區函數（您向其中添加了分區）的分區方案都必須擁有一個 NEXT USED 文件組。否則，ALTER PARTITION FUNCTION 將失敗並出現錯誤，該錯誤顯示缺少 NEXT USED 文件組的一個或多個分區方案。

        MERGE [ RANGE ( boundary_value) ]
刪除一個分區並將該分區中存在的所有值都合併到剩餘的某個分區中。RANGE (boundary_value) 必須是一個現有邊界值，已刪除分區中的值將合併到該值中。如果最初保存 boundary_value 的文件組沒有被剩餘分區使用，也沒有使用 NEXT USED 屬性進行標記，則將從分區方案中刪除該文件組。合併的分區駐留在最初不保存 boundary_value 的文件組中。boundary_value 是一個可以引用變數（包括用戶定義類型變數）或函數（包括用戶定義函數）的常量表達式。它無法引用 Transact-SQL 表達式。boundary_value 必須匹配或可以隱式轉換為其對應列的數據類型，並且當值的大小和小數位數不匹配其對應 input_parameter_type 時，將無法在隱式轉換過程中被截斷。

示例：
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]



備註：
ALTER PARTITION FUNCTION 在單個原子操作中對使用該函數的任何表和索引進行重新分區。但該操作在離線狀態下進行，並且根據重新分區的範圍，可能會消耗大量資源。

ALTER PARTITION FUNCTION 只能用於將一個分區拆分為兩個分區，或將兩個分區合併為一個分區。若要更改其他情況下對錶進行分區方法（例如，將 10 個分區合併為 5 個分區），可以嘗試使用以下任何選項。根據系統配置，這些選項可能在資源消耗方面有所不同：

        使用所需的分區函數創建一個新的已分區表，然後使用 INSERT INTO...SELECT FROM 語句將舊表中的數據插入新表。

        為堆創建分區聚集索引。
註意：
刪除已分區的聚集索引將產生分區堆。

        通過將 Transact-SQL CREATE INDEX 語句與 DROP EXISTING = ON 子句一起使用來刪除並重新生成現有的已分區索引。

        執行一系列 ALTER PARTITION FUNCTION 語句。

ALTER PARITITION FUNCTION 所影響的全部文件組都必須處於聯機狀態。
如果使用分區函數的任何表中存在已禁用的聚集索引，ALTER PARTITION FUNCTION 都將失敗。
Microsoft SQL Server 2005 不對修改分區函數提供複製支持。必須在訂閱資料庫中手動應用對發佈資料庫中的分區函數的更改。

3.2. 移動分區數據
        可以使用 ALTER TABLE ....... SWITCH 語句按一下方式快速有效地移動數據子集：
        將某個表中的數據移動到另一個表中；
        將某個表作為分區添加到現存的已分區表中；
        將分區從一個已分區表切換到另一個已分區表；
        刪除分區以形成單個表。

使用這些方案移動數據時，無論集合有多大，此方案都能快速有效地進行傳輸，因為操作並不以物理方式移動數據，只有關於存儲位置的元數據會從一個分區變為另一個分區。

        ALTER TABLE .... SWITCH 的語法如下：
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
        SWITCH [ PARTITION source_partition_number_expression ]
    TO [schema_name].target_table
        [ PARTITION target_partition_number_expression ]
}
[ ; ] 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 實現密碼輸入
    效果如圖 1 首先這是一個自定義的Dialog，而不是AlertDialog，如果是AlertDialog的話，軟鍵盤彈出的時候在AlertDialog的後面，無法進行輸入。 2 Dialog的上面會有一個黑框，添加Style 3 輸入密碼用的是EditText，改變EditText的格式是 4 給 ...
  • IOS GCD定時器
    提到定時器，NStimer肯定是我們最為熟悉的。 但是NStimer有著很大的缺點，並不准確。 通俗點說，就是它該做他的事了，但是由於其他事件的影響，Nstimer會放棄他應該做的。 而GCD定時器，是不會發生這種事情的。 GCD嚴格按照規定好的規格去做事。 前面介紹RunLoop 的時候已經介紹了 ...
  • Objective-C記憶體管理之-引用計數
    本文會繼續深入學習OC記憶體管理,內容主要參考iOS高級編程,Objective C基礎教程,瘋狂iOS講義,是我學習記憶體管理的筆記 記憶體管理 1 記憶體管理的基本概念 1.1 Objective C中的記憶體管理 手動記憶體管理和自動釋放池 \ (Mannul Reference Counting) 自動 ...
  • IOS RunLoop面試題
    一 什麼是RunLoop？ 從字面意思看就是運行迴圈，其實內部就是do－while迴圈，這個迴圈內部不斷地處理各種任務（比 如Source，Timer，Observer） 一個線程對應一個RunLoop，主線程的RunLoop預設已經啟動，子線程的RunLoop得手動啟動（run方法） RunLoo ...
  • Android自定義折線圖
    老師佈置了個作業：http://www.cnblogs.com/qingxu/p/5316897.html 作業中提到的 “玩了幾天以後，大家發現了一些很有意思的現象，比如黃金點在逐漸地往下移動。” 而只是提到而已，如果不保留歷史結果和不是比較明顯的顯示出來，大家也很難發現這個問題。於是我就想到了折 ...
  • EventBus3.0源碼解析
    本文主要介紹EventBus3.0的源碼 EventBus是一個Android事件發佈/訂閱框架，通過解耦發佈者和訂閱者簡化 Android 事件傳遞。 EventBus使用簡單,並將事件發佈和訂閱充分解耦,從而使代碼更簡潔。 本文主要從以下幾個模塊來介紹 1、EventBus使用 2、EventB ...
  • centos 7 用yum安裝 mysql
    首先centos7 已經不支持mysql，因為收費了你懂得，所以內部集成了mariadb，而安裝mysql的話會和mariadb的文件衝突，所以需要先卸載掉mariadb，以下為卸載mariadb，安裝mysql的步驟。 #列出所有被安裝的rpm package rpm -qa | grep mar ...
  • “SQL*PLUS 幫助不可用”解決步驟
    SQL*PLUS 是oracle管理資料庫最常用的一個CMD界面，其中的幫助系統會給我們執行一些命令帶來快捷的指導和幫助，在oracle安裝時此功能是預設安裝的。有時候由於一些意外原因會失去此項功能，使用幫助系統會出現下麵的錯誤： SP2-0171: 幫助系統不可用。 以至於帶來很多的不便和麻煩。下 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類