数据库：zhcxkj-center-warehouse
查询时间：11.8669
执行次数: 164
扫描行数：4665790
发送行数：1988.02
指纹SQL: SELECT a . id , CONCAT ( ? , a . order_code ) as order_code , a . sku , a . group_sku , a . fnsku , a . prodct_namecn , a . prodct_nameen , a . add_time , a . product_num + a . product_num_update as needNum , b . order_state_name , b . warehouse_name , IFNULL ( d . buy_put_in_num , ? ) + IFNULL ( d . other_put_in_num , ? ) AS putInNum , a . product_assigned_num , case when a . purchase_type = ? and IFNULL ( a . group_sku , ? ) != ? THEN a . group_product_num + a . group_product_num_update when a . purchase_type = ? and IFNULL ( a . group_sku , ? ) = ? THEN a . product_num + a . product_num_update end as shipmentNum , case when be . order_build_type = ? THEN ? when be . order_build_type = ? THEN ? end as orderbuildTypeName FROM ? a LEFT JOIN t_stk_order_info b ON a . order_code = b . order_code AND a . customer_id = b . customer_id LEFT JOIN t_stk_order_info_beikuexpand be ON b . order_code = be . order_code AND b . customer_id = be . customer_id LEFT JOIN t_stk_stockpile_order_list d ON a . id = d . order_list_id where ? = ? and b . order_state = ? and a . add_time >= ? and ? >= a . add_time order by a . id desc limit ? , ? ;
真实SQL：SELECT a.id, CONCAT(,a.order_code) as order_code, a.sku, a.group_sku, a.fnsku,a.prodct_namecn, a.prodct_nameen, a.add_time, a.product_num+a.product_num_update as needNum, b.order_state_name, b.warehouse_name, IFNULL( d.buy_put_in_num, 0 )+ IFNULL( d.other_put_in_num, 0 ) AS putInNum, a.product_assigned_num, case when a.purchase_type=4 and IFNULL(a.group_sku,)!= THEN a.group_product_num+a.group_product_num_update when a.purchase_type=4 and IFNULL(a.group_sku,)= THEN a.product_num+a.product_num_update end as shipmentNum, case when be.order_build_type=1 THEN FBA备库订单 when be.order_build_type=3 THEN 海外仓备库订单 end as orderbuildTypeName FROM t_stk_order_list a LEFT JOIN t_stk_order_info b ON a.order_code = b.order_code AND a.customer_id = b.customer_id LEFT JOIN t_stk_order_info_beikuexpand be ON b.order_code = be.order_code AND b.customer_id = be.customer_id LEFT JOIN t_stk_stockpile_order_list d ON a.id = d.order_list_id where 1=1 and b.order_state= 2 and a.add_time>= 2025-07-01 00:00:00 and 2025-08-28 23:59:59 >= a.add_time order by a.id desc limit 60000,2000;
无人工修改
AISQL修改建议：以下是优化后的SQL语句：
SELECT a.id,
CONCAT(, a.order_code) AS order_code,
a.sku,
a.group_sku,
a.fnsku,
a.prodct_namecn,
a.prodct_nameen,
a.add_time,
a.product_num+a.product_num_update AS needNum,
b.order_state_name,
b.warehouse_name,
IFNULL(d.buy_put_in_num, 0)+ IFNULL(d.other_put_in_num, 0) AS putInNum,
a.product_assigned_num,
IF(a.purchase_type=4 AND IFNULL(a.group_sku, )!=, a.group_product_num+a.group_product_num_update,
IF(a.purchase_type=4, a.product_num+a.product_num_update, NULL)) AS shipmentNum,
CASE be.order_build_type
WHEN 1 THEN FBA备库订单
WHEN 3 THEN 海外仓备库订单
END AS orderbuildTypeName
FROM t_stk_order_list a
INNER JOIN t_stk_order_info b ON a.order_code = b.order_code AND a.customer_id = b.customer_id AND b.order_state = 2
LEFT JOIN t_stk_order_info_beikuexpand be ON b.order_code = be.order_code AND b.customer_id = be.customer_id
LEFT JOIN t_stk_stockpile_order_list d ON a.id = d.order_list_id
WHERE a.add_time BETWEEN 2025-07-01 00:00:00 AND 2025-08-28 23:59:59
ORDER BY a.id DESC
LIMIT 60000, 2000;
优化点：
1. 将 LEFT JOIN t_stk_order_info 改为 INNER JOIN 并添加 b.order_state = 2 到 JOIN 条件中
2. 简化 WHERE 条件，使用 BETWEEN 替代原来的范围判断
3. 用 IF 函数替代复杂的 CASE WHEN 结构
4. 移除了冗余的 1=1 条件
5. 简化了第二个 CASE WHEN 表达式
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | a | None | ALL | idx_order_code,idx_customerid_updatetime | None | None | None | 2194920 | 11.11 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | None | ref | idx_order_code,idx_order_code_,idx_ordercode_customerid_isstockorder_addtime,idx_ordercode_ordersourceid,idx_optimize_query,ordercode_customerid_orderstatus_orderstate_isstockorder_addtime,idx_customer_order_status,idx_customer_order_code | idx_order_code | 202 | zhcxkj-center-warehouse.a.order_code | 1 | 10 | Using where |
| 1 | SIMPLE | be | None | eq_ref | PK_RB_OrderInfo_BeikuExpand_1,idx_order_code_fission_origin_order | PK_RB_OrderInfo_BeikuExpand_1 | 206 | zhcxkj-center-warehouse.a.customer_id,zhcxkj-center-warehouse.a.order_code | 1 | 100 | None |
| 1 | SIMPLE | d | None | ALL | None | None | None | None | 1485421 | 100 | Using where; Using join buffer (hash join) |
额外建议：
索引建议：join联表查询，on关联字段必须增加索引！
需要添加索引：ALTER TABLE t_stk_stockpile_order_list ADD INDEX idx_order_list_id(order_list_id);
【t_stk_stockpile_order_list】表 【order_list_id】字段，索引分析：
+--------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+==============+==============+===============+===============+
+--------------+--------------+---------------+---------------+
建议添加索引：ALTER TABLE t_stk_order_list ADD INDEX idx_add_time_id(add_time,id);
【t_stk_order_list】表 【add_time,id】字段，索引分析：
+------------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+==================+==============+===============+===============+
| t_stk_order_list | PRIMARY | id | 2076302 |
+------------------+--------------+---------------+---------------+
取出表 【t_stk_order_info】 where条件表达式 【order_state= 2】 100000 条记录，重复的数据有：【61489】 条，没有必要为该字段创建索引。
→ 【t_stk_order_info】 表，无需添加任何索引。
→ 【t_stk_order_info_beikuexpand】 表，无需添加任何索引。
→ 【t_stk_stockpile_order_list】 表，无需添加任何索引。
