انتقال اطلاعات از یک جدول به جدولی دیگر بصورت دوره‌ای

یکی از پروژه‌هایی که من روش کار می‌کنم دارای جدولی است که درحال حاضر بطور متوسط هر ثانیه یک سطر به اون اضافه میشه، این جدول علاوه بر چندین ستون، چند تا INDEX هم داره که این باعث میشه، هزینه INSERT یک سطر بالا باشه، برای همین من یک جدول میانی درست کردم که هیچ INDEXای روش نداره و هر ۱۰ دقیقه یکبار سطرهای این جدول میانی رو به جدول اصلی انتقال میدم، اینجوری سربار ساختن ایندکس‌ها موقع INSERT اولیه برداشته میشه. (البته در نظر داشته باشید چون دقیق بودن جدول اصلی در لحظه اهمیت بالایی نداره، ما میتونیم همچین کاری انجام بدیم)

برای اینکه اینکار (انتقال) بصورت دوره‌ای انجام بده، دو راه ساده وجود داره، اولی ایجاد یک cron job توی سیستم عامل و انتفال داده‌ها با زبان برنامه‌نویسی‌ای که دوست دارید. دومی هم استفاده از زمانبند خود MySQL.
من روش دوم رو انجام دادم و اینجا نحوه انجامش رو باهم میبینیم ولی روش اول هم به اندازه کافی ساده و بی دردسر هست.
زمانبند MySQL بصورت پیشفرض فعال نیست و برای استفاده از اون باید اول فعالش کنید.

1
SET GLOBAL event_scheduler = ON;

برای انتفال اطلاعات از جدول میانی به جدول اصلی یک procedure مینویسیم که هروقت خواستیم بتونیم براحتی تغییرش بدیم

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER $
CREATE PROCEDURE clean_temp ()
BEGIN
START TRANSACTION;
INSERT
INTO TableMain
SELECT *
FROM TableTemp;
DELETE
FROM TableTemp;
COMMIT;
END$
DELIMITER ;

حال میخوایم زمان‌بندی ایجاد کنیم که به MySQL بگیم میخوایم پروسیجر ما هر 10 دقیقه اجرا بشه.

1
2
3
4
CREATE EVENT mydatabase.myevent
ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO
call clean_temp();

کار تمومه :)
برای اینکه چک کنید که زمانبندی بدرستی ست شده، کوئری زیر را اجرا کنید و مشاهده خواهید کرد، سطری وجود دارد که User آن برابر event_scheduler و State آن Waiting for next activation است.

بروزرسانی:

  • تا اینجا همه چیز خوب و درست پیش‌رفته ولی اگه زمانی سرور دیتابیس شما ریستارت بشه، شما باید دوباره کوئری SET GLOBAL event_scheduler = ON; رو اجرا کنید. برای اینکه زمانبند بصورت خودکار اجرا بشه، توی فایل /etc/my.cnf یا /etc/my.cnf.d/server.cnf یا /etc/mysql/my.ini (بسته به سیستم عامل شما می‌تونه هرکدوم از مسیر‌های فوق باشه) زیر بخش [mysqld] عبارت زیر رو اضافه کنید.

    1
    event_scheduler=ON
  • همچنین اگر دوست داشته باشید می‌تونید با اجرای کوئری زیر از صحت وجود EVENTای که خودتون ایجاد کردید اطمینان حاصل کنید.

    1
    SHOW EVENTS;

References: