Нужна помощь в оптимизации географического поиска по широте и долготе для mysql

У меня есть таблица myisam mysql (5.0.22) с примерно 300k записями в ней, и я хочу выполнить поиск расстояния по широте / долготе в радиусе пяти миль.

У меня есть индекс, который охватывает поля широты и долготы и работает быстро (миллисекундный ответ), когда я просто выбираю широту / долготу. Но когда я выбираю дополнительные поля в таблице, это ужасно тормозит до 5-8 секунд.

Я использую myisam, чтобы воспользоваться преимуществами полнотекстового поиска. Другие индексы работают хорошо (например, выберите * из листинга, где slug = 'xxxxx').

Как я могу оптимизировать свой запрос, таблицу или индекс, чтобы ускорить работу?

Моя схема:

CREATE TABLE  `Listing` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(125) collate utf8_unicode_ci default NULL,
  `phone` varchar(18) collate utf8_unicode_ci default NULL,
  `fax` varchar(18) collate utf8_unicode_ci default NULL,
  `email` varchar(55) collate utf8_unicode_ci default NULL,
  `photourl` varchar(55) collate utf8_unicode_ci default NULL,
  `thumburl` varchar(5) collate utf8_unicode_ci default NULL,
  `website` varchar(85) collate utf8_unicode_ci default NULL,
  `categoryid` int(10) unsigned default NULL,
  `addressid` int(10) unsigned default NULL,
  `deleted` tinyint(1) default NULL,
  `status` int(10) unsigned default '2',
  `parentid` int(10) unsigned default NULL,
  `organizationid` int(10) unsigned default NULL,
  `listinginfoid` int(10) unsigned default NULL,
  `createuserid` int(10) unsigned default NULL,
  `createdate` datetime default NULL,
  `lasteditdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `lastedituserid` int(10) unsigned default NULL,
  `slug` varchar(155) collate utf8_unicode_ci default NULL,
  `aclid` int(10) unsigned default NULL,
  `alt_address` varchar(80) collate utf8_unicode_ci default NULL,
  `alt_website` varchar(80) collate utf8_unicode_ci default NULL,
  `lat` decimal(10,7) default NULL,
  `lon` decimal(10,7) default NULL,
  `city` varchar(80) collate utf8_unicode_ci default NULL,
  `state` varchar(10) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_fetch` USING BTREE (`slug`,`deleted`),
  KEY `idx_loc` (`state`,`city`),
  KEY `idx_org` (`organizationid`,`status`,`deleted`),
  KEY `idx_geo_latlon` USING BTREE (`status`,`lat`,`lon`),
  FULLTEXT KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

Мой запрос:

SELECT Listing.name, Listing.categoryid, Listing.lat, Listing.lon
, 3956 * 2 * ASIN(SQRT( POWER(SIN((Listing.lat - 37.369195) * pi()/180 / 2), 2) + COS(Listing.lat * pi()/180) * COS(37.369195 * pi()/180) * POWER(SIN((Listing.lon --122.036849) * pi()/180 / 2), 2) )) rawgeosearchdistance
FROM Listing
WHERE
    Listing.status = '2'
    AND ( Listing.lon between -122.10913433498 and -121.96456366502 )
    AND ( Listing.lat between 37.296909665016 and 37.441480334984)
HAVING rawgeosearchdistance < 5
ORDER BY rawgeosearchdistance ASC;

Объясните план без геопоиска:

    +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+
    | id | select_type | table      | type  | possible_keys   | key             | key_len |ref | rows | Extra       |
    +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+
    |  1 | SIMPLE      | Listing    | range | idx_geo_latlon  | idx_geo_latlon  | 19      | NULL |  453 | Using where |
    +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+

Разъясните план с помощью геопоиска:

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table      | type  | possible_keys   | key             | key_len | ref  | rows | Extra                       |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Listing    | range | idx_geo_latlon  | idx_geo_latlon  | 19      | NULL |  453 | Using where; Using filesort |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

Вот план объяснения с указателем покрытия. Расположение столбцов в правильном порядке имело большое значение:

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows   | Extra                                    |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+
|  1 | SIMPLE      | Listing | range | idx_geo_cover | idx_geo_cover | 12      | NULL | 453     | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+

Спасибо!


mysql geolocation optimization performance query-optimization
person Jeff    schedule 04.06.2009    source источник
comment
Опубликуйте план объяснения для быстрых и медленных запросов.   -  person jonstjohn    schedule 04.06.2009
comment
Похоже, у вас слишком много столбцов в одной таблице. Вы можете снизить производительность своих запросов, немного нормализовав структуру данных. :)   -  person tom    schedule 05.06.2009

Ответы (5)


arrow_upward
1
arrow_downward

Вероятно, вы используете «индекс покрытия» в своем запросе только по широте и долготе. Покрывающий индекс возникает, когда индекс, используемый запросом, содержит данные, которые вы выбираете. MySQL должен посещать только индекс, а не строки данных. См. здесь для получения дополнительной информации. Это объясняет, почему запрос широты и долготы выполняется так быстро.

