Quels sont les avantages et les inconvénients de MySQL, et quand est-il adapté ?

MySQL est un système de gestion de base de données relationnelle utilisable pour les services web classiques et le traitement transactionnel en ligne (OLTP), notamment avec le moteur de stockage InnoDB et ses fonctionnalités telles que les transactions, le verrouillage au niveau des lignes et les lectures cohérentes. En revanche, la réplication en lecture est asynchrone par défaut, et les configurations de haute disponibilité, les requêtes complexes et le partitionnement présentent des contraintes de conception et d’exploitation. Ses avantages ne deviennent de véritables avantages que lorsqu’ils correspondent à la charge de travail et aux capacités opérationnelles de l’équipe. dev.mysql.com↗ dev.mysql.com↗

Lors de l’évaluation de MySQL, il est plus juste d’examiner la manière dont les données sont lues et écrites, les garanties nécessaires en cas de panne et le niveau de complexité du schéma, plutôt que de demander simplement s’il s’agit d’une « base de données rapide ». La présentation ci-dessous suit le périmètre de la documentation officielle de MySQL 8.4. Le comportement réel peut varier selon la version, le moteur de stockage, la configuration et la topologie de réplication. dev.mysql.com↗

Quel type de base de données est MySQL ?

Une base de données relationnelle stocke les données dans des tables composées de lignes et de colonnes, et utilise le langage de requête SQL pour gérer les relations entre les tables. Par exemple, une boutique en ligne peut utiliser des tables telles que customers, orders et order_items pour gérer les relations entre les clients, les commandes et les produits commandés. Dans de nombreux cas, plusieurs modifications doivent être regroupées dans une même opération, par exemple la création d’une commande, la diminution du stock et l’enregistrement du statut du paiement.

Les moteurs de stockage sont importants dans MySQL. Un moteur de stockage est un composant chargé de déterminer comment les tables sont stockées, verrouillées et récupérées. InnoDB, en particulier, est le moteur de stockage par défaut de MySQL et fournit les transactions ACID, les validations et annulations, la récupération après incident, le verrouillage au niveau des lignes, le contrôle de concurrence multiversion (MVCC) et les clés étrangères. Ainsi, la fiabilité transactionnelle couramment associée à MySQL désigne souvent MySQL utilisant des tables InnoDB correctement configurées. dev.mysql.com↗

ACID est un terme collectif désignant les propriétés attendues des transactions. L’atomicité signifie qu’une opération complète réussit entièrement ou est annulée. La cohérence signifie que les règles de données définies sont maintenues. L’isolation contrôle les effets que des opérations exécutées simultanément ont les unes sur les autres, tandis que la durabilité signifie que les résultats validés doivent survivre aux pannes. Les caractéristiques ACID de MySQL sont également influencées par le moteur, la configuration, le matériel et les procédures opérationnelles ; le seul nom ne doit donc pas être interprété comme résolvant automatiquement tous les scénarios de panne. dev.mysql.com↗

Pourquoi les transactions InnoDB et la concurrence constituent-elles un avantage ?

OLTP désigne les charges de travail comportant des requêtes fréquentes et relativement courtes, telles que la réception de commandes, la mise à jour d’informations de membre ou le changement du statut d’un paiement. Dans cet environnement, de nombreux utilisateurs peuvent modifier simultanément les mêmes types de données ; il est donc important de regrouper les modifications de données de façon sûre et de limiter autant que possible l’étendue des conflits.

Puisqu’InnoDB fournit les validations de transaction, les annulations et la récupération après incident, une application peut être configurée pour annuler une transaction si une étape échoue, par exemple lors de la création d’une commande et de la décrémentation du stock. Le verrouillage au niveau des lignes verrouille les lignes nécessaires et peut être plus favorable au travail concurrent qu’un verrouillage étendu de toute une table. Cependant, les attentes et les conflits ne disparaissent pas lorsque plusieurs opérations se disputent fréquemment les mêmes lignes ou des plages de données adjacentes. dev.mysql.com↗

