19 MySQL-Optimierungsmethoden im Datenbankmanagement

Nach der MySQL-Datenbankoptimierung kann nicht nur die Redundanz der Datenbank reduziert, sondern auch die Laufgeschwindigkeit der Datenbank geändert werden. Hier sind 19 sehr gute Methoden zur MySQL-Datenbankoptimierung, die wir zu Ihrer Information zusammengestellt haben.

Aussage: Die folgenden Optimierungslösungen basieren alle auf dem „MySQL-Index-BTree-Typ“

1. ERKLÄREN

Bei der Optimierung von MySQL müssen wir EXPLAIN gut nutzen, um den SQL-Ausführungsplan anzuzeigen.

Hier ist ein einfaches Beispiel, bei dem die Daten, auf die wir uns konzentrieren möchten, mit (1,2,3,4,5) markiert werden.

Spalte „Typ“, Verbindungstyp. Eine gute SQL-Anweisung muss mindestens die Bereichsebene erreichen. Vermeiden Sie die Verwendung von Schlüsselspalten und Indexnamen auf allen Ebenen. Wenn kein Index ausgewählt wurde, ist der Wert NULL. Sie können die erzwungene Indexierungsmethode „key_len“-Spalte, „index length“-Spalte und „scan number of rows“-Spalte verwenden. Bei diesem Wert handelt es sich um eine geschätzte zusätzliche Spalte mit detaillierter Beschreibung. Beachten Sie, dass häufige unfreundliche Werte Folgendes umfassen: Using filesort, Using temporary 2. Die in IN in der SQL-Anweisung enthaltenen Werte sollten nicht zu viele sein

MySQL hat entsprechende Optimierungen für IN vorgenommen, das heißt, alle Konstanten in IN werden in einem Array gespeichert und dieses Array wird sortiert. Wenn der Wert jedoch groß ist, ist auch der Verbrauch relativ groß. Ein weiteres Beispiel: select id from t where num in(1,2,3). Verwenden Sie für aufeinanderfolgende Werte „between“ statt „in“ oder stattdessen „join“.

3. Die SELECT-Anweisung muss den Feldnamen angeben

SELECT * verursacht viel unnötigen Verbrauch (CPU, IO, Speicher, Netzwerkbandbreite); erhöht die Möglichkeit der Verwendung von überdeckenden Indizes; wenn sich die Tabellenstruktur ändert, muss auch der vorhergehende Abschnitt aktualisiert werden. Daher ist es erforderlich, den Feldnamen direkt nach der Auswahl hinzuzufügen.

4. Wenn nur ein Datenelement benötigt wird, verwenden Sie Limit 1

Dadurch wird erreicht, dass die Typspalte in EXPLAIN den konstanten Typ erreicht.

5. Wenn das Sortierfeld keinen Index verwendet, sortieren Sie so wenig wie möglich

6. Wenn in der Einschränkung kein Index für andere Felder vorhanden ist, versuchen Sie, so wenig wie möglich zu verwenden

Wenn eines der Felder auf beiden Seiten der oder-Bedingung kein Indexfeld ist und die anderen Bedingungen auch keine Indexfelder sind, verwendet die Abfrage den Index nicht. Oftmals führt die Verwendung von union all oder union (falls nötig) anstelle von „or“ zu besseren Ergebnissen.

7. Versuchen Sie, union all anstelle von union zu verwenden

Der Hauptunterschied zwischen Union und Union All besteht darin, dass bei ersterer die Ergebnismengen zusammengeführt werden müssen, bevor eindeutige Filtervorgänge ausgeführt werden können. Dies beinhaltet das Sortieren, fügt eine große Menge an CPU-Vorgängen hinzu und erhöht den Ressourcenverbrauch sowie die Latenz. Voraussetzung für „Union All“ ist natürlich, dass in den beiden Ergebnismengen keine doppelten Daten vorhanden sind.

8. ORDER BY RAND() nicht verwenden

Wähle die ID aus der `dynamischen` Reihenfolge nach Rand(), Limit 1000;

Die obige SQL-Anweisung kann wie folgt optimiert werden:

Wähle ID aus „dynamisch“ t1, verbinde (wähle Rand() * (wähle max(id) aus „dynamisch“) als NID) t2 auf t1.id > t2.nid, Limit 1000;

9. Unterscheiden Sie zwischen „in“ und „existiert“, nicht „in“ und „nicht existiert“

Wählen Sie * aus Tabelle A, wobei die ID in (Wählen Sie die ID aus Tabelle B) ist.

Die obige SQL-Anweisung entspricht

Wählen Sie * aus Tabelle A, wo vorhanden ist (Wählen Sie * aus Tabelle B, wo Tabelle B.id = Tabelle A.id)

Der Hauptunterschied zwischen „in“ und „exists“ besteht darin, dass die Reihenfolge der treibenden Tabelle geändert wird (was der Schlüssel zur Leistungsänderung ist). Wenn „exists“ ist, ist die äußere Tabelle die treibende Tabelle und wird zuerst aufgerufen. Wenn „IN“ ist, wird die Unterabfrage zuerst ausgeführt. Daher eignet sich IN für Situationen, in denen die äußere Tabelle groß und die innere Tabelle klein ist; EXISTS eignet sich für Situationen, in denen die äußere Tabelle klein und die innere Tabelle groß ist.

In Bezug auf „not in“ und „not exists“ wird empfohlen, „not exists“ zu verwenden. Dies ist nicht nur ein Effizienzproblem, sondern „not in“ kann auch logische Probleme verursachen. Wie schreibt man effizient eine SQL-Anweisung, die „nicht vorhanden“ ersetzt?

Ursprüngliche SQL-Anweisung

select colname … from table A where a.id not in (select b.id from table B)

Effiziente SQL-Anweisungen

select colname … from table A Left join table B on where a.id = b.id wobei b.id null ist

Der abgerufene Ergebnissatz ist in der folgenden Abbildung dargestellt. Die Daten in Tabelle A sind nicht in Tabelle B enthalten.

10. Verwenden Sie angemessene Paging-Methoden, um die Paging-Effizienz zu verbessern

Wählen Sie ID, Name aus Produktlimit 866613, 20

Bei der Verwendung der obigen SQL-Anweisung zum Paginieren stellen manche Benutzer möglicherweise fest, dass die direkte Verwendung von Paginierungsbegrenzungsabfragen mit zunehmender Menge an Tabellendaten immer langsamer wird.

Die Optimierungsmethode ist wie folgt: Sie können die ID der maximalen Zeilenanzahl auf der vorherigen Seite abrufen und dann den Startpunkt der nächsten Seite basierend auf dieser maximalen ID begrenzen. In dieser Spalte ist beispielsweise die größte ID auf der vorherigen Seite 866612. SQL kann wie folgt geschrieben werden:

Wählen Sie ID, Name aus Produkt, wobei ID> 866612 Limit 20

11. Segmentabfrage

Auf manchen Benutzerauswahlseiten wählen manche Benutzer möglicherweise einen zu großen Zeitraum aus, was zu einer langsamen Abfrage führt. Der Hauptgrund sind zu viele Scanzeilen. Derzeit können Sie mit dem Programm segmentweise Abfragen durchführen, in einer Schleife durchlaufen und die Ergebnisse zur Anzeige zusammenführen.

Wie in der folgenden SQL-Anweisung gezeigt, kann eine segmentierte Abfrage verwendet werden, wenn die Anzahl der gescannten Zeilen mehr als eine Million beträgt.

12. Vermeiden Sie die Nullwertbeurteilung von Feldern in der Where-Klausel

Die Beurteilung „Null“ führt dazu, dass die Engine die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt.

13. Es wird nicht empfohlen, % Präfix Fuzzy-Abfrage zu verwenden

Beispielsweise LIKE "%name" oder LIKE "%name%". Diese Abfrage führt zu einem Indexierungsfehler und einem vollständigen Tabellenscan. Sie können jedoch LIKE "name%" verwenden.

Wie fragen Sie also %name% ab?

Wie in der folgenden Abbildung gezeigt, wird dem geheimen Feld zwar ein Index hinzugefügt, dieser wird jedoch nicht im Erläuterungsergebnis verwendet.

Wie lässt sich dieses Problem also lösen? Die Antwort lautet: Verwenden Sie einen Volltextindex

In unseren Abfragen verwenden wir häufig select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%';. Für solche Anweisungen können gewöhnliche Indizes die Abfrageanforderungen nicht erfüllen. Glücklicherweise gibt es in MySQL Volltextindizes, die uns helfen.

Die SQL-Syntax zum Erstellen eines Volltextindexes lautet:

ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`Benutzername`);

Die SQL-Anweisung zur Verwendung des Volltextindex lautet:

Wählen Sie ID, Fnum, FDST aus dynamic_201606, wobei Match (Benutzername) mit (,zhangsan‘ im Booleschen Modus) übereinstimmt.

Hinweis: Bevor Sie einen Volltextindex erstellen, wenden Sie sich an Ihren DBA, um zu bestätigen, ob dieser erstellt werden kann. Gleichzeitig ist es wichtig zu beachten, dass sich das Schreiben von Abfrageanweisungen vom Schreiben gewöhnlicher Indizes unterscheidet.

14. Vermeiden Sie Ausdrucksoperationen auf Feldern in der Where-Klausel

Zum Beispiel

Wählen Sie Benutzer-ID, Benutzerprojekt aus der Benutzerbasis, wobei Alter*2=36;

Im obigen Beispiel werden arithmetische Operationen an den Feldern durchgeführt, was dazu führt, dass die Engine die Verwendung des Indexes aufgibt. Es wird empfohlen, ihn zu ändern in

Wählen Sie Benutzer-ID, Benutzerprojekt aus der Benutzerbasis, wobei Alter = 36/2;

15. Vermeiden Sie implizite Typkonvertierung

Eine Typkonvertierung tritt auf, wenn der Spaltentyp in der Where-Klausel nicht mit dem übergebenen Parametertyp übereinstimmt. Es wird empfohlen, zuerst den Parametertyp in Where zu bestimmen.

16. Bei gemeinsamen Indizes muss die Regel des am weitesten links stehenden Präfixes befolgt werden

Wenn der Index beispielsweise die Felder „ID“, „Name“ und „Schule“ enthält, können Sie das ID-Feld direkt oder in der Reihenfolge „ID“ und „Name“ verwenden. „Name“ und „Schule“ können diesen Index jedoch nicht verwenden. Deshalb muss beim Erstellen eines gemeinsamen Index auf die Reihenfolge der Indexfelder geachtet werden und die häufig verwendeten Abfragefelder müssen an den Anfang gestellt werden.

17. Bei Bedarf können Sie Force Index verwenden, um die Abfrage zu zwingen, einen bestimmten Index zu durchlaufen

Manchmal verwendet der MySQL-Optimierer den Index, den er zum Abrufen von SQL-Anweisungen für geeignet hält, der verwendete Index ist jedoch möglicherweise nicht der gewünschte. Zu diesem Zeitpunkt können wir den Optimierer mithilfe von „Force Index“ zwingen, den von uns festgelegten Index zu verwenden.

18. Achten Sie auf Bereichsabfrageanweisungen

Wenn bei einem gemeinsamen Index eine Bereichsabfrage wie z. B. zwischen, >, < usw. vorliegt, werden die nachfolgenden Indexfelder ungültig.

19. Über JOIN-Optimierung

LEFT JOIN Tabelle A ist die treibende Tabelle INNER JOIN MySQL findet automatisch die Tabelle mit weniger Daten als treibende Tabelle RIGHT JOIN Tabelle B ist die treibende Tabelle

Hinweis: In MySQL gibt es keinen vollständigen Join. Sie können es folgendermaßen lösen

wähle * von A links, schließe dich B an B.name = A.name 
wobei B.name null ist
 Vereinigung alle
wähle * aus B;

Versuchen Sie, Inner Join zu verwenden und Left Join zu vermeiden

An einer gemeinsamen Abfrage sind mindestens zwei Tabellen beteiligt, die in der Regel unterschiedliche Größen aufweisen. Wenn die Verbindungsmethode Inner Join ist, wählt MySQL automatisch die kleine Tabelle als treibende Tabelle aus, wenn keine anderen Filterbedingungen vorliegen. Beim Left Join wird jedoch die treibende Tabelle nach dem Prinzip ausgewählt, dass die linke Seite die rechte Seite treibt, d. h. der Tabellenname auf der linken Seite des Left Join ist die treibende Tabelle.

Richtige Verwendung von Indizes

Als Einschränkungsfeld wird das Indexfeld der angetriebenen Tabelle verwendet.

Verwenden einer kleinen Tabelle zum Ansteuern einer großen Tabelle

Aus dem schematischen Diagramm lässt sich intuitiv erkennen, dass sich durch eine Reduzierung der Treibertabelle die Anzahl der Schleifen in der verschachtelten Schleife verringern lässt, um die Gesamt-E/A-Menge und die Anzahl der CPU-Operationen zu reduzieren.

Verwenden von STRAIGHT_JOIN

Inner Join wird von MySQL gesteuert, in einigen Sonderfällen muss jedoch eine andere Tabelle als steuernde Tabelle ausgewählt werden, z. B. „Group by“, „Sort by“, „Using Filesort“, „Using Temporary“. STRAIGHT_JOIN wird verwendet, um die Verbindungsreihenfolge durchzusetzen. Der Tabellenname auf der linken Seite von STRAIGHT_JOIN ist die treibende Tabelle, und die Tabelle auf der rechten Seite ist die angetriebene Tabelle. Voraussetzung für die Verwendung von STRAIGHT_JOIN ist, dass es sich bei der Abfrage um einen Inner Join handelt. Es wird nicht empfohlen, STRAIGHT_JOIN für andere Links zu verwenden, da sonst die Abfrageergebnisse ungenau sein können.

Mit dieser Methode kann die Zeit manchmal um das Dreifache verkürzt werden.

Hier sind nur die oben genannten Optimierungslösungen aufgeführt. Natürlich gibt es auch andere Optimierungsmethoden. Sie können sie erkunden und ausprobieren. Vielen Dank für Ihre Aufmerksamkeit. .