Introduction détaillée à la théorie de base des bases de données-tutoriel mysql-php.cn

1. Paradigme de la base de données

Premier paradigme : les colonnes sont indivisibles, ex : [Contact] (nom, sexe, numéro de téléphone), un contact a un numéro de téléphone personnel et le numéro de téléphone de l'entreprise, alors cette conception de structure de table n'atteint pas 1NF

Deuxième forme normale : il existe une clé primaire, assurant une dépendance complète ; Par exemple : la table des détails de la commande [OrderDetail] (OrderID, ProductID, UnitPrice, Discount, Quantity, ProductName), Discount (remise), Quantity (quantity) dépendent entièrement de la clé primaire (OderID, ProductID), tandis que UnitPrice, ProductName ne dépendent que on Pour ProductID, il n'est pas conforme à 2NF ;

Troisième forme normale : pas de dépendance transitive (la colonne de clé non primaire A dépend de la colonne de clé non primaire B, la colonne de clé non primaire B dépend de la colonne de clé non primaire B clé), par exemple : table de commande [Order] (OrderID, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity) La clé primaire est (OrderID), CustomerName, CustomerAddr, CustomerCity dépend directement de CustomerID (colonne de clé non primaire), plutôt que s'appuyant directement sur la clé primaire. Cela dépend de la clé primaire via le passage , donc il n'est pas conforme à 3NF.

2. Qu'est-ce qu'un anti-modèle ?

Paradigm peut éviter la redondance des données, réduire l'espace de la base de données et réduire les problèmes de maintien de l'intégrité des données.

Cependant, grâce à la conception de la normalisation des bases de données, le nombre de tables impliquées dans les activités de base de données augmentera et les tables métier impliquées devront peut-être être interrogées avec des connexions multi-tables, ce qui entraînera de mauvaises performances et n'est pas propice. aux sous-bases de données. Par conséquent, dans un souci de priorité en matière de performances, il peut être nécessaire d'utiliser une conception anti-modèle dans la structure de la base de données, c'est-à-dire d'échanger de l'espace contre du temps et d'adopter la redondance des données pour éviter les requêtes associées entre les tables. En ce qui concerne la question de la cohérence des données, comme il est difficile d'atteindre une forte cohérence des données, en général, les données stockées doivent être aussi cohérentes que possible entre les utilisateurs pour garantir que le système parviendra finalement à la cohérence après une auto-récupération et une correction dans un court laps de temps. période de temps.

Les anti-modèles doivent être utilisés avec précaution lors de la conception de bases de données. En général, utilisez autant que possible une conception de base de données normalisée, car une conception de base de données normalisée peut rendre le produit plus flexible et maintenir l'intégrité des données au niveau de la couche de base de données.

Parfois, le meilleur moyen d'améliorer les performances est de stocker les données redondantes dans la même table. Si une petite quantité de données sales peut être tolérée, la création d'une table récapitulative ou d'une table de cache complètement indépendante est une très bonne méthode. . Par exemple, la conception d'une « table de nombre de téléchargements » pour mettre en cache les informations sur le nombre de téléchargements peut augmenter la vitesse d'interrogation des informations totales dans le cas de données massives.

Un autre scénario typique, pour des raisons d'évolutivité, des colonnes de type BLOB et TEXT peuvent être utilisées pour stocker des données structurées JSON. L'avantage est que de nouveaux attributs peuvent y être ajoutés à tout moment sans modifier la table. structure. Cependant, les inconvénients de cette conception sont également évidents, c'est-à-dire que l'intégralité du contenu du champ doit être obtenu et décodé pour obtenir les attributs spécifiés, et que des opérations telles que l'indexation, le tri et l'agrégation ne peuvent pas être effectuées. Par conséquent, si vous devez envisager des scénarios d'utilisation plus complexes, il est plus recommandé d'utiliser une base de données de documents telle que MongoDB.

3. Transaction de base de données

Une transaction est une séquence indivisible d'opérations de base de données et l'unité de base du contrôle de concurrence de base de données. la base de données Passage d'un état de cohérence à un autre état de cohérence.

(1). Caractéristiques des transactions

Atomicité : Une série d'opérations de base de données incluses dans une transaction sont soit toutes exécutées avec succès, soit toutes annulées

Cohérence cohérente : La le résultat de l'exécution de la transaction doit faire passer la base de données d'un état de cohérence à un autre état de cohérence ;

Isolement : les transactions exécutées simultanément ne peuvent pas s'influencer mutuellement

