Как я могу переместить строки из одной таблицы в другую, где они не существуют в третьей?

У меня есть три таблицы:

grade         (grade_id,       grade_value,       grade_date)     ~100M rows
grade_archive (grade_id,       grade_value,       grade_date)         0 rows
peer_review   (grade_id, peer_review_value, peer_review_date)      ~10M rows

Я хочу переместить все строки из таблицы grade в grade_archive которые старше месяца и которых нет в таблице peer_review,

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

Ожидаемые строки таблицы должны выглядеть примерно так:

grade          ~10M rows
grade_archive  ~90M rows
peer_review    ~10M rows

Я полагаю, это что-то близкое к:

INSERT
    LOW_PRIORITY
    INTO grade_archive
        (grade_id,grade_value,grade_date)
    SELECT
        grade_id,grade_value,grade_date
    FROM
        grade
    WHERE
            grade_date < DATE_ADD(NOW(), INTERVAL -1 MONTH)
        AND grade_id NOT IN
            (
                SELECT grade_id FROM peer_review
            );

А потом убирать grade таблицу, удалив все строки в таблице архива:

DELETE LOW_PRIORITY FROM grade WHERE grade_id IN (SELECT grade_id FROM grade_archive);

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

Источник

2 ответа

Решение

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

/* Set time for calculation basis */
SET@calc_time = NOW();
/* Create empty copy of grade table */
CREATE TABLE grade_temp LIKE grade;
/* Add rows you want to save from grade into temp table */
INSERT INTO grade_temp
SELECT
    g.grade_id AS grade_id,
    g.grade_value AS grade_value,
    g.grade_date AS grade_date
FROM grade AS g
LEFT JOIN peer_review AS pr
  ON g.grade_id = pr.grade_id
WHERE
/*
To keep the record it must either have an entry in peer review
or it is less than a month old
*/
    pr.grade_id IS NOT NULL
    OR g.grade_date >= DATE_SUB(@calc_time, INTERVAL 1 MONTH);
/*
Switch new temp table for active table.
This happens really fast (it is just file name switching on the system).
*/
RENAME TABLE grade TO grade_old, grade_temp TO grade;
/*
You are now taking new records into new version of grade table
and free to do your much slower operations against the grade_old table
*/
/* Delete more recent rows */
DELETE FROM grade_old
WHERE grade_date >= DATE_SUB(@calc_time, INTERVAL 1 MONTH);
/* Delete rows that exist in peer review */
DELETE FROM grade old
WHERE grade_id IN (
    SELECT grade_id
    FROM peer_review
    WHERE grade_date < DATE_SUB(@calc_time, INTERVAL 1 MONTH)
);
/*
As an alternate to the above action, you could also try deleting across join as shown below. Which is faster will likely depend upon number of records that are returned from that subquery shown above. You can try both out and see what works best
*/
DELETE go FROM grade_old AS go
INNER JOIN peer_review AS pr
  ON go.grade_id = pr.grade_id
WHERE pr.grade_date < DATE_SUB(@calc_time, INTERVAL 1 MONTH);
/* Add all rows from grade_old to grade_archive */
INSERT INTO grade_archive
SELECT
    grade_id,
    grade_value,
    grade_date
FROM grade_old;
/* Drop date_old table */
DROP TABLE date_old;

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

Я скажу, однако, что ваша схема базы данных, кажется, может быть оптимизирована для такого рода операций. Например, у вас может быть флажок рецензирования в вашей таблице оценок, который вы могли бы использовать для более быстрой фильтрации, а не для фильтрации по объединению. Я на самом деле подвергаю сомнению необходимость этой таблицы рецензирования в целом, если она не имеет отношения много к одному с таблицей оценок (что, по-видимому, не указано в вашем вопросе). Если в классе grade_id есть только одна запись о рецензировании, я бы подумал, что эти столбцы следует просто нормализовать в таблице оценок. Это значительно упростит этот процесс обслуживания.

Источник

Поскольку NOT IN ( SELECT ... ) очень медленно, используйте LEFT JOIN .. IS NULL чтобы получить тот же эффект:

SELECT  g.grade_id, g.grade_value, g.grade_date
    FROM  grade AS g
    LEFT JOIN  peer_review AS p USING(grade_id)
    WHERE  g.grade_date < DATE_ADD(NOW(), INTERVAL -1 MONTH)
      AND  gi.grade_id IS NULL ;

Не требуется явная таблица tmp.

Источник
Другие вопросы по тегам