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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

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

• 代表性 SQL：

select * from `t_member_devices` where (`m_id` = 342660 and `hash` = '1d7c16359f4086113fee075f1ec83469') 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,600,739/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,753 次 × 4.95s = 2.41h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 1131, 17885, 18423, 20005, 20903, 33659, 42161, 43179, 59927, 70788, 74883, 86384, 89366, 89401, 90154, 93554, 95061, 100660, 104714, 108336, 111183, 118819, 122927, 124379, 124705, 126296, 132215, 136489, 140840, 143803, 146581, 147943, 148497, 149283, 151000, 151668, 153840, 154097, 154144, 154813, 155794, 155841, 156863, 157517, 157943, 160012, 162389, 163166, 168734, 172479, 173149, 179979, 179991, 181863, 182862, 185196, 185847, 186776, 191697, 192156, 198348, 199179, 205114, 205943, 206964, 208528, 208729, 210496, 212350, 212934, 214187, 215185, 215479, 215869, 216374, 217333, 218934, 218970, 219063, 219234, 221574, 221732, 222838, 222995, 223010, 223172, 223522, 223585, 224819, 225110, 225837, 225907, 226236, 226258, 226289, 226717, 226904, 227441, 227461, 227796, 228408, 228466, 228698, 229064, 229273, 230162, 230288, 230707, 230763, 230999, 231274, 231747, 231811, 232141, 232175, 232288, 232584, 232716, 233938, 234634, 234854, 235279, 236000, 236105, 236199, 237354, 237689, 237995, 238542, 242575, 245358, 245744, 246601, 246622, 246666, 247334, 247568, 247904, 247951, 248077, 248469, 248811, 249044, 250244, 250875, 250940, 251220, 251343, 251395, 251984, 252248, 252297, 252601, 253487, 254434, 254576, 254813, 254819, 255099, 255294, 255653, 255973, 256579, 256603, 256991, 257490, 257626, 258587, 258995, 259169, 259192, 259283, 259731, 259864, 260016, 260897, 262443, 263613, 266128, 266779, 267879, 270037, 270208, 270481, 271345, 273280, 274483, 274873, 275116, 275471, 275780, 275872, 276756, 276817, 277398, 277557, 277690, 277929, 278207, 279010, 279673, 280561, 281804, 282352, 283729, 283771, 284043, 284064, 284813, 285901, 286046, 286270, 286783, 286974, 288214, 288219, 288397, 288736, 289637, 289645, 289671, 289859, 290070, 290096, 290318, 291535, 292889, 295054, 295482, 295790, 295819, 296434, 297242, 297333, 297997, 298086, 298426, 298624, 299176, 299218, 299323, 299856, 300809, 301295, 301404, 301693, 302410, 303603, 303754, 303898, 304269, 304309, 304607, 304611, 304832, 305758, 306312, 307478, 307517, 307637, 309820, 311653, 311656, 312630, 313236, 313851, 314289, 315973, 316094, 316969, 316974, 317070, 321141, 321150, 321557, 321915, 322167, 322250, 322823, 323133, 323314, 323388, 324076, 324115, 324230, 324410, 325763, 326621, 327390, 327608, 327917, 327927, 328038, 328825, 328889, 329823, 331064, 331582, 331724, 331783, 332172, 333396, 333583, 333849, 334022, 334298, 335059, 335428, 336460, 336619, 336691, 337502, 338143, 339281, 339835, 339930, 340222, 340546, 341240, 341392, 341415, 341962, 342043, 342195, 343563, 343963, 344065, 344233, 344385, 344764, 345379, 345399, 346039, 347249, 347251, 347470, 349111, 349515, 350478, 350644, 350888, 351043, 351290, 351440, 351468, 351522, 351712, 352321, 352388, 352689, 352911, 353245, 353420, 353533, 353670, 353936, 353983, 354441, 354701, 355301, 356368, 356458, 357383, 357411, 357467, 357642, 357706, 358218, 358474, 358555, 359321, 359710, 360011, 361718, 361778, 362761, 363206, 363600, 363948, 364057, 364692, 364833, 365192, 365787, 366605, 366985, 367323, 367389, 367990, 368050, 368245, 368274, 368584, 368733, 369726, 369991, 370144, 370150, 370290, 370891, 371116, 371282, 371361, 371496, 371730, 372738, 372791, 372944, 372959, 373055, 373187, 373878, 374472, 374496, 374592, 375472, 375567, 375679, 375713, 376069, 376886, 377006, 377047, 378399, 378539, 378909, 379977, 380082, 380156, 381344, 381696, 382252, 382654, 382819, 382990, 383020, 383438, 383442, 383784, 385807, 386122, 387857, 388024, 388269, 389341, 389760, 390166, 390372, 390417, 390792, 391308, 391353, 391879, 391899, 392435, 392682, 393340, 393416, 393456, 393768, 393881, 394401, 394406, 395211, 395272, 395279, 395481, 395645, 395725, 396624, 396821, 396964, 397577, 397661, 397676, 397784, 399165, 399587, 399879, 400218, 400497, 401275, 401769, 401943, 402351, 402375, 402740, 402862, 403082, 403644, 403755, 404230, 405168, 

• 库表：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,211/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 次 × 269.40s = 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 * FROM `t8891`.`t_carinfo_coupon_activity` WHERE `type` = 4 LIMIT 10

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

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

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

• 代表性 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,387,090/1,215)、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-10 16:00:13';

• 库表：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,387,090/79)、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,420 次 × 1.31s = 31.1min

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

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

• 代表性 SQL：

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

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

SELECT * FROM `t8891`.`v_items` WHERE `item_class` = 1 LIMIT 10

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

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

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

8. 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,078 次 × 1.17s = 21.0min

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

逐条优化建议汇总

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

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