千万级记录的Discuz论坛导致MySQL CPU 100%的优化笔记
程序员文章站
2023-12-17 15:02:28
发现此主机运行了几个 discuz 的论坛程序， discuz论坛的好几个表也存在着这个问题。于是顺手一并解决，cpu占用再次降下来了。 前几天，一位朋友通过这篇文章找...
发现此主机运行了几个 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 等等。
前几天，一位朋友通过这篇文章找到了我，说他就是运行最新的 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 的官方论坛上，有人就此文引起了一些争论: 。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 可以不？”，我觉得合适就做。大家都很忙，我的时间很值钱，你要我自己报价，我怕吓着你。
- 请通过 与我联系。不要在评论里留个 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 的版本的基本下进行分析得出的校正结果。其他的版本，不敢保证。
这篇文章的重点，并不是作为结果的这段代码，而是如何得出这个结果的分析过程。知道了原理，你自己一样可以分析。