Dans les fichiers d’une base de données (1/3) : InnoDB, de MySQL à MariaDB

Une base de données relationnelle donne l’illusion de tables, de lignes et de colonnes. Sur le disque, il n’y a que des fichiers découpés en blocs de taille fixe. Comprendre comment ces blocs sont organisés explique beaucoup de comportements qui semblent mystérieux : pourquoi un fichier ne rétrécit pas après un DELETE, pourquoi une clé primaire en UUID ralentit les insertions, pourquoi un index secondaire coûte plus cher qu’on ne le pense, ou ce que protège vraiment le « doublewrite buffer ».

Cette série de trois billets ouvre le capot de trois moteurs : InnoDB (le moteur de stockage de MySQL et de MariaDB), PostgreSQL et Oracle. Le même fil conducteur les traverse : les fichiers, les pages, les lignes, et l’endroit où vivent les anciennes versions des données. Premier arrêt : InnoDB.

Le répertoire de données

Chaque fichier a un rôle précis :

• ibdata1, le tablespace système. Historiquement, il contenait tout : tables, index, undo, dictionnaire. Aujourd’hui, il ne garde plus que le change buffer (sur MySQL) et, sur MariaDB, le doublewrite et parfois l’undo. Il ne rétrécit jamais : un ibdata1 de 50 Go gonflé par une vieille transaction le restera, et la seule façon de le réduire est un export complet suivi d’une réimportation ;
• un fichier .ibd par table, grâce à innodb_file_per_table=ON (le défaut depuis MySQL 5.6). C’est ce qui permet de récupérer l’espace d’une table supprimée ou vidée (DROP, TRUNCATE), de déplacer une table seule, ou de restaurer un seul tablespace ;
• les tablespaces d’undo (undo_001…) : les images d’avant des lignes modifiées, pour les ROLLBACK et pour la lecture cohérente des autres transactions ;
• le journal redo : chaque modification de page y est décrite avant que la page elle-même soit écrite sur le disque. Après un crash, InnoDB rejoue ce journal ;
• le doublewrite buffer, expliqué plus bas ;
• les binlogs, qui n’appartiennent pas à InnoDB mais au serveur : ils décrivent les changements au niveau SQL, pour la réplication et la restauration à un instant donné.

Une base de données SQL (CREATE DATABASE shop) n’est rien d’autre qu’un répertoire. Les noms exacts des fichiers globaux varient : MySQL et MariaDB ont divergé sur le dictionnaire (MySQL 8 a remplacé les fichiers .frm par un dictionnaire transactionnel dans mysql.ibd, MariaDB a gardé les .frm), sur le redo et sur le doublewrite. Le format interne des pages, lui, est commun. Pour voir les tablespaces d’une instance :

-- MySQL 8
SELECT name, file_size, allocated_size FROM information_schema.INNODB_TABLESPACES;
-- MariaDB
SELECT name, file_size, allocated_size FROM information_schema.INNODB_SYS_TABLESPACES;

Pages, extents, segments

L’unité de base est la page, de 16 Ko par défaut. Sa taille (innodb_page_size) est fixée à la création de l’instance et ne peut plus changer ensuite. Tout ce qu’InnoDB lit ou écrit, il le lit ou l’écrit par pages entières : lire une ligne de 100 octets, c’est charger 16 Ko en mémoire, dans le buffer pool.

Les pages sont regroupées en extents de 64 pages, soit 1 Mo. Allouer par extents plutôt que page par page garde les pages d’un même index physiquement proches, ce qui rend les lectures séquentielles efficaces.

Chaque index possède deux segments : un pour ses pages feuilles, un pour ses pages intermédiaires. Les séparer garde les feuilles (là où sont les données) contiguës, pour les parcours par plage. Pour ne pas réserver 1 Mo à chaque petite table, un segment reçoit d’abord jusqu’à 32 pages isolées, et ne passe aux extents complets qu’ensuite. C’est pourquoi une table vide occupe une centaine de kilo-octets, et pas plusieurs mégaoctets.

Les premières pages d’un .ibd sont réservées à la gestion de l’espace : la page 0 (FSP_HDR) décrit l’état des extents, la page 1 le bitmap du change buffer, la page 2 les « inodes » des segments. Sur MySQL 8, la page 3 contient la description de la table en JSON (SDI) et la racine de l’index cluster est en page 4. Sur MariaDB, qui garde des .frm, la racine est en page 3.

Anatomie d’une page

Chaque zone a une raison d’être :

• Le FIL header est commun à toutes les pages, quel que soit leur type. Il contient la somme de contrôle, qui détecte une corruption à la lecture, et le LSN (Log Sequence Number) de la dernière modification. Après un crash, InnoDB compare ce LSN à celui de chaque entrée du redo : il ne rejoue que ce que la page n’a pas encore reçu. Les pointeurs vers les pages précédente et suivante relient toutes les pages d’un même niveau de l’arbre ;
• l’INDEX header donne le niveau de la page dans l’arbre (0 pour une feuille), le nombre de lignes et l’identifiant de l’index auquel elle appartient ;
• infimum et supremum sont deux fausses lignes, la plus petite et la plus grande possibles. Elles servent de début et de fin à la liste chaînée des lignes, ce qui simplifie le code d’insertion ;
• les lignes sont écrites dans l’ordre où elles arrivent, mais chacune pointe vers la suivante dans l’ordre de la clé. Insérer au milieu ne déplace rien : il suffit de modifier deux pointeurs ;
• le page directory part de la fin de la page et grandit vers le haut. Il contient un « slot » pour 4 à 8 lignes. Pour trouver une clé, InnoDB fait une recherche dichotomique dans ces slots, puis parcourt au plus quelques lignes de la liste chaînée ;
• le FIL trailer répète une partie du LSN et de la somme de contrôle. Si le début et la fin de la page ne correspondent pas, la page n’a été écrite qu’à moitié.

