Généralités sur MySQL

Introduction

MySQL s’est fait connaître à la fin des années 1990 comme étant un système de gestion de bases de données relationnelles de choix pour les petits projets web, profitant principalement de sa gratuité et de sa rapidité. Puis, dans le courant des années 2000, les géants du Web se sont tous mis à utiliser massivement MySQL. La suite a été plus difficile, puisque dans la seconde moitié des années 2000, tous ces grands acteurs ont été confrontés aux limitations de MySQL concernant sa scalabilité. C’est à ce moment qu’ont émergé de nombreuses solutions NoSQL. Mais finalement, MySQL a su évoluer rapidement au cours des dernières années, alors que les problèmes de jeunesse des solutions NoSQL sont devenus de plus en plus évidents.

Aujourd’hui, MySQL reste un choix très répandu pour les projets web, beaucoup moins pour les projets plus traditionnels. Quelles en sont les raisons ? Tout d’abord, MySQL est capable d’offrir de bonnes performances même sur des serveurs peu puissants. De plus, sa stabilité est excellente et, sur une instance correctement configurée, il est très rare de voir MySQL planter ou de perdre des données. Enfin, sa gratuité permet d’envisager des déploiements...

Architecture

1. Le serveur et les clients

Le serveur MySQL (mysqld) intercepte les requêtes émises par les clients, transforme ces requêtes en un plan d’exécution, récupère les données selon le plan d’exécution généré, et enfin retourne le résultat au client. Il se compose de plusieurs modules chargés de gérer :

• Les protocoles de communication avec les clients (TCP/IP, socket, SSL...).

• Les droits d’accès aux différentes ressources disponibles (voir chapitre Sécurité et gestion des utilisateurs).

• Les caches afin de minimiser les accès disque.

• Les différents types de journaux serveurs (binaire, requêtes lentes...).

• L’analyse, l’optimisation et l’exécution des requêtes.

• Le stockage des données.

Le diagramme suivant résume les différents modules du serveur :

Chaque distribution de MySQL contient plusieurs clients en ligne de commande pour interagir avec le serveur, les plus utilisés étant :

• mysql : pour exécuter des requêtes. MySQL 8.0 contient également mysqlsh (MySQL Shell), une version plus évoluée du client mysql, qui remplacera peut-être le client mysql à terme.

• mysqldump : pour effectuer des sauvegardes logiques (voir le chapitre Sauvegarde et restauration).

• mysqladmin : pour effectuer...

Utilisation des ressources matérielles

Variantes de MySQL

Les moteurs de stockage

L’une des originalités de MySQL est le concept de moteurs de stockage. Chaque moteur doit offrir un socle commun de fonctionnalités, mais il est possible d’ajouter des fonctionnalités manquantes au serveur. Un exemple classique est celui des clés étrangères : bien que la syntaxe soit reconnue par le serveur, les clés étrangères ne déclenchent aucune action spécifique de la part du serveur. Néanmoins, le moteur InnoDB offre le support des clés étrangères, ce qui n’est pas le cas du moteur MyISAM par exemple.

Cette architecture a cependant des inconvénients. Les moteurs ne sont pas tous équivalents en termes de performances : si le moteur X possède une fonctionnalité qui n’est pas disponible avec votre moteur actuel, il n’est pas garanti que le moteur X soit aussi rapide. Les moteurs ne sont pas non plus tous équivalents en termes de sécurité des données : InnoDB, par exemple, a la capacité de récupérer automatiquement les données en cas d’arrêt inopiné, alors qu’un arrêt inopiné avec MyISAM sera sans doute synonyme de perte de données. Enfin, bien qu’il puisse être tentant de choisir un moteur de stockage pour chacune des tables, il n’est pas simple d’administrer un serveur pour lequel plusieurs moteurs de stockage sont utilisés : la mémoire physique du serveur doit être partagée entre les différents moteurs, les sauvegardes sont plus difficiles, diagnostiquer les problèmes est plus compliqué. Pour ces raisons, il est fortement recommandé de n’utiliser qu’un seul moteur de stockage. Sachez également qu’InnoDB est le seul moteur officiellement développé depuis de nombreuses années. MySQL 8.0 est d’ailleurs la première version pour laquelle toutes les tables utilisent par défaut InnoDB. Précédemment, MyISAM était toujours indispensable pour les tables système (tables de la base mysql, contenant par exemple les comptes utilisateurs).

Le choix du moteur se fait à la création de la table, dans l’ordre SQL CREATE TABLE en renseignant la clause ENGINE.

mysql> CREATE TABLE...

Verrous et transactions

Imaginez que vous ouvrez un fichier pour lire son contenu et qu’au même moment un autre utilisateur ouvre ce fichier pour le modifier. Vous risquez de lire un contenu incohérent, puisqu’une partie aura été modifiée en cours de route. Si vous voulez être certain de pouvoir lire le fichier sans que personne ne le modifie, vous devez le signaler aux autres utilisateurs.

Pour MySQL, la situation est identique : les accès concurrents sont autorisés, il est donc parfaitement possible qu’une requête lise un ensemble de lignes alors qu’une autre requête modifie certaines de ces lignes. Il est donc indispensable que chaque connexion MySQL indique aux autres connexions quelles sont les ressources qui ne doivent pas être modifiées. Cette notification se fait en posant des verrous.

Il existe deux sortes de verrous : les verrous en lecture, qui autorisent d’autres connexions à lire les mêmes données mais pas à les modifier, et les verrous en écriture, qui interdisent à toutes les autres connexions de lire ou d’écrire.

La différence entre ces deux types de verrous est simple à comprendre si vous repensez à l’exemple du fichier que vous voulez ouvrir. Si vous souhaitez simplement lire ce fichier, d’autres utilisateurs peuvent également ouvrir le fichier pour le lire. Mais personne n’a le droit de le modifier, sinon vous risquez de lire des données incohérentes. Et si vous souhaitez mettre à jour le contenu du fichier, vous ne voulez pas que d’autres utilisateurs puissent lire ou écrire dans celui-ci pour éviter que le contenu ne devienne incohérent et donc inexploitable.

Toujours en reprenant cette analogie avec le fichier ouvert en lecture ou écriture, si vous imaginez que le fichier a plusieurs sections, il est possible que vous permettiez à un autre utilisateur de modifier des sections que vous n’êtes pas en train de lire. Dans ce cas, ce que vous lirez sera toujours cohérent puisque personne n’en modifiera le contenu, et vous ferez attendre moins longtemps (voire pas du tout) les utilisateurs qui veulent faire des modifications dans le fichier.

Avec MySQL, le même principe existe : il est possible soit de verrouiller une table...