So optimieren Sie MySQL-Tabellen

Einführung

Die Optimierung von MySQL-Tabellen hilft bei der Neuordnung von Informationen in einem dedizierten Speicherserver, um die Dateneingabe- und -ausgabegeschwindigkeiten zu verbessern. Zu wissen, wann Sie jede Optimierungsfunktion verwenden und wie Sie sie auf Ihre Situation anwenden, ist jedoch der Schlüssel zu einer praktikablen Tabellenpflege.

Dieser Artikel enthält praktische Tipps und Funktionen zur Optimierung von MySQL-Tabellen.

Voraussetzungen

• MySQL-Version 8.0 installiert und konfiguriert
• Zugriff auf die Eingabeaufforderung oder Befehlszeile
• Fragmentierte Datenbanktabellen

Warum sollten Sie MySQL-Tabellen optimieren?

Der Hauptgrund für eine nicht optimierte MySQL-Tabelle sind häufig durchgeführte Aktualisierungs- und Löschabfragen. Dies führt wiederum zu einer Fragmentierung und hat einige Konsequenzen:

1. Die Datenbanktabelle benötigt mehr Platz als nötig.

2. Das Abfragen von Daten dauert mehr Zeit als es sollte.

MySQL-Tabellenoptimierungstechniken befassen sich mit der Anordnung von Daten innerhalb einer Datenbank. Das Ergebnis ist eine saubere Speicherung ohne redundanten, ungenutzten Speicherplatz, wodurch Abfragen beschleunigt werden.

Wann sollten Sie MySQL-Tabellen optimieren?

Tabellen, in denen Informationen in einer Datenbank ständig aktualisiert werden, wie z. B. Transaktionsdatenbanken, sind die wahrscheinlichsten Kandidaten für eine Optimierung.

Je nach Größe der Datenbank dauert es jedoch lange, bis die Optimierungsabfrage bei größeren Tabellen abgeschlossen ist. Daher ist das Sperren einer Tabelle für eine lange Anzahl von Stunden für Transaktionssysteme nicht vorteilhaft.

Anstatt eine Tabelle sofort zu optimieren, sollten Sie einige dieser Tricks für INNODB-Engine-Tabellen ausprobieren:

• Index löschen, optimieren und Index wieder hinzufügen . Wenn eine Tabelle einige Zeit ohne Indizes arbeitet, ist das Löschen, Optimieren und Hinzufügen der Indizes in einigen Fällen schneller.
• Analysieren Sie, welcher Wert am meisten von der Komprimierung profitiert . Manchmal ist der Primärindex nicht nützlich oder problematisch. Die Optimierung auf den falschen Wert führt zu einer Fragmentierung mit sekundären Indizes.

Tabellen zur Optimierung finden

Es gibt mehrere Möglichkeiten, Tabellen anzuzeigen und sie zur Optimierung zu analysieren. Verbinden Sie sich zunächst mit Ihrer MySQL-Datenbank:

use <database name>

Filtern Sie je nach Anwendungsfall die benötigten Informationen, indem Sie einen der folgenden Tipps befolgen.

Tipp 1:Ungenutzten Platz in einer Tabelle anzeigen

Überprüfen Sie den Status der gewünschten Tabelle mit:

show table status like "<table name>" \G

Die Ausgabe zeigt einige allgemeine Informationen über die Tabelle. Die folgenden zwei Zahlen sind wichtig:

• Datenlänge stellt den Speicherplatz dar, den die Datenbank insgesamt einnimmt.
• Datenfrei zeigt die zugewiesenen ungenutzten Bytes innerhalb der Datenbanktabelle. Anhand dieser Informationen können Sie ermitteln, welche Tabellen optimiert werden müssen und wie viel Speicherplatz danach freigegeben wird.

Tipp 2:Ungenutzten Platz für alle Tabellen anzeigen

Das Informationsschema speichert Metadaten zu einem Datenbankschema. Führen Sie Folgendes aus, um die zugewiesenen ungenutzten Daten für alle Tabellen in einem ausgewählten Schema zu überprüfen:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Die Abfrage zeigt den Namen der Tabelle, den Gesamtspeicherplatz und den nicht verwendeten zugewiesenen Speicherplatz an. Standardmäßig wird der Speicher in Bytes gedruckt.

Tipp 3:Daten in Megabyte anzeigen

Drucken Sie die Daten in Megabyte mit:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Die Beispieltabellen sind nicht stark fragmentiert. Verwenden Sie jedoch optimize table hilft, etwas Platz in der test_table freizugeben .

Tabellen optimieren

Es gibt mehrere Möglichkeiten, Tabellen durch Defragmentierung zu optimieren. Die allgemeinen Schritte, die Sie ausführen, sind jedoch ähnlich.

Zunächst erstellt der Prozess eine temporäre Kopie der Tabelle, in der die Optimierung stattfindet. Wenn der Vorgang abgeschlossen ist, ersetzt die Funktion dann die ursprüngliche Tabelle durch die optimierte und benennt sie nach dem Original um.

Tipp 1:Optimieren Sie eine Tabelle mit MySQL

MySQL bietet eine Funktion zum Optimieren einer Tabelle. Um eine Tabelle zu optimieren, verwenden Sie:

OPTIMIZE TABLE <table name>;

Die Ausgabe zeigt eine informative Statusmeldung über die Aktionen und die Ergebnisse der Optimierung in tabellarischer Form.

Tipp 2:Optimieren Sie mehrere Tabellen gleichzeitig

Um mehrere Tabellen gleichzeitig zu optimieren, verwenden Sie:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

Das Ergebnis zeigt den Status der Optimierung für jede optimierte Tabelle.

Tipp 3:Tabellen mit dem Terminal optimieren

Tabellenoptimierung über das Linux-Terminal durchführen mit:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Zum Beispiel, um die Prüfung für ein Schema namens test durchzuführen und Tabelle namens test_table unter Verwendung des Stammverzeichnisses Benutzeranmeldeinformationen, führen Sie Folgendes aus:

sudo mysqlcheck -o test test_table -u root

Um die Optimierung für mehrere Tabellen durchzuführen, trennen Sie jeden Tabellennamen durch ein Leerzeichen:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Nach der Optimierung

Die Ergebnisse der Optimierung ändern die Werte von data_length und datenfrei der optimierten Tabelle. Beide Werte werden verringert, was bedeutet:

1. Die Optimierung hat den ungenutzten zugewiesenen Speicher freigegeben.

2. Der Gesamtspeicher der Datenbank ist aufgrund des freigegebenen Speicherplatzes geringer.