Table of Contents

在前幾篇文章裡頭，我們從最基本的 CRUD（SELECT、INSERT、UPDATE、DELETE）到 JOIN 再到 GROUP BY 都已經初步上手了。那麼現在，很多人就會開始問：「資料量越來越大後，為什麼有些查詢跑得越來越慢？這效能是可以提升的嗎？」

「查詢效能優化」可以說是 SQL 領域裡永不退流行的話題。儘管你語法再熟悉，如果沒搭配適當的索引 (Index) 與優化策略，查詢速度很可能會從幾秒變幾分、幾十分，甚至讓系統整個卡死…。

為什麼需要查詢優化？

當資料庫裡的表格越來越大（數十萬、數百萬筆甚至更多），沒有索引或不恰當的查詢方式，就會讓資料庫不得不掃描一整張表 (full table scan)，耗費大量的 I/O 和 CPU 資源。

如果你只有幾百筆資料，SQL 可能還是「秒出」，讓你感覺不到差別；但當資料量達到一定規模時，查詢速度就會急遽下降。更糟糕的是，這種情況會影響到整個系統的效能，甚至可能導致資源耗盡。

因此，好的索引設計 和 優化查詢結構 幾乎是每位後端工程師、資料庫管理者們的必修課。

重點 1：索引 (Index) 的基本原理

索引是什麼？

索引 (Index) 就像一本書的目錄，能讓你快速定位到內容的頁碼。如果沒有做索引，資料庫就必須從第一筆開始往下翻，一路比對到最後一筆，才能確定某個條件的所有符合結果 (也就是全表掃描，或稱做遍歷)。反之，如果有索引的話，資料庫建可以先在「目錄」裡找到符合的索引位置之後，再直接定位到相關資料列，這樣就能大幅減少搜尋的範圍。

常見的索引結構

B-Tree 索引

這是大多數關聯式資料庫的主要索引結構 (MySQL 的 InnoDB、PostgreSQL、MS SQL… 都以 B-Tree 為主)。適合處理「範圍搜尋」(range query) 或「精準搜尋」(point lookup)。像 WHERE age BETWEEN 18 AND 30，或 WHERE username = 'Alice' 這樣常見的寫法，都算是 B-Tree。

Hash 索引

部分資料庫或特殊引擎有支援 (像 PostgreSQL 可以建立 Hash Index)，它的特性是查詢速度快，但只適用「等值搜尋」(=)，範圍的查詢就不支援。

其他索引（如 GiST、GIN、R-Tree…）

主要用在全文檢索、地理空間索引等特殊場景，初學者先了解有這類東西即可。

什麼欄位該加索引？

  • 經常出現在 WHERE 條件或 JOIN 條件的欄位，通常適合加索引。例如 user_id, email, order_id。
  • 需要頻繁做範圍搜尋或排序的欄位，也常常需要加索引。例如 created_at。

但這裡要稍微注意的是，索引雖然能提升查詢速度，卻也會佔用額外儲存空間，並在新增/更新/刪除資料時，增加維護索引的成本。因此「索引越多越好」並不正確，還是要考慮整體讀寫比與應用情境。

重點 2：解讀查詢計劃 (EXPLAIN / Execution Plan)

EXPLAIN 是什麼？

大部分的關聯式資料庫都提供一個指令（或語法）來查看 SQL 查詢的「執行計劃 (Execution Plan)」，像是：

  • MySQL/MariaDB 用 EXPLAIN SELECT ...
  • PostgreSQL 可以用 EXPLAIN 或 EXPLAIN ANALYZE SELECT ...
  • MS SQL Server 有圖形化的「Query Execution Plan」
  • Oracle 也有 EXPLAIN PLAN FOR SELECT ...

執行後系統會告訴你，從我們送出查詢指令、一直到我們收到查詢結果這之間，系統在背景下去用怎樣的順序和邏輯來找出我們想要的資料。比如這條查詢使用了哪個索引？預估要掃描多少筆資料？是否進行了「全表掃描 (table scan)」？有沒有用到「索引搜尋 (index seek)」或「索引範圍掃描 (index range scan)」？JOIN 時是用什麼連接方式 (Nested Loop, Hash Join…)？等等

