数据库：zhcxkj-center-warehouse
查询时间：37.846
执行次数: 28
扫描行数：1465570
发送行数：1348.89
指纹SQL: select id , customer_id , card_no , box_no , sku , order_code , list_no , list_time , list_num , bad_num , task_state , channel , sorting_user_id , sorting_user_name , sorting_date , receive_user_id , receive_user_name , shelf_num , shelf_time , shelf_user_id , shelf_user_name , receive_time , recommend_position_id , shelf_position_id , shelf_type , refer_no , package_id , move_out_box_no , first_shelf_time , is_need_add_stockpile_data , pause_tag , return_good_task_no , order_list_id , ware_house_id , abnormal_number , operate_status , tally_admin_id , tally_user_name , tally_time , two_tally_admin_id , two_tally_user_name , two_tally_time , now ( ) StarRocksUpdateTime FROM ? where ( customer_id = ? AND ( sorting_date >= DATE_SUB ( NOW ( ) , interval ? HOUR ) OR shelf_time >= DATE_SUB ( NOW ( ) , interval ? HOUR ) ) ) ;
真实SQL：select id,customer_id,card_no,box_no,sku,order_code,list_no,list_time,list_num,bad_num,task_state,channel,sorting_user_id,sorting_user_name,sorting_date,receive_user_id,receive_user_name,shelf_num,shelf_time,shelf_user_id,shelf_user_name,receive_time,recommend_position_id,shelf_position_id,shelf_type,refer_no,package_id,move_out_box_no,first_shelf_time,is_need_add_stockpile_data,pause_tag,return_good_task_no,order_list_id,ware_house_id,abnormal_number,operate_status,tally_admin_id,tally_user_name,tally_time,two_tally_admin_id,two_tally_user_name,two_tally_time,now() StarRocksUpdateTime from t_stk_moving_putin_task where (customer_id = 1 AND (sorting_date >= DATE_SUB(NOW(), interval 2 HOUR) OR shelf_time >= DATE_SUB(NOW(), interval 2 HOUR)));
无人工修改
AISQL修改建议：SELECT id,customer_id,card_no,box_no,sku,order_code,list_no,list_time,list_num,bad_num,task_state,channel,sorting_user_id,sorting_user_name,sorting_date,receive_user_id,receive_user_name,shelf_num,shelf_time,shelf_user_id,shelf_user_name,receive_time,recommend_position_id,shelf_position_id,shelf_type,refer_no,package_id,move_out_box_no,first_shelf_time,is_need_add_stockpile_data,pause_tag,return_good_task_no,order_list_id,ware_house_id,abnormal_number,operate_status,tally_admin_id,tally_user_name,tally_time,two_tally_admin_id,two_tally_user_name,two_tally_time,now() StarRocksUpdateTime
FROM t_stk_moving_putin_task
WHERE customer_id = 1
AND (sorting_date >= DATE_SUB(NOW(), INTERVAL 2 HOUR) OR shelf_time >= DATE_SUB(NOW(), INTERVAL 2 HOUR))
ORDER BY GREATEST(IFNULL(sorting_date,0), IFNULL(shelf_time,0)) DESC
LIMIT 1000;
建议添加：
1. 复合索引 (customer_id, sorting_date, shelf_time)
2. 时间字段降序排序并限制结果集大小
3. 使用GREATEST函数避免多次计算DATE_SUB
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_stk_moving_putin_task | None | ref | idx_uniq_boxNo,idx_customer_id_task_state,idx_shelf_time_task_state,idx_customer_id_sorting_date,idx_customerid_box_no_sorting_date,idx_customerid_shelftime | idx_uniq_boxNo | 9 | const | 700401 | 55.55 | Using where |
额外建议：
索引建议：
取出表 【t_stk_moving_putin_task】 where条件字段 【customer_id】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
建议添加索引：ALTER TABLE t_stk_moving_putin_task ADD INDEX idx_sorting_date_shelf_time(sorting_date,shelf_time);
【t_stk_moving_putin_task】表 【sorting_date,shelf_time】字段，索引分析：
+-------------------------+--------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+=========================+================================+===============+===============+
| t_stk_moving_putin_task | idx_customer_id_sorting_date | sorting_date | 1326066 |
+-------------------------+--------------------------------+---------------+---------------+
| t_stk_moving_putin_task | idx_customerid_box_no_sorting_ | sorting_date | 1400802 |
| | date | | |
+-------------------------+--------------------------------+---------------+---------------+
| t_stk_moving_putin_task | idx_customerid_shelftime | shelf_time | 1347981 |
+-------------------------+--------------------------------+---------------+---------------+
| t_stk_moving_putin_task | idx_shelf_time_task_state | shelf_time | 1380454 |
+-------------------------+--------------------------------+---------------+---------------+
select id,customer_id,card_no,box_no,sku,order_code,list_no,list_time,list_num,bad_num,task_state,channel,sorting_user_id,sorting_user_name,sorting_date,receive_user_id,receive_user_name,shelf_num,shelf_time,shelf_user_id,shelf_user_name,receive_time,recommend_position_id,shelf_position_id,shelf_type,refer_no,package_id,move_out_box_no,first_shelf_time,is_need_add_stockpile_data,pause_tag,return_good_task_no,order_list_id,ware_house_id,abnormal_number,operate_status,tally_admin_id,tally_user_name,tally_time,two_tally_admin_id,two_tally_user_name,two_tally_time,now() StarRocksUpdateTime from t_stk_moving_putin_task where (customer_id = 1 AND (sorting_date >= DATE_SUB(NOW(), interval 2 HOUR) ));
union
select id,customer_id,card_no,box_no,sku,order_code,list_no,list_time,list_num,bad_num,task_state,channel,sorting_user_id,sorting_user_name,sorting_date,receive_user_id,receive_user_name,shelf_num,shelf_time,shelf_user_id,shelf_user_name,receive_time,recommend_position_id,shelf_position_id,shelf_type,refer_no,package_id,move_out_box_no,first_shelf_time,is_need_add_stockpile_data,pause_tag,return_good_task_no,order_list_id,ware_house_id,abnormal_number,operate_status,tally_admin_id,tally_user_name,tally_time,two_tally_admin_id,two_tally_user_name,two_tally_time,now() StarRocksUpdateTime from t_stk_moving_putin_task where (customer_id = 1 AND (shelf_time >= DATE_SUB(NOW(), interval 2 HOUR)));