Fassen Sie häufige Interviewfragen zur MySQL-Datenbank zusammen und organisieren Sie sie

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL. Er stellt hauptsächlich einige Zusammenfassungen von Interviewfragen für Datenbankhersteller vor. Ich hoffe, dass er für alle hilfreich ist.

Empfohlenes Lernen: MySQL-Video-Tutorial

1. Gemeinsame Paradigmen von Datenbanken:

• Erste Normalform (1NF): bezieht sich auf die Spalten der Tabelle, die nicht weiter unterteilt werden können, und jede Die Spalte der Tabelle in der Datenbank ist ein unteilbares Basisdatenelement und es können nicht mehrere Werte in derselben Spalte vorhanden sein.
• Zweite Normalform (2NF): Basierend auf 1NF enthält sie auch zwei Teile: Erstens muss die Tabelle einen Primärschlüssel haben. Zweitens müssen die Nicht-Primärschlüsselspalten der Tabelle vollständig vom Primärschlüssel abhängen und dürfen sich nicht nur auf einen Teil des Primärschlüssels verlassen.
• Dritte Normalform (3NF): Auf der Basis Bei 2NF wird die transitive Abhängigkeit von Nicht-Primärschlüsselspalten vom Primärschlüssel eliminiert, und Nicht-Primärschlüsselspalten müssen direkt vom Primärschlüssel abhängen.
• BC-Normalform (BCNF): Basierend auf 3NF wird die transitive Abhängigkeit des Hauptattributs vom Codeteil eliminiert

2. Der Ausführungsprozess der SQL-Anweisung:

2.1 Treiber- und Datenbankverbindungspool:

(1) Bevor der Client mit der Datenbank kommuniziert, stellt er über den Datenbanktreiber eine Verbindung mit MySQL her. Nachdem der Aufbau abgeschlossen ist, sendet er die SQL-Anweisung

(2) Um Reduzieren Sie die Verschlechterung der Systemleistung, die durch häufiges Erstellen und Zerstören von Verbindungen verursacht wird. Der Datenbankverbindungspool verwaltet eine bestimmte Anzahl von Verbindungsthreads. Wenn eine Verbindung benötigt wird, wird sie nach der Verwendung direkt aus dem Verbindungspool abgerufen Der Verbindungspool. Zu den gängigen Datenbankverbindungspools gehören Druid, C3P0, DBCP

2.2 und der Ausführungsprozess der Serverschicht der MySQL-Architektur:

(1) Connector: Hauptsächlich verantwortlich für den Aufbau von Verbindungen mit Clients, den Erhalt von Berechtigungen, die Wartung und Verwaltung Verbindungen

(2) Abfragecache: Wenn gefunden, direkt zurückgeben. Wenn die Abfrage nicht im Cache gefunden werden kann, fragen Sie in der Datenbank ab.

Der MySQL-Cache ist standardmäßig deaktiviert, was bedeutet, dass die Verwendung des Caches nicht empfohlen wird und die gesamte Abfrage-Cache-Funktion in der MySQL8.0-Version gelöscht wurde. Dies ist hauptsächlich auf die Einschränkungen seiner Verwendungsszenarien zurückzuführen:

• Lassen Sie uns zunächst über das Datenspeicherformat im Cache sprechen: Schlüssel (SQL-Anweisung) - Wert (Datenwert). Wenn also die SQL-Anweisung (Schlüssel) etwas anders ist , es wird eine direkte Datenbankabfrage durchgeführt;
• Da die Daten in der Tabelle nicht statisch sind, ändern sich die meisten davon häufig. Wenn sich die Daten in der Datenbank ändern, müssen die entsprechenden zwischengespeicherten Daten, die sich auf diese Tabelle beziehen, entfernt werden
(3) Parser/Analysator: Die Aufgabe des Analysators besteht hauptsächlich darin, eine lexikalische Analyse und Syntaxanalyse für die auszuführende SQL-Anweisung durchzuführen, schließlich den abstrakten Syntaxbaum zu erhalten und dann den Präprozessor zu verwenden, um eine semantische Korrektur am abstrakten Syntaxbaum durchzuführen Überprüfen Sie, ob die Tabelle im abstrakten Syntaxbaum vorhanden ist. Wenn sie vorhanden ist, ermitteln Sie, ob das ausgewählte Projektionsspaltenfeld in der Tabelle vorhanden ist.

(4) Optimierer: Er verwendet hauptsächlich den Syntaxbaum, der nach dem lexikalischen Parsen und dem Syntaxparsen von SQL erhalten wird, durch den Inhalt des Datenwörterbuchs und statistische Informationen und dann durch eine Reihe von Vorgängen, um schließlich einen Ausführungsplan einschließlich der Auswahl abzuleiten Welcher Index soll verwendet werden?

Bei der Analyse, ob eine Indexabfrage verwendet werden soll, wird dieser durch eine

dynamische Datenstichprobenanalyse

ermittelt. Solange er statistisch analysiert wird, kann es zu

Analysefehlern kommen, wenn SQL also nicht ausgeführt wird Bei der Verwendung von index muss dieser Faktor ebenfalls berücksichtigt werden (5) Executor: Gemäß einer Reihe von Ausführungsplänen wird die von der Speicher-Engine bereitgestellte API-Schnittstelle aufgerufen, um die Betriebsdaten aufzurufen und die Ausführung von SQL abzuschließen.

2.3. Ausführungsprozess der Innodb-Speicher-Engine:

• (1) Zuerst ruft der MySQL-Executor die API der Speicher-Engine auf, um die Daten gemäß dem Ausführungsplan abzufragen.
• (2) Die Speicher-Engine fragt zunächst die Daten aus dem Pufferpool ab die Festplatte. Wenn die Abfrage gefunden wird, legen Sie sie in den Cache-Pool Aktualisierungsvorgänge im Pufferpool durchführen
• (5) Die aktualisierten Daten werden im Redo-Log-Puffer aufgezeichnet
• (6) Die Commit-Transaktion führt gleichzeitig mit der Übermittlung die folgenden drei Dinge aus
• (7) (Das Erste) Speichern Sie die Daten im Redo-Log-Puffer. Flashen Sie sie in die Redo-Log-Datei
• (8) (Das Zweite) Schreiben Sie diesen Vorgangsdatensatz in die Bin-Log-Datei
• (9) (Das Dritte) Fügen Sie die Name der Bin-Protokolldatei und Aktualisierungsinhalt in Die Position im Bin-Protokoll wird im Redo-Protokoll aufgezeichnet und am Ende des Redo-Protokolls wird eine Festschreibungsmarkierung hinzugefügt
• (10) Mithilfe eines Hintergrundthreads werden die aktualisierten Daten geleert in unserem Pufferpool bei einer bestimmten Gelegenheit, damit die Daten im Speicher und in der Datenbank vereinheitlicht werden
• 3 Häufig verwendete Speicher-Engines? Was ist der Unterschied zwischen InnoDB und MyISAM?

Die Speicher-Engine ist eine Komponente, die tatsächliche Vorgänge an den zugrunde liegenden physischen Daten ausführt und verschiedene APIs für den Betrieb von Daten für die Serverdienstschicht bereitstellt. Zu den häufig verwendeten Speicher-Engines gehören InnoDB, MyISAM und Memory. Hier stellen wir hauptsächlich die Unterschiede zwischen InnoDB und MyISAM vor: (1) Transaktion: MyISAM unterstützt keine Transaktionen, InnoDB unterstützt Transaktionen

(2) Sperrebene: MyISAM unterstützt nur Sperren auf Tabellenebene, InnoDB unterstützt Sperren auf Zeilenebene und Sperren auf Tabellenebene werden standardmäßig verwendet, Zeilensperren werden jedoch nur verwendet, wenn Daten über den Index abgefragt werden, andernfalls werden Tabellensperren verwendet. Sperren auf Zeilenebene verbrauchen bei jedem Vorgang des Erlangens und Freigebens von Sperren mehr Ressourcen als Tabellensperren. Bei der Verwendung von Zeilensperren kann es zu einem Deadlock kommen, aber bei Sperren auf Tabellenebene gibt es keinen Deadlock

(3) Primärschlüssel und Fremdschlüssel: MyISAM lässt die Existenz von Tabellen ohne Indizes und Primärschlüssel zu und unterstützt keine Fremdschlüssel. Der Primärschlüssel von InnoDB darf nicht leer sein und unterstützt das automatische Wachstum des Primärschlüssels. Wenn der Primärschlüssel oder der nicht leere eindeutige Index nicht festgelegt ist, wird automatisch ein 6-Byte-Primärschlüssel generiert und unterstützt Integritätsbeschränkungen für Fremdschlüssel. (4) Index Struktur: MyISAM und InnoDB verwenden alle B+-Baumindizes. Die Datenfelder des Primärschlüsselindex und des Hilfsindex von MyISAM sind die Adressen, an denen Zeilendatensätze gespeichert werden. Das Datenfeld des Primärschlüsselindex von InnoDB speichert jedoch nicht die Adresse des Zeilendatensatzes, sondern den gesamten Dateninhalt der Zeile, während das Datenfeld des Hilfsindex den Wert des Primärindex speichert.

Da der Hilfsindex von InnoDB den Wert des Primärschlüsselindex speichert, muss der Index bei Verwendung des Hilfsindex zweimal abgerufen werden: Rufen Sie zuerst den Hilfsindex ab, um den Primärschlüssel zu erhalten, und verwenden Sie dann den Primärschlüssel, um die Datensätze im abzurufen Primärindex. Aus diesem Grund wird nicht empfohlen, zu lange Felder als Primärschlüssel zu verwenden: Da der Hilfsindex die Primärschlüsselspalte enthält, werden andere Hilfsindizes größer, wenn der Primärschlüssel zu lange Felder verwendet. Versuchen Sie daher, die zu definieren Primärschlüssel so klein wie möglich.

(5) Volltextindex: MyISAM unterstützte keinen Volltextindex vor Version 5.6 und begann später, Volltextindex zu unterstützen Anzahl der Zeilen in der Tabelle:

① MyISAM: Speichert die Gesamtzahl der Zeilen in der Tabelle. Wenn Sie „select count() from table“ verwenden, wird der Wert direkt entnommen, ohne dass ein vollständiger Tabellenscan erforderlich ist.

② InnoDB: Die Gesamtzahl der Zeilen in der Tabelle wird nicht gespeichert. Wenn Sie select count() from table verwenden, müssen Sie die gesamte Tabelle durchlaufen, was viel Geld kostet.

(7) Speicherstruktur:

• ① MyISAM speichert drei Dateien auf der Festplatte: .frm-Datei zum Speichern der Tabellendefinition, .MYD-Datei zum Speichern von Daten und .MYI-Datei zum Speichern des Index.
• ② InnoDB: Speichern Sie Daten und Indizes im Tabellenbereich. Alle Tabellen werden in derselben Datendatei gespeichert. Die Größe der InnoDB-Tabelle ist nur durch die Größe der Betriebssystemdatei begrenzt, die im Allgemeinen 2 GB beträgt.

