Mysql: наш запрос медленно работает — что делать?

Часто мы видим что простое, прямое решение — не лучшее, и не потом что оно плохое — просто у нас уже в базе пару миллионов строк, хостинг недорогой, и сайт начинает вести себя неприлично. Зависание более чем на 1 секунду на запрос — это недопустимо для небольших сайтов. Дальше пример и шаги как работать со сложным медленным запросом

Наш начальный запрос

Слишком длинный чтобы понять сразу что не так, поэтому просто посмотрим и начнем бить

SELECT t.tour_id, t.title, ci.name, co.name, t.is_hot = 1,
t.sale_percent, t.price_currency, t.nights_cnt,
(
t.additional_transfer = 1 OR
(h.transfer  IS NOT NULL AND h.transfer  = 1) OR
(re.transfer IS NOT NULL AND re.transfer = 1) OR
(co.transfer IS NOT NULL AND co.transfer = 1)
),
t.additional_transfer_text, h.transfer_text, re.transfer_text, co.transfer_text,
t.people_cnt, t.children_cnt, t.room_category,
 
GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
GROUP_CONCAT(DISTINCT ht.type_id, '=', ht.title, '=', ht.slug),
GROUP_CONCAT(DISTINCT ttg.id, '=', ttg.title),
GROUP_CONCAT(DISTINCT htg.id, '=', htg.title),
 
GROUP_CONCAT(DISTINCT d.date, '=', d.price ORDER BY d.date ASC),
h.id, h.name, h.stars
FROM       a_tours             t
INNER JOIN a_tour_dates        d   ON d.tour_id   = t.tour_id AND d.date >= NOW()
LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = t.tour_id
LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
LEFT  JOIN pivot_hotel_new_tour_type p2  ON p2.hotel_id = t.hotel_id
LEFT  JOIN cfg_new_tour_types        ht  ON ht.type_id  = p2.new_tour_type_id
LEFT  JOIN pivot_tour_tour_tag   p3  ON p3.tour_id  = t.tour_id
LEFT  JOIN cfg_tour_tags         ttg ON ttg.id      = p3.tour_tag_id
LEFT  JOIN pivot_hotel_tour_tag  p4  ON p4.hotel_id = t.hotel_id
LEFT  JOIN cfg_tour_tags         htg ON htg.id      = p4.tour_tag_id
INNER JOIN info_countries         co  ON co.id          = t.country_id
LEFT  JOIN info_regions           re  ON re.id          = t.region_id
INNER JOIN info_cities            ci  ON ci.id          = t.city_id
LEFT  JOIN info_cities            fci ON fci.id         = t.from_city_id
INNER JOIN info_hotels            h   ON h.id           = t.hotel_id
WHERE t.is_active = 1
GROUP BY t.tour_id
ORDER BY MIN(d.price) LIMIT 9

Базовый запрос

Время выполнения 0.01 это наш запрос до внесения «до…» джоинов

SELECT t.tour_id, t.title, ci.name, co.name, t.is_hot = 1,
t.sale_percent, t.price_currency, t.nights_cnt,
 
(t.additional_transfer = 1 OR
(h.transfer  IS NOT NULL AND h.transfer  = 1) OR
(re.transfer IS NOT NULL AND re.transfer = 1) OR
(co.transfer IS NOT NULL AND co.transfer = 1)
),
 
t.additional_transfer_text, h.transfer_text, re.transfer_text, co.transfer_text,
t.people_cnt, t.children_cnt, t.room_category,
 
GROUP_CONCAT(DISTINCT d.date, '=', d.price ORDER BY d.date ASC),
 
h.id, h.name, h.stars
 
FROM       a_tours                t
INNER JOIN a_tour_dates           d   ON d.tour_id   = t.tour_id AND d.date >= NOW()
INNER JOIN info_countries         co  ON co.id          = t.country_id
LEFT  JOIN info_regions           re  ON re.id          = t.region_id
INNER JOIN info_hotels            h   ON h.id           = t.hotel_id
INNER JOIN info_cities            ci  ON ci.id          = t.city_id
WHERE t.is_active = 1
GROUP BY t.tour_id
ORDER BY MIN(d.price) LIMIT 9

Один джоин тяжелый

Еще вменяемое время хотя уже тяжелый джоина по тегам тура (таблица pivot_tour_new_tour_type)

