Detaillierte Erläuterung der MySQL-Abfrageoptimierung

1. Was sind die Ideen und Prinzipien der Optimierung

1. Optimieren Sie die Abfragen, die optimiert werden müssen
2. Lokalisieren Sie den Leistungsengpass des Optimierungsobjekts
3. Klären Sie das Ziel der Optimierung
4. Beginnen Sie mit Erklären Sie
5. Verwenden Sie Profile häufiger
6. Verwenden Sie immer kleine Ergebnismengen, um große Ergebnismengen zu erzielen
7. Vervollständigen Sie die Sortierung im Index so weit wie möglich
8. Entfernen Sie nur die Felder (Spalten), die Sie benötigen
9. Verwenden Sie nur die effektivsten Filterbedingungen
10. Vermeiden Sie komplexe Verknüpfungen so weit wie möglich

Verwandte kostenlose Lernempfehlungen: MySQL-Video-Tutorial

1. Optimieren Sie Abfragen, die optimiert werden müssen

Hohe Parallelität und geringer Verbrauch ( relativ) Abfragen wirken sich viel größer auf das gesamte System aus als Abfragen mit geringer Parallelität und hohem Verbrauch.

2. Lokalisieren Sie den Leistungsengpass des Optimierungsobjekts

Wenn wir eine Abfrage erhalten, die optimiert werden muss, müssen wir zunächst feststellen, ob der Engpass der Abfrage E/A oder CPU ist. Ist es der Datenbankzugriff, der mehr verbraucht, oder sind es Datenoperationen (z. B. Gruppieren und Sortieren), die mehr verbrauchen?

3. Klare Optimierungsziele

Verstehen Sie den aktuellen Gesamtstatus der Datenbank. Sie können den maximalen Druck kennen, dem die Datenbank standhalten kann. Das heißt, wir kennen die pessimistischste Situation.
Um die zugehörigen Datenbankobjektinformationen zu erfassen Zur Abfrage können wir wissen, wie viele Ressourcen unter den besten und schlechtesten Bedingungen verbraucht werden.
Um den Status der Abfrage im Anwendungssystem zu ermitteln, können wir den Anteil der Systemressourcen analysieren, die die Abfrage belegen kann Wissen Sie auch, wie effizient die Abfrage auf das Kundenerlebnis ist, wie groß die Auswirkung ist.

4. Beginnen Sie mit Explain

Explain kann Ihnen sagen, welche Art von Ausführungsplan diese Abfrage in der Datenbank implementiert ist. Zunächst müssen wir ein Ziel haben, indem wir ständig anpassen und versuchen und dann mithilfe von Explain überprüfen, ob die Ergebnisse unseren Anforderungen entsprechen, um die erwarteten Ergebnisse zu erzielen.

5. Verwenden Sie immer kleine Ergebnismengen, um große Ergebnismengen zu erzielen. Viele Leute sagen bei der Optimierung von SQL gerne: „Kleine Tabellen führen zu großen Tabellen.“ Da die von der großen Tabelle nach dem Filtern durch die Where-Bedingung zurückgegebene Ergebnismenge nicht unbedingt größer ist als die von der kleinen Tabelle zurückgegebene Ergebnismenge, wird der gegenteilige Leistungseffekt erzielt, wenn zu diesem Zeitpunkt die große Tabelle zum Ansteuern der kleinen Tabelle verwendet wird erhalten werden. Dieses Ergebnis ist auch sehr leicht zu verstehen. In MySQL gibt es nur eine Join-Methode, nämlich Nested Loop. Das heißt, MySQLs Join wird durch verschachtelte Schleifen implementiert. Je größer die gesteuerte Ergebnismenge ist, desto mehr Schleifen sind erforderlich, und die Anzahl der Zugriffe auf die gesteuerte Tabelle wird natürlich höher sein. Bei jedem Zugriff auf die gesteuerte Tabelle ist die Anzahl der Schleifen höher, auch wenn die erforderliche logische E/A sehr gering ist Natürlich kann die Gesamtmenge nicht sehr gering sein, und jeder Zyklus verbraucht zwangsläufig die CPU, sodass auch die Menge an CPU-Berechnungen zunimmt. Wenn wir also nur die Größe der Tabelle als Grundlage für die Beurteilung der Treibertabelle verwenden und die Ergebnismenge, die nach dem Filtern der kleinen Tabelle übrig bleibt, viel größer ist als die der großen Tabelle, führt dies zu mehr Schleifen in der erforderlichen Verschachtelung Im Gegenteil, die Anzahl der erforderlichen Zyklen wird geringer sein und auch die Gesamtmenge an E/A- und CPU-Operationen wird geringer sein. Darüber hinaus ist es auch für nicht-Nested-Loop-Join-Algorithmen wie Hash Join in Oracle immer noch die optimale Wahl, wenn eine kleine Ergebnismenge eine große Ergebnismenge steuern soll.

Daher lautet das grundlegendste Prinzip bei der Optimierung von Join-Abfragen: „Kleine Ergebnismengen führen zu großen Ergebnismengen.“ Durch dieses Prinzip kann die Anzahl der Schleifen in verschachtelten Schleifen reduziert werden, wodurch die Gesamtmenge an E/A und die Anzahl der CPUs reduziert werden Operationen. Vervollständigen Sie die Sortierung im Index so weit wie möglich

6. Nehmen Sie nur die Felder (Spalten) heraus, die Sie benötigen

Für jede Abfrage müssen die zurückgegebenen Daten über Netzwerkpakete an den Client übertragen werden. Je mehr Spalten Sie verwenden heraus, Die zu übertragende Datenmenge wird natürlich größer sein, was unabhängig von der Netzwerkbandbreite oder dem Netzwerkübertragungspuffer eine Verschwendung darstellt.

7. Verwenden Sie nur die effektivsten Filterbedingungen.

Ein Benutzer in der Tabelle hat beispielsweise Felder wie „id“ und „nike_name“. Die folgenden Indizes sind zwei Abfrageanweisungen: „Zwei Abfragen“. ist derselbe, aber der von der ersten Anweisung verwendete Index nimmt viel mehr Platz ein als der der zweiten Anweisung. Der höhere Platzbedarf bedeutet auch, dass mehr Daten gelesen werden müssen. Das heißt, die Abfrageanweisung von 2 ist die optimale Abfrage.

8. Vermeiden Sie komplexe Join-Abfragen

Je mehr Tabellen unsere Abfrage umfasst, desto mehr Ressourcen müssen wir sperren. Mit anderen Worten: Je komplexer die Join-Anweisung ist, desto mehr Ressourcen muss sie sperren und desto mehr andere Threads werden blockiert. Im Gegenteil: Wenn wir eine komplexere Abfrageanweisung in mehrere einfachere Abfrageanweisungen aufteilen und diese Schritt für Schritt ausführen, werden jedes Mal weniger Ressourcen gesperrt und weniger andere Threads blockiert.
Viele Leute haben möglicherweise Fragen: Werden wir nach der Aufteilung der komplexen Join-Anweisung in mehrere einfache Abfrageanweisungen nicht mehr Netzwerkinteraktionen haben? Der Gesamtverbrauch in Bezug auf die Netzwerkverzögerung wird größer sein. Würde es nicht länger dauern, die gesamte Abfrage abzuschließen? Ja, das ist möglich, aber sicher ist es nicht. Wir können es noch einmal analysieren. Wenn eine komplexe Abfrageanweisung ausgeführt wird, müssen mehr Ressourcen gesperrt werden, und die Wahrscheinlichkeit, von anderen blockiert zu werden, ist größer. Wenn es sich um eine einfache Abfrage handelt, müssen weniger Ressourcen gesperrt werden. Auch die Wahrscheinlichkeit, blockiert zu werden, wird deutlich geringer sein. Daher können komplexere Verbindungsabfragen vor der Ausführung blockiert werden und mehr Zeit verschwenden. Darüber hinaus bedient unsere Datenbank nicht nur diese Anfrage, sondern auch viele, viele andere Anfragen. In einem System mit hoher Parallelität lohnt es sich sehr, die kurze Antwortzeit einer einzelnen Anfrage zu opfern, um die Gesamtverarbeitungsfähigkeit zu verbessern. Die Optimierung selbst ist eine Kunst des Gleichgewichts und der Kompromisse. Nur wenn man die Kompromisse kennt und das Ganze ausbalanciert, kann das System besser werden.

2. Verwenden Sie „Explain“ und „Profiling“. Die im Plan abgefragte Seriennummer

Select_type

Abfragetyp:

ABHÄNGIGE UNTERABFRAGE: Das erste SELECT in der inneren Ebene der Unterabfrage, das von der externen Abfrageergebnismenge abhängt;

