MySQL 慢查询日报（线上 prod591）2026-11-01

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. select * from (select user_id from t591.house_rel where type IN (?...) and status > ? and start_time < ? and end_time > ? group by user_id) as a where a.user_id —— 3 次 × 5685.00s = 4.74h

• 代表性 SQL：

select * from (select `user_id` from `t591`.`house_rel` where `type` in (1, 2) and `status` > 0 and `start_time` < 1793462400 and `end_time` > 1790784000 group by `user_id`) as a where `a`.`user_id` > 174179 order by `a`.`user_id` asc limit 5000;

• 库表：t591.house_rel；实例 alias：prod-591/master
• explain SQL：

select * from (select `user_id` from `t591`.`house_rel` where `type` in (1, 2) and `status` > 0 and `start_time` < 1793462400 and `end_time` > 1790784000 group by `user_id`) as a where `a`.`user_id` > 174179 order by `a`.`user_id` asc limit 5000;

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

• 计划标志：⚠️ filesort 排序
• 慢因：扫描行数远大于返回行数(39,459,663/5,000)、EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`house_rel` ADD INDEX `idx_type_status` (`type`, `status`);

• DDL 注：house_rel 与现有索引 tp(type, post_id) 前缀重复(type), 新索引生效后可评估删除旧索引

2. select um.status,sum(um.money) as sum_money from t591.user_money_detail inner join t591.userMoney as um on user_money_detail.the_id = um.id where user_money_det —— 1,947 次 × 4.66s = 2.52h

• 代表性 SQL：

select um.status,sum(um.money) as sum_money from `t591`.`user_money_detail` inner join `t591`.`userMoney` as `um` on `user_money_detail`.`the_id` = `um`.`id` where `user_money_detail`.`user_id` = '1446565' and `user_money_detail`.`pay_time` between '2026-10-01 00:00:00' and '2026-10-31 23:59:59' and `user_money_detail`.`sign` = '2' and `user_money_detail`.`combo_id` = '0' group by `um`.`status` /* ms:tw591ms-revenue */;

• 库表：t591.user_money_detail；实例 alias：prod-591/master
• explain SQL：

select um.status,sum(um.money) as sum_money from `t591`.`user_money_detail` inner join `t591`.`userMoney` as `um` on `user_money_detail`.`the_id` = `um`.`id` where `user_money_detail`.`user_id` = '1446565' and `user_money_detail`.`pay_time` between '2026-10-01 00:00:00' and '2026-10-31 23:59:59' and `user_money_detail`.`sign` = '2' and `user_money_detail`.`combo_id` = '0' group by `um`.`status` /* ms:tw591ms-revenue */;

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

• 计划标志：⚠️ filesort 排序、⚠️ 产生临时表
• 慢因：扫描行数远大于返回行数(502,100/2)、索引选择性差(user_money_detail: key=user_id 扫 375,590 行回表后仅剩 0.41%，建议把其余 WHERE 等值列并入复合索引)、DISTINCT/GROUP BY 产生临时表(大范围去重/聚合)、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`user_money_detail` ADD INDEX `idx_the_id_user_id_sign_combo_id` (`the_id`, `user_id`, `sign`, `combo_id`);

• DDL 注：user_money_detail 与现有索引 the_id(the_id) 前缀重复(the_id), 新索引生效后可评估删除旧索引

3. 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 次 × 6856.86s = 1.90h

• 代表性 SQL：

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 顺序）：

• 慢因：扫描行数远大于返回行数(4,776,473,926/21,804)、索引范围扫描量过大(market_community_agent_bid_record_detail: key=idx_created_at, rows=3,568,860)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

4. select * from (select user_id from t591.house_rel where type IN (?...) and status > ? and start_time < ? and end_time > ? group by user_id) as a order by a.user —— 1 次 × 4251.78s = 1.18h

• 代表性 SQL：

select * from (select `user_id` from `t591`.`house_rel` where `type` in (1, 2) and `status` > 0 and `start_time` < 1793462400 and `end_time` > 1790784000 group by `user_id`) as a order by `a`.`user_id` asc limit 5000;

• 库表：t591.house_rel；实例 alias：prod-591/master
• explain SQL：

select * from (select `user_id` from `t591`.`house_rel` where `type` in (1, 2) and `status` > 0 and `start_time` < 1793462400 and `end_time` > 1790784000 group by `user_id`) as a order by `a`.`user_id` asc limit 5000;

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

