Dieser Abschnitt behandelt ein Thema, das in den meisten SQL-Büchern fehlt: parametrisierte Abfragen und Bind-Parameter.
Bind-Parameter – auch dynamische Parameter oder Bind-Variablen genannt – sind ein alternativer Weg, Daten an Datenbanken zu übergeben. Anstatt die Daten direkt in die SQL-Anweisung zu schreiben, benutzt man Platzhalter wie zum Beispiel ?
, :name
oder @name
. Die tatsächlichen Werte werden der Datenbank über einen eigenen API-Aufruf zur Verfügung gestellt.
Bei manuellen Abfragen spricht nichts dagegen, die Werte direkt in die SQL-Anweisung zu schreiben. Bei Programmen gibt es aber zwei gute Gründe, die für die Verwendung von Bind-Parametern sprechen:
- Sicherheit
Bind-Variablen sind der beste Weg SQL-Injections zu verhindern.
- Performance
Datenbanken mit einem Ausführungsplan-Cache wie SQL Server oder die Oracle Datenbank können Ausführungspläne wiederverwenden, wenn eine SQL-Anweisung mehrfach ausgeführt wird. Dadurch kann der Aufwand, einen Ausführungsplan zu erstellen, vermieden werden. Dieser Mechanismus funktioniert aber nur, wenn genau dieselbe SQL-Anweisung wiederholt ausgeführt wird. Stehen andere Werte in der SQL-Anweisung, erstellt die Datenbank den Ausführungsplan nochmals.
Bei der Verwendung von Bind-Parametern stehen nicht die eigentlichen Werte, sondern nur Platzhalter in der SQL-Anweisung. Der SQL-String ändert sich durch die Werte also nicht. Dadurch ist es für die Datenbank dieselbe Anweisung, auch wenn sie mit verschieden Werten ausgeführt wird.
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
Aber natürlich gibt es auch Ausnahmen, wenn die betroffene Datenmenge, wie im folgenden Beispiel, stark von den eigentlichen Werten abhängig ist.
SELECT first_name, last_name
FROM employees
WHERE subsidiary_id = 20
Bei einer kleinen Zweigstelle liefert ein Indexzugriff die beste Performance. Wenn man aber eine größere Zweigstelle betrachtet, kann es besser sein, die ganze Tabelle zu lesen:
SELECT first_name, last_name
FROM employees
WHERE subsidiary_id = 30
In diesem Fall erfüllt das Histogramm für die SUBSIDIARY_ID
-Spalte seinen Zweck. Der Optimizer verwendet es, um die Häufigkeit der Werte, die direkt in der SQL-Anweisung angegeben sind, zu ermitteln. Dadurch erhält er für die beiden Abfragen unterschiedliche Zeilenschätzungen. Das führt in weiterer Folge dazu, dass die Cost-Werte der beiden Plan-Varianten unterschiedliche sind. Am Schluss wählt der Optimizer den Ausführungsplan mit den geringsten Kosten. Das ist bei der kleineren Zweigstelle der Ausführungsplan mit dem Indexzugriff.
Der Cost-Wert der Operation TABLE ACCESS BY INDEX ROWID
ist aber sehr stark von der Anzahl der Zeilen abhängig. Da die größere Zweigstelle circa zehn Mal so groß ist, steigt der Cost-Wert um diesen Faktor an. Dadurch steigen die Gesamtkosten sogar über die Kosten eines Full-Table-Scans. Wenn der Optimizer am Schluss den Ausführungsplan mit den geringsten Kosten auswählt, ist das bei der großen Zweigstelle also der Ausführungsplan ohne Indexzugriff.
Bei der Verwendung von Bind-Parametern hat der Optimizer aber keine konkreten Werte, deren Häufigkeit er mit dem Histogramm ermitteln könnte. Daher gelangt der Optimizer immer zur selben Zeilenschätzung und zum selben Cost-Wert. Am Schluss wählt er also immer denselben Ausführungsplan aus.
Tipp
Histogramme sind vor allem bei ungleicher Verteilung nützlich.
Bei einer gleichmäßigen Verteilung genügt es oft, die Zahl der unterschiedlichen Werte durch die Anzahl der Zeilen insgesamt zu dividieren. Diese Methode wird auch bei Bind-Parametern verwendet.
Vergleicht man den Optimizer mit einem Compiler, verhalten sich Bind-Parameter wie Programmvariablen, während Werte, die direkt in der SQL-Anweisung stehen, Konstanten entsprechen. So wie ein Compiler konstante Ausdrücke bereits bei der Übersetzung auflösen kann, kann der Optimizer konkrete Werte beim Erstellen des Ausführungsplanes berücksichtigen. Im Gegensatz dazu sind die Werte der Bind-Parameter für den Optimizer – vereinfacht gesagt – nicht sichtbar.
Aus diesem Blickwinkel scheint es etwas widersprüchlich, dass Bind-Parameter die Performance erhöhen können. Denn mit konkreten Werten kann der Optimizer immer den besten Ausführungsplan auswählen. Die Frage ist nur: zu welchem Preis? Denn das Erstellen aller Ausführungsplan-Varianten kann auch ein erheblicher Aufwand sein. Ein Aufwand, der sich nur dann auszahlt, wenn am Ende nicht immer dasselbe Ergebnis steht.
Tipp
Keine Bind-Parameter zu verwenden, ist, als würde man ein Programm jedes Mal neu kompilieren.
Bei der Frage, ob der Optimizer einen allgemeinen oder angepassten Ausführungsplan erstellen soll, steht die Datenbank vor einem Dilemma. Denn um zu wissen, ob sich der Optimierungs-Aufwand auszahlt, muss die Datenbank zuerst mehrere Ausführungspläne erstellen. Aber genau das möchte man durch einen Ausführungsplan-Cache vermeiden. Die Datenbank-Hersteller versuchen dieses Dilemma mit heuristischen Methoden zu lösen, haben dabei aber nur sehr beschränkten Erfolg.
Als Entwickler kann man der Datenbank bei diesem Dilemma aber helfen, indem man Bind-Parameter gezielt einsetzt. Das heißt, dass man prinzipiell immer Bind-Parameter verwenden sollte, außer bei Werten, die sich auf den Ausführungsplan auswirken sollen.
Ungleich verteilte Status-Codes wie „erledigt“ und „noch nicht erledigt“ sind ein gutes Beispiel. Dabei ist die Zahl der noch nicht erledigten Einträge meist gering im Vergleich zu den erledigten. In diesem Fall macht ein Indexzugriff nur dann Sinn, wenn man die noch nicht erledigten Einträge sucht. Ein anderes Beispiel sind Partitionen – das ist, wenn man Tabellen und/oder Indizes auf mehrere Speicherplätze aufteilt. Die Werte können dann beeinflussen, welche Partitionen bei einer Abfrage durchsucht werden müssen. Auch bei LIKE
-Bedingungen kann es durch Bind-Parametern zu suboptimalen Ausführungsplänen kommen, wie wir im nächsten Abschnitt sehen werden.
Tipp
In der Praxis gibt es nur wenige Fälle, in denen sich die Werte auf den Ausführungsplan auswirken.
Im Zweifelsfall sollte man daher immer Bind-Parameter verwenden – schon alleine um SQL-Injections zu vermeiden.
Die folgenden Beispiele zeigen die Verwendung von Bind-Parameter in einigen gängigen Programmiersprachen.
- C#
Ohne Bind-Parameter:
int subsidiary_id; SqlCommand cmd = new SqlCommand( "select first_name, last_name" + " from employees" + " where subsidiary_id = " + subsidiary_id , connection);
Mit Bind-Parameter:
int subsidiary_id; SqlCommand cmd = new SqlCommand( "select first_name, last_name" + " from employees" + " where subsidiary_id = @subsidiary_id , connection); cmd.Parameters.AddWithValue("@subsidiary_id", subsidiary_id);
Siehe: Dokumentation zur Klasse
SqlParameterCollection
.- Java
Ohne Bind-Parameter:
int subsidiary_id; Statement command = connection.createStatement( "select first_name, last_name" + " from employees" + " where subsidiary_id = " + subsidiary_id );
Mit Bind-Parameter:
int subsidiary_id; PreparedStatement command = connection.prepareStatement( "select first_name, last_name" + " from employees" + " where subsidiary_id = ?" ); command.setInt(1, subsidiary_id);
Siehe: Dokumentation zur Klasse
PreparedStatement
.- Perl
Ohne Bind-Parameter:
my $subsidiary_id; my $sth = $dbh->prepare( "select first_name, last_name" . " from employees" . " where subsidiary_id = $subsidiary_id" ); $sth->execute();
Mit Bind-Parameter:
my $subsidiary_id; my $sth = $dbh->prepare( "select first_name, last_name" . " from employees" . " where subsidiary_id = ?" ); $sth->execute($subsidiary_id);
Siehe: „Placeholders and Bind Values“ in der DBI-Dokumentation.
- PHP
Das folgende Beispiel zeigt einen MySQL-Zugriff ohne Bind-Parameter:
$mysqli->query("select first_name, last_name" . " from employees" . " where subsidiary_id = " . $subsidiary_id);
Mit Bind-Parameter:
if ($stmt = $mysqli->prepare("select first_name, last_name" . " from employees" . " where subsidiary_id = ?")) { $stmt->bind_param("i", $subsidiary_id); $stmt->execute(); } else { /* handle SQL error */ }
Siehe: Dokumentation zu
mysqli_stmt::bind_param
und „Prepared statements and stored procedures“ in der PDO-Dokumentation.- Ruby
Ohne Bind-Parameter:
dbh.execute("select first_name, last_name" + " from employees" + " where subsidiary_id = #{subsidiary_id}");
Mit Bind-Parameter:
dbh.prepare("select first_name, last_name" + " from employees" + " where subsidiary_id = ?"); dbh.execute(subsidiary_id);
Siehe: „Quoting, Placeholders, and Parameter Binding“ im Ruby DBI Tutorial
Siehe auch
Im SQL-Standard ist nur das Fragezeichen (?
) als Platzhalter vorgesehen. Verwendet man mehrere Platzhalter, unterschieden sie sich nur durch die Position in der SQL-Anweisung – erster, zweiter, dritter, … und so fort. Das kann beim Arbeiten mit Code sehr unpraktisch sein, da sich die Nummerierung ändert, wenn man Platzhalter hinzufügt oder entfernt. Daher bieten manche Datenbanken proprietäre Lösungen für benannte Platzhalter an. Zum Beispiel mit einem Klammeraffen (@name
) oder Doppelpunkt (:name
).
Beachte
Bind-Parameter kann man nur für Werte verwenden.
Das bedeutet, dass man Tabellen oder Spaltennamen nicht durch Bind-Parameter ersetzen kann. Daher funktioniert keiner der beiden folgenden Bind-Parameter:
String sql = prepare("SELECT * FROM ? WHERE ?");
sql.execute('employees', 'employee_id = 1');
Wenn man die Struktur einer SQL-Anweisung zur Laufzeit ändern muss, muss man dynamisches SQL verwenden.
Siehe auch
„Schlaue Logik“ hat weitere Details zu den Ausführungsplan Caches verschiedener Datenbanken.
Artikel: Planning for Execution Plan Reuse