数据库：zhcxkj-center-warehouse
查询时间：14.6539
扫描行数：1554660
发送行数：1
指纹SQL: SELECT count ( * ) FROM ? pt left join t_stk_mark_record b on pt . box_no = b . mark_no WHERE pt . customer_id = ? AND pt . sorting_date BETWEEN ? AND ? ;
真实SQL：SELECT count(*) FROM t_stk_moving_putin_task pt left join t_stk_mark_record b on pt.box_no=b.mark_no WHERE pt.customer_id = 1 AND pt.sorting_date BETWEEN 2025-07-07 00:00:00 AND 2025-08-06 23:59:59;
无人工修改
AISQL修改建议：SELECT COUNT(b.id)
FROM t_stk_moving_putin_task pt
LEFT JOIN t_stk_mark_record b ON pt.box_no = b.mark_no AND b.id IS NOT NULL
WHERE pt.customer_id = 1
AND pt.sorting_date >= 2025-07-07 00:00:00
AND pt.sorting_date <= 2025-08-06 23:59:59
AND EXISTS (
SELECT 1 FROM t_stk_moving_putin_task
WHERE customer_id = pt.customer_id
AND sorting_date BETWEEN 2025-07-07 00:00:00 AND 2025-08-06 23:59:59
AND box_no = pt.box_no
);
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | pt | None | ref | idx_uniq_boxNo,idx_customer_id_task_state,idx_customer_id_sorting_date | idx_uniq_boxNo | 9 | const | 586747 | 11.11 | Using where |
| 1 | SIMPLE | b | None | ref | idx_mark_no_order_code,idx_mark_no | idx_mark_no_order_code | 259 | zhcxkj-center-warehouse.pt.box_no | 1 | 100 | Using index |
额外建议：
索引建议：
取出表 【t_stk_moving_putin_task】 where条件表达式 【customer_id = 1】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
→ 【t_stk_moving_putin_task】表 【sorting_date】字段，索引已经存在，无需添加任何索引。
【t_stk_moving_putin_task】表 【sorting_date】字段，索引分析：
+-------------------------+------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+=========================+==============================+===============+===============+
| t_stk_moving_putin_task | idx_customer_id_sorting_date | sorting_date | 1173495 |
+-------------------------+------------------------------+---------------+---------------+
→ 【t_stk_mark_record】 表，无需添加任何索引。