13 Schlüsselwörter, die Sie kennen müssen, um MySQL zu lernen (Zusammenfassungsfreigabe)

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL, in dem hauptsächlich Probleme im Zusammenhang mit Schlüsselwörtern vorgestellt werden, darunter drei Normalformen, Zeichensätze, benutzerdefinierte Mengen, Ansichten, Partitionstabellen usw., wie folgt. Werfen wir einen Blick darauf, ich hoffe, dass dies der Fall ist allen hilfreich sein.

Empfohlenes Lernen: MySQL-Video-Tutorial

1. Drei Normalformen

• Erste Normalform: Jede Spalte jeder Tabelle muss ihre Atomizität beibehalten, das heißt, jede Spalte der Tabelle kann nicht geteilt werden;
• Zweite Normalform: Basierend auf der Erfüllung der ersten Normalform muss jede Tabelle ihre Eindeutigkeit beibehalten, das heißt, die Nicht-Primärschlüsselfelder der Tabelle hängen vollständig von den Primärschlüsselfeldern ab.
• Dritte Normalform: Nach der Erfüllung die erste Normalform Auf der Grundlage der Normalform und der zweiten Normalform können keine transitiven Beziehungen in der Tabelle generiert werden, und Redundanzen in der Tabelle müssen eliminiert werden

2Der Zeichensatz gibt die Speicherung an Format der Zeichen in der Datenbank, z. B. Berücksichtigung von wie viel Platz, welche Zeichen unterstützt werden usw. Unterschiedliche Zeichensätze haben unterschiedliche Kodierungsregeln. Die Korrekturleseregeln beziehen sich auf die Sortierung eines Zeichensatzes. Bei der Bedienung, Wartung und Verwendung der MySQL-Datenbank ist es sehr wichtig, das richtige Zeichen auszuwählen Bei einer ungeeigneten Auswahl kann es zumindest zu Beeinträchtigungen der Datenbankleistung und in schwerwiegenden Fällen zu einer Verfälschung der Datenspeicherung kommen.

Es gibt hauptsächlich vier gängige MySQl-Zeichensätze:

ZeichensatzGBK UTF-8latin1

Bei der Entwicklung und dem Betrieb der MySQL-Datenbank gelten folgende Regeln für die Zeichensatzauswahl:

1. Wenn das System für ausländische Unternehmen entwickelt wurde und verschiedene Länder und Sprachen verarbeiten muss, sollten Sie utf-8 oder utf8mb4 wählen Wenn Sie nur Chinesisch unterstützen müssen, kann aus Leistungsgründen GBK verwendet werden. 3. Benutzerdefinierte Variablen
2. Eine benutzerdefinierte Variable ist ein temporärer Container, der zum Speichern von Inhalten verwendet wird und überall vorhanden ist gesamten Prozess der Verbindung zu MySQL. Es kann mit set definiert werden.

SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;SELECT id,name from user where create_time > @last_week;

Hinweise zur Verwendung benutzerdefinierter Variablen:

Abfragen, die benutzerdefinierte Variablen verwenden, können keinen Cache verwenden.

Sie können keine benutzerdefinierten Variablen verwenden, bei denen Konstanten oder Bezeichner verwendet werden, z. B. Tabellennamen, Spaltennamen und In der Limit-Klausel;

• Der Lebenszyklus benutzerdefinierter Variablen ist in einer Verbindung gültig und sie können nicht für die Kommunikation zwischen Verbindungen verwendet werden.

• Vermeiden Sie wiederholte Abfragen der gerade aktualisierten Daten.
• Wenn Sie die Zeile gleichzeitig aktualisieren Ich möchte Informationen zu dieser Zeile erhalten. Was kann ich tun, um wiederholte Abfragen zu vermeiden?
  Dies geschieht im Allgemeinen wie folgt:

update user set update_time = now() where id = 1;select update_time from user where id = 1;

Die Verwendung benutzerdefinierter Variablen kann es optimieren:

update user set update_time = now() where id = 1 and @now := now();select @now;

Es sieht immer noch wie zwei Abfragen aus, aber die zweite Abfrage muss nicht auf Datentabellen zugreifen, sodass sie viel schneller ist.

4. Wählen Sie optimierte Datentypen

MySQL unterstützt viele Datentypen und die Auswahl des richtigen Datentyps ist entscheidend für das Erreichen einer hohen Leistung.

(1) Kleiner

Im Allgemeinen sollten Sie versuchen, kleinere Datentypen zu verwenden, da diese weniger Speicherplatz beanspruchen und die CPU-Zyklen kürzer sind.

