MySQL 慢查询日报（线上 prod591）2026-10-27

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

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

• 代表性 SQL：

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

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

• 慢因：扫描行数远大于返回行数(7,912/1)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`sale` ADD INDEX `idx_status_closed_community__kind_posttime` (`status`, `closed`, `community_id`, `kind`, `posttime`);

2. 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 次 × 28.96s = 1.74h

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

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

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

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 次 × 5831.96s = 1.62h

• 代表性 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,286,335,310/20,380)、索引范围扫描量过大(market_community_agent_bid_record_detail: key=idx_created_at, rows=3,568,860)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

4. 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,079 次 × 2.68s = 1.55h

• 代表性 SQL：

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 */

• 库表：?.im_conversations；实例 alias：None
• explain：未执行（无 alias(该 server 无可连 MCP 实例)）
• 慢因：IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS)
• 建议：改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `im_conversations` ADD INDEX `idx_app_id_uid_conversati_deleted_at` (`app_id`, `uid`, `conversation`, `deleted_at`);

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

5. select number from t591_ivr.ivr_ext_service_call_t591 where project_idc = ? and status != ? and start_time between ? and ? group by number; —— 905 次 × 2.07s = 31.2min

• 代表性 SQL：

select `number` from t591_ivr.ivr_ext_service_call_t591 where `project_idc` = '0940500288' and `status` != 'invalidargs' and `start_time` between 1788192000 and 1790783999 group by `number`;

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

SELECT * FROM `t591_ivr`.`ivr_ext_service_call_t591` WHERE `project_idc` = '0940500288' LIMIT 10

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

• 慢因：扫描行数远大于返回行数(873,087/8,570)、索引范围扫描量过大(ivr_ext_service_call_t591: key=project_idc_status, rows=1,534,460)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

6. select count(*) as aggregate from t591.rent where user_id = ? and status = ? and closed = ?; —— 921 次 × 1.81s = 27.8min

• 代表性 SQL：

select count(*) as aggregate from `t591`.`rent` where `user_id` = 654045 and `status` = 2 and `closed` = 0;

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

select count(*) as aggregate from `t591`.`rent` where `user_id` = 654045 and `status` = 2 and `closed` = 0;

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

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

ALTER TABLE `t591`.`rent` ADD INDEX `idx_user_id_status_closed` (`user_id`, `status`, `closed`);

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

7. 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 —— 838 次 × 1.83s = 25.6min

• 代表性 SQL：

select sum(`ivr_call`) as aggregate from `build_stat_total` where `theday` = '20261026' and `cate_id` = '2' 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 ('6') and `tupd`.`deleted` = '0' and `tupd`.`start_time` <= '1793067993' and `tupd`.`end_time` >= '1793067993' and (`tupd`.`close` = '0' or (`tupd`.`close` in ('1', '2') and `tupd`.`close_time` > '1793067993'))) /* ms:tw591ms-advertising */;

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

SELECT * FROM `t591_new`.`union_package_detail` WHERE `deleted` = '0' LIMIT 10

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

• 慢因：扫描行数远大于返回行数(79,559/1)、EXPLAIN 全表扫描/未用索引、IN (SELECT ...) 子查询(易退化为相关子查询逐行执行, 建议改 JOIN/EXISTS)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改写为 JOIN/EXISTS 并验证子查询能下推; 该实例无 MCP 映射, 建议在库上手动 EXPLAIN 复核
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591_new`.`union_package_detail` ADD INDEX `idx_deleted` (`deleted`);

8. select * from t591_building_vps.build_media_rating_record where t591_building_vps.build_media_rating_record.hid IN (?...) /* ms:tw591ms-building-vps */; —— 226 次 × 6.10s = 23.0min

• 代表性 SQL：

select * from `t591_building_vps`.`build_media_rating_record` where `t591_building_vps`.`build_media_rating_record`.`hid` in (140484, 140485, 140486, 140487, 140488, 140489, 140490, 140491, 140492, 140493, 140494, 140495, 140496, 140497, 140498, 140499, 140500, 140501, 140502, 140503, 140504, 140505, 140506, 140507, 140508, 140509, 140510, 140511, 140512, 140513, 140514, 140515, 140516, 140517, 140518, 140519, 140520, 140521, 140522, 140523, 140524, 140525, 140526, 140528, 140529, 140530, 140531, 140532, 140533, 140534, 140535, 140536, 140537, 140538, 140539, 140540, 140541, 140542, 140543, 140544, 140545, 140546, 140547, 140548, 140549, 140550, 140551, 140552, 140553, 140554, 140556, 140557, 140558, 140559, 140560, 140561, 140562, 140563, 140564, 140565, 140566, 140567, 140568, 140569, 140570, 140571, 140572, 140573, 140574, 140575, 140576, 140577, 140578, 140579, 140580, 140581, 140582, 140583, 140584, 140585, 140586, 140587, 140588, 140589, 140590, 140591, 140592, 140593, 140594, 140595, 140596, 140597, 140598, 140599, 140600, 140601, 140602, 140603, 140604, 140605, 140606, 140607, 140608, 140609, 140610, 140611, 140612, 140613, 140614, 140615, 140616, 140617, 140618, 140619, 140620, 140621, 140622, 140623, 140624, 140625, 140626, 140627, 140628, 140629, 140630, 140631, 140632, 140633, 140634, 140635, 140636, 140637, 140638, 140639, 140640, 140641, 140642, 140643, 140644, 140645, 140646, 140647, 140648, 140649, 140650, 140651, 140652, 140653, 140654, 140655, 140656, 140657, 140658, 140659, 140660, 140661, 140662, 140663, 140664, 140665, 140666, 140667, 140668, 140669, 140670, 140671, 140672, 140673, 140674, 140675, 140676, 140677, 140678, 140679, 140680, 140681, 140682, 140683, 140684, 140685, 140686, 140687, 140688, 140689, 140690, 140691, 140692, 140693, 140694, 140695, 140696, 140697, 140698, 140699, 140700, 140701, 140702, 140703, 140704, 140705, 140706, 140707, 140708, 140709, 140710, 140711, 140712, 140713, 140714, 140715, 140716, 140717, 140718, 140719, 140720, 140721, 140722, 140723, 140724, 140725, 140726, 140727, 140728, 140729, 140730, 140731, 140732, 140733, 140734, 140735, 140736, 140737, 140738, 140739, 140740, 140741, 140742, 140743, 140744, 140745, 140746, 140747, 140748, 140749, 140750, 140751, 140752, 140753, 140754, 140755, 140756, 140757, 140758, 140759, 140760, 140761, 140762, 140763, 140764, 140765, 140766, 140767, 140768, 140769, 140770, 140771, 140772, 140773, 140774, 140775, 140776, 140777, 140778, 140779, 140780, 140781, 140782, 140783, 140784, 140785, 140786, 140787, 140788, 140789, 140790, 140791, 140792, 140793, 140794, 140795, 140796, 140797, 140798, 140799, 140800, 140801, 140802, 140803, 140804, 140805, 140806, 140807, 140808, 140809, 140810, 140811, 140812, 140813, 140814, 140815, 140816, 140817, 140818, 140819, 140820, 140821, 140822, 140823, 140824, 140825, 140826, 140827, 140828, 140829, 140830, 140831, 140832, 140833, 140834, 140835, 140836, 140837, 140838, 140839, 140840, 140841, 140842, 140843, 140844, 140845, 140846, 140847, 140848, 140849, 140850, 140851, 140852, 140853, 140854, 140855, 140856, 140857, 140858, 140859, 140860, 140861, 140862, 140863, 140864, 140865, 140866, 140867, 140868, 140869, 140870, 140871, 140872, 140873, 140874, 140875, 140876, 140877, 140878, 140879, 140880, 140881, 140882, 140883, 140884, 140885, 140886, 140887, 140888, 140889, 140890, 140891, 140892, 140893, 140894, 140895, 140896, 140897, 140898, 140899, 140900, 140901, 140902, 140903, 140904, 140905, 140906, 140907, 140912, 140913, 140914, 140919, 140920, 140921, 140922, 140923, 140924, 140925, 140926, 140927, 140928, 140929, 140930, 140931, 140932, 140933, 140934, 140935, 140936, 140937, 140938, 140939, 140940, 140941, 140942, 140943, 140944, 140945, 140946, 140947, 140948, 140949, 140950, 140951, 140952, 140953, 140954, 140955, 140956, 140957, 140958, 140959, 140960, 140961, 140962, 140963, 140964, 140965, 140966, 140967, 140968, 140969, 140970, 140971, 140972, 140973, 140974, 140975, 140976, 140977, 14

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

SELECT * FROM `t591_building_vps`.`build_media_rating_record` LIMIT 10

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

• 慢因：EXPLAIN 全表扫描/未用索引、大结果集传输 SELECT * (Bytes_sent=300,733,963)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道

逐条优化建议汇总

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

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