Скачать или смотреть Verstehen, warum MySQL 8 den Spatial Index nicht verwendet: Ein Leitfaden zur Fehlerbehebung

Entdecken Sie mögliche Gründe, warum MySQL 8 Ihren Spatial Index ignoriert, und lernen Sie, wie Sie das Problem effektiv beheben können.
---
Dieses Video basiert auf der Frage https://stackoverflow.com/q/62297352/ gestellt von dem Nutzer 'Arbaz Sheikh' ( https://stackoverflow.com/u/12813945/ ) sowie auf der Antwort https://stackoverflow.com/a/62311098/ bereitgestellt von dem Nutzer 'Rick James' ( https://stackoverflow.com/u/1766831/ ) auf der Website 'Stack Overflow'. Vielen Dank an diese großartigen Nutzer und die Stackexchange-Community für ihre Beiträge.

Besuchen Sie diese Links, um den Originalinhalt und weitere Details zu sehen, z. B. alternative Lösungen, aktuelle Entwicklungen zum Thema, Kommentare, Versionsverlauf usw. Der ursprüngliche Titel der Frage lautete beispielsweise: MySql 8 is not using spatial index

Außerdem steht der Inhalt (außer Musik) unter der Lizenz CC BY-SA https://meta.stackexchange.com/help/l...
Der ursprüngliche Fragenbeitrag steht unter der Lizenz 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ), und der ursprüngliche Antwortbeitrag steht unter der Lizenz 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ).

Falls Ihnen irgendetwas auffällt oder Unstimmigkeiten bestehen, schreiben Sie mir bitte an vlogize [AT] gmail [DOT] com.
---
MySQL 8 verwendet Spatial Index nicht: Ein Leitfaden zur Fehlerbehebung

Wenn Sie als MySQL-Nutzer kürzlich auf Version 8 aktualisiert haben, sind Sie möglicherweise auf das Problem gestoßen, dass Ihre Abfragen mit Spatial Indexen schlecht performen. In diesem Leitfaden untersuchen wir einen konkreten Fall, bei dem eine Abfrage mit einem Spatial Index auf einer Spalte namens SHAPE ungewöhnlich lange zur Ausführung braucht. Dieser Blogbeitrag zielt darauf ab, das Problem zu diagnostizieren und eine klare Lösung bereitzustellen, damit Ihre Abfragen effizient mit dem gewünschten Spatial Index ausgeführt werden.

Das Problem

Im Szenario des Nutzers gibt es eine Spatial-Spalte namens SHAPE mit SRID 4269, auf der ein Spatial Index erstellt wurde. Allerdings benötigt die Abfrage, die die Funktion ST_Intersects verwendet, etwa 2 Minuten, um Ergebnisse aus einer Tabelle mit nur 33.000 Datensätzen zurückzugeben. Hier die relevante SQL-Abfrage:

[[Siehe Video, um diesen Text oder Codeausschnitt anzuzeigen]]

Zur weiteren Analyse führte der Nutzer ein EXPLAIN für die Abfrage aus und stellte fest, dass der Spatial Index nicht verwendet wird:

[[Siehe Video, um diesen Text oder Codeausschnitt anzuzeigen]]

Das Ergebnis zeigte, dass die Abfrage die gesamte Tabelle scannt, ohne den Spatial Index zu nutzen, erkennbar am Fehlen des key-Felds in der Ausgabe.

Mögliche Ursachen für das Ignorieren des Index

Es gibt mehrere Gründe, warum ein Spatial Index in MySQL 8 nicht verwendet wird. Hier einige potenzielle Faktoren:

SRID-Kompatibilität: In MySQL 8 hat sich der Umgang mit verschiedenen Spatial Reference Systems geändert. Entscheidend ist, ob SRID 4269 anders behandelt wird als andere SRIDs wie 4326. Das MySQL-Changelog erwähnt, dass bestimmte Spatial-Funktionen nun Fehler auslösen, wenn sie mit Geometrien auf Basis geografischer Koordinatensysteme (geographic SRS) aufgerufen werden, was die Ausführung Ihrer Abfrage beeinflussen könnte.

Vorhandensein des Index: Stellen Sie sicher, dass der Spatial Index auf der SHAPE-Spalte tatsächlich im Datenbankschema vorhanden ist. Es kann vorkommen, dass ein Index unbeabsichtigt gelöscht oder bei Datenbankmigrationen fehlerhaft definiert wurde.

Änderungen bei der Dateninterpretation: MySQL 8 bringt Veränderungen in der Interpretation der Geometrien durch Spatial-Funktionen mit sich. Abfragen, die in MySQL 5.7 gut funktioniert haben, können in 8.x andere Ergebnisse liefern, wenn räumliche Daten neu interpretiert werden.

Abfrageaufbau: Analysieren Sie die Abfrage selbst. Manchmal helfen leichte Anpassungen der SQL-Struktur, die Nutzung von Indizes zu fördern. Zusätzliche Filter oder ein Umformulieren der Abfrage können MySQL helfen, einen effizienteren Ausführungsplan zu wählen.

Schritte zur Fehlerbehebung

Wie können Sie diese Probleme angehen und MySQL zur effektiven Nutzung Ihres Spatial Index bewegen? Folgen Sie diesen Schritten:

1. Überprüfen Sie das SRID

Testen Sie, ob eine Änderung des SRID auf 4326 hilfreich ist. Diese Anpassung kann die Geometrie an die in MySQL 8 unterstützten Formate angleichen:

[[Siehe Video, um diesen Text oder Codeausschnitt anzuzeigen]]

2. Index auf Existenz überprüfen

Führen Sie folgenden SQL-Befehl aus, um sicherzustellen, dass der Spatial Index korrekt eingerichtet ist:

[[Siehe Video, um diesen Text oder Codeausschnitt anzuzeigen]]

Suchen Sie in der Ausgabe explizit nach dem Index auf der SHAPE-Spalte.

3. Abfrage modifizieren und testen

Versuchen Sie, die ursprüngliche Abfrage zu verändern, etwa durch Hinzufügen weiterer Filter oder Ändern der Bedingungen. Beispielsweise kann das Einbinden anderer Spalten analog zur zipcode-Spalte die Performa