In diesem Schritt lernen Sie, wie Sie die Leistung verschiedener Arten von Subqueries in MySQL vergleichen. Das Verständnis der Leistungseigenschaften von Subqueries ist entscheidend für das Schreiben effizienter SQL-Abfragen, insbesondere bei der Arbeit mit großen Datensätzen.
Die Leistung einer Subquery kann von mehreren Faktoren beeinflusst werden, darunter:
Stellen Sie sicher, dass Sie die vorherigen Schritte abgeschlossen haben und die Datenbank labdb sowie die Tabellen customers und orders mit Daten gefüllt sind. Um den Leistungsvergleich aussagekräftiger zu gestalten, fügen wir der Tabelle orders weitere Daten hinzu.
-
Weitere Daten zur Tabelle orders hinzufügen:
Um den Leistungsvergleich realistischer zu gestalten, fügen wir der Tabelle orders eine beträchtliche Menge an Daten hinzu. Wir fügen für jeden Kunden 1000 Bestellungen über eine gespeicherte Prozedur ein. Führen Sie die folgenden SQL-Befehle in der MySQL-Eingabeaufforderung aus:
DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000 DO
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;
Erklärung:
- Dieses SQL-Skript erstellt eine gespeicherte Prozedur namens
insert_many_orders.
- Die Prozedur fügt 1000 Bestellungen für jeden der vier Kunden in die Tabelle
orders ein.
- Nach dem Einfügen der Daten wird die Prozedur gelöscht.
-
Abfrage mit IN:
Führen Sie die folgende Abfrage mit IN aus, um alle Kunden zu finden, die mindestens eine Bestellung getätigt haben:
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-
Abfrageausführungsplan mit EXPLAIN analysieren:
Verwenden Sie vor der Ausführung der Abfrage den Befehl EXPLAIN, um den Abfrageausführungsplan zu analysieren. Dies gibt Ihnen Einblicke, wie MySQL die Abfrage auszuführen plant, und identifiziert potenzielle Leistungsengpässe. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
Die Ausgabe von EXPLAIN zeigt Ihnen die zugegriffenen Tabellen, die verwendeten Indizes (falls vorhanden) und die Reihenfolge, in der die Operationen ausgeführt werden. Achten Sie auf die Spalte type, die die Art des verwendeten Joins oder der Zugriffsmethode angibt.
-
Abfrage mit EXISTS:
Führen Sie die folgende Abfrage mit EXISTS aus, um alle Kunden zu finden, die mindestens eine Bestellung getätigt haben:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
-
Abfrageausführungsplan mit EXPLAIN analysieren:
Verwenden Sie den Befehl EXPLAIN, um den Abfrageausführungsplan für die EXISTS-Abfrage zu analysieren. Führen Sie den folgenden SQL-Befehl in der MySQL-Eingabeaufforderung aus:
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Vergleichen Sie den Ausführungsplan mit dem der IN-Abfrage. Gibt es Unterschiede bei den zugegriffenen Tabellen, den verwendeten Indizes oder den Zugriffsmethoden?
-
Beobachtungen:
Im Allgemeinen tendiert EXISTS dazu, besser zu performen als IN, wenn die Subquery eine große Anzahl von Zeilen zurückgibt. Dies liegt daran, dass IN die Werte der äußeren Abfrage mit allen von der Subquery zurückgegebenen Werten vergleichen muss, während EXISTS stoppt, sobald es eine Übereinstimmung findet. Die tatsächliche Leistung kann jedoch je nach spezifischer Abfrage, den Daten und dem Datenbanksystem variieren. Sie können die Funktion BENCHMARK() (wie im Originaldokument gezeigt) verwenden, um eine genauere Messung der Ausführungszeit zu erhalten, aber für dieses Labor liefert die Analyse der EXPLAIN-Ausgabe ausreichende Einblicke in die Abfragepläne.
-
Bereinigung (Optional):
Wenn Sie die Datenbank und die Tabellen bereinigen möchten, können Sie die folgenden Befehle in der MySQL-Eingabeaufforderung ausführen:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
Wenn Sie alle Schritte abgeschlossen haben, können Sie den MySQL-Client verlassen, indem Sie Folgendes eingeben:
exit