(2) Einfacher

Einfache Datentypen erfordern normalerweise weniger CPU-Zyklen, und Ganzzahlen sind kostengünstiger als Zeichenfolgentypen, da Zeichensätze und Validierungsregeln Zeichenvergleiche komplexer machen als Ganzzahlvergleiche.

(3) Versuchen Sie, NULL zu vermeiden

Viele Tabellen enthalten NULL-fähige Spalten, auch wenn die Anwendung NULL nicht speichern muss, da NULL das Standardattribut der Spalte ist. Normalerweise ist es am besten, Spalte anzugeben ist NICHT NULL.

Wenn die Abfrage NULL-fähige Spalten enthält, ist die Optimierung für MySQL schwieriger, da NULL-fähige Spalten Indizes, Indexstatistiken und Wertvergleiche komplexer machen. NULL-fähige Spalten benötigen mehr Speicherplatz und erfordern eine spezielle Behandlung in MySQL. Wenn NULL-fähige Spalten indiziert werden, ist für jeden Indexdatensatz ein zusätzliches Byte erforderlich, was in MyISAM sogar zu einer festen Größe führen kann. Der Index wird zu einem Index mit variabler Größe .

5. Ansicht

Eine Ansicht (Ansicht) ist eine virtuelle Tabelle, eine logische Tabelle, die selbst keine Daten enthält. Wird im Datenwörterbuch als Select-Anweisung gespeichert. Bei komplexen Abfragen für mehrere Tabellen kann die Verwendung von Ansichten die Abfragen vereinfachen. Wenn die Ansicht eine temporäre Tabelle verwendet, kann die Where-Bedingung nicht verwendet werden und der Index kann nicht verwendet werden.

Eine Einzeltabellenansicht wird im Allgemeinen für Abfragen und Änderungen verwendet, wodurch die Daten der Basistabelle geändert werden. Die Multitabellenansicht wird im Allgemeinen für Abfragen verwendet und ändert nicht die Daten der Basistabelle.

Der Zweck der Verwendung von Ansichten besteht darin, die Datensicherheit zu gewährleisten und die Abfrageeffizienz zu verbessern.

Vorteile von Ansichten:

Benutzer, die Ansichten verwenden, müssen sich nicht um die Struktur, Zuordnungsbedingungen und Filterbedingungen der nachfolgenden entsprechenden Tabellen kümmern. Für Benutzer handelt es sich bereits um eine Ergebnismenge gefilterter zusammengesetzter Bedingungen.

Benutzer, die Ansichten verwenden, können nur auf die Ergebnismengen zugreifen, die sie abfragen dürfen. Die Berechtigungsverwaltung der Tabelle kann nicht auf eine bestimmte Zeile oder Spalte beschränkt werden, kann jedoch problemlos über Ansichten erreicht werden.

• Sobald die Struktur der Ansicht festgelegt ist, können die Auswirkungen von Änderungen in der Tabellenstruktur auf Benutzer abgeschirmt werden. Das Hinzufügen von Spalten zur Quelltabelle hat keine Auswirkungen auf die Änderung der Spaltennamen in der Quelltabelle durch eine Änderung der Ansicht, die keinerlei Auswirkungen auf den Besuchereinfluss hat.
• 6. Cache-Tabellen und Zusammenfassungstabellen
  Manchmal besteht die beste Möglichkeit zur Leistungsverbesserung darin, abgeleitete redundante Daten in derselben Tabelle zu speichern. Manchmal müssen Sie auch eine völlig unabhängige Zusammenfassungstabelle oder Cache-Tabelle erstellen.

Die Cache-Tabelle wird zum Speichern von Daten verwendet, die leicht zu erhalten, aber langsam sind. Die Zusammenfassungstabelle wird zum Speichern der Daten verwendet, die mithilfe der Group-By-Anweisung aggregiert und abgefragt werden Die Tabelle verwendet InnoDB und wird als Engine zum Zwischenspeichern von Tabellen verwendet. MyISAM erhält einen kleineren Index-Footprint und kann eine Volltextsuche durchführen.

Bei der Verwendung von Cache-Tabellen und Übersichtstabellen müssen Sie entscheiden, ob Sie die Daten in Echtzeit verwalten oder regelmäßig neu erstellen möchten. Welche besser ist, hängt von der Anwendung ab, aber regelmäßige Neuerstellungen sparen nicht nur Ressourcen, sondern verhindern auch, dass die Tabelle fragmentiert wird und vollständig sequentiell organisierte Indizes vorhanden sind.

