萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 效能調優 101 招:從硬體到 SQL 的全方位優化指南

MySQL 效能調優是一項系統工程,涵蓋硬體選型、作業系統調校、MySQL 參數配置、資料庫設計、查詢優化、備份策略等多個維度。本文整理 101 個實戰技巧,助你構建高效能、高可用的 MySQL 系統。

一、伺服器硬體與作業系統調優 (1-24)

儲存子系統

  1. 記憶體優先:確保物理記憶體足以容納整個 InnoDB 資料集,記憶體存取速度比磁碟快數個數量級。
  2. 避免 Swap:不惜一切代價避免交換分區使用,Swap I/O 會嚴重拖垮效能。
  3. 電池備援快取:使用帶電池備援的 RAID 控制器快取(BBWC/FBWC),保證寫入快取資料掉電不丟失。
  4. RAID 10 首選:採用 RAID 10(或更高),兼顧效能與冗餘;避免 RAID 5 校驗開銷。
  5. 分離資料與日誌:將資料檔、Redo Log、Binlog、臨時檔案放在不同物理磁碟/分區,減少 I/O 爭用。
  6. 更多磁碟 = 更快:增加磁碟數量可提升並行 I/O 能力。
  7. 選擇高效能磁碟:SAS 優於 SATA;小容量高轉速磁碟在 RAID 中表現優於大容量低轉速。
  8. 考慮 NVMe/PCIe SSD:為熱點資料分區使用企業級固態儲存,可達 2GB/s+ 寫入。

作業系統層面

  1. vm.swappiness = 0:避免主動 Swap,僅在 OOM 時觸發。
  2. 掛載選項 noatime,nodiratime:減少存取時間更新的元資料寫入。
  3. 使用 XFS 檔案系統:比 ext4 更適合大檔案、高並發場景,無雙重緩衝問題。
  4. 調整 XFS 日誌與緩衝參數:logbufs=8,logbsize=256k 等。
  5. I/O 排程器選 noop 或 deadline:避免 CFQ 在資料庫負載下的延遲。
  6. 強制 64 位元 OS:突破 32 位元記憶體限制,支援大型 Buffer Pool。
  7. 精簡 OS 服務:移除非必要套件與 Daemon,減少資源佔用。
  8. /etc/hosts 預解析:消除 DNS 查詢延遲。
  9. 專機專用:避免其他服務搶佔 CPU/記憶體/磁碟 I/O。

二、MySQL 核心配置優化 (25-39)

  1. innodb_flush_method=O_DIRECT:繞過 OS 快取,避免雙重緩衝(需搭配非 ext3 檔案系統)。
  2. innodb_buffer_pool_size:設為實體記憶體 70-80%,容納熱點資料與索引。
  3. innodb_log_file_size:適度設定(如 1-4GB),平衡檢查點頻率與崩潰恢復時間。
  4. 避免同時設定 innodb_thread_concurrency 與 thread_concurrency,二者衝突。
  5. max_connections:設定合理上限(如 500-1000),防止連接風暴耗盡記憶體。
  6. thread_cache_size:設 16-64,減少執行緒創建/銷毀開銷。
  7. skip_name_resolve:禁用 DNS 反向解析,加速連接建立。
  8. 查詢快取:唯讀/少寫場景開啟;高寫入場景關閉(query_cache_type=0)。
  9. tmp_table_size/max_heap_table_size:設為 64MB-256MB,減少臨時表落盤。
  10. sort_buffer_size:每連接分配,勿設過大(建議 2-4MB),防止 OOM。
  11. 監控 Key_read_requests / Key_reads 比率調整 key_buffer_size(MyISAM)。
  12. innodb_flush_log_at_trx_commit:1 最安全,2 或 0 高效能但有丟失風險。
  13. 建立測試環境驗證配置變更,支援熱重啟測試。

三、Schema 設計與索引優化 (40-62)

  1. 定期歸檔冷資料:縮小活躍資料集,加速查詢與備份。
  2. 索引策略:建必要索引,拒絕過度索引。每個索引都會增加寫入開銷。
  3. 壓縮 TEXT/BLOB:COMPRESSED 行格式或應用層壓縮,減少 I/O。
  4. 字符集選擇:utf8mb4 支援完整 Unicode,效能略低於 latin1,但功能完整。
  5. 謹慎使用觸發器:增加寫入延遲與除錯複雜度。
  6. 正規化 vs 反正規化:核心業務正規化,報表/查詢適度反正規化。
  7. 選擇最小足夠的資料類型:TINYINT vs INT,VARCHAR vs CHAR。
  8. 大欄位垂直拆分:將 BLOB/TEXT 移至獨立表,主表保持精簡。
  9. 定期 OPTIMIZE TABLE / ANALYZE TABLE:回收碎片、更新統計資訊。
  10. InnoDB 表定期重建:ALTER TABLE ... ENGINE=InnoDB 整理碎片。
  11. DDL 策略:大表加欄位考慮 pt-online-schema-change 或 gh-ost。
  12. 按業務選擇儲存引擎:InnoDB(事務)、MyISAM(唯讀)、Archive(日誌)、Memory(暫存)。
  13. 會話資料外移 Redis/Memcached:避免頻繁寫入 MySQL 產生碎片。
  14. VARCHAR 替代 CHAR:節省空間(UTF8 除外,固定長度)。
  15. Schema 變更小步快跑:單一變更、灰度發布、可回滾。
  16. 所有變更先在預演環境驗證,模擬生產負載。
  17. 配置檔「少即是多」:只調整理解的參數,避免盲目複製模板。
  18. 疑難雜症回歸預設配置,逐步調優。

