آپدیت میلیون‌ها رکورد در MySQL: آموزش به‌روزرسانی ۵ میلیون رکورد با Batch Update بدون Lock

هنگام آپدیت میلیون‌ها رکورد در MySQL، باید مراقب قفل کردن رکوردها باشید. اگر آن رکوردها قفل شوند، توسط سایر تراکنش‌ها در پایگاه داده، قابل دسترسی برای ویرایش (به‌روز‌رسانی یا حذف) نخواهند بود. یکی از روش ‌های رایج که برای به‌روز‌رسانی تعداد زیادی رکورد استفاده می‌شود، اجرای چندین به‌روز‌رسانی کوچک‌تر به صورت دسته‌ای است. یعنی برای به‌روز‌رسانی تعداد بالای رکورد، رکوردها را به دسته‌‌های کوچک‌تر تقسیم کرده و به‌روزرسانی روی دسته‌‌های کوچک انجام شود. به این ترتیب، تنها رکوردهایی که در هر نقطه در حال به‌روز‌رسانی هستند قفل می‌‌شوند.

آپدیت میلیون‌ها رکورد در MySQL اگر بدون برنامه‌ریزی انجام شود می‌تواند باعث قفل شدن رکوردها و ایجاد اختلال در عملکرد دیتابیس شود.

اگر سوالات زیر ذهن شما را به‌خود مشغول داشته است این مقاله از نیک آموز دقیقا مناسب شماست.

• چگونه میلیون‌ها رکورد را بدون تأثیر قابل توجهی بر تجربه کاربری به‌روز‌رسانی کنیم؟
• چطور دستور به‌روز‌رسانی باعث قفل شدن رکوردها می‌‌شود؟

آپدیت میلیون‌ها رکورد در MySQL معمولاً زمانی چالش‌برانگیز می‌شود که یک دستور UPDATE بزرگ تعداد زیادی رکورد را به صورت همزمان قفل کند.

نصب

ما از داکر برای اجرای کانتینر MySQL و کتابخانه فِیک پایتون برای تولید داده‌‌های فِیک استفاده می‌کنیم. ابتدا پوشه پروژه را ایجاد می‌کنیم.

mkdir lock_update && cd lock_update

سپس یک اسکریپت پایتون تولید کننده داده فیک با نام gen_fake.py اجرا می‌کنیم.

#!/usr/bin/env python3
import argparse
import random
from faker import Faker
def gen_user_data(file_name: str, num_records: int, seed: int = 1) -> None:
    fake = Faker("en_US")
    with open(file_name, "w") as file1:
        for i in range(1, num_records + 1):
            file1.write(
                f"{seed + i},{fake.name()},{random.randint(0,1)},{fake.state()},{fake.country()}n"
            )
if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Generate some fake data")
    parser.add_argument(
        "--file-name",
        type=str,
        default="fake_user.csv",
        help="file name to store fake data",
    )
    parser.add_argument(
        "--num-records", type=int, default=100, help="Num of records to generate"
    )
    parser.add_argument("--seed", type=int, default=0, help="seed")
    args = parser.parse_args()
    gen_user_data(
        file_name=args.file_name, num_records=args.num_records, seed=args.seed
    )

اسکریپت gen_fake.py داده‌های فیک را با فرمت (id, name, is_active flag, state, country)  در هر ردیف تولید می‌‌کند. به تولید کننده داده‌‌های فیک حق دسترسی بدهید و ۱۰ میلیون ردیف مطابق شکل زیر ایجاد کنید.

chmod u+rwx gen_fake.py
# run 5 parallel processes, each generating 2million records as shown below
./gen_fake.py --file-name user_data_1.csv --num-records 2000000 --seed 0 & ./gen_fake.py --file-name user_data_2.csv --num-records 2000000 --seed 2000000 & ./gen_fake.py --file-name user_data_3.csv --num-records 2000000 --seed 4000000 & ./gen_fake.py --file-name user_data_4.csv --num-records 2000000 --seed 6000000 & ./gen_fake.py --file-name user_data_5.csv --num-records 2000000 --seed 8000000
mkdir data
cat user_data_1.csv user_data_2.csv user_data_3.csv user_data_4.csv user_data_5.csv >> ./data/user_data_fin.csv # combine data
rm user_data_1* user_data_2* user_data_3* user_data_4* user_data_5*