Durabilité : une fois qu'une transaction est validée, la les modifications apportées aux données de la base de données sont permanentes.

(2).Problèmes causés par la concurrence des transactions

Lecture sale : une transaction lit les données non validées d'une autre transaction

Lecture non répétable : non répétable L'objectif de la lecture est une modification. Les résultats de deux lectures dans les mêmes conditions sont différents, c'est-à-dire que les données lues peuvent être modifiées par d'autres transactions

Lecture fantôme : l'objectif de la lecture fantôme est d'ajouter ou de supprimer ; . Le nombre d'enregistrements lus deux fois dans les mêmes conditions est différent.

(3). Niveau d'isolement

Le niveau d'isolement détermine l'impact que les transactions d'une session peuvent avoir sur les transactions d'une autre session.

La norme ANSI définit 4 niveaux d'isolement, qui sont pris en charge par InnoDB de MySQL, à savoir :

READ UNCOMMITTED (lecture non validée) : le niveau d'isolement le plus bas, généralement également appelé lecture sale, il permet de transaction pour lire des données qui n'ont pas été validées par une autre transaction, ce qui peut améliorer les performances, mais entraînera des problèmes de lecture sale ;

READ COMMITTED (lecture validée) : seules les autres transactions sont autorisées dans une transaction. été validées sont visibles. Ce niveau d'isolement ne peut pas éviter le problème de lecture non répétable

REPEATABLE READ (lecture répétable) : après le démarrage d'une transaction, les modifications apportées à la base de données par d'autres transactions ne sont pas visibles dans cette transaction jusqu'à ce que cette transaction soit effectuée. la transaction est validée ou annulée. Cependant, les opérations d'insertion/suppression des autres transactions sont visibles par cette transaction, ce qui signifie que ce niveau d'isolement ne peut pas éviter le problème de lecture fantôme. Les résultats d'une sélection répétée dans une transaction sont les mêmes, sauf si la base de données est mise à jour dans cette transaction.

SERIALIZABLE (sérialisable) : le plus haut niveau d'isolement, permettant uniquement l'exécution des transactions en série.

Le niveau d'isolement par défaut de MySQL est REPEATABLE READ.

Introduction détaillée à la théorie de base des bases de données

4. Qu'est-ce qu'une procédure stockée ? Quels sont les avantages et les inconvénients ?

Une procédure stockée est un ensemble d'instructions SQL qui ont été compilées à l'avance et stockées dans la base de données. De plus, une procédure stockée est un bloc de code composé de quelques instructions T-SQL implémentant certaines fonctions comme une méthode (ajouter, supprimer, modifier et interroger une seule table ou plusieurs tables), puis donner ce code. block Donnez-lui simplement un nom et appelez-le lorsque vous utilisez cette fonction. Les procédures stockées ont les caractéristiques suivantes :

4.1. Les procédures stockées ne sont compilées que lorsqu'elles sont créées. Il n'est pas nécessaire de les recompiler à chaque exécution ultérieure. Généralement, les instructions SQL sont compilées une fois par la suite. au moment de leur exécution, donc en utilisant le stockage Le processus peut améliorer l'efficacité d'exécution de la base de données ;

4.2 Lorsque l'instruction SQL change, vous ne pouvez modifier que la procédure stockée dans la base de données sans modifier le code ; >

4.3. Réduire la transmission réseau, sur le client Bien entendu, la quantité de données transférées en appelant une procédure stockée est inférieure à l'exécution d'une série de SQL

4.4. accéder indirectement à la base de données sous contrôle, garantissant ainsi la sécurité des données.

5. Parlons brièvement des différences entre supprimer, supprimer et tronquer

supprimer, supprimer et tronquer en SQL signifient toutes une suppression, mais il y en a. différences entre les trois. Différence :

Supprimer est utilisé pour supprimer tout ou partie des lignes de données de la table. Après avoir exécuté la suppression, l'utilisateur doit valider ou restaurer pour effectuer la suppression ou annuler la suppression. déclencher l'opération sur cette table. Tous les déclencheurs de suppression ;

Truncate supprime toutes les données de la table. Cette opération ne peut pas être annulée et ne déclenchera pas de déclencheurs sur cette table. ;

La commande Drop supprime la table de la base de données. Toutes les lignes de données, index et autorisations seront également supprimés. Tous les déclencheurs DML ne seront pas déclenchés et cette commande ne peut pas être annulée.

Par conséquent, lorsqu'une table n'est plus nécessaire, utilisez drop ; lorsque vous souhaitez supprimer certaines lignes de données, utilisez delete lorsque vous conservez la table mais supprimez toutes les données, utilisez truncate.

6. Qu'est-ce qu'une vue ? Qu'est-ce qu'un curseur ?

Une vue est une table virtuelle, généralement un sous-ensemble de lignes ou de colonnes dans une ou plusieurs tables. Elle a les mêmes fonctions qu'une table physique et peut être ajoutée à la vue. , supprimer, modifier, vérifier et autres opérations. En particulier, les modifications apportées à la vue n'affectent pas la table sous-jacente. Par rapport aux requêtes multi-tables, cela nous permet d'obtenir plus facilement des données.

Le curseur traite efficacement l'ensemble des résultats de la requête comme une unité. Le curseur peut être positionné sur une ligne spécifique de la cellule pour récupérer une ou plusieurs lignes de la ligne actuelle dans le jeu de résultats. Vous pouvez apporter des modifications à la ligne actuelle du jeu de résultats. Les curseurs ne sont généralement pas utilisés, mais lorsque les données doivent être traitées une par une, les curseurs sont très importants.

Lors de l'utilisation de MySQL, nous savons que l'opération de récupération MySQL renvoie un ensemble de lignes appelé ensemble de résultats. L'ensemble de lignes renvoyé est constitué de toutes les lignes qui correspondent à l'instruction SQL (zéro ou plusieurs lignes). Avec une simple instruction SELECT, par exemple, il n'existe aucun moyen d'obtenir la première ligne, la ligne suivante ou les 10 premières lignes, et il n'existe pas non plus de moyen simple de traiter toutes les lignes une par une (au lieu de les traiter dans lots). Parfois, il est nécessaire d'avancer ou de reculer d'une ou plusieurs lignes dans les lignes récupérées. C'est pourquoi des curseurs sont utilisés. Un curseur est une requête de base de données stockée sur le serveur MySQL. Il ne s'agit pas d'une instruction SELECT, mais du jeu de résultats récupéré par l'instruction. Une fois le curseur stocké, l'application peut faire défiler ou parcourir les données qu'il contient selon les besoins. Les curseurs sont principalement utilisés dans les applications interactives où l'utilisateur doit faire défiler les données sur l'écran et parcourir ou apporter des modifications aux données.

7. Qu'est-ce qu'un déclencheur ?

Un déclencheur est un objet de base de données lié à une table qui se déclenche lorsque les conditions définies sont remplies et exécute l'ensemble d'instructions défini dans le déclencheur. Cette fonctionnalité des déclencheurs peut aider les applications à garantir l’intégrité de la base de données côté base de données.

8. Super clé, clé candidate, clé primaire, clé étrangère

Super clé : L'ensemble des attributs qui peuvent identifier de manière unique un tuple dans une relation est appelé le super de la clé du modèle relationnel. Un attribut peut être utilisé comme super clé, et plusieurs attributs combinés peuvent également être utilisés comme super clé. La super clé contient la clé candidate et la clé primaire.

Clé candidate : C'est la super-clé minimale, c'est-à-dire une super-clé sans éléments redondants.

Clé primaire : combinaison de colonnes de données ou d'attributs dans une table de base de données qui identifie de manière unique et complète l'objet de données stocké. Une colonne de données ne peut avoir qu'une seule clé primaire et la valeur de la clé primaire ne peut pas manquer, c'est-à-dire qu'elle ne peut pas être nulle.

Clé étrangère : La clé primaire d'une autre table qui existe dans une table est appelée clé étrangère de cette table.

9. Qu'est-ce qu'une transaction ? Qu'est-ce qu'une serrure ?

Transaction : il s'agit d'un groupe d'instructions SQL liées entre elles en tant qu'unité logique de travail. Si une opération d'instruction échoue, l'opération entière échouera et les opérations futures seront rétablies à l'état précédant l'opération, ou il y aura un nœud. Pour garantir que quelque chose est exécuté ou non, des transactions peuvent être utilisées. Pour qu’une instruction groupée soit considérée comme une transaction, elle doit réussir les tests ACID, à savoir l’atomicité, la cohérence, l’isolement et la durabilité.

Verrouillage : dans tous les SGBD, les verrous sont la clé de la mise en œuvre des transactions. Les verrous peuvent garantir l'intégrité et la simultanéité des transactions. Tout comme un verrou dans la vie réelle, il peut empêcher le propriétaire de certaines données d'utiliser certaines données ou structures de données pendant une certaine période de temps. Bien entendu, les écluses sont également divisées en niveaux.

