【DataBase】SQL优化问题

在DAO层的动态SQL：

    //订单新增，查询配件主数据
    @SuppressWarnings("rawtypes")
    public PageInfoDto getPartsForPartOrder(ElemBean condition) {
//            String oldDate="2015-01-01";//过滤老数据
        UserInfoDto logonUser = UserUtil.getSessionUser();
        List<Map> userList =queryAscInfoById(logonUser.getOrgId());
        Long factoryCompanyId=0L;
        Long ascId =0L;
        String priceUseType = Constant.PRICE_DEALER_TYPE_NODE;
        if(userList !=null && userList.size()>0){
            factoryCompanyId=Long.valueOf(userList.get(0).get("FACTORY_COMPANY_ID")+"");
            ascId=Long.valueOf(userList.get(0).get("ASC_ID")+"");
            priceUseType =String.valueOf(userList.get(0).get("PRICE_USE_TYPE"));
        }
        
        if(condition.eq("priceUseType", Constant.PRICE_DEALER_TYPE_DISCOUNT)) {
            priceUseType = Constant.PRICE_DEALER_TYPE_DISCOUNT;
        }
        
        if(condition.eq("priceUseType", Constant.PRICE_DEALER_TYPE_COST)) {
            priceUseType = Constant.PRICE_DEALER_TYPE_COST;
        }

        if(condition.eq("priceUseType", Constant.PRICE_DEALER_TYPE_PLAN)) {
            priceUseType = Constant.PRICE_DEALER_TYPE_PLAN;
        }

        
//            Double parmValue =getSafeStockParm();
        double securityRadix = 1.5;
        BusinessParamDto bpDto = bpu.getFirstParamOfParam(BPConstant.PRM00068, "JMC");
        if(bpDto != null) {
            securityRadix = new Double(bpDto.getParaValue());
        }
        List<Object> params = new ArrayList<>();
        StringBuffer sql= new StringBuffer();  
        sql.append("SELECT L.PART_ID,L.PART_NO,L.PART_NAME_CN,L.PART_REMARK,L.UNIT,L.CN_UNIT,L.UNIT_PRICE,
");
        sql.append("       L.IS_ZF,L.MIN_PACKAGE_QUANTITY,L.TOTAL_NUM,L.TOTAL_PRICES,L.STCOK_QTY,0 MAX_BUY_NUMS,
");
        sql.append("       L.MAX_BUY_NUM,L.PART_STATUS,L.ENGLISH_DESC,L.HAS_HIS_PART,L.BUY_STOCK_TYPE,
");
        sql.append("       L.LAST_DATE_S,L.PRICE_USE_TYPE
");
        sql.append("FROM (SELECT TPM.PART_ID, 
");  
        sql.append("             TPM.PART_NO, 
");  
        sql.append("             TPM.PART_NAME_CN, 
");  
        sql.append("             TPM.PART_REMARK, 
");  
        sql.append("             TPM.UNIT, 
");  
        sql.append("             IFNULL(TPU.UNIT_NAME,TPM.UNIT) AS CN_UNIT, 
");  
        sql.append("             IFNULL(TPP.PART_PRICE, 0) AS UNIT_PRICE, 
");  
        sql.append("             IFNULL(TOP.IS_ZF,"+Constant.IF_TYPE_NO+") IS_ZF, 
");
        sql.append("                IFNULL(TOP.MIN_PACKAGE_QUANTITY, 0) MIN_PACKAGE_QUANTITY, 
");  
        sql.append("             IFNULL(TOP.MIN_PACKAGE_QUANTITY, 0) * 1 TOTAL_NUM, 
");  
        sql.append("             IFNULL(TPP.PART_PRICE, 0) * IFNULL(TOP.MIN_PACKAGE_QUANTITY, 0) * 1 AS TOTAL_PRICES, 
");
        if(!condition.isNull("orderType") && Constant.PART_ORDER_TYPE_13.equals(condition.get("orderType"))){
            sql.append("             IFNULL(STOCK.STCOK_QTY,0) - IFNULL(STOCK.LOC_QTY, 0) - CEIL("+securityRadix+"*IFNULL(STOCK.SAFE_STOCK_NUM,0)) AS STCOK_QTY, 
");
        }else{
            sql.append("             IFNULL(STOCK.STCOK_QTY,0) - IFNULL(STOCK.LOC_QTY, 0) AS STCOK_QTY, 
");
        }
        sql.append("       TPL.MAX_BUY_NUM, 
");
        sql.append("       TPM.PART_STATUS, 
");  
        sql.append("       TPM.ENGLISH_DESC, 
");  
        sql.append("       T3.PART_ID AS HAS_HIS_PART, 
");  
        sql.append("       '"+condition.get("buyStockType")+"' as BUY_STOCK_TYPE, 
");  
        sql.append("       '"+condition.get("lastDate")+"' as LAST_DATE_S, 
");
        sql.append("       '"+priceUseType+"' as PRICE_USE_TYPE
");
        sql.append("  FROM TT_PT_MAINDATA TPM 
");

         UserInfoDto userInfo = UserUtil.getSessionUser();
        if(!Constant.PRICE_DEALER_TYPE_DISCOUNT.equals(priceUseType)) {
            sql.append("    INNER JOIN (SELECT DISTINCT TI1.PARAMETER_CLASS_CODE, TI1.COMPANY_ID FROM TM_PART_PARM_CLASS TI1, TM_PART_PARM_ITEM TI2 
");
             sql.append("    WHERE TI1.PARAMETER_CLASS_ID = TI2.PARAMETER_CLASS_ID 
");
             sql.append("    AND TI1.PARAMETER_TYPE = 'PART_CLASS' 
");
             sql.append("    AND TI1.COMPANY_ID=").append(factoryCompanyId).append(" 
");
             sql.append("    AND TI2.VALID_STATUS=10011001 
");
             sql.append("    AND TI2.LINK_CODE NOT IN (SELECT BRAND_CODE FROM TT_PART_ORDER_ASC_BRAND WHERE ASC_CODE='ALLNOT' AND VALID_STATUS=10011001) 
");
             sql.append("    UNION 
");
             sql.append("    SELECT DISTINCT TI1.PARAMETER_CLASS_CODE, TI1.COMPANY_ID FROM TM_PART_PARM_CLASS TI1, TM_PART_PARM_ITEM TI2 
");
             sql.append("    WHERE TI1.PARAMETER_CLASS_ID = TI2.PARAMETER_CLASS_ID 
");
             sql.append("    AND TI1.PARAMETER_TYPE = 'PART_CLASS' 
");
             sql.append("    AND TI1.COMPANY_ID=").append(factoryCompanyId).append(" 
");
             sql.append("    AND TI2.VALID_STATUS=10011001 
");
             sql.append("    AND TI2.LINK_CODE IN (SELECT BRAND_CODE FROM TT_PART_ORDER_ASC_BRAND WHERE ASC_CODE=? AND VALID_STATUS=10011001)) TPPC  
");
             params.add(userInfo.getOrgCode());
             sql.append("    ON TPM.CLASS_CODE=TPPC.PARAMETER_CLASS_CODE AND TPM.COMPANY_ID=TPPC.COMPANY_ID 
");
        }
        
        sql.append("  LEFT JOIN TM_PART_UNIT TPU ON TPU.UNIT_CODE = TPM.UNIT 
");  
        sql.append("  LEFT JOIN TM_PT_PART_PRICE TPP ON TPP.PART_ID = TPM.PART_ID 
");     
        sql.append("                                AND TPP.COMPANY_ID =TPM.COMPANY_ID 
");
        sql.append("                                AND TPP.PRICE_TYPE = "+(StringUtils.isNotEmpty(priceUseType) ? priceUseType : Constant.PRICE_DEALER_TYPE_NODE)+" 
");  
        sql.append("  LEFT JOIN TM_PT_ORDER_PARA TOP ON TOP.PART_ID = TPM.PART_ID 
");  
        sql.append("  LEFT JOIN tm_pt_maxbuynum TPL ON TPL.PART_ID = TPM.PART_ID AND TPL.ORDER_TYPE=? AND TPL.VALID_STATUS=10011001 
");
        params.add(condition.get("orderType"));
        sql.append("  LEFT JOIN (SELECT PART_ID, SUM(STCOK_QTY) STCOK_QTY, SUM(LOC_QTY) LOC_QTY, 
");  
        sql.append("                    SUM(SAFE_STOCK_NUM) SAFE_STOCK_NUM 
");
        sql.append("               FROM TT_PT_FACTORY_STOCK 
");  
        sql.append("              GROUP BY PART_ID) STOCK ON STOCK.PART_ID = TPM.PART_ID 
");  
        sql.append("  LEFT JOIN (SELECT TZ.PART_ID, 
");  
        sql.append("                    IFNULL(IFNULL(SUM(TZ.SHIP_NUM), 0) + 
");  
        sql.append("                           IFNULL(SUM(TZ.MATCH_NUM), 0), 
");  
        sql.append("                           0) EXSITS_NUM 
");  
        sql.append("               FROM TT_PT_ORDITEM TZ, TT_PT_ORDER TS 
");  
        sql.append("              WHERE TZ.ORDER_ID = TS.ORDER_ID 
");  
        sql.append("                AND TS.ASC_ID = "+ascId+" 
"); 
        sql.append("                AND DATE_FORMAT(TS.LAST_DATE, '%y/%c/%d') >= 
");  
        sql.append("                    DATE_FORMAT(NOW(), '%y/%c/%d') GROUP BY TZ.PART_ID) T2 ON T2.PART_ID =TPM.PART_ID 
");  
        sql.append("  left join (SELECT DISTINCT T.PART_ID 
");  
        sql.append("               FROM TT_PT_ORDITEM T, TT_PT_ORDER T1 
");  
        sql.append("              WHERE T.ORDER_ID = T1.ORDER_ID 
");  
        sql.append("                AND T1.ASC_ID = "+ascId+" 
");
        sql.append("                AND IFNULL(T.MATCH_NUM, 0) < T.TOTAL_NUM 
");  
        sql.append("                AND STATUS NOT IN ('"+Constant.PART_ORDITEM_STATUS_COMPLETE+"', '"+Constant.PART_ORDITEM_STATUS_CANCEL+"', '"+Constant.PART_ORDITEM_STATUS_CLEAN+"','"+Constant.PART_ORDITEM_STATUS_OTHER_CANCEL+"') 
");  
        sql.append("              GROUP BY T.PART_ID) T3 ON T3.PART_ID = TPM.PART_ID 
");  
        sql.append(" WHERE TPM.COMPANY_ID = "+factoryCompanyId+" 
");  
        sql.append("   AND TOP.BUY_STOCK_TYPE IN ("+condition.get("buyStockType")+") 
");
        
        if(Constant.PRICE_DEALER_TYPE_DISCOUNT.equals(priceUseType)) {
            sql.append("   AND TPP.STATUS=57171002 AND TPP.PART_PRICE>0 
");
        }
        
        //维修站类型
        if(!condition.isNull("category") && (Constant.CATEGORY_FCO.toString()).equals(condition.get("category"))){
            sql.append(" AND (EXISTS (SELECT 1 FROM TT_FCO_PARTS_AUTHORITY TFPA 
");
            sql.append(" WHERE TFPA.PART_CLASS_CODE = TPM.CLASS_CODE AND TFPA.ASC_CODE = "+logonUser.getOrgCode()+" )) 
");
        }
        
        
        sql.append(" AND (EXISTS (SELECT 1 
");
        sql.append("                  FROM TM_ASC_INFO TZ1 
");
        sql.append("                 WHERE IFNULL(TZ1.CATEGORY, '1') <> "+Constant.CATEGORY_FCO+" 
");
        sql.append("                   AND TZ1.ORG_ID = ? AND TZ1.ORG_TYPE IN (15061010, 15061012)) OR 
");
        params.add(userInfo.getOrgId());
        sql.append("        (EXISTS (SELECT 1 
");
        sql.append("                          FROM TM_ASC_INFO TZ1 
");
        sql.append("                         WHERE IFNULL(TZ1.CATEGORY, '1') = "+Constant.CATEGORY_FCO+" 
");
        sql.append("                           AND TZ1.ORG_ID = ? AND TZ1.ORG_TYPE IN (15061010, 15061012)) AND EXISTS 
");
        params.add(userInfo.getOrgId());
//            sql.append("         (SELECT 1 
");
//            sql.append("                   FROM TM_PART_PARM_CLASS TY1, TM_PART_PARM_ITEM TY2 
");
//            sql.append("                  WHERE TY1.PARAMETER_CLASS_ID = TY2.PARAMETER_CLASS_ID 
");
//            sql.append("                    AND TY1.PARAMETER_CLASS_CODE = TPM.CLASS_CODE 
");
//            sql.append("                    AND TY2.LINK_CODE = 'U' 
");
//            sql.append("                    AND TY1.PARAMETER_TYPE = 'PART_CLASS' 
");
//            sql.append("                    )))");
        sql.append("         (SELECT 1 
");
        sql.append("                   FROM TT_FCO_PARTS_AUTHORITY TFPA  
");
        sql.append("                  WHERE TFPA.PART_CLASS_CODE = TPM.CLASS_CODE 
");
        sql.append("                    AND TFPA.ASC_CODE= 
");
        sql.append(" (SELECT ASC_CODE FROM tm_asc_info WHERE ORG_ID= ").append(userInfo.getOrgId()).append(" AND CATEGORY<>"+Constant.CATEGORY_JMC+") 
");
        sql.append("                    )))");
        
        if(!condition.isNull("partNo")){
            sql.append("   AND TPM.PART_NO LIKE ?
");
            params.add("%"+condition.get("partNo")+"%");
        }
        if(!condition.isNull("partName")){
            sql.append("   AND TPM.PART_NAME_CN LIKE ?
");
            params.add("%"+condition.get("partName")+"%");
        }
        if(!condition.isNull("partIds")){
            sql.append("   AND TPM.PART_ID=?
");
            params.add(condition.get("partIds"));
        }
        if(!condition.isNull("partNos")){
            sql.append("   AND TPM.PART_NO IN ("+condition.get("partNos")+") 
");
        }
        if(!condition.isNull("orderType") && Constant.PART_ORDER_TYPE_09.equals(condition.get("orderType"))){
            //直发订单
            sql.append("   AND TOP.IS_ZF="+Constant.IF_TYPE_YES+"
");
            //  sql.append("   AND EXISTS (select 1 from tt_pt_amount_detail a,tt_pt_amount b 
");
            //  sql.append("               where a.amount_id=b.amount_id and b.asc_id="+ascId+" and a.part_id=TPM.part_id) 
");
            sql.append("AND TPM.PART_ID IN (SELECT a.part_id FROM tt_pt_amount_detail a, tt_pt_amount b WHERE a.amount_id = b.amount_id AND b.asc_id = " + ascId + ")
");
        }else if(!condition.isNull("orderType") && Constant.PART_ORDER_TYPE_11.equals(condition.get("orderType"))){
            //特殊加工件
            sql.append("   AND TOP.IS_SPECIAL_MACH="+Constant.IF_TYPE_YES+"
");
        }
        sql.append(" ) L
");
        PageInfoDto pageInfoDto = DcsDaoUtil.pageQuery(sql.toString(),params);
        
        return pageInfoDto;
    }
    

在经过DEBUG之后抽出的SQL和参数合并：

这个SQL会无法查询出结果，原因的语句我用标红显示

最下面联表 MainData TPM

SELECT
    L.PART_ID,
    L.PART_NO,
    L.PART_NAME_CN,
    L.PART_REMARK,
    L.UNIT,
    L.CN_UNIT,
    L.UNIT_PRICE,
    L.IS_ZF,
    L.MIN_PACKAGE_QUANTITY,
    L.TOTAL_NUM,
    L.TOTAL_PRICES,
    L.STCOK_QTY,
    0 MAX_BUY_NUMS,
    L.MAX_BUY_NUM,
    L.PART_STATUS,
    L.ENGLISH_DESC,
    L.HAS_HIS_PART,
    L.BUY_STOCK_TYPE,
    L.LAST_DATE_S,
    L.PRICE_USE_TYPE
FROM
    (
    SELECT
        TPM.PART_ID,
        TPM.PART_NO,
        TPM.PART_NAME_CN,
        TPM.PART_REMARK,
        TPM.UNIT,
        IFNULL( TPU.UNIT_NAME, TPM.UNIT ) AS CN_UNIT,
        IFNULL( TPP.PART_PRICE, 0 ) AS UNIT_PRICE,
        IFNULL( TOP.IS_ZF, 10041002 ) IS_ZF,
        IFNULL( TOP.MIN_PACKAGE_QUANTITY, 0 ) MIN_PACKAGE_QUANTITY,
        IFNULL( TOP.MIN_PACKAGE_QUANTITY, 0 ) * 1 TOTAL_NUM,
        IFNULL( TPP.PART_PRICE, 0 ) * IFNULL( TOP.MIN_PACKAGE_QUANTITY, 0 ) * 1 AS TOTAL_PRICES,
        IFNULL( STOCK.STCOK_QTY, 0 ) - IFNULL( STOCK.LOC_QTY, 0 ) AS STCOK_QTY,
        TPL.MAX_BUY_NUM,
        TPM.PART_STATUS,
        TPM.ENGLISH_DESC,
        T3.PART_ID AS HAS_HIS_PART,
        '57071002,57071003' AS BUY_STOCK_TYPE,
        'null' AS LAST_DATE_S,
        '57111003' AS PRICE_USE_TYPE
    FROM
        TT_PT_MAINDATA TPM
        INNER JOIN (
        SELECT DISTINCT
            TI1.PARAMETER_CLASS_CODE,
            TI1.COMPANY_ID
        FROM
            TM_PART_PARM_CLASS TI1,
            TM_PART_PARM_ITEM TI2
        WHERE
            TI1.PARAMETER_CLASS_ID = TI2.PARAMETER_CLASS_ID
            AND TI1.PARAMETER_TYPE = 'PART_CLASS'
            AND TI1.COMPANY_ID = 1000000001
            AND TI2.VALID_STATUS = 10011001
            AND TI2.LINK_CODE NOT IN ( SELECT BRAND_CODE FROM TT_PART_ORDER_ASC_BRAND WHERE ASC_CODE = 'ALLNOT' AND VALID_STATUS = 10011001 ) UNION
        SELECT DISTINCT
            TI1.PARAMETER_CLASS_CODE,
            TI1.COMPANY_ID
        FROM
            TM_PART_PARM_CLASS TI1,
            TM_PART_PARM_ITEM TI2
        WHERE
            TI1.PARAMETER_CLASS_ID = TI2.PARAMETER_CLASS_ID
            AND TI1.PARAMETER_TYPE = 'PART_CLASS'
            AND TI1.COMPANY_ID = 1000000001
            AND TI2.VALID_STATUS = 10011001
        AND TI2.LINK_CODE IN ( SELECT BRAND_CODE FROM TT_PART_ORDER_ASC_BRAND WHERE ASC_CODE = '21101060' AND VALID_STATUS = 10011001 )) TPPC ON TPM.CLASS_CODE = TPPC.PARAMETER_CLASS_CODE
        AND TPM.COMPANY_ID = TPPC.COMPANY_ID
        LEFT JOIN TM_PART_UNIT TPU ON TPU.UNIT_CODE = TPM.UNIT
        LEFT JOIN TM_PT_PART_PRICE TPP ON TPP.PART_ID = TPM.PART_ID
        AND TPP.COMPANY_ID = TPM.COMPANY_ID
        AND TPP.PRICE_TYPE = 57111003
        LEFT JOIN TM_PT_ORDER_PARA TOP ON TOP.PART_ID = TPM.PART_ID
        LEFT JOIN tm_pt_maxbuynum TPL ON TPL.PART_ID = TPM.PART_ID
        AND TPL.ORDER_TYPE = '57011009'
        AND TPL.VALID_STATUS = 10011001
        LEFT JOIN ( SELECT PART_ID, SUM( STCOK_QTY ) STCOK_QTY, SUM( LOC_QTY ) LOC_QTY, SUM( SAFE_STOCK_NUM ) SAFE_STOCK_NUM FROM TT_PT_FACTORY_STOCK GROUP BY PART_ID ) STOCK ON STOCK.PART_ID = TPM.PART_ID
        LEFT JOIN (
        SELECT
            TZ.PART_ID,
            IFNULL( IFNULL( SUM( TZ.SHIP_NUM ), 0 ) + IFNULL( SUM( TZ.MATCH_NUM ), 0 ), 0 ) EXSITS_NUM
        FROM
            TT_PT_ORDITEM TZ,
            TT_PT_ORDER TS
        WHERE
            TZ.ORDER_ID = TS.ORDER_ID
            AND TS.ASC_ID = 6
            AND DATE_FORMAT( TS.LAST_DATE, '%y/%c/%d' ) >= DATE_FORMAT( NOW(), '%y/%c/%d' )
        GROUP BY
            TZ.PART_ID
        ) T2 ON T2.PART_ID = TPM.PART_ID
        LEFT JOIN (
        SELECT DISTINCT
            T.PART_ID
        FROM
            TT_PT_ORDITEM T,
            TT_PT_ORDER T1
        WHERE
            T.ORDER_ID = T1.ORDER_ID
            AND T1.ASC_ID = 6
            AND IFNULL( T.MATCH_NUM, 0 ) < T.TOTAL_NUM
            AND STATUS NOT IN ( '57091004', '57091005', '57091006', '57091007' )
        GROUP BY
            T.PART_ID
        ) T3 ON T3.PART_ID = TPM.PART_ID
    WHERE
        TPM.COMPANY_ID = 1000000001
        AND TOP.BUY_STOCK_TYPE IN ( 57071002, 57071003 )
        AND (
            EXISTS (
            SELECT
                1
            FROM
                TM_ASC_INFO TZ1
            WHERE
                IFNULL( TZ1.CATEGORY, '1' ) <> 60111004
                AND TZ1.ORG_ID = 416
            AND TZ1.ORG_TYPE IN ( 15061010, 15061012 ))
            OR (
                EXISTS (
                SELECT
                    1
                FROM
                    TM_ASC_INFO TZ1
                WHERE
                    IFNULL( TZ1.CATEGORY, '1' ) = 60111004
                    AND TZ1.ORG_ID = 416
                AND TZ1.ORG_TYPE IN ( 15061010, 15061012 ))
                AND EXISTS (
                SELECT
                    1
                FROM
                    TT_FCO_PARTS_AUTHORITY TFPA
                WHERE
                    TFPA.PART_CLASS_CODE = TPM.CLASS_CODE
                    AND TFPA.ASC_CODE = ( SELECT ASC_CODE FROM tm_asc_info WHERE ORG_ID = 416 AND CATEGORY <> 60111001 )
                )))
        AND TOP.IS_ZF = 10041001
        AND EXISTS (
        SELECT
            1
        FROM
            tt_pt_amount_detail a,
            tt_pt_amount b
        WHERE
            a.amount_id = b.amount_id
            AND b.asc_id = 6
            AND a.part_id = TPM.part_id
        )
    ) L

一开始花了很多时间才定位这个语句导致

然后我的想法是直接在里面联表查

        SELECT
            1
        FROM
            tt_pt_amount_detail a,
            tt_pt_amount b,
            (上面的那张TPM表放这里) TPM
        WHERE
            a.amount_id = b.amount_id
            AND b.asc_id = 6
            AND a.part_id = TPM.part_id
        )

然后他用EXISTS去判断，作为一个条件

所以我就在这个语句结尾追加LIMIT 更快速的返回记录来给EXISTS去判断

但是这样的优化不符合业务的逻辑，问了同事之后又改成了：

AND TPM.PART_ID IN (
  SELECT
   a.part_id
  FROM
   tt_pt_amount_detail a,
   tt_pt_amount b
  WHERE
   a.amount_id = b.amount_id
   AND b.asc_id = 6
)

同事给出的原话是这样：

外大里小用in  外小里大用EXISTS

我的优化结果是 从【无结果】 -> 【4秒】

同事的优化结果是【1.9秒】

今天的收获备忘录