destoon关于archiver归档的性能优化

  今天在处理一个项目时候发现archiver单个模块归档超过百万数据，打开速度就特慢，所以打开archiver下index.php文件进行分析，发现有句sql作怪

$result = $db->query("SELECT title,linkurl,addtime FROM {$table} WHERE $condition ORDER BY addtime DESC LIMIT $offset,$pagesize");

　　因为这sql就是查询，没有用到索引。我们思路要先查询itemid然后再用itemid进行查询，这样速度就快了。

代码如下：

<?php
define('DT_REWRITE', true);
require '../common.inc.php';
$EXT['archiver_enable'] or dheader(DT_PATH);
//$DT_BOT or dheader(DT_PATH);
$N = $M = $T = array();
$mid or $mid = 5;
$vmid = $list = 0;
foreach($MODULE as $k=>$v) {
	if(!$v['islink'] && $v['ismenu'] && $v['moduleid'] > 4) {
		if($k == $mid) $vmid = 1;
		$v['url'] = $DT['rewrite'] ? rewrite('index.php?mid='.$k) : '?mid-'.$k.'.html';
		$N[] = $v;
	}
}
$vmid or $mid = $N[0]['moduleid'];
$table = get_table($mid);
$t = $db->get_one("SELECT MIN(addtime) AS mintime,MAX(addtime) AS maxtime FROM {$table}", 'CACHE');
$fromtime = $t['mintime'];
$fromyear = timetodate($fromtime, 'Y');
if($fromyear < 1990) $fromyear = 1990;
$frommonth = timetodate($fromtime, 'n');
$totime = $t['maxtime'] > $DT_TIME ? $DT_TIME : $t['maxtime'];
$toyear = timetodate($totime, 'Y');
$tomonth = timetodate($totime, 'n');
for($i = $toyear; $i >= $fromyear; $i--) {
	for($j = ($i == $toyear ? $tomonth : 12); $j >= ($i == $fromyear ? $frommonth : 1); $j--) {
		$r = array();
		$r['title'] = $MODULE[$mid]['name'].$i.'年'.($j < 10 ? '0' : '').$j.'月归档';
		$r['month'] = $i.($j < 10 ? '0' : '').$j;
		$r['url'] = $DT['rewrite'] ? rewrite('index.php?mid='.$mid.'&month='.$r['month']) : '?mid-'.$mid.'-month-'.$r['month'].'.html';
		$M[$r['month']] = $r;
	}
}
$head_title = $MODULE[$mid]['name'].'归档';
if(isset($month) && isset($M[$month])) {
	$list = 1;
	$y = substr($month, 0, 4);
	$m = substr($month, 4, 2);
	$ym = $y.'-'.$m;
	$t = timetodate(datetotime($ym.'-01'), 't');
	$ftime = datetotime($ym.'-01 00:00:00');
	$ttime = datetotime($ym.'-'.$t.' 23:59:59');
	$condition = "addtime>$ftime AND addtime<$ttime";	
	$num = $db->count($table, $condition, $CFG['db_expires']);
	$demo_url = $DT['rewrite'] ? rewrite($MODULE[1]['linkurl'].'archiver/index.php?mid='.$mid.'&month='.$month.'&page={destoon_page}') : '?mid-'.$mid.'-month-'.$month.'-page-{destoon_page}.html';
	$pages = pages($num, $page, $pagesize, $demo_url);
	$tmp = explode('<input type="text"', $pages);
	$pages = $tmp[0];
	if($num) {
		//优化查询速度
		//$result = $db->query("SELECT title,linkurl,addtime FROM {$table} WHERE $condition ORDER BY addtime DESC LIMIT $offset,$pagesize");
		 $reitemid = $db->query("SELECT itemid FROM {$table} WHERE $condition ORDER BY addtime DESC LIMIT $offset,$pagesize");
		 //echo "SELECT itemid FROM {$table} WHERE $condition ORDER BY addtime DESC LIMIT $offset,$pagesize";
         while($rid = $db->fetch_array($reitemid)) {
          $getid.=$rid['itemid'].",";
		 }
          $getid=mb_substr($getid,0,-1);

		//判断是否在里面
		$result=$db->query("SELECT title,linkurl,addtime FROM {$table} WHERE itemid in ($getid)");
		while($r = $db->fetch_array($result)) {
			$r['adddate'] = timetodate($r['addtime'], 5);
			if(strpos($r['linkurl'], '://') === false) $r['linkurl'] = $MODULE[$mid]['linkurl'].$r['linkurl'];
			$T[] = $r;
		}
	}
	$head_title = $MODULE[$mid]['name'].$y.'年'.$m.'月归档'.($page > 1 ? '第'.$page.'页' : '');
}
include template('archiver', 'extend');
?>

　　演示地址：工作范文2023年09月归档_醉学网 (nongpin88.com)