数据库：zhcxkj-center-warehouse
查询时间：17.2373
执行次数: 71
扫描行数：31355.3
发送行数：1
指纹SQL: SELECT COUNT ( * ) FROM ( SELECT DISTINCT a . * , b . ware_house_name AS warehouseName , COALESCE ( c . merger_package_no , COALESCE ( e . origin_order_code , e . order_code ) ) AS order_code , d . add_time AS picking_time , d . add_user_name AS picking_person_name , d . area_task_number , d . collection_user_name , d . stock_sorting_user_name , d . stock_sorting_time , '?' packaging_person_name , c . packing_time FROM ? AS a LEFT JOIN rb_ware_house AS b ON a . warehouse_id = b . id AND a . customer_id = b . customer_id LEFT JOIN t_stk_procurement_order_package AS c ON a . package_id = c . id AND a . customer_id = c . customer_id LEFT JOIN t_stk_package_picking_package_child_warehouse_area x ON a . package_id = x . package_id AND a . customer_id = x . customer_id LEFT JOIN t_stk_package_picking_child_warehouse_area_turnover_box AS d ON a . print_batch_no = d . print_batch_no AND FIND_IN_SET ( d . turnover_box_no , a . turnover_box_no ) AND x . area_task_number COLLATE utf8mb4_unicode_ci = d . area_task_number LEFT JOIN t_stk_moving_task AS e ON a . moving_task_no = e . task_no AND a . customer_id = e . customer_id WHERE a . customer_id = ? AND a . add_time >= '?' AND a . add_time <= '?' ) TOTAL ;
真实SQL：SELECT COUNT(*) FROM (SELECT DISTINCT a.*, b.ware_house_name AS warehouseName, COALESCE(c.merger_package_no, COALESCE(e.origin_order_code, e.order_code)) AS order_code, d.add_time AS picking_time, d.add_user_name AS picking_person_name, d.area_task_number, d.collection_user_name, d.stock_sorting_user_name, d.stock_sorting_time, '' packaging_person_name, c.packing_time FROM t_stk_package_stock_sorting_unusual AS a LEFT JOIN rb_ware_house AS b ON a.warehouse_id = b.id AND a.customer_id = b.customer_id LEFT JOIN t_stk_procurement_order_package AS c ON a.package_id = c.id AND a.customer_id = c.customer_id LEFT JOIN t_stk_package_picking_package_child_warehouse_area x ON a.package_id = x.package_id AND a.customer_id = x.customer_id LEFT JOIN t_stk_package_picking_child_warehouse_area_turnover_box AS d ON a.print_batch_no = d.print_batch_no AND FIND_IN_SET(d.turnover_box_no, a.turnover_box_no) AND x.area_task_number COLLATE utf8mb4_unicode_ci = d.area_task_number LEFT JOIN t_stk_moving_task AS e ON a.moving_task_no = e.task_no AND a.customer_id = e.customer_id WHERE a.customer_id = 1 AND a.add_time >= '2025-07-13 00:00:00' AND a.add_time <= '2025-08-12 23:59:59') TOTAL;
##无人工修改##
AISQL修改建议：优化后的SQL语句：
SELECT COUNT(DISTINCT CONCAT(a.id, IFNULL(a.package_id, ''), IFNULL(a.moving_task_no, '')))
FROM t_stk_package_stock_sorting_unusual AS a
LEFT JOIN rb_ware_house AS b ON a.warehouse_id = b.id AND a.customer_id = b.customer_id
LEFT JOIN t_stk_procurement_order_package AS c ON a.package_id = c.id AND a.customer_id = c.customer_id
LEFT JOIN t_stk_moving_task AS e ON a.moving_task_no = e.task_no AND a.customer_id = e.customer_id
WHERE a.customer_id = 1
AND a.add_time BETWEEN '2025-07-13 00:00:00' AND '2025-08-12 23:59:59';
优化说明：
1. 移除了子查询，直接在外部查询计算COUNT
2. 使用COUNT(DISTINCT)替代DISTINCT+COUNT(*)，避免全表扫描
3. 移除了不必要的JOIN表(t_stk_package_picking_package_child_warehouse_area和t_stk_package_picking_child_warehouse_area_turnover_box)
4. 使用BETWEEN替代>=和<=组合
5. 通过CONCAT关键字段确保DISTINCT的正确性同时提升性能
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | None | ALL | None | None | None | None | 441544 | 100 | None |
| 2 | DERIVED | a | None | ref | idx_warehouseid_packageid_printbatchno,idx_add_time,idx_customerid_addtime | idx_warehouseid_packageid_printbatchno | 4 | const | 52869 | 50 | Using where; Using temporary |
| 2 | DERIVED | b | None | eq_ref | PRIMARY,uidx_rb_ware_house | PRIMARY | 4 | zhcxkj-center-warehouse.a.warehouse_id | 1 | 100 | Using where |
| 2 | DERIVED | c | None | eq_ref | PRIMARY,idx_id | PRIMARY | 8 | zhcxkj-center-warehouse.a.package_id | 1 | 100 | Using where |
| 2 | DERIVED | x | None | ref | idx_package_id,idx_customerid_updatetime,idx_packageid_customerid | idx_package_id | 5 | zhcxkj-center-warehouse.a.package_id | 10 | 100 | Using where; Using index |
| 2 | DERIVED | d | None | ref | idx_printbatchno_turnoverboxno | idx_printbatchno_turnoverboxno | 2042 | zhcxkj-center-warehouse.a.print_batch_no | 1 | 100 | Using where |
| 2 | DERIVED | e | None | ref | idx_task_no | idx_task_no | 206 | zhcxkj-center-warehouse.a.moving_task_no,const | 1 | 100 | None |
额外建议：
索引建议：
取出表 【t_stk_package_stock_sorting_unusual】 where条件字段 【customer_id】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
→ 【t_stk_package_stock_sorting_unusual】表 【add_time】字段，索引已经存在，无需添加任何索引。
【t_stk_package_stock_sorting_unusual】表 【add_time】字段，索引分析：
+--------------------------------+------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+================================+========================+===============+===============+
| t_stk_package_stock_sorting_un | idx_add_time | add_time | 66190 |
| usual | | | |
+--------------------------------+------------------------+---------------+---------------+
| t_stk_package_stock_sorting_un | idx_customerid_addtime | add_time | 67425 |
| usual | | | |
+--------------------------------+------------------------+---------------+---------------+
→ 【rb_ware_house】 表，无需添加任何索引。
→ 【t_stk_procurement_order_package】 表，无需添加任何索引。
→ 【t_stk_package_picking_package_child_warehouse_area】 表，无需添加任何索引。
→ 【t_stk_package_picking_child_warehouse_area_turnover_box】 表，无需添加任何索引。
→ 【t_stk_moving_task】 表，无需添加任何索引。
问题说明：
单独的sql优化已经没有优化的余地了，只能根据搜索条件来过滤出指定的数据来过滤
优化：
1、根据周转箱号、周转时间、拣货人、分拣人搜索的时候，先查出对应的包裹ID数据，然后再使用包裹ID的来查询
2、其他优化