Блог

У меня есть база данных MySQL, которая получает много данных из источника раз в неделю в определенный день недели в заданное время (около 1,2 миллиона строк) и сохраняет их в, назовем это, «живой» таблице.

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

Кто-нибудь может предложить эффективный способ сделать это. Я действительно пытаюсь избежать — insert into archive_table select * from live — . Я хотел бы иметь возможность запускать этот архиватор с использованием PHP, поэтому я не могу использовать Maatkit. Есть предложения?

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

Хитрый способ:

Не копируйте записи заново. Это занимает слишком много времени.

Вместо этого просто переименуйте текущую таблицу и создайте заново:

 RENAME TABLE live_table TO archive_table;
CREATE TABLE live_table (...);
  

Это должно быть довольно быстро и безболезненно.

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

Если вы хотите сохранить данные только за несколько периодов, вы могли бы выполнить переименование несколько раз, аналогично ротации журнала. Затем вы могли бы определить представление, которое объединяет архивные таблицы в одну большую гудящую таблицу.

ПРАВКА 2: Если вы хотите поддерживать автоматическое увеличение, вы можете попытаться:

 RENAME TABLE live TO archive1; 
CREATE TABLE live (...); 
ALTER TABLE LIVE AUTO_INCREMENT = (SELECT MAX(id) FROM archive1);
  

но, к сожалению, это не сработает. Однако, если вы управляете процессом с помощью PHP, это довольно легко обойти.

Напишите скрипт для запуска в качестве задания cron для:

1. Выгружайте архивные данные из «живой» таблицы (вероятно, это более эффективно с использованием mysqldump из сценария оболочки)
2. Усекайте текущую таблицу
3. Измените инструкции INSERT в файле дампа так, чтобы имя таблицы ссылалось на архивную таблицу, а не на текущую таблицу
4. Добавляйте архивные данные в архивную таблицу (опять же, можно просто импортировать из файла дампа с помощью сценария оболочки, например, mysql dbname < dumpfile.sql)

Это будет зависеть от того, что вы будете делать с данными после их архивирования, но рассматривали ли вы возможность использования репликации MySQL?

Вы могли бы настроить другой сервер в качестве подчиненного устройства репликации, и как только все данные будут реплицированы, выполните удаление или усечение с SET BIN-LOG 0 перед ним, чтобы избежать репликации этого оператора.