MySQL 慢查询日报（线上 prod8891）2026-09-27
统计概览
| 项目 | 值 |
|---|
| 统计范围 | 2026-09-27 00:00:00 ~ 23:59:59（Asia/Shanghai） |
| 环境 | prod-8891 |
| 慢日志条数（ES mysql-slow_*） | 19,076 条（指纹聚合取样 19,076 条） |
| Top10 单条最慢 | 282.93 s（user=t8891，YCJF_t8891_25） |
| 非备份模式最大累计 | 6.05h（4173 次 × 5.22 s） |
| 备份模式（user 含 backup/databasus_backup） | 0 条、累计 0.0s（单独标注，不触发深析） |
| 监控巡检类（jmysql/proxysql_monitor 或 information_schema） | 1 条、累计 1.5s（多为 # Schema: 空导致采集端 _grokparsefailure、无库名；单独标注，不触发深析） |
| 深析模式 explain 成功率 | 7/8（88%） |
| 数据来源 | ES mysql-slow_*（@timestamp）+ MCP 只读 MySQL（describe_schema/explain） |
| 生成时间 | 2026-09-28 09:31（Asia/Shanghai） |
Top10 明细（按单条耗时）
| # | 耗时(s) | 库.表 | 行数 sent/examined | user / server | 慢因分类 |
|---|
| 1 | 282.93 | t8891.t_carinfo_coupon_activity | 2,051 / 645,045 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,045/2,051) |
| 2 | 279.96 | t8891.t_carinfo_coupon_activity | 2,052 / 645,047 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,047/2,052) |
| 3 | 279.76 | t8891.t_carinfo_coupon_activity | 2,052 / 645,056 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,056/2,052) |
| 4 | 278.98 | t8891.t_carinfo_coupon_activity | 2,052 / 645,047 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(645,047/2,052) |
| 5 | 278.74 | t8891.t_carinfo_coupon_activity | 2,052 / 645,056 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,056/2,052) |
| 6 | 277.46 | t8891.t_carinfo_coupon_activity | 2,051 / 645,045 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,045/2,051) |
| 7 | 276.42 | t8891.t_carinfo_coupon_activity | 2,052 / 645,047 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,047/2,052) |
| 8 | 276.41 | t8891.t_carinfo_coupon_activity | 2,051 / 645,291 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(645,291/2,051) |
| 9 | 275.26 | t8891.t_carinfo_coupon_activity | 2,052 / 645,047 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(645,047/2,052) |
| 10 | 273.74 | t8891.t_carinfo_coupon_activity | 2,052 / 645,047 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(645,047/2,052) |
高频慢查询模式（指纹聚合，取样 19,076 条）
按条数 Top5
| # | 模式（指纹摘要） | 条数 | 平均(s) | 累计 | server | user |
|---|
| 1 | select * from t_member_devices where (m_id = ? and hash = ?) limit ?; | 4,173 | 5.22 | 6.05h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 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_favorite.f_id = t_carinfo_search_base_kind.kind_id and sale_stop = ?) group by f_id order by count desc limit ?; | 3,136 | 5.03 | 4.38h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 3 | select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; | 1,440 | 1.94 | 46.5min | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 4 | select * from t_carinfo_inquiry_agent where expire_status = ? and expire_time <= ?; | 1,440 | 1.85 | 44.5min | YCJF_t8891_24 | t8891 |
| 5 | select max(record_time) as aggregate from t_carinfo_inquiry_call_log; | 1,425 | 1.35 | 32.1min | YCJF_t8891_24 | t8891 |
按累计耗时 Top5
| # | 模式 | 条数 | 平均(s) | 累计 | server | user |
|---|
| 1 | select * from t_member_devices where (m_id = ? and hash = ?) limit ?; | 4,173 | 5.22 | 6.05h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 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_favorite.f_id = t_carinfo_search_base_kind.kind_id and sale_stop = ?) group by f_id order by count desc limit ?; | 3,136 | 5.03 | 4.38h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 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 …（点击展开完整 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 = ? and phase = ? and c.created_at > ? where (t_carinfo_coupon_activity.type = ?) and b.payment_time is null and t_carinfo_coupon_activity.m_id > ? and c.id is null; | 24 | 272.62 | 1.82h | YCJF_t8891_25,YCJF_t8891_25_3307,YCJF_t8 | t8891 |
| 4 | select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; | 1,440 | 1.94 | 46.5min | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 5 | select * from t_carinfo_inquiry_agent where expire_status = ? and expire_time <= ?; | 1,440 | 1.85 | 44.5min | YCJF_t8891_24 | t8891 |
备份模式全天 0 条 / 0.0s，未触发深度分析（如条数环比异常翻倍再人工介入）。
触发深度分析的模式（条数>100 或 累计>30min，非备份，最多 8 个）
1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 4,173 次 × 5.22s = 6.05h
select * from `t_member_devices` where (`m_id` = 342660 and `hash` = 'b3af46e3769a5753c0255c35ba22c5f0') limit 1;
- 库表：t8891.t_member_devices；实例 alias：
prod-8891/master
- explain SQL：
select * from `t8891`.`t_member_devices` where (`m_id` = 342660 and `hash` = 'b3af46e3769a5753c0255c35ba22c5f0') limit 1;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_member_devices | ref | t_member_devices_m_id_index | t_member_devices_m_id_index | 800,289 | 10.0% |
- 慢因：扫描行数远大于返回行数(3,671,900/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 —— 3,136 次 × 5.03s = 4.38h
select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 73631, 133614, 138515, 149733, 186609, 200233, 210089, 212483, 217095, 219710, 225458, 235963, 238725, 240011, 248889, 249085, 250726, 250858, 253728, 255277, 259635, 260843, 263818, 264607, 271407, 278375, 278383, 284798, 294354, 296958, 297660, 303039, 303953, 304861, 306939, 309820, 315664, 318260, 322193, 325763, 333583, 337812, 343669, 345250, 347718, 347780, 349369, 354896, 355038, 355425, 356247, 358218, 363238, 364641, 367711, 368702, 369589, 372738, 372944, 374583, 375719, 378916, 384085, 389176, 390076, 392458, 394598, 394630, 395690, 395725, 410319, 411022, 412183, 413556, 414459, 415253, 418766, 421320, 421876, 424885, 426995, 429834, 432092, 433048, 433970, 434793, 436336, 440789, 440795, 442654, 444217, 445661, 445747, 445968, 446939, 448268, 448334, 448579, 448664, 449496, 450600, 450853, 451235, 451573, 456190, 456363, 456433, 456813, 457498, 457556, 457558, 459077, 460406, 460710, 460979, 461345, 461720, 462939, 462985, 463068, 464639, 467098, 467391, 468683, 468732, 469810, 471249, 471610, 472255, 474378, 474394, 474439, 474639, 474772, 475954, 476064, 476358, 477069, 477150, 478485, 478628, 478636, 479031, 479097, 479434, 479546, 480252, 480268, 480482, 480483, 484802, 487685, 487935, 488005, 488043, 488180, 488372, 490386, 490853, 491091, 491418, 492149, 492419, 493075, 494735, 495796, 496328, 496621, 498706, 500778, 501027, 503795, 503927, 504153, 504162, 506084, 506607, 508369, 510053, 514823, 515011, 516522, 516966, 524914, 526408, 527279, 528417, 528660, 529944, 532172, 533288, 533378, 534755, 535312, 535648, 536751, 537529, 537530, 537808, 537892, 538343, 538778, 538824, 539469, 540285, 540499, 541863, 541994, 542217, 542511, 542913, 543226, 543815, 544041, 544375, 544899, 545100, 545291, 545322, 545454, 546190, 546208, 546272, 546415, 546449, 546597, 546635, 547676, 547696, 547906, 548689, 548854, 549040, 549309, 549560, 549833, 549861, 549886, 551988, 552559, 552675, 553249, 553252, 554308, 556134, 556460, 556541, 556561, 556935, 557375, 557885, 557888, 558285, 558724, 559197, 559356, 559463, 559819, 560726, 561812, 562137, 562549, 562624, 562955, 562979, 562987, 563433, 563826, 563845, 563879, 564389, 564509, 564565, 566049, 566142, 566524, 566703, 566913, 567185, 567276, 567534, 567871, 568060, 568902, 568944, 570358, 570578, 570661, 570732, 571032, 571570, 572954, 572958, 574154, 574627, 575013, 575125, 575312, 575498, 575584, 575634, 575686, 575992, 576202, 576262, 576287, 576430, 576507, 576556, 576708, 576905, 577231, 577797, 577924, 578046, 578742, 579178, 579239, 579273, 579909, 580557, 581005, 581390, 581727, 581766, 582919, 583271, 583378, 583646, 583723, 583763, 584433, 584542, 585104, 585475, 585501, 585502, 585926, 586079, 586205, 586399, 586450, 586670, 586985, 586999, 587327, 587427, 588241, 588262, 588569, 588902, 589106, 589567, 589920, 590350, 590358, 590473, 590532, 590724, 591051, 591924, 592200, 592330, 592507, 593088, 593347, 593642, 593935, 594572, 594947, 595047, 595236, 595303, 595508, 595656, 595908, 596255, 596604, 596733, 596800, 596824, 596987, 597123, 597639, 597640, 598194, 598210, 598520, 598942, 599177, 599522, 601001, 601141, 601622, 601776, 602307, 602924, 604288, 604892, 605128, 605465, 607857, 607905, 608331, 608524, 608780, 608902, 609163, 609744, 609911, 610621, 610845, 611263, 611775, 612538, 612563, 613239, 613290, 613304, 614508, 614665, 614864, 616086, 616298, 616467, 616512, 617001, 618657, 619261, 619772, 620477, 622245, 622486, 623385, 623516, 624756, 625636, 626047, 626252, 626298, 626371, 626376, 626527, 627400, 629059, 629426, 632060, 632215, 632703, 633112, 634508, 634529, 634771, 634833, 635769, 635884, 636186, 636501, 636820, 637021, 637038, 638069, 638223, 638227, 639630, 639700, 640233, 640512, 640683, 640903, 641083, 641177, 641225, 641377, 641478, 641820, 641863, 641881, 642139, 642422, 643179, 643219, 643344, 643360, 644011, 644106, 645711, 645722, 645807, 646091, 646151,
- 库表：t8891.t_carinfo_favorite；实例 alias：
prod-8891/master
- explain：未执行（Error calling tool 'explain': (1054, "Unknown column 'sale_stop' in 'where clause'")）
- 慢因：扫描行数远大于返回行数(1,830,959/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 次 × 272.62s = 1.82h
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 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_carinfo_coupon_activity | ref | idx_type | idx_m_id,idx_type | 3,645 | 50.0% |
| 2 | b | ❌ 全表扫描 | — | — | 1,217 | 10.0% |
| 3 | c | ❌ 全表扫描 | — | — | 474,233 | 10.0% |
- 慢因：扫描行数远大于返回行数(645,291/2,052)、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,440 次 × 1.94s = 46.5min
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 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_carinfo_inquiry_agent | ❌ 全表扫描 | — | — | 1,898,611 | 1.0% |
- 慢因：扫描行数远大于返回行数(2,396,145/978)、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.85s = 44.5min
select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-09-27 14:10: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-27 14:10:11';
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_carinfo_inquiry_agent | ❌ 全表扫描 | — | — | 1,898,611 | 3.33% |
- 慢因：扫描行数远大于返回行数(2,396,145/57)、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,425 次 × 1.35s = 32.1min
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 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_carinfo_inquiry_call_log | ❌ 全表扫描 | — | — | 1,658,596 | 100.0% |
- 慢因：扫描行数远大于返回行数(2,217,644/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 t_vincheck_supervision.* from t_vincheck_supervision left join t_vincheck_supervision_info on t_vincheck_supervision_info.vincheck_supervision_id = t_vin —— 288 次 × 3.41s = 16.4min
select `t_vincheck_supervision`.* from `t_vincheck_supervision` left join `t_vincheck_supervision_info` on `t_vincheck_supervision_info`.`vincheck_supervision_id` = `t_vincheck_supervision`.`id` where `t_vincheck_supervision`.`is_exist` = 1 and `t_vincheck_supervision`.`type` = 1 and `t_vincheck_supervision_info`.`id` is null order by `t_vincheck_supervision`.`id` asc limit 500;
- 库表：t8891.t_vincheck_supervision；实例 alias：
prod-8891/master
- explain SQL：
select `t_vincheck_supervision`.* from `t8891`.`t_vincheck_supervision` left join `t8891`.`t_vincheck_supervision_info` on `t_vincheck_supervision_info`.`vincheck_supervision_id` = `t_vincheck_supervision`.`id` where `t_vincheck_supervision`.`is_exist` = 1 and `t_vincheck_supervision`.`type` = 1 and `t_vincheck_supervision_info`.`id` is null order by `t_vincheck_supervision`.`id` asc limit 500;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_vincheck_supervision | ref | is_exist | is_exist,idx_type | 495,157 | 50.0% |
| 2 | t_vincheck_supervision_info | eq_ref | idx_vincheck_supervision_id | idx_vincheck_supervision_id | 1 | 10.0% |
- 慢因：扫描行数远大于返回行数(1,003,522/61)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t8891`.`t_vincheck_supervision` ADD INDEX `idx_is_exist_type` (`is_exist`, `type`);
- DDL 注：
t_vincheck_supervision 与现有索引 is_exist(is_exist) 前缀重复(is_exist), 新索引生效后可评估删除旧索引
8. select v_items.item_id, v_items.m_id, t_sele_item_update_plan.id as plan_id, t_sele_item_update_plan.type, t_sele_item_update_setting.id, t_sele_item_update_set —— 484 次 × 1.99s = 16.0min
select `v_items`.`item_id`, `v_items`.`m_id`, `t_sele_item_update_plan`.`id` as `plan_id`, `t_sele_item_update_plan`.`type`, `t_sele_item_update_setting`.`id`, `t_sele_item_update_setting`.`time` from `v_items` inner join `t_sele_item_update_plan` on `v_items`.`id` = `t_sele_item_update_plan`.`item_id` inner join `t_sele_item_update_setting` on `t_sele_item_update_plan`.`id` = `t_sele_item_update_setting`.`plan_id` left join `t_sele_item_hidden` on `v_items`.`id` = `t_sele_item_hidden`.`item_id` and `t_sele_item_hidden`.`status` > 0 where `v_items`.`item_display_status` = 1 and `t_sele_item_update_plan`.`status` = 1 and (`t_sele_item_update_setting`.`time` between '08:23:39' and '08:24:38') and `t_sele_item_hidden`.`id` is null;
- 库表：t8891.v_items；实例 alias：
prod-8891/master
- explain SQL：
select `v_items`.`item_id`, `v_items`.`m_id`, `t_sele_item_update_plan`.`id` as `plan_id`, `t_sele_item_update_plan`.`type`, `t_sele_item_update_setting`.`id`, `t_sele_item_update_setting`.`time` from `t8891`.`v_items` inner join `t8891`.`t_sele_item_update_plan` on `v_items`.`id` = `t_sele_item_update_plan`.`item_id` inner join `t8891`.`t_sele_item_update_setting` on `t_sele_item_update_plan`.`id` = `t_sele_item_update_setting`.`plan_id` left join `t8891`.`t_sele_item_hidden` on `v_items`.`id` = `t_sele_item_hidden`.`item_id` and `t_sele_item_hidden`.`status` > 0 where `v_items`.`item_display_status` = 1 and `t_sele_item_update_plan`.`status` = 1 and (`t_sele_item_update_setting`.`time` between '08:23:39' and '08:24:38') and `t_sele_item_hidden`.`id` is null;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_sele_item_update_plan | ❌ 全表扫描 | — | PRIMARY,idx_item_id_status | 315,862 | 10.0% |
| 2 | t_sele_item_data | ref | item_id | item_id,idx_display_status_n_item_id | 1 | 5.0% |
| 3 | t_sele_item_infos | ref | item_id | item_id | 1 | 100.0% |
| 4 | t_sele_items | eq_ref | PRIMARY | PRIMARY | 1 | 100.0% |
| 5 | t_sele_item_hidden | ref | item_id | item_id | 1 | 10.0% |
| 6 | t_sele_item_update_setting | ref | idx_plan_id | idx_plan_id | 4 | 11.11% |
- 慢因：扫描行数远大于返回行数(6,265,796/595)、EXPLAIN 全表扫描/未用索引
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t8891`.`v_items` ADD INDEX `idx_id_item_displ` (`id`, `item_display_status`);
ALTER TABLE `t8891`.`t_sele_item_update_plan` ADD INDEX `idx_status` (`status`);
ALTER TABLE `t8891`.`t_sele_item_hidden` ADD INDEX `idx_item_id_status` (`item_id`, `status`);
- DDL 注：
t_sele_item_hidden 与现有索引 item_id(item_id, recordtime) 前缀重复(item_id), 新索引生效后可评估删除旧索引
逐条优化建议汇总
P 行 = 深析模式（指纹聚合，按全天累计耗时降序 P1 最大，是优化主战场）；T 行 = 单条耗时 Top3 的一次性慢 SQL（多为 bi/导出查询，治理手段通常是限流/降频/移到从库，而非建索引）。
| 编号 | 对象 | 建议 |
|---|
| P1 | t8891.t_member_devices（4,173×5.2s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_member_devices ADD INDEX idx_m_id_hash (m_id, hash); |
| P2 | t8891.t_carinfo_favorite（3,136×5.0s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_favorite ADD INDEX idx_sale_stop (sale_stop); |
| P3 | t8891.t_carinfo_coupon_activity（24×272.6s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: 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); |
| P4 | t8891.t_carinfo_inquiry_agent（1,440×1.9s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_inquiry_agent ADD INDEX idx_contact_st_expire_sta (contact_status, expire_status); |
| P5 | t8891.t_carinfo_inquiry_agent（1,440×1.9s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_inquiry_agent ADD INDEX idx_expire_sta_expire_tim (expire_status, expire_time); |
| P6 | t8891.t_carinfo_inquiry_call_log（1,425×1.4s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_inquiry_call_log ADD INDEX idx_record_tim (record_time); |
| P7 | t8891.t_vincheck_supervision（288×3.4s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_vincheck_supervision ADD INDEX idx_is_exist_type (is_exist, type); |
| P8 | t8891.v_items（484×2.0s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.v_items ADD INDEX idx_id_item_displ (id, item_display_status); ALTER TABLE t8891.t_sele_item_update_plan ADD INDEX idx_status (status); ALTER TABLE t8891.t_sele_item_hidden ADD INDEX idx_item_id_status (item_id, status); |
| T1 | Top1（同 P3 对象） 单条最慢 4.7min，user=t8891 t8891.t_carinfo_coupon_activity | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
| T2 | Top2（同 P3 对象） 单条最慢 4.7min，user=t8891 t8891.t_carinfo_coupon_activity | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
| T3 | Top3（同 P3 对象） 单条最慢 4.7min，user=t8891 t8891.t_carinfo_coupon_activity | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
*采集脚本 .agents/scripts/mysql_slow_prod_report.py，中间数据 outputs/slow_report_prod8891_20260927.json；全程只读。*