為什麼要看 EXPLAIN？

如果我們送出去的查詢很慢，通常可能是在某處出現了某些效能上的瓶頸。而我們可以透過 EXPLAIN 來知道瓶頸具體的發生點都在哪些地方。

你可以發現很多有趣的行為和細節，很可能跟我們在寫指令時所想像的不一樣。你可能會發現原來系統沒使用你以為會用的索引，導致做了全表掃描；或是原來某個 JOIN 條件寫得有問題，造成大量重複運算…等等。這時我們就可以利用這些資訊來思考，是不是可以藉由改寫原有的 SQL 語法的方式，讓資料庫的查詢效能得到提升。

實例：MySQL EXPLAIN

EXPLAIN
SELECT o.order_id, o.amount, c.customer_name
FROM orders AS o
JOIN customers AS c
   ON o.customer_id = c.customer_id
WHERE o.amount > 1000;

執行後，你可能會看到類似這樣的輸出 (簡化版)：

  • type = range 代表此處對 orders 表採用索引範圍掃描 (range scan)。
  • possible_keys 和 key 顯示資料庫選擇了 idx_amount 這個索引。
  • rows = 500 表示預估只會掃 500 筆資料 (而不是整張表的數十萬筆)。
  • JOIN 到 customers 時，用的是 eq_ref（相等連結），並利用了 PRIMARY 這個主鍵索引。

如果某個查詢寫得不理想的話，很可能原本需要把整張表 50 萬筆資料全都掃一遍。現在透過正確的索引就只需要掃 500 筆，這效能差距就會很明顯。

重點 3：SQL 查詢改寫與條件順序

為什麼要改寫？

即使你已經建立了索引，但如果 SQL 語法寫得不恰當，資料庫可能就無法有效運用這些索引。比如下面就是一個常見的例子：

SELECT *
FROM users
WHERE LEFT(email, 5) = 'admin';   -- 錯誤示範

使用函數處理欄位的問題：即使在 email 欄位上有建立索引，但如果使用 LEFT(email, 5) = 'admin' 這種將欄位包在函數內的寫法，資料庫就無法使用索引來做前置比對 (prefix matching)。

索引友善的查詢寫法：正確的做法是用 email LIKE 'admin%'，此時才能有效利用索引。這是因為它是一個前綴查詢 (prefix query)，B-Tree 索引的結構非常適合處理這種模式。而使用 LEFT(email, 5) 的寫法，由於在欄位上套用了函數，即使有建立索引也會被迫進行全表掃描。

%放在哪裡也會有差別

如果你在搜尋時用百分比符號 % 包住關鍵字（像是 '%abc%'）或把 % 放在最前面（像是 '%abc'），資料庫都沒辦法有效地使用索引來加速搜尋。但如果只在關鍵字後面加上 %（像是 'abc%'），資料庫就能使用索引來快速找到符合的資料。

那如果一定要搜尋中間的關鍵字呢？這時候有幾個可行的解決方案：

  • 全文檢索 (Full-Text Search)：大部分主流的資料庫都有內建的全文檢索引擎，比起使用 LIKE '%keyword%' 這種方式要來得有效率得多。
  • 特殊索引類型：例如 PostgreSQL 的 GIN 索引就非常適合做這類的文字搜尋。這類索引雖然會佔用較多空間，但搜尋效能比起一般的 B-tree 索引要好上許多。

不過如果資料量不大，也沒有一定不能用 LIKE '%keyword%' 就是了。但我們還是可以利用一些方式來多少彌補一些效能的問題：

  • 搭配其他能用到索引的條件來縮小搜尋範圍
  • 設定合理的 LIMIT 來限制回傳筆數

條件順序與 AND/OR 的影響

當在 SQL 查詢中使用多個 AND 條件時，資料庫會自動挑選最快的方式來搜尋。

