Die Beispiele in diesem Artikel fassen die Indexnutzung der MySQL-Datenbankoptimierungstechnologie zusammen. Teilen Sie es als Referenz mit allen. Die Details lauten wie folgt:
Hier folgt der vorherige Artikel „Zusammenfassung der Konfigurationstechniken der MySQL-Datenbankoptimierungstechnologie“, um die Indexoptimierungstechniken weiter zu analysieren:
(7) Tabellenoptimierung
1. Wählen Sie die entsprechende Daten-Engine
MyISAM: Tabelle geeignet für eine große Anzahl von Lesevorgängen
InnoDB: Eine Tabelle, die für große Schreib- und Lesemengen geeignet ist
2. Wählen Sie den entsprechenden Spaltentyp
Verwenden Sie SELECT * FROM TB_TEST PROCEDURE ANALYSE(), um jedes Feld dieser Tabelle zu analysieren und Vorschläge zur Optimierung der Spaltentypen zu geben
3. Verwenden Sie NOT NULL für Spalten, die keine NULL-Werte speichern. Dies ist besonders wichtig für Spalten, die Sie indizieren möchten
4. Erstellen Sie geeignete Indizes
5. Verwenden Sie Felder mit fester Länge, die schneller sind als Felder mit variabler Länge
(8) Indexierungsprinzipien
1. Verwenden Sie Indizes angemessen
Eine Tabelle kann nur einen Index in einer Abfrage verwenden. Verwenden Sie die EXPLAIN-Anweisung, um die Funktionsweise des Optimierers zu überprüfen
Verwenden Sie „Analysieren“, um dem Optimierer dabei zu helfen, genauere Vorhersagen über die Indexnutzung zu treffen
2. Indizes sollten für die Datenspalten erstellt werden, die an Suchen, Sortieren, Gruppieren und anderen Vorgängen beteiligt sind
3. Versuchen Sie, den Index in einer Datenspalte mit weniger doppelten Daten zu erstellen, also ist es am besten
Zum Beispiel: Die Spalte „Geburtstag“ kann indiziert werden, die Spalte „Geschlecht“ sollte jedoch nicht indiziert werden
4. Versuchen Sie, kürzere Werte zu indizieren
Reduzieren Sie Festplatten-E/A-Vorgänge, der Indexpuffer kann mehr Schlüsselwerte aufnehmen und verbessern Sie die Trefferquote
Wenn Sie eine lange Zeichenfolge indizieren, können Sie eine Präfixlänge angeben
5. Richtige Verwendung von mehrspaltigen Indizes
Wenn für eine Abfrage häufig mehrere Bedingungen kombiniert werden müssen, sollte ein mehrspaltiger Index verwendet werden (da eine Tabelle nur einen Index für eine Abfrage verwenden kann und nur einer zum Erstellen mehrerer einspaltiger Indizes verwendet werden kann)
6. Nutzen Sie das Präfix ganz links vollständig aus
Das heißt, die Reihenfolge der Spalten im mehrspaltigen Index muss sinnvoll angeordnet sein und die am häufigsten verwendeten sollten an erster Stelle stehen
7. Erstellen Sie nicht zu viele Indizes
Nur Felder, die häufig in „Wo“, „Sortieren nach“ und „Gruppieren nach“ verwendet werden, müssen indiziert werden.
8. Verwenden Sie langsame Abfrageprotokolle, um langsame Abfragen zu finden (log-slow-queries, long_query_time)
(9) Nutzen Sie Indizes voll aus
1. Versuchen Sie, Datenspalten mit demselben Datentyp zu vergleichen
2. Machen Sie die Indexspalte im Vergleichsausdruck so unabhängig wie möglich. WHERE mycol < verwendet den Index, während WHERE mycol * 2 <
3. Versuchen Sie, den Abfragefeldern keine Funktionen hinzuzufügen,Zum Beispiel: WHERE YEAR(date_col) < wird in WHERE date_col < umgewandelt
WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < Cutoff wird in WHERE date_col <
4. Verwenden Sie keine Platzhalterzeichen am Anfang des LIKE-Musters5. Verwenden Sie Straight Join, um den Optimierer zu zwingen, in der Reihenfolge der FROM-Klausel zu verknüpfen. Sie können Straight Join auswählen, um alle Joins zu erzwingen, oder Sie können * aus einem Straight Join b auswählen, um die Reihenfolge der beiden Tabellen zu erzwingen .
6. Verwenden Sie Force Index, um die Verwendung des angegebenen Indexes zu erzwingen. Wählen Sie beispielsweise * aus der Reihenfolge „Song_lib Force Index(Song_Name)“ nach Song_Name aus, als „Force Index“ nicht zu verwenden
7. Versuchen Sie, die automatische Typkonvertierung von MySQL zu vermeiden, da Sie sonst den Index nicht verwenden können. Verwenden Sie beispielsweise where num_col='5'
für num_col vom Typ int (10) Optimierung von SQL-Anweisungen1. Erstellen Sie eine geeignete statistische Zwischenergebnistabelle, um die Wahrscheinlichkeit der Abfrage von Daten aus großen Tabellen zu verringern 2. Vermeiden Sie die Verwendung von Unterabfragen und verwenden Sie stattdessen Verknüpfungen:
kann geändert werden in:
c33947209e2813888067506b540caeb8 Der Code lautet wie folgt:
insert into db_action.action_today(user_id,song_id,action_count) Values(1,1,1) ON DUPLICATE KEY UPDATE action_count=action_count 1;
4. Vermeiden Sie die Verwendung von Cursorn
Die Betriebseffizienz von Cursorn ist äußerst gering. Sie können die Aufgabe durch das Hinzufügen temporärer Tabellen, die Verwendung von Multi-Table-Abfragen, Multi-Table-Updates usw. abschließen. Verwenden Sie keine Cursor. (11) Verwenden Sie Explain, um die Verwendung von Indizes in SQL-Anweisungen zu analysieren
Wenn Sie das Schlüsselwort EXPLAIN vor eine SELECT-Anweisung setzen, erklärt MySQL, wie die SELECT-Anweisung verarbeitet wird, und liefert Informationen darüber, wie die Tabellen verknüpft werden und in welcher Reihenfolge. Mit Hilfe von EXPLAIN können Sie wissen, wann die Tabelle sein muss indiziert Um ein schnelleres SELECT zu erhalten, das den Index zum Suchen von Datensätzen verwendet, können Sie auch wissen, ob der Optimierer die Tabellen in einer optimalen Reihenfolge verknüpft. Um den Optimierer zu zwingen, eine bestimmte Join-Reihenfolge für eine SELECT-Anweisung zu verwenden, fügen Sie eine STRAIGHT_JOIN-Klausel hinzu. .
Die allgemeine Syntax des EXPLAIN-Befehls lautet: EXPLAIN
Detaillierte Erläuterung der Analyseergebnisparameter von EXPLAIN:
1.Tabelle: Dies ist der Name der Tabelle.
2.Typ: Art des Verbindungsvorgangs.
System: Es gibt nur einen Datensatz in der Tabelle (in tatsächlichen Anwendungen werden Tabellen mit nur einem Daten selten verwendet)
const: Die Tabelle hat höchstens eine übereinstimmende Zeile, die beim Vergleich aller Teile des PRIMARY KEY- oder UNIQUE-Index mit einem konstanten Wert verwendet wird,
Zum Beispiel:
(song_id ist der Primärschlüssel der Tabelle)
eq_ref: Lesen Sie für jede Kombination von Zeilen aus der vorherigen Tabelle eine Zeile aus der Tabelle unter Verwendung des Index von UNIQUE oder PRIMARY KEY,
Zum Beispiel:
Der Code lautet wie folgt:
select * from song_lib a inner join Singer_lib b on a.singer_id=b.singer_id
ref: Lesen Sie für jede Kombination von Zeilen aus der vorherigen Tabelle eine Zeile aus der Tabelle mit einem anderen Index als UNIQUE oder PRIMARY KEY
Zum Beispiel:
Der Code lautet wie folgt:
select * from song_lib a inner join Singer_lib b on a.singer_name=b.singer_name
Der Code lautet wie folgt:
select * from Singer_lib b where Singer_name='ccc'
(der Typwert von b ist ref, da b.singer_name ein normaler Index ist)ref_or_null: Dieser Join-Typ ist derselbe wie ref, aber MySQL wurde hinzugefügt, um gezielt nach Zeilen zu suchen, die NULL-Werte enthalten,
Zum Beispiel:
Der Code lautet wie folgt:
wählen Sie * aus der Singer_Lib aus, wobei Singer_name='ccc' oder Singer_name null ist
index_merge: Dieser Join-Typ gibt an, dass die Index-Merge-Optimierungsmethode verwendet wird
Schlüssel: Zeigt den Namen des Index an, der tatsächlich von MySQL verwendet wird. Wenn es leer (oder NULL) ist, verwendet MySQL den Index nicht.
key_len: Die Länge des verwendeten Teils des Index in Bytes.
3.ref: Die Ref-Spalte zeigt, welche Spalte oder Konstante mit der Taste zum Auswählen von Zeilen aus der Tabelle
verwendet wird4.Zeilen: Die Anzahl der Datensätze, von denen MySQL glaubt, dass sie gescannt werden müssen, bevor das richtige Ergebnis gefunden wird. Offensichtlich ist die ideale Zahl hier 1.
5.Extra: Hier können viele verschiedene Optionen erscheinen, von denen sich die meisten negativ auf die Abfrage auswirken. Generell gilt:
wo verwenden: Gibt an, dass die Where-Bedingung verwendet wird
Dateisortierung verwenden: bedeutet, dass die Dateisortierung verwendet wird, d bedeutet, dass die Sortiereffizienz sehr hoch ist. Es muss eine Optimierung durchgeführt werden, z. B. mithilfe des Kraftindex
Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die speziellen Themen dieser Website ansehen: „Zusammenfassung der MySQL-Indexoperationsfähigkeiten“, „Umfassende Sammlung der MySQL-Protokolloperationsfähigkeiten“, „Zusammenfassung der MySQL-Transaktionsoperationsfähigkeiten“, „Umfassende Sammlung von MySQL-Kenntnissen für gespeicherte Prozeduren“, „Zusammenfassung der Techniken zum Sperren von MySQL-Datenbanken“ und „Zusammenfassung häufig verwendeter MySQL-Funktionen“
Ich hoffe, dass dieser Artikel für alle bei der MySQL-Datenbankplanung hilfreich sein wird.
