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

1.问题分析

    智能刊登 gaussdb 慢sql top 10,缺少索引

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

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

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

SELECT m.`Name` AS MarketName, n.SiteCode, n.未采集 AS NoCollectionCount, n.采集中 AS PendingCollectionCount, n.采集成功 AS SuccessCollectionCount
	, n.采集失败 AS FailureCollectionCount, n.采集总数 AS CollectionCount, n.成功率 AS SuccessPercent
FROM (SELECT customerid, marketId, sitecode, SUM(CASE WHEN `Status` = 0 THEN 1 ELSE 0 END) AS "未采集", SUM(CASE WHEN `Status` = 1 THEN 1 ELSE 0 END) AS "采集中"
		, SUM(CASE WHEN `Status` = 2 THEN 1 ELSE 0 END) AS "采集成功", SUM(CASE WHEN `Status` = 3 THEN 1 ELSE 0 END) AS "采集失败", COUNT(1) AS "采集总数", SUM(CASE WHEN `Status` = 2 THEN 1 ELSE 0 END) / SUM(CASE WHEN `Status` = 2
		OR `Status` = 3 THEN 1 ELSE 0 END) AS "成功率", date(CreationTime) AS CreationTime
	FROM p_market_product_category_collection a
	WHERE a.CustomerId = 1
		AND LastModificationTime > '2022-09-08 16:00:00'
		AND MarketId = 97
	GROUP BY a.customerid, a.marketId, a.sitecode
	) n
	INNER JOIN sys_market m ON m.CustomerId = n.CustomerId
		AND m.MarketId = n.MarketId