一次Dsicuz 6.0 Mysql优化经历

一次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  仍然不会使到索引，以下的查询可以验证之：

 

 

 

既然 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. 有时 , 方法比知识更重要