表、索引最后一次分析记录是11年的; 1、需要重新采集系统信息; 2、感觉是索引多了的问题;(订单表:目的地CODE索引) 原始sql; 当AREA.ID in(3) 时,或者(3,4,5,25)时,对应上面左边的执行计划,执行快, ;区域下节点多 当AREA.ID IN(25) 时,对应上边右边的执行计划,执行慢;区域下节点少 但AREA.ID in(3,4) , 执行计划和右边类似(表查询顺序), 执行慢; SELECT DISTINCT STOCK_BOX.ID AS ID, STOCK_BOX.NODE_ID AS STORAGE_NODE_ID, STOCK_BOX.NODE_ID AS FROM_NODE, STOCK_BOX.BUSINESS_ID, STOCK_BOX.BOX_BARCODE AS BOX_CODE, STOCK_BOX.LOT1 AS LOTS, STOCK_BOX.LENGTH AS LENGTH, STOCK_BOX.HEIGHT AS HEIGHT, STOCK_BOX.WIDTH AS WIDTH, STOCK_BOX.WEIGHT AS WEIGHT, STOCK_BOX_PART.PART_ID AS PART_ID, STOCK_BOX_PART.QTY AS QUANTITY, PART.PART_CODE AS PART_CODE, PART.PART_NAME AS PART_NAME, FN.NODE_CODE AS FROM_NODE_CODE, FN.NODE_NAME AS FROM_NODE_NAME, TN.ID AS TO_NODE, TN.NODE_NAME AS TO_NODE_NAME, ORDER_DETAIL.ID as DETAIL_ID from STOCK_BOX_PART INNER JOIN STOCK_BOX ON STOCK_BOX.ID = STOCK_BOX_PART.STOCK_BOX_ID INNER JOIN PART ON STOCK_BOX_PART.PART_ID = PART.ID JOIN NODE FN ON FN.ID = STOCK_BOX.NODE_ID JOIN ORDER_DETAIL ------------数据量大 ON ORDER_DETAIL.ID = STOCK_BOX_PART.ORDER_DETAIL_ID AND ORDER_DETAIL.IS_DELETE = 'N' JOIN NODE TN ON TN.NODE_CODE = ORDER_DETAIL.RECEIVE_NODE_CODE AND TN.IS_DELETE = 'N' JOIN AREA_NODE ON AREA_NODE.NODE_ID = TN.ID JOIN AREA ON AREA.ID = AREA_NODE.AREA_ID AND AREA.IS_DELETE = 'N' AND AREA_NODE.IS_DELETE = 'N' AND AREA.ID in (4) -----------删掉这里的area.id 条件,查询结果返回area.id,在程序中判断 WHERE STOCK_BOX.IS_DELETE = 'N' AND PART.IS_DELETE = 'N' AND STOCK_BOX.NODE_ID = 2149 AND STOCK_BOX.BUSINESS_ID = '3' AND STOCK_BOX.BOX_BARCODE = '33333333333333333333' 在不删除 索引的情况下,怕影响其他性能 第一种:删除area.id 条件,没有直接条件的情况下,不会先查询[color=#800000目的点的信息,查询订单表][/color] ,在查询库存表; 执行计划与 左边类似第二种改进sql:SELECT SDATA.*, PART.PART_CODE FROM (select DISTINCT STOCK_BOX.ID AS ID, STOCK_BOX.NODE_ID AS STORAGE_NODE_ID, STOCK_BOX.NODE_ID AS FROM_NODE, STOCK_BOX.BUSINESS_ID, STOCK_BOX.BOX_BARCODE AS BOX_CODE, STOCK_BOX.LOT1 AS LOTS, STOCK_BOX.LENGTH AS LENGTH, STOCK_BOX.HEIGHT AS HEIGHT, STOCK_BOX.WIDTH AS WIDTH, STOCK_BOX.WEIGHT AS WEIGHT, STOCK_BOX_PART.PART_ID AS PART_ID, STOCK_BOX_PART.QTY AS QUANTITY, /* PART.PART_CODE AS PART_CODE, PART.PART_NAME AS PART_NAME,*/ FN.NODE_CODE AS FROM_NODE_CODE, FN.NODE_NAME AS FROM_NODE_NAME, /* TN.ID AS TO_NODE, TN.NODE_NAME AS TO_NODE_NAME,*/ STOCK_BOX_PART.ORDER_DETAIL_ID as DETAIL_ID from STOCK_BOX INNER JOIN STOCK_BOX_PART ON STOCK_BOX.ID = STOCK_BOX_PART.STOCK_BOX_ID JOIN NODE FN ON FN.ID = STOCK_BOX.NODE_ID WHERE STOCK_BOX.IS_DELETE = 'N' AND STOCK_BOX.NODE_ID = 100 AND STOCK_BOX.BUSINESS_ID = '3' AND STOCK_BOX.BOX_BARCODE = '00000000300003058664') SDATA----这里每次查询结果只会有一条 JOIN PART ON (SDATA.PART_ID = PART.ID) JOIN ORDER_DETAIL ON ORDER_DETAIL.ID = SDATA.DETAIL_ID JOIN NODE TN ON TN.NODE_CODE = ORDER_DETAIL.RECEIVE_NODE_CODE AND TN.IS_DELETE = 'N' JOIN AREA_NODE ON AREA_NODE.NODE_ID = TN.ID JOIN AREA ON AREA.ID = AREA_NODE.AREA_ID WHERE AREA.IS_DELETE = 'N' AND AREA.IS_DELETE = 'N' AND AREA_NODE.IS_DELETE = 'N' AND ORDER_DETAIL.IS_DELETE = 'N' AND AREA.ID in (25)
同意大量的nested loop连接,大量的走索引,你看一看cardinality列,是否与你的数据表记录数一致,不一致就要重新分析一下另外问一句,你是查询备份表慢吧?如果是那肯定是表信息没有分析的原因。
备份一年 =》》》 将2012年的数据 插入到一张新表中;create table as select ...........
将原表2012年的数据delete 掉; 数据量大约在1000w左右吧;
执行计划A: 很快的不到1秒 左边
先根据 发货节点、箱号 查询库存(这里的数据两非常少,一般一条,不会有重复的),
再去查询 订单表(有唯一索引,对应1条),再去 查询目的节点 信息、区域等;执行计划B: 几十分钟;
先根据 区域,查询节点,在查询订单目的点(订单12年的数据delete掉了,还有3000w左右,数据不少;
目的点有索引,是组合索引,lead列不在 条件中,)在去根据订单表id查询库存表,执行计划B,一个是数据量大,一个条件是在组合索引中,循环嵌套的话,估计死在那里了;
delete订单表之前的执行计划没有留意;
区域下的节点数据是不一样的;
执行计划A:区域节点在600
执行B :节点在200左右;但不知为何 表的查询顺序区别这么大;
表、索引最后一次分析记录是11年的;
1、需要重新采集系统信息;
2、感觉是索引多了的问题;(订单表:目的地CODE索引)
原始sql;
当AREA.ID in(3) 时,或者(3,4,5,25)时,对应上面左边的执行计划,执行快, ;区域下节点多
当AREA.ID IN(25) 时,对应上边右边的执行计划,执行慢;区域下节点少
但AREA.ID in(3,4) , 执行计划和右边类似(表查询顺序), 执行慢;
SELECT DISTINCT STOCK_BOX.ID AS ID,
STOCK_BOX.NODE_ID AS STORAGE_NODE_ID,
STOCK_BOX.NODE_ID AS FROM_NODE,
STOCK_BOX.BUSINESS_ID,
STOCK_BOX.BOX_BARCODE AS BOX_CODE,
STOCK_BOX.LOT1 AS LOTS,
STOCK_BOX.LENGTH AS LENGTH,
STOCK_BOX.HEIGHT AS HEIGHT,
STOCK_BOX.WIDTH AS WIDTH,
STOCK_BOX.WEIGHT AS WEIGHT,
STOCK_BOX_PART.PART_ID AS PART_ID,
STOCK_BOX_PART.QTY AS QUANTITY,
PART.PART_CODE AS PART_CODE,
PART.PART_NAME AS PART_NAME,
FN.NODE_CODE AS FROM_NODE_CODE,
FN.NODE_NAME AS FROM_NODE_NAME,
TN.ID AS TO_NODE,
TN.NODE_NAME AS TO_NODE_NAME,
ORDER_DETAIL.ID as DETAIL_ID
from STOCK_BOX_PART
INNER JOIN STOCK_BOX
ON STOCK_BOX.ID = STOCK_BOX_PART.STOCK_BOX_ID
INNER JOIN PART
ON STOCK_BOX_PART.PART_ID = PART.ID
JOIN NODE FN
ON FN.ID = STOCK_BOX.NODE_ID
JOIN ORDER_DETAIL ------------数据量大
ON ORDER_DETAIL.ID = STOCK_BOX_PART.ORDER_DETAIL_ID
AND ORDER_DETAIL.IS_DELETE = 'N'
JOIN NODE TN
ON TN.NODE_CODE = ORDER_DETAIL.RECEIVE_NODE_CODE
AND TN.IS_DELETE = 'N' JOIN AREA_NODE
ON AREA_NODE.NODE_ID = TN.ID
JOIN AREA
ON AREA.ID = AREA_NODE.AREA_ID
AND AREA.IS_DELETE = 'N'
AND AREA_NODE.IS_DELETE = 'N'
AND AREA.ID in (4) -----------删掉这里的area.id 条件,查询结果返回area.id,在程序中判断
WHERE STOCK_BOX.IS_DELETE = 'N'
AND PART.IS_DELETE = 'N'
AND STOCK_BOX.NODE_ID = 2149
AND STOCK_BOX.BUSINESS_ID = '3'
AND STOCK_BOX.BOX_BARCODE = '33333333333333333333'
在不删除 索引的情况下,怕影响其他性能
第一种:删除area.id 条件,没有直接条件的情况下,不会先查询[color=#800000目的点的信息,查询订单表][/color] ,在查询库存表;
执行计划与 左边类似第二种改进sql:SELECT SDATA.*, PART.PART_CODE
FROM (select DISTINCT STOCK_BOX.ID AS ID,
STOCK_BOX.NODE_ID AS STORAGE_NODE_ID,
STOCK_BOX.NODE_ID AS FROM_NODE,
STOCK_BOX.BUSINESS_ID,
STOCK_BOX.BOX_BARCODE AS BOX_CODE,
STOCK_BOX.LOT1 AS LOTS,
STOCK_BOX.LENGTH AS LENGTH,
STOCK_BOX.HEIGHT AS HEIGHT,
STOCK_BOX.WIDTH AS WIDTH,
STOCK_BOX.WEIGHT AS WEIGHT,
STOCK_BOX_PART.PART_ID AS PART_ID,
STOCK_BOX_PART.QTY AS QUANTITY,
/* PART.PART_CODE AS PART_CODE,
PART.PART_NAME AS PART_NAME,*/
FN.NODE_CODE AS FROM_NODE_CODE,
FN.NODE_NAME AS FROM_NODE_NAME,
/* TN.ID AS TO_NODE,
TN.NODE_NAME AS TO_NODE_NAME,*/
STOCK_BOX_PART.ORDER_DETAIL_ID as DETAIL_ID
from STOCK_BOX
INNER JOIN STOCK_BOX_PART
ON STOCK_BOX.ID = STOCK_BOX_PART.STOCK_BOX_ID
JOIN NODE FN
ON FN.ID = STOCK_BOX.NODE_ID
WHERE STOCK_BOX.IS_DELETE = 'N'
AND STOCK_BOX.NODE_ID = 100
AND STOCK_BOX.BUSINESS_ID = '3'
AND STOCK_BOX.BOX_BARCODE = '00000000300003058664') SDATA----这里每次查询结果只会有一条
JOIN PART
ON (SDATA.PART_ID = PART.ID)
JOIN ORDER_DETAIL
ON ORDER_DETAIL.ID = SDATA.DETAIL_ID
JOIN NODE TN
ON TN.NODE_CODE = ORDER_DETAIL.RECEIVE_NODE_CODE
AND TN.IS_DELETE = 'N'
JOIN AREA_NODE
ON AREA_NODE.NODE_ID = TN.ID
JOIN AREA
ON AREA.ID = AREA_NODE.AREA_ID
WHERE AREA.IS_DELETE = 'N'
AND AREA.IS_DELETE = 'N'
AND AREA_NODE.IS_DELETE = 'N'
AND ORDER_DETAIL.IS_DELETE = 'N'
AND AREA.ID in (25)
删除area.id 条件,在没有直接条件的情况下,不会先查询区域下节点的信息,在查询订单表 ,在查询库存表;
执行计划与 左边类似
从字面上看,大表order_detail中,左边的执行计划走了该表的unique索引,右边的走了该表is_delete字段上的索引的范围扫描
如果is_delete取值只有'Y'和'N',且取值为'N'的记录不在少数,就会产生效率问题
可以找一下产生这个问题的原因,重新采下order_detail表的统计信息,确认该索引是否只包含is_delete字段
如果还不能解决,确定该索引不需要以后,删掉它
A: -----------发货点===》目的点 的查询顺序 快
(发货点、物品号)==》库存表===(通过订单id)==》订单表====(目的节点)====》节点===》区域B: --------------目的点===》发货点的 查询顺序 慢
(区域)===》节点==》订单表目的地==(订单ID)==库存表
最终结果只有一个,但中间查询数据肯定不一样;文笔不好,希望对遇到类似问题的人有点帮助