Laravel批量处理37M数据时内存泄漏问题求助

架构老林

2026-6-14

内存泄漏排查：Laravel批量处理37M行数据时内存持续累积

问题背景

有两张业务表：

articles：约3700万行，包含article_id和journal_id字段
journal_specialty：约2.9万行，包含journal_id和specialty_id字段

需求是基于期刊与领域的关联关系，将article_id和对应的specialty_id批量填充到article_specialty表中。编写的Laravel控制台命令采用批量读取（BATCH_SIZE=50000）、分块插入（INSERT_CHUNK_SIZE=25000）的逻辑，并尝试了内存释放操作，但内存仍持续累积，最终触发PHP内存耗尽错误。

原始代码实现

namespace App\Console\Commands\Articles;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class xa02_ArticleSpecialty extends Command
{
  protected $signature = 'articles:xa02-article-specialty';
  protected $description = 'Populate article_specialty table using journal_specialty relationships';

  private const BATCH_SIZE = 50000;
  private const INSERT_CHUNK_SIZE = 25000;

  public function handle()
  {
    $this->info('Starting article_specialty population...');
    $startTime = microtime(true);

    $journalSpecialties = DB::table('journal_specialty')
      ->select('journal_id', 'specialty_id')
      ->get()
      ->groupBy('journal_id')
      ->map(fn($group) => $group->pluck('specialty_id')->toArray())
      ->toArray();

    $totalRecords = DB::select("SHOW TABLE STATUS LIKE 'articles'")[0]->Rows;

    $this->info("Total articles to process: $totalRecords");

    $bar = $this->output->createProgressBar($totalRecords);
    $bar->start();

    $offset = 0;

    // Debugging: Show initial memory usage
    $this->info('Initial memory usage: ' . memory_get_usage() . ' bytes');

    while ($offset < $totalRecords) {
      $articles = DB::table('articles')
        ->orderBy('date_e')
        ->limit(self::BATCH_SIZE)
        ->offset($offset)
        ->get()
        ->toArray();

      // Debugging: Show memory usage after fetching articles
      $this->info('Memory usage after fetching articles: ' . memory_get_usage() . ' bytes');

      if (empty($articles)) {
        break; // ✅ No more data to process
      }

      $insertData = [];

      foreach ($articles as $article) {
        if (isset($journalSpecialties[$article->journal_id])) {
          foreach ($journalSpecialties[$article->journal_id] as $specialty_id) {
            $insertData[] = [
              'article_id' => $article->article_id,
              'specialty_id' => $specialty_id
            ];
          }
        }

        if (count($insertData) >= self::INSERT_CHUNK_SIZE) {
          DB::table('article_specialty')->insertOrIgnore($insertData);
          $insertData = []; // ✅ Free memory immediately
        }
      }

      // Debugging: Show memory usage after inserting
      $this->info('Memory usage after inserting: ' . memory_get_usage() . ' bytes');

      if (!empty($insertData)) {
        DB::table('article_specialty')->insertOrIgnore($insertData);
      }

      $bar->advance(count($articles));

      // Trying to free memory after processing
      $articles = null;
      $insertData = null;
      gc_collect_cycles();
      clearstatcache();
      DB::table('articles')->newQuery();
      DB::flushQueryLog();

      $offset += self::BATCH_SIZE;

      // Debugging: Show memory usage after processing batch
      $this->newLine();
      $this->info('Memory usage after processing batch: ' . memory_get_usage() . ' bytes');
    }

    $bar->finish();
    $this->newLine();

    $totalTime = microtime(true) - $startTime;
    $this->info('✅ Completed! Processed articles in ' . gmdate("H:i:s", $totalTime) . '.');

    // Debugging: Show final memory usage
    $this->info('Final memory usage: ' . memory_get_usage() . ' bytes');
  }
}

执行日志片段

Starting article_specialty population...
Total articles to process: 37765760
        0/37765760 [>---------------------------]   0%
Initial memory usage: 35593704 bytes
Memory usage after fetching articles: 141872128 bytes
Memory usage after inserting: 147389216 bytes
   100000/37765760 [>---------------------------]   0%
Memory usage after processing batch: 41217656 bytes
Memory usage after fetching articles: 145440808 bytes
Memory usage after inserting: 155017720 bytes
   200000/37765760 [>---------------------------]   0%
Memory usage after processing batch: 46857472 bytes
Memory usage after fetching articles: 151319400 bytes
Memory usage after inserting: 161351936 bytes
   300000/37765760 [>---------------------------]   0%
Memory usage after processing batch: 52506008 bytes
...

最终错误信息

PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 1052672 bytes)

问题核心分析

从日志可见，每次处理完一批数据后，内存并未回到初始水平，而是逐步递增（35M→41M→46M→52M...），根源在于：

Offset分页的致命缺陷
使用offset($offset)+limit()分页时，MySQL需要扫描从表开头到offset位置的所有行，随着offset不断增大，扫描的数据量呈线性增长。Laravel的数据库驱动（如PDO）会缓存这些扫描过程中的临时数据，导致内存无法完全释放。
无效的内存释放操作
- DB::table('articles')->newQuery()：无法重置现有查询实例，完全无效。
- clearstatcache()：用于清除文件状态缓存，与数据库操作无关，对释放内存无帮助。
- DB::flushQueryLog()：若未开启查询日志，该操作无意义。
Collection转数组的额外开销
get()->toArray()会先创建Collection对象，再转换为数组，额外占用了内存空间。

