您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

Discuz! 論壇 MySQL 優化實戰：MyISAM→InnoDB 引擎轉換、緩存參數調優、索引重構與慢查詢消除

編輯：MySQL 綜合教學

痛點診斷：Discuz! 並發下的 MyISAM 鎖表地獄

Discuz! 早期版本默認 MyISAM，高並發場景下 **表級鎖** 導致：

寫入請求排隊，Table_locks_waited 暴增。
長查詢阻塞寫入，甚至引發「Waiting for table level lock」堆疊。
緩存命中率低（MyISAM 僅緩存索引，數據依賴 OS Cache）。

核心對策：**全表轉 InnoDB + 緩存池化 + 索引重構 + 慢查詢逐個擊破**。

第一步：引擎遷移與核心參數配置

批量轉換腳本


# 生成 ALTER 語句
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='discuz_db' AND ENGINE='MyISAM' AND TABLE_TYPE='BASE TABLE';

# 低峰期執行，大表建議 pt-online-schema-change
pt-online-schema-change --alter "ENGINE=InnoDB" D=discuz_db,t=cdb_posts --execute

InnoDB 專屬核心配置 (my.cnf)


[mysqld]
# 禁用 MyISAM (可選)
# skip-innodb  # 註釋掉！

# 緩存池：專用 DB 設為物理內存 70-80%
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 8    # 5.6+ 多實例減少鎖爭用

# 日誌持久化
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3

# 並發控制
innodb_thread_concurrency = 0       # 0=不限制，5.6+ 建議 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000           # SSD 可調 5000-10000
innodb_lock_wait_timeout = 120

# 獨立表空間（必開）
innodb_file_per_table = ON

# 內存池
innodb_additional_mem_pool_size = 16M  # 5.6.3 已廢用，保留兼容

第二步：通用緩存與連接參數調優


# 連接層
max_connections = 2000
back_log = 200
thread_cache_size = 128
wait_timeout = 60                # 配合應用連接池心跳
interactive_timeout = 60

# 緩存層
key_buffer_size = 512M           # 僅剩 MyISAM 系統表用
table_open_cache = 2048
table_definition_cache = 1024

# 查詢緩存 (Discuz 讀多寫少可開啟，寫多建議關閉)
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# 排序/臨時表
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
tmp_table_size = 256M
max_heap_table_size = 256M

# 網絡
max_allowed_packet = 32M
net_read_timeout = 30
net_write_timeout = 60

# 關閉不必要
skip_name_resolve = ON
skip_external_locking = ON

第三步：索引重構——針對核心慢查詢

典型慢查詢：搜索用戶發帖


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 t.lastpost DESC LIMIT 0, 80;

EXPLAIN 分析（轉換前）


+----+-------------+-------+------+---------------+------+---------+-------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows  | Extra                           |
+----+-------------+-------+------+---------------+------+---------+-------------+-------+---------------------------------+
|  1 | SIMPLE      | t     | range| PRIMARY,fid   | fid  | 2       | NULL        | 66160 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | tid           | tid  | 3       | discuz.t.tid| 10    | Using where                     |
+----+-------------+-------+------+---------------+------+---------+-------------+-------+---------------------------------+

問題：t.fid 範圍掃描 6.6 萬行，p.author 無索引，產生臨時表 + Filesort，總掃描 ~66 萬行。

索引優化操作


-- 1. cdb_threads: 複合索引覆蓋 WHERE + ORDER BY
ALTER TABLE `cdb_threads` 
  DROP INDEX `enablehot`, 
  DROP INDEX `fid`, 
  ADD INDEX `idx_fid_lastpost` (`fid`, `lastpost`);

-- 2. cdb_posts: author 區分度高，建立前綴索引
ALTER TABLE `cdb_posts` 
  DROP INDEX `fid`, 
  ADD INDEX `idx_author` (`author`(10));  -- 10 字符前綴，根據實際分布調整

-- 3. 執行優化
OPTIMIZE TABLE `cdb_posts`, `cdb_threads`;

優化後效果

查詢時間：**6 秒 → 0.19 秒（提升 30 倍）**。
EXPLAIN 顯示 type=ref、Extra 無 Using temporary; Using filesort。

第四步：慢查詢持續治理機制


# 開啟慢查詢日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

# 定期分析
pt-query-digest /var/log/mysql/slow.log --since "7 days ago" --limit 20 > /tmp/slow_report.txt

# 針對 Top 20 慢查詢，循環：EXPLAIN → 建索引/改 SQL → 驗證 → 上線

避坑指南：Discuz! 遷移 InnoDB 常見問題

問題	現象	解決
外鍵約束報錯	轉換時 `ERROR 1215`	Discuz 無顯式外鍵，檢查是否誤建，`DROP FOREIGN KEY` 後轉換
自增 ID 衝突	主主同步場景	設置 `auto_increment_increment/offset` 錯開
全文索引丟失	MyISAM FULLTEXT 不支持	5.6+ InnoDB 支持 FULLTEXT，重建；或引入 Elasticsearch
磁盤空間暴增	InnoDB 系統表空間不回收	開啟 `innodb_file_per_table`，`OPTIMIZE TABLE` 回收
查詢緩存失效頻繁	寫入導致 QC 失效	寫多場景關閉 QC，改用 Redis 緩存熱點數據

總結

Discuz! 性能優化核心路徑：**MyISAM→InnoDB（解決鎖爭用）→ 緩存池化（Buffer Pool 承載數據+索引）→ 索引重構（覆蓋 WHERE+ORDER BY，消除 Filesort）→ 慢查詢常態化治理**。實測核心搜索查詢提速 30 倍，並發支撐能力質變。建議將優化過程標準化為「Discuz MySQL 優化 SOP」，新部署直接應用最佳配置，存量系統按窗口期滾動升級。