深圳智汇创想科技有限公司

001- 反馈人：袁慧斌
001+ 反馈人：袁慧斌 数据库：zhcxkj_center_adv_amazon 查询时间：14.9769 扫描行数：703.807 发送行数：351.904 指纹SQL: with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= ? UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= ? ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d , 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour >= 2025080405 and cv.own_hour <= 2025080408 and cv.site_date >= 2025-06-10 UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour >= 2025080405 and cv.own_hour <= 2025080408 and cv.site_date >= 2025-06-10 ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku; 袁慧斌,人工修改过 AISQL修改建议：优化后的SQL如下： WITH tem AS ( SELECT DISTINCT p.source_id, p.ad_id, p.ad_group_id, p.campaign_id, p.asin, g.portfolio_id, p.sku AS msku, cv.site_hour FROM t_sp_ad_product p JOIN t_sp_ad_campaign g ON p.source_id = g.source_id AND p.campaign_id = g.campaign_id JOIN ( SELECT ad_id, ad_group_id, campaign_id, site_hour FROM t_amazon_sp_advertisement WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date >= 2025-06-10 UNION ALL SELECT ad_id, ad_group_id, campaign_id, site_hour FROM t_amazon_sp_ad_traffic WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date >= 2025-06-10 ) cv ON p.ad_id = cv.ad_id AND p.ad_group_id = cv.ad_group_id AND p.campaign_id = cv.campaign_id WHERE p.source_id = 16549 ORDER BY p.ad_id, cv.site_hour, p.sku LIMIT 1000 ) SELECT t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) AS currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku FROM ( SELECT 1 AS customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour, 0 AS ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, MIN(cv1.currency) AS currency, SUM(cv1.attributed_conversions_1d) AS attributedConversions1d, SUM(cv1.attributed_conversions_7d) AS attributedConversions7d, SUM(cv1.attributed_conversions_14d) AS attributedConversions14d, SUM(cv1.attributed_conversions_30d) AS attributedConversions30d, SUM(cv1.attributed_conversions_1d_same_sku) AS attributedConversions1dSameMsku, SUM(cv1.attributed_conversions_7d_same_sku) AS attributedConversions7dSameMsku, SUM(cv1.attributed_conversions_14d_same_sku) AS attributedConversions14dSameMsku, SUM(cv1.attributed_conversions_30d_same_sku) AS attributedConversions30dSameMsku, SUM(cv1.attributed_sales_1d) AS attributedSales1d, SUM(cv1.attributed_sales_7d) AS attributedSales7d, SUM(cv1.attributed_sales_14d) AS attributedSales14d, SUM(cv1.attributed_sales_30d) AS attributedSales30d, SUM(cv1.attributed_sales_1d_same_sku) AS attributedSales1dSameMsku, SUM(cv1.attributed_sales_7d_same_sku) AS attributedSales7dSameMsku, SUM(cv1.attributed_sales_14d_same_sku) AS attributedSales14dSameMsku, SUM(cv1.attributed_sales_30d_same_sku) AS attributedSales30dSameMsku, SUM(cv1.attributed_units_ordered_1d) AS attributedUnitsOrdered1d, SUM(cv1.attributed_units_ordered_7d) AS attributedUnitsOrdered7d, SUM(cv1.attributed_units_ordered_14d) AS attributedUnitsOrdered14d, SUM(cv1.attributed_units_ordered_30d) AS attributedUnitsOrdered30d, SUM(cv1.attributed_units_ordered_1d_same_sku) AS attributedUnitsOrdered1dSameMsku, SUM(cv1.attributed_units_ordered_7d_same_sku) AS attributedUnitsOrdered7dSameMsku, SUM(cv1.attributed_units_ordered_14d_same_sku) AS attributedUnitsOrdered14dSameMsku, SUM(cv1.attributed_units_ordered_30d_same_sku) AS attributedUnitsOrdered30dSameMsku FROM tem JOIN t_amazon_sp_advertisement cv1 ON tem.ad_id = cv1.ad_id AND tem.ad_group_id = cv1.ad_group_id AND tem.campaign_id = cv1.campaign_id AND tem.site_hour = cv1.site_hour GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id ) t1 LEFT JOIN ( SELECT 1 AS customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour, 0 AS ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, MIN(tf1.currency) AS currency, SUM(tf1.cost) AS cost, SUM(tf1.impressions) AS impressions, SUM(tf1.clicks) AS clicks FROM tem JOIN t_amazon_sp_ad_traffic tf1 ON tem.ad_id = tf1.ad_id AND tem.ad_group_id = tf1.ad_group_id AND tem.campaign_id = tf1.campaign_id AND tem.site_hour = tf1.site_hour GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id ) t2 ON t1.campaign_id = t2.campaign_id AND t1.ad_group_id = t2.ad_group_id AND t1.ad_id = t2.ad_id AND t1.site_hour = t2.site_hour AND t1.source_id = t2.source_id AND t1.msku = t2.msku; 主要优化点： 1. 简化了临时表tem的查询，将UNION ALL操作下推到子查询中 2. 将LEFT JOIN改为INNER JOIN（改为JOIN），因为WHERE条件已要求匹配 3. 移除了不必要的COLLATE操作，假设表字符集一致 4. 使用BETWEEN简化范围查询 5. 优化了JOIN条件，减少数据量 explain表：id
002- 数据库：zhcxkj_center_adv_amazon
002+ select_type
003- 查询时间：14.9769
003+ table
004- 扫描行数：703.807
004+ partitions
005- 发送行数：351.904
005+ type
006- 指纹SQL: with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= ? UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= ? ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d ,
006+ possible_keys
007- 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour >= 2025080405 and cv.own_hour <= 2025080408 and cv.site_date >= 2025-06-10 UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour >= 2025080405 and cv.own_hour <= 2025080408 and cv.site_date >= 2025-06-10 ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku;
007+ key
008- 袁慧斌,人工修改过
008+ key_len
009- AISQL修改建议：优化后的SQL如下：
009+ ref
010-
010+ rows
011- WITH tem AS (
011+ filtered
012- SELECT DISTINCT
012+ Extra
013- p.source_id,
013+ 1
014- p.ad_id,
014+ PRIMARY
015- p.ad_group_id,
015+ <derived2>
016- p.campaign_id,
016+ None
017- p.asin,
017+ ALL
018- g.portfolio_id,
018+ None
019- p.sku AS msku,
019+ None
020- cv.site_hour
020+ None
021- FROM t_sp_ad_product p
021+ None
022- JOIN t_sp_ad_campaign g ON p.source_id = g.source_id AND p.campaign_id = g.campaign_id
022+ 84426
023- JOIN (
023+ 100
024- SELECT ad_id, ad_group_id, campaign_id, site_hour
024+ None
025- FROM t_amazon_sp_advertisement
025+ 1
026- WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date >= 2025-06-10
026+ PRIMARY
027- UNION ALL
027+ <derived6>
028- SELECT ad_id, ad_group_id, campaign_id, site_hour
028+ None
029- FROM t_amazon_sp_ad_traffic
029+ ALL
030- WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date >= 2025-06-10
030+ None
031- ) cv ON p.ad_id = cv.ad_id AND p.ad_group_id = cv.ad_group_id AND p.campaign_id = cv.campaign_id
031+ None
032- WHERE p.source_id = 16549
032+ None
033- ORDER BY p.ad_id, cv.site_hour, p.sku
033+ None
034- LIMIT 1000
034+ 167423
035- )
035+ 100
036- SELECT
036+ Using where; Using join buffer (hash join)
037- t1.customer_id,
037+ 6
038- t1.report_day,
038+ DERIVED
039- t1.report_hour,
039+ <derived3>
040- t1.ad_type,
040+ None
041- t1.source_id,
041+ ALL
042- t1.msku,
042+ None
043- t1.ad_id,
043+ None
044- t1.ad_group_id,
044+ None
045- t1.campaign_id,
045+ None
046- t1.site_hour,
046+ 1000
047- t1.asin,
047+ 100
048- t1.portfolio_id,
048+ Using temporary
049- IFNULL(t1.currency, t2.currency) AS currency,
049+ 6
050- t2.cost,
050+ DERIVED
051- t2.impressions,
051+ tf1
052- t2.clicks,
052+ None
053- t1.attributedConversions1d,
053+ ref
054- t1.attributedConversions7d,
054+ idx_site_hour,idx_ad_id_own_hour,idx_adid
055- t1.attributedConversions14d,
055+ idx_site_hour
056- t1.attributedConversions30d,
056+ 5
057- t1.attributedConversions1dSameMsku,
057+ tem.site_hour
058- t1.attributedConversions7dSameMsku,
058+ 167
059- t1.attributedConversions14dSameMsku,
059+ 100
060- t1.attributedConversions30dSameMsku,
060+ Using where
061- t1.attributedSales1d,
061+ 2
062- t1.attributedSales7d,
062+ DERIVED
063- t1.attributedSales14d,
063+ <derived3>
064- t1.attributedSales30d,
064+ None
065- t1.attributedSales1dSameMsku,
065+ ALL
066- t1.attributedSales7dSameMsku,
066+ None
067- t1.attributedSales14dSameMsku,
067+ None
068- t1.attributedSales30dSameMsku,
068+ None
069- t1.attributedUnitsOrdered1d,
069+ None
070- t1.attributedUnitsOrdered7d,
070+ 1000
071- t1.attributedUnitsOrdered14d,
071+ 100
072- t1.attributedUnitsOrdered30d,
072+ Using temporary
073- t1.attributedUnitsOrdered1dSameMsku,
073+ 2
074- t1.attributedUnitsOrdered7dSameMsku,
074+ DERIVED
075- t1.attributedUnitsOrdered14dSameMsku,
075+ cv1
076- t1.attributedUnitsOrdered30dSameMsku
076+ None
077- FROM (
077+ ref
078- SELECT
078+ idx_site_hour,idx_adid
079- 1 AS customer_id,
079+ idx_site_hour
080- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day,
080+ 5
081- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour,
081+ tem.site_hour
082- 0 AS ad_type,
082+ 84
083- tem.source_id,
083+ 100
084- tem.msku,
084+ Using where
085- tem.ad_id,
085+ 3
086- tem.ad_group_id,
086+ DERIVED
087- tem.campaign_id,
087+ <derived4>
088- tem.site_hour,
088+ None
089- tem.asin,
089+ ALL
090- tem.portfolio_id,
090+ None
091- MIN(cv1.currency) AS currency,
091+ None
092- SUM(cv1.attributed_conversions_1d) AS attributedConversions1d,
092+ None
093- SUM(cv1.attributed_conversions_7d) AS attributedConversions7d,
093+ None
094- SUM(cv1.attributed_conversions_14d) AS attributedConversions14d,
094+ 5901
095- SUM(cv1.attributed_conversions_30d) AS attributedConversions30d,
095+ 100
096- SUM(cv1.attributed_conversions_1d_same_sku) AS attributedConversions1dSameMsku,
096+ Using temporary; Using filesort
097- SUM(cv1.attributed_conversions_7d_same_sku) AS attributedConversions7dSameMsku,
097+ 4
098- SUM(cv1.attributed_conversions_14d_same_sku) AS attributedConversions14dSameMsku,
098+ DERIVED
099- SUM(cv1.attributed_conversions_30d_same_sku) AS attributedConversions30dSameMsku,
099+ cv
100- SUM(cv1.attributed_sales_1d) AS attributedSales1d,
100+ None
101- SUM(cv1.attributed_sales_7d) AS attributedSales7d,
101+ range
102- SUM(cv1.attributed_sales_14d) AS attributedSales14d,
102+ idx_own_hour,idx_adid
103- SUM(cv1.attributed_sales_30d) AS attributedSales30d,
103+ idx_own_hour
104- SUM(cv1.attributed_sales_1d_same_sku) AS attributedSales1dSameMsku,
104+ 5
105- SUM(cv1.attributed_sales_7d_same_sku) AS attributedSales7dSameMsku,
105+ None
106- SUM(cv1.attributed_sales_14d_same_sku) AS attributedSales14dSameMsku,
106+ 860
107- SUM(cv1.attributed_sales_30d_same_sku) AS attributedSales30dSameMsku,
107+ 33.33
108- SUM(cv1.attributed_units_ordered_1d) AS attributedUnitsOrdered1d,
108+ Using index condition; Using where
109- SUM(cv1.attributed_units_ordered_7d) AS attributedUnitsOrdered7d,
109+ 4
110- SUM(cv1.attributed_units_ordered_14d) AS attributedUnitsOrdered14d,
110+ DERIVED
111- SUM(cv1.attributed_units_ordered_30d) AS attributedUnitsOrdered30d,
111+ p
112- SUM(cv1.attributed_units_ordered_1d_same_sku) AS attributedUnitsOrdered1dSameMsku,
112+ None
113- SUM(cv1.attributed_units_ordered_7d_same_sku) AS attributedUnitsOrdered7dSameMsku,
113+ eq_ref
114- SUM(cv1.attributed_units_ordered_14d_same_sku) AS attributedUnitsOrdered14dSameMsku,
114+ ad_id,uni_key
115- SUM(cv1.attributed_units_ordered_30d_same_sku) AS attributedUnitsOrdered30dSameMsku
115+ ad_id
116- FROM tem
116+ 202
117- JOIN t_amazon_sp_advertisement cv1 ON tem.ad_id = cv1.ad_id AND tem.ad_group_id = cv1.ad_group_id AND tem.campaign_id = cv1.campaign_id AND tem.site_hour = cv1.site_hour
117+ zhcxkj_center_adv_amazon.cv.ad_id
118- GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id
118+ 1
119- ) t1
119+ 9.24
120- LEFT JOIN (
120+ Using index condition; Using where
121- SELECT
121+ 4
122- 1 AS customer_id,
122+ DERIVED
123- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day,
123+ g
124- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour,
124+ None
125- 0 AS ad_type,
125+ eq_ref
126- tem.source_id,
126+ uni_source_campaign_id
127- tem.msku,
127+ uni_source_campaign_id
128- tem.ad_id,
128+ 206
129- tem.ad_group_id,
129+ const,zhcxkj_center_adv_amazon.p.campaign_id
130- tem.campaign_id,
130+ 1
131- tem.site_hour,
131+ 100
132- tem.asin,
132+ None
133- tem.portfolio_id,
133+ 5
134- MIN(tf1.currency) AS currency,
134+ UNION
135- SUM(tf1.cost) AS cost,
135+ p
136- SUM(tf1.impressions) AS impressions,
136+ None
137- SUM(tf1.clicks) AS clicks
137+ ref
138- FROM tem
138+ ad_id,uni_key
139- JOIN t_amazon_sp_ad_traffic tf1 ON tem.ad_id = tf1.ad_id AND tem.ad_group_id = tf1.ad_group_id AND tem.campaign_id = tf1.campaign_id AND tem.site_hour = tf1.site_hour
139+ uni_key
140- GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id
140+ 4
141- ) t2 ON t1.campaign_id = t2.campaign_id AND t1.ad_group_id = t2.ad_group_id AND t1.ad_id = t2.ad_id AND t1.site_hour = t2.site_hour AND t1.source_id = t2.source_id AND t1.msku = t2.msku;
141+ const
142-
142+ 13376
143- 主要优化点：
143+ 100
144- 1. 简化了临时表tem的查询，将UNION ALL操作下推到子查询中
144+ None
145- 2. 将LEFT JOIN改为INNER JOIN（改为JOIN），因为WHERE条件已要求匹配
145+ 5
146- 3. 移除了不必要的COLLATE操作，假设表字符集一致
146+ UNION
147- 4. 使用BETWEEN简化范围查询
147+ cv
148- 5. 优化了JOIN条件，减少数据量
148+ None
149- explain表：
149+ ref
150-
150+ idx_own_hour,idx_ad_id_own_hour,idx_adid
151-
151+ idx_adid
152-
152+ 27
153-
153+ func,func,func
154-
154+ 236
155-
155+ 0.19
156-
156+ Using index condition; Using where
157-
157+ 5
158-
158+ UNION
159-
159+ g
160-
160+ None
161-
161+ eq_ref
162-
162+ uni_source_campaign_id
163-
163+ uni_source_campaign_id
164-
164+ 206
165-
165+ const,zhcxkj_center_adv_amazon.p.campaign_id
166-
166+ 1
167-
167+ 100
168-
168+ None
169-
169+
170-
170+ 额外建议：碎片化 SQL ，补全相关缺失索引 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist") 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date); 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析： +------------------------+--------------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +========================+====================+===============+===============+ | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 | +------------------------+--------------------+---------------+---------------+ | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 | +------------------------+--------------------+---------------+---------------+ | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 | +------------------------+--------------------+---------------+---------------+→ 【t_amazon_sp_advertisement】 表，无需添加任何索引。 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date); 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析： +------------------------+--------------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +========================+====================+===============+===============+ | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 | +------------------------+--------------------+---------------+---------------+ | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 | +------------------------+--------------------+---------------+---------------+ | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 | +------------------------+--------------------+---------------+---------------+→ 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。 【t_sp_ad_product】表 【source_id】字段，索引分析： +-----------------+--------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +=================+==============+===============+===============+ | t_sp_ad_product | uni_key | source_id | 71 | +-----------------+--------------+---------------+---------------+→ 【t_sp_ad_campaign】 表，无需添加任何索引。→ 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。 【t_sp_ad_product】表 【source_id】字段，索引分析： +-----------------+--------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +=================+==============+===============+===============+ | t_sp_ad_product | uni_key | source_id | 71 | +-----------------+--------------+---------------+---------------+ 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date); 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析： +------------------------+--------------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +========================+====================+===============+===============+ | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 | +------------------------+--------------------+---------------+---------------+ | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 | +------------------------+--------------------+---------------+---------------+ | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 | +------------------------+--------------------+---------------+---------------+→ 【t_sp_ad_campaign】 表，无需添加任何索引。
171-
172- id
173-
174-
175- select_type
176-
177-
178- table
179-
180-
181- partitions
182-
183-
184- type
185-
186-
187- possible_keys
188-
189-
190- key
191-
192-
193- key_len
194-
195-
196- ref
197-
198-
199- rows
200-
201-
202- filtered
203-
204-
205- Extra
206-
207-
208-
209-
210-
211-
212- 1
213-
214-
215- PRIMARY
216-
217-
218- <derived2>
219-
220-
221- None
222-
223-
224- ALL
225-
226-
227- None
228-
229-
230- None
231-
232-
233- None
234-
235-
236- None
237-
238-
239- 84426
240-
241-
242- 100
243-
244-
245- None
246-
247-
248-
249-
250- 1
251-
252-
253- PRIMARY
254-
255-
256- <derived6>
257-
258-
259- None
260-
261-
262- ALL
263-
264-
265- None
266-
267-
268- None
269-
270-
271- None
272-
273-
274- None
275-
276-
277- 167423
278-
279-
280- 100
281-
282-
283- Using where; Using join buffer (hash join)
284-
285-
286-
287-
288- 6
289-
290-
291- DERIVED
292-
293-
294- <derived3>
295-
296-
297- None
298-
299-
300- ALL
301-
302-
303- None
304-
305-
306- None
307-
308-
309- None
310-
311-
312- None
313-
314-
315- 1000
316-
317-
318- 100
319-
320-
321- Using temporary
322-
323-
324-
325-
326- 6
327-
328-
329- DERIVED
330-
331-
332- tf1
333-
334-
335- None
336-
337-
338- ref
339-
340-
341- idx_site_hour,idx_ad_id_own_hour,idx_adid
342-
343-
344- idx_site_hour
345-
346-
347- 5
348-
349-
350- tem.site_hour
351-
352-
353- 167
354-
355-
356- 100
357-
358-
359- Using where
360-
361-
362-
363-
364- 2
365-
366-
367- DERIVED
368-
369-
370- <derived3>
371-
372-
373- None
374-
375-
376- ALL
377-
378-
379- None
380-
381-
382- None
383-
384-
385- None
386-
387-
388- None
389-
390-
391- 1000
392-
393-
394- 100
395-
396-
397- Using temporary
398-
399-
400-
401-
402- 2
403-
404-
405- DERIVED
406-
407-
408- cv1
409-
410-
411- None
412-
413-
414- ref
415-
416-
417- idx_site_hour,idx_adid
418-
419-
420- idx_site_hour
421-
422-
423- 5
424-
425-
426- tem.site_hour
427-
428-
429- 84
430-
431-
432- 100
433-
434-
435- Using where
436-
437-
438-
439-
440- 3
441-
442-
443- DERIVED
444-
445-
446- <derived4>
447-
448-
449- None
450-
451-
452- ALL
453-
454-
455- None
456-
457-
458- None
459-
460-
461- None
462-
463-
464- None
465-
466-
467- 5901
468-
469-
470- 100
471-
472-
473- Using temporary; Using filesort
474-
475-
476-
477-
478- 4
479-
480-
481- DERIVED
482-
483-
484- cv
485-
486-
487- None
488-
489-
490- range
491-
492-
493- idx_own_hour,idx_adid
494-
495-
496- idx_own_hour
497-
498-
499- 5
500-
501-
502- None
503-
504-
505- 860
506-
507-
508- 33.33
509-
510-
511- Using index condition; Using where
512-
513-
514-
515-
516- 4
517-
518-
519- DERIVED
520-
521-
522- p
523-
524-
525- None
526-
527-
528- eq_ref
529-
530-
531- ad_id,uni_key
532-
533-
534- ad_id
535-
536-
537- 202
538-
539-
540- zhcxkj_center_adv_amazon.cv.ad_id
541-
542-
543- 1
544-
545-
546- 9.24
547-
548-
549- Using index condition; Using where
550-
551-
552-
553-
554- 4
555-
556-
557- DERIVED
558-
559-
560- g
561-
562-
563- None
564-
565-
566- eq_ref
567-
568-
569- uni_source_campaign_id
570-
571-
572- uni_source_campaign_id
573-
574-
575- 206
576-
577-
578- const,zhcxkj_center_adv_amazon.p.campaign_id
579-
580-
581- 1
582-
583-
584- 100
585-
586-
587- None
588-
589-
590-
591-
592- 5
593-
594-
595- UNION
596-
597-
598- p
599-
600-
601- None
602-
603-
604- ref
605-
606-
607- ad_id,uni_key
608-
609-
610- uni_key
611-
612-
613- 4
614-
615-
616- const
617-
618-
619- 13376
620-
621-
622- 100
623-
624-
625- None
626-
627-
628-
629-
630- 5
631-
632-
633- UNION
634-
635-
636- cv
637-
638-
639- None
640-
641-
642- ref
643-
644-
645- idx_own_hour,idx_ad_id_own_hour,idx_adid
646-
647-
648- idx_adid
649-
650-
651- 27
652-
653-
654- func,func,func
655-
656-
657- 236
658-
659-
660- 0.19
661-
662-
663- Using index condition; Using where
664-
665-
666-
667-
668- 5
669-
670-
671- UNION
672-
673-
674- g
675-
676-
677- None
678-
679-
680- eq_ref
681-
682-
683- uni_source_campaign_id
684-
685-
686- uni_source_campaign_id
687-
688-
689- 206
690-
691-
692- const,zhcxkj_center_adv_amazon.p.campaign_id
693-
694-
695- 1
696-
697-
698- 100
699-
700-
701- None
702-
703-
704-
705-
706-
707- 额外建议：碎片化 SQL ，补全相关缺失索引
708- 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")
709- 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);
710-
711- 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：
712- +------------------------+--------------------+---------------+---------------+
713- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
714- +========================+====================+===============+===============+
715- | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |
716- +------------------------+--------------------+---------------+---------------+
717- | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |
718- +------------------------+--------------------+---------------+---------------+
719- | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |
720- +------------------------+--------------------+---------------+---------------+
721-
722- → 【t_amazon_sp_advertisement】 表，无需添加任何索引。
723-
724- 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);
725-
726- 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：
727- +------------------------+--------------------+---------------+---------------+
728- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
729- +========================+====================+===============+===============+
730- | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |
731- +------------------------+--------------------+---------------+---------------+
732- | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |
733- +------------------------+--------------------+---------------+---------------+
734- | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |
735- +------------------------+--------------------+---------------+---------------+
736-
737- → 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。
738-
739- 【t_sp_ad_product】表 【source_id】字段，索引分析：
740- +-----------------+--------------+---------------+---------------+
741- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
742- +=================+==============+===============+===============+
743- | t_sp_ad_product | uni_key | source_id | 71 |
744- +-----------------+--------------+---------------+---------------+
745-
746- → 【t_sp_ad_campaign】 表，无需添加任何索引。
747-
748- → 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。
749-
750- 【t_sp_ad_product】表 【source_id】字段，索引分析：
751- +-----------------+--------------+---------------+---------------+
752- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
753- +=================+==============+===============+===============+
754- | t_sp_ad_product | uni_key | source_id | 71 |
755- +-----------------+--------------+---------------+---------------+
756-
757- 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);
758-
759- 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：
760- +------------------------+--------------------+---------------+---------------+
761- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
762- +========================+====================+===============+===============+
763- | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |
764- +------------------------+--------------------+---------------+---------------+
765- | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |
766- +------------------------+--------------------+---------------+---------------+
767- | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |
768- +------------------------+--------------------+---------------+---------------+
769-
770- → 【t_sp_ad_campaign】 表，无需添加任何索引。
771-
772-

