• Sie befinden sich hier: Startseite > SQL-Backstube > LIKE und (keine) Performance

Die SQL-Backstube

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

02.04.2011: LIKE und (keine) Performance

SQL-Abfragen werden oft durch Query Builder, Generatoren und flexible Abfragemasken zusammen gebaut. Technisch sind sie zwar okay und liefern auch das gewünschte Ergebnis, aber performant sind sie häufig gar nicht.

Viele Skriptsprachen basieren auf (fast) typenlosen Variablen. Dass dies aber der Datenbank nicht unbedingt gefällt merkt man dann, wenn die Datenmenge ansteigt und der Datenbankserver in die Knie geht.

Getestet mit folgenden Datenbanken:

* IBM DB2 9.1
* MySQL 5.1.55
* ORACLE 10g 10.2 EE

Tabellen und Testdaten

Listing 1:

1. CREATE TABLE kunde (
2. kunde_id INT NOT NULL,
3. ...
4. code VARCHAR(40) NOT NULL,
5. PRIMARY KEY (kunde_id)
6. );
7. -- 1.000.000 Datensätze --

Als Testdaten dienen uns diesmal ca. 1 Million generierte Adressen. Einen PRIMARY KEY gibt es auch und zwar auf die numerische Spalte KUNDE_ID. Wird ein PRIMARY KEY definiert, legen die meisten Datenbanken automatisch einen UNIQUE INDEX auf diese Spalte(n) an. Bei einer Abfrage auf die Spalte KUNDE_ID kann die Datenbank dann zumeist das Ergebnis extrem schnell liefern. Wenn da nicht manchmal LIKE ins Spiel käme.

Listing 2:

1. SELECT MAX(code) FROM kunde
2. WHERE kunde_id LIKE '120';
3. +--------------------------------------+
4. | MAX(code) |
5. +--------------------------------------+
6. | 39fa18fd-5a31-11e0-a69e-19e3281d1825 |
7. +--------------------------------------+
8. 1 row in set (0.48 sec)

"Nur" eine halbe Sekunde, um den Kunden mit der ID 120 zu lesen. Ist das schnell? Nein, dies ist extrem langsam. Zum Vergleich folgende Abfrage (Listing 3:)

Listing 3:

1. SELECT MAX(code) FROM kunde
2. WHERE kunde_id = '120';
3. +--------------------------------------+
4. | MAX(code) |
5. +--------------------------------------+
6. | 39fa18fd-5a31-11e0-a69e-19e3281d1825 |
7. +--------------------------------------+
8. 1 row in set (0.00 sec)
9. -- Profiling meldet 0,005 Sekunden --

Diese Abfrage (Listing 3:) ist ca. 100x schneller! und dabei ist nur LIKE nur durch ein = Zeichen ersetzt worden. LIKE ist bei dieser Abfrage ja auch eigentlich gar nicht notwendig. Wirklich korrekt wäre es aber erst, wenn eine numerische Spalte auch mit einem numerischen Wert verglichen wird. Das Typecasting wäre nicht notwendig und kann der Datenbank erspart werden. Hier (Listing 4:) bringt es aber keinen messbaren Unterschied zu Listing 3.

Listing 4:

1. SELECT MAX(code) FROM kunde
2. WHERE kunde_id = 120;
3. +--------------------------------------+
4. | MAX(code) |
5. +--------------------------------------+
6. | 39fa18fd-5a31-11e0-a69e-19e3281d1825 |
7. +--------------------------------------+
8. 1 row in set (0.00 sec)
9. -- Profiling meldet 0,005 Sekunden --

Warum klappt dies mit LIKE eigentlich nicht?

Listing 5:

1. EXPLAIN
2. SELECT MAX(code) FROM kunde
3. WHERE kunde_id LIKE '120';
4. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
5. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
6. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
7. | 1 | SIMPLE | kunde | ALL | PRIMARY | NULL | NULL | NULL | 1000711 | Using where |
8. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
9. 1 row in set (0.00 sec)

EXPLAIN zeigt sehr drastisch, dass MySQL keinen INDEX nutzt, um die Daten zu finden, sondern die komplette Tabelle mit 1 Million Datensätze durchsucht. 0,46 Sekunden sind zwar schon schnell, aber es geht normalerweise noch viel schneller.

Listing 6:

1. EXPLAIN
2. SELECT MAX(code) FROM kunde
3. WHERE kunde_id = '120';
4. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
5. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
6. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
7. | 1 | SIMPLE | kunde | const | PRIMARY | PRIMARY | 4 | const | 1 | |
8. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
9. 1 row in set (0.00 sec)
11. EXPLAIN
12. SELECT MAX(code) FROM kunde
13. WHERE kunde_id = 120;
14. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
15. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
16. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
17. | 1 | SIMPLE | kunde | const | PRIMARY | PRIMARY | 4 | const | 1 | |
18. +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
19. 1 row in set (0.00 sec)

Bei diesen beiden SQL-Abfragen kann MySQL den PRIMARY Index nutzen und direkt auf die Daten zugreifen. Die Daten werden so ca. 100x schneller als mit LIKE geliefert. Während MySQL und ORACLE relativ tolerant bei unterschiedlichen Datentypen sind, liefert DB2 nur bei der Abfrage (Listing 4:) ein Ergebnis. Bei den anderen (Listing 2: und Listing 3:) wird bei DB2 ein SQL Fehler gemeldet:

SQL0440N Es wurde keine berechtigte Routine "LIKE" des Typs "FUNCTION" mit kompatiblen Argumenten gefunden.

SQL0401N Die Datentypen der Operanden für die Operation "=" sind nicht kompatibel. SQLSTATE=42818

Ergebnis

Belüge nicht den SQL-Parser und benütze LIKE nur, wenn es auch wirklich sinnvoll ist. Wird diese einfache Regel berücksichtigt freut sich jede Datenbank, wenn nicht, freut sich der Hardwareverkäufer.

https://twiedmann.de