Какой самый быстрый способ поиска больших таблиц для точек в радиусе MySQL (широта и долгота)

В настоящее время у меня есть несколько таблиц с 100k + строк. Я пытаюсь найти данные следующим образом.

SELECT
*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM stops
HAVING distance < 5
ORDER BY distance limit 100

Но в настоящее время этот метод замедляется при высокой нагрузке. Для выполнения некоторых запросов требуется более 20 секунд.

Если кто-нибудь знает какие-либо лучшие способы оптимизации, это было бы здорово.

 e4c504 авг. 2016 г., 01:27
Вы проверили геопространственные типы MySQL, как предложено?
 Rick James24 июл. 2016 г., 16:41

Ответы на вопрос(2)

ты также выполняют поиск по широте и долготе, но мы используем более старую версию MySQL (5.1-кое-что) (без пространственных индексов). Ваш запрос похож на наш, но наш основан на радианах. В зависимости от ваших конкретных потребностей, вы можете оптимизировать его (из того, что у вас есть) совсем немного.

определенно удалите sqrt () из запроса к базе данных, он должен вычисляться для каждой строки - вычислять его только в конце при отображении фактического расстояния до пользователя - также возвести в квадрат «имеющее расстояние <5» до «<25». Sqrt является дорогим и легко перемещается туда, где его не нужно вычислять.заключите в кавычки значение lat / lon '49 .1044302', чтобы оно было строго int, и выполняйте проверку типа lat / lon вне запроса. Это не ускорит его, но предотвратит неправильное приведение из-за ложных конечных пробелов в переменной lat / lon.Преобразуйте 5 в фактическую разницу градусов по широте / лоту в каждом направлении + / 5, чтобы получить ограничивающий диапазон (как бы поле). Добавьте его к части запроса «где» - это ограничение даст вам существенно сокращенный, почти точный набор строк результатов - в основном это x и y +/- диапазон по широте, а lon - верхняя граница результатов - вычисляемые диагонали лишь незначительно влияют на результаты и их расстояния.Переместите как можно большую часть математики за пределы выбора и куда - для этого вам нужно будет просканировать всю таблицу и создать временную таблицу, вычисляемую в каждой строке. Большая часть математики в запросе может быть преобразована в константу.Ускорьте уменьшение строки (поле выбора) еще больше, уменьшив разрешение по широте / долготе (копирование) в другое поле (и, возможно, умножьте на 10 или 100 и преобразуйте в INT) и добавив индекс для этого поля, и используя это поле с +/- границами в, где, по крайней мере, тогда он сможет использовать ключ - mysql может уменьшить, и эти результаты будут намного быстрее.

По крайней мере, так мы это делаем.

 Rick James24 июл. 2016 г., 16:37
49.1044302 не "int". «число» будет делать. Кроме того, кавычки не имеют значения при сравнении числового столбца.
 Rick James24 июл. 2016 г., 16:39
Ограничительная рамка (# 3) является наиболее продуктивной частью этого ответа - при условии, что у вас естьINDEX(latitude) или жеINDEX(longitude).
 Beracah25 июл. 2016 г., 00:43
Кроме того, даже если у вас нет INDEX для широты и долготы, указание диапазона в предложении where приведет к уменьшению результирующего набора строк, получаемого при сканировании таблицы, и уменьшению вычислений, затрачиваемых на геометрическое расстояние. Я знаю, что эти пункты верны, потому что, несмотря на код, указанный на вашем сайте, мне пришлось заставить работать запросы lan / lot для больших таблиц в mysql версии 3.23.28. Первый релиз кандидата.
 Beracah25 июл. 2016 г., 00:06
Неверный Рик. В то время как «float» не является «int», существует точка, в которой он должен умножить широту или долготу и снизить точность, чтобы извлечь выгоду из числового, предпочтительного целочисленного индекса. Также цитаты имеют значение, но не по той причине, на которую вы намекаете. Если пробел каким-то образом превращается в переменную lat или long, то $ var не будет преобразован в числовое значение оптимизатором mysql. Оставляя кавычки, любые ошибочные пробелы (возможно, из-за пользовательского ввода) будут заканчиваться пробелами в самом sql и обезвреживаться. Не спешите нажимать кнопку «идиот».

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

Используя сравнение сST_Distance или создание геометрии с радиусом интереса вместе с ST_within может дать вам хорошие результаты и может быть намного быстрее, чем текущий. Однако лучший и быстрый способ достичь этого,ST_Dwithin еще не реализован в MySQL.

 Michael - sqlbot24 июл. 2016 г., 17:09
Формула исходного запроса также может быть переписана наизнанку в хранимой функции, которая вычисляет и возвращает многоугольник, представляющий ограничивающий прямоугольник расстояния x от целевого местоположения, поскольку iircST_Distance это не хаверсин, это плоский.
 Michael - sqlbot24 июл. 2016 г., 16:59
Добавление пространственного индекса, использование пространственных функций для получения набора грубых совпаденийИ еще используя текущие предикаты вWHERE Предложение наряду с функцией пространственного анализа также будет жизнеспособным. Оптимизатор будет автоматически знать (как это умно) использовать пространственный индекс для поиска совпадений кандидатов примерно в правой области, а затем сужать их далее, «используя где», чтобы фильтровать совпадающие строки по непространственным предикатам, давая гораздо лучшая производительность при сохранении точности существующей логики. Пространство, безусловно, путь вперед.

Ваш ответ на вопрос