引用地址：http://tuibian.com/?p=707
discuz中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：采用完全的联表查询：
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中的数据，再用联表查询用户的信息。
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中的信息。
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中的数据，再用联表查询用户的信息的方式，可以更提高程序运行效率，减少对服务器的负载，虽然增加了一次查询，但是由于查询的语句简单化，因此反而可以节省一些运行时间。
*******************************
ps：以上方法我都做了测试，结果却跟上面的不一样，我测得的还是论坛原本的代码用时最少，倒是把leftjoin变为逗号直接连表查询会稍微快一些。
