今天要介紹 SQL 很重要的功能：索引 (Index)

索引是資料庫性能優化中不可或缺的一個工具，正確的使用索引能夠在大型資料庫系統中實現高效的數據檢索。

什麼是索引 (Index)

索引是一種特殊的數據結構，它存儲了資料表中一列或多列的值和這些值在資料表中的物理位置的映射

想像你現在資料庫中有個 users 的資料表，裡面有 1 百萬筆資料。如果你要搜尋特定的用戶，而 users 資料表中沒有索引，資料庫將不得不掃描整個資料表來找到你要的數據，這稱為全表掃描 (sequential scan)，會消耗很多時間和資源。

索引就像一本書的目錄。如果你想快速找到某個主題，你會直接查看目錄，透過目錄你可以快速地找到該主題在書中的哪一頁，而不是一頁一頁地翻閱。在資料庫中，當你對一個有索引的資料表執行查詢時，資料庫能夠用類似的方式快速定位到資料表中的行，從而大大減少查詢時間

這邊我來實際驗證一下加上索引以及沒加上索引的差別

我在我的資料庫建立了一個 users table 並事先寫入 1 百萬筆資料

-- 這邊先故意不加上 Primary key

CREATE TABLE users (
    id SERIAL, 
    name VARCHAR(255), 
    email VARCHAR(255)
)

我們去查詢 id 為 878787 的使用者好了

SELECT * FROM users WHERE id = 878787;

   id   |    name    |            email
--------+------------+------------------------------
 878787 | Elvia Roob | glenn_romaguera@leffler.test
(1 row)

我們用 EXPLAIN ANALYZE 來分析剛剛的 sql 語法所要執行的 查詢計畫 以及 執行時間

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 878787;

                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..15857.43 rows=1 width=46) (actual time=43.447..51.137 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..14857.33 rows=1 width=46) (actual time=38.862..40.451 rows=0 loops=3)
         Filter: (id = 878787)
         Rows Removed by Filter: 333333
 Planning Time: 0.121 ms
 Execution Time: 51.158 ms
(8 rows)

我們先優先關注到 Seq Scan && Execution Time

Seq Scan: 就是 sequential scan，也就是他是執行了全表掃描
Execution Time: 就是這個查詢使用的時間大約是 0.05 秒

好像蠻快的，但是讓我們加上 index 看看結果會如何

-- 幫 id 加上 index 
CREATE INDEX index_users_id ON users (id);

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 878787;

                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using index_users_id on users  (cost=0.42..8.44 rows=1 width=46) (actual time=0.016..0.017 rows=1 loops=1)
   Index Cond: (id = 878787)
 Planning Time: 0.071 ms
 Execution Time: 0.030 ms
(4 rows)

Index Scan:  代表是使用了索引去查詢的
Execution Time: 加上 INDEX 之後同樣的查詢變成 0.00003 秒

如何為你的資料庫加上索引

可以在一個單獨的欄位上加上索引稱為「單一欄位索引」
而有涉及多個欄位的索引稱為「複合索引」或「多欄位索引」

單一欄位索引

當你在一個特定的欄位上建立索引時，資料庫會根據該欄位的值創建一個資料結構，以便更快地查找資料。

例如：

CREATE INDEX idx_users_email ON users (email);

這將建立一個單一欄位索引在 users 表的 email 欄位上。如果有查詢是基於 email 進行的，如：

SELECT * FROM users WHERE email = 'user@example.com';

則該查詢將利用 idx_users_email 索引，從而加速查詢速度。

複合索引

當你在多個欄位上建立索引時，資料庫會根據這些欄位的值組合創建一個資料結構。

例如：

CREATE INDEX index_users_first_last_name ON users (first_name, last_name);

這會在 users 表的 first_name 和 last_name 欄位上創建一個複合索引。如果有查詢是基於 first_name 和 last_name 進行的，如：

SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

則該查詢將利用 index_users_first_last_name 索引，從而加速查詢速度

CREATE UNIQUE INDEX 上述兩種都可以適用 
在資料表上建立一個唯一的索引，不允許使用重複的值：唯一的索引代表著兩筆資料不能有相同的索引值

區別

1. 使用場景：單一欄位索引適用於查詢條件只涉及一個欄位的情況；而複合索引適用於查詢條件涉及多個欄位的情況。

2. 性能：複合索引可以在單個查詢中涉及多個欄位，這可能會帶來更好的性能，特別是在進行 join 操作或對多個欄位進行過濾時。

3. 空間使用：複合索引通常會佔用更多的儲存空間，因為它們需要存儲更多欄位的資料。

選擇索引

選擇正確的索引類型取決於你的具體查詢模式。如果查詢通常只過濾一個欄位，則單一欄位索引可能是最佳選擇。如果查詢通常需要過濾或排序多個欄位，則應考慮使用複合索引。

結語

今天介紹了什麼是索引(index)，以及索引的強大之處

明天將會提到索引的優點與缺點以及在 PostgreSQL 中，有哪幾種不同類型的索引

謝謝大家收看