做後端這麼多年，慢查詢大概是我半夜被叫起來的前三名原因。流量一上來，原本毫秒級的查詢突然變成幾秒，連線池被塞滿，整個服務就跟著躺平。多數人第一反應是「加台機器」或「加快取」，但很多時候真正的病因只是少了一個索引，或加了一個沒被用到的索引。這篇講我實際在交易所訂單系統、金流串接上怎麼抓慢查詢、怎麼判斷該加什麼索引，以及那些「明明加了索引卻沒用」的真實的坑。背景以 PostgreSQL 和 MySQL（InnoDB）為主，兩邊我都踩過。
先建立 B-Tree 的心智模型
絕大多數的索引都是 B-Tree。你不需要去背 B-Tree 的演算法，但你一定要有一個直覺：B-Tree 是一份排好序的目錄。
我自己的比喻是字典。一本英文字典的內容是按字母排序的，當你要查 serendipity 這個字，你不會從第一頁一頁一頁翻，而是直接翻到 S 開頭那一區，再往下縮小範圍。B-Tree 索引就是這個東西：它把欄位的值排好序，存成一棵多層的樹，查找的時候從根節點往下走幾層就能定位到資料。
這個心智模型會直接決定你能不能判斷一個索引有沒有用。從「排好序的目錄」這個角度，你馬上能推導出幾件事：
- 等值查詢（WHERE status = 'paid'）很快，因為就像翻字典直接定位。
- 範圍查詢（WHERE created_at > '2026-01-01'）也很快，因為排好序之後，一個範圍就是連續的一段。
- 排序（ORDER BY created_at）可以直接用索引，因為索引本身就是排好的，省掉一次排序。
- 但「字尾比對」沒辦法用，因為字典是按開頭排序的。你問我「所有結尾是 tion 的字」，字典幫不了你，這就是 LIKE 後面講的坑的根源。
只要你心裡有這張排好序的目錄，後面所有的判斷都會變得很自然。
聚簇與非聚簇的差別
有一個細節值得先講清楚，因為它影響你對「回表」的理解。
在 MySQL 的 InnoDB 裡，主鍵是聚簇索引，也就是資料本身就是按主鍵順序存放的，主鍵索引的葉子節點直接就是整列資料。而你另外建的二級索引，葉子節點存的是主鍵值，不是整列。所以當你用二級索引查到資料、但要的欄位不在索引裡時，它得拿著主鍵再去聚簇索引撈一次，這個動作叫回表。
PostgreSQL 的模型不太一樣，它的表是 heap，索引都指向實體位置（ctid），沒有聚簇索引的概念（除非你手動 CLUSTER，而且它不會自動維持）。但回表的概念類似：索引找到位置後，還要去 heap 撈實際的列。
知道有回表這件事，你才會理解後面講的覆蓋索引為什麼有用。
用 EXPLAIN 看執行計畫，而不是用猜的
我看過太多人加索引是憑感覺，加完也不驗證。正確的做法只有一個：讓資料庫告訴你它打算怎麼執行這個查詢。這就是 EXPLAIN。
EXPLAIN 給你的是查詢規劃器「打算」怎麼做，不會真的執行。而 EXPLAIN ANALYZE 會真的把查詢跑一遍，給你實際的執行時間和實際掃過的列數。我的原則是：線上排查一律用 EXPLAIN ANALYZE，但對寫入語句要小心，它真的會執行（所以對 UPDATE/DELETE 要包在交易裡 ROLLBACK，或乾脆只測 SELECT）。
全表掃描 vs 索引掃描
你要學會在執行計畫裡認出兩個關鍵字。
在 PostgreSQL：
- Seq Scan：循序掃描，也就是全表掃描，從頭到尾翻一遍。
- Index Scan：用索引定位，再回表撈資料。
- Index Only Scan：只用索引就拿到所有要的欄位，不回表，這是覆蓋索引的效果。
- Bitmap Heap Scan：介於兩者之間，先用索引收集一批符合的位置，排序後一次性去 heap 撈，適合命中量中等的情況。
在 MySQL 的 EXPLAIN，重點看 type 這個欄位，由好到壞大致是：const、eq_ref、ref、range、index、ALL。看到 ALL 就是全表掃描，那通常是警訊。另外 key 欄位告訴你實際用了哪個索引，rows 是它估計要掃的列數，Extra 裡的 Using index 代表覆蓋索引、Using filesort 代表它得額外排序、Using temporary 代表用了暫存表，這兩個出現時要特別留意。
一個真實的例子
當年交易所的訂單表有上千萬列，後台要查某個會員某段時間的訂單，SQL 大概長這樣：
SELECT * FROM orders WHERE user_id = 12345 AND created_at BETWEEN '2026-05-01' AND '2026-05-31' ORDER BY created_at DESC
一開始只有主鍵索引，EXPLAIN 出來是 Seq Scan，rows 估計上千萬，實際跑要好幾秒。我先看清楚這個查詢的形狀：等值條件是 user_id，範圍條件是 created_at，排序也是 created_at。這直接決定了我要建的複合索引怎麼排，下一段細講。
重點是：先看執行計畫、看清楚查詢的形狀，再決定加什麼索引，而不是反過來。
複合索引：最左前綴與欄位順序
複合索引（多欄位索引）是實戰裡最常用、也最容易加錯的東西。核心只有兩個概念：最左前綴原則，以及欄位順序。
最左前綴原則
一個建在 (a, b, c) 上的複合索引，你可以把它想成「先按 a 排，a 相同的再按 b 排，b 也相同的再按 c 排」，就像電話簿先按姓排、姓相同再按名排。
從這個排序方式，你就能推出它能服務哪些查詢：
- WHERE a = ? 可以用
- WHERE a = ? AND b = ? 可以用
- WHERE a = ? AND b = ? AND c = ? 完整用上
- WHERE a = ? AND c = ? 只能用到 a，c 用不到（因為中間跳過了 b）
- WHERE b = ? 完全用不到（沒有從最左邊的 a 開始）
這就是最左前綴原則：查詢條件必須從索引的最左欄位開始、連續使用，索引才能發揮作用。電話簿你只知道名、不知道姓，是沒辦法快速定位的。
欄位順序怎麼排
這是真正需要經驗的地方。我的原則是：
- 等值條件的欄位放前面，範圍條件的欄位放後面。 因為一旦走到範圍條件那一欄，後面的欄位在索引裡就不再是全域有序的了。
- 選擇度高（區別力強）的欄位放前面，能更快把候選範圍縮小。但這要跟上一條一起權衡。
- 如果有 ORDER BY，盡量讓排序欄位接在等值條件後面，這樣可以直接用索引的順序，避免額外的 filesort。
回到剛剛訂單那個例子。查詢是 user_id 等值、created_at 範圍兼排序。按我的原則，索引應該建成 (user_id, created_at)：user_id 是等值放最前面，先把幾千萬列縮到單一會員的幾百列；created_at 接在後面，因為在固定 user_id 之下，這段索引是按 created_at 排好序的，所以範圍掃描和 ORDER BY created_at DESC 都能直接吃這個順序，連 filesort 都省了。
加上這個索引之後，EXPLAIN 從 Seq Scan 變成 Index Scan，rows 從上千萬掉到幾百，查詢時間從幾秒變成幾毫秒。這就是加對索引的差別。
反過來，如果我傻傻地建成 (created_at, user_id)，那麼 user_id 的等值條件就只能在一個很大的時間範圍裡慢慢過濾，效果差很多。同樣的兩個欄位，順序不同，威力天差地遠。
覆蓋索引：讓查詢不用回表
前面講過回表的代價。覆蓋索引的概念就是：如果一個查詢需要的所有欄位都在索引裡，資料庫就完全不用回表，直接從索引拿資料回傳。在 PostgreSQL 你會看到 Index Only Scan，在 MySQL 的 Extra 你會看到 Using index。
舉個金流對帳的例子。我們有個排程要定時統計某個商戶當天的成功交易筆數和金額：
SELECT amount FROM payments WHERE merchant_id = ? AND status = 'success' AND created_at >= ?
如果索引只建在 (merchant_id, status, created_at)，查到符合的列之後，因為要 amount，還得每一列都回表撈一次。當符合的列有幾萬筆時，這幾萬次回表是實打實的隨機 IO。
我的做法是把 amount 也帶進索引。在 PostgreSQL 可以用 INCLUDE 子句：建索引在 (merchant_id, status, created_at) 並 INCLUDE (amount)，這樣 amount 存在索引的葉子節點但不參與排序。在 MySQL 直接把 amount 放進複合索引最後面也能達到覆蓋效果。加上之後，這個統計查詢變成 Index Only Scan，完全不回表，速度有明顯提升。
但覆蓋索引不是免費的。你把越多欄位塞進索引，索引就越大、寫入時要維護的成本越高。我只會對那種高頻、固定欄位、又對延遲敏感的查詢做覆蓋索引，不會無腦把所有欄位都塞進去。
選擇度：低基數欄位別亂加索引
這是新手最常犯的錯：看到 WHERE 裡有某個欄位，就想替它加索引。但索引不是萬靈丹，加在低基數欄位上往往沒用，甚至有害。
選擇度指的是這個欄位有多少不同的值，相對於總列數。 不同值越多、選擇度越高，索引越有效。
最經典的反例就是 status 這種欄位。假設訂單表的 status 只有 paid、unpaid、cancelled 三個值，而其中 paid 佔了八成。你在 status 上單獨建索引，當你查 WHERE status = 'paid' 時，這個索引指向全表八成的列。資料庫的規劃器很聰明，它會算一下：與其用索引找出八成的列再一個個回表（大量隨機 IO），不如直接全表循序掃描（循序 IO 更快）。結果就是你建了索引，它還是選擇 Seq Scan，索引白佔空間又拖慢寫入。
我的判斷原則：
- 單獨對布林、狀態列舉、性別這類低基數欄位建索引，通常沒意義。
- 但低基數欄位放進複合索引裡，當作前綴的一部分，常常是有意義的。像前面的 (merchant_id, status, created_at)，status 雖然基數低，但它跟在高基數的 merchant_id 後面，能進一步縮小範圍，這是合理的。
- 如果你的場景是「只查某一種少數狀態」，例如只查 status = 'failed' 而失敗只佔極小比例，那 PostgreSQL 的部分索引（partial index，加 WHERE status = 'failed' 條件）會非常省，索引只收錄那一小撮列。
為什麼有索引卻沒被用到
這是最讓人抓狂的情境：明明加了索引，EXPLAIN 卻還是 Seq Scan。我遇過的原因大致這幾種，全都是真實的坑。
型別不符導致隱式轉換
這個坑我吃過好幾次。假設 user_id 欄位是字串型別（varchar），但程式或手動查詢時寫成數字：
WHERE user_id = 12345（欄位是字串，但傳了數字）
在 MySQL，這會觸發隱式型別轉換。麻煩的是它的轉換規則是把字串那邊轉成數字，等於對整欄做了運算，索引直接失效，變成全表掃描。反過來如果欄位是數字、你傳字串，通常還好。這種 bug 很陰險，因為查詢結果是對的，只是慢，平常測試資料量小根本看不出來，上線資料量一大就爆炸。我的原則是：應用層送進來的參數型別，一定要跟欄位型別嚴格對齊。
對索引欄位做函式運算
只要你對索引欄位本身套了函式或運算，索引就用不上了，因為索引存的是原始值、不是運算後的值。經典例子：
- WHERE DATE(created_at) = '2026-05-01' ——對 created_at 套了 DATE()，索引失效。正確寫法是改成範圍：WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'，這樣才吃得到索引。
- WHERE amount + fee > 1000 ——對欄位做運算同理。
- WHERE UPPER(email) = ... ——對 email 套 UPPER。
如果你真的非得對運算結果查詢，PostgreSQL 支援表達式索引（建索引在 lower(email) 這種表達式上），可以對症下藥。但更多時候，是查詢寫法可以改成不對欄位動手。
LIKE 前綴萬用字元
回到一開始字典的比喻。LIKE 'abc%'（萬用字元在後面）可以用索引，因為這等於是查「開頭是 abc」的範圍，字典查得到。但 LIKE '%abc' 或 LIKE '%abc%'（萬用字元在前面）就用不到 B-Tree 索引，因為字典沒辦法幫你找「結尾是某串」的字。
實務上要做這種「包含某子字串」的搜尋，B-Tree 幫不了你，要嘛上全文檢索（PostgreSQL 的 GIN + tsvector，或 MySQL 的 FULLTEXT），要嘛就上專門的搜尋引擎（Elasticsearch 之類）。別硬要用 LIKE '%x%' 在大表上搜尋，那一定是全表掃描。
規劃器判斷不划算
有時候索引存在、查詢也寫對了，但規劃器就是選擇不用它，因為它估算用索引反而更慢（前面 status 那個例子就是）。這時候要先相信規劃器，它通常是對的。但如果你確定它估錯了，先檢查統計資訊是不是過期——PostgreSQL 跑 ANALYZE、MySQL 跑 ANALYZE TABLE 更新統計，常常就解決了。我很少會去用強制索引的 hint，那是最後手段，因為它會讓查詢計畫僵化，未來資料分布變了反而更糟。
索引的代價：天下沒有白吃的午餐
索引讓讀變快，但它不是免費的，我看過有人替一張表加了十幾個索引，結果寫入慢到不行。索引的代價主要有兩塊。
- 寫入變慢。 每次 INSERT、UPDATE、DELETE，所有相關的索引都要跟著維護更新。一張表上索引越多，寫入的成本越高。在高併發寫入的場景（像搶購、撮合下單），這個成本是很實在的。
- 佔用儲存空間。 索引是額外的資料結構，欄位多的複合索引、覆蓋索引尤其佔空間。
所以加索引永遠是取捨：你在用寫入效能和儲存空間，換讀取效能。我的原則是：
- 只加真的會被查詢用到的索引。 從慢查詢日誌和實際查詢模式出發，而不是想像。
- 定期檢查有沒有沒被用到的索引。 PostgreSQL 可以查 pg_stat_user_indexes 看每個索引的使用次數，長期是零的就該考慮砍掉。
- 注意重複或被涵蓋的索引。 如果你已經有 (a, b)，再單獨建一個 (a) 通常是多餘的，因為 (a, b) 的最左前綴已經涵蓋了對 a 的查詢。
分頁深翻的坑與游標分頁
這是我認為最值得單獨拿出來講的實戰坑，因為它太常見，幾乎每個有列表頁的系統都會中。
傳統分頁用 OFFSET / LIMIT：
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET 100000
問題出在 OFFSET。資料庫沒有辦法「直接跳到第十萬筆」，它必須從頭掃過、數過、丟棄前面十萬筆，才開始拿你要的二十筆。OFFSET 越大，被白白掃過丟棄的列就越多。第一頁很快，翻到第五千頁就慢到不行。我當年後台的訂單列表就中過這個招，前面的頁面都正常，客服一翻到很後面就 timeout。
解法是游標分頁（keyset pagination / cursor pagination）。概念是：不用「跳過幾筆」，而是用「上一頁最後一筆的值」當作起點往下取。
第一頁：
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC, id DESC LIMIT 20
記住這頁最後一筆的 created_at 和 id。下一頁：
SELECT * FROM orders WHERE user_id = ? AND (created_at, id) < (上一頁最後的 created_at, 上一頁最後的 id) ORDER BY created_at DESC, id DESC LIMIT 20
這裡用了 (created_at, id) 這樣的元組比較，是為了處理 created_at 有重複值的情況——光靠 created_at 沒辦法穩定分頁，所以一定要再帶一個唯一的欄位（通常是主鍵 id）當第二排序鍵打破平手。這個查詢吃 (user_id, created_at, id) 這個索引，每次都是直接定位到起點往後拿二十筆，不管翻到第幾頁都一樣快。
游標分頁的代價是：你沒辦法直接跳到「第 N 頁」，只能一頁一頁往下或往上。但說真的，無限捲動和「下一頁」的使用情境佔了絕大多數，真正需要任意跳頁的後台才考慮其他方案。只要是會深翻的列表，我預設一律用游標分頁。
線上加索引要小心鎖表
最後講一個會出人命的操作：在正在服務的線上資料庫上加索引。
加索引要掃描整張表來建立結構，這在大表上可能要好幾分鐘甚至更久。問題是預設行為可能會鎖表：
- MySQL 早期版本加索引會鎖住整張表的寫入，幾百萬列的表加索引，期間所有寫入全部卡住，等於服務中斷。比較新的 InnoDB 支援 Online DDL，加索引時用 ALGORITHM=INPLACE、LOCK=NONE 可以不阻塞 DML，但還是要實測確認，而且某些操作仍會短暫鎖。對超大表，業界常用 pt-online-schema-change 或 gh-ost 這類工具，原理是建一張新表慢慢拷貝資料再切換，把鎖的影響降到最低。
- PostgreSQL 預設的 CREATE INDEX 會鎖住該表的寫入。一定要用 CREATE INDEX CONCURRENTLY，它會用不阻塞寫入的方式建索引，代價是建得比較慢、而且不能放在交易區塊裡，萬一失敗還會留下一個無效索引要你手動清掉。
我的原則很簡單：
- 線上加索引前，先確認表有多大、預估要多久。 小表隨便加沒差，大表一定要走不鎖的路徑。
- PostgreSQL 一律用 CONCURRENTLY。 MySQL 大表用專門的線上 DDL 工具。
- 挑離峰時段做，並且全程盯著。 監控鎖、連線數、複製延遲（replication lag），加索引在主庫跑、同步到從庫也是要時間的，這段延遲在金流系統裡是要命的。
加索引明明是為了讓系統更快，結果加的當下把服務搞掛，這種事我寧可多花十分鐘確認，也不要賭。
小結
索引這件事，講穿了就是一句話：讓資料庫少做白工。你心裡有那張排好序的目錄，就能判斷一個查詢能不能用上索引；你會看 EXPLAIN，就不用憑感覺猜；你懂最左前綴和欄位順序，就能把複合索引排對；你知道選擇度、回表、覆蓋索引，就不會亂加沒用的索引；你記得型別轉換、函式運算、LIKE 前綴這些坑，就不會納悶「為什麼索引沒被用到」。
最後我想說的是：索引是取捨，不是越多越好。先用 EXPLAIN 看清楚問題，加最少、最對的索引，驗證它真的被用上，然後留意寫入和線上加索引的代價。把這套流程內化了，慢查詢就不再是半夜叫醒你的理由。