NodeJS百万级数据导出Excel内存优化方案咨询

在Node.js环境中导出超100万条SQL数据库数据到Excel文件时，出现服务器内存占用过高、耗时久，甚至触发JavaScript堆内存溢出错误。

原实现代码 #

const express = require('express');
const ExcelJS = require('exceljs');
const fs = require('fs');
const app = express();
var db = require.main.require('./src/app/models/db_controller');

app.get('/download-excel', (req, res) => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');
  worksheet.columns = [
    { header: 'Header 1', key: 'date_time' },
    { header: 'Header 2', key: 'shaft_seal_pressure' },
    { header: 'Header 3', key: 'transfer_pressure' },
    { header: 'Header 4', key: 'cip_tem' },
    { header: 'Header 5', key: 'elect_usage' },];
  db.read_data_all('master_data', (err, result) => {
    if (err) {
      console.log(err);
    } else {
      worksheet.addRows(result);
      const tempFilePath = 'temp.xlsx';
      workbook.xlsx.writeFile(tempFilePath)
        .then(() => {
          res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
          res.setHeader('Content-Disposition', 'attachment; filename=excel_template.xlsx');
          const fileStream = fs.createReadStream(tempFilePath);
          fileStream.pipe(res);
          fileStream.on('end', () => {
            fs.unlink(tempFilePath, (err) => {
              if (err) {
                console.error('删除临时文件出错:', err);
              }
            });
          });
        })
        .catch((error) => {
          console.error('错误:', error);
          res.status(500).send('生成Excel文件时出错。');
        });
    }
  });
});

app.listen(5000, () => {
  console.log('Server is running on port 5000');
});

错误日志 #

<--- Last few GCs --->

[14120:000001B1C00BAA90]    75907 ms: Mark-sweep (reduce) 2046.9 (2082.7) -> 2046.5 (2083.5) MB, 2902.5 / 0.0 ms  (average mu = 0.172, current mu = 0.001) allocation failure; scavenge might not succeed


<--- JS stacktrace --->

FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory
 1: 00007FF79DF307BF node_api_throw_syntax_error+175823
 2: 00007FF79DEB5796 DSA_meth_get_flags+59654
 3: 00007FF79DEB7480 DSA_meth_get_flags+67056
 4: 00007FF79E95DCC4 v8::Isolate::ReportExternalAllocationLimitReached+116
 5: 00007FF79E949052 v8::Isolate::Exit+674
 6: 00007FF79E7CAF0C v8::internal::EmbedderStackStateScope::ExplicitScopeForTesting+124
 7: 00007FF79E7C812B v8::internal::Heap::CollectGarbage+3963
 8: 00007FF79E7DE363 v8::internal::HeapAllocator::AllocateRawWithLightRetrySlowPath+2099
 9: 00007FF79E7DEC0D v8::internal::HeapAllocator::AllocateRawWithRetryOrFailSlowPath+93
10: 00007FF79E7EE3D0 v8::internal::Factory::NewFillerObject+816
11: 00007FF79E4DF315 v8::internal::DateCache::Weekday+1349
12: 00007FF79E9FB1F1 v8::internal::SetupIsolateDelegate::SetupHeap+558193
13: 00007FF79E980D02 v8::internal::SetupIsolateDelegate::SetupHeap+57218
14: 00007FF71EC10EC4

请问如何优化该场景下的服务器内存占用，是否有更优实现方案？

核心思路是避免一次性加载所有数据到内存，通过分批读取数据库+流式写入Excel+直接响应客户端的方式，把内存占用控制在低水平。

1. 数据库分批查询（替代一次性全量读取） #

不要用read_data_all一次性拉取100万条数据，改成分页查询（比如基于主键分段拉取），每次只读取固定数量的批次数据，减少单次内存占用。

2. Excel流式写入+直接响应客户端 #

使用ExcelJS的流式API，直接把Excel内容写入响应流，不需要生成临时文件，减少磁盘IO和内存占用。

优化后的代码示例 #

const express = require('express');
const ExcelJS = require('exceljs');
const app = express();
var db = require.main.require('./src/app/models/db_controller');

app.get('/download-excel', async (req, res) => {
  try {
    // 设置响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=excel_template.xlsx');

    // 创建流式Workbook，直接写入响应流
    const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
      stream: res,
      useStyles: false, // 关闭样式减少内存开销
      useSharedStrings: false // 关闭共享字符串，适合大量数据场景
    });
    const worksheet = workbook.addWorksheet('Sheet1');
    
    // 写入表头
    worksheet.columns = [
      { header: 'Header 1', key: 'date_time' },
      { header: 'Header 2', key: 'shaft_seal_pressure' },
      { header: 'Header 3', key: 'transfer_pressure' },
      { header: 'Header 4', key: 'cip_tem' },
      { header: 'Header 5', key: 'elect_usage' }
    ];

    // 分批查询数据库，每次拉取1000条（可根据内存情况调整批次大小）
    const batchSize = 1000;
    let offset = 0;
    let hasMoreData = true;

    while (hasMoreData) {
      // 调用分页查询方法获取当前批次数据
      const result = await new Promise((resolve, reject) => {
        db.read_data_batch('master_data', offset, batchSize, (err, data) => {
          if (err) reject(err);
          else resolve(data);
        });
      });

      if (result.length === 0) {
        hasMoreData = false;
        break;
      }

      // 写入当前批次数据并提交，及时释放内存
      for (const row of result) {
        worksheet.addRow(row).commit();
      }

      offset += batchSize;
      // 手动触发GC（需启动时加--expose-gc参数）
      if (global.gc) global.gc();
    }

    // 完成Excel写入
    await worksheet.commit();
    await workbook.commit();
  } catch (error) {
    console.error('生成Excel出错:', error);
    if (!res.headersSent) {
      res.status(500).send('生成Excel文件时出错。');
    }
  }
});

// 启动命令需添加--expose-gc参数：node --expose-gc app.js
app.listen(5000, () => {
  console.log('Server is running on port 5000');
});

数据库分页方法实现 #

需要在db_controller.js中新增分页查询方法（以MySQL为例）：

// db_controller.js
exports.read_data_batch = function(table, offset, limit, callback) {
  const sql = `SELECT * FROM ${table} LIMIT ?, ?`;
  connection.query(sql, [offset, limit], callback);
};

3. 可选：临时调整Node.js内存限制 #

如果需要快速临时缓解问题，可以启动Node时增加堆内存上限，但这不是根本解决方案：

node --max-old-space-size=4096 app.js # 分配4GB堆内存

4. 额外优化建议 #

• 关闭Excel不必要的功能：比如样式、单元格格式，减少内存开销。
• 使用高效数据库驱动：比如mysql2替代mysql，支持Promise和流式查询，进一步降低内存占用。
• 监控内存使用：通过process.memoryUsage()打印内存占用，验证优化效果。

内容的提问来源于stack exchange，提问作者TuanZin