Requête très lente

[Korben-Dallas]

Bonjour,

je rencontre des pb de lenteur avec une requête.
Comment faire pour l'accélérer au maximum ?
Merci

# Query_time: 125.591514 Lock_time: 0.000000 Rows_sent: 15 Rows_examined: 20581023
SET timestamp=1309878846;

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
SELECT
          count(*) AS 'Nbre',
          Reseaux.Service AS 'Service',
          Reseaux.Serveur AS 'Serveur',
          Reseaux.DNSServeur AS 'Nom DNS des serveurs'
    FROM
        (
                 SELECT DISTINCT 
                         C.AdressesReseaux AS IP, 
                         C.Service AS Service, 
                         C.Serveur AS Serveur, 
                         T.NomDNSServeur AS DNSServeur,
                         (
                                SELECT (count(*)>0) 
                                FROM t_Global_Serveurs R 
                                WHERE R.Service = T.Service
                                  AND C.Service = T.Service
                                  AND C.Serveur = T.NomServeur
                                  AND R.Serveur = T.NomServeur
                                  AND c.adresseIP_traitee BETWEEN r.adresseDebutPlage_traitee 
                                                              AND r.adresseFinPlage_traitee
                         ) AS inrange 
                 FROM t_Global_Clients C, 
                      t_references T
                 WHERE C.Service = T.Service
                   AND C.Serveur = T.NomServeur
        ) AS Reseaux
        WHERE Reseaux.IP LIKE '10.%.%.%'
          AND Reseaux.IP NOT LIKE '10.192.%.%'
          AND Reseaux.IP NOT LIKE '10.254.%.%'
    GROUP BY Serveur;

Le but de cette requête est de rechercher le nombre d'adresses réseaux clients ne se trouvant pas dans les plages réseaux référencées entre débutplage et finplage dans la table serveur.

Les Indexes sont mis en place dans la table :

't_Global_Clients' sur :
AdresseIP_Traitee
Service
Serveur

't_references' sur :
Serveur
Service

't_Global_Serveurs' sur :
adresseDebutPlage_traitee
adresseFinPlage_traitee
Service
Serveur

Merci pour votre aide précieuse.

[skuatamad]

Tout d'abord la requête est probabalement fausse, elle est à minima très peu fiable car :

Code :

GROUP BY Serveur

devrait être :

Code :

GROUP BY Reseaux.Service,Reseaux.Serveur,Reseaux.DNSServeur

Il faut regrouper sur les autres colonnes sélectionnées pour éviter les résultats aléatoires, à lire :
http://cedric-duprez.developpez.com/...fier-group-by/

Dans :

Code :

1
2
3
4
5
6
7
8
SELECT (count(*)>0) 
  FROM t_Global_Serveurs R 
 WHERE R.Service = T.Service
   AND C.Service = T.Service
   AND C.Serveur = T.NomServeur
   AND R.Serveur = T.NomServeur
   AND c.adresseIP_traitee BETWEEN r.adresseDebutPlage_traitee 
                               AND r.adresseFinPlage_traitee

C'est quoi count(*) > 0 ? J'ai jamais vu...

ne se trouvant pas dans les plages réseaux référencées entre débutplage et finplage dans la table serveur.

Si c'est pour tester la non existence j'utiliserais plutôt NOT EXISTS.
Sinon la partie :

Code :

1
2
AND C.Service = T.Service
AND C.Serveur = T.NomServeur

de cette requête est inutile puisque déjà présente dans la requête principale, mais je ne pense pas que ce soit l'origine des lenteurs.
De même pourquoi utiliser la sous requête reseaux ?
Le filtre effectué dans reseaux semble être effectuable directement.

Compte tenu des remarques j'écrirais la requête comme suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT c.Service AS "Service",
       c.Serveur AS "Serveur",
       t.NomDNSServeur AS "Nom DNS des serveurs",
       count(*) AS "Nbre"  
  FROM t_Global_Clients C
  JOIN t_references T ON C.Service = T.Service AND C.Serveur = T.NomServeur
 WHERE C.AdressesReseaux LIKE '10.%.%.%'
   AND C.AdressesReseaux NOT LIKE '10.192.%.%'
   AND C.AdressesReseaux NOT LIKE '10.254.%.%'
   AND NOT EXISTS (SELECT 1
                     FROM t_Global_Serveurs R 
                    WHERE R.Service = c.Service
                      AND R.Serveur = c.Serveur
                      AND c.adresseIP_traitee BETWEEN r.adresseDebutPlage_traitee 
                                                  AND r.adresseFinPlage_traitee)
 GROUP BY c.Service,c.Serveur,t.NomDNSServeur

Est-ce que les résultats sont cohérents ?
La modification du GROUP BY étant une bonne piste d'analyse si les résultats ne sont plus les mêmes.