(8) Speicherplatz:

• ① MyISAM: kann komprimiert werden und hat weniger Speicherplatz. Es werden drei verschiedene Speicherformate unterstützt: statische Tabelle (Standard, aber beachten Sie, dass am Ende der Daten keine Leerzeichen stehen dürfen, da diese entfernt werden), dynamische Tabelle und komprimierte Tabelle.
• ② InnoDB: Benötigt mehr Speicher und Speicherplatz, es wird ein eigener dedizierter Pufferpool im Hauptspeicher zum Zwischenspeichern von Daten und Indizes eingerichtet.

(9) Anwendbare Szenarien:

• ① Wenn ACID-Transaktionsfunktionen erforderlich sind, die Rollback- und Absturzwiederherstellungsfunktionen bieten, und eine Parallelitätskontrolle auf Zeilensperrebene erforderlich ist, ist InnoDB eine gute Wahl Wird hauptsächlich zum Abfragen von Datensätzen verwendet. Wenn weit mehr Lesevorgänge als Schreibvorgänge vorhanden sind und keine Datenbanktransaktionsunterstützung erforderlich ist, kann die MyISAM-Engine eine höhere Verarbeitungseffizienz bieten.

Hinweis: Die MyISAM-Speicher-Engine wurde in mysql8.0 aufgegeben Version

• 4. ACID und Umsetzungsprinzip von Transaktionen?

Eine Datenbanktransaktion ist die Grundeinheit der Parallelitätskontrolle. Sie bezieht sich auf eine logische Reihe von Vorgängen, die entweder alle ausgeführt werden oder keiner.

4.1. ACID der Transaktion:

• (1) Atomizität: Eine Transaktion ist eine unteilbare Arbeitseinheit, die entweder erfolgreich ist oder fehlschlägt. Wenn die Transaktion fehlschlägt, muss sie zurückgesetzt werden.
• (2) Isolation: Der Grad, in dem die von einer Transaktion verarbeiteten Daten für andere Transaktionen sichtbar sind, bevor sie übermittelt werden.
• (3) Persistenz: Sobald eine Transaktion festgeschrieben wurde, sind ihre Änderungen an den Daten in der Datenbank dauerhaft.
• (4) Konsistenz: Transaktionen können die Datenintegrität und Geschäftskonsistenz nicht zerstören. Wenn Sie beispielsweise Geld überweisen, bleibt der Gesamtbetrag zwischen beiden Parteien unabhängig davon, ob die Transaktion erfolgreich ist oder fehlschlägt, unverändert.

4.2. ACID-Implementierungsprinzip:

4.2.1. Atomarität wird durch das Rückgängig-Protokoll von MySQL erreicht: Wenn eine Transaktion die Datenbank ändert, generiert InnoDB das entsprechende Rückgängig-Protokoll Wenn ein Fehler auftritt oder ein Rollback aufgerufen wird, wodurch die Transaktion zurückgesetzt wird, können Sie die Informationen im Rückgängig-Protokoll verwenden, um die Daten auf den Zustand vor der Änderung zurückzusetzen. 来

4.2.2. Isolation:

(1) Transaktionsisolationsstufe:

Um die Integrität und Konsistenz der in einer gleichzeitigen Umgebung gelesenen Daten sicherzustellen, bietet die Datenbank vier Transaktionsisolationsstufen: Je höher die Isolationsstufe Je vollständiger und konsistenter die Daten garantiert werden können, desto größer ist jedoch die Auswirkung auf die hohe Parallelitätsleistung und desto geringer ist die Ausführungseffizienz. (Die vier Isolationsstufen nehmen von oben nach unten zu)

Read Uncommitted: Ermöglicht Transaktionen, nicht festgeschriebene Daten anderer Transaktionen während der Ausführung zu lesen.
• Read Committed: Ermöglicht Transaktionen, während der Ausführung Daten zu lesen, die von anderen übermittelt wurden Transaktionen;
• Wiederholbares Lesen (Standardebene): Innerhalb derselben Transaktion sind die Abfrageergebnisse jederzeit konsistent.
• Serialisierung von Lesevorgängen: Alle Transaktionen werden einzeln ausgeführt, und bei jedem Lesevorgang müssen gemeinsame Sperren auf Tabellenebene erworben werden , und Lesen und Schreiben blockieren sich gegenseitig.

(2) Probleme mit der Transaktionsparallelität:

Wenn die Isolation von Transaktionen nicht berücksichtigt wird, kann es in einer Transaktionsparallelitätsumgebung zu Problemen kommen:

Aktualisierungsverlust: Zwei oder mehr Transaktionsvorgänge Wenn dasselbe Die Daten werden dann basierend auf dem ausgewählten Wert aktualisiert. Da jede Transaktion nicht weiß, dass andere Transaktionen vorhanden sind, tritt ein Problem mit verlorenen Aktualisierungen auf: Die letzte Aktualisierung überschreibt die von anderen Transaktionen vorgenommenen Aktualisierungen.
• Dirty Read: bedeutet, dass Transaktion A auf Daten zugreift und die Daten geändert hat (die Transaktion wird zu diesem Zeitpunkt nicht festgeschrieben). Später bricht Transaktion A das Rollback ab und stellt die geänderten Daten wieder her Originalwert: Die von B gelesenen Daten stimmen nicht mit den Daten in der Datenbank überein, dh die von B gelesenen Daten sind schmutzige Daten.
• Nicht wiederholbares Lesen: Innerhalb einer Transaktion werden dieselben Daten mehrmals gelesen, aber da eine andere Transaktion die Daten in diesem Zeitraum geändert und festgeschrieben hat, sind die vorher und nachher gelesenen Daten inkonsistent.
• Phantom-Lesen: In einer Transaktion Dieselben Daten (normalerweise eine Bereichsabfrage) werden zweimal gelesen, aber da eine andere Transaktion Daten hinzufügt oder löscht, sind die Ergebnisse beider Male inkonsistent.

Unterschiedliche Transaktionsisolationsstufen führen zu unterschiedlichen Parallelitätsproblemen in einer gleichzeitigen Umgebung:

(3) Implementierungsprinzip der Transaktionsisolation:

Innodb-Transaktionsisolationsstufe wird durch MVVC und den Sperrmechanismus bestimmt. Implementiert:

① MVCC (Multi-Version Concurrency Control, Multi-Version-Parallelitätskontrolle) ist eine spezielle Möglichkeit für die InnoDB-Speicher-Engine, Transaktionsisolationsstufen zu implementieren. Die Isolationsstufe „Read Uncommitted“ liest immer die neueste Datenzeile, ohne MVCC zu verwenden. Die Isolationsstufe der Leseserialisierung erfordert das Sperren aller Lesezeilen, was nicht einfach mit MVCC erreicht werden kann.

MVCC wird implementiert, indem zwei versteckte Spalten hinter jeder Datensatzzeile gespeichert werden, wobei eine die Transaktions-ID der Zeile und die andere den Rollback-Segmentzeiger der Zeile speichert. Jedes Mal, wenn eine neue Transaktion gestartet wird, wird automatisch eine neue Transaktions-ID erhöht. Wenn eine Transaktion beginnt, wird die Transaktions-ID in das Transaktions-ID-Feld der von der aktuellen Transaktion betroffenen Zeile eingefügt, und der Zeiger des Rollback-Segments enthält alle Versionsdaten im Zeilendatensatz, der in Form einer Verknüpfung organisiert ist Liste im Rückgängig-Protokoll-Rollback-Protokoll, das heißt, der Wert verweist tatsächlich auf die mit dem Verlauf verknüpfte Liste der Zeile im Rückgängig-Protokoll.

Beim gleichzeitigen Zugriff auf die Datenbank wird die MVCC-Mehrversionsverwaltung für die Daten in der Transaktion durchgeführt, um zu vermeiden, dass Schreibvorgänge Lesevorgänge blockieren, und das Phantomleseproblem der Snapshot-Lesemethode kann durch Vergleichen der Versionen gelöst werden, jedoch für das Phantom Beim Lesen des aktuellen Lesevorgangs kann MVCC das Problem nicht lösen und muss durch eine vorübergehende Tastensperre gelöst werden.

② Sperrmechanismus:

Das grundlegende Funktionsprinzip des MySQL-Sperrmechanismus ist: Bevor eine Transaktion die Datenbank ändert, muss sie die entsprechende Sperre erhalten. Nur die Transaktion, die die Sperre erhält, kann die Daten ändern Dieser Teil der Daten ist gesperrt. Wenn andere Transaktionen Daten ändern müssen, müssen sie warten, bis die aktuelle Transaktion festgeschrieben oder zurückgesetzt wird, um die Sperre aufzuheben.

• Exklusive Sperren lösen schmutzige Lesevorgänge.
• Gemeinsame Sperren lösen nicht wiederholbare Lesevorgänge Die ausgeführte SQL-Anweisung wird in einer Redo-Log-Datei gespeichert. Um die Effizienz zu verbessern, werden die Daten jedoch vor dem Schreiben in das Redo-Log zunächst in den Redo-Log-Puffercache im Speicher geschrieben. Der Schreibvorgang ist wie folgt: Beim Schreiben von Daten in die Datenbank werden die geänderten Daten im Redo-Log-Puffer regelmäßig in die Redo-Log-Datei auf der Festplatte aktualisiert Dies wird als Festplattenspülung bezeichnet (d. h. der Redo-Log-Puffer schreibt das Protokoll in die Redo-Log-Datei auf der Festplatte).
• Die Verwendung des Redo-Log-Puffers kann die Effizienz beim Lesen und Schreiben von Daten erheblich verbessern, bringt aber auch neue Probleme mit sich: Wenn MySQL ausfällt und die im Redo-Log-Puffer geänderten Daten nicht auf die Festplatte im Speicher geleert wurden Dies führt zu Datenverlust und die Dauerhaftigkeit der Transaktion kann nicht garantiert werden. Um die Haltbarkeit der Transaktion sicherzustellen, wird beim Festschreiben der Transaktion die fsync-Schnittstelle aufgerufen, um das Redo-Protokoll zu leeren. Die Aktualisierungsfrequenz wird durch die Variable innodb_flush_log_at_trx_commit gesteuert:

0: bedeutet, dass die Festplatte nicht geleert wird.

1: Bei jeder Transaktion werden die Daten im Pufferpool auf die Festplatte geleert. ，

2: Bei jeder Transaktion werden die Daten im Pufferpool in den der Festplatte entsprechenden Betriebssystemcache geschrieben Datei, anstatt die Festplattendatei direkt einzugeben. Es kann 1 Sekunde dauern, bis die Daten im Betriebssystem-Cache in die Festplattendatei geschrieben werden.

