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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

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

1. SELECT c.* FROM t8_user_id_card_number_list AS c WHERE c.img_type = ? AND c.status = ? AND c.image != ? AND (c.ocr_result = ? or c.ocr_result is null) ORDER BY —— 2,983 次 × 3.48s = 2.88h

• 代表性 SQL：

SELECT c.* FROM t8_user_id_card_number_list AS c WHERE c.img_type = 2 AND c.status = 2 AND c.image != '' AND (c.ocr_result = '' or c.ocr_result is null) ORDER BY c.id ASC,`c`.`id` LIMIT 1;

• 库表：a8591.t8_user_id_card_number_list；实例 alias：prod-8591/8591-a
• explain SQL：

SELECT c.* FROM `a8591`.`t8_user_id_card_number_list` AS c WHERE c.img_type = 2 AND c.status = 2 AND c.image != '' AND (c.ocr_result = '' or c.ocr_result is null) ORDER BY c.id ASC,`c`.`id` LIMIT 1;

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

• 慢因：扫描行数远大于返回行数(827,320/0)、全索引扫描(c: key=PRIMARY, rows=1，缺更优过滤索引)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `a8591`.`t8_user_id_card_number_list` ADD INDEX `idx_img_type_status_ocr_result` (`img_type`, `status`, `ocr_result`);

2. select wallet, sum(if(type = ?, amount, ?)) - sum(if(type = ?, amount, ?)) as balance from a8591.t8_money_trade_log where account = ? and created_at <= ? group —— 142 次 × 56.38s = 2.22h

• 代表性 SQL：

select wallet, sum(if(type = 2, amount, 0)) - sum(if(type = 1, amount, 0)) as balance from `a8591`.`t8_money_trade_log` where `account` = 'A0100' and `created_at` <= '2026-10-19 00:03:19' group by `wallet`;

• 库表：a8591.t8_money_trade_log；实例 alias：prod-8591/8591-a
• explain SQL：

select wallet, sum(if(type = 2, amount, 0)) - sum(if(type = 1, amount, 0)) as balance from `a8591`.`t8_money_trade_log` where `account` = 'A0100' and `created_at` <= '2026-10-19 00:03:19' group by `wallet`;

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

• 慢因：扫描行数远大于返回行数(21,413,734/2)、索引范围扫描量过大(t8_money_trade_log: key=idx_aw, rows=29,267,252)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `a8591`.`t8_money_trade_log` ADD INDEX `idx_account_created_at` (`account`, `created_at`);

• DDL 注：t8_money_trade_log 与现有索引 idx_aw(account, wallet) 前缀重复(account), 新索引生效后可评估删除旧索引

3. select t1_design_img.id, t1_design_img.views, t1_design_img.collection, t1_design_img.favor_count, t1_design_img.comment_count, t1_design_img.pin_share_count + —— 2,720 次 × 1.39s = 1.05h

• 代表性 SQL：

select `t1_design_img`.`id`, `t1_design_img`.`views`, `t1_design_img`.`collection`, `t1_design_img`.`favor_count`, `t1_design_img`.`comment_count`, t1_design_img.pin_share_count + t1_design_img.fb_share_count + t1_design_img.line_share_count as share_count, t1_design_img.downs + t1_design_img.save_count + t1_design_img.screenshot_count as download_count from `t1_design_img` inner join `t1_design_work` on `t1_design_img`.`w_id` = `t1_design_work`.`id` where `t1_design_work`.`check_status` = 1 and `t1_design_work`.`is_show` = 1 and `t1_design_work`.`is_hide` = 0 and `t1_design_work`.`publish_time` <= '2026-10-19 00:04:23' and `t1_design_img`.`id` > 17420 order by `t1_design_img`.`id` asc limit 100;

• 库表：t591.t1_design_img；实例 alias：prod-8591/100
• explain SQL：

select `t1_design_img`.`id`, `t1_design_img`.`views`, `t1_design_img`.`collection`, `t1_design_img`.`favor_count`, `t1_design_img`.`comment_count`, t1_design_img.pin_share_count + t1_design_img.fb_share_count + t1_design_img.line_share_count as share_count, t1_design_img.downs + t1_design_img.save_count + t1_design_img.screenshot_count as download_count from `t591`.`t1_design_img` inner join `t591`.`t1_design_work` on `t1_design_img`.`w_id` = `t1_design_work`.`id` where `t1_design_work`.`check_status` = 1 and `t1_design_work`.`is_show` = 1 and `t1_design_work`.`is_hide` = 0 and `t1_design_work`.`publish_time` <= '2026-10-19 00:04:23' and `t1_design_img`.`id` > 17420 order by `t1_design_img`.`id` asc limit 100;

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

• 计划标志：⚠️ filesort 排序、⚠️ 产生临时表
• 慢因：扫描行数远大于返回行数(596,901/100)、DISTINCT/GROUP BY 产生临时表(大范围去重/聚合)、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`t1_design_img` ADD INDEX `idx_w_id_id` (`w_id`, `id`);
ALTER TABLE `t591`.`t1_design_work` ADD INDEX `idx_check_stat_is_show_is_hide_publish_ti` (`check_status`, `is_show`, `is_hide`, `publish_time`);

