重现步骤

数据库：zhcxkj-center-warehouse
查询时间：42.6117
执行次数: 166
扫描行数：1533360
发送行数：621.807
指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . pending_move_number - IFNULL ( f . sumNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as task_type , ? as taskStatus , b . pending_move_number , IFNULL ( f . sumNumber , ? ) sumNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . origin_order_code , b . order_list_id , SUM ( CASE WHEN a . task_status < ? then b . sku_number else IFNULL ( b . moved_number , ? ) + IFNULL ( b . abnormal_number , ? ) end ) sumNumber FROM ? a join t_stk_moving_task_detail b on b . moving_task_id = a . id and b . customer_id = a . customer_id WHERE task_type = ? AND task_status < ? GROUP BY a . customer_id , a . origin_order_code , b . order_list_id ) f on f . origin_order_code = a . order_code and f . order_list_id = b . id AND f . customer_id = a . customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a . order_code = e . order_code AND a . customer_id = e . customer_id WHERE a . add_time >= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . is_stock_order = ? and b . pending_move_number > ? AND b . pending_move_number - IFNULL ( f . sumNumber , ? ) > ? ;
真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.pending_move_number- IFNULL(f.sumNumber, 0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 3 as task_type, 0 as taskStatus, b.pending_move_number, IFNULL(f.sumNumber, 0) sumNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.origin_order_code,b.order_list_id, SUM(CASE WHEN a.task_status < 4 then b.sku_number else IFNULL(b.moved_number,0)+IFNULL(b.abnormal_number,0)end) sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type=3 AND task_status < 5 GROUP BY a.customer_id,a.origin_order_code,b.order_list_id )f on f.origin_order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id WHERE a.add_time >= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.is_stock_order = 1 and b.pending_move_number>0 AND b.pending_move_number- IFNULL(f.sumNumber, 0) > 0;
王凯,人工修改过
AISQL修改建议：扫描行数 150万 以上，未使用索引
explain表：
额外建议：
索引建议：

