Detaillierte Erläuterung von acht Möglichkeiten zur Optimierung der MySQL-Datenbank (klassische Pflichtlektüre)

Einführung:

Im Internet gibt es zahlreiche Informationen und Methoden zur Datenbankoptimierung. Allerdings ist die Qualität vieler dieser Informationen unterschiedlich, die Zusammenfassungen sind teilweise nicht gründlich und die Inhalte redundant.

Ich habe diesen Artikel gelegentlich gefunden. Es ist eine klassische Zusammenfassung mit viel Verkehr. Also habe ich ihn in meine eigene Zusammenfassungssammlung aufgenommen, hochwertige Artikel gesammelt und meine persönlichen Fähigkeiten verbessert. Ich hoffe, dass er auch bei der zukünftigen Entwicklung für alle hilfreich sein wird.

1. Wählen Sie die am besten geeigneten Feldattribute aus

MySQL kann die Speicherung und den Zugriff auf große Datenmengen gut unterstützen, aber im Allgemeinen gilt: Je kleiner die Tabelle in der Datenbank ist, desto schneller sind die darauf ausgeführten Abfragen. Um eine bessere Leistung zu erzielen, können wir beim Erstellen einer Tabelle die Breite der Felder in der Tabelle so klein wie möglich einstellen.

Wenn Sie beispielsweise beim Definieren des Postleitzahlenfelds CHAR(255) festlegen, wird der Datenbank offensichtlich unnötiger Speicherplatz hinzugefügt. Sogar die Verwendung des Typs VARCHAR ist überflüssig, da CHAR(6) die Aufgabe gut erledigen kann. Ebenso sollten wir, wenn möglich, MEDIUMINT statt BIGIN verwenden, um ganzzahlige Felder zu definieren.

Eine weitere Möglichkeit zur Verbesserung der Effizienz besteht darin, das Feld nach Möglichkeit auf NOTNULL zu setzen, damit die Datenbank bei zukünftigen Abfragen keine NULL-Werte vergleichen muss.

Für einige Textfelder, wie etwa „Provinz“ oder „Geschlecht“, können wir sie als ENUM-Typ definieren. Denn in MySQL wird der ENUM-Typ wie numerische Daten behandelt und numerische Daten werden viel schneller verarbeitet als Textdaten. Auf diese Weise können wir die Performance der Datenbank nochmals verbessern.

2. Verwenden Sie JOIN anstelle von Unterabfragen

MySQL unterstützt SQL-Unterabfragen ab Version 4.1. Bei dieser Technik wird mithilfe einer SELECT-Anweisung ein einspaltiges Abfrageergebnis erstellt und dieses Ergebnis dann als Filterbedingung in einer anderen Abfrage verwendet. Wenn wir beispielsweise Kunden löschen möchten, für die in der Tabelle mit den grundlegenden Kundeninformationen keine Bestellungen vorliegen, können wir mithilfe einer Unterabfrage zunächst die Kunden-IDs aller Kunden abrufen, die Bestellungen aus der Tabelle mit den Verkaufsinformationen aufgegeben haben, und das Ergebnis dann an die Hauptabfrage übergeben, wie unten gezeigt:

LÖSCHEN AUS Kundeninfo
WO Kunden-ID NICHT IN (SELECT CustomerID FROM salesinfo)

Durch die Verwendung von Unterabfragen können viele SQL-Operationen, die logischerweise mehrere Schritte erfordern, gleichzeitig abgeschlossen werden. Gleichzeitig werden Transaktions- oder Tabellensperren vermieden und es ist außerdem einfach zu schreiben. In einigen Fällen können Unterabfragen jedoch durch effizientere Verknüpfungen ersetzt werden. Angenommen, wir möchten alle Benutzer abrufen, die keine Bestelldatensätze haben, können wir dies mit der folgenden Abfrage erledigen:

SELECT * FROM Kundeninfo
WO Kunden-ID NICHT IN (SELECT CustomerID FROM salesinfo)

