Optimisation des requêtes : Filtrer une table pour éviter un Full Table Scan ?

Bonjour à tous,

J'ai une BDD avec une table qui contient à peu près une dizaine de millions de tuples, ces derniers représentent des statiques.

Si je veux des statiques sur une très longue période (par exemple depuis le début des mesures), MySQL devra effectuer un "Full Table Scan", bon encore là d'accord je comprend qu'il ait besoin de parcourir toute la table pour retrouver ces informations mais ça fait beaucoup ^^.

Cependant si je souhaite avoir juste les dernières statistiques (par exemple de la semaine en cours), je ne vois pas l'intérêt de parcourir la table en entier c'est une perte de temps et de ressources pour rien.

J'ai donc essayé de limiter au 1000 dernières entrées (en ne prenant que les 1000 derniers ID) avec une requête imbriquée de ''type'' LIMIT mais quand je regarde l'outil d'optimisation des requêtes de WorkBench il effectue quand même un Full Index Scan, sur lequel il fait un ORDER puis ensuite il parcourt la table entièrement (de 1000 lignes) pour faire un GROUP BY (bon encore 1000 lignes ça va c'est rapide mais voilà.).


Bref tout cela pour dire que je ne suis pas sur que cela soit très optimisé, ça fait un gros goulet d'étranglement alors que mes autres "critères de recherche" eux se base sur un "Unique Key Lookup".

Il y aurait-il un moyen de "filtrer" ma table pour avoir mes 1000 dernières entrées sans effectuer de parcours de la table entière?

Vous remerciant par avance pour votre réponse,
Cordialement,
FLIGHT'

Quel est le ddl des tables de la requête et de leurs index
Quel est l'ordre SQL utilisé
Quelles sont les statistiques et notamment, les keycard pour les critères de recherche et de jointure, le cluster-ratio si index cluster

Salut escartefigue,

Merci pour ta réponse :)


Par contre je n'ai absolument rien compris de ce que tu as demandé xD (je suis débutant en BDD).
Mes statistiques se sont des taux d'utilisation de liens radios UHF. Chaque mesure à son ID chaque mesure fait référence à un lien radio (un ID qui est une FK vers une table contenant l'ID de chaque lien et son nom).

Après j'ai pas compris ce que tu voulais savoir :)

FLIGHT'

LIMIT n'est pas un critère de recherche.
LIMIT implique de trier l'intégralité de la table pour retrouver les n lignes que vous souhaitez.
Un critère de recherche est du ressort de la clause WHERE.

Si dans votre table figure l'horodatage de vos données, il suffit de demander les données après une certaine DATEHEURE. Si la cardinalité du filtre est faible en regard de la table, alors il utilisera l'index et évitera un balayage complet de la table. Sinon, vous ne couperez pas au full scan.

A +

Ha d'accord je comprends mieux ^^

Mais je peux remplacer mon LIMIT par un WHERE alors ?
Si je fais quelque chose comme WHERE idmesure BETWEEN (MAX(idmesure) - 1000) AND MAX(idmesure)) ?

Et là il ne va pas parcourir toute la table ?


FLIGHT'


EDIT: Okay, je vais donc essayer avec la date ! merci :)
Que eux tu dire par ''Si la cardinalité du filtre est faible en regard de la table'' ?

Définition de "cardinalité" : nombre d’occurrence.

A +

Okay merci pour la précision ^^

Du coup il fait quand même un Full Scan, que ça soit avec un WHERE datemesure >= SUBTIME(NOW(),'00:10:00') ou un WHERE idmesure BETWEEN (SELECT MAX(idmesure) - 1000 FROM machin) AND (SELECT MAX(idmesure) FROM machin).....

En mettant un index sur la date cela augmenterait-il la rapidité de la recherche? Mon champ date est de type DATETIME ?

Au moins pour les MAX... Pour le reste c'est possible, mais pas sûr !

A +

il est probable que l'index sur date augmente la perf sauf si
- les dates sont très dispersées (ne correspondent pas au critère de réorg ou réorg très ancienne) et dans ce ca faire des allers retours incessants entre index et data perd plus de temps que n'en gagne
- le critère de filtrage du where correspond à un sous ensemble de dates non discriminant (environ plus de 10% de la population en général ==> pas d'utilisation d'index)
- une fonction de colonne est utilisée dans le where (genre where substring(xxxx) where(decimal(xxxx)) etc...)
- un prédicat différent ou NOT ou est utilisé dans le where
- un wildcard est utilisé au début du where (genre where madate like '%0101', ce qui dans votre cas ne devrait pas se produire

Et pour compléter la réponse de SQLPro, le cluster ratio est le taux d'organisation des données par rapport au critère de rangement physique des données de l'index cluster (index cluster qui est facultatif), l'idéal étant bien sur 100%

Salut !

Merci pour vos éclaircissements, j'y vois un peu plus clair du coup :)

Alors, j'ai réussi à avoir une requête qui fait presque ce que je veux (je crois qu'elle est bien ''optimisée'') car d'après WorkBench je n'ai que des Single Row (constant), cependant ce qui est étonnant c'est que la requête met plus de temps à s’exécuter que ses amies (0,230s au lieu de 0s) qui elles parcouraient toute la table... (Après je suis sur une table de test qui ne contient que 60k tuples donc...).

Finalement je me suis posé la question suivante: quand on dit ''optimisé'' on parle en utilisation de ressources en en temps, et éventuellement dans une certaine limite, comme ici avec 60k entrées une requête mal optimisée sera plus rapide qu'une qui l'est mais avec des millions d'entrées la requête optimisée lui mettra une mine ?

FLIGHT'

On peut voir la requête et même son plan d'exécution ?

Citation:

---

Envoyé par FLIGHTWARS

Finalement je me suis posé la question suivante: quand on dit ''optimisé'' on parle en utilisation de ressources en en temps, et éventuellement dans une certaine limite, comme ici avec 60k entrées une requête mal optimisée sera plus rapide qu'une qui l'est mais avec des millions d'entrées la requête optimisée lui mettra une mine ?

---

Le plus souvent, on s'attache à optimiser le temps CPU : on conserve la requête qui, à résultat égal bien sur, consomme le moins de CPU
Mais il peut y avoir d'autres considérations : consommation en workdb ou en logging par exemple

Holà !

Alors j'ai réussi à avoir une requête qui me donne ce que je veux (tout en étant optimisée ?).


@CinePhil :

Oui voici la requête :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SELECT MAX(id_mesure), nom_frequence, num_frequence   FROM mesure, frequence, equipement, lieu   WHERE   nom_lieu = 'Blihblih'   AND id_lieu = id_lieu_equipement_fk_idlieu_lieu   AND mesure.id_frequence_mesure_fk_idfrequence_frequence = id_frequence   AND id_lieu_frequence_fk_idequipement_equipement = id_equipement AND nom_equipement = 'Blahblah'   GROUP BY id_frequence_mesure_fk_idfrequence_frequence

---

Et le "Explain" Correspondant :

Pièce jointe 182165


@escartefigue Okay d'accord donc à quelques dixièmes de secondes mieux vaut prendre la requête qui est la mieux optimisée :)


EDIT : Ammmmm.... Finalement la requête ne me donne peut-être pas ce que je veux xD

Citation:

---

Envoyé par FLIGHTWARS

@escartefigue Okay d'accord donc à quelques dixièmes de secondes mieux vaut prendre la requête qui est la mieux optimisée :)

---

Pas toujours :?

Si le critère performance n'est pas majeur (exemple : tables de petites taille qui n'ont pas vocation à grossir, comme par exemple des domaines de valeur), alors il vaut mieux privilégier la requête facile à comprendre et à maintenir que celle qui permettra de gagner un pouillème de CPU

Il ne faut pas oublier que ce que l'on produit un jour, est maintenu ensuite par d'autres, qui n'auront pas forcément le temps ou la maitrise des fonctions SQL avancées.

Citation:

