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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 3,553 次 × 5.57s = 5.50h

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,776,363/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,611 次 × 4.97s = 3.60h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 6010, 47887, 184105, 190807, 199331, 212986, 214018, 217330, 217999, 218287, 218320, 220469, 225730, 227987, 228408, 229241, 229262, 229390, 231350, 232018, 235678, 236375, 237070, 241217, 242930, 245791, 246365, 247976, 249085, 250014, 250875, 254344, 254659, 256533, 258502, 259149, 259586, 267854, 268899, 275696, 276817, 277490, 279605, 281224, 281804, 282443, 285709, 289319, 289507, 289613, 290335, 291002, 291875, 294118, 296669, 299142, 303603, 304607, 307003, 309793, 311254, 311324, 314699, 315143, 316131, 317569, 317923, 319049, 320068, 320192, 324351, 324929, 325763, 326275, 326967, 331892, 332172, 334569, 336401, 336421, 339792, 341989, 343341, 343467, 344543, 347718, 350818, 351993, 352394, 352689, 352911, 354494, 361695, 362808, 363689, 364643, 365439, 368584, 370144, 374091, 382657, 384268, 386099, 389176, 390026, 396064, 397472, 397659, 401275, 406253, 407098, 408153, 413556, 415336, 418271, 420585, 424749, 426196, 427136, 431974, 435848, 436881, 437812, 438389, 441061, 441275, 442104, 444019, 445279, 446538, 450122, 450974, 452613, 454356, 460156, 465693, 467098, 467572, 470464, 472477, 472942, 474019, 475115, 479031, 479641, 483668, 488100, 488364, 488949, 488968, 489334, 495453, 503533, 503695, 503869, 506083, 511613, 512725, 513584, 514300, 529573, 529994, 531241, 536051, 536980, 538745, 539188, 541828, 544480, 545074, 546272, 549040, 550385, 552788, 560964, 562955, 563429, 565879, 566062, 570026, 570340, 572978, 573359, 574370, 576415, 580966, 582919, 583075, 585450, 585926, 586079, 589920, 590978, 591061, 591228, 593642, 594328, 594790, 594961, 596582, 597506, 598106, 598435, 599005, 612138, 619403, 629257, 633484, 636501, 636595, 639700, 641111, 641276, 646206, 648933, 649983, 653473, 653673, 653999, 656219, 658646, 659099, 659772, 660063, 660359, 660885, 661326, 662245, 662510, 664162, 664187, 664427, 664783, 664851, 665834, 666031, 666218, 666234, 666436, 666926, 667760, 670844, 672480, 673459, 673791, 674945, 677065, 678006, 680697, 681235, 681919, 681999, 683972, 684401, 686289, 686317, 688020, 688493, 689068, 689740, 691748, 695711, 696538, 698676, 699772, 701699, 702133, 703028, 703946, 705094, 705472, 710545, 711376, 712224, 712830, 714847, 715051, 717694, 718426, 718799, 718983, 721707, 723743, 728056, 728480, 729326, 729607, 730023, 730231, 733027, 736532, 736830, 736972, 737899, 739561, 740080, 744454, 746135, 751695, 767508, 774764, 775281, 777365, 779862, 779987, 785786, 786135, 788405, 790047, 790731, 795226, 797700, 798754, 798839, 799371, 801372, 802508, 803311, 803528, 806671, 807048, 807604, 808444, 808958, 809487, 809696, 810406, 813921, 816936, 818615, 819497, 819557, 819797, 821248, 823186, 824740, 827000, 830308, 831885, 832992, 834430, 835916, 836357, 837673, 837848, 840674, 840956, 842402, 842829, 842838, 847400, 850852, 851032, 860768, 861268, 866254, 869447, 869723, 869768, 880351, 881898, 886454, 887723, 888967, 889904, 891841, 897031, 897851, 915267, 915957, 916125, 917636, 920951, 924075, 929341, 931825, 937177, 940831, 946946, 952695, 952863, 952984, 953001, 956246, 956357, 956466, 957980, 963309, 963473, 963966, 963998, 964865, 966347, 969137, 975554, 976166, 981121, 990248, 995098, 1006773, 1010837, 1016835, 1017676, 1020398, 1021912, 1023713, 1024471, 1030150, 1035484, 1040202, 1044479, 1047054, 1053542, 1053582, 1055226, 1058010, 1060152, 1060551, 1061411, 1076699, 1079278, 1081184, 1086044, 1089687, 1090366, 1093947, 1099064, 1101512, 1101694, 1104008, 1104124, 1106552, 1107218, 1110099, 1111749, 1114708, 1122989, 1133139, 1155772, 1170551, 1175986, 1185105, 1203391, 1204601, 1209184, 1212972, 1214738, 1246341, 1272389, 1278371, 1284966, 1288519, 1289030, 1296141, 1318324, 1324538, 1327841, 1329515, 1351536, 1414409) 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 

• 库表：t8891.t_carinfo_favorite；实例 alias：prod-8891/master
• explain：未执行（Error calling tool 'explain': (1054, "Unknown column 'sale_stop' in 'where clause'")）
• 慢因：扫描行数远大于返回行数(1,833,489/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 —— 22 次 × 277.01s = 1.69h

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

• 慢因：扫描行数远大于返回行数(654,003/2,083)、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,430 次 × 1.93s = 46.1min

• 代表性 SQL：

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

• 库表：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-22 07:20:14';

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

• 慢因：扫描行数远大于返回行数(2,409,835/67)、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,209 次 × 1.96s = 39.5min

• 代表性 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,409,835/982)、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,435 次 × 1.40s = 33.4min

• 代表性 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,233,911/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 count(*) as aggregate from v_items where item_class = ? and item_display_status = ? and new_kind_id = ?; —— 744 次 × 1.61s = 19.9min

• 代表性 SQL：

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

• 库表：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` = 10181;

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

• 慢因：扫描行数远大于返回行数(77,818/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`);

8. 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.50s = 16.8min

• 代表性 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,033,309/93)
• 建议：补索引（按 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), 新索引生效后可评估删除旧索引

逐条优化建议汇总

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

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