Petit cours rapide d'optimisation SQL

Modérateurs: Ligevum, Crepuscule, Wargaming, Okracoke, Simerion, Heroes, Bestiaire, Exsulare, terato, pentacle, Nainwak

Petit cours rapide d'optimisation SQL

Messagepar Haiken » 19 Oct 2009, 19:18

-------- Message original --------
Sujet : Petit cours rapide d'optimisation SQL
Date : Sun, 07 Dec 2008 23:13:11 +0100
De : Assoc. Nainwak / Florent Masson <assoc>
Pour : assoc


Salut,

ayant fait une passe ce soir sur les requêtes SQL gourmandes, je me permet de vous faire un petit mémo vite fait

A)

SELECT * FROM objets WHERE joueur = 15;

=> Mettre un index sur la colonne (joueur)

B)
SELECT * FROM objets WHERE joueur = 15 and etat = 12;
=> Mettre un index sur le couple de colonnes (joueur,etat). Les index multi-colonnes se lisent de gauche à droite et peuvent être utilisés partiellement tels quels. C'est-à-dire qu'un index (joueur,etat) couvre un index (joueur) et sera utilisé pour la requête SELECT * FROM joueur WHERE joueur = 15; L'index (joueur) ne sert à rien si vous avez un index (joueur,etat)

B)

SELECT * FROM objets WHERE joueur = 15 and etat = 12 order by date;
=> Il y a un tri en plus à la fin. On peut mettre un index (joueur,etat,date). Le reste de l'index sera utilisé pour accélérer un petit peu le tri. Bien sûr, si votre requête ne renvoie que quelques lignes, cela n'a pas d'intérêt.


C)
SELECT * FROM joueur WHERE etat = 12 order by date_connexion;
=> Mettre index sur (etat,date) dans cet ordre : d'abord appliquer le filtre de la condition, puis le reste de l'index sert pour le tri

D)
SELECT img,mat,x,y,fond,type FROM crep_carte WHERE x>='-9' && x<='17' && y>='-8' && y<='18' && z='-999' ORDER BY y DESC, x ASC;

=> Grand classique dans nos jeux. Il faut mettre un index sur (z,y,x) puisque le filtre sur z est le plus discriminant (il "élimine" le plus le ligne dans le parcours de la table). Ensuite y et x à cause des filtres et de l'ordre des tris. Un index bateau sur (x,y,z) fontionne mais moins bien. Pour info je suis passé sur l'un des jeux qui se reconnaîtra de 1s à 30ms en transformant l'index (x,y,z) en (z,y,x)

