MySQL 慢查询日报（线上 prod591）2026-09-27
统计概览
| 项目 | 值 |
|---|
| 统计范围 | 2026-09-27 00:00:00 ~ 23:59:59（Asia/Shanghai） |
| 环境 | prod-591 |
| 慢日志条数（ES mysql-slow_*） | 31,123 条（指纹聚合取样 22,508 条，覆盖率 72%，超过 2 万条上限截断） |
| Top10 单条最慢 | 5,738.85 s（user=bi，ZHDX_t591_148） |
| 非备份模式最大累计 | 18.02h（71 次 × 913.46 s） |
| 备份模式（user 含 backup/databasus_backup） | 46 条、累计 1.43h（单独标注，不触发深析） |
| 监控巡检类（jmysql/proxysql_monitor 或 information_schema） | 141 条、累计 3.7min（多为 # Schema: 空导致采集端 _grokparsefailure、无库名；单独标注，不触发深析） |
| 深析模式 explain 成功率 | 4/8（50%） |
| 数据来源 | ES mysql-slow_*（@timestamp）+ MCP 只读 MySQL（describe_schema/explain） |
| 生成时间 | 2026-09-28 09:41（Asia/Shanghai） |
Top10 明细（按单条耗时）
| # | 耗时(s) | 库.表 | 行数 sent/examined | user / server | 慢因分类 |
|---|
| 1 | 5,738.85 | t591_new.market_community_agent_bid_record_detail | 20,099 / 4,347,977,613 | bi / ZHDX_t591_148 | 扫描行数远大于返回行数(4,347,977,613/20,099)、索引范围扫描量过大(market_community_agent_bid_record_detail: key=idx_created_at, rows=3,568,860) |
| 2 | 5,057.75 | t591.sale | 0 / 705,905,953 | bi / ZHDX_t591_148 | 扫描行数远大于返回行数(705,905,953/0)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS) |
| 3 | 4,877.77 | t591.sale | 1 / 705,905,954 | bi / ZHDX_t591_148 | 扫描行数远大于返回行数(705,905,954/1)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS) |
| 4 | 3,183.99 | t591.sale | 1 / 705,906,374 | bi / ZHDX_t591_148 | 扫描行数远大于返回行数(705,906,374/1)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS) |
| 5 | 2,950.53 | t591.view_activity_house | 1,000 / 55,645,940 | Security591 / ZHDX_t591_207 | 扫描行数远大于返回行数(55,645,940/1,000)、索引选择性差(um: key=sign 扫 33,186,399 行回表后仅剩 0.02%，建议把其余 WHERE 等值列并入复合索引)、排序未走索引(filesort) |
| 6 | 1,709.19 | tw591_gateway.verify_log | 8,741,046 / 8,741,046 | databasus_backup / ZHDX_t591_118:3307 | 其他(需结合 SQL 与执行计划人工判断) |
| 7 | 1,145.93 | t5_pop_recmd.user_profile_latest | 5,295,220 / 5,295,220 | databasus_backup / ZHDX_t591_118:3307 | 其他(需结合 SQL 与执行计划人工判断) |
| 8 | 1,115.92 | None.user_profile_latest | 0 / 4,570,959 | t5_pop_recmd / ZHDX_t591_34 | 扫描行数远大于返回行数(4,570,959/0) |
| 9 | 1,102.59 | None.user_profile_latest | 0 / 4,566,502 | t5_pop_recmd / ZHDX_t591_34 | 扫描行数远大于返回行数(4,566,502/0) |
| 10 | 1,102.12 | None.user_profile_latest | 0 / 4,478,326 | t5_pop_recmd / ZHDX_t591_34 | 扫描行数远大于返回行数(4,478,326/0) |
高频慢查询模式（指纹聚合，取样 22,508 条）
按条数 Top5
| # | 模式（指纹摘要） | 条数 | 平均(s) | 累计 | server | user |
|---|
| 1 | select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */; | 3,990 | 1.68 | 1.86h | ZHDX_t591_207 | tw591 |
| 2 | select a.community_id, MIN(a.user_id) as user_id from t591.sale as a inner join t591_new.market_community_agent_bid_record as b on a.user_id = b.user_id inner j …（点击展开完整 SQL）select a.community_id, MIN(a.user_id) as user_id from t591.sale as a inner join t591_new.market_community_agent_bid_record as b on a.user_id = b.user_id inner join t591_new.im_user_bind as im on im.im_uid=CONCAT(a.user_id,?) where a.community_id IN (?...) and b.start_time >= ? and b.end_time <= ? and b.status = ? and b.proc_status = ? and b.deleted = ? and a.status = ? and a.closed = ? group by a.community_id /* ms:tw591ms-community */; | 3,257 | 3.54 | 3.20h | ZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18 | tw591 |
| 3 | select last_contacted_at, uid, is_sticked from im_conversations where conversation IN (?...) and uid != ? and im_conversations.deleted_at is null /* ms:hongyan-web-server */ | 1,798 | 2.60 | 1.30h | RDS_IM | hk591 |
| 4 | select id, name, checkstatus, deleted, build_purpose from t591_new.market_community FORCE INDEX(idx_region_section) where status = ? and deleted = ? and checkstatus = ? and type = ? and regionid = ? and sectionid = ? /* ms:tw591ms-cdc */; | 1,074 | 2.82 | 50.4min | ZHDX_t591_207 | tw591 |
| 5 | select sum(ivr_call) as aggregate from build_stat_total where theday = ? and cate_id = ? and build_id in (select tupd.hid from t591_new.union_package_detail as …（点击展开完整 SQL）select sum(ivr_call) as aggregate from build_stat_total where theday = ? and cate_id = ? and build_id in (select tupd.hid from t591_new.union_package_detail as tupd left join t591.build on tupd.hid = build.id where build.regionid IN (?...) and tupd.deleted = ? and tupd.start_time <= ? and tupd.end_time >= ? and (tupd.close = ? or (tupd.close IN (?...) and tupd.close_time > ?))) /* ms:tw591ms-advertising */; | 746 | 1.74 | 21.6min | ZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18 | tw591 |
按累计耗时 Top5
| # | 模式 | 条数 | 平均(s) | 累计 | server | user |
|---|
| 1 | DELETE FROM user_profile_latest WHERE create_time < ? LIMIT ?; | 71 | 913.46 | 18.02h | ZHDX_t591_34 | t5_pop_recmd |
| 2 | select * from t591.house_rel where status = ? and closed = ? and user_id = ? and type = ? and housetype = ? and kind = ? and area >= ? and regionid IN (?...) order by start_time desc, id desc limit ? offset ? /* ms:tw591ms-user-center */; | 270 | 63.69 | 4.78h | ZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18 | tw591 |
| 3 | select a.community_id, MIN(a.user_id) as user_id from t591.sale as a inner join t591_new.market_community_agent_bid_record as b on a.user_id = b.user_id inner j …（点击展开完整 SQL）select a.community_id, MIN(a.user_id) as user_id from t591.sale as a inner join t591_new.market_community_agent_bid_record as b on a.user_id = b.user_id inner join t591_new.im_user_bind as im on im.im_uid=CONCAT(a.user_id,?) where a.community_id IN (?...) and b.start_time >= ? and b.end_time <= ? and b.status = ? and b.proc_status = ? and b.deleted = ? and a.status = ? and a.closed = ? group by a.community_id /* ms:tw591ms-community */; | 3,257 | 3.54 | 3.20h | ZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18 | tw591 |
| 4 | SELECT CURDATE() dt,count(DISTINCT user_id) ?, count(?) ? FROM t591.sale WHERE posttime>=UNIX_TIMESTAMP(?) and closed=? -- and status=? and end_time>=UNIX_TIMESTAMP(?) and user_id IN (?...); | 2 | 4030.88 | 2.24h | ZHDX_t591_148 | bi |
| 5 | select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */; | 3,990 | 1.68 | 1.86h | ZHDX_t591_207 | tw591 |
备份模式全天 46 条 / 1.43h，未触发深度分析（如条数环比异常翻倍再人工介入）。
触发深度分析的模式（条数>100 或 累计>30min，非备份，最多 8 个）
1. DELETE FROM user_profile_latest WHERE create_time < ? LIMIT ?; —— 71 次 × 913.46s = 18.02h
DELETE FROM user_profile_latest
WHERE create_time < '2026-03-18 00:00:03'
LIMIT 5000;
- 库表：?.user_profile_latest；实例 alias：
None
- explain：未执行（无 alias(该 server 无可连 MCP 实例)）
- 慢因：扫描行数远大于返回行数(4,526,227/0)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `user_profile_latest` ADD INDEX `idx_create_tim` (`create_time`);
- DDL 注：
user_profile_latest 未取到表结构, 索引重复性未核对, 执行前请 SHOW INDEX 确认
2. select * from t591.house_rel where status = ? and closed = ? and user_id = ? and type = ? and housetype = ? and kind = ? and area >= ? and regionid IN (?...) or —— 270 次 × 63.69s = 4.78h
select * from `t591`.`house_rel` where `status` = '2' and `closed` = '0' and `user_id` = '1446565' and `type` = '1' and `housetype` = '1' and `kind` = '11' and `area` >= '40' and `regionid` in (select `id` from `t591`.`area_region` where `name` in ('台北市', '新北市', '桃園市', '新竹縣', '新竹市')) order by `start_time` desc, `id` desc limit 100 offset 0 /* ms:tw591ms-user-center */;
- 库表：t591.house_rel；实例 alias：
prod-591/master
- explain SQL：
select * from `t591`.`house_rel` where `status` = '2' and `closed` = '0' and `user_id` = '1446565' and `type` = '1' and `housetype` = '1' and `kind` = '11' and `area` >= '40' and `regionid` in (select `id` from `t591`.`area_region` where `name` in ('台北市', '新北市', '桃園市', '新竹縣', '新竹市')) order by `start_time` desc, `id` desc limit 100 offset 0 /* ms:tw591ms-user-center */;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | area_region | ❌ 全表扫描 | — | PRIMARY | 23 | 50.0% |
| 2 | house_rel | ref | rid_sid | tp,user_id,rid_sid,sc | 5,366 | 0.0% |
- 计划标志：⚠️ filesort 排序、⚠️ 产生临时表
- 慢因：扫描行数远大于返回行数(21,439,641/0)、EXPLAIN 全表扫描/未用索引、DISTINCT/GROUP BY 产生临时表(大范围去重/聚合)、排序未走索引(filesort)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t591`.`house_rel` ADD INDEX `idx_status_closed_user_id_type_housetype_area` (`status`, `closed`, `user_id`, `type`, `housetype`, `area`);
- DDL 注：
house_rel 与现有索引 sc(status, closed, refreshtime) 前缀重复(status), 新索引生效后可评估删除旧索引
3. select a.community_id, MIN(a.user_id) as user_id from t591.sale as a inner join t591_new.market_community_agent_bid_record as b on a.user_id = b.user_id inner j —— 3,257 次 × 3.54s = 3.20h
select a.community_id, MIN(a.user_id) as user_id from t591.sale as a inner join `t591_new`.`market_community_agent_bid_record` as `b` on `a`.`user_id` = `b`.`user_id` inner join `t591_new`.`im_user_bind` as `im` on im.im_uid=CONCAT(a.user_id,"") where `a`.`community_id` in ('3695078', '3677289', '3681594') and `b`.`start_time` >= '2026-09-27 00:00:00' and `b`.`end_time` <= '2026-09-27 23:59:59' and `b`.`status` = '3' and `b`.`proc_status` = '3' and `b`.`deleted` = '0' and `a`.`status` = '2' and `a`.`closed` = '0' group by `a`.`community_id` /* ms:tw591ms-community */;
- 库表：t591.sale；实例 alias：
prod-591/master
- explain SQL：
SELECT * FROM `t591`.`sale` WHERE `start_time` >= '2026-09-27 00:00:00' LIMIT 10
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | sale | ❌ 全表扫描 | — | — | 20,845,021 | 33.33% |
- 慢因：扫描行数远大于返回行数(1,503,070/3)、EXPLAIN 全表扫描/未用索引、JOIN 条件含函数(如 ON col=CONCAT(...))导致索引失效, 建议冗余类型一致列直连
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; JOIN 两侧列改为同类型直连(如int列配int列), 或新增冗余绑定列建索引
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t591`.`sale` ADD INDEX `idx_start_time` (`start_time`);
4. SELECT CURDATE() dt,count(DISTINCT user_id) ?, count(?) ? FROM t591.sale WHERE posttime>=UNIX_TIMESTAMP(?) and closed=? -- and status=? and end_time>=UNIX_TIMES —— 2 次 × 4030.88s = 2.24h
SELECT CURDATE() dt,count(DISTINCT user_id) '会员数', count(1) '物件数'
FROM t591.sale
WHERE posttime>=UNIX_TIMESTAMP('2023-01-01') and `closed`=0 -- and `status`=2
and end_time>=UNIX_TIMESTAMP('2023-07-03') and user_id in (
SELECT DISTINCT user_id from t591.activity_plan_money where `type`=2 and sign=2 and `status`=1 and posttime<UNIX_TIMESTAMP('2023-07-03')
);
- 库表：t591.sale；实例 alias：
prod-591/master
- explain：未执行（Error calling tool 'explain': (1054, "Unknown column 'type' in 'where clause'")）
- 慢因：扫描行数远大于返回行数(705,906,374/1)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t591`.`sale` ADD INDEX `idx_type` (`type`);
5. select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */; —— 3,990 次 × 1.68s = 1.86h
select count(*) as aggregate from `sale` where `status` = '2' and `closed` = '0' and `community_id` = '37951' and `kind` in ('6', '8', '9', '10', '24') and `posttime` >= '1789142400' /* ms:tw591ms-cdc */;
- 库表：t591.sale；实例 alias：
prod-591/master
- explain SQL：
select count(*) as aggregate from `t591`.`sale` where `status` = '2' and `closed` = '0' and `community_id` = '37951' and `kind` in ('6', '8', '9', '10', '24') and `posttime` >= '1789142400' /* ms:tw591ms-cdc */;
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | sale | ref | idx_community_id | posttime,idx_community_id | 7,484 | 0.0% |
- 慢因：扫描行数远大于返回行数(7,792/1)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t591`.`sale` ADD INDEX `idx_status_closed_community__kind_posttime` (`status`, `closed`, `community_id`, `kind`, `posttime`);
6. select start_time, user_host, query_time, lock_time, rows_sent, rows_examined, db, sql_text from mysql.slow_log WHERE start_time >= DATE_SUB(NOW(), INTERVAL ? M —— 216 次 × 27.64s = 1.66h
select start_time, user_host, query_time, lock_time, rows_sent, rows_examined, db, sql_text from mysql.slow_log WHERE start_time >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) AND start_time <= NOW()
- 库表：mysql.slow_log；实例 alias：
None
- explain：未执行（无 alias(该 server 无可连 MCP 实例)）
- 慢因：扫描行数远大于返回行数(2,302,543/77)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `mysql`.`slow_log` ADD INDEX `idx_start_time` (`start_time`);
- DDL 注：
slow_log 未取到表结构, 索引重复性未核对, 执行前请 SHOW INDEX 确认
7. SELECT mcrd.id, mcbr.period_id as ?, mcrd.record_id ?, if(mcbr.parent_id!=?,mcbr.parent_id,null) ?, mcrd.oid ?, mcrd.user_id ?, case mcrd.payment_type when ? th —— 1 次 × 5738.85s = 1.59h
SELECT
mcrd.`id`,
mcbr.period_id as '期数',
mcrd.`record_id` '竞价ID',
if(mcbr.parent_id!=0,mcbr.parent_id,null) '父竞价ID',
mcrd.oid '订单ID',
mcrd.`user_id` '会员ID',
case mcrd.`payment_type` when 1 then '竞价' when 2 then '调价' end '出价类型',
mcrd.`money` '(出价/调价)金额',
mcrd. `type` '竞价类型',
mcrd.`rank` '预排名',
mcrd.`community_id` '社区ID',
-- mc.`name` '社区名称',
ar.`name` '社区县市',
ase.`name` '社区乡镇',
case when mcrd.`source`=1 then '推荐' when mcrd.`source`=2 then '搜寻' when mcrd.`source`=3 then '收藏' when mcrd.`source`=4 then '上期'
when mcrd.`source`=5 then '历史' when mcrd.`source`=6 then '续购' when mcrd.`source`=7 then '物件管理页' when mcrd.`source`=8 then '会员中心推荐社区弹窗' when mcrd.`source`=9 then '召集令活动页' when mcrd.`source`=10 then '企业号触屏' when mcrd.`source`=11 then '精准营销弹窗' when mcrd.`source`=12 then '物件刊登成功页' when mcrd.`source`=13 then '物件管理页顶部推荐' when mcrd.`source`=14 then '最新续购'
when mcrd.`source`=15 then '优质社区推荐'
when mcrd.`source`=16 then '弹窗复购'
when mcrd.`source`=17 then '弹窗續費'
when mcrd.`source`=18 then '中古屋詳情頁PC端'
when mcrd.`source`=19 then '中古屋詳情頁觸屏端'
when mcrd.`source`=20 then '中古屋詳情頁APP端'
when mcrd.`source`=21 then '租屋詳情頁PC端'
when mcrd.`source`=22 then '租屋詳情頁觸屏端'
when mcrd.`source`=23 then '租詳情頁APP端'
when mcrd.`source`=24 then 'PC-会员中心优惠券'
when mcrd.`source`=25 then '触屏-会员中心'
when mcrd.`source`=26 then 'APP-会员中心'
when mcrd.`source`=27 then '四端-金牌专家版位'
when mcrd.`source`=28 then '四端-介绍页'
when mcrd.`source`=29 then '待办事项提醒续费'
when mcrd.`source`=30 then '待办事项体系到期复购'
when mcrd.`source`=31 then 'PC-现金券列表'
when mcrd.`source`=32 then '触屏-现金券列表'
when mcrd.`source`=33 then 'PC-领取成功弹窗社区推荐'
when mcrd.`source`=34 then 'PC-待使用弹窗社区推荐'
when mcrd.`source`=35 then '物件管理页顶部推荐(物件推荐)'
when mcrd.`source`=37 then '曝光失败弹窗复购'
when mcrd.`source`=38 then '即将到期一键续购'
when mcrd.`source`=39 then '已到期一键续购'
when mcbr.`source`=40 then '即将到期一键续购-触屏'
when mcbr.`source`=41 then '即将到期一键续购-APP'
when mcrd.`source`=101 then '其他'
when mcrd.`source`=102 then 'pc金牌专家购买列表'
when mcrd.`source`=103 then '触屏金牌专家购买列表'
when mcrd.`source`=104 then 'PC物件管理页列表'
when mcrd.`source`=105 then 'PC弹窗引导'
when mcrd.`source`>=100 and mcrd.`source`<=200 then '自定义' else mcrd.`source` end '订单入口来源',
mcrd.`deleted` '是否刪除', -- ，1、為已刪,0、未未刪
case mcrd.`pay_status` when 1 then '待支付' when 2 then '已支付' when 3 then '退款' when 4 then '取消' when 5 then '失败' else mcrd.`pay_status` end '支付状态',
mcrd.`created_at` '创建时间',
mcrd.`updated_at` '更新时间'
from t591_new.market_community_agent_bid_record_detail mcrd
LEFT JOIN t591_new.market_community_agent_bid_record mcbr on mcrd.record_id=mcbr.id and mcrd.user_id=mcbr.user_id
LEFT JOIN t591_new.market_community_bid_rules mcr on mcbr.`community_id`=mcr.community_id -- and mcbr.period_id=mcr.period_id
LEFT JOIN t591_new.`market_community` mc on mcrd.community_id=mc.id
left JOIN t591_new.area_region ar on mcr.`regionid`=ar.id
left JOIN t591_new.`area_section` ase on mcr.sectionid=ase.id
LEFT JOIN t591_new.market_community_agent_order mco on mcrd.oid=mco.id
LEFT JOIN t591_new.market_voucher_use mvu on mcrd.user_id=mvu.user_id and mcrd.oid=mvu.oid and mvu.oid!=0
LEFT join t591_new.market_voucher mv on mvu.voucher_id=mv.id
LEFT join t591_new.market_voucher_config mvc on mv.config_id=mvc.id
WHERE mcrd.created_at>='2023-08-21' and mcbr.parent_id!=0
and (mcrd.created_at>=DATE_SUB(CURDATE(),INTERVAL 2 DAY) or mcrd.updated_at>=DATE_SUB(CURDATE(),INTERVAL 2 DAY));
- 库表：t591_new.market_community_agent_bid_record_detail；实例 alias：
prod-591/master
- explain SQL：
SELECT * FROM `t591_new`.`market_community_agent_bid_record_detail` WHERE created_at>='2023-08-21' LIMIT 10
- 执行计划（EXPLAIN 表节点，按 JOIN 顺序）：
| # | 表 | 访问类型 | 使用索引 | 候选索引 | 预扫行数 | 过滤后 |
|---|
| 1 | market_community_agent_bid_record_detail | range | idx_created_at | idx_created_at | 3,568,860 | 100.0% |
- 慢因：扫描行数远大于返回行数(4,347,977,613/20,099)、索引范围扫描量过大(market_community_agent_bid_record_detail: key=idx_created_at, rows=3,568,860)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
8. select * from (SELECT CURDATE() dt,count(DISTINCT user_id) ?, count(?) ? FROM t591.sale WHERE posttime>=UNIX_TIMESTAMP(?) and closed=? -- and status=? and end_t —— 1 次 × 5057.75s = 1.40h
select *
from (SELECT CURDATE() dt,count(DISTINCT user_id) '会员数', count(1) '物件数'
FROM t591.sale
WHERE posttime>=UNIX_TIMESTAMP('2023-01-01') and `closed`=0 -- and `status`=2
and end_time>=UNIX_TIMESTAMP('2023-07-03') and user_id in (
SELECT DISTINCT user_id from t591.activity_plan_money where `type`=2 and sign=2 and `status`=1 and posttime<UNIX_TIMESTAMP('2023-07-03')
)
) as `T`
limit 0;
- 库表：t591.sale；实例 alias：
prod-591/master
- explain：未执行（Error calling tool 'explain': (1054, "Unknown column 'type' in 'where clause'")）
- 慢因：扫描行数远大于返回行数(705,905,953/0)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS)
- 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核
- 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：
ALTER TABLE `t591`.`sale` ADD INDEX `idx_type` (`type`);
逐条优化建议汇总
P 行 = 深析模式（指纹聚合，按全天累计耗时降序 P1 最大，是优化主战场）；T 行 = 单条耗时 Top3 的一次性慢 SQL（多为 bi/导出查询，治理手段通常是限流/降频/移到从库，而非建索引）。
| 编号 | 对象 | 建议 |
|---|
| P1 | ?.user_profile_latest（71×913.5s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE user_profile_latest ADD INDEX idx_create_tim (create_time); |
| P2 | t591.house_rel（270×63.7s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.house_rel ADD INDEX idx_status_closed_user_id_type_housetype_area (status, closed, user_id, type, housetype, area);; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核 |
| P3 | t591.sale（3,257×3.5s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_start_time (start_time);; JOIN 两侧列改为同类型直连(如int列配int列), 或新增冗余绑定列建索引 |
| P4 | t591.sale（2×4030.9s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_type (type);; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核 |
| P5 | t591.sale（3,990×1.7s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_status_closed_community__kind_posttime (status, closed, community_id, kind, posttime); |
| P6 | mysql.slow_log（216×27.6s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE mysql.slow_log ADD INDEX idx_start_time (start_time); |
| P7 | t591_new.market_community_agent_bid_record_detail（1×5738.9s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中 |
| P8 | t591.sale（1×5057.7s） | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_type (type);; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核 |
| T1 | Top1（同 P7 对象） 单条最慢 1.59h，user=bi t591_new.market_community_agent_bid_record_detail | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
| T2 | Top2（同 P8 对象） 单条最慢 1.40h，user=bi t591.sale | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_type (type);; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
| T3 | Top3（同 P8 对象） 单条最慢 1.35h，user=bi t591.sale | 补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_type (type);; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库 |
*采集脚本 .agents/scripts/mysql_slow_prod_report.py，中间数据 outputs/slow_report_prod591_20260927.json；全程只读。*