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

1.问题分析

   慢sql top 10,缺少索引，执行需要15分钟

2.解决方案： 在晚上业务使用量少的时候创建 
 

1. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_ebay_queue 的 StrategyType,Status,CustomerId,MarketId 列上添加如下索引，以提高查询性能：
   ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_ebay_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_MarketId` (`StrategyType`, `Status`, `CustomerId`, `MarketId`)

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

SELECT DATE_FORMAT(date_add(a.LastModificationTime, INTERVAL 8 HOUR), '%Y-%m-%d') AS 日期, a.OrderSourceId, COUNT(CASE WHEN a.STATUS = 'success' THEN a.Id ELSE NULL END) AS '上架成功数量', NULL AS 下架成功数量
FROM ap_autopublish_ebay_queue a
WHERE a.CustomerId = 1
	AND date_add(a.LastModificationTime, INTERVAL 8 HOUR) >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
	AND date_add(a.LastModificationTime, INTERVAL 8 HOUR) < CURDATE()
GROUP BY a.OrderSourceId, DATE_FORMAT(date_add(a.LastModificationTime, INTERVAL 8 HOUR), '%Y-%m-%d')
UNION ALL
SELECT a.`日期`, a.ordersourceid, NULL AS 上架成功数量, COUNT(a.CustomerId) AS 下架成功数量
FROM (SELECT DATE_FORMAT(date_add(LastModificationTime, INTERVAL 8 HOUR), '%Y-%m-%d') AS 日期, CustomerId, MarketId, aa.OrderSourceId, StrategyType
		, `Status`, FailureReason
	FROM pp_phoenixplan_ebay_queue aa
	WHERE aa.CustomerId = 1
		AND aa.MarketId = 2
		AND date_add(aa.LastModificationTime, INTERVAL 8 HOUR) >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
		AND date_add(aa.LastModificationTime, INTERVAL 8 HOUR) < CURDATE()
		AND aa.StrategyType = 3
		AND aa.`Status` = 'success'
	) a
GROUP BY a.`日期`, a.ordersourceid
UNION ALL
SELECT a.`日期`, a.ordersourceid, NULL AS 上架成功数量, COUNT(a.customerid) AS 下架成功数量
FROM (SELECT DATE_FORMAT(date_add(aa.LastModificationTime, INTERVAL 8 HOUR), '%Y-%m-%d') AS 日期, CustomerId, MarketId, aa.OrderSourceId, aa.RePublishSellerSku
		, StrategyType, `Status`, FailureReason
	FROM pp_phoenixplan_ebay_queue aa
	WHERE aa.CustomerId = 1
		AND aa.MarketId = 2
		AND date_add(aa.LastModificationTime, INTERVAL 8 HOUR) >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
		AND date_add(aa.LastModificationTime, INTERVAL 8 HOUR) < CURDATE()
		AND aa.StrategyType = 2
		AND aa.`Status` = 'success'
	) a
GROUP BY a.`日期`, a.ordersourceid