高流量網站的 MySQL 效能調校
你的網站逐漸受到關注，但隨著流量成長，頁面變慢，使用者開始抱怨。資料庫往往是瓶頸所在。MySQL 雖然強大，但需要調校才能應付高併發與大型資料集。本指南提供實用步驟，協助你為高流量網站最佳化 MySQL，涵蓋索引、查詢最佳化、設定、快取與監控。無論你執行的是小型應用或大型平台，這些技巧都能幫助你從 MySQL 伺服器榨出更多效能。
1. 索引：快速查詢的基礎
沒有適當的索引，MySQL 會為每個查詢掃描整張表，這在規模化時是災難。先分析慢查詢，並在 WHERE、JOIN 與 ORDER BY 子句使用的欄位上建立索引。
使用 EXPLAIN 陳述式查看 MySQL 如何執行查詢。尋找 type: ALL（全表掃描），並以 ref、eq_ref 或 range 為目標。同時留意 Using filesort 與 Using temporary，這些表示有額外的工作。
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped' ORDER BY created_at DESC;如果這個查詢很慢，考慮在 (customer_id, status, created_at) 上建立複合索引。欄位順序很重要：先放等值條件，再放範圍或排序欄位。
避免過度索引：每個索引都會增加寫入負擔。定期使用 performance_schema 或 sys.schema_unused_indexes 檢視未使用的索引。
2. 查詢最佳化技巧
即使有索引，寫得不好的查詢仍可能拖垮效能。以下是一些關鍵實務：
- 只選取需要的欄位：避免
SELECT *；只取用得到的欄位。這能減少 I/O 與記憶體用量。 - 使用 LIMIT 分頁：不要一次取回所有資料列，用
LIMIT與OFFSET分頁。對於大型偏移量，改用 keyset 分頁（例如WHERE id > last_id LIMIT 20）。 - 避免在索引欄位上使用函式：
WHERE YEAR(created_at) = 2025會阻止索引使用。改寫為WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'。 - 明智地使用 JOIN：確保 join 欄位有索引且資料型別相同。避免在單一查詢中 join 過多資料表。
- 子查詢優先使用 EXISTS 而非 IN：檢查存在性時，
EXISTS通常效能較好。
啟用慢查詢日誌以找出有問題的查詢。設定 long_query_time = 1（或更低），並使用 pt-query-digest 或 Nginx Log Analyzer（如果你也管理網頁伺服器日誌）等工具分析日誌。
3. MySQL 伺服器設定調校
MySQL 的預設設定較為保守。對於高流量網站，請在 my.cnf（Windows 上為 my.ini）中調整關鍵參數。套用到生產環境前，務必先在預備環境測試變更。
| 參數 | 建議 | 原因 |
|---|---|---|
innodb_buffer_pool_size | 可用 RAM 的 70-80% | 在記憶體中快取資料與索引，減少磁碟 I/O。 |
innodb_log_file_size | 1-2 GB（寫入密集時） | 較大的日誌可降低檢查點頻率，提升寫入吞吐量。 |
max_connections | 依流量而定；監控 Threads_connected | 過高可能導致記憶體耗盡；請使用連線池。 |
query_cache_size | 0（停用） | 查詢快取在 MySQL 8.0 已棄用，且可能造成競爭。 |
tmp_table_size & max_heap_table_size | 64M-256M | 減少複雜查詢使用以磁碟為基礎的臨時表。 |
變更後，重新啟動 MySQL 並監控效能。使用 SHOW STATUS 檢查 Innodb_buffer_pool_read_requests 與 Innodb_buffer_pool_reads 等指標（快取命中率應該很高）。
4. 減少資料庫負載的快取策略
快取是高流量網站的最佳夥伴。實作多層快取：
- 應用層快取：使用 Redis 或 Memcached 儲存查詢結果或計算後的資料。例如，將首頁的產品列表快取 5 分鐘。
- MySQL 查詢快取：在 MySQL 8.0 已棄用；避免使用。
- InnoDB 緩衝池：如前所述，這至關重要。確保它夠大，能容納你的工作集。
- 整頁快取：使用 CDN 或反向代理（如 Nginx）提供靜態 HTML，完全繞過 PHP 與 MySQL。
快取時，一定要設定過期時間，以及資料變更時失效的策略（例如 write-through 或基於時間）。
5. 連線處理與連線池
為每個請求開啟新的 MySQL 連線代價高昂。請使用持久連線或連線池。在 PHP 中，使用啟用持久連線的 mysqli 或 PDO。在 Java 或 Python 等應用伺服器中，使用連線池（例如 HikariCP、SQLAlchemy pool）。
監控 Threads_connected 與 Threads_running。如果 Threads_running 持續超過 CPU 核心數，你可能需要最佳化查詢或水平擴展。
6. 監控與持續改善
效能調校是持續的過程。設定以下監控：
- 慢查詢日誌：定期分析。
- Performance Schema：提供等待、I/O 與鎖定的詳細指標。
- 系統指標：資料庫伺服器的 CPU、記憶體、磁碟 I/O。
- 複寫延遲：如果使用複本，確保延遲最小。
使用 mysqldumpslow、pt-query-digest 或 MySQL Workbench 等工具將效能視覺化。為異常狀況自動化警報。
常見問題
如何在 MySQL 中找出慢查詢？
啟用慢查詢日誌，設定 slow_query_log = ON 並將 long_query_time 設為較低的值（例如 1 秒）。日誌檔會包含超過該時間的查詢。使用 pt-query-digest 或 mysqldumpslow 分析它。
高流量網站的理想 innodb_buffer_pool_size 是多少？
在專用資料庫伺服器上，設為可用 RAM 的 70-80%。這能確保大多數資料與索引都快取在記憶體中，將磁碟 I/O 降到最低。監控緩衝池命中率；對於讀取密集的工作負載，應高於 99%。
我應該使用 MySQL 查詢快取嗎？
不應該。查詢快取自 MySQL 8.0 起已棄用，且可能因互斥鎖競爭而導致效能問題。請改用應用層快取（例如 Redis）或 InnoDB 緩衝池。
準備好分析你的伺服器日誌了嗎？試試我們免費的 Nginx Log Analyzer，深入了解流量模式並最佳化你的技術堆疊。