MySQL 慢查询日报（线上 prod8891）2026-10-25
统计概览
| 项目 | 值 |
|---|
| 统计范围 | 2026-10-25 00:00:00 ~ 23:59:59（Asia/Shanghai） |
| 环境 | prod-8891 |
| 慢日志条数（ES mysql-slow_*） | 15,796 条（指纹聚合取样 15,796 条） |
| Top10 单条最慢 | 290.42 s（user=t8891，YCJF_t8891_25_3307） |
| 非备份模式最大累计 | 5.41h（3547 次 × 5.49 s） |
| 备份模式（user 含 backup/databasus_backup） | 0 条、累计 0.0s（单独标注，不触发深析） |
| 深析模式 explain 成功率 | 7/8（88%） |
| 数据来源 | ES mysql-slow_*（@timestamp）+ MCP 只读 MySQL（describe_schema/explain） |
| 生成时间 | 2026-10-26 09:31（Asia/Shanghai） |
Top10 明细（按单条耗时）
| # | 耗时(s) | 库.表 | 行数 sent/examined | user / server | 慢因分类 |
|---|
| 1 | 290.42 | t8891.t_carinfo_coupon_activity | 2,086 / 654,729 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(654,729/2,086) |
| 2 | 286.26 | t8891.t_carinfo_coupon_activity | 2,088 / 654,745 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(654,745/2,088) |
| 3 | 286.07 | t8891.t_carinfo_coupon_activity | 2,088 / 655,066 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(655,066/2,088) |
| 4 | 284.56 | t8891.t_carinfo_coupon_activity | 2,088 / 654,753 | t8891 / YCJF_t8891_26 | 扫描行数远大于返回行数(654,753/2,088) |
| 5 | 284.15 | t8891.t_carinfo_coupon_activity | 2,088 / 654,733 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(654,733/2,088) |
| 6 | 283.84 | t8891.t_carinfo_coupon_activity | 2,086 / 654,729 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(654,729/2,086) |
| 7 | 281.30 | t8891.t_carinfo_coupon_activity | 2,088 / 654,747 | t8891 / YCJF_t8891_25_3307 | 扫描行数远大于返回行数(654,747/2,088) |
| 8 | 280.51 | t8891.t_carinfo_coupon_activity | 2,088 / 654,753 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(654,753/2,088) |
| 9 | 280.13 | t8891.t_carinfo_coupon_activity | 2,086 / 654,729 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(654,729/2,086) |
| 10 | 278.29 | t8891.t_carinfo_coupon_activity | 2,087 / 654,731 | t8891 / YCJF_t8891_25 | 扫描行数远大于返回行数(654,731/2,087) |
高频慢查询模式（指纹聚合，取样 15,796 条）
按条数 Top5
| # | 模式（指纹摘要） | 条数 | 平均(s) | 累计 | server | user |
|---|
| 1 | select * from t_member_devices where (m_id = ? and hash = ?) limit ?; | 3,547 | 5.49 | 5.41h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 2 | select max(record_time) as aggregate from t_carinfo_inquiry_call_log; | 1,440 | 1.42 | 34.1min | YCJF_t8891_24 | t8891 |
| 3 | select * from t_carinfo_inquiry_agent where expire_status = ? and expire_time <= ?; | 1,440 | 1.99 | 47.7min | YCJF_t8891_24 | t8891 |
| 4 | select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; | 1,227 | 1.94 | 39.6min | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 5 | 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 ?; | 1,027 | 5.00 | 1.43h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
按累计耗时 Top5
| # | 模式 | 条数 | 平均(s) | 累计 | server | user |
|---|
| 1 | select * from t_member_devices where (m_id = ? and hash = ?) limit ?; | 3,547 | 5.49 | 5.41h | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
| 2 | 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; | 21 | 278.19 | 1.62h | YCJF_t8891_25,YCJF_t8891_25_3307,YCJF_t8 | t8891 |
| 3 | 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 ?; | 1,027 | 5.00 | 1.43h | 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.99 | 47.7min | YCJF_t8891_24 | t8891 |
| 5 | select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; | 1,227 | 1.94 | 39.6min | YCJF_t8891_24,YCJF_t8891_25,YCJF_t8891_2 | t8891 |
备份模式全天 0 条 / 0.0s，未触发深度分析（如条数环比异常翻倍再人工介入）。
触发深度分析的模式（条数>100 或 累计>30min，非备份，最多 8 个）
1. select * from t_member_devices where (m_id = ? and hash = ?) limit ?; —— 3,547 次 × 5.49s = 5.41h
select * from `t_member_devices` where (`m_id` = 342660 and `hash` = '3b3bd3f043dff17c175fd987458bd770') limit 1;
- 库表：t8891.t_member_devices；实例 alias：
prod-8891/master
- explain SQL：
select * from `t8891`.`t_member_devices` where (`m_id` = 342660 and `hash` = '3b3bd3f043dff17c175fd987458bd770') limit 1;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_member_devices | ref | t_member_devices_m_id_index | t_member_devices_m_id_index | 800,293 | 10.0% |
- 慢因：扫描行数远大于返回行数(3,788,911/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 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 —— 21 次 × 278.19s = 1.62h
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,707 | 50.0% |
| 2 | b | ❌ 全表扫描 | — | — | 1,217 | 10.0% |
| 3 | c | ❌ 全表扫描 | — | — | 474,233 | 10.0% |
- 慢因：扫描行数远大于返回行数(655,066/2,088)、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), 新索引生效后可评估删除旧索引
3. 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,027 次 × 5.00s = 1.43h
select f_id, COUNT(*) count from `t_carinfo_favorite` where `m_id` in (0, 61941, 116611, 143326, 150889, 156565, 158990, 176675, 183885, 196743, 196775, 198348, 199040, 213276, 216517, 222657, 223817, 225458, 226278, 226297, 226338, 233643, 235855, 238397, 247721, 247816, 248040, 249634, 250503, 250516, 250527, 253065, 254267, 255277, 260320, 261019, 261730, 262428, 267404, 272671, 277929, 280864, 281804, 290335, 290609, 290789, 292257, 293040, 293388, 300416, 303039, 303602, 303643, 306939, 311164, 314379, 315650, 316535, 317070, 317923, 321307, 322273, 323474, 324010, 324230, 325336, 326466, 326537, 331380, 333380, 335711, 337343, 339475, 340795, 343121, 344273, 345208, 349050, 350967, 351857, 352425, 352754, 352905, 353027, 353938, 353983, 354310, 354352, 354705, 354980, 355819, 356051, 357686, 358218, 359597, 359603, 359929, 360931, 360933, 361064, 362703, 362996, 363606, 364057, 364357, 364964, 368431, 368845, 369416, 369589, 369667, 371721, 372466, 372738, 373395, 375475, 375551, 378130, 378775, 379176, 379420, 380423, 383423, 384609, 387677, 391308, 392299, 392313, 395073, 395482, 398379, 402898, 403816, 405721, 406903, 407416, 408165, 411059, 411093, 412712, 415525, 415564, 417121, 417347, 417746, 418541, 420533, 420719, 421520, 421763, 421826, 422686, 426222, 427932, 431897, 432124, 434698, 435092, 435163, 435605, 437380, 438373, 439731, 441327, 441677, 442053, 442061, 443135, 444217, 446319, 446737, 447904, 449999, 450447, 450502, 451717, 451995, 452106, 452731, 453262, 453723, 454608, 454735, 455513, 455889, 457605, 457837, 458103, 458780, 459217, 461398, 462645, 462939, 464154, 464180, 465295, 465411, 465886, 466346, 467409, 467505, 467571, 467832, 467873, 467974, 468421, 469519, 469938, 470231, 471525, 471610, 473259, 473533, 474132, 474812, 475254, 475767, 475958, 478179, 478541, 478806, 479611, 480768, 483668, 493429, 500004, 501757, 502180, 502483, 503454, 503773, 503927, 504227, 504721, 506854, 507515, 507854, 511129, 512259, 517950, 518589, 518757, 520230, 522197, 524430, 524445, 524658, 527090, 527867, 529029, 529709, 530144, 530539, 530755, 531358, 536735, 536905, 537798, 537802, 538343, 538517, 538534, 540145, 540988, 542790, 543116, 543487, 543682, 543992, 544660, 545103, 545939, 546039, 546941, 547662, 547823, 549048, 549309, 549899, 550162, 550348, 550669, 550817, 551321, 552923, 553439, 554020, 555387, 556215, 556609, 559197, 559631, 559819, 560952, 561300, 562803, 563528, 563845, 565136, 565449, 565636, 566427, 567034, 569905, 570538, 570987, 572289, 572985, 573293, 573773, 575429, 576503, 576515, 576715, 576957, 579450, 580273, 581682, 582522, 582688, 583506, 585224, 585450, 586280, 586405, 586718, 588758, 588889, 589475, 589597, 590198, 590607, 591018, 591228, 591229, 591517, 592096, 592828, 594947, 597003, 597274, 597292, 597340, 597427, 597506, 597903, 598413, 600687, 600903, 601554, 602658, 605047, 605597, 605715, 610826, 610977, 611437, 611671, 613246, 613445, 614526, 616998, 620178, 623967, 625519, 625772, 626751, 626765, 627118, 627404, 627519, 628214, 629031, 629135, 629940, 632129, 632956, 633408, 633913, 634418, 636261, 636501, 636822, 636831, 636927, 638342, 638831, 638874, 639073, 639090, 639673, 640383, 641536, 641698, 642388, 642647, 642765, 642905, 643499, 644067, 644338, 644934, 646500, 646633, 646667, 646745, 647735, 647763, 647910, 648252, 648600, 648644, 650387, 650975, 651780, 652024, 652394, 652550, 652770, 653970, 655257, 655741, 655962, 656020, 657411, 657550, 657911, 658228, 659292, 660019, 660795, 661264, 661524, 661820, 661889, 662275, 662800, 663324, 663601, 664085, 664140, 664519, 664639, 665808, 666175, 667046, 668431, 668911, 671460, 671950, 672307, 672649, 673563, 674666, 674793, 675754, 675777, 676164, 677799, 678539, 678752, 678941, 678966, 679091, 680283, 680832, 681370, 681833, 682230, 682292, 682513, 683577, 683830, 688185, 688257, 689662, 689680, 689743, 690801, 691556, 693953, 694001, 694244, 696001, 696026, 696427, 697336, 698059, 698060, 698717, 698970, 699148,
- 库表：t8891.t_carinfo_favorite；实例 alias：
prod-8891/master
- explain：未执行（Error calling tool 'explain': (1054, "Unknown column 'sale_stop' in 'where clause'")）
- 慢因：扫描行数远大于返回行数(1,833,806/6)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t8891`.`t_carinfo_favorite` ADD INDEX `idx_sale_stop` (`sale_stop`);
4. select * from t_carinfo_inquiry_agent where expire_status = ? and expire_time <= ?; —— 1,440 次 × 1.99s = 47.7min
select * from `t_carinfo_inquiry_agent` where `expire_status` = 0 and `expire_time` <= '2026-10-25 00:20: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-10-25 00:20:12';
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_carinfo_inquiry_agent | ❌ 全表扫描 | — | — | 1,898,725 | 3.33% |
- 慢因：扫描行数远大于返回行数(2,411,196/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`);
5. select * from t_carinfo_inquiry_agent where contact_status = ? and expire_status = ?; —— 1,227 次 × 1.94s = 39.6min
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,725 | 1.0% |
- 慢因：扫描行数远大于返回行数(2,411,196/848)、EXPLAIN 全表扫描/未用索引
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t8891`.`t_carinfo_inquiry_agent` ADD INDEX `idx_contact_st_expire_sta` (`contact_status`, `expire_status`);
6. select max(record_time) as aggregate from t_carinfo_inquiry_call_log; —— 1,440 次 × 1.42s = 34.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,597 | 100.0% |
- 慢因：扫描行数远大于返回行数(2,235,426/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 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 —— 499 次 × 2.02s = 16.8min
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 '11:51:09' and '11:52:08') 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 '11:51:09' and '11:52:08') and `t_sele_item_hidden`.`id` is null;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | t_sele_item_update_plan | ❌ 全表扫描 | — | PRIMARY,idx_item_id_status | 315,864 | 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,342,636/411)、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), 新索引生效后可评估删除旧索引
8. 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.47s = 16.7min
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,158 | 50.0% |
| 2 | t_vincheck_supervision_info | eq_ref | idx_vincheck_supervision_id | idx_vincheck_supervision_id | 1 | 10.0% |
- 慢因：扫描行数远大于返回行数(1,035,086/62)
- 建议：补索引（按 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), 新索引生效后可评估删除旧索引
逐条优化建议汇总
P 行 = 深析模式（指纹聚合，按全天累计耗时降序 P1 最大，是优化主战场）；T 行 = 单条耗时 Top3 的一次性慢 SQL（多为 bi/导出查询，治理手段通常是限流/降频/移到从库，而非建索引）。
| 编号 | 对象 | 建议 |
|---|
| P1 | t8891.t_member_devices（3,547×5.5s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_member_devices ADD INDEX idx_m_id_hash (m_id, hash); |
| P2 | t8891.t_carinfo_coupon_activity（21×278.2s） | 补索引（按 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); |
| P3 | t8891.t_carinfo_favorite（1,027×5.0s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_favorite ADD INDEX idx_sale_stop (sale_stop); |
| P4 | t8891.t_carinfo_inquiry_agent（1,440×2.0s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_inquiry_agent ADD INDEX idx_expire_sta_expire_tim (expire_status, expire_time); |
| P5 | t8891.t_carinfo_inquiry_agent（1,227×1.9s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_inquiry_agent ADD INDEX idx_contact_st_expire_sta (contact_status, expire_status); |
| P6 | t8891.t_carinfo_inquiry_call_log（1,440×1.4s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_carinfo_inquiry_call_log ADD INDEX idx_record_tim (record_time); |
| P7 | t8891.v_items（499×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); |
| P8 | t8891.t_vincheck_supervision（288×3.5s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t8891.t_vincheck_supervision ADD INDEX idx_is_exist_type (is_exist, type); |
| T1 | Top1（同 P2 对象） 单条最慢 4.8min，user=t8891 t8891.t_carinfo_coupon_activity | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
| T2 | Top2（同 P2 对象） 单条最慢 4.8min，user=t8891 t8891.t_carinfo_coupon_activity | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
| T3 | Top3（同 P2 对象） 单条最慢 4.8min，user=t8891 t8891.t_carinfo_coupon_activity | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
*采集脚本 .agents/scripts/mysql_slow_prod_report.py，中间数据 outputs/slow_report_prod8891_20261025.json；全程只读。*