чайников - mysql скачать



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

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

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

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

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

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

https://src-bin.com

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

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

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

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100

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

Я делал все это сразу, но это заставило все в приложении зависнуть в течение 30 секунд или около того, пока НАСТРОЙКИ складывались. Добавление LIMIT помогло смягчить это, но мне интересно, есть ли лучший способ сделать это.


Answer #1

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

Учитывая пример запроса выше

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 ;

Вы можете оставить LIMIT из него.

Скажем, вы хотите удалить данные, которые старше 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 минут в течение двух месяцев


Answer #2

Закончилось использование этого, чтобы оставить только 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;

Answer #3

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

Вот пример. Если вы хотите удалить записи из более чем 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/





mysql