Editions ENI Livres | Vidéos | e-Formations
Optimisation
Matériel et système d’exploitation
1. Processeur
Les architectures avec plusieurs processeurs ou avec plusieurs cœurs étant les plus courantes, on peut se demander s’il vaut mieux privilégier la vitesse ou le nombre de processeurs.
Dans le passé, MySQL était particulièrement faible sur des machines multi-processeurs. Ces problèmes sont maintenant corrigés et il n’existe plus vraiment de limitation sur le nombre maximal de processeurs que MySQL est capable d’utiliser. Il est cependant important de ne pas négliger la rapidité des processeurs. En effet, il n’est pas possible de paralléliser l’exécution d’une requête sur plusieurs processeurs, rendant souvent la vitesse du processeur déterminante dans la rapidité de la réponse. À titre d’exemple, pour un serveur esclave dans le cadre de la réplication, cette limitation risque d’apparaître rapidement : si l’essentiel de la charge provient des requêtes répliquées, seul le thread de réplication sera actif et il ne pourra occuper qu’un seul processeur. Vous aboutirez dans ce cas à un processeur chargé à 100 % alors que les autres ne seront quasiment pas utilisés (si vous configurez la réplication multithread, vous n’aurez peut-être jamais ce problème).
Retenez que sur un serveur esclave, vous aurez tout intérêt à privilégier la vitesse du processeur sur le nombre de cœurs, de manière à ce que la réplication reste la plus synchrone possible, et que sur un serveur maître, plus de cœurs impliquent une meilleure capacité à traiter des connexions simultanées.
Pourquoi ne pas chercher alors sur les serveurs esclaves à la fois la vitesse et le nombre de processeurs ? Tout simplement parce que vous aurez en général plusieurs serveurs esclaves, ce qui vous permettra de distribuer les requêtes, mais que toutes les requêtes provenant de la réplication devront s’exécuter sur tous les esclaves. La question du coût entre bien sûr également en jeu.
Un dernier point pour les utilisateurs de Linux : sur certaines distributions, la fréquence du processeur est modulée...
Optimisation du schéma
1. Types de données
a. Principes généraux
La recherche du meilleur type de données possible est une tâche souvent négligée pendant la phase de conception du schéma de la base de données car il est à la fois difficile et fastidieux de se demander, pour chaque champ, quelles sont les valeurs minimales et maximales à stocker. Et quand l’application est mise en production, la modification des champs est souvent vue comme une opération risquée, avec des risques de pertes de données, et sans impact majeur sur les performances. Pourtant un choix éclairé des types de données sera toujours bénéfique. Cette section vise à établir quelques règles simples à suivre pour sélectionner de manière efficace de bons types de données.
En premier lieu, gardez à l’esprit que plus le type de données est simple et compact, plus il sera léger et performant. Ainsi un entier est plus simple qu’une chaîne de caractères car les notions de jeux de caractères et de collations n’existent pas pour les entiers. De même, parmi les différents types d’entiers, un TINYINT (un octet par valeur) est plus compact qu’un BIGINT (huit octets par valeur) et donc plus rapide à traiter.
En second lieu, essayez d’éviter au maximum les colonnes NULL, ou ce qui revient au même, déclarez toutes les colonnes NOT NULL, sauf en cas de besoin spécifique. Les colonnes pouvant être NULL demandent un travail supplémentaire qu’il vaut mieux éviter au serveur. Souvent, la valeur NULL utilisée comme valeur par défaut peut être avantageusement remplacée par 0 ou une chaîne vide.
b. Nombres
Les données numériques se classent en deux catégories bien distinctes : les entiers et les nombres réels.
Pour les entiers, le type le plus courant est INT (quatre octets par valeur stockée), mais il existe également les types TINYINT (un octet), SMALLINT (deux octets), MEDIUMINT (trois octets) et BIGINT (huit octets). Le nombre d’octets occupés par valeur stockée donne la fenêtre de valeurs de chacun des types : un octet permettant de représenter 256 valeurs...
Indexation
1. Généralités sur les index
a. Rôle d’un index
Lorsque les tables deviennent très volumineuses, le serveur met de plus en plus de temps à retrouver les données que les clients lui demandent, et cette suractivité se traduit entre autres par des requêtes longues à exécuter. Pour retrouver de bonnes performances, une solution usuelle consiste à ajouter un ou plusieurs index sur la table. Un index est une structure de données liée à une table et dont le rôle est comparable à celui d’un index dans un livre : si vous souhaitez rechercher un mot dans un livre, il est plus rapide de chercher ce mot dans l’index, où vous trouverez directement le numéro de toutes les pages où il apparaît, plutôt que de lire tout le livre du début jusqu’à la fin.
De nombreux types d’index existent, certains sont en plus porteurs de contraintes sur les données de la table, mais dans tous les cas la finalité est la même : retrouver au plus vite une référence sur les données recherchées.
L’utilisation ou non d’un index pour résoudre une requête est décidée par le serveur pendant la phase d’optimisation de la requête : avant d’exécuter une requête, le serveur cherche à déterminer quel va être le moyen le plus rapide de rechercher les résultats en faisant appel à un sous-programme spécialisé appelé optimiseur de requêtes.
b. Clés et index
Certains systèmes de bases de données différencient clairement les clés et les index. Une clé est une contrainte sur des données alors qu’un index est une structure qui a pour but de permettre de rechercher rapidement un ensemble de données. Ainsi, on peut définir dans ces systèmes une contrainte d’unicité ou une contrainte pour imposer à toutes les valeurs d’une colonne d’être supérieures à 10.
Avec MySQL, la notion de contrainte existe aussi, mais d’une manière plus limitée. Et surtout, toutes les contraintes sont implémentées à l’aide d’index. Pour cette raison, dans la suite de ce livre...
La commande EXPLAIN
1. Rôle
Lorsqu’une requête s’exécute lentement, vous aurez besoin d’informations sur ce que fait MySQL de manière interne afin de pouvoir influer sur son comportement, par exemple en ajoutant un index, en modifiant la requête ou encore en changeant un ou plusieurs paramètres au niveau du serveur.
La commande EXPLAIN joue ici un rôle particulièrement important puisque c’est grâce à elle que MySQL vous communique le plan d’exécution de la requête. Qu’est-ce qu’un plan d’exécution ? Il s’agit tout simplement de la stratégie adoptée par le serveur pour déterminer le résultat d’une requête. N’oubliez pas que le SQL est un langage dans lequel vous indiquez les critères de la recherche et/ou de l’action (par exemple : ajouter 1 à l’âge de toutes les personnes nées le 1er mars) mais dans lequel vous n’indiquez pas comment trouver les enregistrements correspondant à votre recherche.
2. Accès aux données
Comme vous allez le voir dans la suite de ce chapitre, EXPLAIN va vous renseigner plus ou moins directement sur la manière dont le serveur va accéder aux données pour trouver les résultats d’une requête. Il est donc intéressant de connaître les méthodes d’accès les plus efficaces et celles qu’il faut essayer d’éviter.
a. Accès séquentiel ou aléatoire
Quel que soit le moyen de stockage (mémoire vive, disque dur traditionnel ou SSD), les données sont toujours organisées dans une structure telle qu’un arbre ou une liste chaînée. Quand vous souhaitez accéder à plusieurs données dans une structure, il existe deux possibilités : soit les données sont toutes situées les unes à côté des autres soit elles sont disséminées à travers la structure.
Dans le premier cas, dès que la position de la première donnée est déterminée, il suffit de lire les données suivantes pour récupérer l’ensemble des informations voulues. On parle alors d’accès séquentiel.
Dans le second cas, la position de chaque...
Optimisation des requêtes
Dans cette section, vous allez voir un certain nombre de techniques pour améliorer le temps d’exécution de vos requêtes. Lisez bien les propositions données comme étant des conseils susceptibles de vous aider mais pas comme des recettes absolues fonctionnant dans tous les cas. L’optimisation reste un art et l’expérience reste la qualité essentielle pour trouver la meilleure manière de rendre plus rapide une requête lente.
1. Isolation des colonnes
L’encapsulation d’une colonne dans une fonction interdit l’utilisation d’un index. Si possible, veillez donc à réécrire votre requête de manière à ne pas avoir besoin d’une fonction. Ce cas de figure se présente souvent avec les dates.
Exemple d’une requête ne pouvant pas utiliser d’index :
mysql> EXPLAIN SELECT * FROM rental WHERE TO_DAYS(CURRENT_DATE())
- TO_DAYS(rental_date) < 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ALL
possible_keys: NULL
key: NULL
... En cherchant à isoler la colonne rental_date sur laquelle existe un index, le résultat est meilleur :
mysql> EXPLAIN SELECT * FROM rental WHERE rental_date >
CURRENT_DATE() + INTERVAL 10 DAY\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: rental_date
key: rental_date
... Il serait encore mieux de préciser la liste des champs voulus plutôt que de passer par SELECT *, qui empêche par exemple l’utilisation d’un éventuel index couvrant.
2. Jointures
MySQL ne connaît qu’une seule méthode pour effectuer des jointures : les tables sont jointes une à une et successivement. Cela signifie qu’une jointure entre quatre tables t1 à t4 sera effectuée par exemple en joignant t1 à t2 (donnant une table t2’), puis t2’ à t3 (donnant...
Autres optimisations
MySQL a introduit beaucoup de changements dans l’optimiseur de requêtes. Le but est de réussir à trouver de nouvelles manières pour exécuter des requêtes qui soit étaient très lentes dans les versions anciennes, soit étaient exécutées de façon non optimale.
Les techniques présentées ci-dessous sont automatiquement choisies par l’optimiseur lorsqu’elles sont bénéfiques. Vous n’avez donc en théorie rien à faire pour en profiter. Sachez cependant que seuls de nombreux retours d’expérience permettront de confirmer dans quels cas ces optimisations sont intéressantes et dans quels cas il vaut mieux les éviter.
1. Index Condition Pushdown
Nous avons déjà évoqué le fait que MySQL ne pouvait pas filtrer à l’aide des colonnes d’un index au-delà de la première inégalité. Par exemple si une table contient un index sur les colonnes (a, b) et qu’une requête mentionne la condition WHERE a > 5 AND b = 10, seule la première colonne de l’index pourra être utilisé pour le filtrage.
Depuis MySQL 5.6, une optimisation appelée Index Condition Pushdown permet de pousser dans l’index des conditions qui ne peuvent normalement pas être prises en compte ; cela évite...
Maintenance des tables
1. Mise à jour des statistiques d’index
Avoir des statistiques d’index à jour, c’est avoir la garantie que l’optimiseur de requêtes choisira le bon plan d’exécution. C’est donc s’assurer que les performances du serveur ne vont pas se dégrader de manière aléatoire, simplement parce que certaines requêtes vont être résolues avec un coûteux parcours complet de la table alors qu’un index aurait nettement pu limiter le nombre de lignes à lire.
En général, vous constaterez qu’il n’est pas nécessaire de forcer un recalcul de ces statistiques, parce qu’InnoDB les recalcule de temps à autre grâce à l’option innodb_stats_auto_recalc activée par défaut. Ce recalcul a lieu principalement lorsque le nombre d’enregistrements de la table a fortement varié. Pour calculer les statistiques, InnoDB n’a pas besoin de parcourir la table en entier, il parcourt simplement un échantillon de pages et en déduit des statistiques supposées correctes pour toute la table.
Cette manière de procéder a pour avantage de rendre l’opération de mise à jour des statistiques peu coûteuse en temps et en charge serveur. Mais si certaines tables ont des distributions de données particulièrement hétérogènes...
