Optimieren von Tabellen und Datenbanken in MySQL

1. Wann und warum Tabellen in MySQL optimieren
2. Optimieren von Tabellen in MySQL
3. Verwenden Sie MySQL Shell im Betriebssystem Windows/Ubuntu 20.04, um einzelne oder mehrere Tabellen zu optimieren
4. Verwenden Sie Windows Terminal, um die Tabelle zu optimieren
5. Verwenden Sie Ubuntu 20.04 Terminal, um die Tabelle zu optimieren
6. Optimieren Sie alle Tabellen in einer MySQL-Datenbank
7. Optimieren Sie alle Datenbanken in MySQL

Dieses Tutorial ist eine gründliche Anleitung, wie wir Tabellen und Datenbanken in MySQL optimieren können. Wir werden zwei Betriebssysteme verwenden, Windows und Linux (Ubuntu 20.04).

Es informiert auch über die Bedeutung der Optimierung in MySQL.

Wann und warum Tabellen in MySQL optimieren

Es besteht eine höhere Wahrscheinlichkeit, dass die Datendateien in MySQL fragmentiert sind, wenn unsere Anwendung viele DELETE- und UPDATE-Operationen in einer Datenbank durchführt. Dadurch entsteht ungenutzter Speicherplatz, der sich ebenfalls auf die Leistung auswirken kann.

Es ist dringend erforderlich, dass wir unsere MySQL-Tabellen kontinuierlich defragmentieren und den ungenutzten Speicherplatz zurückfordern. Hier brauchen wir eine Tabellenoptimierung in MySQL, die die Neuordnung der Daten auf einem dedizierten Speicher-Server unterstützt, was letztendlich die Leistung und Geschwindigkeit der Dateneingabe und -ausgabe verbessert.

Woher wissen wir nun, welche Tabelle wir optimieren sollten? Wir sollten die Tabellen optimieren, in denen die Informationen (Daten) ständig aktualisiert werden; Beispielsweise sind Transaktionsdatenbanken die perfekten Kandidaten für die Tabellenoptimierung.

Optimierungsabfragen können jedoch abhängig von der Größe der Tabellen und der Datenbank mehr Zeit in Anspruch nehmen. Daher ist es für ein Transaktionssystem nicht gut, eine Tabelle für viele Stunden zu sperren.

Stattdessen können wir ein paar Tricks in der Engine-Tabelle INNODB ausprobieren. Einige der Tricks sind unten aufgeführt:

1. Manchmal kann die Optimierung des falschen Werts zu einer Fragmentierung mit sekundären Indizes führen, daher ist es wichtig zu analysieren, wie Sie mehr Vorteile aus der Komprimierung eines bestimmten Werts ziehen können. Dies bedeutet, dass die Identifizierung des richtigen Werts für die Optimierung sehr wichtig ist.
2. Eine andere Möglichkeit besteht darin, den Index zu löschen, die Tabellen zu optimieren und die Indizes wieder hinzuzufügen. Dieser Weg ist nur anwendbar, wenn die Tabelle für kurze Zeit ohne Indizes arbeiten kann.

Optimieren von Tabellen in MySQL

Zuerst sollten wir die Tabellen analysieren, die wir optimieren möchten. Wir müssen mit dem folgenden Befehl mit unserer Datenbank verbunden sein.

Beispielcode:

-- Syntax: Use your_database_name;
mysql> USE test;

Sobald Sie mit der gewünschten Datenbank verbunden sind, verwenden Sie die folgende Abfrage, um den Status der Tabelle abzurufen.

Beispielcode:

-- Syntax: SHOW TABLE STATUS LIKE "your_table_name" \G
mysql> SHOW TABLE STATUS LIKE "test_table" \G

AUSGANG:

Wir haben zwei wichtige Eigenschaften, um zu wissen, ob wir diese Tabelle optimieren sollten oder nicht.

1. Die Data_length spricht darüber, wie viel Platz eine Datenbank einnimmt.
2. Das Data_free gibt die allokierten, aber ungenutzten Bytes innerhalb einer Datenbanktabelle an.

Diese Informationen helfen uns bei der Identifizierung, welche Tabelle optimiert werden muss und wie viel Platz wir danach zurückfordern.

