mysql 查询 效率内部测试

初始 语句  

 

// 按照订单角度统计顾问
$sql = " select 
    a.id,
    a.order_num,
    a.child_order_num,
    a.create_time,
    a.order_state,
    a.contract_num,
    a.pay_price,
    a.sk_sku_platform,
    a.sk_sku_name,
    a.discount_price,
    a.pd_product_number,
    concat(a.pd_product_name,'-',a.sk_sku_show,'-',a.sk_sku_spec_group_name) as sk_sku_show,
    a.adviser_code,
    a.after_discount_price,
    a.crm_customer_code,
    b.pay_time,
    b.pay_name,
    b.source,
    c.tel,
    d.adv_name,
    d.status as adv_status,
    d.department_name,
    e.name as adv_group_name,
    g.status as refund_status 
    from order_goods a 
    left join order_info b on a.order_num = b.order_number 
    left join user c on a.uid = c.id 
    left join adviser d on a.adviser_code = d.job_number 
    left join adviser_class e on d.group = e.code 
    left join order_contacts f on b.order_contacts_id = f.id 
    left join (select `status`, `child_order_number` from order_refund order by rf_id desc limit 0, 100) g on a.child_order_num = g.child_order_number 
    where b.is_delete='2' $_sql group by a.child_order_num order by a.id desc limit $start,$page_show";

 

不难看出 7张表联查， 还有子查询 慢是肯定的， 接口响应时间大于2秒

稍稍改进一下 

$sql = " select 
    a.id,
    a.order_num,
    a.child_order_num,
    a.create_time,
    a.order_state,
    a.contract_num,
    a.pay_price,
    a.sk_sku_platform,
    a.sk_sku_name,
    a.discount_price,
    a.pd_product_number,
    concat(a.pd_product_name,'-',a.sk_sku_show,'-',a.sk_sku_spec_group_name) as sk_sku_show,
    a.adviser_code,
    a.after_discount_price,
    a.crm_customer_code,
    b.pay_time,
    b.pay_name,
    b.source,
    c.tel,
    d.adv_name,
    d.status as adv_status,
    d.department_name,
    e.name as adv_group_name
    from order_goods a 
    left join order_info b on a.order_num = b.order_number 
    left join user c on a.uid = c.id 
    left join adviser d on a.adviser_code = d.job_number 
    left join adviser_class e on d.group = e.code 
    where b.is_delete='2' $_sql group by a.child_order_num order by a.id desc limit $start,$page_show";

首先发现一个关联表 字段没有使用， 删掉， 用了子查询的表没有查询条件， 那就用简单的方法优化

foreach($list['list'] as $k=>$v){
    //获得子订单号
    $arr[$k] = $v['child_order_num'];
}
//退款信息
$refundQuery = Db::table('order_refund')
    ->field('status, child_order_number, apply_money, rf_number')
    ->order('rf_id','desc')
    ->buildSql();

$data = Db::table($refundQuery.' a')
    ->where('child_order_number','in',$arr)
    ->group('child_order_number')
    ->select();
$_data = array_column($data, null, 'child_order_number');

$list['list'][$k]['refund_status'] = !empty($_data[$data[$k]])&&!empty($_data[$data[$k]]['status'])?$_data[$data[$k]]['status']:'';

 

接口响应时间 400ms

 

如果 继续优化，  顾问就有查询条件了，  稍稍复杂一些，  但是 肯定是更快。

 

记录一下