Spécification de conception et de développement MySQL

Zhiqiang 2021-08-19 21:14:39 阅读数:528

sp cification conception et veloppement

Spécifications de conception

Toutes les spécifications suivantes seront conformes 【 Risque élevé 】、【Obligatoire】、【Suggestion】 Dimensionnement à trois niveaux , Respecter les priorités de haut en bas

Pour insatisfaction 【 Risque élevé 】Et【Obligatoire】 Deux niveaux de conception ,DBA A le droit d'exiger un rappel pour modification

Nom de la Banque

1.【Obligatoire】 Le nom de la bibliothèque doit être contrôlé à 32 Dans les caractères , Dans la mesure du possible, le nom de la table et le nom de la table des modules pertinents doivent être représentés. joinLa relation entre,Par exemple:userTable etuser_loginTableau

2.【Obligatoire】 Format du nom de la bibliothèque : Nom du système d'affaires _ Nom du sous - système , Essayez d'utiliser un préfixe uniforme pour les noms de bibliothèques utilisés par le même module

3.【Obligatoire】 Le format général de nommage des sous - bases de données est le nom générique de la base de données. _No., Nombre de 0 Début de l'incrémentation ,Par exemple,wenda_001 Le format de nom de la Sous - base de données time est “ Nom de la bibliothèque _Temps”

4.【Obligatoire】 Un jeu de caractères doit être explicitement spécifié lors de la création d'une base de données , Et le jeu de caractères ne peut être que utf8Ouutf8mb4.Créer une base de donnéesSQLExemples:create database db1 default character set utf8;

Structure du tableau

1.【Obligatoire】Le tableau doit avoir une clé primaire, Et les paramètres id Clé primaire auto - incrémentale

2.【Obligatoire】 Tableau désactiver les clés étrangères , Si vous voulez être complet , Doit être mis en œuvre du côté du programme , Les clés étrangères couplent les tables les unes aux autres ,Impactupdate、delete Equiperformance , Risque d'impasse , Goulot d'étranglement de la performance de la base de données dans un environnement de concurrence élevée

3.【Obligatoire】 Les noms des tableaux et des colonnes doivent être contrôlés dans 32 Dans les caractères , Les noms de table ne peuvent utiliser que des lettres 、Nombre et soulignement, Tous en minuscules . Si le nom du tableau est trop long, l'abréviation peut être utilisée.

4.【Obligatoire】 Vous devez explicitement spécifier le jeu de caractères comme utf8Ouutf8mb4

5.【Obligatoire】 Le type de moteur de stockage de table doit être explicitement spécifié lors de la création d'une table , S'il n'y a pas de besoins spéciaux , Tous InnoDB. Diviser si nécessaire InnoDB/MyISAM/Memory Moteur de stockage externe ,Doit passerDBA Audit à utiliser dans l'environnement de production .Parce queInnodb Services d'appui aux tableaux 、Verrouillage des rangées、 Récupération des temps d'arrêt 、MVCC Caractéristiques importantes de la base de données isorelationnelle , Le plus utilisé par l'industrie MySQLMoteur de stockage. Et c'est quelque chose que la plupart des autres moteurs de stockage n'ont pas , C'est pour ça que j'ai commencé. InnoDB

6.【Obligatoire】 La table doit avoir comment, Il doit y avoir des niveaux de table et de champ comment

7.【Suggestion】 À propos de la clé primaire lors de la construction de la table :(1) La clé primaire est obligatoire id,Le type estintOubigint( Pour une ductilité ultérieure , Le nouveau tableau doit être unifié comme suit: bigint),Etauto_increment(2) Le champ principal de chaque ligne du tableau d'identification n'est pas défini comme clé primaire , Il est recommandé de définir d'autres champs comme suit: user_id,order_idAttendez., Et de créer unique keyIndex. Parce que si la clé primaire est définie et que la valeur de la clé primaire est insérée au hasard ,Cela conduit àinnodbInternepage Fractionnement et grande randomisation I/O, Dégradation des performances

