Optimisation d'une requête SQL

Version imprimable

Bonjour,

je sollicite votre aide à propos d'une requête SQL que je trouve anormalement lente.

La requête est la suivante :
Code:

1 2 3 4 5 6 7 SELECT m.nom as magasin, p.reference, f.libelle FROM VENTE_LIGNE vl INNER JOIN VENTE v ON (v.vente_id = vl.vente_id AND v.date_vente = 20160801) INNER JOIN MAGASIN m ON (m.magasin_id = v.magasin_id) INNER JOIN PRODUIT p ON (vl.produit_id = p.produit_id) INNER JOIN FAMILLE f ON (f.famille_id = p.famille_id) INNER JOIN SOUS_RAYON s ON (s.sourayon_id = f.sourayon_id)
Cette requête prend en moyenne 30 secondes à s'exécuter.

Si j'enlève la dernière jointure à la table SOUS_RAYON, ma requête prend moins d'une seconde.
Code:

1 2 3 4 5 6 SELECT m.nom as magasin, p.reference, f.libelle FROM VENTE_LIGNE vl INNER JOIN VENTE v ON (v.vente_id = vl.vente_id AND v.date_vente = 20160801) INNER JOIN MAGASIN m ON (m.magasin_id = v.magasin_id) INNER JOIN PRODUIT p ON (vl.produit_id = p.produit_id) INNER JOIN FAMILLE f ON (f.famille_id = p.famille_id)
J'ai beau chercher, impossible de comprendre pourquoi...

La base de données est sous MySQL.
Il y a bien des clés étrangères (donc index) sur chaque ID.

Nombre de lignes par table : VENTE_LIGNE (10 000 000), VENTE (38 000), MAGASIN (40), PRODUIT (90 000), FAMILLE (140), SOUS_RAYON (60).

