• Sie befinden sich hier: Startseite > SQL-Backstube > Performance von IN und SUB-Select

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

23.04.2011: Performance von IN und SUB-Select

Er wird immer noch häufig verwendet - der SUB-Select - in einer Klammer und schlicht mit einem IN kombiniert. Er ist einfach, verständlich und schnell programmiert. Dass aber eine schnell programmierte Lösung selten wirklich schnell (performant) ist, zeigt sich wenn die Datenmenge anschwillt.

Getestet mit folgenden Datenbanken:

* IBM DB2 9.1
* MySQL 5.6.2-m5
* ORACLE 10g 10.2 EE

Tabellen und Testdaten

Listing 1:

1. /* Tabelle mit ca. 1.000.000 Datensätzen */
2. CREATE TABLE geodaten(
3. geodaten_id INT(11) NOT NULL,
4. latitude INT(10) UNSIGNED NOT NULL,
5. longitude INT(10) UNSIGNED NOT NULL,
6. PRIMARY KEY (geodaten_id),
7. INDEX idx_lat_long_id (latitude, longitude, geodaten_id),
8. INDEX idx_long_lat_id (longitude, latitude, geodaten_id)
9. ) ENGINE = INNODB;
11. /* diese vier IDs existieren auch in der Tabelle geodaten */
12. CREATE TABLE id_check (
13. id INT NOT NULL
14. );
16. INSERT INTO id_check VALUES
17. ( 100 ), ( 10000 ), ( 200 ), ( 20000 );

Die Tabelle GEODATEN enthält ca. eine Million Datensätze. Einen PRIMARY KEY gibt es auch und zwar auf die numerische Spalte GEODATEN_ID. Mit Hilfe der Tabelle ID_CHECK, sollen vier IDs gesucht werden, die in der Tabelle GEODATEN auch vorhanden sind.

Abfrage 1 mit Konstanten

Listing 2:

1. SELECT geodaten_id
2. FROM geodaten
3. WHERE geodaten_id IN ( 100,10000,200,20000);

Die Abfrage 1 (Listing 2:) dauert laut MySQL Profile jetzt 0,00030 Sekunden. Klein, klar, übersichtlich und schnell. Warum? Die Datenbank-Engine kann hier auf Konstanten zugreifen und muss keine weitere dynamische Datenmenge (Tabellen) dazumischen.

Listing 3:

1. EXPLAIN
2. SELECT geodaten_id
3. FROM geodaten
4. WHERE geodaten_id IN ( 100,10000,200,20000);
5. +----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
7. +----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
8. | 1 | SIMPLE | geodaten | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where; Using index |
9. +----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+

Der EXPLAIN zeigt deutlich: MySQL greift mit Hilfe eines Range und des Primary Key flott und direkt auf die Daten zu. Insgesamt werden nur vier Rows verarbeitet.

Abfrage 2 mit SUB-Select

Listing 4:

1. SELECT geodaten_id
2. FROM geodaten
3. WHERE geodaten_id IN (SELECT id FROM id_check);

Diese Abfrage dauert laut MySQL Profile nun 3,48 Sekunden und ist damit - mit der identischen Datenmenge - fast 10000 mal langsamer!

Listing 5:

1. EXPLAIN
2. SELECT geodaten_id
3. FROM geodaten
4. WHERE geodaten_id IN (SELECT id FROM id_check);
5. +----+--------------------+----------+-------+---------------+---------+---------+------+---------+-----------------------------+
6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
7. +----+--------------------+----------+-------+---------------+---------+---------+------+---------+-----------------------------+
8. | 1 | PRIMARY | geodaten | index | NULL | PRIMARY | 4 | NULL | 1000395 | Using where; Using index |
9. | 2 | DEPENDENT SUBQUERY | id_check | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
10. +----+--------------------+----------+-------+---------------+---------+---------+------+---------+-----------------------------+

Die SQL-Abfrage 2 liefert ebenfalls alle Datensätzen, deren ID Werte in der Tabelle existieren. Klein, klar, übersichtlich und gar NICHT mehr schnell. Warum? Die Datenbank-Engine muss nun dynamisch die IDs aus der Tabelle ID_CHECK holen und zwar für jeden Datensatz der Tabelle GEODATEN. Ist das intelligent vom Datenbank-Optimizer? Nein, ganz und gar nicht.

Abfrage 3 mit SUB-Select und vertauschten Tabellen

Listing 6:

1. SELECT id
2. FROM id_check
3. WHERE id IN (SELECT geodaten_id FROM geodaten);

Die SQL-Abfrage 3 (Listing 6:) liefert in 0,00027 Sekunden alle Datensätzen, deren ID Werte in der Tabelle existieren. Klein, klar, übersichtlich und WIEDER schnell. Warum? Die Datenbank-Engine liest dynamisch die IDs aus der Tabelle GEODATEN und zwar für jeden Datensatz der kleinen Tabelle ID_CHECK. Der Datenbank-Optimizer findet nun wieder auf den "richtigen" Pfad zurück, obwohl eigentlich die Ergebnismenge identisch geblieben ist.

Listing 7:

1. EXPLAIN
2. SELECT id
3. FROM id_check
4. WHERE id IN (SELECT geodaten_id FROM geodaten);
5. +----+--------------------+----------+-----------------+---------------+---------+---------+-------------+------+-------------+
6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
7. +----+--------------------+----------+-----------------+---------------+---------+---------+-------------+------+-------------+
8. | 1 | PRIMARY | id_check | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
9. | 2 | DEPENDENT SUBQUERY | geodaten | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
10. +----+--------------------+----------+-----------------+---------------+---------+---------+-------------+------+-------------+

Seitenblick auf ORACLE und IBM DB2

ORACLE verhält sich ähnlich wie MySQL. Die Abfrage 2 benötigt ca. 100x so viele "Costs" wie die Abfrage 3. Gänzlich anders bei IBM DB2. Der Query Optimizer erkennt das Problem und schreibt die Abfrage 2 und 3 jeweils in einen JOIN um. Bei IBM DB2 braucht der Query 2 nur etwa 2x so viele "Timerons" wie die Abfrage 3.

Abfrage 4 - SUB-Select ReWrite und JOIN

Wenn der Query-Optimizer nicht schlau genug ist, muss eben wieder der Mensch mitdenken. Grundsätzlich gilt deshalb: Einen SUB-Select wenn möglich vermeiden und das Ganze in einen JOIN umformulieren.

Listing 8:

1. SELECT g.geodaten_id
2. FROM geodaten g
3. JOIN id_check c
4. ON c.id = g.geodaten_id;

Die Abfrage 4 dauert laut MySQL Profile ist jetzt wieder schnell und liefert nach 0,000545 Sekunden das Ergebnis.

Listing 9:

1. EXPLAIN
2. SELECT g.geodaten_id
3. FROM geodaten g
4. JOIN id_check c
5. ON c.id = g.geodaten_id;
6. +----+-------------+-------+-----------------+---------------+---------+---------+-------------+------+-------------+
7. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
8. +----+-------------+-------+-----------------+---------------+---------+---------+-------------+------+-------------+
9. | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 4 | |
10. | 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | sample.c.id | 1 | Using index |
11. +----+-------------+-------+-----------------+---------------+---------+---------+-------------+------+-------------+

Ergebnis

SUB-Selects waren und sind ein Problem. Es bedarf schon eines intelligenten Query-Optimizers, um einen SUB-Select automatisch umzuschreiben. Am einfachsten ist es, wenn der "zwei-beinige" Abfrageperfektionierer seinen Gehirnschmalz mit einbringt.

https://twiedmann.de