MySQL 慢查询日报（线上 prod8591）2026-11-01

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

触发深度分析的模式（条数>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 —— 3,714 次 × 3.60s = 3.71h

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

• 慢因：扫描行数远大于返回行数(829,956/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 * FROM t8_mall_stat ORDER BY created_at DESC; —— 2 次 × 4734.58s = 2.63h

• 代表性 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,823,090,367)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

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

3. select wallet, sum(if(type = ?, amount, ?)) - sum(if(type = ?, amount, ?)) as balance from a8591.t8_money_trade_log where account = ? and created_at <= ? group —— 106 次 × 48.82s = 1.44h

• 代表性 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-11-01 03:59:50' 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-11-01 03:59:50' group by `wallet`;

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

• 慢因：扫描行数远大于返回行数(21,578,583/2)、索引范围扫描量过大(t8_money_trade_log: key=idx_aw, rows=29,809,160)
• 建议：补索引（按 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), 新索引生效后可评估删除旧索引

4. 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 —— 868 次 × 5.63s = 1.36h

• 代表性 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 排序、⚠️ 产生临时表
• 慢因：扫描行数远大于返回行数(2,695,763/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), 新索引生效后可评估删除旧索引

5. SELECT a.user_id AS buyerId, IFNULL(c.sellUser,IFNULL(d.sellUser,NULL)) AS sellerId, a.order_number AS orderNo, IFNULL(c.id,IFNULL(d.id,NULL)) AS txNo, a.order_ —— 1 次 × 4359.78s = 1.21h

• 代表性 SQL：

SELECT
	a.user_id AS buyerId,
	IFNULL(c.sellUser,IFNULL(d.sellUser,NULL)) AS sellerId,
	a.order_number AS orderNo,
	IFNULL(c.id,IFNULL(d.id,NULL)) AS txNo,
	a.order_status AS orderStatus,
	IFNULL(c.status,IFNULL(d.status,NULL)) AS txStatus,
  IFNULL(e.type,IFNULL(f.type,NULL)) AS gameType,
  CONVERT(CAST(IFNULL(e.name,IFNULL(f.name,NULL)) AS BINARY) USING utf8) AS gameName,
  CONVERT(CAST(IFNULL(g.type_name,IFNULL(h.type_name,NULL)) AS BINARY) USING utf8) AS goodsType,
  CONVERT(CAST(IFNULL(c.title,IFNULL(d.title,NULL)) AS BINARY) USING utf8) AS goodsTitle,
	a.pay_by AS payMethod,
	a.user_agent AS orderClient,
	IFNULL(c.price,IFNULL(d.price,NULL)) AS price,
	a.voucher_money AS couponAmount,
	i.active_id AS activityName,
	i.coupon_id AS couponId,
	IF(IFNULL(j.set_type,IFNULL(k.set_type,NULL)) = 'specifyBuyer','yes','no') AS IF_special,
	IF(a.order_createtime NOT LIKE '0000-00-00 00:00:00', a.order_createtime, NULL) AS createTime,
	IF(a.order_paytime NOT LIKE '0000-00-00 00:00:00', a.order_paytime, NULL) AS payTime,
	FROM_UNIXTIME( IFNULL(IF(c.actionTime=0,NULL,c.actionTime),IFNULL(IF(d.actionTime=0,NULL,d.actionTime),NULL)), '%Y-%m-%d %H:%i:%s' ) AS actionTime,
	IF(a.order_canceltime NOT LIKE '0000-00-00 00:00:00', a.order_canceltime, NULL) AS cancelTime
