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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 4,174 次 × 5.17s = 5.99h

• 代表性 SQL：

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

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

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

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

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

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,852 次 × 4.99s = 2.57h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 6010, 17396, 17401, 30663, 33659, 61399, 70246, 75488, 84678, 90296, 111183, 113686, 123734, 135932, 136137, 137358, 147647, 148024, 149733, 151991, 156792, 158759, 165322, 168734, 168756, 169939, 184105, 186181, 187437, 198348, 199154, 199432, 203661, 204908, 207437, 207511, 208440, 208729, 212104, 214018, 218544, 219710, 220379, 223644, 229390, 230200, 230544, 230676, 243916, 246666, 248996, 253728, 255587, 266858, 267274, 267315, 268669, 269454, 269977, 270577, 274458, 276330, 277432, 277929, 278168, 284054, 286884, 287475, 290070, 294118, 294354, 294662, 296669, 300717, 303732, 304341, 305952, 306939, 308643, 310339, 312080, 315522, 315664, 317070, 317858, 318116, 320068, 325188, 325763, 327535, 331250, 332172, 334569, 335444, 336498, 339412, 340311, 340364, 342149, 344955, 345250, 347686, 347718, 349687, 352174, 354464, 356681, 357370, 357602, 358022, 358218, 358996, 359637, 360090, 362141, 362407, 364275, 370301, 372738, 373125, 373381, 374091, 374251, 374472, 376301, 376730, 378845, 379080, 379420, 380143, 380628, 382130, 382657, 383423, 384984, 385128, 385900, 388269, 389176, 389580, 390134, 390792, 394401, 395211, 397108, 400367, 400464, 402283, 404268, 406349, 406670, 407079, 409334, 410722, 410959, 411806, 413790, 414459, 415024, 415253, 415336, 416724, 417076, 419131, 419938, 420583, 420585, 420760, 421295, 421876, 422011, 424136, 424202, 424980, 425249, 425283, 425290, 425388, 425433, 425853, 426560, 426716, 426995, 427120, 427142, 427228, 427250, 427491, 428002, 428145, 429004, 429024, 429322, 429400, 429522, 429555, 429772, 429962, 430456, 430572, 430850, 431485, 431511, 431974, 432196, 432261, 432440, 432451, 432482, 433242, 433336, 433404, 433457, 433581, 433608, 433856, 434004, 434481, 434698, 434860, 434941, 435131, 435279, 435534, 435716, 435731, 435792, 435804, 436062, 436095, 436360, 436390, 436577, 436709, 436732, 437122, 437214, 437371, 437535, 437545, 437753, 437812, 438587, 438726, 439724, 439992, 440188, 441187, 441283, 441372, 441394, 441440, 441575, 442029, 442544, 442671, 442778, 442779, 443132, 443399, 443535, 443663, 443777, 443867, 443951, 443960, 444019, 444087, 444125, 444225, 444317, 444502, 444521, 444681, 444810, 445052, 445645, 445705, 445709, 445821, 445835, 445846, 445873, 446001, 446538, 446967, 447024, 447168, 447440, 447548, 447571, 447717, 447728, 447787, 447914, 447974, 448117, 448207, 448277, 448466, 448565, 448709, 449043, 449347, 449352, 449390, 449425, 449509, 449611, 449638, 449698, 449719, 450188, 450198, 450200, 450591, 450600, 451135, 451221, 451235, 451689, 451690, 451704, 451911, 452107, 452111, 452207, 452377, 452643, 452700, 452801, 452888, 453035, 453172, 453236, 453347, 453348, 453432, 453757, 454284, 454355, 454372, 454408, 454683, 454763, 454829, 455009, 455070, 455256, 455579, 455593, 455757, 455979, 456260, 456363, 456519, 456705, 456708, 456754, 456813, 456865, 456876, 456987, 457124, 458073, 458226, 458288, 458341, 458345, 458346, 458738, 458739, 458892, 459170, 459267, 459903, 460000, 460126, 460183, 460710, 460741, 460749, 461345, 461543, 461584, 461704, 461720, 461812, 461902, 461946, 462426, 462462, 462691, 462863, 462865, 462872, 462934, 462939, 463284, 463427, 463450, 463515, 463524, 463552, 463686, 463694, 463730, 463872, 463913, 463964, 464008, 464046, 464180, 464337, 464379, 464461, 464657, 464741, 464756, 464795, 464796, 464852, 465733, 466051, 466182, 466262, 466304, 466720, 466902, 467046, 467098, 467290, 467380, 467390, 467662, 467805, 467818, 468205, 468432, 468710, 469039, 469102, 469248, 469526, 469593, 470084, 470091, 470178, 470384, 470433, 470464, 470557, 470760, 470938, 471196, 471454, 471848, 472612, 472702, 472818, 472945, 472952, 473057, 473080, 473088, 473104, 473483, 473594, 473860, 474004, 474019, 474211, 474258, 474378, 474579, 474625, 474713, 474736, 474755, 474772, 475040, 475044, 475047, 475106, 475556, 475571, 475872, 476091, 476105, 476326, 476480, 476844, 4

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

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

• 慢因：扫描行数远大于返回行数(641,238/2,043)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

4. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,439 次 × 1.94s = 46.6min

• 代表性 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,389,742/1,045)、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.82s = 43.7min

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-09-15 20:30: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-15 20:30:11';

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

• 慢因：扫描行数远大于返回行数(2,389,742/93)、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,412 次 × 1.32s = 31.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,210,469/1)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

7. select * from t_super_top_bidding inner join (select bidding_id, max(id) as sort from t_super_top_data_record group by bidding_id order by id desc) as r on r.bi —— 1,149 次 × 1.18s = 22.6min

• 代表性 SQL：

select * from `t_super_top_bidding` inner join (select bidding_id, max(id) as sort from `t_super_top_data_record` group by `bidding_id` order by `id` desc) as `r` on `r`.`bidding_id` = `t_super_top_bidding`.`id` where `t_super_top_bidding`.`period_id` = 1584 and `status` >= 1 order by `r`.`sort` desc limit 100;

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

select * from `t8891`.`t_super_top_bidding` inner join (select bidding_id, max(id) as sort from `t8891`.`t_super_top_data_record` group by `bidding_id` order by `id` desc) as `r` on `r`.`bidding_id` = `t_super_top_bidding`.`id` where `t_super_top_bidding`.`period_id` = 1584 and `status` >= 1 order by `r`.`sort` desc limit 100;

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

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

8. select brand_id from t_carinfo_inquiry where is_del = ? group by brand_id; —— 1,033 次 × 1.09s = 18.7min

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

• 慢因：扫描行数远大于返回行数(692,528/30)、全索引扫描(t_carinfo_inquiry: key=brand_id, rows=704,435，缺更优过滤索引)
• 建议：补索引（按 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_20260915.json；全程只读。*