反馈人：李冠
问题1：
mercadolibre慢SQL，引起策略库性能问题，慢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_3 force index(idx_promotion_id_source_id_site_id_version_is_deleted)
WHERE
source_id = 29226
AND site_id = MLM
AND promotion_id = P-MLM15307068
AND id > 278640168
AND version != 2025101600
ORDER BY
id
LIMIT 100;
性能分析为：
原SQL分析：
优化后的SQL分析：
解决方案：使用强制索引 force index(idx_promotion_id_source_id_site_id_version_is_deleted)
优化后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_3 force index(idx_promotion_id_source_id_site_id_version_is_deleted)
WHERE
source_id = 29226
AND site_id = MLM
AND promotion_id = P-MLM15307068
AND id > 278640168
AND version != 2025101600
ORDER BY
id
LIMIT 100;
问题2：
Mercadolibre平台队列明细统计-严重慢SQL，扫描2000多万数据量，严重影响策略库其他业务线，慢SQL为（太长已被截断）：
select strategy_id, sum(case when status = 4 and update_time between 2025-10-16 00:00:00 and 2025-10-16 23:59:59 then 1 else 0 end) as successTotal, sum(case when status in (3, 5, 7) and update_time between 2025-10-16 00:00:00 and 2025-10-16 23:59:59 then 1 else 0 end) as failTotal, sum(case when status = 0 then 1 else 0 end) as waitTotal from t_strategy_execute_queue_promotion_mercadolibre WHERE strategy_id in ( 4241 , 4240 , 4239 , 4235 , 4234 , 4233 , 4232 , 4231 , 4230 , 4229 , 4228 , 4168 , 4167 , 4166 , 4165 , 4048 , 4047 , 4024 , 3956 , 3812 , 3810 , 3808 , 3806 , 3739 , 3738 , 3737 , 3736 , 3735 , 3734 , 3676 , 3675 , 3674 , 3673 , 3672 , 3668 , 3317 , 3316 , 3315 , 3314 , 3313 , 3312 , 3311 , 3310 , 3309 , 3308 , 3307 , 3306 , 3305 , 3304 , 3303 , 3302 , 3301 , 3300 , 3299 , 3298 , 3124 , 3123 , 3122 , 3120 , 3119 , 3118 , 3117 , 3116 , 3115 , 3114 , 3113 , 3112 , 3111 , 3110 , 3109 , 3108 , 3107 , 3106 , 3105 , 3104 , 3103 , 3102 , 3101 , 3100 , 3099 , 3098 , 3096 , 3095 , 3094 , 3031 , 3030 , 3029 , 3028 , 3027 , 3026 , 3025 , 3024 , 3023 , 3022 , 3021 , 2991
解决方案：由于策略不是超级多，建议一个策略一次统计，不要IN
建议优化后SQL为：
SELECT
strategy_id,
sum(CASE WHEN STATUS = 4 AND update_time BETWEEN 2025-10-16 00:00:00 AND 2025-10-16 23:59:59 THEN 1 ELSE 0 END) AS successTotal,
sum(CASE WHEN STATUS IN (3, 5, 7) AND update_time BETWEEN 2025-10-16 00:00:00 AND 2025-10-16 23:59:59 THEN 1 ELSE 0 END) AS failTotal,
sum(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END) AS waitTotal
FROM
t_strategy_execute_queue_promotion_mercadolibre
WHERE
strategy_id = 4241