8.【Suggestion】 Tableau de base ( Comme la table des utilisateurs , Tableaux liés à l'argent ) Doit avoir un champ de temps de création de données de ligne create_time Et dernier champ de mise à jour update_time, Facile à trouver

9.【Suggestion】 Tous les champs du tableau doivent être NOT NULL default Par défaut Propriétés, Les entreprises peuvent être définies au besoin DEFAULTValeur. Parce que l'utilisation NULL Les valeurs existent, chaque ligne prend de l'espace de stockage supplémentaire 、 La migration des données est sujette aux erreurs 、 Erreur de calcul de la fonction d'agrégation et défaillance de l'index

10.【Suggestion】 Il est recommandé de faire correspondre blob、text Champ de taille égale , Diviser verticalement en autres tableaux , N'y allez que si vous avez besoin de lire ces objets select

11.【Suggestion】 Conception anti - paradigme : Pour mettre join Champs de requête , Une copie redondante dans un autre tableau .Par exemple:user_name Propriété user_account,user_login_log Attendez qu'il y ait une redondance. ,DiminutionjoinRequête

12.【Obligatoire】 Les tableaux intermédiaires sont utilisés pour conserver les ensembles de résultats intermédiaires , Le nom doit être tmp_Au début. La table de sauvegarde est utilisée pour sauvegarder ou récupérer un instantané de la table source , Le nom doit être bak_Au début. Nettoyage périodique des tables intermédiaires et de sauvegarde

13.【Obligatoire】 Pour l'exécution en ligne DDLChangements,Doit passerDBAAudit,Et parDBA En période de pointe

Optimisation du type de données de colonne

1.【Suggestion】 Auto - ajout dans le tableau (auto_incrementPropriétés),RecommandébigintType. Parce qu'il n'y a pas de symbole int La plage de stockage est -2147483648~2147483647(Environ21Environ un milliard), Le débordement peut entraîner une erreur

2.【Suggestion】 Un état peu sélectif dans l'entreprise status、Typetype Les champs égaux sont recommandés tinytintOusmallint Type Save Storage empty

3.【Suggestion】En affairesIP Champ d'adresse recommandé intType, Non recommandé char(15).Parce queint Seulement 4Octets, Les fonctions suivantes peuvent être utilisées pour convertir ,Etchar(15) Occuper au moins 15Octets. Une fois que les lignes de données du tableau sont comptées 1100 millions, C'est beaucoup. 1.1GEspace de stockage. SQL:select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);

4.【Suggestion】Non recommandéenum,set. Parce qu'ils gaspillent de l'espace , Et la valeur d'énumération est morte. , Changement gênant .RecommandétinyintOusmallint

5.【Suggestion】Non recommandéblob,textIsotype. Ils gaspillent le disque dur et la mémoire. . Lors du chargement des données du tableau , Il lit de grands champs dans la mémoire et gaspille de l'espace mémoire , Influence sur le rendement du système . Recommandations et PM、RDCommunication, Avez - vous vraiment besoin d'un champ aussi grand

6.【Suggestion】 Champ de stockage de l'argent ,Recommandé pourint, Côté programme multiplié par 100 Et divisé par 100 Accès .Ou avecdecimalType, Au lieu de l'utiliser double

7.【Suggestion】 Les données textuelles sont utilisées autant que possible varcharStockage.Parce quevarchar Est un stockage de longueur variable ,Quechar Plus d'espace .MySQL server Le calque spécifie un maximum de texte sur une ligne 65535Octets

8.【Suggestion】 Essayez de sélectionner le type de temps datetime.Ettimestamp Bien qu'il y ait moins d'espace , Mais il y a un délai 1970-01-01 00:00:01À2038-01-01 00:00:00La question de

Conception de l'index

1.【Obligatoire】InnoDB Le tableau doit avoir une clé primaire de id int/bigint auto_increment, Et la valeur de la clé primaire ne peut pas être mise à jour

2.【Suggestion】 Clé unique “uk_”Ou“uq_”Au début, Index général “idx_”Au début, Toujours en minuscules , Suffixe par le nom ou l'abréviation du champ

3.【Obligatoire】InnoDBEtMyISAM Liste des moteurs de stockage , Le type d'index doit être BTREE;MEMORY Le tableau peut être sélectionné au besoin HASHOuBTREEIndex des types

