Я управляю социальной сетью на основе геолокации. Участники могут видеть других участников, исходя из того, насколько они близки.
Прямо сейчас мой запрос MySQL выглядит так:
$lat_min = $geo['user_lat'] - 5; $lat_max = $geo['user_lat'] + 5; $long_min = $geo['user_long'] - 5; $long_max = $geo['user_long'] + 5; $members_query = "SELECT " . $fields . " FROM members WHERE (user_lat BETWEEN " . $lat_min . " AND " . $lat_max . " AND user_long BETWEEN " . $long_min . " AND " . $long_max . ") OR (gc_lat BETWEEN " . $lat_min . " AND " . $lat_max . " AND gc_long BETWEEN " . $long_min . " AND " . $long_max . ")"; user_lat и user_long – это координаты, основанные на геолокации, если они включены в их браузере. gc_lat и gc_long и координаты на основе их IP-адреса. Эти строки индексируются в базе данных. Я тяну всех членов в течение 10 градусов.
Проблема заключается в том, что этот запрос занимает около 2 секунд для наших 250 000+ участников, и мы хотим, чтобы сайт масштабировался.
ATTEMPT 2: Я попытался присвоить квадранты каждому члену, например, «36x-99» … Я округляю широту и долготу до ближайшего кратного 3, чтобы маркировать квадрант, а затем я только вытягиваю квадранты в пределах 12 градусов квадрант, в котором находится член.
$members_query = "SELECT " . $fields . " FROM members WHERE quadrant IN ('36x-99', '33x-99', '30x-99', ...);
Это не меняло заметной разницы в скорости запросов.
У кого-нибудь есть идеи, что я должен делать? Мне нужно найти решение, которое позволит сайту лучше масштабироваться.
Проблема в том, как вы храните данные в базе данных, не подходит для типа выполняемой задачи. Использование значений Point в точках данных Geometry – путь. На самом деле закодировано что-то еще 4 года назад для этой цели, но есть проблемы с поиском. Но этот пост, похоже, хорошо его охватывает.
EDIT. Хорошо, нашел мой старый код, но это относится к старым клиентским данным, которые я, очевидно, не могу предоставить. Но ключом к скорости с координатами в базах данных является использование данных POINT хранящихся в таблице базы данных, с типом GEOMETRY . Подробнее здесь, на официальном сайте MySQL. Поскольку мне понадобилась причина пересмотреть этот тип кода, а концепции – на некоторое время вот быстрый скрипт MySQL, который я взбивал, чтобы создать образец таблицы с образцами данных, чтобы передать основные понятия. Как только вы поймете, что происходит, оно открывает множество интересных вариантов.
Также было найдено это большое / простое объяснение концепции.
И нашел еще одну большую оценку пространственных данных в MySQL 5.6. Много информации об индексах и производительности. В частности, относительно производительности пространственного индекса MySQL:
Таблицы MyISAM поддерживают пространственные индексы , поэтому вышеупомянутые запросы будут использовать эти индексы.
А с другой стороны:
Двигатель InnoDB не поддерживает пространственные индексы , поэтому эти запросы будут медленными.
И вот мои основные сценарии тестирования MySQL, чтобы помочь проиллюстрировать концепцию:
/* Create the database `spatial_test` */ CREATE DATABASE `spatial_test` CHARACTER SET utf8 COLLATE utf8_general_ci; /* Create the table `locations` in `spatial_test` */ CREATE TABLE `spatial_test`.`locations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `coordinates` point NOT NULL, UNIQUE KEY `id` (`id`), SPATIAL KEY `idx_coordinates` (`coordinates`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; /* Insert some test data into it. */ INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(27.174961 78.041822)')); INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(27.985818 86.923596)')); INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(44.427963 -110.588455)')); INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(19.896766 -155.582782)')); INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(40.748328 -73.985560)')); INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(40.782710 -73.965310)')); /* A sample SELECT query that extracts the 'latitude' & 'longitude' */ SELECT x(`spatial_test`.`locations`.`coordinates`) AS latitude, y(`spatial_test`.`locations`.`coordinates`) AS longitude FROM `spatial_test`.`locations`; /* Another sample SELECT query calculates distance of all items in database based on GLength using another set of coordinates. */ SELECT GLength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(`spatial_test`.`locations`.`coordinates`))), GeomFromText(astext(PointFromWKB(POINT(40.782710,-73.965310))))))) AS distance FROM `spatial_test`.`locations` ; /* Yet another sample SELECT query that selects items by using the Earth's radius. The 'HAVING distance < 100' equates to a distance of less than 100 miles or kilometers based on what you set the query for. */ /* Earth's diameter in kilometers: 6371 */ /* Earth's diameter in miles: 3959 */ SELECT id, (3959 * acos(cos(radians(40.782710)) * cos(radians(x(`spatial_test`.`locations`.`coordinates`))) * cos(radians(y(`spatial_test`.`locations`.`coordinates`)) - radians(-73.965310)) + sin(radians(40.782710)) * sin(radians(x(`spatial_test`.`locations`.`coordinates`))))) AS distance FROM `spatial_test`.`locations` HAVING distance < 100 ORDER BY id ;