High Performance MySQL [Ch. 4 : Optimiser le schéma et les types de données]

Trois règles pour choisir un type

• Plus petit, c'est généralement mieux. Choisissez le plus petit type capable de contenir toutes les valeurs que vous stockerez de façon réaliste. Chaque octet se répète pour chaque ligne, dans chaque index et en mémoire. Prévoyez la croissance que vous pouvez réellement anticiper, mais pas celle que vous fantasmez : élargir une colonne sur une grosse table coûte cher.
• Simple, c'est bien. Les entiers se comparent plus vite que les chaînes, qui portent un jeu de caractères et une collation. Une adresse IPv4 est un nombre de 32 bits déguisé : INET_ATON() et INET_NTOA() convertissent d'une forme à l'autre.
• Évitez NULL quand c'est possible. Les colonnes nullables compliquent les index, les statistiques et les comparaisons. Modifier un schéma existant uniquement pour cela est rarement rentable ; déclarer les nouvelles colonnes NOT NULL par défaut, si.

Choisissez d'abord la famille générale (nombre, chaîne, temporel), puis le type précis au sein de celle-ci, en comparant plage, précision et taille.

Nombres

Les nombres entiers utilisent TINYINT, SMALLINT, MEDIUMINT, INT ou BIGINT : 1, 2, 3, 4 et 8 octets. UNSIGNED supprime les valeurs négatives et double la plage positive pour la même taille. La largeur d'affichage de INT(11) n'a jamais changé le stockage et est dépréciée dans MySQL 8.

Pour les nombres à virgule, FLOAT (4 octets) et DOUBLE (8 octets) sont approximatifs ; DECIMAL est exact et coûte plus d'espace et de CPU. Utilisez des types exacts pour l'argent. Une astuce courante pour les montants consiste à stocker des centimes entiers dans un BIGINT.

Chaînes

Des longueurs maximales généreuses ne sont pas gratuites : quand MySQL construit des tables temporaires en mémoire ou des buffers de tri, il peut allouer l'espace correspondant à la taille déclarée, et non à la taille réelle.

Dates et heures

DATETIME couvre les années 1000 à 9999 et stocke la valeur telle qu'elle est écrite. TIMESTAMP stocke les secondes écoulées depuis l'epoch Unix en UTC, convertit depuis et vers le fuseau horaire de la session, et s'arrête en 2038. Les deux acceptent les fractions de seconde, par exemple DATETIME(3) pour les millisecondes. Une convention courante et simple consiste à stocker l'UTC dans un DATETIME et à gérer les fuseaux horaires dans l'application.

Pour les indicateurs et les ensembles de bits, BIT et SET regroupent les booléens de façon compacte, au prix de requêtes peu commodes.

Choisir les identifiants

• Le même type des deux côtés d'une jointure. Mélanger INT et BIGINT UNSIGNED, ou des nombres et des chaînes, force des conversions et peut empêcher l'utilisation des index.
• Les entiers avec AUTO_INCREMENT sont le choix par défaut : petits, rapides à comparer, et ajoutés dans l'ordre à l'index clusterisé.
• Évitez ENUM et SET comme clés, ainsi que les chaînes longues, lentes à comparer et qui alourdissent chaque index secondaire.
• Les clés aléatoires comme les UUID s'insèrent à des endroits aléatoires du B-Tree : scissions de pages, fragmentation et buffer pool froid. Si vous en avez besoin, stockez-les en BINARY(16) (le UUID_TO_BIN(uuid, 1) de MySQL 8 réordonne aussi les bits de temps pour que les insertions restent à peu près séquentielles).

Pièges de schéma propres à MySQL

• Tables très larges. Le serveur et InnoDB s'échangent les lignes dans un buffer qui doit être décodé colonne par colonne. Avec des centaines de colonnes, cette conversion devient visible dans la consommation CPU.
• Trop de jointures. Les modèles entité-attribut-valeur transforment chaque lecture en un empilement d'auto-jointures. MySQL limite une jointure à 61 tables, et les performances se dégradent bien avant.
• ENUM comme cible mouvante. Si la liste de valeurs change souvent, une table de référence est plus facile à faire évoluer.
• NULL contre valeurs magiques. Évitez les NULL inutiles, mais ne remplacez pas un véritable « inconnu » par des valeurs sentinelles comme 0 ou '1970-01-01', que les requêtes devront ensuite traiter comme des cas particuliers.

Normalisé ou dénormalisé

Un schéma normalisé stocke chaque fait une seule fois. Les mises à jour touchent une seule ligne, les tables sont étroites et tiennent en mémoire, et nul besoin de DISTINCT pour défaire des doublons. Le prix à payer : des jointures sur presque chaque lecture.

Un schéma dénormalisé copie les données là où elles sont lues. Les lectures évitent les jointures et, ce qui compte souvent davantage, les colonnes de filtre et de tri peuvent vivre dans une même table et partager un même index. Imaginez un outil de suivi de bugs qui liste les derniers bugs des clients payants. Si plan n'existe que dans la table des clients, MySQL doit soit parcourir les bugs par date en vérifiant chaque client, soit récupérer tous les bugs des clients payants puis les trier. Copiez plan dans la table des bugs, et un index sur (plan, created_at) répond directement à la requête.

La réponse habituelle est un mélange : normaliser, puis dupliquer les quelques colonnes dont les lectures fréquentes ont besoin, et les garder synchronisées dans l'application ou via des triggers. La duplication coûte le moins cher pour les valeurs qui changent rarement.

Tables de cache et de synthèse

Quand un certain décalage est acceptable, construisez des tables séparées, pensées pour la lecture. Une table de cache contient des copies dénormalisées des données d'autres tables ; une table de synthèse contient des agrégats précalculés, comme le nombre de bugs par projet et par jour, rafraîchis à intervalles réguliers. Les lectures deviennent de simples recherches peu coûteuses, et le coût passe à une tâche de fond. Les tables de compteurs, parfois réparties sur plusieurs lignes pour éviter la contention de verrous sur une seule ligne très sollicitée, en sont proches.

Rien n'est jamais définitif

Les schémas évoluent, et sur les grandes tables, c'est les modifier qui coûte cher. Dans MySQL 8, beaucoup d'opérations ALTER TABLE s'exécutent sur place ou instantanément (ajouter une colonne se fait souvent en ALGORITHM=INSTANT), mais d'autres reconstruisent encore la table en copiant chaque ligne, ce qui demande jusqu'à deux fois l'espace disque et des heures. Pour celles-ci, les équipes utilisent des outils de modification de schéma en ligne (pt-online-schema-change, gh-ost) qui construisent une table fantôme puis la substituent à l'originale, ou appliquent d'abord le changement sur les réplicas avant de basculer.

Questions fréquentes