چگونه از حجیم شدن فایل داده جداول innodb جلوگیری کنیم؟

در مقایسه با سایر نرم افزارهای مدیریت پایگاه داده، MySQL دارای نقصی است که آن عدم آزادسازی فضای بدون استفاده فایل داده جداول InnoDB است. این مسئله به مرور زمان باعث حجیم شدن فایل داده می شود. در این مقاله به بررسی این مشکل و نیز روش های موجود برای حل آن می پردازیم.

MySQL به صورت پیش فرض تمام داده های جداول ¹InnoDB را در یک فایل داده ذخیره می کند، صرفنظر از این که چند پایگاه داده و چند جدول InnoDB تعریف شده باشد.

وقتی شما دستور حذف رکوردی از جدول را صادر می کنید رکورد به طور منطقی حذف می شود ولی فضای اشغال شده توسط آن آزاد نمی شود. و نیز هنگامی جدولی را با دستور DROP TABLE حذف می کنید، جدول به طور منطقی حذف می شود ولی فضای اشغال شده توسط آن آزاد نمی شود. این مسئله باعث حجیم شدن فایل داده می شود. متاسفانه در MySQL برخلاف MS-SQL ابزار یا دستوری برای ²Shrink کردن فایل داده وجود ندارد. در MySQL اگر نیاز دارید فایل داده InnoDB را Shrink کنید باید با ابزار mysqldump از کل پایگاه های داده Backup گرفته و مجددا Restore نمایید تا فایل داده دوباره ساخته شود و فضاهای خالی از بین برود. ولی در شرایط واقعی گاه حجم داده ها به چندین گیگابایت می رسد و استفاده از این روش بنظر ناممکن می رسد. در ادامه چند نکته جهت کنترل این مسئله اشاره می کنیم.

اولین راه کار ایجاد یک فایل داده به ازای هر جدول InnoDB است. این کار مشکل عدم آزادسازی فضای جداول پس از حذف آنها را حل می کند. برای این کار باید مقدار innodb-file-per-table=ON در فایل تنظیمات MySQL اضافه شود. با این کار وقتی شما یک جدول InnoDB می سازید یک فایل داده جدا برای آن ساخته می شود و وقتی آن را حذف می کنید فایل داده نیز حذف می شود.

اما مشکل دوم هنوز در جای خود باقی است. با حذف رکوردها فضاهای خالی داخل فایل داده پدید می آیند. در هنگام درج رکورد جدید موتور ذخیره سازی ابتدا سعی می کند تا رکورد جدید را در این فضاهای خالی جای دهد ولی بیشتر اوقات این فضاهای خالی قابل استفاده نیستند. در نهایت افزایش تعداد این فضاهای خالی غیرقابل استفاده، باعث حجیم شدن فایل داده می شود. برای کنترل این مشکل راه حل هایی وجود دارد:

• اگر در هر عملیات تعداد زیادی از رکوردها را حذف می کنید و تعداد خیلی کمی را باقی می گذارید، بهتر است با رکوردهایی که باید باقی بمانند یک جدول جدید بسازید و جدولی قبلی را حذف کنید. و یا می توانید بعد از حذف تعداد زیادی رکورد از دستور OPTIMIZE TABLE جهت حذف فضاهای خالی استفاده کنید. دقت داشته باشید که این دستور برای جداول با حجم بالا مدت زمان قابل توجهی طول می کشد.
• اگر حذف رکوردها براساس تاریخ باشد، و رکوردها دارای کلید اصلی³ متوالی نظیر Auto Increament Number باشند، حذف تعداد زیادی رکورد باعث به وجود آمدن یک فضای خالی بزرگ و پیوسته می شود که در عملیات درج رکورد مورد استفاده قرار می گیرد و این امر از افزایش حجم فایل داده جلوگیری می کند. پس در صورتی که جدولی قرار است با چنین شرایطی کار کند انتخاب کلید اصلی متوالی می تواند بسیار مفید باشد.

¹ InnoDB یکی از موتورهای ذخیره سازی در MySQL است.
² Shrink دستوری است که باعث حذف فضاهای خالی بین رکوردها در فایل داده می شود. انجام این دستور سبب می شود حجم فایل داده به کمترین حد ممکن خود برسد.
³ کلید اصلی(Primary Key) در جداول پایگاه داده جهت یکتاسازی رکوردها استفاده می شود.