Оптимизация запросов MySQL со сложным индексом

У меня есть база данных, используемая для простого обратного геокодирования. База данных опирается на таблицу, содержащую широту, долготу и название места. Каждый раз, когда пара широта, долгота отсутствует или, лучше, каждый раз, когда искомая широта, долгота слишком сильно отличается от существующей широты, долготы, я добавляю новую строку, используя сервис обратного геокодирования GoogleMaps. Ниже код для создания таблицы адресов:

CREATE TABLE `data_addresses` (
    `ID` int(11) NOT NULL COMMENT 'Primary Key',
    `LAT` int(11) NOT NULL COMMENT 'Latitude x 10000',
    `LNG` int(11) NOT NULL COMMENT 'Longitude x 10000',
    `ADDRESS` varchar(128) NOT NULL COMMENT 'Reverse Geocoded Street Address'
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
ALTER TABLE `data_addresses`
    ADD PRIMARY KEY (`ID`),
    ADD UNIQUE KEY `IDX_ADDRESS_UNIQUE_LATLNG` (`LAT`,`LNG`),
    ADD KEY `IDX_ADDRESS_LAT` (`LAT`),
    ADD KEY `IDX_ADDRESS_LNG` (`LNG`);
ALTER TABLE `data_addresses`
    MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key';

Как видите, хитрость заключается в том, чтобы использовать два индекса для широты и долготы. Поскольку обычно широта и долгота являются плавающими, мы используем их значение, умноженное на 10000, поэтому каждая пара широта/долгота уникальна. Это подразумевает разрешение около 50 м, которое удовлетворяет мои потребности.

Теперь проблема: каждый раз, когда мне нужно знать, присутствует ли заданная широта/долгота (MyLat, MyLon) или нет, я выполняю следующий запрос:

SELECT `id`, ROUND(SQRT(POW(ABS(`LAT`-ROUND(MyLat*10000)),2)+POW(ABS(`LNG`-ROUND(MyLon*10000)),2))) AS R FROM splc_smarttrk.`data_addresses` ORDER BY R ASC LIMIT 1

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

Проблема в том, что, несмотря на индексы, которые я разместил, этот запрос слишком медленный (занимает около 2 секунд на сервере с 2x Xeon). Ниже результаты объяснения:

21.03.2018

Я пытался загрузить снимок результатов объяснения в виде изображения, но редактор stackoverflow ужасен, когда вам нужно загрузить изображение... не спрашивайте меня, почему он вверху страницы... 21.03.2018
Вы ORDER BY в вычисляемом столбце, который никогда не оптимизировался с помощью индекса, поскольку MySQL должен использовать алгоритм сортировки быстрой сортировки для оценки 615088 строк... использование файловой сортировки в дополнительном столбце вне вывода объяснения указывает, что... 21.03.2018
@RaymondNijland, ты прав, это суть проблемы. Итак, реальный вопрос: как я могу проверить ближайшую пару lat/lon среди доступных lat/lon в таблице адресов? Мне просто нужно найти ближайший, независимо от того, как я это проверю. Можно ли создать расчетный индекс, возможно, на основе пары широты и долготы? 21.03.2018
Проверьте мой ответ, который может вам помочь 21.03.2018
Не трудитесь делать ID ПК дважды. 23.03.2018

Ответы:

Разве вы не можете оптимизировать это, извлекая фиксированный набор данных с ближайшими широтами и долготами, вычисляя рейтинг (R) и выбирая наименьший рейтинг в этом фиксированном наборе данных.

p.s. не проверено, могут быть ошибки в сортировке. но это может помочь вам на вашем пути.

SELECT 
   id 
 , ROUND(SQRT(POW(ABS(`LAT`-ROUND([LAT]*10000)),2)+POW(ABS(`LNG`- ROUND([LNG]*10000)),2))) AS R

FROM ( 

  SELECT 
   LAT 
  FROM  
   data_addresses
  WHERE 
   LAT <= [LAT]  
  ORDER BY
   LAT DESC
  LIMIT 100

  UNION ALL

  SELECT 
   LAT   
  FROM 
   data_addresses
  WHERE 
   LAT >= [LAT]
  ORDER BY
   LAT ASC
  LIMIT 100

  SELECT 
   LNG 
  FROM 
   data_addresses
  WHERE 
   LNG <= [LNG]
  ORDER BY
   LNG DESC
  LIMIT 100

  UNION ALL

  SELECT 
   LNG
  FROM 
   data_addresses
  WHERE 
   LNG >= [LNG]
  ORDER BY
   LNG ASC
  LIMIT 100
) 
 AS data_addresses_range
ORDER BY 
 R ASC
LIMIT 1

21.03.2018

ваше предложение было правильным, хотя и не на 100% адекватным, но вы указали мне правильный путь решения проблемы, поэтому я дал вам заслуженный +1. Проблема заключалась в том, что мне действительно не нужно расстояние, просто нужно знать, превышает ли расстояние определенный уровень. Поэтому я изменил запрос, как описано ниже в моем ответе. 21.03.2018

Вместо вычисления расстояния (или в дополнение к нему) предоставьте «ограничивающий прямоугольник». Это будет намного быстрее.

Еще быстрее будет сложный код здесь: mysql.rjweb.org/doc.php/latlng

Если у вас есть UNIQUE KEY IDX_ADDRESS_UNIQUE_LATLNG (LAT, LNG), KEY IDX_ADDRESS_LAT (LAT) больше не нужно.

*10000 может поместиться в MEDIUMINT. И хорошо около 16 метров или 52 футов.

23.03.2018

поздравляю! Ваша ссылка на mysql.rjweb.org/doc.php/latlng действительно ориентирована на моя проблема! На самом деле мое решение (разработанное до вашего предложения) идет в том же направлении, реализуя ограничивающую рамку, но без разделения. Действительно полезная и очень хорошо сделанная веб-страница! Огромное спасибо! 26.03.2018

Следуя предложению Raymond Nijland, я изменил запрос следующим образом:

SELECT  `id` AS ID,
ROUND(SQRT(POW(ABS(`LAT`-ROUND(NLat*10000)), 2) +
           POW(ABS(`LNG`-ROUND(NLon*10000)), 2))
     ) AS RT INTO  ADDR_ID, RATING
    FROM  splc_smarttrk.`data_addresses`
    WHERE  (`LAT` BETWEEN (ROUND(NLat*10000)-R) AND (ROUND(NLat*10000)+R))
      AND  (`LNG` BETWEEN (ROUND(NLon*10000)-R) AND (ROUND(NLon*10000)+R))
    ORDER BY  RT ASC
    LIMIT  1;

этот трюк уменьшает набор данных до 10 записей в худшем случае, поэтому скорость довольно хорошая, несмотря на предложение ORDER BY. На самом деле мне не нужно знать расстояние от существующей точки, мне просто нужно знать, превышает ли это расстояние заданный предел (здесь, если оно находится в пределах прямоугольника 10x10, это означает, что R = 5).

21.03.2018

он же ограничивающий прямоугольник. Нужен косинус широты, чтобы учесть, что линии долготы расположены ближе друг к другу, чем широта. 26.03.2018

Новые материалы

Коллекции публикаций по глубокому обучению

Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..

Представляем: Pepita

Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..

Советы по коду Laravel #2

1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..

Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)

Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..

3 способа решить квадратное уравнение (3-й мой любимый) -

1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..

Создание VR-миров с A-Frame

Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..

Демистификация рекурсии

КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..

Метки

Machine Learning JavaScript Data Science Artificial Intelligence Web Development Software Development Python Coding Deep Learning AI React Software Engineering Nodejs Front End Development Java Algorithms Computer Science Typescript Data Tech Development CSS HTML Programming Languages Startup Productivity Python Programming Tutorial Angular Neural Networks Computer Vision Open Source Javascript Tips AWS Education Learning NLP Mlops