ABHÄNGIGE UNION: Alle nachfolgenden SELECTs in der UNION in der Unterabfrage, beginnend mit der zweiten SELECT SELECT, hängt auch von der externen Abfrageergebnismenge ab; PRIMARY: die äußerste Abfrage in der Unterabfrage, nicht die Primärschlüsselabfrage; SUBQUERY: die erste SELECT der inneren Abfrage der Unterabfrage, das Ergebnis hängt nicht von der externen ab Ergebnismenge; UNCACHEABLE SUBQUERY: Eine Unterabfrage, deren Ergebnismenge nicht zwischengespeichert werden kann; UNION RESULT: Die zusammengeführten Ergebnisse in UNION Table Zugriff Der Tabellenname in der Datenbank Zugriffsmethode: ALL: Vollständiger Tabellenscan eq_ref: Es gibt höchstens eine Übereinstimmung. Auf das Ergebnis wird im Allgemeinen über den Primärschlüssel oder den eindeutigen Index zugegriffen. Index: vollständiger Indexscan. Bereich: Indexbereichsscan. Ref: Referenzabfrage des gesteuerten Tabellenindex in der Jion-Anweisung System: Systemtabelle, es gibt nur eine Datenzeile in der Tabelle Zusätzliche Informationen

2、Profiling使用

该工具可以获取一条Query在整个执行过程中多种资源消耗情况，如CPU,IO,IPC,SWAP等，以及发生PAGE FAULTS, CONTEXT SWITCHE等等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。

1、开启profiling参数 1-开启，0-关闭

#开启profiling参数 1-开启，0-关闭set profiling=1;SHOW VARIABLES LIKE '%profiling%';

2、然后执行一条Query

3、获取系统保存的profiling信息

show PROFILES;

4、通过QUERY_ID获取profile的详细信息（下面以获取CPU和IO为例）

show profile cpu, block io for QUERY 7;

三、合理利用索引

1、什么是索引

 简单来说，在关系型数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构。就像书的目录，可以根据目录中的页码快速找到需要的内容。
 在MySQL中主要有四种类型索引，分别是：B-Tree索引，Hash索引，FullText索引，R-Tree索引，下面主要说一下我们常用的B-Tree索引，其他索引可以自行查找资料。

2、索引的数据结构

 一般来说，MySQL中的B-Tree索引的物理文件大多数都是以平衡树的结构来存储的，也就是所有实际需要存储的数据都存储于树的叶子节点，二到任何一个叶子节点的最短路径的长度都是完全相同的。MySQL中的存储引擎也会稍作改造，比如Innodb存储引擎的B-Tree索引实际上使用的存储结构是B+Tree,在每个叶子节点存储了索引键相关信息之外，还存储了指向相邻的叶子节点的指针信息，这是为了加快检索多个相邻的叶子节点的效率。
 在Innodb中，存在两种形式的索引，一种是聚簇形式的主键索引，另外一种形式是和其他存储引擎（如MyISAM）存放形式基本相同的普通B-Tree索引，这种索引在Innodb存储引擎中被称作二级索引。

 图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在根节点和 分支节点方面都还是完全一样的。而 叶子节点就出现差异了。在主键索引中，叶子结点存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而二级索引则和其他普通的 B-Tree 索引没有太大的差异，只是在叶子结点除了存放索引键的相关信息外，还存放了 Innodb 的主键值。
 所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过二级索引来访问数据的话，Innodb 首先通过二级索引的相关信息，通过相应的索引键检索到叶子节点之后，需要再通过叶子节点中存放的主键值再通过主键索引来获取相应的数据行。
 MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的二级索引的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在叶子节点上面除了存放索引键信息之外，再存放能直接定位MyISAM 数据文件中相应的数据行的信息（如 Row Number），但并不会存放主键的键值信息。

3、索引的利弊

优点： 提高数据的检索速度，降低数据库的IO成本；
缺点：查询需要更新索引信息带来额外的资源消耗，索引还会占用额外的存储空间

4、如何判断是否需要建立索引

 上面说了索引的利弊，我们知道索引并不是越多越好，索引也会带来副作用。那么我们该怎么判断是否需要建立索引呢？
1、 较频繁的作为查询条件的字段应该创建索引；
2、更新频繁的字段不适合建立索引；
3、唯一性太差的不适合创建索引，如状态字段；
4、不出现在where中的字段不适合创建索引；

5、单索引还是组合索引？

In allgemeinen Anwendungsszenarien bevorzuge ich im Allgemeinen die Erstellung eines kombinierten Index, insbesondere bei Parallelität, solange eines der Filterfelder in den meisten Szenarien mehr als 90 % der Daten filtern kann und andere Filterfelder häufig aktualisiert werden Dies gilt insbesondere in Szenarien mit höheren Volumina. Denn wenn die Parallelität erhöht wird, ist die Gesamtmenge der eingesparten Ressourcen immer noch sehr groß, da das Ausführungsvolumen sehr groß ist, selbst wenn wir für jede Abfrage einen kleinen Teil des E/A-Verbrauchs einsparen.
Wenn wir jedoch einen kombinierten Index erstellen, bedeutet dies nicht, dass alle Felder in den Abfragebedingungen in einem Index platziert werden müssen. Wir sollten einen Index von mehreren Abfragen verwenden lassen und die Anzahl der Indizes so weit wie möglich reduzieren Kosten und Speicherung von Updates.
MySQL stellt uns eine Funktion zur Verfügung, die die Optimierung des Index selbst reduziert, nämlich „Präfixindex“. Das heißt, wir können nur den vorherigen Teil eines Felds als Indexschlüssel zum Indizieren des Felds verwenden, wodurch der vom Index belegte Platz reduziert und die Zugriffseffizienz des Index verbessert wird. Natürlich sind Präfixindizes nur für Felder geeignet, bei denen die Präfixe relativ zufällig sind und nur wenige Wiederholungen aufweisen.

6. Indexauswahl

1. Versuchen Sie, den besten Index für die aktuelle Abfrage zu filtern.
2. Wenn Sie einen kombinierten Index auswählen, befindet sich das beste Filterfeld in der aktuellen Abfrage Feld Je höher die Rangfolge in der Reihenfolge, desto besser.
3. Versuchen Sie bei der Auswahl eines kombinierten Indexes, einen Index auszuwählen, der mehr Felder in der Where-Klausel der aktuellen Abfrage enthalten kann.
4. Versuchen Sie, statistische Informationen zu analysieren und anzupassen Es wird davon ausgegangen, dass die späteren Wartungskosten sehr hoch sein werden, um das Ziel der Auswahl des geeigneten Index zu erreichen und die Auswahl der Indexsteuerung durch manuelle Hinweise zu reduzieren.

7. Einschränkungen von MySQL-Indizes

1. Die Summe der Indexschlüssellängen der MyISAM-Speicher-Engine darf 1000 Bytes nicht überschreiten.
3. MySQL unterstützt keine Funktionsindizes
4. Bei Verwendung von != oder kann der MySQL-Index nicht verwendet werden
5. Nachdem das Filterfeld für die Funktionsoperation verwendet wurde, kann der MySQL-Index nicht verwendet werden; Wenn die Near-Feldtypen in der Jion-Anweisung inkonsistent sind, kann der MySQL-Index nicht verwendet werden Nicht äquivalente Abfrage, MySQL kann den HASH-Index nicht verwenden
9. Wenn der Zeichentyp eine Zahl ist, kann nicht = 10 verwendet werden. Verwenden Sie stattdessen „in“ oder „union all“.

8. Join-Prinzip und Optimierung

Join-Prinzip

: In MySQL gibt es nur einen Join-Algorithmus, der tatsächlich die Ergebnismenge der Treibertabelle als Basisdaten der Schleife verwendet. Anschließend werden die Daten im Ergebnissatz als Filterbedingungen verwendet, um die Daten in der nächsten Tabelle einzeln abzufragen, und dann werden die Ergebnisse zusammengeführt. Wenn noch aktuelle Teilnehmer vorhanden sind, wird der vorherige aktuelle Ergebnissatz als Basisdaten für den Zyklus verwendet und der Zyklus wird erneut durchlaufen usw. Optimierung

:

1. Reduzieren Sie die Gesamtzahl der Schleifen in der Join-Anweisung so weit wie möglich (denken Sie daran, dass die kleine Ergebnismenge die zuvor erwähnte große Ergebnismenge antreibt); 2. Priorisieren Sie die Optimierung der inneren Schleife 3. Stellen Sie sicher, dass das Join-Bedingungsfeld der gesteuerten Tabelle indiziert wurde.
4. Wenn keine Garantie dafür besteht, dass das Join-Bedingungsfeld der gesteuerten Tabelle indiziert ist und genügend Speicherressourcen vorhanden sind, gehen Sie nicht geizig mit der Einstellung um des Join-Puffers (Join-Puffer wird nur in All, Index, Range verwendet);
9 ORDER BY-Optimierung


In MySQL gibt es nur zwei Arten von ORDER BY-Implementierungen:

1. Erhalten geordnete Daten direkt über geordnete Indizes, sodass kein Sortiervorgang erforderlich ist, um die vom Client benötigten geordneten Daten zu erhalten

2. Sortieren Sie die in der Speicher-Engine zurückgegebenen Daten über den MySQL-Sortieralgorithmus und geben Sie die sortierten Daten dann an zurück der Kunde.