SELECT t.tour_id, t.title, ci.name, co.name, t.is_hot = 1,
t.sale_percent, t.price_currency, t.nights_cnt,
 
(t.additional_transfer = 1 OR
(h.transfer  IS NOT NULL AND h.transfer  = 1) OR
(re.transfer IS NOT NULL AND re.transfer = 1) OR
(co.transfer IS NOT NULL AND co.transfer = 1)
),
 
t.additional_transfer_text, h.transfer_text, re.transfer_text, co.transfer_text,
t.people_cnt, t.children_cnt, t.room_category,
 
GROUP_CONCAT(DISTINCT d.date, '=', d.price ORDER BY d.date ASC),
 
GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
 
h.id, h.name, h.stars
 
FROM       a_tours                t
INNER JOIN a_tour_dates           d   ON d.tour_id   = t.tour_id AND d.date >= NOW()
INNER JOIN info_countries         co  ON co.id          = t.country_id
LEFT  JOIN info_regions           re  ON re.id          = t.region_id
INNER JOIN info_hotels            h   ON h.id           = t.hotel_id
INNER JOIN info_cities            ci  ON ci.id          = t.city_id
 
LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = t.tour_id
LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
 
WHERE t.is_active = 1
GROUP BY t.tour_id
ORDER BY MIN(d.price) LIMIT 9

Два джоина нас убили

Таблица тегов отеля (pivot_hotel_new_tour_type) присоединилась к запросу и просто все улетело в 5-6 секунд. Это наша проблема! *источник найден значит будет обезврежен

SELECT t.tour_id, t.title, ci.name, co.name, t.is_hot = 1,
t.sale_percent, t.price_currency, t.nights_cnt,
 
(t.additional_transfer = 1 OR
(h.transfer  IS NOT NULL AND h.transfer  = 1) OR
(re.transfer IS NOT NULL AND re.transfer = 1) OR
(co.transfer IS NOT NULL AND co.transfer = 1)
),
 
t.additional_transfer_text, h.transfer_text, re.transfer_text, co.transfer_text,
t.people_cnt, t.children_cnt, t.room_category,
 
GROUP_CONCAT(DISTINCT d.date, '=', d.price ORDER BY d.date ASC),
 
GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
 
GROUP_CONCAT(DISTINCT ht.type_id, '=', ht.title, '=', ht.slug),
 
h.id, h.name, h.stars
 
FROM       a_tours                t
INNER JOIN a_tour_dates           d   ON d.tour_id   = t.tour_id AND d.date >= NOW()
INNER JOIN info_countries         co  ON co.id          = t.country_id
LEFT  JOIN info_regions           re  ON re.id          = t.region_id
INNER JOIN info_hotels            h   ON h.id           = t.hotel_id
INNER JOIN info_cities            ci  ON ci.id          = t.city_id
 
LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = t.tour_id
LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
 
LEFT  JOIN pivot_hotel_new_tour_type p2  ON p2.hotel_id = t.hotel_id
LEFT  JOIN cfg_new_tour_types        ht  ON ht.type_id  = p2.new_tour_type_id
 
WHERE t.is_active = 1
GROUP BY t.tour_id
ORDER BY MIN(d.price) LIMIT 9

Убираем запрос тегов во внешний запрос

Суть в том что прежде чем выбрать 9 — наша мегатаблица строится со всеми тегами, со всеми всеми строками, потом группируется, и потом только… не надо так, а надо вынести все что можно наружу