Wenn Sie zum Abschließen dieser Abfrage einen JOIN verwenden, ist die Geschwindigkeit wesentlich höher. Insbesondere wenn in der Tabelle „salesinfo“ ein Index für CustomerID vorhanden ist, ist die Leistung besser. Die Abfrage lautet wie folgt:

SELECT * FROM Kundeninfo
LEFTJOIN Verkaufsinfo ON Kundeninfo.Kunden-ID = Verkaufsinfo.Kunden-ID
WHERE Verkaufsinfo.KundenIDISNULL

JOIN.. ist effizienter, da MySQL keine temporäre Tabelle im Speicher erstellen muss, um diese logische zweistufige Abfrage abzuschließen.

3. Verwenden Sie UNION anstelle manuell erstellter temporärer Tabellen

MySQL unterstützt ab Version 4.0 Union-Abfragen, die zwei oder mehr Auswahlabfragen, die die Verwendung temporärer Tabellen erfordern, zu einer Abfrage kombinieren können. Wenn die Abfragesitzung des Clients endet, wird die temporäre Tabelle automatisch gelöscht, um sicherzustellen, dass die Datenbank ordentlich und effizient ist. Wenn wir Union zum Erstellen einer Abfrage verwenden, müssen wir nur UNION als Schlüsselwort verwenden, um mehrere Select-Anweisungen zu verbinden. Dabei ist zu beachten, dass die Anzahl der Felder in allen Select-Anweisungen gleich sein muss. Das folgende Beispiel zeigt eine Abfrage mit UNION.

Wählen Sie Name, Telefon aus Client UNION
SELECT Name,Geburtsdatum FROM Autor UNION
Wählen Sie Name, Lieferant aus Produkt

4. Angelegenheiten

Obwohl wir Unterabfragen, Verknüpfungen und Vereinigungen verwenden können, um eine Vielzahl von Abfragen zu erstellen, können nicht alle Datenbankoperationen mit nur einer oder wenigen SQL-Anweisungen abgeschlossen werden. Meistens sind zum Abschließen einer Aufgabe eine Reihe von Anweisungen erforderlich. Wenn in diesem Fall jedoch die Ausführung einer Anweisung in diesem Block fehlschlägt, wird die Funktion des gesamten Blocks unsicher. Stellen Sie sich vor, Sie möchten bestimmte Daten gleichzeitig in zwei verknüpfte Tabellen einfügen. Dabei kann folgende Situation eintreten: Nachdem die erste Tabelle erfolgreich aktualisiert wurde, tritt plötzlich eine unerwartete Situation in der Datenbank auf, die dazu führt, dass der Vorgang in der zweiten Tabelle nicht abgeschlossen werden kann. Dies führt zu unvollständigen Daten oder sogar zu einer Beschädigung der Daten in der Datenbank. Um diese Situation zu vermeiden, sollten Sie Transaktionen verwenden, die über die folgenden Funktionen verfügen: Entweder ist jede Anweisung im Anweisungsblock erfolgreich oder sie schlägt fehl. Mit anderen Worten: Die Konsistenz und Integrität der Daten in der Datenbank kann gewahrt werden. Transaktionen beginnen mit dem Schlüsselwort BEGIN und enden mit dem Schlüsselwort COMMIT. Wenn während dieser Zeit eine SQL-Operation fehlschlägt, kann der Befehl ROLLBACK die Datenbank in den Zustand vor dem Start von BEGIN wiederherstellen.

BEGINNEN;
 INSERTINTOsalesinfoSETCustomerID=14;
 UPDATEinventorySETQuantity=11WHEREitem='Buch';
BEGEHEN;

Eine weitere wichtige Funktion von Transaktionen besteht darin, dass sie, wenn mehrere Benutzer gleichzeitig auf dieselbe Datenquelle zugreifen, den Benutzern durch Sperren der Datenbank eine sichere Zugriffsmethode bieten und so sicherstellen können, dass die Vorgänge des Benutzers nicht durch andere Benutzer gestört werden.

5. Tabelle sperren

Obwohl Transaktionen eine sehr gute Möglichkeit darstellen, die Datenbankintegrität aufrechtzuerhalten, wirkt sich ihre Exklusivität manchmal auf die Datenbankleistung aus, insbesondere in großen Anwendungssystemen. Da die Datenbank während der Ausführung der Transaktion gesperrt ist, können andere Benutzeranforderungen nur vorübergehend warten, bis die Transaktion beendet ist. Wenn ein Datenbanksystem nur von wenigen Benutzern verwendet wird, stellen die Auswirkungen von Transaktionen kein großes Problem dar. Wenn jedoch Tausende von Benutzern gleichzeitig auf ein Datenbanksystem zugreifen, beispielsweise auf eine E-Commerce-Website, kommt es zu erheblichen Reaktionsverzögerungen.

Tatsächlich können wir in manchen Fällen durch Sperren der Tabelle eine bessere Leistung erzielen. Das folgende Beispiel verwendet die Methode zum Sperren der Tabelle, um die Funktion der Transaktion im vorherigen Beispiel abzuschließen.

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='Buch';
...
UPDATEinventorySETQuantity=11WHEREItem='Buch';UNLOCKTABLES

Hier verwenden wir eine Select-Anweisung, um die Anfangsdaten abzurufen, führen einige Berechnungen durch und verwenden eine Update-Anweisung, um die neuen Werte in die Tabelle einzuspielen. Die LOCKTABLE-Anweisung mit dem Schlüsselwort WRITE stellt sicher, dass kein anderer Zugriff auf das Inventar Daten einfügen, aktualisieren oder löschen kann, bis der Befehl UNLOCKTABLES ausgeführt wird.

6. Verwenden Sie Fremdschlüssel

Mit der Sperrtabellenmethode kann die Integrität der Daten gewahrt werden, die Relevanz der Daten kann jedoch nicht garantiert werden. Zu diesem Zeitpunkt können wir Fremdschlüssel verwenden.

Ein Fremdschlüssel kann beispielsweise sicherstellen, dass jeder Verkaufsdatensatz auf einen bestehenden Kunden verweist. Hier kann der Fremdschlüssel die CustomerID in der Tabelle „customerinfo“ der CustomerID in der Tabelle „salesinfo“ zuordnen. Datensätze ohne gültige CustomerID werden nicht aktualisiert oder in „salesinfo“ eingefügt.

CREATETABLE Kundeninfo (Kunden-ID INTNOTNULL, PRIMARYKEY (Kunden-ID)) TYP = INNODB;
CREATETABLE Verkaufsinfo( VerkaufsIDINTNOTNULL, KundenIDINTNOTNULL,
PRIMARYKEY(Kunden-ID,Verkaufs-ID),
FOREIGNKEY(Kunden-ID)REFERENCEScustomerinfo(Kunden-ID)ONDELETECASCADE)TYPE=INNODB;

Beachten Sie im Beispiel den Parameter „ONDELETECASCADE“. Dieser Parameter stellt sicher, dass beim Löschen eines Kundendatensatzes in der Tabelle „Customerinfo“ auch alle mit diesem Kunden in der Tabelle „Salesinfo“ verknüpften Datensätze automatisch gelöscht werden. Wenn Sie Fremdschlüssel in MySQL verwenden möchten, denken Sie beim Erstellen der Tabelle daran, den Tabellentyp als transaktionssicheren InnoDB-Tabellentyp zu definieren. Dieser Typ ist nicht der Standardtyp für MySQL-Tabellen. Die Definitionsmethode besteht darin, TYPE=INNODB in der Anweisung CREATE TABLE hinzuzufügen. Wie im Beispiel gezeigt.

7. Verwenden Sie Indizes

Die Indizierung ist eine gängige Methode zur Verbesserung der Datenbankleistung. Sie ermöglicht es dem Datenbankserver, bestimmte Zeilen viel schneller abzurufen als ohne Index. Dies gilt insbesondere, wenn die Abfrageanweisung Befehle wie MAX(), MIN() und ORDER BY enthält.

Welche Felder sollten also indiziert werden?

Generell sollten Indizes für Felder erstellt werden, die für JOIN- und WHERE-Beurteilungen sowie für die ORDER BY-Sortierung verwendet werden. Versuchen Sie, keinen Index für ein Feld in Ihrer Datenbank zu erstellen, das eine große Anzahl wiederholter Werte enthält. Bei einem Feld vom Typ ENUM ist es sehr wahrscheinlich, dass eine große Anzahl doppelter Werte auftritt.

Beispielsweise das Feld „Provinz“ in der Kundeninformation. Das Erstellen eines Indexes für ein solches Feld ist nicht hilfreich. Im Gegenteil, es kann sogar die Leistung der Datenbank beeinträchtigen. Wir können entsprechende Indizes gleichzeitig mit der Erstellung der Tabelle erstellen oder wir können ALTER TABLE oder CREATE INDEX verwenden, um Indizes später zu erstellen. Darüber hinaus unterstützt MySQL ab Version 3.23.23 die Volltextindizierung und -suche. Der Volltextindex in MySQL ist ein Index vom Typ FULLTEXT, kann jedoch nur für Tabellen vom Typ MyISAM verwendet werden. Bei einer großen Datenbank geht das Laden der Daten in eine Tabelle ohne FULLTEXT-Index und das anschließende Erstellen des Index mit ALTER TABLE oder CREATE INDEX sehr schnell. Wenn Sie jedoch Daten in eine Tabelle laden, die bereits über einen FULLTEXT-Index verfügt, ist der Ausführungsprozess sehr langsam.

8. Optimierte Abfrageanweisungen

In den meisten Fällen kann die Verwendung von Indizes die Abfragegeschwindigkeit verbessern, wenn die SQL-Anweisung jedoch nicht richtig verwendet wird, kann der Index nicht die ihm zustehende Rolle spielen.

Dabei sollten einige Aspekte beachtet werden.

a. Zunächst ist es am besten, Felder desselben Typs zu vergleichen.

Vor MySQL 3.23 war dies sogar eine Anforderung. Beispielsweise können Sie ein indiziertes INT-Feld nicht mit einem BIGINT-Feld vergleichen. Als Sonderfall können Sie jedoch ein Feld vom Typ CHAR und ein Feld vom Typ VARCHAR vergleichen, wenn sie dieselbe Feldgröße haben.

b. Zweitens: Versuchen Sie, keine Funktionen für Operationen an indizierten Feldern zu verwenden.

Wenn Sie beispielsweise die Funktion YEAE() auf ein Feld vom Typ DATE anwenden, funktioniert der Index nicht richtig. Obwohl die folgenden beiden Abfragen dieselben Ergebnisse zurückgeben, ist die letztere viel schneller als die erstere.

c. Drittens verwenden wir bei der Suche in Zeichenfeldern manchmal das Schlüsselwort LIKE und Platzhalter. Obwohl dieser Ansatz einfach ist, geht er auf Kosten der Systemleistung.

Die folgende Abfrage vergleicht beispielsweise jeden Datensatz in der Tabelle.

SELECT * FROM Bücher
WHERE namensähnlich "MySQL%"

Wenn Sie stattdessen die folgende Abfrage verwenden, werden dieselben Ergebnisse zurückgegeben, die Geschwindigkeit ist jedoch wesentlich höher:

SELECT * FROM Bücher
Wobei Name＞="MySQL" und Name＜"MySQM"

Schließlich sollten Sie darauf achten, dass MySQL in Ihren Abfragen keine automatischen Typkonvertierungen durchführt, da der Konvertierungsprozess auch die Indizes unwirksam machen kann.

Oben sind die acht Möglichkeiten zur Optimierung der MySQL-Datenbank, die ich Ihnen vorgestellt habe (klassische Pflichtlektüre). Ich hoffe, sie werden Ihnen hilfreich sein. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und ich werde Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!