discuz算是比较优秀的论坛了，很多大网站论坛都在使用。从最近做的项目中想到discuz中有些sql查询的方式对于数据库记录超过100万条的论坛来说，或许可以有优化的地方，所以回来做了一下下面的尝试：
数据库内cdb_posts表有记录3918401，将近400万，会员720000人左右，算是中等论坛。
viewthread.php是用来读取一个话题内相关帖子的程序，相关的sql语句大概为：
SELECT p.*, m.uid, m.username, m.groupid, m.regdate, m.lastactivity, m.posts, m.digestposts, m.oltime,
m.pageviews, m.credits, m.extcredits1, m.extcredits2, m.extcredits3, m.extcredits4, m.extcredits5, m.extcredits6,
m.extcredits7, m.extcredits8, m.email, m.gender, m.showemail, m.invisible, m.avatarshowid, m.xspacestatus, mf.nickname, mf.site,
mf.icq, mf.qq, mf.yahoo, mf.msn, mf.taobao, mf.alipay, mf.location, mf.medals, mf.avatar, mf.avatarwidth,
mf.avatarheight, mf.sightml AS signature, mf.customstatus
FROM {$tablepre}posts p
LEFT JOIN {$tablepre}members m ON m.uid=p.authorid
LEFT JOIN {$tablepre}memberfields mf ON mf.uid=m.uid
WHERE p.tid=’TID’ AND p.invisible=’0′ ORDER BY dateline LIMIT 60
这个语句中采用了两个left join查询，我想在大数据量的情况下，如果先查询posts表，然后再查询members和memberfields表是不是会速度更快呢，所以做了如下的实验：
程序1：采用完全的联表查询：
<?php
require_once ‘./include/common.inc.php’;
for ($i = 395000; $i<=398770; $i++)
{
$query = $db->query("SELECT p.*, m.uid, m.username, m.groupid, m.regdate, m.lastactivity, m.posts, m.digestposts, m.oltime,
m.pageviews, m.credits, m.extcredits1, m.extcredits2, m.extcredits3, m.extcredits4, m.extcredits5, m.extcredits6,
m.extcredits7, m.extcredits8, m.email, m.gender, m.showemail, m.invisible, m.avatarshowid, m.xspacestatus, mf.nickname, mf.site,
mf.icq, mf.qq, mf.yahoo, mf.msn, mf.taobao, mf.alipay, mf.location, mf.medals, mf.avatar, mf.avatarwidth,
mf.avatarheight, mf.sightml AS signature, mf.customstatus
FROM {$tablepre}posts p
LEFT JOIN {$tablepre}members m ON m.uid=p.authorid
LEFT JOIN {$tablepre}memberfields mf ON mf.uid=m.uid
WHERE p.tid=’$i’ AND p.invisible=’0′ ORDER BY dateline LIMIT 60");
while($post = $db->fetch_array($query)) {
//do somthing;
}
}
$mtime = explode(‘ ‘, microtime());
$runtime = ($mtime[1] + $mtime[0] – $discuz_starttime);
?>
程序读tid从395000到398770的所有话题，运行8次，运行时间分别为：
19.4085040092 16.5643789768 13.6189751625 14.2483608723 14.4175901413 14.3845379353 17.1536540985 17.2402610779
可以看到运行两三次后mysql的query cache开始生效，速度逐渐稳定在14秒左右
程序2：先读取相关cdb_posts中的数据，再用联表查询用户的信息。
<?php
require_once ‘./include/common.inc.php’;
for ($i = 395000; $i<=398770; $i++)
{
$uid = 0;
$query = $db->query("SELECT * FROM {$tablepre}posts WHERE tid=’$i’ AND invisible=’0′ ORDER BY dateline LIMIT 60");
while ($post = $db->fetch_array($query))
{
if ($post['authorid'])
{
$uid .= ",".$post['authorid'];
}
}
$query = $db->query("SELECT m.uid, m.username, m.groupid, m.regdate, m.lastactivity, m.posts, m.digestposts, m.oltime,
m.pageviews, m.credits, m.extcredits1, m.extcredits2, m.extcredits3, m.extcredits4, m.extcredits5, m.extcredits6,
m.extcredits7, m.extcredits8, m.email, m.gender, m.showemail, m.invisible, m.avatarshowid, m.xspacestatus, mf.nickname, mf.site,
mf.icq, mf.qq, mf.yahoo, mf.msn, mf.taobao, mf.alipay, mf.location, mf.medals, mf.avatar, mf.avatarwidth,
mf.avatarheight, mf.sightml AS signature, mf.customstatus FROM {$tablepre}members m
LEFT JOIN {$tablepre}memberfields mf ON mf.uid=m.uid
WHERE m.uid IN ($uid)
");
}
$mtime = explode(‘ ‘, microtime());
$runtime = ($mtime[1] + $mtime[0] – $discuz_starttime);
?>
运行时间依次为：
14.0723490715 14.4422261715 13.4820911884 13.289301157 13.0309917927 13.1161420345 12.8048489094 12.6428101063
运行时间大大缩短，基本在13秒左右
程序三：先读取相关cdb_posts中的数据，再分别查询cdb_members和cdb_memberfields中的信息。
<?php
require_once ‘./include/common.inc.php’;
for ($i = 395000; $i<=398770; $i++)
{
$uid = 0;
$query = $db->query("SELECT * FROM {$tablepre}posts WHERE tid=’$i’ AND invisible=’0′ ORDER BY dateline LIMIT 60");
while ($post = $db->fetch_array($query))
{
if ($post['authorid'])
{
$uid .= ",".$post['authorid'];
}
}
$query = $db->query("SELECT uid, username, groupid, regdate, lastactivity, posts, digestposts, oltime,
pageviews, credits, extcredits1, extcredits2, extcredits3, extcredits4, extcredits5, extcredits6,
extcredits7, extcredits8, email, gender, showemail, invisible, avatarshowid, xspacestatus FROM {$tablepre}members
WHERE uid IN ($uid)
");
$query = $db->query("SELECT nickname, site,icq, qq, yahoo, msn, taobao, alipay, location, medals, avatar, avatarwidth,
avatarheight, sightml AS signature, customstatus FROM {$tablepre}memberfields
WHERE uid IN ($uid)
");
}
$mtime = explode(‘ ‘, microtime());
$runtime = ($mtime[1] + $mtime[0] – $discuz_starttime);
?>
运行时间依次为：
14.2967050076 13.7778971195 14.3256859779 15.5803039074 13.5902359486 13.9162919521 14.1429729462 14.4423420429
基本在14秒左右
结论，对于数据量较大的网站，采用先读取相关cdb_posts中的数据，再用联表查询用户的信息的方式，可以更提高程序运行效率，减少对服务器的负载，虽然增加了一次查询，但是由于查询的语句简单化，因此反而可以节省一些运行时间。 |
发表于 2011-5-4 10:52
|