Нужна помощь в оптимизации лат / лон гео поиска для mysql
У меня есть таблица myisam mysql (5.0.22) с примерно 300 тысячами записей, и я хочу выполнить поиск по широте / долготе в радиусе пяти миль.
У меня есть индекс, который охватывает поля широты и долготы и быстрый (миллисекундный ответ), когда я просто выбираю широту / долготу. Но когда я выбираю дополнительные поля в таблице, это ужасно замедляется до 5-8 секунд.
Я использую myisam, чтобы воспользоваться полнотекстовым поиском. Другие индексы работают хорошо (например, select * from Listing, где 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 | стол | тип | возможные_ключи | ключ | key_len |ref | строки | Extra |
+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+
| 1 | ПРОСТО | Листинг | диапазон | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Используя где |
+ ---- + ------------- + ------------ + ------- + --------- -------- + ----------------- + --------- + ------ + ------ + ------------- +Объясните план с геоархива:
+ ---- + ------------- + ------------ + ------- + --------- -------- + ----------------- + --------- + ------ + ------ + ----------------------------- + | id | select_type | стол | тип | возможные_ключи | ключ | key_len |ref | строки | Extra | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+ | 1 | ПРОСТО | Листинг | диапазон | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Используя где; Использование сортировки файлов | + ---- + ------------- + ------------ + ------- + --------- -------- + ----------------- + --------- + ------ + ------ + ----------------------------- +
Вот план объяснения с индексом покрытия. Наличие столбцов в правильном порядке имело большое значение:
+ ---- + ------------- + -------- + ------- + ------------- - +---------------+---------+------+--------+------------------------------------------+ | id | select_type | стол | тип | возможные_ключи | ключ | key_len |ref | строки | Extra | +----+-------------+--------+-------+-------------- +---------------+---------+------+--------+------------------------------------------+ | 1 | ПРОСТО | Листинг | диапазон | idx_geo_cover | idx_geo_cover | 12 | NULL | 453 | Используя где; Использование индекса; Использование сортировки файлов | + ---- + ------------- + -------- + ------- + ------------- - + --------------- + --------- + ------ + -------- + ----- ------------------------------------- +
Спасибо!
5 ответов
Вы, вероятно, используете "индекс покрытия" в своем запросе только по широте / долготе. Индекс покрытия возникает, когда индекс, используемый запросом, содержит данные, которые вы выбираете. MySQL нужно только посетить индекс, а не строки данных. Смотрите это для получения дополнительной информации. Это объясняет, почему запрос lat / lon такой быстрый.
Я подозреваю, что вычисления и огромное количество возвращаемых строк замедляют более длинный запрос. (плюс любая временная таблица, которая должна быть создана для предложения has).
Я думаю, что вы действительно должны рассмотреть использование PostgreSQL (в сочетании с Postgis).
Я отказался от MySQL для геопространственных данных (на данный момент) по следующим причинам:
- MySQL поддерживает только пространственные типы данных / пространственные индексы в таблицах MyISAM с недостатками, присущими MyISAM (в отношении транзакций, ссылочной целостности...)
- MySQL реализует некоторые спецификации OpenGIS только на основе MBR (минимальный ограничивающий прямоугольник), что довольно бесполезно для большинства серьезных геопространственных запросов (см. Эту ссылку в руководстве MySQL). Скорее всего, вам понадобятся некоторые из этих функций раньше.
PostgreSQL / Postgis с правильными (GIST) пространственными индексами и правильными запросами может быть чрезвычайно быстрым.
Пример: определив перекрывающиеся полигоны между "небольшим" набором полигонов и таблицей с более чем 5 миллионами (!) Очень сложных полигонов, вычислите количество совпадений между этими результатами + сортировка. Среднее время выполнения: от 30 до 100 миллисекунд (у этой конкретной машины много оперативной памяти. Не забывайте настраивать установку PostgreSQL... (см. Документацию)).
Когда я реализовал поиск по географическому радиусу, я просто загрузил все используемые нами Zip-коды в память с их широтой, а затем использовал мою начальную точку с радиусом, чтобы получить список почтовых индексов по радиусу, а затем использовал ее для своего запроса в БД. Конечно, я использовал solr для поиска, потому что пространство поиска находилось в диапазоне 20 миллионов строк, но должны применяться те же принципы. Извиняюсь за поверхностность этого ответа, поскольку я нахожусь на моем телефоне.
Вы действительно должны избегать такого большого количества математики в своем утверждении. Это, вероятно, источник многих ваших замедлений. Помните, что SQL - это язык запросов; это действительно не оптимизировано для тригонометрических функций.
SQL будет быстрее, и ваши общие результаты будут быстрее, если вы будете выполнять очень наивный дистанционный поиск (который даст больше результатов), а затем потерять результаты.
Если вы хотите использовать расстояние в своем запросе, по крайней мере, используйте вычисление квадрата расстояния; Квадратные вычисления печально известны своей медлительностью. Квадратное расстояние намного проще в использовании. Вычисление квадрата расстояния просто использует квадрат расстояния вместо расстояния; это намного проще. Для декартовых систем координат, поскольку сумма квадратов коротких сторон прямоугольного треугольника равна квадрату гипотенузы, проще вычислить квадратное расстояние (просто сложить два квадрата), чем вычислить расстояние; все, что вам нужно сделать, это убедиться, что вы возводите в квадрат расстояние, с которым хотите сравнить (поэтому вместо того, чтобы находить точное расстояние и сравнивать его с желаемым расстоянием (скажем, 5), вы находите квадратное расстояние и сравниваете это на квадрат желаемого расстояния (25, если желаемое расстояние было 5).
В зависимости от количества ваших объявлений вы можете создать представление, содержащее
Listing1Id, Listing2ID, Расстояние
В основном просто есть все расстояния "заранее рассчитаны"
Тогда вы можете сделать что-то вроде:
Выберите list2ID из v_Distance d, где расстояние< 5, и перечисление1ID = XXX