MySQL-Abfragen optimieren

MySQL-Abfragen optimieren

MySQL-Abfragen optimieren : Mehreren tausend Tipps, um Ihr Leben einfacher machen.

Wenn Abfragen viel Zeit nimmt und Datenbankverbindungen und Rückstände wachsen, dann ist es Zeit, Ihre Datenbank zu optimieren. In MySQL können Sie Mysqlreport Befehl Report auf ausreichend verwendeten Datenbank erstellen und Einrichten einer Basislinie für weitere Maßnahmen. Sie können auch verpflichtet werden, ändern Sie die Konfigurationsdatei (my.cnf) zum Protokollieren von langsamer Abfragen. Die langsamen ausgeführten Abfragen und die möglichen Ursachen zu identifizieren. In der Regel beziehen sich die Ursachen auf Indizes wie kein Index oder unsachgemäße Index. Dieser Artikel gibt eine detaillierte Beschreibung, welche Befehle und Konzepte, die wir verwenden können, um MySQL zu optimieren.

Schritt 1

Verwendung von Indizes

Verwendung von Indizes wird die Abfrage schneller auf die gleiche Weise, wie ein Index in einem Buch hilft bei der Suche nach einem bestimmten Wort.

Zu wissen, wie und welche Indizes in einer bestimmten Abfrage verwendet werden, verwenden Sie EXPLAIN Befehl als Präfix, um die Abfrage auswählen.

EXPLAIN SELECT Emp_id FROM ORGN_DATA WHERE Dept_code = 'IT';

Dieser Befehl gibt folgende Informationen zurück:

Tabelle - welche Tabellen in der Abfrage verwendet werden
Typ - JOIN-Typ. Werte können sein System, Const, Eq_ref, Ref, Palette, Index, alle
Möglichen Schlüssel - alle Schlüssel, die für Indizes verwendet werden kann
Schlüssel - der Schlüssel tatsächlich für index
Schlüssellänge - kürzere Schlüssel sind besser
Ref - andere Spalten mit Schlüssel verwendet, um die Ergebnisse zu erzielen
Zeilen - Anzahl der Zeilen ab, woher die Daten kommen soll
Extra - Extra-Informationen. Einige mögliche Werte können Index verwenden, verwenden Where, temporäre, wobei Filesort verwenden

Sie sollten Ihre Tabellenstruktur überdenken, wenn mögliche Schlüssel NULL-Wert enthalten.
In Schlüsselfeld der Nullwert bedeutet, dass kein Index verwendet wird. Verwenden keine Indizes wird die Leistung abschrecken, wenn zu viele Datensätze in der Tabelle vorhanden sind, da für jede Abfrage, die Datensätze für die angegebene Bedingung durchsucht werden.

Verwenden Sie die ALTER-Befehl Index laut unten hinzufügen:

ALTER TABLE Table_name ADD INDEX (Column_name);

Sie können tun, Indizierung über mehrere Spalten verwenden:

ALTER TABLE Table_name ADD INDEX (column_name1,.., Column_nameN);

MySQL verwendet am weitesten links stehende voranstellen, damit automatisch Indizes hinzugefügt werden soll, für column_name1, (column_name1, column_name2)... (column_name1,.., Column_NameN-1). Dies ist sehr hilfreich in Situationen wie Suche nach Familiennamen basiert, so dass ein Index für (Name, Vorname, Middlename) mehrere Indizes beim schneller Abfragen verwendet automatisch hinzugefügt werden.

Punkte zu erinnern:

• Wählen Sie die Spalten für die Indizierung sehr sorgfältig.

• Indizierte Felder sollte verwendet werden für Suche und Abfrage neu gerahmt sein sollte, wenn eine Berechnung auf indizierte Felder durchgeführt wird.

• Index selbst hat einen Speicherplatz.

• Jeden Vorgang auf Datenbank aktualisieren Index als auch erforderlich ist.

• Ein kleiner Schlüssellänge von EXPLAIN Befehl gemeldet ist besser. Kleinere Schlüssellänge bedeutet weniger Speicherplatz wird von Index-Datei benötigt und auch damals zur Suche im Index ist kleiner. Hierzu können Indizes auf Teil der Spalten festgelegt werden. Z. B. INDEX(col1(10),col2(20)). Auch hier müssen Sie vernünftig entscheiden, welche Länge Spalten für die Indizierung verwendet werden.