MVCC fournit des lectures cohérentes en utilisant plusieurs versions des données. Cela ne signifie pas simplement que les lectures et les écritures ne se gênent jamais. Les résultats observés et le comportement de verrouillage peuvent différer selon le niveau d’isolation de la transaction, les instructions SQL exécutées et l’utilisation ou non de lectures verrouillantes. Par conséquent, pour résoudre des problèmes de concurrence, ne vérifiez pas seulement le nom du moteur. Définissez d’abord, dans les règles métier, quelles lectures doivent voir la valeur la plus récente et quelles mises à jour doivent être mutuellement exclusives.

Une clé étrangère est une contrainte qui aide à garantir qu’une valeur dans une table référence une ligne existante dans une autre table. Par exemple, elle peut imposer qu’un identifiant client d’une commande corresponde à un client réel. Cela peut contribuer à réduire les références invalides, mais implique également que les règles de suppression et de mise à jour, ainsi que la structure des tables, soient conçues soigneusement à l’avance. Si vous prévoyez d’introduire ultérieurement le partitionnement, vous devez aussi vérifier les restrictions de compatibilité liées aux clés étrangères. dev.mysql.com↗ dev.mysql.com↗

Quels sont les avantages pour les environnements de développement et le contrôle d’accès ?

MySQL fournit plusieurs protocoles clients et API pour C/C++, Java, PHP, Python, Ruby et d’autres langages. Les applications utilisant déjà ces langages et outils disposent donc d’options pour construire une couche de connexion, et il peut être relativement facile d’établir un chemin de base entre une application web et la base de données. Toutefois, la présence d’une API pour un langage donné ne garantit pas à elle seule que le pool de connexions, les nouvelles tentatives après erreur, les jeux de caractères et la gestion des fuseaux horaires sont correctement configurés. L’approche d’accès aux données de l’application doit être validée séparément. dev.mysql.com↗

Le système de privilèges fait aussi partie de la conception opérationnelle. MySQL fournit des privilèges aux niveaux global, base de données et objet, ainsi que des privilèges dynamiques. Ils peuvent servir à séparer les rôles : par exemple, attribuer à un compte applicatif uniquement les autorisations de lecture et d’écriture dont il a besoin sur certaines tables, tout en utilisant des comptes distincts pour les sauvegardes et les tâches administratives. Le principe du moindre privilège est un principe de conception utile pour limiter l’impact si un compte est compromis ou si un programme contient une erreur. dev.mysql.com↗

Toutefois, rendre les privilèges plus granulaires ne suffit pas, à lui seul, à assurer la sécurité. En pratique, vous devez gérer les comptes qui possèdent quels privilèges, vérifier si les comptes administrateur et applicatif sont séparés, et définir le processus qui régit les modifications de privilèges. Autrement dit, les fonctionnalités de privilèges de MySQL fournissent des mécanismes de contrôle, mais la responsabilité de les attribuer selon les rôles métier reste du ressort de l’exploitation.

Quels problèmes les index et le partitionnement résolvent-ils ?

Un index est une structure de données conçue pour réduire la nécessité de parcourir une table entière afin de trouver les lignes recherchées. Par exemple, si les requêtes visant à trouver une commande unique par son numéro sont fréquentes, un index sur cette colonne peut aider. Les index multicolonnes peuvent être utiles aux requêtes utilisant plusieurs colonnes comme conditions, mais l’ordre des colonnes et les prédicats réels de la requête sont importants. InnoDB prend en charge jusqu’à 64 index secondaires par table et jusqu’à 16 colonnes par index multicolonne. dev.mysql.com↗

