رفع مشکل طول کشیدن ورود اطلاعات پایگاه داده حجیم در InnoDB MySQL

در برخی موارد مشاهده می شود که برای ورود یک فایل dump از دیتابیسی حجیم ساعت ها زمان نیاز هست در حالی که با رعایت موارد زیر می توان این زمان را چند دقیقه کاهش داد.

نگام وارد کردن داده‌ها به InnoDB، مهم است که حالت autocommit در MySQL غیرفعال باشد. دلیل آن این است که در صورت فعال بودن autocommit، هر بار که یک رکورد درج می‌شود، نیاز به نوشتن لاگ بر روی دیسک دارد که این فرآیند را کند می‌کند. به همین دلیل، توصیه می‌شود که در طول عملیات وارد کردن داده‌ها، autocommit را غیرفعال کرده و از دستورات SET autocommit و COMMIT استفاده کنید تا این مشکل رفع شود. به شکل زیر می‌توانید autocommit را در زمان وارد کردن داده‌ها غیرفعال کنید:

SET autocommit=0;
... دستورات وارد کردن داده‌ها ...
COMMIT;

استفاده از دستور COMMIT پس از وارد کردن داده‌ها باعث می‌شود که تمام تغییرات به یکباره در دیتابیس اعمال شوند، که این کار باعث کاهش تعداد دفعات نوشتن بر روی دیسک می‌شود و کارایی را افزایش می‌دهد.

بهینه‌سازی وارد کردن داده‌ها با mysqldump

اگر از ابزار mysqldump و گزینه --opt استفاده کنید، فایل‌های خروجی ایجاد شده به گونه‌ای هستند که به سرعت می‌توانند در جداول InnoDB وارد شوند. حتی بدون نیاز به استفاده از دستورات SET autocommit و COMMIT، وارد کردن داده‌ها بهینه‌تر انجام می‌شود.

غیرفعال کردن بررسی‌های UNIQUE در کلیدهای ثانویه

در صورتی که جداول شما شامل کلیدهای UNIQUE در ایندکس‌های ثانویه باشند، می‌توانید با غیرفعال کردن موقت بررسی‌های یکتایی، سرعت وارد کردن داده‌ها را افزایش دهید. به این شکل:

SET unique_checks=0;
... دستورات وارد کردن داده‌ها ...
SET unique_checks=1;

این کار باعث می‌شود که InnoDB از بافر درج خود برای نوشتن رکوردهای ایندکس‌های ثانویه به صورت دسته‌ای استفاده کند، که منجر به کاهش I/O دیسک می‌شود. توجه داشته باشید که حتماً مطمئن شوید داده‌هایی که وارد می‌کنید شامل کلیدهای تکراری نباشند، چرا که در غیر این صورت خطاهای یکتایی رخ خواهد داد.

بهینه‌سازی وارد کردن داده‌ها با غیرفعال کردن کلیدهای خارجی

اگر جداول شما دارای محدودیت‌های کلید خارجی باشند، می‌توانید با غیرفعال کردن موقت بررسی کلیدهای خارجی در طول وارد کردن داده‌ها، سرعت عملیات را بهبود بخشید. به این صورت:

SET foreign_key_checks=0;
... دستورات وارد کردن داده‌ها ...
SET foreign_key_checks=1;

این روش نیز مشابه غیرفعال کردن بررسی یکتایی، باعث کاهش قابل توجه عملیات I/O بر روی دیسک می‌شود، خصوصاً وقتی با جداول بزرگی سروکار دارید. پس از وارد کردن داده‌ها، حتماً بررسی‌های کلید خارجی را مجدداً فعال کنید تا یکپارچگی داده‌ها تضمین شود.

نتیجه‌گیری

وارد کردن داده‌ها به InnoDB می‌تواند با استفاده از تکنیک‌های بهینه‌سازی مانند غیرفعال کردن autocommit، غیرفعال کردن موقت بررسی‌های یکتایی و کلیدهای خارجی به شدت سریع‌تر شود. این روش‌ها به شما کمک می‌کنند که از بار اضافی نوشتن بر روی دیسک جلوگیری کرده و عملکرد وارد کردن داده‌ها را بهبود ببخشید.