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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 4,183 次 × 5.11s = 5.93h

• 代表性 SQL：

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

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

SELECT * FROM `t8891`.`t_member_devices` WHERE `m_id` = 342660 LIMIT 10

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

• 慢因：扫描行数远大于返回行数(3,596,555/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,007 次 × 4.98s = 2.78h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 6010, 8857, 17346, 18423, 20005, 23428, 33659, 40921, 42655, 45778, 48565, 53141, 58558, 62938, 70788, 73071, 87544, 90723, 91360, 93806, 97122, 98409, 106570, 115262, 123177, 124379, 125700, 126156, 132215, 134549, 135311, 140840, 143663, 147647, 150085, 150889, 151399, 151679, 152065, 154662, 156828, 159201, 160758, 162580, 164433, 165122, 165974, 167926, 168412, 168718, 168734, 170482, 170637, 171379, 171681, 173897, 176738, 179473, 180044, 183346, 188783, 189252, 190730, 192156, 192784, 193060, 195984, 196607, 197586, 197605, 200132, 200741, 200999, 205472, 206031, 206812, 210089, 210620, 212067, 212156, 212164, 212483, 213622, 214328, 214347, 214981, 215208, 216429, 216678, 216930, 218074, 218440, 218733, 218878, 220076, 220987, 221262, 221641, 221654, 222014, 223293, 223379, 224013, 224216, 224369, 224819, 224943, 225458, 225837, 225961, 226202, 226254, 226307, 226338, 226499, 227017, 227036, 227254, 227476, 227613, 227623, 227831, 227987, 228297, 228982, 229241, 229378, 229390, 230200, 230291, 230366, 230424, 230455, 230510, 231275, 232138, 232141, 232195, 233286, 234025, 234083, 234121, 234899, 234906, 234958, 235309, 235333, 237995, 238867, 239568, 241319, 243888, 244265, 245107, 245791, 245896, 246622, 247789, 248648, 248749, 249911, 250387, 250851, 251753, 251891, 252434, 253193, 253341, 253379, 253494, 253910, 254082, 254089, 254358, 254659, 254819, 255598, 255861, 255863, 256886, 257084, 257497, 258803, 259682, 260315, 260670, 261106, 261753, 263689, 263783, 264324, 265051, 266034, 266919, 267274, 267312, 267337, 267467, 268699, 268885, 269454, 269977, 270558, 270725, 272706, 272990, 275043, 275696, 277055, 277509, 277929, 279176, 280107, 280145, 281224, 281225, 281369, 281747, 281804, 282031, 282054, 282260, 283027, 283046, 284686, 287109, 287697, 287730, 287985, 288397, 288736, 289103, 289319, 289507, 289752, 290167, 290318, 291016, 291367, 291396, 291659, 291742, 291875, 292571, 293040, 293053, 293117, 293200, 293435, 294118, 295336, 295370, 295734, 296087, 296501, 296669, 297295, 297401, 297744, 298299, 298836, 299092, 299413, 300061, 300657, 300667, 302129, 302337, 302474, 303222, 303646, 304274, 304309, 304924, 305439, 305518, 305837, 305952, 306288, 306307, 306939, 307349, 307582, 308242, 308676, 308748, 309115, 309618, 310210, 310355, 310369, 310645, 310768, 310845, 310944, 311798, 312358, 313169, 313718, 313851, 315664, 315757, 315849, 315867, 316976, 317070, 317662, 317923, 318074, 318574, 319012, 319049, 319174, 320192, 320195, 320637, 321238, 321299, 322230, 323133, 323551, 324178, 324351, 324829, 324929, 324975, 325228, 325425, 325610, 325763, 326675, 326965, 327535, 328090, 328568, 328943, 329067, 329629, 329836, 329983, 330040, 330932, 331116, 331724, 331892, 332172, 332760, 332825, 333024, 333138, 333147, 334138, 334275, 334298, 334304, 335126, 335595, 336354, 336485, 336592, 337447, 337502, 337649, 337963, 338143, 338195, 338430, 338555, 339026, 340133, 340671, 341630, 341831, 342292, 342447, 343073, 343288, 343849, 344072, 344233, 344260, 344273, 344474, 344557, 345140, 345399, 346218, 346257, 346669, 347087, 347249, 347352, 347539, 347697, 347768, 347884, 347949, 348361, 348747, 349705, 349874, 349929, 350214, 350287, 350509, 350693, 350789, 351059, 351356, 351417, 351577, 351678, 351712, 351761, 351786, 351909, 351923, 351933, 352084, 352275, 352321, 352522, 352553, 352573, 352589, 352593, 352658, 352736, 352745, 352747, 352813, 352842, 352879, 353077, 353095, 353156, 353264, 353285, 353477, 353673, 353747, 354100, 354124, 354179, 354209, 354407, 354441, 354494, 354504, 354603, 355708, 356154, 356341, 356354, 356393, 357234, 357389, 357687, 358170, 358218, 358253, 358425, 358617, 358696, 358838, 358965, 359030, 359112, 359395, 359583, 359673, 359682, 359782, 359830, 360058, 360340, 360445, 360773, 361038, 361064, 361259, 361534, 361695, 361783, 361828, 361831, 362011, 362161, 362428, 362440, 362566, 362572, 363093, 363095

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

SELECT * FROM `t8891`.`t_carinfo_favorite` WHERE `type` = 3 LIMIT 10

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

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

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 次 × 268.10s = 1.79h

• 代表性 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 * FROM `t8891`.`t_carinfo_coupon_activity` WHERE `type` = 4 LIMIT 10

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

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

4. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,438 次 × 1.93s = 46.2min

• 代表性 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 LIMIT 10

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

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

ALTER TABLE `t8891`.`t_carinfo_inquiry_agent` ADD INDEX `idx_contact_st` (`contact_status`);

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

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-09-08 23:50:12';

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

SELECT * FROM `t8891`.`t_carinfo_inquiry_agent` WHERE `expire_status` = 0 LIMIT 10

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

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

ALTER TABLE `t8891`.`t_carinfo_inquiry_agent` ADD INDEX `idx_expire_sta` (`expire_status`);

6. select max(record_time) as aggregate from t_carinfo_inquiry_call_log; —— 1,433 次 × 1.36s = 32.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 * FROM `t8891`.`t_carinfo_inquiry_call_log` LIMIT 10

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

• 慢因：扫描行数远大于返回行数(2,206,621/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,031 次 × 1.17s = 20.2min

• 代表性 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` = 1578 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` WHERE `period_id` = 1578 LIMIT 10

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

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

8. select count(*) as aggregate from v_items where item_class = ? and item_display_status = ? and new_kind_id = ?; —— 758 次 × 1.54s = 19.5min

• 代表性 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 * FROM `t8891`.`v_items` WHERE `item_class` = 1 LIMIT 10

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

• 慢因：扫描行数远大于返回行数(77,437/1)、索引范围扫描量过大(t_sele_item_data: key=item_class, rows=2,418,824)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`v_items` ADD INDEX `idx_item_class` (`item_class`);

逐条优化建议汇总

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

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