任务描述
1.问题分析
智能刊登 gaussdb 慢sql top 10,缺少索引，每天100多次
2.解决方案： 在晚上业务使用量少的时候创建
诊断成功
索引优化建议：
- 建议在数据表 irobotbox_marketpublish.P_Product_ProhibitionSaleMarket 的 Sku,CustomerId,SiteCodes,MarketId 列上添加如下索引，以提高查询性能：
ALTER TABLE `irobotbox_marketpublish`.`P_Product_ProhibitionSaleMarket` ADD INDEX `idx_Sku_CustomerId_SiteCodes_MarketId` (`Sku`, `CustomerId`, `SiteCodes`, `MarketId`)
注意：在业务高峰期，变更表结构存在风险，请谨慎操作！
语句优化建议：
- 条件中包含表达式或函数，查询会使用到临时表，将带来较差的性能。
- 发现IN表达式包含元素超过10，请慎用，因为元素过多会导致全表扫描。
- 使用`IS NULL`或`IS NOT NULL`将可能导致引擎放弃使用索引而进行全表扫描。
SELECT `m`.`CustomerId` AS `CustomerId`, `m`.`Sku` AS `Sku`, `m`.`RealSku` AS `RealSku`, `m`.`CustomSku` AS `CustomSku`, `m`.`IsVariant` AS `IsVariant`
, `m`.`IsMainVariant` AS `IsMainVariant`, `m`.`ParentId` AS `ParentId`, `m`.`Id` AS `Id`, `m`.`LogisticsAttribute` AS `LogisticsAttribute`, `m`.`FinalJudgmentStatus` AS `FinalJudgmentStatus`
, `m`.`FinalJudgmentTime` AS `FinalJudgmentTime`, `m`.`AccurateKeywords` AS `AccurateKeywords`, `m`.`BrandId` AS `BrandId`, `m`.`BrandName` AS `BrandName`, `m`.`CartonPcsNum` AS `CartonPcsNum`
, `m`.`CategoryId` AS `CategoryId`, `m`.`CategoryIds` AS `CategoryIds`, `m`.`FirstCategoryId` AS `FirstCategoryId`, `m`.`Color` AS `Color`, `m`.`DeclarationCode` AS `DeclarationCode`
, `m`.`DeclarationLocalName` AS `DeclarationLocalName`, `m`.`DeclarationName` AS `DeclarationName`, `m`.`DeclarationPrice` AS `DeclarationPrice`, `m`.`DevelopmentType` AS `DevelopmentType`, `m`.`EditStatus` AS `EditStatus`
, `m`.`GrossWeight` AS `GrossWeight`, `m`.`GroupSkuId` AS `GroupSkuId`, `m`.`Height` AS `Height`, `m`.`IsDelete` AS `IsDelete`, `m`.`IsGroup` AS `IsGroup`
, `m`.`RiskInfringement` AS `RiskInfringement`, `m`.`Length` AS `Length`, `m`.`Material` AS `Material`, `m`.`MaxPurchasePrice` AS `MaxPurchasePrice`, `m`.`MinPurchasePrice` AS `MinPurchasePrice`
, `m`.`Name` AS `Name`, `m`.`NameEn` AS `NameEn`, `m`.`NetWeight` AS `NetWeight`, `m`.`PackageHeight` AS `PackageHeight`, `m`.`PackageLength` AS `PackageLength`
, `m`.`PackageWeight` AS `PackageWeight`, `m`.`PackageWidth` AS `PackageWidth`, `m`.`PurchasePrice` AS `PurchasePrice`, `m`.`StrongPlug` AS `StrongPlug`, `m`.`StrongPlugItem` AS `StrongPlugItem`
, `m`.`Size` AS `Size`, `m`.`ShoeTightness` AS `ShoeTightness`, `m`.`StartBatch` AS `StartBatch`, `m`.`State` AS `State`, `m`.`SuitableforGender` AS `SuitableforGender`
, `m`.`SupplyPrice` AS `SupplyPrice`, `m`.`Type` AS `Type`, `m`.`Width` AS `Width`
FROM (SELECT `CustomerId`, `Sku`, `CustomSku`, `IsVariant`, `IsMainVariant`
, `ParentId`, `Name`, `NameEn`, `BrandId`, `BrandName`
, `Material`, `FirstCategoryId`, `CategoryId`, `CategoryIds`, `State`
, `StartBatch`, `RiskInfringement`, `Type`, `LogisticsAttribute`, `StrongPlug`
, `StrongPlugItem`, `PurchasePrice`, `MinPurchasePrice`, `MaxPurchasePrice`, `SupplyPrice`
, `Color`, `Size`, `PackageWeight`, `GrossWeight`, `NetWeight`
, `Length`, `Width`, `Height`, `PackageLength`, `PackageWidth`
, `PackageHeight`, `DevelopmentType`, `FinalJudgmentTime`, `EditStatus`, `DeclarationPrice`
, `AccurateKeywords`, `FinalJudgmentStatus`, `DeclarationName`, `DeclarationCode`, `SuitableforGender`
, `ShoeTightness`, `CartonPcsNum`, `IsGroup`, `GroupSkuId`, `DeclarationLocalName`
, `IsDelete`, `RealSku`, `LastModifierId`, `LastModifier`, `LastModificationTime`
, `CreatorId`, `Creator`, `CreationTime`, `Id`
FROM `P_Product`
) m
LEFT JOIN (SELECT `CustomerId` AS `CustomerId`, `Sku` AS `Sku`
FROM `Ap_AutoPublish_Ebay_AllSku`
WHERE `CustomerId` = 1
AND `MarketId` = 2
AND `StrategyId` = 47656
AND `OrderSourceId` = 5415
AND `SiteCode` = 'US'
) s ON `m`.`CustomerId` = `s`.`CustomerId`
AND `m`.`RealSku` = `s`.`Sku`
WHERE NOT `m`.`IsDelete` = 1
AND `m`.`Sku` LIKE concat('ZN', '%')
AND IFNULL(`m`.`IsGroup`, 0) = 1
AND `m`.`Id` < 75611209
AND `m`.`CustomerId` = 1
AND `m`.`FinalJudgmentStatus` = 1
AND `s`.`Sku` IS NULL
AND `m`.`FinalJudgmentTime` >= '2021-07-24 00:00:00'
AND `m`.`FinalJudgmentTime` <= '2021-08-03 23:59:59'
AND `m`.`State` IN (0, 1, 3, 4, 5)
AND CASE WHEN `m`.`State` = 1 THEN `m`.`StartBatch` ELSE 1 END >= 1
AND CASE WHEN `m`.`State` = 1 THEN `m`.`StartBatch` ELSE 200 END <= 200
AND `m`.`RiskInfringement` IN (1)
AND `m`.`LogisticsAttribute` IN (1, 21, 406, 27, 14, 5, 38, 403, 12, 3, 382, 404, 429, 20, 268, 387, 384, 439, 448, 416, 383, 386, 389)
AND `m`.`PackageLength` <= 40.00
AND `m`.`PackageWidth` <= 40.00
AND `m`.`PackageHeight` <= 40.00
AND `m`.`PackageLength` + `m`.`PackageWidth` + `m`.`PackageHeight` <= 90.00
AND CASE WHEN `m`.`PackageWeight` = 0 THEN `m`.`GrossWeight` ELSE `m`.`PackageWeight` END <= 2.00 * 1000
AND NOT EXISTS (SELECT *
FROM `P_Product_ProhibitionSaleMarket`
WHERE `CustomerId` = `m`.`CustomerId`
AND `MarketId` = 2
AND `Sku` = `m`.`Sku`
AND (`SiteCodes` = ''
OR `SiteCodes` IS NULL))
AND NOT EXISTS (SELECT *
FROM `M_Ebay_Listing`
WHERE `CustomerId` = `m`.`CustomerId`
AND `SiteCode` = 'US'
AND `OrderSourceId` = 5415
AND `Sku` = `m`.`RealSku`
AND `OnlineStatus` IN (1)
AND `Type` = 4)
ORDER BY `m`.`Id` DESC
LIMIT 0, 100
