数据库：zhcxkj_center_adv_amazon
查询时间：21.6606
扫描行数：287.4
发送行数：143.7
指纹SQL: with tem as ( select DISTINCT source_id , creative_asins , portfolio_id , ad_id , ad_group_id , campaign_id , site_hour from ( select p . source_id , p . creative_asins , g . portfolio_id , p . ad_id , p . ad_group_id , p . campaign_id , cv . site_hour FROM ? p left join t_sb_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sb_advertisement 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 = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= '?' UNION all select p . source_id , p . creative_asins , g . portfolio_id , p . ad_id , p . ad_group_id , p . campaign_id , cv . site_hour FROM ? p left join t_sb_ad_campaign g on p . source_id = g . source_id and p . campaign_id = g . campaign_id left join t_amazon_sb_ad_traffic 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 = ? and cv . own_hour >= ? and cv . own_hour <= ? and cv . site_date >= '?' ) t where LENGTH ( t . creative_asins ) > ? order by ad_id , site_hour limit ? offSet ? ) select t1 . source_id , t1 . creative_asins , t1 . portfolio_id , t1 . ad_id , t1 . ad_group_id , t1 . campaign_id , t1 . site_hour , ifnull ( t1 . currency , t2 . currency ) as currency , t2 . clicks , t2 . impressions , t2 . cost , t1 . attributed_sales_14d , t1 . attributed_sales_14d_same_sku , t1 . attributed_units_ordered_14d , t1 . attributed_conversions_14d , t1 . attributed_conversions_14d_same_sku from ( select tem . source_id , tem . creative_asins , tem . portfolio_id , tem . ad_id , tem . ad_group_id , tem . campaign_id , tem . site_hour , min ( cv1 . currency ) as currency , sum ( cv1 . view_attributed_sales_14d ) as attributed_sales_14d , sum ( ? ) as attributed_sales_14d_same_sku , sum ( cv1 . view_attributed_units_ordered_14d ) as attributed_units_ordered_14d , sum ( cv1 .
真实SQL：with tem as ( select DISTINCT source_id, creative_asins,portfolio_id, ad_id, ad_group_id, campaign_id, site_hour from( select p.source_id,p.creative_asins,g.portfolio_id, p.ad_id, p.ad_group_id, p.campaign_id, cv.site_hour from t_sb_ad_product p left join t_sb_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sb_advertisement 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 and cv.own_hour >= 2025080221 and cv.own_hour <= 2025080300 and cv.site_date >= '2025-06-10' UNION all select p.source_id, p.creative_asins,g.portfolio_id, p.ad_id, p.ad_group_id, p.campaign_id, cv.site_hour from t_sb_ad_product p left join t_sb_ad_campaign g on p.source_id = g.source_id and p.campaign_id = g.campaign_id left join t_amazon_sb_ad_traffic 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 and cv.own_hour >= 2025080221 and cv.own_hour <= 2025080300 and cv.site_date >= '2025-06-10' ) t where LENGTH(t.creative_asins) >2 order by ad_id, site_hour limit 2000 offSet 0 ) select t1.source_id, t1.creative_asins, t1.portfolio_id, t1.ad_id, t1.ad_group_id, t1.campaign_id, t1.site_hour, ifnull(t1.currency, t2.currency) as currency, t2.clicks, t2.impressions, t2.cost, t1.attributed_sales_14d, t1.attributed_sales_14d_same_sku, t1.attributed_units_ordered_14d, t1.attributed_conversions_14d, t1.attributed_conversions_14d_same_sku from ( select tem.source_id, tem.creative_asins, tem.portfolio_id, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, min(cv1.currency) as currency, sum(cv1.view_attributed_sales_14d) as attributed_sales_14d, sum(0) as attributed_sales_14d_same_sku, sum(cv1.view_attributed_units_ordered_14d) as attributed_units_ordered_14d, sum(cv1.view_attributed_conversions_14d) as attributed_conversions_14d, sum(0) as attributed_conversions_14d_same_sku from tem left join t_amazon_sb_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.creative_asins, tem.portfolio_id, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour ) t1 left join ( select tem.source_id, tem.creative_asins, tem.portfolio_id, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour, min(tf1.currency) as currency, sum(tf1.clicks) as clicks, sum(tf1.impressions) as impressions, sum(tf1.cost) as cost from tem left join t_amazon_sb_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.creative_asins, tem.portfolio_id, tem.ad_id, tem.ad_group_id, tem.campaign_id, tem.site_hour ) t2 on t1.source_id=t2.source_id and t1.ad_id= t2.ad_id and t1.ad_group_id = t2.ad_group_id and t1.campaign_id=t2.campaign_id and t1.site_hour=t2.site_hour;
##无人工修改##
AISQL修改建议：SELECT t1.source_id,
t1.creative_asins,
t1.portfolio_id,
t1.ad_id,
t1.ad_group_id,
t1.campaign_id,
t1.site_hour,
IFNULL(t1.currency, t2.currency) AS currency,
t2.clicks,
t2.impressions,
t2.cost,
t1.attributed_sales_14d,
t1.attributed_sales_14d_same_sku,
t1.attributed_units_ordered_14d,
t1.attributed_conversions_14d,
t1.attributed_conversions_14d_same_sku
FROM (
SELECT p.source_id,
p.creative_asins,
g.portfolio_id,
p.ad_id,
p.ad_group_id,
p.campaign_id,
cv.site_hour,
MIN(cv.currency) AS currency,
SUM(cv.view_attributed_sales_14d) AS attributed_sales_14d,
SUM(0) AS attributed_sales_14d_same_sku,
SUM(cv.view_attributed_units_ordered_14d) AS attributed_units_ordered_14d,
SUM(cv.view_attributed_conversions_14d) AS attributed_conversions_14d,
SUM(0) AS attributed_conversions_14d_same_sku
FROM t_sb_ad_product p
JOIN t_sb_ad_campaign g ON p.source_id = g.source_id AND p.campaign_id = g.campaign_id
JOIN t_amazon_sb_advertisement 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
AND cv.own_hour BETWEEN 2025080221 AND 2025080300
AND cv.site_date >= '2025-06-10'
AND LENGTH(p.creative_asins) > 2
GROUP BY p.source_id, p.creative_asins, g.portfolio_id, p.ad_id, p.ad_group_id, p.campaign_id, cv.site_hour
) t1
LEFT JOIN (
SELECT p.source_id,
p.creative_asins,
g.portfolio_id,
p.ad_id,
p.ad_group_id,
p.campaign_id,
tf.site_hour,
MIN(tf.currency) AS currency,
SUM(tf.clicks) AS clicks,
SUM(tf.impressions) AS impressions,
SUM(tf.cost) AS cost
FROM t_sb_ad_product p
JOIN t_sb_ad_campaign g ON p.source_id = g.source_id AND p.campaign_id = g.campaign_id
JOIN t_amazon_sb_ad_traffic tf ON p.ad_id = tf.ad_id AND p.ad_group_id = tf.ad_group_id AND p.campaign_id = tf.campaign_id
WHERE p.source_id = 16549
AND tf.own_hour BETWEEN 2025080221 AND 2025080300
AND tf.site_date >= '2025-06-10'
AND LENGTH(p.creative_asins) > 2
GROUP BY p.source_id, p.creative_asins, g.portfolio_id, p.ad_id, p.ad_group_id, p.campaign_id, tf.site_hour
) t2 ON t1.source_id = t2.source_id
AND t1.ad_id = t2.ad_id
AND t1.ad_group_id = t2.ad_group_id
AND t1.campaign_id = t2.campaign_id
AND t1.site_hour = t2.site_hour
ORDER BY t1.ad_id, t1.site_hour
LIMIT 2000;
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | None | ALL | None | None | None | None | 3314 | 100 | None |
| 1 | PRIMARY | <derived6> | None | ref | <auto_key0> | <auto_key0> | 787 | t1.source_id,t1.ad_id,t1.ad_group_id,t1.campaign_id,t1.site_hour | 15 | 100 | None |
| 6 | DERIVED | <derived3> | None | ALL | None | None | None | None | 126 | 100 | Using temporary |
| 6 | DERIVED | tf1 | None | ref | idx_adid,idx_site_hour | idx_adid | 27 | tem.campaign_id,tem.ad_group_id,tem.ad_id | 416 | 100 | Using where |
| 2 | DERIVED | <derived3> | None | ALL | None | None | None | None | 126 | 100 | Using temporary |
| 2 | DERIVED | cv1 | None | ref | idx_adid,idx_site_hour | idx_site_hour | 5 | tem.site_hour | 26 | 100 | Using where |
| 3 | DERIVED | <derived4> | None | ALL | None | None | None | None | 126 | 100 | Using where; Using temporary; Using filesort |
| 4 | DERIVED | cv | None | range | idx_adid,idx_own_hour | idx_own_hour | 5 | None | 221 | 33.33 | Using index condition; Using where |
| 4 | DERIVED | p | None | eq_ref | uidx_ad_id | uidx_ad_id | 782 | const,zhcxkj_center_adv_amazon.cv.campaign_id,zhcxkj_center_adv_amazon.cv.ad_group_id,zhcxkj_center_adv_amazon.cv.ad_id | 1 | 100 | Using index condition |
| 4 | DERIVED | g | None | eq_ref | uidx_campaign_id | uidx_campaign_id | 266 | const,zhcxkj_center_adv_amazon.p.campaign_id | 1 | 100 | None |
| 5 | UNION | p | None | ref | uidx_ad_id | uidx_ad_id | 8 | const | 163 | 100 | None |
| 5 | UNION | cv | None | ref | idx_adid,idx_own_hour | idx_adid | 27 | zhcxkj_center_adv_amazon.p.campaign_id,zhcxkj_center_adv_amazon.p.ad_group_id,zhcxkj_center_adv_amazon.p.ad_id | 416 | 0.08 | Using index condition; Using where |
| 5 | UNION | g | None | eq_ref | uidx_campaign_id | uidx_campaign_id | 266 | const,zhcxkj_center_adv_amazon.p.campaign_id | 1 | 100 | None |
额外建议：
索引建议：join查询分析出错: (1146, "Table 'zhcxkj_center_adv_amazon.tem' doesn't exist")join查询分析出错: (1146, "Table 'zhcxkj_center_adv_amazon.tem' doesn't exist")join查询分析出错: (1146, "Table 'zhcxkj_center_adv_amazon.tem' doesn't exist")join查询分析出错: (1146, "Table 'zhcxkj_center_adv_amazon.tem' doesn't exist")join查询分析出错: (1146, "Table 'zhcxkj_center_adv_amazon.tem' doesn't exist")
建议添加索引：ALTER TABLE t_amazon_sb_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);
【t_amazon_sb_ad_traffic】表 【own_hour,site_date】字段，索引分析：
+------------------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+========================+==============+===============+===============+
| t_amazon_sb_ad_traffic | idx_own_hour | own_hour | 2368 |
+------------------------+--------------+---------------+---------------+
→ 【t_amazon_sb_advertisement】 表，无需添加任何索引。
建议添加索引：ALTER TABLE t_amazon_sb_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);
【t_amazon_sb_ad_traffic】表 【own_hour,site_date】字段，索引分析：
+------------------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+========================+==============+===============+===============+
| t_amazon_sb_ad_traffic | idx_own_hour | own_hour | 2368 |
+------------------------+--------------+---------------+---------------+
建议添加索引：ALTER TABLE t_sb_ad_product ADD INDEX idx_source_id_creative_asins(source_id,creative_asins);
【t_sb_ad_product】表 【source_id,creative_asins】字段，索引分析：
+-----------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+=================+==============+===============+===============+
| t_sb_ad_product | uidx_ad_id | source_id | 45 |
+-----------------+--------------+---------------+---------------+
→ 【t_sb_ad_campaign】 表，无需添加任何索引。
建议添加索引：ALTER TABLE t_sb_ad_product ADD INDEX idx_source_id_creative_asins(source_id,creative_asins);
【t_sb_ad_product】表 【source_id,creative_asins】字段，索引分析：
+-----------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+=================+==============+===============+===============+
| t_sb_ad_product | uidx_ad_id | source_id | 45 |
+-----------------+--------------+---------------+---------------+
建议添加索引：ALTER TABLE t_amazon_sb_ad_traffic ADD INDEX idx_own_hour_site_date(own_hour,site_date);
【t_amazon_sb_ad_traffic】表 【own_hour,site_date】字段，索引分析：
+------------------------+--------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+========================+==============+===============+===============+
| t_amazon_sb_ad_traffic | idx_own_hour | own_hour | 2368 |
+------------------------+--------------+---------------+---------------+
→ 【t_sb_ad_campaign】 表，无需添加任何索引。