Cas pratiques MySQL

Contexte : La table orders contient plusieurs milliers d’enregistrements. Le Problème : Les recherches par customer_id sont lentes. Contraintes : Ajouter un index sur la colonne customer_id sans modifier les données existantes. Livrable attendu : Fournir la requête SQL permettant de créer un index adapté pour améliorer les performances.

Contexte : Une requête doit retourner uniquement les utilisateurs actifs. Le Problème : La requête actuelle retourne tous les enregistrements. Requête actuelle : SELECT * FROM users WHERE active = 'true'; Contraintes : Adapter la requête en supposant que la colonne active est de type TINYINT(1). Livrable attendu : Fournir la requête corrigée retournant uniquement les utilisateurs actifs.

Contexte : Une application interne utilise MySQL pour stocker les informations clients. Le Problème : Une requête simple échoue avec une erreur de syntaxe lors de son exécution. Contraintes : Corriger uniquement la requête SQL sans modifier la structure de la table. Requête actuelle : SELECT id name, email FROM customers WHERE active = 1; Livrable attendu : Fournir la version corrigée de la requête permettant de récupérer correctement les colonnes id, name et email.

Contexte : Une application doit se connecter à MySQL avec un compte dédié. Le Problème : Aucun utilisateur spécifique n’a été créé pour cette application. Contraintes : Créer un utilisateur appuser avec mot de passe et lui accorder des privilèges uniquement sur la base project_db. Livrable attendu : Fournir les commandes SQL permettant de créer l’utilisateur et d’accorder les privilèges nécessaires.

Contexte : Un nouveau projet nécessite une base de données isolée dans MySQL. Le Problème : Aucune base spécifique n’a été créée pour ce projet. Contraintes : Créer une base nommée project_db avec encodage UTF8MB4. Livrable attendu : Fournir la commande SQL permettant de créer correctement la base de données avec le bon charset.

Contexte : Une nouvelle fonctionnalité nécessite le stockage des produits dans la base MySQL. Le Problème : Aucune table n’existe encore pour stocker ces données. Contraintes : Créer une table products avec id auto-incrémenté en clé primaire, name obligatoire et price de type DECIMAL. Livrable attendu : Fournir la requête CREATE TABLE complète incluant les contraintes adaptées.

Contexte : La table users contient les colonnes id, username et email. Le Problème : Une requête INSERT échoue car le nombre de colonnes ne correspond pas aux valeurs fournies. Contraintes : Corriger la requête sans modifier la structure de la table. Requête actuelle : INSERT INTO users (username, email) VALUES ('john'); Livrable attendu : Fournir une requête INSERT valide permettant l’ajout correct d’un utilisateur.

Contexte : Un client a modifié son adresse email. Le Problème : L’ancienne valeur doit être remplacée dans la table customers. Contraintes : Mettre à jour uniquement l’enregistrement dont l’id est égal à 15. Livrable attendu : Fournir la requête UPDATE permettant de modifier l’email du client concerné.

Contexte : L’équipe souhaite connaître le nombre total de produits enregistrés. Le Problème : Aucun indicateur simple n’est disponible. Contraintes : Utiliser une requête SQL simple sans modifier les données. Livrable attendu : Fournir la requête permettant de compter le nombre total de lignes dans la table products.

Contexte : Une table temporaire test_data a été créée durant des tests. Le Problème : Elle doit être supprimée sans provoquer d’erreur si elle n’existe pas. Contraintes : Utiliser une requête SQL sécurisée. Livrable attendu : Fournir la commande SQL permettant de supprimer la table uniquement si elle existe.

Contexte : Une entreprise souhaite garder une trace des modifications effectuées sur la table products. Le Problème : Il n’y a actuellement aucun mécanisme d’audit des mises à jour. Contraintes : Créer un trigger avant toute mise à jour qui enregistre les anciennes valeurs dans une table d’audit. Livrable attendu : Fournir la requête SQL pour créer un trigger permettant d’auditer les mises à jour sur la table products.

