Accueil > Développement > Bases de données > Optimisation : MySQL

Optimisation : MySQL

Nous continuons cette série d’articles sur l’optimisation avec la base de données MySQL.

vendredi 15 juin 2007, par Prélude

Le but ici est de comparer les types de tables MyISAM et InnoDB. Pour cela, nous avons pris une base de test que nous avons remplie au fur à et mesure avec du texte aléatoire, jusqu’à obtenir 1 000 000 d’enregistrements. C’est à partir de cette base que nous avons effectué les tests.

Pour commencer, voici donc le temps nécessaire pour remplir une base :

TableEnregistrementsTemps
MyIsam100 00072sec
InnoDB100 000>2min
MyISAM20 0001.43 sec
InnoDB20 000105 sec

C’est indiscutable, InnoDB semble plus lent pour l’insertion des données. Et pourquoi donc ?! Il faut savoir que InnoDB est de type transactionnelle alors que MyISAM est (on s’en doute un peu) non-transactionnelle. Bon... Et ?! Ca veux dire quoi au juste ces termes barbares ?!

Les tables non-transactionnelles sont plus rapide, utilisent moins d’espace disque et moins de mémoire. Alors pourquoi choisir InnoDB ?! Car les tables transactionnelles sont plus sûr (en cas de crash de MySQL, vous pouvez tout de même récupérer vos données) et pour d’autres petits détails qui sortent un peu de cet article.

Les tables contiennent 4 champs :

ChampType
Idbigint
Nomvarchar(30), null autorisé
Prenomvarchar(30) , null autorisé
AgeTinyint

Alors, nous voilà avec une table de 1 000 000 enregistrements (lignes) et nous allons pouvoir commencer les premiers tests. Juste pour info, voilà la place occupée par les différentes tables utilisées pour les tests (estimation de PhpMyAdmin, après optimisation) :

table index sur NomTaille pour VACHAR(30 Taille pour CHAR(30)
MyISAMnon56.4Mo194.9Mo
InnoDBnon60.6Mo93.6Mo
MyISAMoui61.8Mo202Mo
InnoDBoui104.2Mo168.4Mo

On constate que InnoDB ne prend pas forcément plus de place que MyISAM. Mais cela tient surtout aux choix de stockage des données si vous choisissez plutôt CHAR que VARCHAR par exemple. Il faut bien garder à l’esprit que CHAR va allouer la taille indiqué (ici 30) quelque soit la quantité d’information stockée alors que VARCHAR va allouer la taille de l’information stockée (plus un reliquat pour indiqué la fin).
CHAR peut s’avérer plus rapide sur des tailles importantes (à partir de 100), mais n’a pratiquement aucune incidence en dessous.

Dans tous les tests que nous avons fait, le terme recherché, que ce soit un chiffre ou des lettres, est changé à chaque itération afin de ne pas récupérer un résultat dans un cache. Car, en effet, si MySQL reçoit une requête déjà effectuée, cette seconde requête (et les suivantes) est nettement plus rapide. La recherche est lancée plusieurs fois et le temps affiché est une moyenne. Les tests ont été relancés plusieurs fois à différents moments pour être certain que rien ne pouvait interférer sur les résultats.

Les tests


SELECT * FROM MaTable WHERE Age>45

TableRésultat
MyISAM1.370
InnoDB2.376

InnoDB est plus lent sur une requête simple.


SELECT * FROM MaTable WHERE Nom=’dummy’

Table Résultat
MyISAM0.504
InnoDB1.382

SELECT * FROM MaTable WHERE Nom<>’dummy’

Table Résultat
MyISAM1.996
InnoDB3.163

La recherche par différence est plus longue que l’égalité.


SELECT * FROM MaTable WHERE Nom LIKE ’xyz%’

Table Résultat
MyISAM0.512
InnoDB1.282

Aussi étonnant que cela puisse sembler, ici le temps est pratiquement le même pour une égalité pour un LIKE. Notons également que le temps reste le même si les données sont du type CHAR(30) ou VARCHAR(30).


SELECT * FROM MaTable WHERE Nom LIKE ’xyz%’ ORDER BY Age

Table Résultat
MyISAM0.484
InnoDB1.409

Nous rajoutons un tri à la recherche. Du coup, InnoDB augmente un peu son temps alors que MyISAM le réduit.


Attention, maintenant, nous allons faire exactement la même requête, mais en ajoutant un index sur le champs Nom. Et là, vous allez voir que ce n’est pas négligeable d’y penser :

SELECT * FROM MaTable WHERE Nom LIKE ’xyz%’ ORDER BY Age

Table Résultat
MyISAM0.0005
InnoDB0.002

Conclusion

Pas simple de conclure avec ces chiffres. Disons que si vous avez des informations importantes à stocker et que vous avez beaucoup d’utilisateurs qui vont faire des écritures simultanées dans votre base, il vaut peut-être mieux choisir InnoDB.
Si vous faites beaucoup de lecture de données et peu d’écriture, le choix doit se porter sur MyISAM.

Mettre un index sur un champs permet d’augmenter considérablement la vitesse lors d’une requête, mais augmente aussi la taille de la base. Faites donc attention surtout si vous êtes sur un serveur mutualisé.

Un dernier mot : il existe d’autres type de tables pour MySQL. Notamment les tables MEMORY. Mais ce type ne peut-être utilisé pour des tables de cette taille sans avoir une quantité de mémoire très importante. Nous effectuerons très certainement des tests dans un prochain article.

Messages

  • Bon article mais on aurait aimé avoir le détail de la configuration utilisé : myisam et innodb utilisent des zones de mémoire distinctes. Et il aurait été utile faire les tests avec et sans le cache de requêtes : il a un énorme impact pour des select.
    Un site incontournable sur le sujet (en anglais) : http://www.mysqlperformanceblog.com/

    • Détailler la configuration n’aurait pas été simple. Mais voilà en gros : PHP5.2.2 et MySQL 5.0.37, le tout sur un Intel Core 2 CPU 1.87Ghz et 2Go de ram.
      Ces détails un peu techniques et carrément rébarbatifs passé, ce qui est interressant à noté est que l’installation reste de base : installation simple, sans modification de l’envirronement. Les temps annoncés sont surtout à prendre en compte comme rapport entre eux évidement.