MySQL 慢查询日报（线上 prod8891）2026-11-01

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 2,527 次 × 5.52s = 3.88h

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,818,155/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 —— 1,109 次 × 5.00s = 1.54h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 61941, 98819, 156565, 159450, 213276, 226594, 237426, 251592, 256314, 256453, 264917, 268565, 281804, 282487, 286454, 289402, 289731, 290335, 291535, 297780, 298526, 303602, 306939, 308748, 309346, 321345, 324115, 334232, 349050, 352246, 354310, 354705, 356307, 365035, 366300, 370356, 370960, 374883, 382677, 383609, 388662, 389189, 392144, 395132, 395644, 399544, 406349, 415525, 417740, 419461, 419574, 424852, 426001, 430336, 432451, 434503, 437878, 438373, 441206, 444599, 446783, 447130, 448436, 452106, 452309, 459255, 462313, 462634, 464736, 466128, 470577, 476868, 478131, 497817, 516231, 517950, 525927, 527090, 535114, 542587, 546939, 549309, 549738, 550817, 561300, 570307, 573918, 576840, 581847, 582688, 584964, 585502, 587410, 588758, 591018, 594733, 595095, 597359, 607905, 609252, 617301, 620100, 633884, 636289, 638698, 640712, 642627, 643344, 647735, 648734, 650975, 652723, 653970, 657993, 659002, 661264, 665266, 666449, 667858, 667996, 672787, 673167, 680506, 689170, 690871, 693632, 693973, 696026, 703225, 704730, 704889, 708055, 708364, 708425, 719986, 724581, 730226, 735035, 737537, 744121, 772800, 775454, 780750, 785119, 793005, 798839, 810597, 821532, 832104, 841833, 850870, 858401, 870258, 894992, 919612, 920732, 931764, 934593, 989779, 992728, 1041058, 1055086, 1061672, 1085463, 1085689, 1098650, 1099555, 1107341, 1118526, 1125334, 1134359, 1136111, 1141274, 1188942, 1191214, 1194193, 1198065, 1279051, 1320154, 1321721, 1353891, 1369857, 1413731, 1436588, 1468356, 1478717, 1499676, 1528942, 1603340) 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：未执行（Error calling tool 'explain': (1054, "Unknown column 'sale_stop' in 'where clause'")）
• 慢因：扫描行数远大于返回行数(1,834,522/6)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_favorite` ADD INDEX `idx_sale_stop` (`sale_stop`);

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 —— 14 次 × 278.95s = 1.08h

• 代表性 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 顺序）：

• 慢因：扫描行数远大于返回行数(657,321/2,092)、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 <= ?; —— 960 次 × 1.90s = 30.5min

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-11-01 11:15:09';

• 库表：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-11-01 11:15:09';

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

• 慢因：扫描行数远大于返回行数(2,414,413/55)、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 = ?; —— 865 次 × 1.94s = 28.0min

• 代表性 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,414,413/818)、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; —— 960 次 × 1.19s = 19.1min

• 代表性 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,239,058/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 —— 192 次 × 4.11s = 13.2min

• 代表性 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,042,796/23)
• 建议：补索引（按 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 v_items.item_id, v_items.m_id, t_sele_item_update_plan.id as plan_id, t_sele_item_update_plan.type, t_sele_item_update_setting.id, t_sele_item_update_set —— 333 次 × 2.04s = 11.3min

• 代表性 SQL：

select `v_items`.`item_id`, `v_items`.`m_id`, `t_sele_item_update_plan`.`id` as `plan_id`, `t_sele_item_update_plan`.`type`, `t_sele_item_update_setting`.`id`, `t_sele_item_update_setting`.`time` from `v_items` inner join `t_sele_item_update_plan` on `v_items`.`id` = `t_sele_item_update_plan`.`item_id` inner join `t_sele_item_update_setting` on `t_sele_item_update_plan`.`id` = `t_sele_item_update_setting`.`plan_id` left join `t_sele_item_hidden` on `v_items`.`id` = `t_sele_item_hidden`.`item_id` and `t_sele_item_hidden`.`status` > 0 where `v_items`.`item_display_status` = 1 and `t_sele_item_update_plan`.`status` = 1 and (`t_sele_item_update_setting`.`time` between '17:15:09' and '17:16:08') and `t_sele_item_hidden`.`id` is null;

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

select `v_items`.`item_id`, `v_items`.`m_id`, `t_sele_item_update_plan`.`id` as `plan_id`, `t_sele_item_update_plan`.`type`, `t_sele_item_update_setting`.`id`, `t_sele_item_update_setting`.`time` from `t8891`.`v_items` inner join `t8891`.`t_sele_item_update_plan` on `v_items`.`id` = `t_sele_item_update_plan`.`item_id` inner join `t8891`.`t_sele_item_update_setting` on `t_sele_item_update_plan`.`id` = `t_sele_item_update_setting`.`plan_id` left join `t8891`.`t_sele_item_hidden` on `v_items`.`id` = `t_sele_item_hidden`.`item_id` and `t_sele_item_hidden`.`status` > 0 where `v_items`.`item_display_status` = 1 and `t_sele_item_update_plan`.`status` = 1 and (`t_sele_item_update_setting`.`time` between '17:15:09' and '17:16:08') and `t_sele_item_hidden`.`id` is null;

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

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

ALTER TABLE `t8891`.`v_items` ADD INDEX `idx_id_item_displ` (`id`, `item_display_status`);
ALTER TABLE `t8891`.`t_sele_item_update_plan` ADD INDEX `idx_status` (`status`);
ALTER TABLE `t8891`.`t_sele_item_hidden` ADD INDEX `idx_item_id_status` (`item_id`, `status`);

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

逐条优化建议汇总

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

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