【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,\n"); sql.append(" L.IS_ZF,L.MIN_PACKAGE_QUANTITY,L.TOTAL_NUM,L.TOTAL_PRICES,L.STCOK_QTY,0 MAX_BUY_NUMS,\n"); sql.append(" L.MAX_BUY_NUM,L.PART_STATUS,L.ENGLISH_DESC,L.HAS_HIS_PART,L.BUY_STOCK_TYPE,\n"); sql.append(" L.LAST_DATE_S,L.PRICE_USE_TYPE\n"); sql.append("FROM (SELECT TPM.PART_ID, \n"); sql.append(" TPM.PART_NO, \n"); sql.append(" TPM.PART_NAME_CN, \n"); sql.append(" TPM.PART_REMARK, \n"); sql.append(" TPM.UNIT, \n"); sql.append(" IFNULL(TPU.UNIT_NAME,TPM.UNIT) AS CN_UNIT, \n"); sql.append(" IFNULL(TPP.PART_PRICE, 0) AS UNIT_PRICE, \n"); sql.append(" IFNULL(TOP.IS_ZF,"+Constant.IF_TYPE_NO+") IS_ZF, \n"); sql.append(" IFNULL(TOP.MIN_PACKAGE_QUANTITY, 0) MIN_PACKAGE_QUANTITY, \n"); sql.append(" IFNULL(TOP.MIN_PACKAGE_QUANTITY, 0) * 1 TOTAL_NUM, \n"); sql.append(" IFNULL(TPP.PART_PRICE, 0) * IFNULL(TOP.MIN_PACKAGE_QUANTITY, 0) * 1 AS TOTAL_PRICES, \n"); 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, \n"); }else{ sql.append(" IFNULL(STOCK.STCOK_QTY,0) - IFNULL(STOCK.LOC_QTY, 0) AS STCOK_QTY, \n"); } sql.append(" TPL.MAX_BUY_NUM, \n"); sql.append(" TPM.PART_STATUS, \n"); sql.append(" TPM.ENGLISH_DESC, \n"); sql.append(" T3.PART_ID AS HAS_HIS_PART, \n"); sql.append(" '"+condition.get("buyStockType")+"' as BUY_STOCK_TYPE, \n"); sql.append(" '"+condition.get("lastDate")+"' as LAST_DATE_S, \n"); sql.append(" '"+priceUseType+"' as PRICE_USE_TYPE\n"); sql.append(" FROM TT_PT_MAINDATA TPM \n"); 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 \n"); sql.append(" WHERE TI1.PARAMETER_CLASS_ID = TI2.PARAMETER_CLASS_ID \n"); sql.append(" AND TI1.PARAMETER_TYPE = 'PART_CLASS' \n"); sql.append(" AND TI1.COMPANY_ID=").append(factoryCompanyId).append(" \n"); sql.append(" AND TI2.VALID_STATUS=10011001 \n"); 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) \n"); sql.append(" UNION \n"); sql.append(" SELECT DISTINCT TI1.PARAMETER_CLASS_CODE, TI1.COMPANY_ID FROM TM_PART_PARM_CLASS TI1, TM_PART_PARM_ITEM TI2 \n"); sql.append(" WHERE TI1.PARAMETER_CLASS_ID = TI2.PARAMETER_CLASS_ID \n"); sql.append(" AND TI1.PARAMETER_TYPE = 'PART_CLASS' \n"); sql.append(" AND TI1.COMPANY_ID=").append(factoryCompanyId).append(" \n"); sql.append(" AND TI2.VALID_STATUS=10011001 \n"); sql.append(" AND TI2.LINK_CODE IN (SELECT BRAND_CODE FROM TT_PART_ORDER_ASC_BRAND WHERE ASC_CODE=? AND VALID_STATUS=10011001)) TPPC \n"); params.add(userInfo.getOrgCode()); sql.append(" ON TPM.CLASS_CODE=TPPC.PARAMETER_CLASS_CODE AND TPM.COMPANY_ID=TPPC.COMPANY_ID \n"); } sql.append(" LEFT JOIN TM_PART_UNIT TPU ON TPU.UNIT_CODE = TPM.UNIT \n"); sql.append(" LEFT JOIN TM_PT_PART_PRICE TPP ON TPP.PART_ID = TPM.PART_ID \n"); sql.append(" AND TPP.COMPANY_ID =TPM.COMPANY_ID \n"); sql.append(" AND TPP.PRICE_TYPE = "+(StringUtils.isNotEmpty(priceUseType) ? priceUseType : Constant.PRICE_DEALER_TYPE_NODE)+" \n"); sql.append(" LEFT JOIN TM_PT_ORDER_PARA TOP ON TOP.PART_ID = TPM.PART_ID \n"); sql.append(" LEFT JOIN tm_pt_maxbuynum TPL ON TPL.PART_ID = TPM.PART_ID AND TPL.ORDER_TYPE=? AND TPL.VALID_STATUS=10011001 \n"); params.add(condition.get("orderType")); sql.append(" LEFT JOIN (SELECT PART_ID, SUM(STCOK_QTY) STCOK_QTY, SUM(LOC_QTY) LOC_QTY, \n"); sql.append(" SUM(SAFE_STOCK_NUM) SAFE_STOCK_NUM \n"); sql.append(" FROM TT_PT_FACTORY_STOCK \n"); sql.append(" GROUP BY PART_ID) STOCK ON STOCK.PART_ID = TPM.PART_ID \n"); sql.append(" LEFT JOIN (SELECT TZ.PART_ID, \n"); sql.append(" IFNULL(IFNULL(SUM(TZ.SHIP_NUM), 0) + \n"); sql.append(" IFNULL(SUM(TZ.MATCH_NUM), 0), \n"); sql.append(" 0) EXSITS_NUM \n"); sql.append(" FROM TT_PT_ORDITEM TZ, TT_PT_ORDER TS \n"); sql.append(" WHERE TZ.ORDER_ID = TS.ORDER_ID \n"); sql.append(" AND TS.ASC_ID = "+ascId+" \n"); sql.append(" AND DATE_FORMAT(TS.LAST_DATE, '%y/%c/%d') >= \n"); sql.append(" DATE_FORMAT(NOW(), '%y/%c/%d') GROUP BY TZ.PART_ID) T2 ON T2.PART_ID =TPM.PART_ID \n"); sql.append(" left join (SELECT DISTINCT T.PART_ID \n"); sql.append(" FROM TT_PT_ORDITEM T, TT_PT_ORDER T1 \n"); sql.append(" WHERE T.ORDER_ID = T1.ORDER_ID \n"); sql.append(" AND T1.ASC_ID = "+ascId+" \n"); sql.append(" AND IFNULL(T.MATCH_NUM, 0) < T.TOTAL_NUM \n"); 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+"') \n"); sql.append(" GROUP BY T.PART_ID) T3 ON T3.PART_ID = TPM.PART_ID \n"); sql.append(" WHERE TPM.COMPANY_ID = "+factoryCompanyId+" \n"); sql.append(" AND TOP.BUY_STOCK_TYPE IN ("+condition.get("buyStockType")+") \n"); if(Constant.PRICE_DEALER_TYPE_DISCOUNT.equals(priceUseType)) { sql.append(" AND TPP.STATUS=57171002 AND TPP.PART_PRICE>0 \n"); } //维修站类型 if(!condition.isNull("category") && (Constant.CATEGORY_FCO.toString()).equals(condition.get("category"))){ sql.append(" AND (EXISTS (SELECT 1 FROM TT_FCO_PARTS_AUTHORITY TFPA \n"); sql.append(" WHERE TFPA.PART_CLASS_CODE = TPM.CLASS_CODE AND TFPA.ASC_CODE = "+logonUser.getOrgCode()+" )) \n"); } sql.append(" AND (EXISTS (SELECT 1 \n"); sql.append(" FROM TM_ASC_INFO TZ1 \n"); sql.append(" WHERE IFNULL(TZ1.CATEGORY, '1') <> "+Constant.CATEGORY_FCO+" \n"); sql.append(" AND TZ1.ORG_ID = ? AND TZ1.ORG_TYPE IN (15061010, 15061012)) OR \n"); params.add(userInfo.getOrgId()); sql.append(" (EXISTS (SELECT 1 \n"); sql.append(" FROM TM_ASC_INFO TZ1 \n"); sql.append(" WHERE IFNULL(TZ1.CATEGORY, '1') = "+Constant.CATEGORY_FCO+" \n"); sql.append(" AND TZ1.ORG_ID = ? AND TZ1.ORG_TYPE IN (15061010, 15061012)) AND EXISTS \n"); params.add(userInfo.getOrgId()); // sql.append(" (SELECT 1 \n"); // sql.append(" FROM TM_PART_PARM_CLASS TY1, TM_PART_PARM_ITEM TY2 \n"); // sql.append(" WHERE TY1.PARAMETER_CLASS_ID = TY2.PARAMETER_CLASS_ID \n"); // sql.append(" AND TY1.PARAMETER_CLASS_CODE = TPM.CLASS_CODE \n"); // sql.append(" AND TY2.LINK_CODE = 'U' \n"); // sql.append(" AND TY1.PARAMETER_TYPE = 'PART_CLASS' \n"); // sql.append(" )))"); sql.append(" (SELECT 1 \n"); sql.append(" FROM TT_FCO_PARTS_AUTHORITY TFPA \n"); sql.append(" WHERE TFPA.PART_CLASS_CODE = TPM.CLASS_CODE \n"); sql.append(" AND TFPA.ASC_CODE= \n"); sql.append(" (SELECT ASC_CODE FROM tm_asc_info WHERE ORG_ID= ").append(userInfo.getOrgId()).append(" AND CATEGORY<>"+Constant.CATEGORY_JMC+") \n"); sql.append(" )))"); if(!condition.isNull("partNo")){ sql.append(" AND TPM.PART_NO LIKE ?\n"); params.add("%"+condition.get("partNo")+"%"); } if(!condition.isNull("partName")){ sql.append(" AND TPM.PART_NAME_CN LIKE ?\n"); params.add("%"+condition.get("partName")+"%"); } if(!condition.isNull("partIds")){ sql.append(" AND TPM.PART_ID=?\n"); params.add(condition.get("partIds")); } if(!condition.isNull("partNos")){ sql.append(" AND TPM.PART_NO IN ("+condition.get("partNos")+") \n"); } if(!condition.isNull("orderType") && Constant.PART_ORDER_TYPE_09.equals(condition.get("orderType"))){ //直发订单 sql.append(" AND TOP.IS_ZF="+Constant.IF_TYPE_YES+"\n"); // sql.append(" AND EXISTS (select 1 from tt_pt_amount_detail a,tt_pt_amount b \n"); // sql.append(" where a.amount_id=b.amount_id and b.asc_id="+ascId+" and a.part_id=TPM.part_id) \n"); 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 + ")\n"); }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+"\n"); } sql.append(" ) L\n"); 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秒】
今天的收获备忘录