Quand une page est pleine et qu’une ligne doit y entrer, InnoDB la coupe en deux (page split) et met à jour la page parente. Quand deux pages voisines descendent sous la moitié de leur remplissage, il peut les fusionner. Ces opérations sont coûteuses et laissent des pages à moitié vides : c’est la fragmentation.

Le doublewrite buffer : pourquoi écrire deux fois

Un disque garantit l’atomicité d’écriture de 512 octets ou 4 Ko, pas de 16 Ko. Si le serveur s’arrête au milieu de l’écriture d’une page, celle-ci peut se retrouver à moitié ancienne, à moitié nouvelle : une torn page. Le redo ne peut pas la réparer, car il décrit des modifications à appliquer sur une page cohérente. InnoDB écrit donc chaque page d’abord dans le doublewrite buffer, puis à son emplacement définitif. Après un crash, une page déchirée est remplacée par sa copie intacte, puis le redo s’applique. Sur un système de fichiers qui garantit lui-même des écritures atomiques de 16 Ko (ZFS, certains SSD), on peut le désactiver.

Une ligne

• Les colonnes cachées : DB_TRX_ID est l’identifiant de la dernière transaction qui a modifié la ligne, DB_ROLL_PTR pointe vers l’image précédente dans l’undo. Une transaction qui ne doit pas voir la modification suit ce pointeur pour reconstruire l’ancienne version ;
• sans clé primaire, InnoDB utilise la première clé unique non nulle, ou à défaut ajoute une colonne cachée DB_ROW_ID de 6 octets, alimentée par un compteur global partagé par toutes ces tables. Définissez toujours une clé primaire ;
• le bit « deleted » : un DELETE ne fait que marquer la ligne. Elle est réellement retirée plus tard par le purge thread, une fois qu’aucune transaction ne peut plus la voir ;
• les NULL ne coûtent qu’un bit dans le bitmap : aucune place dans les données ;
• les colonnes longues (TEXT, BLOB, gros VARCHAR) : une ligne doit tenir dans environ la moitié d’une page, pour qu’une page contienne au moins deux lignes. Au format DYNAMIC, une valeur trop longue part entière dans des pages de débordement, et la ligne ne garde qu’un pointeur de 20 octets.

L’index cluster : la table est un arbre

C’est la décision de conception la plus importante d’InnoDB : la table est l’index de la clé primaire. Il n’existe pas de « tas » de lignes à côté : les lignes complètes sont stockées dans les feuilles d’un arbre B+ trié par clé primaire. Plusieurs conséquences pratiques en découlent :

• une recherche par clé primaire descend l’arbre et trouve directement la ligne. Avec des pages de 16 Ko et une clé BIGINT, une page intermédiaire référence plus d’un millier de pages filles : trois niveaux suffisent pour des centaines de millions de lignes ;
• une clé primaire croissante (AUTO_INCREMENT) ajoute toujours à droite de l’arbre : les pages se remplissent proprement, sans split au milieu. Une clé aléatoire (UUID v4) insère n’importe où, provoque des splits partout et laisse des pages à moitié pleines. Les UUID ordonnés dans le temps (UUID v7) évitent ce problème ;
• un index secondaire ne contient pas l’adresse physique de la ligne mais sa clé primaire. Une recherche par index secondaire coûte donc deux descentes d’arbre. En contrepartie, déplacer une ligne lors d’un split ne demande pas de mettre à jour les index secondaires ;
• une clé primaire longue est recopiée dans chaque index secondaire : une clé de 36 caractères multiplie la taille de tous les index ;
• un index couvrant, qui contient toutes les colonnes dont la requête a besoin, évite la seconde descente : EXPLAIN affiche alors Using index.

Pourquoi le fichier ne rétrécit pas

Un DELETE massif libère de la place dans les pages, que les insertions suivantes réutiliseront, mais ne rend rien au système de fichiers. Pour récupérer l’espace, il faut reconstruire la table :

OPTIMIZE TABLE shop.orders;          -- équivaut, pour InnoDB, à ALTER TABLE … FORCE
-- MariaDB : vérifier l'espace réellement libérable avant
SELECT table_name, data_free/1024/1024 AS free_mb
FROM information_schema.tables WHERE table_schema='shop' ORDER BY data_free DESC;

La reconstruction copie toutes les lignes dans un nouveau fichier, compact, puis remplace l’ancien. Elle se fait en ligne sur les versions récentes, mais demande temporairement l’espace d’une seconde copie de la table.

Prochain billet : PostgreSQL, qui a fait le choix inverse sur presque tous ces points : une table en tas, des pages de 8 Ko, et des anciennes versions gardées dans la table elle-même.

---

Ce billet fait partie d’une série de notes d’administration système (tag « carnet d’admin »).
Série « Dans les fichiers d’une base de données », partie 1/3. · Billet suivant → · English version