4.2.4. Konsistenz:
• Konsistenz bezieht sich auf die Tatsache, dass Transaktionen die Datenintegrität und Geschäftskonsistenz nicht zerstören können:

Integrität von Daten: Entitätsintegrität, Spaltenintegrität (z. B. Typ, Größe usw.). Die Länge der Felder muss den Anforderungen entsprechen), Fremdschlüsseleinschränkungen usw.

Geschäftskonsistenz: Beispielsweise bleibt bei einer Banküberweisung der Gesamtbetrag zwischen beiden Parteien unverändert, unabhängig davon, ob die Transaktion erfolgreich ist oder fehlschlägt.

• 5. Der Sperrmechanismus in der Datenbank?

Wenn mehrere Transaktionen in der Datenbank gleichzeitig auf dieselben Daten zugreifen und gleichzeitige Vorgänge nicht kontrolliert werden, können falsche Daten gelesen und gespeichert werden, wodurch die Konsistenz der Datenbank zerstört wird. Das grundlegende Funktionsprinzip des MySQL-Sperrmechanismus besteht darin, dass eine Transaktion zuerst die entsprechende Sperre erhalten muss, um die Daten während des Transaktionsvorgangs zu ändern Wenn andere Transaktionen sie ändern müssen, muss Data warten, bis die aktuelle Transaktion festgeschrieben oder zurückgesetzt wird, um die Sperre aufzuheben.

• Je nach verschiedenen Klassifizierungsmethoden können die Sperrtypen in die folgenden Typen unterteilt werden:

Unterteilt nach der Granularität der Sperre: Sperren auf Tabellenebene, Sperren auf Zeilenebene, Sperren auf Seitenebene; Geteilt nach Sperrtyp: gemeinsam genutzt (Sperre S), exklusive Sperre (X-Sperre); Sperre, Sperre auf Seitenebene:

Sperre auf Tabellenebene: Die Sperrebene mit der größten Granularität, die Wahrscheinlichkeit eines Sperrkonflikts ist am höchsten, die Parallelität ist am niedrigsten, aber der Overhead ist gering, die Sperre ist schnell, und es wird kein Deadlock auftreten;

Sperre auf Zeilenebene: Die Ebene mit der kleinsten Granularität, dem Auftreten von Sperrenkonflikten ist am geringsten und die Parallelität ist am höchsten, aber das Hinzufügen von Sperren ist teuer und langsam, und es kommt zu Deadlocks auftreten;

Sperren auf Seitenebene: Die Sperrgranularität ist zwischen Sperren auf Tabellenebene und Sperren auf Zeilenebene begrenzt. Die Sperre ist ein Kompromiss und die Parallelität ist durchschnittlich. Der Overhead und die Sperrzeit sind auch zwischen Tabellensperren und Zeilensperren begrenzt, und es kommt zu Deadlocks. Standardmäßig werden Sperren auf Zeilenebene verwendet, Sperren auf Zeilenebene werden jedoch nur verwendet, wenn Daten über den Index abgefragt werden. Andernfalls werden Sperren auf Tabellenebene verwendet.
• MyISAM- und MEMORY-Speicher-Engines verwenden Sperren auf Tabellenebene;
• BDB-Speicher-Engines verwenden Sperren auf Tabellenebene, unterstützen aber auch Sperren auf Tabellenebene; Arten von Sperren:

Gemeinsame Sperren (S-Sperren, Lesesperren): Mehrere Transaktionen können sich eine S-Sperre für dieselbe Datenzeile teilen, sie können jedoch nur gelesen und nicht geändert werden;

Exklusive Sperren (X-Sperren, Schreibsperren). Sperre): Nachdem eine Transaktion eine exklusive Sperre erworben hat, kann sie Schreibvorgänge für die Datenzeilen innerhalb des Sperrbereichs ausführen. Während des Sperrzeitraums können andere Transaktionen keine Sperren mehr für diesen Teil der Datenzeilen erwerben (gemeinsame Sperren, exklusive Sperren). ), Nur die Transaktion, die die exklusive Sperre erhält, aktualisiert die Daten.

Bei Aktualisierungs-, Lösch- und Einfügevorgängen fügt InnoDB automatisch exklusive Sperren zu den beteiligten Datenzeilen hinzu; bei normalen SELECT-Anweisungen fügt InnoDB keine Sperren hinzu.

• 5.3. InnoDB-Tabellensperren und Absichtssperren:
• Da die InnoDB-Engine die Koexistenz von Zeilensperren und Tabellensperren zulässt, implementiert sie einen Sperrmechanismus mit mehreren Granularitäten. sie werden miteinander in Konflikt geraten. Wenn Sie eine Tabellensperre hinzufügen möchten, müssen Sie zunächst alle Datensätze in der Tabelle durchlaufen, um festzustellen, ob eine exklusive Sperre vorhanden ist. Diese Durchlaufprüfungsmethode ist offensichtlich eine ineffiziente Methode, mit der MySQL Absichtssperren einführt, um Konflikte zwischen Tabellensperren und Zeilensperren zu erkennen.

Absichtssperren sind auch Sperren auf Tabellenebene, unterteilt in Leseabsichtssperren (IS-Sperren) und Schreibabsichtssperren (IX-Sperren). Wenn eine Transaktion einem Datensatz eine Zeilensperre hinzufügen möchte, fügt sie zunächst die entsprechende Absichtssperre zur Tabelle hinzu. Wenn eine Transaktion später die Tabelle sperren möchte, muss sie zunächst nur feststellen, ob die beabsichtigte Sperre vorhanden ist. Wenn sie vorhanden ist, kann die Tabellensperre nicht aktiviert werden, andernfalls muss sie warten, um die Effizienz zu verbessern.

5.4. Implementierung von InnoDB-Zeilensperren und Tastensperren:

InnoDB-Zeilensperren werden durch Sperren von Indexelementen im Index implementiert. Zeilensperren können nur verwendet werden, wenn Daten über einen Index abgerufen werden, andernfalls werden Tabellensperren verwendet.

Um das Phänomen des Phantomlesens zu lösen, wird in InnoDB die Next-Key-Sperre (Next-Key) eingeführt. Laut Index ist es in links offene und rechts geschlossene Intervalle unterteilt. Wenn bei der Durchführung einer Bereichsabfrage der Index erreicht wird und die Daten abgerufen werden können, werden das Intervall, in dem sich der Datensatz befindet, und sein nächstes Intervall gesperrt. Tatsächlich ist Next-Key = Datensatzsperre + Lückensperre

• Lückensperre: Wenn Bereichsabfragen anstelle präziser Abfragen zum Abrufen von Daten verwendet werden und gemeinsame oder exklusive Sperren angefordert werden, sperrt InnoDB die Indexeinträge vorhandener Datensätze die Bereichsbedingungen erfüllen; für Datensätze, deren Schlüsselwerte innerhalb des Bedingungsbereichs liegen, aber nicht vorhanden sind, spricht man von einer Lücke (GAP).
• Datensatzsperre: Wenn Sie einen eindeutigen Index und eine genaue Abfrage der Datensatzexistenz verwenden, verwenden Sie die Datensatzsperre.

5.5, verwenden Sie den Sperrmechanismus, um Parallelitätsprobleme zu lösen:

• löst nicht wiederholbares Lesen
• Pro-Tastensperre löst Phantomlesung

Einzelheiten zum Sperrmechanismus der InnoDB-Speicher-Engine und zum Sperrmechanismus der MyISAM-Speicher-Engine finden Sie in diesem Artikel: MySQL-Datenbank: Sperrmechanismus_Zhang Weipengs Blog-CSDN-Blog_Lock-Mechanismus in der Datenbank

6. Implementierungsprinzip des MySQL-Index:

Index ist im Wesentlichen eine Datenstruktur, die die Abfrageleistung beschleunigt, indem die Anzahl der Zeilen, die durchlaufen werden müssen, reduziert wird

und Volllast vermieden wird Tabellenscans in der Datenbank, ähnlich dem Inhaltsverzeichnis eines Buches, sodass Sie Inhalte schneller finden können. (Eine Tabelle kann bis zu 16 Indizes haben)

6.1. Vor- und Nachteile von Indizes:

(1) Vorteile von Indizes:

Reduzieren Sie die Anzahl der Zeilen, die von der Abfrage abgerufen werden müssen, und beschleunigen Sie sie Führen Sie die Abfrage durch und vermeiden Sie vollständige Tabellenscans. Dies ist auch der Hauptgrund für die Erstellung von Indizes.

• Wenn die Datenstruktur des Index ein B+-Baum ist, kann bei Verwendung von Gruppierung und Sortierung die Zeit für die Gruppierung und Sortierung in der Abfrage erheblich verkürzt werden.
• Durch die Erstellung eines eindeutigen Index können Sie die Einzigartigkeit jeder Datenzeile in der Datenbanktabelle sicherstellen.
• (2) Nachteile des Index:

Wenn die Daten in der Tabelle hinzugefügt, gelöscht und geändert werden, muss auch der Index aktualisiert werden, und die Wartungszeit erhöht sich mit zunehmender Datenmenge.

• Indizes müssen physischen Speicherplatz belegen. Wenn Sie einen Clustered-Index erstellen möchten, ist der erforderliche Speicherplatz größer.

6.2. Indexverwendungsszenarien:

(1) Für welche Spalten werden Indizes erstellt:

Erstellen Sie Indizes für Spalten, die häufig in der WHERE-Klausel erscheinen, um die Beurteilung von Bedingungen zu beschleunigen.

• Spalten, auf die nach Bereich zugegriffen wird, oder Spalten, die in der Gruppe nach oder in der Reihenfolge verwendet werden. Da der Index sortiert wurde, kann der Index verwendet werden, um die Sortierabfragezeit zu beschleunigen.
• Diese Spalten werden häufig für verbundene Spalten verwendet und sind hauptsächlich Fremdschlüssel, die die Verbindung beschleunigen können.
• Als Primärschlüsselspalte erzwingt sie die Eindeutigkeit der Spalte und organisiert die Anordnungsstruktur der Daten in der Tabelle
• (2) Welche Spalten sollten nicht indiziert werden?

Spalten, die nicht stark differenziert sind. Da diese Spalten in den Abfrageergebnissen nur sehr wenige Werte enthalten, z. B. das Geschlecht, machen die Datenzeilen im Ergebnissatz einen großen Teil der Datenzeilen in der Tabelle aus, das heißt, ein großer Teil der Datenzeilen muss vorhanden sein in der Tabelle gesucht. Durch Erhöhen des Index wird der Abruf nicht wesentlich beschleunigt.