Contexte : Une vue doit être créée pour afficher les informations des clients et leurs commandes. Le Problème : Les données sont stockées dans deux tables distinctes : customers et orders. Contraintes : Créer une vue SQL qui effectue une jointure entre les deux tables et affiche le nom du client et la date de commande. Livrable attendu : Fournir la requête SQL pour créer une vue avec la jointure appropriée.

Contexte : Une série d’insertion dans plusieurs tables doit être exécutée dans une seule transaction. Le Problème : Si une des insertions échoue, la transaction entière doit être annulée. Contraintes : Créer une procédure stockée avec une gestion transactionnelle permettant le commit ou rollback selon le succès des opérations. Livrable attendu : Fournir la procédure stockée avec gestion des transactions et des erreurs, utilisant COMMIT et ROLLBACK.

Contexte : Un utilisateur doit être créé pour accéder à la base de données des ventes sans pouvoir modifier les données sensibles. Le Problème : Le rôle actuel accorde trop de privilèges. Contraintes : Créer un utilisateur avec des privilèges en lecture seule sur la table sales. Livrable attendu : Fournir la requête SQL permettant de créer l’utilisateur et de lui accorder uniquement des privilèges SELECT sur la table sales.

Contexte : Un client veut modifier son adresse email, mais seulement s’il a effectué un achat récent. Le Problème : La requête de mise à jour doit vérifier l’historique des achats avant de procéder. Contraintes : Utiliser une sous-requête pour vérifier l’achat effectué dans les 30 derniers jours. Livrable attendu : Fournir la requête SQL permettant de mettre à jour l'email d'un client seulement s’il a effectué un achat récent.

Contexte : Une table orders contient des millions de lignes et plusieurs requêtes filtrent par customer_id et order_date. Le Problème : Ces requêtes deviennent lentes en raison du volume de données. Contraintes : Ajouter un index composite sur les colonnes customer_id et order_date sans affecter les autres requêtes. Livrable attendu : Fournir la requête SQL permettant de créer un index composite et expliquer pourquoi cet index améliore les performances.

Contexte : Une table employees contient une colonne department_id qui est fréquemment utilisée pour filtrer les résultats. Le Problème : Les requêtes utilisant cette colonne prennent trop de temps. Contraintes : Ajouter un index sur la colonne department_id sans modifier la structure de la table. Livrable attendu : Fournir la requête SQL permettant de créer un index sur department_id et expliquer pourquoi cet index améliore les performances.

Contexte : La table transactions contient des millions de lignes et les requêtes sur certaines périodes prennent trop de temps. Le Problème : Les requêtes filtrant par date sont lentes. Contraintes : Implémenter un partitionnement basé sur la date pour améliorer les performances des requêtes sur les anciennes transactions. Livrable attendu : Fournir la stratégie de partitionnement des données et la commande SQL pour partitionner la table transactions par date.

Contexte : Une application doit supprimer automatiquement les commandes plus anciennes que 2 ans pour libérer de l’espace. Le Problème : Les anciennes données ne sont pas supprimées régulièrement. Contraintes : Utiliser une condition de date pour supprimer les commandes plus anciennes que 2 ans sans affecter les commandes récentes. Livrable attendu : Fournir la requête SQL permettant de supprimer ces anciennes commandes.

Contexte : L’équipe marketing souhaite savoir combien de clients ont effectué un achat au cours du dernier mois. Le Problème : La table des commandes est volumineuse et les statistiques doivent être calculées rapidement. Contraintes : Utiliser une requête COUNT avec une condition de date pour obtenir le nombre total de clients ayant passé commande en janvier. Livrable attendu : Fournir la requête COUNT adaptée pour obtenir ce total.

Contexte : Une application critique repose sur une instance MySQL unique en production. Le Problème : Toute panne du serveur entraîne une interruption complète du service. Contraintes : Mettre en place une réplication primaire-réplique avec bascule automatique sans modifier le code applicatif. Garantir la cohérence des données et minimiser le downtime. Livrable attendu : Proposer une architecture détaillée incluant configuration de la réplication (binlog, server-id), stratégie de failover (ex: MHA ou Orchestrator) et étapes de validation.

