MySQL 性能優化非單點突破，而是系統工程。本文依據 MySQL 官方參考手冊 (8.0 版本核心章節) 精華，結合生產實戰經驗，將優化體系歸納為四大維度：SQL 與索引優化、儲存引擎深度調優、系統資源與架構優化、鎖與併發控制。拒絕碎片化技巧，建立全鏈路優化思維。
優化必從 EXPLAIN 開始。重點關注三欄：
ref / eq_ref / range，避免 ALL (全表掃描) 與 index (全索引掃描)。Using index (覆蓋索引)、Using where、Using temporary、Using filesort 等關鍵提示。EXPLAIN ANALYZE (8.0.18+) 可顯示實際執行耗時與迴圈次數，比估算值更可信。
idx_name(name(10)))。COUNT(DISTINCT col)/COUNT(*) > 0.1 才適合建索引，性別/狀態等低基數列勿單獨建索引。SELECT a,b FROM t WHERE c=1 → INDEX(c,a,b)，避免回表。WHERE 條件下推至儲存引擎層過濾，減少上層回表次數。| 反模式 | 優化重寫 | 核心原理 |
|---|---|---|
SELECT * FROM t WHERE f(x)=val |
避免函數作用於索引列；改用 WHERE x = f_inv(val) 或生成列 + 索引 |
函數導致索引失效 |
SELECT * FROM t WHERE a=1 OR b=2 |
UNION ALL 拆分 + 索引合併 |
OR 通常導致全表掃描 |
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2) |
LEFT JOIN ... WHERE t2.id IS NULL 或 NOT EXISTS |
子查詢優化器易退化；8.0 支援 Semi-Join 物化 |
ORDER BY rand() LIMIT 10 |
業務層隨機 ID 或 WHERE id >= (SELECT FLOOR(MAX(id)*RAND()) FROM t) LIMIT 10 |
臨時表 + filesort 極慢 |
SELECT * FROM t LIMIT 1000000, 10 |
延遲關聯：SELECT * FROM t INNER JOIN (SELECT id FROM t LIMIT 1000000, 10) tmp USING(id) |
減少回表行數 |
ANALYZE TABLE 定時更新統計資訊 (8.0 持久化直方圖 ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 100 BUCKETS)。range 查詢將離散 IO 轉順序 IO，SET optimizer_switch='mrr=on,mrr_cost_based=off' 強制開啟。ALTER TABLE t ALTER INDEX idx_name INVISIBLE，驗證索引無用後再真刪除，零風險。[mysqld] # === 內存與緩衝 === innodb_buffer_pool_size = 12G # 物理內存 60-80%，專用 DB 可到 80% innodb_buffer_pool_instances = 8 # 避免單實例鎖爭用，每實例 ≥ 1GB innodb_buffer_pool_chunk_size = 256M # 動態調整塊大小 # === 寫入優化 === innodb_flush_log_at_trx_commit = 1 # ACID 嚴格模式；可放寬為 2 (秒級刷盤) 換極致寫入 innodb_log_file_size = 2G # 大日誌減少 Checkpoint 衝刷，啟動恢復稍慢 innodb_log_files_in_group = 4 innodb_log_buffer_size = 64M # 大事務防刷盤 innodb_flush_method = O_DIRECT # 繞過 OS Cache，避免雙重緩衝 innodb_io_capacity = 4000 # SSD IOPS 基線，NVMe 可設 10000+ innodb_io_capacity_max = 8000 # === 併發與鎖 === innodb_thread_concurrency = 0 # 0=無限制，高併發建議 64-128 避免上下文切換風暴 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_purge_threads = 4 innodb_lock_wait_timeout = 5 # 業務層超時控制，避免長事務堵塞 # === 索引與統計 === innodb_stats_persistent = ON innodb_stats_auto_recalc = ON innodb_stats_transient_sample_pages = 64 innodb_adaptive_hash_index = ON # 熱點頁自動建 Hash，唯讀負載可關閉 # === 8.0 新特性 === innodb_redo_log_capacity = 4G # 8.0.30+ 替代 log_file_size，動態調整 innodb_parallel_read_threads = 4 # 全表掃描並行度
key_buffer_size：專用機 25-30% 內存，但僅緩存索引，數據依賴 OS Cache。ALTER TABLE t DISABLE KEYS; LOAD DATA...; ALTER TABLE t ENABLE KEYS; 避免逐行維護索引。concurrent_insert = 2：尾部插入與讀取並行 (無空洞時)。OPTIMIZE TABLE 整理碎片，或 myisamchk -rq 離線重建索引。innodb_file_per_table=ON) 必開，利於單表回收空間、遷移、I/O 隔離。ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8)：適合歷史冷數據，CPU 換 I/O，讀多寫少場景測試後啟用。large-pages + OS HugeTLB)：減少 TLB Miss，大內存機器 (＞64GB) 可獲 2-5% 性能增益。noatime,nodiratime 掛載選項關閉訪問時間更新。sync_binlog=1 保證不丟事務；innodb_flush_log_at_trx_commit=1 雙 1 配置最安全。vm.swappiness=1，防止 InnoDB Buffer Pool 被換出。numactl --interleave=all 啟動 mysqld，避免跨節點內存訪問延遲。vm.nr_hugepages = (Buffer Pool 大小 / 2MB) + 10%。max_connections 設業務峰值 * 1.5，thread_cache_size 設 100+ 避免頻繁創建銷毀線程。skip-name-resolve 禁用 DNS 反解，消除連接建立延遲。PREPARED STATEMENT 緩存執行計劃 (8.0 持久化至 mysql.server_cost / mysql.engine_cost 成本表)。SELECT ... FOR UPDATE / LOCK IN SHARE MODE 僅在事務中使用，且儘量縮短持鎖時間。innodb_deadlock_detect=ON (預設)，捕獲 ERROR 1213 後應用層指數退避重試。ALTER TABLE) 需獨佔 MDL，會阻塞後續所有 CRUD。pt-online-schema-change / gh-ost (影子表 + Binlog 同步)，零鎖表風險。innodb_autoinc_lock_mode = 2 (Interleaved)：Statement-based Replication 必須用 1/0；Row-based (RBR) 建議用 2，批量插入無自增鎖爭用，性能提升 30%+。SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 結束後恢復。| 維度 | 關鍵指標 | 告警閾值參考 |
|---|---|---|
| 吞吐 | QPS / TPS, Questions, Com_select/insert/update/delete |
同比下降 30% |
| 延遲 | P99 Latency, Slow_queries, Innodb_row_lock_time_avg |
P99 > 500ms |
| 緩衝池 | Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests (命中率) |
命中率 < 99% |
| 併發 | Threads_running, Threads_connected |
Running > CPU 核心 * 4 |
| I/O | Innodb_data_reads/writes, Innodb_log_write_requests, Innodb_os_log_fsyncs |
IOPS 擊穿磁盤上限 |
| 鎖/事務 | Innodb_row_lock_waits, Innodb_deadlocks, Trx_rseg_history_len (Undo 積壓) |
History Len > 100K |
| Replication | Seconds_Behind_Master, Relay_log_space |
延遲 > 10s |
slow_query_log=ON, long_query_time=0.5 (秒), log_queries_not_using_indexes=ON。pt-query-digest 聚合 Top N，關注 Rows_examined/Rows_sent 比率 (大於 10:1 必優化)。EXPLAIN ANALYZE 對比執行計劃，FLUSH TABLES t FOR EXPORT 導入生產數據樣本測試。pt-osc，SQL 重寫灰度發布。Data_Length + Index_Length × 1.3 (碎片/備份/臨時表) × 增長率 (年) × 保留年限。Buffer Pool + (Sort/Join/Read Buffer × Max_Connections) + Dict/MDL/Thread Stack + OS Reserve (4-8GB)。SELECT client_id, COUNT(DISTINCT client_id), created, total, passed, hard_bin, soft_bin, (passed/total) AS yield FROM system_pref_report GROUP BY client_id ORDER BY id; -- 錯誤：非聚合列 id 不在 GROUP BY 中，MySQL ONLY_FULL_GROUP_BY 會報錯
GROUP BY client_id，全表掃描 + 臨時表 + Filesort。COUNT(DISTINCT client_id) 在分組內冗余 (分組列本身唯一)，等價於 COUNT(*)。ORDER BY id 無意義且觸發排序。-- 1. 建立覆蓋索引
ALTER TABLE system_pref_report ADD INDEX idx_client_created (client_id, created, total, passed, hard_bin, soft_bin);
-- 2. 重寫 SQL (移除冗余 DISTINCT、無效 ORDER BY)
SELECT
client_id,
COUNT(*) AS cnt,
MIN(created) AS first_created, -- 假設取最早時間
SUM(total) AS total_sum,
SUM(passed) AS passed_sum,
SUM(hard_bin) AS hard_sum,
SUM(soft_bin) AS soft_sum,
SUM(passed)/SUM(total) AS yield
FROM system_pref_report
GROUP BY client_id;
-- 3. 若需分頁，使用延遲關聯
SELECT t.* FROM system_pref_report t
INNER JOIN (
SELECT client_id FROM system_pref_report GROUP BY client_id LIMIT 1000, 20
) tmp USING (client_id);
MySQL 優化是「業務理解 + 原理透徹 + 數據驅動」的循環。沒有銀彈，只有在特定業務場景、數據分佈、硬體條件下的最優解。建立標準化的 監控體系 → 慢查詢閉環 → 容量規劃 → 變更管理 流程，讓優化從「救火」變為「防火」，才是 DBA 與架構師的核心價值所在。
延伸閱讀：
• MySQL 8.0 官方優化章節
• Percona Toolkit (pt-query-digest, pt-osc, pt-stalk)
• 《High Performance MySQL》第 4 版 (8.0 覆蓋)
• 《MySQL 8.0 核心技術與最佳實踐》 (周彥偉 著)
