关于MySQL性能的比较
需求：在传递一组职位编号的时候，需要统计该职位的 当天的投递情况 和 有历史记录以来总的投递量
解决方案一：
每次都进行一次数据库查询，遍历职位id，再根据职位id去查询相应时间内的投递量
/** * [exportData 导出相应职位的投递量] * @param [array] $params [需要查询的职位id,时间等] * @return [array] [相应职位id的投递量] */ public function exportData($params){ //进行数据库查询 [查询所要导出的数据] $getWhere = '1 =1'; //开始时间和结束时间 if( (isset($params['start_date']) && $params['start_date']) && (isset($params['end_date']) && $params['end_date']) ){ $getWhere.=" and `addtime` between '{$params['start_date']}' and '{$params['end_date']}' "; } $jobidsArr = explode("," , $params['jobids']); $count = []; $dealCount = []; $actionTotal = []; $actionDealTotal = []; foreach ($jobidsArr as $key => $value) { $sql = "SELECT count(*) as count from hr_action where jobid = ".$value. " and ".$getWhere; $count[$key] = $this->db->findOne($sql);//返回数组 //获取当天的处理状态 $sqlDeal = "SELECT count(*) as count from hr_action where jobid = ".$value. " and `astatus` > 0 and ".$getWhere; $dealCount[$key] = $this->db->findOne($sqlDeal);//返回数组 //总职位数和总处理情况进行查询 $sqlTotal = "SELECT count(*) as count from hr_action where jobid = $value"; $actionTotal[$key] = $this->db->findOne($sqlTotal); $sqlDealTotal = "SELECT count(*) as count from hr_action where jobid = $value and `astatus` > 0"; $actionDealTotal[$key] = $this->db->findOne($sqlDealTotal); } foreach ($count as $ckey => $cvalue) { $actionCount[$ckey] = $cvalue['count']; } foreach ($dealCount as $dkey => $dvalue) { $actionDealCount[$dkey] = $dvalue['count']; } foreach ($actionTotal as $atkey => $atvalue) { $actionTotalCount[$atkey] = $atvalue['count']; } foreach ($actionDealTotal as $adtkey => $adtvalue) { $actionDealTotalCount[$adtkey] = $adtvalue['count']; } return ['jobid'=> $jobidsArr , 'actionCount'=> $actionCount , 'actionDealCount' => $actionDealCount , 'actionTotalCount'=>$actionTotalCount,'actionDealTotalCount'=>$actionDealTotalCount]; }
以上这种方法思路确实很简单，但是仔细查看，在运行的时候可以算一下进行了多少次的数据库查询，每一次的foreach循环都会进行四次查询数据库，当我一次传递30个职位的时候，就是4*30 次数据库查询了，由于数据库中的数量比较大，每次都全部便利一次这样的查询不满才怪呢。咳咳咳咳。。
方法二：尽量的减少数据库的查询，对数据库的操作越少越好。为什么不在第一次遍历投递情况的时候，把所有的投递情况都遍历出来保存到一个数组中，然后对数组进行操作，把对数据库的操作转换成php在内存中操作，这样不是挺好的吗？
因此，首次遍历的时候利用in 查询，把所有的投递情况都查询出来。
/** * [exportData 导出相应职位的投递量] * @param [array] $params [需要查询的职位id,时间等] * @return [array] [相应职位id的投递量] */ public function exportData($params){ //进行数据库查询 [查询所要导出的数据] $getWhere = '1 =1'; //开始时间和结束时间 if( (isset($params['start_date']) && $params['start_date']) && (isset($params['end_date']) && $params['end_date']) ){ $getWhere.=" and `addtime` between '{$params['start_date']}' and '{$params['end_date']}' "; } $jobids = $params['jobids'];//获取传入的职位id $jobidsArr = explode("," , $params['jobids']); //根据职位id来查询相应的职位信息和公司信息 $sqlInfo = "SELECT j.`title`,j.`id` from hr_job as j where id in ($jobids)"; $jobTitle = $this->db->find($sqlInfo); $sqlCompany = "SELECT c.`comname`,j.`id` from hr_company as c right join hr_job as j on j.`user_id` = c.`user_id` where j.`id` in ($jobids)"; $jobCompany = $this->db->find($sqlCompany); //print_r($jobTitle); $newjobTemp = []; foreach ($jobTitle as $key => $value) { $newjobTemp[$value['id']] = $value['title']; } $companyTemp = []; foreach ($jobCompany as $key => $value) { $companyTemp[$value['id']] = $value['comname']; } //print_r($newjobTemp); // // /*************总职位数和总处理情况进行查询*************/ $sqlTotal = "SELECT `jobid`,`addtime` ,`astatus` from hr_action where jobid in ($jobids)"; $sqlDealTotal = "SELECT `jobid`,`addtime` ,`astatus` from hr_action where jobid in ($jobids) and ".$getWhere; $retTotal = $this->db->find($sqlTotal);//返回总数 $retDealTotal = $this->db->find($sqlDealTotal);//返回当天处理总数 /*************总职位数和总处理情况进行查询*************/ $temp1 = []; foreach ($retTotal as $ckey => $cvalue) { $temp1[$ckey] = $cvalue['jobid']; } $actionCount1 = array_count_values($temp1); //print_r($actionCount1);//投递总数 $temp2 = []; foreach ($retTotal as $dkey => $dvalue) { $temp2[$dkey] = [$dvalue['astatus'],$dvalue['jobid']]; } //print_r($temp2); $temp3 = []; foreach ($retDealTotal as $atkey => $atvalue) { $temp3[$atkey] = $atvalue['jobid']; } $actionCount3 = array_count_values($temp3); //print_r($actionCount3); $temp4 = []; foreach ($retDealTotal as $adtkey => $adtvalue) { $temp4[$adtkey] = [$adtvalue['astatus'],$adtvalue['jobid']]; } //print_r($temp4); //按照预定义格式输出 $actionCount = [];//总投递量 $actionDayCount = [];//当天投递总量 $adealCount = [];//定义一个新数组，存放相对应的总处理量 $adaydealCount = [];//定义一个新数组，存放相对应的当天总处理量 $actionDealCount = [];//存放转换后的总处理量 $actionDayDealCount = [];//存放转换后的当天处理量 $actionJobTitle = [];//职位名称 $actionJobCompany = [];//公司名称 foreach ($jobidsArr as $key => $value) {//循环传入的IDS $i = 0; foreach ($temp2 as $t2key => $t2value) { if ($value == $t2value[1]) { if ($t2value[0] > 0) { $i++; } } $adealCount[$value] = $i; } $j = 0; foreach ($temp4 as $t4key => $t4value) { if ($value == $t4value[1]) { if ($t4value[0] > 0) { $j++; } } $adaydealCount[$value] = $j; } $actionCount[$key] = $actionCount1[$value]; $actionDayCount[$key] = isset($actionCount3[$value])?$actionCount3[$value]:0; $actionDealCount[$key] = $adealCount[$value]; $actionDayDealCount[$key] = $adaydealCount[$value]; $actionJobTitle[$key] = $newjobTemp[$value]; $actionJobCompany[$key] = $companyTemp[$value]; } // print_r($actionJobTitle); /* print_r($actionCount);//总投递量 print_r($actionDayCount);//当天投递总量 print_r($actionDealCount);//总处理量 print_r($actionDayDealCount);//当天处理量*/ return ['jobid'=> $jobidsArr , 'actionCount'=> $actionCount , 'actionDealCount' => $actionDealCount , 'actionDayCount'=>$actionDayCount,'actionDayDealCount'=>$actionDayDealCount,'actionJobTitle'=>$actionJobTitle , 'actionJobCompany'=>$actionJobCompany]; }
导出数据，都是使用的同一套方法
//导出相应职位的投递情况 public function exportData($params = array()){ /*[start_date] => 2016-11-23 12:09:05 [end_date] => 2016-11-24 12:09:07 [jobids] => 12,23,234,345,345,4564,5646*/ /*echo "<pre>"; print_r($params);*/ $jobModel = new joblist_jobModel(); $job_action_count = $jobModel->exportData($params); ini_set('memory_limit','500M'); set_time_limit(300); $jobids = $job_action_count['jobid'];//查询的职位id $actionDayCount = $job_action_count['actionDayCount'];//当天的投递量 $actionDayDealCount = $job_action_count['actionDayDealCount'];//当天处理量 $actionCount = $job_action_count['actionCount'];//总投递量 $actionDealCount = $job_action_count['actionDealCount'];//总处理量 $actionJobTitle = $job_action_count['actionJobTitle'];//职位标题 $actionJobCompany = $job_action_count['actionJobCompany'];//企业名称 if(empty($jobids)){ exit("暂无数据"); } $temp = []; foreach ($jobids as $key => $value) { $temp[$key] = [$value,$actionJobCompany[$key],$actionJobTitle[$key],$actionDayCount[$key], $actionDayDealCount[$key], $actionCount[$key], $actionDealCount[$key]]; } /*print_r($temp); exit;*/ $column_name = array(); header ( "Content-type:application/vnd.ms-excel" ); header ( "Content-Disposition:filename=" . iconv ( "UTF-8", "GBK", date("Y-m-d_H_i_s" , time())."-".rand(1,10000) ) . ".csv" ); // 打开PHP文件句柄，php://output 表示直接输出到浏览器 $fp = fopen('php://output', 'a'); $head = ['职位编号','企业名称','职位名称','当天的投递量','当天的处理量','总投递量','总处理量']; foreach ($head as $i => $v) { $column_name[$i] = iconv('utf-8', 'GBK', $v); } fputcsv($fp, $column_name); foreach ($temp as $tkey => $tvalue ) { $rows = array(); foreach ($tvalue as $newkey => $newvalue) { $rows[] = iconv('utf-8', 'GBK', $newvalue); } fputcsv($fp, $rows); } ob_flush(); flush(); die(); }
