複雜學說查詢中左連結緩慢的可能解決方案

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)