优化sql，测试环境可以，正式环境直接无了

Question

各位帮我看看那有没有优化的可能了。这个sql在测试环境需要五六秒钟，正式环境直接超时无了

SELECT
    d.AREANO,
    SUM(c.ORDERQTY) - SUM(c.DELIQTY) num
FROM
    db.TBSOYF012 c
LEFT JOIN db.TBSOYF011 b ON
    c.compId = b.compId
    AND b.orderNo = c.orderNo
LEFT JOIN db.TBSBYF01 a
ON
    a.COMPID = b.COMPID AND a.TRANSPORTWAYNO = b.transType
LEFT JOIN DB.TBSHYF18 d ON
    c.compId = d.compId
    AND a.AREANO = d.SEQNO
WHERE
    c.compId = 'yf'
    AND b.transType = 'T'
    AND c.CREDATE <= '20200928'
    AND c.CREDATE >= '20200928'
    AND b.PRODTYPE LIKE 'H%'
    AND c.STATUS != 'X'
GROUP BY
    d.AREANO

Answer 1

使用索引：
CREATE INDEX idx_TBSOYF012_compId_CREDATE ON db.TBSOYF012 (compId, CREDATE);
CREATE INDEX idx_TBSOYF011_compId_orderNo_transType_PRODTYPE ON db.TBSOYF011 (compId, orderNo, transType, PRODTYPE);
CREATE INDEX idx_TBSBYF01_COMPID_TRANSPORTWAYNO ON db.TBSBYF01 (COMPID, TRANSPORTWAYNO);
CREATE INDEX idx_TBSHYF18_compId_SEQNO ON db.TBSHYF18 (compId, SEQNO);