---

Il ne faut pas oublier que ce que l'on produit un jour, est maintenu ensuite par d'autres, qui n'auront pas forcément le temps ou la maitrise des fonctions SQL avancées.

---

Oui, effectivement c'est vrai qu'il faut penser aux personnes qui passent après nous :)


Bon du coup je n'arrive toujours pas à faire ce que je désire...

Donc voici la requête qui donne ce que je veux, c'est à dire qu'elle va chercher dans ma table contenant les statistiques (table mesure) la dernière entrée (donc le max_id) pour chaque fréquence appartenant à un équipement situé sur le lieu "0033-Pgxxxx" (par exemple).

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

SELECT DISTINCT ce que je veux FROM mes_tables     WHERE id_lieu = (SELECT id_lieu FROM lieu WHERE nom_lieu = '0033-Pgxxxx')   AND id_lieu = id_lieu_equipement_fk_idlieu_lieu   AND id_equipement = id_equipement_frequence_fk_idequipement_equipement   AND id_frequence = id_frequence_mesure_fk_idfrequence_frequence   AND id_mesure IN (SELECT MAX(mesurefiltree.id_mesure)     FROM (SELECT *   FROM mesure   ORDER BY id_mesure DESC LIMIT 1000) AS mesurefiltree   GROUP BY mesurefiltree.id_frequence_mesure_fk_idfrequence_frequence)   ORDER BY id_mesure DESC

---

Cependant cette dernière commence par un Full Index Scan de la table mesure, sur laquelle elle effectue un ORDER suivit d'un Full Table Scan de ma "table virtuelle (je ne sais pas comment on nomme ce ''type'' de table?) sur laquelle elle regroupe tout.

Ensuite elle effectue le même travail que dans la photo que j'ai postée 2 posts plus haut :)

Alors, comment je peux éviter cette sous-requête, ou plutôt la modifier pour ne pas parcourir à chaque fois la table dans son intégralité (car quand j'aurais 21millions d'entrées ça prendra un max de temps !:0).

Je suis sûr que c'est possible de faire plus propre que ce que j'ai fait non ?


Mercii pour votre aide :)

Citation:

---

@CinePhil :

Oui voici la requête :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SELECT MAX(id_mesure), nom_frequence, num_frequence   FROM mesure, frequence, equipement, lieu   WHERE   nom_lieu = 'Blihblih'   AND id_lieu = id_lieu_equipement_fk_idlieu_lieu   AND mesure.id_frequence_mesure_fk_idfrequence_frequence = id_frequence   AND id_lieu_frequence_fk_idequipement_equipement = id_equipement AND nom_equipement = 'Blahblah'   GROUP BY id_frequence_mesure_fk_idfrequence_frequence

---

---

1) Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN ; il serait temps de s'y mettre !

2) Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

3) L'utilisation d'alias pour les tables est fortement recommandé dès qu'il y a plus d'une table dans la requête. Cela facilite l'écriture et la lecture de celle-ci. Il faut ensuite utiliser systématiquement ces alias devant chaque colonne nommée pour savoir facilement de quelle table elle vient.



Votre besoin :

Citation:

---

chercher dans ma table contenant les statistiques (table mesure) la dernière entrée (donc le max_id) pour chaque fréquence appartenant à un équipement situé sur le lieu "0033-Pgxxxx" (par exemple)

---

Essayez ça :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT f.nom_frequence, f.num_frequence MAX(m.id_mesure) AS id_derniere_mesure FROM mesure m INNER JOIN frequence f ON m.id_frequence_mesure_fk_idfrequence_frequence = f.id_frequence INNER JOIN equipement e ON -- conditions de jointure à compléter INNER JOIN lieu l ON -- conditions de jointure à compléter WHERE l.nom_lieu = 'Blihblih' AND e.nom_equipement = 'Blahblah' GROUP BY f.nom_frequence, f.num_frequence

---

Bonjour CinePhil !

Merci pour ta réponse j'ai appris

Citation:

---

1) Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN ; il serait temps de s'y mettre !

