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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

触发深度分析的模式（条数>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,376 次 × 1.78s = 2.17h

• 代表性 SQL：

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

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

• 慢因：扫描行数远大于返回行数(7,903/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.76s = 1.73h

• 代表性 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,445,134/26)
• 建议：补索引（按 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 次 × 6142.38s = 1.71h

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

4. SELECT * FROM view_activity_house LIMIT ?; —— 1 次 × 2391.44s = 39.9min

• 代表性 SQL：

SELECT * FROM `view_activity_house` LIMIT 100;

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

SELECT * FROM `t591`.`view_activity_house` LIMIT 100;

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

• 计划标志：⚠️ filesort 排序
• 慢因：扫描行数远大于返回行数(56,273,688/100)、索引选择性差(um: key=sign 扫 33,186,369 行回表后仅剩 0.02%，建议把其余 WHERE 等值列并入复合索引)、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围

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- —— 1,013 次 × 1.90s = 32.1min

• 代表性 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 确认

6. select number from t591_ivr.ivr_ext_service_call_t591 where project_idc = ? and status != ? and start_time between ? and ? group by number; —— 732 次 × 2.00s = 24.4min

• 代表性 SQL：

select `number` from t591_ivr.ivr_ext_service_call_t591 where `project_idc` = '0940500288' and `status` != 'invalidargs' and `start_time` between 1767196800 and 1798732799 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 顺序）：

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

7. select * from t591_building_vps.build_media_rating_record where t591_building_vps.build_media_rating_record.hid IN (?...) /* ms:tw591ms-building-vps */; —— 207 次 × 6.42s = 22.1min

• 代表性 SQL：

select * from `t591_building_vps`.`build_media_rating_record` where `t591_building_vps`.`build_media_rating_record`.`hid` in (137477, 137478, 137479, 137480, 137481, 137482, 137483, 137484, 137485, 137486, 137487, 137488, 137489, 137490, 137491, 137492, 137493, 137494, 137495, 137496, 137497, 137498, 137499, 137500, 137501, 137502, 137503, 137504, 137505, 137506, 137507, 137508, 137509, 137510, 137511, 137512, 137513, 137514, 137515, 137516, 137517, 137518, 137519, 137520, 137521, 137522, 137523, 137524, 137525, 137526, 137527, 137528, 137529, 137530, 137531, 137532, 137533, 137534, 137535, 137536, 137537, 137538, 137539, 137540, 137541, 137542, 137543, 137544, 137545, 137546, 137547, 137548, 137549, 137550, 137551, 137552, 137553, 137554, 137555, 137556, 137557, 137558, 137559, 137560, 137561, 137562, 137563, 137564, 137565, 137566, 137567, 137568, 137569, 137570, 137571, 137572, 137573, 137574, 137575, 137576, 137577, 137578, 137579, 137580, 137581, 137582, 137583, 137584, 137585, 137586, 137587, 137588, 137589, 137590, 137591, 137592, 137593, 137594, 137595, 137596, 137597, 137598, 137599, 137600, 137601, 137602, 137603, 137604, 137605, 137606, 137607, 137608, 137609, 137610, 137611, 137612, 137613, 137614, 137615, 137616, 137617, 137618, 137619, 137620, 137621, 137622, 137623, 137624, 137625, 137626, 137627, 137628, 137629, 137630, 137631, 137632, 137633, 137634, 137635, 137636, 137637, 137638, 137639, 137640, 137641, 137642, 137643, 137644, 137645, 137646, 137647, 137648, 137649, 137650, 137651, 137652, 137653, 137654, 137655, 137656, 137657, 137658, 137659, 137660, 137661, 137662, 137663, 137664, 137665, 137666, 137667, 137668, 137669, 137670, 137671, 137672, 137673, 137674, 137675, 137676, 137677, 137678, 137679, 137680, 137681, 137682, 137683, 137684, 137685, 137686, 137687, 137688, 137689, 137690, 137691, 137692, 137693, 137694, 137695, 137696, 137697, 137698, 137699, 137700, 137701, 137702, 137703, 137704, 137705, 137706, 137707, 137708, 137709, 137710, 137711, 137712, 137713, 137714, 137715, 137716, 137717, 137718, 137719, 137720, 137721, 137722, 137723, 137724, 137725, 137726, 137727, 137728, 137729, 137730, 137731, 137732, 137733, 137734, 137735, 137736, 137737, 137738, 137739, 137740, 137741, 137742, 137743, 137744, 137745, 137746, 137747, 137748, 137749, 137750, 137751, 137752, 137753, 137754, 137755, 137756, 137757, 137758, 137759, 137760, 137761, 137762, 137763, 137764, 137765, 137766, 137767, 137768, 137769, 137770, 137771, 137772, 137773, 137774, 137775, 137776, 137777, 137778, 137779, 137780, 137781, 137782, 137783, 137784, 137785, 137786, 137787, 137788, 137789, 137790, 137791, 137792, 137793, 137794, 137795, 137796, 137797, 137798, 137799, 137800, 137801, 137802, 137803, 137804, 137805, 137806, 137807, 137808, 137809, 137810, 137811, 137812, 137813, 137814, 137815, 137816, 137817, 137818, 137819, 137820, 137821, 137822, 137823, 137824, 137825, 137826, 137827, 137828, 137829, 137830, 137831, 137832, 137833, 137834, 137835, 137836, 137837, 137838, 137839, 137840, 137841, 137842, 137843, 137844, 137845, 137846, 137847, 137848, 137849, 137851, 137852, 137853, 137854, 137855, 137856, 137857, 137858, 137859, 137860, 137861, 137862, 137863, 137864, 137865, 137866, 137867, 137868, 137869, 137870, 137871, 137872, 137873, 137874, 137875, 137876, 137877, 137878, 137879, 137880, 137881, 137882, 137883, 137884, 137885, 137886, 137887, 137888, 137889, 137890, 137891, 137892, 137893, 137894, 137895, 137896, 137897, 137898, 137899, 137900, 137901, 137902, 137903, 137904, 137905, 137906, 137907, 137908, 137909, 137910, 137911, 137912, 137913, 137914, 137915, 137916, 137917, 137918, 137919, 137920, 137921, 137922, 137923, 137924, 137925, 137926, 137927, 137928, 137929, 137930, 137931, 137932, 137933, 137934, 137935, 137936, 137937, 137938, 137939, 137940, 137941, 137942, 137943, 137944, 137945, 137946, 137947, 137948, 137949, 137950, 137951, 137952, 137953, 137954, 137955, 137956, 137957, 137958, 137959, 137960, 137961, 13

• 库表：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=298,089,412)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道

8. select count(*) as aggregate from t591.rent where user_id = ? and status = ? and closed = ?; —— 647 次 × 1.73s = 18.7min

• 代表性 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), 新索引生效后可评估删除旧索引

逐条优化建议汇总

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

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