四、查詢優化實戰 (63-86)

  1. 開啟 Slow Query Log:long_query_time=1,配合 pt-query-digest 分析。
  2. EXPLAIN 分析執行計畫:關注 type、key、rows、Extra。
  3. 定期回歸測試核心查詢:統計資訊變化會導致執行計畫劣化。
  4. 避免 COUNT(*) 全表掃描:維護計數表或使用近似值。
  5. 查詢保持一致:相同參數化查詢才能命中 Query Cache(已棄用)或 Prepared Statement 快取。
  6. GROUP BY 替代 DISTINCT:可利用索引排序避免臨時表。
  7. WHERE/GROUP BY/ORDER BY 列建立複合索引,遵循最左前綴原則。
  8. 索引保持簡單:避免重複索引、冗餘索引(idx_a + idx_a_b)。
  9. USE INDEX / FORCE INDEX 干預錯誤選擇。
  10. SQL_MODE=STRICT 暴露資料品質問題。
  11. 小基數列 UNION 替代 OR:利用索引合併。
  12. INSERT ... ON DUPLICATE KEY UPDATE 替代「先查後更新」。
  13. 避免 MAX() 全掃:索引列 + ORDER BY DESC LIMIT 1。
  14. 禁用 ORDER BY RAND():大表效能殺手,見文章 72422 優化方案。
  15. LIMIT M,N 深分頁效能差:改用游標/Keyset Pagination(WHERE id > last_id LIMIT N)。
  16. UNION ALL 替代子查詢:減少臨時表開銷。
  17. 更新加鎖策略:SELECT ... LOCK IN SHARE MODE / FOR UPDATE 控制並發。
  18. 重啟後預熱:SELECT ... FROM table FORCE INDEX(idx) WHERE ... 讓熱點頁載入 Buffer Pool。
  19. TRUNCATE TABLE 替代 DELETE 清空大表:瞬間完成,不產生 Binlog(非事務)。
  20. 避免 SELECT *:只取需要欄位,減少網路傳輸與解析開銷。
  21. 持久連接/連接池:減少握手開銷,注意 wait_timeout 配置。
  22. 基準測試含負載:單查詢快不代表高並發下快,用 sysbench/tpcc-mysql。
  23. SHOW PROCESSLIST / Performance Schema 實時診斷阻塞與慢查詢。
  24. 生產鏡像資料驗證可疑查詢:真實資料分布才能暴露真實計畫。

五、備份與恢復策略 (87-101)

  1. 從備庫備份:不影響主庫效能,避免主庫鎖表。
  2. 備份前停止 Replication:STOP SLAVE SQL_THREAD 保證一致性切面。
  3. 物理冷備份:停庫複製資料目錄,最快恢復速度(需相同版本/架構)。
  4. mysqldump --master-data=2 --flush-logs:備份同時記錄 Binlog 位點,支援 PITR。
  5. 不信任 LVM 快照:資料庫一致性無保證,恢復風險高。
  6. 單表導出:mysqldump db table,支援單表級恢復。
  7. mysqldump --single-transaction --quick --routines --triggers --events:InnoDB 熱備份黃金參數組合。
  8. 壓縮備份:| gzip 或 pigz 並行壓縮,節省空間與傳輸時間。
  9. 備份驗證機制:定期在測試環境恢復驗證,未測試的備份 = 無備份。
  10. Binlog 保留策略:expire_logs_days 配合備份週期,支援 PITR 窗口。
  11. 多地備份:異地/雲端存儲,防災難級故障。
  12. 備份監控告警:成功/失敗/耗時/大小全方位監控。
  13. 恢復演練 SOP:文檔化恢復步驟,定期演練,量化 RTO/RPO。

總結

MySQL 調優無銀彈,核心在於:理解業務存取模式、建立監控基線、小步快跑驗證、持續迭代優化。這 101 招不是清單,而是工具箱——根據你的瓶頸選擇合適的工具,才能事半功倍。

copyright © 萬盛學電腦網 all rights reserved