SQL Server 索引維護與查詢優化完整指南

SQL Server 索引類型概述

SQL Server 索引主要分為兩大類：聚集索引與非聚集索引。兩者在儲存結構與查詢效能上有本質差異，理解其特性是進行效能調校的基礎。

聚集索引：資料的物理排序

當資料表中的某一欄位被設定為主鍵時，SQL Server 會自動為其建立聚集索引。聚集索引決定了資料在磁碟上的物理儲存順序，因此每個資料表只能擁有一個聚集索引。由於它直接對應資料列的實際位置，查詢優化器幾乎會在所有查詢中嘗試利用它。

非聚集索引：獨立的查詢加速結構

非聚集索引與資料表獨立組織，SQL Server 以獨立結構儲存。它包含索引鍵值與指向資料列的書籤（RID 或聚集索引鍵），適合用於頻繁查詢但非主鍵的欄位。

為何查詢優化器會放棄使用索引？

DBA 不應嘗試強制指定索引，而應避免導致優化器「不想用」索引的情況。以下四大常見原因需特別注意：

1. 索引碎片率過高

當索引碎片率超過 40% 時，優化器傾向放棄使用索引，改採全表掃描。建議 DBA 定期監控，碎片率達 30% 即應考慮重組（REORGANIZE）或重建（REBUILD）索引。

2. 索引缺乏唯一性

若索引非唯一，優化器會評估「直接掃描資料表」比「使用索引 + 書籤查找」更有效率，從而放棄索引。

3. 查詢中對索引欄位使用函數

例如 WHERE CONVERT(nvarchar, col) = 'test'，此寫法會導致索引失效，因為函數操作破壞了索引鍵的有序性。應改寫為 WHERE col = CAST('test' AS 原資料型別) 或使用 SARGable 寫法。

4. 複合索引未遵循「最左前綴」原則

建立複合索引 (col1, col2, col3) 時，只有查詢條件包含 col1 才會使用該索引。單獨查詢 col2 或 col3 不會命中。若需針對 col3 查詢，需另建單欄索引或調整欄位順序。

非聚集索引的填充因子設定

聚集索引因資料順序追加，填充因子通常設為 100% 且無碎片。非聚集索引則需預留空間減少分頁分裂，一般建議 70%–80%，極端高寫入場景可降至 65%。重建索引時應依歷史監控數據動態調整。