Оптимизация SQL запроса (CPU 100%)

257

30 мая 2017, 03:11

Всем привет! Имею такой код:

        $key_cache = 'cache_banners_tpl_'. $idpl;
        $dateNow = date(MYSQL_DATE_FORMAT);
        $dateTimeNow = date("H:i:s");
        if(($banners = $this->app->cache5min->get($key_cache)) === NULL) {
          $find_items = array();
          $banners = $this->app->db->fetchAll("SELECT b.id,b.active,b.richmedia,b.rich_position,b.ip_limit,b.cookie_limit,b.cookie_interval,b.day_limit,b.limit_interval,b.frequency,
                                         b.close_btn, b.use_geo, bpl.s_x, bpl.s_y FROM bs_items_places AS bp
                                         LEFT JOIN bs_items AS b ON b.id = bp.item_id
                                         LEFT JOIN bs_places AS bpl ON bpl.id = bp.place_id
                                         WHERE b.active = 1 AND b.date_start < '".$dateNow."' AND b.date_stop > '".$dateNow."'
                                         AND bpl.active = 1 AND bp.place_id = {$idpl}
                                         AND (IF((time_from!='00:00:00' AND time_to!='00:00:00'),(time_from<='".$dateTimeNow."' AND time_to>='".$dateTimeNow."'),1))
                                         GROUP BY b.id
                                         ORDER BY b.frequency DESC, b.day_limit DESC
                                          ");
        foreach($banners AS $bnr) {
            $find_items[$bnr['id']] = $bnr;
        }
        $banners = $find_items;
        $this->app->cache5min->save($key_cache,$banners);
    }

EXPLAIN запроса:

explain SELECT `bs_items_ip`.`id`, `bs_items_ip`.`item_id`, `bs_items_ip`.`place_id`, `bs_items_ip`.`date_show`, `bs_items_ip`.`ip`, `bs_items_ip`.`shows` FROM `bs_items_ip` WHERE `bs_items_ip`.`item_id` = 146 AND `bs_items_ip`.`ip` = '94.100.184.101' AND `bs_items_ip`.`date_show` = '2017-05-29' LIMIT 1;
+----+-------------+-------------+------+---------------+---------+---------+-------+--------+-------------+
| id | select_type | table       | type | possible_keys | key     | key_len | ref   | rows   | Extra       |
+----+-------------+-------------+------+---------------+---------+---------+-------+--------+-------------+
|  1 | SIMPLE      | bs_items_ip | ref  | item_id       | item_id | 5       | const | 580178 | Using where |
+----+-------------+-------------+------+---------------+---------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

меня очень сильно он грузит процессор, можно ли его каким-то образом оптимизировать? Чтобы процессор не грузил, ибо из-за этого плохо работает сайт. Как только я убираю и комментирую этот код, нагрузка сразу спадает.

Answer 1

Как минимум, могу посоветовать по sql запросу:

Не используйте left join, когда накладываете на присоединяемую таблицу условия потом в where, таким образом у вас left join превращается в join. Пишите сразу обычный join, он отрабатывает быстрее.
Зачем используете group by без агрегирующих функций? Это вызывает дополнительную нагрузку.
Можете подумать над вашим IF в WHERE, возможно, это условие можно переписать гораздо проще.

В результате ваш запрос будет примерно таким:

SELECT b.id, b.active, b.richmedia, b.rich_position, 
    b.ip_limit, b.cookie_limit, b.cookie_interval,
    b.day_limit, b.limit_interval, b.frequency,
    b.close_btn, b.use_geo, bpl.s_x, bpl.s_y 
FROM bs_items_places AS bp
    JOIN bs_items AS b ON b.id = bp.item_id
    JOIN bs_places AS bpl ON bpl.id = bp.place_id
WHERE b.active = 1 AND b.date_start < '".$dateNow."' AND b.date_stop > '".$dateNow."'
    AND bpl.active = 1 AND bp.place_id = {$idpl}
    AND (IF((time_from!='00:00:00' AND time_to!='00:00:00'),
            (time_from<='".$dateTimeNow."' AND time_to>='".$dateTimeNow."'),1))
ORDER BY b.frequency DESC, b.day_limit DESC

Answer 2

Замените LEFT JOIN на INNER - всё равно связывания внутренние (секция WHERE такая);
Ещё лучше - перепишите на картезианку с отборами;
Добавьте алиасы таблиц КАЖДОМУ полю (а то неясно, из какой таблицы взяты поля time_from и time_to);

А потом смотрите EXPLAIN.

SELECT b.id, b.active, b.richmedia, b.rich_position, b.ip_limit
     , b.cookie_limit, b.cookie_interval, b.day_limit, b.limit_interval
     , b.frequency, b.close_btn, b.use_geo, bpl.s_x, bpl.s_y 
FROM bs_items_places AS bp,
     bs_items AS b,
     bs_places AS bpl 
WHERE b.active = 1 
  AND b.id = bp.item_id
  AND bpl.id = bp.place_id
  AND b.date_start < '".$dateNow."' 
  AND b.date_stop > '".$dateNow."'
  AND bpl.active = 1 AND bp.place_id = {$idpl}
  AND (IF((time_from != '00:00:00' AND time_to != '00:00:00'), (time_from <= '".$dateTimeNow."' AND time_to >= '".$dateTimeNow."'), 1))
GROUP BY b.id
ORDER BY b.frequency DESC, b.day_limit DESC

Ну и полностью согласен с Denis - есть смысл убрать группировку. А если нужна только одна запись для каждого b.id - то её оставить, а поля bpl.s_x, bpl.s_y обернуть любой групповой функцией (скажем, MIN).

А также, если PHP-сервер с MySQL-сервером в одном часовом поясе, то уберите подстановку текущих даты и времени из PHP и вставьте соответствующие MySQL-функции.