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

1.问题分析

   慢sql top 10,缺少索引


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

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

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

SELECT '产品状态' AS 属性, CASE a.State WHEN 0 THEN '正常' WHEN 1 THEN '仅批量' WHEN 2 THEN '停产' WHEN 3 THEN '锁定' WHEN 4 THEN '暂时缺货' WHEN 5 THEN '清库' END AS 值, COUNT(a.sku) AS sku数量
FROM irobotbox_marketpublish.p_product a
WHERE a.customerid = 1
	AND a.FinalJudgmentStatus = 1
GROUP BY CASE a.State WHEN 0 THEN '正常' WHEN 1 THEN '仅批量' WHEN 2 THEN '停产' WHEN 3 THEN '锁定' WHEN 4 THEN '暂时缺货' WHEN 5 THEN '清库' END
UNION ALL
SELECT '是否侵权仿牌' AS 属性, CASE a.RiskInfringement WHEN 2 THEN '是' ELSE '否' END AS 值, COUNT(a.sku) AS sku数量
FROM irobotbox_marketpublish.p_product a
WHERE a.customerid = 1
	AND a.FinalJudgmentStatus = 1
GROUP BY CASE a.RiskInfringement WHEN 2 THEN '是' ELSE '否' END
UNION ALL
SELECT '是否本平台禁售' AS 属性, CASE WHEN b.sku IS NULL THEN '否' ELSE '是' END AS 值, COUNT(a.sku) AS sku数量
FROM irobotbox_marketpublish.p_product a
	LEFT JOIN irobotbox_marketpublish.p_product_prohibitionsalemarket b ON a.customerid = a.customerid
		AND a.sku = b.sku
		AND b.marketid = 2
WHERE a.customerid = 1
	AND a.FinalJudgmentStatus = 1
GROUP BY CASE WHEN b.sku IS NULL THEN '否' ELSE '是' END