SQL筆記：關於索引的知識與索引使用率查詢

最近有需要使用到SQL資料庫索引的相關知識，因此在網路上找了一些文章吸取經驗，並且把相關的重點筆記在此，供給日後查閱。

資料庫索引相關名詞與知識

SQL提供二種索引：「叢集索引」採用平衡樹（B-Tree）架構、「非叢集索引」採用堆積（Heap）架構。
叢集索引：任何一個資料表最多就只能有一個「叢集索引」，該資料表中的「實體資料列」的順序就會依據叢集索引鍵的值做排列，值變動率高的欄位適合被當成叢集索引。
非叢集索引：每個資料表能建立多個「非叢集索引」，索引單純存放著指標資料對應到「叢集索引」內實體資料所存放的位置，值更新頻率較低或理想情况下根本不更新的欄位適合被當成非叢集索引。
在SSMS管理介面中若設定了主索引鍵（PK；Primary Key），它會自動把這個主索引鍵加入成「叢集索引」，當然要打破這個規矩也不是不能，主要就是在設定主索引鍵PK前先到索引設定介面設定好哪個欄位是要進行「叢集索引」後，再去設定主索引鍵PK即可，但傳統上設定PK就自動將其添加為叢集索引這個動作，其實已經適用於絕大部分的應用場景。
在SSMS管理介面中，若對某資料表點選「+」這個符號，可以快速的綜覽「索引鍵」、「索引」兩種資料夾中關於索引方面資訊，其中的，。
在SSMS管理介面中，「索引鍵」資料夾中最主要是展示PK、FK等資料表關聯資訊。
在SSMS管理介面中，「索引」資料夾中則主要是在表達「叢集索引」與「非叢集索引」方面的陳述與管理，如可以透過右鍵來進行「重建（Rebuild）索引」、「重新組織（Reorganize）索引」或「停用（Disable）索引」等動作。
出現外部碎片（External Fragmentation）或內部碎片（Internal Fragmentation）時，就需要進行重建或重組索引，可以用「DBCC SHOWCONTIG('YourTableName') WITH ALL_INDEXES 」查詢相關數據，不過我認為會查詢資料庫索引相關的文章表示這張資料表表的寫入或查詢已經很慢了，這時候應該不太會去討論資料索引碎片化的程度才對。

如何觀察某資料表現在的索引狀態

答案當然是下SQL查詢指令：

DECLARE @cTableName AS nvarchar(1000)
SET @cTableName = 'YourTableName'

--查看索引綜覽
EXEC sp_helpindex @cTableName

--查看索引欄位細節
SELECT
  B.object_id,
  B.index_id,
  B.name,
  B.type,
  B.type_desc,
  A.column_id,
  COL_NAME(A.object_id, A.column_id) AS column_name
FROM
  sys.index_columns AS A
LEFT OUTER JOIN
  sys.indexes AS B
ON
  A.object_id =  B.object_id AND A.index_id = B.index_id
WHERE
  A.object_id
IN
(
  SELECT object_id FROM sys.objects where name = @cTableName
)
ORDER BY
  B.index_id

--索引使用細部資料
SELECT
  B.name,
  A.*
FROM
  sys.dm_db_index_usage_stats AS A
LEFT OUTER JOIN
  sys.indexes AS B
ON
  A.object_id =  B.object_id AND A.index_id = B.index_id
WHERE
  A.object_id
IN
(
  SELECT object_id FROM sys.objects where name = @cTableName
)
ORDER BY
  B.index_id

呈現出來的結果畫面如下圖：

其中第一個結果其實就可以綜覽這張表所有的索引、索引方式、索引欄位等資訊，第二個結果則是利用T-SQL的方式真的去把這些對應的資訊找出來（可以真實的知道索引裡面包含了那些欄位與編號），第三個結果則是我們必須觀看的重點：

user_seeks：這個索引被使用在 index seek 次數（該索引被真正使用到的次數，而且該索引回傳的資料沒有讀取整份索引的資料）
user_scans：這個索引被使用在 index scan 次數（該索引被完整掃瞄過的次數，如果該索引屬於叢集索引的話，就代表該表格被TableScan的次數。通常這裡的數字越高也意味著這不是一個好的索引，應該可進一步重新規劃索引）
user_lookups：這個索引被使用在 index lookup 次數（該索引被更新寫入的總次數，也就是當執行到INSERT、UPDATE、DELETE指令並且異動到該索引的次數）
user_updates：索引更新次數
last_user_seek：上一次執行 index seek 的時間
last_user_scan：上一次執行 index scan 的時間
last_user_lookup：上一次執行 index lookup 的時間

SQL筆記：關於索引的知識與索引使用率查詢

資料庫索引相關名詞與知識

如何觀察某資料表現在的索引狀態

相關連結

SqlServer ClusteredIndex NonClusteredIndex