索引與查詢效能調校

索引解決什麼問題

沒有索引時，查詢需要逐列掃描整張資料表。資料量小時感覺不出來，但資料成長到數萬、數十萬筆之後，差異會非常明顯。

索引讓資料庫能快速定位到符合條件的資料，代價是額外的儲存空間與寫入時的維護成本。

用 EXPLAIN 判斷查詢

EXPLAIN SELECT * FROM tw_posts
WHERE cat_id = 5 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

重點看四個欄位

Extra 中值得注意的訊息

• Using filesort——排序無法利用索引，需額外排序
• Using temporary——需要建立暫存表，通常出現在分組或排序
• Using index——這是好事，代表只讀索引就能完成，不必回表
• Using where——在取出資料後才過濾

目標是避免 type 為 ALL、且 key 不為 NULL。

常見的索引缺失

一、WHERE 條件的欄位沒有索引

最基本也最常見。經常用來過濾的欄位應該有索引。

二、JOIN 的關聯欄位沒有索引

兩表關聯時，被關聯的欄位若沒有索引，會造成大量的重複掃描。

三、ORDER BY 無法利用索引

排序欄位若不在索引中，或順序不符，就會出現 filesort。

複合索引的順序很重要

複合索引是多個欄位組成的索引。欄位的順序決定了它能被哪些查詢使用。

-- 建立
CREATE INDEX idx_cat_status_time
  ON tw_posts (cat_id, status, created_at);

能使用這個索引的查詢

• 只用 cat_id 過濾
• 用 cat_id + status
• 用 cat_id + status + created_at 排序

不能有效使用的

• 只用 status 過濾——跳過了第一個欄位
• 只用 created_at 排序

原則：從最左邊的欄位開始，連續使用才有效。

順序怎麼決定

1. 等值比較的欄位放前面
2. 範圍比較的欄位放後面——範圍條件之後的欄位無法再用於過濾
3. 排序用的欄位放最後

會讓索引失效的寫法

一、在索引欄位上套用函式

-- 索引失效
WHERE DATE(created_at) = '2026-08-05'

-- 建議
WHERE created_at >= '2026-08-05 00:00:00'
  AND created_at < '2026-08-06 00:00:00'

二、開頭的萬用字元

-- 無法使用索引
WHERE title LIKE '%關鍵字%'

-- 可以使用索引
WHERE title LIKE '關鍵字%'

需要全文檢索時，應使用全文索引或專門的搜尋引擎，而不是用前後萬用字元的 LIKE。

三、型別不一致

欄位是數字型別但查詢傳入字串，或反之，可能導致隱含轉換而無法使用索引。

四、對欄位做運算

-- 索引失效
WHERE price * 2 > 1000

-- 建議
WHERE price > 500

不是加越多索引越好

每個索引都有成本：

• 佔用儲存空間
• 每次寫入都要維護——新增、修改、刪除都會變慢
• 過多索引會讓查詢最佳化器的判斷變複雜

不需要加索引的情況

• 資料量很小的資料表——全表掃描反而更快
• 區別度很低的欄位——例如只有兩三種值的狀態欄位，單獨建索引效益有限
• 幾乎不用於查詢條件的欄位

找出沒在用的索引

可查詢系統的索引使用統計，找出長期未被使用的索引並評估移除。

慢查詢日誌

這是找出問題查詢最直接的方法。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

設定的建議

• long_query_time 先設 1 秒，找出最嚴重的；改善後再逐步調低
• log_queries_not_using_indexes 平常關閉——開啟會產生大量記錄，包含小表的正常全掃描

分析日誌

可使用專門的分析工具彙整，找出「總耗時最多」的查詢——不一定是單次最慢的那個，而是執行頻繁又不夠快的那個。

應用層的常見問題

有時候問題不在單一查詢，而在查詢的方式。

迴圈中重複查詢

取出一百筆主資料，再逐筆查詢關聯資料，就是一百零一次查詢。

應改為一次查詢取得所有關聯資料，或使用 JOIN。這通常是效能問題的最大來源。

撈取不必要的欄位

-- 不建議
SELECT * FROM tw_posts;

-- 建議
SELECT post_id, title, created_at FROM tw_posts;

只取需要的欄位，尤其是資料表中有長文字欄位時，差異明顯。

沒有分頁

一次撈出全部資料再由應用程式處理，資料量大時會耗盡記憶體。應在資料庫端分頁。

PHP 端的記憶體問題見PHP 錯誤排查與日誌判讀。

調校的順序

1. 開啟慢查詢日誌，找出實際的問題查詢
2. 用 EXPLAIN 分析
3. 補上缺少的索引
4. 檢查應用層是否有重複查詢的問題
5. 最後才調整伺服器參數

順序反過來是常見的錯誤。 參數調到極致，也救不了一個缺索引的查詢。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。