PowerShell处理大SQLite数据库转CSV时内存溢出问题求助

我正在编写PowerShell脚本，将大小超过14000000KB的.db文件记录转换为.csv文件。为避免内存问题，我采用循环逐条读取记录的方式，但仍出现内存溢出错误。请问是否有办法在每次操作间隙清理内存以降低RAM占用？

原脚本 #

$sql = "DROP TABLE IF EXISTS EXPORT;
        CREATE TABLE EXPORT AS SELECT sha256, sha1, md5, crc32, file_name, file_size, package_id FROM FILE;
        UPDATE EXPORT SET file_name = REPLACE(file_name, '`"', '');
        "
$sql | sqlite3 $DBfile2Convert
$sqla = "SELECT COUNT(*) FROM EXPORT;"
$obja = $sqla | sqlite3 $DBfile2Convert
$TotalItems = $obja
$CurrentItem = 0
$PercentComplete = 0
Write-Host ""
1..$obja | % {
    $sqla = "SELECT '`"' || sha256 || '`"', '`"' || sha1 || '`"', '`"' || md5 || '`"', '`"' || crc32 || '`"', '`"' || file_name || '`"', file_size, package_id, '`"' || 0 || '`"', '`"' || '`"' FROM EXPORT ORDER BY sha1 LIMIT 1 OFFSET " + $_ + ";"
    $sqla | sqlite3 $DBfile2Convert | Add-Content -Path output.txt
    Write-Progress -Activity "Exporting DB Rows to CSV" -Status "$PercentComplete% Complete:" -PercentComplete $PercentComplete
    If ([int](($_ / $obja) * 100) -lt "5")  {$PERC = "[                    ]"}
    If ([int](($_ / $obja) * 100) -eq "5")  {$PERC = "[0                   ]"}
    If ([int](($_ / $obja) * 100) -eq "10") {$PERC = "[00                  ]"}
    If ([int](($_ / $obja) * 100) -eq "15") {$PERC = "[000                 ]"}
    If ([int](($_ / $obja) * 100) -eq "20") {$PERC = "[0000                ]"}
    If ([int](($_ / $obja) * 100) -eq "25") {$PERC = "[00000               ]"}
    If ([int](($_ / $obja) * 100) -eq "30") {$PERC = "[000000              ]"}
    If ([int](($_ / $obja) * 100) -eq "35") {$PERC = "[0000000             ]"}
    If ([int](($_ / $obja) * 100) -eq "40") {$PERC = "[00000000            ]"}
    If ([int](($_ / $obja) * 100) -eq "45") {$PERC = "[000000000           ]"}
    If ([int](($_ / $obja) * 100) -eq "50") {$PERC = "[0000000000          ]"}
    If ([int](($_ / $obja) * 100) -eq "55") {$PERC = "[00000000000         ]"}
    If ([int](($_ / $obja) * 100) -eq "60") {$PERC = "[000000000000        ]"}
    If ([int](($_ / $obja) * 100) -eq "65") {$PERC = "[0000000000000       ]"}
    If ([int](($_ / $obja) * 100) -eq "70") {$PERC = "[00000000000000      ]"}
    If ([int](($_ / $obja) * 100) -eq "75") {$PERC = "[000000000000000     ]"}
    If ([int](($_ / $obja) * 100) -eq "80") {$PERC = "[0000000000000000    ]"}
    If ([int](($_ / $obja) * 100) -eq "85") {$PERC = "[00000000000000000   ]"}
    If ([int](($_ / $obja) * 100) -eq "90") {$PERC = "[000000000000000000  ]"}
    If ([int](($_ / $obja) * 100) -eq "95") {$PERC = "[0000000000000000000 ]"}
    If ([int](($_ / $obja) * 100) -eq "100"){$PERC = "[00000000000000000000]"}
    Write-Host -NoNewLine "`r  $PercentComplete% COMPLETE     $PERC"
    $PercentComplete = ([int](($_ / $obja) * 100))
}
Write-Host ""
Write-Progress -Activity "Exporting DB Rows to CSV" -Status "Done" -Completed
Set-Content "${DBfile2Convert}.csv" '"SHA-256","SHA-1","MD5","CRC32","FileName","FileSize","ProductCode","OpSystemCode","SpecialCode"'
Get-Content output.txt | Foreach-Object {$_.Replace('"""', '"')} | Foreach-Object {$_.Replace('|', ',')} | Add-Content "${DBfile2Convert}.csv"
Remove-Item "output.txt" -Force

