optimisation de requete sql

[mercurey]

Bonjour à tous,

J'utilise Mysql et j'ai un requete qui rame de temps en temps. La plupart du temps elle met 0.02s à s’exécuter mais des fois ça monte à 25s, je ne comprends pas.

SELECT SQL_CALC_FOUND_ROWS idhabit, telhab, ville, departement, numero, loc, dated, cc, piece, surface, descriptif, descen, descde, url, locasais, demande, idpass, datevignette, datemaj, nbphotos, h.type, h.prix, 0 AS trilast
FROM habitation h, personne p
WHERE h.telhab = p.telpers && h.visible =1 && p.visible =1 && ( h.cpdep = '24' ) && ( h.type = 'maison' ) && h.demande = 'annonce' && h.loc = 'vente' && h.prix >=50000 && h.prix <=70000
ORDER BY trilast ASC , prix DESC
LIMIT 50 , 10

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE  | h | ref type,prix,demande,visible,telhab,loc,demande_2,cpdep | cpdep | 5 | const 6976 Using where; Using filesort
1 | SIMPLE | p | eq_ref telpers_2,telpers,visible | telpers | 32 | lesclesd_db.h.telhab | 1 | Using where

est ce que l'un de vous aurai une idée ?

merci

Modifié 26 Novembre 2012 par mercurey

[Dadou]

As tu mis un index sur les colonnes de tes critères, avec un index, cela tournera bien plus vite

[mercurey]

Oui, c'est vrai que sans index, là, je seais à 1min / requete ;o)

j'ai mis la sortie du "explain" justement après la requête, mais c'est pas forcement facile de l’interpréter.

[Dadou]

Essai en faisant une jointure pour la table personne, pour que le critère de rapprochement ne soit pas dans le where, une jointure, c'est normalement plus rapide

SELECT SQL_CALC_FOUND_ROWS idhabit, telhab, ville, departement, numero, loc, dated, cc, piece, surface, descriptif, descen, descde, url, locasais, demande, idpass, datevignette, datemaj, nbphotos, h.type, h.prix, 0 AS trilast
FROM habitation h join personne p on h.telhab = p.telpers
WHERE h.visible =1 && p.visible =1 && ( h.cpdep = '24' ) && ( h.type = 'maison' ) && h.demande = 'annonce' && h.loc = 'vente' && h.prix >=50000 && h.prix <=70000
ORDER BY trilast ASC , prix DESC
LIMIT 50 , 10

[mercurey]

Bonne idée, mais apparemment moins performant (en tout cas dans mon cas), je tourne à 0.03s (contre 0.02 avant), j'ai également eu un 12s sur les 10 tests.

le "explain" ressort exactement la même chose

[Dadou]

Une jointure est normalement plus rapide, puisque dans ta requête, il y a un produit cartésien de fait alors que dans la mienne non.

Pour une requête aussi simple, pour moi il ne devrait pas y avoir de lenteurs, quand c'est le cas, il y a un problème d'index, je pense qu'il doit en manquer

[mercurey]

Tous les champs sont en index, c'est ce qui me pose problème et comme tu dis, c'est une requête très simple qui rame "seulement" à peux près 1 fois sur 10, sinon, elle va très bien.

si il y a un problème d'index, est ce qu'on devrait pas le voir avec le "explain" ?

merci pour ton aide

[Dadou]

La longueur de ta clé telpers est vraiment à 32 ? ça me parait grand comme longueur d'index

[mercurey]

Mince, tu as raison, j'avais pas fait gaffe à ça, tu crois que ça peux avoir une incidence sur le fait que ça rame aléatoirement ?

[Dadou]

Oui, plus l'index est grand, plus il met de temps a chercher, après, il y a le cache SQL qui peut jouer

[jcaron]

Si je décode correctement les choses, tu as des index sur chaque colonne, plutôt qu'un (ou plusieurs) index sur plusieurs colonnes? Si c'est bien le cas, suivant la répartition des données dans la table, c'est pas du tout évident qu'aucun index n'aide tant que ça, et en plus ça complique le boulot de mysql pour trouver quel index il va utiliser.