Spalten mit wenigen Spalten in der Abfrage sollten nicht indiziert werden. Da diese Spalten selten verwendet werden, verringert das Hinzufügen von Indizes tatsächlich die Systemwartungsgeschwindigkeit und erhöht den Platzbedarf.
• Wenn durch das Hinzufügen eines Index der Anstieg der Änderungskosten weitaus größer ist als die Verbesserung der Abrufleistung, sollte der Index nicht erstellt werden. Beim Hinzufügen von Indizes wird die Abrufleistung verbessert, die Änderungsleistung wird jedoch verringert. Beim Reduzieren von Indizes erhöht sich die Änderungsleistung und die Abrufleistung verringert sich.
• Spalten, die als Text-, Bild- und Bit-Datentypen definiert sind, sollten nicht indiziert werden. Das Datenvolumen dieser Spalten ist entweder recht groß oder weist nur sehr wenige Werte auf.

6.3. Klassifizierung von Indizes:

(1) Gewöhnlicher Index, eindeutiger Index, Primärschlüsselindex, Volltextindex, kombinierter Index.

• Gewöhnlicher Index: Der einfachste Index ohne Einschränkungen
• Einzigartiger Index: Der Wert der Indexspalte muss jedoch eindeutig sein, Nullwerte sind zulässig und es können mehrere NULL-Werte vorhanden sein. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein.
• Primärschlüsselindex: ein spezieller eindeutiger Index, der keine Nullwerte zulässt.
• Volltextindex: Der Volltextindex kann nur für MyISAM-Tabellen verwendet werden und unterstützt nur die Typen CHAR, VARCHAR oder TEXT. Er wird verwendet, um weniger effiziente Operationen wie Fuzzy-Matching zu ersetzen, und kann für einmalige Volltexte verwendet werden Fuzzy-Matching durch kombinierte Mehrfeld-Volltextindizes.
• Kombinierter Index: Hauptsächlich zur Verbesserung der Effizienz von MySQL sollten beim Erstellen eines zusammengesetzten Index die am häufigsten als restriktive Bedingungen verwendeten Spalten in absteigender Reihenfolge ganz links platziert werden.

(2) Clustered-Index und Nicht-Clustered-Index:

Wenn der Index nach der physischen Reihenfolge der Datenspeicherung und der Reihenfolge der Indexwerte klassifiziert wird, kann er in zwei Kategorien unterteilt werden: Clustered-Index und Nicht-Clustered-Index :

• Clustered-Index: Die physische Reihenfolge der Datenspeicherung in der Tabelle stimmt häufig mit der Reihenfolge der Indexwerte überein Änderungen an der physischen Reihenfolge der Datensätze in der Tabelle sind kostspielig, daher ist es nicht geeignet, Clustered-Indizes für häufig aktualisierte Spalten zu erstellen: Eine Indexorganisation, bei der die physische Reihenfolge der Daten in der Tabelle inkonsistent ist mit der Reihenfolge der Indexwerte. Eine Basistabelle kann mehrere Indexe haben.

6.4. Indexdatenstruktur:

Zu den allgemeinen Indexdatenstrukturen gehören: B+Tree, Hash-Index.

(1) Hash-Index: Nur die Speicher-Engine in MySQL unterstützt den Hash-Index, den Standardindextyp der Speichertabelle. Der Hash-Index organisiert die Daten in Form von Hash-Werten, sodass die Abfrageeffizienz sehr hoch ist und sie gleichzeitig lokalisiert werden können.

Nachteile des Hash-Index:

Der Hash-Index kann nur Abfragen gleicher Werte erfüllen, jedoch keine Bereichsabfragen und Sortierungen. Denn nachdem die Daten den Hash-Algorithmus durchlaufen haben, kann sich ihr Größenverhältnis ändern.

• Beim Erstellen eines zusammengesetzten Indexes können Sie nicht nur einige Spalten des zusammengesetzten Indexes für die Abfrage verwenden. Da der Hash-Index mehrere Spaltendaten kombiniert und dann den Hash-Wert berechnet, ist es sinnlos, den Hash-Wert für einzelne Spaltendaten zu berechnen.
• Wenn eine Hash-Kollision auftritt, kann der Hash-Index das Scannen von Tabellendaten nicht vermeiden. Da es nicht ausreicht, nur den Hash-Wert zu vergleichen, müssen Sie den tatsächlichen Wert vergleichen, um festzustellen, ob er den Anforderungen entspricht.

(2) B+Tree-Index: B+Tree ist die am häufigsten verwendete Indexdatenstruktur in MySQL. Es handelt sich um den Indextyp der Innodb- und Myisam-Speicher-Engine-Modi. Der B+Tree-Index erfordert bei der Suche mehrere E/A-Vorgänge vom Wurzelknoten zum Blattknoten. Die Abfragegeschwindigkeit ist nicht so gut wie beim Hash-Index, eignet sich jedoch besser für Vorgänge wie das Sortieren.

Vorteile des B+Tree-Index:

In-Page-Knoten speichern keinen Inhalt, mehr Zeilen können pro IO gelesen werden, wodurch die Anzahl der Festplatten-I/O-Lesevorgänge erheblich reduziert wird

• B+Tree mit sequentiellen Zugriffszeigern: Alle Indexdaten von B+Tree werden auf Blattknoten gespeichert und jedem Blattknoten wird ein Zeiger auf den benachbarten Blattknoten hinzugefügt. Dies geschieht, um die Effizienz der Intervallabfrage zu verbessern.

6.5. Warum B+Tree als Index verwenden:

Der Index selbst ist ebenfalls sehr groß und es ist unmöglich, alles im Speicher zu speichern, daher werden

Indizes häufig in Form eines Indexes auf der Festplatte gespeichert Dateien. In diesem Fall wird während des Indexsuchvorgangs ein Festplatten-E/A-Verbrauch erzeugt. Im Vergleich zum Speicherzugriff ist der Verbrauch des Festplatten-E/A-Zugriffs um mehrere Größenordnungen höher. Daher ist es am wichtigsten, die Qualität eines zu bewerten Datenstruktur als Index. Der Indikator ist die asymptotische Komplexität der Anzahl der Festplatten-I/O-Operationen während des Suchvorgangs. Mit anderen Worten: Die Datenstruktur des

index sollte die Anzahl der Festplatten-E/A-Zugriffe während des Suchvorgangs minimieren. (1) Lokalitätsprinzip und Vorlesen des Programms:

Da der Zugriff auf die Festplatte selbst viel langsamer ist als auf den Hauptspeicher, gepaart mit den Kosten für die mechanische Bewegung, muss zur Verbesserung der Effizienz die Festplatten-E/A erfolgen minimiert. Um dieses Ziel zu erreichen, liest die Festplatte häufig nicht ausschließlich bei Bedarf, sondern jedes Mal im Voraus. Auch wenn nur ein Byte benötigt wird, beginnt die Festplatte an dieser Position und liest sequentiell eine bestimmte Länge an Daten rückwärts Erinnerung. Die theoretische Grundlage dafür ist das berühmte Lokalitätsprinzip der Informatik: Wenn ein Datenelement verwendet wird, werden in der Regel sofort benachbarte Daten verwendet. Die während der Programmausführung benötigten Daten sind in der Regel konzentriert.

Da sequenzielle Lesevorgänge auf der Festplatte sehr effizient sind (keine Suchzeit erforderlich, sehr kurze Spin-Zeit), kann das Vorauslesen die E/A-Effizienz für Programme mit Lokalität verbessern. Die Länge des Vorauslesens ist im Allgemeinen ein ganzzahliges Vielfaches der Seite. Wenn sich die vom Programm zu lesenden Daten nicht im Hauptspeicher befinden, wird eine Seitenfehlerausnahme ausgelöst. Zu diesem Zeitpunkt sendet das System ein Lesesignal an die Festplatte und die Festplatte findet die Startposition der Daten und eine oder mehrere Seiten rückwärts lesen, dann abnormal zurückkehren und das Programm weiter ausführen.

(2) Leistungsanalyse des B+Tree-Index:

Wie oben erwähnt, wird die Anzahl der Festplatten-E/As im Allgemeinen zur Bewertung der Qualität der Indexstruktur verwendet. Beginnen wir mit der B-Tree-Analyse. Ein B-Tree-Abruf erfordert Zugriff auf bis zu h Knoten. Gleichzeitig nutzt die Datenbank geschickt das Prinzip des Festplatten-Vorauslesens, um die Größe eines Knotens auf eine Seite festzulegen. Das heißt, jedes Mal, wenn ein neuer Knoten erstellt wird, wird ein direkter Seitenbereich erstellt. Dadurch wird sichergestellt, dass ein Knoten physisch auf einer Seite gespeichert wird und die Speicherzuweisung des Computers seitenorientiert ist, sodass jeder Knoten vollständig geladen werden kann mit nur einem I/O. Ein Abruf im B-Baum erfordert höchstens h-1 E/A (der Wurzelknoten befindet sich im Speicher) und die Zeitkomplexität beträgt O(h)=O(logdN). In allgemeinen praktischen Anwendungen ist der Außengrad d eine sehr große Zahl, normalerweise mehr als 100, sodass h sehr klein ist. Zusammenfassend lässt sich sagen, dass die Verwendung von B-Tree als Indexstruktur sehr effizient ist.

Für Strukturen wie rot-schwarze Bäume ist die Zeitkomplexität zwar ebenfalls O(h), h ist jedoch offensichtlich viel tiefer, und da Knoten, die logisch nahe beieinander liegen, physisch weit entfernt sein können, kann die IO-Effizienz nicht ausgenutzt werden ist offensichtlich viel schlimmer als das von B-Tree.

Darüber hinaus eignet sich B+Tree besser als Indexdatenstruktur. Der Grund liegt im Out-Grade d des internen Knotens. Aus der obigen Analyse können wir ersehen, dass die Leistung des Index umso besser ist, je größer d ist. Die Obergrenze des Out-Grades d hängt von der Größe des Schlüssels und der Daten im Knoten ab, da die Datendomäne entfernt wird Von den Knoten im B+Tree kann es einen größeren Out-Grad haben, die Anzahl der Festplatten-IOs wird geringer sein.

(3) Vergleich zwischen B+-Baum-Index und B-Baum-Index?

Anhand der Strukturen von B-Tree und B+Tree können wir feststellen, dass der B+-Baum in Dateisystemen oder Datenbanksystemen mehr Vorteile hat als der B-Baum. Die Gründe sind wie folgt:

