深圳智汇创想科技有限公司

1.问题分析

BI自动补货慢sql,5分钟自动刷新页面统计自动补货异常，

2.解决方案

分析发现慢sql 缺少索引：在业务低峰时添加一下索引

1. 建议在数据表 irobotbox_marketpublish.pp_replenishment 的 StrategyId,MarketId,BusinessType,CustomerId,OrderSourceId,ProductId,SellerSku,Sku 列上添加如下索引，以提高查询性能：
   ALTER TABLE `irobotbox_marketpublish`.`pp_replenishment` ADD INDEX `idx_StrategyId_MarketId_BusinessType_CustomerId_OrderSourceId_ProductId_SellerSku_Sku` (`StrategyId`, `MarketId`, `BusinessType`, `CustomerId`, `OrderSourceId`, `ProductId`, `SellerSku`, `Sku`)

注意：在业务高峰期，变更表结构存在风险，请谨慎操作！

3.慢sql

SELECT A37_T_1_.`补货情况` AS T_AE8_2_, SUM(A37_T_1_.`执行数量`) AS T_AC9_3_ FROM (-- shopee本地仓动态库存最新补货时间超过本地仓动态库存最近同步时间1小时的数据(范围48小时)
SELECT
CASE 
WHEN a.补货执行时间差>1 THEN '补货执行异常'
WHEN a.Status='normal' THEN '补货执行异常'
else '补货执行正常'
end as '补货情况',
count(a.sku) '执行数量'
FROM (
SELECT
  r.StrategyId,
  r.OrderSourceId,
  r.ProductId,
  r.SellerSku,
  r.Sku,
  r.`Status`,
  r.Inventory AS 修改后可售数,
  r.OriginalInventory AS 原可售数,
  w.DynamicInventory AS SKU最新动态库存合计,
  r.Remark AS 补货队列日志,
  r.FailureReason,
  date_add( w.LastModificationTime, INTERVAL 8 HOUR ) AS 动态库存最新更新时间,
  date_add( r.lastReplenishmentTime , INTERVAL 8 HOUR ) AS 补货队列最新执行时间,
  TIMESTAMPDIFF(hour, w.LastModificationTime, r.lastReplenishmentTime) AS 补货执行时间差
FROM
  (SELECT CustomerId,Sku,Sum(AvailableNum) as DynamicInventory,LastModificationTime FROM p_product_warehouse_dynamicinventory GROUP BY CustomerId,Sku)  w
  JOIN (
SELECT 
r.CustomerId,
r.StrategyId,
r.OrderSourceId,
r.ProductId,
r.SellerSku,
r.OriginalInventory,
r.Inventory ,
r.Remark,
r.FailureReason,
r.Sku,
r.`Status`,
r.LastModificationTime as lastReplenishmentTime 
FROM pp_replenishment as r
JOIN (
  SELECT
  Max(Id) as Id
  FROM
    pp_replenishment 
  WHERE
    CustomerId = 1 
    AND MarketId = 97 
  AND StrategyId IN (328,320,351)-- 设置补货策略
--  AND StrategyId IN (SELECT ID FROM pp_replenishment_strategy WHERE CustomerId=1 AND MarketId=97 AND IsEnabled=1 AND IsDeleted=0)
    AND Time>= DATE_SUB(NOW(),interval 72 HOUR)-- 可以设置成比当前查询时间差3天
AND BusinessType =1 -- 只统计动态库存变更添加到队列的数据
  GROUP BY
    CustomerId,
    StrategyId, 
    OrderSourceId,
    ProductId,
    SellerSku,
    Sku) p
  ON r.Id = p.Id
) r ON w.CustomerId = r.CustomerId 
  AND w.Sku = r.Sku 
WHERE
  w.LastModificationTime >= DATE_ADD(NOW(),INTERVAL -16 HOUR) -- 设置近6小时的数据
  and w.LastModificationTime <= DATE_ADD(NOW(),INTERVAL -10 HOUR)-- 设置比当前时间晚2个小时
  -- AND DATE_ADD( w.LastModificationTime, INTERVAL 2 HOUR ) < r.lastReplenishmentTime -- 最新补货时间超过最近动态库存同步时间2小时
  AND w.LastModificationTime <= r.lastReplenishmentTime
) as a
GROUP BY
CASE 
WHEN a.补货执行时间差>1 THEN '补货执行异常'
WHEN a.Status='normal' THEN '补货执行异常'
end
) AS A37_T_1_  GROUP BY A37_T_1_.`补货情况`  ORDER BY T_AC9_3_ DESC LIMIT 0, 1000;