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

1.问题分析

华为日志里大量的这个慢sql,执行时间15秒左右，请求很多

2.解决方案

2.1因要查询多个字段，而且Apx_BasicPlan_LogDetail表数据量大，优化方案是先查询出id,在通过id join 查询出需要的字段，测试速度非常快1秒左右

2.2 优化后的sql

SELECT `CustomerId`,`PlanId`,`PlanType`,`DeleteCheckId`,`ExecuteGuid`,`ExecuteType`,`OrderSourceId`,`NewOrderSourceId`,`Brand`,`OrderSourceName`,`NewOrderSourceName`,`Sku`,`OrderSourceSku`,`NewOrderSourceSku`,`Asin`,`IsFba`,`IsUpdateMsrp`,`Status`,`JobNo`,`FeedSubmissionId`,`OperTime`,`UploadUserId`,`UploadUserName`,`Remark`,`FeedMessage`,`Quantity`,`StandardPrice`,`SalePrice`,`OriginalPrice`,`Currency`,`SaleStartDate`,`SaleEndDate`,`NewAsin`,`PublishTime`,`NewPublishTime`,`IsStatistics`,`CreationDate`,`LastModifierId`,`LastModifier`,`LastModificationTime`,`CreatorId`,`Creator`,`CreationTime`,a.Id 
from Apx_BasicPlan_LogDetail a join 
(
select Id 
FROM `Apx_BasicPlan_LogDetail`   WHERE ((((`OrderSourceId` = 14663) AND (`ExecuteType` IN (3,4)) ) AND ( `CustomerId` = 1 )) AND  (`Status` = 2) )  ORDER BY  Id Desc  LIMIT 0,2000
) b on a.Id=b.Id

3.慢sql

SELECT `CustomerId`,`PlanId`,`PlanType`,`DeleteCheckId`,`ExecuteGuid`,`ExecuteType`,`OrderSourceId`,`NewOrderSourceId`,`Brand`,`OrderSourceName`,`NewOrderSourceName`,`Sku`,`OrderSourceSku`,`NewOrderSourceSku`,`Asin`,`IsFba`,`IsUpdateMsrp`,`Status`,`JobNo`,`FeedSubmissionId`,`OperTime`,`UploadUserId`,`UploadUserName`,`Remark`,`FeedMessage`,`Quantity`,`StandardPrice`,`SalePrice`,`OriginalPrice`,`Currency`,`SaleStartDate`,`SaleEndDate`,`NewAsin`,`PublishTime`,`NewPublishTime`,`IsStatistics`,`CreationDate`,`LastModifierId`,`LastModifier`,`LastModificationTime`,`CreatorId`,`Creator`,`CreationTime`,`Id` FROM `Apx_BasicPlan_LogDetail`   WHERE ((((`OrderSourceId` = 14663) AND (`ExecuteType` IN (3,4)) ) AND ( `CustomerId` = 1 )) AND  (`Status` = 2) )  ORDER BY  Id Desc  LIMIT 0,2000;