数据库：zhcxkj_center_strategy_comprehensive
查询时间：197.989
执行次数: 272
扫描行数：4468090
发送行数：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 ? FORCE INDEX ( idx_source_site_listing_id ) where source_id = ? and site_id = ? and promotion_id in ( ? , ? ) and STATUS = ? and id > ? and promotion_type = ? and is_deleted = ? order by id 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_1 FORCE INDEX(idx_source_site_listing_id) where source_id = 30704 and site_id = MLC and promotion_id in ( P-MLC15219004 , P-MLC15561030 ) and STATUS = candidate and id > 139530994 and promotion_type = DEAL and is_deleted = 0 order by id limit 200;
无人工修改
AISQL修改建议：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_1
WHERE source_id = 30704
AND site_id = MLC
AND promotion_id IN (P-MLC15219004, P-MLC15561030)
AND STATUS = candidate
AND id > 139530994
AND promotion_type = DEAL
AND is_deleted = 0
ORDER BY id
LIMIT 200;
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_promotion_candidate_detail_mercadolibre_1 | None | ref | idx_source_site_listing_id | idx_source_site_listing_id | 90 | const,const | 7777814 | 0.01 | Using index condition; Using where; Using filesort |
额外建议：
索引建议：
取出表 【t_promotion_candidate_detail_mercadolibre_1】 where条件表达式 【source_id = 30704】 100000 条记录，重复的数据有：【100000】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_1】 where条件表达式 【site_id = MLC】 100000 条记录，重复的数据有：【85513】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_1】 where条件表达式 【STATUS = candidate】 100000 条记录，重复的数据有：【63700】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_1】 where条件表达式 【promotion_type = DEAL】 100000 条记录，重复的数据有：【80106】 条，没有必要为该字段创建索引。
取出表 【t_promotion_candidate_detail_mercadolibre_1】 where条件表达式 【is_deleted = 0】 100000 条记录，重复的数据有：【99998】 条，没有必要为该字段创建索引。
建议添加索引：ALTER TABLE t_promotion_candidate_detail_mercadolibre_1 ADD INDEX idx_promotion_id_id(promotion_id,id);
【t_promotion_candidate_detail_mercadolibre_1】表 【promotion_id,id】字段，索引分析：
+--------------------------------+--------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+================================+================================+===============+===============+
| t_promotion_candidate_detail_m | idx_promotion_id_source_id_sit | promotion_id | 6342 |
| ercadolibre_1 | e_id_version_is_deleted | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_promotion_candidate_detail_m | idx_source_promotion_status | promotion_id | 23779 |
| ercadolibre_1 | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_promotion_candidate_detail_m | idx_source_site_promotion | promotion_id | 29778 |
| ercadolibre_1 | | | |
+--------------------------------+--------------------------------+---------------+---------------+
| t_promotion_candidate_detail_m | PRIMARY | id | 26038018 |
| ercadolibre_1 | | | |
+--------------------------------+--------------------------------+---------------+---------------+