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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. 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 次 × 5804.51s = 1.61h

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

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 —— 180 次 × 28.92s = 1.45h

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

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

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

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

• 代表性 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` >= '1791907200' /* 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` >= '1791907200' /* ms:tw591ms-cdc */;

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

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

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

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- —— 1,596 次 × 2.72s = 1.21h

• 代表性 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 * from t591_building_vps.build_media_rating_record where t591_building_vps.build_media_rating_record.hid IN (?...) /* ms:tw591ms-building-vps */; —— 211 次 × 9.58s = 33.7min

• 代表性 SQL：

select * from `t591_building_vps`.`build_media_rating_record` where `t591_building_vps`.`build_media_rating_record`.`hid` in (138484, 138485, 138486, 138487, 138488, 138489, 138490, 138491, 138492, 138493, 138494, 138495, 138496, 138497, 138498, 138499, 138500, 138501, 138502, 138503, 138504, 138505, 138506, 138507, 138508, 138509, 138510, 138511, 138512, 138513, 138514, 138515, 138516, 138517, 138518, 138519, 138520, 138521, 138522, 138523, 138524, 138525, 138526, 138527, 138528, 138529, 138530, 138531, 138532, 138533, 138534, 138535, 138536, 138537, 138538, 138539, 138540, 138541, 138542, 138543, 138544, 138545, 138546, 138547, 138548, 138549, 138550, 138551, 138552, 138553, 138554, 138555, 138556, 138557, 138558, 138559, 138560, 138561, 138562, 138563, 138564, 138565, 138566, 138567, 138568, 138569, 138570, 138571, 138572, 138573, 138574, 138575, 138576, 138577, 138578, 138579, 138580, 138581, 138582, 138583, 138584, 138585, 138586, 138587, 138588, 138589, 138590, 138591, 138592, 138593, 138594, 138595, 138596, 138597, 138598, 138599, 138600, 138601, 138602, 138603, 138604, 138605, 138606, 138607, 138608, 138609, 138610, 138611, 138612, 138613, 138614, 138615, 138616, 138617, 138618, 138619, 138620, 138621, 138622, 138623, 138624, 138625, 138626, 138627, 138628, 138629, 138630, 138631, 138632, 138633, 138634, 138635, 138636, 138637, 138638, 138639, 138640, 138641, 138642, 138643, 138644, 138645, 138646, 138647, 138648, 138649, 138650, 138651, 138652, 138653, 138654, 138655, 138656, 138657, 138658, 138659, 138660, 138661, 138662, 138663, 138664, 138665, 138666, 138667, 138668, 138669, 138670, 138671, 138672, 138673, 138674, 138675, 138676, 138677, 138678, 138679, 138680, 138681, 138682, 138683, 138684, 138685, 138686, 138687, 138688, 138689, 138690, 138691, 138692, 138693, 138694, 138695, 138696, 138697, 138698, 138699, 138700, 138701, 138702, 138703, 138704, 138705, 138706, 138707, 138708, 138709, 138710, 138711, 138712, 138713, 138714, 138715, 138716, 138717, 138718, 138719, 138720, 138721, 138722, 138723, 138724, 138725, 138726, 138727, 138728, 138729, 138730, 138731, 138732, 138733, 138734, 138735, 138736, 138737, 138738, 138739, 138740, 138741, 138742, 138743, 138744, 138745, 138746, 138747, 138748, 138749, 138750, 138751, 138752, 138753, 138754, 138755, 138756, 138757, 138758, 138759, 138760, 138761, 138762, 138763, 138764, 138765, 138766, 138767, 138768, 138769, 138770, 138771, 138772, 138773, 138774, 138775, 138776, 138777, 138778, 138779, 138780, 138781, 138782, 138783, 138784, 138785, 138786, 138787, 138788, 138789, 138790, 138791, 138792, 138793, 138794, 138795, 138796, 138797, 138798, 138799, 138800, 138801, 138802, 138803, 138804, 138805, 138806, 138807, 138808, 138809, 138810, 138811, 138812, 138813, 138814, 138815, 138816, 138817, 138818, 138819, 138820, 138821, 138822, 138823, 138824, 138825, 138826, 138827, 138828, 138829, 138830, 138831, 138832, 138833, 138834, 138835, 138836, 138837, 138838, 138839, 138840, 138841, 138842, 138843, 138844, 138845, 138846, 138847, 138848, 138849, 138850, 138851, 138852, 138853, 138854, 138855, 138856, 138857, 138858, 138859, 138860, 138861, 138862, 138863, 138864, 138865, 138866, 138867, 138868, 138869, 138870, 138871, 138872, 138873, 138874, 138875, 138876, 138877, 138878, 138879, 138880, 138881, 138882, 138883, 138884, 138885, 138886, 138887, 138888, 138889, 138890, 138891, 138892, 138893, 138894, 138895, 138896, 138897, 138898, 138899, 138900, 138901, 138902, 138903, 138904, 138905, 138906, 138907, 138908, 138909, 138910, 138911, 138912, 138913, 138914, 138915, 138916, 138917, 138918, 138919, 138920, 138921, 138922, 138923, 138924, 138925, 138926, 138927, 138928, 138929, 138930, 138931, 138932, 138933, 138934, 138935, 138936, 138937, 138938, 138939, 138940, 138941, 138942, 138943, 138944, 138945, 138946, 138947, 138948, 138949, 138950, 138951, 138952, 138953, 138954, 138955, 138956, 138957, 138958, 138959, 138960, 138961, 138962, 138963, 138964, 138965, 138966, 138967, 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=305,388,339)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道

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

• 代表性 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,438/8,591)、索引范围扫描量过大(ivr_ext_service_call_t591: key=project_idc_status, rows=1,535,808)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

7. select id, name, checkstatus, deleted, build_purpose from t591_new.market_community FORCE INDEX(idx_region_section) where status = ? and deleted = ? and checkst —— 1,099 次 × 1.52s = 27.8min

• 代表性 SQL：

select `id`, `name`, `checkstatus`, `deleted`, `build_purpose` from t591_new.market_community FORCE INDEX(`idx_region_section`) where `status` = '1' and `deleted` = '0' and `checkstatus` = '1' and `type` = '1' and `regionid` = '6' and `sectionid` = '67' /* ms:tw591ms-cdc */;

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

SELECT * FROM `t591_new`.`market_community` WHERE `status` = '1' LIMIT 10

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

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

ALTER TABLE `t591_new`.`market_community` ADD INDEX `idx_status` (`status`);

8. select * from external_sale where platform = ? and status = ? and region_id = ? and section_id = ? /* ms:tw591ms-cdc-distribute */; —— 553 次 × 2.56s = 23.6min

• 代表性 SQL：

select * from `external_sale` where `platform` = 'pacific' and `status` = '1' and `region_id` = '8' and `section_id` = '103' /* ms:tw591ms-cdc-distribute */;

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

select * from `t591`.`external_sale` where `platform` = 'pacific' and `status` = '1' and `region_id` = '8' and `section_id` = '103' /* ms:tw591ms-cdc-distribute */;

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

• 慢因：大结果集传输 SELECT * (Bytes_sent=11,790,654)
• 建议：改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`external_sale` ADD INDEX `idx_platform_status_region_id_section_id` (`platform`, `status`, `region_id`, `section_id`);

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

逐条优化建议汇总

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

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