Detaillierte Einführung in die MySQL-Abfrageoptimierung

1. Einleitung

Das Wichtigste für eine gute Webanwendung ist eine hervorragende Zugriffsleistung. Die Datenbank MySQL ist ein integraler Bestandteil der Webanwendung und ein wichtiger Teil, der deren Leistung bestimmt. Daher ist es von entscheidender Bedeutung, die Leistung von MySQL zu verbessern.

Die Verbesserung der MySQL-Leistung kann in drei Teile unterteilt werden, darunter Hardware, Netzwerk und Software. Unter anderem hängen Hardware und Netzwerk von den finanziellen Ressourcen des Unternehmens ab und erfordern viel Geld, daher werde ich hier nicht näher darauf eingehen. Die Software ist in viele Typen unterteilt. Hier erreichen wir eine Leistungsverbesserung durch MySQL-Abfrageoptimierung.

Kürzlich habe ich einige Bücher über Abfrageoptimierung gelesen und auch einige Artikel von Senioren online gelesen.

Im Folgenden finden Sie einige Zusammenfassungen zur Abfrageoptimierung, die ich zusammengestellt und daraus gelernt habe:

2. SQL-Anweisungen abfangen

1. Umfassendes Abfrageprotokoll

2 . Langsames Abfrageprotokoll

3. Binärprotokoll

4. Prozessliste

VOLLSTÄNDIGE PROZESSLISTE ANZEIGEN;

. . .

3. Grundlegende Analysebefehle zur Abfrageoptimierung

1. EXPLAIN {PARTITIONS|EXTENDED}

2. SHOW CREATE TABLE tab;

3. SHOW INDEXS FROM tab;

　4. SHOW TABLE STATUS LIKE 'tab';

　5. SHOW [GLOBAL|SESSION] STATUS LIKE '';

　6. SHOW VARIABLES

. . . .

ps: Ich persönlich habe das Gefühl, dass sie alle ernährungsphysiologisch frei von jeglichen Nährstoffen sind. Hier ist das Echte.

4. Mehrere Anweisungen zur Abfrageoptimierung

1. Versuchen Sie, Volltextscans zu vermeiden, fügen Sie Indizes zu entsprechenden Feldern hinzu und verwenden Sie Indizes zum Abfragen

2. Löschen Sie nicht verwendete oder doppelte Indizes

3. Umschreiben von Abfragen, äquivalente Konvertierung (Prädikat, Unterabfrage, Join-Abfrage)

4. Inhalte löschen und unnötige Anweisungen wiederholen, Anweisungen rationalisieren

5. Integrieren wiederholt ausgeführte Anweisungen

6. Abfrageergebnisse zwischenspeichern

5. Indexoptimierung

5.1. Indexvorteile:

1. Datenintegrität aufrechterhalten

　 2. Verbessern Sie die Leistung von Datenabfragen

3. Verbessern Sie Tabellenverbindungsvorgänge (jion)

4. Sortieren Sie Abfrageergebnisse. Wenn kein Index vorhanden ist, wird der interne Dateisortierungsalgorithmus zum Sortieren verwendet, was effizienter ist

5. Vereinfachen Sie den Aggregationsdatenvorgang

5.2. Nachteile des Index

1. Der Index muss einen bestimmten Speicherplatz belegen

2. Das Einfügen, Aktualisieren und Löschen von Daten wird durch den Index beeinträchtigt und die Leistung wird verringert. Da sich die Daten ändern, muss auch der Index aktualisiert werden

3. Mehrere Indizes, wenn der Optimierer Zeit benötigt, ist die beste Wahl

5.3 Indexauswahl

1 . Wenn die Datenmenge groß ist, verwenden Sie

2. Wenn die Daten stark repetitiv sind, verwenden Sie nicht

3. Wenn die Abfrage mehr als 20 % der Daten abruft, Volltext Das Scannen wird ohne Indizierung verwendet

5.4. Detaillierte Untersuchung des Index

Datenabfrage:

InnoDB und MyISAM in MySQL sind Indizes vom Typ B-Tree

B-Tree umfasst: PRIMARY KEY, UNIQUE, INDEX und FULLTEXT

