1.问题分析
优惠券mysql 慢sql top 10,缺少索引
2.解决方案： 在晚上业务使用量少的时候创建
1. 关联查询的表无主键：
coupon_amazon_listing_84，
base_source_sale_admin，
base_order_source
base_country，无主键，执行计划全表扫描
3慢sql
SELECT
t5.id,
t5.image_url,
t5.sale_price,
t5.order_source_country,
t5.customer_id,
t5.order_source_id,
t5.update_time,
t5.is_fba,
t5.order_source_sku,
t5.asin_or_item_id,
t5.sku,
t5.kykc_num,
t5.ware_house_type,
t5.add_time,
t5.is_off_shelf,
t5.listing_price,
t5.listing_sold_num,
t5.business_admin_id,
t5.business_user_name,
t5.currency,
t5.exchange_rmb,
t5.review_star,
t5.inv_age_0_to_90_days,
t5.inv_age_91_to_180_days,
t5.inv_age_181_to_270_days,
t5.inv_age_271_to_365_days,
t5.inv_age_365_plus_days,
t5.business_team,
t5.business_team_name,
t5.parent_asin,
t5.parent_id,
t5.on_shelf_mode,
t5.publish_time,
t5.order_source_sku_order_time,
t5.asin_order_time,
t5.order_source_name,
t5.remark,
t5.publishDay,
t5.seller_id,
t5.marketplace_id
FROM
(SELECT
t1.id,
t6.image_url,
t1.sale_price,
t1.order_source_country,
t1.customer_id,
t1.order_source_id,
t1.update_time,
t1.is_fba,
t1.order_source_sku,
t1.asin_or_item_id,
t1.sku,
t1.kykc_num,
t1.ware_house_type,
t1.add_time,
t1.is_off_shelf,
t1.listing_price,
t1.listing_sold_num,
t1.business_admin_id,
t1.business_user_name,
t1.currency,
t1.exchange_rmb,
t1.review_star,
t1.inv_age_0_to_90_days,
t1.inv_age_91_to_180_days,
t1.inv_age_181_to_270_days,
t1.inv_age_271_to_365_days,
t1.inv_age_365_plus_days,
t1.business_team,
t1.business_team_name,
t1.parent_asin,
t1.parent_id,
t1.on_shelf_mode,
t1.publish_time,
t1.order_source_sku_order_time,
t1.asin_order_time,
t2.order_source_name,
t1.remark,
TIMESTAMPDIFF (DAY, t1.publish_time, NOW ()) publishDay,
t1.seller_id,
t1.marketplace_id
FROM
prod_coupon.coupon_amazon_listing_84 t1
INNER JOIN prod_coupon.base_order_source t2
ON t1.customer_id = t2.customer_id
AND t1.order_source_id = t2.id
AND t2.order_source_country = t1.order_source_country
LEFT JOIN prod_coupon.base_country t6
ON t2.order_source_country = t6.shorthand
INNER JOIN prod_coupon.base_source_sale_admin t7
ON t2.customer_id = t7.customer_id
AND t7.order_source_id = t2.id
WHERE t1.order_source_country NOT IN ('PL', 'TR')
AND t1.is_delete = 0
AND t1.asin_or_item_id IS NOT NULL
AND t1.asin_or_item_id != ''
AND t2.is_active = 1
AND t1.review_star > 3
AND t2.order_source_type = 1
AND t1.seller_id IS NOT NULL
AND t1.marketplace_id IS NOT NULL
AND NOT EXISTS
(SELECT
1
FROM
prod_coupon.coupon c,
prod_coupon.coupon_asin ca
WHERE c.id = ca.coupon_id
AND t1.customer_id = ca.customer_id
AND ca.department_id = 84
AND c.source_id = t1.order_source_id
AND c.data_status IN (1, 2, 4, 5)
AND (
c.start_time BETWEEN '2022-09-20 00:00:00'
AND '2022-09-22 23:59:59'
OR c.end_time BETWEEN '2022-09-20 00:00:00'
AND '2022-09-22 23:59:59'
)
AND ca.asin = t1.asin_or_item_id)
AND t1.order_source_country = 'US'
AND t7.admin_id = 389
AND t1.order_source_id = 5267
AND t1.is_fba = 0) t5
ORDER BY t5.order_source_id,
t5.id
LIMIT 0, 1000;
