Ausführliche Erläuterung der MySQL-Lern-Engine, Erläuterungen und Berechtigungen

Motor

Einführung

Innodb-Engine

Die Innodb-Engine bietet Unterstützung für Datenbank-ACID-Transaktionen und implementiert die vier Isolationsebenen des SQL-Standards. Die Engine bietet außerdem Sperren auf Zeilenebene und Fremdschlüsseleinschränkungen. Sie ist für die Handhabung von Datenbanksystemen mit großer Kapazität ausgelegt. Tatsächlich handelt es sich um ein vollständiges Datenbanksystem, das auf dem MySQL-Backend basiert. Wenn MySQL ausgeführt wird, richtet Innodb einen Pufferpool im Speicher ein, um Daten und Indizes zu puffern. Diese Engine unterstützt jedoch keine FULLTEXT-Indizes und speichert nicht die Anzahl der Zeilen in der Tabelle. Wenn SELECT COUNT(*) FROM TABLE ausgeführt wird, muss die gesamte Tabelle gescannt werden. Diese Engine ist natürlich die erste Wahl, wenn Datenbanktransaktionen erforderlich sind. Da die Sperrgranularität geringer ist, wird bei Schreibvorgängen nicht die gesamte Tabelle gesperrt. Bei hoher Parallelität verbessert die Verwendung der Innodb-Engine daher die Effizienz. Die Verwendung von Sperren auf Zeilenebene ist jedoch nicht absolut. Wenn MySQL beim Ausführen einer SQL-Anweisung den zu scannenden Bereich nicht bestimmen kann, sperrt die InnoDB-Tabelle auch die gesamte Tabelle.

MyIASM-Engine

MyIASM ist die Standard-Engine für MySQL, bietet jedoch keine Unterstützung für Datenbanktransaktionen, Sperren auf Zeilenebene und Fremdschlüssel. Daher muss beim INSERT (Einfügen) oder UPDATE (Aktualisieren) von Daten der Schreibvorgang die gesamte Tabelle sperren, was weniger effizient ist. Im Gegensatz zu Innodb speichert MyIASM jedoch die Anzahl der Zeilen in der Tabelle, sodass Sie bei SELECT COUNT(*) FROM TABLE nur den gespeicherten Wert direkt lesen müssen, ohne die gesamte Tabelle zu scannen. Wenn die Tabelle deutlich mehr Lese- als Schreibvorgänge aufweist und keine Unterstützung von Datenbanktransaktionen benötigt, ist MyIASM ebenfalls eine gute Wahl.

Hauptunterschiede

1. MyIASM ist nicht transaktionssicher, während InnoDB transaktionssicher ist

2. Die Granularität der MyIASM-Sperren erfolgt auf Tabellenebene, während InnoDB Sperren auf Zeilenebene unterstützt

3. MyIASM unterstützt die Volltextindizierung, während InnoDB die Volltextindizierung nicht unterstützt

4. MyIASM ist relativ einfach und effizienter als InnoDB. Kleine Anwendungen können die Verwendung von MyIASM in Betracht ziehen

5. Die MyIASM-Tabelle wird im Dateiformat gespeichert, was für die plattformübergreifende Verwendung praktischer ist

Anwendungsszenario

1. MyIASM verwaltet nicht transaktionale Tabellen, bietet Hochgeschwindigkeitsspeicherung und -abruf sowie Volltextsuchfunktionen. Wenn Sie in Ihrer Anwendung eine große Anzahl von Auswahlvorgängen durchführen, sollten Sie sich für MyIASM entscheiden.

2. InnoDB wird für die Transaktionsverarbeitung verwendet und verfügt über Funktionen wie ACID-Transaktionsunterstützung. Wenn in der Anwendung eine große Anzahl von Einfüge- und Aktualisierungsvorgängen ausgeführt wird, sollte InnoDB ausgewählt werden

MySQL erklären

Bei unserer täglichen Arbeit führen wir manchmal langsame Abfragen aus, um einige SQL-Anweisungen aufzuzeichnen, deren Ausführung lange dauert. Das Auffinden dieser SQL-Anweisungen bedeutet nicht, dass die Arbeit erledigt ist. Manchmal verwenden wir häufig den Befehl „explain“, um den Ausführungsplan dieser SQL-Anweisungen anzuzeigen und festzustellen, ob die SQL-Anweisung einen Index verwendet und ob ein vollständiger Tabellenscan durchgeführt wird. Dies kann über den Befehl „explain“ angezeigt werden. Wir verfügen also über ein tiefes Verständnis des kostenbasierten Optimierers von MySQL und können auch viele Einzelheiten zu den Zugriffsstrategien erhalten, die vom Optimierer in Betracht gezogen werden können, und dazu, welche Strategie vom Optimierer voraussichtlich beim Ausführen von SQL-Anweisungen übernommen wird. (QEP: SQL generiert einen Ausführungsplan für die Abfrageausführung)

