Symfony命令批量填充identitie_name表提速方案咨询

问题背景 #

需要从Identitie实体取出约10万条数据，将关联关系与名称字段同步到identitie_name表（含identitie关联字段与name字段），当前逻辑可行但执行速度极慢，已设置php -d memory_limit=-1 bin/console my-command解除内存限制。

现有实现代码 #

Command.php #

protected function execute(InputInterface $input, OutputInterface $output) {
    $this->io = new SymfonyStyle($input, $output);
    $identities = $this->identitieRepository->getAllIdentities();
    $output->writeln('progressing...');

    foreach($identities as $identitie) {
        $this->insertIntoIdentitieName($identitie, $identitie->getName());
        $this->entityManager->flush();
    }

    $this->io->success('good!');
    
    return 0;
}

private function insertIntoIdentiteName($identitieId, $name) {
    $identitieName = new IdentiteName();
    $identitieName->setIdentite($identitieId);
    $identitieName->setName($name);
    $identitieName->setActive(true); 

    $this->entityManager->persist($identitieName);
}

Repository.php #

public function getAllIdentities()
{
    $query = $this->getEntityManager()->createQueryBuilder()
        ->select('i')->from('App\Entity\Identitie', 'i')
        ->orderBy('i.id', 'DESC');

    return $query->getQuery()->getResult();
}

提速优化建议 #

1. 批量执行flush，避免单次提交 #

当前每次循环都调用flush()，会频繁和数据库建立连接、执行事务，极大拖慢速度。建议每N条（比如1000条）批量flush一次，同时清理实体管理器避免内存堆积：

protected function execute(InputInterface $input, OutputInterface $output) {
    $this->io = new SymfonyStyle($input, $output);
    $identities = $this->identitieRepository->getAllIdentities();
    $output->writeln('progressing...');
    $batchSize = 1000;
    $count = 0;

    foreach($identities as $identitie) {
        $this->insertIntoIdentitieName($identitie, $identitie->getName());
        $count++;
        if ($count % $batchSize === 0) {
            $this->entityManager->flush();
            $this->entityManager->clear(); // 清理已持久化的对象，释放内存
            $output->writeln("已处理 {$count} 条数据");
        }
    }
    // 处理剩余不足批量的条目
    $this->entityManager->flush();
    $this->entityManager->clear();

    $this->io->success('good!');
    
    return 0;
}

2. 分页加载数据，避免一次性加载10万条实体 #

一次性加载10万条Identitie实体到内存，即使内存足够，ORM的对象跟踪和实例化也会消耗大量CPU和时间。改用分页查询分批获取数据：

// 修改Repository的方法，支持分页与字段筛选
public function getIdentitiesByPage(int $page, int $pageSize)
{
    return $this->createQueryBuilder('i')
        ->select('i.id, i.name') // 只查询需要的字段，减少数据传输
        ->orderBy('i.id', 'DESC')
        ->setFirstResult(($page - 1) * $pageSize)
        ->setMaxResults($pageSize)
        ->getQuery()
        ->getArrayResult(); // 返回数组而非实体对象，进一步提速
}

// 在Command中循环分页获取
protected function execute(InputInterface $input, OutputInterface $output) {
    $this->io = new SymfonyStyle($input, $output);
    $pageSize = 1000;
    $page = 1;
    $totalProcessed = 0;
    $output->writeln('progressing...');

    while (true) {
        $identities = $this->identitieRepository->getIdentitiesByPage($page, $pageSize);
        if (empty($identities)) {
            break;
        }
        foreach($identities as $identitie) {
            // 通过实体引用关联，避免加载整个Identitie实体
            $identiteRef = $this->entityManager->getReference(Identite::class, $identitie['id']);
            $this->insertIntoIdentiteName($identiteRef, $identitie['name']);
        }
        $this->entityManager->flush();
        $this->entityManager->clear();
        $totalProcessed += count($identities);
        $output->writeln("已处理 {$totalProcessed} 条数据");
        $page++;
    }

    $this->io->success('good!');
    
    return 0;
}

3. 使用原生SQL批量插入（最快方案） #

直接通过数据库的INSERT ... SELECT语句完成数据迁移，完全绕开PHP循环和ORM实例化，速度提升最明显：

protected function execute(InputInterface $input, OutputInterface $output) {
    $this->io = new SymfonyStyle($input, $output);
    $connection = $this->entityManager->getConnection();

    $sql = <<<SQL
        INSERT INTO identitie_name (identite_id, name, active)
        SELECT i.id, i.name, 1
        FROM identitie i
        ORDER BY i.id DESC
    SQL;

    $connection->executeQuery($sql);

    $this->io->success('good!');
    
    return 0;
}

注意：需确保表名、字段名与数据库实际一致，active字段的值根据实际需求调整（这里用1对应true）。

4. 关闭Doctrine SQL日志 #

默认情况下Doctrine会记录所有SQL语句，处理大量数据时会产生额外开销，在命令开头添加：

$this->entityManager->getConnection()->getConfiguration()->setSQLLogger(null);

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