Dans ton exemple, il se pose donc la question de quel index il va utiliser: celui sur type, celui sur prix, celui sur demande, etc. Il finit par choisir celui sur cpdep (probablement parce qu'au vu des infos qu'il a, c'est le plus "discriminant"). Ça lui donne 6976 lignes, qu'il faut qu'il lise toutes, ensuite il trie tout ça (en utilisant un fichier temporaire), puis il fait le LIMIT, puis pour chacune de ces lignes, il faut qu'il fasse la jointure avec l'autre table. Ça représente une tonne d'I/O. Ta requête va vite quand les parties utiles de la table sont entièrement dans le cache de l'OS et que les E/S de ton disque ne sont pas saturées, mais il suffit de peu pour que ça soit éjecté du cache (ou que ce soit une requête sur une partie moins utilisée et donc pas en cache), et hop, il faut qu'il recharge tout ça depuis le disque, ce qui prend nettement plus de temps. Il peut aussi arriver qu'en fonction des valeurs des critères, il décide d'utiliser un autre index qui se révèle être moins discriminant, et l'oblige à charger encore plus de données.

Plusieurs solutions:

- la première, c'est au minimum d'ajouter les critères de tri dans chaque index susceptible d'être utilisé (ici ça donnerait un index sur cpdep,prix). Comme ça, il lit les lignes directement dans le bon ordre, et il peut s'arrêter dès qu'il en a assez (en tous cas postgresql le ferait, je suppose que mysql aussi)

- la deuxième, c'est d'avoir un ou plusieurs index qui incluent plusieurs des colonnes qui sont utilisées comme critère. Si par exemple tu as forcément tous ces critères dans ton WHERE, un index sur cpdep,type,demande,loc,visible,prix serait idéal. Si c'est plus souple que ça il va falloir adapter un peu. L'ordre des colonnes dans l'index peut avoir pas mal d'importance aussi, suivant le niveau de "discrimination" de chaque critère.

_AT_Dadou: Heureusement, un serveur SQL décent utilise généralement le même plan pour une jointure explicite (a JOIN b ON a.c=b.d) et une jointure implicite (a,b WHERE a.c=b.d) tant qu'il n'y a pas de OUTER JOIN dans les parages. D'ailleurs ça se voit bien dans le plan choisi: il utilise un index sur la première table pour limiter le produit cartésien à faire, donc il utilise le critère avant le produit cartésien, pas après.

Au final, sauf cas particuliers, c'est plus une question de lisibilité qu'autre chose. Ça permet aussi de ne pas oublier un critère de jointure, et ça permet de permuter entre un INNER et un OUTER JOIN plus facilement (et aussi de mélanger les différents types de JOINs dans la même requête, parce que dans ce cas, un critère dans le WHERE et un dans le ON ne veulent pas du tout dire la même chose).

Jacques.

[Dadou]

Malheureusement Mysql n'est pas un serveur SQL décent et il merdouille quelque peu sur les optimisations (du moins jusqu’à sa version 5.1), en l'occurence, quand tu commences a avoir du volume, tu t’aperçois vite qu'il y a une réelle différence entre la jointure explicite, et l'implicite. Il merdouille aussi pas mal quand tu met une requête dans un "in". Au boulot, avec la volumétrie que l'on a, on titille pas mal les limites de Mysql, on s'arrache parfois les cheveux, sur des points que Postgres gérerait sans aucun soucis.

[jcaron]

Le planner de mysql est probablement nettement plus simpliste que celui de postgresql, mais j'espère quand même que sur un cas aussi facile il retombe sur ses pattes :-) D'ailleurs le plan choisi montre que c'est le cas, c'est juste que les index ne sont pas suffisants pour arriver à un bon résultat.

Jacques.

[mercurey]

Merci beaucoup à vous 2, je vais donc me re-pencher sur les index. Tous les champs utilisé sont bien en index, mais pas forcement en index multiple, je penses qu'il serait plus néfaste que positif de prévoir tous les cas. Cette requete sort du moteur de recherche et varie d'une recherche à l'autre.

Est ce que si je met un index multiple sur "cpdep,type,demande,loc,visible" qui est la base, ce sera mieux ? sachant qu'il y aura toujours en plus soit le prix et/ou la surface et/ou ...

+++

j'ai créé des index multiples "generique", ont va voir

Modifié 27 Novembre 2012 par mercurey

[mercurey]

le nouvel explain :

id |  select_type |  table |  type |  possible_keys |  key |  key_len |  ref |  rows |  Extra
1 | SIMPLE |  h | ref |  type,prix,demande,visible,telhab,loc,demande_2,cpd... | type_2 | 40 | const,const,const | 4516 | Using where; Using filesort
1 | SIMPLE | p | eq_ref | telpers_2,telpers,visible | telpers | 32 | lesclesd_db.h.telhab | 1 | Using where

cela devrait apporter qq chose vu qu'il travail maintenant sur 4516 lignes au lieux de 6976 lignes

[jcaron]

C'est mieux, mais c'est loin d'être optimal, surtout qu'il utilise toujours un filesort. Idéalement, avec les bons index, il ne devrait avoir besoin que de charger les N premières lignes nécessaires, et ne pas faire de filesort.

Tu as combien de critères optionnels et combien de critères de tri possibles?

Jacques.

[mercurey]

Désolé pour la réponse tardive.

J'ai 7 critères optionnels ou variables (je peux choisir 4 pièces ou 4 et 5 et 6 pièces par exemple) et 4 critères de tri * desc et asc

[jcaron]

Effectivement là ça risque d'être difficile de faire des index pour toutes les combinaisons de critères. Ceci dit, suivant la proportion de lectures et d'écritures, ça peut être tout à fait "rentable". Sinon tu peux essayer de faire des stats sur les combinaisons les plus courantes, et mettre les index correspondants pour voir ce que ça donne.

Jacques.

[mercurey]

Merci Jacques, j'ai fait le tests avec plusieurs index multiples de "base" les plus courant, j'ai l'impression que c'est un petit peux mieux, mais c'est pas encore ça.

Ça rame toujours aléatoirement de temps en temps.

to be continued ...

[Dadou]

Si ça rame encore, il est peut être nécessaire de repenser ton schéma de base de données

Ou de monitorer les requêtes lentes, comme cela, tu sauras précisément, avec quels critères la requête est plus longue

[jcaron]

Une méthode qui permet quand tout va bien de réduire les temps d'accès, c'est de séparer une table avec des champs importants en deux.

Par exemple, pour tes annonces, tu fais une première table avec les colonnes qui correspondent aux critères de filtrage et de tri (département, prix, type, date, etc.), et une deuxième table avec les colonnes qui ne sont pas utilisées pour le filtrage ou le tri et qui peuvent être plus longues (titre, description, etc.).

Comme ça, si le planner SQL fait bien son travail, il n'a besoin d'accéder qu'à la première table (qui est beaucoup plus petite et donc a plus de chances de rester en cache) pour déterminer les lignes à retourner, et ensuite seulement il va piocher les quelques lignes dont il a besoin dans l'autre table.

Mais quoi qu'il arrive, si tu as plus de données que de RAM, il y a un moment où des bouts de table ne vont pas être en RAM, et il va falloir aller les chercher sur le disque, ce qui est forcément plus lent.

Jacques.