历史记录  
  1. 2025-09-09 10:51:09, 由 周杰 创建。
  2. 2025-09-09 11:00:29, 由 程伟 解决，方案为 无法重现-先挂起 。
    修改了 解决方案，旧值为 ""，新值为 "notrepro2"。
    修改了 解决版本，旧值为 ""，新值为 "trunk"。
    修改了 解决日期，旧值为 ""，新值为 "2025-09-09 11:00:09"。
    修改了 指派给 ，旧值为 "chengwei"，新值为 "zhoujie"。
    修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
    修改了 是否确认，旧值为 "0"，新值为 "1"。
    修改了 解决者 ，旧值为 ""，新值为 "chengwei"。

    1.37秒出查询结果，从业务角度看，无优化空间

  3. 2025-09-09 19:44:12, 由 周杰 指派给 王凯。
    修改了 指派给，旧值为 "zhoujie"，新值为 "wangkai"。
    看下有没有疑问 没有的话关闭BUG
  4. 2025-09-10 09:22:21, 由 王凯 编辑。
    修改了 所属模块 ，旧值为 "0"，新值为 "1932"。
    修改了 业务部门 ，旧值为 "0"，新值为 "2"。
    修改了 测试责任人，旧值为 ""，新值为 "closed"。
    修改了 解决时效 ，旧值为 "0"，新值为 "0.15"。
    修改了 重现步骤 ，区别为：
    001-
    001+ 数据库：zhcxkj-center-warehouse 查询时间：42.6117 执行次数: 166 扫描行数：1533360 发送行数：621.807 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . pending_move_number - IFNULL ( f . sumNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as task_type , ? as taskStatus , b . pending_move_number , IFNULL ( f . sumNumber , ? ) sumNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . origin_order_code , b . order_list_id , SUM ( CASE WHEN a . task_status &lt; ? then b . sku_number else IFNULL ( b . moved_number , ? ) + IFNULL ( b . abnormal_number , ? ) end ) sumNumber FROM ? a join t_stk_moving_task_detail b on b . moving_task_id = a . id and b . customer_id = a . customer_id WHERE task_type = ? AND task_status &lt; ? GROUP BY a . customer_id , a . origin_order_code , b . order_list_id ) f on f . origin_order_code = a . order_code and f . order_list_id = b . id AND f . customer_id = a . customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a . order_code = e . order_code AND a . customer_id = e . customer_id WHERE a . add_time &gt;= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . is_stock_order = ? and b . pending_move_number &gt; ? AND b . pending_move_number - IFNULL ( f . sumNumber , ? ) &gt; ? ; 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.pending_move_number- IFNULL(f.sumNumber, 0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 3 as task_type, 0 as taskStatus, b.pending_move_number, IFNULL(f.sumNumber, 0) sumNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.origin_order_code,b.order_list_id, SUM(CASE WHEN a.task_status &lt; 4 then b.sku_number else IFNULL(b.moved_number,0)+IFNULL(b.abnormal_number,0)end) sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type=3 AND task_status &lt; 5 GROUP BY a.customer_id,a.origin_order_code,b.order_list_id )f on f.origin_order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.is_stock_order = 1 and b.pending_move_number&gt;0 AND b.pending_move_number- IFNULL(f.sumNumber, 0) &gt; 0; 王凯,人工修改过 AISQL修改建议：扫描行数 150万 以上，未使用索引 explain表： 额外建议： 索引建议：
    002- 数据库：zhcxkj-center-warehouse
    003- 查询时间：42.6117
    004- 执行次数: 166
    005- 扫描行数：1533360
    006- 发送行数：621.807
    007- 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . pending_move_number - IFNULL ( f . sumNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as task_type , ? as taskStatus , b . pending_move_number , IFNULL ( f . sumNumber , ? ) sumNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . origin_order_code , b . order_list_id , SUM ( CASE WHEN a . task_status &lt; ? then b . sku_number else IFNULL ( b . moved_number , ? ) + IFNULL ( b . abnormal_number , ? ) end ) sumNumber FROM ? a join t_stk_moving_task_detail b on b . moving_task_id = a . id and b . customer_id = a . customer_id WHERE task_type = ? AND task_status &lt; ? GROUP BY a . customer_id , a . origin_order_code , b . order_list_id ) f on f . origin_order_code = a . order_code and f . order_list_id = b . id AND f . customer_id = a . customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a . order_code = e . order_code AND a . customer_id = e . customer_id WHERE a . add_time &gt;= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . is_stock_order = ? and b . pending_move_number &gt; ? AND b . pending_move_number - IFNULL ( f . sumNumber , ? ) &gt; ? ;
    008- 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.pending_move_number- IFNULL(f.sumNumber, 0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 3 as task_type, 0 as taskStatus, b.pending_move_number, IFNULL(f.sumNumber, 0) sumNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.origin_order_code,b.order_list_id, SUM(CASE WHEN a.task_status &lt; 4 then b.sku_number else IFNULL(b.moved_number,0)+IFNULL(b.abnormal_number,0)end) sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type=3 AND task_status &lt; 5 GROUP BY a.customer_id,a.origin_order_code,b.order_list_id )f on f.origin_order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.is_stock_order = 1 and b.pending_move_number&gt;0 AND b.pending_move_number- IFNULL(f.sumNumber, 0) &gt; 0;
    009- 王凯,人工修改过
    010- AISQL修改建议：扫描行数 150万 以上，未使用索引
    011- explain表：
    012- 额外建议：
    013- 索引建议：
    014-
    001- <br />
    001+ <br /> 数据库：zhcxkj-center-warehouse<br /> 查询时间：42.6117<br /> 执行次数: 166<br /> 扫描行数：1533360<br /> 发送行数：621.807<br /> 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . pending_move_number - IFNULL ( f . sumNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as task_type , ? as taskStatus , b . pending_move_number , IFNULL ( f . sumNumber , ? ) sumNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . origin_order_code , b . order_list_id , SUM ( CASE WHEN a . task_status &lt; ? then b . sku_number else IFNULL ( b . moved_number , ? ) + IFNULL ( b . abnormal_number , ? ) end ) sumNumber FROM ? a join t_stk_moving_task_detail b on b . moving_task_id = a . id and b . customer_id = a . customer_id WHERE task_type = ? AND task_status &lt; ? GROUP BY a . customer_id , a . origin_order_code , b . order_list_id ) f on f . origin_order_code = a . order_code and f . order_list_id = b . id AND f . customer_id = a . customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a . order_code = e . order_code AND a . customer_id = e . customer_id WHERE a . add_time &gt;= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . is_stock_order = ? and b . pending_move_number &gt; ? AND b . pending_move_number - IFNULL ( f . sumNumber , ? ) &gt; ? ;<br /> 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.pending_move_number- IFNULL(f.sumNumber, 0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 3 as task_type, 0 as taskStatus, b.pending_move_number, IFNULL(f.sumNumber, 0) sumNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.origin_order_code,b.order_list_id, SUM(CASE WHEN a.task_status &lt; 4 then b.sku_number else IFNULL(b.moved_number,0)+IFNULL(b.abnormal_number,0)end) sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type=3 AND task_status &lt; 5 GROUP BY a.customer_id,a.origin_order_code,b.order_list_id )f on f.origin_order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.is_stock_order = 1 and b.pending_move_number&gt;0 AND b.pending_move_number- IFNULL(f.sumNumber, 0) &gt; 0;<br /> 王凯,人工修改过<br /> AISQL修改建议：扫描行数 150万 以上，未使用索引<br /> explain表：<br /> 额外建议：<br /> 索引建议：<br /><br />
    002- 数据库：zhcxkj-center-warehouse<br />
    003- 查询时间：42.6117<br />
    004- 执行次数: 166<br />
    005- 扫描行数：1533360<br />
    006- 发送行数：621.807<br />
    007- 指纹SQL: SELECT b . order_code , a . warehouse_id , b . id AS order_list_id , b . sku , b . customer_id , ( b . product_num + IFNULL ( b . product_num_update , ? ) ) product_num , b . pending_move_number - IFNULL ( f . sumNumber , ? ) lock_num , d . order_source_type , a . amazon_shipment_id , IFNULL ( e . order_build_type , ? ) order_build_type , IFNULL ( e . is_sta_com_order , ? ) is_sta_com_order , IFNULL ( e . interface_type , ? ) interface_type , ? as task_type , ? as taskStatus , b . pending_move_number , IFNULL ( f . sumNumber , ? ) sumNumber FROM ? a JOIN t_stk_order_list b ON a . order_code = b . order_code AND a . customer_id = b . customer_id JOIN rb_order_source d ON a . order_source_id = d . id AND a . customer_id = d . customer_id LEFT JOIN ( SELECT a . customer_id , a . origin_order_code , b . order_list_id , SUM ( CASE WHEN a . task_status &lt; ? then b . sku_number else IFNULL ( b . moved_number , ? ) + IFNULL ( b . abnormal_number , ? ) end ) sumNumber FROM ? a join t_stk_moving_task_detail b on b . moving_task_id = a . id and b . customer_id = a . customer_id WHERE task_type = ? AND task_status &lt; ? GROUP BY a . customer_id , a . origin_order_code , b . order_list_id ) f on f . origin_order_code = a . order_code and f . order_list_id = b . id AND f . customer_id = a . customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a . order_code = e . order_code AND a . customer_id = e . customer_id WHERE a . add_time &gt;= DATE_ADD ( CURRENT_TIMESTAMP , INTERVAL ? MONTH ) AND a . is_stock_order = ? and b . pending_move_number &gt; ? AND b . pending_move_number - IFNULL ( f . sumNumber , ? ) &gt; ? ;<br />
    008- 真实SQL：SELECT b.order_code, a.warehouse_id, b.id AS order_list_id, b.sku, b.customer_id, (b.product_num + IFNULL(b.product_num_update,0)) product_num, b.pending_move_number- IFNULL(f.sumNumber, 0) lock_num, d.order_source_type, a.amazon_shipment_id, IFNULL(e.order_build_type, -1) order_build_type, IFNULL(e.is_sta_com_order, 0) is_sta_com_order, IFNULL(e.interface_type, 0) interface_type, 3 as task_type, 0 as taskStatus, b.pending_move_number, IFNULL(f.sumNumber, 0) sumNumber FROM t_stk_order_info a JOIN t_stk_order_list b ON a.order_code = b.order_code AND a.customer_id = b.customer_id JOIN rb_order_source d ON a.order_source_id = d.id AND a.customer_id = d.customer_id LEFT JOIN ( SELECT a.customer_id,a.origin_order_code,b.order_list_id, SUM(CASE WHEN a.task_status &lt; 4 then b.sku_number else IFNULL(b.moved_number,0)+IFNULL(b.abnormal_number,0)end) sumNumber from t_stk_moving_task a join t_stk_moving_task_detail b on b.moving_task_id=a.id and b.customer_id=a.customer_id WHERE task_type=3 AND task_status &lt; 5 GROUP BY a.customer_id,a.origin_order_code,b.order_list_id )f on f.origin_order_code=a.order_code and f.order_list_id=b.id AND f.customer_id=a.customer_id LEFT JOIN t_stk_order_info_beikuexpand e ON a.order_code = e.order_code AND a.customer_id = e.customer_id WHERE a.add_time &gt;= DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -12 MONTH) AND a.is_stock_order = 1 and b.pending_move_number&gt;0 AND b.pending_move_number- IFNULL(f.sumNumber, 0) &gt; 0;<br />
    009- 王凯,人工修改过<br />
    010- AISQL修改建议：扫描行数 150万 以上，未使用索引<br />
    011- explain表：<br />
    012- 额外建议：<br />
    013- 索引建议：<br />
    014- <br />
  5. 2025-09-10 09:22:50, 由 王凯 编辑。
    修改了 上线时间，旧值为 "0000-00-00 00:00:00"，新值为 "2025-09-10 09:22"。
    修改了 上线时效，旧值为 "-1"，新值为 "22.51"。
  6. 2025-09-10 09:22:52, 由 王凯 关闭。
    修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
    修改了 指派给 ，旧值为 "wangkai"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "wangkai"。
    修改了 关闭日期，旧值为 ""，新值为 "2025-09-10 09:22:52"。
  7. 2025-09-10 16:15:05, 由 周杰 编辑。
    修改了 上线时间，旧值为 "2025-09-10 09:22:00"，新值为 "2025-09-09 11:00"。
    修改了 上线时效，旧值为 "22.51"，新值为 "0.15"。
  8. 2025-09-10 18:32:33, 由 程伟 添加备注。
    搞错了，应该是点非BUG，性能很快，1.37秒出结果，不小心点错了
  9. 2025-09-10 18:32:42, 由 程伟 编辑。
    修改了 解决方案，旧值为 "notrepro2"，新值为 "feibug"。
  10. 2025-09-12 15:32:11, 由 陈莉珍 编辑。
    修改了 测试责任人，旧值为 "closed"，新值为 "wangkai"。
所属项目 技术中心-需求池
所属模块 供应链   仓储物流   仓储
所属计划
所属迭代
Bug类型
严重程度 一般
优先级 P2
Bug状态 已关闭
业务部门 总经办
影响功能
开发责任人 程伟
测试责任人 王凯
解决时效 0.15
激活次数 0
激活日期
是否确认 是
当前指派 Closed 于 2025-09-12 15:32:11
截止日期 0000-00-00
上线时间 2025-09-09 11:00:00
上线时效 0.15
操作系统
浏览器
关键词
抄送给
所属迭代
相关需求
相关任务
由谁创建 周杰 于 2025-09-09 10:51:09
影响环境 生产环境-业务反馈
由谁解决 程伟 于 2025-09-09 11:00:09
解决版本 主干
解决方案 非BUG-纯属误判
由谁关闭 王凯 于 2025-09-10 09:22:52
最后修改 陈莉珍 于 2025-09-12 15:32:11
相关Bug