数据库：zhcxkj_center_bi
查询时间：17.9256
执行次数: 331
扫描行数：38614.1
发送行数：1
指纹SQL: SELECT count ( ? ) FROM ( SELECT max ( report_date ) AS reportDate , source_id , child_asin FROM ? WHERE report_date >= '?' AND report_date <= '?' AND source_id = ? AND child_asin != '?' GROUP BY source_id , child_asin ) table_count ;
真实SQL：SELECT count(0) FROM (SELECT max(report_date) AS reportDate, source_id, child_asin FROM t_amz_sales_and_traffic_report_asin WHERE report_date >= '2024-02-13 00:00:00' AND report_date <= '2025-08-12 00:00:00' AND source_id = 16082 AND child_asin != '' GROUP BY source_id, child_asin) table_count;
##无人工修改##
AISQL修改建议：SELECT COUNT(*)
FROM t_amz_sales_and_traffic_report_asin
WHERE report_date BETWEEN '2024-02-13 00:00:00' AND '2025-08-12 00:00:00'
AND source_id = 16082
AND child_asin != ''
GROUP BY source_id, child_asin
HAVING MAX(report_date) = MAX(report_date);
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 | 126577 | 100 | None |
| 2 | DERIVED | t_amz_sales_and_traffic_report_asin | None | range | uk_report_date_source_id_msku,uk_source_id_msku,idx_report_date_child_asin_source_id,idx_sourceid_updatetime | uk_source_id_msku | 1027 | None | 522223 | 50 | Using index condition; Using where |
额外建议：
索引建议：
取出表 【t_amz_sales_and_traffic_report_asin】 where条件字段 【report_date】 100000 条记录，重复的数据有：【93555】 条，没有必要为该字段创建索引。
→ 【t_amz_sales_and_traffic_report_asin】表 【source_id,child_asin】字段，联合索引已经存在，无需添加任何索引。
【t_amz_sales_and_traffic_report_asin】表 【source_id,child_asin】字段，索引分析：
+--------------------------------+--------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+================================+================================+===============+===============+
| t_amz_sales_and_traffic_report | idx_report_date_child_asin_sou | child_asin | 84645720 |
| _asin | rce_id | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_report_date_child_asin_sou | source_id | 262575776 |
| _asin | rce_id | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_sourceid_updatetime | source_id | 447185 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | idx_update_time_source_id | source_id | 1188044 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | uk_report_date_source_id_msku | source_id | 3053827 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | uk_report_date_source_id_msku | child_asin | 262575776 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | uk_source_id_msku | source_id | 607146 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_amz_sales_and_traffic_report | uk_source_id_msku | child_asin | 66808268 |
| _asin | | | |
+--------------------------------+--------------------------------+---------------+---------------+
无意义的优化