解决方案

1. 改用键集分页（Keyset Pagination）

键集分页基于上一页最后一条记录的唯一标识（如date_e+article_id）进行查询，彻底避免offset分页的扫描开销，同时解决内存累积问题。

2. 优化`journalSpecialties`构建方式

直接用原生数组存储关联关系，避免Collection的额外内存开销。

3. 精简内存回收操作

只保留必要的内存释放步骤，移除无效操作。

修改后的代码

namespace App\Console\Commands\Articles;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class xa02_ArticleSpecialty extends Command
{
    protected $signature = 'articles:xa02-article-specialty';
    protected $description = 'Populate article_specialty table using journal_specialty relationships';

    private const BATCH_SIZE = 50000;
    private const INSERT_CHUNK_SIZE = 25000;

    public function handle()
    {
        $this->info('Starting article_specialty population...');
        $startTime = microtime(true);

        // 直接构建数组，避免Collection内存开销
        $journalSpecialties = [];
        DB::table('journal_specialty')
            ->select('journal_id', 'specialty_id')
            ->chunk(1000, function ($rows) use (&$journalSpecialties) {
                foreach ($rows as $row) {
                    $journalSpecialties[$row->journal_id][] = $row->specialty_id;
                }
            });

        $totalRecords = DB::select("SHOW TABLE STATUS LIKE 'articles'")[0]->Rows;
        $this->info("Total articles to process: $totalRecords");

        $bar = $this->output->createProgressBar($totalRecords);
        $bar->start();

        $lastDate = null;
        $lastArticleId = null;
        $processed = 0;

        $this->info('Initial memory usage: ' . memory_get_usage() . ' bytes');

        while (true) {
            $query = DB::table('articles')
                ->select('article_id', 'journal_id', 'date_e')
                ->orderBy('date_e')
                ->orderBy('article_id')
                ->limit(self::BATCH_SIZE);

            // 键集分页条件：基于上一页最后一条记录的标识查询
            if ($lastDate !== null) {
                $query->where(function ($q) use ($lastDate, $lastArticleId) {
                    $q->where('date_e', '>', $lastDate)
                      ->orWhere(function ($subQ) use ($lastDate, $lastArticleId) {
                          $subQ->where('date_e', '=', $lastDate)
                               ->where('article_id', '>', $lastArticleId);
                      });
                });
            }

            // 直接获取StdClass数组，跳过Collection转换
            $articles = $query->get()->all();

            $this->info('Memory usage after fetching articles: ' . memory_get_usage() . ' bytes');

            if (empty($articles)) {
                break;
            }

            $insertData = [];

            foreach ($articles as $article) {
                if (isset($journalSpecialties[$article->journal_id])) {
                    foreach ($journalSpecialties[$article->journal_id] as $specialty_id) {
                        $insertData[] = [
                            'article_id' => $article->article_id,
                            'specialty_id' => $specialty_id
                        ];
                    }
                }

                if (count($insertData) >= self::INSERT_CHUNK_SIZE) {
                    DB::table('article_specialty')->insertOrIgnore($insertData);
                    $insertData = [];
                }
            }

            if (!empty($insertData)) {
                DB::table('article_specialty')->insertOrIgnore($insertData);
            }

            $batchCount = count($articles);
            $processed += $batchCount;
            $bar->advance($batchCount);

            // 更新下一页的起始标识
            $lastArticle = end($articles);
            $lastDate = $lastArticle->date_e;
            $lastArticleId = $lastArticle->article_id;

            // 内存回收：销毁变量+强制GC
            unset($articles, $insertData, $lastArticle);
            gc_collect_cycles();

            $this->newLine();
            $this->info('Memory usage after processing batch: ' . memory_get_usage() . ' bytes');
        }

        $bar->finish();
        $this->newLine();

        $totalTime = microtime(true) - $startTime;
        $this->info('✅ Completed! Processed articles in ' . gmdate("H:i:s", $totalTime) . '.');
        $this->info('Final memory usage: ' . memory_get_usage() . ' bytes');
    }
}

额外优化建议

优化索引：
- 给articles表的date_e和article_id创建联合索引，加速键集分页查询。
- 给article_specialty表的article_id和specialty_id创建联合唯一索引，确保insertOrIgnore逻辑生效。
调整批量大小：
根据服务器内存配置，适当减小BATCH_SIZE（如30000），进一步降低单批次内存占用。
禁用查询日志：
在命令开头添加DB::disableQueryLog();，避免日志占用额外内存。

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

Laravel批量处理37M数据时内存泄漏问题求助

问题背景 #

原始代码实现 #

执行日志片段 #

最终错误信息 #

问题核心分析 #

解决方案 #

1. 改用键集分页（Keyset Pagination） #

2. 优化journalSpecialties构建方式 #

3. 精简内存回收操作 #

修改后的代码 #

额外优化建议 #