1.问题分析
从华为云虎鲸-及时销量gaussdb 抓取的慢sql，华为云记录查询时间需78秒
分析sql发现 查询输出的字段和where条件的字段都是a表里的字段，所以可以去掉left查询
优化前 查询输出3010行记录用时7秒，优化后查询输出3010行记录用时2.5秒
//7 3010 rows
//2.5 3010 rows
2.解决方案
优化后的sql, 去掉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` >= '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 DATE_FORMAT(`a`.`purchase_date`, '%Y-%m-%d %H:00:00');
//7 3010 rows
//2.5 3010 rows
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` >= '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 DATE_FORMAT(`a`.`purchase_date`, '%Y-%m-%d %H:00:00');
//7 3010 rows
//2.5 3010 rows