MySQL : Introduction à l'optimisation

• Par Emacs
• 8 commentaires
• 29823 lectures
• RSS -  Atom

MySQL est un SGBDR libre, performant et surtout simple à utiliser et à administrer. Il est toujours étonnant de voir autant de développeurs et de DBA sans expériences SQL, chargés de l'administration des serveurs MySQL. Sur de petites applications, peu sollicitées, cela ne porte pas toujours à conséquences, mais petite application deviendra grande et de mauvais choix faits au départ imposent presque à chaque fois une remise en question profonde du schéma et la réécriture des requêtes.

A. Présentation de l’optimisation

Il y a quatre axes majeurs pour optimiser les performances d’une base de données :

• La structure de la base de données.
• Les requêtes SQL.
• La configuration du serveur MySQL.
• Le serveur (ordinateur) qui héberge la base de données MySQL.

L’optimisation des requêtes et de la structure offre souvent le plus grand gain, cependant optimiser le serveur peut augmenter les performances générales.

B. Optimiser le schéma

1. Amélioration de la structure logique : normalisation

La normalisation permet d'organiser les données afin de limiter les redondances. Les tables sont souvent divisées en plusieurs tables plus petites reliées entre elles par des relations, clés primaires et clés étrangères. L'objectif est d'isoler les données afin que l'ajout, la modification ou l'effacement d'un enregistrement puisse se faire sur une seule table, et se propager au reste de la base par le biais des relations.

Concevoir et normaliser sa base de données correctement, c'est utiliser le modèle 3NF (3rd Normal Form).

2. Structure physique de la base de données

a) Choisir les bons types de champs

MySQL supporte un grand nombre de types de champs. Ils peuvent être rassemblés en trois catégories : les types numériques, temporels et chaîne de caractères.

Les numériques entiers

TINYINT

Intervalle de validité : de -128 à 127.

Non signé: de 0 à 255.

SMALLINT

Intervalle de validité : de -32768 à 32767.

Non signé: de 0 à 65535.

MEDIUMINT

Intervalle de validité : de -8388608 à 8388607.

Non signé: de 0 à 16777215.

INTEGER

Intervalle de validité : de -2147483648 à 2147483647.

Non signé: de 0 à 4294967295.

BIGINT

Intervalle de validité : de -9223372036854775808 à 9223372036854775807.

Non signé: de 0 à 18446744073709551615.

Les numériques réels à virgule flottante

FLOAT

Intervalles de validité : de -3.402823466E+38 à -1.175494351E-38

0

1.175494351E-38 à 3.402823466E+38

DOUBLE

Intervalle de validité : de -1.7976931348623157E+308 à -2.2250738585072014E-308

0

2.2250738585072014E-308 à 1.7976931348623157E+308.

Les numériques réels à virgule fixe

• DECIMAL

Les chaînes de caractères

• Largeur fixe : CHAR (255 caractères max)
• Largeur variable : VARCHAR (65535 caractères max)
• Textes longs : TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT (jusqu'a 4Go)

Les chaînes binaires

• Largeur fixe : BINARY
• Largeur variable : VARBINARY
• Binaires longs : TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

Les énumérations et ensembles

• ENUM type énumération, 65535 valeurs distinctes max
• SET type ensemble, 64 valeurs distinctes max

Les types temporels

DATE

YYYY-MM-DD

Intervalle de validité: de 1000-01-01 à 9999-12-31

TIME

HH:MM:SS

Intervalle de validité: de -838:59:59 à 838:59:59

YEAR

YYYY

Intervalle de validité: de 1901 à 2155

DATETIME

YYYY-MM-DD HH:MM:SS

Intervalle de validité: de 1000-01-01 00:00:00 à 9999-12-31 23:59:59

TIMESTAMP

YYYY-MM-DD HH:MM:SS

Intervalle de validité: de 1970-01-01 00:00:01 à 2038

Le choix du type est important, il doit être adapté au contenu du champ.

Par exemple, une quantité sera de type numérique entier non signé, une monnaie sera de type decimal. Alors qu'un numéro de téléphone ou un code postal seront de type char, car on ne fera pas de calcul avec.

b) Choisir la bonne longueur de champ

Comme nous venons de le voir, choisir le bon type est important. Un autre aspect tout aussi important mais trop souvent négligé, est le choix de la longueur du type de données.

• Ai-je besoin d'un varchar(255) pour stocker le code postal ou même le nom de mes clients ?
• Ai-je besoin d'un bigint comme clé primaire auto incrémentée pour la liste des produits vendus sur mon site ?