001- 反馈人：袁慧斌<br />
001+ 反馈人：袁慧斌<br /> 数据库：zhcxkj_center_adv_amazon<br /> 查询时间：14.9769<br /> 扫描行数：703.807<br /> 发送行数：351.904<br /> 指纹SQL: with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= ? UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= ? ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d , <br /> 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour &gt;= 2025080405 and cv.own_hour &lt;= 2025080408 and cv.site_date &gt;= 2025-06-10 UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour &gt;= 2025080405 and cv.own_hour &lt;= 2025080408 and cv.site_date &gt;= 2025-06-10 ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku;<br /> 袁慧斌,人工修改过<br /> AISQL修改建议：优化后的SQL如下：<br /><br /> WITH tem AS (<br /> SELECT DISTINCT <br /> p.source_id,<br /> p.ad_id,<br /> p.ad_group_id,<br /> p.campaign_id,<br /> p.asin,<br /> g.portfolio_id,<br /> p.sku AS msku,<br /> cv.site_hour<br /> FROM t_sp_ad_product p<br /> JOIN t_sp_ad_campaign g ON p.source_id = g.source_id AND p.campaign_id = g.campaign_id<br /> JOIN (<br /> SELECT ad_id, ad_group_id, campaign_id, site_hour <br /> FROM t_amazon_sp_advertisement <br /> WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date &gt;= 2025-06-10<br /> UNION ALL<br /> SELECT ad_id, ad_group_id, campaign_id, site_hour <br /> FROM t_amazon_sp_ad_traffic <br /> WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date &gt;= 2025-06-10<br /> ) cv ON p.ad_id = cv.ad_id AND p.ad_group_id = cv.ad_group_id AND p.campaign_id = cv.campaign_id<br /> WHERE p.source_id = 16549<br /> ORDER BY p.ad_id, cv.site_hour, p.sku<br /> LIMIT 1000<br /> )<br /> SELECT <br /> t1.customer_id,<br /> t1.report_day,<br /> t1.report_hour,<br /> t1.ad_type,<br /> t1.source_id,<br /> t1.msku,<br /> t1.ad_id,<br /> t1.ad_group_id,<br /> t1.campaign_id,<br /> t1.site_hour,<br /> t1.asin,<br /> t1.portfolio_id,<br /> IFNULL(t1.currency, t2.currency) AS currency,<br /> t2.cost,<br /> t2.impressions,<br /> t2.clicks,<br /> t1.attributedConversions1d,<br /> t1.attributedConversions7d,<br /> t1.attributedConversions14d,<br /> t1.attributedConversions30d,<br /> t1.attributedConversions1dSameMsku,<br /> t1.attributedConversions7dSameMsku,<br /> t1.attributedConversions14dSameMsku,<br /> t1.attributedConversions30dSameMsku,<br /> t1.attributedSales1d,<br /> t1.attributedSales7d,<br /> t1.attributedSales14d,<br /> t1.attributedSales30d,<br /> t1.attributedSales1dSameMsku,<br /> t1.attributedSales7dSameMsku,<br /> t1.attributedSales14dSameMsku,<br /> t1.attributedSales30dSameMsku,<br /> t1.attributedUnitsOrdered1d,<br /> t1.attributedUnitsOrdered7d,<br /> t1.attributedUnitsOrdered14d,<br /> t1.attributedUnitsOrdered30d,<br /> t1.attributedUnitsOrdered1dSameMsku,<br /> t1.attributedUnitsOrdered7dSameMsku,<br /> t1.attributedUnitsOrdered14dSameMsku,<br /> t1.attributedUnitsOrdered30dSameMsku<br /> FROM (<br /> SELECT <br /> 1 AS customer_id,<br /> DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day,<br /> DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour,<br /> 0 AS ad_type,<br /> tem.source_id,<br /> tem.msku,<br /> tem.ad_id,<br /> tem.ad_group_id,<br /> tem.campaign_id,<br /> tem.site_hour,<br /> tem.asin,<br /> tem.portfolio_id,<br /> MIN(cv1.currency) AS currency,<br /> SUM(cv1.attributed_conversions_1d) AS attributedConversions1d,<br /> SUM(cv1.attributed_conversions_7d) AS attributedConversions7d,<br /> SUM(cv1.attributed_conversions_14d) AS attributedConversions14d,<br /> SUM(cv1.attributed_conversions_30d) AS attributedConversions30d,<br /> SUM(cv1.attributed_conversions_1d_same_sku) AS attributedConversions1dSameMsku,<br /> SUM(cv1.attributed_conversions_7d_same_sku) AS attributedConversions7dSameMsku,<br /> SUM(cv1.attributed_conversions_14d_same_sku) AS attributedConversions14dSameMsku,<br /> SUM(cv1.attributed_conversions_30d_same_sku) AS attributedConversions30dSameMsku,<br /> SUM(cv1.attributed_sales_1d) AS attributedSales1d,<br /> SUM(cv1.attributed_sales_7d) AS attributedSales7d,<br /> SUM(cv1.attributed_sales_14d) AS attributedSales14d,<br /> SUM(cv1.attributed_sales_30d) AS attributedSales30d,<br /> SUM(cv1.attributed_sales_1d_same_sku) AS attributedSales1dSameMsku,<br /> SUM(cv1.attributed_sales_7d_same_sku) AS attributedSales7dSameMsku,<br /> SUM(cv1.attributed_sales_14d_same_sku) AS attributedSales14dSameMsku,<br /> SUM(cv1.attributed_sales_30d_same_sku) AS attributedSales30dSameMsku,<br /> SUM(cv1.attributed_units_ordered_1d) AS attributedUnitsOrdered1d,<br /> SUM(cv1.attributed_units_ordered_7d) AS attributedUnitsOrdered7d,<br /> SUM(cv1.attributed_units_ordered_14d) AS attributedUnitsOrdered14d,<br /> SUM(cv1.attributed_units_ordered_30d) AS attributedUnitsOrdered30d,<br /> SUM(cv1.attributed_units_ordered_1d_same_sku) AS attributedUnitsOrdered1dSameMsku,<br /> SUM(cv1.attributed_units_ordered_7d_same_sku) AS attributedUnitsOrdered7dSameMsku,<br /> SUM(cv1.attributed_units_ordered_14d_same_sku) AS attributedUnitsOrdered14dSameMsku,<br /> SUM(cv1.attributed_units_ordered_30d_same_sku) AS attributedUnitsOrdered30dSameMsku<br /> FROM tem<br /> JOIN t_amazon_sp_advertisement cv1 ON tem.ad_id = cv1.ad_id AND tem.ad_group_id = cv1.ad_group_id AND tem.campaign_id = cv1.campaign_id AND tem.site_hour = cv1.site_hour<br /> GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id<br /> ) t1<br /> LEFT JOIN (<br /> SELECT <br /> 1 AS customer_id,<br /> DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day,<br /> DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour,<br /> 0 AS ad_type,<br /> tem.source_id,<br /> tem.msku,<br /> tem.ad_id,<br /> tem.ad_group_id,<br /> tem.campaign_id,<br /> tem.site_hour,<br /> tem.asin,<br /> tem.portfolio_id,<br /> MIN(tf1.currency) AS currency,<br /> SUM(tf1.cost) AS cost,<br /> SUM(tf1.impressions) AS impressions,<br /> SUM(tf1.clicks) AS clicks<br /> FROM tem<br /> JOIN t_amazon_sp_ad_traffic tf1 ON tem.ad_id = tf1.ad_id AND tem.ad_group_id = tf1.ad_group_id AND tem.campaign_id = tf1.campaign_id AND tem.site_hour = tf1.site_hour<br /> GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id<br /> ) t2 ON t1.campaign_id = t2.campaign_id AND t1.ad_group_id = t2.ad_group_id AND t1.ad_id = t2.ad_id AND t1.site_hour = t2.site_hour AND t1.source_id = t2.source_id AND t1.msku = t2.msku;<br /><br /> 主要优化点：<br /> 1. 简化了临时表tem的查询，将UNION ALL操作下推到子查询中<br /> 2. 将LEFT JOIN改为INNER JOIN（改为JOIN），因为WHERE条件已要求匹配<br /> 3. 移除了不必要的COLLATE操作，假设表字符集一致<br /> 4. 使用BETWEEN简化范围查询<br /> 5. 优化了JOIN条件，减少数据量<br /> explain表：<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><table><tbody><tr><th>id</th>
002- 数据库：zhcxkj_center_adv_amazon<br />
002+ <th>select_type</th>
003- 查询时间：14.9769<br />
003+ <th>table</th>
004- 扫描行数：703.807<br />
004+ <th>partitions</th>
005- 发送行数：351.904<br />
005+ <th>type</th>
006- 指纹SQL: with tem as ( select DISTINCT source_id , ad_id , ad_group_id , campaign_id , asin , portfolio_id , sku as msku , site_hour from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_advertisement cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= ? UNION all select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , p . asin , g . portfolio_id , p . sku , cv . site_hour FROM ? p left join t_sp_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sp_ad_traffic cv on p . ad_id COLLATE utf8mb4_general_ci = cv . ad_id and p . ad_group_id COLLATE utf8mb4_general_ci = cv . ad_group_id and p . campaign_id COLLATE utf8mb4_general_ci = cv . campaign_id where p . source_id = ? and cv . own_hour &gt;= ? and cv . own_hour &lt;= ? and cv . site_date &gt;= ? ) t order by ad_id , site_hour , msku limit ? offset ? ) select t1 . customer_id , t1 . report_day , t1 . report_hour , t1 . ad_type , t1 . source_id , t1 . msku , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , t1 . asin , t1 . portfolio_id , IFNULL ( t1 . currency , t2 . currency ) as currency , t2 . cost , t2 . impressions , t2 . clicks , t1 . attributedConversions1d , t1 . attributedConversions7d , t1 . attributedConversions14d , t1 . attributedConversions30d , t1 . attributedConversions1dSameMsku , t1 . attributedConversions7dSameMsku , t1 . attributedConversions14dSameMsku , t1 . attributedConversions30dSameMsku , t1 . attributedSales1d , t1 . attributedSales7d , t1 . attributedSales14d , <br />
006+ <th>possible_keys</th>
007- 真实SQL：with tem as ( select DISTINCT source_id, ad_id, ad_group_id, campaign_id,asin,portfolio_id, sku as msku ,site_hour from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku, cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_advertisement cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour &gt;= 2025080405 and cv.own_hour &lt;= 2025080408 and cv.site_date &gt;= 2025-06-10 UNION all select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id,p.asin,g.portfolio_id,p.sku,cv.site_hour from t_sp_ad_product p left join t_sp_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sp_ad_traffic cv on p.ad_id COLLATE utf8mb4_general_ci = cv.ad_id and p.ad_group_id COLLATE utf8mb4_general_ci = cv.ad_group_id and p.campaign_id COLLATE utf8mb4_general_ci = cv.campaign_id where p.source_id = 16549 and cv.own_hour &gt;= 2025080405 and cv.own_hour &lt;= 2025080408 and cv.site_date &gt;= 2025-06-10 ) t order by ad_id, site_hour, msku limit 1000 offset 0 ) select t1.customer_id, t1.report_day, t1.report_hour, t1.ad_type, t1.source_id, t1.msku, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, t1.asin, t1.portfolio_id, IFNULL(t1.currency, t2.currency) as currency, t2.cost, t2.impressions, t2.clicks, t1.attributedConversions1d, t1.attributedConversions7d, t1.attributedConversions14d, t1.attributedConversions30d, t1.attributedConversions1dSameMsku, t1.attributedConversions7dSameMsku, t1.attributedConversions14dSameMsku, t1.attributedConversions30dSameMsku, t1.attributedSales1d, t1.attributedSales7d, t1.attributedSales14d, t1.attributedSales30d, t1.attributedSales1dSameMsku, t1.attributedSales7dSameMsku, t1.attributedSales14dSameMsku, t1.attributedSales30dSameMsku, t1.attributedUnitsOrdered1d, t1.attributedUnitsOrdered7d, t1.attributedUnitsOrdered14d, t1.attributedUnitsOrdered30d, t1.attributedUnitsOrdered1dSameMsku, t1.attributedUnitsOrdered7dSameMsku, t1.attributedUnitsOrdered14dSameMsku, t1.attributedUnitsOrdered30dSameMsku from ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(cv1.currency) as currency, sum(cv1.attributed_conversions_1d) as attributedConversions1d, sum(cv1.attributed_conversions_7d) as attributedConversions7d, sum(cv1.attributed_conversions_14d) as attributedConversions14d, sum(cv1.attributed_conversions_30d) as attributedConversions30d, sum(cv1.attributed_conversions_1d_same_sku) as attributedConversions1dSameMsku, sum(cv1.attributed_conversions_7d_same_sku) as attributedConversions7dSameMsku, sum(cv1.attributed_conversions_14d_same_sku) as attributedConversions14dSameMsku, sum(cv1.attributed_conversions_30d_same_sku) as attributedConversions30dSameMsku, sum(cv1.attributed_sales_1d) as attributedSales1d, sum(cv1.attributed_sales_7d) as attributedSales7d, sum(cv1.attributed_sales_14d) as attributedSales14d, sum(cv1.attributed_sales_30d) as attributedSales30d, sum(cv1.attributed_sales_1d_same_sku) as attributedSales1dSameMsku, sum(cv1.attributed_sales_7d_same_sku) as attributedSales7dSameMsku, sum(cv1.attributed_sales_14d_same_sku) as attributedSales14dSameMsku, sum(cv1.attributed_sales_30d_same_sku) as attributedSales30dSameMsku, sum(cv1.attributed_units_ordered_1d) as attributedUnitsOrdered1d, sum(cv1.attributed_units_ordered_7d) as attributedUnitsOrdered7d, sum(cv1.attributed_units_ordered_14d) as attributedUnitsOrdered14d, sum(cv1.attributed_units_ordered_30d) as attributedUnitsOrdered30d, sum(cv1.attributed_units_ordered_1d_same_sku) as attributedUnitsOrdered1dSameMsku, sum(cv1.attributed_units_ordered_7d_same_sku) as attributedUnitsOrdered7dSameMsku, sum(cv1.attributed_units_ordered_14d_same_sku) as attributedUnitsOrdered14dSameMsku, sum(cv1.attributed_units_ordered_30d_same_sku) as attributedUnitsOrdered30dSameMsku from tem left join t_amazon_sp_advertisement cv1 on tem.ad_id COLLATE utf8mb4_general_ci = cv1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = cv1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = cv1.campaign_id and tem.site_hour = cv1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t1 left join ( select 1 as customer_id, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) as report_day, DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) as report_hour, 0 as ad_type, tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id, min(tf1.currency) as currency, sum(tf1.cost) as cost, sum(tf1.impressions) as impressions, sum(tf1.clicks) as clicks from tem left join t_amazon_sp_ad_traffic tf1 on tem.ad_id COLLATE utf8mb4_general_ci = tf1.ad_id and tem.ad_group_id COLLATE utf8mb4_general_ci = tf1.ad_group_id and tem.campaign_id COLLATE utf8mb4_general_ci = tf1.campaign_id and tem.site_hour = tf1.site_hour group by tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour,tem.asin, tem.portfolio_id ) t2 on t1.campaign_id=t2.campaign_id and t1.ad_group_id=t2.ad_group_id and t1.ad_id = t2.ad_id and t1.site_hour = t2.site_hour and t1.source_id=t2.source_id and t1.msku = t2.msku;<br />
007+ <th>key</th>
008- 袁慧斌,人工修改过<br />
008+ <th>key_len</th>
009- AISQL修改建议：优化后的SQL如下：<br />
009+ <th>ref</th>
010- <br />
010+ <th>rows</th>
011- WITH tem AS (<br />
011+ <th>filtered</th>
012- SELECT DISTINCT <br />
012+ <th>Extra</th>
013- p.source_id,<br />
013+ </tr></tbody><tbody><tr><td>1</td>
014- p.ad_id,<br />
014+ <td>PRIMARY</td>
015- p.ad_group_id,<br />
015+ <td>&lt;derived2&gt;</td>
016- p.campaign_id,<br />
016+ <td>None</td>
017- p.asin,<br />
017+ <td>ALL</td>
018- g.portfolio_id,<br />
018+ <td>None</td>
019- p.sku AS msku,<br />
019+ <td>None</td>
020- cv.site_hour<br />
020+ <td>None</td>
021- FROM t_sp_ad_product p<br />
021+ <td>None</td>
022- JOIN t_sp_ad_campaign g ON p.source_id = g.source_id AND p.campaign_id = g.campaign_id<br />
022+ <td>84426</td>
023- JOIN (<br />
023+ <td>100</td>
024- SELECT ad_id, ad_group_id, campaign_id, site_hour <br />
024+ <td>None</td>
025- FROM t_amazon_sp_advertisement <br />
025+ </tr><tr><td>1</td>
026- WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date &gt;= 2025-06-10<br />
026+ <td>PRIMARY</td>
027- UNION ALL<br />
027+ <td>&lt;derived6&gt;</td>
028- SELECT ad_id, ad_group_id, campaign_id, site_hour <br />
028+ <td>None</td>
029- FROM t_amazon_sp_ad_traffic <br />
029+ <td>ALL</td>
030- WHERE own_hour BETWEEN 2025080405 AND 2025080408 AND site_date &gt;= 2025-06-10<br />
030+ <td>None</td>
031- ) cv ON p.ad_id = cv.ad_id AND p.ad_group_id = cv.ad_group_id AND p.campaign_id = cv.campaign_id<br />
031+ <td>None</td>
032- WHERE p.source_id = 16549<br />
032+ <td>None</td>
033- ORDER BY p.ad_id, cv.site_hour, p.sku<br />
033+ <td>None</td>
034- LIMIT 1000<br />
034+ <td>167423</td>
035- )<br />
035+ <td>100</td>
036- SELECT <br />
036+ <td>Using where; Using join buffer (hash join)</td>
037- t1.customer_id,<br />
037+ </tr><tr><td>6</td>
038- t1.report_day,<br />
038+ <td>DERIVED</td>
039- t1.report_hour,<br />
039+ <td>&lt;derived3&gt;</td>
040- t1.ad_type,<br />
040+ <td>None</td>
041- t1.source_id,<br />
041+ <td>ALL</td>
042- t1.msku,<br />
042+ <td>None</td>
043- t1.ad_id,<br />
043+ <td>None</td>
044- t1.ad_group_id,<br />
044+ <td>None</td>
045- t1.campaign_id,<br />
045+ <td>None</td>
046- t1.site_hour,<br />
046+ <td>1000</td>
047- t1.asin,<br />
047+ <td>100</td>
048- t1.portfolio_id,<br />
048+ <td>Using temporary</td>
049- IFNULL(t1.currency, t2.currency) AS currency,<br />
049+ </tr><tr><td>6</td>
050- t2.cost,<br />
050+ <td>DERIVED</td>
051- t2.impressions,<br />
051+ <td>tf1</td>
052- t2.clicks,<br />
052+ <td>None</td>
053- t1.attributedConversions1d,<br />
053+ <td>ref</td>
054- t1.attributedConversions7d,<br />
054+ <td>idx_site_hour,idx_ad_id_own_hour,idx_adid</td>
055- t1.attributedConversions14d,<br />
055+ <td>idx_site_hour</td>
056- t1.attributedConversions30d,<br />
056+ <td>5</td>
057- t1.attributedConversions1dSameMsku,<br />
057+ <td>tem.site_hour</td>
058- t1.attributedConversions7dSameMsku,<br />
058+ <td>167</td>
059- t1.attributedConversions14dSameMsku,<br />
059+ <td>100</td>
060- t1.attributedConversions30dSameMsku,<br />
060+ <td>Using where</td>
061- t1.attributedSales1d,<br />
061+ </tr><tr><td>2</td>
062- t1.attributedSales7d,<br />
062+ <td>DERIVED</td>
063- t1.attributedSales14d,<br />
063+ <td>&lt;derived3&gt;</td>
064- t1.attributedSales30d,<br />
064+ <td>None</td>
065- t1.attributedSales1dSameMsku,<br />
065+ <td>ALL</td>
066- t1.attributedSales7dSameMsku,<br />
066+ <td>None</td>
067- t1.attributedSales14dSameMsku,<br />
067+ <td>None</td>
068- t1.attributedSales30dSameMsku,<br />
068+ <td>None</td>
069- t1.attributedUnitsOrdered1d,<br />
069+ <td>None</td>
070- t1.attributedUnitsOrdered7d,<br />
070+ <td>1000</td>
071- t1.attributedUnitsOrdered14d,<br />
071+ <td>100</td>
072- t1.attributedUnitsOrdered30d,<br />
072+ <td>Using temporary</td>
073- t1.attributedUnitsOrdered1dSameMsku,<br />
073+ </tr><tr><td>2</td>
074- t1.attributedUnitsOrdered7dSameMsku,<br />
074+ <td>DERIVED</td>
075- t1.attributedUnitsOrdered14dSameMsku,<br />
075+ <td>cv1</td>
076- t1.attributedUnitsOrdered30dSameMsku<br />
076+ <td>None</td>
077- FROM (<br />
077+ <td>ref</td>
078- SELECT <br />
078+ <td>idx_site_hour,idx_adid</td>
079- 1 AS customer_id,<br />
079+ <td>idx_site_hour</td>
080- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day,<br />
080+ <td>5</td>
081- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour,<br />
081+ <td>tem.site_hour</td>
082- 0 AS ad_type,<br />
082+ <td>84</td>
083- tem.source_id,<br />
083+ <td>100</td>
084- tem.msku,<br />
084+ <td>Using where</td>
085- tem.ad_id,<br />
085+ </tr><tr><td>3</td>
086- tem.ad_group_id,<br />
086+ <td>DERIVED</td>
087- tem.campaign_id,<br />
087+ <td>&lt;derived4&gt;</td>
088- tem.site_hour,<br />
088+ <td>None</td>
089- tem.asin,<br />
089+ <td>ALL</td>
090- tem.portfolio_id,<br />
090+ <td>None</td>
091- MIN(cv1.currency) AS currency,<br />
091+ <td>None</td>
092- SUM(cv1.attributed_conversions_1d) AS attributedConversions1d,<br />
092+ <td>None</td>
093- SUM(cv1.attributed_conversions_7d) AS attributedConversions7d,<br />
093+ <td>None</td>
094- SUM(cv1.attributed_conversions_14d) AS attributedConversions14d,<br />
094+ <td>5901</td>
095- SUM(cv1.attributed_conversions_30d) AS attributedConversions30d,<br />
095+ <td>100</td>
096- SUM(cv1.attributed_conversions_1d_same_sku) AS attributedConversions1dSameMsku,<br />
096+ <td>Using temporary; Using filesort</td>
097- SUM(cv1.attributed_conversions_7d_same_sku) AS attributedConversions7dSameMsku,<br />
097+ </tr><tr><td>4</td>
098- SUM(cv1.attributed_conversions_14d_same_sku) AS attributedConversions14dSameMsku,<br />
098+ <td>DERIVED</td>
099- SUM(cv1.attributed_conversions_30d_same_sku) AS attributedConversions30dSameMsku,<br />
099+ <td>cv</td>
100- SUM(cv1.attributed_sales_1d) AS attributedSales1d,<br />
100+ <td>None</td>
101- SUM(cv1.attributed_sales_7d) AS attributedSales7d,<br />
101+ <td>range</td>
102- SUM(cv1.attributed_sales_14d) AS attributedSales14d,<br />
102+ <td>idx_own_hour,idx_adid</td>
103- SUM(cv1.attributed_sales_30d) AS attributedSales30d,<br />
103+ <td>idx_own_hour</td>
104- SUM(cv1.attributed_sales_1d_same_sku) AS attributedSales1dSameMsku,<br />
104+ <td>5</td>
105- SUM(cv1.attributed_sales_7d_same_sku) AS attributedSales7dSameMsku,<br />
105+ <td>None</td>
106- SUM(cv1.attributed_sales_14d_same_sku) AS attributedSales14dSameMsku,<br />
106+ <td>860</td>
107- SUM(cv1.attributed_sales_30d_same_sku) AS attributedSales30dSameMsku,<br />
107+ <td>33.33</td>
108- SUM(cv1.attributed_units_ordered_1d) AS attributedUnitsOrdered1d,<br />
108+ <td>Using index condition; Using where</td>
109- SUM(cv1.attributed_units_ordered_7d) AS attributedUnitsOrdered7d,<br />
109+ </tr><tr><td>4</td>
110- SUM(cv1.attributed_units_ordered_14d) AS attributedUnitsOrdered14d,<br />
110+ <td>DERIVED</td>
111- SUM(cv1.attributed_units_ordered_30d) AS attributedUnitsOrdered30d,<br />
111+ <td>p</td>
112- SUM(cv1.attributed_units_ordered_1d_same_sku) AS attributedUnitsOrdered1dSameMsku,<br />
112+ <td>None</td>
113- SUM(cv1.attributed_units_ordered_7d_same_sku) AS attributedUnitsOrdered7dSameMsku,<br />
113+ <td>eq_ref</td>
114- SUM(cv1.attributed_units_ordered_14d_same_sku) AS attributedUnitsOrdered14dSameMsku,<br />
114+ <td>ad_id,uni_key</td>
115- SUM(cv1.attributed_units_ordered_30d_same_sku) AS attributedUnitsOrdered30dSameMsku<br />
115+ <td>ad_id</td>
116- FROM tem<br />
116+ <td>202</td>
117- JOIN t_amazon_sp_advertisement cv1 ON tem.ad_id = cv1.ad_id AND tem.ad_group_id = cv1.ad_group_id AND tem.campaign_id = cv1.campaign_id AND tem.site_hour = cv1.site_hour<br />
117+ <td>zhcxkj_center_adv_amazon.cv.ad_id</td>
118- GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id<br />
118+ <td>1</td>
119- ) t1<br />
119+ <td>9.24</td>
120- LEFT JOIN (<br />
120+ <td>Using index condition; Using where</td>
121- SELECT <br />
121+ </tr><tr><td>4</td>
122- 1 AS customer_id,<br />
122+ <td>DERIVED</td>
123- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %Y-%m-%d) AS report_day,<br />
123+ <td>g</td>
124- DATE_FORMAT(STR_TO_DATE(tem.site_hour, %Y%m%d%H), %H) AS report_hour,<br />
124+ <td>None</td>
125- 0 AS ad_type,<br />
125+ <td>eq_ref</td>
126- tem.source_id,<br />
126+ <td>uni_source_campaign_id</td>
127- tem.msku,<br />
127+ <td>uni_source_campaign_id</td>
128- tem.ad_id,<br />
128+ <td>206</td>
129- tem.ad_group_id,<br />
129+ <td>const,zhcxkj_center_adv_amazon.p.campaign_id</td>
130- tem.campaign_id,<br />
130+ <td>1</td>
131- tem.site_hour,<br />
131+ <td>100</td>
132- tem.asin,<br />
132+ <td>None</td>
133- tem.portfolio_id,<br />
133+ </tr><tr><td>5</td>
134- MIN(tf1.currency) AS currency,<br />
134+ <td>UNION</td>
135- SUM(tf1.cost) AS cost,<br />
135+ <td>p</td>
136- SUM(tf1.impressions) AS impressions,<br />
136+ <td>None</td>
137- SUM(tf1.clicks) AS clicks<br />
137+ <td>ref</td>
138- FROM tem<br />
138+ <td>ad_id,uni_key</td>
139- JOIN t_amazon_sp_ad_traffic tf1 ON tem.ad_id = tf1.ad_id AND tem.ad_group_id = tf1.ad_group_id AND tem.campaign_id = tf1.campaign_id AND tem.site_hour = tf1.site_hour<br />
139+ <td>uni_key</td>
140- GROUP BY tem.source_id, tem.msku, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, tem.asin, tem.portfolio_id<br />
140+ <td>4</td>
141- ) t2 ON t1.campaign_id = t2.campaign_id AND t1.ad_group_id = t2.ad_group_id AND t1.ad_id = t2.ad_id AND t1.site_hour = t2.site_hour AND t1.source_id = t2.source_id AND t1.msku = t2.msku;<br />
141+ <td>const</td>
142- <br />
142+ <td>13376</td>
143- 主要优化点：<br />
143+ <td>100</td>
144- 1. 简化了临时表tem的查询，将UNION ALL操作下推到子查询中<br />
144+ <td>None</td>
145- 2. 将LEFT JOIN改为INNER JOIN（改为JOIN），因为WHERE条件已要求匹配<br />
145+ </tr><tr><td>5</td>
146- 3. 移除了不必要的COLLATE操作，假设表字符集一致<br />
146+ <td>UNION</td>
147- 4. 使用BETWEEN简化范围查询<br />
147+ <td>cv</td>
148- 5. 优化了JOIN条件，减少数据量<br />
148+ <td>None</td>
149- explain表：<br />
149+ <td>ref</td>
150- <br />
150+ <td>idx_own_hour,idx_ad_id_own_hour,idx_adid</td>
151- <br />
151+ <td>idx_adid</td>
152- <br />
152+ <td>27</td>
153- <br />
153+ <td>func,func,func</td>
154- <br />
154+ <td>236</td>
155- <br />
155+ <td>0.19</td>
156- <br />
156+ <td>Using index condition; Using where</td>
157- <br />
157+ </tr><tr><td>5</td>
158- <br />
158+ <td>UNION</td>
159- <br />
159+ <td>g</td>
160- <br />
160+ <td>None</td>
161- <br />
161+ <td>eq_ref</td>
162- <br />
162+ <td>uni_source_campaign_id</td>
163- <br />
163+ <td>uni_source_campaign_id</td>
164- <br />
164+ <td>206</td>
165- <br />
165+ <td>const,zhcxkj_center_adv_amazon.p.campaign_id</td>
166- <br />
166+ <td>1</td>
167- <br />
167+ <td>100</td>
168- <table>
168+ <td>None</td>
169- <tbody>
169+ </tr></tbody></table><br />
170- <tr>
170+ 额外建议：碎片化 SQL ，补全相关缺失索引<br /> 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")<span style="color:#FFA500;"><br /> 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);</span><br /><br /> 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：<br /> +------------------------+--------------------+---------------+---------------+<br /> | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br /> +========================+====================+===============+===============+<br /> | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |<br /> +------------------------+--------------------+---------------+---------------+<br /> | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |<br /> +------------------------+--------------------+---------------+---------------+<br /> | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |<br /> +------------------------+--------------------+---------------+---------------+<br /><br /><span style="color:#008000;font-weight:bold;">→ 【t_amazon_sp_advertisement】 表，无需添加任何索引。</span><br /><span style="color:#FFA500;"><br /> 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);</span><br /><br /> 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：<br /> +------------------------+--------------------+---------------+---------------+<br /> | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br /> +========================+====================+===============+===============+<br /> | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |<br /> +------------------------+--------------------+---------------+---------------+<br /> | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |<br /> +------------------------+--------------------+---------------+---------------+<br /> | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |<br /> +------------------------+--------------------+---------------+---------------+<br /><br /><span style="color:#008000;font-weight:bold;">→ 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。</span><br /><br /> 【t_sp_ad_product】表 【source_id】字段，索引分析：<br /> +-----------------+--------------+---------------+---------------+<br /> | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br /> +=================+==============+===============+===============+<br /> | t_sp_ad_product | uni_key | source_id | 71 |<br /> +-----------------+--------------+---------------+---------------+<br /><br /><span style="color:#008000;font-weight:bold;">→ 【t_sp_ad_campaign】 表，无需添加任何索引。</span><br /><br /><span style="color:#008000;font-weight:bold;">→ 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。</span><br /><br /> 【t_sp_ad_product】表 【source_id】字段，索引分析：<br /> +-----------------+--------------+---------------+---------------+<br /> | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br /> +=================+==============+===============+===============+<br /> | t_sp_ad_product | uni_key | source_id | 71 |<br /> +-----------------+--------------+---------------+---------------+<br /><span style="color:#FFA500;"><br /> 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);</span><br /><br /> 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：<br /> +------------------------+--------------------+---------------+---------------+<br /> | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br /> +========================+====================+===============+===============+<br /> | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |<br /> +------------------------+--------------------+---------------+---------------+<br /> | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |<br /> +------------------------+--------------------+---------------+---------------+<br /> | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |<br /> +------------------------+--------------------+---------------+---------------+<br /><br /><span style="color:#008000;font-weight:bold;">→ 【t_sp_ad_campaign】 表，无需添加任何索引。</span><br /><br /><br />
171- <th>
172- id
173- </th>
174- <th>
175- select_type
176- </th>
177- <th>
178- table
179- </th>
180- <th>
181- partitions
182- </th>
183- <th>
184- type
185- </th>
186- <th>
187- possible_keys
188- </th>
189- <th>
190- key
191- </th>
192- <th>
193- key_len
194- </th>
195- <th>
196- ref
197- </th>
198- <th>
199- rows
200- </th>
201- <th>
202- filtered
203- </th>
204- <th>
205- Extra
206- </th>
207- </tr>
208- </tbody>
209- <tbody>
210- <tr>
211- <td>
212- 1
213- </td>
214- <td>
215- PRIMARY
216- </td>
217- <td>
218- &lt;derived2&gt;
219- </td>
220- <td>
221- None
222- </td>
223- <td>
224- ALL
225- </td>
226- <td>
227- None
228- </td>
229- <td>
230- None
231- </td>
232- <td>
233- None
234- </td>
235- <td>
236- None
237- </td>
238- <td>
239- 84426
240- </td>
241- <td>
242- 100
243- </td>
244- <td>
245- None
246- </td>
247- </tr>
248- <tr>
249- <td>
250- 1
251- </td>
252- <td>
253- PRIMARY
254- </td>
255- <td>
256- &lt;derived6&gt;
257- </td>
258- <td>
259- None
260- </td>
261- <td>
262- ALL
263- </td>
264- <td>
265- None
266- </td>
267- <td>
268- None
269- </td>
270- <td>
271- None
272- </td>
273- <td>
274- None
275- </td>
276- <td>
277- 167423
278- </td>
279- <td>
280- 100
281- </td>
282- <td>
283- Using where; Using join buffer (hash join)
284- </td>
285- </tr>
286- <tr>
287- <td>
288- 6
289- </td>
290- <td>
291- DERIVED
292- </td>
293- <td>
294- &lt;derived3&gt;
295- </td>
296- <td>
297- None
298- </td>
299- <td>
300- ALL
301- </td>
302- <td>
303- None
304- </td>
305- <td>
306- None
307- </td>
308- <td>
309- None
310- </td>
311- <td>
312- None
313- </td>
314- <td>
315- 1000
316- </td>
317- <td>
318- 100
319- </td>
320- <td>
321- Using temporary
322- </td>
323- </tr>
324- <tr>
325- <td>
326- 6
327- </td>
328- <td>
329- DERIVED
330- </td>
331- <td>
332- tf1
333- </td>
334- <td>
335- None
336- </td>
337- <td>
338- ref
339- </td>
340- <td>
341- idx_site_hour,idx_ad_id_own_hour,idx_adid
342- </td>
343- <td>
344- idx_site_hour
345- </td>
346- <td>
347- 5
348- </td>
349- <td>
350- tem.site_hour
351- </td>
352- <td>
353- 167
354- </td>
355- <td>
356- 100
357- </td>
358- <td>
359- Using where
360- </td>
361- </tr>
362- <tr>
363- <td>
364- 2
365- </td>
366- <td>
367- DERIVED
368- </td>
369- <td>
370- &lt;derived3&gt;
371- </td>
372- <td>
373- None
374- </td>
375- <td>
376- ALL
377- </td>
378- <td>
379- None
380- </td>
381- <td>
382- None
383- </td>
384- <td>
385- None
386- </td>
387- <td>
388- None
389- </td>
390- <td>
391- 1000
392- </td>
393- <td>
394- 100
395- </td>
396- <td>
397- Using temporary
398- </td>
399- </tr>
400- <tr>
401- <td>
402- 2
403- </td>
404- <td>
405- DERIVED
406- </td>
407- <td>
408- cv1
409- </td>
410- <td>
411- None
412- </td>
413- <td>
414- ref
415- </td>
416- <td>
417- idx_site_hour,idx_adid
418- </td>
419- <td>
420- idx_site_hour
421- </td>
422- <td>
423- 5
424- </td>
425- <td>
426- tem.site_hour
427- </td>
428- <td>
429- 84
430- </td>
431- <td>
432- 100
433- </td>
434- <td>
435- Using where
436- </td>
437- </tr>
438- <tr>
439- <td>
440- 3
441- </td>
442- <td>
443- DERIVED
444- </td>
445- <td>
446- &lt;derived4&gt;
447- </td>
448- <td>
449- None
450- </td>
451- <td>
452- ALL
453- </td>
454- <td>
455- None
456- </td>
457- <td>
458- None
459- </td>
460- <td>
461- None
462- </td>
463- <td>
464- None
465- </td>
466- <td>
467- 5901
468- </td>
469- <td>
470- 100
471- </td>
472- <td>
473- Using temporary; Using filesort
474- </td>
475- </tr>
476- <tr>
477- <td>
478- 4
479- </td>
480- <td>
481- DERIVED
482- </td>
483- <td>
484- cv
485- </td>
486- <td>
487- None
488- </td>
489- <td>
490- range
491- </td>
492- <td>
493- idx_own_hour,idx_adid
494- </td>
495- <td>
496- idx_own_hour
497- </td>
498- <td>
499- 5
500- </td>
501- <td>
502- None
503- </td>
504- <td>
505- 860
506- </td>
507- <td>
508- 33.33
509- </td>
510- <td>
511- Using index condition; Using where
512- </td>
513- </tr>
514- <tr>
515- <td>
516- 4
517- </td>
518- <td>
519- DERIVED
520- </td>
521- <td>
522- p
523- </td>
524- <td>
525- None
526- </td>
527- <td>
528- eq_ref
529- </td>
530- <td>
531- ad_id,uni_key
532- </td>
533- <td>
534- ad_id
535- </td>
536- <td>
537- 202
538- </td>
539- <td>
540- zhcxkj_center_adv_amazon.cv.ad_id
541- </td>
542- <td>
543- 1
544- </td>
545- <td>
546- 9.24
547- </td>
548- <td>
549- Using index condition; Using where
550- </td>
551- </tr>
552- <tr>
553- <td>
554- 4
555- </td>
556- <td>
557- DERIVED
558- </td>
559- <td>
560- g
561- </td>
562- <td>
563- None
564- </td>
565- <td>
566- eq_ref
567- </td>
568- <td>
569- uni_source_campaign_id
570- </td>
571- <td>
572- uni_source_campaign_id
573- </td>
574- <td>
575- 206
576- </td>
577- <td>
578- const,zhcxkj_center_adv_amazon.p.campaign_id
579- </td>
580- <td>
581- 1
582- </td>
583- <td>
584- 100
585- </td>
586- <td>
587- None
588- </td>
589- </tr>
590- <tr>
591- <td>
592- 5
593- </td>
594- <td>
595- UNION
596- </td>
597- <td>
598- p
599- </td>
600- <td>
601- None
602- </td>
603- <td>
604- ref
605- </td>
606- <td>
607- ad_id,uni_key
608- </td>
609- <td>
610- uni_key
611- </td>
612- <td>
613- 4
614- </td>
615- <td>
616- const
617- </td>
618- <td>
619- 13376
620- </td>
621- <td>
622- 100
623- </td>
624- <td>
625- None
626- </td>
627- </tr>
628- <tr>
629- <td>
630- 5
631- </td>
632- <td>
633- UNION
634- </td>
635- <td>
636- cv
637- </td>
638- <td>
639- None
640- </td>
641- <td>
642- ref
643- </td>
644- <td>
645- idx_own_hour,idx_ad_id_own_hour,idx_adid
646- </td>
647- <td>
648- idx_adid
649- </td>
650- <td>
651- 27
652- </td>
653- <td>
654- func,func,func
655- </td>
656- <td>
657- 236
658- </td>
659- <td>
660- 0.19
661- </td>
662- <td>
663- Using index condition; Using where
664- </td>
665- </tr>
666- <tr>
667- <td>
668- 5
669- </td>
670- <td>
671- UNION
672- </td>
673- <td>
674- g
675- </td>
676- <td>
677- None
678- </td>
679- <td>
680- eq_ref
681- </td>
682- <td>
683- uni_source_campaign_id
684- </td>
685- <td>
686- uni_source_campaign_id
687- </td>
688- <td>
689- 206
690- </td>
691- <td>
692- const,zhcxkj_center_adv_amazon.p.campaign_id
693- </td>
694- <td>
695- 1
696- </td>
697- <td>
698- 100
699- </td>
700- <td>
701- None
702- </td>
703- </tr>
704- </tbody>
705- </table>
706- <br />
707- 额外建议：碎片化 SQL ，补全相关缺失索引<br />
708- 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_adv_amazon.tem doesnt exist")<span style="color:FFA500;"><br />
709- 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);</span><br />
710- <br />
711- 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：<br />
712- +------------------------+--------------------+---------------+---------------+<br />
713- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br />
714- +========================+====================+===============+===============+<br />
715- | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |<br />
716- +------------------------+--------------------+---------------+---------------+<br />
717- | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |<br />
718- +------------------------+--------------------+---------------+---------------+<br />
719- | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |<br />
720- +------------------------+--------------------+---------------+---------------+<br />
721- <br />
722- <span style="color:green;font-weight:bold;">→ 【t_amazon_sp_advertisement】 表，无需添加任何索引。</span><br />
723- <span style="color:FFA500;"><br />
724- 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);</span><br />
725- <br />
726- 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：<br />
727- +------------------------+--------------------+---------------+---------------+<br />
728- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br />
729- +========================+====================+===============+===============+<br />
730- | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |<br />
731- +------------------------+--------------------+---------------+---------------+<br />
732- | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |<br />
733- +------------------------+--------------------+---------------+---------------+<br />
734- | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |<br />
735- +------------------------+--------------------+---------------+---------------+<br />
736- <br />
737- <span style="color:green;font-weight:bold;">→ 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。</span><br />
738- <br />
739- 【t_sp_ad_product】表 【source_id】字段，索引分析：<br />
740- +-----------------+--------------+---------------+---------------+<br />
741- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br />
742- +=================+==============+===============+===============+<br />
743- | t_sp_ad_product | uni_key | source_id | 71 |<br />
744- +-----------------+--------------+---------------+---------------+<br />
745- <br />
746- <span style="color:green;font-weight:bold;">→ 【t_sp_ad_campaign】 表，无需添加任何索引。</span><br />
747- <br />
748- <span style="color:green;font-weight:bold;">→ 【t_sp_ad_product】表 【source_id】字段，索引已经存在，无需添加任何索引。</span><br />
749- <br />
750- 【t_sp_ad_product】表 【source_id】字段，索引分析：<br />
751- +-----------------+--------------+---------------+---------------+<br />
752- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br />
753- +=================+==============+===============+===============+<br />
754- | t_sp_ad_product | uni_key | source_id | 71 |<br />
755- +-----------------+--------------+---------------+---------------+<br />
756- <span style="color:FFA500;"><br />
757- 建议添加索引：ALTER TABLE t_amazon_sp_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);</span><br />
758- <br />
759- 【t_amazon_sp_ad_traffic】表 【own_hour,site_date】字段，索引分析：<br />
760- +------------------------+--------------------+---------------+---------------+<br />
761- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br />
762- +========================+====================+===============+===============+<br />
763- | t_amazon_sp_ad_traffic | idx_ad_id_own_hour | own_hour | 2457870 |<br />
764- +------------------------+--------------------+---------------+---------------+<br />
765- | t_amazon_sp_ad_traffic | idx_adid | own_hour | 2157216 |<br />
766- +------------------------+--------------------+---------------+---------------+<br />
767- | t_amazon_sp_ad_traffic | idx_own_hour | own_hour | 14491 |<br />
768- +------------------------+--------------------+---------------+---------------+<br />
769- <br />
770- <span style="color:green;font-weight:bold;">→ 【t_sp_ad_campaign】 表，无需添加任何索引。</span><br />
771- <br />
772- <br />