Réserve de points de connaissances de base de la base de données MySQL (résumé organisé)

Cet article vous apporte des connaissances pertinentes sur la base de données mysql. Il organise principalement certains points de connaissances de base de la base de données, notamment l'indexation, l'ordre syntaxique, l'ordre d'exécution, les procédures stockées et d'autres problèmes connexes.

Apprentissage recommandé : Tutoriel mysql

1. Base de données

1.1 Transactions

1.1.1 Quatre caractéristiques des transactions (ACID)

• Atomicité ( Atomicité )Atomicity）
  整个事务中的所要操作要么全部提交成功，要么全部失败回滚。
• 一致性（Consistency）
  保证数据库中的数据操作之前和操作之后的一致性。（比如用户多个账户之间的转账，但是用户的总金额是不变的）
• 隔离性（Isolation）
  隔离性要求一个事务对数据库中数据的修改，在未提交完成前对于其它事务是不可见的。（即事务之间要串行执行）
• 持久性（Durability）
  持久性是指一个事务一旦被提交了，那么对数据库中的数据改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

SQL标准定义了四种隔离性：（下面隔离性是由低到高，并发性由高到低）

• 未提交读。
  最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。
• 已提交读。
  由于数据库是读写分离，事务读取的时候获取读锁，但是在读完之后立即释放，释放读锁之后，就可能被其他事务修改数据，再进行读是就发现前后读取数据的结果不同，造成不可重复读。（读锁不需要事务提交后释放，而写锁需要事务提交后释放。）
• 可重复读。
  所有被select Toutes les opérations requises dans l'ensemble de la transaction doivent être soumises avec succès ou toutes ont échoué et annulées.
• Cohérence (Cohérence)
  assure la cohérence des données dans la base de données avant et après l'opération. (Par exemple, les transferts entre plusieurs comptes d'un utilisateur, mais le montant total de l'utilisateur reste inchangé)

Isolement (Isolation)

L'isolement nécessite qu'une transaction modifie les données de la base de données et ne modifie pas modifiez-le à l'avenir. Il n'est pas visible pour les autres transactions tant que la validation n'est pas terminée. (C'est-à-dire que les transactions doivent être exécutées en série)

Durabilité (Durability)
La durabilité signifie qu'une fois qu'une transaction est soumise, les modifications apportées aux données dans la base de données sont permanentes, même si dans le cas où le système de base de données rencontre une panne, l'opération de validation de la transaction ne sera pas perdue.


La norme SQL définit quatre types d'isolement : (L'isolement suivant va de bas en haut, et la concurrence est de haut en bas)

Lecture non validée.

Le niveau d'isolement le plus bas, permettant aux autres transactions de voir les données non validées, ce qui entraînera des lectures sales. 🎜🎜Soumis pour lecture. 🎜 Étant donné que la base de données est séparée en lecture et en écriture, le verrou de lecture est acquis lors de la lecture de la transaction, mais il est libéré immédiatement après la lecture. Une fois le verrou de lecture libéré, les données peuvent être modifiées par d'autres transactions lors de la nouvelle lecture des résultats. de lecture des données avant et après sont différents, provoquant des lectures non répétables. (Les verrous de lecture n'ont pas besoin d'être libérés une fois la transaction validée, tandis que les verrous d'écriture doivent être libérés une fois la transaction validée.) 🎜🎜Lecture répétable. 🎜 Toutes les données obtenues par select ne peuvent pas être modifiées, afin d'éviter une lecture incohérente avant et après une transaction. Mais il n'y a aucun moyen de contrôler la lecture fantôme, car les autres transactions ne peuvent pas modifier les données sélectionnées pour le moment, mais elles peuvent ajouter des données 🎜🎜Sérialisable ; 🎜 Toutes les transactions sont exécutées les unes après les autres pour éviter les lectures fantômes. Pour les bases de données qui implémentent un contrôle de concurrence basé sur des verrous, la sérialisation nécessite que lors de l'exécution de requêtes de plage, un verrouillage de plage soit obtenu. Si le contrôle de concurrence n'est pas implémenté sur la base de verrous, la base de données. , lorsqu'une transaction qui viole les opérations en série est détectée, la transaction doit être annulée. 🎜🎜🎜Résumé : Les quatre niveaux sont progressivement améliorés et chaque niveau résout les problèmes. Plus le niveau de transaction est élevé, plus les performances sont mauvaises. 🎜🎜Niveau d'isolement              Lecture sale   Lecture non répétable   Lecture fantôme 🎜 Lecture non validée (lecture non validée)  Possible         Possible 🎜 Lecture validée (lecture validée)                     Lecture sale     Lecture non répétable   Lecture fantôme        🎜🎜🎜Résumé : Les lectures non validées provoqueront des lectures sales -> Les lectures validées résoudront les lectures sales, mais provoqueront des lectures non répétables -> Les lectures répétables résoudront le problème de la lecture des résultats avant et après l'incohérence, mais provoqueront des lectures fantômes (pas avant, mais maintenant) - > La sérialisabilité résout les lectures fantômes, mais ajoute de nombreux verrous de plage, ce qui peut entraîner des délais d'attente de verrouillage 🎜 ;

1.1.2 Lecture sale, lecture non répétable et lecture fantôme

• Lecture sale (pour opération de restauration) : la transaction T1 a mis à jour le contenu d'une ligne d'enregistrements, mais n'a pas validé la modification, la transaction T2 a lu Get la ligne mise à jour, puis T1 effectue une opération de restauration, annulant la modification qui vient d'être effectuée. Désormais, le nombre de lignes lues par T2 n'est pas valide (une transaction lit une autre transaction
• Lecture non répétable (pour les opérations modifiées) : la transaction T1 lit une ligne d'enregistrements, puis T2 modifie l'enregistrement que T1 vient de lire cette ligne ; d'enregistrements, puis T1 a relu cette ligne d'enregistrements et a constaté que les résultats étaient différents de ceux qui viennent d'être lus.
• Lecture fantôme (pour les opérations de mise à jour) : la transaction T1 lit l'ensemble de résultats renvoyé par une clause Where spécifiée, puis la transaction T2 insère une nouvelle ligne d'enregistrements, qui satisfait aux conditions de requête utilisées par T1. Ensuite, T1 récupère à nouveau la table, mais voit à nouveau les données insérées par T2. (Je ne l'ai pas vu la première fois, mais je l'ai vu la deuxième fois)

2. Index

2.1 Les fonctionnalités d'index

1. peuvent accélérer la récupération de la base de données
2. ne peuvent être créées que sur des tables, pas de vues ;
3. Peut être créé directement ou indirectement ;
4. Les index peuvent être utilisés dans le masquage d'optimisation ;
5. Utilisez le processeur de requêtes pour exécuter des instructions SQL sur une table, un seul index peut être utilisé à la fois.

2.1.1 Avantages de l'index

1. Créez un index unique pour garantir l'unicité de chaque ligne de données dans la table de la base de données
2. Accélérez considérablement la récupération des données, qui est la principale raison de la création d'un index ;
Accélérer la base de données Les liens entre les tables sont particulièrement importants pour atteindre l'intégrité référentielle de la base de données
3. Lors de la récupération à l'aide de clauses de regroupement et de tri, le temps de regroupement et de tri dans les requêtes peut également être considérablement réduit ; les cacheurs peuvent être utilisés dans les requêtes pour améliorer les performances du système ;
5. 2.1.2 Inconvénients des index

La création et la maintenance des index prennent du temps, ce qui augmente avec l'augmentation du nombre

Les index nécessitent de l'espace physique ; En plus de l'espace de données occupé par la table de données, chaque index occupe également une certaine quantité d'espace physique. Si un index clusterisé est établi, l'espace requis sera plus grand
1. Lors de l'ajout, de la suppression et de la modification de données dans la table. l'index doit également être maintenu, ce qui réduit la vitesse de maintenance des données ;
3. 2.2 Classification de l'index

(1) Index ordinaire (il n'a aucune restriction.) (2) Index unique (la valeur de la colonne d'index doit être unique, mais les valeurs nulles sont autorisées.)

(3) Index de clé primaire (un index unique spécial qui n'autorise pas les valeurs nulles. Généralement, l'index de clé primaire est créé en même temps lors de la création de la table.)

(4) Index combiné
(5) L'index clusterisé construit un arbre B+ basé sur la clé primaire de chaque table, et les données d'enregistrement de ligne de la table entière sont stockées dans les nœuds feuilles, donc les nœuds feuilles de l'index clusterisé deviennent également des pages de données.
(6) Index non clusterisé (index auxiliaire) (le nœud de page ne stocke pas une ligne entière d'enregistrements).


2.3 Défaillance de l'index

(1) S'il y a ou dans l'état, même s'il y a un index dans l'état, il ne sera pas utilisé (utiliser ou le moins possible) ; (2) J'aime la requête commence par %, comme SELECT * FROM mytable WHEREt Name like'%admin';

(3) Si le type de colonne est une chaîne, il doit être mis entre guillemets dans la condition, sinon l'index ne sera pas utilisé;

2.4 Chaque moteur prend en charge les index

Comparaison des types de moteur :

Index   Indice MyISAM   Indice InnoDB  Indice mémoire   Indice B-tree  Support       Supporté Hash index  Non pris en charge     Indice de hachage  Non pris en charge     Supporté  Indice R-Tree  Support         Non pris en charge        Non pris en charge   Index du texte intégral Non pris en charge     Non pris en charge       Non pris en charge

MyISAM，InnoDB,Memonry三个常用MySQL
2.5 Structure d'index dans la base de données


Parce que lors de l'utilisation d'un arbre binaire, la profondeur de l'arbre binaire est trop grande, ce qui entraîne des lectures et écritures d'E/S trop fréquentes, ce qui conduit à son tour à une faible efficacité des requêtes. Par conséquent, en utilisant une structure arborescente à plusieurs voies, diverses opérations sur l’arbre B peuvent maintenir la hauteur de l’arbre B à un niveau bas.

L'arbre B est également appelé arbre de recherche multi-chemins équilibré. Les caractéristiques d'un arbre B d'ordre m sont les suivantes :

• 1. Chaque nœud de l'arborescence contient au plus m enfants (m>=2) ;
• 2 À l'exception du nœud racine et des nœuds feuilles, chaque autre nœud a au moins (plafond (m/2)) ; où ceil(x) est une fonction qui prend la limite supérieure );
• 3. Le nœud racine a au moins 2 enfants (sauf si le B-tree ne contient qu'un seul nœud : le nœud racine
• 4. Les points apparaissent tous dans la même couche et les nœuds feuilles ne contiennent aucune information de mot-clé (peuvent être considérés comme des nœuds externes ou des nœuds sur lesquels la requête a échoué, et les pointeurs pointant vers ces nœuds sont nuls (Remarque : les nœuds feuilles) ; mais ne faites pas d'enfants et de pointeurs vers des enfants, ces nœuds existent également, et il y a des éléments similaires à l'arbre rouge-noir, chaque pointeur nul est considéré comme un nœud feuille, mais il n'est pas dessiné)
  
  B+ tree.
  
  Où est-il Dans quelles circonstances est-il approprié de créer un index ?
  (1) Créer un index pour les champs qui apparaissent souvent après les mots-clés classer par, regrouper par, distinct ; définir des champs de résultats d'union et d'autres opérations d'ensemble, et créer un index Le but est le même que ci-dessus
  (3) Créer des index pour les champs qui sont souvent utilisés pour la sélection de requêtes ; comme liens de table ;
  (5) Pensez à utiliser la couverture d'index, qui nécessite peu de données. Si l'utilisateur n'interroge souvent que quelques champs de la table mise à jour, vous pouvez envisager de créer des index sur ces champs pour changer l'analyse de la table en une analyse d'index.
  
  
3. Ordre de la syntaxe Mysql

C'est-à-dire que lorsque les mots-clés suivants existent dans SQL, ils doivent conserver cet ordre :

select[distinct], from, join (comme left join), on,where, group

par, avoir, union, ordre par, limite

4. Séquence d'exécution de MySQL

Autrement dit, lors de l'exécution, sql est exécuté dans l'ordre suivant :

à partir de, sur, rejoindre, où, grouper par, avoir, sélectionner, distinct, union, commander par

grouper par doit être utilisé avec des fonctions d'agrégation,

Par exemple :

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;

implémenter une requête multi-table (jointure interne)

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

en utilisant sélectionner à partir d'où peut également être utilisé Implémenter

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

5. Procédure stockée select from where同样可以实现

delimiter $$
create procedure procedure_bill()
comment '查询所有销售情况'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

五、存储过程

call procedure_bill();

调用存储过程

show procedure status like 'procedure_bill';

查看存储过程

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

六、建立多对多数据表关系

在数据库中，如果两个表的之间的关系为多对多的关系，如：“学生表和课程表”，一个学生可以选多门课，一门课也可以被多个学生选;根据数据库的设计原则，应当形成第三张关联表。
步骤1:创建三张数据表Student ,Course,Stu_Cour

/*添加外键约束*/
ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);
ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id);

第二步:为Stu_Cour关联表添加外键

rrreee

完成创建！

• 注:为已经添加好的数据表添加外键:
  -语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

七、数据库引擎（存储引擎）

当你访问数据库时，不管是手工访问，还是程序访问，都不是直接读写数据库文件，而是通过数据库引擎去访问数据库文件。

以关系型数据库为例，发SQL语句给数据库引擎，数据库引擎解释SQL语句，提取出你需要的数据返回给你。因此，对访问者来说，数据库引擎就是SQL语句的解释器。

7.1 MYISAM和InnoDB引擎的区别

主要区别：

• MYISAM 是非事务安全型的，而InnoDB是事务安全型；
• NYISAM锁的粒度是表级锁，而InnoDB支持行级锁；
• MYISAM支持全文本索引，而InnoDB不支持全文索引
• MYISAM相对简单，所以在效率上要优于InnoDB,小型应用可以考虑使用MYISAM;
• MYISAM表是保存成文件的形式，在跨平台的数据转移中使用MYISAM存储会省去不少的麻烦;
• （6）InnoDB表比MYISAMrrreee
Appeler la procédure stockée

rrreee

Afficher la procédure stockée

rrreee🎜 6. Établir une relation de table de données plusieurs à plusieurs 🎜🎜 Dans la base de données, si la relation entre deux tables est composée de plusieurs paires. Relations multiples, telles que : « table des étudiants et horaire des cours », un étudiant peut choisir plusieurs cours, et un cours peut également être choisi par plusieurs étudiants selon les principes de conception de la base de données ; une troisième table d'association devrait être formée. 🎜 Étape 1 : Créez trois tables de données Student, Course, Stu_Cour🎜rrreee🎜Étape 2 : Ajoutez des clés étrangères à la table associée Stu_Cour🎜rrreee🎜Création complète ! 🎜🎜🎜Remarque : Ajoutez des clés étrangères à la table de données déjà ajoutée :🎜 - Syntaxe : alter table nom de la table ajouter une contrainte FK_ID clé étrangère (le nom de votre champ de clé étrangère) REFERENCES nom de la table étrangère (clé primaire de la table correspondante) Nom du champ);🎜🎜🎜Exemple : alter table tb_active ajouter une contrainte FK_ID clé étrangère (user_id) RÉFÉRENCES tb_user(id);🎜🎜Moteur de base de données (moteur de stockage)🎜🎜Quand. Lorsque vous accédez à la base de données, qu'il s'agisse d'un accès manuel ou d'un accès par programme, vous ne lisez ni n'écrivez directement le fichier de base de données, mais accédez au fichier de base de données via le moteur de base de données. 🎜🎜Prenons l'exemple d'une base de données relationnelle. Envoyez une instruction SQL au moteur de base de données. Le moteur de base de données interprète l'instruction SQL, extrait les données dont vous avez besoin et vous les renvoie. Par conséquent, pour les visiteurs, le moteur de base de données est l’interprète des instructions SQL. 🎜

🎜7.1 La différence entre les moteurs MYISAM et InnoDB🎜

🎜Principale différence : 🎜🎜🎜MYISAM n'est pas sécurisé sur le plan transactionnel, tandis que InnoDB l'est sur le plan transactionnel type sécurisé ; 🎜🎜la granularité du verrouillage NYISAM est un verrouillage au niveau de la table, tandis que InnoDB prend en charge le verrouillage au niveau des lignes ; 🎜🎜MYISAM prend en charge le texte intégral ; index, tandis que InnoDB ne prend pas en charge l'index en texte intégral 🎜🎜MYISAM est relativement simple, il est donc meilleur que InnoDB en termes d'efficacité. Les petites applications peuvent envisager d'utiliser MYISAM  ; 🎜🎜La table MYISAM est enregistrée sous forme de fichier. L'utilisation du stockage MYISAM dans le transfert de données multiplateforme permettra d'enregistrer un fichier. beaucoup de problèmes ;🎜🎜 (6) Les tables InnoDB sont plus sécurisées que les tables MYISAM Vous pouvez basculer les tables non transactionnelles vers les tables transactionnelles sans perdre de données ; scénarios : 🎜

• MYISAM gère les tables non transactionnelles. Il offre un stockage et une récupération à grande vitesse, ainsi que des capacités de recherche en texte intégral si l'application doit effectuer un grand nombre de selectcode> requêtes, alors <code>MYISAM est un meilleur choix. <code>MYISAM管理非事务表，它提供高速存储和检索，以及全文搜索能力，如果应用中需要执行大量的select查询，那么MYISAM是更好的选择。
• InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的insert或update操作，则应该使用innodb
InnoDB est utilisé pour les applications de traitement de transactions et possède de nombreuses fonctionnalités, notamment la prise en charge des transactions ACID. Si votre application doit effectuer un grand nombre d'opérations insert ou update, vous devez utiliser innodb, qui peut améliorer les performances des applications multi-utilisateurs. opérations concurrentes.

8. Paradigme de base de données

Actuellement, il existe 6 paradigmes dans les bases de données relationnelles : première forme normale {1NF}, deuxième forme normale {2NF}, troisième forme normale {3NF}, forme normale Bass-Codd {BCNF}, quatrième forme normale forme Forme normale {4NF}, cinquième forme normale {5NF, également connue sous le nom de forme normale parfaite}. Le paradigme qui répond aux exigences minimales est la première forme normale. Sur la base de la première forme normale, celle qui répond en outre aux exigences les plus standard est appelée la deuxième forme normale {2NF}, et les autres paradigmes emboîtent le pas. De manière générale, la base de données n'a besoin que de satisfaire la troisième forme normale (3NF). .

Paradigme :

• 1NF : Assurez-vous que chaque colonne reste atomique ;
• 2NF : Assurez-vous que chaque colonne du tableau est liée à la clé primaire (clé primaire conjointe)
• 3NF : Assurez-vous que chaque colonne du tableau est directement lié à la clé primaire (Clé étrangère) ;
• BCNF : Sur la base de 1NF, tout attribut non primaire ne peut dépendre du sous-ensemble de clé primaire (sur la base de 3NF, la dépendance vis-à-vis du sous-ensemble de clé primaire est éliminée) ;
• 4NF : Il est nécessaire de combiner plusieurs paires dans la même table Supprimer plusieurs relations
• 5NF : Rétablir la structure originale à partir de la structure finale

Apprentissage recommandé : Tutoriel vidéo mysql

🎜 ;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!