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

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

Lassen Sie uns zunächst über die in()-Abfrage sprechen. 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. Bei Verwendung der In-Methode konvertiert der MySQL-Optimierer die Abfrage tatsächlich in n*m-Kombinationen und führt schließlich die Rückgabewerte zusammen, was der Union etwas ähnelt, aber effizienter ist. Gleichzeitig gibt es aber auch einige Probleme:

Ältere MySQL-Versionen verursachen viele Probleme, 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 „bestimmte Zahl“ wird hier durch den Parameter eq_range_index_dive_limit in MySQL 5.6.5 und späteren Versionen gesteuert (danke an @叶金荣 für seinen Rat). Die Standardeinstellung ist 10 und wird in Versionen nach 5.7 auf 200 geändert. Natürlich können wir es manuell einstellen. Schauen wir uns die Anweisungen im 5.6-Handbuch an:

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 äquivalent zu eq_range_index_dive_limit=0 ist.

Das heißt:

1. eq_range_index_dive_limit = 0: nur Index-Dive kann verwendet werden
2. 0 < eq_range_index_dive_limit <= N Indexstatistiken verwenden
3. eq_range_index_dive_limit > N: nur Index-Dive kann verwendet werden

Index Dive und Indexstatistiken sind die Methoden, die der MySQL-Optimierer zur Kostenschätzung verwendet. Erstere hat eine langsame statistische Geschwindigkeit, kann aber genaue Werte ermitteln, während letztere eine schnelle statistische Geschwindigkeit hat, die Daten jedoch möglicherweise nicht genau sind.

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

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.

Sag es im Voraus

Der heutige Artikel hat zwei Themen:

  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 `unsichtbar` IN('0','-2') 
ORDER BY Datumsgrenze DESC LIMIT 10;

Der Index lautet wie folgt:

+----------------+------------+-----------+--------------+-------------+-----------+-------------+-----------+-------------+----------+--------+---------+---------+---------+---------+---------+
| Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+-----------+-------------+----------+--------+---------+---------+---------+---------+---------+
| pre_forum_post | 0 | PRIMARY | 1 | tid | A | NULL | NULL | NULL | | BTREE | | | 
| pre_forum_post | 0 | PRIMARY | 2 | Position | A | 25521392 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 0 | pid | 1 | pid | A | 25521392 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | fid | 1 | fid | A | 1490 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | Anzeigereihenfolge | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | Anzeigereihenfolge | 2 | unsichtbar | A | 945236 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | Anzeigereihenfolge | 3 | Datumsgrenze | A | 25521392 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | erster | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | erster | 2 | erster | A | 1215304 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | new_auth | 1 | Autor-ID | A | 1963184 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | new_auth | 2 | unsichtbar | A | 1963184 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | neue_Authentifizierung | 3 | tid | A | 12760696 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | idx_dt | 1 | Datumsgrenze | A | 25521392 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | mul_test | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | mul_test | 2 | unsichtbar | A | 945236 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | mul_test | 3 | Datumsgrenze | A | 25521392 | NULL | NULL | | BTREE | | | 
| pre_forum_post | 1 | mul_test | 4 | pid | A | 25521392 | NULL | NULL | | BTREE | | | 
+----------------+------------+-----------+--------------+-------------+-----------+-------------+-----------+-------------+----------+--------+---------+---------+---------+---------+---------+

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. Bei der abschließenden Logikoptimierung wird der Index mit den niedrigsten Kosten, also idx_1, ausgewählt.

Oben sehen Sie, wie sich der Wert von eq_range_index_dive_limit auf den Berechnungsaufwand des MySQL-Optimierers bei Abfragen mit gleichem Bereich 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 | 
| optimieren | 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

Verweise

http://dev.mysql.com/doc/refman/5.6/en/range-optimization.html

http://imysql.com/2014/08/05/a-fake-bug-with-eq-range-index-dive-limit.shtml

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