SELECT bigt.*,
GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
GROUP_CONCAT(DISTINCT ht.type_id, '=', ht.title, '=', ht.slug)
FROM
  (
    SELECT t.tour_id, t.title, ci.name AS ci_name, co.name AS co_name, t.is_hot = 1,
    t.sale_percent, t.price_currency, t.nights_cnt,
 
    (t.additional_transfer = 1 OR
    (h.transfer  IS NOT NULL AND h.transfer  = 1) OR
    (re.transfer IS NOT NULL AND re.transfer = 1) OR
    (co.transfer IS NOT NULL AND co.transfer = 1)
    ),
 
    t.additional_transfer_text,
    h.transfer_text AS h_transfer_text, re.transfer_text AS re_transfer_text, co.transfer_text AS co_transfer_text,
    t.people_cnt, t.children_cnt, t.room_category,
 
    GROUP_CONCAT(DISTINCT d.date, '=', d.price ORDER BY d.date ASC),
 
    GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
 
    h.id, h.name AS hotel_name, h.stars
 
    FROM       a_tours                t
    INNER JOIN a_tour_dates           d   ON d.tour_id   = t.tour_id AND d.date >= NOW()
    INNER JOIN info_countries         co  ON co.id          = t.country_id
    LEFT  JOIN info_regions           re  ON re.id          = t.region_id
    INNER JOIN info_hotels            h   ON h.id           = t.hotel_id
    INNER JOIN info_cities            ci  ON ci.id          = t.city_id
 
    LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = t.tour_id
    LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
 
    WHERE t.is_active = 1
    GROUP BY t.tour_id
    ORDER BY MIN(d.price) LIMIT 9
  )
AS bigt
 
LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = bigt.tour_id
LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
 
LEFT  JOIN pivot_hotel_new_tour_type p2  ON p2.hotel_id = bigt.id
LEFT  JOIN cfg_new_tour_types        ht  ON ht.type_id  = p2.new_tour_type_id
GROUP BY bigt.tour_id

Добиваем общий вид

Добавляем условие если нужно конкретные теги — и запросик всего 0.02 уже)

SELECT bigt.*,
GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
GROUP_CONCAT(DISTINCT ht.type_id, '=', ht.title, '=', ht.slug)
FROM
  (
    SELECT t.tour_id, t.title, ci.name AS ci_name, co.name AS co_name, t.is_hot = 1,
    t.sale_percent, t.price_currency, t.nights_cnt,
 
    (t.additional_transfer = 1 OR
    (h.transfer  IS NOT NULL AND h.transfer  = 1) OR
    (re.transfer IS NOT NULL AND re.transfer = 1) OR
    (co.transfer IS NOT NULL AND co.transfer = 1)
    ),
 
    t.additional_transfer_text,
    h.transfer_text AS h_transfer_text, re.transfer_text AS re_transfer_text, co.transfer_text AS co_transfer_text,
    t.people_cnt, t.children_cnt, t.room_category,
 
    GROUP_CONCAT(DISTINCT d.date, '=', d.price ORDER BY d.date ASC),
 
    GROUP_CONCAT(DISTINCT tt.type_id, '=', tt.title, '=', tt.slug),
 
    h.id, h.name AS hotel_name, h.stars
 
    FROM       a_tours                t
    INNER JOIN a_tour_dates           d   ON d.tour_id   = t.tour_id AND d.date >= NOW()
    INNER JOIN info_countries         co  ON co.id          = t.country_id
    LEFT  JOIN info_regions           re  ON re.id          = t.region_id
    INNER JOIN info_hotels            h   ON h.id           = t.hotel_id
    INNER JOIN info_cities            ci  ON ci.id          = t.city_id
 
    LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = t.tour_id
    LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
 
    WHERE t.is_active = 1
        AND (
            t.hotel_id IN (SELECT hotel_id FROM pivot_hotel_new_tour_type WHERE new_tour_type_id=2)
            OR t.tour_id IN (SELECT tour_id FROM pivot_tour_new_tour_type WHERE new_tour_type_id=2)
        )
    GROUP BY t.tour_id
    ORDER BY MIN(d.price) LIMIT 9
  )
AS bigt
 
LEFT  JOIN pivot_tour_new_tour_type  p1  ON p1.tour_id  = bigt.tour_id
LEFT  JOIN cfg_new_tour_types        tt  ON tt.type_id  = p1.new_tour_type_id
 
LEFT  JOIN pivot_hotel_new_tour_type p2  ON p2.hotel_id = bigt.id
LEFT  JOIN cfg_new_tour_types        ht  ON ht.type_id  = p2.new_tour_type_id
GROUP BY bigt.tour_id

2 комментария “Mysql: наш запрос медленно работает — что делать?”

  1. boosters

    спасибо за инфу! подскажите пожалуйста, можно ли место INNER JOIN использовать group by?

    Ответить
    • полный запрос какой будет?
      если просто посчитать то вообще разный план запросов — т.е. group by вы по умноженному значению таблиц сделаете — и это всЕ)

      Ответить

Оставить комментарий

XHTML: Вы можете использовать такие теги: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">