统计口径:2026-10-22 00:00:00 ~ 23:59:59(Asia/Shanghai),ES@timestampUTC 换算区间[2026-10-22T00:00:00+08:00, 2026-10-23T00:00:00+08:00),env=staging。
数据来源:MCPes_slow_logs(Top10)+es_search(高频模式,mysql_slow-*);MySQL 全程只读(connect / describe_schema / explain)。
生成时间:2026-10-23 10:00 +08:00。
commit;t8891.t_item_modi_histories(单条 26.2 分钟)commit;(171 条)、SHOW ENGINE INNODB STATUS(113 条)、gameList 联查(109 条);另对未达阈值但累计耗时的 h8_finance_account_logs 余额求和、t_sale_order_item COUNT 做了补充深挖| # | 耗时(s) | 用户 | 实例(server) | SQL 摘要 | rows_sent / examined | 慢因判定 |
|---|---|---|---|---|---|---|
| 1 | 1572.7 | backup | staging/8891(LAN_addcn_84) | SELECT * FROM t8891.t_item_modi_histories(mysqldump) | 5,693,964 / 5,693,964(EXPLAIN:ALL 全扫,~14G) | 大结果集传输(备份) |
| 2 | 1156.5 | backup | staging/8891 | SELECT * FROM t8891.t_sele_item_infos | 3,290,762 / 3,290,762(ALL,~20G) | 大结果集传输(备份) |
| 3 | 489.0 | backup | staging/8891 | SELECT * FROM t8891.yahoo_car_details | 16,681 / 16,681(ALL,~18M,宽行大字段) | 大结果集传输(备份) |
| 4 | 350.5 | backup | staging/8891 | SELECT * FROM t8891.t_subscription_push | 39,105,741 / 39,105,741(ALL,~33G) | 大结果集传输(备份) |
| 5 | 328.1 | backup | staging/8891 | SELECT * FROM t8891.t_sele_item_photos | 22,929,970 / 22,929,970(ALL,~80G) | 大结果集传输(备份) |
| 6 | 309.5 | backup | staging/100-slave(LAN_addcn_46) | SELECT * FROM t591.t1_daily_report | 30,309,750 / 30,309,750(ALL,~7G) | 大结果集传输(备份) |
| 7 | 290.6 | sonic | staging/8891 | commit;(rows_sent=0) | 0 / 0 | 大事务提交(redo flush/IO 争用) |
| 8 | 287.5 | backup | staging/TD-slave(LAN_addcn_51) | SELECT * FROM anylink.access_audit | 61,890,895 / 61,890,895(ALL,~72G) | 大结果集传输(备份) |
| 9 | 249.3 | backup | staging/tw8591(LAN_addcn_27_3307 → hk8591:3307) | SELECT * FROM hk8591.h8_finance_account_logs | 13,603,238 / 13,603,238 | 大结果集传输(备份) |
| 10 | 243.8 | backup | staging/TD-slave | SELECT * FROM anylink_corp.access_audit | 56,850,468 / 56,850,468(ALL,~65G) | 大结果集传输(备份) |
Top10 判定说明:
mysqldump 的 /*!40001 SQL_NO_CACHE */ * 全表导出,EXPLAIN 全部 access_type=ALL、rows=全表行数,属于备份固有行为,不建议按业务慢 SQL 处理;真正可优化点是备份窗口与目标表体积(见建议)。commit; 290s:语句本身无扫描,慢在事务提交阶段(大事务 redo 刷盘 / 备份期间 IO 争用),与 #1~#5 同机(staging/8891)同时段高度相关。| # | 条数 | 平均(s) | 最大(s) | 累计 | 用户 | server | SQL 骨架 | 备注 |
|---|---|---|---|---|---|---|---|---|
| 1 | 171 | 4.60 | 290.6 | 13.1m | 8591/addcn8891_test/bee/chenruijia/jumper_dev/sonic | 27/37/84 | commit; | 大事务提交,深度分析✔ |
| 2 | 113 | 1.45 | 2.99 | 2.7m | jmysql | 27_3307/37/40_3308/46 | SHOW ENGINE INNODB STATUS; | 监控采集,非业务 SQL |
| 3 | 109 | 1.05 | 1.21 | 1.9m | a8591 | 27 | SELECT a.id,... FROM gameList a LEFT JOIN hitCount_month b ... | 深度分析✔ |
| 4 | 91 | 1.70 | 4.81 | 2.6m | 8591/jmysql | 27/46 | # administrator command: Ping; | 连接池 Ping,非 SQL |
| 5 | 72 | 1.62 | 4.53 | 1.9m | 8591 | 27 | rollback; | 回滚,反映事务冲突/超时 |
| # | 条数 | 平均(s) | 最大(s) | 累计 | 用户 | server | SQL 骨架 | 备注 |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1572.7 | 1572.7 | 26.2m | backup | 84 | SELECT * FROM t8891.t_item_modi_histories | 【备份模式】 |
| 2 | 1 | 1156.5 | 1156.5 | 19.3m | backup | 84 | SELECT * FROM t8891.t_sele_item_infos | 【备份模式】 |
| 3 | 171 | 4.60 | 290.6 | 13.1m | 多用户 | 27/37/84 | commit; | 大事务提交 |
| 4 | 7 | 91.9 | 196.6 | 10.7m | t8891 | 84 | /*memberPoint*/SELECT COUNT(DISTINCT a.order_no) FROM t_sale_order_item a,t_sale_order b WHERE ... | 补充深挖✔ |
| 5 | 15 | 33.1 | 108.4 | 8.3m | 8591 | 27_3307 | SELECT sum(after_amount-before_amount) FROM h8_finance_account_logs WHERE user_id=? AND sub_account=? | 补充深挖✔ |
backup 模式说明:昨日各备份指纹均为单条出现,条数与历史常规持平,未触发深度分析。
commit;(171 条,累计 13.1m)— 触发:条数>100SHOW ENGINE INNODB STATUS(113 条 avg 1.45s)、Ping(91 条 avg 1.7s)同批变慢同样印证实例级 IO 压力。--single-transaction + 限流(iostat/ionice),或将大表备份放到 slave。SHOW ENGINE INNODB STATUS;(113 条)— 触发:条数>100SELECT a.id,a.name,a.color,a.setInfo+0,... FROM gameList a LEFT JOIN hitCount_month b ON a.id=b.hitId AND b.hitType='deal' AND b.themonth='202610' WHERE a.id NOT IN(65 个字面量) AND a.type='game' AND a.parent_id='0' AND find_in_set('denyShow',a.setInfo)<>2gameList 无二级索引(仅主键);hitCount_month 有 year/month/themonth/hitType/commendId(hitId) 单列索引。access_type=ALL 全扫 53,820 行、filtered 2%,attached_condition 含 find_in_set('denyShow', setInfo)<>2;ORDER BY 触发 using_temporary_table + using_filesort。find_in_set 不可走索引 + NOT IN 大列表。gameList 加组合索引 (type, parent_id)(等值条件前置,把 53,820 行过滤到千级);② setInfo 中的 denyShow 标志拆独立 tinyint 列(或位运算列)并纳入索引,替换 find_in_set;③ 65 个 NOT IN id 若为固定黑名单,考虑落地临时表 JOIN。h8_finance_account_logs 余额求和(15 条,avg 33.1s,累计 8.3m)SELECT sum(after_amount-before_amount) FROM hk8591.h8_finance_account_logs WHERE user_id=? AND sub_account=?(server LAN_addcn_27_3307 → 实例 staging/hk8591)。asacca(user_id) 仅用首列,rows_examined_per_scan≈33,434,filtered 10%,sub_account 靠 ICP 过滤;possible_keys 还有 idx_user_sub_account_id 未被选中。(user_id, sub_account, after_amount, before_amount)(若 idx_user_sub_account_id 即 (user_id,sub_account),可用 FORCE INDEX 或调优化器使其生效,聚簇统计过期时 ANALYZE TABLE);② 余额类查询考虑从流水表改为余额快照表/缓存,避免每次全量 SUM。t_sale_order_item × t_sale_order COUNT(7 条,avg 91.9s,累计 10.7m)/*memberPoint:setUpdateTime*/SELECT COUNT(DISTINCT a.order_no) FROM t_sale_order_item a, t_sale_order b WHERE a.order_no=b.order_no AND b.status>0 AND a.sale_code>=? AND a.sale_code<=? AND b.m_id=?(t8891,staging/8891)。t_sale_order_item 有 order_no(order_no,deal_item_id,related_item_id,sale_code,status)、deal_time、related_item_id、deal_item_id 索引;EXPLAIN:b 走 m_id 扫 31,686 行(status>0 过滤 50%),a 走 order_no 索引 using_index + ICP 过滤 sale_code 区间。t_sale_order 加组合索引 (m_id, status)(可含 order_no 作覆盖:(m_id, status, order_no));② t_sale_order_item 如该模式高频,评估 (sale_code, order_no) 索引让 sale_code 区间先收敛;③ 定时任务改为增量计算并缓存结果。h8_orders buyer IN 批量查询(29 条,avg 2.35s)bica(buyer_id,created_at),rows≈5,索引本身健康;慢主要来自 IN 列表数百个 id 逐一探测 + 实例整体 IO 偏高,优先级低,暂无需改索引。(type,parent_id) 索引;find_in_set('denyShow',setInfo) 改独立标志列。(user_id,sub_account) 复合索引生效并覆盖金额列;余额查询改快照/缓存。(m_id,status[,order_no]) 组合索引;memberPoint 统计改增量。