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

统计概览

项目	值
统计范围	2026-09-19 00:00:00 ~ 23:59:59（Asia/Shanghai）
环境	prod-8591
慢日志条数（ES mysql-slow_*）	61,983 条（指纹聚合取样 21,036 条，覆盖率 34%，超过 2 万条上限截断）
Top10 单条最慢	5,610.65 s（user=bi，ZHDX_t8591_107）
非备份模式最大累计	2.96h（3043 次 × 3.50 s）
备份模式（user 含 backup/databasus_backup）	643 条、累计 10.07h（单独标注，不触发深析）
监控巡检类（jmysql/proxysql_monitor 或 information_schema）	940 条、累计 30.0min（多为 `# Schema:` 空导致采集端 `_grokparsefailure`、无库名；单独标注，不触发深析）
深析模式 explain 成功率	8/8（100%）
数据来源	ES `mysql-slow_*`（@timestamp）+ MCP 只读 MySQL（describe_schema/explain）
生成时间	2026-09-20 09:51（Asia/Shanghai）

Top10 明细（按单条耗时）

#	耗时(s)	库.表	行数 sent/examined	user / server	慢因分类
1	5,610.65	a8591.t8_mall_stat	164,299,311 / 328,598,622	bi / ZHDX_t8591_107	EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
2	3,855.15	a8591.t8_mall_stat	164,486,600 / 328,973,200	bi / ZHDX_t8591_107	EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
3	3,783.45	a8591.t8_ware_order	3,021,492 / 36,984,049	bi / ZHDX_t8591_107	扫描行数远大于返回行数(36,984,049/3,021,492)、索引范围扫描量过大(t8_ware_order: key=order_createtime, rows=6,251,240)
4	3,421.58	a8591.t8_ware_item_desc	23,155,826 / 23,155,826	databasus_backup / ZHDX_t8591_52	EXPLAIN 全表扫描/未用索引
5	3,090.45	a8591.ware_item_history	73 / 32,017,794	bi / ZHDX_t8591_107	扫描行数远大于返回行数(32,017,794/73)、EXPLAIN 全表扫描/未用索引
6	3,060.49	a8591.t8_mall_stat	164,550,366 / 329,100,732	bi / ZHDX_t8591_107	EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
7	3,001.67	a8591.t8_mall_stat	164,416,190 / 328,832,380	bi / ZHDX_t8591_107	EXPLAIN 全表扫描/未用索引、排序未走索引(filesort)
8	2,874.37	a8591.t8_ware_order_item	21,026,538 / 21,026,538	bi / ZHDX_t8591_107	全索引扫描(t8_ware_order_item: key=user_id, rows=22,988,936，缺更优过滤索引)
9	2,521.99	t8_game.t8_embeddings_cache	720,632 / 720,632	databasus_backup / ZHDX_t8591_91	EXPLAIN 全表扫描/未用索引
10	2,508.19	a8591.ware_item_history	1,165,596 / 10,092,535	bi / ZHDX_t8591_107	EXPLAIN 全表扫描/未用索引

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

按条数 Top5

