从 60 万行全表扫描到 1 行：一次库存导出超时问题的排查与索引优化

从 60 万行全表扫描到 1 行：一次库存导出超时问题的排查与索引优化

摘要

库存管理功能中，某冷库的导出请求一直转圈，后台最终出现 MySQL 读取超时。出问题的库存明细只有 1010 条，单看结果集并不大，但导出 SQL 会为每条库存记录关联查询一张约 60 万行的二维码表。由于关联字段 batch_id 没有索引，数据库可能反复执行全表扫描，最终触发 JDBC 的 10 秒读取超时。

本文记录完整的排查过程：如何从异常堆栈定位到 SQL，如何结合表数据量和业务关系分析瓶颈，为什么简单增加 IS NOT NULL 不能解决问题，以及为什么只增加一个普通索引、不修改 Java 代码，就能将目标子查询的预计扫描行数从约 60 万行降到 1 行。

截至本文整理时，索引及执行计划已经验证正常；完整导出功能仍需进行最终回归验证。

一、问题现象

库存管理导出功能存在以下现象：

• 数据量较小时可以正常导出；
• 数据量较大的冷库点击导出后，页面一直转圈；
• 请求不能稳定返回 Excel 文件；
• 后台日志反复出现数据库通信异常。

业务上，该功能需要导出指定冷库下的全部库存数据，不是只导出当前分页，因此需要重点考虑全量查询和全量生成 Excel 的开销。

二、先从日志确定故障发生在哪一层

导出功能大致可以分为三个阶段：

1. 查询数据库，取得汇总和详单数据；
2. 使用 Apache POI 生成 Excel；
3. 返回文件地址或下载文件。

只有先确定慢在哪一个阶段，才能避免盲目优化。

后台日志中反复出现以下核心异常：

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10,012 milliseconds ago.

Caused by: java.net.SocketTimeoutException: Read timed out

异常调用链指向：

SgWarehousingController.generateInventoryExcel
→ SgWarehousingServiceImpl.generateInventoryExcel
→ selectInventoryDetailList

日志中共出现了 15 次相同的数据库读取超时。

这说明当前请求还没有走到 Excel 生成阶段，而是在等待 MySQL 返回详单查询结果时，就达到了 JDBC 的读取超时时间。因此，第一阶段应该分析 SQL 和索引，而不是先调整 POI 或前端 loading。

需要注意：

• SocketTimeoutException 是直接故障表现；
• 它说明应用等待数据库返回超过约 10 秒；
• 真正需要继续寻找的是数据库为什么迟迟没有返回。

三、导出 SQL 中隐藏的开销

库存详单查询中包含下面这个标量子查询：

(
    SELECT CAST(q.id AS CHAR)
    FROM qr_code_info q
    WHERE q.batch_id = g.batch_id
    LIMIT 1
) AS fruitNo

它的业务目的，是取得导出详单中的“果框编号”。

此外，SQL 中还有多个字典翻译子查询，以及用户、冷库等关联查询。初步分析时，不能只看最终返回多少条数据，还要看：

• 每张表实际有多少数据；
• 每个子查询会执行多少次；
• 子查询是否能通过索引快速定位；
• 数据库实际扫描了多少行。

返回 1010 行，不代表数据库只处理了 1010 行。

四、为什么导出需要查询二维码表

当前业务中的二维码与库存批次关系如下：

sg_grwarehousing.batch_id
            ↓
qr_code_info.batch_id
            ↓
qr_code_info.id = 果框编号

系统预先生成二维码时，会在 qr_code_info 中创建记录：

• id：二维码 ID，同时作为打印在二维码上的果框编号；
• batch_id：未使用时为 NULL，扫码入库后绑定对应库存批次；
• url：二维码相关地址。

库存表保存了 batch_id，但没有直接保存果框编号，因此导出时需要根据批次号反查二维码表。

CAST(q.id AS CHAR) 的作用是把数值 ID 转成字符串，便于按文本写入 Excel，与本次性能问题无关。

业务还明确了一点：一个批次 ID 只对应一个果框编号。因此当前 LIMIT 1 不会主动舍弃同批次的其他果框记录，但数据库层面当时并没有唯一约束。

五、用真实数据量缩小问题范围

首先查询出问题冷库的库存数量：

SELECT status, COUNT(*) AS row_count
FROM sg_grwarehousing
WHERE cold_storage = '399'
  AND status IN ('0', '5')
GROUP BY status;

结果：

status	row_count
0	1010

1010 条库存明细本身不算大，正常情况下不应该因为结果集大小而查询超过 10 秒。