یک کانتینر MySQL را با داده‌‌هایی که در بالا ایجاد کردیم راه اندازی کرده و وارد آن شوید.

docker run -d -p 3306:3306 --name mysql-updates -v "$(pwd)"/data:/var/lib/data -e MYSQL_ROOT_PASSWORD=Password1234 mysql:8.0
docker exec -it mysql-updates bash # open docker shell
mysql -h 127.0.0.1 -P 3306 -u root --local-infile=1 -p # password is Password1234

یک پایگاه داده به نام updates و یک جدول user  ایجاد کنید. داده‌‌های تولید شده را مطابق شکل زیر در جدول وارد کنید.

create database updates;
use updates;
drop table if exists user;
CREATE TABLE user (
    user_id int,
    name varchar(100),
    is_active boolean,
    st varchar(100),
    country varchar(100),
    PRIMARY KEY (user_id)
);
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE '/var/lib/data/user_data_fin.csv' INTO TABLE user FIELDS TERMINATED BY ',';
-- time taken to load 1 min 22.36 sec
select count(*) from user;
-- should be 10 m

مشکلات آپدیت میلیون‌ها رکورد در MySQL

آپدیت میلیون‌ها رکورد در MySQL در سیستم‌های واقعی مثل فروشگاه‌های آنلاین می‌تواند تأثیر مستقیم روی تجربه کاربری داشته باشد. فرض کنید برای یک وب‌سایت تجارت الکترونیک کار می‌کنیم. یک باگ باعث شده فیلد st(state) را به اشتباه برای کاربرانی با id‌های بین ۳ میلیون (۳۰۰۰۰۰۰) تا ۸ میلیون (۸۰۰۰۰۰۰) تنظیم گردد. ما باید ۵ میلیون رکورد از مجموع ۱۰ میلیون رکورد را به‌روز کنیم تا مقدار st با مقدار NY تنظیم شود. اجازه دهید ببینیم که چگونه تجربه کاربری ممکن است تحت تأثیر یک به‌روز‌رسانی بزرگ که روی رکورد آن‌ها قفل نگه‌داشته‌شده، تأثیر گذار باشد.
در ترمینال sql خود این دستور به‌روز‌رسانی را اجرا کنید.

update user set st = 'NY' where user_id between 3000000 and 8000000;
-- ۲ min 13.46 sec

 هم زمان در یک ترمینال SQL دیگر سعی کنید یک رکورد کاربری که توسط به‌روز‌رسانی فوق قفل شده است را به‌روز‌رسانی کنید. ما از Where user_id = 3300000 برای انتخاب ردیفی استفاده می‌‌کنیم که توسط به‌روز‌رسانی بالا قفل شده است.

docker exec -it mysql-updates bash
mysql -h 127.0.0.1 -P 3306 -u root -p # password is Password1234

update updates.user set name = 'Amos Burton' where user_id = 3300000;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

این خطا به این دلیل رخ می‌دهد که به‌روز‌رسانی بزرگ ما رکورد را با user_id = 3300000 قفل کرده است و این مانع از تغییر داده‌های قفل شده توسط تراکنش‌های دیگر می‌شود.
به‌روز‌رسانی دوم به طور پیش‌ فرض ۵۰ ثانیه صبر می‌‌کند. با استفاده از عبارت زیر می‌توانید این تنظیم را بررسی کنید.

show variables like 'innodb_lock_wait_timeout';

 چه اتفاقی خواهد افتاد در صورتی که به جای به‌ روز رسانی ۵ میلیون رکورد در یک دستور به روز رسانی، فقط تعداد کمی از رکوردهای کاربران را در یک زمان به ‌روز رسانی کنیم؟ به این ترتیب، می‌توانیم تعداد رکوردهای قفل‌شده در هر زمان معین را کم نگه داریم و زمان انتظار برای سایر تراکنش‌های به روز رسانی را کاهش دهیم.

نگاهی به ابزارهای مختلف برای کار با دیتابیس در دات‌نت

در کنار یادگیری روش‌های آپدیت میلیون‌ها رکورد در MySQL، آشنایی با ابزارهای مختلف تعامل با دیتابیس نیز اهمیت دارد. در این ویدیو، دو تکنولوژی پرکاربرد در دنیای دات‌نت یعنی Entity Framework و Dapper با هم مقایسه می‌شوند تا دید بهتری نسبت به انتخاب ابزار مناسب در پروژه‌های مختلف پیدا کنید.