Contexte : Une entreprise doit tracer toutes les modifications sur une table sensible contenant des données financières. Le Problème : Aucun mécanisme d’audit détaillé n’est en place. Contraintes : Mettre en place un système d’audit combinant logs MySQL et triggers pour enregistrer les anciennes valeurs. Livrable attendu : Proposer une architecture d’audit incluant configuration des logs et création de triggers adaptés.

Contexte : Une application financière subit des incohérences de données lors d’accès concurrents. Le Problème : Les phénomènes de dirty reads ou phantom reads perturbent la logique métier. Contraintes : Ajuster le niveau d’isolation transactionnelle et tester les effets sans compromettre les performances. Livrable attendu : Décrire les différents isolation levels MySQL et proposer le niveau adapté avec justification technique.

Contexte : Une base MySQL atteint ses limites en termes de volume et de performance. Le Problème : Les requêtes deviennent lentes malgré les optimisations classiques. Contraintes : Concevoir une stratégie de sharding horizontal basée sur un identifiant client sans casser la logique métier. Livrable attendu : Proposer une architecture de sharding, définir la clé de distribution et expliquer la gestion des requêtes multi-shards.

Contexte : Plusieurs bases MySQL régionales doivent consolider leurs données vers un serveur central. Le Problème : Les données sont actuellement isolées et aucune synchronisation automatique n’existe. Contraintes : Mettre en place une réplication multi-source fiable garantissant la cohérence et la gestion des conflits. Livrable attendu : Proposer une architecture détaillée incluant configuration multi-source replication, gestion des conflits et validation de la cohérence des données.

Contexte : Une table transactions dépasse 100 millions de lignes et continue de croître. Le Problème : Les requêtes historiques et les opérations de maintenance deviennent lentes. Contraintes : Implémenter un partitionnement RANGE basé sur une colonne de type DATE sans perte de données et avec compatibilité applicative. Livrable attendu : Proposer la stratégie de migration vers une table partitionnée et les commandes SQL nécessaires pour créer et gérer les partitions.

Contexte : Une entreprise souhaite réduire la durée des sauvegardes complètes tout en garantissant une restauration précise. Le Problème : Les sauvegardes full quotidiennes sont longues et consomment beaucoup d’espace disque. Contraintes : Utiliser mysqldump pour une sauvegarde initiale et exploiter les binary logs pour les sauvegardes incrémentielles. Livrable attendu : Décrire la stratégie complète incluant configuration des binlogs, procédure de sauvegarde et restauration point-in-time.

Contexte : Une requête impliquant plusieurs jointures sur des tables volumineuses provoque une forte latence. Le Problème : Le serveur effectue des full table scans coûteux et la consommation CPU augmente. Contraintes : Utiliser EXPLAIN ANALYZE pour identifier les goulots d’étranglement et optimiser via index adaptés ou réécriture de requête. Livrable attendu : Décrire une méthodologie complète d’analyse du plan d’exécution et proposer des optimisations concrètes justifiées.

Contexte : Plusieurs index existent sur une table volumineuse mais les performances restent médiocres. Le Problème : Certains index ne sont pas utilisés ou ont une faible sélectivité. Contraintes : Analyser la cardinalité des index existants et proposer une stratégie de suppression ou de création d’index plus pertinents. Livrable attendu : Décrire la méthodologie d’analyse et fournir des recommandations d’optimisation justifiées.

Contexte : Un serveur MySQL subit une charge élevée avec de nombreuses écritures concurrentes. Le Problème : Les performances se dégradent en raison d’un mauvais dimensionnement des buffers. Contraintes : Ajuster des paramètres comme innodb_buffer_pool_size, innodb_log_file_size et max_connections sans interrompre durablement le service. Livrable attendu : Proposer une stratégie de tuning basée sur l’analyse des métriques serveur et expliquer l’impact des paramètres ajustés.