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

1.问题分析

慢sql日志里的，出现次数比较多

2.解决方案

分析慢sql，缺少索引，在业务低峰创建索引

1. 建议在数据表 irobotbox_marketpublish.M_Lazada_Listing 的 OnlineStatus,CustomerId,IsTimingPublish,TimingPublishTime 列上添加如下索引，以提高查询性能：
   ALTER TABLE `irobotbox_marketpublish`.`M_Lazada_Listing` ADD INDEX `idx_OnlineStatus_CustomerId_IsTimingPublish_TimingPublishTime` (`OnlineStatus`, `CustomerId`, `IsTimingPublish`, `TimingPublishTime`)

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

3.慢sql

SELECT `CustomerId`, `OrderSourceId`, `SiteCode`, `ProductId`, `Type`
	, `ParentSku`, `Name`, `MainImage`, `CategoryId`, `Tags`
	, `Brand`, `Gtin`, `SpuId`, `AssociatedSku`, `SubProductId`
	, `SellerSku`, `Price`, `Inventory`, `ShippingFee`, `Color`
	, `Size`, `Quantity`, `Weight`, `Sku`, `CustomSku`
	, `ChangeStatus`, `SiteCodes`, `IsTranslation`, `PublishTime`, `UnPublishTime`
	, `AutoPublishStrategyId`, `AutoPublishTime`, `IsAutoPublish`, `RePublishStrategyId`, `RePublishTime`
	, `AutoUnPublishStrategyId`, `AutoUnPublishTime`, `AutoAdjustPriceStrategyId`, `AutoAdjustPriceTime`, `AutoPromotionStrategyId`
	, `AutoPromotionTime`, `LastPhoenixPlanStrategyType`, `OnlineStatus`, `IsTimingPublish`, `TimingPublishTime`
	, `LastModifierId`, `LastModifier`, `LastModificationTime`, `CreatorId`, `Creator`
	, `CreationTime`, `Id`
FROM `M_Lazada_Listing`
WHERE `CustomerId` = 1
	AND `OnlineStatus` = 15
	AND `IsTimingPublish` = 1
	AND `TimingPublishTime` < timestamp('2023-04-25 08:40:02.382561')
ORDER BY TimingPublishTime DESC
LIMIT 0, 100