E)
SELECT COUNT(*) FROM boards WHERE (black_user_id=11206 OR white_user_id=11206) AND game_status = 3;
=> Ce genre de requête est très difficile car les index sont mal utilisés à cause de la condition OR. Il vaut mieux fractionner la requête avec UNION ou UNION DISTINCT ou + dans ce cas précis (parce que c'est un compte) : select (SELECT COUNT(*) FROM boards WHERE (black_user_id=11206) AND game_status = 3)+SELECT COUNT(*) FROM boards WHERE (white_user_id=11206) AND game_status = 3))


Pour vérifier quels index sont utilisés sur une requête, faites un EXPLAIN requête qui vous fournira le plan d'exécution. Regardez en particulier la colonne rows qui indique le nombre de lignes remontées à chaque étape. Plus c'est petit, mieux c'est !
Sinon il y a la méthode barbare de mesurer le temps que prends une requête (phpmyadmin vous l'affiche). De façon général et sauf exception (une requête qui renvoie vraiment bcp de lignes) si ça prend plus de 100 ms, c'est mauvais.

Vous pouvez bien sûr nous envoyer un mail (tech@nainwak.org) si vous avez des soucis ou des questions sur le sujet, on est là pour vous aider là dessus aussi !
Des requêtes un peu trop gourmandes et ce sont tous les jeux qui en pâtissent, pensez-y ! Alors que des fois un simple index fait la différence.

Bonne soirée
Florent




-------- Message original --------
Sujet : Re: Petit cours rapide d'optimisation SQL
Date : Mon, 08 Dec 2008 21:57:50 +0100
De : Assoc. Nainwak / Florent Masson <assoc>
Pour : assoc


Oui merci pour l'importante précision que j'ai oubliée !
Lorsque l'on teste une requête, bien indiquer le SQL_NO_CACHE pour
éviter que le résultat soit récupéré en cache (ce qui arrivera
systématiquement si vous êtes seul sur votre machine)

En ce qui concerne le choix d'un requête avec 17 jointures ou plein de
petites requêtes la question ne doit pas se poser au début : on commence
par construire son modèle de données en troisième forme normale, et donc
la requête qui va bien (avec autant de jointures qu'il faut). Si après
coup on arrive effectivement aux limites de mysql, ou qu'on arrive pas à
optimiser la requête (et après avoir demandé à DT ou moi bien sûr ;), il
est raisonnable de la fractionner. Ceci dit je doute que beaucoup de
jeux aient besoin de requêtes sur 17 tables ^^ Et je n'ai pas vu le
plan d'exécution de celle dont tu parles Pierre, mais il est possible
que ce soit normal... Voir aussi si le temps cumulé des requêtes uniques
ne dépasse pas 3s ! Dans ce cas c'est une grossière erreur de les
transformer juste parce que ça dépasse un seuil et que ça logge une alerte.

Enfin, sur le fait de considérer plus d'1s pour une grosse requête, tout
dépend ce que tu appelles une grosse requête ! Les requêtes que tu fais
sur ton application professionnelle sont loin à mon avis de ce qu'on
trouve habituellement sur un jeu php. Une requête classique c'est
- récupérer la liste des messages du joueur
- récupérer les cases visibles du joueur
- récupérer l'inventaire du joueur
- sélectionner un joueur dans une liste (choix de cible etc)
- récupérer la liste des joueurs triés (classement)
Toutes ces requêtes parcourent généralement 2-3 tables et remontent au
max une centaine de lignes. 100ms pour une requête de ce type c'est déjà
beaucoup.
Bien sûr que ça dépend du serveur, mais tu auras un rapport de 2-3x
entre une bonne machine et une mauvaise, ce n'est pas significatif. Ce
qui est sûr c'est que la requête mal optimisée on construite prendra
facilement 10s même sur une bonne machine !

Par contre si tu as une requête qui parcoure 15 tables pour rechercher
les joueurs agés de moins de 42 ans ayant plus de 15 messages dans leur
boite aux lettres et ayant tué au moins 3 joueurs verts dans les
dernières 24 heures (et mangé du potiron au petit dej'), c'est sûr que
ça va prendre un peu de temps (et encore pas tant que ça...)

Autre chose aussi, n'ayez pas peur de la volumétrie. Je ne dis pas qu'il
faut laisser des lignes qui ne servent à rien dans vos tables, mais
prenez juste conscience qu'une base de données est faite pour gérer des
millions de lignes. Le mécanisme pour faire en sorte d'éviter de la
parcourir en entier, ce sont les index ! Avec de bons index vos requêtes
prendront de 0ms pour une table vide à quelques dizaines de ms pour une
table en millions d'enregistrements. Sans index, ça prendra plutôt
quelques dizaines de secondes...

That's all for tonight
Flo

Pierre Pmithrandir a écrit :
> Je rajouterais dans les tests de requêtes My-SQL que
> SELECT SQL_NO_CACHE toto
> FROM Matable
> WHERE coucou = "test"
>
> Exécutera toujours la requête sans utiliser le cache, ce qui peut être
> pratique dans le debug pour trouver les requêtes lentes ou valider des
> optimisation de requetes (il m'est arrivé plus d'une fois au boulot de
> penser optimiser une requete avec une modification mineure alors
> qu'elle passait juste partiellement ou totalement en cache...).
> A voir si cela ne dépend pas des regles de l'hébergeur un peu aussi...
>
> Petite question :
> mieux vaut une grosse requetes avec 17 tables en jointure JOIN ou LEFT
> JOIN, ou pleins de petites requetes pour le serveur...
>
> Je sais que au boulot on est aller vers des requetes unique, jusqu'a
> en revenir parce qu'elles entraient dans la liste des slow query (+ de
> 3sec)
>
> Dailleur, les grosses requetes sont considérée comme calide en dessous
> de 1 seconde chez nous, ca dépend du serveur, ou on est vraiment trés
> mausvais en perf mysql ?
>
> Pierre
>
Avatar de l’utilisateur
Haiken
Président de l'Association
 
Messages: 666
Inscription: 01 Avr 2004, 09:00

Retourner vers Questions Pratiques/Techniques

Qui est en ligne

Utilisateurs parcourant ce forum: Aucun utilisateur enregistré et 1 invité

cron