Cependant, les index ne deviennent pas automatiquement meilleurs lorsqu’on en crée davantage. Ils consomment de l’espace de stockage et doivent aussi être maintenus lors de l’insertion, de la mise à jour ou de la suppression de lignes. La limite du nombre d’index pris en charge est une limite technique, et non un objectif de conception. Il convient d’évaluer, à partir de requêtes représentatives et de la distribution des données, si un index raccourcissant le chemin de recherche est réellement nécessaire et quelle charge il ajoute aux chemins d’écriture.

Les index de chaînes de caractères présentent aussi des contraintes physiques. La limite de préfixe de clé d’index InnoDB est généralement de 3 072 octets, bien qu’elle puisse être réduite à 767 octets selon le format de ligne. Si vous tentez d’indexer de longues chaînes avec un jeu de caractères ayant une taille de stockage élevée par caractère, tel que utf8mb4, cette limite peut affecter la conception du schéma. Il est particulièrement important de distinguer qu’il s’agit d’une limite exprimée en octets, et non en nombre de caractères. dev.mysql.com↗

Le partitionnement est une fonctionnalité qui stocke une table dans plusieurs partitions selon des règles définies. Si une condition correspond aux règles de partitionnement, l’élagage de partitions peut exclure les partitions que MySQL n’a pas besoin de parcourir. Par exemple, pour une grande table d’historique interrogée par plage de dates, si la table est partitionnée par date, vous pouvez envisager une conception réduisant la plage cible lors des recherches sur une période précise. dev.mysql.com↗

Cela ne signifie pas que toute grande table doit être partitionnée. Si les conditions fréquemment utilisées ne correspondent pas à la clé de partitionnement, la réduction attendue des données cibles peut ne pas se produire. Le partitionnement introduit aussi des règles supplémentaires concernant l’exploitation, la conception des clés et les contraintes ; il est donc préférable de déterminer d’abord si le problème peut être résolu au moyen d’index plus simples et d’améliorations de requêtes.

Quelles contraintes s’appliquent au partitionnement et à la recherche en texte intégral ?

Dans MySQL 8.4, le partitionnement est pris en charge par les moteurs de stockage InnoDB et NDB. Une table InnoDB partitionnée ne peut pas avoir de clés étrangères ni être la cible de références par clé étrangère provenant d’une autre table. De plus, chaque colonne utilisée dans la clé de partitionnement doit faire partie de chaque clé unique, y compris la clé primaire. Cette condition peut modifier considérablement le modèle lorsque vous tentez de partitionner une table centrale avec de nombreuses références, telle qu’une table de commandes. dev.mysql.com↗

La recherche en texte intégral est une fonctionnalité permettant de rechercher des mots dans du texte. MySQL prend en charge la recherche en texte intégral avec InnoDB et MyISAM, mais elle n’est pas prise en charge sur les tables partitionnées. Par conséquent, si vous attendez à la fois une fonctionnalité de recherche dans de longs documents et un partitionnement pour des données d’historique à grande échelle dans la même table, vous devez vérifier tôt si cette combinaison est possible. L’ajout ultérieur d’une fonctionnalité peut nécessiter de scinder des tables ou de modifier l’architecture de recherche. dev.mysql.com↗

Ces restrictions ne montrent pas simplement que MySQL manque de fonctionnalités, mais que les fonctionnalités ne sont pas nécessairement combinables indépendamment. Vous pouvez examiner un à un les besoins en clés étrangères, clés uniques, clés de partitionnement et recherche en texte intégral. Il est plus sûr d’éviter de décider d’un schéma sur le seul bénéfice d’une fonctionnalité.

Comment utiliser la réplication pour la montée en charge en lecture et les sauvegardes ?

La réplication est une architecture qui transmet les modifications d’un serveur à un autre. Généralement, un serveur source enregistre les modifications et les serveurs réplicas les appliquent. La distribution d’une partie des requêtes de lecture entre plusieurs réplicas peut réduire la charge de lecture de la source, et vous pouvez aussi envisager de déporter les tâches de sauvegarde ou d’analyse vers les réplicas. dev.mysql.com↗

