Каков наилучший способ удаления старых строк из MySQL на скользящей основе?

Я обнаружил, что хочу удалить строки старше (x) - дней на скользящей основе во многих приложениях. Как лучше всего сделать это наиболее эффективно на столе с высоким трафиком?



Например, если у меня есть таблица, в которой хранятся уведомления, и я хочу хранить их только в течение 7 дней. Или высокие баллы, которые я хочу сохранить только в течение 31 дня.

Прямо сейчас я храню строку, в которой хранится время эпохи, и запускаю задание cron, которое выполняется один раз в час и удаляет их с шагом, как это: 



DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100


Я делаю это до тех пор, пока mysql_affected_rows не вернет 0.



Раньше я делал это все сразу, но это приводило к тому, что все в приложении зависало на 30 секунд или около того, пока вставки накапливались. Добавление предела работало, чтобы облегчить это, но мне интересно, есть ли лучший способ сделать это.

620   6  

6 ответов:

Проверьте секционирование MySQL:

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

Смотрите, например, этот пост, чтобы получить некоторые идеи о том, как его применить:

Использование секционирования и Планировщик событий для обрезки архивных таблиц

И вот это:

Разбиение по датам: краткое руководство

25  
2012-02-27 23:23:45

Попробуйте создать событие, которое будет запускаться в базе данных автоматически через требуемый интервал времени.

Вот пример:: Если вы хотите удалить записи, возраст которых превышает 30 дней, из некоторой таблицы "tableName", имеющей запись столбца "datetime". Затем каждый день выполняется следующий запрос, который будет выполнять необходимые действия по очистке.

CREATE EVENT AutoDeleteOldNotifications
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY 
ON COMPLETION PRESERVE
DO 
DELETE LOW_PRIORITY FROM databaseName.tableName WHERE datetime < DATE_SUB(NOW(), INTERVAL 30 DAY)

Нам нужно добавить ON COMPLETION PRESERVE, чтобы сохранить событие после каждого запуска. Вы можете найти дополнительную информацию здесь: http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

54  
2015-05-18 15:42:42

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

Приведенный выше пример запроса 

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 ;

Вы можете оставить предел вне его.

Допустим, вы хотите удалить данные, которые старше 31 дня.

Давайте вычислим 31 день в секундах (86400 X 31 = 2678400)
  • начните с сбора ключей
  • Далее проиндексируйте ключи
  • затем выполните команду DELETE JOIN
  • наконец, отбросьте собранные ключи

Вот алгоритм 

CREATE TABLE delete_keys SELECT id FROM my_table WHERE 1=2;
INSERT INTO delete_keys
SELECT id FROM
(
    SELECT id FROM my_table
    WHERE time_stored < (UNIX_TIMESTAMP() - 2678400)
    ORDER BY time_stored
) A LIMIT 100;
ALTER TABLE delete_keys ADD PRIMARY KEY (id);
DELETE B.* FROM delete_keys
INNER JOIN my_table B USING (id);
DROP TABLE delete_keys;

Если сбор ключей занимает менее 5 минут, то запускайте этот запрос каждые 5 минут.

Попробуйте !!!

ОБНОВЛЕНИЕ 2012-02-27 16: 55 EDT

Вот то, что должно ускорить сбор ключей немного больше. Добавить следующий индекс: 

ALTER TABLE my_table ADD INDEX time_stored_id_ndx (time_stored,id);

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

ОБНОВЛЕНИЕ 2012-02-27 16: 59 EDT

Так как вы должны удалять часто, вы можете попробовать это каждые два месяца 

OPTIMIZE TABLE my_table;

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

2  
2012-02-27 23:55:01

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

DELETE FROM t1 WHERE expiration < UNIXTIME(NOW());

Это происходило раз в час, но у нас были проблемы, похожие на то, что вы испытываете. Мы увеличили его до одного раза в минуту. Затем 6 раз в минуту. Установите cron с помощью скрипта bash, который в основном выполняет запрос, затем спит в течение нескольких секунд и повторяется до тех пор, пока не закончится минута.

Повышенная частота значительно уменьшилось количество строк, которые мы удаляли. Что облегчило спор. Это маршрут, по которому я бы пошел.

Однако, если вы обнаружите, что у вас все еще слишком много строк для удаления, используйте ограничение и сделайте спящий режим между ними. Например, если вам нужно удалить 50 тысяч строк, сделайте фрагмент размером 10 тысяч с интервалом между ними в 2 секунды. Это позволит предотвратить накопление запросов и позволит серверу выполнять некоторые обычные операции между этими массовыми удалениями.

1  
2012-02-27 23:14:13

Вы можете рассмотреть вопрос о введении в ваш проект решенияmaster/slave (replication) . Если вы переместите весь трафик чтения на ведомое устройство, вы откроете ведущее устройство для обработки "на лету" CRUD-действий, которые затем реплицируются на ведомое устройство (ваш сервер чтения).

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

1  
2012-02-27 23:31:43

В конечном итоге, используя это, чтобы оставить только 100 последних строк на месте, так что значительное отставание при выполнении часто (каждую минуту)

delete a from tbl a left join (
    select ID
    from tbl
    order by id desc limit 100
) b on a.ID = b.ID
where b.ID is null;
0  
2018-06-20 21:40:57

Comments

    Ничего не найдено.
Click here to cancel reply.