SQL Server效能調教系列(6)—Index Structure and Tuning

一：前言

Index對資料庫效能有著舉足輕重的作用。Index設計的優劣直接影響到DB執行的效率。所以在做DB Tuning時，一部分會從Index著手處理，SQL Server也提供了很好的工具Database Engine Tuning Advisor，會給出一些建Index和最佳化方面的建議。

 

二：Index概述

這方面在各個部落格論壇上面已經講的比較多了，在此大致總結一下：

1. 資料表的基本結構

當建立一個新表時，系統將在磁碟中分配一段以8K為單位的連續空間；當第一個8K用完的時候，SQL Server指標會自動分配8K的空間。每個8K空間成為一個資料頁（Page），又稱頁面或者資料頁面，並分配0-7的頁號，每個檔案的第0頁記錄引導資訊，叫檔案頭（File Header）；每8個資料頁（64K）的組合形成擴充區（Extent），成為擴充。全部的資料頁的組合形成堆（Heap）。

 

2. 索引的基本概念

建立索引的目的就是提高資料檢索效率，改善資料庫工作效能，提高資料訪問速度。系統資料表sysindexes儲存Index的重要訊息。以B-Tree為儲存結構。

 

3. 資料表掃描與索引的使用

沒有索引時，訪問表的資料時按照Table Scan，平均效率比較低。

建立索引時，訪問表的資料時按照Index Scan/Seek，平均效率很高。

 

4. 叢集索引和非叢集索引(Clustered Index and Non Clustered Index)

相同點：

• 以B-Tree為儲存結構存放的一組資料頁
• 不同階的節點包含指向另一個階的資料頁
• 子節點包含所有的索引值
• 在sysindexes中可以找到索引的大小和內容分布
• 都會提高資料查詢的效率

不同點：

• 葉子節點存放什麼：叢集索引存放實際的資料頁；非叢集索引存放指標

 

注意：子葉層級存放的內容不一樣。

 

5. 覆蓋索引(Covering Index)

索引覆蓋是指建索引的欄位正好是覆蓋查詢條件中所涉及的欄位,這裡需要注意的是,必須是從第一個開始覆蓋。

 

6. 死結(DackLock)

請參照

http://www.cnblogs.com/changbluesky/archive/2010/06/10/1753021.html

 

三：效能簡述(Performance)

1. Index片段

1.1 查詢片段

sys.dm_db_index_physical_stats可以用來檢測特定索引、表或索引檢視表的所有索引、資料庫中所有索引或所有資料庫中所有索引中的片段。

重要欄位:

avg_fragmentation_in_percent	邏輯片段（索引中的無序頁）的百分比
fragment_count	索引中的片段（物理上連續的葉頁）數量
avg_fragment_size_in_pages	索引中一個片段的平均頁數

 

1.2. 重建索引與重組索引(rebuild and reorganize)

無論何時對基礎資料執行插入、更新或刪除操作，SQL Server 資料庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導致索引中的資訊分散在資料庫中（含有片段）。當索引包含的頁中的邏輯排序（基於索引值）與資料檔案中的物理排序不匹配時，就存在片段。片段非常多的索引可能會降低查詢效能，導致應用程式響應緩慢。通過重新組織索引或重建索引來修複索引片段，提高效能。

• ALTER INDEX IX_IndexName ON dbo.TableName REBUILD WITH (ONLINE=ON)
• ALTER INDEX IX_IndexName ON dbo.TableName REORGANIZE

 

兩種方法的區別：

• 重新組織索引是通過對葉頁進行物理重新排序，使其與分葉節點的邏輯順序（從左至右）相匹配，從而對錶或視圖的叢集索引和非叢集索引的分葉層級進行磁碟重組。
• 重建索引將刪除該索引並建立一個新索引。此過程中將刪除片段，通過使用指定的或現有的填滿因數設定壓縮頁來回收磁碟空間，並在連續頁中對索引行重新排序（根據需要分配新頁）。這樣可以減少擷取所請求資料所需的頁讀取數，從而提高磁碟效能。

 

建議根據片段程度，使用修複片段的最佳方法：

 

2. 選擇正確而的Index

2.1 主要的考量

以範圍查詢

常常需要排序的資料

2.2 次要考量

欄位長度要短

• 會影響所有的非叢集索引
• 非叢集索引的子也曾都包含所有叢集索引的索引值

資料的類型

 

3.建立索引的方針

所有SQL文法的優先性

優先建立多個查詢文法可以共通使用的索引

建立符合索引時，最佳的欄位順序

 

四：總結

 

與書中的索引一樣，資料庫中的索引使您可以快速找到表或索引檢視表中的特定資訊。索引包含從表或視圖中一個或多個列產生的鍵，以及映射到指定資料的儲存位置的指標。通過建立設計良好的索引以支援查詢，可以顯著提高資料庫查詢和應用程式的效能。索引可以減少為返回查詢結果集而必須讀取的資料量。索引還可以強製表中的行具有唯一性，從而確保表資料的資料完整性。

設計良好的索引可以減少磁碟 I/O 操作，並且消耗的系統資源也較少，查詢最佳化工具也能夠很好的利用索引，提高查詢效能。

 

>>>SQL Server效能調校系列入口地址