High Performance MySQL [Ch. 1 : Architecture et histoire de MySQL]

Trois couches, une frontière qui compte

Représentez-vous un serveur MySQL comme une pile. Tout en haut se trouve la plomberie dont tout service réseau a besoin : accepter les connexions, les authentifier, attribuer un thread à chacune. Au milieu, la partie que l'on désigne quand on dit « MySQL » : le parseur, l'optimiseur, les fonctions intégrées, et tout ce qui fonctionne de la même façon quel que soit le mode de stockage des données, comme les vues, les triggers et les routines stockées.

Tout en bas, les moteurs de stockage. Ils stockent et récupèrent des lignes, rien d'autre ; ils n'analysent pas le SQL. Le serveur leur parle via l'API handler, un petit vocabulaire d'appels comme « se positionner sur cette clé », « donne-moi la ligne suivante » ou « insère cette ligne ». Le moteur se choisit par table, pas par serveur, ce qui explique qu'une base puisse techniquement mélanger plusieurs moteurs.

Connexions et privilèges

Chaque connexion cliente est servie par un thread à l'intérieur du processus serveur. Créer des threads a un coût, donc serveurs et pilotes les conservent : un cache de threads côté serveur, des pools de connexions côté client. Les pools servent aussi de régulateur, en plafonnant le nombre de clients qui peuvent solliciter la base en même temps.

L'authentification (utilisateur, hôte, mot de passe, certificat TLS) ne fait qu'admettre une connexion. Ce qu'elle a ensuite le droit de faire relève d'une décision distincte, prise par le système de privilèges, jusqu'au niveau des tables et des colonnes. Accorder à un utilisateur un accès en lecture seule à un seul schéma en est un usage quotidien.

Du texte SQL à un plan

Une requête est analysée en arbre, vérifiée, puis confiée à l'optimiseur, qui peut la réécrire, réordonner les jointures et choisir les index. L'optimiseur ignore comment le moteur dispose les octets ; il lui demande des statistiques et des estimations de coût, et planifie à partir de celles-ci. EXPLAIN affiche le résultat, et les hints d'optimiseur permettent de le contredire quand vous en savez plus que lui.

Les éditions plus anciennes du livre décrivent un cache de requêtes qui renvoyait des résultats stockés pour un texte SELECT identique. Il passait mal à l'échelle sous les écritures et a été supprimé dans MySQL 8.0 : mettre les résultats en cache est désormais le travail de l'application.

Verrous et granularité

Toute ressource partagée entre lecteurs et écrivains concurrents a besoin de règles. Le couple classique associe un verrou partagé (lecture), que plusieurs lecteurs peuvent détenir simultanément, et un verrou exclusif (écriture), qui bloque tous les autres. La justesse est facile si l'on verrouille tout ; la performance vient du fait de verrouiller le moins possible.

Les verrous de ligne d'InnoDB sont plus subtils que « une ligne par instruction ». Pour empêcher l'apparition de lignes dans une plage qu'une autre transaction a déjà lue, il verrouille aussi les intervalles entre les entrées d'index (gap locks et next-key locks). Deux insertions dans des intervalles voisins peuvent donc s'attendre mutuellement, et parfois provoquer un deadlock.

Transactions et ACID

Une transaction regroupe des instructions pour qu'elles réussissent ou échouent ensemble. Déplacer du stock d'une ligne d'entrepôt à une autre demande deux UPDATE ; sans transaction, un crash entre les deux fait disparaître de l'inventaire. ACID désigne les garanties qui rendent cela sûr :

• Atomicité : toutes les instructions s'appliquent, ou aucune.
• Cohérence : la base passe d'un état valide à un autre.
• Isolation : les modifications en cours sont masquées aux autres transactions, à un degré configurable.
• Durabilité : une fois validée, une modification survit à un crash. La force de cette promesse (flush à chaque commit, réplication avant acquittement) est un coût que vous choisissez.

Les deadlocks font partie de la vie normale avec les transactions. InnoDB détecte les cycles et annule la transaction la moins coûteuse à défaire ; les applications doivent être prêtes à réessayer.

Niveaux d'isolation

MVCC : des lecteurs qui ne bloquent pas

Les verrous de ligne seuls feraient encore attendre les lecteurs derrière les écrivains. InnoDB évite l'essentiel de ce problème grâce au contrôle de concurrence multiversion : un SELECT simple lit un instantané cohérent au lieu de poser des verrous. Chaque version de ligne enregistre la transaction qui l'a écrite, et les versions plus anciennes sont reconstruites à partir de l'undo log lorsqu'un instantané en a besoin. Deux transactions peuvent donc voir des contenus différents de la même table au même instant, et avoir toutes deux raison.

Le prix, c'est le stockage et le nettoyage : l'historique d'undo doit être conservé tant qu'un instantané en a besoin, c'est pourquoi une transaction laissée ouverte pendant des heures ralentit le serveur pour tout le monde. Le MVCC s'applique à READ COMMITTED et REPEATABLE READ ; les deux autres niveaux n'utilisent pas les instantanés de la même manière.

Moteurs de stockage : InnoDB et les autres

InnoDB est le moteur par défaut et presque toujours le bon choix : transactionnel, résistant aux crashs, avec verrous de ligne et sauvegardes à chaud. Il stocke chaque table à l'intérieur du B-Tree de sa clé primaire (un index clusterisé), donc les recherches par clé primaire sont rapides et chaque index secondaire embarque une copie de la clé primaire. Gardez des clés primaires petites.

MyISAM, l'alternative historique, n'a que des verrous de table, aucune transaction et une mauvaise reprise après crash. Ses anciens arguments, la recherche full-text et les tables compressées en lecture seule, sont désormais couverts par InnoDB aussi. Pour choisir un moteur, les questions restent les mêmes : avez-vous besoin de transactions, de sauvegardes à chaud et d'une reprise rapide après crash ? Si oui, c'est InnoDB. Mélanger les moteurs dans une même base est possible, mais attire des cas limites étranges.

Comment cela apparaît dans QueryCrack

Chaque défi s'exécute sur InnoDB, via une connexion appartenant à un utilisateur au privilège minimal : SELECT sur un seul schéma, rien d'autre. Ce privilège suffit pour inspecter le schéma : Exécuter accepte donc aussi SHOW INDEX, DESCRIBE et EXPLAIN. Le correcteur lit les compteurs handler avant et après votre requête : ce que vous optimisez, c'est donc le nombre d'appels que la couche serveur adresse au moteur de stockage. Une clé primaire clusterisée rend certains de ces appels très peu coûteux, et c'est toute l'astuce derrière count_em_bugs.

Questions fréquentes