Die Verwendung der Indexsortierung ist die beste Methode. Wenn jedoch kein Index Lin Yong vorhanden ist, implementiert MySQL hauptsächlich zwei Algorithmen:

1. Entfernen Sie die zum Sortieren verwendeten Felder, die die Filterbedingungen erfüllen, und diejenigen, die die Zeilendaten direkt lokalisieren können Zeilenzeigerinformationen, führen Sie den eigentlichen Sortiervorgang im Sortierpuffer durch und kehren Sie dann mithilfe der sortierten Daten gemäß den Zeilenzeigerinformationen zur Tabelle zurück, um die Daten anderer vom Client angeforderter Felder abzurufen, und geben Sie sie dann an den Client zurück ;

2. Nehmen Sie gemäß den Filterbedingungen die Daten des Sortierfelds und aller anderen vom Client angeforderten Felder gleichzeitig heraus, speichern Sie die Felder, die nicht sortiert werden müssen, in einem Speicherbereich und sortieren Sie sie dann Feld- und Zeilenzeigerinformationen im Sortierpuffer und schließlich die Sortierung verwenden. Der resultierende Zeilenzeiger wird mit den im Speicherbereich zusammen mit anderen Feldern gespeicherten Zeilenzeigerinformationen abgeglichen, um die Ergebnismenge zusammenzuführen, und dann der Reihe nach an den Client zurückgegeben.

Im Vergleich zum ersten Algorithmus reduziert der zweite Algorithmus hauptsächlich den sekundären Zugriff auf Daten. Nach dem Sortieren ist es nicht erforderlich, zur Tabelle zurückzukehren, um Daten abzurufen, wodurch E/A-Vorgänge eingespart werden. Natürlich verbraucht der zweite Algorithmus mehr Speicher, was eine typische Optimierungsmethode ist, bei der Raum gegen Zeit getauscht wird.

Bei der Join-Sortierung mit mehreren Tabellen wird der vorherige Join-Ergebnissatz zunächst über eine temporäre Tabelle in der temporären Tabelle gespeichert und dann werden die Daten der temporären Tabelle zur Operation in den Sortierpuffer abgerufen.

Für die Nicht-Index-Sortierung versuchen Sie, den zweiten Sortieralgorithmus auszuwählen:

1. Erhöhen Sie die Parametereinstellung „max_length_for_sort_data“:
MySQL entscheidet, welcher Algorithmus verwendet werden soll, wenn wir das Feld „Wann“ zurückgeben Ist die maximale Länge kleiner als dieser Parameter, wählt MySQL den zweiten Algorithmus und umgekehrt. Wenn also ausreichend Speicher vorhanden ist, kann MySQL durch Erhöhen dieses Parameterwerts den zweiten Algorithmus auswählen

3. Erhöhen Sie die Einstellung des Parameters sort_buffer_size:

Durch Erhöhen von sort_buffer_size soll MySQL nicht die Auswahl eines verbesserten Sortieralgorithmus ermöglicht werden, sondern MySQL ermöglicht werden, die Segmentierung der Daten, die während des Sortiervorgangs sortiert werden müssen, zu minimieren, da dies zu einem Ergebnis führt , MySQL muss temporäre Tabellen verwenden, um die Austauschsortierung durchzuführen.

4. Endlich

Tuning ist eigentlich eine sehr schwierige Sache, und Tuning ist nicht auf die oben genannte Abfrageoptimierung beschränkt. Wie zum Beispiel die Optimierung des Tabellendesigns, die Optimierung von Datenbankparametern, die Optimierung von Anwendungen (Reduzierung zyklischer Datenbankvorgänge, Batch-Hinzufügen; Datenbankverbindungspool; Cache;) und so weiter. Natürlich gibt es viele Stimmtechniken, die erst in der Praxis wirklich erlernt werden können. Nur wenn Sie ständig versuchen, sich anhand von Theorie und Fakten zu verbessern, können Sie ein wahrer Tuning-Meister werden.

Verwandte kostenlose Lernempfehlungen:

MySQL-Datenbank(Video)

UNION: Alle SELECTs ab dem zweiten SELECT in der UNION-Anweisung, das erste SELECT ist PRIMARY
	TYPE
const: Konstante, es stimmt höchstens ein Datensatz überein. Da es sich um eine Konstante handelt, muss sie eigentlich nur gelesen werden einmal	Zeilen Die geschätzte Anzahl der Ergebnissatzdatensätze Extra

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der MySQL-Abfrageoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!