Vergleichende Analyse von IN und Exists in MySQL-Anweisungen

Hintergrund

Als ich kürzlich SQL-Anweisungen schrieb, war ich mir nicht sicher, ob ich IN oder Exists wählen sollte. Also schrieb ich das SQL beider Methoden auf, um die Ausführungseffizienz zu vergleichen. Ich stellte fest, dass die Abfrageeffizienz von IN viel höher ist als die von Exists, also nahm ich natürlich an, dass die Effizienz von IN besser ist als die von Exists. Im Einklang mit dem Prinzip, den Dingen auf den Grund zu gehen, möchte ich jedoch wissen, ob diese Schlussfolgerung auf alle Szenarien zutrifft und warum dieses Ergebnis auftritt.
Ich habe die relevanten Informationen online überprüft und sie lassen sich grob wie folgt zusammenfassen: Wenn die externe Tabelle klein und die interne Tabelle groß ist, ist „Exists“ anwendbar; wenn die externe Tabelle groß und die interne Tabelle klein ist, ist „IN“ anwendbar. Dann bin ich verwirrt, denn in meiner SQL-Anweisung hat die äußere Tabelle nur 10.000 Daten und die innere Tabelle 300.000 Daten. Laut Internet sollte die Effizienz von Exists höher sein als die von IN, aber mein Ergebnis ist genau das Gegenteil! !
„Keine Untersuchung, kein Rederecht“! Also begann ich, den tatsächlichen Ausführungsprozess von IN und Exists zu untersuchen und versuchte aus praktischer Sicht die Grundursache zu finden, was zu diesem Blogbeitrag führte.

Experimentelle Daten

Meine experimentellen Daten umfassen zwei Tabellen: t_author-Tabelle und t_poetry-Tabelle.
Der Datenumfang in der entsprechenden Tabelle:

t_author-Tabelle, 13355 Datensätze;
t_poetry-Tabelle, 289.917 Datensätze.

Die entsprechende Tabellenstruktur ist wie folgt:

Tabelle erstellen t_poetry (
id bigint(20) NICHT NULL AUTO_INCREMENT,
poetry_id bigint(20) NOT NULL COMMENT 'Poesie-ID',
poetry_name varchar(200) NOT NULL COMMENT 'Name des Gedichts',
<font color=red> author_id bigint(20) NOT NULL COMMENT 'Autor-ID'</font>
PRIMÄRSCHLÜSSEL ( id ),
EINZIGARTIGER SCHLÜSSEL pid_idx ( poetry_id ) MIT BTREE,
SCHLÜSSEL aid_idx ( author_id ) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4

Tabelle erstellen t_author (
id int(15) NICHT NULL AUTO_INCREMENT,
author_id bigint(20) NICHT NULL,</font>
author_name varchar(32) NICHT NULL,
dynasty varchar (16) NICHT NULL,
poetry_num int(8) NICHT NULL STANDARD '0'
PRIMÄRSCHLÜSSEL ( id ),
EINZIGARTIGER SCHLÜSSEL authorid_idx ( author_id ) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4

Ausführungsplananalyse im Ausführungsprozess

SQL-Beispiel: select * from tabA where tabA.x in (select x from tabB where y>0 );

Sein Ausführungsplan:
(1) Führen Sie die Unterabfrage der Tabelle tabB aus, um den Ergebnissatz B zu erhalten, und verwenden Sie den Index y der Tabelle tabB.
(2) Führen Sie eine Abfrage auf der Tabelle tabA aus. Die Abfragebedingung ist, dass tabA.x im Ergebnissatz B liegt. Der Index x der Tabelle tabA kann verwendet werden.

Existiert Ausführungsprozess

SQL-Beispiel: select from tabA where exists (select from tabB where y>0);

Sein Ausführungsplan:

(1) Rufen Sie zunächst alle Datensätze aus der Tabelle tabA ab.
(2) Ordnen Sie jedem Datensatz der Tabelle tabA die Tabelle tabB Zeile für Zeile zu, um zu ermitteln, ob die Unterabfrage der Tabelle tabB Daten zurückgibt. Versionen nach 5.5 verwenden Block Nested Loop.
(3) Wenn die Unterabfrage Daten zurückgibt, wird der aktuelle Datensatz von tabA an die Ergebnismenge zurückgegeben.
tabA entspricht dem Durchlaufen der gesamten Tabellendaten und tabB kann den Index verwenden.

Experimentelle Verfahren

Das Experiment analysiert die SQL-Anweisungen „IN“ und „Exists“ für denselben Ergebnissatz.
SQL-Anweisung mit IN:

Wählen Sie aus t_author ta, wobei author_id in
(Wählen Sie die Autor-ID aus t_poetry tp, wobei tp.poetry_id>3650);

SQL-Anweisung mit „Existiert“:

Wählen Sie aus t_author ta, wo vorhanden
(Wählen Sie * aus t_poetry tp, wobei tp.poetry_id>3650 und tp.author_id=ta.author_id);

Daten aus dem ersten Experiment

t_author-Tabelle, 13355 Datensätze; t_poetry-Tabelle, Unterabfrage-Filterergebnissatz, wobei poetry_id>293650, 121 Datensätze;

Ausführungsergebnisse

Die Verwendung von „exists“ dauert 0,94 Sekunden, die Verwendung von „in“ 0,03 Sekunden. „IN“ ist effizienter als „Exists“.

Ursachenanalyse

Der Ergebnisset der Unterabfrage der Tabelle t_poetry ist sehr klein, und beide können Indizes der Tabelle t_poetry verwenden, und der Verbrauch der Unterabfrage t_poetry ist grundsätzlich derselbe. Der Unterschied zwischen beiden besteht darin, dass bei Verwendung von „in“ die Tabelle „t_author“ den Index verwenden kann:

Bei Verwendung von exists wird die Tabelle t_author vollständig gescannt:

Wenn der Unterabfrage-Ergebnissatz klein ist, spiegelt sich die Abfragezeit hauptsächlich in der Durchquerung der t_author-Tabelle wider.

Zweite experimentelle Daten

t_author-Tabelle, 13.355 Datensätze; t_poetry-Tabelle, Unterabfrage-Filterergebnissatz, wobei poetry_id>3650, 287.838 Datensätze;

Ausführungszeit

Die Verwendung von exists dauert 0,12 Sekunden, die Verwendung von in 0,48 Sekunden. Exists ist effizienter als IN .

Ursachenanalyse

Die Indexverwendung der beiden Experimente stimmt mit der des ersten Experiments überein. Der einzige Unterschied ist die Größe des Unterabfragefilter-Ergebnissatzes. Die experimentellen Ergebnisse unterscheiden sich jedoch von denen des ersten. In diesem Fall ist der Ergebnissatz der Unterabfrage sehr groß. Schauen wir uns den MySQL-Abfrageplan an:
Bei Verwendung in sind sowohl die Tabellen t_author als auch t_poetry fast vollständige Tabellenscans, da der Ergebnissatz der Unterabfrage sehr groß ist. Zu diesem Zeitpunkt kann der Unterschied im Zeitaufwand zum Durchlaufen der Tabelle t_author im Hinblick auf die Gesamteffizienz vernachlässigt werden. Im Ausführungsplan gibt es noch eine Zeile <auto_key>. Im Fall eines vollständigen Tabellenscans wählt der MySQL-Optimierer auto_key zum Durchlaufen der Tabelle t_author:

Bei Verwendung von exists ändert die Änderung des Datenvolumens den Ausführungsplan nicht. Aufgrund des großen Unterabfrageergebnissatzes verwenden MySQL-Versionen nach 5.5 jedoch Block Nested-Loop (Block Nested Loop, Einführung eines Join-Puffer, ähnlich der Cache-Funktion), wenn die Abfrageergebnisse von exists abgeglichen werden, was sich erheblich auf die Abfrageeffizienz auswirkt, insbesondere wenn der Unterabfrageergebnissatz groß ist, kann dies die Abfrageabgleicheffizienz erheblich verbessern:

Experimentelle Schlussfolgerung

Basierend auf den beiden oben genannten Experimenten und ihren Ergebnissen können wir den Ausführungsprozess von IN und Exists klar verstehen und die anwendbaren Szenarien von IN und Exists zusammenfassen:

IN-Abfragen können Indizes sowohl für innere als auch für äußere Tabellen verwenden; Exists-Abfragen können Indizes nur für innere Tabellen verwenden. Wenn der Ergebnissatz der Unterabfrage groß und die äußere Tabelle klein ist, beginnt der Block Nested Loop von Exists eine Rolle zu spielen und gleicht den Mangel aus, dass die äußere Tabelle keine Indizes verwenden kann. Die Abfrageeffizienz wird dann besser sein als bei IN. Wenn der Unterabfrageergebnissatz klein und die externe Tabelle groß ist, ist der Optimierungseffekt der Blockverschachtelungsschleife von Exists nicht offensichtlich, und der Vorteil des externen Tabellenindex von IN spielt eine wichtige Rolle. In diesem Fall ist die Abfrageeffizienz von IN besser als die von Exists. Was online gesagt wird, ist ungenau. Tatsächlich bezieht sich die „Größe der Tabelle“ nicht auf die interne Tabelle und die externe Tabelle, sondern auf die externe Tabelle und den Unterabfrage-Ergebnissatz . Der letzte Punkt ist zugleich der wichtigste: Es gibt keine absolute Wahrheit auf der Welt. Das Wesen der Dinge zu erfassen und praktische Überprüfungen für verschiedene Szenarien durchzuführen, ist die zuverlässigste und effektivste Methode. Ergänzende Informationen zu während des Experiments festgestellten Problemen

Bei der Analyse der oben genannten Exist-Anweisungen unter verschiedenen Datensätzen habe ich festgestellt, dass der Zeitaufwand umso geringer ist, je größer der Datensatz ist, was sehr merkwürdig ist.
Die spezifischen Abfragebedingungen sind:

wobei tp.poetry_id>3650, dauert 0,13 S
wobei tp.poetry_id>293650, dauert 0,46 S

Mögliche Gründe: Je größer der Bedingungswert ist, desto später erfolgt die Abfrage, desto mehr Datensätze müssen durchlaufen werden, was zu einem höheren Zeitaufwand führt. Diese Erklärung muss noch weiter überprüft werden.