• DDL 注：t1_design_img 与现有索引 w_id(w_id) 前缀重复(w_id), 新索引生效后可评估删除旧索引
• DDL 注：t1_design_work 与现有索引 check_status(check_status, is_hide, is_show, publish_time) 前缀重复(check_status), 新索引生效后可评估删除旧索引

4. SELECT * FROM t8_mall_stat ORDER BY created_at DESC; —— 1 次 × 2876.16s = 47.9min

• 代表性 SQL：

SELECT *
FROM t8_mall_stat
ORDER BY created_at DESC;

• 库表：a8591.t8_mall_stat；实例 alias：prod-8591/8591-a
• explain SQL：

SELECT *
FROM `a8591`.`t8_mall_stat`
ORDER BY created_at DESC;

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

• 计划标志：⚠️ filesort 排序
• 慢因：EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)、大结果集传输 SELECT * (Bytes_sent=2,766,722,199)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `a8591`.`t8_mall_stat` ADD INDEX `idx_created_at` (`created_at`);

5. select i.*, COALESCE(v.views_30d, ?) AS views_30d, COALESCE(vip.vip_level, ?) AS vip_level, COALESCE(uc.collections_30d, ?) AS collections_30d from t1_design_im —— 782 次 × 2.99s = 39.0min

• 代表性 SQL：

select i.*, COALESCE(v.views_30d, 0) AS views_30d, COALESCE(vip.vip_level, 0) AS vip_level, COALESCE(uc.collections_30d, 0) AS collections_30d from `t1_design_img` as `i` left join `t1_design_work` as `w` on `w`.`id` = `i`.`w_id` left join (select `c`.`company_id`, `c`.`work_id`, MAX(c.level_order) AS vip_level from `t1_vip_benefit_calendar` as `c` where `c`.`date` = '2026-10-19' and `c`.`benefit_type` = 'work_top' and `c`.`region_id` > 0 group by `c`.`company_id`, `c`.`work_id`) as `vip` on `vip`.`company_id` = `w`.`design_id` and `vip`.`work_id` = `i`.`w_id` left join (select `source_id`, COALESCE(SUM(view_count), 0) AS views_30d from `t1_visits` where `source_type` = 'img' and `date` >= '2026-09-19' group by `source_id`) as `v` on `v`.`source_id` = `i`.`id` left join (select `work_id`, COUNT(*) AS collections_30d from `t1_user_collect` where `type` = 'img' and `created_at` >= '2026-09-19 03:07:36' group by `work_id`) as `uc` on `uc`.`work_id` = `i`.`id` where `i`.`id` > 125450 order by `i`.`id` asc limit 1000;

• 库表：t591.t1_design_img；实例 alias：prod-8591/100
• explain SQL：

select i.*, COALESCE(v.views_30d, 0) AS views_30d, COALESCE(vip.vip_level, 0) AS vip_level, COALESCE(uc.collections_30d, 0) AS collections_30d from `t591`.`t1_design_img` as `i` left join `t591`.`t1_design_work` as `w` on `w`.`id` = `i`.`w_id` left join (select `c`.`company_id`, `c`.`work_id`, MAX(c.level_order) AS vip_level from `t591`.`t1_vip_benefit_calendar` as `c` where `c`.`date` = '2026-10-19' and `c`.`benefit_type` = 'work_top' and `c`.`region_id` > 0 group by `c`.`company_id`, `c`.`work_id`) as `vip` on `vip`.`company_id` = `w`.`design_id` and `vip`.`work_id` = `i`.`w_id` left join (select `source_id`, COALESCE(SUM(view_count), 0) AS views_30d from `t591`.`t1_visits` where `source_type` = 'img' and `date` >= '2026-09-19' group by `source_id`) as `v` on `v`.`source_id` = `i`.`id` left join (select `work_id`, COUNT(*) AS collections_30d from `t591`.`t1_user_collect` where `type` = 'img' and `created_at` >= '2026-09-19 03:07:36' group by `work_id`) as `uc` on `uc`.`work_id` = `i`.`id` where `i`.`id` > 125450 order by `i`.`id` asc limit 1000;

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

