PHP #3: MySQL : Introduction à l'optimisation
SQL 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.
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.
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).
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.
- 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.
- 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.
- DECIMAL
- Largeur fixe : CHAR (255 caractères max)
- Largeur variable : VARCHAR (65535 caractères max)
- Textes longs : TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT (jusqu'a 4Go)
- Largeur fixe : BINARY
- Largeur variable : VARBINARY
- Binaires longs : TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
- ENUM type énumération, 65535 valeurs distinctes max
- SET type ensemble, 64 valeurs distinctes max
- 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.
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 :
Nous obtenons le résultat suivant :
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 :
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.
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.
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
...
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
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.
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.
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.
Comme son nom l'indique, il impose l'unicité.
Pas de contraintes. Il ne sert qu'à augmenter la vitesse de recherche.
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.
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 :
Ainsi que ces données :
La requête suivante permet de calculer le moment où la réduction de l'index n'est plus pertinente :
Le résultat ainsi obtenu figure ci-dessous :
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.
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
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é.
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 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 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.
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.
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...
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.
NB: Je rend grâce un vibrant hômmage au propriétairedu site https://apprendre-php.com/tutoriels.html, un site qui ma permis moi même d'apprendre les bases de PHP et le PHP Objet. Aujourd'hui je parviens à former pas mal d'étudiants raison pour laquelle je me dis pas meilleur que se site pour faire des tutos.