Tutorial zur SQL-Optimierung: IN- und RANGE-Abfragen

Vorwort

In „High Performance MySQL“ wird erwähnt, dass die Verwendung der In-Methode bestimmte Bereichsabfragen effektiv ersetzen und die Abfrageeffizienz verbessern kann, da in einem Index der Teil nach dem Bereichsfeld ungültig ist (ps. ICP muss berücksichtigt werden). Der MySQL-Optimierer konvertiert die Abfrage in n*m-Kombinationen für die Abfrage und führt schließlich die zurückgegebenen Werte zusammen, was einer Vereinigung ähnelt, aber effizienter ist.

Bei MySQL treten viele Probleme auf, wenn zu viele IN()-Kombinationsbedingungen vorhanden sind. Die Abfrageoptimierung kann lange dauern und viel Speicher verbrauchen. Die neue Version von MySQL führt keine Planauswertung durch, wenn die Anzahl der Kombinationen eine bestimmte Zahl überschreitet, was dazu führen kann, dass MySQL die Indizes nicht optimal nutzt.

Die hier angegebene Zahl wird in MySQL 5.6.5 und späteren Versionen durch den Parameter eq_range_index_dive_limit gesteuert. Die Standardeinstellung ist 10 und wurde seit Version 5.7 auf 200 geändert. Natürlich kann sie manuell eingestellt werden. 5.6 Im Handbuch heißt es:

Mit der Systemvariable eq_range_index_dive_limit können Sie die Anzahl der Werte konfigurieren, bei denen der Optimierer von einer Zeilenschätzungsstrategie zur anderen wechselt. Um die Verwendung von Statistiken zu deaktivieren und immer Index-Dives zu verwenden, setzen Sie eq_range_index_dive_limit auf 0. Um die Verwendung von Index-Dives für Vergleiche von bis zu N Gleichheitsbereichen zuzulassen, setzen Sie eq_range_index_dive_limit auf N + 1. eq_range_index_dive_limit ist ab MySQL 5.6.5 verfügbar. Vor 5.6.5 verwendet der Optimierer Index-Dives, was eq_range_index_dive_limit=0 entspricht.

Mit anderen Worten:

eq_range_index_dive_limit = 0 Nur Index-Dive kann verwendet werden

0 < eq_range_index_dive_limit <= N Indexstatistiken verwenden

eq_range_index_dive_limit > N: nur Index-Dive kann verwendet werden

Der Standardwert wurde in MySQL 5.7 von 10 auf 200 geändert, um sicherzustellen, dass der Ausführungsplan für Bereichsgleichheitsoperationen (IN()) so genau wie möglich ist, da die Anzahl der IN()-Listen häufig 10 übersteigt.

Im offiziellen MySQL-Handbuch gibt es einen Satz:

Der Optimierer kann die Zeilenanzahl für jeden Bereich schätzen, indem er in den Index oder in die Indexstatistiken eintaucht.

Hauptidee:

Der Optimierer schätzt die Anzahl der in jedem Bereichssegment enthaltenen Tupel. Beispielsweise wird „a IN (10, 20, 30)“ als Gleichheitsvergleich betrachtet, und die drei Bereichssegmente werden tatsächlich auf drei einzelne Werte vereinfacht, nämlich 10, 20 und 30. Der Grund für die Verwendung von Bereichssegmenten liegt darin, dass die meisten „Bereichs“-Scans in MySQL Bereichsscans sind und der einzelne Wert hier als Sonderfall eines Bereichssegments betrachtet werden kann.

Es gibt zwei Schätzmethoden:

1. In den Index einzutauchen bedeutet, den Index zu verwenden, um die Anzahl der Tupel zu schätzen. Dies wird als Index Dive bezeichnet.
2. Indexstatistik: Verwenden Sie die statistischen Werte des Index, um Schätzungen vorzunehmen.

Vergleichen Sie diese beiden Methoden

1. Index-Dive: langsam, kann aber genaue Werte erhalten (die Implementierung von MySQL besteht darin, die Anzahl der Indexelemente zu zählen, die dem Index entsprechen, sodass es genau ist)
2. Indexstatistik: Schnell, aber die erhaltenen Werte sind möglicherweise nicht genau

Einfach ausgedrückt legt der Wert der Option eq_range_index_dive_limit die Obergrenze der Anzahl der Bedingungen in der IN-Liste fest. Wenn der Wert überschritten wird, wird der Ausführungsplan von Index Dive auf Indexstatistiken umgestellt.

