Выполнение заданий по расписанию в MySQL

Если вы записываете в БД какую-либо статистическую информацию (логи посещений или каких-то действий), то растущую таблицу нужно время от времени чистить. Утратившие актуальность записи занимают место на диске и замедляют работу БД (если, скажем, записей уже миллионы). Есть и другие задачи, для которых требуется выполнение MySQL-запросов по расписанию. Например, какие-то «тяжёлые» запросы, как подбор релевантных постов по очень интеллектуальному алгоритму. Такие запросы лучше выполнять отдельно от генерации страниц сайта, а при генерации выдавать кэшированные результаты. Возникает идея написать для этих целей php-скрипт, который прописать в crontab. Но эту задачу можно решить и не отходя от MySQL-сервера. Сейчас вы убедитесь, что это просто.

Но понадобится войти в MySQL от имени root, иначе чудо, скорее всего, не произойдёт. Первым делом пишем команду:

SHOW PROCESSLIST

В процессах должен быть user event_scheduler

Если нет...

Активация планировщика событий MySQL

Сделать такой запрос:

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

Если пришлось включать event_scheduler, после перезагрузки сервера он, скорее всего, снова будет выключен. Ваши задания сохранятся, но не будут выполняться по расписанию. Тогда нужно войти в файловую систему сервера от имени root и отредактировать файл /etc/mysql/my.cnf. А именно, найти строчку [mysqld] и после неё добавить (или изменить) опцию event_scheduler = ON. Например, у вас должно получиться так:

[mysqld]
#
# * Basic Settings
#
event_scheduler = ON

Создание события

CREATE EVENT clearing_hourly
ON SCHEDULE EVERY 1 HOUR
DO DELETE
FROM `database`.`table`
WHERE `time` < (NOW() - INTERVAL 7 DAY)

Предполагается, что в БД database у вас таблица table с одним из полей time временного типа (например, timestamp). Описанное событие будет каждый час удалять из таблицы строки с меткой времени старше 7 дней.

Имена вымышлены, но пример с реального сайта. Так на сайте hope-recipes.ru реализована чистка популярных рецептов (которые выводятся на главной и в рубриках). При каждом посещении любого рецепта в базу заносятся строки с уникальным идентификатором IP + N рецепта и пометкой времени. Ежечасно event_scheduler удаляет старые записи (старше недели), в результате на сайте вы видите актуальное популярное содержимое.

И база не «раздувается» (за неделю собирается всего примерно 15 000 записей — точно не знает даже сам MySQL). Как видите, простая выборка из такой базы осуществляется за десятитысячные доли секунды.

Выполнение по расписанию группы запросов

Создать такое задание нисколько не сложнее.

ON SCHEDULE EVERY 1 HOUR DO
BEGIN
-- запросы, разделённые ";"
END

Теперь можете хоть программу писать.

Изменение события

Для изменения существующего события вместо CREATE используется инструкция ALTER. Например, включение или отключение (ENABLE | DISABLE) события с именем name:

ALTER EVENT name DISABLE

Смена периодичности:

ALTER EVENT name ON SCHEDULE EVERY 5 MINUTE

Теперь событие name будет выполняться каждые 5 минут.

И, конечно же, можем изменить само задание:

ALTER EVENT name DO ...

Просмотр событий

Для просмотра событий служит команда:

SHOW EVENTS

Или:

SELECT * FROM INFORMATION_SCHEMA.EVENTS

Вывод последней более информативный (там даже есть SQL-запрос на выполнение):

Только вывод запроса в phpmyadmin усекается для удобочитаемости таблицы. Чтобы увидеть запрос целиком, нажмите под таблицей ссылку «Экспорт» или «Версия для печати (полностью)».

P.S. Наконец нашёл время перенести эти команды MySQL-планирования из текстового документа в блог. Сам я неоднократно к ним обращался — ведь эта тема на «раз сделал и забыл», потому команды со временем в буквальном смысле забываются. Надеюсь, они будут полезны и вам. Сделайте закладку, чтобы не потерять. Приветствуется ваше мнение в комментариях и полезные дополнения. Интересует также, для каких целей вы планируете (или используете) выполнение MySQL-запросов по расписанию.