MySQL订单慢查询语句优化求解

SELECT b.goodsid
         , b.skuid
         , b.instate
         , sum(b.num) num
from gt_wh_order a1 left join  gt_wh_orderdetail b on  a1.id = b.orderid
where a1.ordertype = '0'
      and a1.deptid = '001002001001'
      AND b.outstate = '0'
    GROUP BY b.goodsid, b.skuid

该语句执行了大概5s，太就了，希望帮忙看一下

首先确定一下外键对应的字段有没有创建索引。

查看一下执行计划

explain SELECT b.goodsid
         , b.skuid
         , b.instate
         , sum(b.num) num
from gt_wh_order a1 left join  gt_wh_orderdetail b on  a1.id = b.orderid
where a1.ordertype = '0'
      and a1.deptid = '001002001001'
      AND b.outstate = '0'
    GROUP BY b.goodsid, b.skuid

SELECT 
b.goodsid
, b.skuid
, min(b.instate) as instate
, sum(b.num) num
from gt_wh_order a1 
left join  gt_wh_orderdetail b 
on 1 = 1 
and a1.id = b.orderid 
and a1.deptid = '001002001001' 
and a1.ordertype = '0' 
and b.outstate = '0'
where 1 = 1 
and b.outstate is not null
GROUP BY b.goodsid, b.skuid

有等值的条件放到on里面去,这样临时表就不会太大
顺序:
on 生成临时表
where 过滤