mysql> erläutern Sie „select * from servers“;
+----+----------+---------+------+---------------+-----------+---------+---------+------+------+------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------+------+---------------+-----------+---------+---------+------+------+------+
| 1 | EINFACH | Server | ALLE | NULL | NULL | NULL | NULL | 1 | NULL |
+----+----------+---------+------+---------------+-----------+---------+---------+------+------+------+
Zeile im Satz (0,03 Sek.)

Die von expain ausgegebenen Informationen haben 10 Spalten, nämlich id, select_type, table, type, Possible_keys, key, key_len, ref, rows und Extra. Im Folgenden finden Sie eine Erklärung des möglichen Erscheinungsbilds dieser Felder:

1. Ich würde

Nach meinem Verständnis ist es die Reihenfolge, in der SQL ausgeführt wird. SQL wird von groß nach klein ausgeführt.

1. Wenn die ID gleich ist, erfolgt die Ausführungsreihenfolge von oben nach unten

2. Wenn es sich um eine Unterabfrage handelt, erhöht sich die ID-Nummer. Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt.

3. Wenn die ID gleich ist, kann sie als Gruppe betrachtet und von oben nach unten ausgeführt werden. In allen Gruppen gilt: Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt

2. Typ auswählen

Gibt den Typ jeder Select-Klausel in der Abfrage an

(1) SIMPLE (einfaches SELECT, ohne Verwendung von UNION oder Unterabfragen usw.)

(2) PRIMARY (Wenn die Abfrage komplexe Unterteile enthält, wird die äußerste Auswahl als PRIMARY gekennzeichnet)

(3) UNION (die zweite oder nachfolgende SELECT-Anweisung in UNION)

(4) DEPENDENT UNION (die zweite oder nachfolgende SELECT-Anweisung in einer UNION hängen von der äußeren Abfrage ab)

(5) UNION-ERGEBNIS

(6) SUBQUERY (das erste SELECT in einer Unterabfrage)

(7) DEPENDENT SUBQUERY (das erste SELECT in einer Unterabfrage, die von der äußeren Abfrage abhängt)

(8) DERIVED (abgeleitete Tabelle SELECT, Unterabfrage in FROM-Klausel)

(9) UNCACHEABLE SUBQUERY (das Ergebnis einer Unterabfrage kann nicht zwischengespeichert werden und die erste Zeile des äußeren Links muss erneut ausgewertet werden)

3. Tabelle

Zeigt an, um welche Tabelle es sich bei den Daten in dieser Zeile handelt. Manchmal ist es nicht der eigentliche Tabellenname, sondern derivedx (x ist eine Zahl, die ich als Ergebnis der Ausführung des Schrittes verstehe)

mysql> erklären Sie „select * from“ (select * from (select * from t1 where id=2602) a) b;
+----+----------+------------+--------+-------------------+---------+---------+------+------+------+----------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+------------+--------+-------------------+---------+---------+------+------+------+----------+
| 1 | PRIMARY | <abgeleitet2> | System | NULL | NULL | NULL | NULL | 1 | |
| 2 | ABGELEITET | <abgeleitet3> | System | NULL | NULL | NULL | NULL | 1 | |
| 3 | ABGELEITET | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |
+----+----------+------------+--------+-------------------+---------+---------+------+------+------+----------+

Typ

Gibt an, wie MySQL die erforderliche Zeile in der Tabelle findet (auch als „Zugriffstyp“ bezeichnet).

Gängige Typen sind: ALL, index, range, ref, eq_ref, const, system, NULL (von links nach rechts, Leistung von der schlechtesten zur besten)

ALL: Vollständiger Tabellenscan, MySQL durchsucht die gesamte Tabelle nach übereinstimmenden Zeilen

index: Vollständiger Index-Scan. Der Unterschied zwischen index und ALL besteht darin, dass der Indextyp nur den Indexbaum durchläuft.

Bereich: Ruft nur die Zeilen in einem bestimmten Bereich ab und verwendet einen Index zum Auswählen der Zeilen.

ref: Gibt die Join-Matching-Bedingung der obigen Tabelle an, d. h. welche Spalten oder Konstanten verwendet werden, um den Wert in der Indexspalte zu finden

eq_ref: Ähnlich wie ref, der Unterschied besteht darin, dass der verwendete Index ein eindeutiger Index ist. Für jeden Indexschlüsselwert gibt es nur einen übereinstimmenden Datensatz in der Tabelle. Einfach ausgedrückt bedeutet dies, dass bei Verknüpfungen mehrerer Tabellen der Primärschlüssel oder eindeutige Schlüssel als Verknüpfungsbedingung verwendet wird.