4.【Obligatoire】 La longueur de chaque enregistrement d'index dans un seul index ne peut dépasser 64KB

5.【Suggestion】 Le nombre d'index sur une seule table ne peut dépasser 5- Oui.

6.【Suggestion】 Lors de l'indexation , Envisager d'établir un index fédéré , Et placez le champ le plus différencié en premier . Comme dans la colonne userid Le degré de différenciation peut être déterminé par select count(distinct userid)Calculer

7.【Suggestion】 Dans plusieurs tableaux joinDeSQL- Oui., Assurez - vous qu'il y a un index sur la colonne de connexion de la table entraînée ,Voilà.join Exécution la plus efficace

8.【Suggestion】 Lors de la création d'une table ou de l'indexation , S'assurer qu'il n'y a pas d'index redondants dans le tableau .PourMySQLDis, Si la table existe déjà key(a,b),Etkey(a) Index redondant , Doit être supprimé

Sous - entrepôt sous - Tableau、Table de partition

1.【Obligatoire】 Champ de partition de la table de partition (partition-key) Il doit y avoir un index , Ou la première colonne de l'index combiné

2.【Obligatoire】 Partitions dans une seule table de partitions ( Inclure les sous - zones ) Le nombre ne doit pas dépasser 1024

3.【Obligatoire】 Avant la mise en ligne RDOuDBA Vous devez spécifier la création d'une table de partition 、 Politique de nettoyage

4.【Obligatoire】 Accès à la table partitionnée SQL Doit contenir une clé de partition

5.【Suggestion】 Un seul fichier de partition ne dépasse pas 2G, La taille totale ne dépasse pas 50G. Le nombre total de partitions recommandé ne dépasse pas 20- Oui.

6.【Obligatoire】 Pour les tables partitionnées alter tableFonctionnement, Doit être effectué pendant les périodes de pointe

7.【Obligatoire】 Sous - base , Le nombre de bibliothèques ne peut dépasser 1024

8.【Obligatoire】 Avec une stratégie de sous - table , Le nombre de tableaux ne doit pas dépasser 4096

9.【Suggestion】 Un seul sous - tableau ne recommande pas plus de 500WD'accord, Cela garantit une meilleure performance de la requête de données

10.【Suggestion】 Essayez d'utiliser la méthode d'extraction des moules pour le Sous - mètre horizontal , Et réserver suffisamment buffer, Afin de ne pas avoir à se diviser et à migrer à l'avenir ,Log、 Il est recommandé que les données du type de rapport soient ventilées par date.

Jeu de caractères

1.【Obligatoire】 Base de données elle - même 、Tableau、 Colonne tous les jeux de caractères doivent être cohérents ,Pourutf8Ouutf8mb4

2.【Obligatoire】 Jeu de caractères du programme frontal ou jeu de caractères dans une variable d'environnement , Base de données 、 Le jeu de caractères du tableau doit être cohérent ,Unified asutf8

SQLCompilation

DMLDéclarations

1.【Obligatoire】SELECT L'instruction doit spécifier un nom de champ spécifique , Ne pas écrire .Parce queselect Les données qui ne devraient pas être lues MySQL Lis - le. , Provoque une pression sur la carte réseau . Et une fois que les champs du tableau sont mis à jour , Mais le programme n'a pas eu le temps de mettre à jour , Le système signale une erreur

2.【Obligatoire】insert Instruction spécifiant un nom de champ spécifique , Ne l'écrivez pas. insert into t1 values(…), C'est la même chose.

3.【Suggestion】insert into…values(XX),(XX),(XX)….Ici.XX Ne pas dépasser 500- Oui.. C'est trop cher, mais c'est rapide. , Mais provoque un retard de synchronisation maître - esclave

4.【Suggestion】SELECT Instruction n'utilisez pas UNION,RecommandéUNION ALL,EtUNION Le nombre de clauses est limité à 3 À l'intérieur de .Parce queunion all Pas besoin de poids. , Économiser les ressources de la base de données ,Amélioration des performances