La logique derrière ces choix, quand il y en a une, est "qui peut le plus peut le moins". Le problème est que "le plus" est très coûteux et cela a un impact sur les performances du SGBDR. Le maître mot est d'avoir les données les plus compactes possibles, pour en avoir le plus possible en mémoire et donc limiter les accès disque.

Il faut donc anticiper le peuplement de chaque table et choisir le type avec la longueur la plus adaptée au contenu de la colonne.

MySQL possède une commande pour vous faciliter cette analyse : PROCEDURE ANALYSE.

Cette commande renvoie une analyse ainsi que le champ optimal de toutes les colonnes choisies dans une requête SELECT.

Prenons l'exemple de requête suivant :

Requête d'analyse de l'état de la base de données
SELECT id, nom, age FROM utilisateur PROCEDURE ANALYSE()

Nous obtenons le résultat suivant :

Résultat de la requête d'analyse
*************************** 1. row ***************************
 
  Field_name: anaska.utilisateur.id
  Min_value: 1
  Max_value: 4079
  Min_length: 1
  Max_length: 4
  Empties_or_zeros: 0
  Nulls: 0
  Avg_value_or_avg_length: 2040.0000
  Std: 1177.5058
  Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
 
*************************** 2. row ***************************
 
  Field_name: anaska.utilisateur.nom
  Min_value: daz
  Max_value: mysql4ever
  Min_length: 3
  Max_length: 10
  Empties_or_zeros: 0
  Nulls: 0
  Avg_value_or_avg_length: 8.5295
  Std: NULL
  Optimal_fieldtype: VARCHAR(10) NOT NULL
 
*************************** 3. row ***************************
 
  Field_name: anaska.utilisateur.age
  Min_value: 18
  Max_value: 33
  Min_length: 2
  Max_length: 2
  Empties_or_zeros: 0
  Nulls: 0
  Avg_value_or_avg_length: 26.1667
  Std: 4.5613
  Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL

c) Évitez l’utilisation des champs NULL spécialement pour les index

La valeur NULL est une valeur spéciale. En fait c'est une valeur qui représente l'absence de valeurs. Ce n'est ni 0 ni ''. Regardons le comportement de la valeur NULL :

Comportement de la valeur NULL
+--------------------------+
| if(NULL=0,'vrai','faux') |
+--------------------------+
| faux                     |
+--------------------------+
1 row IN SET (0.06 sec)
 
mysql> SELECT if(NULL='','vrai','faux');
+---------------------------+
| if(NULL='','vrai','faux') |
+---------------------------+
| faux                      |
+---------------------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT if(NULL=NULL,'vrai','faux');
+-----------------------------+
| if(NULL=NULL,'vrai','faux') |
+-----------------------------+
| faux                        |
+-----------------------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT if(NULL IS NULL,'vrai','faux');
+--------------------------------+
| if(NULL IS NULL,'vrai','faux') |
+--------------------------------+
| vrai                           |
+--------------------------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT if(NULL <=> NULL,'vrai','faux');
+---------------------------------+
| if(NULL <=> NULL,'vrai','faux') |
+---------------------------------+
| vrai                            |
+---------------------------------+
1 row IN SET (0.00 sec)

NULL est, comme vous pouvez le constater, une valeur spéciale, qui implique un traitement spécial ce qui induit un coût supplémentaire. Si vous n'avez pas besoins de cette valeur, préciser la clause NOT NULL pour chaque colonne.

d) Accordez une attention particulière aux clauses JOIN

Lors d'un jointure, les critères de jointure doivent être du même type, sinon MySQL est obligé de faire une conversion avant d'effectuer la jointure.

Requête avec jointure
SELECT col1 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.pk_t1=t2.fk_t2;

Avec pk_t1 et fk_t2 de même type et non :

pk_t1 int et fk_t2 char(5)
pk_t1 int et fk_t2 bigint
...

e) Choisir son moteur de stockage

MySQL a la particularité de posséder plusieurs moteurs de stockage. Chaque moteur a ses avantages et ses fonctionnalités. Le choix du moteur de stockage optimal, par rapport à ses besoins, fait partie intégrante du processus d'optimisation. Voici une rapide description des moteurs les plus utilisés :

MyISAM

Moteur par défaut

Très rapide pour les applications exigeantes en lecture

Prend en charge les index FULLTEXT

Prend en charge les types de données spatiales conformément à la norme GIS

Permet la compression

InnoDB

Moteur de stockage transactionnel (conformité intégrale à la norme ACID)

Prend en charge les quatre niveaux d’isolation