Beim Neuaufbau von Übersichtstabellen und Cache-Tabellen muss in der Regel sichergestellt werden, dass die Daten während des Betriebs weiterhin verfügbar sind. Dies muss durch die Verwendung einer Schattentabelle erreicht werden, die sich auf eine Tabelle bezieht, die hinter der realen Tabelle erstellt wird Die Konstruktion ist abgeschlossen. Nach der Tabellenoperation können die Schattentabelle und die Originaltabelle durch eine atomare Umbenennungsoperation vertauscht werden.

• Um die Lesegeschwindigkeit zu verbessern, erstellen wir häufig zusätzliche Indizes, fügen redundante Spalten hinzu oder erstellen sogar Cache-Tabellen und Zusammenfassungstabellen. Diese Methoden erhöhen den Schreibaufwand und erfordern zusätzliche Wartungsaufgaben, aber beim Entwurf einer Hochleistungsdatenbank. Hierbei handelt es sich um gängige Techniken, die zwar Schreibvorgänge verlangsamen, aber die Leseleistung erheblich verbessern.

  7. Partitionstabelle

  Normalerweise werden Daten in derselben Tabelle zusammen auf physischer Ebene gespeichert. Wenn das Unternehmen wächst und die Datenmenge in derselben Tabelle zu groß wird, führt dies zu Verwaltungsproblemen. Die Partitionsfunktion kann eine Tabelle anhand bestimmter Regeln physisch in mehrere Partitionen unterteilen. Mehrere Partitionen können separat verwaltet oder sogar auf verschiedenen Festplatten/Dateisystemen gespeichert werden, um die Effizienz zu verbessern.

  Vorteile der Partitionstabelle:

  • Daten können auf mehreren Festplatten gespeichert werden, geeignet zum Speichern großer Datenmengen;

  • Die Datenverwaltung ist sehr praktisch, da Daten in Partitionen verwaltet werden, ohne den normalen Betrieb anderer zu beeinträchtigen Partitionen;

  • Sie können den Abfragebereich einschränken und die Abfrageleistung verbessern, indem Sie die Partitionsfunktion während der Abfrage sperren.
    Fremdschlüssel erfordern normalerweise, dass Sie die Daten ändern Dies ist eine zusätzliche Abfrageoperation, obwohl InnoDB die Verwendung von Indizes durch Fremdschlüssel erzwingt, den Overhead dieser Einschränkungsprüfung jedoch nicht beseitigen kann. Wenn die Selektivität des Fremdschlüssels sehr gering ist, führt dies zu einem sehr selektiven Index.
  In einigen Szenarien verbessern Fremdschlüssel jedoch die Leistung. Wenn Sie beispielsweise sicherstellen möchten, dass zwei zusammengehörige Tabellen immer über konsistente Daten verfügen, ist die Verwendung von Fremdschlüsseln außerdem wesentlich leistungsfähiger als die Überprüfung der Konsistenz in der Anwendung. Fremdschlüssel sind auch beim Löschen und Aktualisieren verwandter Daten effizienter als bei der Verwaltung von Fremdschlüsseln. Wartungsvorgänge für Fremdschlüssel werden jedoch Zeile für Zeile durchgeführt, und solche Aktualisierungen sind langsamer als Stapellöschungen und -aktualisierungen.

  Fremdschlüsseleinschränkungen erfordern bei Abfragen zusätzlichen Zugriff auf andere Tabellen, was zusätzliche Sperren erfordert. Wenn ein Datensatz in die untergeordnete Tabelle geschrieben wird, führt die Fremdschlüsseleinschränkung dazu, dass InnoDB den entsprechenden Datensatz der übergeordneten Tabelle überprüft. Dies bedeutet, dass der entsprechende Datensatz der übergeordneten Tabelle gesperrt werden muss, um sicherzustellen, dass dieser Datensatz nicht verloren geht nach Abschluss der Transaktion gelöscht wurde. Dies kann zu zusätzlichen Sperrwartezeiten und sogar zu einigen Deadlocks führen. Da kein direkter Zugriff auf diese Tabellen besteht, ist die Fehlerbehebung bei dieser Art von Deadlock schwierig.

  Daher werden in vielen aktuellen Projekten aus Performancegründen keine Fremdschlüssel mehr verwendet.
  9. Abfrage-Cache
  Der MySQL-Abfrage-Cache speichert die vollständigen Ergebnisse, die von der Abfrage zurückgegeben werden. Wenn die Abfrage den Cache erreicht, gibt MySQL die Ergebnisse sofort zurück und überspringt den Analyse-, Optimierungs- und Ausführungsprozess.

  Das Abfrage-Cache-System verfolgt jede an der Abfrage beteiligte Tabelle. Wenn sich diese Tabellen ändern, sind alle zwischengespeicherten Daten, die sich auf diese Tabelle beziehen, ungültig. Dieser Mechanismus scheint relativ ineffizient zu sein, da die Datentabelle möglicherweise keine Änderungen aufweist Auswirkungen auf die Abfrageergebnisse, aber die Kosten dieser einfachen Implementierung sind sehr gering, was für ein sehr ausgelastetes System sehr wichtig ist.

  (1) Wie ermittelt MySQL einen Cache-Treffer? Bei der Feststellung, ob es sich um einen Treffer handelt, analysiert MySQL ihn nicht, sondern verwendet direkt die SQL-Anweisung und andere vom Client gesendete Originalinformationen. Jegliche Zeichenunterschiede wie Leerzeichen oder Kommentare führen zu Cache-Fehlern. Im Allgemeinen ist die Verwendung einheitlicher Codierungsregeln eine gute Angewohnheit und führt dazu, dass Ihr System schneller läuft.

  Wenn die Abfrageanweisung unsichere Daten enthält, werden diese nicht zwischengespeichert, z. B. die Funktion now(). Wenn der Cache benutzerdefinierte Funktionen, gespeicherte Funktionen, Benutzervariablen, temporäre Tabellen, MySQL-Systemtabellen oder Tabellen mit Berechtigungen auf Spaltenebene enthält, werden diese tatsächlich nicht zwischengespeichert.

  (2) Seien Sie vorsichtig, wenn Sie den Abfragecache verwenden.

  Das Öffnen des Abfragecaches führt zu einem zusätzlichen Verbrauch sowohl für Lese- als auch für Schreibvorgänge:
  Die Leseabfrage muss vor der Ausführung zunächst prüfen, ob sie auf den Cache trifft

  Wenn Leseabfragen zwischengespeichert werden können, speichert MySQL nach Abschluss der Ausführung die Ergebnisse im Abfragecache, was zu zusätzlichem Systemverbrauch führt

  hat auch Auswirkungen auf Schreibvorgänge, denn Beim Schreiben von Daten in eine Tabelle muss MySQL alle Cache-Einstellungen für die entsprechende Tabelle ungültig machen. Wenn der Abfragecache sehr groß oder fragmentiert ist, kann dieser Vorgang einen hohen Systemverbrauch verursachen.
  Der Abfragecache verbessert jedoch dennoch die Leistung des Systems. Der oben erwähnte zusätzliche Verbrauch kann jedoch auch weiter zunehmen. Darüber hinaus handelt es sich bei der Abfrage-Cache-Operation um eine Sperroperation, und dieser Verbrauch ist nicht gering.

  • Für InnoDB-Benutzer schränken einige Merkmale von Transaktionen die Verwendung des Abfragecaches ein. Wenn eine Anweisung eine Tabelle in einer Transaktion ändert, macht MySQL die der Tabelle entsprechende Abfrage-Cache-Einstellung ungültig, bevor die Transaktion übermittelt wird. Daher wird die Trefferquote des Abfrage-Cache durch lang laufende Transaktionen erheblich verringert.

    (3) So analysieren und konfigurieren Sie den Abfragecache

    10. Gespeicherte Prozedur

    Eine gespeicherte Prozedur ist eine Reihe von SQL-Anweisungen zur Ausführung einer bestimmten Funktion. Sie wird durch Angabe des Namens kompiliert und in der Datenbank gespeichert Wenn Sie die gespeicherte Prozedur eingeben und den Wert des Parameters angeben, können Sie auch das Ergebnis zurückgeben.

    Vorteile gespeicherter Prozeduren:

    • Reduzieren Sie den Netzwerkverkehr

    • Verbessern Sie die Ausführungsgeschwindigkeit

    • Reduzieren Sie die Anzahl der Datenbankverbindungen

    • Hohe Sicherheit

    • Hohe Wiederverwendbarkeit

    Nachteile gespeicherter Prozeduren:

    • Schlechte Portabilität

    11. Transaktionen

    Anweisungen innerhalb einer Transaktion werden entweder alle ausgeführt oder überhaupt nicht ausgeführt. Transaktionen haben ACID-Merkmale, die Atomizität, Konsistenz, Isolation und Haltbarkeit darstellen.

    (1) Atomizität

    Eine Transaktion muss als unteilbare Mindestarbeitseinheit betrachtet werden. Alle Vorgänge in der gesamten Transaktion werden entweder vollständig ausgeführt und erfolgreich übermittelt oder alle werden ohne Fehler zurückgesetzt.

    (2) Konsistenz

    Die Datenbank wechselt immer von einem konsistenten Zustand in einen anderen konsistenten Zustand.

    (3) Isolation

    Die von einer Transaktion vorgenommenen Änderungen sind für andere Transaktionen nicht sichtbar, bevor sie endgültig übermittelt werden.

    (4) Haltbarkeit

    Sobald die Transaktion übermittelt wurde, werden die vorgenommenen Änderungen dauerhaft in der Datenbank gespeichert.

    12. Index

    Der Index ist eine Datenstruktur, die von der Speicher-Engine zum schnellen Auffinden von Datensätzen verwendet wird. Ich denke, der wichtigste Wissenspunkt in der Datenbank ist der Index.

    Speicher-Engines verwenden B-Tree-Indizes auf unterschiedliche Weise und auch ihre Leistung ist unterschiedlich, jede mit ihren eigenen Vor- und Nachteilen. MyISAM verwendet beispielsweise die Präfixkomprimierungstechnologie, um den Index zu verkleinern, InnoDB speichert ihn jedoch im ursprünglichen Datenformat. MyISAM-Indizes beziehen sich auf indizierte Zeilen anhand des physischen Speicherorts der Daten, während InnoDB sich auf indizierte Zeilen anhand ihres Primärschlüssels bezieht.

    

    B-Baum bedeutet normalerweise, dass alle Werte der Reihe nach gespeichert werden und jede Blattseite den gleichen Abstand von der Wurzel hat.

    Der B-Tree-Index kann den Datenzugriff beschleunigen, da die Speicher-Engine keinen vollständigen Tabellenscan mehr durchführen muss, um die erforderlichen Daten zu erhalten, sondern stattdessen vom Wurzelknoten des Index aus sucht. Die Slots des Root-Knotens speichern Zeiger auf untergeordnete Knoten, und die Speicher-Engine sucht basierend auf diesen Zeigern abwärts. Indem Sie den Wert der Knotenseite mit dem gesuchten Wert vergleichen, können Sie geeignete Zeiger auf die unteren untergeordneten Knoten finden. Diese Zeiger definieren tatsächlich die Ober- und Untergrenzen der Werte auf der untergeordneten Knotenseite. Schließlich findet die Speicher-Engine entweder den entsprechenden Wert oder der Datensatz ist nicht vorhanden.

    Blattknoten sind etwas Besonderes. Ihre Zeiger zeigen auf die indizierten Daten und nicht auf andere Knotenseiten. B-Tree organisiert und speichert Indexspalten nacheinander und eignet sich daher sehr gut für die Suche nach Bereichsdaten. B-Tree eignet sich für die Suche nach vollständigen Schlüsselwerten, Schlüsselwertbereichen oder Schlüsselpräfixen.
    Da die Knoten im Indexbaum geordnet sind, kann der Index nicht nur für die Suche nach Wert, sondern auch für die Sortierung nach Vorgängen in Abfragen verwendet werden. Wenn ein B-Baum einen Wert auf eine bestimmte Weise finden kann, kann er im Allgemeinen auch zum Sortieren auf diese Weise verwendet werden.

    13. Volltextindex

    Der Zweck des Volltextindex besteht darin, Abfragen durch Schlüsselwortübereinstimmung zu filtern, basierend auf Ähnlichkeitsabfragen, und nicht auf präzisen Abfragen.

    Der Volltextindex analysiert mithilfe der Wortsegmentierungstechnologie die Häufigkeit und Wichtigkeit bestimmter Schlüsselwörter im Text und filtert auf intelligente Weise die gewünschten Ergebnisse gemäß einem bestimmten Algorithmus heraus.

    Der Volltextindex wird im Allgemeinen zum Abfragen bestimmter Schlüsselwörter in Zeichenfolgen verwendet, z. B. char, varchar und text. Er unterstützt auch den Volltextindex in natürlicher Sprache und den booleschen Volltextindex.

    Empfohlenes Lernen: MySQL-Video-Tutorial

Länge	Anweisungen
2	unterstützt Chinesisch, ist aber kein internationaler Zeichensatz
3	unterstützt gemischte chinesische und englische Szenarien und ist ein internationaler universeller Zeichensatz.
1	MySQL-Standardzeichensatz. 8. Verwenden Sie vier Bytes, um mehr Zeichen zu speichern

Das obige ist der detaillierte Inhalt von13 Schlüsselwörter, die Sie kennen müssen, um MySQL zu lernen (Zusammenfassungsfreigabe). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!