MySQL 慢查询日报（线上 prod8891）2026-09-28

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 4,175 次 × 5.25s = 6.09h

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,676,088/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,711 次 × 5.04s = 3.79h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 11929, 12720, 33659, 36933, 43179, 47677, 51238, 56135, 60891, 66959, 68567, 74342, 74883, 86684, 89401, 90296, 94896, 103516, 104714, 108336, 109111, 109654, 111183, 118022, 121691, 123463, 124479, 130057, 136604, 137358, 146787, 150429, 150889, 151407, 151763, 154097, 156828, 157802, 159836, 159990, 165146, 170763, 172479, 179146, 179843, 180367, 181863, 182523, 185044, 186610, 186795, 187697, 188716, 190946, 191179, 191514, 191697, 193195, 195352, 197618, 197691, 198348, 199558, 202917, 203116, 203827, 205271, 206812, 206964, 207437, 207438, 209525, 213979, 216217, 217095, 220478, 220987, 222741, 226338, 226631, 227179, 227565, 228539, 229064, 229390, 229473, 229918, 230510, 232175, 236000, 236729, 236747, 236955, 237249, 242575, 242784, 243888, 246010, 246022, 246666, 246914, 246970, 249044, 249498, 249634, 252014, 254161, 255737, 259864, 263490, 267282, 269977, 270577, 276749, 277929, 278168, 278503, 278922, 281747, 283290, 283650, 284798, 284813, 286495, 287814, 288574, 288736, 290715, 292329, 294705, 295684, 298712, 298845, 299008, 302608, 303039, 303750, 305439, 305837, 306939, 307337, 308643, 309154, 309820, 312688, 313553, 313772, 313985, 314620, 315472, 315911, 316262, 321022, 322230, 324296, 325160, 325898, 327535, 327608, 328724, 329836, 331113, 331720, 332172, 334264, 334298, 334598, 335059, 336498, 337611, 337649, 337812, 339132, 343160, 343669, 344923, 346482, 347991, 348237, 349111, 349665, 350965, 351412, 352308, 354135, 355038, 356304, 357026, 358022, 358218, 358682, 358753, 358965, 359878, 360906, 361792, 363517, 364057, 364601, 364692, 365053, 365080, 370412, 370546, 371050, 371361, 372791, 373125, 374496, 374585, 375371, 375587, 377564, 378315, 379153, 379328, 380261, 381937, 382677, 385621, 387549, 388321, 388383, 388459, 389072, 389279, 389580, 389953, 390417, 391311, 392359, 392556, 395690, 395725, 396964, 397748, 397784, 399587, 401902, 402740, 403350, 403440, 403644, 405480, 406188, 406670, 406689, 408724, 409334, 410433, 412217, 412912, 412952, 413219, 413501, 413556, 413763, 414152, 414457, 415253, 415933, 416599, 417740, 420258, 420351, 421295, 421431, 421677, 421876, 425133, 425283, 426377, 426646, 428946, 429184, 430545, 432572, 432882, 433581, 433620, 433801, 434573, 434679, 436125, 436709, 437160, 437326, 438264, 438903, 439993, 441180, 441976, 442297, 442311, 443470, 443535, 444125, 444372, 444565, 444787, 445052, 445093, 445688, 446733, 446863, 446870, 447968, 448664, 449065, 449496, 451235, 452888, 453236, 453784, 454287, 454808, 455787, 455793, 455979, 457419, 457534, 457613, 461120, 461522, 461722, 462307, 462831, 462865, 463450, 464610, 466151, 466468, 467098, 467901, 467965, 467999, 468925, 469017, 470446, 470833, 471549, 471764, 471938, 472264, 474935, 474954, 475343, 476126, 477029, 479031, 479122, 480202, 480669, 481175, 482204, 483377, 484377, 485071, 485159, 485245, 487475, 487503, 488288, 488372, 488862, 489312, 489605, 489691, 490393, 490835, 491262, 491645, 492373, 493027, 493917, 494202, 494670, 495062, 496311, 496330, 496397, 496726, 497072, 498139, 498598, 498661, 499691, 500671, 503165, 503746, 503795, 503927, 504484, 505844, 506090, 506555, 506566, 506803, 507181, 507409, 508409, 509143, 509189, 510053, 510293, 510826, 513057, 513906, 514397, 514612, 514913, 516057, 516107, 517539, 517605, 517738, 517950, 518803, 518815, 520197, 520233, 522694, 522874, 524912, 525784, 525996, 526408, 528763, 528991, 529462, 529709, 531130, 531875, 532369, 532406, 533473, 533846, 534191, 535145, 535777, 536072, 537012, 537213, 537874, 537907, 538240, 538343, 538668, 539469, 540637, 540741, 540825, 541298, 542601, 542751, 543533, 544328, 544673, 544727, 544895, 545100, 545420, 545861, 546635, 546975, 547101, 547113, 547627, 547864, 548358, 548896, 549040, 549057, 549251, 549522, 549823, 550525, 550686, 551730, 552775, 552811, 552975, 552988, 553064, 554002, 554719, 555965, 556371, 556425, 556771, 556862, 557267, 557842,

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

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

• 慢因：扫描行数远大于返回行数(645,541/2,053)、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 contact_status = ? and expire_status = ?; —— 1,437 次 × 1.94s = 46.4min

• 代表性 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,396,527/885)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

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

5. select * from t_carinfo_inquiry_agent where expire_status = ? and expire_time <= ?; —— 1,440 次 × 1.89s = 45.3min

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-09-28 06:59:11';

• 库表：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-09-28 06:59:11';

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

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

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

6. select max(record_time) as aggregate from t_carinfo_inquiry_call_log; —— 1,436 次 × 1.34s = 32.0min

• 代表性 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,218,105/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 = ?; —— 709 次 × 1.58s = 18.7min

• 代表性 SQL：

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

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

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

• 慢因：扫描行数远大于返回行数(58,839/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 brand_id from t_carinfo_inquiry where is_del = ? group by brand_id; —— 972 次 × 1.09s = 17.6min

• 代表性 SQL：

select `brand_id` from `t_carinfo_inquiry` where `is_del` = 0 group by `brand_id`;

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

select `brand_id` from `t8891`.`t_carinfo_inquiry` where `is_del` = 0 group by `brand_id`;

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

• 慢因：扫描行数远大于返回行数(695,849/30)、全索引扫描(t_carinfo_inquiry: key=brand_id, rows=707,761，缺更优过滤索引)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_inquiry` ADD INDEX `idx_is_del` (`is_del`);

逐条优化建议汇总

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

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