MySQL 效能調優是一項系統工程,涵蓋硬體選型、作業系統調校、MySQL 參數配置、資料庫設計、查詢優化、備份策略等多個維度。本文整理 101 個實戰技巧,助你構建高效能、高可用的 MySQL 系統。
vm.swappiness = 0:避免主動 Swap,僅在 OOM 時觸發。noatime,nodiratime:減少存取時間更新的元資料寫入。logbufs=8,logbsize=256k 等。noop 或 deadline:避免 CFQ 在資料庫負載下的延遲。/etc/hosts 預解析:消除 DNS 查詢延遲。innodb_flush_method=O_DIRECT:繞過 OS 快取,避免雙重緩衝(需搭配非 ext3 檔案系統)。innodb_buffer_pool_size:設為實體記憶體 70-80%,容納熱點資料與索引。innodb_log_file_size:適度設定(如 1-4GB),平衡檢查點頻率與崩潰恢復時間。innodb_thread_concurrency 與 thread_concurrency,二者衝突。max_connections:設定合理上限(如 500-1000),防止連接風暴耗盡記憶體。thread_cache_size:設 16-64,減少執行緒創建/銷毀開銷。skip_name_resolve:禁用 DNS 反向解析,加速連接建立。query_cache_type=0)。tmp_table_size/max_heap_table_size:設為 64MB-256MB,減少臨時表落盤。sort_buffer_size:每連接分配,勿設過大(建議 2-4MB),防止 OOM。Key_read_requests / Key_reads 比率調整 key_buffer_size(MyISAM)。innodb_flush_log_at_trx_commit:1 最安全,2 或 0 高效能但有丟失風險。COMPRESSED 行格式或應用層壓縮,減少 I/O。utf8mb4 支援完整 Unicode,效能略低於 latin1,但功能完整。TINYINT vs INT,VARCHAR vs CHAR。OPTIMIZE TABLE / ANALYZE TABLE:回收碎片、更新統計資訊。ALTER TABLE ... ENGINE=InnoDB 整理碎片。pt-online-schema-change 或 gh-ost。VARCHAR 替代 CHAR:節省空間(UTF8 除外,固定長度)。long_query_time=1,配合 pt-query-digest 分析。EXPLAIN 分析執行計畫:關注 type、key、rows、Extra。COUNT(*) 全表掃描:維護計數表或使用近似值。GROUP BY 替代 DISTINCT:可利用索引排序避免臨時表。WHERE/GROUP BY/ORDER BY 列建立複合索引,遵循最左前綴原則。idx_a + idx_a_b)。USE INDEX / FORCE INDEX 干預錯誤選擇。SQL_MODE=STRICT 暴露資料品質問題。UNION 替代 OR:利用索引合併。INSERT ... ON DUPLICATE KEY UPDATE 替代「先查後更新」。MAX() 全掃:索引列 + ORDER BY DESC LIMIT 1。ORDER BY RAND():大表效能殺手,見文章 72422 優化方案。LIMIT M,N 深分頁效能差:改用游標/Keyset Pagination(WHERE id > last_id LIMIT N)。UNION ALL 替代子查詢:減少臨時表開銷。SELECT ... LOCK IN SHARE MODE / FOR UPDATE 控制並發。SELECT ... FROM table FORCE INDEX(idx) WHERE ... 讓熱點頁載入 Buffer Pool。TRUNCATE TABLE 替代 DELETE 清空大表:瞬間完成,不產生 Binlog(非事務)。SELECT *:只取需要欄位,減少網路傳輸與解析開銷。wait_timeout 配置。sysbench/tpcc-mysql。SHOW PROCESSLIST / Performance Schema 實時診斷阻塞與慢查詢。STOP SLAVE SQL_THREAD 保證一致性切面。mysqldump --master-data=2 --flush-logs:備份同時記錄 Binlog 位點,支援 PITR。mysqldump db table,支援單表級恢復。mysqldump --single-transaction --quick --routines --triggers --events:InnoDB 熱備份黃金參數組合。| gzip 或 pigz 並行壓縮,節省空間與傳輸時間。expire_logs_days 配合備份週期,支援 PITR 窗口。MySQL 調優無銀彈,核心在於:理解業務存取模式、建立監控基線、小步快跑驗證、持續迭代優化。這 101 招不是清單,而是工具箱——根據你的瓶頸選擇合適的工具,才能事半功倍。