MySQL 效能調校完整指南：從慢查詢、EXPLAIN、索引到伺服器最佳化

Jesse Yen - 9月 05, 2026

MySQL 資料庫剛建立時，資料量可能只有幾百筆、幾千筆，即使 SQL 寫得不是很好，使用者通常也感覺不到明顯的效能問題。

但當資料成長到幾十萬、幾百萬甚至更多筆之後，相同的 SQL 查詢可能從原本不到 0.1 秒，逐漸變成數秒甚至數十秒。

這時候就需要進行 MySQL 效能調校（MySQL Performance Tuning）。

所謂效能調校，並不是看到資料庫慢了就增加記憶體，也不是看到 SQL 慢了就到處建立索引。

比較合理的流程是：發現問題 → 找出慢查詢 → 分析執行計畫 → 判斷瓶頸 → 修改 SQL 或索引 → 再次測試。


MySQL 為什麼會變慢？

造成 MySQL 效能下降的原因很多，大致可以分成幾個層次：

  • SQL 查詢設計不佳
  • 缺少適當索引
  • 索引設計錯誤
  • JOIN 或子查詢處理大量資料
  • 資料表結構設計不佳
  • 一次讀取過多不必要的資料
  • Transaction 或 Lock 造成等待
  • 記憶體不足
  • Disk I/O 成為瓶頸
  • MySQL Server 參數配置不適合目前工作負載

因此，MySQL 很慢，不代表問題一定出在 MySQL Server。很多時候真正的問題其實只是某一條 SQL。


第一步：先找到哪一條 SQL 很慢

資料庫效能最佳化最忌諱的做法，就是還沒有找到問題，就開始修改設定。

第一步應該先確認：到底是哪一條 SQL 慢？

MySQL 可以利用 慢查詢紀錄（Slow Query Log） 找出執行時間較長的 SQL。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

實際正式環境不應只看「超過幾秒」，還要一起考慮 SQL 的執行頻率。

SQL A：
一次執行 3 秒
每天執行 2 次

SQL B：
一次執行 0.2 秒
每天執行 100,000 次

從整體系統負擔來看，SQL B 反而可能更值得優先最佳化。

因此效能分析至少要同時觀察：

  • 執行時間
  • 執行次數
  • 掃描資料列數
  • 回傳資料列數
  • CPU 使用量
  • I/O
  • Lock Wait


第二步：使用 EXPLAIN 分析 SQL

找到可疑 SQL 之後，下一步通常不是立刻修改 SQL，而是使用 EXPLAIN。

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;

EXPLAIN 可以讓我們看到 MySQL Optimizer（查詢最佳化器）準備如何執行這條 SQL。

分析時可以特別注意：

  • type
  • possible_keys
  • key
  • rows
  • filtered
  • Extra

例如：

type = ALL
key = NULL
rows = 500000

通常表示 MySQL 可能需要掃描大量資料。

但看到 ALL 並不能直接下結論說「一定要建立索引」，還要一起考慮資料量、資料分布、WHERE 條件、選擇性（Selectivity）以及查詢目的。


第三步：理解 Index 索引

索引（Index）是 MySQL 效能調校中最重要的工具之一。

假設有一張 customers :

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    city VARCHAR(50)
);

現在執行：

SELECT *
FROM customers
WHERE email = '[email protected]';

如果 email 沒有 Index，資料量很大時，MySQL 可能必須掃描大量資料。

建立：

CREATE INDEX idx_email
ON customers(email);

就有機會讓 MySQL 更快速定位符合條件的紀錄。

但是，Index 並不是越多越好。因為 Index 本身也需要儲存空間，並且在 INSERT、UPDATE、DELETE 時持續維護。


哪些欄位比較需要 Index？

WHERE 條件

SELECT *
FROM orders
WHERE customer_id = 100;

customer_id 就值得評估是否建立 Index。

JOIN 條件

SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;

JOIN 使用的欄位通常是重要的索引候選。

ORDER BY

SELECT *
FROM orders
ORDER BY order_date;

某些情況下，適當 Index 可以降低排序成本。

GROUP BY

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

