Ich versuche ein kleines Experiment, um einen Datensatz zu verschieben, der nicht geo-räumlich ist, aber ziemlich gut passt und die Ergebnisse etwas beunruhigend findet. Der Datensatz ist genomische Daten, z.B. Das menschliche Genom, in dem wir eine DNA-Region haben, in der Elemente wie Gene bestimmte Start- und Stopp-Koordinaten einnehmen (unsere X-Achse). Wir haben mehrere Regionen von DNA (Chromosomen), die die Y-Achse besetzen. Das Ziel besteht darin, alle Elemente, die zwei X-Koordinaten schneiden, entlang einer einzigen Y-Koordinate, z. LineString (START 1, ENDE 2).
Die Theorie schien vernünftig zu sein, also habe ich sie in ein bestehendes MySQL-basiertes Genomprojekt gepusht und eine Tabellenstruktur wie folgt entwickelt:
%Vor% external_id steht für den Bezeichner der Entität, die wir in diese Tabelle & amp; external_type codiert die Quelle davon. Alles sah gut aus und ich schob einige vorläufige Daten (30.000 Zeilen) ein, die gut zu funktionieren schienen. Als dies über die 3-Millionen-Zeilen-Marke hinausging, weigerte sich MySQL, den räumlichen Index zu verwenden, und war langsamer, als es gezwungen wurde, es zu benutzen (40 Sekunden vs. 5 Sekunden mit einem vollständigen Tabellen-Scan). Wenn mehr Daten hinzugefügt wurden, begann der Index verwendet zu werden, aber die Leistungseinbuße blieb bestehen. Das Erzwingen des Indexes brachte die Abfrage auf 8 Sekunden herunter. Die Abfrage, die ich verwende, sieht folgendermaßen aus:
Die Daten, die hier eingehen, sind entlang der Y-Dimensionen sehr dicht (denken Sie daran, als hätten Sie die Position jedes Gebäudes, jeder Telefonzelle, jedes Postfachs und jeder Taube auf einer sehr langen Straße aufgezeichnet). Ich habe Tests durchgeführt, wie sich R-Indizes mit diesen Daten in Java verhalten, und andere im Feld haben sie erfolgreich auf flache Dateiformate angewendet. Jedoch hat niemand sie auf die Datenbanken AFAIK angewendet, was das Ziel dieses Tests ist.
Hat jemand da draußen ein ähnliches Verhalten gesehen, wenn er große Datenmengen in ein räumliches Modell einfügt, das entlang einer bestimmten Achse nicht sehr unterschiedlich ist? Das Problem besteht weiterhin, wenn ich die Verwendung der Koordinaten umkehre. Ich führe das folgende Setup aus, wenn das eine Ursache ist
Hilfe!
Auch erklären Plan in
%Vor%Das neu geschriebene SQL sieht wie folgt aus
%Vor%Es wird immer noch nicht hervorgehoben, warum die Leistung dieser Abfrage so schlecht ist
Nach dem Lesen des Artikels von @Fraser von rickonrails Es scheint, als ob das Problem damit zu tun hat, dass der Index nicht im Speicher ist. Wenn ich ähnliche Techniken zu den im Artikel erwähnten anwenden (Schlüsselpuffer wirklich sehr groß machend) und ich erzwinge dann die Abfrage, die Indexabfragezeiten plumet zu benutzen. Wir sehen immer noch eine Verzögerung zwischen der Abfrage einer Region & amp; Dann suche ich nach einer Teilmenge der Region, aber alles deutet darauf hin, dass die Belastung der Indizes korrekt ist.
Was ist die Moral der Geschichte? R-Indizes in MySQL haben ziemlich schlechte Leistung, bis sie im Speicher sind und dann haben sie eine ausgezeichnete Leistung. Nicht wirklich eine gute Lösung für das, was ich mit ihnen machen wollte, aber es bietet trotzdem einen interessanten Blickwinkel auf MySQL.
Danke für die Hilfe Leute.
Aus dem EXPLAIN-Plan sehen wir, dass, obwohl das Spatial für die Abfrage verwendet werden kann (Spalte 'possible_keys'), es nicht verwendet wird (NULL in der Spalte 'Schlüssel'). Ich bin nicht sicher, warum es nicht automatisch ausgewählt wird, aber Sie können MySql explizit anweisen, den Index zu verwenden, indem Sie ihn in der Abfrage mit einer 'force index'-Klausel angeben:
%Vor%Das Ziel ist es, all das zurückzubringen Elemente, die zwei X schneiden Koordinaten entlang eines einzigen Y koordiniere
Haben Sie darüber nachgedacht, einen Index mit mehreren Feldern zu verwenden? Wie:
%Vor% Wenn Sie mit einer begrenzten Anzahl von y -Werten arbeiten, ist dies der richtige Weg.
Ich habe einen Abschluss in Genetik und ich bin Programmierer, du brauchst kein X und Y zu benutzen, da deine Nomenklatur viel zu dämlich wird ... du brauchst einen Anfang und einen Stopp Position (keine "Achse") und eine Chromosom Nummer. Sie indexieren zuerst die Chromosomenzahl und dann die Position und dann indexieren Sie die Position und dann das Chromosom. (Frage: Haben Sie es mit Eukaryoten oder Chromosomen zu tun, die zwei Leserahmen haben können?)
EG: (wo "x" = Position und "y" = Chromosom)
%Vor%Übrigens sind Chromosomen sehr lange Strings (genau wie Daten), die Sie (um Dinge zu beschleunigen, wie Blobs (d. h. kodierende Gene und Junk-DNA) loswerden können
Sind Sie sicher, dass eine relationale Datenbank der richtige Weg ist? Wenn ich Sie wäre, würde ich versuchen, Ihre Datensätze auf Solr oder Elastic Search zu schieben (wahrscheinlich die Master-Datensätze woanders speichern). Diese Engines sind für die Indizierung gebaut, Sie werden den Unterschied in den Antwortzeiten bemerken.
Tags und Links mysql database performance gis spatial