📋 目录统计概览Top10 明细（按单条耗时）高频慢查询模式（指纹聚合，取样 23,220 条触发深度分析的模式（条数>100 或 累计>30逐条优化建议汇总

MySQL 慢查询日报（线上 prod591）2026-09-20

统计概览

项目值
统计范围2026-09-20 00:00:00 ~ 23:59:59（Asia/Shanghai）
环境prod-591
慢日志条数（ES mysql-slow_*）32,617 条（指纹聚合取样 23,220 条，覆盖率 71%，超过 2 万条上限截断）
Top10 单条最慢5,777.87 s（user=bi，ZHDX_t591_148）
非备份模式最大累计17.91h（174 次 × 370.46 s）
备份模式（user 含 backup/databasus_backup）47 条、累计 1.44h（单独标注，不触发深析）
监控巡检类（jmysql/proxysql_monitor 或 information_schema）129 条、累计 5.2min（多为 # Schema: 空导致采集端 _grokparsefailure、无库名；单独标注，不触发深析）
深析模式 explain 成功率5/8（62%）
数据来源ES mysql-slow_*（@timestamp）+ MCP 只读 MySQL（describe_schema/explain）
生成时间2026-09-21 09:42（Asia/Shanghai）

Top10 明细（按单条耗时）

#耗时(s)库.表行数 sent/examineduser / server慢因分类
15,777.87t591_new.market_community_agent_bid_record_detail20,906 / 4,443,898,257bi / ZHDX_t591_148扫描行数远大于返回行数(4,443,898,257/20,906)、索引范围扫描量过大(market_community_agent_bid_record_detail: key=idx_created_at, rows=3,568,860)
23,610.82t591.view_activity_house1,000 / 55,508,933Security591 / ZHDX_t591_207扫描行数远大于返回行数(55,508,933/1,000)、索引选择性差(um: key=sign 扫 33,186,361 行回表后仅剩 0.02%，建议把其余 WHERE 等值列并入复合索引)、排序未走索引(filesort)
32,256.42t591.view_activity_house1,000 / 55,514,592Security591 / ZHDX_t591_207扫描行数远大于返回行数(55,514,592/1,000)、索引选择性差(um: key=sign 扫 33,186,361 行回表后仅剩 0.02%，建议把其余 WHERE 等值列并入复合索引)、排序未走索引(filesort)
41,700.83tw591_gateway.verify_log8,741,046 / 8,741,046databasus_backup / ZHDX_t591_118:3307其他(需结合 SQL 与执行计划人工判断)
51,303.28t5_pop_recmd.user_profile_latest9,145,219 / 9,145,219databasus_backup / ZHDX_t591_118:3307其他(需结合 SQL 与执行计划人工判断)
61,041.10t591_new.market_voucher_use153 / 617,798,622bi / ZHDX_t591_148扫描行数远大于返回行数(617,798,622/153)
7799.64None.user_profile_latest0 / 3,155,904t5_pop_recmd / ZHDX_t591_34扫描行数远大于返回行数(3,155,904/0)
8791.75None.user_profile_latest0 / 3,160,045t5_pop_recmd / ZHDX_t591_34扫描行数远大于返回行数(3,160,045/0)
9783.32None.user_profile_latest0 / 3,153,845t5_pop_recmd / ZHDX_t591_34扫描行数远大于返回行数(3,153,845/0)
10778.33t591.sale0 / 37,795,054bi / ZHDX_t591_148扫描行数远大于返回行数(37,795,054/0)、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS)

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

按条数 Top5

#模式（指纹摘要）条数平均(s)累计serveruser
1select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */;4,1001.721.96hZHDX_t591_207tw591
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,4324.424.22hZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18tw591
3select 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 */2,4222.851.92hRDS_IMhk591
4
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 */;
7491.6220.2minZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18tw591
5select count(*) as aggregate from user_house_source where status = ? and community_id > ? and version = ? /* ms:tw591ms-house-resource */;62611.772.05hZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18tw591

按累计耗时 Top5

#模式条数平均(s)累计serveruser
1DELETE FROM user_profile_latest WHERE create_time < ? LIMIT ?;174370.4617.91hZHDX_t591_34t5_pop_recmd
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,4324.424.22hZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18tw591
3select count(*) as aggregate from user_house_source where status = ? and community_id > ? and version = ? /* ms:tw591ms-house-resource */;62611.772.05hZHDX_t591_137,ZHDX_t591_181,ZHDX_t591_18tw591
4select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */;4,1001.721.96hZHDX_t591_207tw591
5select 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 */2,4222.851.92hRDS_IMhk591
备份模式全天 47 条 / 1.44h，未触发深度分析（如条数环比异常翻倍再人工介入）。

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

1. DELETE FROM user_profile_latest WHERE create_time < ? LIMIT ?; —— 174 次 × 370.46s = 17.91h

DELETE FROM user_profile_latest
    WHERE create_time < '2026-03-18 00:00:03'
    LIMIT 5000;
ALTER TABLE `user_profile_latest` ADD INDEX `idx_create_tim` (`create_time`);

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 —— 3,432 次 × 4.42s = 4.22h

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 ('105035', '5880979', '49563') and `b`.`start_time` >= '2026-09-20 00:00:00' and `b`.`end_time` <= '2026-09-20 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 */;
SELECT * FROM `t591`.`sale` WHERE `start_time` >= '2026-09-20 00:00:00' LIMIT 10
#表访问类型使用索引候选索引预扫行数过滤后
1sale❌ 全表扫描——17,771,19333.33%
ALTER TABLE `t591`.`sale` ADD INDEX `idx_start_time` (`start_time`);

