MySQL 慢查询日报（线上 prod8891）2026-10-20

统计概览

Top10 明细（按单条耗时）

高频慢查询模式（指纹聚合，取样 20,249 条）

按条数 Top5

按累计耗时 Top5

备份模式全天 2 条 / 2.7s，未触发深度分析（如条数环比异常翻倍再人工介入）。

触发深度分析的模式（条数>100 或 累计>30min，非备份，最多 8 个）

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 3,419 次 × 5.56s = 5.28h

• 代表性 SQL：

select * from `t_member_devices` where (`m_id` = 342660 and `hash` = '724c89588389e7f800ab2891705ebde0') limit 1;

• 库表：t8891.t_member_devices；实例 alias：prod-8891/master
• explain SQL：

select * from `t8891`.`t_member_devices` where (`m_id` = 342660 and `hash` = '724c89588389e7f800ab2891705ebde0') limit 1;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(3,767,998/0)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_member_devices` ADD INDEX `idx_m_id_hash` (`m_id`, `hash`);

• DDL 注：t_member_devices 与现有索引 t_member_devices_m_id_index(m_id) 前缀重复(m_id), 新索引生效后可评估删除旧索引

2. select f_id, COUNT(*) count from t_carinfo_favorite where m_id IN (?...) and type = ? and exists (select * from t_carinfo_search_base_kind where t_carinfo_favor —— 2,340 次 × 5.08s = 3.30h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 178575, 186609, 189025, 198675, 213322, 219593, 229533, 240724, 246666, 254737, 255598, 281804, 282352, 304748, 320062, 321378, 321515, 324929, 329251, 336161, 347686, 351595, 353085, 358994, 364708, 365948, 365984, 368379, 381201, 385591, 388975, 392558, 395072, 408766, 414156, 418597, 419750, 419873, 420963, 425602, 429620, 431974, 434373, 434404, 434601, 437812, 438373, 442104, 443726, 458932, 461720, 462653, 466262, 467098, 468455, 468797, 471728, 473346, 473758, 503533, 518810, 527279, 538893, 542555, 542751, 550479, 564565, 565459, 570191, 580458, 581766, 591061, 610701, 638376, 641111, 655610, 668738, 829090, 882691, 900938, 943785, 1033109, 1041929, 1099555, 1361731) and `type` = 3 and exists (select * from `t_carinfo_search_base_kind` where `t_carinfo_favorite`.`f_id` = `t_carinfo_search_base_kind`.`kind_id` and `sale_stop` = 0) group by `f_id` order by `count` desc limit 6;

• 库表：t8891.t_carinfo_favorite；实例 alias：prod-8891/master
• explain SQL：

select f_id, COUNT(*) count from `t8891`.`t_carinfo_favorite` where `m_id` in (0, 178575, 186609, 189025, 198675, 213322, 219593, 229533, 240724, 246666, 254737, 255598, 281804, 282352, 304748, 320062, 321378, 321515, 324929, 329251, 336161, 347686, 351595, 353085, 358994, 364708, 365948, 365984, 368379, 381201, 385591, 388975, 392558, 395072, 408766, 414156, 418597, 419750, 419873, 420963, 425602, 429620, 431974, 434373, 434404, 434601, 437812, 438373, 442104, 443726, 458932, 461720, 462653, 466262, 467098, 468455, 468797, 471728, 473346, 473758, 503533, 518810, 527279, 538893, 542555, 542751, 550479, 564565, 565459, 570191, 580458, 581766, 591061, 610701, 638376, 641111, 655610, 668738, 829090, 882691, 900938, 943785, 1033109, 1041929, 1099555, 1361731) and `type` = 3 and exists (select * from `t8891`.`t_carinfo_search_base_kind` where `t_carinfo_favorite`.`f_id` = `t_carinfo_search_base_kind`.`kind_id` and `sale_stop` = 0) group by `f_id` order by `count` desc limit 6;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 计划标志：⚠️ filesort 排序、⚠️ 产生临时表
• 慢因：扫描行数远大于返回行数(1,833,349/6)、DISTINCT/GROUP BY 产生临时表(大范围去重/聚合)、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_favorite` ADD INDEX `idx_type_sale_stop_m_id_count` (`type`, `sale_stop`, `m_id`, `count`);

• DDL 注：t_carinfo_favorite 与现有索引 type(type) 前缀重复(type), 新索引生效后可评估删除旧索引

3. select t_carinfo_coupon_activity.* from t_carinfo_coupon_activity left join t_carinfo_coupon_activity_callback as b on t_carinfo_coupon_activity.id = b.activity —— 18 次 × 281.62s = 1.41h

• 代表性 SQL：

select t_carinfo_coupon_activity.* from `t_carinfo_coupon_activity` left join `t_carinfo_coupon_activity_callback` as `b` on `t_carinfo_coupon_activity`.`id` = `b`.`activity_item_id` left join `t_carinfo_agent_call_to_bidding` as `c` on `t_carinfo_coupon_activity`.`m_id` = `c`.`m_id` and `c`.`type` = 'callback' and `phase` = 4 and `c`.`created_at` > 't_carinfo_coupon_activity.created_at' where (`t_carinfo_coupon_activity`.`type` = 4) and `b`.`payment_time` is null and `t_carinfo_coupon_activity`.`m_id` > 0 and `c`.`id` is null;

• 库表：t8891.t_carinfo_coupon_activity；实例 alias：prod-8891/master
• explain SQL：

