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

Question

[Антон Шелестов]

Всем привет.
Краткое описание:
В базе есть таблица со всеми координатами Московского метро (id, name, lat, lng) - lat и lng это координаты широты и долготы.
Так же в базе есть другая таблица, например таблица с организациями, в ней куча полей и тоже есть поля lat и lng (координаты этой организации)

Задача:
Получить все метро в радиусе 2000 метров от организации.

И вот тут вопрос: какое из решений лучше по производительности?
Решение 1:
Сразу вычисляем вхождение в радиус прямо в sql запросе:
пример написан под laravel, знающий думаю разберется

$metro = \App\Metro::select(\DB::raw('*, (
                                                6378137 * acos (
                                                  cos ( radians(' . $res['lat'] . ') )
                                                  * cos( radians( lat ) )
                                                  * cos( radians( lng ) - radians(' . $res['lng'] . ') )
                                                  + sin ( radians(' . $res['lat'] . ') )
                                                  * sin( radians( lat ) )
                                                )
                                              ) AS distance'))
            ->having('distance', '<=', $res['radius_metro'])
            ->orderBy('distance')
            ->get();

При таком варианте у нас всегда будет свежий результат, например если добавится новая станция метро.

Решение 2:
Заносить результат поиска ближайших метро в отдельную таблицу и периодически ее обновлять по cron например.

Вот интересует какой вариант лучше использовать, мне нравится 1й.
Планируется сильно нагруженный портал

Какие мысли?
Спасибо!

Answer 1

[Станислав Почепко]

Подумай на счёт кеширования. Думаю первый вариант лучше, но надо подключить кеширование. И если что-то связанное меняется (Новая станция метро) то чистить кэш.

Comments

[Антон Шелестов]

Уже думаю))

Answer 2

[Максим Тимофеев]

Идея с таблицей - бред, но направление верное. Надо использовать Ваш запрос, но кешировать его результаты. Не знаю как в laravel, но в Yii точно можно сделать кеш зависимым от запроса count() например, который будет возвращать кол-во станций. А кеш можно уже хранить в бд или в файле - это второй вопрос, мне кажется для этого filecache более подходит. Можно в принципе сделать кеш вечным и очищать при добавлении новой станции.

Comments

[Антон Шелестов]

Спасибо за комментарий.
Да второй вариант мне тоже не нравится)) но лучше рассмотреть сразу все варианты решения чем потом все переделывать)
Забыл кстати про кеширование, в laravel он есть laravel.su/docs/5.0/queries#caching-queries

[Максим Тимофеев]

Антон Шелестов: Наверняка в laravel он есть и не хуже yii, я просто не знаю его глубоко, поковыряйтесь в доках, наверняка есть удобное решение из коробки

[Антон Шелестов]

Максим Тимофеев: Да есть там кеширование в запросах, все работает отлично! В админке будет без кеширования, а вот на внешней части уже закешируем)

Answer 3

[Евгений Вольф]

Всё на самом деле зависит от того, что лично Вы подразумеваете под определением "сильно нагруженный портал" и от многих других факторов, в том числе, от используемого железа, версии MySQL (и/или его форка) и конечных параметров и настроек, и так далее. А так же, от "качества" администратора, которому вверят эту БД.

Субъективно, оценивая Ваш вопрос, я бы порекомендовал примерно следующее:
В "интернете", активно практикуется подход кэширования данных, не зависимо от того, какие это данные именно. Кэшируют всё, от отдельных блоков на сайте, до целых страниц вместе с cookie's, картинками и скриптами.

В пользу кэша говорят многие факторы. Против же кэширования обычно приводятся два основных аргумента:
а) Лишний расход оперативной памяти и/или жесткого диска
б) Кэш не стоит применять в тех случаях, когда ресурсозатраты (машинные) на кэширование выше, чем полученный эффект экономии от получения данных из кэша (обычно это те случаи, когда количество циклов записи меньше, или незачительно больше чем количество циклов чтения данных)

В вашем же случае, кэширование возможно в двух вариантах:
1. MySQL (в отличии от некоторых других БД) сам кэширует результаты запросов, причем делает он это по умолчанию, но параметры кэширования можно (и чаще всего нужно) настраивать дополнительно
2. Использование кэша с помощью специально разработанных для этих целей программ, например Redis или Memcached. PHP (и Laravel в т.ч., если мне не изменяет память) отлично дружат с ними.

Продолжая мысль, в Вашем случае, можно использовать один двух вариантов: либо ограничиться тем, что MySQL сам закэширует результаты многих запросов и сам будет брать их из кэша (причем это касается не только запросов относительно координат, но и почти всех остальных запросов),

либо, Вы можете использовать для хранения подобных данных (например, соотношения координат с результатами поиска) либо в таблицы в памяти (в MySQL есть тип таблиц "MEMORY"), либо хранить эти данные в Redis/Memcached/etc, глобально суть не изменится, скорость работы обоих вариантов (Таблицы в памяти vs Redis/Memcached) будет примерно сопоставимой.

Обновлять данные в хранилище можно на основе событий, реализация которых в достаточной мере присутствуют в Laravel. То есть, вкратце выглядит это так:
1. Обновляется (добавляется, изменяется) какой-то блок данных, связанный с фирмой, это в свою очередь порождает какое-то событие (одно из событий)
2. Слушатель событий получает данные и делает соответствующие изменения в кэше данных, таким образом, данные в кэше всегда актуальны (либо рассинхронизация данных довольно мизерная, в пределах примерно секунды) и никакие CRON'ы, которые бестолку будут мучить базу или кэш-хранилище - не нужны.

Comments

[Антон Шелестов]

Спасибо за развернутый ответ!
Много информации для размышления) почитаю про Redis и Memcached

Answer 4

[sim3x]

СУБД должна давать апи попроще, без арктангенсов и быстро
Постгрес такое умеет из коробки
gis.stackexchange.com/questions/77072/return-all-r...
Возможно мускул тоже
Как минимум сравни их производительность на своем кейсе

Предварительно оптимизировать не нужно

Answer 5

[Viktor Vsk]

Как часто добавляется новое метро или изменяется адрес компании?
У вас есть таблица с данными метро, таблица с данными компаний и таблица с ссылкой на компанию, на метро и расстоянием между ними
В последнюю таблицу вносите данные при создании\обновлении компании\метро
Можно, конечно, для экономии в последней таблице хранить только записи, которые удовлетворяют условию (расстояние <= 2000 метров), но 2000 метров у вас очень скоро станет 1500 метров или 2500 метров.

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

Answer 6

[Andrzej Wielski]

Вы говорите о крупном проекте, но используете MySql. Не надо так.
Копайте в сторону PostgreSQL, у него есть великолепные, мощные инструменты по работе с гео-данными.