---

Wow sérieux ?! Pourtant nous avons appris comme ça en cours cette année :0


J'ai essayé votre requête mais ça ne donne pas exactement ce que je veux ^^

En fait si je rajoute dans le SELECT mon champ date_mesure, pour les lignes affichées la date ne correspond pas à ce qu'elle devrait être...
Cela est-il un problème de condition de jointure?

Ou alors de ce dont vous avez parlé :

Citation:

---

2) Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

---

Du coup j'ai essayé en mettant le champ date_mesure dans le GROUP BY de fin, mais du coup il me mets tous les enregistrements de la table et non pas le max id....
Puis j'ai eu l'idée de faire un ORDER BY id_mesure DESC et paf ça fait de chocapics j'ai ce que je veux, enfin presque, car cela m'affiche toutes les entrées de la table pour ces fréquences...

Avec un LIMIT ça pourrait fonctionner, seulement nous n'auront pas forcément le dernier enregistrement pour chaque fréquence (si par exemple une valeur n'a pas été relevée pour une de ces fréquences).

Je ne vois pas comment faire...


FLIGHT'

Citation:

---

Envoyé par FLIGHTWARS

Citation:

---

Envoyé par CinéPhil

1) Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN ; il serait temps de s'y mettre !

---

Wow sérieux ?! Pourtant nous avons appris comme ça en cours cette année :0

---

Et bien vous pourrez dire à votre prof de se recycler en lui fournissant le lien vers le site de SQLPro que je vous ai donné dans mon précédent message !

Citation:

---

J'ai essayé votre requête mais ça ne donne pas exactement ce que je veux ^^

En fait si je rajoute dans le SELECT mon champ date_mesure, pour les lignes affichées la date ne correspond pas à ce qu'elle devrait être...
Cela est-il un problème de condition de jointure?

---

Non, la condition de jointure exprime les colonnes des deux tables en jointure qui ont la même valeur pour "rapprocher" les lignes des deux tables.

Citation:

---

Du coup j'ai essayé en mettant le champ date_mesure dans le GROUP BY de fin, mais du coup il me mets tous les enregistrements de la table et non pas le max id....

---

Si je comprends bien, vous voulez la date de la ligne correspondant à l'identifiant maximum ?
Il faut alors faire une sous requête pour chercher le MAX et utiliser cette requête en jointure pour récupérer les informations qui vous intéressent :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT tmp.nom_frequence, tmp.num_frequence, m1.date_mesure AS date_derniere_mesure FROM mesure m1 INNER JOIN ( SELECT f.nom_frequence, f.num_frequence MAX(m.id_mesure) AS id_derniere_mesure FROM mesure m INNER JOIN frequence f ON m.id_frequence_mesure_fk_idfrequence_frequence = f.id_frequence INNER JOIN equipement e ON -- conditions de jointure à compléter INNER JOIN lieu l ON -- conditions de jointure à compléter WHERE l.nom_lieu = 'Blihblih' AND e.nom_equipement = 'Blahblah' GROUP BY f.nom_frequence, f.num_frequence ) tmp ON tmp.id_derniere_mesure = m1.id_mesure

---

Citation:

---

Et bien vous pourrez dire à votre prof de se recycler en lui fournissant le lien vers le site de SQLPro que je vous ai donné dans mon précédent message !

---

Okay Je ferai ça :p

Citation:

---

Non, la condition de jointure exprime les colonnes des deux tables en jointure qui ont la même valeur pour "rapprocher" les lignes des deux tables.

---

Ha oui effectivement je m'en souviens maintenant ! :)

Merci CinePhil avec votre requête ça fonctionne exactement comme je le voulais !

J'ai même pu faire d'autres requêtes avec les jointures que vous m'avez apprises ça tourne super bien, et c'est BEAUCOUP plus simple qu'avant pour les faire :D

Merci encore pour votre aide, je pense que je vais pouvoir passer ce topic en résolut. (Vais attendre un peu avant d'être sûr ^^)


Amicalement,
FLIGHTWARS