Блог

РЕДАКТИРОВАТЬ Эти отдельные запросы являются частью более масштабной процедуры, которая используется для создания отчета в Excel, который может автоматически обновляться другими пользователями.

Я конвертирую запросы с SQL Server в MySQL. Приведенный ниже запрос содержит около 1,6 миллиона записей.

   CREATE TEMPORARY TABLE table_1
    AS
       (SELECT...
          FROM accounts a -- also a temporary table
              JOIN tdate d ON a.date1 <= d.date2
          WHERE PERIOD_DIFF(DATE_FORMAT(d.date2, '%Y%m'), DATE_FORMAT(a.date1, '%Y%m')) >= 0
         );
  

Затем он должен быть обновлен сам по себе и присоединен к самому себе. Из-за ограничений MySQL мне нужно дублировать таблицу два раза. UPDATE Именно здесь запрос начинает занимать много времени.

 
    /* Create duplicate temp tables */
    CREATE TEMPORARY TABLE table_2
    AS
       (SELECT * FROM table_1);

    CREATE TEMPORARY TABLE table_3
    AS
       (SELECT * FROM table_2);

    /* UPDATE table_1 */
        UPDATE table_1 a
        JOIN
           (SELECT ...
                   SUM(ind_costs) as tcosts
            FROM table_2 b
                JOIN table_3 s ON b.id = s.id
            WHERE b.dperiod >= s.dperiod
            GROUP BY b.id, 
                     b.dperiod) z
        ON a.id = z.id
                 AND a.dperiod = z.dperiod
    SET a.tcosts = z.tcosts;
  

Запрос отлично выполняется на SQL Server, я полагаю, потому что мне не нужно создавать 3 копии временной таблицы с 1,6 миллионами строк для UPDATE и JOIN самой себя. Этот запрос выполняется чрезвычайно медленно в MySQL. Есть предложения о том, как я мог бы оптимизировать это для более эффективной работы? Я работаю на сервере MySQL 5.7.30

РЕДАКТИРОВАТЬ Наконец, есть еще один запрос, который следует за UPDATE , который сужает область SELECT , JOIN содержит другую таблицу и фильтрует с помощью WHERE . Это используется в отчете, который экспортируется в Excel и может быть обновлен другими пользователями.

     DROP TEMPORARY TABLE IF EXISTS table_2;
    DROP TEMPORARY TABLE IF EXISTS table_3;

-- The final query results from the procedure
    SELECT  ...
    FROM table_1 a
        JOIN stats s ON a.state = s.state 
            AND a.column1 = s.column1
            AND a.column2 = s.column2
    WHERE a.dperiod = DATE_FORMAT(NOW(),'%Y%m')
        AND a.costs < s.avg_costs
    ORDER BY period DESC;
  

Выполнение запроса на SQL Server занимает несколько секунд, но я позволил ему выполняться более часа в MySQL, и он все еще не был завершен. Я ищу любые способы, которые потенциально могли бы ускорить весь процесс. Я понимаю, что это немного расплывчато. Заранее спасибо за ваши предложения.

РЕДАКТИРОВАНИЕ РЕШЕНИЯ Я опубликовал решение, которое ускорило выполнение запроса с более чем часа (я никогда не даю ему завершиться) до 4 минут. Хотя это и не идеально, это намного лучше, чем то, с чем я работал. Я был бы открыт для других предложений, если у людей есть улучшения, однако я выбрал то, что работало для меня на данный момент.

Зачем вам вообще нужна временная таблица? Просто запустите один запрос. Я думаю, вы хотите:

 select t.*,
       sum(ind_costs) over (partition by id order by dperiod) as running_ind_costs
from table_1 t1;
  

Вы могли бы включить это в исходное определение таблицы.

Хотя это и не идеально, то, что я сделал до сих пор, сократило время выполнения запроса с более чем часа (никогда не позволяя ему выполняться полностью) примерно до 4 минут при использовании INDEX в указанном мной id столбце и в моей period колонке. Мой id столбец был уникальным для отдельных пользователей, а период представлял собой дату в '%Y%m' формате.

Я использовал этот метод, потому что это были обычные столбцы в предложениях JOIN , WHERE и GROUP BY . Я наткнулся на это, либо на составной, либо на покрывающий индекс, метод, считывающий ТАК и это.

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

РЕДАКТИРОВАТЬ После тестирования мне нужно было только создать индекс для table_1 и для table_2 . Похоже, что индекс был скопирован из table_2 в table_3 .

 CREATE TEMPORARY TABLE table_1
    AS
       (SELECT...
          FROM accounts a -- also a temporary table
              JOIN tdate d ON a.date1 <= d.date2
          WHERE PERIOD_DIFF(DATE_FORMAT(d.date2, '%Y%m'), DATE_FORMAT(a.date1, '%Y%m')) >= 0
         );

/*
#############################################
added index here and for the first temp table
#############################################
*/
ALTER TABLE table_1
   ADD INDEX (id, reporting_period);
  

  /* Create duplicate temp tables */
    CREATE TEMPORARY TABLE table_2
    AS
       (SELECT * FROM table_1);

-- additional index
ALTER TABLE table_2
   ADD INDEX (placedetail_id, reporting_period);

    CREATE TEMPORARY TABLE table_3
    AS
       (SELECT * FROM table_2);

    /* UPDATE table_1 */
        UPDATE table_1 a
        JOIN
           (SELECT ...
                   SUM(ind_costs) as tcosts
            FROM table_2 b
                JOIN table_3 s ON b.id = s.id
            WHERE b.dperiod >= s.dperiod
            GROUP BY b.id, 
                     b.dperiod) z
        ON a.id = z.id
                 AND a.dperiod = z.dperiod
    SET a.tcosts = z.tcosts;

    DROP TEMPORARY TABLE IF EXISTS table_2;
    DROP TEMPORARY TABLE IF EXISTS table_3;
  

 -- The final query results from the procedure
    SELECT  ...
    FROM table_1 a
        JOIN stats s ON a.state = s.state 
            AND a.column1 = s.column1
            AND a.column2 = s.column2
    WHERE a.dperiod = DATE_FORMAT(NOW(),'%Y%m')
        AND a.costs < s.avg_costs
    ORDER BY period DESC;