1.问题分析
从华为云虎鲸--及时销量抓取的慢sql,日志记录需要20秒
分析sql发现，查询输出字段和where字段都是a表的字段，所以可以去掉left 查询
优化前查询3024条数据需要16.5秒；优化后查询3024条数据需要5.5秒
//3024 16.5
//3024 5.5s
2.解决方案
去掉left查询：
SELECT CAST(DATE_FORMAT(`a`.`purchase_date`, '%Y-%m-%d %H:00:00') AS DATETIME) AS `paymentTime`
, SUM(CASE
WHEN 'usd' = 'rmb' THEN `a`.`item_price` * `a`.`exchange_rmb`
ELSE `a`.`item_price` * `a`.`exchange_usd`
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` >= '2021-03-01 00:00:00'
AND `a`.`purchase_date` <= '2021-07-05 00:00:00'
AND `a`.`sale_shop_id` > 0
AND (`a`.`order_status` <> 'Cancelled'
AND `a`.`is_multi_channel_fulfillment` = 0)
GROUP BY DATE_FORMAT(`a`.`purchase_date`, '%Y-%m-%d %H:00:00');
//3024 16.5
//3024 5.5s
3.慢sql
SELECT CAST(DATE_FORMAT(`a`.`purchase_date`, '%Y-%m-%d %H:00:00') AS DATETIME) AS `paymentTime`
, SUM(CASE
WHEN 'usd' = 'rmb' THEN `a`.`item_price` * `a`.`exchange_rmb`
ELSE `a`.`item_price` * `a`.`exchange_usd`
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` >= '2021-03-01 00:00:00'
AND `a`.`purchase_date` <= '2021-07-05 00:00:00'
AND `a`.`sale_shop_id` > 0
AND (`a`.`order_status` <> 'Cancelled'
AND `a`.`is_multi_channel_fulfillment` = 0)
GROUP BY DATE_FORMAT(`a`.`purchase_date`, '%Y-%m-%d %H:00:00');
