Partage
  • Partager sur Facebook
  • Partager sur Twitter

Optimisation d'une requête

Modification d'un code pour l'accélérer

    16 février 2020 à 16:52:11

    Bonjour,

    Il y a quelques temps j'avais fait un formulaire qui aboutissait à la requête suivante (elle fonctionnait sans problème) :

    SELECT
    -- Partie 1 : Informations qui seront affichées --
    A.Id AS IDR,
    GROUP_CONCAT(DISTINCT V.Id SEPARATOR ', ') AS Dossiers,
    CONCAT( CONCAT( 'Du volume ', V1.Volumes, ', chapitre ', V1.Dossiers ), ' ', CONCAT( 'au volume ', V2.Volumes, ', chapitre ', V2.Dossiers ) ) AS Manga, A.Resume AS Resume,
    GROUP_CONCAT(DISTINCT PP.Nom SEPARATOR ', ') AS PersosP,
    GROUP_CONCAT(DISTINCT PV.Nom SEPARATOR ', ') AS PersosV,
    GROUP_CONCAT(DISTINCT PC.Nom SEPARATOR ', ') AS PersosC,
    GROUP_CONCAT(DISTINCT L.Nom SEPARATOR ', ') AS Lieux,
    GROUP_CONCAT(DISTINCT Ty.Nom SEPARATOR ', ') AS Types,
    GROUP_CONCAT(DISTINCT Th.Nom SEPARATOR ', ') AS Themes,
    GROUP_CONCAT(DISTINCT E.Numero SEPARATOR ', ') AS Episodes
    FROM DC_ResVol_Resume A
    INNER JOIN DC_ResVol_Dossiers LAD
        ON (A.Id = LAD.Rid)
    INNER JOIN DC_Volumes V
        ON LAD.Did = V.Id
    INNER JOIN DC_V_ResId_ChapMinMax VRCMM
        ON (A.Id = VRCMM.Id)
    INNER JOIN DC_Volumes V1
        ON VRCMM.chap_min = V1.id
    INNER JOIN DC_Volumes V2
        ON VRCMM.chap_max = V2.id
    LEFT JOIN DC_ResVol_PersosP LAPP
        ON A.Id = LAPP.Rid
    LEFT JOIN DC_Persos PP
        ON LAPP.Pid = PP.Id
    LEFT JOIN DC_ResVol_PersosC LAPC
        ON A.Id = LAPC.Rid
    LEFT JOIN DC_Persos PC
        ON LAPC.Cid = PC.Id
    LEFT JOIN DC_ResVol_PersosV LAPV
        ON A.Id = LAPV.Rid
    LEFT JOIN DC_Persos PV
        ON LAPV.sVid = PV.Id
    LEFT JOIN DC_ResVol_Lieux LAL
        ON (A.Id = LAL.Rid)
    LEFT JOIN DC_Lieux L
        ON (LAL.Lid = L.Id)
    LEFT JOIN DC_ResVol_Types LATY
        ON (A.Id = LATY.Rid)
    LEFT JOIN DC_Types TY
        ON (LATY.Tyid = TY.Id)
    LEFT JOIN DC_ResVol_Themes LATH
        ON (A.Id = LATH.Rid)
    LEFT JOIN DC_Themes TH
        ON (LATH.Thid = TH.Id)
    LEFT JOIN DC_ResVol_Episodes LAE
        ON (A.Id = LAE.Rid)
    LEFT JOIN DC_Episodes E
        ON (LAE.Episode_id = E.Id)
    
    -- Partie 2 : Jointures effectuées par requête PHP --   
    INNER JOIN DC_ResVol_Dossiers LAD2
        ON A.Id = LAD2.Rid
    INNER JOIN DC_Volumes V3
        ON LAD2.Did = V3.Id
    INNER JOIN DC_ResVol_Types LATY2
        ON A.Id = LATY2.Rid
    INNER JOIN DC_Types TY2
        ON LATY2.Tyid = TY2.Id
    INNER JOIN DC_ResVol_Lieux LAL2
        ON A.Id = LAL2.Rid
    INNER JOIN DC_Lieux L2
        ON LAL2.Lid = L2.Id
    INNER JOIN DC_ResVol_PersosP rp2
        ON A.Id = rp2.Rid
    INNER JOIN DC_Persos p2
        ON rp2.Pid = p2.Id
    INNER JOIN DC_ResVol_PersosP rp1
        ON A.Id = rp1.Rid
    INNER JOIN DC_Persos p1
        ON rp1.Pid = p1.Id
    
    -- Partie 3 : Conditions ajoutées par requête PHP --    
    WHERE (
        (V3.Id BETWEEN :volume_depart AND :volume_fin)
        AND (TY2.Id = :type)
        AND (L2.Id = :lieu)
        AND (p2.Id = :persos2)
        AND (p1.Id = :persos1)
        ) 
    GROUP BY A.Id

       
    Seulement, il y avait un problème : DC_Volumes (nom mal choisi) contenait la liste des dossiers et le volume auquel ils étaient rattachés soit :
    Ancienne table volumes

    J'ai voulu faire les choses bien et séparer les volumes des dossiers en deux tables puis les relier par une clé étrangère :
    Lien Tables Dossiers et Volumes

    Par conséquent, j'ai adapté la vue mentionnée à la ligne 18.
    En outre, j'ai modifié le nom des tables et certaines colonnes pour que ce soit plus compréhensible. Au final, j'arrive à cette requête :

    SELECT
    --  Partie 1 : Informations qui seront affichées --
        A.Id AS IDR,
        GROUP_CONCAT(DISTINCT D.Id SEPARATOR ', ') AS Dossiers,
        CONCAT( CONCAT( 'Du volume ', V1.Numero, ', chapitre ', D1.Chapitre ), ' ', CONCAT( 'au volume ', V2.Numero, ', chapitre ', D2.Chapitre ) ) AS Manga,
        A.Resume AS Resume,
        GROUP_CONCAT(DISTINCT PP.Nom SEPARATOR ', ') AS PersosP,
        GROUP_CONCAT(DISTINCT PV.Nom SEPARATOR ', ') AS PersosV,
        GROUP_CONCAT(DISTINCT PC.Nom SEPARATOR ', ') AS PersosC,
        GROUP_CONCAT(DISTINCT L.Nom SEPARATOR ', ') AS Lieux,
        GROUP_CONCAT(DISTINCT Ty.Nom SEPARATOR ', ') AS Types,
        GROUP_CONCAT(DISTINCT Th.Nom SEPARATOR ', ') AS Themes,
        GROUP_CONCAT(DISTINCT E.Numero SEPARATOR ', ') AS Episodes
    FROM DC_Affaires A
    INNER JOIN DC_Lien_Affaires_dossiers LAD
        ON (A.Id = LAD.Affaire_Id)
    INNER JOIN DC_Dossiers D
        ON LAD.Dossier_Id = D.Id
    INNER JOIN DC_V_ResId_ChapMinMax VRCMM
        ON (A.Id = VRCMM.Id)
    INNER JOIN DC_Dossiers D1
        ON VRCMM.chap_min = D1.Id
    INNER JOIN DC_Volumes V1
        ON D1.Volume_Id = V1.Id
    INNER JOIN DC_Dossiers D2
        ON VRCMM.chap_max = D2.Id
    INNER JOIN DC_Volumes V2
        ON D2.Volume_Id = V2.Id
    LEFT JOIN DC_Lien_Affaires_Persos_Presents LAPP
        ON A.Id = LAPP.Affaire_Id
    LEFT JOIN DC_Personnages PP
        ON LAPP.Personnage_Id = PP.Id
    LEFT JOIN DC_Lien_Affaires_Persos_Cites LAPC
        ON A.Id = LAPC.Affaire_Id
    LEFT JOIN DC_Personnages PC
        ON LAPC.Personnage_Id = PC.Id
    LEFT JOIN DC_Lien_Affaires_Persos_Victimes LAPV
        ON A.Id = LAPV.Affaire_Id
    LEFT JOIN DC_Personnages PV
        ON LAPV.Personnage_Id = PV.Id
    LEFT JOIN DC_Lien_Affaires_Lieux LAL
        ON (A.Id = LAL.Affaire_Id)
    LEFT JOIN DC_Lieux L
        ON (LAL.Lieu_Id = L.Id)
    LEFT JOIN DC_Lien_Affaires_Themes LATH
        ON (A.Id = LATH.Affaire_Id)
    LEFT JOIN DC_Themes TH
        ON (LATH.Theme_Id = TH.Id)
    LEFT JOIN DC_Lien_Affaires_Types LATY
        ON (A.Id = LATY.Affaire_Id)
    LEFT JOIN DC_Types TY
        ON (LATY.Type_Id = TY.Id)
    LEFT JOIN DC_Lien_Affaires_Episodes LAE
        ON (A.Id = LAE.Affaire_Id)
    LEFT JOIN DC_Episodes E
        ON (LAE.Episode_Id = E.Id)
       
    -- Partie 2 : Jointures effectuées par requête PHP --
    INNER JOIN DC_Lien_Affaires_dossiers LAD2
        ON A.Id = LAD2.Affaire_Id
    INNER JOIN DC_Lien_Affaires_Types LATY2
        ON A.Id = LATY2.Affaire_Id
    INNER JOIN DC_Types TY2
        ON LATY2.Type_Id = TY2.Id
    INNER JOIN DC_Lien_Affaires_Lieux LAL2
        ON A.Id = LAL2.Affaire_Id
    INNER JOIN DC_Lieux L2
        ON LAL2.Lieu_Id = L2.Id
    INNER JOIN DC_Lien_Affaires_Persos_Presents rp2
        ON A.Id = rp2.Affaire_Id
    INNER JOIN DC_Personnages p2
        ON rp2.Personnage_Id = p2.Id
    INNER JOIN DC_Lien_Affaires_Persos_Presents rp1
        ON A.Id = rp1.Affaire_Id
    INNER JOIN DC_Personnages p1
    ON rp1.Personnage_Id = p1.Id
    
    -- Partie 3 : Conditions ajoutées par requête PHP --
    WHERE (
        (LAD2.Dossier_Id BETWEEN :dossier_depart AND :dossier_fin)
        AND (TY2.Id = :type)
        AND (L2.Id = :lieu)
        AND (p2.Id = :persos2)
        AND (p1.Id = :persos1)
        )
        GROUP BY A.Id

       
    Cette requête fonctionne et arrive au même résultat mais est très lente (20 secondes) par rapport à la première (< 1 seconde). Je n'arrive pas à comprendre pourquoi. Pouvez-vous m'aider ? Comment puis-je l'optimiser ? Est-ce que créer une vue à partir de la première partie est recommandée (cela ferait une vue dans une autre) ?

    Merci de votre aide,

    EDIT : Je viens d'essayer en créant une vue sur la première partie. C'est plus rapide (4-5 secondes) mais toujours lent par rapport à la première requête.

    J'ai essayé de supprimer les alias dans la partie 2, rien ne change non plus...

    -
    Edité par KerberosK 16 février 2020 à 18:37:29

    • Partager sur Facebook
    • Partager sur Twitter
      Staff 17 février 2020 à 9:02:44

      Bonjour,

      Pour creuser un problème de lenteur, il faut regarder du côté des index.

      Je te conseille la lecture de ce site : https://use-the-index-luke.com/fr/

      Il te faudra d'abord observer le plan d'exécution de la requête, avec la commande EXPLAIN.

      • Partager sur Facebook
      • Partager sur Twitter
      Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
        17 février 2020 à 18:55:38

        Bonjour,

        Merci pour la réponse.
        Effectivement, c'était bien une piste à creuser. En retouchant les tables, j'avais également touché aux INDEX. J'ai comparé avec l'ancienne architecture et la principale différence résidait dans le fait que mes champs "Id" n'étaient plus des clefs primaires, seulement des INDEX. Donc, dans toutes mes tables principales, j'ai mis le champ "Id" en PRIMAIRE et là... on passe de plus de 25 secondes à 5 secondes.

        Voici l'ancien EXPLAIN :
        Tableau ancien explain

        Et voici le nouveau :
        Nouveau explain

        J'ai du mal à les interpréter. J'ai l'impression que certains problèmes dans "EXTRA" ont disparu quand d'autres sont apparus.

        J'ai du mal à saisir la différence entre clé primaire et Index. Si je comprends bien ce qui est indiqué sur ce site, la clé primaire permet de rendre identifiable chaque ligne d'une table (et refuse la valeur NULL). Et l'index sert à la recherche pour retrouver la valeur (la ligne) plus rapidement.

        Donc est-ce qu'un champ "Id" dans une table peut-il être et clé primaire et index ? Est-ce pertinent si on recherche souvent par "Id" ? Par ailleurs, sur ton lien, il est dit que le système "B-Tree" est plus performant, donc il faudrait l'activer ?

        J'ai regardé ces sites :

        https://use-the-index-luke.com/fr/sql/plans-dexecution/mysql/operations#index-only-scan
        https://blog.seboss666.info/2015/11/utiliser-explain-pour-ecrire-de-meilleures-requetes-sql/
        http://sylvain.benest.free.fr/Documentation%20APACHE_PHP_MYSQL/Manuel%20MYSQL/Manuel%20html/explain.html

        J'arrive vaguement à comprendre le tableau EXPLAIN, mais je ne parviens pas à comprendre si mes index sont bons ou non. Lorsque je regarde la colonne "possible_keys" je vois que cela correspond en grande partie à la colonne "keys".

        Sur le quatrième lien, il est dit il est dit que les "const" et les "eq_ref" que j'ai  dans la colonne "type" sont les plus efficaces. Donc, peut-on faire mieux ? Puis il est dit :
        "Si vous voulez rendre vos requêtes aussi rapide que possible, vous devriez examiner les lignes qui utilisent Using filesort et Using temporary."

        Pour moi cela correspond à la 5e ligne avec la table RP2. Il s'agit d'une requête qui s'ajoute en fonction des cases à cocher du formulaire rempli par l'utilisateur. De fait je ne comprends pas pourquoi cette ligne est différente de RP1.

        Bref je suis un peu dans le flou. j'ai gagné en rapidité en mettant mes "Id" en Primaire (sauf dans les tables de relation) et je ne sais pas si je dois me contenter de cela ou non. Sinon, je ne sais pas trop par où commencer. J'ai l'impression de toucher là à quelque chose de très technique.

        • Partager sur Facebook
        • Partager sur Twitter
          Staff 17 février 2020 à 19:02:52

          KerberosK a écrit:

          un champ "Id" dans une table peut-il être et clé primaire et index ?

          Une clé primaire est un index, enfin, un type d'index.

          KerberosK a écrit:

          J'ai l'impression de toucher là à quelque chose de très technique.

          C'est vrai, même les bons ne s'y frotte pas trop :p

          KerberosK a écrit:

          on passe de plus de 25 secondes à 5 secondes

          C'est encore un peu long je trouve. Quelle est la volumétrie de tes principales tables ?

          -
          Edité par Benzouye 17 février 2020 à 19:03:17

          • Partager sur Facebook
          • Partager sur Twitter
          Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
            17 février 2020 à 20:59:11

            En fait, le temps d'exécution, varie énormément. J'ai fait la même requête de nombreuses fois aujourd'hui et cela varie entre 1 et 5 secondes. Je ne sais pas trop comment expliquer la différence.

            Mes tables sont plutôt légères. En rouge, les tables principales appelées dans la requête et en bleu les tables de relation (j'ai tronqué la colonne du milieu pour gagner de la place :

            Structures des tables

            Je me demande (question naïve) si la lenteur ne viendrait pas de l'appel répété des mêmes tables comme DC_Personnages qui est appelé 5 fois avec des alias différents (PP, PC, PV, p1, p2).

            Mais si même les bons ne s'y frottent pas trop, je pense que je vais éviter de faire n'importe quoi ;)

            • Partager sur Facebook
            • Partager sur Twitter
              Staff 18 février 2020 à 9:26:11

              Côté SQL tu peux regarder pour limitrt le nombre de LEFT JOIN pour ne laisser que ceux absolument nécessaires, en gros en LEFT JOIN seulement les tables pour lesquelles il est possible qu'aucun enregistrement n'existe.

              Tu peux également remplacer le BETWEEN par un couple ... >= ...  AND ... <= ...

              • Partager sur Facebook
              • Partager sur Twitter
              Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
                18 février 2020 à 13:59:16

                Bonjour,

                Merci pour ta réponse et pour ton aide.

                En remplaçant uniquement le BETWEEN par les opérateurs, je tourne autour de 1,9 seconde (testé directement sur la console mariadb).

                Par contre pour remplacer les LEFT JOIn par des INNER JOIN, ça a produit tout l'effet inverse. Dans la première partie du code, j'ai voulu remplacer ces lignes :

                LEFT JOIN DC_Lien_Affaires_Persos_Presents LAPP
                    ON A.Id = LAPP.Affaire_Id
                LEFT JOIN DC_Personnages PP
                    ON LAPP.Personnage_Id = PP.Id 
                par :

                INNER JOIN DC_Lien_Affaires_Persos_Presents LAPP
                    ON A.Id = LAPP.Affaire_Id
                INNER JOIN DC_Personnages PP
                    ON LAPP.Personnage_Id = PP.Id 
                Considérant que dans une histoire, il y a forcément des personnages présents, cela semblait logique. Résultat : 2'16".

                J'ai donc laissé le LEFT sur ces lignes et mis des INNER pour les lignes suivantes et j'ai remonté ces dernières dans le code :

                INNER JOIN DC_Lien_Affaires_Lieux LAL
                    ON (A.Id = LAL.Affaire_Id)
                INNER JOIN DC_Lieux L
                    ON (LAL.Lieu_Id = L.Id)
                INNER JOIN DC_Lien_Affaires_Types LATY
                    ON (A.Id = LATY.Affaire_Id)
                INNER JOIN DC_Types TY
                    ON (LATY.Type_Id = TY.Id)

                   
                Résultat : 33 secondes.

                À chaque fois, EXPLAIN n'indique rien de plus que ce que j'ai déjà montré.

                Du coup, j'ai tenté l'inverse, de remplacer les premiers INNER JOIN par des LEFT JOIN :

                LEFT JOIN DC_Lien_Affaires_dossiers LAD 
                	ON (A.Id = LAD.Affaire_Id) 
                LEFT JOIN DC_Dossiers D 
                	ON LAD.Dossier_Id = D.Id 
                LEFT JOIN DC_V_ResId_ChapMinMax VRCMM 
                	ON (A.Id = VRCMM.Id) 
                LEFT JOIN DC_Dossiers D1 
                	ON VRCMM.chap_min = D1.Id 
                LEFT JOIN DC_Volumes V1 
                	ON D1.Volume_Id = V1.Id 
                LEFT JOIN DC_Dossiers D2 
                	ON VRCMM.chap_max = D2.Id 
                LEFT JOIN DC_Volumes V2 
                	ON D2.Volume_Id = V2.Id 

                et là j'arrive à moins de 1,5 secondes. Bizarre.

                Je vais donc rester sur ma version actuelle (avec le BETWEEN remplacé) qui semble être le meilleur compromis entre logique et rapidité.

                Merci pour ton aide :)

                • Partager sur Facebook
                • Partager sur Twitter

                Optimisation d'une requête

                × Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
                • Editeur
                • Markdown