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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 4,180 次 × 5.22s = 6.06h

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,646,794/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 —— 2,191 次 × 5.05s = 3.08h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 8542, 13003, 17216, 27208, 28471, 33659, 40449, 45321, 45778, 66644, 75488, 80063, 82289, 82537, 124379, 126561, 132215, 138515, 138559, 148281, 152811, 154504, 164683, 165146, 166607, 168797, 172594, 176118, 176738, 182033, 182846, 194481, 197484, 197486, 198348, 199123, 200999, 204908, 205392, 210026, 210089, 211684, 212156, 213276, 216331, 216429, 216616, 216910, 217116, 217148, 217364, 220076, 220225, 220541, 220781, 222014, 222176, 222368, 222887, 223092, 223172, 223605, 223817, 224216, 224612, 225157, 225358, 225458, 225525, 225746, 225837, 225839, 225867, 225961, 226017, 226266, 226352, 227017, 227670, 227987, 229143, 229512, 230291, 230366, 230424, 230510, 230720, 231601, 232076, 232133, 232138, 232141, 232175, 232195, 233015, 233056, 234025, 235254, 235680, 237249, 237774, 238867, 240011, 241319, 242930, 244230, 244902, 245683, 245791, 246106, 246203, 246800, 247464, 247838, 247910, 248648, 248889, 249791, 249911, 249936, 249945, 250137, 250370, 250484, 250538, 250562, 250851, 250920, 250984, 251521, 251592, 251707, 252963, 253183, 253609, 253910, 254089, 254267, 254701, 255277, 256399, 257328, 257497, 257509, 258146, 258434, 258786, 260949, 261403, 264183, 264324, 264515, 266034, 266520, 268144, 269564, 269845, 271407, 272990, 274722, 275025, 275696, 275715, 276776, 277760, 277929, 278375, 278383, 279891, 281125, 281325, 281804, 282054, 282162, 282260, 282422, 282823, 284518, 286751, 287109, 289319, 290966, 291659, 292016, 292620, 292770, 293200, 293435, 293972, 294118, 295336, 295747, 296669, 296964, 297660, 298373, 298887, 300485, 300667, 300731, 301712, 303039, 303386, 303953, 304309, 304323, 304607, 304611, 305415, 305837, 306307, 306823, 306939, 308748, 308778, 309242, 309820, 309901, 309924, 310355, 310564, 311590, 312605, 313848, 313851, 314403, 314616, 314699, 315664, 315849, 315867, 317070, 317923, 319048, 319049, 319174, 319384, 320269, 320724, 320987, 321557, 322230, 322863, 322903, 322987, 323282, 323411, 323591, 324178, 324230, 324351, 324911, 325188, 325217, 325425, 325763, 325851, 329301, 330018, 330040, 330270, 330932, 331892, 332288, 333024, 333144, 333380, 333396, 333583, 333867, 333869, 334275, 334298, 334304, 334556, 335508, 336077, 336421, 336485, 336759, 337447, 337500, 337732, 337741, 338244, 338430, 338555, 339260, 339763, 340133, 340671, 341831, 342209, 343182, 343208, 343213, 344474, 345008, 345250, 345399, 346196, 347697, 347718, 347780, 347949, 348020, 348237, 348729, 349284, 349327, 349705, 349929, 350266, 350882, 351014, 351349, 351417, 351489, 351511, 351577, 351655, 351712, 351779, 351786, 351809, 351909, 351933, 351979, 352127, 352658, 352736, 352745, 352779, 352942, 352987, 353077, 353203, 353645, 353983, 354128, 354592, 354982, 355038, 355696, 355853, 356508, 357690, 358218, 358265, 358838, 359069, 359637, 359782, 359929, 360324, 360760, 361064, 361255, 362250, 362636, 362703, 362808, 363093, 363238, 363661, 364386, 364674, 364944, 365674, 365984, 366042, 366077, 366268, 366829, 366919, 367612, 367675, 367706, 368050, 368123, 368199, 368431, 368471, 368584, 368675, 369589, 371184, 371282, 371334, 371524, 371662, 372031, 372705, 372738, 373510, 373681, 373820, 374091, 374180, 374252, 374400, 374583, 374682, 375475, 375508, 375673, 376301, 377929, 377976, 378490, 378775, 378916, 379063, 379273, 380261, 380834, 381469, 381710, 382029, 382611, 382990, 383090, 383423, 384085, 384268, 384713, 384862, 384909, 385099, 385128, 385138, 385187, 385591, 385705, 386063, 386099, 386869, 387179, 388288, 388846, 388847, 389116, 389435, 390076, 390479, 391197, 391405, 391547, 392303, 392556, 392734, 392792, 392876, 393054, 393549, 393717, 395211, 395410, 395726, 395754, 396064, 396624, 397701, 398189, 398379, 398384, 398642, 399160, 399304, 400367, 401005, 401049, 401709, 401769, 402452, 402621, 402737, 402862, 404268, 404705, 404988, 405480, 405591, 405727, 406689, 406794, 407098, 407621, 407923, 408090, 408176, 408589, 40933

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

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

4. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,437 次 × 1.94s = 46.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,393,083/973)、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.87s = 45.0min

• 代表性 SQL：

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

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

• 慢因：扫描行数远大于返回行数(2,393,083/75)、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,433 次 × 1.37s = 32.7min

• 代表性 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,214,387/1)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

7. select count(*) as aggregate from v_items where item_class = ? and item_display_status = ? and new_kind_id = ?; —— 895 次 × 1.57s = 23.4min

• 代表性 SQL：

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

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

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

• 慢因：扫描行数远大于返回行数(58,794/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; —— 1,093 次 × 1.08s = 19.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 顺序）：

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