• (1) B+-Baum ist von Vorteil beim Scannen der Datenbank: B-Tree verbessert die Festplatten-E/A-Leistung, löst aber nicht das Problem der geringen Effizienz der Elementdurchquerung, während B+-Baum nur Blattknoten durchqueren muss, um das Problem des Scannens aller Schlüsselwortinformationen zu lösen, also Bereichsabfrage und Sortieren Für andere Vorgänge bietet der B+-Baum eine höhere Leistung.
• (2) Die Festplatten-IO-Kosten des B+-Baums sind niedriger: Das Datenfeld des internen Knotens des B+-Baums speichert keine Daten, daher ist sein interner Knoten kleiner als der des B-Baums. Wenn alle Schlüsselwörter desselben internen Knotens im selben Plattenblock gespeichert sind, kann der Plattenblock umso mehr Schlüsselwörter enthalten. Je mehr Schlüsselwörter gesucht werden müssen, desto mehr werden gleichzeitig in den Speicher eingelesen, und die Anzahl der E/A-Lese- und Schreibvorgänge wird relativ reduziert.
• (3) Die Abfrageeffizienz des B+-Baums ist stabiler: Da die internen Knoten des B+-Baums nur Indizes von Schlüsselwörtern in Blattknoten sind, speichern sie keine Daten. Daher muss jede Stichwortsuche einen Pfad vom Wurzelknoten zum Blattknoten nehmen. Die Pfadlänge aller Schlüsselwortabfragen ist gleich, was zu einer gleichen Abfrageeffizienz für alle Daten führt.

(4) Implementierung des B+Tree-Index in den InnoDB- und MyISAM-Speicher-Engines von MySQL?

Sowohl MyISAM als auch InnoDB verwenden B+-Baumindizes. Die Datenfelder des Primärschlüsselindex und des Hilfsindex von MyISAM speichern beide die Adresse der Zeile, aber der Primärschlüsselindex von InnoDB speichert nicht die Adresse der Zeile, sondern alle Daten der Zeile . und das Datenfeld des Hilfsindex speichert den Wert des Primärindex.

Indexlängenbeschränkung:

• Wenn für den kombinierten Index von Innodb die Länge jeder Spalte 767 Bytes überschreitet, wird für Spalten mit mehr als 767 Bytes ein Präfixindex verwendet 767 überschreitet, nehmen Sie den Präfixindex (nehmen Sie die ersten 255 Zeichen)
• Für den kombinierten Index von MyISAM darf die Summe der erstellten Indexlängen 1000 Bytes nicht überschreiten, andernfalls wird ein Fehler gemeldet und die Erstellung schlägt für den einspaltigen Index von MyISAM fehl Index, die maximale Länge Es darf 1000 nicht überschreiten, sonst wird ein Alarm ausgelöst, aber die Erstellung ist erfolgreich und die endgültige Erstellung ist ein Präfixindex (nehmen Sie die ersten 333 Zeichen)

7 Strukturoptimierung:

(1) MySQL SQL-Optimierung und Indexoptimierung: https://blog.csdn.net/a745233700/article/details/84455241

(2) MySQL-Tabellenstrukturoptimierung: https://blog.csdn .net/a745233700/article/details /84405087

8. Datenbankparameteroptimierung:

MySQL ist eine IO-intensive Anwendung, deren Hauptaufgabe in der Datenverwaltung und -speicherung liegt. Und wir wissen, dass die Zeit zum Lesen einer Datenbank aus dem Speicher im Mikrosekundenbereich liegt, während die Zeit zum Lesen eines IO von einer normalen Festplatte im Millisekundenbereich liegt. Der Unterschied zwischen beiden beträgt drei Größenordnungen. Um die Datenbank zu optimieren, muss daher zunächst die E/A optimiert und die Festplatten-E/A so weit wie möglich in Speicher-E/A konvertiert werden. Daher optimieren wir bei der Optimierung der Parameter der MySQL-Datenbank hauptsächlich Parameter, die die Festplatten-E/A reduzieren: Verwenden Sie beispielsweise query_cache_size, um die Größe des Abfragecaches anzupassen, und verwenden Sie innodb_buffer_pool_size, um die Größe des Puffers anzupassen

9. Ausführungsplan von EXPLAIN:

Der Ausführungsplan ist ein Abfrageplan, der auf dem abstrakten Syntaxbaum und den statistischen Informationen der zugehörigen Tabellen basiert, die durch die SQL-Anweisung nach Durchlaufen des Abfrageanalysators erhalten werden. Dieser Plan wird automatisch analysiert durch den Abfrageoptimierer generiert. Da es sich um das Ergebnis einer dynamischen Datenabtastung und statistischen Analyse handelt, kann es zu Analysefehlern kommen, das heißt, der Ausführungsplan ist nicht optimal. Verwenden Sie das Schlüsselwort EXPLAIN, um zu erfahren, wie MySQL SQL-Abfrageanweisungen ausführt, die Leistungsengpässe von Select-Anweisungen analysieren und unsere Abfragen verbessern. Die Ergebnisse von EXPLAIN sind wie folgt:

Die wichtigen sind ID, Typ, Schlüssel, key_len und rows, extra:

(1) id: Die ID-Spalte kann als Kennung der SQL-Ausführungsreihenfolge verstanden werden. Es gibt so viele IDs wie Selects.

id-Werte sind unterschiedlich: Je größer der id-Wert, desto höher die Priorität und werden zuerst ausgeführt;
• id-Werte sind gleich: werden nacheinander von oben nach unten ausgeführt;
• id-Spalte ist null : bedeutet, dass es sich um eine Ergebnismenge handelt und nicht um eine Abfrage.

(2) select_type: Der Abfragetyp, der hauptsächlich zur Unterscheidung komplexer Abfragen wie gewöhnlicher Abfragen, gemeinsamer Abfragen, Unterabfragen usw. verwendet wird.

(3) Tabelle: Gibt an, auf welche Tabelle eine Erklärungszeile zugreift

(4) Typ: Zugriffstyp, dh MySQL entscheidet, wie Zeilen in der Tabelle gefunden werden. Vom besten zum schlechtesten: system > ref_or_null > index_subquery > Indizes. Mit Ausnahme von index_merge können andere Typen nur einen Index verwenden. Im Allgemeinen muss sich der Typ auf Referenzebene befinden und die Bereichssuche muss die Bereichsebene erreichen.

system: Es gibt nur eine Datenübereinstimmung in der Tabelle (entspricht der Systemtabelle), die als Sonderfall des const-Typs betrachtet werden kann.
• const: Wird einmal über den Index gefunden, was die Verwendung von anzeigt Primärschlüsselindex oder eindeutiger Index
• eq_ref: Primärschlüssel oder eindeutig. Die Felder im Index werden für die Verbindung verwendet und es wird nur eine Zeile übereinstimmender Daten zurückgegeben.
• ref: Gewöhnlicher Indexscan, der möglicherweise mehrere übereinstimmende Zeilen zurückgibt die Abfragebedingungen.
• Volltext: Die Priorität des Volltextindex ist sehr hoch. Wenn der Volltextindex und der normale Index gleichzeitig vorhanden sind, räumt MySQL der Verwendung des Volltextindex Vorrang ein der Kosten.
• ref_or_null: Ähnlich der ref-Methode, außer dass der Vergleich von Nullwerten hinzugefügt wird.
• index_merge: Zeigt an, dass die Abfrage mehr als zwei Indizes verwendet, die Optimierungsmethode der Indexzusammenführung, und schließlich die Schnittmenge oder Vereinigung annimmt, und oder Bedingungen, die unterschiedliche Indizes verwenden.
• unique_subquery: Wird für In-Form-Unterabfragen verwendet, wobei die Unterabfrage einen eindeutigen Wert ohne doppelte Werte zurückgibt.
• index_subquery: Wird für In-Form-Unterabfragen mit Hilfsindex oder in einer Konstantenliste verwendet. Die Unterabfrage kann wiederholte Werte zurückgeben Verwenden Sie Indizes, um Unterabfragen zu deduplizieren.
• Bereich: Indexbereichsscan, der häufig bei Abfragen mit Operatoren wie >, <, between, in, like usw. verwendet wird.
• Index: Vollständigen Tabellenscan durchsuchen, Indexbaum von Anfang bis Ende durchsuchen;
• All: Durchsuchen Sie die gesamte Tabelle, um übereinstimmende Zeilen zu finden (Obwohl Index und ALL beide die gesamte Tabelle lesen, wird Index aus dem Index gelesen. ALL ist von der Festplatte lesen)
• NULL: MySQL zerlegt die Anweisung während des Optimierungsprozesses und muss während der Ausführung nicht einmal auf die Tabelle oder den Index zugreifen

(5) mögliche_Schlüssel: Indizes, die während der Abfrage verwendet werden können

(6) Schlüssel: Welcher Index wird tatsächlich verwendet, um den Zugriff auf diese Tabelle zu optimieren

(7) Schlüssel_len: Die Indexlänge, die tatsächlich zur Optimierung der Abfrage verwendet wird, dh die Anzahl der im Index verwendeten Bytes. Anhand dieses Werts können Sie berechnen, welche Felder des Index tatsächlich in einem mehrspaltigen Index verwendet werden.

(8)ref: Zeigt an, welches Feld oder welche Konstante mit dem Schlüssel verwendet wird

(9)rows: Schätzen Sie basierend auf Tabellenstatistiken und Indexauswahl grob die Anzahl der Zeilen, die für die Abfrage gelesen werden müssen, hier nicht genau Wert.

(10)extra: Einige weitere zusätzliche Informationen

Index verwenden: abdeckenden Index verwenden
• Indexbedingung verwenden: Die abgefragte Spalte wird nicht vom Index abgedeckt, und die Where-Filterbedingung verwendet den Index
• temporär verwenden: Verwenden einer temporären Tabelle Das Speichern von Zwischenergebnissen wird häufig in Gruppierungs- und Sortierungsvorgängen verwendet. Dies liegt normalerweise daran, dass es keinen Index für die Spalte von „Gruppieren nach“ gibt Wenn Sie dies sehen, bedeutet dies, dass die Abfrage mithilfe der Dateisortierung optimiert werden muss: MySQL verfügt über zwei Möglichkeiten, Abfrageergebnisse zu sortieren: Die eine besteht darin, Indizes zu verwenden, und die andere ist die Dateisortierung (eine externe Sortierung). basierend auf schneller Sortierung mit schlechter Leistung. Wenn die Datenmenge groß ist, ist dies ein CPU-intensiver Prozess, sodass die Sortierleistung durch die Einrichtung geeigneter Indizes optimiert werden kann Einzelheiten zum Ausführungsplan finden Sie in diesem Artikel: https://blog.csdn.net/a745233700/article/details/84335453

