复杂学说查询中左连接缓慢的可能解决方案

Question

我有一个 symfony 存储库方法，它获取一个相当复杂的数据集，然后由导出管理器类将其放置在 CSV 文件中。我不想放置处理导出作业的整个代码，但我设法查出查询变慢的点，所以我的问题是关于任何其他使查询更快的替代方案，而不是代码本身。 因此，获取的数据是一些“站点”数据，它具有多个“成员资格”，然后具有“用户”。所以问题是，当我的查询尝试将用户信息连接到站点时，它会减慢执行速度。它看起来像这样：

$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');

有几件事要提一下（我尝试过或想了一下可能会有所帮助）：

• 我检查了表格，所有链接的列都有一个索引，并且它们都是相同的 int 数据类型。
• 我红色了一篇关于 DQL 性能问题的文章，其中提到过度使用 DQL Left Join 调用可能会降低性能，因为它们一遍又一遍地重新映射同一实体对象。提到的一个可能的解决方案是获取主数据集，然后循环遍历集合，直接从字段的实体类向每个元素添加附加（连接数据字段）。这可能可行（不确定它会产生多大影响），问题是我拥有的是非常复杂的遗留代码，我不想触及导出管理器的逻辑，因为这需要太多的测试。导出管理器需要一个查询生成器类，因此我必须在查询本身中找到解决方案。
• 该问题肯定是由连接引起的，而不是由“WITH”子句或附加条件引起的。我尝试使用普通的 leftJoin 调用来调用查询，结果相同。
• 我知道 leftJoin 方法可以相互链接调用，代码看起来是这样的，因为其中一些调用在 if 语句中使用。
• 我花了 2 天的时间尝试了这里和其他网站上找到的所有内容。

有 6 种不同的用户类型，现在我只是调用脚本来获取上面的用户类型，花了 33 分钟才返回数据。我们谈论的是 512 个站点，这并不是一个巨大的数据集合。所以我的问题是：是否有另一种 DQL 或任何 Doctrine 方法来简化或减少如此复杂的查询中 leftJoins 的调用数量，并以某种方式提高性能？

更新： 我认为问题出在索引上，所以我给出了一些关于关系的细节： “memberships”实体来自名为“access”的表，其模型中与用户的关系如下所示：

/*** 该成员资格封装的用户。
 *
 * @ORM\ManyToOne(targetEntity=“用户”, inversedBy=“siteMemberships”, 级联={“坚持”})
 * @ORM\JoinColumn(name="security_identity_id",referencedColumnName="id")
 *
 * @var 用户*/
protected $user;

这是分配给“security_identity_id”列的索引的屏幕截图

相关的用户来自具有指向成员资格的关系的“用户”表

/*** @ORM\OneToMany(targetEntity=“SiteMembership”，mappedBy=“用户”，级联={“persist”}，fetch=“EXTRA_LAZY”)*/
protected $siteMemberships;

主键是实体中的“id”。希望这可以更好地看待问题。我不是 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)

这将返回第一个站点记录及其加入的成员资格和用户权限。但即使是这一排的时间也需要 2 分钟以上。

这里是访问（会员实体）表的建表信息

'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'

我删除了一些不相关的列。

Answer 1

你加入了很多阵营。这就是速度变慢的原因

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

会员资格越多，查询速度就越慢。我不知道完整查询如何，但您可以从成员资格表开始查询，也可以进行第二次查询。

Answer 2

当进行LEFT JOIN时，ON需要说明表是如何关联的。 WHERE 子句通常有 IS NULL 或 IS NOT NULL 来表示是否排除或包含右侧行。

LEFT JOIN 和 INNER JOIN 的速度基本相同。但我需要查看索引 (SHOW CREATE TABLE) 和 SELECT 的 SQL，看看是否存在其他问题。

更多

替换

KEY `IDX_6692B5481257D5D` (`entity_id`),

与

INDEX(entity_id, type, revoked_at)