Modoer列表页性能分析及优化-php手册-PHP中文網

首頁

php教程

php手册

Modoer列表页性能分析及优化

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 13, 2016 am 10:17 AM

httpli最佳化分析清單在效能

在 http://www.modoer.org/beijing/item/list-8 的页面中，会执行以下2个sql

SELECT s.sid,pid,catid,domain,name,avgsort,sort1,sort2,sort3,sort4,sort5,sort6,sort7,sort8,best,finer,pageviews,reviews,pictures,favorites,thumb,aid,map_lat,map_lng,c_tel,c_dz FROM modoer_subject s LEFT JOIN modoer_subject_shishang sf ON (s.sid = sf.sid) WHERE exists(SELECT 1 FROM modoer_subjectatt st WHERE s.sid=st.sid AND attid=173) AND exists(SELECT 1 FROM modoer_subjectatt st WHERE s.sid=st.sid AND attid=4) ORDER BY finer DESC LIMIT 0, 20

SELECT COUNT(*) FROM modoer_subject s WHERE exists(SELECT 1 FROM modoer_subjectatt st WHERE s.sid=st.sid AND attid=173) AND exists(SELECT 1 FROM modoer_subjectatt st WHERE s.sid=st.sid AND attid=4)

如果数据量不大的话，还凑活，如果数据量大的话呢？以本人的数据为例，分别是subjectatt数据量和subject数据量

sql运行结果如图：

仅仅一个查询数据量的sql花了44.16秒，网站根本打不开，难道技术没有测试没有优化吗？复合语句用起来很爽但是效果很差

经本人暂时优化，subject_class.php中改掉以下代码，性能大大提升
if($atts) {
         $attlist = array_values($atts);
         $num = count($attlist);
if($num>0){
$or = '';
$sql ='select GROUP_CONCAT(sid) sids from(
select count(sid) count,sid from (
select sid,attid from modoer_subjectatt where ';
foreach($attlist as $attid) {
   //$this->db->where_exist("SELECT 1 FROM dbpre_subjectatt st WHERE s.sid=st.sid AND attid=$attid");
if($attid
   $sql .= $or . ' attid='.$attid;
   $or = ' or ';
}
$sql .=  ') as temp
group by sid) as temp1
where count='.$num;
}
$query=$this->db->query($sql);$b=$query->fetch_array();$sids=$b['sids'];
//$where = ' s.sid in('.$sids.') ';

                        $this->db->where('s.sid', explode(',', $sids), '');
      }else{
$where = '';
}

还有查询数量的地方
if($atts) {
         $attlist = array_values($atts);
         $num = count($attlist);
if($num>0){
$or = '';
$sql ='select count(*) as nums from(
select count(sid) count from (
select sid,attid from modoer_subjectatt where ';
foreach($attlist as $attid) {
   //$this->db->where_exist("SELECT 1 FROM dbpre_subjectatt st WHERE s.sid=st.sid AND attid=$attid");
if($attid
   $sql .= $or . ' attid='.$attid;
   $or = ' or ';
}
$sql .=  ') as temp
group by sid) as temp1
where count='.$num;
}
      }

希望有用到的朋友可以改下，也只是暂时解决办法。另有问题请联系qq  272164179