Le coût des index inutiles

16 mai 2011 par stephane

On vous a tout le temps dit et redit que les index étaient indispensables pour les performances en lecture d’une base de données et vous avez eu droit à des exemples spectaculaires où les temps de réponses sont divisés par 10 000 ou par 1 000 000 rien qu’en ajoutant un index judicieux. Bien. On vous a également prévenu que chaque index posé dégrade les écritures et qu’il ne faut donc pas en abuser. Mais vous a-t-on déjà montré quel type de dégradation en écriture on peut attendre quand on ajoute un index ? C’est ce dont nous allons parler dans cet article.

Prenons une table InnoDB toute simple :


CREATE TABLE (
id int(11) NOT NULL AUTO_INCREMENT,
col_a varchar(30) NOT NULL DEFAULT '',
col_b varchar(30) NOT NULL DEFAULT '',
col_c varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB;

et testons l’influence des index sur le temps de chargement d’un million de lignes au moyen d’un LOAD DATA INFILE.

Cas 1, données et index tiennent en mémoire (innodb_buffer_pool suffisamment grand) :
Seulement la clé primaire : 15s
Clé primaire + index sur col_a : 28,5s
Clé primaire + index sur col_a + index sur col_b : 44,5s
Clé primaire + index sur (col_a,col_b) : 36,3s

Cas 2, données et index ne tiennent plus en mémoire :
Seulement la clé primaire : 22,5s
Clé primaire + index sur col_a : 370s
Clé primaire + index sur col_a + index sur col_b : 1144s
Clé primaire + index sur (col_a,col_b) : 716s

Quelles conclusions pouvons-nous faire de ces tests ? Tout d’abord, les insertions sont nettement plus rapides quand la table tient en mémoire. Ce n’est pas une surprise, tout va toujours plus vite lorsqu’on travaille en mémoire que lorsqu’on travaille sur le disque. Les différences sont cependant spectaculaires, puisque le temps de chargement dans le cas 2 est x1,5 dans le meilleur cas et x25 dans le pire des cas.

On remarque également qu’à chaque index ajouté, le temps de chargement augmente de manière très importante (x3 avec 2 index pour le cas 1 et x50 pour le cas 2), même lorsque la table tient en mémoire.

La conclusion de ce petit test est donc la suivante : les index inutiles dégradent très fortement les performances en écriture, et cela même si les données tiennent intégralement en mémoire. Si vous avez des problèmes de capacité d’écriture sur vos serveurs MySQL, par exemple avec des slaves qui ont du mal à répliquer les requêtes du master sans prendre de retard, une bonne piste consiste à partir à la chasse aux index inutiles. Reste une dernière question : comment déterminer les index inutiles ? C’est loin d’être évident, et nous en reparlerons en détail dans un prochain article.

Mots-clefs : ,

5 commentaires sur “Le coût des index inutiles”

  1. Cédric dit :

    Hello, c’est vrai qu’avec du concret, on se rend mieux compte…
    Pour notre info, quelle était la valeur du paramètre innodb_max_dirty_pages_pct pour ces tests ?
    Les mêmes tests ont-ils été réalisés avec MyISAM ?

  2. Arnaud dit :

    Salut à tous les deux,

    Bonne idée Stéphane cet article, effectivement pas si simple de trouver les index inutiles… et un redundant index l’est-il systématiquement eheh…

    @Cédric : si on se pose la question (intéressante) sur MyISAM, alors quid de l’ordre d’insertion des données dans InnoDB ? on insère dans l’ordre de la PK ?

    A+

  3. H2LSOFT dit :

    C’est vrai avec un test MyISAM serait vraiment pertinent, j’attends la suite avec impatience ;-)

    Bravo

    pS: un article intérressant: http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

  4. stephane dit :

    innodb_max_dirty_pages_pct était à sa valeur par défaut, soit 75% pour la version utilisée (5.1.54 avec InnoDB plugin).

    Avec MyISAM, on observe le même type de dégradation en ajoutant les index.

    C’est vrai que l’ordre d’insertion a de l’importance, je vais refaire quelques tests et poster un complément à cet article.

  5. Cédric dit :

    Merci pour ces réponses Stéphane.
    Nous attendons le résultat de tes tests complémentaires avec impatience.

Laisser une réponse