Database Index 是什麼？

12月 1 2021

以下會以 PostgreSQL 為例

PostgreSQL 將 table 資料都存成一個一個的檔案，我們稱為 Heap 或 Heap files，Heap file 由多個 Block 組成，每個 Block 為 8KB，Block 裡面的 Item 或叫 Tuple ，就是一行一行的 Row

關係：Heap File > Block > Item

假設我們跑這個 Query

SELECT * FROM users WHERE username = 'Rianna';

PostgreSQL 會從硬碟裡讀取整個 Head File 載入到記憶體，然後執行 Query ，一個一個找，直到最終找到 username 等於 Rianna，我們稱這樣的的查詢方式為 Full Table Scan，通常這樣會導致不好的效能

Index

如果有個方法可以提前知道 Rianna 在哪個 Block，我們是不是可就只載入有 Rianna 在的 Block，就可以快速找到 Rianna 了，這就是 Index，可以想像 Index 就是書的章節目錄，告訴我們哪個章節從第幾頁開始

Index 主要為類型為 B-Tree 的結構，查找的時間為 O(log n) ，比原本的 O(n) 相比快多了

我們可以用各欄位建立 index 索引

用 username 建立 index

CREATE INDEX ON users(username);

會用預設命名規則來命名 index, ex: users_username_idx

如果要為 Index 命名可以用：

CREATE INDEX {YOUR_CUSTOM_NAME} ON users(username);

我們可以利用 EXPLAIN ANALYZE 語法來得到 Query 的時間

EXPLAIN ANALYZE SELECT * 
FROM users
WHERE username = 'Emil30'

我們使用一張約 5000 行 row 的 users table 做測試

沒有 index

加入 index 後

OMG! 竟然快 25 倍

PostgreSQL 會對 Primary key 和 Unique Constraint 的欄位自動產生 Index

我使用的 database 的 GUI 工具是 TablePlus ，可以讓我看到該 table 的所有的 Index

如果是用 pgAdmin 的話，也可用 SQL 印出所有 Table 的 index

SELECT relname, relkind
FROM pg_class
WHERE relkind = 'i';

需要額外的儲存空間：以剛剛 5000 個 user 的 table 來說，資料的大小是 880KB，index 則需 180KB，這個數字聽起來或許沒什麼感覺，但現在大家都會用 AWS, GCP 等雲端的服務，用戶數一多，每個月的金額也是非常驚人的
Insert 和 Update 的速度會變慢，因為 Insert 和 Update 時，需要同時更新 Index

#Database