1.问题分析
华为云慢sql top 10，查询时间：98秒
2.解决方案
索引优化建议：
- 建议在数据表 irobotbox_marketpublish.pp_replenishment 的 StrategyId,MarketId,BusinessType,CustomerId,Time 列上添加如下索引，以提高查询性能：
ALTER TABLE `irobotbox_marketpublish`.`pp_replenishment` ADD INDEX `idx_StrategyId_MarketId_BusinessType_CustomerId_Time` (`StrategyId`, `MarketId`, `BusinessType`, `CustomerId`, `Time`)
注意：在业务高峰期，变更表结构存在风险，请谨慎操作！在业务低峰下班后操作
3.慢sql
SELECT A55_T_1_.`补货情况` AS T_A26_2_, SUM(A55_T_1_.`执行数量`) AS T_AE3_3_ FROM (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 = 70 AND StrategyId IN (180) AND Time >= DATE_SUB(NOW(), INTERVAL 72 HOUR) 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) AND w.LastModificationTime <= DATE_ADD(NOW(), INTERVAL -10 HOUR) AND w.LastModificationTime <= r.lastReplenishmentTime ) a GROUP BY CASE WHEN a.补货执行时间差 > 1 THEN '补货执行异常' WHEN a.Status = 'normal' THEN '补货执行异常' ELSE '补货执行正常' END ) A55_T_1_ GROUP BY A55_T_1_.`补货情况` ORDER BY T_AE3_3_ DESC LIMIT 0, 1000
