MySQL 優化與 EXPLAIN：避免全表掃描

簡介

在日常開發中，我們常常會直接寫出看起來「能跑」的 SELECT 語句，卻忽略了資料量成長後的執行效能。全表掃描（Full Table Scan） 是最常見的效能瓶頸：資料庫必須把整個資料表逐行讀取，導致 I/O、CPU 甚至鎖定資源的消耗急速上升。

本文將說明什麼是全表掃描、為何要避免它，並透過 MySQL 內建的 EXPLAIN 工具，教你一步步找出問題、加上適當的索引或改寫查詢，讓資料庫在大資料量下仍能保持快速回應。

核心概念

1. 什麼是全表掃描

當 MySQL 在執行 SELECT、UPDATE、DELETE 時，若無法利用任何索引，就會以 全表掃描 的方式讀取所有資料列。這種情況通常出現在：

查詢條件使用了未被索引的欄位
使用了函式或運算子（例如 WHERE YEAR(date) = 2023）
條件過於寬鬆，導致索引選擇性太低

2. 為什麼全表掃描會拖慢系統

影響面向	說明
磁碟 I/O	需要讀取整個資料檔，對 SSD/機械硬碟都是高負載
CPU	每筆資料都要做比對，CPU 使用率會急升
鎖定	大量資料列被掃描，可能導致行鎖或表鎖，影響其他交易
快取失效	全表掃描會把大量不必要的頁面帶入緩衝池，驅逐掉熱點資料

3. 使用 EXPLAIN 觀察執行計畫

EXPLAIN 可以讓我們看到 MySQL 為查詢選擇的執行方式。最常關注的欄位有：

欄位	含義
type	存取類型，`ALL` 代表全表掃描，`index` 代表只掃描索引，`ref`、`range`、`eq_ref` 為更佳的索引使用方式
possible_keys	查詢可能使用的索引
key	實際使用的索引
rows	估計需要讀取的資料列數
Extra	其他資訊，如 `Using where`、`Using index`、`Using filesort` 等

程式碼範例

以下示範 4 個常見情境，說明如何從全表掃描轉為索引掃描。所有範例皆使用 MySQL 8.0，語法以 SQL 為主。

3.1 基本全表掃描 → 加索引

-- 原始查詢：條件使用未被索引的欄位
SELECT * FROM orders WHERE status = 'completed';

EXPLAIN SELECT * FROM orders WHERE status = 'completed';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ALL	NULL	NULL	500000	Using where

問題：status 欄位沒有索引，導致 type = ALL（全表掃描）。

解決方案：為 status 建立單欄位索引，或根據查詢頻率建立組合索引。

-- 建立索引
CREATE INDEX idx_orders_status ON orders(status);

重新執行 EXPLAIN：

EXPLAIN SELECT * FROM orders WHERE status = 'completed';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ref	idx_orders_status	idx_orders_status	5000	Using where

現在 type 變為 ref，只需掃描約 5,000 筆資料，效能大幅提升。

3.2 使用函式導致全表掃描 → 改寫查詢

-- 使用函式 YEAR() 會使索引失效
SELECT * FROM sales WHERE YEAR(sale_date) = 2023;

EXPLAIN SELECT * FROM sales WHERE YEAR(sale_date) = 2023;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	sales	ALL	NULL	NULL	200000	Using where

原因：YEAR(sale_date) 使 MySQL 必須對每筆資料計算年份，無法使用索引。

解決方案：改寫為範圍條件，並在 sale_date 上建立索引。

-- 建立索引（若尚未有）
CREATE INDEX idx_sales_date ON sales(sale_date);

-- 改寫查詢
SELECT * FROM sales 
WHERE sale_date >= '2023-01-01' 
  AND sale_date <  '2024-01-01';

EXPLAIN SELECT * FROM sales 
WHERE sale_date >= '2023-01-01' 
  AND sale_date <  '2024-01-01';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	sales	range	idx_sales_date	idx_sales_date	8200	Using where

現在使用 range 存取方式，僅掃描 8,200 筆，效能提升顯著。

3.3 多條件查詢 → 建立複合索引

SELECT * FROM products 
WHERE category_id = 5 AND price BETWEEN 100 AND 200;

EXPLAIN SELECT * FROM products 
WHERE category_id = 5 AND price BETWEEN 100 AND 200;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	products	ALL	NULL	NULL	150000	Using where

分析：category_id 與 price 均未被索引，導致全表掃描。

最佳做法：根據查詢的篩選順序，建立複合索引 (category_id, price)。

CREATE INDEX idx_prod_cat_price ON products(category_id, price);

再次檢查：

EXPLAIN SELECT * FROM products 
WHERE category_id = 5 AND price BETWEEN 100 AND 200;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	products	range	idx_prod_cat_price	idx_prod_cat_price	3400	Using where