Der Index vom Typ B-Tree wird nicht unterstützt (d. h. wenn das Feld die folgenden Symbole verwendet, wird der Index nicht verwendet):

　>, <, >=, <=, BETWEEN, !=, < >,like '%**'

　【Lassen Sie mich zuerst den Deckungsindex vorstellen】

　Lassen Sie es mich auf eine Weise vorstellen, die ich verstehe. Es gibt nicht wirklich abdeckende Indizes wie Primärschlüsselindizes und eindeutige Indizes. Es handelt sich lediglich um eine Definition bestimmter spezifischer Szenarien für die Indexanwendung [anderes Verständnis: Die abgefragte Spalte ist eine Indexspalte, daher wird die Spalte vom Index abgedeckt]. Es kann herkömmliche Einschränkungen durchbrechen, die oben genannten Operatoren verwenden und dennoch Indizes für Abfragen verwenden.

Da es sich bei der abgefragten Spalte um eine Indexspalte handelt, muss die Zeile nicht gelesen werden, sondern nur die Spaltenfelddaten. [Wenn Sie beispielsweise ein Buch lesen und einen bestimmten Inhalt suchen müssen und dieser Inhalt zufällig im Inhaltsverzeichnis erscheint, müssen Sie nicht Seite für Seite umblättern, sondern einfach die Seite im Inhaltsverzeichnis suchen Inhalte und Suche]

So aktivieren Sie Was ist mit der Abdeckung von Indizes? Was ist ein bestimmtes Szenario?

Das Indexfeld erscheint nur in der Auswahl.

Der zusammengesetzte Index kann auch andere spezielle Szenarien haben. Beispielsweise muss bei einem dreispaltigen zusammengesetzten Index die Spalte ganz links des zusammengesetzten Index nur einmal in „Auswählen“, „Wo“, „Gruppieren nach“ und „Ordnen nach“ erscheinen, um die Verwendung des abdeckenden Indexes zu aktivieren.

Ansicht:

Extra in EXPLAIN zeigt Using index an, was angibt, dass diese Anweisung einen abdeckenden Index verwendet.

Fazit:

Es wird nicht empfohlen, beim Abfragen „select*from“ zu verwenden. Sie sollten die erforderlichen Felder schreiben und entsprechende Indizes hinzufügen, um die Abfrageleistung zu verbessern.

Tatsächliche Messergebnisse für die oben genannten Operatoren:

1. In der Form von select*from kann der Primärschlüssel in wo zum Töten verwendet werden [außer wie] (verwenden Sie den Primärschlüssel für query); Index funktioniert überhaupt nicht.

2. Testen Sie in Form von Select Field A From Tab, wobei Field A „Oben Operator“ ist, das Ergebnis kann weiterhin über den Index abgefragt werden. [Deckungsindex verwenden]

　 Andere Indexoptimierungsmethoden:

1. Verwenden Sie Indexschlüsselwörter als Bedingungen für die Verbindung

2. Verwenden Sie zusammengesetzte Indizes

3. Die Indexzusammenführung oder und wird beinhalten Die beteiligten Felder werden zu einem zusammengesetzten Index zusammengeführt

4. Index zu den beteiligten Feldern in „wo“ hinzufügen und nach gruppieren

6. Unterabfrageoptimierung

In from ist es so eine nicht korrelierte Unterabfrage. Unterabfragen können auf die übergeordnete Ebene hochgezogen werden. Berücksichtigen Sie bei Join-Abfragen mit mehreren Tabellen die Join-Kosten, bevor Sie eine Auswahl treffen.

Der Abfrageoptimierer verwendet im Allgemeinen eine verschachtelte Ausführung für Unterabfragen, dh die Unterabfrage wird einmal für jede Zeile in der übergeordneten Abfrage ausgeführt, sodass die Unterabfrage viele Male ausgeführt wird. Diese Ausführungsmethode ist sehr ineffizient.

Vorteile der Konvertierung von Unterabfragen in Join-Abfragen:

1. Die Unterabfrage muss nicht viele Male ausgeführt werden

2. Der Optimierer kann basierend auf verschiedenen Methoden und Verbindungsreihenfolgen auswählen zu den Informationen

3. Die Verbindungsbedingungen und Filterbedingungen der Unterabfrage werden zu Filterbedingungen der übergeordneten Abfrage, um die Effizienz zu verbessern.