• 慢因：其他(需结合 SQL 与执行计划人工判断)
• 建议：结合 SQL 文本与执行计划人工复核; 考虑降频/缓存
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t591`.`t1_vip_benefit_calendar` ADD INDEX `idx_benefit_ty_region_id` (`benefit_type`, `region_id`);

• DDL 注：t1_vip_benefit_calendar 与现有索引 idx_benefit_date(benefit_type, date) 前缀重复(benefit_type), 新索引生效后可评估删除旧索引

6. SELECT GROUP_CONCAT(mall_id) AS del_mall_ids FROM (SELECT mall_id FROM t8_ware_mall_logging WHERE action_time BETWEEN ? AND ? AND action_type = ? LIMIT ?) a; —— 292 次 × 7.61s = 37.1min

• 代表性 SQL：

SELECT GROUP_CONCAT(mall_id) AS del_mall_ids FROM (SELECT mall_id FROM t8_ware_mall_logging WHERE action_time BETWEEN '2026-09-21 04:30:02' AND '2026-10-05 04:30:02' AND action_type = 'del' LIMIT 100) a;

• 库表：t8_extends.t8_ware_mall_logging；实例 alias：prod-8591/8591-b
• explain SQL：

SELECT GROUP_CONCAT(mall_id) AS del_mall_ids FROM (SELECT mall_id FROM `t8_extends`.`t8_ware_mall_logging` WHERE action_time BETWEEN '2026-09-21 04:30:02' AND '2026-10-05 04:30:02' AND action_type = 'del' LIMIT 100) a;

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

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

ALTER TABLE `t8_extends`.`t8_ware_mall_logging` ADD INDEX `idx_action_typ` (`action_type`);

7. SELECT g.id AS game_id, g.name, pg.id AS platform_game_id, pg.platform_app_id FROM t8_platform_games pg INNER JOIN t8_games g ON g.id = pg.game_id WHERE pg.plat —— 1,080 次 × 1.88s = 33.8min

• 代表性 SQL：

SELECT
                    g.id AS game_id,
                    g.name,
                    pg.id AS platform_game_id,
                    pg.platform_app_id
                FROM t8_platform_games pg
                INNER JOIN t8_games g
                    ON g.id = pg.game_id
                WHERE pg.platform_id = 1
                  AND pg.enable = 1
                  AND pg.id > 0
                  AND pg.platform_app_id IS NOT NULL
                  AND pg.platform_app_id <> ''
                  AND g.type = 'game'
                  AND g.enable = 1
                  AND NOT EXISTS (
                      SELECT 1
                      FROM t8_platform_game_media pgm
                      WHERE pgm.platform_game_id = pg.id
                        AND pgm.scene_type = 7
                      LIMIT 1
                  )
                ORDER BY pg.id ASC
                LIMIT 450;

• 库表：t8_game.t8_platform_games；实例 alias：prod-8591/8591-b
• explain SQL：

SELECT
                    g.id AS game_id,
                    g.name,
                    pg.id AS platform_game_id,
                    pg.platform_app_id
                FROM `t8_game`.`t8_platform_games` pg
                INNER JOIN `t8_game`.`t8_games` g
                    ON g.id = pg.game_id
                WHERE pg.platform_id = 1
                  AND pg.enable = 1
                  AND pg.id > 0
                  AND pg.platform_app_id IS NOT NULL
                  AND pg.platform_app_id <> ''
                  AND g.type = 'game'
                  AND g.enable = 1
                  AND NOT EXISTS (
                      SELECT 1
                      FROM `t8_game`.`t8_platform_game_media` pgm
                      WHERE pgm.platform_game_id = pg.id
                        AND pgm.scene_type = 7
                      LIMIT 1
                  )
                ORDER BY pg.id ASC
                LIMIT 450;

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

• 计划标志：⚠️ filesort 排序、⚠️ 产生临时表
• 慢因：扫描行数远大于返回行数(1,853,544/450)、DISTINCT/GROUP BY 产生临时表(大范围去重/聚合)、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `t8_game`.`t8_platform_games` ADD INDEX `idx_game_id_platform_i_enable_platform_a_id` (`game_id`, `platform_id`, `enable`, `platform_app_id`, `id`);
ALTER TABLE `t8_game`.`t8_games` ADD INDEX `idx_type_enable` (`type`, `enable`);
ALTER TABLE `t8_game`.`t8_platform_game_media` ADD INDEX `idx_platform_g_scene_type` (`platform_game_id`, `scene_type`);

• DDL 注：t8_platform_games 与现有索引 idx_bid(game_id) 前缀重复(game_id), 新索引生效后可评估删除旧索引
• DDL 注：t8_games 与现有索引 type(type) 前缀重复(type), 新索引生效后可评估删除旧索引
• DDL 注：t8_platform_game_media 与现有索引 idx_paid(platform_game_id) 前缀重复(platform_game_id), 新索引生效后可评估删除旧索引

8. SELECT id FROM ware_item WHERE id > ? AND status > ? AND (actionTime BETWEEN ? AND ? ) ORDER BY id ASC LIMIT ?; —— 1,080 次 × 1.65s = 29.7min

• 代表性 SQL：

SELECT `id` FROM `ware_item` WHERE id > 0 AND status > 0 AND (actionTime BETWEEN 1792346130 AND 1792349670 ) ORDER BY id ASC LIMIT 1000;

• 库表：a8591.ware_item；实例 alias：prod-8591/8591-a
• explain SQL：

SELECT `id` FROM `a8591`.`ware_item` WHERE id > 0 AND status > 0 AND (actionTime BETWEEN 1792346130 AND 1792349670 ) ORDER BY id ASC LIMIT 1000;

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

• 慢因：扫描行数远大于返回行数(3,215,958/615)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

逐条优化建议汇总

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

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