Discuz! 論壇 MySQL 性能優化實戰：引擎轉換、參數調優、索引重構三板斧

核心痛點

Discuz 高並發下 MyISAM 表級鎖導致大量 Lock wait，查詢效率低。**核心解法：全表轉 InnoDB + 關鍵參數調優 + 慢查詢索引重構。**

第一板斧：啟用 InnoDB 並調優核心參數

# 關鍵：註釋掉 skip-innodb
# innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 6G              # 專用 DB 設內存 80%
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1                 # 必開

執行轉換：ALTER TABLE cdb_access ENGINE=InnoDB; (逐表執行)。轉換後數據與索引均駐留 Buffer Pool，讀寫性能質變。

第二板斧：緩存與連接層參數優化

skip-locking                  # 禁用外部鎖
skip-name-resolve             # 禁用 DNS 反解析（注意權限授權改用 IP）
key_buffer = 512M             # 僅用於系統表/臨時表，視內存調整
back_log = 200
max_allowed_packet = 2M
table_cache = 512             # 避免頻繁打開表開銷
sort_buffer_size = 4M
read_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 128            # 緩存線程，減少創建銷毀
query_cache_size = 128M       # 視命中率決定是否保留
wait_timeout = 60             # 短連接防泄漏
thread_concurrency = 4        # CPU 核心數 * 2
log-slow-queries = slow.log
long_query_time = 1
skip-bdb                      # 禁用不需要的引擎

參數需根據實際硬件（CPU/內存/磁盤）微調，僅供參考基線。

第三板斧：慢查詢索引重構實戰

典型慢 SQL

SELECT t.* FROM cdb_posts p, cdb_threads t
WHERE t.fid IN ('37','45','4','6','17','41','28','32','31','1','42')
  AND p.tid=t.tid AND p.author LIKE 'JoansWin'
GROUP BY t.tid ORDER BY lastpost DESC LIMIT 0, 80;

EXPLAIN 分析（優化前）

t 表: type=range, key=fid, rows=66160, Using where; Using temporary; Using filesort
p 表: type=ref, key=tid, rows=10, Using where
總掃描: 66160 * 10 = 66萬次索引查找

索引現狀

• cdb_threads：fid (Cardinality=10 極低)、enablehot (Cardinality=2) 幾乎無效。
• cdb_posts：無 author 索引。

重構動作

ALTER TABLE `cdb_threads` DROP INDEX `enablehot`, DROP INDEX `fid`, ADD INDEX (`fid`, `lastpost`);
ALTER TABLE `cdb_posts` DROP INDEX `fid`, ADD INDEX (`author`(10));
OPTIMIZE TABLE `cdb_posts`, `cdb_threads`;

• cdb_threads：建立 (fid, lastpost) 聯合索引，直接支持 WHERE fid IN ... ORDER BY lastpost，消除 filesort 與臨時表。
• cdb_posts：author 列加前綴索引長度 10（經分析區分度足夠），精準命中作者篩選。

優化後效果

執行時間從 6 秒降至 0.19 秒，提升 30 倍。EXPLAIN 顯示 type=ref、rows 大幅下降、Extra 無 filesort/temporary。

持續優化建議

1. 定期 pt-query-digest 分析慢查詢，針對性加索引/改 SQL。
2. 監控 Innodb_row_lock_waits、Innodb_buffer_pool_wait_free 等狀態值。
3. 大表 DDL 必用 pt-online-schema-change 避免業務阻塞。