八、IO最佳化（5）使用檔案組

一、檔案與檔案組

650) this.width=650;" title="檔案組.png" src="http://www.bkjia.com/uploads/allimg/131229/210HHa5-0.png" alt="111522649.png" />

　　在同一個檔案組內部，多個資料檔案起到負載平衡的作用。因此，盡量使每個資料檔案的大小相同，這樣可以最佳化比例填充的效能。

　　例如：某檔案組有2個資料檔案，現在大量匯入10MB的資料，結果會使這2個資料檔案分別增加5MB的資料使用空間。

注意：請將同一個檔案組的多個資料檔案分別放置在不同的物理磁碟上。如果都放在同一個物理磁碟，由於資料需在多個檔案中存取，導致磁頭頻繁尋道、定位，反而使效能降低。

二、表和索引分割區

1.概念

　　表和索引分割區是SQL Server 2008引入的一項新特性。在維護資料集合的完整性時，使用分區可以快速而有效地管理和訪問資料子集，從而使大型表或索引更易於管理。通過使用分區，將資料從 OLTP 載入到 OLAP 系統之類的操作僅需幾秒鐘即可完成，不像在 SQL Server 的早期版本那樣需要幾分鐘或幾小時。對資料子集執行的維護操作只針對所需的資料而不是整個表，因此，這些操作的執行效果也會更為有效。

　　表和區分區的資料劃分為分佈於一個資料庫中多個檔案組的單元。資料是按水平方式分區的，因此多組行映射到單個的分區。對資料進行查詢或更新時，表或索引將被視為單個邏輯實體。單個索引或表的所有分區都必須位於同一個資料庫中。

　　資料分割資料表和資料分割索引支援與設計和查詢標準表和索引相關的所有屬性和功能，包括約束、預設值、標識和時間戳記值以及觸發器。因此，如果要實現位於伺服器本地的已分區視圖，則可能需要改為實現資料分割資料表。

　　決定是否實現分區主要取決於表當前的大小或將來的大小、如何使用表以及對錶執行使用者查詢和維護操作的完善程度。

　　通常，如果某個大型表同時滿足下列兩個條件，則可能適於進行分區：

1）該表包含或將包含）以多種不同方式使用的大量資料。

2）不能按預期對錶執行查詢或更新，或維護開銷超過了預定義的維護期。

2. 分區的優勢

　　例如，如果對當前月份的資料主要執行 INSERT、UPDATE、DELETE 和 MERGE 操作，而對以前月份的資料主要執行 SELECT 查詢，則按月份對錶進行分區可能會使表的管理工作更容易一些。如果對錶的常規維護操作只針對一個資料子集，那麼此優點尤為明顯。如果該表沒有分區，那麼就需要對整個資料集執行這些操作，這樣就會消耗大量資源。例如，通過分區，可以針對具有唯寫資料的單個月份執行類似索引重建和磁碟重組的維護操作，而唯讀資料仍可用於聯機訪問。

　　擴充一下此樣本，假設要將該表中一個月的唯讀資料移至資料倉儲表中以進行分析。分區後，可以快速將資料子集分散到臨時地區中以進行離線維護，然後將這些資料作為分區添加到現有的資料分割資料表假定這些表都位於同一個資料庫執行個體中）中。類似這樣的操作通常只需幾秒鐘，而不是像未分區時那樣需要數分鐘或數小時。

　　如果根據頻繁執行的查詢的類型和硬體設定正確地設計分區，那麼對錶或索引進行分區可以提高查詢效能。http://technet.microsoft.com/zh-cn/library/ms177411(v=sql.105).aspx

3. 實現表和索引分割區

　　建立資料分割資料表或索引通常包含四個操作：

1）建立將持有資料分割配置所指定的分區的檔案組和相應的檔案。

2）建立一個分區函數，該函數根據指定列的值將表或索引的各行映射到分區。

3）建立一個將資料分割資料表或資料分割索引的分區映射到新檔案組的資料分割配置。

4）建立或修改表或索引，並指定資料分割配置作為儲存位置。

http://technet.microsoft.com/zh-cn/library/ms188730.aspx

三、隔離資料庫對象

　　在建立表和索引時注意到ON參數，該參數指定將表或索引建立到某個檔案組，而該檔案組可以將檔案指定在特定的物理磁碟。例如：

CREATE TABLE [dbo].[EmpTable]( [EmpID] [varchar](10) NULL, [DepID] [nvarchar](10) NULL, [Title] [nvarchar](10) NULL ) ON [PRIMARY] CREATE NONCLUSTERED INDEX [IX_EmpTable] ON [dbo].[EmpTable] ( [EmpID] ASC ) ON [PRIMARY]

　　在某些實際案例中，有一些表和索引被頻繁訪問，希望將這些表和索引隔離到另一個物理磁碟以加速訪問的效能。

1. 移動表

　　如果要將表從一個檔案組移動到另一個檔案組，有以下3種方法：

1） 使用分區

　　如果該表已經分區或者臨時使用分區，可以切換分區到另一個物理磁碟。

2） 重新建立表或索引

　　在重新建立表或索引時，使用ON參數將表或索引建立在指定的檔案組。

3） 使用叢集索引搬移物理表

　　叢集索引實際上就是物理上的資料頁面，因此，修改叢集索引也能將物理表搬移到另一個檔案組。

2. 移動索引

1）使用資料表設計工具僅適用於非叢集索引）

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210HJ393-1.png" title="資料表設計工具.png" alt="134911733.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210HL557-2.png" style="float:none;" title="移動非叢集索引1.png" alt="134313224.png" />

2）修改索引的屬性

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210HI421-3.png" title="索引-屬性.png" alt="135120522.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210HJ433-4.png" title="移動非叢集索引2.png" style="float:none;" alt="134315708.png" />

3）使用T-SQL

CREATE CLUSTERED INDEX [IX_EmpTable] ON [dbo].[EmpTable] ( [EmpID] ASC ) WITH (DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SecondFG]

本文結語：

　　通過檔案組並將資料檔案分隔到多個物理磁碟，可以實現表和索引分割區，或者隔離資料庫的表和索引對象，提升效能。

本文出自 “我們一起追過的MSSQL” 部落格，轉載請與作者聯絡！