使用 range 只掃描 3,400 筆，明顯減少 I/O。

3.4 大量文字搜尋 → 使用全文索引（FULLTEXT）

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE);

EXPLAIN SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE);

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	articles	ALL	NULL	NULL	500000	Using where

原因：若未建立 FULLTEXT 索引，MySQL 只能做全表掃描。

解決：

ALTER TABLE articles 
ADD FULLTEXT idx_ft_title_content (title, content);

再次執行 EXPLAIN：

EXPLAIN SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE);

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	articles	const	idx_ft_title_content	idx_ft_title_content	1	Using where; Using index

現在只需一次索引查找，效能提升數十倍。

常見陷阱與最佳實踐

陷阱	說明	解決方式
忘記更新統計資訊	索引建立後，MySQL 仍可能因統計資訊過時而選擇全表掃描	定期執行 `ANALYZE TABLE` 或設定 `innodb_stats_on_metadata=0`
過度索引	每個索引都會佔用磁碟與寫入成本，過多索引會降低 INSERT/UPDATE 效能	只為頻繁的 SELECT 建索引，使用 `EXPLAIN` 確認實際使用情況
索引列序不當	複合索引的欄位順序不符合查詢過濾條件的使用順序	依照「最左前綴原則」排列欄位，或使用 `INDEX MERGE` 前先測試
使用 `LIKE '%字串'`	前置通配符會導致無法使用索引	改為全文索引或使用 `LIKE '字串%'` 之類的前綴匹配
資料型別不匹配	比較時 MySQL 需要隱式轉型，會放棄索引	確保欄位與常數的資料型別一致（例如 `INT` vs `'123'`）

最佳實踐：

先寫查詢，再用 EXPLAIN 檢測：每次加入新條件或索引，都要確認 type 已不再是 ALL。
使用覆蓋索引（Covering Index）：若 SELECT 只取索引欄位，MySQL 可以直接從索引返回結果，避免回表。
適度使用分割表（Partition）：對超大表可考慮依日期或區域分割，讓查詢只掃描相關分區。
設定合理的 innodb_buffer_pool_size：確保常用索引能駐留在記憶體，減少磁碟 I/O。

實際應用場景

場景 1：電商網站的訂單查詢

需求：每日需要產生「狀態為已付款且付款時間在過去 24 小時」的報表。

做法：

CREATE INDEX idx_orders_status_paid_at 
ON orders(status, paid_at);

查詢：

SELECT order_id, user_id, total 
FROM orders 
WHERE status = 'paid' 
  AND paid_at >= NOW() - INTERVAL 1 DAY;

使用 EXPLAIN 可確認 type = ref 且 rows 為數千，而非全表掃描的百萬筆。

場景 2：部落格平台的全文搜尋

需求：使用者搜尋關鍵字時，必須在 0.2 秒內返回結果。

做法：

ALTER TABLE posts 
ADD FULLTEXT idx_ft_title_body (title, body);

搜尋語句：

SELECT post_id, title 
FROM posts 
WHERE MATCH(title, body) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE) 
LIMIT 20;

此時 EXPLAIN 顯示 type = const，說明僅透過全文索引完成查找，符合即時回應需求。

場景 3：金融系統的交易紀錄檢索

需求：查詢特定帳戶在指定日期範圍內的所有交易，且必須保證不會因全表掃描而阻塞其他交易。

做法：

CREATE INDEX idx_txn_account_date 
ON transactions(account_id, txn_date);

查詢：

SELECT txn_id, amount, txn_date 
FROM transactions 
WHERE account_id = 123456 
  AND txn_date BETWEEN '2023-01-01' AND '2023-12-31';

使用範圍索引 (range) 能把讀取的列數從數百萬降至數千，大幅降低鎖定與 I/O。

總結

全表掃描是 MySQL 效能的主要殺手之一，透過 EXPLAIN 觀察執行計畫、建立適當的索引、避免在條件中使用函式或不必要的通配符，即可將 type = ALL 轉換為 ref、range、index 或 const 等更有效的存取方式。

在實務開發中，建議採取以下步驟：

先寫需求 → 撰寫最直接的 SELECT。
執行 EXPLAIN → 檢查是否出現 ALL。
分析條件 → 看是否能加索引、改寫或使用全文搜尋。
測試與驗證 → 再次執行 EXPLAIN，確保 rows 大幅下降。
持續監控 → 隨著資料量成長，定期 ANALYZE、調整索引與參數。

只要養成「每次查詢都先看 EXPLAIN」的好習慣，避免全表掃描將不再是難題，系統效能也能隨資料規模穩健成長。祝你在 MySQL 優化的路上，越走越順！