Besoin d'aide concernant les indexes - Mysql

Discussion dans 'Développement d'un site Web ou d'une appli mobile' créé par noren, 24 Juillet 2014.

  1. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    Salut

    bon ça fait des mois que je galères avec ces foutus indexes et requêtes à optimiser et j'aurais vraiment besoin d'un sacré coup de main, car mes projets ne peuvent avancer si je ne règle pas tous ces problèmes d'optimisation de requêtes et d'indexes. :?

    Donc je poserais ici une question a la fois, concernant ces soucis d'indexes et optimisation, pour avancer pas par pas.

    Je ne comprend pas le fonctionnement de ce fichu optimiseur mysql (et c'est pas faute d'avoir lu de nombreux articles sur le sujet), qui me rend chèvre.

    Procédons donc par étape.
    Voici ma première question, normalement simple pour une mise en bouche (les autres on verra plus tard quand celle ci sera d'abord résolu)

    ma table t_commentaires

    Code:
    CREATE TABLE `t_commentaires` (
     `id_commentaire` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `id_bloc` int(10) unsigned NOT NULL DEFAULT '0',
     `id_membre` int(10) unsigned NOT NULL DEFAULT '0',
     `pseudo` varchar(15) NOT NULL,
     `commentaire` text NOT NULL,
     `email` varchar(100) NOT NULL,
     `url_perso` varchar(200) NOT NULL,
     `ip` varchar(100) NOT NULL,
     `date_ajout` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
     `en_ligne` tinyint(1) unsigned NOT NULL DEFAULT '0',
     `id_commentaire_parent` int(10) unsigned NOT NULL DEFAULT '0',
     PRIMARY KEY (`id_commentaire`),
     KEY `id_commentaire_parent` (`id_commentaire_parent`),
     KEY `idx_ip_date` (`ip`,`date_ajout`),
     KEY `idx_membre_date` (`id_membre`,`en_ligne`,`date_ajout`),
     KEY `date_ajout` (`date_ajout`),
     KEY `idx_bloc_date` (`id_bloc`,`en_ligne`,`date_ajout`),
     KEY `idx_ligne_date` (`en_ligne`,`date_ajout`)
    ) ENGINE=MyISAM AUTO_INCREMENT=105265 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
    voici une requête simple qui devrait poser aucun problème au 1er abord :

    Code:
    select c.pseudo,c.date_ajout, c.commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.date_ajout DESC LIMIT 0, 5
    avec la clause where et le order by par date je devrais donc utiliser correctement l'index `idx_ligne_date` hors voici le résultat de l'explain

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
    1 	SIMPLE 	c 	ref 	idx_ligne_date 	idx_ligne_date 	1 	const 	53495 	Using where 
    on constate qu'il prend bien la clé idx_ligne_date,mais qu'il ne s'en sert pas correctement.
    La preuve :
    - il balai toutes les lignes ayant en_ligne=1, soit quasi toutes (rows = 53495), alors qu'on devrait avoir rows = 5
    - dans le extra on devrait trouver : Using where, Using index

    dans l'index idx_ligne_date il devrait y avoir ceci :

    1 dateA
    1 dateB
    1 dateC
    ....
    0 dateAA
    0 dateBB

    Par conséquent mysql n'a aucune raison de balayer quasiment tous les enregistrements et devrez utiliser correctement l'index idx_ligne_date.
     
  2. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    Si mon problème et mon post n’est pas clair, n’hésitez pas à le dire hein :mrgreen:
     
  3. baud74
    baud74 WRInaute impliqué
    Inscrit:
    21 Juillet 2014
    Messages:
    586
    J'aime reçus:
    0
    il faut être un expert en mysql pour pouvoir savoir s'il y a moyen de faire mieux, et c'est pas sur que ces experts soient sur un tel forum.
     
  4. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    Rooo tu sous estime les compétences des Wrinautes :wink:
    J'en ai pourtant vu quelques un de très très compétents
     
  5. chnain35
    chnain35 WRInaute discret
    Inscrit:
    7 Avril 2010
    Messages:
    53
    J'aime reçus:
    0
    noren, en fait tu n'utilises que en_ligne dans le where, mysql ne mélange pas les colonnes du where et du order by.

    De plus, les index sur les colonnes date ne sont jamais très efficaces, par expérience. Il me semble que les type timestamp sont plus efficaces.

    Sinon tu stockes juste Année + Mois dans un colonne de type char(6) ce qui permet de faire un index assez court.

    Autre idée, quand tu as des soucis de mysql et de performances, il faut toujours se tourner vers les scripts crontab. Pour ce cas précis je n'ai pas d'idée, car tu cherches avant tout à trier.

    Essaie de simplifier l'index en enlevant le champ date, juste avec en_ligne, car il n'est pas bon non plus d'avoir des index trop gros.

    Certaines colonnes peuvent aussi être optimisées, par exemple INT(10) est surdimensionné, essaie avec MEDIUMINT(7) unsigned
     
  6. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    Arf j'avais tapé tout une réponse et j'ai fait ctrl w et ca a fermé la fenetre grrr :|

    bon je vais refaire ma réponse de facon plus succinte

    pas d'accord avec toi : :wink:

    http://dev.mysql.com/doc/refman/5.0/fr/order-by-optimization.html

    Code:
    SELECT * FROM t1 WHERE partie_clef1=constante ORDER BY partie_clef2
    C'est exactement mon cas

    concernant timestamp, et datetime, c’est un long débat mais pour prouver que ça ne venait pas de date_ajout et de son format j'ai essayé de remplacer date_ajout par id_commentaire, et j'ai créé un index multiple (en_ligne, id_commentaire).

    Code:
    select c.id_commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.id_commentaire DESC LIMIT 0, 5
    J'ai exactement le même problème qu'avec date_ajout

    Je ne connais pas, je ferais des recherche la dessus. Pourrais tu me dire ne gros à quoi ca sert et serait utile dans mon cas?

    Mettre un index sur un champ "en_ligne" est inutile dans la mesure ou il y a une cardinalité de 2. D'autant plus qu'a 99% mes enregistrements ont en_ligne à 1
    je ne crois pas non plus qu'une clé multiple (en_ligne, date_ajout) soit trop grosse :wink:, encore moins pour 50 000 malheureux petits enregistrements :wink:

    sinon j'ai fait la modifications pour les int, et pas de changement au niveau des perfs :(
     
  7. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    707
    J'aime reçus:
    0
    Tu pourrais nous faire une archive avec la structure et le jeu de données que tu utilises. Cela nous permettra de travailler avec les mêmes infos.
     
  8. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    Blount je t'ai envoyé un message :wink:
     
  9. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    707
    J'aime reçus:
    0
    Je pense avoir trouvé quelques réponses.

    Ton "problème" pour le "Using index" semble tout à fait normal.
    D'après la doc :
    Ils indiquent bien que cela arrive quand seul des colonnes indexées sont utilisées. Or, tu indiques des colonnes non indexées dans le SELECT (c.pseudo,c.date_ajout, c.commentaire). Donc, certes, MySQL n'indique pas "Using index", mais cela ne signifie pas que MySQL n'utilisera pas d'index mais plutôt que MySQL n'a pas eu besoin de lire le fichier de la table pour traiter la requête et que seul le fichier d'index lui a suffit. J'ai fait le test en définissant un index sur "en_ligne". Ensuite, cette requête :
    Code:
    EXPLAIN SELECT c.pseudo, c.date_ajout, c.commentaire, c.en_ligne FROM t_commentaires AS c WHERE c.en_ligne = 1
    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1	SIMPLE	c	ref	en_ligne	en_ligne	1	const	49992	
    
    Effectivement, rien dans "Extra".
    Maintenant :
    Code:
    EXPLAIN SELECT c.en_ligne FROM t_commentaires AS c WHERE c.en_ligne = 1
    
    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1	SIMPLE	c	ref	en_ligne	en_ligne	1	const	49992	Using index
    
    Et là, tu as bien le "Using index".

    Ensuite, toujours dans la doc concernant les colonnes index :
    "possible_keys" indique bien les clés que MySQL a détecté comme utilisables. Et la colonne "key" indique ensuite les clés que MySQL a choisi d'utiliser. Donc, pour traiter ta requête MySQL utilise bien une clé.


    Concernant la colonne ROW explain, la doc indique :
    Ici, tu remarques une subtilité : estime devoir.
    En clair, je pense que quand tu fais un EXPLAIN, la requête n'est pas exécutée et MySQL ne sait donc pas réellement combien de ligne seront lues. Il indique donc le nombre total.
    Tu le remarques aussi avec cette requête :
    Code:
    EXPLAIN SELECT c.id_commentaire FROM t_commentaires AS c WHERE c.id_commentaire = 105257
    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1	SIMPLE	c	const	PRIMARY	PRIMARY	4	const	1	Using index
    Tu remarques dans la clause WHERE que j’utilise le champ avec la clé primaire PRIMAIRE. MySQL sait tout de suite que un seul résultat sera lu puisqu'une clé primaire induit une valeur unique, d'où le rows=1.
    Tu le vérifies aussi avec une clé UNIQUE.

    Pour savoir si MySQL va analyser toute la table, il faut vérifier la colonne "type" d'EXPLAIN.
    Dans le doc :
    Code:
    ALL
    
    Une analyse complète de la table sera faîte pour chaque combinaison de lignes issue des premières tables. Ce n'est pas bon si la première table n'est pas une jointure de type const et c'est très mauvais dans les autres cas. Normalement vous pouvez éviter ces situations de ALL en ajoutant des index basée sur des parties de colonnes.
    
    Si tu n'as pas ALL, tu n'as donc pas une analyse complète de la table.

    Dans ta requête, le type indiqué est REF, toujours dans la doc:
    Code:
    ref
    
    Toutes les lignes avec des valeurs d'index correspondantes seront lues dans cette table, pour chaque combinaison des lignes précédentes. ref est utilisé si la jointure n'utilise que le préfixe de gauche de la clé, ou si la clé n'est pas UNIQUE ou PRIMARY KEY (en d'autres termes, si la jointure ne peut pas sélectionner qu'une seule ligne en fonction de la clé). Si la clé qui est utilisée n'identifie que quelques lignes à chaque fois, la jointure est bonne.
    
    ref peut être utilisé pour les colonnes indexées, qui sont comparées avec l'opérateur =
    
    "Toutes les lignes avec des valeurs d'index correspondantes seront lues dans cette table". En claire, il ne lit pas toute la table.

    Je pense que ton problème provient d'une mauvaise interprétation de EXPLAIN.
    Regarde ici : http://dev.mysql.com/doc/refman/5.0/fr/explain.html
    Beaucoup d'information sur ce que EXPLAIN retourne.

    J'espère que ce n'était pas trop galère à me lire :)
     
  10. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    salut

    tout d'abord merci :wink:
    par contre tu as faite une erreur importante dans ton test tu as oublié un élément essentiel dans la requete le order by :

    Code:
    select c.pseudo,c.date_ajout, c.commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.date_ajout DESC LIMIT 0, 5
    j'ai également essayé avec cette requete :

    Code:
    select c.id_commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.date_ajout DESC LIMIT 0, 5
    ici je prend bien un index couvrant (en_ligne, date_ajout, id_commentaire), effectivement on peut voir qu'il utilise l'index adéquat (on a un beau use where, use index) mais pas comme il le faudrait.
    Comme tu peux le constater tu as un rows = 49900 (ce qui est tout simplement énorme car il ballait toute la table) alors que si il utilisait l'index convenablement il devrait y avoir un rows = 5
    Ce problème implique qu'à la première exécution de la requête (avant la mise en cache) tu as une requête assez longue a s’exécuter :wink:

    C'est un peu de ma faute j'avais oublié de préciser que j'avais déjà réussi a chopper le use index en ajoutant id_commentaire dans mon index multiple. Mais ca change pas grand chose étant donné que j'ai toujours ce rows = 50 000 ce qui n’est pas bon au niveau perf

    ps : non aucun soucis pour te lire :wink:
     
  11. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    j'ai peut etre une piste, celle de la cardinalité de l'index multiple .

    l'index multiple utilisé est (en_ligne, date_ajout, id_commentaire)
    le soucis c’est qu'il commence par le champ en_ligne (champs qui a la plus faible cardinalité (2) )

    résultat malgré qu'en fin de compte cette index est une cardinalité global importante, l'optimiseur semble se baser sur le 2 de en_ligne, d'ou le balayage de toute la table malgré le limit 0,5

    j'ai modifié l'index par celui-ci (date_ajout, en_ligne, id_commentaire) et la ça semble mieux, au premier abord :

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
    1 	SIMPLE 	c 	index 	NULL	date_ajout 	10 	NULL	5 	Using where; Using index
    mais si on y regarde de plus près (et la c'est peut être en effet lié a des soucis d'interprétation du explain), j'ai encore un type = index, c’est la valeur la plus mauvaise juste après le "ALL" :?
    ref serait un minimum je pense.

    Par contre dans le cas précédent celui ou j'utilise l'index (en_ligne, date_ajout, id_commentaire) j'ai bien type = ref , le type est donc mieux de ce côté là

    d'ailleurs si je laisse le choix a l'optimiseur entre ces 2 index :

    A (en_ligne, date_ajout, id_commentaire)
    B (date_ajout, en_ligne, id_commentaire)

    l'optimiseur choisira A. :roll:

    Donc pour le moment je ne trouve toujours pas satisfaction pour cette requête qui me semble pourtant très simples et qui ne devrait pas mettre plus de 0,2s (à la première exécution) avec un limit 0,5, ça devrait être quasi instantané. :?
     
  12. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    707
    J'aime reçus:
    0
    Comme je t'ai indiqué dans mon précédant poste, la valeur ROWS n'est pas à prendre au pied de la lettre (ORDER By et LIMIT ou pas).

    Puisque tu n'es pas convaincu, je vais te redonner 2 exemples.

    Le premier, il concerne ton temps d'exécution de ta requête. Dans quel cadre tu le mesures ? Suite à un redémarrage de MySQL ?
    Dans ce cas, c'est normal que le temps soit légèrement plus élevé. Chez moi aussi. Mais ça n'a rien à voir avec ta requête.
    C'est sans doute (je ne suis pas expert non plus) du à l'initialisation du cache interne à MySQL (lecture des fichiers index, analyse, etc.).
    Pour exemple, je prend cette requête:
    Code:
    select c.pseudo,c.date_ajout, c.commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.date_ajout DESC LIMIT 0, 5;
    Le temps de la première exécution est de 0.05s après un redémarrage de MySQL. Ensuite, ça fait du 0.01/0.00.
    MAIS, si tu fais un COUNT avant et ensuite la requête (après redémarrage MySQL) :
    Code:
    select COUNT(*) FROM t_commentaires AS c;
    select c.pseudo,c.date_ajout, c.commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.date_ajout DESC LIMIT 0, 5;
    
    Le COUNT prend 0.05s alors que la seconde prend 0.01. En clair, la fameuse initialisation (que je suppose) s'est fait au moment du count (les count suivant prennent 0.00s).
    D'où l'importance d'avoir une cache bien configuré.

    Deuxième chose, je reprend ta requête et cette fois-ci au lieu de regarder le temps en console, on va détourner un truc sympa de MySQL, c'est le log des requêtes longues. Tu ouvres ton fichier my.cnf et tu décommentes log_slow_queries et tu met 0 à long_query_time (à faire en local biensure, sinon je te raconte pas les logs…).
    Maintenant, toutes tes requêtes sont loguées (tu as même log-queries-not-using-indexes pour trouver les requêtes n'utilisant pas d'index).
    Après, tu redémarres MySQL et tu lances ta requête. Résultat:
    Code:
    # User@Host: test[test] @  [192.168.1.5]
    # Query_time: 0.002816  Lock_time: 0.000610 Rows_sent: 5  Rows_examined: 5
    SET timestamp=1406456923;
    select c.pseudo,c.date_ajout, c.commentaire FROM t_commentaires AS c WHERE c.en_ligne = 1 ORDER BY c.date_ajout DESC LIMIT 0, 5;
    
    Regardes le Query_time : 0.002816. on est loin des 0.05s au moment de l'exécution dans la console.
    Autre info très intéressante: Rows_examined: 5
    Comme ici, c'est l'analyse de l'exécution de la requête, tu as la vrai info au niveau du nombre de ligne traitée.

    Regardes cette article, notamment cette partie :
    Utilises explain pour configurer correctement tes indexes, mais une fois que MySQL t'indique qu'il utilise le bon index et que le type n'est pas en ALL, alors pour le reste, fait confiance à MySQL.
     
  13. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    707
    J'aime reçus:
    0
    Un modo pourrait me corriger "cette article" en "cet article" svp (on peut pas le faire).
    Il y en a surement d'autre, mais là c'est en gros bleu, ça pique les yeux :/

    Désolé pour cette erreur.
     
  14. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    merci milles fois pour ton aide et ces infos qui me seront fortes utiles pour l'analyse de mes requêtes et leur performance.

    J'avais en effet pas bien compris l'explication pour rows dans ton post plus haut, et c’est maintenant plus clair :wink:
    donc à première vu cette requête et mes index utilisés sont bons et je ne dois aps m'inquieter sur les 1ers temps d’exécutions. :)
    Pas forcément évidemment de bien interpréter le explain.

    Pour me rassurer j'ai regardé les logs slow_query avec un site wordpress en local comportant seulement 5 articles de tests, et j'ai effectivement vu qu'à la 1ere exécution pour des requêtes de bases il mettait parfois plus de 0,05 voir 0,07s et qu'en suite ça retombait à 0,001s

    c’est quand même flippant ces 1ers temps d’exécution, ça nous fait douter de la performance de nos requêtes :)

    Ce matin j'ai même eu droit à 1,7s sur cette requête, en 1ere exécution :

    Code:
    select c.pseudo, m.id_membre, c.date_ajout, c.commentaire, b.titre, b.permalien, a.id_article, m.avatar FROM t_commentaires AS c INNER JOIN t_article AS a ON (a.id_article=c.id_article) LEFT JOIN t_membres AS m ON (m.id_membre=c.id_membre) WHERE c.id_commentaire IN (105256,95256,85256,75256,65256) ORDER BY c.date_ajout DESC 
    avec un explain qui semble bon :

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
    1 	SIMPLE 	c 	range 	PRIMARY,idx_article_date 	PRIMARY 	4 	NULL	5 	Using index condition; Using filesort
    1 	SIMPLE 	m 	eq_ref 	PRIMARY 	PRIMARY 	4 	c.id_membre 	1 	NULL
    1 	SIMPLE 	b 	eq_ref 	PRIMARY 	PRIMARY 	4 	c.id_article 	1 	NULL
    j'imagine que dans ce cas (a cause du IN) on ne peut pas éviter le 'Using index condition; Using filesort'
    par contre je ne sais pas si le extra à NULL est normal (j'imagine que oui) pour m et b alors que j'ai bien les index sur les clés primaires (logique) et les clés étrangères (celles utilisées pour les jointures).

    Si tu pouvais jeter un œil sur ce topic :mrgreen: :

    https://www.webrankinfo.com/forum/t/optimisation-requetes-jointures-index.174080/

    Dans le cas de ce topic j'ai affaire à d'horribles 'use temporary, use filesort" , qui semblent a priori inévitables. Ce cas semble bien plus complexe. :wink:
     
Chargement...
Similar Threads - Besoin aide concernant Forum Date
Besoin d'aide concernant modification sur mon blog dans la barre URL. Développement d'un site Web ou d'une appli mobile 25 Juillet 2018
Besoin d'aide pour comprendre mon Analyse Dareboost Débuter en référencement 24 Août 2019
Besoin d'aide pour configurer mon htaccess URL Rewriting et .htaccess 24 Octobre 2018
AJAX BESOIN D'AIDE Développement d'un site Web ou d'une appli mobile 2 Juin 2018
Besoin d'aide pour le référencement Débuter en référencement 25 Mai 2018
Une femme qui fait 2D animation sur Youtube et qui a besoin d'aide pour referencement Débuter en référencement 21 Janvier 2018
Besoin d'aide pour internet Débuter en référencement 2 Décembre 2017
Besoin d'aide pour bien positionner mon blog ! Débuter en référencement 9 Septembre 2017
Besoin d'aide pour Google Search Console Référencement Google 16 Juin 2017
Besoin d'aide niveau juridique Droit du web (juridique, fiscalité...) 28 Février 2017
  1. Ce site utilise des cookies. En continuant à utiliser ce site, vous acceptez l'utilisation des cookies.
    Rejeter la notice