Solutions possibles pour ralentir les jointures gauches dans les requêtes de doctrine complexes

Question

J'ai une méthode de référentiel Symfony qui récupère un ensemble de données assez complexe qui est ensuite placé dans un fichier CSV par une classe de gestionnaire d'exportation. Je ne veux pas mettre l'intégralité du code qui gère le travail d'exportation, mais j'ai réussi à identifier le point auquel la requête ralentit, donc ma question porte sur d'autres alternatives pour rendre la requête plus rapide, plutôt que sur le code lui-même. Ainsi, les données récupérées sont des données de « site » qui ont plusieurs « adhésions », puis des « utilisateurs ».Le problème est donc que lorsque ma requête tente de connecter les informations utilisateur au site, cela ralentit l'exécution. Cela ressemble à ceci :

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

Quelques points à mentionner (que j'ai essayés ou que je pensais pouvoir aider) :

• J'ai vérifié la table et toutes les colonnes liées ont un index et elles sont toutes du même type de données int. ≪/li>
• J'ai lu un article sur les problèmes de performances DQL qui mentionnait que la surutilisation des appels DQL Left Join peut diminuer les performances car ils remappent encore et encore le même objet d'entité. Une solution possible mentionnée consiste à obtenir l'ensemble de données principal, puis à parcourir la collection en ajoutant des ajouts (champs de données connectés) à chaque élément directement à partir de la classe d'entité du champ. Cela pourrait fonctionner (je ne sais pas quel impact cela aurait), le problème est que j'ai un code hérité très complexe et je ne veux pas toucher à la logique du gestionnaire d'exportation car cela nécessiterait trop de tests. Le gestionnaire d'exportation nécessite une classe de création de requêtes, je dois donc trouver la solution dans la requête elle-même. ≪/li>
• Le problème est définitivement causé par la jointure, et non par la clause WITH ou des conditions supplémentaires. J'ai essayé d'appeler la requête en utilisant un appel leftJoin normal, avec le même résultat. ≪/li>
• Je sais que les appels de la méthode leftJoin peuvent être enchaînés les uns aux autres, le code ressemble à ceci car certains des appels sont utilisés dans les instructions if. ≪/li>
• J'ai passé 2 jours à essayer tout ce que j'ai trouvé ici et sur d'autres sites. ≪/li>

Il existe 6 types d'utilisateurs différents, maintenant j'appelle simplement le script pour obtenir le type d'utilisateur ci-dessus et il a fallu 33 minutes pour renvoyer les données. Nous parlons de 512 sites, ce qui ne représente pas une énorme collection de données.Ma question est donc la suivante : existe-t-il un autre moyen DQL ou Doctrine pour simplifier ou réduire le nombre d'appels à leftJoins dans une requête aussi complexe et améliorer d'une manière ou d'une autre les performances ?

Mise à jour : Je pensais que le problème venait de l'index, j'ai donc donné quelques détails sur la relation : L'entité "adhésions" provient d'une table nommée "accès" et la relation avec les utilisateurs dans son modèle est la suivante :

/*** L'utilisateur encapsulé par cette adhésion.
 *
 * @ORMManyToOne(targetEntity="User", inversedBy="siteMemberships", cascade={"persist"})
 * @ORMJoinColumn(name="security_identity_id", referencedColumnName="id")
 *
 * Utilisateur @var*/
protégé $user;

Ceci est une capture d'écran de l'index attribué à la colonne "security_identity_id"

Les utilisateurs associés proviennent de la table Utilisateurs avec une relation pointant vers l'adhésion

/*** @ORMOneToMany(targetEntity="SiteMembership", mappedBy="user", cascade={"persist"}, fetch="EXTRA_LAZY")*/
protégé $siteMemberships;

La clé primaire est "l'identifiant" de l'entité.J'espère que cela met les choses dans une meilleure perspective. Je ne suis pas un expert SQL, mais j'ai essayé tout ce que j'ai trouvé et je peux le comprendre jusqu'à présent.

Mise à jour : Voici la requête exécutée :

SELECT s0_.name AS nom_0, s0_.id AS id_1, GROUP_CONCAT(DISTINCT u1_.name SEPARATOR ', ') AS sclr_2 FROM site s0_
  LEFT JOIN accède à a2_ ON s0_.id = a2_.entity_id
  ET a2_.type IN ('site_member')
  ET (a2_.revoked_at EST NULL)
  LEFT JOIN utilisateur u1_ ON a2_.security_identity_id = u1_.id
  ET (a2_.approver_job_reactive_weight N'EST PAS NULL)

Cela renverra le premier enregistrement du site ainsi que son adhésion et ses autorisations utilisateur. Mais même cette ligne prend plus de 2 minutes.

Voici les informations de création de table pour accéder à la table (entité membre)

'CREATE TABLE `access` (
  `id` int(11) NON NULL AUTO_INCREMENT,
  `buddy_id` int(11) NULL PAR DÉFAUT,
  `security_identity_id` int(11) NULL PAR DÉFAUT,
  `revoked_at` dateheure PAR DÉFAUT NULL,
  `created_at` datetime NON NULL,
  `updated_at` datetime NON NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `approver_job_reactive_weight` int(11) NULL PAR DÉFAUT,
  `entity_id` int(11) NULL PAR DÉFAUT,
  CLÉ PRIMAIRE (`id`),
  CLÉ UNIQUE `access_idx` (`type`,`security_identity_id`,`entity_id`,`buddy_id`),
  CLÉ `IDX_6692B54395CE8D6` (`buddy_id`),
  CLÉ `IDX_6692B54DF9183C9` (`security_identity_id`),
  CLÉ `IDX_6692B5481257D5D` (`entity_id`),
  CLÉ `idx_revoked_id_approver_type` (`revoked_at`, `entity_id`, `approver_job_reactive_weight`, `approver_job_planned_weight`, `type`),
  CLÉ `idx_user_site_access` (`revoked_at`, `security_identity_id`, `buddy_id`, `type`),
  CLÉ `idx_user` (`security_identity_id`),
  CLÉ `idx_user_id` (`security_identity_id`),
  CONTRAINTE `FK_6692B54DF9183C9` CLÉ ÉTRANGÈRE (`security_identity_id`) RÉFÉRENCES `user` (`id`)
)
MOTEUR=InnoDB AUTO_INCREMENT=262441 CHARSET PAR DÉFAUT=utf8
COLLATE=utf8_unicode_ci'

J'ai supprimé certaines colonnes non pertinentes.

Answer 1

Vous rejoignez de nombreux camps. C'est pour ça que ça ralentit

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

Plus vous avez d'abonnements, plus la vitesse de requête sera lente. Je ne connais pas la requête complète, mais vous pouvez démarrer la requête à partir de la table d'adhésion ou effectuer une deuxième requête.

Answer 2

Une fois terminé LEFT JOIN时，ON需要说明表是如何关联的。 WHERE 子句通常有 IS NULL 或 IS NOT NULL indique s'il faut exclure ou inclure la ligne de droite.

LEFT JOIN 和 INNER JOIN 的速度基本相同。但我需要查看索引 (SHOW CREATE TABLE) 和 SELECT SQL pour voir s'il y a d'autres problèmes.

Plus

Remplacement

KEY `IDX_6692B5481257D5D` (`entity_id`),

et

INDEX(entity_id, type, revoked_at)