Détails du schéma avec les clés primaires soulignées, et les clés étrangères avec un # :
VENTE (vente_id)
VENTE_LIGNE (vente_ligne_id, #vente_id, #magasin_id, #produit_id)
MAGASIN (magasin_id)
PRODUIT (produit_id, #famille_id)
FAMILLE (famille_id, #sousrayon_id)
SOUS_RAYON (sourayon_id)

La différence entre les 2 requêtes est donc une jointure sur une table de 58 enregistrements.
Je comprends que cela multiplie les données à interroger mais cette différence de temps me parait énorme.

Remarque : ma requête originale est bien plus complexe mais je l'ai simplifiée au maximum pour une meilleure compréhension.

Merci de m'éclairer.

05/09/2016, 12h16
François DORIN

Bonjour,

Il faudrait regarder les plans d'exécution. Il y a fort à parier que la présence/absence de la dernière jointure change complètement le plan d'exécution de la requête.
05/09/2016, 12h27
escartefigue

Bonjour,

Vu que la table sous_rayon ne contient que 60 lignes, même si l'index sur cette table est inefficient (non discriminant par exemple) un parcours séquentiel de cette table est très peu couteux si le nombre de lignes concerné par la requête sans cette jointure n'est pas énorme

Ou alors vos stats ne sont pas à jour et la table sous_rayon a bien plus de 60 lignes, auquel cas l'éligibilité de l'index devient sensible

Véirifez que les colonnes de jointure sont bien de même type et même longueur
05/09/2016, 13h00
mik3.42

2 pièce(s) jointe(s)

Citation:

Envoyé par dorinf

Bonjour,

Il faudrait regarder les plans d'exécution. Il y a fort à parier que la présence/absence de la dernière jointure change complètement le plan d'exécution de la requête.

En effet, j'avais remarqué que les plans d'exécution étaient différents.

Avec Jointure :
Pièce jointe 219632

Sans Jointure :
Pièce jointe 219633
05/09/2016, 13h01
mik3.42

Citation:

Envoyé par escartefigue

Bonjour,

Vu que la table sous_rayon ne contient que 60 lignes, même si l'index sur cette table est inefficient (non discriminant par exemple) un parcours séquentiel de cette table est très peu couteux si le nombre de lignes concerné par la requête sans cette jointure n'est pas énorme

Ou alors vos stats ne sont pas à jour et la table sous_rayon a bien plus de 60 lignes, auquel cas l'éligibilité de l'index devient sensible

Véirifez que les colonnes de jointure sont bien de même type et même longueur

Oui c'est exactement ce que je me disais.
Ces quelques lignes ne devraient rien changer.

La table a bien 60 lignes et les colonnes de jointure ont bien le même type de données et la même longueur.
05/09/2016, 14h07
escartefigue

Je n'ai pas l'habitude de ce type de formalisme pour la stratégie d'accès, mais il semble que la stratégie d'accès à la table MAGASIN (m) soit du table scan quand vous utilisez la jointure alors qu'il y a un accès par PK quand vous supprimez cette jointure.

N'y a -t- il pas un filtre where sur le code magasin qui aurait sauté entre les 2 requêtes ?
05/09/2016, 14h12
mik3.42

Citation:

Envoyé par escartefigue

Je n'ai pas l'habitude de ce type de formalisme pour la stratégie d'accès, mais il semble que la stratégie d'accès à la table MAGASIN (m) soit du table scan quand vous utilisez la jointure alors qu'il y a un accès par PK quand vous supprimez cette jointure.

N'y a -t- il pas un filtre where sur le code magasin qui aurait sauté entre les 2 requêtes ?

Non j'ai vérifié et je n'ai aucun WHERE dans la requête.

Que donne le plan d'exécution avec cette syntaxe ci ? (et le temps d'exécution) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT m.nom as magasin, p.reference, f.libelle
FROM  VENTE v 
INNER JOIN VENTE_LIGNE vl 
   ON vl.vente_id = v.vente_id
INNER JOIN MAGASIN m 
   ON m.magasin_id = v.magasin_id
INNER JOIN PRODUIT p 
   ON p.produit_id = vl.produit_id
INNER JOIN FAMILLE f 
   ON f.famille_id = p.famille_id
INNER JOIN SOUS_RAYON s 
   ON s.sourayon_id = f.sourayon_id
WHERE v.date_vente = 20160801

05/09/2016, 14h30
mik3.42

1 pièce(s) jointe(s)

Le temps d'exécution est le même : 30 secondes environ.

Voilà le plan d'exécution :
Pièce jointe 219647
05/09/2016, 15h30
escartefigue

Avec le jointure l'optimiseur part de la table rayon en faisant un index scan
Sans la jointure, l'optimiseur choisit de partir de la table magasin via un table scan
Cet écart est déjà surprenant mais admettons

Ce qui est également surprenant, c'est que
- le nombre de lignes estimées pour la table VL qui est la plus grosse est favorable à la requête avec jointure
- le nombre de lignes estimées pour la table V est très favorable à la requete avec jointure (1 seul accès contre 566 grâce à l'utilisation de la PK)

Par contre, l'accès à la table "P" qui comporte 90 000 lignes est plus favorable pour la requête sans jointure (1 accès estimé contre 338) : dans un cas c'est la PK qui est utilisée, dans l'autre une FK

Il est donc probable que ce soit le choix de la FK plutôt que de la PK pour la table P qui justifie cet écart

Combien y a -t- il de rayons distincts dans la table des produits ? si très peu, ceci explique cela

Ce que vous pouvez faire c'est imbriquer votre requete initiale, celle qui dure une seconde dans une sous-requête, encapsulée dans une requete qui fait l'accès au sous-rayon
05/09/2016, 15h44
aieeeuuuuu

Bonjour,

Citation:

Envoyé par mik3.42

Remarque : ma requête originale est bien plus complexe mais je l'ai simplifiée au maximum pour une meilleure compréhension

Observez-vous le même phénomène avec votre requete simplifiée ?

Sinon, il serait préférable de poster la requête initiale, le problème vient peut être de ce que vous avez supprimé...
05/09/2016, 15h48
escartefigue

Damned ! je n'avais pas vu cette remarque de Mik3.42, c'est fort possible en effet

Citation:

Envoyé par escartefigue

Avec le jointure l'optimiseur part de la table rayon en faisant un index scan
Sans la jointure, l'optimiseur choisit de partir de la table magasin via un table scan
Cet écart est déjà surprenant mais admettons

Ce qui est également surprenant, c'est que
- le nombre de lignes estimées pour la table VL qui est la plus grosse est favorable à la requête avec jointure
- le nombre de lignes estimées pour la table V est très favorable à la requete avec jointure (1 seul accès contre 566 grâce à l'utilisation de la PK)

Par contre, l'accès à la table "P" qui comporte 90 000 lignes est plus favorable pour la requête sans jointure (1 accès estimé contre 338) : dans un cas c'est la PK qui est utilisée, dans l'autre une FK

Il est donc probable que ce soit le choix de la FK plutôt que de la PK pour la table P qui justifie cet écart

Combien y a -t- il de rayons distincts dans la table des produits ? si très peu, ceci explique cela

Ce que vous pouvez faire c'est imbriquer votre requete initiale, celle qui dure une seconde dans une sous-requête, encapsulée dans une requete qui fait l'accès au sous-rayon

En effet, en faisant cette requête, la requête dure moins d'1 seconde :
Code:

1 2 3 4 5 6 7 8 SELECT m.nom as magasin, p.reference, f.libelle FROM VENTE_LIGNE vl INNER JOIN VENTE v ON (v.vente_id = vl.vente_id AND v.date_vente = 20160801) INNER JOIN MAGASIN m ON (m.magasin_id = v.magasin_id) INNER JOIN PRODUIT p USE INDEX (PRIMARY) ON (vl.produit_id = p.produit_id) INNER JOIN FAMILLE f ON (f.famille_id = p.famille_id) INNER JOIN SOUS_RAYON s ON (s.sourayon_id = f.sourayon_id)
Je vais donc laisser ma requête comme cela mais ça me parait compliqué de trouver ce genre de soucis...
Je pensais que MySQL était capable de toujours trouver la méthode optimale.
S'il faut que j'analyse toutes mes requêtes un peu complexe je n'ai pas fini...

Vous le faites régulièrement ?

Merci beaucoup pour votre aide.

05/09/2016, 15h55
mik3.42

Citation:

Envoyé par aieeeuuuuu

Bonjour,

Observez-vous le même phénomène avec votre requete simplifiée ?

Sinon, il serait préférable de poster la requête initiale, le problème vient peut être de ce que vous avez supprimé...

Bonjour, c'est bien la requête simplifié que je vous ai envoyé pour gagner du temps puisque le problème se posait aussi (complexe ou simplifié).
05/09/2016, 16h07
escartefigue

Citation:

Envoyé par mik3.42

Je pensais que MySQL était capable de toujours trouver la méthode optimale.
S'il faut que j'analyse toutes mes requêtes un peu complexe je n'ai pas fini...
Vous le faites régulièrement ?

Tous les SGBD peuvent avoir des failles en termes d'optimisation, mais le plus souvent ce sont les statistiques qui ne sont pas à jour, ce qui trompe l'optimiseur.
La bonne démarche est de faire préventivement des explain sur vos requêtes avant de les installe, puis de les prototyper sur volume significatif, et enfin de surveiller régulièrement les requêtes les plus couteuses.
Quand le contexte change (volumétrie, index, valeurs distinctes, organisation des données etc...) une requête jadis performante peut devenir très lente

Avez vous testé la méthode que je vous ai proposée dans mon post de 15h30

1 pièce(s) jointe(s)

C'est noté ! J'aurai appris quelque chose :)

Non je n'ai pas essayé la 2nde méthode car je ne vois pas bien comment la mettre en place...

De plus, ma requête de base est la suivante, et le USE INDEX (PRIMARY) sur la table PRODUIT ne résout pas le problème.
Code:

1 2 3 4 5 6 7 8 9 10 SELECT m.nom as magasin, f.libelle_court as famille, s.libelle_court as sourayon, r.libelle_court as rayon, SUM(vl.montant_ttc) as montant FROM VENTE_LIGNE vl INNER JOIN VENTE v ON (v.vente_id = vl.vente_id AND v.date_vente = 20160801) INNER JOIN MAGASIN m ON (m.magasin_id = v.magasin_id) INNER JOIN PRODUIT p ON (vl.produit_id = p.produit_id) INNER JOIN FAMILLE f ON (f.famille_id = p.famille_id) INNER JOIN SOUS_RAYON s ON (s.sourayon_id = f.sourayon_id) INNER JOIN RAYON r ON (r.rayon_id = s.rayon_id) GROUP BY m.magasin_id, f.famille_id, s.sourayon_id, r.rayon_id
Je vais essayer d'analyser le parcours de la requête pour l'optimiser :
Pièce jointe 219660

Merci pour votre aide précieuse.

Quelque chose comme ça

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT SUBQ.*
FROM   SOUS_RAYON S
INNER JOIN 
     (SELECT m.nom          as magasin
           , p.reference    as reference
           , f.libelle      as libelle
           , f.sourayon_id  as srid
      FROM  VENTE v 
      INNER JOIN VENTE_LIGNE vl 
         ON vl.vente_id = v.vente_id
      INNER JOIN MAGASIN m 
         ON m.magasin_id = v.magasin_id
      INNER JOIN PRODUIT p 
         ON p.produit_id = vl.produit_id
      INNER JOIN FAMILLE f 
         ON f.famille_id = p.famille_id
      WHERE v.date_vente = 20160801) as SUBQ
   ON SUBQ.srid = s.sourayon_id

L'idée est de conserver la stratégie de votre requête initiale sans la table rayon, et de faire la jointure entre la table résultante et la table rayon
Il est préférable d'éviter un HINT

06/09/2016, 08h44
mik3.42

Cette syntaxe fonctionne très bien et est aussi rapide.
Je ne savais pas qu'on pouvait mettre un alias a une sous requête, j'apprends encore quelques chose :)

Merci.
06/09/2016, 08h47
StringBuilder

Citation:

Envoyé par mik3.42

Je pensais que MySQL était capable de toujours trouver la méthode optimale.

Je dirais plutôt que MySQL est toujours capable de faire de la merde là où on ne l'attend pas :)

Voilà ma requête finale pour info :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT SUBQ.*, s.libelle_court as sourayon, r.libelle_court as rayon
FROM   SOUS_RAYON S
INNER JOIN RAYON r ON (r.rayon_id = s.rayon_id)
INNER JOIN
 
  (SELECT f.sourayon_id as srid, m.nom as magasin, f.libelle_court as famille, SUM(vl.montant_ttc) as montant
  FROM VENTE_LIGNE vl
  INNER JOIN VENTE v ON (v.vente_id = vl.vente_id AND v.date_vente >= 20160801 AND v.date_vente <= 20160831)
  INNER JOIN MAGASIN m ON (m.magasin_id = v.magasin_id)
  INNER JOIN PRODUIT p ON (vl.produit_id = p.produit_id)
  INNER JOIN FAMILLE f ON (f.famille_id = p.famille_id)
  GROUP BY f.sourayon_id, m.nom, f.libelle_court) as SUBQ
 
ON SUBQ.srid = s.sourayon_id

Merci à tous pour votre aide.

Problème résolu ;)