数据库：zhcxkj_center_strategy_comprehensive
查询时间：41.9807
执行次数: 27
扫描行数：2072730
发送行数：200
指纹SQL: SELECT id , promotion_id , promotion_name , promotion_type , source_id , source_name , site_id , offer_id , pf_listing_id , original_price , price , currency_id , STATUS , meli_percent , seller_percent , create_by , update_by , create_name , update_name , create_time , update_time , is_deleted , version FROM ? where source_id = ? and site_id = ? and promotion_id in ( ? ) and STATUS = ? and id > ? and promotion_type = ? limit ? ;
真实SQL：SELECT id, promotion_id, promotion_name, promotion_type, source_id, source_name, site_id, offer_id, pf_listing_id, original_price, price, currency_id, STATUS, meli_percent, seller_percent, create_by, update_by, create_name, update_name, create_time, update_time, is_deleted, version FROM t_promotion_candidate_detail_mercadolibre_2 where source_id = 16105 and site_id = MCO and promotion_id in ( P-MCO15205004 ) and STATUS = candidate and id > 41659415 and promotion_type = DEAL limit 200;
无人工修改
AISQL修改建议： 优化后的SQL语句
SELECT id, promotion_id, promotion_name, promotion_type, source_id, source_name, site_id,
offer_id, pf_listing_id, original_price, price, currency_id, STATUS, meli_percent,
seller_percent, create_by, update_by, create_name, update_name, create_time,
update_time, is_deleted, VERSION
FROM t_promotion_candidate_detail_mercadolibre_2
WHERE source_id = 16105
AND site_id = MCO
AND promotion_id = P-MCO15205004
AND STATUS = candidate
AND id > 41659415
AND promotion_type = DEAL
ORDER BY id
LIMIT 200;
优化说明：
1. 将 IN (P-MCO15205004) 改为 = P-MCO15205004，因为IN子句在只含单个值时效率较低
2. 添加了 ORDER BY id 以确保结果有序，这可以利用索引提高查询效率
3. 建议在以下列上创建复合索引：(site_id, source_id, promotion_type, STATUS, promotion_id, id)
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_promotion_candidate_detail_mercadolibre_2 | None | range | PRIMARY,idx_source_id,idx_source_site_listing_id,idx_source_promotion_status,idx_source_site_promotion,idx_promotion_id_source_id_site_id_version_is_deleted | PRIMARY | 8 | None | 3700028 | 0 | Using where |
额外建议：
索引建议：
取出表 【t_promotion_candidate_detail_mercadolibre_2】 where条件表达式 【source_id = 16105】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_2】 where条件表达式 【site_id = MCO】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_2】 where条件表达式 【STATUS = candidate】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_2】 where条件表达式 【promotion_type = DEAL】 100000 条记录，重复的数据有：【58323】 条，没有必要为该字段创建索引。
建议添加索引：ALTER TABLE t_promotion_candidate_detail_mercadolibre_2 ADD INDEX idx_promotion_id_id(promotion_id,id);
【t_promotion_candidate_detail_mercadolibre_2】表 【promotion_id,id】字段，索引分析：
+--------------------------------+--------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+================================+================================+===============+===============+
| t_promotion_candidate_detail_m | idx_promotion_id_source_id_sit | promotion_id | 7602 |
| ercadolibre_2 | e_id_version_is_deleted | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_promotion_candidate_detail_m | idx_source_promotion_status | promotion_id | 17098 |
| ercadolibre_2 | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_promotion_candidate_detail_m | idx_source_site_promotion | promotion_id | 16086 |
| ercadolibre_2 | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_promotion_candidate_detail_m | PRIMARY | id | 6842940 |
| ercadolibre_2 | | | |
+--------------------------------+--------------------------------+---------------+---------------+