資料庫系列 - 2: 索引

這個小節我們來談一下關於資料庫中的索引吧！

何謂索引 index

基本上，索引是一種 B-tree 的資料結構，主要用於加速資料的查詢以及更新，並大幅減少資料庫的壓力。[註1]

一般來說，資料庫使用者只會直接看到存放資料的資料表，然而實際上在 DB 裡面會存放著這些資料以 index 去建立 B-tree 的結構，有多少個 index 就會建立多少的 B-tree ，而實際在進行 SQL 處理時也是用 B-tree 去處理，並把結果更新在我們一般看到的表以及其他 index 的 B-tree 上。

情境

介紹完了索引後，應該不難發現索引的高效能是建立在損失記憶體的狀況下達成的，因此如果不分你我直接將所有欄位都設定 index 的話，那就失去了當初 index 的意義了，不僅失去了記憶體，在更新的同時也會因為需要更新其他的 B-tree 而損失效能。那麼，通常我們會在什麼情境或條件去設定索引呢？以下三點是我們通常會考量的因素：

1. 唯一性：要建立索引的欄位內容必須為唯一值、不能重複。
2. 讀寫次數：如上述所提到，由於每次更動資料都會去改變 B-tree ，因此只有讀的次數大於寫的次數時才會考慮建立索引。
3. 頻率：在查詢與這張表有關的內容時，通常會用哪些欄位去找資料。

類型

索引的類型可以分為叢集索引( Clustered index )以及非叢集索引( NonClustered index )。最好理解的方式是如參考資料的第一篇文章中所提到，叢集索引就有如一本書的目錄，而非叢集索引則像是一本書的附錄。一本書只會有一個目錄，但可以有零到多個附錄。

最重要的是，資料庫中的資料實際上是用叢集索引的方式去排列的，因此叢集索引基本上等於資料存放位置，而非叢集索引存放的是指標，負責告訴你要查的資料在哪個位置。

索引失效

當我們辛辛苦苦建立完索引後，自然會希望此舉能增加我們查詢的效能，然而在實際應用中，有些 SQL 的情境反而會用不到我們建立好的索引，包含以下這些語句：

1. 類型轉換：如果要搜尋的欄位類型與查詢時使用的類型不同，會需要做類型轉換去查詢(但是仍會有正確的查詢結果)。
2. 模糊搜尋：模糊搜尋時會降低索引的效能，尤其模糊的部分在字串前綴時。
3. 特定函數操作：有些函數會讓索引失效，例如 count、sum等。
4. 不等於：使用 != 或者 <> 都會變成全表掃描去確認是否不相等。
5. 聯合索引：在聯合索引的情況下時常會讓索引失效。

Ｂ tree 跟 B+ tree 的差別

在不同資料庫中，索引所採用的 B-tree 結構稍有不同，分為 B tree 跟 B+ tree 兩種。大家常用的Mysql 採用 B+ tree ，而 mongoDB 採用 B tree 。

B tree 結構中的每個節點都會儲存所有數據；而 B+ tree 結構只有葉節點儲存所有數據，非葉節點則只儲存 key 。

主鍵、外鍵以及索引

最後來稍微區分一下這三個會讓人稍微混淆的名詞吧。

主鍵：設立目的是保持數據唯一性，以避免在特殊狀況下寫了兩筆完全相同的資料進去。每張表都只有一個主鍵(可設為多欄位)，此外通常主鍵也會是索引之一。

外鍵：是為了建立表與表間的連結，透過外鍵可以更容易知道表之間是如何串接的。

索引：主要作用是加速資料的查詢，不論是單獨這張表，還是表與表之間的聯合查詢。

[註1] 在此不詳細解釋 Binary-tree 的相關內容，有興趣者可自行去查找資料。

參考資料:

1. https://isdaniel.github.io/dbindex-1/
2. https://medium.com/weis-note/索引是什麼-為什麼加了索引查詢會變快-what-is-indexing-how-the-indexing-makes-select-queries-faster-1310ca328b3c
3. https://wenwender.wordpress.com/2022/08/26/資料庫的索引index工作原理/