Beispiele für Optimierungstechniken zur Verbesserung der Effizienz langsamer Abfragen in MySQL IN-Anweisungen
|
Die Tabellenstruktur ist wie folgt. Es gibt nur 690 Artikel. Artikeltabelle Artikel (ID, Titel, Inhalt) Tag-Tabelle tag(tid,tag_name) Tag-Artikel-Zwischentabelle article_tag (id, tag_id, article_id) Es gibt ein Tag mit der TID 135. Suchen Sie nach der Artikelliste mit dem Tag mit der TID 135. 690 Artikel, mit der folgenden Abfrage, sehr langsam: Wählen Sie ID, Titel aus dem Artikel, in dem die ID enthalten ist ( Wählen Sie die Artikel-ID aus dem Artikel-Tag, wobei die Tag-ID = 135 ist. ) Das hier ist sehr schnell: Wählen Sie die Artikel-ID aus dem Artikel-Tag, wobei die Tag-ID = 135 ist. Die Abfrageergebnisse sind fünf Artikel mit den IDs 428, 429, 430, 431 und 432 Die Suche nach Artikeln kann auch schnell mit dem folgenden SQL durchgeführt werden: Wählen Sie ID, Titel aus dem Artikel, in dem die ID enthalten ist ( 428,429,430,431,432 ) Lösung: Wählen Sie ID, Titel aus dem Artikel, in dem die ID enthalten ist ( wähle article_id aus (wähle article_id aus article_tag, wobei tag_id=135) als tbt ) Andere Lösungen: (Beispiele) mysql> wähle * aus abc_number_prop, wobei number_id in (wähle number_id aus abc_number_phone, wobei phone = '82306839'); Um Platz zu sparen, wird der Ausgabeinhalt weggelassen, dasselbe gilt unten. 67 Zeilen im Satz (12,00 Sek.) Es wurden nur 67 Datenzeilen zurückgegeben, aber es dauerte 12 Sekunden. Es können viele solcher Abfragen gleichzeitig im System vorhanden sein, und das System wird definitiv nicht in der Lage sein, damit umzugehen. Verwenden Sie die Beschreibung, um es anzuzeigen (Hinweis: Erklären ist auch OK). mysql> desc select * from abc_number_prop where number_id in (wählen Sie number_id aus abc_number_phone, wobei phone = „82306839“); +----+--------------------+------------------+--------+-----------------+-----------+---------+---------+---------+---------+--------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+--------------------+------------------+--------+-----------------+-----------+---------+---------+---------+---------+--------------------------+ | 1 | PRIMARY | abc_number_prop | ALL | NULL | NULL | NULL | NULL | 2679838 | Verwenden von „where“ | | 2 | ABHÄNGIGE UNTERABFRAGE | abc_Nummer_Telefon | eq_ref | Telefon, Nummer_ID | Telefon | 70 | const, Funktion | 1 | Verwenden von „where“; Verwenden von „index“ | +----+--------------------+------------------+--------+-----------------+-----------+---------+---------+---------+---------+--------------------------+ 2 Zeilen im Satz (0,00 Sek.) Es ist ersichtlich, dass bei der Ausführung dieser Abfrage mehr als zwei Millionen Zeilen gescannt werden. Liegt es daran, dass kein Index erstellt wird? Werfen wir einen Blick darauf mysql>Index von abc_number_phone anzeigen; +------------------+------------+----------+--------------+-----------------+-----------+------------+----------+----------+--------+----------+---------+-------------+--------------+ | Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar | +------------------+------------+----------+--------------+-----------------+-----------+------------+----------+----------+--------+----------+---------+-------------+--------------+ | abc_Nummer_Telefon | 0 | PRIMARY | 1 | Nummer_Telefon-ID | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 0 | Telefon | 1 | Telefon | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 0 | Telefon | 2 | number_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_Nummer_Telefon | 1 | Nummer_ID | 1 | Nummer_ID | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | erstellt von | 1 | erstellt von | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | geändert_von | 1 | geändert_von | A | 36879 | NULL | NULL | JA | BTREE | | | +------------------+------------+----------+--------------+-----------------+-----------+------------+----------+----------+--------+----------+---------+-------------+--------------+ 6 Zeilen im Satz (0,06 Sek.) mysql>Index von abc_number_prop anzeigen; +-----------------+------------+----------+--------------+----------------+-----------+-------------+----------+----------+--------+------+------------+---------+---------+---------+-----------+ | Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar | +-----------------+------------+----------+--------------+----------------+-----------+-------------+----------+----------+--------+------+------------+---------+---------+---------+-----------+ | abc_number_prop | 0 | PRIMARY | 1 | number_prop_id | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | Nummern-ID | 1 | Nummern-ID | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | erstellt_von | 1 | erstellt_von | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | geändert von | 1 | geändert von | A | 311268 | NULL | NULL | JA | BTREE | | | +-----------------+------------+----------+--------------+----------------+-----------+-------------+----------+----------+--------+------+------------+---------+---------+---------+-----------+ 4 Reihen im Satz (0,15 Sek.) Wie aus der obigen Ausgabe ersichtlich, wurden für diese beiden Tabellen Indizes für das Feld „number_id“ erstellt. mysql> desc wähle number_id aus abc_number_phone, wobei phone = „82306839“; +----+----------+------------------+---------+---------------+-----------+-----------+-----------+----------+-----------+--------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+----------+------------------+---------+---------------+-----------+-----------+-----------+----------+-----------+--------------------------+ | 1 | SIMPLE | abc_number_phone | ref | phone | phone | 66 | const | 6 | Where verwenden; Index verwenden | +----+----------+------------------+---------+---------------+-----------+-----------+-----------+----------+-----------+--------------------------+ 1 Zeile im Satz (0,00 Sek.) Kein Problem, wir müssen nur ein paar Datenzeilen scannen und der Index wird funktionieren. Hör zu: mysql> wähle number_id aus abc_number_phone, wobei phone = „82306839“; +-------------+ | Nummern-ID | +-------------+ | 8585 | | 10720 | |148644| |151307| |170691| |221897| +-------------+ 6 Zeilen im Satz (0,00 Sek.) Fügen Sie die aus der Unterabfrage erhaltenen Daten direkt in die obige Abfrage ein mysql> wähle * aus abc_number_prop, wobei number_id in (8585, 10720, 148644, 151307, 170691, 221897) ist; 67 Zeilen im Satz (0,03 Sek.) Die Geschwindigkeit ist auch hoch. Es scheint, dass MySQL bei der Verarbeitung von Unterabfragen nicht gut genug ist. Ich habe dies sowohl unter MySQL 5.1.42 als auch unter MySQL 5.5.19 versucht und bei beiden trat dieses Problem auf. Nach einer Suche im Internet stellte ich fest, dass viele Leute auf dieses Problem gestoßen sind: Referenz 1: MySQL-Optimierung: Verwendung von Joins anstelle von Unterabfragen Referenz 2: Beispielanalyse zur Optimierung von MySQL-Unterabfragen und verschachtelten Abfragen Versuchen Sie es gemäß den Vorschlägen in diesen Online-Materialien stattdessen mit der Verwendung von „join“. Wählen Sie * aus abc_number_prop, wobei number_id in (Wählen Sie number_id aus abc_number_phone, wobei phone = „82306839“); Nach der Änderung: Wählen Sie a.* aus abc_number_prop, einem inneren Join abc_number_phone b auf a.number_id = b.number_id, wobei phone = „82306839“; mysql> wähle a.* aus abc_number_prop, einem inneren Join abc_number_phone b auf a.number_id = b.number_id, wobei phone = „82306839“; 67 Zeilen im Satz (0,00 Sek.) Die Wirkung ist gut und die Abfragezeit beträgt fast 0. Sehen wir uns an, wie MySQL diese Abfrage ausführt. mysql>desc wähle a.* aus abc_number_prop ein innerer Join abc_number_phone b auf a.number_id = b.number_id wobei phone = „82306839“; +----+--------------+-------+------+-----------------+-----------+---------+----------------+------+--------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+--------------+-------+------+-----------------+-----------+---------+----------------+------+--------------------------+ | 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | Verwenden von where; Verwenden von index | | 1 | EINFACH | a | ref | Nummer_ID | Nummer_ID | 4 | eap.b.Nummer_ID | 3 | | +----+--------------+-------+------+-----------------+-----------+---------+----------------+------+--------------------------+ 2 Zeilen im Satz (0,00 Sek.) Zusammenfassung: Wenn die Unterabfrage langsam ist, können Sie JOIN verwenden, um die Abfrage zur Optimierung neu zu schreiben. Es gibt im Internet auch Artikel, in denen es heißt, dass Abfragen mit JOIN-Anweisungen nicht unbedingt immer schneller sind als solche mit Unterabfragen. Dies wird auch im MySQL-Handbuch erwähnt und der spezifische Originaltext befindet sich in diesem Kapitel der MySQL-Dokumentation: Auszug: 1) Über Unterabfragen mit IN: Die Unterabfrageoptimierung für IN ist nicht so effektiv wie für den =-Operator oder für IN(value_list)-Konstrukte. Ein typischer Fall für eine schlechte IN-Unterabfrageleistung liegt vor, wenn die Unterabfrage eine kleine Anzahl von Zeilen zurückgibt, die äußere Abfrage jedoch eine große Anzahl von Zeilen zurückgibt, die mit dem Unterabfrageergebnis verglichen werden müssen. Das Problem besteht darin, dass der Optimierer eine Anweisung, die eine IN-Unterabfrage verwendet, als korrelierte Unterabfrage umschreibt. Betrachten Sie die folgende Anweisung, die eine unkorrelierte Unterabfrage verwendet: WÄHLEN SIE ... AUS t1, WO t1.a IN (WÄHLEN SIE b AUS t2); Der Optimierer schreibt die Anweisung in eine korrelierte Unterabfrage um: WÄHLEN SIE ... AUS t1, WO EXISTIERT (WÄHLEN SIE 1 AUS t2, WO t2.b = t1.a); Wenn die inneren und äußeren Abfragen M bzw. N Zeilen zurückgeben, liegt die Ausführungszeit in der Größenordnung von O(M×N) und nicht von O(M+N), wie es bei einer nicht korrelierten Unterabfrage der Fall wäre. Eine Folge davon ist, dass eine IN-Unterabfrage viel langsamer sein kann als eine Abfrage, die mit einer IN(value_list)-Konstruktion geschrieben wurde, die dieselben Werte auflistet, die die Unterabfrage zurückgeben würde. 2) Informationen zur Konvertierung von Unterabfragen in Verknüpfungen: Der Optimierer ist für Joins ausgereifter als für Unterabfragen. Daher kann eine Anweisung, die eine Unterabfrage verwendet, in vielen Fällen effizienter ausgeführt werden, wenn Sie sie als Join neu schreiben. Eine Ausnahme tritt für den Fall auf, dass eine IN-Unterabfrage als SELECT DISTINCT-Join umgeschrieben werden kann. Beispiel: Wählen Sie Spalte aus t1, wobei ID_Spalte in (Wählen Sie ID_Spalte2 aus t2, wobei Bedingung); Diese Aussage kann wie folgt umgeschrieben werden: WÄHLEN SIE DISTINCT col FROM t1, t2, WO t1.id_col = t2.id_col UND Bedingung; In diesem Fall erfordert der Join jedoch eine zusätzliche DISTINCT-Operation und ist nicht effizienter als die Unterabfrage Zusammenfassen Dies ist der gesamte Inhalt dieses Artikels über die Optimierungstechniken für die langsame Effizienz von MySQL in Anweisungsunterabfragen. Interessierte Freunde können sich Folgendes ansehen: Eine kurze Diskussion über die Effizienz der Vereinigung und Einbindung von MySQL-Unterabfragen, eine Einführung in die MySQL-Optimierung für die Unternehmensproduktion usw. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Jeder ist herzlich eingeladen, sich zu melden und darauf zu verweisen. Ich hoffe, dieser Artikel ist hilfreich für Sie. Das könnte Sie auch interessieren:
|
<<: Beispiel für die Verwendung des Supervisors zum Verwalten von Nginx+Tomcat-Containern
>>: Gründe und Lösungen für das Versagen des React-Ereignisdrosselungseffekts
Artikel empfehlen
Implementierung eines einfachen Rechners auf Basis von JavaScript
In diesem Artikel wird der spezifische JavaScript...
So legen Sie die Anzahl der MySQL-Verbindungen fest (zu viele Verbindungen)
Während der Verwendung von MySQL wurde festgestel...
Installieren Sie MySQL 5.7.18 mit dem RPM-Paket unter CentOS 7
Ich habe kürzlich MySQL verwendet. Im Artikel „My...
Zusammenfassung der Erfahrungen mit der Verwendung des Div-Box-Modells
Berechnung des Boxmodells <br />Rand + Rahme...
Einführung in geplante Aufgaben im Linux-System
Inhaltsverzeichnis 1. Planaufgaben anpassen 2. Ze...
Eine kurze Diskussion über die magischen Verwendungsmöglichkeiten von CSS-Pseudoelementen und Pseudoklassen
CSS spielt auf einer Webseite eine sehr wichtige ...
HTML erlaubt teilweise erzwungene Bildlaufleisten, um den Gesamtstil und das Layout nicht zu zerstören.
Poste zuerst das Effektbild: Damit der Gesamtstil ...
Beispielanalyse der Verwendung gespeicherter MySQL-Prozeduren
Dieser Artikel beschreibt die Verwendung gespeich...
Detaillierte Erklärung des Ref-Attributs von Vue
Zusammenfassen Dieser Artikel endet hier. Ich hof...
Vue und React im Detail
Inhaltsverzeichnis 1. Panorama II. Hintergrund 1....
So übertragen Sie Dateien zwischen Windows und Linux
Dateiübertragung zwischen Windows und Linux (1) V...
Detaillierte Erklärung verschiedener Loop-Speed-Tests in JS, die Sie nicht kennen
Inhaltsverzeichnis Vorwort 1. for-Schleife 2. whi...
Klicken Sie in Vue auf den Umschaltknopf, um die Schaltfläche zu aktivieren und dann zu deaktivieren.
Die Implementierungsmethode gliedert sich in drei...
Einführung in berechnete Eigenschaften in Vue
Inhaltsverzeichnis 1. Was ist eine berechnete Eig...
Webprojektentwicklung VUE-Mischungs- und Vererbungsprinzip
Inhaltsverzeichnis Mischen Mixin-Hinweis (doppelt...