Mögliche Lösungen zur Verlangsamung von Left-Joins in komplexen Doktrinabfragen

Question

Ich habe eine Symfony-Repository-Methode, die einen ziemlich komplexen Datensatz abruft, der dann von einer Export-Manager-Klasse in einer CSV-Datei abgelegt wird. Ich möchte nicht den gesamten Code einfügen, der den Exportauftrag abwickelt, aber es ist mir gelungen, den Punkt zu bestimmen, an dem die Abfrage langsamer wird. Daher bezieht sich meine Frage auf andere Alternativen, um die Abfrage schneller zu machen, und nicht auf den Code selbst. Bei den abgerufenen Daten handelt es sich also um einige „Site“-Daten, die über mehrere „Mitgliedschaften“ und dann über „Benutzer“ verfügen.Das Problem besteht also darin, dass meine Abfrage, wenn sie versucht, die Benutzerinformationen mit der Site zu verbinden, die Ausführung verlangsamt. Es sieht so aus:

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

Ein paar Dinge, die ich erwähnen sollte (von denen ich versucht habe oder von denen ich dachte, dass sie helfen könnten):

• Ich habe die Tabelle überprüft und festgestellt, dass alle verknüpften Spalten einen Index haben und alle vom gleichen int-Datentyp sind.
• Ich habe einen Artikel über DQL-Leistungsprobleme gelesen, in dem erwähnt wurde, dass eine übermäßige Verwendung von DQL Left Join-Aufrufen die Leistung beeinträchtigen kann, weil sie dasselbe Entitätsobjekt immer wieder neu zuordnen. Eine mögliche mögliche Lösung besteht darin, den Hauptdatensatz abzurufen und dann die Sammlung zu durchlaufen und jedem Element Anhänge (verbundene Datenfelder) direkt aus der Entitätsklasse des Felds hinzuzufügen. Das könnte funktionieren (ich bin mir nicht sicher, wie groß die Auswirkungen wären). Das Problem ist, dass ich sehr komplexen Legacy-Code habe und ich die Logik des Exportmanagers nicht berühren möchte, da dies zu viele Tests erfordern würde. Der Exportmanager benötigt eine Abfrage-Builder-Klasse, daher muss ich die Lösung in der Abfrage selbst finden.
• Das Problem wird definitiv durch den Join verursacht, nicht durch die WITH-Klausel oder zusätzliche Bedingungen. Ich habe versucht, die Abfrage mit einem normalen leftJoin-Aufruf aufzurufen, mit dem gleichen Ergebnis.
• Ich weiß, dass die Aufrufe der leftJoin-Methode miteinander verkettet werden können. Der Code sieht so aus, weil einige der Aufrufe in if-Anweisungen verwendet werden.
• Ich habe zwei Tage lang alles ausprobiert, was ich hier und auf anderen Websites gefunden habe.

Es gibt 6 verschiedene Benutzertypen. Jetzt rufe ich einfach das Skript auf, um den oben genannten Benutzertyp abzurufen, und es dauerte 33 Minuten, bis die Daten zurückgegeben wurden. Wir sprechen von 512 Websites, was keine große Datensammlung darstellt.Meine Frage lautet also: Gibt es eine andere DQL- oder Doctrine-Möglichkeit, die Anzahl der Aufrufe von leftJoins in einer so komplexen Abfrage zu vereinfachen oder zu reduzieren und die Leistung irgendwie zu verbessern?

Update: Ich dachte, das Problem läge beim Index, also habe ich einige Details zur Beziehung angegeben: Die Entität „Mitgliedschaften“ stammt aus einer Tabelle mit dem Namen „Zugriff“ und die Beziehung zu Benutzern in ihrem Modell ist wie folgt:

/*** Der Benutzer, den diese Mitgliedschaft umfasst.
 *
 * @ORMManyToOne(targetEntity="User", inversedBy="siteMemberships", cascade={"persist"})
 * @ORMJoinColumn(name="security_identity_id", referencedColumnName="id")
 *
 * @var Benutzer*/
protected $user;

Dies ist ein Screenshot des Index, der der Spalte „security_identity_id“ zugewiesen ist

Die zugehörigen Benutzer stammen aus der Tabelle „Benutzer“ mit einer Beziehung, die auf die Mitgliedschaft verweist.

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

Der Primärschlüssel ist die „id“ in der Entität.Ich hoffe, das bringt die Dinge in eine bessere Perspektive. Ich bin kein SQL-Experte, habe aber alles versucht, was ich gefunden habe, und kann es bisher herausfinden.

Update: Dies ist die ausgeführte Abfrage:

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 Zugriff auf a2_ ON s0_.id = a2_.entity_id
  AND a2_.type IN ('site_member')
  UND (a2_.revoked_at IST NULL)
  LEFT JOIN Benutzer u1_ ON a2_.security_identity_id = u1_.id
  UND (a2_.approver_job_reactive_weight IST NICHT NULL)

Dadurch wird der erste Site-Datensatz zusammen mit seiner Mitgliedschaft und seinen Benutzerberechtigungen zurückgegeben. Aber selbst diese Reihe dauert über 2 Minuten.

Hier sind die Informationen zur Tabellenerstellung für den Zugriff auf die Tabelle (Mitgliedsentität)

'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` Datum/Uhrzeit NICHT NULL,
  `updated_at` datetime NICHT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `approver_job_reactive_weight` int(11) DEFAULT NULL,
  `entity_id` int(11) STANDARD NULL,
  PRIMÄRSCHLÜSSEL („id“),
  EINZIGARTIGER SCHLÜSSEL „access_idx“ („type“, „security_identity_id“, „entity_id“, „buddy_id“),
  SCHLÜSSEL `IDX_6692B54395CE8D6` (`buddy_id`),
  SCHLÜSSEL `IDX_6692B54DF9183C9` (`security_identity_id`),
  SCHLÜSSEL `IDX_6692B5481257D5D` (`entity_id`),
  SCHLÜSSEL `idx_revoked_id_approver_type` (`revoked_at`, `entity_id`, `approver_job_reactive_weight`, `approver_job_planned_weight`, `type`),
  SCHLÜSSEL `idx_user_site_access` (`revoked_at`, `security_identity_id`, `buddy_id`, `type`),
  SCHLÜSSEL `idx_user` (`security_identity_id`),
  SCHLÜSSEL `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'

Ich habe einige irrelevante Spalten gelöscht.

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)