Низкая производительность при использовании пространственных индексов в MySQL

Я пытаюсь провести небольшой эксперимент по продвижению набора данных, который не является геопространственным, но достаточно хорошо ему подходит, и нахожу результаты несколько тревожными. Набор данных - это геномные данные, например, геном человека, где у нас есть область ДНК, в которой такие элементы, как гены, занимают определенные координаты начала и конца (наша ось X). У нас есть несколько участков ДНК (хромосом), которые занимают ось Y. Цель состоит в том, чтобы вернуть все элементы, которые пересекают две координаты X по одной координате Y, например LineString (START 1, END 2).

Теория казалась разумной, поэтому я вставил ее в существующий проект генома на основе MySQL и придумал такую ​​структуру таблицы, как:

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_id представляет идентификатор объекта, который мы закодированы в этой таблице & external_type кодирует источник этого. Все выглядело хорошо, и я ввел некоторые предварительные данные (30 000 строк), которые, похоже, работали хорошо. Когда это значение превысило отметку в 3 миллиона строк, MySQL отказался от использования пространственного индекса и работал медленнее, когда был вынужден его использовать (40 секунд против 5 секунд при полном сканировании таблицы). Когда было добавлено больше данных, индекс начал использоваться, но снижение производительности сохранялось. При принудительном отключении индекса время запроса сократилось до 8 секунд. Запрос, который я использую, выглядит так:

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

Данные, входящие в него, очень плотны по оси Y (представьте, что вы записали положение каждого здания, телефонной будки, почтового ящика и голубя на очень длинной дороге). Я провел тесты того, как R-индексы ведут себя с этими данными в Java, а также другие в этой области успешно применили их к форматам плоских файлов. Однако никто не применял их к базам данных AFAIK, что является целью этого теста.

Кто-нибудь видел подобное поведение при добавлении больших объемов данных в пространственную модель, которая не очень различается по определенной оси? Проблема не исчезнет, ​​если я отменю использование координат. Я использую следующую установку, если это причина

• MacOS 10.6.6
• MySQL 5.1.46

Помогите!

Также представлен план объяснения в

+----+-------------+-----------------+------+-----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table           | type | possible_keys   | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------+------+-----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | spatial_feature | ALL  | sf_location_idx | NULL | NULL    | NULL | 3636060 |    33.33 | Using where |
+----+-------------+-----------------+------+-----------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Переписанный SQL выглядит следующим образом

select count(0) AS `count(*)` from `arabidopsis_thaliana_core_7_60_9`.`spatial_feature` where intersects(geometryfromtext('LineString(7420023 1, 7420023 1)'),`arabidopsis_thaliana_core_7_60_9`.`spatial_feature`.`location`)

По-прежнему не раскрывается, почему этот запрос работает так плохо

После прочтения статьи, опубликованной @Fraser из rickonrails Похоже, проблема связана с тем, что индекс не находится в памяти. Если я применяю те же методы, что и упомянутые в статье (действительно делая ключевой буфер очень большим), я заставляю запрос использовать время запроса индекса plumet. Мы по-прежнему наблюдаем задержку между запросом региона и последующим поиском подмножества региона, но все это указывает на правильную загрузку индексов.

В чем мораль этой истории? R-индексы в MySQL имеют довольно низкую производительность, пока они не находятся в памяти, а затем они имеют отличную производительность. Не совсем хорошее решение для того, что я хотел сделать с ними, но все же оно дает интересный угол зрения MySQL.

Спасибо за помощь.