Besoin d'aide concernant les indexes - Mysql

[noren]

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

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 :

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

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.

 

[noren]

Si mon problème et mon post n’est pas clair, n’hésitez pas à le dire hein :mrgreen:

 

[baud74]

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.

 

[noren]

Rooo tu sous estime les compétences des Wrinautes :wink:
J'en ai pourtant vu quelques un de très très compétents

 

[chnain35]

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

 

[noren]

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

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.

pas d'accord avec toi : :wink:

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

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).

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

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.

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

 

[Blount]

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.

 

[noren]

Blount je t'ai envoyé un message :wink:

 

[Blount]

Je pense avoir trouvé quelques réponses.

Ton "problème" pour le "Using index" semble tout à fait normal.
D'après la doc :

Les informations de la colonne sont lues de la table, en utilisant uniquement les informations contenues dans l'index, sans avoir à faire d'autres lectures. Cela peut arriver lorsque toutes les colonnes utilisées dans une table font partie de l'index.

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 :

EXPLAIN SELECT c.pseudo, c.date_ajout, c.commentaire, c.en_ligne FROM t_commentaires AS c WHERE c.en_ligne = 1

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 :

EXPLAIN SELECT c.en_ligne FROM t_commentaires AS c WHERE c.en_ligne = 1

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

La colonne possible_keys indique quels index MySQL va pouvoir utiliser pour trouver les lignes dans cette table. Notez que cette colonne est totalement dépendante de l'ordre des tables. Cela signifie que certaines clés de la colonne possible_keys pourraient ne pas être utilisées dans d'autres cas d'ordre de tables.

Si cette colonne est vide, il n'y a pas d'index pertinent. Dans ce cas, vous pourrez améliorer les performances en examinant votre clause WHERE pour voir si des colonnes sont susceptibles d'être indexée. Si c'est le cas, créez un index approprié, et examinez le résultat avec la commande EXPLAIN. See Section 13.2.2, « Syntaxe de ALTER TABLE ».

Pour connaître tous les index d'une table, utilisez le code SHOW INDEX FROM nom_de_table.

key

La colonne key indique l'index que MySQL va décider d'utiliser. Si la clé vaut NULL, aucun index n'a été choisi. Pour forcer MySQL à utiliser un index listé dans la colonne possible_keys, utilisez USE KEY/IGNORE KEY dans votre requête. See Section 13.1.7, « Syntaxe de SELECT ».

Pour les tables MyISAM et BDB, la commande ANALYZE TABLE va aider l'optimiseur à choisir les meilleurs index. Pour les tables MyISAM, myisamchk --analyze fera la même chose. Voyez Section 13.5.2.1, « Syntaxe de ANALYZE TABLE » et Section 5.7.3, « Utilisation de myisamchk pour la maintenance des tables et leur recouvrement ».

"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 :

La colonne rows indique le nombre de ligne que MySQL estime devoir examiner pour exécuter la requête.

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 :

EXPLAIN SELECT c.id_commentaire FROM t_commentaires AS c WHERE c.id_commentaire = 105257

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 :

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:

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

 

[noren]

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 :

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 :

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:

 

[noren]

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 :

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.

index

C'est la même chose que ALL, hormis le fait que seul l'arbre d'index est étudié. C'est généralement plus rapide que ALL, car le fichier d'index est plus petit que le fichier de données.

Cette méthode peut être utilisée lorsque la requête utilise une colonne qui fait partie d'un index.

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é. :?

 

[Blount]

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:

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) :

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:

# 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 :

LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number.

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.

 

[Blount]

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.

 

[noren]

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 :

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 :

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: