發現此主機運行了幾個 Discuz 的論壇程式， Discuz論壇的好幾個表也存在著這個問題。於是順手一併解決，cpu佔用再次降下來了。
前幾天，一位朋友通過這篇文章找到了我，說他就是運行最新的 discuz 版本，MySQL 佔用 CPU 100%，導致系統假死，每天都要重啟好幾次，花了一個多月的時間一直沒有解決，希望我幫忙一下。經過檢查，他的這個論壇最重要的幾個表中，目前 cdb_members 表，有記錄 6.2 萬；cdb_threads 表，有記錄 11萬；cdb_posts表，有記錄 1740 萬；所有資料表的記錄加起來，超過 2000 萬；資料庫的大小超過 1GB。經過半天的調試，總算完成了 discuz 論壇最佳化，於是將其解決經過記錄在這篇文章中。
2007年3月我發現 discuz 論壇的資料庫結構設計有一些疏忽，有許多查詢子句的條件比較，都沒有建立 Index 索引。當時我所檢查的那個資料表，記錄只有幾千條，因此對 CPU 負荷不大。現在這個資料庫表，上千萬的記錄檢索，可以想象，如果資料表結構設計不規範，沒有提供索引，所耗費的時間是一個恐怖的數字。有關 MySQL 建立索引的重要性，可以參見我的這篇文章底部的說明
為了調試方便，我從 dizcus 的官網下載了其最新的 Dizcus! 5.5.0 論壇程式．
我首先檢查了 my.ini 的參數配置，一切正常。進入 MySQL 的命令列，調用 show processlist 語句，尋找負荷最重的 SQL 陳述式，結合 Discuz 論壇的源碼，發現有以下語句導致 CPU 上升：
複製代碼 代碼如下:
mysql> show processlist;
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
| 363 | root | localhost:1393 | history | Query | 0 | statistics | SELECT C
OUNT(*) FROM cdb_pms WHERE msgfromid=11212 AND folder='outbox' |
+-----+------+----------------+---------+---------+------+------------+---------
檢查 cdb_pms 表的結構：
複製代碼 代碼如下:
mysql> show columns from cdb_pms;
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| pmid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| msgfrom | varchar(15) | NO | | | |
| msgfromid | mediumint(8) unsigned | NO | MUL | 0 | |
| msgtoid | mediumint(8) unsigned | NO | MUL | 0 | |
| folder | enum('inbox','outbox') | NO | | inbox | |
| new | tinyint(1) | NO | | 0 | |
| subject | varchar(75) | NO | | | |
| dateline | int(10) unsigned | NO | | 0 | |
| message | text | NO | | | |
| delstatus | tinyint(1) unsigned | NO | | 0 | |
+-----------+------------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
這條語句: WHERE msgfromid=11212 AND folder='outbox'，我們看到，在 cdb_pms 表中，msgfromid 欄位已經建立了索引，但是，folder 欄位並沒有。目前這個表已經有記錄 7823 條。顯然，這會對查詢造成一定影響。於是為其建立索引：
複製代碼 代碼如下:
mysql> ALTER TABLE `cdb_pms` ADD INDEX ( `folder` );
Query OK, 7823 rows affected (1.05 sec)
Records: 7823 Duplicates: 0 Warnings: 0
繼續檢查：
複製代碼 代碼如下:
mysql> show processlist;
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+
| Id | User | Host | db | Command | Time | State | Info
|
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+
| 1583 | root | localhost:2616 | history | Query | 0 | statistics | SELECT
t.tid, t.closed, f.*, ff.* , f.fid AS fid
FROM cdb_threads t
INNER JOIN cdb_forums f |
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+
1 rows in set (0.00 sec)
這條 SQL 陳述式是針對最重要的資料表 cdb_threads 進行操作的，由於 show processlist 沒有將這條 SQL 陳述式全部顯示完全，經對比 Discuz 論壇的源碼，此SQL語句的原型位於 common.inc.php 的 Line 283，內容如下：
複製代碼 代碼如下:
$query = $db->query("SELECT t.tid, t.closed,".(defined('SQL_ADD_THREAD') ?
SQL_ADD_THREAD : '')." f.*, ff.* $accessadd1 $modadd1, f.fid AS fid
FROM {$tablepre}threads t
INNER JOIN {$tablepre}forums f ON f.fid=t.fid
LEFT JOIN {$tablepre}forumfields ff ON ff.fid=f.fid $accessadd2 $modadd2
WHERE t.tid='$tid'".($auditstatuson ? '' : " AND t.displayorder>=0")." LIMIT 1");
經檢查，資料表 cdb_threads, 並沒有針對 displayorder 欄位建立索引。在 discuz 論壇中，displayorder欄位多次參與了 Where 子句的比較。於是為其建立索引：
複製代碼 代碼如下:
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `displayorder` );
Query OK, 110330 rows affected (2.36 sec)
Records: 110330 Duplicates: 0 Warnings: 0
此時 cpu 已經輕微下降了一部分。
繼續檢查，發現 下面這條 discuz 的 SQL 陳述式，也導致負荷增加，這條語句位於 rss.php 程式中的第 142 行。
複製代碼 代碼如下:
$query = $db->query("SELECT t.tid, t.readperm, t.price, t.author, t.dateline, t.subject, p.message
FROM {$tablepre}threads t
LEFT JOIN {$tablepre}posts p ON p.tid=t.tid AND p.first=1
WHERE t.fid='$fid' AND t.displayorder>=0
ORDER BY t.dateline DESC LIMIT $num");
在這個 Order by 子句中，用到了 cdb_threads 表中的 dataline 欄位。這個欄位是用來儲存 unixtime 的時間戳記，在整個論壇程式中，大部分時候資料的排序也是基於這個欄位，竟然沒有建立索引。於是加上：
複製代碼 代碼如下:
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `dateline` );
Query OK, 110330 rows affected (12.27 sec)
Records: 110330 Duplicates: 0 Warnings: 0
尋找佔用 CPU 高負茶的 SQL 陳述式，是一件麻煩而又枯燥的事，需要一條一條排除、分析。後面的工作，都是依此類推，經過檢查，共查出有八處地方，需要增加索引，如果你也碰到了 discuz 5.5.0 論壇導致 cpu 佔用 100% 的情況，可以直接將下列語句複製過去，在 mysql 的命令列下執行即可：
複製代碼 代碼如下:
ALTER TABLE `cdb_pms` ADD INDEX ( `folder` );
ALTER TABLE `cdb_threads` ADD INDEX ( `displayorder` );
ALTER TABLE `cdb_threads` ADD INDEX ( `dateline` );
ALTER TABLE `cdb_threads` ADD INDEX ( `closed` );
ALTER TABLE `cdb_threadsmod` ADD INDEX ( `dateline` );
ALTER TABLE `cdb_sessions` ADD INDEX ( `invisible` );
ALTER TABLE `cdb_forums` ADD INDEX ( `type` );
ALTER TABLE `cdb_forums` ADD INDEX ( `displayorder` );
注意：“cdb_” 是 discuz 論壇的預設資料表首碼。如果你的表名首碼不是 “cdb_”，則應該改成你對應的表名。例如：my_threads, my_pms 等等。
完成這些結構的最佳化之後，整個系統的 CPU 負荷在 10%~20%左右震蕩，問題解決。
我很奇怪，設計資料庫結構，是一個資料庫開發人員的基本功，discuz 論壇好歹也是一個發展了有六七年的論壇了，為何資料庫結構設計得如此糟糕？我想也許有如下三個原因：
- 資料庫開發人員設計時本身的疏忽
- 故意留下的缺陷，當普通論壇沒有上數量級的記錄時，不會感覺到這個問題，當資料量增大（例如千萬級），此問題突現，以便針對使用者提供個性服務收取服務費．呵呵，估且以最大的惡意來猜測此事，玩笑而已，不必當真。:)
- 另一個可能就是使用者的論壇是從低版本升級而來，程式升了級，但資料結構也許沒有做相應的更新
附1: 補充筆記 2007-07-09
今天查看網站日誌的 reffer, 發現在 discuz 的官方論壇上，有人就此文引起了一些爭論: http://www.discuz.net/thread-673887-1-1.html。discuz 的管理員和管理員有如下言論：
引用自 cnteacher:
恰恰相反，discuz 的最佳化措施和資料庫的索引是按照大規模論壇設計的。
TO 一樓：資料庫結構的設計都是按照程式應用來進行的，使用任何非Discuz! 標準版本以外的代碼和程式，或者變更標準資料結構，均可能遇到不可預知的各種問題。
引用自 童虎:
你們可以看看xxxxx, xxxx之類的比較大型的網站，這種網站使用dz論壇都沒有問題，說明dz標準程式是沒有問題，出現樓主說的情況，多半屬於伺服器或者安裝一些外掛程式造成的
顯然將問題推給外掛程式的原因是不正確的．舉個簡單的例子：在最新的 discuz 5.5.0 forumdisplay.php 第183 行，有如下語句：
$query = $db->query("SELECT uid, groupid, username, invisible, lastactivity, action FROM {$tablepre}sessions WHERE $guestwhere fid='$fid' AND invisible=0"); 這裡的 invisible 並沒有建立索引。本文中有評論認為 session 表是記憶體表, 速度會很快。理論是如此。不過我在 show processlist 中，觀察到上面這條語句佔用了大量 CPU， 所以也將其一併加上了 index。cdb_threads 中的 closed 等欄位, 也多次參與 where 運算, 也沒有建立索引。這些運算的語句, 是 discuz 自己的程式中的。
附2: 補充筆記 2007-11-11
自從這篇筆記發表以來，在我的這篇文章的評論、以及我的聯絡訊息中，就經常收到許多下面兩種類型的評論和郵件：一、許多技術人員批評我胡說八道、Dizcus 論壇不需要做最佳化或者不能亂建索引的；二、許多使用Dizcus 的站長找我“冰天雪地裸體跪求”解決他們的 CPU 佔用 100% 的問題。
一、關於 MySQL 資料庫最佳化技術上的爭論，我的觀點再次聲明如下：
- 技術上的爭論是可以放開了討論的。而我的水平也確實只是半瓶水，對資料庫的理論知識也只懂這麼點，牛牛們的批評，我虛心接心，非常感謝。但是，評論裡的批評不要上升到人身攻擊，否則，我的地盤我作主，直接刪除。
- 資料庫的最佳化，要涉及到的方方面面很多。關說理論是沒有用的，得靠事實說話。一個千萬級資料庫的執行個體最佳化說明不了問題，兩個千萬級的資料庫最佳化也許還說明不了問題，但我相信，三個、四個、五個總是可以說明問題的，－－截止到 2007.11.09，我已經協助朋友最佳化過五個記錄數超過 1000 萬的 discuz 論壇了。我想事實勝於雄辯：最佳化之前，cpu 都是 100%；最佳化之後，cpu 降到 30%~40% 左右。沒錯，做 ADD INDEX 會增加資料庫 INSERT/UPDATE 時的開銷，但別忘了論壇最主要的操作，是 SELECT 查詢。
二、關於找我幫忙解決資料庫最佳化的評論和郵件，回覆如下:
- 資料庫的最佳化，不同的版本有不同的實際情況，最佳化一個 database，短則三兩小時，慢則半天一天。請大家理解這個中年老男人養家的壓力，我的精力有限，不可能一一幫到。
- 對於沒有收入的個人網站，我可以在周六周日的空餘時間內幫忙。請事先與我聯絡好。
- 對於有收入的網站，嗯嗯，自覺點，請帶價格與我聯絡，或者直接安排美女請我吃飯，否則免談。:) 請不要來信問“最佳化我們這個論壇你要多少費用？”這樣沒營養的話，而是直接說“幫我們最佳化 XXXX 論壇， XXXX RMB 可以不？”，我覺得合適就做。大家都很忙，我的時間很值錢，你要我自己報價，我怕嚇著你。
- 請通過 http://www.xiaohui.com/support/ 與我聯絡。不要在評論裡留個 QQ 號然後要我加你，我不會時時盯著評論看。
附3: 補充筆記 2007-11-17: 關於裝有首頁四格外掛程式的 dz 論壇導致 MySQL 佔用 大量CPU 的分析
今天手機巴士的站長( http://bbs.sj84.com )找到我，他的基於 Discuz 的論壇，也存在 CPU 佔用 100% 的問題，伺服器從 Win 2003 換到 CentOS，記憶體 2G, CPU 1.86G, 資料：cdb_threads 4 萬，cdb_posts 96 萬，cdb_members 35 萬，已經按我上面文章所說的最佳化過索引。按說這個配置足夠運行論壇了，但問題一直得不到解決。
經過調試，將慢查詢的結果 dump 到 /usr/local/mysql/var/localhost-slow.log，運行 /usr/local/mysql/bin/mysqldumpslow /usr/local/mysql/var/localhost-slow.log 查看，結合 show processlist 命令，發現慢查詢集中在下列語句:
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE t.fid<>'S' AND f.fid=t.fid AND f.fid NOT IN (N,N,N,N) AND t.closed NOT LIKE 'S' AND t.replies !=N AND t.displayorder>=N ORDER BY t.views DESC LIMIT N, N
然而搜尋 Dizcus 論壇的源碼，並沒有找到這行代碼。懷疑是外掛程式的原因。經查，論壇裝了首頁四格的外掛程式，這行語句位於 include/toplist.php 中: 仔細檢查這行代碼，發現存在許多效能或文法規範上的問題：
- AND t.closed NOT LIKE 'S':t.closed 是數值欄位，不應該用 LIKE 'S' 的形式參與比較。
- ORDER BY t.views: t.views 在 dizcus 的未經處理資料表中，是沒有做索引的。
- SELECT t.*: 這種寫法，是不被推薦的。如果要選擇某個表內的所有欄位，最好是按實全部寫出來，例如：select t.aa, t.bb, t.cc, t.dd, ...
- WHERE t.fid <> 'S': t.fid 是數值型欄位，不應該寫成 字元比較的形式。這個對效能影響不大，是個編程規範的問題。
- ....
toplist.php 的其他三條 sql 語句，都存在這些問題。如果要針對他的 sql 語句去最佳化 MySQL 結構，會帶來不良的後果；如果直接改他的 toplist.php 程式，如果站長以後升級 toplist.php 又怕帶來不相容問題。於是我建議他乾脆關閉首頁四格外掛程式。
關閉首頁四格外掛程式之後，CPU 降到 18% 左右震蕩，表現非常良好。
如果是我來寫首頁四格的程式，我不會採用這種方案，我會用定時15分鐘或30分鐘查詢一次資料庫，將結果寫入 TXT 檔案或暫存資料表，然後程式再從中讀取，效率會高許多。
結論：
- 如果裝了外掛程式的論壇碰到 CPU 高負荷時，建議關掉外掛程式再評估效能。
- 慎裝第三方外掛程式。沒事不要亂插。:)
附4：補充筆記 2008-06-10：這篇文章，重要的是分析過程，而不是進行修正的那段代碼
最近有幾位在評論中留言，以及給我 EMAIL，說到將我在文中給出的 那8行 ALTER TABLE 代碼，在他的出現 CPU 100% 的 dz 論壇上，用了之後沒有效果。
我的解釋如下：這段代碼，不能保證在 dz 的所有版本下通用。具體問題，要具體分析。這段代碼，是我在 Dizcus! 5.5.0 的版本的基本下進行分析得出的校正結果。其他的版本，不敢保證。
這篇文章的重點，並不是作為結果的這段代碼，而是如何得出這個結果的分析過程。知道了原理，你自己一樣可以分析。