继续统计相关表的数据量：

SELECT 'sg_grwarehousing' AS table_name, COUNT(*) AS row_count
FROM sg_grwarehousing
UNION ALL
SELECT 'qr_code_info', COUNT(*)
FROM qr_code_info
UNION ALL
SELECT 'sys_dict_data', COUNT(*)
FROM sys_dict_data;

结果：

table_name	row_count
sg_grwarehousing	5726
qr_code_info	609427
sys_dict_data	195

由此可以看到：

• 库存主表只有 5726 行，暂时不像主要瓶颈；
• 字典表只有 195 行，字典子查询的开销相对有限；
• 二维码表有约 60.9 万行，而且每条库存都可能触发一次相关子查询，嫌疑最大。

如果二维码子查询没有可用索引，最坏情况下可能形成：

1010 次库存子查询 × 每次扫描约 609427 行
≈ 6.15 亿行次检查

这只是帮助理解开销数量级的估算，不代表数据库一定完整执行了这么多次物理读取，但足以说明“结果只有 1010 条”与“查询成本低”完全不是一回事。

六、60 万条记录中只有 58 条有效绑定，加 IS NOT NULL 有用吗

进一步分析发现，qr_code_info 中大部分记录都是尚未使用的预生成二维码：

• 大部分 batch_id 为 NULL；
• 只有约 58 条记录的 batch_id 非空。

从业务上看，真正有效的绑定关系确实很少，但这并不意味着数据库天然只需要读取 58 条记录。

原条件：

q.batch_id = g.batch_id

本来就不会匹配 NULL。再增加：

AND q.batch_id IS NOT NULL

通常只是重复限制，并不能改变查询结果，也不一定改善执行计划。

更关键的是：没有索引时，数据库仍需要逐行读取记录，才能判断每一行的 batch_id 是否为 NULL。

因此必须区分：

• 最终结果只有 58 条；
• 数据库为了找到结果实际读取了多少行。

如果没有合适的访问路径，即使目标数据很少，也可能需要扫描整张表。

七、确认索引缺失

检查二维码表的索引：

SHOW INDEX FROM qr_code_info;

当时只有：

Key_name	Column_name	说明
PRIMARY	id	主键索引

查询使用的是 batch_id，主键 id 索引无法帮助按批次号定位记录，因此 batch_id 缺少索引的问题非常明确。

同时检查非空批次号是否存在重复：

SELECT batch_id, COUNT(*) AS cnt
FROM qr_code_info
WHERE batch_id IS NOT NULL
GROUP BY batch_id
HAVING COUNT(*) > 1;

结果为空，说明当前非空 batch_id 没有重复，符合“一批次对应一个果框”的业务描述。

八、优化方案：先增加普通索引

本次目标是尽快消除全表扫描，同时不改变现有业务约束，因此先使用普通索引：

CREATE INDEX idx_qr_code_info_batch_id
ON qr_code_info(batch_id);

选择普通索引而不是立即使用唯一索引，原因是：

• 普通索引已经能够解决查询定位效率问题；
• 不要求额外改变数据写入约束；
• 排查和回归范围更小；
• 唯一约束可以在确认完整业务规则后单独评估。

创建完成后检查：

SHOW INDEX FROM qr_code_info
WHERE Key_name = 'idx_qr_code_info_batch_id';

返回结果表明：

• Non_unique = 1：普通非唯一索引；
• Column_name = batch_id：索引字段正确；
• Index_type = BTREE：索引结构正常；
• Cardinality = 69：优化器使用的估算值，不要求与实际非空批次数量完全一致。

九、通过 EXPLAIN 验证优化效果

使用一个真实批次号执行：

EXPLAIN
SELECT CAST(q.id AS CHAR)
FROM qr_code_info q
WHERE q.batch_id = 'BATCH2026-10-23 20:10:24594'
LIMIT 1;

结果：

| id | select_type | table | type | possible_keys | key | ref | rows | filtered | Extra |
|---😐---|---|---|---|---|---|---|---😐---😐---|
| 1 | SIMPLE | q | ref | idx_qr_code_info_batch_id | idx_qr_code_info_batch_id | const | 1 | 100.00 | Using index |

几个关键指标说明优化已经生效。

1. type = ref

表示通过非唯一索引进行等值查找，不再是 ALL 全表扫描。

2. key = idx_qr_code_info_batch_id

表示优化器实际选择了新建的 batch_id 索引，而不只是“存在这个索引”。

3. rows = 1

预计只需检查 1 行。与约 60 万行的表规模相比，定位成本已经发生了数量级变化。

4. Extra = Using index