const, system: Wenn MySQL einen Teil der Abfrage optimiert und in eine Konstante umwandelt, verwendet es diese Zugriffsarten. Wenn Sie den Primärschlüssel in die Where-Liste einfügen, kann MySQL die Abfrage in eine Konstante umwandeln. System ist ein Sonderfall des Typs const. Wenn die Abfragetabelle nur eine Zeile hat, verwenden Sie System.

NULL: MySQL zerlegt Anweisungen während der Optimierung und greift während der Ausführung nicht einmal auf Tabellen oder Indizes zu. Beispielsweise kann die Auswahl des Mindestwerts aus einer Indexspalte mit einer einzigen Indexsuche erfolgen.

5. mögliche Schlüssel

Gibt an, welchen Index MySQL verwenden kann, um Datensätze in der Tabelle zu finden. Wenn für das in der Abfrage enthaltene Feld ein Index vorhanden ist, wird dieser Index aufgelistet, kann aber von der Abfrage nicht verwendet werden.

Diese Spalte ist völlig unabhängig von der Reihenfolge der in der EXPLAIN-Ausgabe angezeigten Tabellen. Dies bedeutet, dass einige der Schlüssel in „possible_keys“ in der Reihenfolge, in der die Tabelle generiert wird, nicht verwendet werden können.
Wenn die Spalte NULL ist, gibt es keinen zugehörigen Index. In diesem Fall können Sie die Abfrageleistung verbessern, indem Sie die WHERE-Klausel überprüfen, um festzustellen, ob sie auf bestimmte Spalten oder auf für die Indizierung geeignete Spalten verweist. Wenn ja, legen Sie einen entsprechenden Index an und prüfen Sie die Abfrage noch einmal mit EXPLAIN.

6. Schlüssel

In der Schlüsselspalte wird der Schlüssel (Index) angezeigt, für dessen Verwendung sich MySQL entschieden hat.

Wenn kein Index ausgewählt wurde, ist der Schlüssel NULL. Um MySQL zu zwingen, den Index der Spalte „possible_keys“ zu verwenden oder zu ignorieren, verwenden Sie in Ihrer Abfrage FORCE INDEX, USE INDEX oder IGNORE INDEX.

7. Schlüssellänge

Gibt die Anzahl der im Index verwendeten Bytes an. Mit dieser Spalte kann die Länge des in der Abfrage verwendeten Indexes berechnet werden (der von key_len angezeigte Wert ist die maximal mögliche Länge des Indexfeldes, nicht die tatsächlich verwendete Länge, d. h. key_len wird basierend auf der Tabellendefinition berechnet und nicht aus der Tabelle abgerufen).

Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren.

8. Schiedsrichter

Gibt die Join-Matching-Bedingungen der obigen Tabellen an, d. h., welche Spalten oder Konstanten verwendet werden, um die Werte in den Indexspalten zu finden

9. Reihen

Gibt die Anzahl der Zeilen an, die MySQL nach Einschätzung seiner Mitarbeiter lesen muss, um die erforderlichen Datensätze basierend auf Tabellenstatistiken und Indexauswahl zu finden.

10. Extra

In dieser Spalte finden Sie detaillierte Informationen darüber, wie MySQL die Abfrage löst. Es gibt mehrere Fälle:

Verwenden von where: Spaltendaten werden aus der Tabelle zurückgegeben, wobei nur die Informationen im Index verwendet werden, ohne dass die Tabelle tatsächlich gelesen wird. Dies geschieht, wenn alle angeforderten Spalten für die Tabelle Teil desselben Index sind, was bedeutet, dass der MySQL-Server eine Filterung durchführt, nachdem die Speicher-Engine die Zeilen abgerufen hat.

Temporär verwenden: Gibt an, dass MySQL eine temporäre Tabelle zum Speichern des Ergebnissatzes verwenden muss, was beim Sortieren und Gruppieren von Abfragen üblich ist

Verwenden von Filesort: Der Sortiervorgang in MySQL, der nicht mithilfe von Indizes abgeschlossen werden kann, wird als "Dateisortierung" bezeichnet.

Join-Puffer verwenden: Dieser Wert betont, dass beim Abrufen der Join-Bedingung kein Index verwendet wird und ein Join-Puffer zum Speichern von Zwischenergebnissen erforderlich ist. Wenn dieser Wert angezeigt wird, sollten Sie sich darüber im Klaren sein, dass Sie je nach den spezifischen Umständen der Abfrage möglicherweise einen Index hinzufügen müssen, um die Leistung zu verbessern.

Unmöglich, wo: Dieser Wert betont, dass die Where-Klausel keine qualifizierenden Zeilen zur Folge hat.

Tabellen mit optimierter Auswahl: Dieser Wert bedeutet, dass der Optimierer nur eine Zeile aus dem Ergebnis der Aggregatfunktion zurückgeben kann, indem nur der Index verwendet wird.

