反馈人：袁慧斌
问题1：
数据库：zhcxkj_center_adv_amazon查询时间：10.1304
扫描行数：914.98
发送行数：1
指纹SQL: select count ( DISTINCT source_id , ad_id , ad_group_id , campaign_id , site_hour ) from ( select p . source_id , p . ad_id , p . ad_group_id , p . campaign_id , cv . site_hour , p . creative_asins FROM ? p 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 . ad_id , p . ad_group_id , p . campaign_id , cv . site_hour , p . creative_asins FROM ? p 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 ) > ? ;
真实SQL：select count(DISTINCT source_id, ad_id, ad_group_id, campaign_id, site_hour) from( select p.source_id, p.ad_id, p.ad_group_id, p.campaign_id, cv.site_hour, p.creative_asins from t_sb_ad_product p 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 >= 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, cv.site_hour, p.creative_asins from t_sb_ad_product p 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 >= 2025080405 and cv.own_hour <= 2025080408 and cv.site_date >= 2025-06-10 ) t where LENGTH(t.creative_asins) >2;
无人工修改
AISQL修改建议：SELECT COUNT(*) FROM (
SELECT DISTINCT p.source_id, p.ad_id, p.ad_group_id, p.campaign_id, cv.site_hour
FROM t_sb_ad_product p
INNER 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 2025080405 AND 2025080408
AND cv.site_date >= 2025-06-10
AND LENGTH(p.creative_asins) > 2
UNION
SELECT DISTINCT p.source_id, p.ad_id, p.ad_group_id, p.campaign_id, cv.site_hour
FROM t_sb_ad_product p
INNER 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 BETWEEN 2025080405 AND 2025080408
AND cv.site_date >= 2025-06-10
AND LENGTH(p.creative_asins) > 2
) t;
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 | 434 | 100 | Using where |
| 2 | DERIVED | cv | None | range | idx_adid,idx_own_hour | idx_own_hour | 5 | None | 290 | 33.33 | Using index condition; Using where |
| 2 | 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 |
| 3 | UNION | cv | None | range | idx_own_hour,idx_adid | idx_own_hour | 5 | None | 6218 | 33.33 | Using index condition; Using where |
| 3 | UNION | p | None | ref | uidx_ad_id | uidx_ad_id | 8 | const | 163 | 0.1 | Using index condition |
额外建议：索引列使用了函数作计算：【LENGTH(t.creative_asins) >】，会导致索引失效。如果你是MySQL 8.0可以考虑创建函数索引；如果你是MySQL 5.7，你要更改你的SQL逻辑了。
索引建议：
建议添加索引：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_adid | own_hour | 121811 |
+------------------------+--------------+---------------+---------------+
| 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 |
+-----------------+--------------+---------------+---------------+
建议添加索引：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_adid | own_hour | 121811 |
+------------------------+--------------+---------------+---------------+
| 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 |
+-----------------+--------------+---------------+---------------+
问题2：
数据库：zhcxkj_center_bi
查询时间：14.7593
扫描行数：292326
发送行数：247.826
指纹SQL:
SELECT id , report_date , source_id , customer_id , parent_asin ,
child_asin , units_ordered , units_ordered_b2b ,
ordered_product_sales_amount , ordered_product_sales_currency_code ,
ordered_product_sales_b2b_amount ,
ordered_product_sales_b2b_currency_code , total_order_items ,
total_order_items_b2b , browser_sessions , browser_sessions_b2b ,
mobile_app_sessions , mobile_app_sessions_b2b , sessions , sessions_b2b ,
browser_session_percentage , browser_session_percentage_b2b ,
mobile_app_session_percentage , mobile_app_session_percentage_b2b ,
session_percentage , session_percentage_b2b , browser_page_views ,
browser_page_views_b2b , mobile_app_page_views ,
mobile_app_page_views_b2b , page_views , page_views_b2b ,
browser_page_views_percentage , browser_page_views_percentage_b2b ,
mobile_app_page_views_percentage , mobile_app_page_views_percentage_b2b ,
page_views_percentage , page_views_percentage_b2b , buy_box_percentage ,
buy_box_percentage_b2b , unit_session_percentage ,
unit_session_percentage_b2b , update_time FROM ? WHERE id in ( SELECT id
FROM ? WHERE source_id IN ( ? ) AND update_time >= ? AND update_time
<= ? ) ORDER BY id asc LIMIT ? ;
真实SQL：SELECT id,
report_date, source_id, customer_id,
parent_asin, child_asin,
units_ordered, units_ordered_b2b,
ordered_product_sales_amount,
ordered_product_sales_currency_code,
ordered_product_sales_b2b_amount,
ordered_product_sales_b2b_currency_code, total_order_items,
total_order_items_b2b, browser_sessions,
browser_sessions_b2b, mobile_app_sessions,
mobile_app_sessions_b2b, sessions, sessions_b2b,
browser_session_percentage,
browser_session_percentage_b2b,
mobile_app_session_percentage,
mobile_app_session_percentage_b2b, session_percentage,
session_percentage_b2b, browser_page_views,
browser_page_views_b2b, mobile_app_page_views,
mobile_app_page_views_b2b, page_views,
page_views_b2b, browser_page_views_percentage,
browser_page_views_percentage_b2b,
mobile_app_page_views_percentage,
mobile_app_page_views_percentage_b2b, page_views_percentage,
page_views_percentage_b2b, buy_box_percentage,
buy_box_percentage_b2b, unit_session_percentage,
unit_session_percentage_b2b, update_time
FROM t_amz_sales_and_traffic_report_asin WHERE
id in ( SELECT id FROM
t_amz_sales_and_traffic_report_asin WHERE
source_id IN ( 6153 )
AND update_time >= 2025-08-05 00:00:00
AND update_time <= 2025-08-05 23:59:59 ) ORDER BY id asc LIMIT
300;
无人工修改
AISQL修改建议：SELECT t1.id, t1.report_date, t1.source_id,
t1.customer_id, t1.parent_asin, t1.child_asin, t1.units_ordered,
t1.units_ordered_b2b, t1.ordered_product_sales_amount,
t1.ordered_product_sales_currency_code,
t1.ordered_product_sales_b2b_amount,
t1.ordered_product_sales_b2b_currency_code, t1.total_order_items,
t1.total_order_items_b2b, t1.browser_sessions, t1.browser_sessions_b2b,
t1.mobile_app_sessions, t1.mobile_app_sessions_b2b, t1.sessions,
t1.sessions_b2b, t1.browser_session_percentage,
t1.browser_session_percentage_b2b, t1.mobile_app_session_percentage,
t1.mobile_app_session_percentage_b2b, t1.session_percentage,
t1.session_percentage_b2b, t1.browser_page_views,
t1.browser_page_views_b2b, t1.mobile_app_page_views,
t1.mobile_app_page_views_b2b, t1.page_views, t1.page_views_b2b,
t1.browser_page_views_percentage, t1.browser_page_views_percentage_b2b,
t1.mobile_app_page_views_percentage,
t1.mobile_app_page_views_percentage_b2b, t1.page_views_percentage,
t1.page_views_percentage_b2b, t1.buy_box_percentage,
t1.buy_box_percentage_b2b, t1.unit_session_percentage,
t1.unit_session_percentage_b2b, t1.update_time FROM
t_amz_sales_and_traffic_report_asin t1 JOIN (SELECT id FROM
t_amz_sales_and_traffic_report_asin WHERE source_id = 6153 AND
update_time BETWEEN 2025-08-05 00:00:00 AND 2025-08-05 23:59:59 ORDER BY
id ASC LIMIT 300) t2 ON t1.id = t2.id;
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_amz_sales_and_traffic_report_asin | None | range | PRIMARY,uk_source_id_msku,idx_update_time_source_id,idx_sourceid_updatetime | idx_sourceid_updatetime | 10 | None | 1350 | 100 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | t_amz_sales_and_traffic_report_asin | None | eq_ref | PRIMARY | PRIMARY | 8 | zhcxkj_center_bi.t_amz_sales_and_traffic_report_asin.id | 1 | 100 | None |
额外建议：
索引建议：
建议添加索引：ALTER TABLE t_amz_sales_and_traffic_report_asin ADD INDEX idx_id_source_id_update_time(id,source_id,update_time);
【t_amz_sales_and_traffic_report_asin】表 【id,source_id,update_time】字段，索引分析：
+--------------------------------+--------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+================================+================================+===============+===============+
| t_amz_sales_and_traffic_report | idx_report_date_child_asin_sou | source_id | 262883712 |
| _asin | rce_id | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_sourceid_updatetime | source_id | 447185 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_sourceid_updatetime | update_time | 1255422 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_update_time_source_id | source_id | 1188044 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_update_time_source_id | update_time | 276078 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | PRIMARY | id | 260823952 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | uk_report_date_source_id_msku | source_id | 3053827 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | uk_source_id_msku | source_id | 607146 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
第一个问题SQL都是秒出的，不是慢SQL
第二个问题是因为表数据量过大，已经发通知了，后续会分表，预计下周上线