Dies ist das Ende dieses Artikels über das Tutorial zur MySQL SQL-Optimierung für In- und Range-Abfragen. Weitere Informationen zur MySQL 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, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!

Das könnte Sie auch interessieren:
  • MySQL-Abfrage im Betrieb Abfrageergebnisse werden in der Reihenfolge der in eingestellten angezeigt
  • Lösen Sie das Problem, dass IN-Unterabfragen in MySQL dazu führen, dass der Index unbrauchbar wird
  • Beispiele für Optimierungstechniken zur Verbesserung der Effizienz langsamer Abfragen in MySQL IN-Anweisungen
  • Vergleich der Effizienz der Verwendung von „or“, „in“ und „union all“ in MySQL-Abfragebefehlen
  • Mysql-Unterabfrage IN mit LIMIT-Anwendungsbeispiel
  • Wird der Index in der MySQL-Abfragebedingung verwendet?
  • Detaillierte Erläuterung der Reihenfolge der MySQL-Abfrageergebnisse gemäß der Reihenfolge der ID in in ()
  • Eine kurze Diskussion über die Effizienz der MySQL-Unterabfrage-Vereinigung und in
  • Implementierung von MySQL Select in der Unterabfrageoptimierung

<<:  Vue: Detaillierte Erklärung von Speicherlecks

>>:  HTML implementiert die Funktion zur Erkennung der Eingabevervollständigung

Artikel empfehlen

Beheben Sie das Problem, dass die Lese-/Schreibtrennung von MySQL dazu führt, dass Daten nach dem Einfügen nicht ausgewählt werden

MySQL richtet eine unabhängige Schreibtrennung ei...

Awk-Befehlszeile oder Skript, das Ihnen beim Sortieren von Textdateien hilft (empfohlen)

Awk ist ein leistungsfähiges Tool, das einige Auf...

Was ist die Datei mysql-bin.000001 in MySQL? Kann sie gelöscht werden?

Nachdem ich MySQL über Ports installiert hatte, s...

Verwendung des Zielattributs des HTML-Tags a

1: Wenn Sie das Tag <a> zum Verlinken auf ei...

Implementierungscode der HTML-Floating-Promptbox-Funktion

Allgemeine Formulareingabeaufforderungen belegen ...

Konfigurationsschema für die MySQL-Master-Slave-Synchronisierung unter dem Centos7-System

Vorwort Bei der Arbeit an einem Hochverfügbarkeit...

Konzept und Anwendungsbeispiele für die MySQL-Indexkardinalität

Dieser Artikel erläutert anhand von Beispielen da...

So lösen Sie das Problem, dass MySQL nicht gestartet werden kann, da keine PID erstellt werden kann

Problembeschreibung Die MySQL-Startfehlermeldung ...

Detaillierte Erläuterung des Implementierungsprinzips der Transaktionsisolationsstufe in MySQL

Vorwort Wenn es um Datenbanktransaktionen geht, f...

So ändern Sie MySQL-Berechtigungen, um Hosts den Zugriff zu ermöglichen

Aktivieren Sie Remote-Zugriffsrechte für MySQL My...

Beispiele und Zusammenfassung der In-, Out- und Inout-Parameter von gespeicherten MySQL-Prozeduren

Gespeicherte Prozeduren 1. Erstellen Sie eine ges...

Tutorial zu HTML-Tabellen-Tags (23): Zeilenrahmen-Farbattribut BORDERCOLORDARK

In Zeilen können dunkle Rahmenfarben individuell ...

Der HTML-Seitenkopfcode ist völlig klar

Alle folgenden Codes stehen zwischen <head>....

Stellen Sie Ihre Website automatisch auf den IE7-Kompatibilitätsmodus ein, wenn Sie mit IE8 surfen

Vorwort Um sicherzustellen, dass Ihre Webseiten in...

So installieren Sie nginx unter Linux

Nginx wurde in der Programmiersprache C entwickel...