智能刊登gaussdb 慢sql top 10
91.900 秒
2.解决方案
索引优化建议：
- 建议在数据表 irobotbox_marketpublish.Ap_AutoPublish_Allegro_Queue 的 StrategyId,CustomerId,MarketId,Date,Status 列上添加如下索引，以提高查询性能：
ALTER TABLE `irobotbox_marketpublish`.`Ap_AutoPublish_Allegro_Queue` ADD INDEX `idx_StrategyId_CustomerId_MarketId_Date_Status` (`StrategyId`, `CustomerId`, `MarketId`, `Date`, `Status`)
语句优化建议：
- 发现IN表达式包含元素超过10，请慎用，因为元素过多会导致全表扫描。
3.原sql
SELECT CustomerId, MarketId, StrategyId, COUNT(1) AS LastTotals, SUM(CASE `Status` WHEN 'success' THEN 1 ELSE 0 END) AS LastSuccesses
, SUM(CASE `Status` WHEN 'failure' THEN 1 ELSE 0 END) AS LastFails, SUM(CASE WHEN Date = 20220801
AND Status = 'pending' THEN 1 ELSE 0 END) AS LastPendings, SUM(CASE WHEN Date = 20220801
AND Status = 'normal' THEN 1 ELSE 0 END) AS LastNormals
FROM Ap_AutoPublish_Allegro_Queue
WHERE CustomerId = 1
AND MarketId = 67
AND StrategyId IN (48622, 48621, 48591, 48577, 48566, 48563, 48554, 48547, 47878, 47553, 47171, 47056, 47032, 46610, 46542, 46541, 46432, 46278, 46241, 46062)
AND Date BETWEEN 20220725 AND 20220801
GROUP BY CustomerId, MarketId, StrategyId
1. idx_StrategyId_CustomerId_MarketId_Date_Status 索引我已添加；
2. 有空把In 查询的优化一下