Table des matières de l'article :
L'un des aspects les plus frustrants d'être un ingénieur système est de devoir se salir les mains dans des tâches que d'autres professionnels devraient faire.
En effet, il peut arriver qu'après avoir livré un serveur bien dimensionné sur le matériel et bien configuré au niveau logiciel que le client se plaint de performances absolument insuffisantes de l'application qui s'exécute dessus.
Qu'il s'agisse d'un CMS personnalisé, d'un CRM personnalisé ou de toute autre application Web commandée et développée sur mesure selon les besoins du client, l'essentiel est que le logiciel ne fonctionne pas comme il le devrait.
Souvent après les plaintes initiales et calmes, alors que la situation s'aggrave et continue, nous pouvons nous exclamer avec un ton agacé que "Rien ne fonctionneSouvent avec des épithètes, un ton et une terminologie de bar du port.
Après une réponse normale et calme dans laquelle il est souligné à quel point cette configuration est plus que suffisante pour les besoins du client, nous arrivons à l'invitation du client à discuter avec le développeur du logiciel car peut-être que quelque chose est devenu incontrôlable et qu'il s'agit d'une application personnalisée sans spécificité et les cas documentés (puisque le logiciel n'est pas connu ni répandu) pourraient présenter des problèmes critiques manifestement inconnus de nous et non attribuables.
Evidemment pour le programmeur, dans 99% des cas de ce type, le problème n'est jamais dans son logiciel, mais toujours imputable à l'administrateur système qui ne sait pas configurer un serveur.
Et la voici, la plus redoutée des batailles : ingénieur système contre développeur.
L'ingénieur système est généralement une ceinture noire XNUMXe dan d'UNIX, a très probablement une formation en tant que développeur et DBA. C'est le potentiel Bruce Lee capable de vous renverser d'un coup qui préfère TOUJOURS éluder le défi, faute de temps mais surtout de compétences.
Et il est clair, que par compétences, on n'entend pas les compétences théoriques et pratiques ou académiques pour pouvoir analyser brillamment le problème et par conséquent le résoudre, mais ce n'est tout simplement pas de sa compétence, ou que cette tâche a été confiée à une autre personnalité estimée d'une autre entreprise qui est grassement payé pour concevoir et développer des logiciels fonctionnels et bien optimisés.
Dommage que dans la triste vie de l'informaticien et surtout de l'ingénieur système, le jeu du blâme soit toujours au coin de la rue et donc si vous n'acceptez pas le défi à mort, vous serez toujours étiqueté comme celui qui ne le fait pas. savoir comment faire son travail, de même que vous perdrez le client qui ira à une autre entreprise de fumeurs jusqu'à ce qu'un ingénieur système ceinture noire décide de se salir les mains et de prouver une fois pour toutes que tous les problèmes sont venus d'un mauvais mise en œuvre du logiciel.
Non seulement vous aurez sauvé la vie de votre client qui se serait retrouvé avec un logiciel inutilisable, mais aussi du programmeur qui, grâce à vos conseils et vos interventions, aura bénéficié d'une amélioration de son logiciel qu'il pourra même revendre à d'autres les clients.
La vérité? Vous ne savez pas comment utiliser MySQL, et encore moins SQL en général.
Un phénomène intéressant émerge chez de nombreux développeurs LAMP (acronyme désignant la combinaison des technologies Linux, Apache, MySQL, PHP) : peu ou pas de connaissance de l'utilisation de MySQL. Bien que MySQL ait atteint sa huitième version, avec un ensemble de caractéristiques et de fonctionnalités qui pourraient rivaliser avec SQL Server, PostgreSQL ou Oracle de Microsoft, on observe qu'une partie importante des développeurs continue de l'utiliser comme s'il en était encore à la version 3.23.
Cette version, datant de 2001, ne supportait pas les fonctionnalités avancées telles que les contraintes d'intégrité référentielle, les procédures stockées, les fonctions stockées, les triggers, les vues ou les transactions, qui sont aujourd'hui disponibles et sont de puissants outils de gestion et d'analyse des données. Par conséquent, de nombreux développeurs n'exploitent pas pleinement le potentiel offert par MySQL, se limitant à l'utiliser de manière démodée et sous-optimale.
Il est important de rappeler à ces développeurs que l'époque où MySQL était limité dans ses capacités est révolue depuis longtemps. Le moteur MyISAM, qui caractérisait la version 3.23, a été remplacé par InnoDB, qui est considérablement plus performant et adapté à un usage professionnel. De plus, des fourches de MySQL ont été développées, telles que XtraDB de Percona Server, qui ont encore amélioré ses capacités, ce qui en fait un SGBD relationnel extrêmement puissant (Database Management System).
Il est donc essentiel que les développeurs se mettent à jour et se forment adéquatement pour profiter pleinement du potentiel offert par MySQL. Cette montée en compétences vous permettra non seulement de créer des applications plus performantes et robustes, mais également de vous tenir davantage en phase avec l'évolution du domaine du génie logiciel, qui est en constante évolution.
Personnellement, nous n'avons rien contre MySQL ou MariaDB, mais actuellement, nous avons standardisé pratiquement tous nos serveurs avec Percona Server qui dispose également d'outils accessoires tels que Percona Utilities ou Percona Xtrabackup pour effectuer des sauvegardes à chaud de manière beaucoup plus rapide, plus sûre, plus cohérente et plus efficace que mysqldump banal habituel.
Pour donner un petit exemple, un programmeur aujourd'hui est convaincu qu'un DMBS comme MySQL est un simple tiroir dans lequel stocker et récupérer des données. En d'autres termes, ils utilisent un SGBD très puissant (et donc lourd) comme s'il s'agissait presque d'une base de données NO-SQL et donc légère.
Un SGBD comme MySQL aujourd'hui est plutôt comparable à une ligne de production industrielle avancée qu'à une commode très banale. Bref, ces grandes chaînes de production de bonbons, où sont mis 3 ingrédients principaux et un colorant, au bout du carrousel sortent des paquets d'oursons en gélatine, coupés, mélangés, emballés et prêts à être chargés sur les camions de livraison.
Par exemple, peu de développeurs LAMP savent que MySQL est très puissant et permet de gérer la plupart des opérations qui se font au niveau de l'application, directement au niveau de la base de données puisque MySQL lui-même possède un langage de programmation interne.
Voyons quelques-unes des principales lacunes ou mauvaises pratiques de ceux qui développent des applications Web à l'aide de MySQL.
La lecture suivante peut transformer n'importe quel ancien développeur Web recyclé peracottaio au chômage en un bon développeur et peut-être même un bon DBA si vous continuez sur cette voie.
1. Clés étrangères et intégrité référentielle
Il existe une croyance commune, en particulier parmi les programmeurs qui s'appuient uniquement sur le langage de programmation PHP, selon laquelle la seule façon de supprimer des données interconnectées dans une base de données passe par la couche application. Cette croyance les incite à mettre en œuvre un processus dans lequel ils interrogent les données qu'ils souhaitent supprimer, les suppriment une par une, puis ne reviennent qu'à la table parente pour supprimer l'élément d'origine. Ils ont également une vision similaire si vous souhaitez utiliser une formule mathématique pour effectuer un calcul complexe : selon eux, cela ne devrait être fait que par PHP, qui produira un résultat qui sera ensuite stocké dans la base de données.
Cependant, ce qui est souvent négligé, c'est la puissance des clés étrangères et des fonctionnalités d'intégrité référentielle que l'on trouve dans les systèmes de gestion de bases de données relationnelles (RDBMS). Par exemple, si vous souhaitez supprimer un artiste et tous ses albums d'une hypothétique base de données d'archives musicales, vous pouvez utiliser l'action ON CASCADE DELETE. Cette fonctionnalité permet d'effectuer une suppression référencée à la suppression de la clé étrangère correspondante, en évitant d'avoir à supprimer chaque valeur liée individuellement.
Dans un contexte RDBMS, l'intégrité référentielle représente une contrainte d'intégrité interrelationnelle, qui exige que chaque valeur d'un attribut (ou colonne) d'une relation (ou table) soit présente en tant que valeur d'un autre attribut dans une autre relation. En termes plus simples, dans les bases de données relationnelles, pour maintenir l'intégrité référentielle, chaque champ d'une table qui a été déclaré comme clé étrangère ne peut contenir que des valeurs qui correspondent à la clé primaire ou à une clé candidate d'une autre table liée.
Pour illustrer cela, considérons le cas où vous souhaitez supprimer un enregistrement qui contient une valeur référencée par une clé étrangère d'une autre table. Cette action violerait l'intégrité référentielle. Cependant, certains SGBDR peuvent empêcher cette violation, soit en supprimant les lignes correspondantes de la clé étrangère, soit en abandonnant l'opération et en ne procédant pas à la suppression. Cela sert à préserver l'intégrité des données dans la base de données, en soulignant l'importance des clés étrangères et de l'intégrité référentielle.
2. Procédure stockée
Les procédures stockées sont l'une des principales caractéristiques de nombreux systèmes de gestion de bases de données relationnelles (RDBMS). Cette fonctionnalité, auparavant absente des versions antérieures à la 5.0 de MySQL, a finalement été implémentée, répondant ainsi aux critiques de nombreux utilisateurs de cette plateforme.
Une procédure stockée peut être définie comme une série d'instructions SQL qui sont stockées directement dans la base de données avec un nom unique pour les identifier. Ce nom permet d'appeler et d'exécuter l'ensemble des instructions, éliminant ainsi le besoin de réécrire l'intégralité du code chaque fois que la même opération doit être effectuée.
Une procédure stockée peut avoir un ou plusieurs paramètres, qui sont utilisés pour personnaliser l'opération effectuée par la procédure. Chaque paramètre a un nom spécifique, un type de données défini et peut être classé comme paramètre d'entrée, paramètre de sortie ou les deux. S'il n'est pas spécifié, le paramètre est considéré par défaut comme un paramètre d'entrée.
Les procédures stockées offrent de nombreux avantages. Tout d'abord, ils peuvent aider à réduire le trafic réseau, car un seul appel de procédure remplace la nécessité d'émettre plusieurs requêtes. De plus, étant donné que le code s'exécute directement dans la base de données, la procédure stockée peut être plus rapide qu'un ensemble équivalent d'instructions SQL exécutées individuellement. Enfin, l'utilisation de procédures stockées peut aider à améliorer la sécurité, car les procédures stockées fournissent une couche d'abstraction qui masque les détails de la base de données.
3. Fonctions stockées
Les fonctions stockées partagent certaines similitudes avec les procédures stockées dans une base de données, mais ont un objectif spécifique plus limité. Alors qu'une procédure stockée est un groupe d'instructions SQL pouvant effectuer des opérations complexes, une fonction stockée est conçue pour effectuer une opération spécifique et renvoyer une valeur unique.
En pratique, une fonction stockée fonctionne exactement comme une fonction ordinaire fournie par MySQL ou un autre SGBDR. Ces fonctions prennent un ou plusieurs paramètres, effectuent une opération et renvoient un résultat. Par exemple, ils peuvent calculer la somme d'une série de nombres, renvoyer la longueur d'une chaîne de texte ou effectuer un calcul plus complexe basé sur les données de la base de données.
Contrairement aux procédures stockées, cependant, les fonctions stockées ne peuvent pas renvoyer un jeu de résultats, qui est un ensemble de lignes renvoyées par une requête SELECT. Au lieu de cela, ils renvoient une valeur unique d'un type de données spécifique, tel qu'un entier, un flottant, une chaîne ou une date.
Dans les versions de MySQL antérieures à 5.0, certaines fonctions définies par l'utilisateur étaient stockées en dehors du serveur. Ces fonctions utilisateur personnalisées ont permis aux développeurs de créer leurs propres fonctions et de les utiliser dans leurs requêtes SQL. Cependant, à partir de la version 5.0, MySQL a introduit des fonctions stockées, qui sont stockées directement dans la base de données.
Bien que les fonctions définies par l'utilisateur soient toujours prises en charge dans MySQL, l'utilisation de fonctions stockées est généralement recommandée. En effet, les fonctions stockées peuvent tirer parti de certaines optimisations de MySQL et avoir moins de restrictions que les fonctions définies par l'utilisateur. Par exemple, une fonction stockée peut accéder à des tables, des variables de session et d'autres ressources de base de données, tandis qu'une fonction définie par l'utilisateur a des limites plus strictes sur ce qu'elle peut faire.
Essentiellement, les fonctions stockées fournissent un moyen puissant et efficace d'encapsuler la logique métier dans la base de données, permettant aux développeurs d'écrire du code plus propre et plus maintenable.
4. Déclencheurs
Les déclencheurs sont des objets spéciaux dans les bases de données relationnelles, étroitement associés à des tables spécifiques, qui s'activent ou « se déclenchent » en réponse à certains événements. Cette fonctionnalité a été introduite dans MySQL à partir de la version 5.0.2.
Un déclencheur peut être défini pour répondre à divers événements affectant la table à laquelle il est associé, tels que l'insertion de nouvelles lignes, la modification de lignes existantes ou la suppression de lignes. Un déclencheur peut être configuré pour se déclencher avant (AVANT) ou après (APRES) que l'événement se produise. Nous avons donc six types de déclencheurs distincts :
- AVANT INSÉRER
- APRÈS INSERTION
- AVANT LA MISE À JOUR
- APRÈS LA MISE À JOUR
- AVANT DE SUPPRIMER
- APRÈS SUPPRIMER
Chaque déclencheur est associé à une instruction SQL ou à un bloc d'instructions qui sont automatiquement exécutées pour chaque ligne affectée par l'événement. En d'autres termes, un déclencheur est un mécanisme qui permet de définir une réponse automatique à un événement spécifique. Cette réponse peut consister en diverses actions, telles que la mise à jour d'une valeur dans une colonne spécifique, l'insertion d'une nouvelle ligne dans une autre table ou la journalisation de l'événement dans un journal.
Prenons un exemple pratique. Supposons que vous souhaitiez suivre les ventes totales d'un commercial au cours d'un mois donné. Nous pourrions créer un déclencheur qui répond à l'événement "insérer une nouvelle ligne" sur la table "commandes". Lorsqu'une nouvelle commande est saisie, le déclencheur calcule automatiquement la somme totale des commandes du mois en cours pour l'agent spécifique et met à jour la valeur dans la table "total_monthly_sales". De cette façon, nous n'aurions pas besoin d'exécuter une requête SELECT à chaque fois que nous voudrions connaître les ventes totales du mois pour un agent, car la valeur serait pré-calculée et immédiatement disponible.
Les déclencheurs peuvent grandement simplifier la logique de l'application en automatisant des tâches qui, autrement, devraient être effectuées manuellement. Cependant, ils doivent être utilisés avec prudence, car ils peuvent compliquer le débogage et la maintenance de la base de données s'ils ne sont pas gérés correctement.
5. Vues
Les vues d'une base de données représentent une perspective virtualisée sur un sous-ensemble de données contenues dans une ou plusieurs tables. Traduit littéralement du terme anglais "view", qui signifie "vue", les vues ne stockent pas physiquement les données, mais offrent une présentation tabulaire de lignes et de colonnes dérivées de l'exécution d'une requête SQL spécifique. Les vues fonctionnent comme s'il s'agissait de "vraies" tables, vous permettant d'effectuer des opérations telles que SELECT, UPDATE, INSERT ou DELETE, selon les autorisations et les restrictions que vous définissez.
La création d'une vue implique la définition d'une requête qui "sélectionne" un sous-ensemble de données dans une ou plusieurs tables. Cette requête est stockée dans le cadre de la définition de la vue et s'exécute chaque fois que la vue est appelée. Essentiellement, une vue fonctionne comme une fenêtre sur un ensemble spécifique de données, filtrant ou réorganisant les informations en fonction des besoins spécifiques de l'utilisateur ou de l'application.
L'utilisation des vues présente plusieurs avantages. Premièrement, les vues peuvent simplifier les opérations de requête en masquant la complexité des opérations de jointure, de filtrage ou d'agrégation qui peuvent être impliquées dans la génération du sous-ensemble de données. Deuxièmement, les vues peuvent aider à améliorer la sécurité des données en limitant l'accès à un sous-ensemble de données et en masquant les détails sensibles ou non pertinents. Enfin, les vues peuvent aider à maintenir l'intégrité des données en fournissant une interface cohérente avec les données même lorsque la structure sous-jacente des tables change.
Un exemple de vue peut être une vue « CustomerOrders » qui regroupe les informations d'une table « Orders » et d'une table « Customers », affichant uniquement les détails de la commande et les informations client de base pour chaque commande. Cette vue pourrait être utilisée par une application de vente pour afficher une liste de commandes sans effectuer manuellement de filtrage ou de jointure complexe.
Les vues sont des outils puissants qui facilitent, sécurisent et gèrent l'interaction avec les données d'une base de données.
6. Opérations ACID
L'utilisation des transactions dans les bases de données est une fonction critique qui permet de gérer un ensemble d'opérations de manière sécurisée et cohérente, garantissant que les modifications apportées aux données ne sont effectuées qu'à un moment bien défini. En lançant une transaction, toutes les modifications ou mises à jour des données restent dans un état temporaire et non final (suspendues) et ne sont pas visibles pour les autres utilisateurs. Ces modifications peuvent ensuite être soit confirmées par une opération appelée "commit", qui rend les modifications permanentes, soit annulées par une opération appelée "rollback", qui remet les données dans leur état d'origine. Ce concept est souvent décrit comme « tout ou rien » : soit tous les changements prévus par la transaction sont effectués, soit aucun ne l'est.
Ce processus de gestion des transactions est essentiel dans les situations où le maintien de la cohérence des données est essentiel. Par exemple, si vous avez 100 enregistrements à entrer dans une commande de restaurant et que l'entrée s'arrête au 90e en raison d'un problème de connexion Wi-Fi, le système annulera la transaction, annulant les 90 entrées car elles se terminent par rapport au total de 100 attendu.
Cette façon de traiter les transactions suit les principes ACID, un acronyme qui signifie Atomicité, Cohérence, Isolement et Durabilité:
- Atomicité: Ce principe garantit qu'une transaction est considérée comme une unité de travail indivisible. Soit toutes les opérations de la transaction sont terminées avec succès, soit aucune ne l'est. Si une opération au sein d'une transaction échoue, la totalité de la transaction est annulée (rollback).
- Cohérence: Ce principe garantit qu'une transaction fait passer la base de données d'un état cohérent à un autre. Si les données de la base de données étaient cohérentes avant la transaction, elles le seront après la transaction, quel que soit le succès ou l'échec de la transaction.
- Isolement: Ce principe garantit que chaque transaction s'exécute indépendamment des autres transactions. Cela signifie qu'aucune transaction ne peut interférer avec les autres, et chacune doit être réalisée sans connaissance des autres transactions en cours.
- Durabilité: Ce principe garantit qu'une fois qu'une transaction est terminée, ses modifications de données persistent dans la base de données, même en cas de panne ou de plantage du système.
Ensemble, ces principes ACID fournissent un cadre robuste pour gérer les transactions et garantir l'intégrité des données dans les bases de données relationnelles.
Et n'oublions pas les performances.
Si on voulait parler de vitesse d'exécution des requêtes, on pourrait dire que de moins en moins de développeurs LAMP savent concevoir correctement une base de données et implémenter une utilisation correcte des index qui sont absolument indispensables si l'on veut accélérer l'exécution des requêtes elles-mêmes.
Autrement dit, très souvent des applications lentes qui arrivent au timeout de connexion et même à la saturation du maximum de connexions disponibles ont pour seule cause le manque d'index sur les tables.
Un index de base de données est une structure de données qui améliore la vitesse des opérations sur une table.
Chaque fois qu'une application Web envoie une requête à une base de données, la base de données recherchera toutes les lignes de votre table pour trouver celle qui correspond à votre demande. Au fur et à mesure que vos tables de base de données grandissent, davantage de lignes doivent être inspectées à chaque fois, ce qui peut ralentir les performances de la base de données et donc de l'application.
Les index MySQL résolvent ce problème en prenant les données d'une colonne de votre table et en les stockant par ordre alphabétique dans un point séparé appelé index.
Est-ce que ça marche ? Non. Il faut aussi que ce soit rapide.
Il semble que, dans le cadre du développement LAMP (Linux, Apache, MySQL, PHP), l'un des principaux problèmes réside dans l'équilibre entre la programmation PHP et la conception et l'optimisation des bases de données. En regardant les tendances générales, on remarque que les développeurs ont souvent tendance à se concentrer davantage sur la programmation PHP, négligeant l'importance d'une base de données bien conçue et optimisée.
Une des raisons de cette tendance peut résider dans un manque de formation académique pertinente, ce qui conduit le développeur à se limiter à tester le logiciel sur de très petits jeux de données et dans des contextes de faible concurrence. Dans ces conditions, il est facile de conclure que le logiciel fonctionne correctement et que les requêtes renvoient les résultats attendus. Cependant, ce type de test n'est pas suffisamment représentatif de la réalité opérationnelle dans laquelle le logiciel sera effectivement utilisé.
En fait, c'est une chose de tester une application sur un jeu de données de 100 enregistrements de démonstration avec un seul utilisateur connecté, et c'en est une autre de la tester sur un jeu de données de dizaines de millions d'enregistrements avec 100 opérateurs connectés en temps réel. Ce dernier scénario présente des défis importants en termes de gestion de la concurrence et d'efficacité des opérations basées sur les données, défis qui n'apparaissent pas dans un environnement de test limité.
La morale de l'histoire est donc que "fonctionner" ne suffit pas à décrire un bon logiciel. Un logiciel doit être non seulement fonctionnel, mais aussi efficace. Il doit être capable de gérer de gros volumes de données et un nombre élevé d'utilisateurs connectés simultanément, en renvoyant des résultats rapidement et sans erreur. En d'autres termes, il ne suffit pas que cela fonctionne, il faut aussi que cela soit rapide.
Pour atteindre ces objectifs, les développeurs doivent considérer l'optimisation de la base de données comme une partie essentielle du processus de développement, et non comme une activité secondaire. Cela prend du temps et de la formation, mais le résultat sera un logiciel plus robuste, plus efficace et finalement plus utile pour les utilisateurs finaux.