Sinon concernant l'indexation, les infos fournies sont floues.
Est-ce qu'il y a plusieurs index sur une colonne par table ou est-ce qu'il n'y a qu'un index multi-colonne par table ?

Dans le cas présent il faut à minima un index sur les couples de colonnes suivants :
t_Global_Clients (Service,Serveur)
t_references (Service,NomServeur)
t_Global_Serveurs (Service,Serveur)

Pour t_Global_Serveurs, un index sur les 4 colonnes peut être intéressant :
t_Global_Serveurs (Service,Serveur,adresseDebutPlage_traitee,adresseFinPlage_traitee)

De même pour t_Global_Clients indéxer le triplet suivant peut être pas mal en utilisant l'alias C dans la requête corrélée (NOT EXISTS dans ma proposition) plutôt que T :
t_Global_Clients (Service,Serveur, adresseIP_traitee)


PS : l'ordre des colonnes Service,Serveur (ou NomServeur) dans la création des index est peut-être à inverser.
Par ailleurs sur MySql la création d'index "lock" les tables, donc il faut les créer lors d'une faible utilisation de la base si les tables sont volumineuses, mais tu as sûrement une DEV pour tester.

[SQLpro]

Les lenteurs de votre requête seront toujours là quelle que soit la façon dont vous vous prendrez et indexerez la base de données, car vous n'avez pas modélisé votre base correctement.

En effet vous n'avez pas respecté la première forme normale (1FN) qui dit que toutes les données des relations doivent être atomiques.

Or votre adresse IP ne l'est pas ! D’où vos LIKE ...% qui seront toujours lents.

En sus MySQL est connu pour être très mauvais en terme de performances sur les sous requêtes. Et là, à part de changer de SGBD pour un vrai SGBDR (MySQL n'étant que peu relationnel, à lire : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/), y'a pas de solution !

A +

[Korben-Dallas]

Envoyé par SQLpro

Les lenteurs de votre requête seront toujours là quelle que soit la façon dont vous vous prendrez et indexerez la base de données, car vous n'avez pas modélisé votre base correctement.

En effet vous n'avez pas respecté la première forme normale (1FN) qui dit que toutes les données des relations doivent être atomiques.

Or votre adresse IP ne l'est pas ! D’où vos LIKE ...% qui seront toujours lents.

OK Merci
Que signifie la phrase sur les données qui doivent être atomiques ?
Je ne suis pas DBA alors fabriquer une BDD avec le peu de connaissance que j'avais en plus en devant être rapide (on ne peut pas dire que ce soit simple, j'ai fait au plus vite, tout en sachant qu'elle est loin d'être nickel)
Les LIKE ....% ne sont pas ce qui me ralentit le plus la requête, qu'ils soient ou non présents, certes ça change mais pas tant que ça.

Sinon skuatamad je n'ai pas tout compris mais j'ai essayé la requête que tu indiques et malheureusement celle-ci ne me retourne pas les valeurs attendues.
Pour les index par contre, je ne sais pas trop comment renseigner.
J'ai un index par colonnes indiquées. C'est peut-être ça le pb de ma BDD, en fait je n'ai pas encore bien assimilé les histoires d'index, comment ça marche, comment bien les fabriquer, etc... donc j'ai fait au plus simple (ce qui n'est probablement pas la meilleure des solutions)

[ced]

Pour les index, regarde le tutoriel suivant : http://sqlpro.developpez.com/cours/quoi-indexer/

[skuatamad]

En quoi le résultat diffère ?
Si c'est le nombre de ligne alors c'est probablement lié au GROUP BY mais ça veut dire que ta requête initiale renvoyait des résultats aléatoires, donc elle n'était pas correcte.
Pour vérifier il suffit de rajouter les colonnes dans le GROUP BY de ta requête.

Si le résultat ne renvoit pas le bon nombre pour le COUNT, essaie avec

Code :

count(DISTINCT c.Service)

ou avec une autre colonne sélectionnée, parce qu'effectivement tu utilisais DISTINCT dans ta sous requête alors que je l'avais supprimé.

Si ça ne convient pas un jeu de test serait le bien venu pour aider à réécrire la requête.

Concernant les index, il faut que tu crées des index sur plusieurs colonnes.
PhpMyAdmin permet de le faire graphiquement sinon tu peux utiliser une commande comme :

Code :

CREATE INDEX idx_Clients_1 ON t_Global_Clients (Service,Serveur)

A décliner sur les différentes propositions déjà faites.

[Korben-Dallas]

Le fait de mettre

Code :

1
2
 
count( DISTINCT C.AdressesReseaux ) AS "Nbre"

à la place de

Code :

1
2
 
count(*) AS "Nbre"