FROM
	t8_ware_order a
	LEFT JOIN t8_ware_order_item b ON a.id = b.order_id
	LEFT JOIN ware_item c ON b.ware_id = c.id
	LEFT JOIN ware_item_history d ON b.ware_id = d.id
	LEFT JOIN gameList e ON c.gameName = e.id
	LEFT JOIN gameList f ON d.gameName = f.id
	LEFT JOIN t8_ware_type g ON c.wareType = g.id
	LEFT JOIN t8_ware_type h ON d.wareType = h.id
	LEFT JOIN t8_user_voucher i ON a.voucher_id = i.id
	LEFT JOIN ( SELECT * FROM ware_set WHERE set_type = 'specifyBuyer' ) j ON c.id = j.ware_id
	LEFT JOIN ( SELECT * FROM ware_set WHERE set_type = 'specifyBuyer' ) k ON d.id = k.ware_id
WHERE 
	a.order_createtime >= '2026-01-01' AND a.order_createtime < '2027-01-01'
ORDER BY 
	a.order_createtime DESC;

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

SELECT * FROM `a8591`.`t8_ware_order` WHERE order_createtime >= '2026-01-01' LIMIT 10

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

• 慢因：扫描行数远大于返回行数(38,816,562/3,413,635)、索引范围扫描量过大(t8_ware_order: key=order_createtime, rows=6,685,148)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

6. SELECT a.buyUser AS buyerId, a.sellUser AS sellerId, c.order_number AS orderNo, a.id AS txNo, c.order_status AS orderStatus, a.status AS txStatus, d.type AS gam —— 1 次 × 3467.12s = 57.8min

• 代表性 SQL：

SELECT
	a.buyUser AS buyerId,
	a.sellUser AS sellerId,
	c.order_number AS orderNo,
	a.id AS txNo,
	c.order_status AS orderStatus,
	a.status AS txStatus,
	d.type AS gameType,
	CONVERT ( CAST( d.NAME AS BINARY ) USING utf8 ) AS gameName,
	CONVERT ( CAST( e.type_name AS BINARY ) USING utf8 ) AS goodsType,
	CONVERT ( CAST( a.title AS BINARY ) USING utf8 ) AS goodsTitle,
	c.pay_by AS payMethod,
	c.user_agent AS orderClient,
	a.price AS price,
	f.money AS couponAmount,
	f.active_id AS activityName,
	f.coupon_id AS couponId,
	IF ( g.set_type = 'specifyBuyer', 'yes', 'no' ) AS IF_special,
	IF ( c.order_createtime != '0000-00-00 00:00:00', c.order_createtime, NULL ) AS createTime,
	IF ( c.order_paytime != '0000-00-00 00:00:00', c.order_paytime, NULL ) AS payTime,
	FROM_UNIXTIME( IF ( a.actionTime = 0, NULL, a.actionTime ), '%Y-%m-%d %H:%i:%s' ) AS actionTime,
	IF ( c.order_canceltime != '0000-00-00 00:00:00', c.order_canceltime, NULL ) AS cancelTime
FROM
	ware_item_history a
	LEFT JOIN t8_ware_order_item b ON a.id = b.ware_id 
	LEFT JOIN t8_ware_order c ON b.order_id = c.id
	LEFT JOIN gameList d ON a.gameName = d.id
	LEFT JOIN t8_ware_type e ON a.wareType = e.id
	LEFT JOIN t8_user_voucher f ON b.ware_id = f.id
	LEFT JOIN ( SELECT * FROM ware_set WHERE set_type = 'specifyBuyer' ) g ON a.id = g.ware_id
WHERE
	a.postTime >= 1546272000 
	AND c.order_number IS NULL;

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

