1.问题分析
华为云gaussdb 及时销量慢sql, 执行时间87.653
分析发现问题点：
A. count 查询 去掉其他字段的查询，只保留`a`.`asin`；
B. count查询where条件用到的都是a表的字段，输出字段也是a表的asin，可以去掉left查询；
C.测试优化前在执行需13秒，查询到8125行，优化后 1.9秒，查询到8125行数据，结果是一样的。
2.解决方案
A.去掉多余的输出字段,只保留`a`.`asin`
B.去掉left 查询
优化后的sql:
SELECT COUNT(1)
FROM (
SELECT `a`.`asin`
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`.`asin`
) CountTable;
3.慢sql
SELECT COUNT(1)
FROM (
SELECT `c`.`image_url` AS `url`, `a`.`asin` AS `asin`, `a`.`currency` AS `currency`, `b`.`ParentAsin` AS `parentAsin`, `a`.`sku` AS `sku`
, `c`.`erp_sku` AS `erpSku`, `c`.`client_sku` AS `clientSku`, `a`.`sale_shop_id` AS `shopId`, `c`.`product_brand_id` AS `brandId`, `c`.`product_category3_id` AS `categoryId`
, `a`.`product_name` AS `title`, `c`.`product_name_zh` AS `name`
, 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`
, SUM(`a`.`item_price` * `a`.`exchange_usd`) AS `saleAmountUSD`
, COUNT(`a`.`amazon_order_id`) AS `orderNum`, SUM(`a`.`quantity`) AS `saleNum`
, SUM(CASE
WHEN 'usd' = 'rmb' THEN `a`.`item_price` * `a`.`exchange_rmb`
WHEN 'usd' = 'usd' THEN `a`.`item_price` * `a`.`exchange_usd`
WHEN 'usd' = 'original' THEN `a`.`item_price`
ELSE 0
END) AS `evSaleAmount`
, COUNT(CASE
WHEN `e`.`amazon_order_id` IS NOT NULL THEN `a`.`amazon_order_id`
ELSE 0
END) AS `evOrderNum`
, SUM(CASE
WHEN `e`.`amazon_order_id` IS NOT NULL THEN `a`.`quantity`
ELSE 0
END) AS `evSaleNum`
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 `product` c ON `c`.`erp_sku` = `b`.`Sku`
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`.`asin`
) CountTable;
//8125 12秒
/8125 1.9秒
