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

1.问题分析

分析慢sql发现缺少索引

2.解决方案

在业务低峰创建索引

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

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

3.慢sql

SELECT A87_T_1_.`RuleName` AS T_A6C_2_, SUM(A87_T_1_.`近7天刊登成功数`) AS T_AE9_3_, SUM(A87_T_1_.`近7天销量`) AS T_A7C_4_
FROM (SELECT a.RuleId, CASE WHEN a.RuleId = 0 THEN '旧标题库' ELSE c.RuleName END AS RuleName, COUNT(1) AS '近7天刊登成功数', SUM(b.Sales) AS '近7天销量'
	FROM ap_autopublish_shopeev2_queue a
		LEFT JOIN m_shopeev2_sale_day_summary b ON a.CustomerId = b.CustomerId
			AND a.MarketId = b.MarketId
			AND a.SellerSku = b.SellerSku
		LEFT JOIN sys_title_assemble_rules c ON a.MarketId = c.MarketId
			AND a.RuleId = c.Id
	WHERE a.CustomerId = 1
		AND a.MarketId = 97
		AND a.`Status` = 'success'
		AND a.Date >= 20230201
		AND a.IsAgainPublish = 0
		AND a.Date >= date_add(curdate(), INTERVAL -8 DAY)
		AND a.Date <= date_add(curdate(), INTERVAL -1 DAY)
	GROUP BY a.RuleId
	) A87_T_1_
GROUP BY A87_T_1_.`RuleName`
ORDER BY T_A7C_4_ DESC
LIMIT 0, 1000