[步骤]
产品审核美客多平台页面
已审核 组合产品
F12
搜索
[结果]
汇总数据一直汇总不出来，有一个接口报504[期望]
汇总数据正常重现步骤
历史记录
- 2023-01-09 15:36:02, 由 李乐同 创建。
-
2023-01-09 16:19:04, 由 程伟 指派给 shilin。
修改了 指派给，旧值为 "chengwei"，新值为 "shilin"。
-
2023-01-10 11:32:03, 由 shilin 解决，方案为 非BUG-纯属误判 。
修改了 解决方案，旧值为 ""，新值为 "feiBUG"。
修改了 解决日期，旧值为 ""，新值为 "2023-01-10 11:30:58"。
修改了 指派给 ，旧值为 "shilin"，新值为 "liuhanze"。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 是否确认，旧值为 "0"，新值为 "1"。
修改了 解决者 ，旧值为 ""，新值为 "shilin"。
-
2023-01-11 18:57:49, 由 刘瀚泽 指派给 李乐同。
修改了 指派给，旧值为 "liuhanze"，新值为 "liletong"。
再观察下，2023年1月11日搜索，没有发现问题，可以在1.49S内搜索出来
-
2023-01-11 19:17:48, 由 李乐同 指派给 陈少军。
修改了 指派给，旧值为 "liletong"，新值为 "chenshaojun"。
备注优化方案 -
2023-01-12 09:13:27, 由 陈少军 编辑。
修改了 影响环境，旧值为 "647,"，新值为 "647"。
超时sql:
SELECT COUNT(*)
FROM (SELECT *
FROM (SELECT `Id` AS `Id`,
`CustomerId` AS `CustomerId`,
`OrderSourceId` AS `OrderSourceId`,
`SiteId` AS `SiteId`,
`ProductId` AS `ProductId`,
`SellerId` AS `SellerId`,
`OfficialStoreId` AS `OfficialStoreId`,
`Type` AS `Type`,
`ParentSku` AS `ParentSku`,
`Name` AS `Name`,
`SubName` AS `SubName`,
`Tags` AS `Tags`,
`MainImage` AS `MainImage`,
`CategoryId` AS `CategoryId`,
`Price` AS `Price`,
`BasePrice` AS `BasePrice`,
`OriginalPrice` AS `OriginalPrice`,
`Currency` AS `Currency`,
`InitialQuantity` AS `InitialQuantity`,
`Quantity` AS `Quantity`,
`SoldQuantity` AS `SoldQuantity`,
`BuyingMode` AS `BuyingMode`,
`ListingTypeId` AS `ListingTypeId`,
`StartTime` AS `StartTime`,
`StopTime` AS `StopTime`,
`Condition` AS `Condition`,
`PermaLink` AS `PermaLink`,
`AcceptsMercadopago` AS `AcceptsMercadopago`,
`VideoId` AS `VideoId`,
`DescriptionId` AS `DescriptionId`,
`CatalogProductId` AS `CatalogProductId`,
`DomainId` AS `DomainId`,
`DateCreated` AS `DateCreated`,
`LastUpdate` AS `LastUpdate`,
`Health` AS `Health`,
`CatalogListing` AS `CatalogListing`,
`AutomaticRelist` AS `AutomaticRelist`,
`Warranty` AS `Warranty`,
`Status` AS `Status`,
`SubStatus` AS `SubStatus`,
`SellerSku` AS `SellerSku`,
`Inventory` AS `Inventory`,
`Color` AS `Color`,
`Size` AS `Size`,
`Length` AS `Length`,
`Height` AS `Height`,
`Width` AS `Width`,
`Weight` AS `Weight`,
`Sku` AS `Sku`,
`CustomSku` AS `CustomSku`,
`PublishTime` AS `PublishTime`,
`UnPublishTime` AS `UnPublishTime`,
`AutoPublishStrategyId` AS `AutoPublishStrategyId`,
`AutoPublishTime` AS `AutoPublishTime`,
`IsAutoPublish` AS `IsAutoPublish`,
`RePublishStrategyId` AS `RePublishStrategyId`,
`RePublishTime` AS `RePublishTime`,
`AutoUnPublishStrategyId` AS `AutoUnPublishStrategyId`,
`AutoUnPublishTime` AS `AutoUnPublishTime`,
`AutoAdjustPriceStrategyId` AS `AutoAdjustPriceStrategyId`,
`AutoAdjustPriceTime` AS `AutoAdjustPriceTime`,
`AutoPromotionStrategyId` AS `AutoPromotionStrategyId`,
`AutoPromotionTime` AS `AutoPromotionTime`,
`LastPhoenixPlanStrategyType` AS `LastPhoenixPlanStrategyType`,
`OnlineStatus` AS `OnlineStatus`,
`LastModifierId` AS `LastModifierId`,
`LastModifier` AS `LastModifier`,
`LastModificationTime` AS `LastModificationTime`,
`CreatorId` AS `CreatorId`,
`Creator` AS `Creator`,
`CreationTime` AS `CreationTime`
FROM `M_Mercadolibre_Listing`
WHERE (((`CustomerId` = 1) AND NOT (`Sku` IS NULL OR `Sku` = '')) AND
(`OnlineStatus` IN (1)))) MergeTable) m
LEFT JOIN `M_Mercadolibre_Listing_Site` site ON ((((`site`.`CustomerId` = `m`.`CustomerId`) AND
(`site`.`OrderSourceId` = `m`.`OrderSourceId`)) AND
(`site`.`ProductId` = `m`.`ProductId`)) AND
(`site`.`Status` IN ('active'))) -
2023-01-12 11:04:00, 由 陈少军 添加备注。
已和石林沟通，目前慢的是 Listing 管理页中的平台查询结果总数，
慢sql如上，
这个慢sql,执行要283秒，执行超时，这个要优化慢sql+表数据归档 M_Mercadolibre_Listing （18771761条记录）
分析这个慢sql:发现，慢主要是查询这个表慢，CustomerId=1 and NOT (`Sku` IS NULL OR `Sku` = '') and `OnlineStatus`=1 查询条件要查询900万数据，
查询条件少，不能充分过滤数据，导致的慢
select count(0) from M_Mercadolibre_Listing where CustomerId=1 and NOT (`Sku` IS NULL OR `Sku` = '') and `OnlineStatus`=1
采取优化措施：
1. 从慢sql上没有改进的空间，查询条件少，需产品从业务上看看是否可以添加条件减少查询的数据；
2. 从数据归档上处理后，看看效果：已发给翰泽：
这2个表的归档业务帮梳理一下，把归档sql发给高发
select count(0) from M_Mercadolibre_Listing_Site -- 22484974
select count(0) from M_Mercadolibre_Listing （18771761条记录） -
2023-01-12 11:34:27, 由 陈少军 指派给 李乐同。
修改了 指派给，旧值为 "chenshaojun"，新值为 "liletong"。
修改了 抄送给，旧值为 ""，新值为 ",shilin"。
如上采取的优化措施
1. 已添加索引idx_customerid_OnlineStatus_Sku；
2. 去掉不必要的字段查询：只保留`Id` AS `Id`,
`CustomerId` AS `CustomerId`,
`OrderSourceId` AS `OrderSourceId`,
`ProductId` AS `ProductId`测试查询从283秒左右到90秒查询出结果；
3.带翰泽梳理出归档sql执行后，速度会近一步提高
优化后的sql:
SELECT COUNT(*)
FROM (SELECT *
FROM (SELECT `Id` AS `Id`,
`CustomerId` AS `CustomerId`,
`OrderSourceId` AS `OrderSourceId`,
`ProductId` AS `ProductId`
FROM `M_Mercadolibre_Listing`
WHERE (((`CustomerId` = 1) AND NOT (`Sku` IS NULL OR `Sku` = '')) AND
(`OnlineStatus` IN (1)))) MergeTable) m
LEFT JOIN `M_Mercadolibre_Listing_Site` site ON ((((`site`.`CustomerId` = `m`.`CustomerId`) AND
(`site`.`OrderSourceId` = `m`.`OrderSourceId`)) AND
(`site`.`ProductId` = `m`.`ProductId`)) AND
(`site`.`Status` IN ('active'))) -
2023-01-19 11:46:45, 由 李乐同 编辑。
修改了 指派给，旧值为 "liletong"，新值为 "shilin"。
修改了 抄送给，旧值为 ",shilin"，新值为 ""。
还是汇总超时
-
2023-01-19 13:58:33, 由 shilin 指派给 李乐同。
修改了 指派给，旧值为 "shilin"，新值为 "liletong"。
-
2023-01-30 18:16:11, 由 李乐同 关闭。
修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
修改了 指派给 ，旧值为 "liletong"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "liletong"。
修改了 关闭日期，旧值为 ""，新值为 "2023-01-30 18:16:11"。
-
2023-03-24 09:36:37, 由 李乐同 编辑。
修改了 Bug标题，区别为：
001-
正式环境：美客多平台产品审核搜索接口报504
001+ 【生产环境】美客多平台产品审核搜索接口报504001-
正式环境：美客多平台产品审核搜索接口报504
001+ 【生产环境】美客多平台产品审核搜索接口报504 -
2023-11-17 09:46:12, 由 刘超 编辑。
修改了 所属模块，旧值为 "1735"，新值为 "1874"。
| 由谁创建 | 李乐同 于 2023-01-09 15:36:02 |
|---|---|
| 影响环境 |
647 |
| 由谁解决 | shilin 于 2023-01-10 11:30:58 |
| 解决版本 | |
| 解决方案 | 非BUG-纯属误判 |
| 由谁关闭 | 李乐同 于 2023-01-30 18:16:11 |
| 最后修改 | 刘超 于 2023-11-17 09:46:10 |
| 相关Bug |
|---|
该问题转优化
SQL 如下: