Grundlagen der PHP-Datenbank

Transaktion

Die vier Merkmale der Transaktion (ACID):

  Atomizität, Konsistenz, Isolation, Haltbarkeit
- (1) Atomizität
Alle erforderlichen Vorgänge in der gesamten Transaktion müssen entweder erfolgreich übermittelt werden oder alle sind fehlgeschlagen und werden zurückgesetzt.
- (2) Konsistenz
Stellen Sie die Konsistenz der Daten in der Datenbank vor und nach der Operation sicher. (Zum Beispiel Übertragungen zwischen mehreren Konten eines Benutzers, aber der Gesamtbetrag des Benutzers bleibt unverändert)
- (3) Isolierung
Die Isolierung erfordert eine Transaktion, um die Daten in der Datenbank zu ändern, bevor sie übermittelt wird ist für andere Transaktionen nicht sichtbar. (Das heißt, Transaktionen müssen seriell ausgeführt werden)

Im SQL-Standard sind vier Arten der Isolation definiert: (Die folgende Isolation reicht von niedrig nach hoch und die Parallelität von hoch nach niedrig)

1) Nicht festgeschriebener Lesevorgang ist die niedrigste Isolationsstufe, die es anderen Transaktionen ermöglicht, nicht festgeschriebene Daten zu sehen, was zu schmutzigen Lesevorgängen führt.

2) Read Committed
Da die Datenbank lese- und schreibgetrennt ist, wird die Lesesperre beim Lesen der Transaktion erworben, aber sofort nach dem Aufheben der Lesesperre freigegeben Wenn Sie es erneut lesen, werden Sie feststellen, dass die Ergebnisse des Lesens der Daten vorher und nachher unterschiedlich sind, was zu einem nicht wiederholbaren Lesen führt. (Lesesperren müssen nach der Übermittlung der Transaktion nicht freigegeben werden, während Schreibsperren nach der Übermittlung der Transaktion freigegeben werden müssen)

3) Wiederholbares Lesen
Alle durch Select erhaltenen Daten können nicht geändert werden, so dass eine Transaktion erfolgt kann vermieden werden Die Situation des inkonsistenten Vorher-Nachher-Lesens. Es gibt jedoch keine Möglichkeit, das Phantomlesen zu steuern, da andere Transaktionen die ausgewählten Daten zu diesem Zeitpunkt nicht ändern können, aber sie können Daten hinzufügen

4) Serialisierbar
Alle Transaktionen werden nacheinander ausgeführt Phantomlesen kann vermieden werden. Bei Datenbanken, die eine Parallelitätskontrolle basierend auf Sperren implementieren, erfordert die Serialisierung, dass bei der Ausführung von Bereichsabfragen Bereichssperren erworben werden. Wenn die Datenbank keine Parallelitätskontrolle basierend auf Sperren implementiert, werden Transaktionen erkannt, die gegen serielle Vorgänge verstoßen. , muss die Transaktion zurückgesetzt werden.

Zusammenfassung: Die vier Ebenen werden schrittweise verbessert und jede Ebene löst das Problem. Je höher die Transaktionsebene, desto schlechter ist die Leistung (Read Committed kann verwendet werden)

Isolation Ebene                Dirty Reading kann nicht wiederholt gelesen werden. >Serialisierbar    unmöglich  unmöglich   unmöglich

Zusammenfassung: Nicht festgeschriebene Lesevorgänge führen zu schmutzigen Lesevorgängen –> Festgeschriebene Lesevorgänge lösen fehlerhafte Lesevorgänge, führen jedoch zu nicht wiederholbaren Lesevorgängen –> Wiederholbarer Lesevorgang löst das Problem Problem inkonsistenter Leseergebnisse, verursacht aber Phantom-Lesevorgänge (nicht vorher, sondern jetzt) ​​-> Serialisierbar löst Phantom-Lesevorgänge, fügt jedoch viele Bereichssperren hinzu, die zu Sperrzeitüberschreitungen führen können.

(4) Persistenz Sobald die Wird die Transaktion festgeschrieben, werden die vorgenommenen Änderungen dauerhaft in der Datenbank gespeichert. Auch bei einem Systemabsturz gehen die übermittelten geänderten Daten nicht verloren.

Dirty Read, nicht wiederholbarer Lesevorgang und Phantom Read

(1) Dirty Read (für Rollback-Vorgang): Transaktion T1 hat den Inhalt einer Zeile von Datensätzen aktualisiert, das Ergebnis jedoch nicht festgeschrieben Bei der Änderung liest die Transaktion T2 die aktualisierte Zeile, und dann führt T1 einen Rollback-Vorgang durch, wodurch die gerade vorgenommene Änderung abgebrochen wird. Jetzt ist die Anzahl der von T2 gelesenen Zeilen ungültig (eine Transaktion liest eine andere Transaktion);

(2) Nicht wiederholbares Lesen (für geänderte Vorgänge): Transaktion T1 liest eine Zeile mit Datensätzen, gefolgt von T2, die die geändert hat Dann las T1 die Zeile mit den Datensätzen erneut und stellte fest, dass sich die Ergebnisse von den gerade gelesenen Ergebnissen unterschieden.

(3) Phantom-Lesen (für Aktualisierungsvorgänge): Transaktion T1 liest die von einer angegebenen WHERE-Klausel zurückgegebene Ergebnismenge, und dann fügt Transaktion T2 eine neue Datensatzzeile ein, die die Anforderungen von T1 genau erfüllen kann verwendete Abfragekriterien. Dann ruft T1 die Tabelle erneut ab, sieht aber erneut die von T2 eingefügten Daten. (Ich habe es beim ersten Mal nicht gesehen, aber beim zweiten Mal)

Index

2.1 Was sind die Vor- und Nachteile von Datenbankindizes und wann schlagen Datenbankindizes fehl?

Funktionen des Index

(1) Es kann den Datenbankabruf beschleunigen;

(2) Es kann nur für Tabellen erstellt werden, nicht für Ansichten

(3) Es kann direkt oder indirekt erstellt werden;

(4) Indizes können beim Optimieren verwendet werden.

(5) Verwenden Sie den Abfrageprozessor, um SQL-Anweisungen gleichzeitig auszuführen.

Vorteile des Index

(1) Erstellen Sie einen eindeutigen Index, um die Eindeutigkeit jeder Datenzeile in der Datenbanktabelle sicherzustellen. (2) Beschleunigen Sie den Datenabruf erheblich. Dies ist der Hauptgrund für die Erstellung eines Index. 3) Die Beschleunigung der Verknüpfungen zwischen Datenbanktabellen ist besonders wichtig, um die referenzielle Integrität der Datenbank zu erreichen. (4) Bei der Verwendung von Gruppierungs- und Sortierklauseln zum Abrufen kann dies auch die zum Gruppieren und Sortieren in Abfragen erforderliche Zeit erheblich verkürzen.
(5) Durch die Verwendung von Indizes können Sie Optimierungs-Hider in Abfragen verwenden, um die Systemleistung zu verbessern

Nachteile von Indizes

(1) Anforderungen für die Erstellung und Pflege von Indizes Es braucht Zeit, und diese Zeit nimmt mit zunehmender Anzahl zu; (2) Indizes müssen zusätzlich zum von der Datentabelle belegten Datenraum auch eine bestimmte Menge an physischem Platz belegen etabliert, dann ist der erforderliche Speicherplatz größer;

(3) Beim Hinzufügen, Löschen und Ändern von Daten in der Tabelle muss auch der Index gepflegt werden, was die Geschwindigkeit der Datenpflege verringert Klassifizierung

(1) Gewöhnlicher Index (es gibt keine Einschränkungen.)
(2) Eindeutiger Index (der Wert der Indexspalte muss eindeutig sein, aber Nullwerte sind zulässig.)
(3) Primärschlüsselindex (Ein spezieller eindeutiger Index, der keine Nullwerte zulässt. Im Allgemeinen wird der Primärschlüsselindex gleichzeitig mit der Erstellung der Tabelle erstellt.)

(4) Zusammengesetzter Index

(5) Der Clustered-Index wird gemäß dem Primärschlüssel jedes Tabellen-A-B-Baums erstellt und die Zeilendatensatzdaten der gesamten Tabelle werden in den Blattknoten gespeichert, sodass die Blattknoten des Clustered-Index auch zu Datenseiten werden.

(6) Nicht gruppierter Index (Hilfsindex) (der Seitenknoten speichert nicht eine ganze Zeile von Datensätzen).

Indexfehler

(1) Wenn eine Bedingung vorliegt oder vorhanden ist, wird diese nicht verwendet (oder so wenig wie möglich verwendet); 🎜> (2) Wie die Abfrage beginnt mit %, zum Beispiel SELECT * FROM mytable WHEREt Name like'�min' (3) Wenn der Spaltentyp eine Zeichenfolge ist, muss er in der Bedingung in Anführungszeichen gesetzt werden. andernfalls wird der Index nicht verwendet;

Jede Engine unterstützt Indizes: (Core versteht B-Tree-Index)

Vergleich von drei häufig verwendeten MySQL-Engine-Typen: MyISAM, InnoDB und Memory

Index  MyISAM-Index  InnoDB-Index  Speicherindex

B-TREE-Index unterstützt die Unterstützung
Hash-Index unterstützt nicht die Unterstützung nicht
R-Tree-Index unterstützt die Unterstützung nicht

Volltext-Index unterstützt dies nicht , unterstützt

in der Datenbank in der Datenbank-Indexstruktur nicht? Unter welchen Umständen ist es sinnvoll, einen Index zu erstellen?

Indexstruktur in der Datenbank?
 Denn bei Verwendung eines Binärbaums ist die Tiefe des Binärbaums zu groß, was zu zu häufigem E/A-Lesen und Schreiben führt, was wiederum zu einer geringen Abfrageeffizienz führt. Daher können verschiedene Operationen am B-Baum unter Verwendung einer Mehrwege-Baumstruktur die Höhe des B-Baums niedrig halten.
B-Baum wird auch als ausgeglichener Mehrpfad-Suchbaum bezeichnet. Die Merkmale eines B-Baums m-Ordnung sind wie folgt:
- 1. Jeder Knoten im Baum enthält höchstens m Kinder (m> = 2);
- 2. Mit Ausnahme des Wurzelknotens und der Blattknoten hat jeder andere Knoten mindestens (ceil (m/2)) Kinder (wobei ceil (x) eine Funktion mit einer Obergrenze ist); >- 3. Wurzel Der Knoten hat mindestens 2 untergeordnete Elemente (es sei denn, der B-Baum enthält nur einen Knoten: den Wurzelknoten

- 4. Alle Blattknoten erscheinen auf derselben Ebene und die Blattknoten enthalten keinen beliebige Schlüsselwortinformationen (Sie können sehen, ob es sich um einen externen Knoten oder einen Knoten handelt, bei dem die Abfrage fehlgeschlagen ist, die Zeiger, die auf diese Knoten zeigen, sind null); (Hinweis: Blattknoten haben einfach keine untergeordneten Knoten und Zeiger auf untergeordnete Knoten. Diese Knoten existieren auch und haben Elemente, ähnlich wie Rot und Schwarz. Im Baum wird jeder Nullzeiger als Blattknoten betrachtet, aber nicht gezeichnet)

B-Baum

Unter welchen Umständen ist es angebracht, einen Index zu erstellen

(1) Erstellen Sie einen Index für Felder, die häufig nach den Schlüsselwörtern „Ordnung nach“, „Gruppierung nach“ und „Eindeutig“ erscheinen (2) Ergebnismengenfelder in Vereinigungs- und anderen Mengenoperationen Erstellen Sie einen Index. Der Zweck der Indizierung ist derselbe wie oben. (3) Erstellen Sie einen Index für Felder, die häufig für die Abfrageauswahl verwendet werden. 4) Erstellen Sie einen Index für Attribute, die häufig als Tabellenverknüpfungen verwendet werden. (5) Erwägen Sie die Verwendung einer Indexabdeckung. Für Tabellen, deren Daten selten aktualisiert werden, können Benutzer die Erstellung von Indizes für diese in Betracht ziehen Felder, wodurch Tabellenscans in Indexscans umgewandelt werden.

Mysql-Syntaxreihenfolge

Das heißt, wenn die folgenden Schlüsselwörter in SQL vorhanden sind, müssen sie diese Reihenfolge beibehalten:
select[distinct], from, join (z. B. left join), on, where , gruppieren nach, mit, Union, bestellen nach, begrenzen;

Mysql-Ausführungsreihenfolge

Das heißt, bei der Ausführung wird SQL in der folgenden Reihenfolge ausgeführt:
von, an, beitreten , wo , gruppieren nach, mit, auswählen, verschieden, vereinen, bestellen nach
Gruppieren nach sollte zusammen mit Aggregatfunktionen verwendet werden, zum Beispiel:
wählen Sie a.Customer,sum(a.OrderPrice) aus Bestellungen a wo a.Customer= 'Bush' oder a.Customer = 'Adams' group by a.Customer
Mehrtabellenabfrage implementieren (Inner Join)
Wählen Sie u.uname,a.addr aus lm_user u Inner Join lm_addr a on u.uid = a.uid;
Mit select from where kann auch erreicht werden
select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

Gespeicherte Prozedur

Trennzeichen

createprocedureprocedurebill()comment'Query all sales'beginselectbillid,txtime,amtfromlmbill;end

delimiter;
Gespeicherte Prozedur aufrufen
call procedure_bill ();
Gespeicherte Prozeduren anzeigen
Prozedurstatus wie „procedure_bill“ anzeigen;

Eine Viele-zu-Viele-Datentabellenbeziehung herstellen in der MySQL-Datenbank

 Wenn in der Datenbank die Beziehung zwischen zwei Tabellen eine Viele-zu-Viele-Beziehung ist, wie zum Beispiel: „Studententabelle und Kursplan“, kann ein Student mehrere Kurse und einen Kurs auswählen kann auch von mehreren Studierenden gewählt werden ;Entsprechend den Gestaltungsprinzipien der Datenbank sollte eine dritte Assoziationstabelle gebildet werden.
Schritt 1: Erstellen Sie drei Datentabellen Student, Course, Stu_Cour

/**学生表*/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)
)

Schritt 2: Fügen Sie Fremdschlüssel zur Stu_Cour-bezogenen Tabelle hinzu

/*添加外键约束*/
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);

Schließen Sie die Erstellung ab!

Hinweis: Fügen Sie Fremdschlüssel zur bereits hinzugefügten Datentabelle hinzu:
-Syntax: Tabelle ändern, Tabellenname, Einschränkung hinzufügen, FK_ID, Fremdschlüssel (Name Ihres Fremdschlüsselfelds), REFERENZEN, Name der Fremdtabelle (entsprechendes Primärschlüsselfeld der Tabelle). Name); Beispiel: Tabelle ändern tb_active Einschränkung hinzufügen FK_ID Fremdschlüssel (Benutzer-ID) REFERENZEN tb_user(id);

SQL-Optimierung

KEINE SQL-Datenbank

Häufig verwendet Was sind die Datenbanken? Haben Sie jemals Redis verwendet?

Häufig verwendete relationale Datenbanken:
Mysql, SQLServer, Oracle
Häufig verwendete schemalose Datenbanken:
MongoDB, Merncached, Redis...
Redis
(1) Redis ist eine sehr schnelle nicht relationale Datenbank, die Zuordnungen zwischen Schlüsseln und fünf verschiedenen Werttypen speichern und im Speicher gespeicherte Schlüssel-Wert-Paardaten auf der Festplatte speichern kann.
(2) Im Vergleich zu Merncached
1) Beide können zum Speichern von Schlüsselwertzuordnungen verwendet werden und ihre Leistung ist nahezu gleich.
2) Redis kann Daten automatisch auf zwei verschiedene Arten schreiben.
3) Zusätzlich zum Speichern gewöhnlicher Zeichenfolgenschlüssel kann Redis auch vier weitere Datenstrukturen speichern.
4) Redis kann sowohl als Hauptdatenbank als auch als Hilfsdatenbank für andere verwendet werden Speichersysteme;

Datenbank-Engine (Speicher-Engine)

Was ist die Engine?
 Wenn Sie auf die Datenbank zugreifen, sei es manueller Zugriff oder Programmzugriff, lesen und schreiben Sie die Datenbankdatei nicht direkt, sondern greifen über die Datenbank-Engine auf die Datenbankdatei zu.
Am Beispiel einer relationalen Datenbank senden Sie eine SQL-Anweisung an die Datenbank-Engine, und die Datenbank-Engine interpretiert die SQL-Anweisung, extrahiert die benötigten Daten und gibt sie an Sie zurück. Für Besucher ist die Datenbank-Engine daher der Interpreter von SQL-Anweisungen.

Der Unterschied zwischen MYISAM- und InnoDB-Engines

Hauptunterschiede:
- (1) MYISAM ist nicht transaktionssicher, während InnoDB transaktionssicher ist;
- (2) Die Granularität von NYISAM-Sperren besteht aus Sperren auf Tabellenebene, während InnoDB Zeilen unterstützt -Level-Sperren;
- (3) MYISAM unterstützt die Volltextindizierung, während InnoDB keine Volltextindizierung unterstützt
- (4) MYISAM ist relativ einfach und daher hinsichtlich der Effizienz besser als InnoDB kann die Verwendung von MYISAM in Betracht ziehen;
- (5) MYISAM-Tabellen werden in Form von Dateien gespeichert. Die Verwendung von MYISAM-Speicher bei der plattformübergreifenden Datenübertragung erspart Ihnen viel Ärger.
- (6) InnoDB-Tabellen sind sicherer als MYISAM-Tabellen und kann sicherstellen, dass die Daten nicht verloren gehen.
Anwendungsszenarien:
- (1) MYISAM verwaltet nicht-transaktionale Tabellen -Schnelles Speichern und Abrufen sowie Volltextsuchfunktionen. Wenn die Anwendung eine große Anzahl von Auswahlabfragen durchführen muss, ist MYISAM die bessere Wahl.
- (2) InnoDB wird für Transaktionsverarbeitungsanwendungen verwendet und verfügt über viele Funktionen, einschließlich ACID-Transaktionsunterstützung. Wenn Ihre Anwendung eine große Anzahl von Einfüge- oder Aktualisierungsvorgängen ausführen muss, sollten Sie innodb verwenden, um die Leistung gleichzeitiger Vorgänge mehrerer Benutzer zu verbessern.

Andere

Was sind die Paradigmen in Datenbanken?

  Derzeit gibt es 6 Paradigmen in relationalen Datenbanken: erste Normalform {1NF}, zweite Normalform {2NF}, dritte Normalform Form {3NF}, Bass-Codd-Normalform {BCNF}, vierte Normalform {4NF}, fünfte Normalform {5NF, auch als perfekte Normalform bekannt}. Das Paradigma, das die Mindestanforderungen erfüllt, ist die erste Normalform. Basierend auf der ersten Normalform wird diejenige, die mehr Standardanforderungen erfüllt, als zweite Normalform {2NF} bezeichnet, und die anderen Paradigmen folgen diesem Beispiel. Im Allgemeinen muss die Datenbank nur die dritte Normalform (3NF) erfüllen. .
Norm:

(1) 1NF: Stellen Sie sicher, dass jede Spalte atomar bleibt.

(2) 2NF: Stellen Sie sicher, dass jede Spalte in der Tabelle mit dem Primärschlüssel (gemeinsamer Primärschlüssel) verknüpft ist Schlüssel);

(3) 3NF: Stellen Sie sicher, dass jede Spalte in der Tabelle direkt mit dem Primärschlüssel (Fremdschlüssel) zusammenhängt;

(4) BCNF: Basierend auf 1NF, alle nicht -primäres Attribut kann nicht von der Primärschlüssel-Set-Abhängigkeit subtrahiert werden (Beseitigung der Abhängigkeit von der Hauptcode-Teilmenge auf Basis von 3NF)

(5) 4NF: erfordert das Löschen der Viele-zu-Viele-Beziehung in derselben Tabelle ;

(6) 5NF :Erstellen Sie die ursprüngliche Struktur aus der endgültigen Struktur