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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

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

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

• 代表性 SQL：

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

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

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

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

3. 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 次 × 29.07s = 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,496,075/85)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

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

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

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,799 次 × 2.73s = 1.37h

• 代表性 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 external_sale where platform = ? and status = ? and region_id = ? and section_id = ? /* ms:tw591ms-cdc-distribute */; —— 399 次 × 3.91s = 26.0min

• 代表性 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 顺序）：

• 慢因：扫描行数远大于返回行数(85,823/8,509)、大结果集传输 SELECT * (Bytes_sent=11,562,925)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 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), 新索引生效后可评估删除旧索引

6. select * from t591_building_vps.build_media_rating_record where t591_building_vps.build_media_rating_record.hid IN (?...) /* ms:tw591ms-building-vps */; —— 172 次 × 8.45s = 24.2min

• 代表性 SQL：

select * from `t591_building_vps`.`build_media_rating_record` where `t591_building_vps`.`build_media_rating_record`.`hid` in (139494, 139495, 139496, 139497, 139498, 139499, 139500, 139501, 139502, 139503, 139504, 139505, 139506, 139507, 139508, 139509, 139510, 139511, 139512, 139513, 139514, 139515, 139516, 139517, 139518, 139519, 139520, 139521, 139522, 139523, 139524, 139525, 139526, 139527, 139528, 139529, 139530, 139531, 139532, 139533, 139534, 139535, 139536, 139537, 139538, 139539, 139540, 139541, 139542, 139543, 139544, 139545, 139546, 139547, 139548, 139549, 139550, 139551, 139552, 139553, 139554, 139555, 139556, 139557, 139558, 139559, 139560, 139561, 139562, 139563, 139564, 139565, 139566, 139567, 139568, 139569, 139570, 139571, 139572, 139573, 139574, 139575, 139576, 139577, 139578, 139579, 139580, 139581, 139582, 139583, 139584, 139585, 139586, 139587, 139588, 139589, 139590, 139591, 139592, 139593, 139594, 139595, 139596, 139597, 139598, 139599, 139600, 139601, 139602, 139603, 139604, 139605, 139606, 139607, 139608, 139609, 139610, 139611, 139612, 139613, 139614, 139615, 139616, 139617, 139618, 139619, 139620, 139621, 139622, 139623, 139624, 139625, 139626, 139627, 139628, 139629, 139630, 139631, 139632, 139633, 139634, 139635, 139636, 139637, 139638, 139639, 139640, 139641, 139642, 139643, 139644, 139645, 139646, 139647, 139648, 139649, 139650, 139651, 139652, 139653, 139654, 139655, 139656, 139657, 139658, 139659, 139660, 139661, 139662, 139663, 139664, 139665, 139666, 139667, 139668, 139669, 139670, 139671, 139672, 139673, 139674, 139675, 139676, 139677, 139678, 139679, 139680, 139681, 139682, 139683, 139684, 139685, 139686, 139687, 139688, 139689, 139690, 139691, 139692, 139693, 139694, 139695, 139696, 139697, 139698, 139699, 139700, 139701, 139702, 139703, 139704, 139705, 139706, 139707, 139708, 139709, 139710, 139711, 139712, 139713, 139714, 139715, 139716, 139717, 139718, 139719, 139720, 139721, 139722, 139723, 139724, 139725, 139726, 139727, 139728, 139729, 139730, 139731, 139732, 139733, 139734, 139735, 139736, 139737, 139738, 139739, 139740, 139741, 139742, 139743, 139744, 139745, 139746, 139747, 139748, 139749, 139750, 139751, 139752, 139753, 139754, 139755, 139756, 139757, 139758, 139759, 139760, 139761, 139762, 139763, 139764, 139765, 139766, 139767, 139768, 139769, 139770, 139771, 139772, 139773, 139774, 139775, 139776, 139777, 139778, 139779, 139780, 139781, 139782, 139783, 139784, 139785, 139786, 139787, 139788, 139789, 139790, 139791, 139792, 139793, 139794, 139795, 139796, 139797, 139798, 139799, 139800, 139801, 139802, 139803, 139804, 139805, 139806, 139807, 139808, 139809, 139810, 139811, 139812, 139813, 139814, 139815, 139816, 139817, 139818, 139819, 139820, 139821, 139822, 139823, 139824, 139825, 139826, 139827, 139828, 139829, 139830, 139831, 139832, 139833, 139834, 139835, 139836, 139837, 139838, 139839, 139840, 139841, 139842, 139843, 139844, 139845, 139846, 139847, 139848, 139849, 139850, 139851, 139852, 139853, 139854, 139855, 139856, 139857, 139858, 139859, 139860, 139861, 139862, 139863, 139864, 139865, 139866, 139867, 139868, 139869, 139870, 139871, 139872, 139873, 139874, 139875, 139876, 139877, 139878, 139879, 139880, 139881, 139882, 139883, 139884, 139885, 139886, 139887, 139888, 139889, 139890, 139891, 139892, 139893, 139894, 139895, 139896, 139897, 139898, 139899, 139900, 139901, 139902, 139903, 139904, 139905, 139906, 139907, 139908, 139910, 139911, 139912, 139913, 139914, 139915, 139916, 139917, 139918, 139919, 139920, 139921, 139922, 139923, 139924, 139925, 139926, 139927, 139928, 139929, 139931, 139932, 139933, 139934, 139935, 139936, 139937, 139938, 139939, 139940, 139941, 139942, 139943, 139944, 139945, 139946, 139947, 139948, 139949, 139950, 139951, 139952, 139953, 139954, 139955, 139956, 139957, 139958, 139959, 139960, 139961, 139962, 139963, 139964, 139965, 139966, 139967, 139968, 139969, 139970, 139971, 139972, 139973, 139974, 139975, 139976, 139977, 139978, 139979, 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=321,849,177)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道

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 —— 784 次 × 1.71s = 22.3min

• 代表性 SQL：

select sum(`ivr_call`) as aggregate from `build_stat_total` where `theday` = '20261102' 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` <= '1793847362' and `tupd`.`end_time` >= '1793847362' and (`tupd`.`close` = '0' or (`tupd`.`close` in ('1', '2') and `tupd`.`close_time` > '1793847362'))) /* 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 顺序）：

• 慢因：扫描行数远大于返回行数(77,870/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 id, out_id, land_no, region_id, section_id, sect_id from land_mark where region_id = ? and section_id = ? and ((sect_id = ? and land_no IN (?...))) /* ms —— 650 次 × 1.72s = 18.7min

• 代表性 SQL：

select `id`, `out_id`, `land_no`, `region_id`, `section_id`, `sect_id` from `land_mark` where `region_id` = '8' and `section_id` = '110' and ((`sect_id` = '948' and `land_no` in ('0306-0000'))) /* ms:tw591ms-trend-price */;

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

select `id`, `out_id`, `land_no`, `region_id`, `section_id`, `sect_id` from `t591_transcription`.`land_mark` where `region_id` = '8' and `section_id` = '110' and ((`sect_id` = '948' and `land_no` in ('0306-0000'))) /* ms:tw591ms-trend-price */;

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

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

ALTER TABLE `t591_transcription`.`land_mark` ADD INDEX `idx_region_id_section_id_sect_id_land_no` (`region_id`, `section_id`, `sect_id`, `land_no`);

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

逐条优化建议汇总

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

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