1 .问题分析
从华为云虎鲸-及时销量获取的慢sql,记录查询用时69秒
分析sql发现，查询输出字段和where字段都是a表的字段，所以可以去掉left
//1 6.6S 优化前查询到1条记录用时6秒，优化后查询到1条记录用时1.9秒
//1 1.9S
2.解决方案
去掉left
SELECT 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);
//1 6.6S
//1 1.9S
3.慢sql
SELECT 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);
//1 6.6S
//1 2S