Symfony中Doctrine复杂左连接查询性能优化求助

问题背景 #

Symfony项目中，仓库方法获取复杂数据集供导出管理器生成CSV文件，性能瓶颈定位在用户信息关联站点数据的左连接操作。仅处理512个站点数据，单类用户查询耗时达33分钟。由于导出逻辑为遗留代码，仅能接收QueryBuilder实例，需在查询本身范围内通过DQL或Doctrine层面优化。

现有代码与关联信息 #

DQL关联代码 #

$qb->leftJoin('s.memberships', 'ex_sm', 'WITH', 'ex_sm.revokedAt IS NULL');
$qb->leftJoin('ex_sm.user', 'ex_jappr', 'WITH', 'ex_sm.approverJobReactiveWeight IS NOT NULL');  

实体关联定义 #

SiteMembership实体（对应access表）与User关联

/**
 * The user this membership encapsulates.
 *
 * @ORM\ManyToOne(targetEntity="User", inversedBy="siteMemberships", cascade={"persist"})
 * @ORM\JoinColumn(name="security_identity_id", referencedColumnName="id")
 *
 * @var User
 */
protected $user; 

User实体反向关联

/**
 * @ORM\OneToMany(targetEntity="SiteMembership", mappedBy="user", cascade={"persist"}, fetch="EXTRA_LAZY")
 */
protected $siteMemberships;

实际执行SQL #

SELECT s0_.name AS name_0, s0_.id AS id_1, GROUP_CONCAT(DISTINCT u1_.name SEPARATOR ', ') AS sclr_2 FROM site s0_ 
  LEFT JOIN access a2_ ON s0_.id = a2_.entity_id 
  AND a2_.type IN ('site_member') 
  AND (a2_.revoked_at IS NULL) 
  LEFT JOIN user u1_ ON a2_.security_identity_id = u1_.id 
  AND (a2_.approver_job_reactive_weight IS NOT NULL)

access表结构 #

CREATE TABLE `access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `buddy_id` int(11) DEFAULT NULL,
  `security_identity_id` int(11) DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `approver_job_reactive_weight` int(11) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_idx` (`type`,`security_identity_id`,`entity_id`,`buddy_id`),
  KEY `IDX_6692B54395CE8D6` (`buddy_id`),
  KEY `IDX_6692B54DF9183C9` (`security_identity_id`),
  KEY `IDX_6692B5481257D5D` (`entity_id`),
  KEY `idx_revoked_id_approver_type` (`revoked_at`,`entity_id`,`approver_job_reactive_weight`,`approver_job_planned_weight`,`type`),
  KEY `idx_user_site_access` (`revoked_at`,`security_identity_id`,`buddy_id`,`type`),
  KEY `idx_user` (`security_identity_id`),
  KEY `idx_user_id` (`security_identity_id`),
  CONSTRAINT `FK_6692B54DF9183C9` FOREIGN KEY (`security_identity_id`) REFERENCES `user` (`id`)
)
ENGINE=InnoDB AUTO_INCREMENT=262441 DEFAULT CHARSET=utf8 
COLLATE=utf8_unicode_ci

优化方案 #

1. 用子查询替代左连接，避免数据膨胀 #

当前左连接会导致站点数据因关联多条membership而重复，后续GROUP BY需处理大量冗余数据。改用标量子查询直接获取用户名称拼接结果，彻底避免左连接：

$qb->select('s.name', 's.id')
    ->addSelect('(SELECT GROUP_CONCAT(DISTINCT u.name SEPARATOR \', \') 
                  FROM SiteMembership sm 
                  JOIN sm.user u 
                  WHERE sm.entity = s 
                    AND sm.revokedAt IS NULL 
                    AND sm.approverJobReactiveWeight IS NOT NULL 
                    AND sm.type = :siteMember) AS approverNames')
    ->setParameter('siteMember', 'site_member');

该方案让数据库仅针对每个站点执行一次子查询获取结果，避免了连接后的数据行数爆炸。

2. 预筛选关联数据再连接 #

若必须保留左连接逻辑，先通过子查询筛选出符合条件的membership数据，再与站点连接，减少连接的数据量：

// 子查询筛选符合条件的SiteMembership
$subQb = $this->createQueryBuilder('sm')
    ->select('sm')
    ->where('sm.revokedAt IS NULL')
    ->andWhere('sm.approverJobReactiveWeight IS NOT NULL')
    ->andWhere('sm.type = :siteMember')
    ->setParameter('siteMember', 'site_member');

// 左连接筛选后的membership，再关联用户
$qb->leftJoin('s.memberships', 'ex_sm', 'WITH', 'ex_sm IN (:filteredMemberships)')
    ->leftJoin('ex_sm.user', 'ex_jappr')
    ->setParameter('filteredMemberships', $subQb->getQuery()->getResult());

或直接在JOIN子句中使用子查询（Doctrine 2.1+支持）：

$qb->leftJoin(
    '(SELECT sm FROM SiteMembership sm 
      WHERE sm.revokedAt IS NULL 
        AND sm.approverJobReactiveWeight IS NOT NULL 
        AND sm.type = :siteMember)',
    'ex_sm',
    'WITH',
    'ex_sm.entity = s'
)->leftJoin('ex_sm.user', 'ex_jappr')
 ->setParameter('siteMember', 'site_member');

3. 优化复合索引 #

针对当前查询的条件组合，创建精准的复合索引，避免数据库回表查询：

CREATE INDEX idx_access_export ON access (type, revoked_at, approver_job_reactive_weight, entity_id, security_identity_id);

该索引覆盖了查询中所有筛选条件（type、revoked_at IS NULL、approver_job_reactive_weight IS NOT NULL）以及关联所需的entity_id和security_identity_id字段，让数据库可直接通过索引获取所有需要的数据。

4. 禁用对象 hydration，直接返回数组 #

导出CSV无需实体对象，禁用Doctrine的对象实例化可大幅减少内存开销和处理时间：

$qb->getQuery()->setHydrationMode(\Doctrine\ORM\Query::HYDRATE_ARRAY);

若导出管理器直接执行QueryBuilder，可在构建时添加此配置。

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