a permis de récupérer les résultats attendus
En fait je comparais les 2 requêtes pour savoir si les résultats étaient bien identiques et maintenant c'est le cas.
En plus, ta requête a fait un gain de tps énorme : je passe de 125s à 50s environ de tps d'exécution.
Les 2 requêtes n'ont bien sûr pas été lancées en même tps mais l'une après l'autre afin de ne pas fausser les tps d'exécution.
Le serveur étant chargé à fond lorsque les requêtes s'exécutent.
Maintenant j'essaie d'adapter la requête afin de chercher la liste des réseaux des clients qui ne sont pas référencés dans les plages d'adresses indiquées par les serveurs.
Par contre pour les index, j'ai encore bcp de mal à comprendre ce que je dois créer comme index. Suis qq peu perdu avec ça.

Voici ce que j'ai comme index :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Pour la TABLE 't_Global_Clients'
 
	INDEX `Service` (`Service`),
	INDEX `Serveur` (`Serveur`),
	INDEX `AdresseIP` (`AdresseIP`),
	INDEX `AdresseIP_Traitee` (`AdresseIP_Traitee`),
	INDEX `AdressesReseaux` (`AdressesReseaux`),
	INDEX `AdressesReseaux_Traitees` (`AdressesReseaux_Traitees`)
 
 
 
 
Pour la TABLE 't_Global_Serveurs'
 
	INDEX `AdresseDebutPlage_Traitee` (`AdresseDebutPlage_Traitee`) USING BTREE,
	INDEX `AdresseFinPlage_Traitee` (`AdresseFinPlage_Traitee`) USING BTREE,
	INDEX `Service` (`Service`) USING BTREE,
	INDEX `Serveur` (`Serveur`) USING BTREE

Je pense que ça veut dire que j'ai un index par colonne mais le fait d'avoir un index par colonne (comme ici) pose-t-il de réels pb ?
Que font les index sur les multiples colonnes comme tu indiques ?
J'ai bcp de mal à comprendre

[CinePhil]

Pour cette partie de la requête :

Code :

1
2
3
WHERE C.AdressesReseaux LIKE '10.%.%.%'
   AND C.AdressesReseaux NOT LIKE '10.192.%.%'
   AND C.AdressesReseaux NOT LIKE '10.254.%.%'

À la place de LIKE, essaie avec BETWEEN :

Code :

1
2
3
WHERE C.AdressesReseaux BETWEEN '10.0.0.0' AND '10.255.255.255'
   AND C.AdressesReseaux NOT BETWEEN '10.192.0.0' AND '10.192.255.255'
   AND C.AdressesReseaux NOT BETWEEN '10.254.0.0' AND '10.254.255.255'

Le LIKE peut empêcher l'utilisation de l'index alors qu'avec BETWEEN ça devrait le faire.

[Yanika_bzh]

Envoyé par CinePhil

Pour cette partie de la requête :

Code :

À la place de LIKE, essaie avec BETWEEN :

Code :

Le LIKE peut empêcher l'utilisation de l'index alors qu'avec BETWEEN ça devrait le faire.

Surtout ne pas faire le between !
"10.192.3.1" n'est pas compris entre "10.192.0.0" et "10.192.255.255"
La comparaison de chaines de caracteres n'a rien a voir a celle de numériques.

MySql utilse les indexes pour les chaines de caracteres ne commencant pas par un caractere joker (source ici)

Pouvez vous poster le plan d'execution de votre requete ? Cela sera beaucoup plus facile pour analyser les goulots d'etranglement.

Bon courage

[CinePhil]

Envoyé par Yanika_bzh

"10.192.3.1" n'est pas compris entre "10.192.0.0" et "10.192.255.255"

Au temps pour moi !
J'ai répondu un peu vite sur ce coup là !

[skuatamad]

Envoyé par Korben-Dallas

Je pense que ça veut dire que j'ai un index par colonne mais le fait d'avoir un index par colonne (comme ici) pose-t-il de réels pb ?
Que font les index sur les multiples colonnes comme tu indiques ?
J'ai bcp de mal à comprendre

Oui tu as un index par colonne. MySql utilisera 1 index (ou 0) par accès à une table, comme les jointure se font sur plusieurs colonnes, il sera probablement plus performant d'indexer les 2 colonnes en question.
De plus il peut même devenir inutile d'accéder à la table, l'index contenant déjà toutes les colonnes nécessaires.

De plus, la base ne semble pas particulièrement modélisée, j'imagine que ce type de joiture est également réalisé dans d'autres requêtes.

Code :

1
2
FROM t_Global_Clients C
JOIN t_references T ON C.Service = T.Service AND C.Serveur = T.NomServeur

Pour savoir si les index existant sont utilisés utilise EXPLAIN.

Le mieux est de pouvoir tester la création d'index multi colonne sur une base de test, car l'ordre des colonnes a un impact et que la création d'index vérouille la table.