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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 4,176 次 × 5.18s = 6.01h

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,655,167/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,744 次 × 4.99s = 2.42h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 1424, 14967, 20840, 23428, 31438, 33659, 41741, 76448, 82289, 85454, 94521, 97122, 111028, 132215, 132976, 134270, 135375, 135932, 148128, 152496, 154367, 154658, 154767, 160012, 165146, 167744, 167912, 167916, 168734, 168787, 171238, 176454, 180044, 183346, 184105, 185196, 186592, 186776, 186778, 187587, 189342, 190832, 192660, 198085, 198152, 199486, 200259, 202741, 207621, 208428, 208440, 210291, 211902, 213395, 213572, 216285, 217095, 217991, 218477, 218934, 219334, 220376, 220645, 220888, 221180, 221479, 223198, 223585, 223600, 223644, 223848, 224244, 224324, 224348, 224454, 224607, 224987, 225473, 225857, 226289, 226788, 226800, 226973, 227277, 227587, 227989, 228241, 229262, 229411, 229423, 229670, 230162, 230200, 230203, 232141, 232173, 232175, 232971, 233110, 233286, 233938, 235638, 236215, 237290, 237298, 238898, 239499, 239600, 241801, 242164, 242794, 243079, 243331, 243756, 244121, 244181, 245216, 245327, 246106, 246622, 246666, 246803, 247334, 247747, 247951, 247976, 248469, 249342, 249911, 249928, 250075, 250139, 250167, 250811, 250821, 250875, 251220, 251575, 252151, 253057, 253291, 253341, 253487, 253728, 254659, 254766, 254813, 255042, 255890, 256361, 256533, 256634, 257302, 257754, 258062, 258146, 259169, 259192, 259641, 259936, 260212, 260767, 262058, 262443, 264515, 265927, 266520, 266919, 267854, 267879, 270037, 270131, 270183, 270577, 270799, 271063, 271345, 271838, 272714, 273514, 274993, 277296, 277929, 278168, 278207, 278383, 279176, 281224, 281320, 281369, 281773, 281804, 282352, 285920, 286200, 286626, 286751, 286783, 286884, 287787, 288736, 289239, 289304, 289702, 289752, 289838, 290263, 290421, 291354, 291822, 291883, 292186, 293040, 293253, 294370, 295013, 295114, 295790, 296061, 296149, 296565, 297242, 297333, 297997, 298526, 299233, 299784, 299840, 300603, 302474, 302499, 302513, 302702, 303033, 303039, 303953, 304633, 305009, 305010, 305258, 305764, 306122, 306212, 306256, 306307, 306312, 306495, 307131, 307177, 308136, 308778, 310296, 310564, 312080, 313224, 314620, 316215, 317070, 317450, 317584, 318634, 319730, 319932, 320969, 321378, 321515, 321694, 322076, 323434, 324076, 324230, 324284, 324929, 325598, 325763, 326257, 326644, 326738, 328558, 330693, 331117, 332452, 332543, 332653, 333380, 333583, 334138, 334176, 334402, 334884, 335309, 336236, 336498, 336684, 338143, 338342, 338905, 339281, 339412, 339857, 339930, 340531, 340671, 341303, 342195, 342280, 342319, 342423, 342747, 343010, 343475, 346926, 347332, 347470, 349687, 349738, 350082, 351280, 351712, 351741, 351746, 352005, 352013, 352526, 352689, 352811, 353668, 354154, 354317, 354441, 354494, 354701, 355077, 355389, 356776, 356849, 358218, 358474, 358548, 358838, 359170, 359710, 360340, 360776, 361073, 361649, 362526, 362584, 362624, 362703, 362710, 362808, 362948, 363996, 364001, 364057, 364547, 364907, 365109, 365277, 365439, 365770, 366268, 366500, 366774, 367626, 367720, 367880, 368191, 368250, 368401, 368702, 368868, 369149, 369386, 369945, 370150, 370412, 371361, 371478, 371527, 371662, 372146, 372381, 372480, 372944, 373395, 374089, 374438, 374583, 374679, 374682, 375417, 375567, 375713, 375731, 376602, 377373, 377377, 378246, 378249, 378690, 379251, 379696, 380156, 380628, 381201, 381373, 381799, 383808, 384225, 384542, 384862, 385682, 385705, 386700, 386996, 387168, 387860, 388269, 389116, 389176, 389399, 389435, 389580, 389655, 390196, 390306, 390792, 391308, 392520, 392558, 392651, 393173, 393727, 393765, 393881, 394501, 394540, 394598, 394630, 394704, 395410, 395481, 395542, 395754, 396094, 397659, 397687, 398278, 398601, 398753, 400047, 401137, 401521, 402598, 402862, 403057, 403203, 403475, 404268, 404594, 404988, 405732, 405814, 406188, 406349, 406431, 407433, 407909, 408589, 408883, 409334, 409501, 409742, 410169, 410955, 411495, 412598, 412613, 412815, 413299, 413601, 413790, 413956, 414156, 414158, 414630, 414969, 414972, 415229, 415253, 415

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

• 慢因：扫描行数远大于返回行数(644,034/2,049)、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,418 次 × 1.94s = 45.9min

• 代表性 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,394,280/1,131)、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,420 次 × 1.83s = 43.2min

• 代表性 SQL：

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

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

• 慢因：扫描行数远大于返回行数(2,394,280/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,424 次 × 1.38s = 32.8min

• 代表性 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,215,488/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 = ?; —— 786 次 × 1.56s = 20.4min

• 代表性 SQL：

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

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

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

• 慢因：扫描行数远大于返回行数(58,833/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,106 次 × 1.08s = 19.9min

• 代表性 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,600/30)、全索引扫描(t_carinfo_inquiry: key=brand_id, rows=706,501，缺更优过滤索引)
• 建议：补索引（按 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_20260923.json；全程只读。*