GTID est une méthode de gestion des positions de réplication en attribuant un identifiant à chaque transaction. La réplication basée sur GTID peut aider à réduire la charge liée à l’alignement manuel des noms de fichiers binlog et des positions. Toutefois, l’établissement d’une topologie de réplication est distinct de la surveillance du retard de réplication et de l’exploitation des procédures de récupération. Vous devez déterminer quel serveur traite les écritures, quels serveurs peuvent servir les lectures et quoi faire lorsqu’un retard survient. dev.mysql.com↗

La réplication par défaut est asynchrone. Cela signifie qu’au moment précis où une validation sur la source est terminée, il n’est pas garanti que chaque réplica ait appliqué la même modification. Par exemple, une requête routée vers un réplica immédiatement après qu’un utilisateur a modifié son adresse peut afficher l’adresse précédente. Cela peut être considéré comme un problème de cohérence lecture après écriture. Les requêtes qui doivent disposer des données les plus récentes nécessitent une politique les dirigeant vers la source ou tenant compte de l’état d’application des réplicas. dev.mysql.com↗

La réplication semi-synchrone adopte une approche dans laquelle la source reçoit la confirmation qu’un réplica a reçu et journalisé un événement de transaction. C’est une alternative à la réplication asynchrone par défaut, mais cela ne signifie pas que toutes les exigences deviennent entièrement synchrones. Lorsqu’il est question de fortes exigences de synchronisme, définissez clairement le niveau de cohérence requis, la plage de latence acceptable et le comportement en cas de panne, puis envisagez également des options distinctes telles que NDB Cluster. dev.mysql.com↗

Group Replication résout-il automatiquement la haute disponibilité ?

La haute disponibilité est l’objectif de configurer un système afin qu’un service puisse continuer à fonctionner lorsqu’une partie d’un serveur ou du réseau tombe en panne. Group Replication gère l’appartenance au groupe, élit automatiquement un primaire en mode primaire unique ou prend en charge des configurations multi-primaires. Sa capacité à former une topologie de haute disponibilité lorsqu’il est combiné à InnoDB Cluster et MySQL Router constitue une option importante de MySQL. dev.mysql.com↗

Cependant, le consensus entre serveurs de base de données et le basculement des connexions applicatives sont deux problèmes distincts. Group Replication n’inclut pas de fonctionnalité permettant de basculer les clients défaillants vers des membres sains. Les applications ont besoin de MySQL Router, d’un répartiteur de charge, d’un connecteur ou d’un middleware personnalisé pour déterminer où se connecter, et cette couche doit elle aussi être exploitée en tenant compte des pannes, des nouvelles tentatives et des mises à jour d’état. dev.mysql.com↗

Par conséquent, lorsque vous entendez « basculement automatique », posez au moins trois questions distinctes. Premièrement, un primaire peut-il être élu ? Deuxièmement, les nouvelles connexions applicatives sont-elles dirigées vers un serveur sain ? Troisièmement, quels résultats les requêtes en cours et les requêtes répétées par l’utilisateur obtiendront-elles ? L’existence d’une fonctionnalité répondant à la première question ne garantit pas automatiquement les deux autres.

Une configuration multi-primaire est aussi difficile à comprendre comme un simple interrupteur destiné à augmenter les performances d’écriture. Lorsque les écritures sont autorisées depuis plusieurs emplacements, vous devez également concevoir, au niveau métier, la manière d’éviter ou de traiter les modifications concurrentes des mêmes données, ainsi que les règles que doit suivre le chemin d’écriture de l’application. La haute disponibilité est une préoccupation opérationnelle qui inclut non seulement le choix des fonctionnalités, mais aussi les exercices de panne, l’observabilité et les procédures de récupération.

Pourquoi les requêtes complexes peuvent-elles augmenter la charge de réglage ?