Schritt 2

ANALYZE-Befehl

Verwenden Sie ANALYZE-Befehl zum Generieren von Schlüsselverteilung für die Tabelle von MySQL-Abfrage-Optimierer verwendet werden, um zu entscheiden, welche Indizes sind am besten in einer Abfrage verwenden.

ANALYZE TABLE Table_name;

Schritt 3

OPTIMIZE-Befehl

Wenn es zu viele Einsätze/Löschvorgänge in einer Tabelle gibt muss OPTIMIZE-Befehl häufig verwendet werden, um Datenträger Kopfbewegung bei Abruf zu optimieren. Dies ist besonders nützlich, wenn Variable Größe Spalten wie Varchar, Text, Blob etc. verwendet werden. Optimieren Sie Befehl tut Defragmentierung der Fragmentierung verursacht durch Sonderlänge Felder wie VARCHAR.

OPTIMIZE TABLE Table_name;

Schritt 4

Verwenden spezielle Funktionen zum Laden von Daten

Zum Laden von Daten aus einer Datei in eine Datenbanktabelle, ist die Verwendung von LOAD DATA INFILE wesentlich schneller als das Lesen von Daten aus Datei iterativ und INSERT-Befehl. Die Syntax für diesen Befehl lautet:

LOAD DATA INFILE 'filedata.dat' INTO TABLE Table_name (col1, col2,..., ColM) Felder beendet durch ' |'');

Schritt 5

Einstellung Priorität von Befehlen

Wenn es mehr Anfragen als Dateneinfügungen gibt, können Sie Priorität des INSERT-Befehl verwenden:

LOW_PRIORITY einfügen oder SELECT HIGH_PRIORITY

Wenn der Client nicht Ergebnisse INSERT-, interessiert ist, dann sie sofort freigegeben werden kann mit dem folgenden Befehl:

Einfügen, die verzögert.

Dies macht das System schneller von Batchverarbeitung Einfügungen.

Schritt 6

Löschen vs. Abschneiden

DELETE Löscht Zeile für Zeile, während TRUNCATE alle Zeilen auf einmal löscht. Also, wenn Sie nicht die Anzahl von Zeilen aus einer Tabelle als Ergebnis der Löschbefehl gelöscht interessiert sind, dann verwenden Abschneiden mit der folgenden Syntax:

TRUNCATE TABLE Table_name;

Schritt 7

Senkung der Berechtigung überprüfen overhead

Einfachere Berechtigungen reduzieren Berechtigung überprüfen overhead Verwenden Sie GRANT-Befehl, um die Berechtigungen festgelegt. Allgemeine Syntax für GRANT lautet:

GRANT Oper1,..., OperN-ON db_name.tb_name user_name@computer_name IDENTIFIED BY Kennwort

Zum Beispiel:

GRANT SELECT, UPDATE AUF EMP_DET. SALARY_DET auf dem feroz@localhost IDENTIFIED BY 'abc123';

Wenn Benutzerkonto 'Feroz' nicht vorhanden ist, dann es wird auf 'localhost' erstellt (obwohl Benutzer Daten von jedem Computer aus zugreifen kann) und Kennwort 'abc123' wird es zugeordnet werden.

Schritt 8

Verwenden Sie BENCHMARK()-Funktion

Um zu wissen, wieviel einer bestimmten MySQL-Funktion oder einem Ausdruck Zeit stattfindet, verwenden Sie MySQL integrierte Funktion:

Maßstab (Loop_count, Ausdruck)

Es gibt immer 0 zurück, sondern druckt auch Zeitaufwand durch den Ausdruck.

Schritt 9

Synchrozing Datentypen

Wenn Spalten in Tabellen identische Informationen enthalten, dann deren Datentypen können auch gleich damit, die tritt schneller durchgeführt werden.

Schritt 10

Verschiedenes

• Mehrere Zeilen mit einzelnen SQL-Anweisung einfügen.

• Entfernen Sie unnötige Klammer in WHERE-Klausel.

• Wenn die Option SQL_SMALL_RESULT verwendet wird, verwendet MySQL temporäre Tabellen im Arbeitsspeicher die schnelleren Zugriff zu bieten.

Abfrageoptimierung ist keine einmalige Zeit Stelle. Wenn Ihre Datenbank stark ausgelastet ist dann musst du den Optimierungsprozess alle 5-6 Monate wiederholen.