Выполнение запроса занимает более 40 секунд

Question

Выполнение запроса занимает более 40 секунд

0

Этот запрос занимает более 40 секунд для выполнения на таблице с 200 тыс. Строк

SELECT 
     my_robots.*, 
     (
        SELECT count(id) 
        FROM hpsi_trading 
        WHERE estado <= 1 and idRobot = my_robots.id
     ) as openorders,
     apikeys.apikey, 
     apikeys.apisecret 
FROM my_robots, apikeys 
WHERE estado <= 1 
     and idRobot = '2' 
     and ready = '1' 
     and apikeys.id = my_robots.idApiKey 
     and (my_robots.id LIKE '%0' 
          OR my_robots.id LIKE '%1' 
          OR my_robots.id LIKE '%2')

Я знаю, что это связано с подсчетом внутри запроса, но как я могу исправить это эффективно.

Изменение: Объяснить

Благодарю.

Donnie Darko 11 дек. 2017, в 10:57

Источник

Теги:

mysql

sql

2 ответа

Ещё вопросы

Radim Bača · Answer 1 · 2017-12-11T08-06-00.000Z

Вместо этого используйте GROUP BY

SELECT my_robots.*, 
       count(id) as openorders, 
       apikeys.apikey, 
       apikeys.apisecret 
FROM my_robots
JOIN apikeys ON apikeys.id = my_robots.idApiKey
LEFT JOIN hpsi_trading ON hpsi_trading.idRobot = my_robots.id and estado <= 1
WHERE estado <= 1 and 
      idRobot = '2' and 
      ready = '1' and 
      (
          my_robots.id LIKE '%0' OR 
          my_robots.id LIKE '%1' OR 
          my_robots.id LIKE '%2'
      ) 
GROUP BY my_robots.id, apikeys.apikey, apikeys.apisecret

Используйте явный синтаксис JOIN. Некоторые индексы понадобятся для его быстрого запуска, однако структура базы данных не ясно из вашего сообщения (и из вашего запроса).

небольшой комментарий - есть дополнительный оператор 'и' вокруг "ready = '1'". Пытался отредактировать ответ, но ТАК требуется изменить как минимум 6 символов ...

Tomer Shay · Answer 2 · 2017-12-12T14-03-00.000Z

План объяснения показывает, что наибольшая боль - это выбор данных из таблицы hpsi_trading.

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

Замена этого подзапроса на JOIN + GROUP BY потребует от MySQL соединения между всеми этими записями (раздувание) и только затем сбрасывать данные с помощью GROUP BY, что может занять некоторое время.

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

Это далеко не все. Но здесь мы видим, что извлечение подзапроса во временную таблицу может потребовать больших усилий со стороны разработки.

Пожалуйста, попробуйте эту версию и сообщите мне, помогли ли она (если нет, укажите свежий снимок экрана EXPLAIN):

Создание таблицы temp:

CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS 
      SELECT idRobot, COUNT(id) as openorders
      FROM   hpsi_trading
      WHERE  estado <= 1
      GROUP BY idRobot;

Измененный запрос:

SELECT 
    my_robots.*,
    temp1.openorders,
    apikeys.apikey,
    apikeys.apisecret
FROM
    my_robots,
    apikeys
    LEFT JOIN temp1 on temp1.idRobot = my_robots.id
WHERE
    estado <= 1 AND idRobot = '2'
        AND ready = '1'
        AND apikeys.id = my_robots.idApiKey
        AND (my_robots.id LIKE '%0'
        OR my_robots.id LIKE '%1'
        OR my_robots.id LIKE '%2')

Индексы, добавляемые для этого решения (я предположил по логике, что estado, idRobot и готовы из таблицы apikeys. Если это не так, дайте мне знать, и я настрою индексы):

ALTER TABLE 'temp1' ADD INDEX 'temp1_index_1' (idRobot);
ALTER TABLE 'hpsi_trading' ADD INDEX 'hpsi_trading_index_1' (idRobot, estado, id);
ALTER TABLE 'apikeys' ADD INDEX 'apikeys_index_1' ('idRobot', 'ready', 'id', 'estado');
ALTER TABLE 'my_robots' ADD INDEX 'my_robots_index_1' ('idApiKey');