Optimierung:

Unterabfragen zusammenführen Wenn mehrere Unterabfragen vorhanden sind, versuchen Sie, diese so weit wie möglich zusammenzuführen.

Unterabfrageerweiterung, das heißt, Pull-up wird zu einer Abfrage mit mehreren Tabellen (äquivalente Änderungen sind jederzeit garantiert)

Hinweis:

Unterabfrageerweiterung kann nur einfach erweitert werden Wenn die Unterabfrage Aggregatfunktionen, GROUP BY und DISTINCT enthält, kann sie nicht abgerufen werden.

Wählen Sie * aus t1 (select*from tab where id>10) als t2, wobei t1.age>10 und t2.age<25;

select*from t1,tab as t2 where t1.age>10 und t2.age<25 und t2.id>10;

Spezifische Schritte:

1. Von und nach zusammenführen, die entsprechenden Parameter ändern

2, verschmelze where mit where, verwende und zum Verbinden

3. Ändere das entsprechende Prädikat (in wird in = geändert)

7. Schreibe das entsprechende Prädikat neu:

1 . ZWISCHEN UND Schreiben Sie es um in >=, <= und so weiter. Tatsächliche Messung: 100.000 Daten, Zeit vor und nach dem Umschreiben, 1,45 s, 0,06 s

　2. In konvertiert mehrere oder. Wenn das Feld ein Index ist, können beide den Index verwenden oder es ist effizienter als in

3. Namen wie „abc%“ werden in name>='abc' und name<'abd';< umgeschrieben 🎜 >

Hinweis: Beim Datentest auf Millionenebene ist like schneller als die letztgenannte Abfrage; nach dem Hinzufügen eines Index zum Feld ist die letztgenannte Abfrage etwas schneller, aber es gibt keinen großen Unterschied , da bei der Abfrage auf den Index beide Methoden verwendet werden.

. . . .

8. Bedingte Vereinfachung und Optimierung

1. Kombinieren Sie Where, Have (wenn es keine Groupby- und Aggregatfunktionen gibt) und Join-On-Bedingungen so weit wie möglich

2. Löschen Sie unnötige Klammern, reduzieren Sie die Or- und-Baum-Syntaxebenen und reduzieren Sie den CPU-Verbrauch

3. Konstante Übertragung. a=b und b=2 wird in a=2 und b=2 umgewandelt. Versuchen Sie, keine Variablen a=b oder a=@var zu verwenden

4. Beseitigen Sie nutzlose SQL-Bedingungen

5. Versuchen Sie, keine Ausdrücke auf der rechten Seite des Gleichheitszeichens zu berechnen Felder verwenden, in denen Ausdrücke berechnet und Funktionen verwendet werden

6. Identitätstransformation und Ungleichheitstransformation. Beispiel: Das Testen von Millionen von Daten aus a>b und b>10 wird zu a>b und a>10 und b>10 mit erheblicher Optimierung

9. Externe Verbindungsoptimierung

Das heißt, externe Verbindungen zu konvertieren Verbindungen Für innere Verknüpfungen

Vorteile:

1. Der Optimierungsprozessor verarbeitet äußere Verknüpfungen mit mehr Schritten als innere Verknüpfungen und ist zeitaufwändig

2. Nach den äußeren Verknüpfungen eliminiert, wählt der Optimierer mehrere Tabellen aus. Es gibt mehr Auswahlmöglichkeiten für die Verbindungssequenz. Sie können die beste auswählen

3. Sie können die Tabelle mit den strengsten Filterbedingungen als Erscheinungsbild verwenden (die Vorderseite der Verbindungssequenz ist). die äußere Schleifenschicht des mehrschichtigen Schleifenkörpers),

Es kann unnötigen E/A-Overhead reduzieren und die Algorithmusausführung beschleunigen.

　Der Unterschied zwischen on a.id=b.id und where a.id=b.id, on bedeutet, dass die Tabelle verbunden wird und wo der Datenvergleich durchgeführt wird

Hinweis: Die Voraussetzung muss sein, dass das Ergebnis NULL-Abstinenz ist (d. h. die Bedingung ist auf keine NULL-Datenzeilen beschränkt, semantisch handelt es sich um einen Inner Join)

Optimierungsprinzipien:

Abfrage und Verbindung optimieren Eliminierung, äquivalente Konvertierung, Entfernung redundanter Tabellenobjektverbindungen

Beispiel: Der Primärschlüssel/eindeutige Schlüssel wird als Verbindungsbedingung verwendet, und die Zwischentabellenspalte wird nur als äquivalente Bedingung verwendet, und die Zwischentabellenverbindung kann entfernt werden

10. Andere Abfrageoptimierung

1. Folgendes wird bewirkt, dass die Indexabfrage abgebrochen wird und die Volltextsuche erfolgt

1.1. Verwenden Sie != oder < >-Operator in der Where-Klausel. Hinweis: Primärschlüsselunterstützung. Nicht-Primärschlüssel werden nicht unterstützt.

1.2 Vermeiden Sie die Verwendung oder Verwendung, daher sollte die spezifische Situation von Fall zu Fall analysiert werden.

　Ähnliche Optimierung:

　　select * from tab name='aa' or name='bb';

　　=>

　　select * from tab name='aa'

　　union all

　　select * from tab name='bb';

　 Tatsächliche Messung:

　1. Einhunderttausend Daten Test: Ohne Index ist die obige Abfrage doppelt so schnell wie die folgende.

2. Wenn im 300.000-Datentest aa und bb separat indiziert werden, ist die folgende Abfragegeschwindigkeit etwas schneller als or.

　1.3. Vermeiden Sie die Verwendung von Nicht-Indizes. Das Primärschlüsselfeld kann nicht in

verwendet werden 🎜>　1.5. like darf kein Prozentzeichen wie „%.com“ vorangestellt werden

Lösung:

1. Wenn Sie % voranstellen müssen und die Datenlänge nicht groß ist, wie z URL, Sie können die Daten umdrehen und speichern. Geben Sie die Datenbank ein und überprüfen Sie sie erneut. LIKE REVERSE'%.com';

2. Covering-Index verwenden

1.6 Wenn es sich um einen zusammengesetzten Index handelt, wird der Feldname mit dem Präfix ganz links verwendet Index sollte verwendet werden

2. Ersetzen Sie „existiert in in“

wählen Sie Nummer aus a aus, wobei Nummer in (wählen Sie Nummer aus b)

wählen Sie Nummer aus einem Feld aus, in dem vorhanden ist (wählen Sie 1 von b, wobei num =a.num)

Bei einer Million Daten dauert es 6,65 Sekunden und 4,18 Sekunden, um 59417 Daten zu filtern. Es wurden keine weiteren Optimierungen vorgenommen, sondern lediglich „exists“ durch „in“ ersetzt.

3. Die Felddefinition ist eine Zeichenfolge. Bei der Abfrage werden keine Anführungszeichen verwendet und es wird kein Volltextscan durchgeführt.

[Das Folgende ist ein Auszug aus Luantanqins Blog-Beitrag http://www.cnblogs.com/lingiu/p/3414134.html Ich habe den entsprechenden Test nicht durchgeführt]

4. Versuchen Sie, es so oft wie möglich zu verwenden. Tabellenvariablen ersetzen temporäre Tabellen

5. Vermeiden Sie das häufige Erstellen und Löschen temporärer Tabellen, um den Verbrauch von Systemtabellenressourcen zu reduzieren

6. Wenn eine temporäre Tabelle verwendet wird , fügen Sie es unbedingt am Ende der gespeicherten Prozedur hinzu. Löschen Sie explizit alle temporären Tabellen, kürzen Sie zuerst die Tabelle und löschen Sie dann die Tabelle. Dadurch kann eine langfristige Sperrung von Systemtabellen vermieden werden

7. Versuchen Sie, die Verwendung zu vermeiden Cursor, da Cursor weniger effizient sind. Wenn die Daten 10.000 Zeilen überschreiten, sollten Sie darüber nachdenken,

neu zu schreiben. Wenn das Datenvolumen zu groß ist, sollten Sie überlegen, ob die entsprechende Anforderungen sind angemessen.

9. Versuchen Sie, große Transaktionsvorgänge zu vermeiden und die Systemparallelität zu verbessern.

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in die MySQL-Abfrageoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!