L’optimiseur est un composant qui choisit, parmi plusieurs façons d’exécuter une instruction SQL, le plan d’exécution dont le coût est estimé le plus faible. Par exemple, il détermine quel index utiliser en premier et dans quel ordre joindre les tables. L’optimiseur basé sur les coûts de MySQL peut s’appuyer sur des estimations lorsque les statistiques sont insuffisantes ; il peut donc choisir un plan différent de celui attendu par une personne. dev.mysql.com↗

À mesure que le nombre de tables jointes augmente, le nombre de plans d’exécution candidats peut croître de façon exponentielle. Dans ce cas, non seulement la récupération des données elle-même, mais aussi le temps d’optimisation nécessaire pour explorer des plans adaptés peuvent devenir un goulot d’étranglement. Ainsi, dans les systèmes exécutant fréquemment des requêtes analytiques complexes ou de nombreuses jointures, il est difficile de déterminer l’adéquation à partir de la seule capacité d’exécuter syntaxiquement du SQL. Les tests doivent utiliser des distributions de données réelles et des conditions représentatives. dev.mysql.com↗

EXPLAIN est un outil permettant de vérifier le plan d’exécution choisi pour une requête. Lorsque les résultats sont lents, examinez d’abord les prédicats, les conditions de jointure, les index utilisés et les nombres de lignes estimés. Si nécessaire, vous pouvez actualiser les statistiques ou ajuster les index et la structure des requêtes. Des indications d’index et des fonctionnalités de contrôle de l’optimiseur sont également disponibles, mais les approches qui forcent un plan donné doivent être vérifiées continuellement afin de garantir qu’elles restent valides après les modifications de données. dev.mysql.com↗ dev.mysql.com↗

Cela ne signifie pas que des analyses complexes ne peuvent pas être réalisées dans MySQL. Toutefois, si les jointures à grande échelle entre plusieurs tables et les requêtes analytiques constituent la charge de travail principale, il est réaliste de comparer à l’avance le temps que vous pouvez consacrer au réglage, si les analyses doivent être déportées vers des réplicas et s’il faut ajouter un système analytique dédié. À l’inverse, cette charge peut être relativement moindre pour un service composé principalement de transactions courtes et prévisibles.

Quand faut-il être prudent avec les routines stockées ?

Les routines stockées sont des procédures ou fonctions stockées et exécutées sur le serveur de base de données. Elles peuvent conserver certaines règles de traitement des données près de la base de données, mais les fonctions stockées utilisables dans les instructions SQL sont soumises à des restrictions. Par exemple, une fonction stockée ne peut pas utiliser une instruction qui retourne un jeu de résultats. Une fonction calculant une unique valeur de retour et une opération de requête retournant plusieurs lignes ont des objectifs et des modes d’utilisation différents. dev.mysql.com↗ dev.mysql.com↗

Le déterminisme des routines stockées est également important dans les environnements de réplication. Déterministe signifie produire le même résultat pour la même entrée. Les routines non déterministes ou dépendantes du temps, qui varient selon l’heure ou l’état de l’environnement, peuvent créer des problèmes de reproductibilité selon la méthode de réplication, ce qui exige une attention particulière avec la réplication basée sur les instructions. Lorsque vous placez de la logique métier dans la base de données, vous devez aussi vérifier si cette logique peut produire le même résultat pendant la réplication et la récupération après incident. dev.mysql.com↗

La décision d’utiliser ou non des routines stockées dépend moins de l’existence d’une fonctionnalité que de l’emplacement de la responsabilité des modifications, des tests et du déploiement. Lorsque les règles sont réparties entre le code applicatif et les routines de base de données, le traçage et les tests peuvent devenir plus complexes. À l’inverse, elles peuvent être utiles pour des règles simples proches de l’intégrité des données. La question essentielle est de savoir si l’équipe peut comprendre et gérer le lieu d’exécution de ces règles ainsi que leur impact sur la réplication.

Quand MySQL est-il adapté, et quand faut-il faire preuve de prudence ?

