Как разбить пространственный индекс в MySQL или объединить его с индексом BTREE?

У меня есть таблица в MySQL, содержащая отдельные точки строк географических линий, которые я показываю на интерактивной карте. Помимо прочего, таблица содержит следующие поля:

`pos` point NOT NULL,
`zoom` int(10) unsigned NOT NULL

pos содержит координаты точки, а zoom - это число от 1 до 20, при каком уровне масштабирования карты необходимо загрузить эту конкретную точку. Когда карта сильно уменьшена (низкий уровень масштабирования), необходимо загрузить большой участок линии, но с низким разрешением (необходимо загрузить несколько точек). Когда карта увеличена (высокий уровень масштабирования), необходимо загрузить небольшой участок линии, но с высоким разрешением. Обычно это означает, что таблица содержит гораздо больше точек с более высокими уровнями масштабирования, чем с более низкими уровнями масштабирования.

Когда пользователь перемещает или масштабирует карту, точки линий для текущего вида карты загружаются с использованием следующего запроса:

SELECT * FROM LinePoints WHERE zoom <= 7 AND MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos);

Я хотел бы оптимизировать этот запрос, чтобы он выполнялся быстро даже при большом количестве точек.

Я уже добавил индекс SPATIAL в поле pos и индекс BTREE в поле zoom. Это хорошо работает для очень высоких и очень низких уровней масштабирования:

  • При очень высоких уровнях масштабирования ограничивающая рамка мала, поэтому с помощью пространственного индекса выбирается лишь небольшое количество точек. Это небольшое количество точек можно быстро отфильтровать по уровню масштабирования.
  • При очень низких уровнях масштабирования для этого уровня масштабирования существует лишь небольшое количество точек, поэтому используется индекс BTREE. Затем можно быстро отфильтровать это небольшое количество точек с помощью ограничительной рамки.
  • Однако при средних уровнях масштабирования это зависит от данных, которые выбирает индекс MySQL, но оба случая медленные. Если он использует индекс BTREE, много точек возвращается для средних уровней масштабирования, и их фильтрация с помощью ограничивающей рамки занимает много времени. Если он использует пространственный индекс, для довольно большого ограничивающего прямоугольника возвращается много точек, и требуется много времени, чтобы отфильтровать их по уровню масштабирования. В обоих случаях запрос данных, находящихся в настоящее время в моей базе данных, занимает несколько секунд, что неприемлемо для интерактивной карты.

Как я могу оптимизировать свою базу данных?

  • Есть ли способ объединить индексы SPATIAL и BTREE?
  • Поскольку количество уровней масштабирования фиксировано, выиграю ли я от того, чтобы каким-то образом разбить таблицу на части? Мне не удалось попробовать это, потому что кажется, что MySQL не поддерживает таблицы разделения, содержащие пространственные столбцы. Получу ли я пользу от разделения данных на 20 разных таблиц?
  • Смогу ли я добиться лучших результатов, переключившись на другую систему баз данных, например PostgreSQL?

mysql indexing spatial-index spatial
person cdauth    schedule 29.05.2021    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Поскольку у вас есть четкое представление о том, как выполнить запрос, выберите один из двух запросов.

SELECT * FROM LinePoints WHERE zoom <= 3 
    HAVING  MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos);

SELECT * FROM LinePoints
    WHERE MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos)
    HAVING  zoom <= 10;

(Если Оптимизатор решит свернуть HAVING в WHERE, возможно, потребуется подзапрос. Или, может быть, какая-то функция IF().)

person Rick James    schedule 29.05.2021