jmaxzh/sdlc-conception-mysql

Conception de Tables MySQL et de Requêtes SQL

Applicabilité

• Appliquer lorsque la tâche ou le projet établit MySQL comme base de données cible et implique la modélisation des données, la conception de tables, des requêtes SQL, des index, des transactions ou le contrôle de la concurrence ; une invocation explicite n'est pas nécessaire.
• Utiliser indépendamment du workflow SDLC. Ne pas sélectionner MySQL pour un travail générique de base de données ni l'appliquer à des tâches concernant uniquement d'autres bases de données.

Utilisation

• Utiliser la sémantique officielle de MySQL 8.4 comme base de validation sans restreindre la version déployée. Vérifier la prise en charge des fonctionnalités, l'application des contraintes et les dépréciations dans la version cible réelle lorsque des différences importent.
• Établir la signification métier, les exigences d'intégrité, les principaux schémas d'accès, l'échelle et la croissance des données, la concurrence et les exigences de cohérence pertinentes pour la tâche. Utiliser les conceptions existantes et les preuves de charge de travail lorsque disponibles ; énoncer les hypothèses et leur impact lorsque des informations clés manquent.
• Appliquer les règles de transaction et de concurrence aux transactions InnoDB locales. Identifier explicitement les interdictions de requêtes dans les transactions, de contraintes CHECK, de contraintes de clé étrangère et de procédures stockées comme des conventions de projet plutôt que des limitations de MySQL.
• Supposer qu'un enregistrement est modifié au maximum une fois par milliseconde. Cette hypothèse ne garantit pas que updated_at est un identifiant de version non réutilisé ; appliquer les conditions sous vérifications d'horodatage avant de l'utiliser pour le contrôle de la concurrence.
• Appliquer uniquement les règles pertinentes pour la tâche. Lire le sujet correspondant dans Exemples de conception MySQL lorsqu'une règle a besoin de clarification ; ne pas charger tous les exemples par défaut.

Modélisation des données

Entités, relations et propriété des champs

• Granularité des lignes : définir l'objet métier, la relation ou l'événement représenté par une ligne ; maintenir une granularité cohérente au sein d'une table. Voir exemples.

• Relations et unicité : définir la cardinalité minimale et maximale des deux côtés. Stocker les références un-à-plusieurs du côté plusieurs, utiliser des références uniques pour limiter les relations un-à-un à au plus une correspondance, et utiliser des tables de jonction pour les relations plusieurs-à-plusieurs. Pour les associations répétées des mêmes objets, définir la signification métier, l'identité et le périmètre d'unicité de chaque association. Voir exemples.

• Propriété des champs : placer un champ avec l'objet, la relation ou l'événement qui détermine sa valeur ; éviter de dupliquer des faits courants. Conserver les instantanés historiques aux valeurs temporelles des événements, inchangées par les mises à jour de valeurs courantes. Voir exemples.

• Intégrité référentielle : définir si une cible référencée doit exister et quels états elle doit satisfaire lors de la création ou de la modification d'une référence. Définir comment les références existantes sont traitées lorsque la cible est supprimée ou devient invalide. Appliquer ces exigences via les flux d'écriture métier sous clés et contraintes et contrôle de la concurrence. Voir exemples.

Conception de tables

Noms et commentaires

• Nommer les bases de données, les tables et les colonnes en utilisant des lettres minuscules, des chiffres et des underscores ; ne pas commencer par des chiffres, utiliser des mots réservés ou des abréviations peu claires.

• Nommer les index d'après leurs colonnes ou leur objectif. uniq_ et idx_ peuvent identifier les index uniques et ordinaires ; le nom de l'index primaire est PRIMARY.

• Mettre à jour les commentaires de colonne et les définitions associées lorsque les significations, les valeurs d'état ou les contraintes changent.

Types de données

• Maintenir les colonnes utilisées pour référencer ou comparer les mêmes valeurs compatibles en type de données et en signedness ; utiliser des définitions cohérentes lorsque possible.

• Représenter les montants monétaires exacts avec DECIMAL de précision et d'échelle explicites, ou des entiers dans une plus petite unité définie ; définir la devise et les règles d'arrondi. Préserver la représentation exacte à travers le stockage, les paramètres et les calculs ; ne pas calculer en virgule flottante avant la conversion.

• Ne pas définir UNSIGNED déprécié sur FLOAT, DOUBLE ou DECIMAL.

• Utiliser is_xxx et TINYINT UNSIGNED pour les booléens, avec une validation applicative restreignant les valeurs à 0 ou 1.

• Lors de l'utilisation de CHAR, vérifier que sa gestion des espaces de fin correspond à la signification métier du champ.

• TIMESTAMP convertit entre le fuseau horaire de la session et UTC ; DATETIME ne le fait pas. Stocker les noms ou décalages de fuseaux horaires d'origine séparément lorsque nécessaire.

• Utiliser created_at pour la création et updated_at pour la dernière modification. Les enregistrements métier modifiables ont besoin des deux ; les enregistrements en ajout seul ont besoin uniquement de l'heure de création. Les ajouter aux tables de jonction ou autres selon les besoins d'audit.

