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

1.问题分析

出现在慢日志列表比较靠前的sql，分析缺少索引

2.解决方案

在业务低峰创建索引

索引优化建议

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

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

3.慢sql

SELECT l.*
FROM (SELECT `CustomerId` AS `CustomerId`, `Sku` AS `Sku`
	FROM `P_Product`
	WHERE `CustomerId` = 1
		AND `State` IN (1)
		AND `StartBatch` >= 50
		AND `StartBatch` <= 1000
	) p
	INNER JOIN (SELECT *
		FROM `M_Kogan_Listing`
		WHERE `CustomerId` = 1
			AND NOT (`Sku` IS NULL
			OR `Sku` = '')
			AND `OrderSourceId` IN (10779)
			AND `OrderSourceId` IN (10779)
			AND `OnlineStatus` IN (1)
		) l ON `p`.`CustomerId` = `l`.`CustomerId`
		AND `p`.`Sku` = `l`.`Sku`
ORDER BY `l`.`Id` DESC
LIMIT 0, 500