Prend en charge les clés étrangères et l’intégrité référentielle, y compris les suppressions et mises à jour en cascade

MEMORY

Table entièrement en mémoire

Ne crée aucun fichier de données ou d’index sur le disque

Extrêmement rapide en lecture et en écriture

Utile pour la création de tables temporaires

ARCHIVE

Stocke de grandes quantités de données

NDBCluster

Solution de haute disponibilité

S’exécute dans la mémoire du serveur

Prend en charge les transactions et la réplication synchrone

Les arrêts inattendus de noeuds individuels n’arrêtent pas le cluster

Synchronisation automatique du noeud de données au redémarrage

C. Optimiser les requêtes

Les requêtes sql sont très souvent le lieu où les progrès en matière de performances sont les plus visibles voire spectaculaires. L'optimisation des requêtes passe essentiellement par la pose d'index pertinents et dans une moindre mesure par leur réécriture.

Les index

Le but d'un index est d'accélérer la recherche de l'information en privilégiant les accès au fichier d'index (optimisé car les données sont triées) au détriment du fichier de données. Mais l'optimisation des requêtes ne passe pas par l'ajout massif d'index. Les index se doivent d'être pertinents car ils peuvent ralentir les requêtes d'écritures.

La clé primaire

Cet index impose deux contraintes : l'unicité et l'interdiction d'avoir des valeurs nulles. Il ne peut donc y en avoir qu'une seule par table.

L'index unique

Comme son nom l'indique, il impose l'unicité.

L'index (simple)

Pas de contraintes. Il ne sert qu'à augmenter la vitesse de recherche.

L'index fulltext

Permet de chercher des mots dans du texte. Il ne fonctionne qu'avec les tables en MyISAM, pour les champs de types CHAR, VARCHAR et TEXT.

Optimiser les index

Il est possible de réduire la taille des index des champs de type CHAR et VARCHAR.

Lorsqu'un index est créé, c'est toute la longueur de la chaîne de caractères qui est indexée. Mais ce n'est pas toujours utile, si les x premiers caractères sont suffisamment discriminants, il est intéressant de n'indexer qu'eux. Au final j'ai un index plus petit, avec comme incidence majeure, une diminution des accès disque et donc des temps de réponse plus rapides.

Prenons la table suivante comme exemple :

Code SQL de création de la table "anaska"
CREATE TABLE anaska 
( 
  prenom CHAR(30) NOT NULL DEFAULT '', 
  PRIMARY KEY (`prenom`)
);

Ainsi que ces données :

Enregistrement d'informations dans la table
INSERT INTO anaska (prenom) VALUES 
     ('Olivier'), 
    ('Cyril'), 
    ('Romain'), 
    ('Fabien'), 
    ('Sarah'), 
    ('Pierre'), 
    ('Omar'), 
    ('Loic'), 
    ('Guillaume'), 
    ('Sandrine'), 
    ('Laurence');

La requête suivante permet de calculer le moment où la réduction de l'index n'est plus pertinente :

Calcul de la pertinence de la réduction de l'index
SELECT 
  COUNT(DISTINCT LEFT(prenom,3)) AS 'Valeurs prefix distinctes', 
  COUNT(*) - COUNT(DISTINCT LEFT(prenom,3)) AS 'Valeurs prefix dupliquées' 
FROM anaska;

Le résultat ainsi obtenu figure ci-dessous :

Résultat de l'exécution de la requête
+---------------------------+---------------------------+
| Valeurs prefix distinctes | Valeurs prefix dupliquées |
+---------------------------+---------------------------+
|                        10 |                        0  |
+---------------------------+---------------------------+

Dans notre exemple, si les données ne changent pas, il est possible de réduire la taille de l'index à 3 caractères. Ce qui nous donnera un index 10 fois plus petit que l'index par défaut. Voyons comment réduire la taille d'un index.

Réduction de la taille de l'index
mysql> ALTER TABLE anaska DROP PRIMARY KEY; 
Query OK, 10 rows affected (0.30 sec) Records: 10 Duplicates: 0 WARNINGS: 0 
mysql> ALTER TABLE anaska ADD PRIMARY KEY (prenom(3)); 
Query OK, 10 rows affected (0.23 sec) Records: 10 Duplicates: 0 WARNINGS: 0

Attention toutefois à ne pas trop réduire la taille, dans notre exemple si on réduit encore, une erreur sera générée par MySQL

Erreur générée par MySQL en cas de réduction trop importante de l'index
mysql> ALTER TABLE anaska ADD PRIMARY KEY (prenom(2)); 
ERROR 1062 (23000): Duplicate entry 'Sa' for key 1