#	模式（指纹摘要）	条数	平均(s)	累计	server	user
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 c.id ASC,c.id LIMIT ?;	3,043	3.50	2.96h	ZHDX_t8591_143,ZHDX_t8591_16,ZHDX_t8591_	a8591
2	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 + …（点击展开完整 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 = ? and t1_design_work.is_show = ? and t1_design_work.is_hide = ? and t1_design_work.publish_time <= ? and t1_design_img.id > ? order by t1_design_img.id asc limit ?;	2,732	1.36	1.03h	ZHDX_t8591_73	a591
3	SELECT id FROM ware_item WHERE id > ? AND status > ? AND (actionTime BETWEEN ? AND ? ) ORDER BY id ASC LIMIT ?;	1,080	1.47	26.5min	ZHDX_t8591_61	a8591
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 …（点击展开完整 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 = ? AND pg.enable = ? AND pg.id > ? AND pg.platform_app_id IS NOT NULL AND pg.platform_app_id <> ? AND g.type = ? AND g.enable = ? AND NOT EXISTS ( SELECT ? FROM t8_platform_game_media pgm WHERE pgm.platform_game_id = pg.id AND pgm.scene_type = ? LIMIT ? ) ORDER BY pg.id ASC LIMIT ?;	1,080	1.85	33.3min	ZHDX_t8591_136	a8591
5	SELECT t.table_schema, t.table_name, column_name, auto_increment, pow(?, case data_type when ? then ? when ? then ? when ? then ? when ? then ? when ? then ? en …（点击展开完整 SQL） SELECT t.table_schema, t.table_name, column_name, auto_increment, pow(?, case data_type when ? then ? when ? then ? when ? then ? when ? then ? when ? then ? end+(column_type like ?))-? as max_int FROM information_schema.columns c STRAIGHT_JOIN information_schema.tables t ON BINARY t.table_schema = c.table_schema AND BINARY t.table_name = c.table_name WHERE c.extra = ? AND t.auto_increment IS NOT NULL; 🔍监控	784	1.87	24.4min	ZHDX_t8591_143,ZHDX_t8591_16,ZHDX_t8591_	jmysql

按累计耗时 Top5

#	模式	条数	平均(s)	累计	server	user
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 c.id ASC,c.id LIMIT ?;	3,043	3.50	2.96h	ZHDX_t8591_143,ZHDX_t8591_16,ZHDX_t8591_	a8591
2	SELECT * FROM t8_mall_stat ORDER BY created_at DESC;	2	4306.16	2.39h	ZHDX_t8591_107	bi
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 by wallet;	167	50.41	2.34h	ZHDX_t8591_61	a8591
4	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_ …（点击展开完整 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)) = ?,?,?) AS IF_special, IF(a.order_createtime NOT LIKE ?, a.order_createtime, NULL) AS createTime, IF(a.order_paytime NOT LIKE ?, a.order_paytime, NULL) AS payTime, FROM_UNIXTIME( IFNULL(IF(c.actionTime=?,NULL,c.actionTime),IFNULL(IF(d.actionTime=?,NULL,d.actionTime),NULL)), ? ) AS actionTime, IF(a.order_canceltime NOT LIKE ?, 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 = ? ) j ON c.id = j.ware_id LEFT JOIN ( SELECT * FROM ware_set WHERE set_type = ? ) k ON d.id = k.ware_id WHERE a.order_createtime >= ? AND a.order_createtime < ? ORDER BY a.order_createtime DESC;	1	3783.45	1.05h	ZHDX_t8591_107	bi
5	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 + …（点击展开完整 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 = ? and t1_design_work.is_show = ? and t1_design_work.is_hide = ? and t1_design_work.publish_time <= ? and t1_design_img.id > ? order by t1_design_img.id asc limit ?;	2,732	1.36	1.03h	ZHDX_t8591_73	a591

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

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

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

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	c	⚠️ 全索引扫描	PRIMARY	—	1	5.0%