Zusammenfassen:

• EXPLAIN informiert Sie nicht über Trigger, gespeicherte Prozeduren oder die Auswirkung benutzerdefinierter Funktionen auf die Abfrage.

• EXPLAIN berücksichtigt nicht verschiedene Caches

• EXPLAIN zeigt nicht die Optimierungsarbeit, die MySQL bei der Ausführung der Abfrage durchführt.

• Einige Statistiken sind Schätzungen und keine genauen Werte

• EXPALIN kann nur SELECT-Operationen erklären. Andere Operationen müssen als SELECT umgeschrieben und anschließend der Ausführungsplan angezeigt werden.

MySQL-Autorisierungsverwaltung

Grammatik:

Berechtigungen für „Benutzer“@„IP-Adresse“ anzeigen; – Berechtigungen anzeigen
Erteilen Sie 'Benutzer'@'IP-Adresse' die Berechtigung für Datenbank.Tabelle; -- Autorisierung
Berechtigung für Datenbank.Tabelle von „Benutzer“@„IP-Adresse“ widerrufen; – Berechtigung widerrufen

Berechtigungsparameter

alle Privilegien Alle Privilegien außer grant
Wählen Sie „Nur Berechtigungen prüfen“ aus.
Auswählen, Abfrage einfügen und Berechtigungen einfügen
...
Nutzung Keine Zugriffsrechte
alter Verwenden Sie die Alter-Tabelle
Routine mit „Alter Procedure“ und „Drop Procedure“ ändern
erstellen Verwenden Sie „create table“
Routine erstellen Prozedur „Erstellen“ verwenden
Temporäre Tabellen erstellen Verwenden Sie „Temporäre Tabellen erstellen“
Benutzer erstellen Verwenden Sie die Funktionen „Benutzer erstellen“, „Benutzer löschen“, „Benutzer umbenennen“ und „Alle Berechtigungen widerrufen“.
Ansicht erstellen Ansicht erstellen verwenden
löschen Löschen verwenden
drop Verwenden der Drop-Tabelle
Ausführen mit Aufruf und gespeicherter Prozedur
Datei verwendet „Select into outfile“ und „Load data infile“
Gewährungsoption mithilfe von „Grant“ und „Revocation“
index Verwenden von index
einfügen Einfügen verwenden
Sperrtabellen Verwenden der Sperrtabelle
Prozessverwendung: Vollständige Prozessliste anzeigen
auswählen Auswahl verwenden
Datenbanken anzeigen Verwenden von Datenbanken anzeigen
Ansicht anzeigen Ansicht anzeigen verwenden
aktualisieren
Neuladen mit Flush
shutdown Verwenden Sie mysqladmin shutdown (MySQL herunterfahren)
super 􏱂􏰈Verwenden Sie „Change Master“, „Kill“, „Logs“, „Purge“, „Master“ und „Set Global“. Ermöglicht auch mysqladmin􏵗􏵘􏲊􏲋debug-Login
Replikation Client Server Standort Zugriff
Replikations-Slave Wird von Replikations-Slaves verwendet

Für Berechtigungen

Berechtigungsparameter

Datenbankparameter

Für die Zieldatenbank und andere darin:
Datenbankname.* Alle in der Datenbank
Datenbankname. Tabelle gibt eine Tabelle in der Datenbank an
Datenbankname.Gespeicherte Prozedur Gibt die gespeicherte Prozedur in der Datenbank an
*.* Alle Datenbanken

Benutzer-IP-Parameter

Benutzername@IP-Adresse Der Benutzer kann nur unter der geänderten IP-Adresse darauf zugreifen. [email protected].% Der Benutzer kann nur unter dem geänderten IP-Segment darauf zugreifen (das Platzhalterzeichen % bedeutet beliebig).
Benutzername@% Der Benutzer kann von jeder IP-Adresse aus auf die Site zugreifen (die Standard-IP-Adresse ist %)

Beispiel

gewähre 'Benutzername'@'IP' alle Privilegien für db1.tb1;
 
   gewähre Auswahl für db1.* an „Benutzername“@„IP“;
 
   grant select, insert on *.* TO 'Benutzername'@'IP';
 
   Auswahl von db1.tb1 von „Benutzername“@„IP“ widerrufen;

Berechtigungen aktualisieren

Löschen Sie die Berechtigungen und lesen Sie die Daten in den Speicher, damit sie sofort wirksam werden.

Passwort vergessen

# Starten Sie den autorisierungsfreien Server mysqld --skip-grant-tables
 
# Client mysql -u root -p
 
# Benutzernamen und Passwort ändern update mysql.user set authentication_string=password('666') where user='root';
Berechtigungen leeren;
 
Passwort vergessen

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.