آپدیت میلیون‌ها رکورد در MySQL به صورت دسته‌ای

آپدیت میلیون‌ها رکورد در MySQL را می‌توان با استفاده از روش به‌روزرسانی دسته‌ای (Batch Update) به شکل بسیار بهینه‌تری انجام داد. برای آپدیت میلیون‌ها رکورد در MySQL به صورت دسته‌ای ابتدا رکوردهایی که باید به‌روز‌رسانی شوند را به دسته‌‌های کوچک‌تر تقسیم می‌کنیم. در مثال فوق ۵۰۰۰۰۰ رکورد باید به‌روز‌رسانی می‌شد. قصد داریم این تعداد را به دسته‌ های ۵۰۰۰۰ رکوردی تقسیم کنیم و سپس به‌روز‌رسانی را انجام دهیم.
برای تقسیم رکوردها در جدول user، از شناسه id که به‌صورت خودکار افزایش می‌یابد استفاده می‌کنیم.

فرض کنید می‌خواهیم این بار رکورد st را به NJ به‌روز‌رسانی کنیم. برای اجرای به‌روز‌رسانی‌ها از یک حلقه while استفاده می‌کنیم. هر دسته یک شناسه شروع به نام batch_start_id و یک شناسه پایان به نام batch_ end _id خواهد داشت. در میان این دو شناسه به تعداد batch_size رکورد خواهیم داشت کد زیر مراحل توضیح داده شده بالا را به این ترتیب پیاده‌سازی می‌‌کند.

USE updates;
DROP PROCEDURE IF EXISTS BatchUpdate;
DELIMITER $$
CREATE PROCEDURE BatchUpdate(
    start_id INT,
    end_id INT,
    batch_size INT)
BEGIN
    DECLARE batch_start_id INT DEFAULT start_id;
    DECLARE batch_end_id INT DEFAULT start_id + batch_size;
    DECLARE loop_counter INT DEFAULT 0;
    WHILE batch_end_id <= end_id DO
        SELECT CONCAT('UPDATING FROM ', batch_start_id, ' TO: ', batch_end_id) as log;
        UPDATE user SET st = 'NJ' WHERE user_id BETWEEN batch_start_id and batch_end_id;
        SET batch_start_id = batch_start_id + batch_size;
        SET batch_end_id = batch_end_id + batch_size;
        SET loop_counter = loop_counter + 1;
    END WHILE;
    SELECT CONCAT('UPDATING FROM ', batch_end_id - batch_size, ' TO: ', end_id) as log, loop_counter;
    UPDATE user SET is_active = true WHERE user_id BETWEEN (batch_end_id - batch_size) and end_id;
END$$
DELIMITER ;
call BatchUpdate(3000000, 8000000, 50000);
-- takes a total of 4 min 43.49 sec

در حالی که BatchUpdate بالا در حال اجرا است، یک به‌روز‌رسانی را در یک ترمینال SQL دیگری اجرا کنید.

docker exec -it mysql-updates bash
mysql -h 127.0.0.1 -P 3306 -u root -p # password is Password1234
 

update updates.user set name = 'James Holden' where user_id = 3300000;
exit

با استفاده از دستورات زیر می‌توانید کانتینرهای docker خود را از بین ببرید.

docker stop mysql-updates
docker rm -f mysql-updates

سخن پایانی آپدیت میلیون‌ها رکورد در MySQL

در این مقاله دیدیم که  آپدیت میلیون‌ها رکورد در MySQL اگر با یک کوئری بزرگ انجام شود می‌تواند باعث قفل شدن رکوردها و ایجاد خطای Lock wait timeout شود. استفاده از روش Batch Update کمک می‌کند عملیات به‌روزرسانی در بخش‌های کوچک انجام شود و سایر تراکنش‌ها کمتر تحت تأثیر قرار بگیرند.