SELECT
	a.buyUser AS buyerId,
	a.sellUser AS sellerId,
	c.order_number AS orderNo,
	a.id AS txNo,
	c.order_status AS orderStatus,
	a.status AS txStatus,
	d.type AS gameType,
	CONVERT ( CAST( d.NAME AS BINARY ) USING utf8 ) AS gameName,
	CONVERT ( CAST( e.type_name AS BINARY ) USING utf8 ) AS goodsType,
	CONVERT ( CAST( a.title AS BINARY ) USING utf8 ) AS goodsTitle,
	c.pay_by AS payMethod,
	c.user_agent AS orderClient,
	a.price AS price,
	f.money AS couponAmount,
	f.active_id AS activityName,
	f.coupon_id AS couponId,
	IF ( g.set_type = 'specifyBuyer', 'yes', 'no' ) AS IF_special,
	IF ( c.order_createtime != '0000-00-00 00:00:00', c.order_createtime, NULL ) AS createTime,
	IF ( c.order_paytime != '0000-00-00 00:00:00', c.order_paytime, NULL ) AS payTime,
	FROM_UNIXTIME( IF ( a.actionTime = 0, NULL, a.actionTime ), '%Y-%m-%d %H:%i:%s' ) AS actionTime,
	IF ( c.order_canceltime != '0000-00-00 00:00:00', c.order_canceltime, NULL ) AS cancelTime
FROM
	`a8591`.`ware_item_history` a
	LEFT JOIN `a8591`.`t8_ware_order_item` b ON a.id = b.ware_id 
	LEFT JOIN `a8591`.`t8_ware_order` c ON b.order_id = c.id
	LEFT JOIN `a8591`.`gameList` d ON a.gameName = d.id
	LEFT JOIN `a8591`.`t8_ware_type` e ON a.wareType = e.id
	LEFT JOIN `a8591`.`t8_user_voucher` f ON b.ware_id = f.id
	LEFT JOIN ( SELECT * FROM `a8591`.`ware_set` WHERE set_type = 'specifyBuyer' ) g ON a.id = g.ware_id
WHERE
	a.postTime >= 1546272000 
	AND c.order_number IS NULL;

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

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

ALTER TABLE `a8591`.`ware_item_history` ADD INDEX `idx_gameName_wareType_set_type_postTime` (`gameName`, `wareType`, `set_type`, `postTime`);
ALTER TABLE `a8591`.`t8_ware_order_item` ADD INDEX `idx_ware_id_order_id` (`ware_id`, `order_id`);

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

7. SELECT a.buyUser AS buyerId, a.sellUser AS sellerId, c.order_number AS orderNo, a.id AS txNo, c.order_status AS orderStatus, a.status AS txStatus, d.type AS gam —— 1 次 × 3462.71s = 57.7min

• 代表性 SQL：

SELECT
	a.buyUser AS buyerId,
	a.sellUser AS sellerId,
	c.order_number AS orderNo,
	a.id AS txNo,
	c.order_status AS orderStatus,
	a.status AS txStatus,
	d.type AS gameType,
	CONVERT ( CAST( d.NAME AS BINARY ) USING utf8 ) AS gameName,
	CONVERT ( CAST( e.type_name AS BINARY ) USING utf8 ) AS goodsType,
	CONVERT ( CAST( a.title AS BINARY ) USING utf8 ) AS goodsTitle,
	c.pay_by AS payMethod,
	c.user_agent AS orderClient,
	a.price AS price,
	f.money AS couponAmount,
	f.active_id AS activityName,
	f.coupon_id AS couponId,
	IF ( g.set_type = 'specifyBuyer', 'yes', 'no' ) AS IF_special,
	IF ( c.order_createtime != '0000-00-00 00:00:00', c.order_createtime, NULL ) AS createTime,
	IF ( c.order_paytime != '0000-00-00 00:00:00', c.order_paytime, NULL ) AS payTime,
	FROM_UNIXTIME( IF ( a.actionTime = 0, NULL, a.actionTime ), '%Y-%m-%d %H:%i:%s' ) AS actionTime,
	IF ( c.order_canceltime != '0000-00-00 00:00:00', c.order_canceltime, NULL ) AS cancelTime
FROM
	ware_item a
	LEFT JOIN t8_ware_order_item b ON a.id = b.ware_id 
	LEFT JOIN t8_ware_order c ON b.order_id = c.id
	LEFT JOIN gameList d ON a.gameName = d.id
	LEFT JOIN t8_ware_type e ON a.wareType = e.id
	LEFT JOIN t8_user_voucher f ON b.ware_id = f.id
	LEFT JOIN ( SELECT * FROM ware_set WHERE set_type = 'specifyBuyer' ) g ON a.id = g.ware_id