10. Mécanisme de verrouillage de la base de données

Le mécanisme de verrouillage de la base de données signifie simplement que afin d'assurer la cohérence des données, la base de données permet d'accéder simultanément à diverses ressources partagées, et les accès changent. Une règle conçue de manière ordonnée. Chaque moteur de stockage MySQL utilise trois types (niveaux) de mécanismes de verrouillage : le verrouillage au niveau de la ligne, le verrouillage au niveau de la page et le verrouillage au niveau de la table.

Verrouillage au niveau de la table (niveau table) : le verrouillage au niveau de la table est le mécanisme de verrouillage le plus granulaire parmi les moteurs de stockage MySQL. La plus grande caractéristique de ce mécanisme de verrouillage est que la logique de mise en œuvre est très simple et n’a qu’un impact négatif minimal sur le système. L’acquisition et la libération des verrous sont donc très rapides. Étant donné que les verrous au niveau de la table verrouillent la table entière en même temps, le problème de blocage qui nous tourmente peut être évité. Bien entendu, le plus grand impact négatif d’une grande granularité de verrouillage est que la probabilité de conflit pour le verrouillage des ressources sera la plus élevée, ce qui réduira considérablement l’efficacité. Les verrous au niveau de la table sont divisés en verrous de lecture et verrous d'écriture. Verrouillage au niveau de la page (au niveau de la page) : la caractéristique du verrouillage au niveau de la page est que la granularité du verrouillage se situe entre le verrouillage au niveau de la ligne et le verrouillage au niveau de la table, donc la surcharge de ressources requise pour obtenir le verrou et la capacité de traitement simultané qu'il peut fournir sont également entre les deux ci-dessus. De plus, le verrouillage au niveau de la page et au niveau de la ligne entraînera un blocage. Verrouillage au niveau de la ligne (au niveau de la ligne) : la plus grande caractéristique du verrouillage au niveau de la ligne est que la granularité de l'objet verrouillé est très faible, et il s'agit également de la plus petite granularité de verrouillage atteinte par les principaux logiciels de gestion de bases de données actuellement. Étant donné que la granularité du verrouillage est très faible, la probabilité de conflit pour le verrouillage des ressources est également minime, ce qui peut donner à l'application autant de capacités de traitement simultané que possible et améliorer les performances globales de certains systèmes d'application qui nécessitent une simultanéité élevée. Bien qu'il présente de grands avantages en termes de capacités de traitement simultané, le verrouillage au niveau des lignes présente également de nombreux inconvénients. Étant donné que la granularité des ressources de verrouillage est très faible, il faut faire plus de choses à chaque fois pour acquérir et libérer le verrou, et la consommation sera naturellement plus importante. De plus, le verrouillage au niveau des lignes est également le plus sujet aux blocages. Les verrous au niveau des lignes d'InnoDB sont également divisés en deux types, les verrous partagés et les verrous exclusifs. De même, InnoDB a également introduit le concept de verrous d'intention (verrous au niveau de la table), il existe donc des verrous partagés d'intention et des verrous exclusifs d'intention, donc InnoDB a réellement. Quatre types de verrous, à savoir le verrou partagé (S), le verrou exclusif (X), le verrou partagé d'intention (IS) et le verrou exclusif d'intention (IX)

Dans la base de données MySQL, le verrouillage au niveau de la table est principalement ; utilisé par MyISAM, Memory, CSV et d'autres moteurs de stockage non transactionnels. Le verrouillage au niveau des lignes est principalement utilisé par le moteur de stockage Innodb et le moteur de stockage NDBCluster est principalement la méthode de verrouillage du moteur de stockage BerkeleyDB.

La fonction du verrouillage d'intention est que lorsqu'une transaction doit obtenir un verrou de ressource, si la ressource dont elle a besoin est déjà occupée par un verrou exclusif, la transaction peut ajouter un verrou approprié à la table qui doit se verrouiller. la ligne. Si vous avez besoin d'un verrou partagé, ajoutez un verrou partagé d'intention sur la table. Si vous devez ajouter un verrou exclusif sur une certaine ligne (ou plusieurs lignes), ajoutez d'abord un verrou exclusif d'intention sur la table. Plusieurs verrous partagés d'intention peuvent exister en même temps, mais un seul verrou exclusif d'intention peut exister en même temps.

Introduction détaillée à la théorie de base des bases de données