1.问题分析
从华为云虎鲸--及时销量抓取的慢sql,日志记录需要78.311秒
分析sql发现，查询输出字段和where字段都是a表的字段，所以可以去掉left 查询
优化前查询143条数据需要7秒；优化后查询143条数据需要2.5秒
//143 7s
//143 2.55
2.解决方案
去掉left查询：
SELECT CAST(`a`.`sale_shop_id` AS CHAR) AS `item`, `a`.`is_multi_channel_fulfillment` AS `is_multi_channel_fulfillment`, `a`.`currency` AS `currency`
, SUM(`a`.`item_price` * `a`.`exchange_usd`) AS `saleAmountUSD`
, SUM(CASE
WHEN 'usd' = 'rmb' THEN `a`.`item_price` * `a`.`exchange_rmb`
WHEN 'usd' = 'usd' THEN `a`.`item_price` * `a`.`exchange_usd`
ELSE `a`.`item_price`
END) AS `saleAmount`
, COUNT(`a`.`amazon_order_id`) AS `orderNum`, SUM(`a`.`quantity`) AS `saleNum`
FROM `all_orders_data_by_last_update` a
WHERE `a`.`purchase_date` >= '2022-03-01 00:00:00'
AND `a`.`purchase_date` <= '2022-07-04 23:59:59'
AND `a`.`sale_shop_id` > 0
AND (`a`.`order_status` <> 'Cancelled'
AND `a`.`is_multi_channel_fulfillment` = 0)
GROUP BY `a`.`sale_shop_id`;
//143 7s
//143 2.55
3.慢sql
SELECT CAST(`a`.`sale_shop_id` AS CHAR) AS `item`, `a`.`is_multi_channel_fulfillment` AS `is_multi_channel_fulfillment`, `a`.`currency` AS `currency`
, SUM(`a`.`item_price` * `a`.`exchange_usd`) AS `saleAmountUSD`
, SUM(CASE
WHEN 'usd' = 'rmb' THEN `a`.`item_price` * `a`.`exchange_rmb`
WHEN 'usd' = 'usd' THEN `a`.`item_price` * `a`.`exchange_usd`
ELSE `a`.`item_price`
END) AS `saleAmount`
, COUNT(`a`.`amazon_order_id`) AS `orderNum`, SUM(`a`.`quantity`) AS `saleNum`
FROM `all_orders_data_by_last_update` a
LEFT JOIN `Amazon_Listing` b
ON `b`.`OrderSourceSku` = `a`.`sku`
AND `b`.`OrderSourceId` = `a`.`sale_shop_id`
LEFT JOIN `evaluation_order` e ON `e`.`amazon_order_id` = `a`.`amazon_order_id`
WHERE `a`.`purchase_date` >= '2022-03-01 00:00:00'
AND `a`.`purchase_date` <= '2022-07-04 23:59:59'
AND `a`.`sale_shop_id` > 0
AND (`a`.`order_status` <> 'Cancelled'
AND `a`.`is_multi_channel_fulfillment` = 0)
GROUP BY `a`.`sale_shop_id`;
//143 7s
//143 2.55
