資料庫索引建立的3個建議

資料庫索引建立的3個建議

index 可以有效幫助資料庫查詢更有效率，降低 logical Reads、Table Scan 等

另一方面，過多的索引也會造成資料更新、修改、新增時，額外的負擔，

因為索引的資料也必須要更新，也會間接造成資料不連續 fragmentation 的問題

那建立索引有沒有建議的參考依據呢? 這裡舉出三個主要 index 的考量

1. 根據Where or Join 的條件

可以根據 Query中， Where 與 join 的欄位來建立相關的索引。

因為 SQL 在進行 Query 的時候，內部運作的流程如下：

SQL optimizer 會先尋找 Where 或是 Join 欄位

• 並且看看這些欄位是否已經有相關的索引 index
• 評估該索引的狀況，例如 Density , 統計資訊等
• 對於 primary/ foreign key進行評估
• 根據上述所收集的資訊，計算出最低 cost 的 query plan 來執行

SELECT p.ProductID,
p.ProductName,
FROM Production.Product p

Where  p.ProductID = ‘1234’

 

這個例子中，該 query 以 ProductID 為查詢條件，因此如果 index 在該 productID 上的話，可以大幅減少 logical reads 的次數。
(  註：Logical reads ：每一次讀取為 1 Page 8K。8 Logical reads 表示讀取 8 次。)

 2. Index 欄位資料型態越小越好

Index 建立的時候，SQL Server 會建立出一個 B Tree，資料型態如果越小，

例如 SmallInt 的選擇成為 index 就會比選擇在 BigInt資料型態的欄位作為Index還要好

同樣的，Integer 的資料型態也會比字串資料型態 (CHAR, VARCHAR, NCHAR, NVARCHAR)的選擇還好

由於選擇索引欄位的資料型態較小，所以建出來的B tree 所需要的節點也會越小，

所佔的記憶體或是磁碟空間也就會比較小。

相對的，每一次讀取的最小單位為 1 Page (8K)，也會減少讀取的次數。

 3. 欄位值的同質性

在選擇索引的欄位時，我們會希望建立索引在資料同質性比較低的欄位上，索引也會比較有效率。

舉例來說，醫院診所的掛號系統，有下列欄位資訊，哪一個會是比較佳的索引欄位?

• 病歷號
• 身分證字號
• 性別

以資料的同質性來說，性別的同質性最高，因為性別只有兩個值，男生或是女生，

針對性別這個來位來說，大部分的值 50% 是相同的，男生或是女生，

對於資料索引查詢來說，並不會有很大的幫助，因此高同質性的欄位並不適合當索引

病歷號呢? 病歷號相對比較適合當作索引

因為一來病歷號每個人都不同，每個人都有唯一的值，二來病歷號通常有一定的流水編號順序，

這也會有助於資料庫建立索引的排序

那身分證字號呢?

身分證字號雖然也有唯一性，但是相對來說較為隨機的號碼，

另外一個更重要的是，當送進來的病人因為身體狀況，暫時無法提供身分證字號的時候，

然到要把他搖醒跟他要到身分證字號才有辦法進行診療建檔嗎?

因此，這個例子中，病歷號碼是比較適合的，因為資料的唯一性、號碼的順序性與商業的屬性等考量

欄位順序

當一個索引是由許多欄位所組成的時候，建立索引的欄位順序也會是一個考量。

因為資料的排序方式會以第一個欄位排序、接著第二個。

例如：病人資料的 index ，所組成的欄位為[病歷號]與[姓名]兩個欄位，

所以該資料的排序就會以病歷號為優先值排序，接著姓名：

總結

Index 的建立主要為提升資料查詢的效率

SQL 會根據 index 欄位資料建立出 Balance Tree

相對的 index 也會讓資料的新增、修改等造成額外的存取負擔

因此，建立Index 的考量，主要有三個建議

1. 根據 where or Join 的欄位，可以大幅改善該 Query 的效能

2. index 資料欄位型態越小越好，可以減少 index 建立時所需要的空間，提高存取的效率

3. 欄位值的同質性 (Density)：同質性越低越好，[性別]就比較不適合當 index ，

相對的病歷號碼就比較適合因為病歷號的同質性很低，而且資料的排序性較高。