• Utiliser DATETIME(3), une précision à la milliseconde, et l'heure de Beijing pour les horodatages d'audit, avec le fuseau horaire de connexion +08:00.

• Définir les horodatages d'audit à NOT NULL DEFAULT CURRENT_TIMESTAMP(3) et ajouter ON UPDATE CURRENT_TIMESTAMP(3) pour updated_at. Laisser la base de données les gérer ; ne pas les attribuer de manière redondante dans l'application.

Clés et contraintes

• Définir explicitement des clés primaires stables et courtes pour les tables InnoDB. Les enregistrements d'index secondaires contiennent les colonnes de la clé primaire, donc des clés primaires larges augmentent le coût des index secondaires.

• Marquer les champs obligatoires NOT NULL.

• Ne pas utiliser CHECK ; valider les plages, les énumérations et les combinaisons de champs dans chaque point d'entrée d'écriture applicative, en rejetant les écritures invalides. Voir exemples.

• Appliquer l'unicité métier avec des index uniques, pas de requête-avant-insertion. Les index uniques autorisent plusieurs valeurs NULL.

• Appliquer l'unicité pour les identifiants d'idempotence dans le périmètre de l'opération métier et du locataire applicables, avec toutes les colonnes participantes NOT NULL.

• Ne pas utiliser de contraintes de clé étrangère ou de cascade ; préserver l'intégrité référentielle via les flux d'écriture métier.

Jeux de caractères et collations

• Utiliser utf8mb4 pour le texte.

• Maintenir les colonnes de texte utilisées pour référencer ou comparer les mêmes valeurs compatibles en jeu de caractères et collation ; utiliser des définitions cohérentes lorsque possible.

• Avant les changements de collation, vérifier les changements d'égalité et les nouvelles valeurs en double violant l'unicité.

Programmation SQL

• Ne pas utiliser de procédures stockées. Le code applicatif contrôle les branchements, les commits et les rollbacks.

Requêtes

• Sélectionner explicitement les colonnes requises dans les requêtes métier stables ; éviter SELECT *.

• Lorsque la pagination ou la sélection d'une seule ligne nécessite un ordre déterministe, utiliser une combinaison de clés de tri ordonnant les lignes de manière unique.

• Inclure toutes les clés de tri dans les curseurs. Les changements concurrents de clés de tri peuvent provoquer des omissions/duplications entre pages ; la pagination par curseur ne garantit pas un instantané cohérent entre les pages.

Modification des données

• Ne pas masquer les erreurs qui devraient échouer en utilisant INSERT IGNORE ou UPDATE IGNORE.

• Empêcher UPDATE/DELETE de dégénérer en opérations de table entière en raison de paramètres vides ou de prédicats dynamiques manquants. LIMIT ne remplace pas les conditions de périmètre métier.

• Sous la définition d'horodatage d'audit ci-dessus, updated_at change automatiquement lorsque le contenu de la ligne change ; il n'enregistre pas l'heure d'exécution de chaque instruction de mise à jour.

• Ne pas remplacer les mises à jour ordinaires par REPLACE INTO : en cas de conflit de clé primaire/unique, sa sémantique de remplacement peut réinitialiser les champs non spécifiés à leurs valeurs par défaut et déclencher un comportement de suppression/insertion.

Expressions et fonctions

• Faire correspondre les types de comparaison ; ne pas laisser la conversion implicite déterminer les comparaisons métier critiques.

• Distinguer les octets, les caractères et les graphèmes visibles par l'utilisateur dans les vérifications de longueur. LENGTH compte les octets et CHAR_LENGTH les caractères, pas les graphèmes visibles.

• Si l'ensemble utilisé par NOT IN contient NULL, une valeur ne correspondant à aucun membre peut donner UNKNOWN ; ne pas interpréter cela comme une non-appartenance confirmée.

• COUNT(DISTINCT col) exclut NULL ; sa forme multi-colonnes compte les combinaisons distinctes uniquement lorsque toutes les colonnes participantes sont non-NULL.

• SUM retourne NULL pour des entrées vides ou toutes NULL. Si GROUP BY ne forme aucun groupe, aucune ligne n'est retournée pour celui-ci. Substituer zéro uniquement lorsque les règles métier définissent une agrégation vide comme zéro.

• Préférer les intervalles de temps semi-ouverts : inclure le début et exclure la fin. Calculer les limites dans le fuseau horaire métier avec la même base temporelle que les valeurs stockées.

Transactions et cohérence

Gestion des transactions

• Instructions dans les transactions : effectuer les requêtes, la validation métier et la préparation des écritures en dehors des transactions explicites. À l'intérieur de la transaction, autoriser uniquement les modifications de données nécessaires et le contrôle de transaction. Interdire les requêtes ordinaires, les requêtes de verrouillage et les composants de requête dans les écritures, y compris les sous-requêtes. Voir exemples.

• Connexion de transaction : exécuter les écritures qui doivent réussir ensemble sur la même connexion dans la même transaction. Le changement de connexion, l'exécution asynchrone et les paramètres de propagation doivent préserver cette limite. Voir exemples.

