Lesson 4: 效能殺手與救星-Index 索引的原理、複合索引與 Explain 分析
我們很常聽到索引(Index)，大部分工程師也都知道 RDBMS最基本的加速方式就是加上Index，但Index是個雙面刃，加的不好反而會嚴重拖累DB的速度，我們這個章節就來探討，Index的概念以及實務上的用法吧！
為什麼我們需要索引？
想像一本厚達 1,000 頁的字典。
沒有索引 (Full Table Scan)： 如果你想查「Apple」這個字，卻沒有目錄也沒有頁碼順序，你必須從第一頁翻到最後一頁，直到找到為止。這在資料庫中叫做 全表掃描 (Full Table Scan)，是效能殺手。
有索引 (Index Scan)： 字典有按字母排序的側邊標籤。你可以直接翻到 "A" 開頭的區域，快速找到 Apple。
核心概念： 索引是一種特殊的資料結構，用來幫助資料庫快速定位資料，而無需掃描整個資料表。
索引背後的結構
B+tree
雖然我們的DB管理軟體中，在設定索引的地方會看到索引類型：B-tree/Btree，在軟體世界中的B-tree以及B+tree並不一樣。
比較資料來源：Gmini
B+tree：
A. 矮胖型的樹 (矮 = 少 I/O)
特性： B+Tree 每個節點可以存很多個數字（不像二元樹只能存一個）。這使得樹變得非常「寬」且「矮」。
意義： 資料庫讀取每一個節點（Node）通常代表一次 磁碟 I/O（讀取硬碟）。
普通的二元樹如果要存 100 萬筆資料，樹高可能達 20 層（需讀 20 次硬碟）。
B+Tree 存 100 萬筆資料，樹高通常只有 3 層（只需讀 3 次硬碟）。 這就是快的原因！
B. 只有葉子存資料 (Non-Leaf Nodes store ONLY keys)
Root 與中間層 (黃色/粉色)： 您會發現圖中的上層節點 只存數字 (Key)，不存實際資料。它們純粹是「路標」。
葉子層 (綠色)： 所有的實際資料（或是指向資料的硬碟地址 Address）都只存在最底層。
好處： 因為中間節點不存資料，體積很小，可以一次塞很多個路標進記憶體（RAM），讓搜尋路徑幾乎都在記憶體中完成，最後一下才去讀硬碟。
C. 葉子之間的連結 (Linked Leaves) -> 這是 B+Tree 的殺手鐧
請看圖中綠色方塊之間的 雙向箭頭 (
<==>)。這是 B+Tree 與 B-Tree 最大的不同。 所有葉子節點被串成了一個有序的鏈結串列 (Linked List)。
情境：範圍搜尋 (Range Scan)
當你下 SQL：
SELECT * FROM users WHERE age > 20資料庫只要透過樹找到
20的位置（一次搜尋）。接下來不需要再回頭查樹，直接沿著葉子的箭頭往右抓取所有資料即可。這讓範圍搜尋的效率極高。
3. 搜尋流程模擬 (以找 ID = 45 為例)
讀取 Root (30, 60)： 目標 45 在 30 和 60 之間 -> 走中間的路。
讀取 NodeB (40, 50)： 目標 45 在 40 和 50 之間。
讀取 Leaf3 (40...50)： 進入葉子節點，在裡面逐一比對，找到 45，取出資料指針。
回傳資料。
總共只經歷了 3 個節點的讀取。
其他的索引方法
Hash
原理： 像 Hash Map 一樣，算出一組雜湊值來對應位置。
適用： 只有 完全相等 (=) 的查詢。
缺點： 不支援範圍查詢（你不能查 Hash > 100），也不能用來排序。通常 B-Tree 已經夠快，所以 Hash 索引在實務上比較少用。
PosgreSQL的殺手鐧
GIN
用途： JSONB 欄位搜尋、全文檢索 (Full Text Search)、陣列 (Array)。
例子： 如果你的資料庫存了一堆 JSON，想查 { “brand": ["apple", "samsung"] } 裡面有沒有 "samsung"，用 B-Tree 會慢死，用 GIN 會飛快。
GiST
用途： 地理資訊系統 (GIS)，例如算座標距離「離我最近的 5 家餐廳」。
BRIN
用途： 超大數據量的時間序列資料（例如 Log 記錄、IoT 感測器數據）。
特色： 它非常節省空間，它不存每一筆資料的位置，只存「這一區塊的資料是從 2023-01-01 到 2023-01-02」。適合那種資料已經按時間排序寫入的場景。
索引也是要慎用
既然索引是加速我們查找資料，那每個欄位都集上所引最讚了。
這是錯誤的概念喔，為什麼不要每個欄位都加上索引呢？
索引的應用上會產生一張index table，如果過多index table會發生以下災難：
1. 寫入效能的災難
原理： 索引表在硬碟上是實體的檔案。
情境： 假設一個
User表有 10 個欄位，你把它們全部都加了索引。後果： 當你執行 新增一筆資料 (
INSERT) 時，資料庫其實在做 11 件事：把資料寫入主資料表 (Main Table)。
去更新第 1 個索引的 B+Tree。
去更新第 2 個索引的 B+Tree。
...以此類推直到第 10 個。
結論： 這會導致寫入速度變慢 10 倍以上。這就是為什麼高頻率寫入的 Log 表通常不建太多索引的原因。
2. 儲存空間的災難 (Storage Bloat)
原理： 索引就是把該欄位的資料複製一份出來排序。
情境： 如果你的資料表很大（例如 1 億筆），而你對一個很長的字串欄位（例如
address）建索引。後果： 你的索引檔（Index Size）可能會比資料檔（Data Size）還要大！這不僅浪費硬碟，還會讓備份（Backup）和還原（Restore）的時間變得極長。
3. 查詢效能的「反效果」
很多人以為索引多只是寫入慢，查詢一定快，其實不一定。
干擾優化器 (Confusing the Optimizer)： 資料庫的「查詢優化器」是負責為 SQL 查詢找出最有效率執行計畫的資料庫元件。當索引太多時，資料庫的「查詢優化器」在分析 SQL 時，需要評估的路徑變多，有時反而會因為統計資訊誤差而選錯索引（例如明明該做全表掃描比較快，它卻堅持走一個效率差的索引）。
搶佔記憶體： 資料庫為了快，會把常用的索引載入記憶體 。如果你的廢索引太多，它們會佔滿珍貴的記憶體空間，把真正熱門的資料或索引擠出去，導致系統必須頻繁去讀硬碟，進而拖垮整體查詢效能。
❌ 低基數 (Low Cardinality) 的欄位不要加索引：
例子：
性別(男/女)、啟用狀態(True/False)。原因： 假設有 100 萬人，50 萬男、50 萬女。
如果你查
WHERE gender = 'Male'，資料庫透過索引發現有一半的人都要抓出來。這時候，直接掃描全表 (Full Table Scan) 其實比跑索引更快。因為跑索引要「先查索引、再回表抓資料」，來來回回跳躍式讀取反而慢。
✅ 高基數 (High Cardinality) 的才適合：
例子：
身份證字號、Email、手機號碼、UUID。原因： 一查就能精準定位到極少數的幾筆，這才是索引發揮威力的地方。
附件提供：索引決策檢核表 (Index Decision Checklist)
什麼是複合索引？
關於複合索引
簡單說，就是 「一個索引結構裡面，同時存了兩個以上的欄位值」。
假設我們有一個索引 INDEX idx_name_age (name, age)。 在 B+Tree 的葉子節點裡，資料是這樣排列的：
('Aaron', 20) -> ('Aaron', 30) -> ('Bob', 18) -> ('Bob', 25) -> ('Charlie', 40)
請注意排序邏輯：
先看第一個欄位
name，按字母順序排。只有當
name一樣的時候，才會依據第二個欄位age排序。如果
name不一樣（例如Aaron和Bob），後面的age順序是無關緊要的（20, 30, 18, 25... 這是亂的）。
這就是為什麼會有 「最左前綴原則」 —— 因為跳過第一欄，第二欄就是亂序的，索引無法導航。
為什麼要用複合索引？ 兩個單一索引不行嗎？
這是很多初學者會感到困惑的問題，我們來看看根據上面的範例資料。
兩個單一索引會發生什麼事情：
資料庫會這樣做：
先用
INDEX(name)找出所有叫 Aaron 的人（假設有 1000 個）。再用
INDEX(age)找出所有 20 歲的人（假設有 5000 個）。最後在記憶體中把這兩堆資料做 交集運算 (Intersection)，找出重疊的那幾個。
- 缺點： 做了兩次索引掃描，還要做集合運算，效率中等。
一個複合索引會發生什麼事情：
資料庫會這樣做：
直接進入 B+Tree，鎖定
name='Aaron'的區塊。在這個區塊內，因為
age也是排好序的，直接跳到age=20的位置。
- 一次到位，不用做交集運算。
覆蓋索引：
效能的極致
定義： 當你的 索引 (Index) 已經包含了 查詢所需的所有欄位，資料庫就不需要回表 (回主表查資料)。
實例演練
假設表 users 有 id (PK), name, age, city。 我們建立複合索引：INDEX idx_name_age (name, age)。
情境 1：普通的查詢 (需要回表)
SQL
SELECT * FROM users WHERE name = 'Aaron';
流程：
查
idx_name_age索引，找到 Aaron。索引裡只有
name和age，但你要 (包含city)。回表 (Back to Table)： 拿著 PK ID 回去主資料表撈出
city。這會產生額外的 Disk I/O。
情境 2：覆蓋索引 (不需要回表)
SQL
SELECT age FROM users WHERE name = 'Aaron';
流程：
查
idx_name_age索引，找到 Aaron。你要找
age？剛好！索引裡面就有age的值。直接回傳結果，完全不讀取主資料表！
效能： 這是 SQL 優化的最高境界，速度極快（因為索引檔通常比資料檔小很多，且都在記憶體中）。
小技巧： 如果你發現某個高頻率的查詢只差一兩個欄位就能達成「覆蓋索引」，有時候我們會故意把那幾個欄位加進複合索引裡（即使它們不參與 WHERE 篩選），純粹為了讓查詢能直接從索引取值。
複合索引的欄位順序怎麼排？
建立 INDEX(a, b) 和 INDEX(b, a) 是完全不同的。如何決定誰在左邊？
原則一：高過濾性的放左邊 (Selectivity)
誰能篩掉更多資料，誰就當老大。
例子：
WHERE status = 'Paid' AND uuid = 'xxx'status只有幾種可能（過濾性低）。uuid是唯一的（過濾性極高）。選擇： 雖然理論上
uuid一查就準，但在複合索引中，通常把最常用作「等值查詢 (=)」且基數高的欄位放前面。不過在這個例子中，單獨建uuid就夠了。更好的例子：
WHERE department_id = 10 AND last_name = 'Wang'如果部門很少人，姓 Wang 的很多 ->
(department_id, last_name)如果部門幾萬人，姓 Wang 的很少 ->
(last_name, department_id)
原則二：考慮排序 (ORDER BY)
索引可以幫你省下排序的時間。
SQL:
WHERE a = 10 ORDER BY b最佳索引：
(a, b)。- 因為在
a=10的區塊內，b已經是排好序的，資料庫直接拿出來就是結果，不用再做 FileSort。
- 因為在
原則三：範圍查詢 (Range) 放最後
這就是之前提到的「範圍會導致索引中斷」。
如果你常查
WHERE a > 10 AND b = 5。錯誤索引：
(a, b)。因為a是範圍，走到a之後索引就斷了，b用不到。正確索引：
(b, a)。先鎖定b=5(精確)，再在裡面找a > 10(範圍)。
5. 總結：複合索引設計
精確查詢 (
=) 的欄位往左邊放。(
>,<,BETWEEN) 的欄位放在索引的右邊。SELECT的欄位如果能被索引包住，效能會起飛。(A, B)不等於(B, A)，一定要看你的 SQL 怎麼寫。
Explain 分析
為什麼需要 Explain？
工程師優化 SQL 不能靠「感覺」或「猜測」。
❌ 「我覺得加了這個索引應該會變快吧？」
✅ 「我看過 Explain，這個查詢走了
ref模式，只掃描了 10 行，確認優化成功。」
EXPLAIN 指令就像是 SQL 的 X 光機，它不會真正執行查詢，而是顯示資料庫優化器 (Optimizer) 「打算」 怎麼執行這句 SQL。
使用方式
用法非常簡單，只要在原本的 SELECT 語法最前面加上 EXPLAIN 關鍵字即可。
SQL
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
關鍵指標解讀
以下看關鍵的5個指標：type , possible_keys , key , rows , extra
type(查詢類型) — 最重要的指標這代表資料庫是用什麼方式找到資料的。效能由 好 (上) 到 壞 (下) 排序
type 值 意義 說明 system/const常數查找 最快。通常是用 Primary Key 或 Unique Key 查單一筆資料。 eq_ref唯一關聯 在 JOIN 時，使用的是 Primary Key 或 Unique Key。 ref普通索引 使用一般的索引查找 (Non-Unique Index)。回傳可能多於一筆。 range範圍搜尋 使用索引進行範圍查找。如 BETWEEN,>,<,IN。index全索引掃描 ⚠️ 陷阱！ 別看到 index 就以為好。這代表掃描了整棵索引樹，雖然比全表掃描快一點點，但依然很慢。 ALL全表掃描 ❌ 最慢。從頭翻到尾 (Full Table Scan)。資料量大時會導致系統崩潰。 possible_keys(可能的救星)意義： 系統分析後，覺得「有哪些索引」是可以被拿來用的。
用途： 這裡列出的索引不一定會被用。如果這裡是
NULL，代表你完全沒有相關的索引，請立刻去建索引！
key(實際的選擇)意義： 優化器最後 「真正決定使用」 的索引。
重點：
如果是
NULL➝ 代表沒用索引 (走全表掃描)。如果
possible_keys有值，但key是NULL➝ 代表4. 系統覺得「用了索引反而比較慢」（可能是因為要回表太多次），所以決定掃全表。
rows(預估掃描行數)意義： 資料庫預估要找到目標資料，需要讀取多少行。
重點： 這是一個估計值，不是精確值。數字越小越好。
rows: 10000vsrows: 5➝ 當然是 5 的效能好。
Extra(額外資訊) — 魔鬼藏在細節裡這裡是優化器的備註欄，常出現這三種狀況：
Using index(最好)：- 代表使用了 覆蓋索引 (Covering Index)。資料直接從索引檔就拿到了，完全不用讀取資料表，效能極致。
Using filesort(不好)：代表無法用索引排序，資料庫被迫在記憶體或硬碟中進行額外的排序運算。CPU 消耗大。
解法： 調整索引順序以符合
ORDER BY。
Using temporary(極差)：代表查詢太複雜（通常是
GROUP BY或DISTINCT），資料庫必須建立一張「臨時表」來暫存資料。解法： 極需優化 SQL 結構或索引。