• 10. MySQL-Master-Slave-Replikation:

  10.1. Das Prinzip der MySQL-Master-Slave-Replikation:

  Slave ruft die binlog-Binärprotokolldatei vom Master ab und analysiert die Protokolldatei Führen Sie die Vorgänge des Hauptservers auf dem Slave-Server erneut aus, um die Datenkonsistenz sicherzustellen. Da der Master-Slave-Replikationsprozess asynchron ist, können sich die Daten zwischen Slave und Master verzögern und nur die endgültige Konsistenz der Daten kann garantiert werden. Der gesamte Replikationsprozess zwischen Master und Slave wird hauptsächlich durch drei Threads abgeschlossen:

  • (1) Slave-SQL-Thread: erstellt, um das Relay-Log-Relay-Log zu lesen und die im Protokoll enthaltenen Aktualisierungen auszuführen, die sich auf der Slave-Seite befinden
  • (2) Slave-E/A-Thread: Lesen Sie den vom Master-Server gesendeten Binlog-Dump-Thread und speichern Sie ihn im Relay-Protokoll des Slave-Servers, der sich auf der Slave-Seite befindet:
  • (3) Binlog-Dump-Thread (Auch bekannt als IO-Thread): Senden Sie den Inhalt des Bin-Log-Binärprotokolls an den Slave-Server, der sich auf der Master-Seite befindet.

  Hinweis: Wenn ein Master-Server mit zwei Slave-Servern ausgestattet ist, sind zwei Slave-Server vorhanden Der Master-Server. Jeder Slave-Server verfügt über zwei Threads.

  10.2. Der Master-Server führt die SQL-Anweisung aus Binlog-Binärdatei ;

  (2) Der E/A-Thread auf der Slave-Seite stellt eine Verbindung zur Master-Seite her und fordert das Kopieren des nächsten Protokollinhalts von der angegebenen Pos-Knotenposition der angegebenen Bin-Log-Protokolldatei (oder vom Anfang an) an das Protokoll).

  (3) Nach dem Empfang der E/A-Thread-Anfrage von der Slave-Seite benachrichtigt die Master-Seite den für den Replikationsprozess verantwortlichen E/A-Thread und liest die Protokollinformationen nach der angegebenen Pos-Knotenposition des angegebenen Binlog-Protokolls gemäß den Anforderungsinformationen des Slave-seitigen E/A-Threads und wird dann an den E/A-Thread der Slave-Seite zurückgegeben. Zusätzlich zu den im Binlog-Protokoll enthaltenen Informationen umfassen die zurückgegebenen Informationen auch den Binlog-Dateinamen der zurückgegebenen Informationen auf der Masterseite und die Position des POS-Knotens im Binlog-Protokoll.
  • (4) Nach dem Empfang der vom E/A auf der Masterseite zurückgegebenen Informationen schreibt der E/A-Thread auf der Slave-Seite nacheinander den empfangenen Binlog-Protokollinhalt an das Ende der Relay-Protokolldatei auf der Slave-Seite und liest das Binlog Der Name und der Standort des Pos-Knotens werden in der Master-Info-Datei aufgezeichnet (die Datei wird auf der Slave-Seite gespeichert), sodass dem Master bei der nächsten Synchronisierung mitgeteilt werden kann, von welcher Position aus die Datensynchronisierung gestartet werden soll.
  • (5) Der SQL-Thread auf der Slave-Seite erkennt das Relay-Protokoll. Nachdem der Inhalt der Datei hinzugefügt wurde, wird der Inhalt in der Relay-Log-Datei sofort analysiert und dann auf die tatsächlich auf der Master-Seite ausgeführten SQL-Anweisungen zurückgesetzt. und dann ausgeführt, um Datenkonsistenz zwischen der Master-Seite und der Slave-Seite zu erreichen Fügen Sie dynamisch Slave-Server hinzu, führen Sie Schreibvorgänge und Aktualisierungen auf dem Master-Server durch und führen Sie Aktualisierungen auf dem Slave-Server durch. Führen Sie die Lesefunktion auf aus.
  • (2) Verbessern Sie die Datensicherheit, da die Daten auf den Slave-Server kopiert wurden und der Slave-Server den Replikationsprozess beenden kann, sodass sie auf dem Slave-Server gesichert werden können, ohne die entsprechenden Daten des Master-Servers zu zerstören.
  • (3) Generieren Sie Echtzeitdaten auf dem Master-Server und analysieren Sie diese Daten auf dem Slave-Server, wodurch die Leistung des Master-Servers verbessert wird.

  10.4. Die von MySQL unterstützten Replikationstypen und ihre Vor- und Nachteile:

  Die Binlog-Protokolldatei hat zwei Formate, eines ist Anweisungsbasiert (anweisungsbasierte Replikation) und das andere ist zeilenbasiert (basiert). auf Zeilenkopie). Das Standardformat ist Statement-Based. Wenn Sie das Format ändern möchten, verwenden Sie beim Starten des Dienstes die Option -binlog-format. Der spezifische Befehl lautet wie folgt:

  • mysqld_safe –user=msyql –binlog-format=format&
  • (1) Aussagebasierte Replikation: Eine SQL-Anweisung wird auf dem Master-Server ausgeführt und dieselbe Anweisung wird auf dem Slave-Server ausgeführt. Der Wirkungsgrad ist relativ hoch. Sobald festgestellt wird, dass ein exaktes Kopieren nicht möglich ist, wird automatisch das zeilenbasierte Kopieren ausgewählt.

  Vorteile:

  ① Da die SQL-Anweisungen aufgezeichnet werden, wird weniger Speicherplatz benötigt. Das Binlog-Protokoll enthält Ereignisse, die Datenbankvorgänge beschreiben. Diese Ereignisse umfassen jedoch nur Vorgänge, die die Datenbank ändern, z. B. Einfügen, Aktualisieren, Erstellen, Löschen und andere Vorgänge. Im Gegenteil, ähnliche Vorgänge wie Auswählen und Absteigen werden nicht aufgezeichnet.

  ② Die Binlog-Protokolldatei zeichnet alle Anweisungen auf, die die Datenbank ändern, sodass diese Datei als Grundlage für die Prüfung der Datenbank verwendet werden kann.

  Nachteile:

  • ① Nicht sicher, nicht alle Aussagen, die Daten ändern, werden aufgezeichnet. Nicht deterministisches Verhalten wird nicht protokolliert. Beispiel: Wenn bei Lösch- oder Aktualisierungsanweisungen „Limit“ verwendet wird, aber keine Reihenfolge nach angegeben ist, handelt es sich um eine nicht deterministische Anweisung, die nicht aufgezeichnet wird.
  • ② Bei update, insert...select-Anweisungen ohne Indexbedingungen müssen mehr Daten gesperrt werden, was die Leistung der Datenbank verringert.

  (2) Zeilenbasiert: Kopieren Sie den geänderten Inhalt, anstatt den Befehl auf dem Slave-Server auszuführen.

  Vorteile:

  • ① Alle Änderungen werden kopiert, was am sichersten ist Möglichkeit zum Kopieren;
  • ② Weniger Zeilen sind für Aktualisierungs-, Einfügungs- und andere Anweisungen gesperrt;

  Nachteile:

  • ① Was nicht über die Binlog-Protokolldatei angezeigt werden kann, haben wir nicht So erfahren wir, welche Anweisung auf dem Slave-Server empfangen wurde. Wir können nur sehen, welche Daten sich geändert haben.
  • ② Da Daten aufgezeichnet werden, ist der von Binlog-Protokolldateien belegte Speicherplatz größer als der von Statement-based.
  • ③ Vorgänge mit großen Datenmengen dauern länger.

  (3) Replikation gemischter Typen: Die anweisungsbasierte Replikation wird standardmäßig übernommen. Sobald festgestellt wird, dass die anweisungsbasierte Replikation nicht genau sein kann, wird die zeilenbasierte Replikation übernommen.

  Weitere Informationen zur Master-Slave-Replikation finden Sie in diesem Artikel: https://blog.csdn.net/a745233700/article/details/85256818

  11. Lese- und Schreibtrennung:

  11.1. Implementierung Prinzip:

  Die Trennung von Lesen und Schreiben löst das Problem, dass der Schreibvorgang der Datenbank die Effizienz der Abfrage beeinflusst, und eignet sich für Szenarien, in denen das Lesen weitaus größer ist als das Schreiben. Die Grundlage für die Realisierung der Lese-/Schreibtrennung ist die Master-Slave-Replikation. Die Master-Datenbank verwendet die Master-Slave-Replikation, um ihre eigenen Datenänderungen mit dem Slave-Datenbankcluster zu synchronisieren. Dann ist die Master-Datenbank für die Verarbeitung von Schreibvorgängen verantwortlich Leseoperationen durchführen) und die Slave-Datenbank für die Verarbeitung von Leseoperationen verantwortlich ist, können keine Schreiboperationen durchgeführt werden. Je nach Drucksituation können mehrere Slave-Datenbanken bereitgestellt werden, um die Geschwindigkeit von Lesevorgängen zu erhöhen, den Druck auf die Hauptdatenbank zu verringern und die Gesamtleistung des Systems zu verbessern.

  11.2 Gründe, warum die Lese- und Schreibtrennung die Leistung verbessert:

  • (1) Fügen Sie physische Server hinzu, um die Last zu teilen;
  • (2) Der Master und der Slave sind nur für ihr eigenes Schreiben und Lesen verantwortlich lindert X-Lock- und S-Lock-Konflikte erheblich.
  • (3) Die Slave-Bibliothek kann die Abfrageleistung verbessern und System-Overhead einsparen.
  • (4) Eine weitere wichtige Funktion der Master-Slave-Replikation besteht darin, die Redundanz zu erhöhen und zu verbessern Wenn ein Datenbankserver ausfällt, kann der Dienst schnellstmöglich wiederhergestellt werden, indem eine andere Slave-Datenbank angepasst wird.

  11.3. Implementierungsmethode für das Lesen und Schreiben von MySQL:

  • (1) Basierend auf der internen Implementierung des Programmcodes: Die Routing-Klassifizierung erfolgt basierend auf Auswahl und Einfügen in den Code. Der Vorteil besteht darin, dass die Leistung besser ist, da das Programm im Code implementiert ist und keine zusätzlichen Hardwarekosten erforderlich sind. Der Nachteil besteht darin, dass Entwickler es implementieren müssen und das Betriebs- und Wartungspersonal keine Möglichkeit hat, damit zu beginnen.
  • (2) Implementierung basierend auf der Zwischen-Proxy-Schicht: Der Proxy befindet sich im Allgemeinen zwischen dem Anwendungsserver und dem Datenbankserver. Der Proxy-Datenbankserver empfängt die Anfrage vom Anwendungsserver und leitet sie basierend auf seiner Beurteilung an die Back-End-Datenbank weiter. Es gibt die folgenden repräsentativen Proxy-Schichten.

  12. Unterdatenbank und Untertabelle: vertikale Untertabelle, vertikale Unterdatenbank, horizontale Untertabelle, horizontale Unterdatenbank

  Die Trennung von Lesen und Schreiben löst den Druck beim Lesen der Datenbank und Schreibvorgänge, verteilt jedoch nicht den Speicher der Datenbank. Unter Druck kann die Verwendung von Unterdatenbanken und Untertabellen den Speicherengpass der Datenbank lösen und die Abfrageeffizienz der Datenbank verbessern.

  12.1. Vertikale Aufteilung:

  (1) Vertikale Tabellenpartitionierung: Teilen Sie eine Tabelle entsprechend den Feldern in mehrere Tabellen auf, und jede Tabelle speichert einen Teil der Felder. Im Allgemeinen werden häufig verwendete Felder in einer Tabelle und weniger häufig verwendete Felder in einer anderen Tabelle platziert.

  Vorteile:

  • (1) Vermeiden Sie IO-Wettbewerb und verringern Sie die Wahrscheinlichkeit, dass der Tisch gesperrt wird. Da große Felder weniger effizient sind, beanspruchen große Felder erstens mehr Platz und die Anzahl der auf einer einzelnen Seite gespeicherten Zeilen verringert sich, was zu mehr E/A-Vorgängen führt. Zweitens ist die Datenmenge groß und dauert lange zu lesen.

  • (2) kann die Abfrageeffizienz beliebter Daten besser verbessern.

  (2) Vertikale Datenbankaufteilung: Teilen Sie Tabellen entsprechend unterschiedlicher Geschäftsmodule in verschiedene Datenbanken auf, was für Systeme mit sehr geringer Kopplung zwischen Unternehmen und klarer Geschäftslogik geeignet ist.

  Vorteile:

  • Reduzieren Sie die Kopplung im Unternehmen und erleichtern Sie die hierarchische Verwaltung verschiedener Unternehmen
  • Kann die Anzahl der E/A- und Datenbankverbindungen erhöhen und das Engpassproblem von Hardware-Speicherressourcen auf einer Maschine lösen

  ( 3) Vertikale Aufteilung Nachteile von (Unterdatenbank, Untertabelle):

  • Der Primärschlüssel ist redundant und redundante Spalten müssen verwaltet werden
  • Die Transaktionsverarbeitung wird kompliziert
  • Es besteht immer noch das Problem eines übermäßigen Datenvolumens ein einzelner Tisch

  12.2, horizontale Aufteilung:

  (1) Horizontale Tabellenaufteilung: Teilen Sie in derselben Datenbank die Daten derselben Tabelle nach bestimmten Regeln in mehrere Tabellen auf.

  Vorteile:

  • Lösen Sie das Problem des übermäßigen Datenvolumens in einer einzelnen Tabelle.
  • Vermeiden Sie IO-Konkurrenz und verringern Sie die Wahrscheinlichkeit von Tabellensperren.

  (2) Horizontale Datenbankaufteilung: Teilen Sie die Daten derselben Tabelle entsprechend auf nach bestimmten Regeln in unterschiedliche Datenbanken übertragen und unterschiedliche Datenbanken können auf unterschiedlichen Servern abgelegt werden.

  Vorteile:

  • Lösen Sie das Engpassproblem großer Datenmengen in einer einzelnen Datenbank.
  • E/A-Konflikte werden reduziert, Sperrenkonkurrenz wird reduziert, Probleme in einer Datenbank wirken sich nicht auf andere Datenbanken aus, was die Stabilität und Verfügbarkeit des Systems verbessert

  (3) Nachteile der horizontalen Aufteilung (Sharding von Tabellen und Datenbanken):

  • Die Konsistenz von Sharding-Transaktionen ist schwer zu lösen.
  • Die knotenübergreifende JOIN-Leistung ist schlecht und die Logik wird komplex.
  • Daten Die Erweiterung ist schwierig und schwer zu warten

  12.3 Lösung für die Probleme von Unterdatenbanken und Untertabellen:

  (1) Transaktionsproblem:

  ① Option 1: Verteilte Transaktionen verwenden:

  • Vorteile: Datenbankverwaltet, einfach und effektiv.
  • Nachteile: Der Leistungsaufwand ist hoch, insbesondere da es immer mehr Shards gibt.

  ② Option 2: Das Programm und die Datenbank steuern gemeinsam die Implementierung. Das Prinzip besteht darin, eine verteilte Transaktion über mehrere Datenbanken in mehrere kleine Transaktionen zu zerlegen, die nur in einer einzigen Datenbank vorhanden sind, und das Anwendungsprogramm die Gesamtsteuerung jeder kleinen Transaktion zu überlassen. Angelegenheiten.

  • Vorteile: Vorteile in der Leistung;
  • Nachteile: In der Anwendung ist eine flexible Steuerung der Transaktionen erforderlich. Wenn Sie das Transaktionsmanagement von Spring verwenden, werden Sie bei der Durchführung von Änderungen auf gewisse Schwierigkeiten stoßen.

  (2) Problem bei knotenübergreifenden Verknüpfungen:

  Der übliche Weg, dieses Problem zu lösen, besteht darin, es in zwei Schritten abzufragen: Suchen Sie die ID der zugehörigen Daten im Ergebnissatz der ersten Abfrage und starten Sie sie Die zweite Abfrage basiert auf diesen IDs und fordert das zweite Mal an, um die zugehörigen Daten abzurufen.

  (3) Probleme mit der knotenübergreifenden Zählung, Sortierung nach, Gruppierung nach, Paging und Aggregation:

  Da solche Probleme eine Berechnung auf der Grundlage der gesamten Datenerfassung erfordern. Die meisten Agenten übernehmen die Zusammenführungsarbeit nicht automatisch. Die Lösung ähnelt der Lösung des Cross-Node-Join-Problems. Die Ergebnisse werden auf jedem Knoten abgerufen und dann auf der Anwendungsseite zusammengeführt. Im Gegensatz zum Join kann die Abfrage jedes Knotens parallel ausgeführt werden, sodass die Geschwindigkeit viel schneller ist als bei einer einzelnen großen Tabelle. Wenn die Ergebnismenge jedoch groß ist, stellt der Verbrauch von Anwendungsspeicher ein Problem dar.

  12.4. Wie gehe ich mit dem ID-Schlüssel um, nachdem die Datenbank in Tabellen unterteilt wurde?

  Nachdem die Datenbank in Tabellen unterteilt ist, kann die ID jeder Tabelle nicht bei 1 beginnen, daher ist eine globale ID erforderlich. Es gibt hauptsächlich die folgenden Methoden zum Festlegen der globalen ID:

  (1) UUID:

  • Vorteile: Die ID wird lokal generiert, es sind keine Remote-Aufrufe erforderlich und sie ist global eindeutig und wiederholt sich nicht.
  • Nachteile: Es nimmt viel Platz ein und ist nicht für die Indizierung geeignet.

  (2) Datenbank-Autoinkrement-ID: Die Verwendung der Datenbank-Autoinkrement-ID nach der Aufteilung der Datenbank in Tabellen erfordert eine Bibliothek, die für die Generierung von Primärschlüsseln zuständig ist. Jedes Mal, wenn der Dienst eine Anfrage erhält, macht es keinen Sinn, einen Eintrag einzufügen Rufen Sie für Daten zunächst eine ID auf, die von der Datenbank automatisch erhöht wird, und verwenden Sie diese ID, um Daten in Unterdatenbanken und Untertabellen zu schreiben.

  • Vorteile: Einfach und leicht umzusetzen.
  • Nachteile: Bei hoher Parallelität gibt es einen Engpass.

  (3) Von Redis generierte ID:

  • Vorteile: Ist nicht auf die Datenbank angewiesen und bietet eine bessere Leistung.
  • Nachteile: Die Einführung neuer Komponenten erhöht die Systemkomplexität

  (4) Twitters Schneeflockenalgorithmus: Es handelt sich um eine 64 Bit lange ID, von der 1 Bit ungenutzt ist, 41 Bit als Millisekunden verwendet werden und 10 Bit werden als Arbeitsmaschinen-ID verwendet, 12 Bit als Seriennummer.

  • 1bit: Das erste Bit ist standardmäßig 0, denn wenn das erste Bit im Binärformat 1 ist, ist es eine negative Zahl, aber die ID darf keine negative Zahl sein.
  • 41bit: Es stellt den Zeitstempel dar, und die Einheit ist es Millisekunden.
  • 10 Bit: Zeichnen Sie die Arbeitsmaschinen-ID auf, wobei 5 Bit die Computerraum-ID und 5 Bit die Maschinen-ID darstellen.
  • 12bit: Wird zum Aufzeichnen verschiedener IDs verwendet, die innerhalb derselben Millisekunde generiert wurden.

  (5) Meituan’s Leaf verteiltes ID-Generierungssystem, Meituan-Dianping verteiltes ID-Generierungssystem:

  13. Partitionierung:

  Partitionierung besteht darin, Tabellendaten an verschiedenen Orten nach bestimmten Regeln zu speichern , Unterteilen der Datendatei der Tabelle in mehrere kleine Blöcke. Beim Abfragen von Daten müssen Sie nur wissen, in welchen Regionen die Daten gespeichert sind, und dann direkt in der entsprechenden Region abfragen. Verbessern Sie die Abfrageleistung. Wenn die Tabellendaten besonders groß sind und nicht auf eine Festplatte passen, können wir die Daten gleichzeitig auch verschiedenen Festplatten zuweisen, um das Problem des Speicherengpasses zu lösen. Durch die Verwendung mehrerer Festplatten kann auch die E/A-Effizienz der Festplatte verbessert werden Leistung der Datenbank. Bei Verwendung einer partitionierten Tabelle müssen Sie beachten, dass das Partitionsfeld im Primärschlüssel oder eindeutigen Index platziert werden muss und die maximale Anzahl von Partitionen pro Tabelle 1024 beträgt. Übliche Partitionstypen sind: Bereichspartition, Listenpartition, Hash-Partition , Schlüsselpartition,

  • (1) Bereichspartitionierung: Partitionierung gemäß dem kontinuierlichen Intervallbereich
  • (2) Listenpartitionierung: Wählen Sie die Partitionierung gemäß den Werten im angegebenen Satz aus.
  • (3) Hash-Partitionierung: Partitionierung basierend auf dem Rückgabewert eines benutzerdefinierten Ausdrucks, der anhand der Spaltenwerte dieser Zeilen berechnet wird, die in die Tabelle eingefügt werden. Diese Funktion kann jeden gültigen Ausdruck in MySQL enthalten, der einen nicht negativen ganzzahligen Wert erzeugt.
  • (4) Schlüsselpartitionierung: Ähnlich wie bei der HASH-Partitionierung besteht der Unterschied darin, dass die Schlüsselpartitionierung nur die Berechnung einer oder mehrerer Spalten unterstützt und die Hash-Funktion der Schlüsselpartitionierung vom MySQL-Server bereitgestellt wird.

  (1) Vorteile der Tabellenpartitionierung:

  ① Skalierbarkeit:

  • Die Aufteilung von Partitionen in verschiedene Festplatten kann das Problem des Kapazitätsengpasses einzelner Festplatten lösen, mehr Daten speichern und auch das Problem einzelner Festplatten lösen Kapazitätsengpass. Festplatten-E/A-Engpassproblem.

  ② Verbessern Sie die Datenbankleistung:

  • Reduzieren Sie die Datenmenge, die beim Datenbankabruf durchlaufen werden muss. Beim Abfragen müssen Sie nur die Partition abfragen, die den Daten entspricht.
  • Vermeiden Sie die sich gegenseitig ausschließenden Zugriffsbeschränkungen des einzelnen Index von Innodb.
  • Bei Aggregatfunktionen wie sum() und count() können diese in jeder Partition parallel verarbeitet werden. Am Ende müssen Sie nur die erhaltenen Ergebnisse zählen von allen Partitionen

  ③ Bequeme Betriebs- und Wartungsverwaltung von Daten:

  • Bequeme Verwaltung von Daten, die ihre Bedeutung für die Aufbewahrung verloren haben, können durch Löschen der entsprechenden Partition schnell gelöscht werden. Um beispielsweise historische Daten zu einem bestimmten Zeitpunkt zu löschen, führen Sie direkt „truncate“ aus oder löschen Sie direkt die gesamte Partition, was effizienter ist als das Löschen.
  • In einigen Szenarien ist die Sicherung und Wiederherstellung einer einzelnen Partitionstabelle effizienter.

  14. Wird im Allgemeinen die Auto-Inkrement-ID oder UUID als Primärschlüssel verwendet?

  (1) Auto-Inkrement-ID:

  Vorteile der Verwendung der Auto-Inkrement-ID:

  • Die Feldlänge ist viel kleiner als die UUID.
  • Die Datenbank wird automatisch nummeriert und in der richtigen Reihenfolge gespeichert, was den Abruf erleichtert.
  • Sie müssen sich keine Sorgen über die Duplizierung von Primärschlüsseln machen In einigen Geschäftsszenarien ist es für andere leicht zu finden. Geschäftsvolumen.

  Es wird sehr problematisch sein, wenn eine Datenmigration oder eine Tabellenzusammenführung stattfindet.

  In Szenarien mit hoher Parallelität verringert der Wettbewerb um automatische Inkrementierungssperren den Durchsatz der Datenbank.

  • (2) UUID: Universeller eindeutiger Identifikationscode, UUID ist basierend auf Es wird durch Berechnung von Daten wie aktueller Uhrzeit, Zähler und Hardware-Identifikation generiert.
  • Vorteile der Verwendung von UUID:

  Eindeutige Identifizierung, keine Notwendigkeit, Duplizierungsprobleme zu berücksichtigen, und globale Eindeutigkeit kann erreicht werden, wenn Daten geteilt und zusammengeführt werden.

  Kann auf der Anwendungsebene generiert werden, um den Durchsatz der Datenbank zu verbessern.

  Sie müssen sich keine Sorgen über Verluste des Geschäftsvolumens machen.

  • Nachteile der Verwendung von UUID:
  • Da die UUID zufällig generiert wird, kommt es zu zufälligen E/A-Vorgängen, die sich auf die Einfügegeschwindigkeit auswirken und zu einer geringen Festplattennutzung führen.

  UUID nimmt viel Platz ein. Je mehr Indizes Sie erstellen, desto größer ist die Wirkung.

  Der Vergleich der Größen zwischen UUIDs ist viel langsamer als bei sich selbst erhöhenden IDs, was sich auf die Abfragegeschwindigkeit auswirkt.

  • Im Allgemeinen empfiehlt
  MySQL die Verwendung einer automatischen Inkrementierungs-ID, da in der InnoDB-Speicher-Engine von MySQL der Primärschlüsselindex ein Clustered-Index ist und die Blattknoten des B + -Baums des Primärschlüsselindex den Primärschlüsselwert und speichern Daten in der richtigen Reihenfolge. Wenn der Primärschlüsselindex eine automatisch inkrementierende ID ist, muss er nur in der richtigen Reihenfolge angeordnet werden. Wenn es sich um eine UUID handelt, wird die ID zufällig generiert, was zu einer großen Datenbewegung beim Einfügen der Daten führt. Dies führt zu einer starken Speicherfragmentierung, was zu einer Verringerung der Einfügeleistung führt.

  15. Ansicht:
  Eine Ansicht ist eine Tabelle, die von einer oder mehreren Tabellen (oder Ansichten) abgeleitet ist und deren Inhalt durch eine Abfrage definiert wird. Eine Ansicht ist eine virtuelle Tabelle. In der Datenbank wird nur die Definition der Ansicht gespeichert, und die der Ansicht entsprechenden Daten werden nicht gespeichert. Beim Bearbeiten der Daten der Ansicht betreibt das System die entsprechende Basistabelle die Aussicht.

  Man kann sagen, dass eine Ansicht eine Tabelle ist, die auf einer Basistabelle basiert. Ihre Struktur und ihr Inhalt stammen aus der Basistabelle und basieren auf der Existenz der Basistabelle. Eine Ansicht kann einer Basistabelle oder mehreren Basistabellen entsprechen. Ansichten sind Abstraktionen grundlegender Tabellen und neuer Beziehungen, die im logischen Sinne hergestellt werden.

  (1) Vorteile von Ansichten:

  Vereinfacht Vorgänge und definiert häufig verwendete Daten als Ansichten

  Sicherheit, Benutzer können nur sichtbare Daten abfragen und ändern

  Logische Unabhängigkeit, Abschirmung Es eliminiert die Auswirkungen der Struktur der Realität Tabelle

  • (2) Nachteile der Ansicht:
  • schlechte Leistung Die Datenbank muss die Abfrage für die Ansicht in eine Abfrage für die Basistabelle umwandeln. Wenn die Abfrage besteht Selbst wenn es sich um eine einfache Abfrage der Ansicht handelt, wird sie von der Datenbank in eine komplexe Kombination umgewandelt, was eine gewisse Zeit in Anspruch nehmen wird.

  16. Gespeicherte Prozedur Prozedur:

  SQL-Anweisungen müssen zuerst kompiliert und dann ausgeführt werden, und

  gespeicherte Prozeduren sind eine Reihe von SQL-Anweisungen, um bestimmte Funktionen auszuführen Der Benutzer kann den Speicher angeben. Rufen Sie ihn mit dem Namen der Prozedur und den angegebenen Argumenten auf.

  Komplexe Logik zum Betreiben der Datenbank kann auch mithilfe von Programmen implementiert werden. Wozu brauchen wir also gespeicherte Prozeduren? Der Hauptgrund dafür ist, dass die Effizienz beim Aufrufen der API relativ langsam ist. Die Anwendung muss die SQL-Anweisung zur Ausführung an die MYSQL-Engine übergeben. Es ist besser, MySQL die Arbeit direkt zu überlassen es ist am besten darin und in der Lage, es zu vollenden.

  Vorteile gespeicherter Prozeduren:

  • (1) Standardkomponentenprogrammierung: Nachdem eine gespeicherte Prozedur erstellt wurde, kann sie im Programm mehrmals aufgerufen werden, ohne dass die SQL-Anweisung der gespeicherten Prozedur neu geschrieben werden muss. Und der DBA kann die gespeicherte Prozedur jederzeit ändern, ohne dass dies Auswirkungen auf den Quellcode der Anwendung hat.
  • (2) Schnellere Ausführungsgeschwindigkeit: Wenn eine Operation eine große Menge an Transaktions-SQL-Code enthält oder mehrmals ausgeführt wird, wird die gespeicherte Prozedur viel schneller ausgeführt als die Stapelverarbeitung. Da gespeicherte Prozeduren vorkompiliert sind, analysiert und optimiert der Optimierer die Abfrage, wenn Sie eine gespeicherte Prozedur zum ersten Mal ausführen, und erstellt einen Ausführungsplan, der letztendlich in der Systemtabelle gespeichert wird. Die Batch-Transaction-SQL-Anweisung muss bei jeder Ausführung kompiliert und optimiert werden, und die Geschwindigkeit ist relativ langsamer.
  • (3) Verbessern Sie die Funktion und Flexibilität der SQL-Sprache: Gespeicherte Prozeduren können mit Steueranweisungen geschrieben werden, weisen eine hohe Flexibilität auf und können komplexe Beurteilungen und komplexe Operationen durchführen.
  • (4) Reduzieren Sie den Netzwerkverkehr: Wenn bei Vorgängen für dasselbe Datenbankobjekt (z. B. Abfragen, Änderungen) die an diesem Vorgang beteiligten Transaktions-SQL-Anweisungen in gespeicherten Prozeduren organisiert sind, dann wird die gespeicherte Prozedur auf dem Client aufgerufen Computer Zu diesem Zeitpunkt wird nur die Anrufanweisung im Netzwerk übertragen, wodurch der Netzwerkverkehr erheblich reduziert und die Netzwerklast verringert wird.
  • (5) Nutzen Sie ihn als Sicherheitsmechanismus voll aus: Durch die Einschränkung der Berechtigungen zur Ausführung eines bestimmten gespeicherten Prozesses können die Zugriffsberechtigungen auf die entsprechenden Daten eingeschränkt werden, wodurch der Zugriff unbefugter Benutzer auf die Daten verhindert und die Datensicherheit gewährleistet wird .

  17. Auslöser:

  Ein Auslöser ist ein Datenbankobjekt, das sich auf eine Tabelle bezieht, in der sich der Auslöser befindet und die definierten Bedingungen erfüllt sind Der Trigger wird ausgeführt. Die Triggerfunktion kann auf der Datenbankseite angewendet werden, um die Datenintegrität sicherzustellen. Ein Trigger ist eine spezielle gespeicherte Prozedur. Der Unterschied besteht darin, dass die gespeicherte Prozedur mithilfe eines Aufrufs aufgerufen werden muss, während der Trigger keinen Aufruf oder manuellen Aufruf erfordert. Er löst die Ausführung aus, wenn Daten in eine bestimmte Tabelle eingefügt, gelöscht oder geändert werden. Sie verfügt über ausgefeiltere und komplexere Datenkontrollfunktionen als die Standardfunktionen der Datenbank selbst.
  

  18. Cursor:

  Der Cursor ist die Kennung des Schwimmens und kann als Zeiger verwendet werden, um alle Datensätze in der von der Abfragedatenbank zurückgegebenen Ergebnismenge zu durchlaufen, jedoch nur einen Der Datensatz kann jeweils extrahiert werden, das heißt, es kann jeweils nur auf eine Datenzeile verwiesen und diese abgerufen werden, um entsprechende Vorgänge auszuführen. Wenn Sie den Cursor nicht verwenden, ist das so, als würde Ihnen jemand alles auf einmal geben und Sie es dann wegnehmen, nachdem Sie den Cursor verwendet haben. Zu diesem Zeitpunkt können Sie es zuerst tun Sehen Sie, ob das Ding gut ist oder nicht, und treffen Sie dann Ihre eigene Wahl.

  Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonFassen Sie häufige Interviewfragen zur MySQL-Datenbank zusammen und organisieren Sie sie. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!