一次Dsicuz 6.0 Mysql 优化经历
单位论坛使用了Discuz 6.0 架设的论坛,初步统计数据:
分区和版块(cdb_forums 表 )共 109 个
主题贴(cdb_threads) 18 万
帖子表(cdb_posts 表 ) 152 万
表现情况是,当用户登录后,论坛首页加载非常缓慢,竟然需要3 - 4 秒 , 而在以下情况下访问速度正常:
1 . 以游客身份浏览所有页面(包含论坛首页,贴子列表页,版块首页)很正常。
2 . 登录后浏览其它页面(即非首页)很正常。
经过层层检查,将瓶颈锁定在了论坛安装的“火狼”插件,此插件用于在Discuz 6.0 首页显示一些推荐的贴子,发贴排行等等。
虽然“火狠”插件后台提供了数据缓存功能,打开数据缓存后,首页加载速度很正常,但因为实时性要求,数据又不能缓存,故只能从SQL 优化入手。
正常的思路就是找出可能有问题的SQL , 再检查其执行计划,反复验证之。
现在的问题是: 我现在根本不知道论坛究竟作了哪些SQL 查询! 又因为论坛已经投入使用,无法擅自修改程序(影响其它用户浏览)。
为此,我使用了一个条件输出SQL 语句的调整功能:
1. 下载discuz 6.0 的 include/db_mysql.class.php 这个文件是 dz6.0 的核心数据库访问类,所有 SQL 查询都由此类处理。
2. 找到 function query($sql, $type = '') {
将它下面的
global $debug, $discuz_starttime, $sqldebug, $sqlspenttimes;
修改为
global $debug, $discuz_starttime, $sqldebug, $sqlspenttimes,$discuz_uid;
在以上行后面加一行:
if($discuz_uid == 1041538) {
echo $sql."\n\n" ;
}
1041538是我在论坛的用户 id, 换成你自己的 uid ,然后保存并上传覆盖论坛原文件。
这时,以你自己的论坛帐号登录并浏览论坛首页,应该可以显示出discuz 6 的 SQL 查询, 逐个复制这些 SQL 语句, 到 PHPAyAdmin 或 Mysql 命令行下查询,最终发现有问题的语句是:
SELECT t.tid, t.author, t.authorid, t.subject, t.dateline, t.lastpost, t.lastposter,
t.views, t.replies, t.highlight, t.highlight, f.name FROM cdb_threads t, cdb_forums f
WHERE f.fid=t.fid AND t.displayorder >-1 ORDER BY t.dateline DESC LIMIT 0,10
使用phpmyadmin 执行这个语句结果:
果不其然,查询用时2.0527 秒, 点击 PHPmyadmin 右下角的 Explain SQL ,检查执行计划,输出结果如下:
上述图中的 t 表,也就是 cdb_threads 表,其扫描的行数为 174984 , 首页将疑点锁定在这 t 表的查询上,为验证 t 表的查询,在不连接 f 表 ( 即 cdb_forums) 的情况,单独执行其查询:
select t.tid, t.author, t.authorid, t.subject, t.dateline, t.lastpost, t.lastposter,
t.views, t.replies, t.highlight, t.highlight from cdb_threads t
where t.displayorder not in(-1,-2) ORDER BY t.dateline DESC LIMIT 0,10
phpmyadmin执行结果如下:
用时0.0005 秒,速度非常正常,那么可疑的查询只可能是在 f 表 where 过滤上:
SELECT t.tid, t.author, t.authorid, t.subject, t.dateline, t.lastpost, t.lastposter,
t.views, t.replies, t.highlight, t.highlight, f.name FROM cdb_threads t,
cdb_forums f WHERE f.fid=t.fid AND f.fid not in (0)
AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0,10
为此,我逐个去掉f 表的 where 条件,再测试其效率。经过反复测试,发现去去掉 f.fid not in (0) 条件时,查询速度很正常,根据其意义,是查询 cdb_forums 表中 fid 字段不等于 0 的记录。
而cdb_forums 表中 fid 字段的定义是:
SMALLINT, 正整数,自增主键。
可见,fid 定义成了正整数,那么这里的 fid not in(0) 限定将没有任何意义,因为 NOT IN 之类的查询,是无法使用索引的,那么读者可能认为 fid>0 是否可使用索引? 这得看情况,因为 fid 已经被定义成了正整数,那么 fid>0 仍然不会使到索引,以下的查询可以验证之:
![](http://dl.iteye.com/upload/attachment/235804/a7e4ef31-4829-368a-bd27-343fb9ced427.jpg)
既然 fid 已经是正整数,那么, fid Not in(0) 完全没有意义,我们将这个条件去掉:
现在的查询,速度是0.0005 秒, 终于解决之。
那么我的解决方法就是修改plugins/firewolf_indextoplist/threadfocus.php , 找到其中的 fid Not in(0) 限定 , 将其全部删除之 .
如果站在程序员的角度,以上的方案完全可以进行尝试优化,避免与cdb_forums 表联合查询,仅仅为了从 fid 得到 cdb_forums.name 而进行联合查询,的确是得不偿失,完全可以根据需要将 cdb_forums 表中的数据缓存成数组,如:
$fnames = array(
1 => '谈天说地 ',
2 => '休闲一刻 ',
...
);
$fnames[2]即可取得指定 fid 的 name, 何乐而不为呢?
经过以上处理, 加载速度已经提高了不少 , 但发现还是慢 , 重复以上方法 , 找到以下 SQL 的查询时间过长 :
SELECT t.tid, t.author, t.authorid, t.subject, t.dateline, t.lastpost,
t.lastposter, t.views, t.replies, t.highlight, t.highlight, f.name
FROM cdb_threads t, cdb_forums f
WHERE f.fid=t.fid AND t.closed NOT LIKE 'moved|%' AND t.replies >=1
AND t.displayorder >-1 ORDER BY t.lastpost DESC LIMIT 0,10
经过检查, 是因为 cdb_threads 的 lastpost 字段没有加索引 , 为其增加索引 , 一切 OK.
通过这个案例,你应该至少明白两个索引原理:
1. NOT IN, <> , NOT LIKE 之类的查询,是无法使用索引的
2. 某个字段如果定义成正整数, 那么 >0 之类的查询也是无法使用索引的(既然字段是非 0 值, 那么如此的限定只能是废话)。
3. 有时 , 方法比知识更重要