destoon关于archiver归档的性能优化

发布时间 2023-09-28 08:48:13作者: 麒麟速排

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

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

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

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<?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');
?>

  演示地址:战争指挥官小米手游v8.5.1 安卓版-9335游戏网