WHERE
	a.postTime >= 1546272000 
	AND c.order_number IS NULL
ORDER BY 
	actionTime DESC;

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

SELECT
	a.buyUser AS buyerId,
	a.sellUser AS sellerId,
	c.order_number AS orderNo,
	a.id AS txNo,
	c.order_status AS orderStatus,
	a.status AS txStatus,
	d.type AS gameType,
	CONVERT ( CAST( d.NAME AS BINARY ) USING utf8 ) AS gameName,
	CONVERT ( CAST( e.type_name AS BINARY ) USING utf8 ) AS goodsType,
	CONVERT ( CAST( a.title AS BINARY ) USING utf8 ) AS goodsTitle,
	c.pay_by AS payMethod,
	c.user_agent AS orderClient,
	a.price AS price,
	f.money AS couponAmount,
	f.active_id AS activityName,
	f.coupon_id AS couponId,
	IF ( g.set_type = 'specifyBuyer', 'yes', 'no' ) AS IF_special,
	IF ( c.order_createtime != '0000-00-00 00:00:00', c.order_createtime, NULL ) AS createTime,
	IF ( c.order_paytime != '0000-00-00 00:00:00', c.order_paytime, NULL ) AS payTime,
	FROM_UNIXTIME( IF ( a.actionTime = 0, NULL, a.actionTime ), '%Y-%m-%d %H:%i:%s' ) AS actionTime,
	IF ( c.order_canceltime != '0000-00-00 00:00:00', c.order_canceltime, NULL ) AS cancelTime
FROM
	`a8591`.`ware_item` a
	LEFT JOIN `a8591`.`t8_ware_order_item` b ON a.id = b.ware_id 
	LEFT JOIN `a8591`.`t8_ware_order` c ON b.order_id = c.id
	LEFT JOIN `a8591`.`gameList` d ON a.gameName = d.id
	LEFT JOIN `a8591`.`t8_ware_type` e ON a.wareType = e.id
	LEFT JOIN `a8591`.`t8_user_voucher` f ON b.ware_id = f.id
	LEFT JOIN ( SELECT * FROM `a8591`.`ware_set` WHERE set_type = 'specifyBuyer' ) g ON a.id = g.ware_id
WHERE
	a.postTime >= 1546272000 
	AND c.order_number IS NULL
ORDER BY 
	actionTime DESC;

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

• 计划标志：⚠️ filesort 排序
• 慢因：EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
• 建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
• 可执行 DDL（建议先在低峰期 EXPLAIN 验证再执行）：

ALTER TABLE `a8591`.`ware_item` ADD INDEX `idx_gameName_wareType_set_type_postTime_actionTime` (`gameName`, `wareType`, `set_type`, `postTime`, `actionTime`);
ALTER TABLE `a8591`.`t8_ware_order_item` ADD INDEX `idx_ware_id_order_id` (`ware_id`, `order_id`);

• DDL 注：ware_item 与现有索引 gnat(gamename, actiontime) 前缀重复(gamename), 新索引生效后可评估删除旧索引
• DDL 注：t8_ware_order_item 与现有索引 ware_id(ware_id) 前缀重复(ware_id), 新索引生效后可评估删除旧索引

8. SELECT order_id, ware_id FROM t8_ware_order_item; —— 1 次 × 3029.66s = 50.5min

• 代表性 SQL：

SELECT 
	order_id,
    ware_id
FROM 
	t8_ware_order_item;

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

SELECT 
	order_id,
    ware_id
FROM 
	`a8591`.`t8_ware_order_item`;

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

• 慢因：全索引扫描(t8_ware_order_item: key=user_id, rows=22,988,934，缺更优过滤索引)
• 建议：扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表

逐条优化建议汇总

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

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