Niska wydajność podczas korzystania z indeksów przestrzennych w MySQL

Ponownie opublikuj pytanie zadane na temat Przepełnienia stosu, gdy zasugerowano, że byłoby to lepsze forum.

Próbuję małego eksperymentu w wypychaniu zestawu danych, który nie jest przestrzenny, ale pasuje do niego całkiem dobrze, a wyniki są nieco niepokojące. Zbiór danych to dane genomowe, np. Ludzki genom, w którym mamy region DNA, w którym elementy takie jak geny zajmują określone współrzędne początkowe i końcowe (nasza oś X). Mamy wiele regionów DNA (chromosomów), które zajmują oś Y. Celem jest przywrócenie wszystkich elementów, które przecinają dwie współrzędne X wzdłuż jednej współrzędnej Y, np. LineString (START 1, END 2).

Teoria wydawała się solidna, więc wepchnąłem ją do istniejącego projektu genomu opartego na MySQL i opracowałem strukturę tabeli, taką jak:

CREATE TABLE `spatial_feature` (
  `spatial_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` int(10) unsigned NOT NULL,
  `external_type` int(3) unsigned NOT NULL,
  `location` geometry NOT NULL,
  PRIMARY KEY (`spatial_feature_id`),
  SPATIAL KEY `sf_location_idx` (`location`)
) ENGINE=MyISAM;

external_idreprezentuje identyfikator encji, którą zakodowaliśmy w tej tabeli i external_typekoduje jej źródło. Wszystko wyglądało dobrze i wrzuciłem kilka wstępnych danych (30 000 wierszy), które wydawały się działać dobrze. Kiedy wzrósł on ponad 3 miliony wierszy, MySQL odmówił użycia indeksu przestrzennego i był wolniejszy, gdy był zmuszony do jego użycia (40 sekund vs. 5 sekund przy pełnym skanowaniu tabeli). Gdy dodano więcej danych, indeks zaczął być używany, ale kara za wydajność utrzymywała się. Wymuszenie wyłączenia indeksu sprowadziło zapytanie do 8 sekund. Zapytanie, którego używam wygląda następująco:

select count(*)
from spatial_feature
where MBRIntersects(GeomFromText('LineString(7420023 1, 7420023 1)'), location);

Dane wchodzące w to są bardzo gęste wzdłuż wymiarów Y (pomyśl o tym, jakbyś zarejestrował pozycję każdego budynku, budki telefonicznej, skrzynki pocztowej i gołębia na bardzo długiej drodze). Zrobiłem testy zachowania R-Indexes z tymi danymi w Javie, a inne w tej dziedzinie z powodzeniem zastosowały je do formatów plików płaskich. Jednak nikt nie zastosował ich do baz danych AFAIK, co jest celem tego testu.

Czy ktoś tam widział podobne zachowanie podczas dodawania dużych ilości danych do modelu przestrzennego, który nie jest bardzo zróżnicowany wzdłuż określonej osi? Problem występuje nadal, jeśli odwrócę użycie współrzędnych. Korzystam z następującej konfiguracji, jeśli to jest przyczyna

• MacOS 10.6.6
• MySQL 5.1.46

MySQL, podobnie jak PostGIS, przechowuje dane indeksu przestrzennego w strukturze R-drzewa, dzięki czemu może szybko znaleźć rzeczy. Drzewo R, podobnie jak drzewo B, jest zorganizowane w taki sposób, że jest zoptymalizowane do wyszukiwania tylko niewielkiej części wszystkich danych w tabeli. W rzeczywistości szybciej jest zignorować indeks dla zapytań, które muszą przeczytać dużą sekcję tabeli, aby zwrócić dane lub wykonać ogromne sprzężenie, co jest klasycznym przypadkiem, który powoduje powstanie wielu forów [plakatów] na bazach danych narzekających na zapytanie zwracające połowę ich tabela „nie używa nowego, właśnie utworzonego indeksu”.

From http://rickonrails.wordpress.com/2009/03/30/big-ole-mysql-spatial-table-optimization-tricks/

Jeśli zmieścisz wszystkie dane z tabeli w pamięci, wydajność będzie dobra. Jeśli / kiedy musisz zacząć odczytywać dyski, wydajność szybko spada. Czy robisz wzorce wykorzystania pamięci dla instancji mysql dla dwóch przypadków: 30 tys. Wierszy vs. 3000 tys. Wierszy?

Coś musi być nie tak z instalacją mysql lub ustawieniami .ini. Właśnie przetestowałem indeks geoprzestrzenny na moim starym komputerze Mac (10.6.8 / MySQL 5.2). Ta konfiguracja jest podobna do twojej i przetestowałem duży zrzut geodanych ( 9 milionów rekordów ). Zrobiłem to zapytanie:

SET @radius = 30;
SET @center = GeomFromText('POINT(51.51359 7.465425)');
SET @r = @radius/69.1;
SET @bbox = CONCAT('POLYGON((', 
  X(@center) - @r, ' ', Y(@center) - @r, ',', 
  X(@center) + @r, ' ', Y(@center) - @r, ',', 
  X(@center) + @r, ' ', Y(@center) + @r, ',', 
  X(@center) - @r, ' ', Y(@center) + @r, ',', 
  X(@center) - @r, ' ', Y(@center) - @r, '))' 
);

SELECT geonameid, SQRT(POW( ABS( X(point) - X(@center)), 2) + POW( ABS(Y(point) - Y(@center)), 2 ))*69.1 
AS distance
FROM TABLENAME AS root
WHERE Intersects( point, GeomFromText(@bbox) ) 
AND SQRT(POW( ABS( X(point) - X(@center)), 2) + POW( ABS(Y(point) - Y(@center)), 2 )) < @r 
ORDER BY distance; 

Zajęło to zaledwie 0,0336 sekundy.

Używam powyższego zapytania np. Do porównań między tabelami, w których tabela, z której pochodzą tylko wartości lat / lng dla @center, ma zwykły INDEKS z city_latitude / city_longitude i 9-12 Mio. tabela z geonames.org ma indeks geoprzestrzenny.

Chciałem tylko dodać, że kiedy ktoś wstawi duże dane do tabeli, bardziej wydajne może być dodanie indeksu po INSERT. Jeśli nie, zajmie to więcej czasu dla każdego dodawanego wiersza ... [ale to nie jest ważne]

Czy zastanawiałeś się nad podzieleniem go na dwie kolumny 1D zamiast jednej kolumny 2D?

Optymalizator może zadławić wszystkie podobne dane i pomocne mogą być dwie kolumny o większej różnorodności.

Możesz także sprawdzić kolejność sprawdzania elementów. Miałem problem w Oracle Spatial, gdzie szukałem nazwiska i filtra IN_REGION. Oracle zdecydowało, że najszybszym sposobem jest użycie nazwiska, a następnie sprawdzenie regionu. Pozwól, że ci powiem, że sprawdzanie w regionie wszystkich Robinsonów w Cleveland jest powolne . Pamiętam, że musiałem przekazać specyficzny dla Oracle argument, aby zmusić go do użycia najpierw indeksu przestrzennego.