本文场景:统计图界面优化:
1:统计界面:适当加入缓存;
2:设备统计界面:
条形图: 这是原代码
$fill_res = $eqpt->field('count(EqptId) as count,date_format(CreateDate,"%y") as CreateDate')->where($where)->group('CreateDate')->buildSql();
$fill_res=Db::query("select sum(a.count) as count,a.CreateDate as CreateDate from $fill_res as a GROUP by CreateDate");
思考1:我们想一下,新增的数据去年是否已经是固定的?
然后这些我们可以把他们成为“冷数据"
$redis = new Predis();
$cache_key = md5(json_encode($where));
// redis 实现统计图冷热数据分离
if(!$redis->getHash($cache_key)){
$fill_res = $eqpt->field('count(EqptId) as count,date_format(CreateDate,"%y") as CreateDate')->where($where)->group('CreateDate')->buildSql();
$fill_res=Db::query("select sum(a.count) as count,a.CreateDate as CreateDate from $fill_res as a GROUP by CreateDate");
$list_data = [];
$list_count = count($fill_res)- 1;
foreach ($fill_res as $key=>$val){
if ($key < $list_count){
$list_data[$val['CreateDate']] = $val['count'];
}
}
$redis->setHash($cache_key,$list_data);
}else{
$search_where = $where;
$search_where['CreateDate'] = ['EGT',date("Y-01-01",strtotime("this year"))];
$current_year_count = $eqpt->where($search_where)->count();
$fill_list = $redis->getHash($cache_key);
$fill_res = [];
foreach ($fill_list as $k=>$value){
$fill_res[] = ['CreateDate'=>$k,'count'=>$value];
}
$fill_res[] = ['CreateDate'=>date("Y"),"count"=>$current_year_count];
}
代码很烂;毕竟菜鸟大佬勿喷;
2:sql优化毕竟explain必不可少
此处附上一篇比较齐全的文章:
MySQL Explain详解 - 杰克思勒(Jacksile) - 博客园www.cnblogs.com3:注意点:where条件是从右往左依次筛选的;尽量在末尾就过滤大量数据
这条sql一看就很长;而且wherein太长了 wherein 就是笛卡尔积
咋:来搞搞呗
不看不知道一看吓一跳有核扫描行数太多了~,减少
这个表的扫描行数