De plus on ne peut pas insérer la valeur Romuald car maintenant seules les 3 premiers caractères sont pris en compte pour tester l'unicité.

Tentative d'import d'une valeur dont l'index est identique à un autre dans la table
mysql> INSERT INTO anaska (prenom) VALUES ('Romuald'); 
ERROR 1062 (23000): Duplicate entry 'Rom' for key 1

Enfin, dans le cas d'un index non unique, aucune erreur ne sera générée, mais l'index pourrait ne plus être pertinent, si le nombre de doublons est trop important.

La commande EXPLAIN

La commande EXPLAIN permet de savoir comment l'optimiseur de MySQL résout une requête SELECT. Grâce à cette commande, il est possible de savoir si les index sont pertinents où s'il faut en créer de nouveaux, s'il est nécessaire de réécrire la requête ou non...

Explain est particulièrement utile pour optimiser des jointures.

Le cache de requêtes

Le cache de requêtes de MySQL permet de sauvegarder une requête SELECT et son résultat en mémoire. A la prochaine requête, strictement identique, le serveur retourne le résultat à partir du cache plutôt que d'analyser puis exécuter la requête à nouveau.

Le cache de requêtes est extrêmement utile dans un environnement où les tables ne changent pas souvent.

Il n'est pas opérationnel par défaut. Pour l'activer, deux conditions :

• query_cache_size différent de 0 (en octets)
• query_cache_type à On ou DEMAND

Analysons les paramètres du cache des requêtes.

Affichage de l'état des variables de configuration du cache de requêtes
mysql> SHOW VARIABLES LIKE 'query_cache_%';
 
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8192    |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows IN SET (0.00 sec)

D. Optimiser le Serveur MySQL

L'optimisation du serveur MySQL est une phase assez délicate dans le sens où elle peut nécessiter beaucoup d'efforts, pour des résultats pas toujours spectaculaires. Il est fortement conseillé de laisser le soin à un expert de manipuler les nombreux paramètres du serveur MySQL, sous peine de passer des heures à essayer de remettre en marche un serveur qui finalement ne fonctionnait pas si mal que ça avant.

L'idée maîtressee est de faire en sorte que MySQL fasse au maximum ses traitements en mémoire. Le but étant encore une fois, de réduire les accès disque.

La première étape consiste à collecter l'information, tout simplement afin de savoir quoi optimiser. les outils utiles dans cette mission seront les outils de diagnostique liés à l'OS, les logs de MySQL ainsi que les variables d'état.

L'optimisation dépend également du type de moteur de stockage utilisé.

Pour les tables MyISAM, le premier paramètre à regarder est le key_buffer_size.
Un moyen simple de savoir s'il est bien paramétré est de faire le rapport key_reads / key_read_request, le résultat doit être inférieur à 0.03.

Dans le cas contraire essayez d'augment votre key_buffer_size mais tout en ne dépassant pas plus de 25% de la RAM.

Pour InnoDB, le paramètre "équivalent" est innodb_buffer_pool_size. Là, il n'y a pas de calcul magique, mais la limite à ne pas dépasser est 80% de la RAM, pour un serveur dédié à MySQL avec toutes les tables en InnoDB.

Il y a bien entendu pleins d'autres choses à voir, mais qui ne rentrent pas dans le cadre de cette introduction à l'optimisation.

E. Optimiser le serveur

L'optimisation coté hardware est finalement la plus simple, à condition d'en avoir les moyens. L'idée directrice est plus de RAM, plus de processeurs, plus de disques...

Conclusion

Voici une petite vue d'ensemble des principaux points, à considérer en priorité, dans le cadre d'une optimisation de votre SGBDR préféré. Cet article étant une introduction à l'optimisation, tous les points n'ont bien entendu pas été abordés. Le but est avant tout de sensibiliser les développeurs et les administrateurs de bases de données aux bonnes pratiques MySQL.

Un dernier conseil, en cas de problèmes de performances, n'hésitez pas à contacter des experts.

Auteurs

Olivier DASINI est formateur officiel et certifié MySQL. Responsable du pôle SGBDR chez Anaska, il est également l'auteur de nombreux articles sur MySQL.

Cyril PIERRE de GEYER est Directeur Technique adjoint chez Anaska (www.anaska.com). Anaska est un organisme de formation spécialisé dans l'Open Source. Militant pour la philosophie OpenSource Cyril PIERRE de GEYER est également l'auteur du livre « PHP5 Avancé », membre du club mandriva, de l'ASS2L et du club Sénat.