destoon关于archiver归档的性能优化
今天在处理一个项目时候发现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' ); ?> |