MySQL Spatial Functions Performance

Seit der Version 5.6 sind die räumlichen Funktionen (Spatial Functions) in MySQL nutzbar. In den vorherigen Versionen waren sie nur rudimentär - basierend auf umschließenden Rechtecken (MBR) - umgesetzt. Doch die Performance von Datenbankabfragen ist schlechter als erwartet. Ein kleiner Trick kann Abhilfe schaffen.

MBR-Funktionen sind schneller

Ruft man Daten aus einer Tabelle mit räumlichen, mittels SPATIAL INDEX indizierten (!!!) Spalten ab und nutzt dabei die ST_-Funktionen (z.B. ST_INTERSECTS()), so kann die Abfragedauer unangenehm überraschen. Testet man die einfacheren MBR-Funktionen (z.B. INTERSECTS()), so erhält man Ergebnisse in kürzerer Zeit.

INTERSECTS() ist schneller als ST_INTERSECTS(), analog gilt dies für alle räumlichen Funktionen.

Da INTERSECTS() aber nur die umschließenden Rechtecke (MBR) berücksichtigt, wird es mehr Übereinstimmungen finden als ST_INTERSECTS(). Hat man beispielsweise eine Tabelle mit Geokoordinaten europäischer Orte und will mittels WITHIN() überprüfen, ob diese in Deutschland liegen, würde im untenstehenden Bild auch Prag dazugehören.

MBR von Deutschland (roter Rahmen) und das darin liegende Prag (roter Punkt),
© OpenStreetMap-Mitwirkende. Tiles: Humanitarian OpenStreetMap Team

Der Query (sinngemäß):

@deutschland: Ein Polygon mit den Umrissen Deutschlands.


SELECT name FROM orte WHERE ST_WITHIN(koordinaten,@deutschland);

Resultat: Nur deutsche Städte. Lange Aufrufdauer.

SELECT name FROM orte WHERE WITHIN(koordinaten,@deutschland);

Resultat: Deutsche Städte und solche, die sich im MBR befinden, u.a. Prag. Dafür kurze Aufrufdauer.

Der Trick

Natürlich nützt uns eine Liste "deutscher" Städte, zu denen die der Nachbarländer dazugehören, nicht viel. Selbst, wenn wir sie viel schneller aus einer Tabelle auslesen können. Die mittels MBR-Funktionen erzeugte Liste braucht einen Korrekturlauf:

SELECT name,ST_WITHIN(koordinaten,@deutschland) as wi FROM orte WHERE WITHIN(koordinaten,@deutschland) HAVING wi;

Dazugekommen ist also ein ST_WITHIN() im SELECT-Teil und ein HAVING nach dem WHERE-Teil. HAVING dürfte zu den seltener genutzten Möglichkeiten von MySQL gehören, es handelt sich um einen Filter, der über das Ergebnis einer Abfrage gejagt wird und dabei jeden einzelnen Datensatz prüft. HAVING ist normalerweise langsamer als die gleiche Bedingung im WHERE. Im Falle räumlicher Funktionen kann es aber Geschwindigkeitsvorteile bringen, ein Ergebnis zunächst mit MBR-Funktionen zu berechnen und nur über dieses Ergebnis die normalen räumlichen Funktionen zu jagen.

Ein alternativer Ansatz mit der gleichen Grundidee ist folgender:

SELECT name from orte WHERE CASE WHEN WITHIN(koordinaten,@deutschland) THEN ST_WITHIN(koordinaten,@deutschland) ELSE 0 END

Nur, wenn WITHIN() erfüllt ist, leitet die CASE-Abfrage weiter auf ST_WITHIN(). Somit profitiert man von der Schnelligkeit des WITHIN() auf der indizierten Spalte "koordinaten" und der Genauigkeit von ST_WITHIN().

Fazit

Das o.g. Beispiel ist bewusst unkonkret gehalten. Ich habe auf Angaben zu möglichen Zeitersparnissen verzichtet, ebenso auf konkrete Tabellenstrukturen. Es handelt sich um einen Denkansatz, der mir bei der Lösung eines Performance-Problems geholfen hat und Queries teils um Größenordnungen verschnellert hat.

Möglicherweise beobachten Sie in Ihrem Projekt die Zeitunterschiede zwischen MBR- und sonstigen räumlichen Funktionen in der Abfragedauer nicht. Vielleicht sind Ihre Daten anders oder MySQL hat sich weiterentwickelt. Aber da Sie nunmal hier sind, werden Sie in der Suchmaschine wohl nach Performance-Problemen mit räumlichen Funktionen gesucht haben, und da wird etwas Testen sicher nicht schaden. Über Ihren Kommentar freue ich mich!

code:wendt