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

1.问题分析

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

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

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

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

INSERT IGNORE INTO m_ebay_listing_update (CustomerId, OrderSourceId, ProductId, SellerSku, sku
	, Status, Date, CreatorId, Creator, CreationTime
	, TitleStatus, remark)
SELECT 1, t.OrderSourceId, t.productid, t.sellersku, t.sku
	, 10 AS Status, '20220629' AS Date, 0 AS CreatorId, '0' AS Creator, now() AS CreationTime
	, 0 AS TitleStatus, 'ebay邮件禁售下架' AS remark
FROM (SELECT DISTINCT c.OrderSourceId, c.OrderSourceCountry, c.ProductId, c.SellerSku, t.sku
	FROM (SELECT DISTINCT b.sku, b.SiteCode AS country
		FROM `temp_ebay_jsyj` a
			LEFT JOIN m_ebay_listing_sub b ON b.CustomerId = 1
				AND b.OrderSourceId = a.ordersourceid
				AND a.items = b.ProductId
		WHERE a.items IS NOT NULL
			AND b.sku != ''
			AND a.systemmessagecode NOT IN ('SM000015697888097749052199', 'SM000015272630687764448748')
		) t
		LEFT JOIN m_ebay_listing_sub b ON b.CustomerId = 1
			AND b.SiteCode = t.country
			AND b.sku = t.sku
		LEFT JOIN m_ebay_listing c ON b.CustomerId = c.CustomerId
			AND b.ProductId = c.ProductId
			AND b.OrderSourceId = c.OrderSourceId
		LEFT JOIN sys_ordersource os ON os.customerid = c.CustomerId
			AND os.OrderSourceId = c.OrderSourceId
	WHERE c.CustomerId = 1
		AND c.OnlineStatus = 1
		AND os.OrderSourceName LIKE '%直发%'
	) t