MySQL 表優化實戰：從配置參數到索引調整的完整清單

前言

隨著併發增長，1GB 記憶體伺服器出現卡頓、甚至每日死機。本文記錄實戰優化歷程：調整核心參數、關閉非必要日誌、優化索引、監控關鍵狀態值，最終穩定服務。

一、配置檔選擇與核心參數

依記憶體選官方範本：my-medium.cnf (128-256MB)、my-large.cnf (512MB-1GB)。複製至 /etc/my.cnf 後重點調整三大參數：

二、實際監控數值分析 (運行 20 天)

key_buffer_size = 128M
Key_read_requests = 650,759,289
Key_reads = 79,112
-- 未命中率 = 0.012% (優)

Open_tables = 256
Opened_tables = 9,046
-- 20 天內 Opened_tables 增長極慢，table_cache=256 足夠

三、日誌策略調整

• 二進位日誌：若不需時間點還原/複寫，註解 log-bin 節省 I/O。警告：關閉後無法 PITR，需加強邏輯備份。
• 慢查詢日誌：長期開啟，long_query_time=10 (或 1-2 秒)，log-queries-not-using-indexes 開啟。

四、連線與緩衝區微調參數

[mysqld]
sort_buffer_size = 1M
max_connections = 120
wait_timeout = 120
interactive_timeout = 120
back_log = 100
read_buffer_size = 1M
thread_cache = 32
thread_concurrency = 4  # 建議 CPU 核心數 x 2

參數說明：

• back_log：TCP 監聽隊列，高併發突發時避免連線拒絕，受 OS somaxconn 限制。
• thread_concurrency：Solaris 專用，Linux 忽略。

五、索引優化實戰原則

1. 慢查詢日誌 / EXPLAIN 找出 type=ALL、Using filesort、Using temporary。
2. 複合索引遵循「最左前綴」、「等值在前、範圍在後」、「覆蓋索引避免回表」。
3. 定期 ANALYZE TABLE 更新統計資訊，避免優化器誤判。
4. 刪除重複/未使用索引：pt-index-usage / sys.schema_unused_indexes。

六、phpMyAdmin 監控入口

推薦使用 phpMyAdmin 「狀態」頁面，圖形化呈現上述關鍵指標趨勢，適合快速巡檢。

七、總結與持續優化循環

1. 基準線：穩定運行一週收集 SHOW GLOBAL STATUS。
2. 單一參數微調 → 觀察 30 分鐘以上。
3. SQL 優化優於參數調整（索引、改寫、分頁優化）。
4. 硬體瓶頸 (CPU/記憶體/磁碟 I/O) 參數無解，需擴容或架構變更 (讀寫分離、分庫分表)。