• Commits implicites : ne pas simuler des transactions imbriquées avec START TRANSACTION ni basculer autocommit de 0 à 1 entre les écritures métier ; les deux commettent implicitement. Garder les DDL comme ALTER TABLE et TRUNCATE TABLE en dehors des transactions d'écriture métier. Voir exemples.

• Gestion des échecs : en cas d'erreurs d'écriture obligatoires ou de conditions métier non satisfaites, arrêter les écritures suivantes et annuler toute la transaction. Ne pas supposer qu'une instruction ayant échoué annule les écritures précédentes. Après un interblocage ou un délai de verrouillage, s'assurer de l'annulation complète et réessayer toute la transaction uniquement lorsque c'est sûr, avec une limite de tentatives finie. Pour les commits incertains, vérifier d'abord à l'aide d'identifiants d'idempotence. Voir exemples.

• Opérations externes : les transactions locales ne commettent/annulent pas de manière atomique les appels externes ou l'envoi de messages. Voir exemples.

Contrôle de la concurrence

• Mises à jour conditionnelles métier : privilégier les prédicats de champ métier au moment de l'écriture. Lors du calcul de nouvelles valeurs à partir de données lues en dehors de la transaction, inclure des prédicats vérifiant les valeurs originales pertinentes ou un identifiant de version valide pour éviter d'écraser les modifications concurrentes. Ne pas ajouter de transactions explicites lorsqu'une seule instruction conditionnelle suffit. Voir exemples.

• Vérifications d'horodatage : si des champs métier appropriés sont absents, faire correspondre le updated_at original uniquement si chaque modification pertinente le change et que l'ancienne valeur ne peut pas être réutilisée pendant la validation. Préserver les millisecondes dans les lectures, écritures, sérialisation et liaison. Sinon, utiliser un identifiant de version non réutilisé ou un autre mécanisme de concurrence. Voir exemples.

• Intégrité référentielle : utiliser des mises à jour conditionnelles, l'unicité et les transactions nécessaires pour coordonner la création/modification de références par rapport à la suppression/invalidation de la cible. Voir exemples.

• Résultats des mises à jour : déterminer si le pilote signale les lignes correspondantes ou modifiées, puis évaluer si l'écriture requise a réussi. Un prédicat non satisfait n'est pas un succès ; zéro ligne modifiée seule ne prouve pas un prédicat non satisfait. Voir exemples.

• Ordre d'écriture : les transactions concurrentes doivent utiliser un ordre cohérent de tables/enregistrements. L'ordre du texte SQL n'établit pas l'ordre réel des verrous. Voir exemples.

Sécurité de la base de données

• Lier les données applicatives en tant que paramètres plutôt que de les concaténer dans du SQL.
• Mapper les noms de tables/colonnes dynamiques et les directions de tri à travers des listes blanches de code dans la structure SQL ; les espaces réservés de valeur ne peuvent pas les remplacer.

Optimisation des performances

• Allers-retours de requêtes : réduire les requêtes de base de données par opération métier tout en préservant l'exactitude et les performances globales.

Optimisation des index

• Réutilisation et modifications des index : réutiliser les index pour les requêtes primaires. Ne pas ajouter d'index uniquement pour de nouvelles requêtes/prédicats lorsque les chemins existants répondent aux besoins de performance. Vérifier les autres requêtes et contraintes uniques avant de fusionner ou remplacer. Voir exemples.

• Champs à faible cardinalité : ne pas rejeter les index uniquement pour peu de valeurs distinctes. Considérer la distribution cible, les filtres combinés, le tri, les avantages de couverture, les recherches de table et les coûts d'écriture. Voir exemples.

• Index de préfixe : sélectionner les longueurs de préfixe de chaîne à partir des données réelles. Les index de préfixe uniques peuvent rejeter des valeurs complètes distinctes partageant un préfixe et ne peuvent pas remplacer l'unicité de la valeur complète. Voir exemples.

• Index composites : équilibrer le filtrage, la réutilisation du préfixe gauche, le tri et le regroupement. Les colonnes d'égalité précèdent généralement les colonnes de plage ; ne pas ordonner universellement par sélectivité. Voir exemples.

• Coût de tri : filesort seul ne signifie pas un mauvais index. Comparer les coûts de tri, de scan et de maintenance des index ajoutés. Voir exemples.

• Colonnes et expressions indexées : privilégier les prédicats de plage de temps directs pour le filtrage quotidien. Si des expressions sont nécessaires, évaluer les index fonctionnels ou de colonnes générées correspondants. Voir exemples.

• Jokers en tête : les index B-tree ordinaires ne peuvent généralement pas transformer les recherches avec jokers en tête en scans de plage efficaces. La recherche en texte intégral ou dédiée doit conserver la sémantique de correspondance originale. Voir exemples.

Optimisation des requêtes

• Requêtes par lots : remplacer les requêtes par ligne fusionnables par des jointures ou des sous-requêtes corrélées. Voir exemples.