نکته مهمی که در اینجا باید به آن توجه کرد ترتیب آپدیت میلیون‌ها رکورد در MySQL است. هنگام اجرای به‌روز‌رسانی‌های دسته‌ای و به‌روز‌رسانی‌های تکی در همان ستون رکوردی که در حال ویرایش دسته است، آخرین به‌روز‌رسانی اعمال می‌‌شود. همچنین می‌توان این به‌روز‌رسانی‌‌های دسته‌‌ای را به صورت موازی نیز انجام داد، فقط باید مراقب باشید که به بن ‌بست نخورید. اگر منطق به‌روز‌رسانی پیچیده‌تر از مثال این مقاله بود و جدول مورد نظر دارای index بود، می‌توانید از دستور SELECT … FOR UPDATE به همراه SKIP LOCKED یا NOWAIT استفاده کنید.
دفعه بعد که یک به‌روز‌رسانی بزرگ را روی یک جدول قصد دارید انجام دهید، به‌روز‌رسانی‌ها را به‌صورت تکه‌ای اجرا کنید تا دیگر تراکنش‌ها تحت تأثیر خطاهای Lock wait timeout قرار نگیرند.

سوالات متداول آپدیت میلیون‌ها رکورد در MySQL

۱. چرا آپدیت میلیون‌ها رکورد در MySQL می‌تواند مشکل‌ساز شود؟

زیرا اجرای یک دستور UPDATE بزرگ می‌تواند تعداد زیادی رکورد را قفل کند. در این حالت سایر تراکنش‌ها قادر به ویرایش یا حذف آن رکوردها نخواهند بود و این موضوع ممکن است عملکرد سیستم و تجربه کاربری را تحت تأثیر قرار دهد.

۲. قفل شدن رکوردها در زمان به‌روزرسانی چه تأثیری روی سیستم دارد؟

وقتی رکوردها قفل می‌شوند، سایر عملیات‌های پایگاه داده که به همان رکوردها نیاز دارند باید منتظر بمانند. این انتظار می‌تواند باعث کندی سیستم یا حتی بروز خطا در تراکنش‌های دیگر شود.

۳. روش Batch Update چیست؟

Batch Update روشی است که در آن عملیات به‌روزرسانی بزرگ به چندین عملیات کوچک‌تر تقسیم می‌شود و هر بخش به‌صورت جداگانه اجرا می‌گردد تا از قفل شدن طولانی‌مدت تعداد زیادی رکورد جلوگیری شود.

۴. چرا تقسیم رکوردها به دسته‌های کوچک‌تر در آپدیت میلیون‌ها رکورد در MySQL مفید است؟

زیرا در هر مرحله فقط تعداد محدودی از رکوردها قفل می‌شوند و مدت زمان قفل بودن آن‌ها کوتاه‌تر است. این کار باعث می‌شود سایر تراکنش‌ها راحت‌تر به داده‌ها دسترسی داشته باشند.

۵. در مثال مطرح‌شده چند رکورد باید به‌روزرسانی شوند؟

در سناریوی مطرح‌شده، باید حدود ۵ میلیون رکورد از بین ۱۰ میلیون رکورد موجود در جدول به‌روزرسانی شوند.

۶. چرا قفل شدن طولانی‌مدت جدول کاربران می‌تواند مشکل ایجاد کند؟

زیرا جدول کاربران معمولاً در بسیاری از عملیات‌های سیستم استفاده می‌شود و قفل شدن آن حتی برای چند ثانیه می‌تواند روی عملکرد سایت و تجربه کاربران تأثیر منفی بگذارد.

۷. چگونه می‌توان عملیات به‌روزرسانی ۵ میلیون رکورد را مدیریت کرد؟

می‌توان رکوردها را به دسته‌های کوچک‌تر تقسیم کرد؛ برای مثال هر بار ۵۰ هزار رکورد را به‌روزرسانی کرد تا فرآیند به‌صورت مرحله‌ای انجام شود و فشار کمتری به پایگاه داده وارد شود.

 💡 برای آشنایی بیشتر با مفاهیم مرتبط، مطالعه مقالات زیر می‌تواند برای شما مفید باشد:

• راه اندازی یک پروژه مهندسی داده برای تازه کاران
• چطور تسک‌ های مهندسی داده خود را درک کنیم؟ ۵ راهکار عملی برای مهندسان داده تازه‌کار
• مسیر راه یادگیری مهندسی داده
• آشنایی با انواع کاربرد لینوکس برای مهندسی داده

منابع

https://www.startdataengineering.com/post/update-mysql-in-batch/