1. 用SQLite内置导出功能，彻底解决内存问题 #

当前逐条查询的方式效率极低，每次调用sqlite3都会启动新进程，大量进程残留和上下文切换是内存溢出的核心原因。直接让SQLite处理导出，完全绕开PowerShell循环：

$exportSql = @"
DROP TABLE IF EXISTS EXPORT;
CREATE TABLE EXPORT AS SELECT sha256, sha1, md5, crc32, REPLACE(file_name, '"', '') AS file_name, file_size, package_id FROM FILE;
.mode csv
.headers off
.output "${DBfile2Convert}_temp.csv"
SELECT 
    '"' || sha256 || '"',
    '"' || sha1 || '"',
    '"' || md5 || '"',
    '"' || crc32 || '"',
    '"' || file_name || '"',
    file_size,
    package_id,
    '"0"',
    '""'
FROM EXPORT ORDER BY sha1;
.output stdout
"@
$exportSql | sqlite3 $DBfile2Convert
# 写入表头
Set-Content "${DBfile2Convert}.csv" '"SHA-256","SHA-1","MD5","CRC32","FileName","FileSize","ProductCode","OpSystemCode","SpecialCode"'
# 合并临时文件和表头
Get-Content "${DBfile2Convert}_temp.csv" | Add-Content "${DBfile2Convert}.csv"
# 清理临时文件
Remove-Item "${DBfile2Convert}_temp.csv" -Force

这种方式让SQLite直接处理所有数据逻辑，PowerShell仅负责调用，内存占用会骤降，同时速度提升几十倍。

2. 若必须保留循环，优化内存回收与批量操作 #

如果一定要用PowerShell循环，做以下调整：

• 强制垃圾回收：每次循环（或批量操作）后触发.NET垃圾回收，释放未使用的内存：

  [GC]::Collect()
  [GC]::WaitForPendingFinalizers()
  

• 批量写入：累积N条记录后一次性写入文件，减少IO次数和内存中暂存的对象（比如每次处理1000条）：
• 清空临时变量：循环内的$sqla、$row等变量使用后手动置空，加速对象回收：$sqla = $null

调整后的循环示例：

$batchSize = 1000
$currentBatch = @()
# 先写入表头
Set-Content "${DBfile2Convert}.csv" '"SHA-256","SHA-1","MD5","CRC32","FileName","FileSize","ProductCode","OpSystemCode","SpecialCode"'

1..$obja | % {
    $sqla = "SELECT '`"' || sha256 || '`"', '`"' || sha1 || '`"', '`"' || md5 || '`"', '`"' || crc32 || '`"', '`"' || file_name || '`"', file_size, package_id, '`"' || 0 || '`"', '`"' || '`"' FROM EXPORT ORDER BY sha1 LIMIT 1 OFFSET $_;"
    $row = $sqla | sqlite3 $DBfile2Convert
    $processedRow = $row.Replace('"""', '"').Replace('|', ',')
    $currentBatch += $processedRow

    # 批量写入并清理内存
    if ($_ % $batchSize -eq 0) {
        $currentBatch | Add-Content "${DBfile2Convert}.csv"
        $currentBatch = @()
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
        $sqla = $null
        $row = $null
    }

    # 进度条逻辑保留...
    $PercentComplete = [int](($_ / $obja) * 100)
    Write-Progress -Activity "Exporting DB Rows to CSV" -Status "$PercentComplete% Complete:" -PercentComplete $PercentComplete
    # 进度条符号逻辑...
    Write-Host -NoNewLine "`r  $PercentComplete% COMPLETE     $PERC"
}

# 写入剩余的记录
if ($currentBatch.Count -gt 0) {
    $currentBatch | Add-Content "${DBfile2Convert}.csv"
}

3. 调整PowerShell内存限制（治标） #

PowerShell默认有内存上限，可临时提升：

# 设为4GB，根据你的系统配置调整
$env:__PSMaximumMemoryPerShellMB = 4096

注意这只是临时缓解，核心还是优化导出逻辑。

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