慢因：扫描行数远大于返回行数(820,213/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 次 × 4306.16s = 2.39h

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

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	t8_mall_stat	❌ 全表扫描	—	—	21,632,214	100.0%

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

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 —— 167 次 × 50.41s = 2.34h

代表性 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-09-19 00:04:40' 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-09-19 00:04:40' group by `wallet`;

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

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	t8_money_trade_log	ref	idx_aw	idx_created_at,idx_aw	29,611,880	50.0%

慢因：扫描行数远大于返回行数(21,031,902/2)、索引范围扫描量过大(t8_money_trade_log: key=idx_aw, rows=29,611,880)
建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

4. 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 次 × 3783.45s = 1.05h

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

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	t8_ware_order	range	order_createtime	order_createtime	6,251,240	100.0%

慢因：扫描行数远大于返回行数(36,984,049/3,021,492)、索引范围扫描量过大(t8_ware_order: key=order_createtime, rows=6,251,240)
建议：补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中

5. 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,732 次 × 1.36s = 1.03h

代表性 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-09-19 00:04:59' and `t1_design_img`.`id` > 1455 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-09-19 00:04:59' and `t1_design_img`.`id` > 1455 order by `t1_design_img`.`id` asc limit 100;

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

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	t1_design_work	ref	is_hide	PRIMARY,is_hide,is_show,check_status	15,322	8.33%
2	t1_design_img	ref	w_id	PRIMARY,w_id	24	50.0%

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

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

6. SELECT order_id, ware_id FROM t8_ware_order_item; —— 1 次 × 2874.37s = 47.9min

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

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	t8_ware_order_item	⚠️ 全索引扫描	user_id	—	22,988,946	100.0%

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

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 次 × 2508.19s = 41.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 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 顺序）：

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	a	❌ 全表扫描	—	postTime	919,316	50.0%
2	b	ref	ware_id	ware_id	1	100.0%
3	c	eq_ref	PRIMARY	PRIMARY	1	10.0%
4	d	eq_ref	PRIMARY	PRIMARY	1	100.0%
5	e	eq_ref	PRIMARY	PRIMARY	1	100.0%
6	f	eq_ref	PRIMARY	PRIMARY	1	100.0%
7	ware_set	ref	ware_id	ware_id,set_type	6	100.0%

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

ALTER TABLE `a8591`.`ware_item` ADD INDEX `idx_set_type_id_postTime` (`set_type`, `id`, `postTime`);

8. 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 次 × 2380.72s = 39.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_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 顺序）：

#	表	访问类型	使用索引	候选索引	预扫行数	过滤后
1	a	❌ 全表扫描	—	postTime	15,803,708	100.0%
2	b	ref	ware_id	ware_id	1	100.0%
3	c	eq_ref	PRIMARY	PRIMARY	1	10.0%
4	d	eq_ref	PRIMARY	PRIMARY	1	100.0%
5	e	eq_ref	PRIMARY	PRIMARY	1	100.0%
6	f	eq_ref	PRIMARY	PRIMARY	1	100.0%
7	ware_set	ref	ware_id	ware_id,set_type	6	100.0%

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

ALTER TABLE `a8591`.`ware_item_history` ADD INDEX `idx_set_type_id_postTime` (`set_type`, `id`, `postTime`);

逐条优化建议汇总

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

编号	对象	建议
P1	a8591.t8_user_id_card_number_list（3,043×3.5s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表; DDL: ALTER TABLE a8591.t8_user_id_card_number_list ADD INDEX idx_img_type_status_ocr_result (img_type, status, ocr_result);
P2	a8591.t8_mall_stat（2×4306.2s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; 改分页/按需列, 避免 SELECT * 整表捞取; 大导出走离线通道
P3	a8591.t8_money_trade_log（167×50.4s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
P4	a8591.t8_ware_order（1×3783.4s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中
P5	t591.t1_design_img（2,732×1.4s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE t591.t1_design_img ADD INDEX idx_check_stat_is_show_is_hide_publish_ti (check_status, is_show, is_hide, publish_time);; 缩小聚合/去重范围(补时间等过滤条件), 或预聚合到中间表/定时任务; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
P6	a8591.t8_ware_order_item（1×2874.4s）	扩复合索引: 把其余 WHERE 等值列追加到现有索引尾部, 避免 10 万+ 行回表
P7	a8591.ware_item（1×2508.2s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE a8591.ware_item ADD INDEX idx_set_type_id_postTime (set_type, id, postTime);; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围
P8	a8591.ware_item_history（1×2380.7s）	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; DDL: ALTER TABLE a8591.ware_item_history ADD INDEX idx_set_type_id_postTime (set_type, id, postTime);
T1	Top1（同 P2 对象）单条最慢 1.56h，user=bi a8591.t8_mall_stat	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库
T2	Top2（同 P2 对象）单条最慢 1.07h，user=bi a8591.t8_mall_stat	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; 把 ORDER BY 列纳入复合索引, 或减小排序前扫描范围; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库
T3	Top3（同 P4 对象）单条最慢 1.05h，user=bi a8591.t8_ware_order	补索引（按 WHERE 等值列在前、范围列收尾建复合索引）; 用 EXPLAIN 验证 key 命中; bi/导出类: 限流降频、移从库/离线跑, 避免高峰占用主库

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

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

统计概览

Top10 明细（按单条耗时）

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

按条数 Top5

按累计耗时 Top5

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

2. SELECT * FROM t8_mall_stat ORDER BY created_at DESC; —— 2 次 × 4306.16s = 2.39h

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 —— 167 次 × 50.41s = 2.34h

4. 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 次 × 3783.45s = 1.05h

5. 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,732 次 × 1.36s = 1.03h

6. SELECT order_id, ware_id FROM t8_ware_order_item; —— 1 次 × 2874.37s = 47.9min

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 次 × 2508.19s = 41.8min

8. 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 次 × 2380.72s = 39.7min

逐条优化建议汇总

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