復(fù)雜學(xué)說查詢中左連接緩慢的可能解決方案
P粉517475670
2023-09-02 18:55:02
<p>我有一個(gè) symfony 存儲(chǔ)庫(kù)方法，它獲取一個(gè)相當(dāng)復(fù)雜的數(shù)據(jù)集，然后由導(dǎo)出管理器類將其放置在 CSV 文件中。我不想放置處理導(dǎo)出作業(yè)的整個(gè)代碼，但我設(shè)法查出查詢變慢的點(diǎn)，所以我的問題是關(guān)于任何其他使查詢更快的替代方案，而不是代碼本身。
因此，獲取的數(shù)據(jù)是一些“站點(diǎn)”數(shù)據(jù)，它具有多個(gè)“成員資格”，然后具有“用戶”。所以問題是，當(dāng)我的查詢嘗試將用戶信息連接到站點(diǎn)時(shí)，它會(huì)減慢執(zhí)行速度。它看起來像這樣：</p>
<pre class="brush:php;toolbar:false;">$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');</pre>
<p>有幾件事要提一下（我嘗試過或想了一下可能會(huì)有所幫助）：</p>
<ul>
<li>我檢查了表格，所有鏈接的列都有一個(gè)索引，并且它們都是相同的 int 數(shù)據(jù)類型。</li>
<li>我紅色了一篇關(guān)于 DQL 性能問題的文章，其中提到過度使用 DQL Left Join 調(diào)用可能會(huì)降低性能，因?yàn)樗鼈円槐橛忠槐榈刂匦掠成渫粚?shí)體對(duì)象。提到的一個(gè)可能的解決方案是獲取主數(shù)據(jù)集，然后循環(huán)遍歷集合，直接從字段的實(shí)體類向每個(gè)元素添加附加（連接數(shù)據(jù)字段）。這可能可行（不確定它會(huì)產(chǎn)生多大影響），問題是我擁有的是非常復(fù)雜的遺留代碼，我不想觸及導(dǎo)出管理器的邏輯，因?yàn)檫@需要太多的測(cè)試。導(dǎo)出管理器需要一個(gè)查詢生成器類，因此我必須在查詢本身中找到解決方案。</li>
<li>該問題肯定是由連接引起的，而不是由“WITH”子句或附加條件引起的。我嘗試使用普通的 leftJoin 調(diào)用來調(diào)用查詢，結(jié)果相同。</li>
<li>我知道 leftJoin 方法可以相互鏈接調(diào)用，代碼看起來是這樣的，因?yàn)槠渲幸恍┱{(diào)用在 if 語句中使用。</li>
<li>我花了 2 天的時(shí)間嘗試了這里和其他網(wǎng)站上找到的所有內(nèi)容。</li>
</ul>
<p>有 6 種不同的用戶類型，現(xiàn)在我只是調(diào)用腳本來獲取上面的用戶類型，花了 33 分鐘才返回?cái)?shù)據(jù)。我們談?wù)摰氖?512 個(gè)站點(diǎn)，這并不是一個(gè)巨大的數(shù)據(jù)集合。所以我的問題是：是否有另一種 DQL 或任何 Doctrine 方法來簡(jiǎn)化或減少如此復(fù)雜的查詢中 leftJoins 的調(diào)用數(shù)量，并以某種方式提高性能？</p>
<p>更新：
我認(rèn)為問題出在索引上，所以我給出了一些關(guān)于關(guān)系的細(xì)節(jié)：
“memberships”實(shí)體來自名為“access”的表，其模型中與用戶的關(guān)系如下所示：</p>
<pre class="brush:php;toolbar:false;">/*** 該成員資格封裝的用戶。
*
* @ORM\ManyToOne(targetEntity=“用戶”, inversedBy=“siteMemberships”, 級(jí)聯(lián)={“堅(jiān)持”})
* @ORM\JoinColumn(name="security_identity_id",referencedColumnName="id")
*
* @var 用戶*/
protected $user;</pre>
<p>這是分配給“security_identity_id”列的索引的屏幕截圖
</p>
<p>相關(guān)的用戶來自具有指向成員資格的關(guān)系的“用戶”表</p>
<pre class="brush:php;toolbar:false;">/*** @ORM\OneToMany(targetEntity=“SiteMembership”，mappedBy=“用戶”，級(jí)聯(lián)={“persist”}，fetch=“EXTRA_LAZY”)*/
protected $siteMemberships;</pre>
<p>主鍵是實(shí)體中的“id”。希望這可以更好地看待問題。我不是 sql 專家，但嘗試了我發(fā)現(xiàn)的所有內(nèi)容并且到目前為止可以理解。</p>
<p>更新：
這是執(zhí)行的查詢：</p>
<pre class="brush:php;toolbar:false;">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)</pre>
<p>這將返回第一個(gè)站點(diǎn)記錄及其加入的成員資格和用戶權(quán)限。但即使是這一排的時(shí)間也需要 2 分鐘以上。</p>
<p>這里是訪問（會(huì)員實(shí)體）表的建表信息</p>
<pre class="brush:php;toolbar:false;">'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'</pre>
<p>我刪除了一些不相關(guān)的列。</p>
你加入了很多陣營(yíng)。這就是速度變慢的原因
$qb->leftJoin('s.memberships', 'ex_sm', 'WITH', 'ex_sm.revokedAt IS NULL');
會(huì)員資格越多，查詢速度就越慢。我不知道完整查詢?nèi)绾?，但您可以從成員資格表開始查詢，也可以進(jìn)行第二次查詢。
點(diǎn)贊 +0
P粉517475670
當(dāng)進(jìn)行LEFT JOIN時(shí)，ON需要說明表是如何關(guān)聯(lián)的。 WHERE 子句通常有 IS NULL 或 IS NOT NULL 來表示是否排除或包含右側(cè)行。
LEFT JOIN 和 INNER JOIN 的速度基本相同。但我需要查看索引 (SHOW CREATE TABLE) 和 SELECT 的 SQL，看看是否存在其他問題。
更多
替換
KEY `IDX_6692B5481257D5D` (`entity_id`),
與
INDEX(entity_id, type, revoked_at)
點(diǎn)贊 +0
P粉517475670
2
0
0