3. select count(*) as aggregate from user_house_source where status = ? and community_id > ? and version = ? /* ms:tw591ms-house-resource */; —— 626 次 × 11.77s = 2.05h

select count(*) as aggregate from `user_house_source` where `status` = '1' and `community_id` > '0' and `version` = '1' /* ms:tw591ms-house-resource */;
select count(*) as aggregate from `t591_new`.`user_house_source` where `status` = '1' and `community_id` > '0' and `version` = '1' /* ms:tw591ms-house-resource */;
#表访问类型使用索引候选索引预扫行数过滤后
1user_house_sourcerefidx_versionidx_version526,1623.33%
ALTER TABLE `t591_new`.`user_house_source` ADD INDEX `idx_status_version_community_` (`status`, `version`, `community_id`);

4. select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */; —— 4,100 次 × 1.72s = 1.96h

select count(*) as aggregate from `sale` where `status` = '2' and `closed` = '0' and `community_id` = '8139' and `kind` in ('6', '8', '9', '10', '24') and `posttime` >= '1788537600' /* ms:tw591ms-cdc */;
select count(*) as aggregate from `t591`.`sale` where `status` = '2' and `closed` = '0' and `community_id` = '8139' and `kind` in ('6', '8', '9', '10', '24') and `posttime` >= '1788537600' /* ms:tw591ms-cdc */;
#表访问类型使用索引候选索引预扫行数过滤后
1salerefidx_community_idposttime,idx_community_id5,0120.01%
ALTER TABLE `t591`.`sale` ADD INDEX `idx_status_closed_community__kind_posttime` (`status`, `closed`, `community_id`, `kind`, `posttime`);

5. select last_contacted_at, uid, is_sticked from im_conversations where conversation IN (?...) and uid != ? and im_conversations.deleted_at is null /* ms:hongyan- —— 2,422 次 × 2.85s = 1.92h

select `last_contacted_at`, `uid`, `is_sticked` from `im_conversations` where `conversation` in (select `conversation` from `im_conversations` where `app_id` = '3' and `uid` = '1001' and `deleted_at` is null) and `uid` != '1001' and `im_conversations`.`deleted_at` is null /* ms:hongyan-web-server */
ALTER TABLE `im_conversations` ADD INDEX `idx_app_id_uid_conversati` (`app_id`, `uid`, `conversation`);

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.49s = 1.65h

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()
ALTER TABLE `mysql`.`slow_log` ADD INDEX `idx_start_time` (`start_time`);

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 次 × 5777.87s = 1.60h

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));
SELECT * FROM `t591_new`.`market_community_agent_bid_record_detail` WHERE created_at>='2023-08-21' LIMIT 10
#表访问类型使用索引候选索引预扫行数过滤后
1market_community_agent_bid_record_detailrangeidx_created_atidx_created_at3,568,860100.0%

8. select bank_id, actiontime, dealtime, f_time, id, item_id, money, nnote, post_id, posttime, sign, type, status, tag, typetag, truemoney, userId from t591.view_a —— 1 次 × 3610.82s = 1.00h

select `bank_id`, `actiontime`, `dealtime`, `f_time`, `id`, `item_id`, `money`, `nnote`, `post_id`, `posttime`, `sign`, `type`, `status`, `tag`, `typetag`, `truemoney`, `userId` from `t591`.`view_activity_house` limit 1000;
select `bank_id`, `actiontime`, `dealtime`, `f_time`, `id`, `item_id`, `money`, `nnote`, `post_id`, `posttime`, `sign`, `type`, `status`, `tag`, `typetag`, `truemoney`, `userId` from `t591`.`view_activity_house` limit 1000;
#表访问类型使用索引候选索引预扫行数过滤后
1umrefsignsign,tp33,186,3760.02%
2umneq_refPRIMARYPRIMARY1100.0%

逐条优化建议汇总

P 行 = 深析模式（指纹聚合，按全天累计耗时降序 P1 最大，是优化主战场）；T 行 = 单条耗时 Top3 的一次性慢 SQL（多为 bi/导出查询，治理手段通常是限流/降频/移到从库，而非建索引）。
编号对象建议
P1?.user_profile_latest（174×370.5s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE user_profile_latest ADD INDEX idx_create_tim (create_time);
P2t591.sale（3,432×4.4s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_start_time (start_time);; JOIN 两侧列改为同类型直连(如int列配int列), 或新增冗余绑定列建索引
P3t591_new.user_house_source（626×11.8s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591_new.user_house_source ADD INDEX idx_status_version_community_ (status, version, community_id);
P4t591.sale（4,100×1.7s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.sale ADD INDEX idx_status_closed_community__kind_posttime (status, closed, community_id, kind, posttime);
P5?.im_conversations（2,422×2.9s）改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核
P6mysql.slow_log（216×27.5s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE mysql.slow_log ADD INDEX idx_start_time (start_time);
P7t591_new.market_community_agent_bid_record_detail（1×5777.9s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
P8t591.view_activity_house（1×3610.8s）补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
T1Top1（同 P7 对象） 单条最慢 1.60h，user=bi t591_new.market_community_agent_bid_record_detail补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库
T2Top2（同 P8 对象） 单条最慢 1.00h，user=Security591 t591.view_activity_house补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库
T3Top3（同 P8 对象） 单条最慢 37.6min，user=Security591 t591.view_activity_house补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库

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

由 md2html_report.py 渲染 ｜ 生成时间 2026-09-21 09:42:06（Asia/Shanghai）