5.【Suggestion】in La liste des valeurs est limitée à 500À l'intérieur.Par exempleselect… where userid in(….500 À l'intérieur de …), Ceci est fait pour réduire les scans sous - jacents , Réduire la pression sur la base de données pour accélérer les requêtes

6.【Suggestion】 Quantité de contrôle nécessaire pour mettre à jour les données par lots dans la transaction , Faire ce qui est nécessaire sleep, Un petit nombre de fois

7.【Obligatoire】 Toutes les tables impliquées dans la transaction doivent être innodbTableau. Sinon, en cas d'échec, tout ne reviendra pas en arrière. , Et il est facile d'interrompre la synchronisation de la Bibliothèque maître - esclave

8.【Obligatoire】 Écrire et effectuer des transactions à la bibliothèque principale ,Lire seulementSQL Envoyer à partir de la bibliothèque , C'est - à - dire que le côté du programme réalise la séparation lecture - écriture

9.【Obligatoire】DML L'instruction doit avoir whereConditions, Et utiliser l'index pour trouver

10.【Obligatoire】 Environnement de production interdit hint,Par exemple:sql_no_cache,force index,ignore key,straight joinAttendez..Parce quehint Est utilisé pour forcer SQL Suivre un plan d'exécution , Mais au fur et à mesure que les données changent, nous ne pouvons pas nous assurer que nos prévisions initiales sont correctes. , Nous devons essayer MySQL L'optimiseur choisit son propre plan d'exécution

11.【Obligatoire】where Le type de champ gauche et droit du signe égal dans l'état doit être cohérent , Sinon, l'index ne peut pas être utilisé

12.【Suggestion】SELECT|UPDATE|DELETE|REPLACE Oui. WHEREClause,EtWHERE Les conditions d'une clause doivent être trouvées à l'aide d'un index

13.【Obligatoire】 Il est fortement déconseillé d'effectuer un balayage complet des grandes tables dans la base de données de production. ,Mais pour100 Les tables statiques en dessous de la ligne peuvent être scannées à travers la table . La quantité de données de requête ne doit pas dépasser le nombre de lignes de tableau 25%, Sinon, l'index n'est pas utilisé

14.【Obligatoire】WHERE Interdire l'utilisation du flou complet seulement dans la clause LIKE Recherche conditionnelle ,Si vous utilisezlike,Veuillez utiliserlike ‘xxxx%’De la façon dont, Il doit y avoir d'autres critères de requête d'équivalence ou de plage , Sinon, l'index ne peut pas être utilisé

15.【Suggestion】 Les colonnes indexées n'utilisent pas de fonctions ou d'expressions , Sinon, l'index ne peut pas être utilisé .Par exemple:where length(name)='Admin'Ouwhere user_id+2=10023

16.【Suggestion】 Réduction de l'utilisation orDéclarations,Peut êtreor Instruction optimisée pour union, Et puis dans chaque where Indexation conditionnelle .Par exemple:where a=1 or b=2Optimisé pourwhere a=1… union …where b=2, key(a),key(b)

17.【Suggestion】Requête pagée,Quandlimit Lorsque le point de départ est élevé , Les conditions de filtrage peuvent être utilisées en premier .Par exemple:select a,b,c from t1 limit 10000,20;Optimisé pour: select a,b,c from t1 where id>10000 limit 20;

Connexion Multi - tables

1.【Obligatoire】 Interdiction de traverser dbDejoinDéclarations. Parce que cela réduit le couplage entre les modules , Jeter les bases solides du fractionnement des bases de données

2.【Obligatoire】 Interdire les mises à jour dans les classes d'affaires SQLUtilisé dans l'énoncéjoin,Par exemple,update t1 join t2…

3.【Suggestion】 Les sous - requêtes ne sont pas recommandées , Sous - Requête recommandée SQL Requête multiple de Split Union ,Ou utiliserjoinPour remplacer les sous - Requêtes

4.【Suggestion】Environnement en ligne, Multi - tables joinNe pas dépasser3 Tableaux

5.【Suggestion】 Alias recommandé pour les requêtes de connexion Multi - tables ,EtSELECT Pour référencer un champ avec un alias dans la Liste ,Base de données. Format du tableau ,Par exemple:select a from db1.table1 alias1 where …