select t_carinfo_coupon_activity.* from `t8891`.`t_carinfo_coupon_activity` left join `t8891`.`t_carinfo_coupon_activity_callback` as `b` on `t_carinfo_coupon_activity`.`id` = `b`.`activity_item_id` left join `t8891`.`t_carinfo_agent_call_to_bidding` as `c` on `t_carinfo_coupon_activity`.`m_id` = `c`.`m_id` and `c`.`type` = 'callback' and `phase` = 4 and `c`.`created_at` > 't_carinfo_coupon_activity.created_at' where (`t_carinfo_coupon_activity`.`type` = 4) and `b`.`payment_time` is null and `t_carinfo_coupon_activity`.`m_id` > 0 and `c`.`id` is null;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(653,246/2,082)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_coupon_activity` ADD INDEX `idx_type_m_id` (`type`, `m_id`);
ALTER TABLE `t8891`.`t_carinfo_coupon_activity_callback` ADD INDEX `idx_activity_i_payment_ti` (`activity_item_id`, `payment_time`);
ALTER TABLE `t8891`.`t_carinfo_agent_call_to_bidding` ADD INDEX `idx_m_id_phase_type_created_at` (`m_id`, `phase`, `type`, `created_at`);

• DDL 注：t_carinfo_coupon_activity 与现有索引 idx_type(type) 前缀重复(type), 新索引生效后可评估删除旧索引

4. select * from t_carinfo_inquiry_agent where expire_status = ? and expire_time <= ?; —— 1,441 次 × 1.92s = 46.2min

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-10-20 14:00:13';

• 库表：t8891.t_carinfo_inquiry_agent；实例 alias：prod-8891/master
• explain SQL：

select * from `t8891`.`t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-10-20 14:00:13';

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(2,408,892/73)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_inquiry_agent` ADD INDEX `idx_expire_sta_expire_tim` (`expire_status`, `expire_time`);

5. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,154 次 × 1.97s = 37.8min

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `contact_status` = 0 and `expire_status` = 0;

• 库表：t8891.t_carinfo_inquiry_agent；实例 alias：prod-8891/master
• explain SQL：

select * from `t8891`.`t_carinfo_inquiry_agent` where `contact_status` = 0 and `expire_status` = 0;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(2,408,892/1,004)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_inquiry_agent` ADD INDEX `idx_contact_st_expire_sta` (`contact_status`, `expire_status`);

6. select max(record_time) as aggregate from t_carinfo_inquiry_call_log; —— 1,431 次 × 1.37s = 32.6min

• 代表性 SQL：

select max(`record_time`) as aggregate from `t_carinfo_inquiry_call_log`;

• 库表：t8891.t_carinfo_inquiry_call_log；实例 alias：prod-8891/master
• explain SQL：

select max(`record_time`) as aggregate from `t8891`.`t_carinfo_inquiry_call_log`;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(2,232,834/1)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_inquiry_call_log` ADD INDEX `idx_record_tim` (`record_time`);

• DDL 注：MAX/MIN 聚合: record_time 单列索引可让优化器走索引取极值, 亦可改写为 ORDER BY record_time DESC/ASC LIMIT 1

7. select t_vincheck_supervision.* from t_vincheck_supervision left join t_vincheck_supervision_info on t_vincheck_supervision_info.vincheck_supervision_id = t_vin —— 288 次 × 3.53s = 16.9min

• 代表性 SQL：

select `t_vincheck_supervision`.* from `t_vincheck_supervision` left join `t_vincheck_supervision_info` on `t_vincheck_supervision_info`.`vincheck_supervision_id` = `t_vincheck_supervision`.`id` where `t_vincheck_supervision`.`is_exist` = 1 and `t_vincheck_supervision`.`type` = 1 and `t_vincheck_supervision_info`.`id` is null order by `t_vincheck_supervision`.`id` asc limit 500;

• 库表：t8891.t_vincheck_supervision；实例 alias：prod-8891/master
• explain SQL：

select `t_vincheck_supervision`.* from `t8891`.`t_vincheck_supervision` left join `t8891`.`t_vincheck_supervision_info` on `t_vincheck_supervision_info`.`vincheck_supervision_id` = `t_vincheck_supervision`.`id` where `t_vincheck_supervision`.`is_exist` = 1 and `t_vincheck_supervision`.`type` = 1 and `t_vincheck_supervision_info`.`id` is null order by `t_vincheck_supervision`.`id` asc limit 500;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(1,030,845/92)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_vincheck_supervision` ADD INDEX `idx_is_exist_type` (`is_exist`, `type`);

• DDL 注：t_vincheck_supervision 与现有索引 is_exist(is_exist) 前缀重复(is_exist), 新索引生效后可评估删除旧索引

8. select count(*) as aggregate from v_items where item_class = ? and item_display_status = ? and new_kind_id = ?; —— 629 次 × 1.60s = 16.8min

• 代表性 SQL：

select count(*) as aggregate from `v_items` where `item_class` = 1 and `item_display_status` = 1 and `new_kind_id` = 10927;

• 库表：t8891.v_items；实例 alias：prod-8891/master
• explain SQL：

select count(*) as aggregate from `t8891`.`v_items` where `item_class` = 1 and `item_display_status` = 1 and `new_kind_id` = 10927;

• 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：

• 慢因：扫描行数远大于返回行数(77,798/1)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`v_items` ADD INDEX `idx_item_class_item_displ_new_kind_i` (`item_class`, `item_display_status`, `new_kind_id`);

逐条优化建议汇总

P 行 = 深析模式（指纹聚合，按全天累计耗时降序 P1 最大，是优化主战场）；T 行 = 单条耗时 Top3 的一次性慢 SQL（多为 bi/导出查询，治理手段通常是限流/降频/移到从库，而非建索引）。

*采集脚本 .agents/scripts/mysql_slow_prod_report.py，中间数据 outputs/slow_report_prod8891_20261020.json；全程只读。*