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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

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

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,617,485/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,300 次 × 4.98s = 3.18h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 15480, 33659, 48752, 70246, 102124, 126141, 132215, 136494, 136515, 143485, 143709, 146404, 156565, 162657, 168297, 169525, 181392, 183088, 193663, 203357, 203476, 205128, 209465, 213276, 216050, 217330, 217333, 217418, 217961, 220043, 222551, 223013, 223314, 225837, 226131, 226297, 227200, 227809, 228524, 229696, 230039, 230098, 230162, 230954, 231378, 232576, 235234, 235782, 236199, 237012, 237590, 237859, 238580, 241319, 244555, 244753, 245142, 245645, 245744, 246174, 246505, 247926, 249613, 250224, 250503, 251676, 251866, 252601, 253057, 255780, 256505, 256770, 259127, 259317, 262058, 262791, 262997, 266185, 267413, 267968, 268073, 268811, 269470, 270360, 270481, 271872, 277929, 278424, 278571, 281804, 283771, 284594, 289671, 290335, 290609, 291535, 293040, 293227, 294017, 295269, 295348, 297253, 297631, 297780, 299189, 299686, 302753, 303602, 304810, 307234, 308017, 310339, 312022, 314620, 316969, 317045, 318376, 318982, 319935, 320734, 321918, 322432, 324606, 324670, 324788, 325970, 329301, 333399, 334022, 334246, 334275, 334962, 335870, 337343, 338590, 338905, 342211, 343073, 344273, 346232, 347251, 349050, 351053, 351572, 351630, 352181, 352754, 353464, 353893, 353983, 354310, 354661, 354705, 355562, 355708, 355866, 357974, 358753, 359109, 359597, 359603, 361020, 361905, 362549, 362761, 364399, 364643, 365290, 365584, 366693, 366919, 369098, 369145, 369589, 370010, 370969, 372466, 373426, 374883, 375070, 375501, 379080, 379503, 379644, 380156, 380812, 380995, 381139, 381699, 382481, 382611, 383775, 384310, 385869, 386099, 386429, 386585, 388269, 389309, 389425, 389760, 390196, 390444, 393835, 394812, 395073, 395482, 395864, 397701, 399145, 399555, 400503, 405115, 408848, 409076, 410235, 410285, 410366, 410717, 411129, 411295, 411508, 411770, 411881, 412756, 412938, 413397, 413572, 414747, 414891, 414972, 415525, 418541, 419461, 419670, 422646, 424581, 426222, 427932, 429727, 430461, 430572, 431495, 431719, 432440, 432655, 433337, 433355, 434793, 435425, 435490, 436488, 437375, 438285, 438373, 439237, 439261, 439619, 440208, 440758, 441327, 442203, 443243, 445801, 446061, 446557, 447917, 448283, 448619, 448890, 449200, 449599, 449865, 451399, 452106, 453056, 453206, 453236, 453875, 454373, 454608, 454735, 457558, 457686, 458007, 458043, 458681, 459417, 459997, 460142, 460437, 462096, 462939, 463392, 463402, 464227, 465095, 465886, 465895, 465964, 466273, 466930, 466975, 468528, 468536, 469102, 469236, 469311, 470075, 470132, 470530, 470633, 471762, 471763, 472139, 473509, 473533, 473696, 474772, 475115, 475491, 476002, 476092, 476366, 477213, 481484, 483668, 483713, 484448, 488147, 488434, 495595, 495804, 496019, 497172, 498706, 499691, 502307, 509660, 510301, 511582, 516081, 516449, 517066, 517950, 518364, 521258, 521270, 522694, 526111, 528558, 529015, 529029, 529113, 529250, 529458, 529602, 529804, 532953, 533763, 534435, 534814, 535114, 535333, 535338, 535454, 536087, 536203, 537455, 538351, 538392, 539283, 539617, 539713, 540379, 540901, 541117, 541730, 541760, 541862, 543469, 543600, 543682, 544128, 545074, 545234, 546039, 546871, 547792, 548354, 549040, 549899, 551401, 552165, 552425, 554644, 555607, 555939, 556609, 556729, 558097, 558724, 558900, 559670, 560981, 561017, 561145, 561219, 561300, 561354, 561601, 561704, 562597, 563285, 563433, 563659, 563845, 564029, 564649, 566049, 566703, 567476, 567880, 569538, 569549, 570405, 571114, 571193, 573035, 573267, 574933, 575157, 575659, 575858, 576430, 576709, 576889, 576905, 576957, 577016, 577501, 577874, 577882, 578292, 578665, 578787, 578813, 578863, 580213, 580229, 580273, 580940, 582337, 582688, 582741, 582999, 585224, 585502, 585580, 585805, 586153, 588080, 588758, 588854, 589475, 589599, 589629, 589708, 589803, 589886, 590421, 590467, 590742, 591228, 591460, 592040, 592774, 593150, 593398, 594266, 594318, 594947, 595475, 595535, 595908, 596371, 596753, 596871, 596945, 597198, 597199, 59

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

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

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

4. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,439 次 × 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,389,134/1,008)、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.8min

• 代表性 SQL：

select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-09-14 03:59:13';

• 库表：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-14 03:59:13';

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

• 慢因：扫描行数远大于返回行数(2,389,134/65)、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,431 次 × 1.37s = 32.6min

• 代表性 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,209,797/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,084 次 × 1.17s = 21.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` = 1583 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` = 1583 and `status` >= 1 order by `r`.`sort` desc limit 100;

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

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

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

• 代表性 SQL：

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

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

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

• 慢因：扫描行数远大于返回行数(128,751/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`);

逐条优化建议汇总

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

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