6.【Suggestion】 Dans plusieurs tableaux joinMoyenne, Essayez de sélectionner une table avec un ensemble de résultats plus petit comme table d'entraînement ,Allez, viens.join Autres tableaux

Services

1.【Suggestion】 En cours INSERT|UPDATE|DELETE|REPLACE Le nombre de lignes pour l'opération d'instruction est contrôlé à 1000À l'intérieur,EtWHEREDans la clauseIN Le nombre de paramètres passés dans la liste est contrôlé à 500À l'intérieur

2.【Suggestion】 Lors de l'exploitation des données par lots , Besoin de contrôler l'intervalle de transaction , Faire ce qui est nécessaire sleep, Recommandations générales 1-2Secondes

3.【Suggestion】 Oui auto_increment Insertion d'une table pour un champ de propriété , La concurrence doit être contrôlée 200À l'intérieur

4.【Obligatoire】 La programmation doit tenir compte “ Niveau d'isolement des transactions de la base de données ”Impact, Y compris la lecture sale 、Lecture non répétitive et fictive. Le niveau d'isolement des transactions recommandé en ligne est repeatable-read

5.【Suggestion】 La transaction contient SQLPas plus de5- Oui.( Autres opérations de paiement ). Parce qu'une transaction trop longue peut entraîner des données de verrouillage plus longues ,MySQL Cache interne 、 Problèmes d'avalanche tels que la consommation excessive de connexions

6.【Suggestion】 Les déclarations de mise à jour dans une transaction sont basées autant que possible sur la clé primaire ou unique key,Par exemple:update … where id=XX; Sinon, il y aura un verrou de dégagement , Extension interne de la portée de verrouillage , Réduit les performances du système , Générer une impasse

7.【Suggestion】 Essayez de déplacer certains appels externes typiques hors de la transaction , Comme appelé webservice, Accès au stockage de fichiers, etc. , Pour éviter une transaction trop longue

8.【Suggestion】PourMySQL Délai maître - esclave strictement sensible selectDéclarations, Veuillez ouvrir la transaction pour forcer l'accès à la bibliothèque principale

Tri et regroupement

1.【Suggestion】 Réduction de l'utilisation order by, Communiquer avec les entreprises sans trier , Ou mettre le tri sur le côté du programme .order by、group by、distinct Ces déclarations sont coûteuses CPU,De la base de donnéesCPU Les ressources sont extrêmement précieuses

2.【Suggestion】order by、group by、distinctCesSQL Essayez d'utiliser l'index pour récupérer directement les données triées .Par exemple:where a=1 order byPeut être utilisékey(a,b)

3.【Suggestion】Inclusorder by、group by、distinct Déclarations pour ces requêtes ,where L'ensemble de résultats filtrés par condition doit être maintenu à 1000 En ligne ,SinonSQL Ça va être lent.

Interdit en ligne SQLDéclarations

1.【 Risque élevé 】Désactiverupdate|delete t1 … where a=XX limit XX; Cette ceinture. limit Déclaration de mise à jour pour . Si oui non rowFormatbinlogFormat, Provoque une incohérence maître - esclave , Provoque une confusion des données . Recommandation plus order by PK

2.【 Risque élevé 】 Désactiver les sous - requêtes associées ,Par exemple:update t1 set … where name in(select name from user where…); Extrêmement inefficace

3.【Obligatoire】Désactiverprocedure、function、trigger、views、event、 Contraintes des clés étrangères . Parce qu'ils consomment des ressources de base de données , Réduire l'extensibilité des instances de base de données . Les recommandations sont mises en œuvre du côté du programme

4.【Suggestion】Désactiverinsert into …on duplicate key update…、replace intoAttendez la Déclaration, Dans un environnement de concurrence élevée , Très susceptible de provoquer une impasse

5.【Obligatoire】 Désactiver les mises à jour de table associées ,Par exemple:update t1,t2 where t1.id=t2.id…

Copyright:Cet article est[Zhiqiang]Établi,Veuillez apporter le lien original pour réimprimer,remercier。 https://fra.fheadline.com/2021/08/20210819211053779v.html