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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

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

• 代表性 SQL：

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

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

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

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

• 慢因：扫描行数远大于返回行数(3,638,423/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,230 次 × 5.04s = 3.12h

• 代表性 SQL：

select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 33415, 34243, 85447, 90015, 100660, 121879, 136232, 148615, 152796, 155199, 156828, 159171, 173836, 190202, 196936, 198348, 200895, 202003, 209315, 211259, 212156, 212632, 212986, 213317, 214201, 218440, 218915, 219593, 221707, 222287, 222368, 223092, 223172, 223423, 223930, 224013, 225033, 225458, 225537, 225748, 226338, 226352, 226904, 227017, 227472, 227677, 228023, 228285, 228490, 228520, 228621, 228706, 229390, 229530, 230510, 230811, 231540, 232195, 234352, 234958, 236032, 237249, 241605, 242347, 244075, 245791, 246106, 246803, 248625, 248765, 249791, 249911, 250545, 251307, 251366, 253910, 253985, 254604, 257641, 260898, 261730, 262721, 265212, 267385, 268699, 269396, 272695, 272990, 274658, 275024, 275352, 275389, 276091, 276392, 277929, 280274, 280724, 281369, 281804, 282054, 283842, 286751, 286974, 287542, 288159, 288602, 289613, 290279, 290318, 290973, 291206, 291742, 291822, 292571, 294118, 295336, 295651, 295747, 296276, 296899, 302297, 303602, 303646, 304611, 305518, 306307, 306512, 306939, 309747, 310002, 310210, 310645, 315849, 316628, 317023, 318752, 319049, 319875, 321180, 321770, 323632, 324351, 325096, 325763, 330270, 331450, 331892, 333024, 334138, 334962, 337515, 337649, 338555, 339145, 339763, 342586, 343428, 345008, 346196, 347031, 347087, 347718, 348020, 348463, 349569, 351059, 351388, 351678, 351746, 351779, 351979, 352438, 352699, 352745, 352942, 353080, 353085, 354009, 354603, 354651, 357225, 357693, 357700, 358218, 358838, 358935, 359069, 359743, 361783, 362428, 362703, 363093, 364671, 364674, 365166, 365439, 365586, 365984, 366042, 366126, 366577, 366965, 367159, 368099, 368431, 368675, 370404, 370757, 371004, 371160, 371662, 373125, 373819, 374252, 374400, 375643, 375756, 376301, 378448, 378666, 378755, 380745, 380797, 380839, 382197, 382677, 382742, 383423, 383545, 383784, 385591, 388975, 390281, 390863, 390923, 391197, 391598, 393322, 394724, 395211, 395754, 396653, 397701, 398614, 399160, 399304, 399555, 399620, 401709, 402117, 402621, 403082, 403306, 403493, 404705, 405031, 406262, 406670, 407621, 409333, 410056, 410146, 410655, 411149, 411295, 411502, 411713, 411853, 412849, 413308, 413432, 413951, 413982, 414117, 414459, 414558, 417087, 417121, 417909, 419635, 420258, 420612, 421216, 421311, 421320, 422018, 422427, 422739, 424283, 424817, 424885, 425401, 426491, 426628, 427491, 427667, 427896, 428904, 429555, 430083, 430109, 430175, 430396, 430510, 432357, 432451, 433171, 433203, 433242, 433252, 434698, 434751, 435364, 435716, 435764, 436085, 436668, 436881, 437160, 437465, 438190, 438373, 438599, 439261, 439466, 440296, 440593, 441061, 441876, 441941, 442178, 442248, 442297, 442509, 442618, 442671, 442883, 443135, 443208, 444337, 444521, 444562, 444681, 445045, 445400, 445429, 445640, 445661, 446745, 446794, 446878, 447291, 447913, 448062, 448369, 448661, 448682, 449599, 450198, 451235, 451926, 452998, 453014, 453119, 454190, 454683, 456797, 457613, 457713, 458039, 458335, 458443, 458738, 458796, 458832, 459355, 459775, 459973, 461701, 461905, 461999, 462426, 462934, 462939, 463050, 464379, 464475, 464756, 466182, 466340, 466504, 466522, 467098, 467122, 467406, 467459, 467631, 467799, 468403, 468715, 469039, 469250, 469352, 470099, 470847, 471080, 471229, 471565, 471893, 471940, 472060, 472255, 472961, 473645, 474485, 474606, 475113, 475146, 475958, 476622, 476663, 476759, 478509, 479246, 479972, 481883, 482229, 482559, 482784, 482958, 484226, 484553, 486187, 486393, 487401, 487746, 488472, 489172, 489191, 489282, 489285, 489830, 492633, 492866, 493088, 493640, 495546, 497172, 498598, 498706, 499515, 500598, 501027, 501120, 502536, 503795, 504337, 506536, 506682, 506803, 507411, 509020, 509275, 510058, 510841, 511026, 511060, 512991, 514921, 515419, 516918, 518966, 519610, 520953, 521877, 525076, 525112, 527157, 527208, 527279, 528490, 528522, 529012, 530679, 530872, 531820, 532056, 532466, 532571, 532869, 533485, 53

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

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

4. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,438 次 × 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 and `expire_status` = 0;

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

• 慢因：扫描行数远大于返回行数(2,392,049/1,101)、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-19 07:00:12';

• 库表：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-19 07:00:12';

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

• 慢因：扫描行数远大于返回行数(2,392,049/52)、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,427 次 × 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,213,204/1)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

7. select brand_id from t_carinfo_inquiry where is_del = ? group by brand_id; —— 1,277 次 × 1.08s = 22.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 顺序）：

• 慢因：扫描行数远大于返回行数(693,568/30)、全索引扫描(t_carinfo_inquiry: key=brand_id, rows=705,497，缺更优过滤索引)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8891`.`t_carinfo_inquiry` ADD INDEX `idx_is_del` (`is_del`);

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

• 代表性 SQL：

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

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

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

• 慢因：扫描行数远大于返回行数(69,136/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_20260919.json；全程只读。*