MySQL 效能調優實戰技巧大全

MySQL 效能調優實戰技巧大全

從啟動參數、SQL 寫法、索引設計、架構優化到硬體選型，系統整理經實戰驗證的 MySQL 調優技巧，助你解決 90% 的效能瓶頸。

一、啟動參數優化

# /etc/my.cnf [mysqld] 區塊核心調優參數

# 連線緩衝
back_log = 64

# 禁用外部鎖
external-locking = 0

# 禁用不需要的引擎
skip-bdb
skip-innodb   # 確定不需 InnoDB 時

# 索引緩衝（專用 DB 伺服器可設 50% 記憶體）
key_buffer = 512M

# 表快取（參考 open_tables / opened_tables）
table_cache = 128

# 禁用 DNS 解析（授權表需用 IP）
skip-name-resolve

# 慢查詢日誌
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes

# 其他需查閱手冊依硬體調整：
# sort_buffer_size, net_buffer_length, read_buffer_size, read_rnd_buffer_size,
# myisam_sort_buffer_size, thread_cache_size, query_cache_size, max_binlog_cache_size

二、SQL 寫法與索引設計鐵律

1. InnoDB 禁用 SELECT COUNT(*)：無內部計數器，全表掃描極慢。改用觸發器維護計數表或 SELECT COUNT(1) FROM t WHERE id>0 利用二級索引。
2. 優先 MyISAM：讀寫效能高，缺點是表鎖。除非需事務/外鍵/行鎖，否則 MyISAM 總體更快。
3. 善用 EXPLAIN：每條重要 SQL 必跑 EXPLAIN，觀察 type、key、rows、Extra。
4. 大表統計用摘要表：高併發大表 GROUP BY 極慢。建摘要表定時更新，查詢摘要表秒級回傳。
5. ORDER BY 欄位順序匹配索引順序：索引 idx(a,b,c)，查詢 ORDER BY a,b,c 可用索引排序；ORDER BY b,a 無法使用。
6. WHERE 常數條件前置：WHERE status=1 AND name='abc'，status=1 選擇性高則前置，快速過濾。
7. JOIN 關聯欄位型別長度完全一致：INT JOIN INT、VARCHAR(20) JOIN VARCHAR(20)，否則隱含轉型導致索引失效。
8. 字串索引用前綴長度：CREATE INDEX idx_name ON t(name(10))，通常 10-15 字元區分度足夠，大幅縮小索引。
9. 選最小合適資料型別：ENUM > TINYINT > SMALLINT > MEDIUMINT > INT > BIGINT。節省空間 = 更多資料進記憶體 = 更快。
10. 熱表放入熱快取：

    
    SET GLOBAL hot_cache.key_buffer_size = 134217728;  -- 128MB
    CACHE INDEX `hot_table` IN hot_cache;
    LOAD INDEX INTO CACHE `hot_table`;
    

11. 拆解複雜查詢：一條慢 SQL 拆成多條簡單 SQL，總耗時常更低，且減少鎖表風險。
12. 避免 WHERE 條件用函數：WHERE create_time > UNIX_TIMESTAMP(NOW()) → 應用層計算時間值，SQL 寫 WHERE create_time > 1700000000。
13. LIKE 不以 % 開頭：name LIKE 'abc%' 可用索引；name LIKE '%abc' 全表掃描。
14. 多用預存程序：約 22% 效能提升（減少網路往返、解析優化開銷）。
15. 定期 OPTIMIZE TABLE：MyISAM 整理碎片，提升索引效率。
16. 大量異動後 ALTER TABLE ... ORDER BY：按常用查詢順序重排物理存放，加速範圍掃描。
17. 避免頻繁更新 VARCHAR/BLOB/TEXT：MyISAM 易產生碎片。
18. 單表超 500 萬行考慮分表：按 ID 區間、時間、Hash 等策略。
19. 欄位定義 NOT NULL + DEFAULT：NULL 使索引/統計/比較更複雜。
20. 用 LOAD DATA 替代大量 INSERT：快 20 倍。
21. 表名/欄位名儘量短：user.name 優於 user.user_name，減少網路傳輸、解析開銷。
22. DELAY_KEY_WRITE = 1：MyISAM 延遲索引寫入，關表才刷盤。風險：崩潰需 myisamchk 修復。
23. 讀寫分離架構：主庫寫、從庫讀，大幅提升系統吞吐。

三、硬體選型指南

四、編譯優化（源碼安裝時）

• 使用 pgcc (Pentium GCC) 編譯：奔騰架構下效能提升 10%+
• 靜態編譯 mysqld：./configure --with-mysqld-ldflags=-all-static，效能提升
• 優化編譯旗標範例：

  
  CFLAGS="-O6 -mpentiumpro -fomit-frame-pointer" \
  CXX=gcc CXXFLAGS="-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \
  ./configure --prefix=/usr/local --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared
  

• 官方文檔稱優化編譯可提升 10-30% 效能

五、配置檔案選擇與部署

• 官方範本：/usr/local/mysql/share/mysql/my-{small,medium,large,huge}.cnf
• 根據記憶體選擇：64M→small、256M→medium、512M→large、1G+→huge
• 部署三種方式：
  1. 全域：/etc/my.cnf
  2. 實例級：[mysql-install-dir]/var/my.cnf
  3. 用戶級：~/.my.cnf

六、常規優化參數速查表

七、資料表優化

大量刪除後資料檔不縮小，因碎片。Discuz! 等系統提供「資料表優化」功能，本質執行 OPTIMIZE TABLE。建議批量刪除後或每 1-2 個月執行一次。