Блог

Главная - Вопросы по программированию - Оптимизация этого запроса MySQL с помощью подзапроса внутри

Оптимизация этого запроса MySQL с помощью подзапроса внутри

#mysql #sql #database #query-optimization

#mysql #sql #База данных #оптимизация запроса

Вопрос:

Для заданных долготы, широты и радиуса я должен выбрать averagePrice, numberOfListings, … из базы данных с 500 000 записями данных.

(id) 1 (select_type) ПРОСТАЯ (табличная) база данных (разделы) NULL (тип) ВСЕ (possible_keys) NULL (ключ) NULL (key_len) NULL (ссылка) NULL (строки) 623612 (фильтрованный) 100.00 (дополнительный) NULL

 CREATE TABLE `database` (
  `id` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `bathrooms` int(11) DEFAULT NULL,
  `bedrooms` int(11) DEFAULT NULL,
  `person_capacity` int(11) DEFAULT NULL,
  `rev_count` int(11) DEFAULT NULL,
  KEY `hosting_id` (`hosting_id`),
  KEY `price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Выберите запрос без группировки.

 SELECT
   avg(price) as averagePrice,
   count(*) as numberOfListings,
   min(price) as minprice,
   max(price) as maxprice,
   avg(bedrooms) as averagebedrooms,
   avg(bathrooms) as averagebathrooms,
   avg(person_capacity) as averagepc,
   avg(rev_count) as averageReviews,
   avg(time_appartement) as averageDateHasBeenListed  
FROM
   (SELECT
      r.*,
      ( 6371 * acos( cos( radians(37.774929) ) * cos( radians( ANY_VALUE(`latitude` )) ) * cos( radians( ANY_VALUE(`longitude`) ) - radians(-122.419416) )   sin( radians(37.774929) ) * sin( radians( ANY_VALUE(`latitude`) ) ) ) ) AS distance        
   FROM
      `database` r       ) r  
WHERE
   distance <= 25 
   AND price >= 10  
ORDER BY
   distance ASC

Это хорошо работает при времени запроса около 1 секунды. Теперь мои следующие шаги будут заключаться в том, чтобы сгруппировать подзапрос с идентификатором и выбрать там для каждого идентификатора среднюю цену, спальни, ванные комнаты, person_capacity, rev_count и time_appartement.

 SELECT
   avg(price) as averagePrice,
   count(*) as numberOfListings,
   min(price) as minprice,
   max(price) as maxprice,
   avg(bedrooms) as averagebedrooms,
   avg(bathrooms) as averagebathrooms,
   avg(person_capacity) as averagepc,
   avg(rev_count) as averageReviews,
   avg(time_appartement) as averageDateHasBeenListed      
FROM
   (SELECT
      id,
      avg(r.price) as price,
      avg(r.bedrooms) as bedrooms,
      avg(r.bathrooms) as bathrooms,
      avg(r.person_capacity) as person_capacity,
      avg(r.rev_count) as rev_count,
      avg(r.time_appartement) as time_appartement,
      ( 6371 * acos( cos( radians(37.774929) ) * cos( radians( ANY_VALUE(`latitude` )) ) * cos( radians( ANY_VALUE(`longitude`) ) - radians(-122.419416) )   sin( radians(37.774929) ) * sin( radians( ANY_VALUE(`latitude`) ) ) ) ) AS distance            
   FROM
      `database` r 
   GROUP BY
      r.id           ) r      
WHERE
   distance <= 25 
   AND price >= 10      
ORDER BY
   distance ASC

Это работает, но проблема в том, что время для этого запроса составляет около 7 секунд.

Возможно ли сократить время? Спасибо за ваши ответы.

Комментарии:

1. Вопросы об оптимизации всегда требуют, как минимум, инструкций CREATE table для всех соответствующих таблиц и результата EXPLAIN

2. И, пожалуйста, форматируйте свои запросы

3. Кроме того, посмотрите на построение ограничивающей рамки для фильтрации геометрических данных

4. Я добавил инструкции CREATE table и отформатировал запросы

5. Отлично. Теперь для ОБЪЯСНЕНИЯ

Ответ №1:

Вы можете переместить условие цены в подзапрос.

  • ГДЕ r.price> = 10, если вы рассматриваете только записи с ценой> = 10 (до вычисления среднего)
  • ИМЕЯ среднее значение (r.price)> = 10, если учесть среднюю цену> = 10

Кроме того, убедитесь, что у вас есть индекс по идентификатору и цене

Ответ №2:

Ваш latitude и longitude хуже, чем бесполезный as VARCHAR(255) . Для домов это должно быть хорошо:

 latitude DECIMAL(6,4),
longitude DECIMAL(7,4)

Что id должно быть VARCHAR(255) ?

Вы ожидаете миллионы или миллиарды спален? Используйте TINYINT UNSIGNED (1 байт, диапазон 0 ..255) для большей эффективности.

Нет PRIMARY KEY ; это плохо для InnoDB.

Среднее значение (среднее значение (…)) математически плохо.

Ваш подзапрос подразумевает, что существует несколько строк с одним и тем же id ; что происходит?

Исправьте эти вещи, прочитайте о «ограничивающих рамках», затем вернитесь за дополнительной помощью / злоупотреблениями.