Warum müssen wir zwischen diesen beiden Methoden unterscheiden?

1. Der Abfrageoptimierer berechnet anhand des Kostenschätzungsmodells die Kosten der einzelnen Pläne und wählt den Plan mit den niedrigsten Kosten aus.
2. Beim Scannen einer einzelnen Tabelle müssen die Kosten berechnet werden. Daher müssen beim Indexscan einer einzelnen Tabelle auch die Kosten berechnet werden.
3. Die Berechnungsformel für eine einzelne Tabelle lautet normalerweise: Kosten = Anzahl der Tupel * Durchschnittlicher IO-Wert
4. Unabhängig davon, welche Scanmethode verwendet wird, muss die Anzahl der Tupel berechnet werden
5. Wenn wir auf einen Ausdruck wie „a IN (10, 20, 30)“ stoßen und feststellen, dass Spalte a einen Index hat, müssen wir sehen, wie viele Tupel dieser Index scannen kann, und die Kosten für den Indexscan berechnen. Daher verwenden wir die beiden in diesem Artikel erwähnten Methoden: „Index Dive“ und „Index Statistics“.

Diskussionsthemen

1. Bereichsabfrage und Indexverwendung
2. Beschreibung von eq_range_index_dive_limit

Bereichsabfrage und Indexverwendung

Das SQL lautet wie folgt:

SELECT * FROM pre_forum_post WHERE tid=7932552 AND invisible IN('0','-2') ORDER BY dateline DESC LIMIT 10;

Der Index lautet wie folgt:

PRIMARY(tid,position),
pid(pid),
fid(tid),
Anzeigereihenfolge (TID, unsichtbar, Datumszeile)
zuerst(tid, zuerst)
new_auth(Autor-ID, unsichtbar, TID)
idx_dt(Datumsgrenze)
mul_test(tid,unsichtbar,Datumsgrenze,pid)

Schauen Sie sich den Ausführungsplan an:

root@localhost 16:08:27 [ultrax]> erklären SELECT * FROM pre_forum_post WHERE tid=7932552 AND `unsichtbar` IN('0','-2') 
 -> ORDER BY Datumszeile DESC LIMIT 10;
+----+--------------+----------------+-------+-------------------------------------------+--------------+---------+---------+------+------+---------------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+----------------+-------+-------------------------------------------+--------------+---------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | pre_forum_post | Bereich | PRIMARY,displayorder,first,mul_test,idx_1 | displayorder | 4 | NULL | 54 | Indexbedingung wird verwendet; Dateisortierung wird verwendet | 
+----+--------------+----------------+-------+-------------------------------------------+--------------+---------+---------+------+------+---------------------------------------+
1 Zeile im Satz (0,00 Sek.)

Der MySQL-Optimierer geht davon aus, dass es sich um eine Bereichsabfrage handelt, sodass das Datumsfeld im Index (tid, invisible, dateline) definitiv nicht verwendet wird. Mit anderen Worten, die endgültige Sortierung dieses SQL wird definitiv einen temporären Ergebnissatz generieren und dann die Sortierung im Ergebnissatz abschließen, anstatt die Sortieraktion direkt im Index abzuschließen. Wir haben also versucht, einen Index hinzuzufügen.

