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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

备份模式全天 48 条 / 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 */; —— 3,853 次 × 1.64s = 1.76h

• 代表性 SQL：

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

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

• 慢因：扫描行数远大于返回行数(7,882/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 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 次 × 5941.38s = 1.65h

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

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 —— 198 次 × 28.40s = 1.56h

• 代表性 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,407,819/137)
• 建议：补索引（按 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,964 次 × 2.83s = 1.54h

• 代表性 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 */; —— 224 次 × 8.64s = 32.3min

• 代表性 SQL：

select * from `t591_building_vps`.`build_media_rating_record` where `t591_building_vps`.`build_media_rating_record`.`hid` in (136475, 136476, 136477, 136478, 136479, 136480, 136481, 136482, 136483, 136484, 136485, 136486, 136487, 136488, 136489, 136490, 136491, 136492, 136493, 136494, 136495, 136496, 136497, 136498, 136499, 136500, 136501, 136502, 136503, 136504, 136505, 136506, 136507, 136508, 136509, 136510, 136511, 136512, 136513, 136514, 136515, 136516, 136517, 136518, 136519, 136520, 136521, 136522, 136523, 136524, 136525, 136526, 136527, 136528, 136529, 136530, 136531, 136532, 136533, 136534, 136535, 136536, 136537, 136538, 136539, 136540, 136541, 136542, 136543, 136544, 136545, 136546, 136547, 136548, 136549, 136550, 136551, 136552, 136553, 136554, 136555, 136556, 136557, 136558, 136559, 136560, 136561, 136562, 136563, 136564, 136565, 136566, 136567, 136568, 136569, 136570, 136571, 136572, 136573, 136574, 136575, 136576, 136577, 136578, 136579, 136580, 136581, 136582, 136583, 136584, 136585, 136586, 136587, 136588, 136589, 136590, 136591, 136592, 136593, 136594, 136595, 136596, 136597, 136598, 136599, 136600, 136601, 136602, 136603, 136604, 136605, 136606, 136607, 136608, 136609, 136610, 136611, 136612, 136613, 136614, 136615, 136616, 136617, 136618, 136619, 136620, 136621, 136622, 136623, 136624, 136625, 136626, 136627, 136628, 136629, 136630, 136631, 136632, 136633, 136634, 136635, 136636, 136637, 136638, 136639, 136640, 136641, 136642, 136643, 136644, 136645, 136646, 136647, 136648, 136649, 136650, 136651, 136652, 136653, 136654, 136655, 136656, 136657, 136658, 136659, 136660, 136661, 136662, 136663, 136664, 136665, 136666, 136667, 136668, 136669, 136670, 136671, 136672, 136673, 136674, 136675, 136676, 136677, 136678, 136679, 136680, 136681, 136682, 136683, 136684, 136685, 136686, 136687, 136688, 136689, 136690, 136691, 136692, 136693, 136694, 136695, 136696, 136697, 136698, 136699, 136700, 136701, 136702, 136703, 136704, 136705, 136706, 136707, 136708, 136709, 136710, 136711, 136712, 136713, 136714, 136715, 136716, 136717, 136718, 136719, 136720, 136721, 136722, 136723, 136724, 136725, 136726, 136727, 136728, 136729, 136730, 136731, 136732, 136733, 136734, 136735, 136736, 136737, 136738, 136739, 136740, 136741, 136742, 136743, 136744, 136745, 136746, 136747, 136748, 136749, 136750, 136751, 136752, 136753, 136754, 136755, 136756, 136757, 136758, 136759, 136760, 136761, 136762, 136763, 136764, 136765, 136766, 136767, 136768, 136769, 136770, 136771, 136772, 136773, 136774, 136775, 136776, 136777, 136778, 136779, 136780, 136781, 136782, 136783, 136784, 136785, 136786, 136787, 136788, 136789, 136790, 136791, 136792, 136793, 136794, 136795, 136796, 136797, 136798, 136799, 136800, 136801, 136802, 136803, 136804, 136805, 136806, 136807, 136808, 136809, 136810, 136811, 136812, 136813, 136814, 136815, 136816, 136817, 136818, 136819, 136820, 136821, 136822, 136823, 136824, 136825, 136826, 136827, 136828, 136829, 136830, 136831, 136832, 136833, 136834, 136835, 136836, 136837, 136838, 136839, 136840, 136841, 136842, 136843, 136844, 136845, 136846, 136847, 136848, 136849, 136850, 136851, 136852, 136853, 136854, 136855, 136856, 136857, 136858, 136859, 136860, 136861, 136862, 136863, 136864, 136865, 136866, 136867, 136868, 136869, 136870, 136871, 136872, 136873, 136874, 136875, 136876, 136877, 136878, 136879, 136880, 136881, 136882, 136883, 136884, 136885, 136886, 136887, 136888, 136889, 136890, 136891, 136892, 136893, 136894, 136895, 136896, 136897, 136898, 136899, 136900, 136901, 136902, 136903, 136904, 136905, 136906, 136907, 136908, 136909, 136910, 136911, 136912, 136913, 136914, 136915, 136916, 136917, 136918, 136919, 136920, 136921, 136922, 136923, 136924, 136925, 136926, 136927, 136928, 136929, 136930, 136931, 136932, 136933, 136934, 136935, 136936, 136937, 136938, 136939, 136940, 136941, 136942, 136943, 136944, 136945, 136946, 136947, 136948, 136949, 136950, 136951, 136952, 136953, 136954, 136955, 136956, 136957, 136958, 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=281,259,298)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道

6. select id, name, checkstatus, deleted, build_purpose from t591_new.market_community FORCE INDEX(idx_region_section) where status = ? and deleted = ? and checkst —— 448 次 × 2.42s = 18.1min

• 代表性 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` = '15' and `sectionid` = '230' /* 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,791/1,474)、EXPLAIN 全表扫描/未用索引
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

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

7. select count(*) as aggregate from t591.rent where user_id = ? and status = ? and closed = ?; —— 612 次 × 1.72s = 17.6min

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

8. select number from t591_ivr.ivr_ext_service_call_t591 where project_idc = ? and status != ? and start_time between ? and ? group by number; —— 547 次 × 1.90s = 17.3min

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

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

逐条优化建议汇总

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

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