Wir können diese beiden Zahlen (Data_length und Data_free) für alle Tabellen in einer bestimmten Datenbank erhalten, indem wir die folgende Abfrage verwenden. Derzeit haben wir nur eine Tabelle namens test_table in der Datenbank test.

Beispielcode:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

AUSGANG:

Die obige Abfrage gibt die Namen der Tabelle, den gesamten Speicherplatz in Bytes und den zugewiesenen ungenutzten Speicherplatz in Bytes aus. Wenn Sie gerne in Megabyte arbeiten, können Sie die folgende Abfrage verwenden, um eine Ausgabe in Megabyte zu erhalten.

Beispielcode:

mysql> SELECT TABLE_NAME,
    -> round(data_length/1024/1024) AS Data_Length_in_MBs,
    -> round(data_free/1024/1024) AS Data_Free_in_MBs
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

Obwohl die angegebene Beispieltabelle nicht stark fragmentiert ist, können wir den Speicherplatz mit dem Befehl OPTIMIZE TABLE zurückgewinnen.

Wir werden lernen, einzelne/mehrere Tabellen oder Datenbanken mit MySQL-Shell und -Terminal zu optimieren.

Verwenden Sie MySQL Shell im Betriebssystem Windows/Ubuntu 20.04, um einzelne oder mehrere Tabellen zu optimieren

Beispielcode:

mysql> OPTIMIZE TABLE test_table;

AUSGANG:

Verwenden Sie die folgende Abfrage, um mehrere Tabellen zu optimieren.

Beispielcode:

mysql> OPTIMIZE TABLE tableName1, tableName2;

Verwenden Sie nun den folgenden Befehl, um zu bestätigen, dass die gewünschte Tabelle optimiert ist.

Beispielcode:

mysql> SELECT TABLE_NAME, data_length, data_free
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> ORDER BY data_free DESC;

AUSGANG:

Dieselben Abfragen funktionieren auf der MySQL-Shell im Linux-Betriebssystem (Ubuntu 20.04).

Verwenden Sie Windows Terminal, um die Tabelle zu optimieren

Beispielcode:

-- Syntax mysqlcheck -o <schema> <table> -u <username> -p <password>
mysqlcheck -o test test_table -u root -p

Sobald wir den oben angegebenen Befehl geschrieben und Enter gedrückt haben, werden wir aufgefordert, das MySQL-Root-Passwort einzugeben. Geben Sie das einfach ein.

Wir müssen uns im Ordner bin befinden, um die Abfrage auszuführen (siehe folgendes Beispiel).

Beispielcode:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlcheck -o test test_table -u root -p
Enter password: *****

Verwenden Sie Ubuntu 20.04 Terminal, um die Tabelle zu optimieren

Wenn wir mit dem Befehl sudo su als superuser angemeldet sind, führen Sie die Abfrage wie unten angegeben aus. Es wird nur nach einem MySQL-Root-Passwort gefragt.

Beispielcode:

mysqlcheck -o test test_table -u root -p

Sind wir nicht als superuser angemeldet, führen wir den mysqlcheck-Befehl aus. Hier werden wir aufgefordert, das root-Passwort des Systems und das root-Passwort von MySQL einzugeben.

Beispielcode:

sudo mysqlcheck -o test test_table -u root -p

Optimieren Sie alle Tabellen in einer MySQL-Datenbank

Optimieren Sie alle Tabellen über die Windows-Befehlszeile:

-- Syntax: mysqlcheck -o your_database_name -u username -pPassword
mysqlcheck -o test -u root -p

Optimieren Sie alle Tabellen mit dem Ubuntu-Terminal:

-- if you are signed in as a superuser
mysqlcheck -o test -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o test -u root -p

Optimieren Sie alle Datenbanken in MySQL

Optimieren Sie alle Datenbanken über die Windows-Befehlszeile:

-- Syntax: mysqlcheck -o --all-databases -u username -pPassword
mysqlcheck -o --all-databases -u root -p

Optimieren Sie alle Datenbanken mit Ubuntu Terminal:

-- if you are signed in as a superuser
mysqlcheck -o --all-databases -u root -p

-- if you are not signed in as a superuser
sudo mysqlcheck -o --all-databases -u root -p