LIKE-Filter indizieren

LIKE-Filter führen immer wieder zu überraschendem Performance-Verhalten, weil manche Suchbegriffe eine effiziente Index-Nutzung verhindern können. Das heißt, es gibt Suchbegriffe, die sehr gut indiziert werden können, andere hingegen nicht. Den Ausschlag macht die Position der Wildcard-Zeichen.

Die folgende Abfrage verwendet das Wildcard-Zeichen „%“ mitten im Suchbegriff:

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) LIKE 'WIN%D'

Eine LIKE-Bedingung kann nur die Zeichen vor dem ersten Wildcard-Zeichen beim Durchwandern des Indexbaumes nutzen. Alle anderen Zeichen sind lediglich Filterprädikate, die den durchsuchten Indexbereich nicht einschränken. Eine einzige LIKE-Bedingung kann also zwei Prädikat-Typen beinhalten: (1) den Teil vor dem ersten Wildcard-Zeichen als Zugriffsprädikat; (2) alle weiteren Zeichen als Filterprädikat.

Je selektiver der Teil vor dem ersten Wildcard-Zeichen ist, desto kleiner ist der durchsuchte Indexbereich. Das wiederum macht die Index-Suche schneller. Abbildung 2.4 stellt dieses Verhältnis mit drei LIKE-Suchen dar. Alle drei selektieren denselben Eintrag. Dennoch ist die Größe des durchsuchten Indexbereiches – und damit auch die Performance – sehr unterschiedlich.

Abbildung 2.4 Unterschiedliche LIKE-Suchen

Der erste Suchbegriff hat nur zwei Zeichen vor dem Wildcard. Dadurch wird der durchsuchte Indexbereich auf 18 Einträge begrenzt. Nur einer davon entspricht dem vollständigen LIKE-Filter – die restlichen 17 Einträge werden zwar geladen, aber verworfen. Der zweite Suchbegriff hat einen längeren Präfix, der den durchsuchten Indexbereich von vornherein auf zwei Einträge eingeschränkt. Mit diesem Suchbegriff wird nur eine Zeile gelesen, die nicht im Endergebnis aufscheint. Beim letzten Suchbegriff gibt es überhaupt kein Filterprädikat. Es wird nur der Eintrag gelesen, der dem vollständigen LIKE-Filter entspricht.

Natürlich gibt es auch den umgekehrten Grenzfall: einen LIKE-Filter, der mit einem Wildcard-Zeichen beginnt. Dann entfällt das Zugriffsprädikat. Die Datenbank muss die ganze Tabelle durchsuchen, wenn es keine anderen, indizierten, Bedingungen in der where-Klausel gibt.

Die Position der Wildcards im Suchbegriff beeinflusst also die Indexwahl – zumindest theoretisch. In der Praxis erstellt der Optimizer aber einen generischen Ausführungsplan, wenn der Suchbegriff über einen Bind-Parameter angegeben wird. Dann muss der Optimizer raten, ob die Abfrage mehrheitlich mit führendem Wildcard-Zeichen ausgeführt wird, oder nicht.

Die meisten Datenbanken gehen bei einem LIKE-Filter mit Bind-Parameter davon aus, dass der Suchbegriff kein Wildcard-Zeichen am Anfang stehen hat. Das ist aber falsch, wenn die LIKE-Bedingung für eine Volltext-Suche verwendet wird. Es gibt leider keine direkte Möglichkeit, eine LIKE-Suche mit Bind-Parameter als Volltext-Suche zu kennzeichnen. Der Kasten „Volltext-Suchen statisch kennzeichnen“ zeigt einen Versuch, der nicht funktioniert. Die naheliegendste Lösung wäre, den Suchbegriff ohne Bind-Parameter anzugeben. Das führt aber nicht nur zu erhöhtem Optimierungsaufwand, sondern öffnet auch eine SQL-Injection-Schwachstelle. Eine effektive und dennoch sichere und portable Lösung ist, die LIKE-Bedingung absichtlich zu verschleiern. „Spalten zusammenfügen“ geht genauer darauf ein.

WHERE text_column LIKE '%' || ? || '%'

Bei PostgreSQL steht man vor einem anderen Problem, weil diese Datenbank für LIKE-Suchen mit Bind-Parameter immer ein führendes Wildcard-Zeichen annimmt. Daher verwendet PostgreSQL in diesem Fall keinen Index. Nur wenn man dem Optimizer den konkreten Suchbegriff zur Verfügung stellt, kann PostgreSQL eine LIKE-Bedingung mit einem Indexzugriff optimieren. Wenn man auf den Bind-Paramter verzichtet und den Suchbegriff direkt in die SQL-Anweisung schreibt, muss man natürlich andere Vorsichtsmaßnahmen gegen SQL-Injections treffen.

Selbst wenn der Ausführungsplan für eine LIKE-Bedingung mit führendem Wildcard-Zeichen ausgelegt ist, kann die Performance noch immer unzureichend sein. Dann kann man immer noch auf andere Bedingungen der where-Klausel zurückgreifen – siehe „Index-Filterprädikate gezielt einsetzen“. Wenn es keinen anderen Zugriffsweg gibt, könnte einer der folgenden, proprietären Volltext-Indizes helfen.