Warum SQL Bind-Variablen für die Performance wichtig sind

© Shutterstock.com / Paolo De Gasperis
SQL-Bindings können helfen, die Performance von dynamischem SQL ganz erheblich zu verbessern. Unser Autor Lukas Eder erklärt anhand eines Beispiels, wie genau das funktionieren kann. Außerdem zeigt er, worauf man achten muss und welche Unterschiede es zwischen Tools wie jOOQ, Hibernate und JDBC bzw. JDBC-Wrappern gibt.
Dieser Artikel wurde zuerst auf jooq.org veröffentlicht, einem Blog, das sich mit allen Fragen rund um Open Source, Java and Software-Entwicklung aus der Perspektive von jOOQ befasst.
Ein häufiges Problem mit dynamischem SQL ist das Parsen der Performance in der Produktion. Was die Sache noch schlimmer macht, ist, dass viele Entwickler keinen Zugang zu Produktionsumgebungen haben, sodass sie sich des Problems nicht bewusst sind (auch wenn es an diesem Thema nichts Neues gibt). Was genau ist das Problem?
Ausführungsplan-Caches
Die meisten Datenbankanbieter verfügen heutzutage über einen Ausführungsplan-Cache (Oracle nennt ihn Cursor-Cache), in dem zuvor analysierte SQL-Anweisungen gespeichert sind und deren Ausführungsplan (und -pläne) zur Wiederverwendung zwischengespeichert werden. Dies ist der Hauptgrund, warum Bind-Variablen so wichtig sind (der andere Grund ist die Verhinderung von SQL-Injektionen). Durch die Verwendung von Bind-Variablen können wir sicherstellen, dass die Datenbank eine identische SQL-Anweisung aus einer früheren Ausführung leicht erkennt und den zuvor gefundenen Ausführungsplan erneut ausführen kann. Dies ist in der Tat eines meiner Lieblingsthemen aus meinem SQL-Training. Mal sehen, was in verschiedenen Datenbanken passiert, wenn wir die folgenden Abfragen durchführen:
-- First, run them with "inline values" or "constant literals" SELECT first_name, last_name FROM actor WHERE actor_id = 1; SELECT first_name, last_name FROM actor WHERE actor_id = 2; -- Then, run the same queries again with bind values SELECT first_name, last_name FROM actor WHERE actor_id = ?; SELECT first_name, last_name FROM actor WHERE actor_id = ?;
Es spielt keine Rolle, ob die Abfragen von JDBC, jOOQ, Hibernate oder der prozeduralen Sprache in der Datenbank ausgeführt werden, z.B. PL/SQL, T-SQL, pgplsql. Das Ergebnis ist immer das gleiche.
Führen wir ein Beispiel aus
Ich werde die folgenden Beispiele nur mit Oracle ausführen. Andere Datenbanken verhalten sich ähnlich.
Wir führen zunächst ein Skript aus, das die obigen Abfragen enthält, und eine Abfrage, um alle Ausführungspläne abzurufen:
SELECT first_name, last_name FROM actor WHERE actor_id = 1; SELECT first_name, last_name FROM actor WHERE actor_id = 2; SET SERVEROUTPUT ON DECLARE v_first_name actor.first_name%TYPE; v_last_name actor.last_name%TYPE; BEGIN FOR i IN 1 .. 2 LOOP SELECT first_name, last_name INTO v_first_name, v_last_name FROM actor WHERE actor_id = i; dbms_output.put_line(v_first_name || ' ' || v_last_name); END LOOP; END; / SELECT s.sql_id, p.* FROM v$sql s, TABLE ( dbms_xplan.display_cursor ( s.sql_id, s.child_number, 'ALLSTATS LAST' ) ) p WHERE lower(s.sql_text) LIKE '%actor_id = %';
Der Output ist:
SQL_ID 90rk04nhr45yz, child number 0 ------------------------------------- SELECT FIRST_NAME, LAST_NAME FROM ACTOR WHERE ACTOR_ID = :B1 Plan hash value: 457831946 --------------------------------------------------------- | Id | Operation | Name | E-Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | |* 2 | INDEX UNIQUE SCAN | PK_ACTOR | 1 | --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ACTOR_ID"=:B1) SQL_ID 283s8m524c9rk, child number 0 ------------------------------------- SELECT first_name, last_name FROM actor WHERE actor_id = 2 Plan hash value: 457831946 --------------------------------------------------------- | Id | Operation | Name | E-Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | |* 2 | INDEX UNIQUE SCAN | PK_ACTOR | 1 | --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ACTOR_ID"=2) SQL_ID 3mks715670mqw, child number 0 ------------------------------------- SELECT first_name, last_name FROM actor WHERE actor_id = 1 Plan hash value: 457831946 --------------------------------------------------------- | Id | Operation | Name | E-Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | |* 2 | INDEX UNIQUE SCAN | PK_ACTOR | 1 | --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ACTOR_ID"=1)
Die Pläne sind immer die gleichen und da wir auf Primärschlüsselwerte zugreifen, bekommen wir immer die gleichen Kardinalitäten, sodass bei jeder einzelnen Ausführung nichts falsch zu sein scheint. Aber die Prädikatinformationen sind etwas anders. Wenn man nach einem konstanten Wert fragt, dann enthält das Prädikat diesen Wert genau dort, während wir bei der Bind-Variablen nicht wissen, was der Prädikatswert ist, aus dem Plan. Dies ist durchaus zu erwarten, da wir diesen Plan für beide Ausführungen der Query wiederverwenden wollen.
Mit einer weiteren Abfrage können wir die Anzahl der Ausführungen jeder Anweisung sehen:
SELECT sql_id, sql_text, executions FROM v$sql WHERE sql_id IN ( '90rk04nhr45yz', '283s8m524c9rk', '3mks715670mqw' );
SQL_ID SQL_TEXT EXECUTIONS ------------------------------------------------------------------------------------------ 90rk04nhr45yz SELECT FIRST_NAME, LAST_NAME FROM ACTOR WHERE ACTOR_ID = :B1 2 283s8m524c9rk SELECT first_name, last_name FROM actor WHERE actor_id = 2 1 3mks715670mqw SELECT first_name, last_name FROM actor WHERE actor_id = 1 1
Hier wird es interessanter. Im zweiten Fall, in dem wir eine Bind-Variable verwendet haben (die von PL/SQL automatisch generiert wurde), konnten wir die Anweisung wiederverwenden, ihren Plan zwischenspeichern und zweimal ausführen.
Meh, ist das wichtig?
Es ist sogar aus zwei Gründen wichtig:
- Durchführung der einzelnen Ausführungen
- Leistung Ihres gesamten Systems
Wie sich dies auf die einzelnen Ausführungen auswirkt
Es scheint sehr offensichtlich zu sein, dass es einen leichten Overhead für die Cache-Pflege gibt, wenn man in der Lage ist, etwas zwischenzuspeichern. Jedenfalls verglichen mit dem Gewinn, die Arbeit nicht machen zu müssen, deren Ergebnis zwischengespeichert wird. Hier geht es darum, die SQL-Anweisung zu analysieren und einen Ausführungsplan dafür zu erstellen. Auch wenn der Plan trivial ist, wie in den obigen Beispielen, ist die Berechnung dieses Plans mit Gemeinkosten verbunden.
Dieser Overhead lässt sich am besten in einem Benchmark darstellen, eine Technik, die ich auch in meinem SQL-Training anwende:
SET SERVEROUTPUT ON -- Don't run these on production -- But on your development environment, this guarantees clean caches ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE; CREATE TABLE results ( run NUMBER(2), stmt NUMBER(2), elapsed NUMBER ); DECLARE v_ts TIMESTAMP WITH TIME ZONE; v_repeat CONSTANT NUMBER := 2000; v_first_name actor.first_name%TYPE; v_last_name actor.last_name%TYPE; BEGIN -- Repeat whole benchmark several times to avoid warmup penalty FOR r IN 1..5 LOOP v_ts := SYSTIMESTAMP; FOR i IN 1..v_repeat LOOP BEGIN EXECUTE IMMEDIATE ' SELECT first_name, last_name FROM actor WHERE actor_id = ' || i -- Just fixing a syntax highlighting bug of this blog ' INTO v_first_name, v_last_name; EXCEPTION -- Please forgive me WHEN OTHERS THEN NULL; END; END LOOP; INSERT INTO results VALUES ( r, 1, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE); v_ts := SYSTIMESTAMP; FOR i IN 1..v_repeat LOOP BEGIN EXECUTE IMMEDIATE ' SELECT first_name, last_name FROM actor WHERE actor_id = :i' INTO v_first_name, v_last_name USING i; EXCEPTION -- Please forgive me WHEN OTHERS THEN NULL; END; END LOOP; INSERT INTO results VALUES ( r, 2, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE); END LOOP; FOR rec IN ( SELECT run, stmt, CAST(elapsed / MIN(elapsed) OVER() AS NUMBER(10, 5)) ratio FROM results ) LOOP dbms_output.put_line('Run ' || rec.run || ', Statement ' || rec.stmt || ' : ' || rec.ratio); END LOOP; END; / DROP TABLE results;
Wir veröffentlichen keine tatsächlichen Ausführungszeiten, um die Lizenzbeschränkungen für Benchmark-Publikationen einzuhalten, daher vergleichen wir jede Ausführung nur mit der schnellsten Ausführung. Dies ist das Ergebnis der obigen Ausführungen:
Run 1, Statement 1 : 83.39893 Run 1, Statement 2 : 1.1685 Run 2, Statement 1 : 3.02697 Run 2, Statement 2 : 1 Run 3, Statement 1 : 2.72028 Run 3, Statement 2 : 1.03996 Run 4, Statement 1 : 2.70929 Run 4, Statement 2 : 1.00866 Run 5, Statement 1 : 2.71895 Run 5, Statement 2 : 1.02198
Wir können sehen, dass die SQL-Version, die eine Bind-Variable verwendet, 2,5-mal so schnell ist wie die, die die Bind-Variable nicht verwendet. Dieser Overhead ist bei trivialen Abfragen sehr wichtig – bei komplexeren Abfragen, bei denen die Ausführung selbst mehr Zeit in Anspruch nimmt, mag das weniger wichtig sein als beim Parsen. Aber es sollte klar sein, dass der Overhead ein Preis ist, den wir nicht zahlen wollen. Wir wollen, dass die Abfrage und ihr Plan zwischengespeichert werden!
Bemerkenswert ist auch, dass die allererste Ausführung des Benchmarks einen sehr hohen Overhead hat, da alle 2000 Abfragen zum ersten Mal aufgetreten sind, bevor sie für den zweiten Lauf zwischengespeichert werden. Das ist ein Preis, den wir nur beim ersten Lauf zahlen.
Wie sich dies auf das gesamte System auswirkt
Nicht nur einzelne Abfrageausführungen leiden, sondern auch Ihr gesamtes System. Nachdem ich den Benchmark einige Male ausgeführt habe, sind dies die Ausführungsstatistiken, die ich aus dem Oracle-Cursor-Cache erhalte:
SELECT count(*), avg(executions), min(executions), max(executions) FROM v$sql WHERE lower(sql_text) LIKE '%actor_id = %' AND sql_text NOT LIKE '%v$sql%';
Ergibt:
count avg min max 2001 9.9950 5 10000
Momentan befinden sich 2000 Abfragen in meinem Cache. Diejenige, die 10000 Mal ausgeführt wurde (Benchmark wurde 5 Mal wiederholt und es gab 2000 Ausführungen der Query pro Lauf), und 2000 Queries, die 5 Mal ausgeführt wurde (Benchmark wurde 5 Mal wiederholt).
Wenn wir stattdessen die Abfrage 20000 Mal ausführen (und nicht vergessen, dass die Abfrage der gefilterten ACTOR_ID
entspricht), dann wird das Ergebnis ganz anders aussehen!
Run 1, Statement 1 : 86.85862 Run 1, Statement 2 : 1.13546 Run 2, Statement 1 : 78.39842 Run 2, Statement 2 : 1.01298 Run 3, Statement 1 : 72.45254 Run 3, Statement 2 : 1 Run 4, Statement 1 : 73.78357 Run 4, Statement 2 : 2.24365 Run 5, Statement 1 : 84.89842 Run 5, Statement 2 : 1.143
Oh mein Gott! Was ist passiert? Überprüfen wir noch einmal die Cursor-Cache-Statistiken:
SELECT count(*), avg(executions), min(executions), max(executions) FROM v$sql WHERE lower(sql_text) LIKE '%actor_id = %' AND sql_text NOT LIKE '%v$sql%';
Ergibt:
count avg min max 15738 3.4144 1 20000
Das ist ein ganz anderes Ergebnis. Wir haben nicht alle unsere 20000 Abfragen im Cursor-Cache, nur einige davon. Dies bedeutet, dass einige Anweisungen aus dem Cache gelöscht wurden, um Platz für neue zu schaffen (was für jeden Cache vernünftig ist).
Aber sie zu bereinigen ist auch problematisch, denn so wie der Benchmark entworfen wurde, werden sie im zweiten, dritten, vierten und fünften Lauf wieder auftauchen, also hätten wir sie im Cache behalten sollen. Und da wir jede Abfrage gleich oft ausführen, gab es wirklich keine Möglichkeit, eine „vernünftigere“ (d.h. seltenere) Abfrage zu identifizieren.
Ressourcen in einem System sind immer begrenzt, ebenso wie die Größe des Cursor-Cache. Je mehr verschiedene Abfragen wir in einem System ausführen, desto weniger können sie vom Cursor-Cache profitieren.
Dies ist kein Problem für selten ausgeführte Abfragen, einschließlich Berichte, Analysen oder einige spezielle Abfragen, die nur von sehr wenigen Benutzern ausgeführt werden. Aber die Anfragen, die ständig ausgeführt werden, sollten immer zwischengespeichert werden.
Ich kann nicht genug betonen, wie ernst das sein kann
Im obigen Fall explodierte eine einzelne Abfrage im Cursor-Cache in 20000 Abfragen, wodurch viele weitere nützliche Abfragen aus dem Cache geschoben wurden. Dadurch wird nicht nur die Ausführung dieser speziellen Abfrage verlangsamt, sondern es werden auch Tonnen von völlig unabhängigen Abfragen aus dem Cache entfernt, wodurch das gesamte System um ähnliche Faktoren verlangsamt wird. Wenn jeder drastisch verlangsamt wird, beginnt jeder, sich in die Warteschlange zu stellen, um seine SQL-Abfragen analysieren zu lassen, und das kann (im schlimmsten Fall) den gesamten Server zum Absturz bringen!
Workaround
Einige Datenbanken unterstützen das Parsen der konstanten Literale, um Variablen zu binden. In Oracle können CURSOR_SHARING = FORCE
als „Quick Fix“ angegeben werden. Im SQL Server nennt man das forced parametrization
.
Dieser Ansatz hat jedoch seine eigenen Grenzen und seinen eigenen Overhead, da dies bedeutet, dass jedes Mal zusätzliche Parsing-Arbeiten durchgeführt werden müssen, um konstante Literale zu erkennen und durch Bind-Variablen zu ersetzen. Dieser Overhead gilt dann für alle Anfragen!
Fazit
Bind-Variablen sind sehr wichtig für die SQL Performance. Nach diversen Schulungen für die Verwendung von SQL Injection Reasons (was bereits ein Fortschritt ist), haben wir jetzt gesehen, wie wichtig sie auch aus Performance-Gründen sind.
Wenn man keine Bind-Variable für Werte wie IDs, Zeitstempel, Namen oder alles, was gleichmäßig verteilt ist und viele Werte in Ihrer Spalte hat, verwendet, wird das obige Problem auftreten. Die Ausnahme sind Bind-Variablen für Spalten mit nur sehr wenigen eindeutigen Werten (wie True/False-Flags, Codes, die einen bestimmten Zustand kodieren, usw.), bei denen ein konstantes Literal eine sinnvolle Option sein kann.
Aber Bind-Variablen sollten immer die erste Wahl sein. Wenn ein Client-seitiges Tool wie jOOQ oder Hibernate verwendt wird, sind Bind-Variablen in der Regel die Standardeinstellung, und alles ist gut. Wenn eine gespeicherte prozedurale Sprache wie PL/SQL oder T-SQL verwendet wird, werden Bind-Variablen automatisch generiert und es ist ebenfalls alles gut. Aber wenn JDBC oder einen JDBC-Wrapper wie Spring’s JdbcTemplates oder eine andere String-basierte API wie JPA’s native Abfrage-API zum Einsatz kommen, dann muss man sich bei jeder Variableneingabe explizit um die Verwendung von Bind-Variablen kümmern.
Hinterlasse einen Kommentar