root@localhost 16:09:06 [ultrax]> Tabelle ändern, pre_forum_post, Index idx_1 hinzufügen (tid, dateline); 
Abfrage OK, 20374596 Zeilen betroffen, 0 Warnungen (600,23 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
root@localhost 16:20:22 [ultrax]> erklären SELECT * FROM pre_forum_post force index (idx_1) WHERE tid=7932552 AND `unsichtbar` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
+----+--------------+----------------+------+---------------+-----------+---------+---------+---+--------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+----------------+------+---------------+-----------+---------+---------+---+--------+-------------+
| 1 | SIMPLE | pre_forum_post | ref | idx_1 | idx_1 | 3 | const | 120646 | Verwenden von where | 
+----+--------------+----------------+------+---------------+-----------+---------+---------+---+--------+-------------+
1 Zeile im Satz (0,00 Sek.)
root@localhost 16:22:06 [ultrax]> SELECT sql_no_cache * FROM pre_forum_post WHERE tid=7932552 AND `unsichtbar` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
...
10 Reihen im Satz (0,40 Sek.)
root@localhost 16:23:55 [ultrax]> SELECT sql_no_cache * FROM pre_forum_post force index (idx_1) WHERE tid=7932552 AND `unsichtbar` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
...
10 Zeilen im Satz (0,00 Sek.)

Experimente haben gezeigt, dass die Wirkung hervorragend ist. Tatsächlich ist es nicht schwer zu verstehen. Wir haben oben gesagt, dass in() im MySQL-Optimierer Daten in verschiedenen Kombinationen abruft. Wenn eine Sortierung oder Gruppe hinzugefügt wird, kann sie nur auf einem temporären Ergebnissatz ausgeführt werden. Mit anderen Worten, selbst wenn der Index Sortierungs- oder Gruppenfelder enthält, ist er immer noch nutzlos. Der einzige Kritikpunkt ist, dass die Auswahl des MySQL-Optimierers immer noch nicht zuverlässig genug ist.

Zusammenfassend lässt sich sagen: Wenn Sie in() in MySQL-Abfragen verwenden, müssen Sie nicht nur auf die Anzahl der in()-Listen und den Wert von eq_range_index_dive_limit (Details siehe unten) achten, sondern auch auf die Verwendung von Indizes, wenn das SQL Sortierung/Gruppierung/Deduplizierung usw. enthält.

Beschreibung von eq_range_index_dive_limit

Wenn wir immer noch das obige Beispiel verwenden, warum kann idx_1 nicht direkt verwendet werden? Muss ich einen Hinweis verwenden, um die Verwendung nur dieses Indexes zu erzwingen? Hier schauen wir uns zuerst den Wert von eq_range_index_dive_limit an.

root@localhost 22:38:05 [ultrax]> Variablen wie „eq_range_index_dive_limit“ anzeigen;
+-----------------------------------------+----------+
| Variablenname | Wert |
+-----------------------------------------+----------+
| eq_range_index_tauchgrenze | 2 | 
+-----------------------------------------+----------+
1 Zeile im Satz (0,00 Sek.)

Gemäß der oben genannten Situation verwendet 0 < eq_range_index_dive_limit <= N Indexstatistiken. Verwenden wir also OPTIMIZER_TRACE, um das herauszufinden.

{
 "index": "Anzeigereihenfolge",
 "Bereiche": [
 "7932552 <= tid <= 7932552 UND -2 <= unsichtbar <= -2",
 „7932552 <= tid <= 7932552 UND 0 <= unsichtbar <= 0“
 ],
 "index_dives_for_eq_ranges": falsch,
 "rowid_ordered": falsch,
 "using_mrr": falsch,
 "index_only": falsch,
 "Zeilen": 54,
 "Kosten": 66,81,
 "ausgewählt": wahr
}
// Index dive ist falsch, und finally chose ist wahr
...
{
 "index": "idx_1",
 "Bereiche": [
 „7932552 <= tid <= 7932552“
 ],
 "index_dives_for_eq_ranges": wahr,
 "rowid_ordered": falsch,
 "using_mrr": falsch,
 "index_only": falsch,
 "Zeilen": 120646,
 "Kosten": 144776,
 "ausgewählt": falsch,
 "Ursache": "Kosten"
}

Wir können sehen, dass die Kosten des Displayorder-Index 66,81 betragen, während die Kosten von idx_1 120646 betragen, und schließlich wählt der MySQL-Optimierer den Displayorder-Index. Wenn wir also eq_range_index_dive_limit auf > N setzen, sollten wir dann die Index-Dive-Berechnungsmethode verwenden, um einen genaueren Ausführungsplan zu erhalten?

root@localhost 22:52:52 [ultrax]> setze eq_range_index_dive_limit = 3;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
root@localhost 22:55:38 [ultrax]> erklären SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 10;
+----+--------------+----------------+------+-------------------------------------------+-----------+---------+-------+--------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+----------------+------+-------------------------------------------+-----------+---------+-------+--------+-------------+
| 1 | SIMPLE | pre_forum_post | ref | PRIMARY,displayorder,first,mul_test,idx_1 | idx_1 | 3 | const | 120646 | Verwenden von where | 
+----+--------------+----------------+------+-------------------------------------------+-----------+---------+-------+--------+-------------+
1 Zeile im Satz (0,00 Sek.)

Die optimize_trace-Ergebnisse lauten wie folgt

{
 "index": "Anzeigereihenfolge",
 "Bereiche": [
 "7932552 <= tid <= 7932552 UND -2 <= unsichtbar <= -2",
 „7932552 <= tid <= 7932552 UND 0 <= unsichtbar <= 0“
 ],
 "index_dives_for_eq_ranges": wahr,
 "rowid_ordered": falsch,
 "using_mrr": falsch,
 "index_only": falsch,
 "Zeilen": 188193,
 "Kosten": 225834,
 "ausgewählt": wahr
}
...
{
 "index": "idx_1",
 "Bereiche": [
 „7932552 <= tid <= 7932552“
 ],
 "index_dives_for_eq_ranges": wahr,
 "rowid_ordered": falsch,
 "using_mrr": falsch,
 "index_only": falsch,
 "Zeilen": 120646,
 "Kosten": 144776,
 "ausgewählt": wahr
}
...
 "Kosten für Plan": 144775,
 "Zeilen für Plan": 120646,
 "ausgewählt": wahr

Bei der alternativen Indexauswahl werden beide Indizes ausgewählt und bei der abschließenden logischen Optimierung wird der Index mit den niedrigsten Kosten, idx_1, ausgewählt. Oben ist dargestellt, wie sich der Wert von eq_range_index_dive_limit bei Abfragen mit gleichem Bereich auf den Berechnungsaufwand des MySQL-Optimierers auswirkt und somit die Indexauswahl beeinflusst. Darüber hinaus können wir durch Profiling den statistischen Zeitaufwand des Optimierers einsehen:

Index-Tauchgang

+----------------------+----------+
| Status | Dauer |
+----------------------+----------+
| beginnt | 0,000048 | 
| Berechtigungen werden geprüft | 0,000004 | 
| Eröffnungstabellen | 0,000015 | 
| init | 0,000044 | 
| Systemsperre | 0,000009 | 
| Optimierung | 0,000014 | 
| Statistik | 0,032089 | 
| Vorbereitung | 0,000022 | 
| Sortierergebnis | 0,000003 | 
| wird ausgeführt | 0,000003 | 
| Daten werden gesendet | 0,000101 | 
| Ende | 0,000004 | 
| Abfrageende | 0,000002 | 
| Tabellen schließen | 0,000009 | 
| Elemente freigeben | 0,000013 | 
| aufräumen | 0,000012 | 
+----------------------+----------+

Index-Statistiken

+----------------------+----------+
| Status | Dauer |
+----------------------+----------+
| beginnt | 0,000045 | 
| Berechtigungen werden geprüft | 0,000003 | 
| Eröffnungstabellen | 0,000014 | 
| init | 0,000040 | 
| Systemsperre | 0,000008 | 
| Optimierung | 0,000014 | 
| Statistik | 0,000086 | 
| Vorbereitung | 0,000016 | 
| Sortierergebnis | 0,000002 | 
| wird ausgeführt | 0,000002 | 
| Daten werden gesendet | 0,000016 | 
| Sortierindex wird erstellt | 0,412123 | 
| Ende | 0,000012 | 
| Abfrageende | 0,000004 | 
| Tabellen schließen | 0,000013 | 
| Elemente freigeben | 0,000023 | 
| aufräumen | 0,000015 | 
+----------------------+----------+

Es ist ersichtlich, dass bei einer Erhöhung von eq_range_index_dive_limit und Verwendung von Index Dive die Erstellung von Optimiererstatistiken deutlich länger dauert als die Methode der Ndex-Statistiken, am Ende jedoch ein vernünftigerer Ausführungsplan verwendet wird. Die statistische Zeit beträgt 0,032089 s gegenüber 0,000086 s, aber die SQL-Ausführungszeit beträgt etwa 0,03 s gegenüber 0,41 s.

Anhang:

So verwenden Sie optimize_trace

Setzen Sie optimizer_trace='enabled=on'.

Wählen Sie * aus information_schema.optimizer_trace\G

Hinweis: optimizer_trace empfiehlt, das Debuggen nur im Sitzungsmodus zu aktivieren

So verwenden Sie das Profil

Profilerstellung auf EIN setzen;
SQL ausführen;
Profile anzeigen;
Profil für Abfrage 2 anzeigen;
Profilblock „IO, CPU“ für Abfrage 2 anzeigen;

Sie können auch Informationen wie Speicher, Swaps, Kontextwechsel, Quelle usw. sehen.

Verweise

[1]MySQL SQL-Optimierungsreihe: IN- und RANGE-Abfragen

https://www.jb51.net/article/201251.htm

[2]MySQL-Technologie zur physischen Abfrageoptimierung --- Index-Dive-Analyse

http://blog.163.com/li_hx/blog/static/18399141320147521735442/

Dies ist das Ende dieses Artikels über das SQL-Optimierungs-Tutorial für In- und Range-Abfragen. Weitere relevante Inhalte zur SQL-Optimierung für In- und Range-Abfragen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!