也有機會利用 Index 改善執行效率。

Composite Index 複合索引

實務上經常不是只有單一條件，例如：

SELECT *
FROM orders
WHERE customer_id = 100
AND status = 'PAID'
ORDER BY order_date;

這時可以評估複合索引（Composite Index）：

CREATE INDEX idx_customer_status_date
ON orders(customer_id, status, order_date);

但複合索引不是簡單把 WHERE 裡面的欄位全部放進去。欄位順序會影響索引能否有效利用，因此還必須理解 最左前綴原則（Leftmost Prefix Rule）。


為什麼建立 Index，MySQL 還是不使用？

常見原因包括：

  • 資料量太少
  • 條件會取得大量資料
  • Index 選擇性太低
  • SQL 對欄位進行函數運算
  • 資料型別不一致
  • LIKE 條件前方使用 %
  • 複合索引欄位順序不適合
  • Optimizer 判斷 Full Table Scan 成本較低

例如：

WHERE YEAR(order_date) = 2026

與：

WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01'

可能產生完全不同的索引使用方式。


SELECT * 也可能造成效能問題

很多初學者習慣：

SELECT *
FROM orders;

如果實際只需要 order_id、customer_id 與 total，比較合理的寫法是：

SELECT order_id, customer_id, total
FROM orders;

這樣可以降低資料庫讀取量、記憶體使用量、網路傳輸量與 Application 處理量。某些情況甚至可以形成 覆蓋索引（Covering Index）。


JOIN 效能最佳化

JOIN 並不是效能不好，真正需要注意的是：JOIN 的資料表有多大，以及 JOIN 條件是否可以有效定位資料。

SELECT o.order_id, c.name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;

如果資料量很大，就必須觀察：

  • JOIN 欄位是否有 Index
  • MySQL 從哪張 Table 開始讀
  • 每個步驟預估讀取多少 Rows
  • WHERE 是否可以提早縮小結果
  • 是否讀取不必要欄位


Transaction 與 Lock 也會讓資料庫看起來很慢

有時候 SQL 本身執行很快，但 Application 卻卡住。原因可能不是查詢效能，而是正在等待 Lock。

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

如果這個 Transaction 遲遲沒有 COMMIT，另一個 Transaction 又要修改相同資料時，就可能必須等待。

因此資料庫效能問題不能只看 SQL Query，也必須了解 Transaction、Row Lock、Gap Lock、Lock Wait 與 Deadlock。

InnoDB Buffer Pool

如果 SQL 與 Index 都沒有明顯問題，才應該進一步觀察 MySQL Server。

InnoDB 最重要的記憶體區域之一，就是 InnoDB Buffer Pool。

它可以快取經常使用的資料頁與 Index Page，降低重複從磁碟讀取資料的需求。

innodb_buffer_pool_size

但是 Buffer Pool 並不是越大越好，還需要考慮 Server 總記憶體、是否為 Dedicated Database Server、作業系統需要的 RAM、其他服務需要的 RAM，以及實際 Working Set。


MySQL 效能調校正確流程

使用者覺得系統很慢
        ↓
確認到底慢在哪裡
        ↓
找到高成本 SQL
        ↓
Slow Query Log
        ↓
EXPLAIN
        ↓
分析 Rows / Index / Join
        ↓
修改 SQL
        ↓
修改 Index
        ↓
重新測試
        ↓
仍有問題？
        ↓
觀察 Lock / CPU / Memory / Disk I/O
        ↓
最後才考慮 Server Configuration

資料庫效能最佳化不是找到一個神奇參數，MySQL 就突然快十倍；它是一個持續觀察、測量、修改與驗證的過程。

延伸學習

  • MySQL EXPLAIN 怎麼看？
  • MySQL Index 索引完整指南
  • Composite Index 複合索引
  • Covering Index 覆蓋索引
  • MySQL 為什麼沒有使用 Index？
  • Slow Query Log 教學
  • JOIN 如何最佳化？
  • InnoDB Buffer Pool 是什麼？
  • MySQL Transaction、Lock 與 Deadlock
標籤 :

張貼留言

0 留言