舉例來說，假設你的資料表有兩個索引：一個在 customer_id 欄位上，另一個在 amount 欄位上，或是有一個結合兩者的索引 (customer_id, amount)，資料庫會自動選擇最有效率的方式來執行查詢。

SELECT *
FROM orders
WHERE customer_id = 1001
 AND amount > 500;

但如果我們把這裡的AND 改成 OR 的話，資料庫通常就無法有效運用複合索引了。

這是因為複合索引的設計主要是為了處理 AND 條件的查詢。此時我們有兩種解決方案：一是採用其他的優化策略 (如為每個條件建立獨立的索引)，二是將查詢拆分成多個使用 UNION 的子查詢。

重點 4：善用 LIMIT、分頁查詢 (Pagination) 與分批讀取

為什麼需要分頁或分批處理？

有時候你需要從資料庫一次撈出幾萬筆資料，但其實顯示在前端或報表裡只需要幾筆關鍵資料。過多的資料傳輸量也會拖慢整體速度。比如我們可以只拿最新 10 筆，而不是整張表。一方面減少網路傳輸量，另一方面避免資料庫產生過重的負擔。

SELECT *
FROM big_table
ORDER BY created_at DESC
LIMIT 10;

使用 OFFSET 的效能隱憂

如果你在分頁時使用 OFFSET，像 LIMIT 10 OFFSET 1000000，有些資料庫仍然得先掃過前面的一百萬筆，再丟棄後再拿 10 筆，這樣效率就不會好了。

這時可以透過 「記錄最後一筆 id」 的方式來做「鍵值分頁 (Keyset Pagination)」，讓每次查詢只要比對「id 大於多少」，就可以繼續往下撈。對於「只往前 / 往後翻頁」的場景效率比較高。

-- 初始查詢

SELECT *
FROM big_table
WHERE id > 0  -- 從開始處查詢
ORDER BY created_at DESC
LIMIT 10;


-- 下一頁查詢（假設 last_seen_id 是上一次查詢的最後一筆 id）

SELECT *
FROM big_table
WHERE id > :last_seen_id
ORDER BY created_at DESC
LIMIT 10;

重點 5：觀察寫入 (INSERT/UPDATE) 與讀取的平衡

索引過多，也會拖慢寫入

每多一個索引，就代表每次新增或更新資料，都要額外維護那個索引。若你的業務場景是高頻寫入 (例如每秒萬筆交易) 又加了一堆索引，寫入效能會嚴重下降。

所以在「讀多寫少」的場景裡 (如報表查詢系統)，可以大膽加更多索引，提高查詢速度；在「寫多讀少」的場景裡 (如即時交易系統)，就要謹慎選擇必要的索引。

觀察實際 Query Patterns 再來決定

建議使用資料庫的慢查詢紀錄或監控工具來找出最需要優化的地方。具體來說，要觀察哪些查詢最常被使用、哪些欄位最常被用來搜尋或篩選。接著，針對這些常用的欄位來加入索引，這樣效果會最好。

另外要定期檢查舊的索引是否還有在使用、以及是否可以合併或移除不需要的索引。這樣可以讓資料庫維持在最好的狀態。

效能優化是不斷迭代的

只要資料庫是持續有在使用的，它就會隨著時間成長，而且業務需求也會隨時間改變，所以它的結構與設計也需要跟著不斷調整。但無論如何，掌握「索引」與「查詢結構」的調整，是我們優化效能的第一步。只要你養成「查詢一寫完，就用 EXPLAIN 看一下」的好習慣，長期下來就能積累不少優化經驗，讓你的 SQL 隨時都能擁有處理大量資料的餘裕喔。

•     •     •
系列文章
  • SQL 新手入門：用 SELECT、INSERT、UPDATE、DELETE 建立你的第一個資料表
  • 搞懂 SQL JOIN：LEFT、RIGHT、INNER、FULL 不再被搞混！
  • GROUP BY 使用指南：3 個實用的範例，教你掌握 SQL 當中的統計與聚合