Le tableau suivant n’est pas un classement de produits. Il offre une perspective permettant de vérifier l’adéquation entre les exigences et les fonctionnalités.

Les trois premières lignes du tableau reposent sur les fonctionnalités officielles d’InnoDB, de la réplication et de Group Replication. Les deux dernières reflètent également le comportement et les restrictions du partitionnement et de l’optimiseur. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗

En particulier, si une forte cohérence multi-région ou un basculement ininterrompu est une exigence centrale, vous ne devez pas prendre de décision en vous fondant uniquement sur la réplication asynchrone par défaut. Précisez les exigences de fraîcheur, la latence acceptable, la possibilité ou non de poursuivre les écritures durant les pannes et le chemin de basculement applicatif, puis comparez Group Replication, NDB Cluster ou d’autres options distribuées. À l’inverse, si vous souhaitez traiter de manière fiable des transactions lecture-écriture classiques au sein d’une seule zone de service et distribuer les lectures vers des réplicas lorsque nécessaire, la combinaison de fonctionnalités de MySQL peut constituer un point de départ pratique. dev.mysql.com↗ dev.mysql.com↗

Que faut-il vérifier avant l’adoption ?

Commencez par vérifier que les tables centrales utilisent InnoDB et que les limites de transaction correspondent aux unités métier. Les modifications qui doivent réussir ou échouer ensemble, telles que la création d’une commande, doivent être définies comme une transaction unique, tout en évitant les transactions inutilement longues qui augmentent la durée des verrous. Deuxièmement, listez les requêtes de lecture et d’écriture les plus fréquentes, et vérifiez que les index requis correspondent aux prédicats réels et à la méthode de tri. dev.mysql.com↗ dev.mysql.com↗

Troisièmement, si vous utilisez la réplication, décidez « quelles lectures sont autorisées depuis les réplicas ». Une approche consiste à distinguer les requêtes nécessitant de la fraîcheur, telles que la vérification d’un statut immédiatement après un paiement, des requêtes de listes et de statistiques pouvant tolérer un certain délai. Quatrièmement, si une configuration de haute disponibilité est requise, testez les scénarios de panne non seulement pour l’élection des membres de base de données, mais aussi pour l’emplacement vers lequel les connexions applicatives se déplacent réellement. dev.mysql.com↗ dev.mysql.com↗

Cinquièmement, supposez que les données vont croître et vérifiez si le partitionnement est véritablement nécessaire et si vous pouvez accepter les contraintes liées aux clés étrangères et aux clés uniques. Si vous exigez simultanément la recherche dans de longues chaînes, la recherche en texte intégral et le partitionnement, examinez d’abord les limitations entre ces fonctionnalités. Enfin, si les jointures complexes sont centrales, inspectez EXPLAIN dans des conditions proches des données de production et évaluez si vous avez la capacité de gérer continuellement les statistiques et les changements d’index. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗

Conclusion : comment évaluer les avantages et les inconvénients de MySQL ?

Les points forts de MySQL incluent le contrôle des transactions et de la concurrence fondé sur InnoDB, l’intégration avec un large éventail d’environnements de développement, la distribution des lectures via la réplication et les fonctionnalités officielles permettant de construire des configurations de haute disponibilité. Ces éléments peuvent fournir une base significative pour les services web généraux et les charges de travail OLTP classiques. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗

Dans le même temps, le retard potentiel de la réplication par défaut, la conception supplémentaire nécessaire au basculement des connexions en haute disponibilité, la validation des plans d’exécution pour les requêtes complexes, ainsi que les contraintes impliquant les index, le partitionnement et la recherche en texte intégral doivent être considérés comme des coûts réels. En définitive, MySQL n’est pas un choix aux qualités universellement positives. C’est une base de données dont l’adéquation peut être évaluée lorsque les exigences de cohérence des données, le ratio lecture-écriture, les contraintes de schéma, le niveau de réponse aux pannes et la capacité de réglage et d’exploitation sont précisés. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