MySQL
Mysql Performance-Tipps

Wählen Sie Statement Optimization aus

Nachfolgend finden Sie einige Tipps, die Sie beim Schreiben einer Auswahlabfrage in MySQL berücksichtigen sollten, die uns helfen und unsere Abfragezeit verkürzen kann:

1. Wann immer wir wo in einer großen Tabelle verwenden, sollten wir sicherstellen, dass die Spalte in der where-Klausel index ist oder nicht. Bsp .: - Wählen Sie * aus dem Mitarbeiter, bei dem Benutzer-ID> 2000 ist. Benutzer-ID (falls indiziert) beschleunigt die Auswertung des Abfrageslot. Indizes sind auch bei Verknüpfungen und Fremdschlüsseln sehr wichtig.

2. Wenn Sie einen kleineren Inhaltsbereich benötigen, anstatt ganze Daten aus der Tabelle abzurufen, versuchen Sie, die Begrenzung zu verwenden. Schreiben Sie stattdessen Ex: - Wählen Sie * vom Mitarbeiter aus. Wenn Sie nur die ersten 20 Mitarbeiter von Lakhs benötigen, verwenden Sie einfach die Grenze Ex: - Wählen Sie * aus dem Mitarbeiter LIMIT 20 aus.

3. Sie können Ihre Abfrage auch optimieren, indem Sie den gewünschten Spaltennamen in der Ergebnismenge angeben. Schreiben Sie stattdessen Ex: - Wählen Sie * vom Mitarbeiter aus. Geben Sie einfach den Namen der Spalte an, aus der Sie Daten benötigen, wenn Ihre Tabelle viele Spalten enthält und Sie Daten für einige davon haben möchten. Beispiel: - Wählen Sie die ID und den Namen des Mitarbeiters aus.

4. Indexspalte, wenn Sie verwenden, um in where-Klausel nach NULL zu suchen. Wenn Sie eine Anweisung als SELECT * FROM Tabellenname WHERE key_col IS NULL haben; Wenn key_col indiziert wird, wird die Abfrage schneller ausgewertet.

Speicherlayout für InnoDB-Tabellen optimieren

1. In InnoDB kostet der lange PRIMARY KEY (entweder eine einzelne Spalte mit einem langen Wert oder mehrere Spalten, die einen langen zusammengesetzten Wert bilden) viel Speicherplatz. Der Primärschlüsselwert für eine Zeile wird in allen sekundären Indexdatensätzen dupliziert, die auf dieselbe Zeile zeigen. Erstellen Sie eine AUTO_INCREMENT-Spalte als Primärschlüssel, wenn Ihr Primärschlüssel lang ist.
2. Verwenden Sie den Datentyp VARCHAR anstelle von CHAR zum Speichern von Zeichenfolgen mit variabler Länge oder für Spalten mit vielen NULL-Werten. Eine CHAR (N) -Spalte benötigt immer N Zeichen, um Daten zu speichern, auch wenn die Zeichenfolge kürzer ist oder der Wert NULL ist. Kleinere Tabellen passen besser in den Pufferpool und reduzieren die Platten-E / A.

Wenn Sie das COMPACT-Zeilenformat (das Standard-InnoDB-Format) und Zeichensätze mit variabler Länge (z. B. utf8 oder sjis) verwenden, belegen CHAR (N) -Spalten einen variablen Speicherplatz, aber immer noch mindestens N Byte.

3. Bei großen Tabellen, die viele sich wiederholende Text- oder numerische Daten enthalten, sollten Sie das COMPRESSED-Zeilenformat verwenden. Es sind weniger Festplatten-E / A erforderlich, um Daten in den Pufferpool zu laden oder vollständige Tabellenscans durchzuführen. Bevor Sie eine dauerhafte Entscheidung treffen, messen Sie den Umfang der Komprimierung, die Sie durch die Verwendung des Zeilenformats COMPRESSED gegenüber COMPACT erreichen können. Vorbehalt: Benchmarks zeigen selten eine bessere Komprimierung als die 2: 1-Komprimierung, und im buffer_pool für COMPRESSED gibt es viel Aufwand.
4. Wenn Ihre Daten eine stabile Größe erreicht haben oder eine wachsende Tabelle um einige Dutzend oder einige Hundert Megabyte gestiegen ist, sollten Sie die Anweisung OPTIMIZE TABLE verwenden, um die Tabelle neu zu ordnen und überflüssigen Speicherplatz zu komprimieren. Die reorganisierten Tabellen erfordern weniger Platten-E / A, um vollständige Tabellenscans durchzuführen. Dies ist eine unkomplizierte Technik, die die Leistung verbessern kann, wenn andere Techniken, wie beispielsweise die Verbesserung der Indexnutzung oder das Optimieren von Anwendungscode, nicht praktikabel sind. Vorbehalt : Unabhängig von der Tabellengröße sollte OPTIMIZE TABLE nur selten ausgeführt werden. Dies liegt daran, dass es teuer ist und den Tisch selten genug verbessert, um es wert zu sein. InnoDB ist ziemlich gut darin, seine B + Trees von viel verschwendetem Speicherplatz freizuhalten.

OPTIMIZE TABLE kopiert den Datenteil der Tabelle und erstellt die Indizes neu. Die Vorteile ergeben sich aus dem verbesserten Packen von Daten in Indizes und einer geringeren Fragmentierung in den Tablespaces und auf der Festplatte. Die Vorteile variieren je nach Daten in jeder Tabelle. Möglicherweise gibt es für einige einige und nicht für andere signifikante Gewinne, oder die Gewinne nehmen mit der Zeit ab, bis Sie die Tabelle das nächste Mal optimieren. Dieser Vorgang kann langsam sein, wenn die Tabelle groß ist oder die neu erstellten Indizes nicht in den Pufferpool passen. Der erste Durchlauf, nachdem einer Tabelle viele Daten hinzugefügt wurden, ist häufig viel langsamer als spätere Durchläufe.

Erstellen eines zusammengesetzten Index

In vielen Situationen ist ein zusammengesetzter Index besser als ein Index mit einer einzelnen Spalte. Um einen optimalen zusammengesetzten Index zu erstellen, füllen Sie ihn mit Spalten in dieser Reihenfolge.

• = Spalte (n) zuerst aus der WHERE Klausel. (zB INDEX(a,b,...) für WHERE a=12 AND b='xyz' ... )
• IN Spalte (n); Der Optimierer kann möglicherweise den Index überspringen.
• Ein "Bereich" (zB x BETWEEN 3 AND 9 , name LIKE 'J%' ) Es wird nichts weiter als die erste Bereichsspalte verwendet.
• Alle Spalten in GROUP BY
• Alle Spalten in der Reihenfolge ORDER BY . Funktioniert nur, wenn alle ASC oder alle DESC oder Sie 8.0 verwenden.

Anmerkungen und Ausnahmen:

• Dupliziere keine Spalten.
• Überspringen Sie alle Fälle, die nicht zutreffen.
• Wenn Sie nicht alle Spalten von WHERE , müssen Sie nicht zu GROUP BY usw. weitergehen.
• Es gibt Fälle, in denen es nützlich ist, nur die ORDER BY Spalten zu indizieren, wobei WHERE ignoriert wird.
• "Hide" eine Spalte in einer Funktion nicht (zB DATE(x) = ... kann x im Index nicht verwenden.)
• Die "Präfix" text_col(99) (z. B. text_col(99) ) ist unwahrscheinlich hilfreich. kann weh tun

Weitere Details und Tipps .