当前查询只需要：

• 查询条件中的 batch_id；
• 返回字段 id。

InnoDB 二级索引的叶子记录中除了索引字段，还包含主键值，因此本次查询所需内容可以直接从索引取得，不需要再回表读取完整数据行。

十、为什么增加索引后不用修改代码

索引改变的是数据库查找数据的方式，而不是 SQL 的业务含义。

没有索引时：

读取一行
→ 检查 batch_id 是否匹配
→ 不匹配则继续读取下一行
→ 直到找到目标或扫描结束

有索引后：

根据 batch_id 在索引中定位
→ 找到对应的主键 id
→ 返回结果

查询条件、表结构和返回字段都没有变化，因此：

• Java 代码不需要修改；
• MyBatis SQL 不需要修改；
• MySQL 优化器会自动评估并选择成本更低的执行计划；
• 不需要重启应用或数据库；
• CREATE INDEX 完成后，新发起的查询即可使用索引。

已经开始执行的旧查询不会中途切换执行计划，但新的导出请求可以使用新索引。

十一、通俗理解：索引为什么能提升效率

可以把索引理解成一份特殊的目录，但关键点不是“多了一张表”，而是这份目录按照适合查找的结构进行了组织。

假设原表数据为：

id	batch_id	url
1001	NULL	url-A
1002	BATCH002	url-B
1003	NULL	url-C
1004	BATCH001	url-D

batch_id 二级索引可以粗略理解为：

NULL      → 1001
NULL      → 1003
BATCH001  → 1004
BATCH002  → 1002

实际实现不是简单的二维表，而是 B+Tree。数据库可以沿树状目录快速缩小范围，而不是逐行检查所有记录。

这和查字典类似：

• 没有目录时，只能一页一页翻；
• 有拼音目录时，可以先定位大致范围，再找到具体词条。

如果只是另外复制一份无序数据，查询时仍然从头扫描，并不会自然变快。索引的价值在于其组织方式和定位能力。

十二、二级索引为什么已经包含 id

在 InnoDB 中，二级索引记录会包含主键值，用于定位对应的聚簇索引记录。

因此，虽然只执行了：

CREATE INDEX idx_qr_code_info_batch_id
ON qr_code_info(batch_id);

仍可粗略理解为索引维护了：

batch_id → 主键 id

本次查询恰好只需要 batch_id 和 id，所以出现 Using index。

这也意味着：

• 不需要再为 id 创建一个重复普通索引，因为 id 已经有主键索引；
• 不需要为了当前查询额外创建 (batch_id, id) 组合索引；
• 两个索引并不意味着同一个查询会自动快两倍。

MySQL 通常会根据成本为一个表访问选择合适的主要访问路径，而不是把索引数量简单叠加成性能收益。

十三、数据变化后，索引什么时候更新

索引由 MySQL 自动维护，不需要业务代码额外更新。

新增记录

插入数据行时，MySQL 同步写入相关索引项。

修改批次号

扫码绑定批次时：

UPDATE qr_code_info
SET batch_id = 'BATCH001'
WHERE id = 1001;

MySQL 会在同一事务中更新原表和索引中的对应关系。

删除记录

删除数据行时，对应索引项也会被删除。

事务一致性

数据与索引变更处于同一个事务中：

• 提交后一起对外可见；
• 回滚时一起撤销；
• 正常情况下不会出现数据已改变而索引仍保留旧业务值的问题。

索引维护不是每次重新排序整张表，而是修改 B+Tree 中相关节点，必要时进行局部页分裂或合并。

十四、普通索引与唯一索引怎么选择

普通索引只负责加速，不限制重复值；唯一索引除了加速，还会强制非空值不能重复。

当前业务规定“一批次对应一个果框”，因此后续可以评估：

CREATE UNIQUE INDEX uk_qr_code_info_batch_id
ON qr_code_info(batch_id);

MySQL 唯一索引通常允许多个 NULL，所以大量未绑定二维码不会因此冲突。但需要注意：

• 空字符串 '' 与 NULL 不同；
• 创建前必须确认历史数据无重复；
• 需要确认“批次永不被多个果框使用”是长期业务约束；
• 唯一索引会让不符合约束的写入直接报错。

因此，唯一索引更适合作为独立的数据完整性优化，而不是本次导出超时修复的必要条件。

十五、推荐的数据库变更执行方案

1. 低峰期执行

约 60 万行数据创建索引会产生一定的 CPU、I/O 和磁盘开销，建议避开出入库及导出高峰期。

2. 检查索引是否已存在