• 计划标志：⚠️ filesort 排序
• 慢因：扫描行数远大于返回行数(39,459,148/5,000)、EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`house_rel` ADD INDEX `idx_type_status` (`type`, `status`);

• DDL 注：house_rel 与现有索引 tp(type, post_id) 前缀重复(type), 新索引生效后可评估删除旧索引

5. select count(*) as aggregate from sale where status = ? and closed = ? and community_id = ? and kind IN (?...) and posttime >= ? /* ms:tw591ms-cdc */; —— 2,569 次 × 1.64s = 1.17h

• 代表性 SQL：

select count(*) as aggregate from `sale` where `status` = '2' and `closed` = '0' and `community_id` = '34840' and `kind` in ('6', '8', '9', '10', '24') and `posttime` >= '1792166400' /* 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` = '34840' and `kind` in ('6', '8', '9', '10', '24') and `posttime` >= '1792166400' /* ms:tw591ms-cdc */;

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

• 慢因：扫描行数远大于返回行数(7,930/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 —— 144 次 × 28.89s = 1.16h

• 代表性 SQL：

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,475,938/34)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `mysql`.`slow_log` ADD INDEX `idx_start_time` (`start_time`);

• DDL 注：slow_log 未取到表结构, 索引重复性未核对, 执行前请 SHOW INDEX 确认

7. select sum(store_pay) as sum_store_pay,sum(send_pay) as sum_send_pay from t591.user_money_detail where user_id = ? and pay_time between ? and ? and sign = ? and —— 1,178 次 × 3.49s = 1.14h

• 代表性 SQL：

select sum(store_pay) as sum_store_pay,sum(send_pay) as sum_send_pay from `t591`.`user_money_detail` where `user_id` = '2022457' and `pay_time` between '2026-10-01 00:00:00' and '2026-10-31 23:59:59' and `sign` = '1' and `the_id` <> '0' and `combo_id` = '0' limit 1 /* ms:tw591ms-revenue */;

• 库表：t591.user_money_detail；实例 alias：prod-591/master
• explain SQL：

select sum(store_pay) as sum_store_pay,sum(send_pay) as sum_send_pay from `t591`.`user_money_detail` where `user_id` = '2022457' and `pay_time` between '2026-10-01 00:00:00' and '2026-10-31 23:59:59' and `sign` = '1' and `the_id` <> '0' and `combo_id` = '0' limit 1 /* ms:tw591ms-revenue */;

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

• 慢因：扫描行数远大于返回行数(501,046/1)、索引选择性差(user_money_detail: key=user_id 扫 18,996 行回表后仅剩 0.2%，建议把其余 WHERE 等值列并入复合索引)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`user_money_detail` ADD INDEX `idx_user_id_sign_combo_id` (`user_id`, `sign`, `combo_id`);

• DDL 注：user_money_detail 与现有索引 user_id(user_id, combo_id) 前缀重复(user_id), 新索引生效后可评估删除旧索引

8. select * from t591_new.order_item_use where posttime between ? and ? and status = ? and (plangroup = ? or (plangroup = ? and name IN (?...))) /* ms:tw591ms-reve —— 1 次 × 3309.42s = 55.2min

• 代表性 SQL：

select * from `t591_new`.`order_item_use` where `posttime` between '2026-10-01 00:00:00' and '2026-10-31 23:59:59' and `status` = '1' and (`plangroup` = 'package' or (`plangroup` = 'bidding' and `name` in ('daily_bid_pc', 'daily_bid_mobile'))) /* ms:tw591ms-revenue */;

• 库表：t591_new.order_item_use；实例 alias：prod-591/master
• explain SQL：

select * from `t591_new`.`order_item_use` where `posttime` between '2026-10-01 00:00:00' and '2026-10-31 23:59:59' and `status` = '1' and (`plangroup` = 'package' or (`plangroup` = 'bidding' and `name` in ('daily_bid_pc', 'daily_bid_mobile'))) /* ms:tw591ms-revenue */;

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

• 慢因：索引范围扫描量过大(order_item_use: key=posttime, rows=2,880,042)、大结果集传输 SELECT * (Bytes_sent=107,494,864)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591_new`.`order_item_use` ADD INDEX `idx_status_plangroup_name` (`status`, `plangroup`, `name`);

逐条优化建议汇总

P 行 = 深析模式（指纹聚合，按全天累计耗时降序 P1 最大，是优化主战场）；T 行 = 单条耗时 Top3 的一次性慢 SQL（多为 bi/导出查询，治理手段通常是限流/降频/移到从库，而非建索引）。

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