Я подозреваю, что вычисления и огромное количество возвращаемых строк замедляют выполнение более длинного запроса. (плюс любая временная таблица, которая должна быть создана для предложения Have).

person jonstjohn    schedule 04.06.2009
comment
Вы близки к проблеме, связанной с индексом покрытия. Индекс, который у меня был, не охватывал достаточное количество столбцов. Я расширил его, чтобы охватить все необходимые столбцы, и он был быстрее, но все равно занимает 1–1,7 с. Мне также пришлось уменьшить кодировку до latin1, если я хотел включить такую ​​информацию, как телефон, электронная почта, веб-сайт и т. Д. (1 байт против 3 байта для utf8) - person Jeff; 05.06.2009

arrow_upward
4
arrow_downward

Я думаю, вам действительно стоит подумать об использовании PostgreSQL (в сочетании с Postgis).

Я отказался от MySQL для геопространственных данных (пока) по следующим причинам:

  • MySQL поддерживает только пространственные типы данных / пространственные индексы для таблиц MyISAM с присущими MyISAM недостатками (в отношении транзакций, ссылочной целостности ...)
  • MySQL реализует некоторые из спецификаций OpenGIS только на основе MBR (минимальный ограничивающий прямоугольник), что довольно бесполезно для большинства серьезных геопространственных запросов и обработки (см. эта ссылка в руководстве по MySQL). Скорее всего, вам понадобятся некоторые из этих функций раньше или позже.

PostgreSQL / Postgis с правильными пространственными индексами (GIST) и правильными запросами может работать очень быстро.

Пример: определение перекрывающихся полигонов между "небольшим" набором полигонов и таблицей с более чем 5 миллионами (!) очень сложных полигонов, вычислить степень перекрытия между этими результатами + сортировка. Среднее время выполнения: от 30 до 100 миллисекунд (на этой конкретной машине, конечно, много оперативной памяти. Не забудьте настроить установку PostgreSQL ... (прочтите документацию)).

person ChristopheD    schedule 04.06.2009
comment
+1, да, это пространственная проблема, поэтому она требует и пространственного решения. - person none; 05.06.2009

arrow_upward
0
arrow_downward

В зависимости от количества ваших списков вы можете создать представление, содержащее

Listing1Id, Listing2ID, Distance

По сути, все расстояния должны быть "предварительно рассчитаны".

Тогда вы могли бы сделать что-нибудь вроде:

Выберите листинг2ID из v_Distance d, где расстояние ‹5, а листинг1ID = XXX

person Community    schedule 04.06.2009

arrow_upward
0
arrow_downward

Вам действительно следует избегать использования математических вычислений в операторе select. Вероятно, это источник многих ваших замедлений. Помните, что SQL - это язык запросов; он действительно не оптимизирован для тригонометрических функций.

SQL будет быстрее, и ваши общие результаты будут быстрее, если вы выполните очень наивный поиск на расстоянии (который даст больше результатов), а затем просеиваете свои результаты.

Если вы хотите использовать расстояние в своем запросе, по крайней мере, используйте вычисление квадрата расстояния; Вычисления sqrt заведомо медленные. Квадратное расстояние использовать намного проще. При вычислении квадрата расстояния просто используется квадрат расстояния вместо расстояния; это намного проще. Для декартовых систем координат, поскольку сумма квадратов коротких сторон прямоугольного треугольника равна квадрату гипотенузы, проще вычислить квадратное расстояние (просто суммировать два квадрата), чем вычислить расстояние; все, что вам нужно сделать, это убедиться, что вы возводите в квадрат расстояние, с которым хотите сравнить (поэтому вместо того, чтобы найти точное расстояние и сравнить его с желаемым расстоянием (скажем, 5), вы найдете квадратное расстояние и сравните его в квадрат желаемого расстояния (25, если желаемое расстояние было 5).

person Paul Sonier    schedule 04.06.2009
comment
Вы знаете, где я могу найти дополнительную информацию о вычислении квадрата расстояния? - person Jeff; 05.06.2009
comment
@ Джефф: Я добавлю это к ответу. - person Paul Sonier; 05.06.2009
comment
Вот простое предложение: определите вычисление расстояния по дуге большого круга как сохраненную функцию и переработайте свой запрос так, чтобы это было третье предложение AND после широты и долготы. Сначала сделайте свой запрос по широте, а затем по длинному запросу. Морская миля к северу или югу составляет примерно один градус широты во всем мире, но расстояние в градус долготы зависит от широты. - person O. Jones; 28.11.2009

arrow_upward
0
arrow_downward

Когда я реализовал поиск по географическому радиусу, я просто загрузил все наши почтовые индексы в память с их длинной широтой, а затем использовал свою начальную точку с радиусом, чтобы получить список почтовых индексов в радиусе, а затем использовал это для своего запроса к базе данных. Конечно, я использовал solr для поиска, потому что пространство поиска находилось в диапазоне 20 миллионов строк, но должны применяться те же принципы. Приносим извинения за поверхностный ответ, так как я разговариваю по телефону.

person Hardwareguy    schedule 04.06.2009