SHOW INDEX FROM qr_code_info
WHERE Key_name = 'idx_qr_code_info_batch_id';

3. 创建索引

CREATE INDEX idx_qr_code_info_batch_id
ON qr_code_info(batch_id);

4. 验证索引

SHOW INDEX FROM qr_code_info
WHERE Key_name = 'idx_qr_code_info_batch_id';

5. 验证执行计划

使用真实批次号执行 EXPLAIN，确认：

• 使用 idx_qr_code_info_batch_id；
• 访问类型不再是全表扫描；
• 预计扫描行数显著下降。

6. 回归完整导出

验证以下内容：

• 导出请求是否正常结束；
• Excel 是否成功生成；
• 果框编号是否正确；
• 日志是否还出现 SocketTimeoutException；
• 汇总与详单数据是否符合业务预期。

验证期间不要同时调整 SQL、JDBC 超时时间和其他索引，以便准确判断本次变更的效果。

7. 回滚方式

如果后续确认不再需要该索引，可以执行：

DROP INDEX idx_qr_code_info_batch_id
ON qr_code_info;

删除索引不会删除业务表数据，但查询可能重新退化为全表扫描，因此回滚前应评估影响。

十六、索引不是越多越好

本次优化有效，是因为索引精准匹配了高频关联条件，而不是因为“索引数量增加”。

索引的收益：

• 减少查询需要检查的数据量；
• 提升等值查询、范围查询和关联查询效率；
• 在合适场景中减少回表或排序开销。

索引的成本：

• 占用额外磁盘空间；
• 新增、修改、删除时需要同步维护；
• 索引过多可能增加写入成本和优化器选择成本。

所以正确原则是：

不是索引越多越快，而是查询使用了正确的索引才快。

当前执行计划已经达到 rows = 1，继续为 id 添加重复索引不会让这个查询更快。

十七、后续仍需要关注的问题

索引解决了本次二维码反查的主要瓶颈，但全量导出仍可能随着业务数据增长出现新的性能问题。

1. 全量数据加载

当前导出会一次性查询详单并放入内存，数据继续增长后可能增加内存压力。

2. Excel 生成方式

使用 XSSFWorkbook 时，工作簿对象主要保存在内存中。大数据量场景可以评估流式写入方案，例如 SXSSFWorkbook。

3. 同步 HTTP 请求

查询和 Excel 生成都在一次请求中完成，未来仍可能受到 JDBC、HTTP 或代理超时限制。对于真正的全量大文件导出，可以评估：

创建导出任务
→ 后台异步查询并生成文件
→ 页面查询任务状态
→ 完成后下载文件

4. 数据库变更管理

应将建索引语句纳入正式数据库变更记录，确保测试、预发布和生产环境一致，避免代码升级后由于环境缺少索引再次出现同类问题。

十八、排查经验总结

这次问题可以沉淀为以下几条经验：

1. 页面转圈只是现象，异常堆栈才是定位入口。
2. 先拆分查询、Excel 生成、文件下载三个阶段，不要盲目优化。
3. 结果集很小，不代表查询成本很低。 相关子查询、连接和全表扫描可能隐藏大量工作。
4. 目标数据只有 58 条，不代表数据库只读取 58 条。 没有索引时，仍需逐行判断。
5. LIMIT 1 不能代替索引。 它只能限制返回数量，不能保证快速找到第一条匹配记录。
6. 增加 IS NOT NULL 不等于只扫描非空数据。 是否减少扫描取决于执行计划和可用索引。
7. 通过 EXPLAIN 验证，而不是只凭经验判断。 type、key、rows 和 Extra 能直接反映优化效果。
8. 索引改变访问路径，不改变业务语义。 因此很多纯查询性能问题无需修改 Java 代码。
9. 索引数量不是性能指标。 合适的索引才有价值，多余索引会增加写入和维护成本。
10. 优化完成后仍需完整业务回归。 执行计划正常是重要证据，但最终还要确认导出结果和端到端耗时。

结语

这次库存导出超时的核心，不是 1010 条库存数据太大，而是每条库存都可能在一个约 60 万行、缺少关联字段索引的二维码表中查找果框编号。

为 qr_code_info.batch_id 增加普通索引后，目标子查询的执行计划已经优化为索引等值查找，预计检查行数降至 1，并且可以直接从索引返回所需字段。整个优化不修改业务 SQL 和 Java 代码，也不改变导出结果，是一次典型的通过补齐索引解决数据库访问路径问题的案例。

真正值得沉淀的，不只是这一条建索引语句，而是从日志、调用链、数据分布、业务关系、索引结构到执行计划的完整分析方法。