کدام موتور ذخیره سازی در mysql ؟

سلام دوباره به همه 🙂

بعد چند وقت دوباره اومدم 🙂

خب موتورهای ذخیره سازی یه مسئله ای هستش که خیلی وقتا انتخابشون برای برنامه نویس ها مشکل ایجاد میکنه .

چون مطلب زیاده و نمیخوام وبلاگ دراز!‌ بشه میزارمش تو ادامه ی مطلب

خب اول ببینیم mysql چه موتورهایی برای ذخیره سازی داره‌‌ . این مطلب به نقل از یکتا بلاگ به آدرس yektapardaz.com می باشد :‌

InnoDB: این موتور پرکابردترین موتور ذخیره سازی است. این موتور از transaction پشتیبانی می‌کند و نیز دارای امکان crash-recovery است که در صورت آسیب دیدن جدول، به بازیابی آن کمک می‌کند. قفل (lock) در سطح رکورد و نیز عدم قفل شدن کل جدول در عملیات ثبت و ویرایش ردیف‌ها، باعث کارایی بالای این موتور شده و آن را به موتور ذخیره سازی ایده آل برای سیستم‌های چند کاربره تبدیل کرده است. همچنین این موتور از کلید خارجی پیشتیبانی می‌کند. از نسخه 5.5.5 به بعد، InnoDB موتور ذخیره سازی پیش فرض Mysql است.
MyISAM: جداول با این موتور ذخیره سازی فضای کمتری از دیسک را نسبت به سایر موتورهای ذخیره سازی اشغال می‌کنند. قفل در سطح جدول در این موتور ذخیره سازی باعث کاهش کارآیی در محیط‌هایی می‌شود، که در آنها نیاز به خواندن و نوشتن همزمان است. بنابراین، این موتور ذخیره سازی بیشتردر محیط‌هایی استفاده می‌شود که در آنها فقط نیاز به خواندن اطلاعات است، یا دفعات خواندن بمراتب بیشتر از دفعات نوشتن است؛ نظیر وب و انباره داده. برخلاف InnoDB، این موتور ذخیره سازی از full-text-search پشتیبانی می‌کند.
Memory: این موتور، اطلاعات جدول را جهت دسترسی سریع در RAM ذخیره می‌کند.
CSV: جداول در این موتور، در واقع فایل‌های متنی comma-separated هستند. بیشترین استفاده این نوع جداول در import/export است.
Archive: این نوع از جداول کم حجم و فشرده بوده وامکان شاخص گذاری (Indexing) ندارند. این نوع جدول، معمولاً در موارد زیر استفاده می‌شود:
ذخیره و بازیابی حجم بالای اطلاعاتی که به ندرت به آنها نیاز داریم.
آرشیو اطلاعاتی.
ذخیره اطلاعات امنیتی.
Blackhole: این نوع جدول اطلاعات را گرفته ولی ذخیره نمی‌کند و مانند /dev/null در یونیکس است. این نوع جدول برای replication، بررسی سربار وارده به سرور هنگامی که binary-log فعال است، و … استفاده می‌شود.
Merge: این موتور ذخیره سازی امکانی را فراهم می‌کند که مدیر پایگاه داده، یک سری از جداول یکسان MyISAM را بطور منطقی دسته بندی کرده و بعنوان یک شی واحد درنظر بگیرد. این موتور ذخیره سازی برای محیط‌های با پایگاه‌های داده بسیار بزرگ، نظیر انبار داده، مناسب است.
Federated: این موتور ذخیره سازی امکانی را فراهم می‌آورد که چند سرور Mysql که ازنظر فیزیکی جدا هستند، به هم متصل شوند و یک پایگاه داده مجازی را تشکیل دهند. از اینرو، برای محیط‌هایی که اطلاعات توزیع شده دارند، نظیر بازارها و مراکز بازرگانی و … مناسب است.

خب توضیحات قشنگی بودش. بین این موتورها ۲ موتور از همه خوش دست تر و پر کاربردتر هستند ‌:‌ myIsam و InnoDB ‌. اما بین این ۲ تا کدوم رو انتخاب کنیم؟؟

خب اول میایم بررسی کنیم چه فرقایی دارن با هم دارن :

MyISAM و InnoDB دو تا engine معروف mysql هستند. با اینکه MyISAM سرعت بیشتر و حجم کمتری داره ولی معمولا برای  دیتابیس های حجیم از InnoDB استفاده میشه. این کار سه تا علت عمده داره:

• InnoDB  در عملیات نوشتن از row-level lock استفاده میکنه ولی MyISAM از table-level lock استفاده میکنه.
  به عبارت دیگه InnoDB وقتی میخواد یک رکورد بنویسه یا آپدیت کنه فقط همون یک رکورد رو قفل میکنه ولی MyISAM کل جدول رو قفل میکنه. این تفاوت باعث میشه زمانی که تعداد عملیاتهای نوشتن و آپدیت زیاده سرعت InnoDB بیشتر باشه. به عبارتی این عملیات ها در InnoDB میتونن همزمان اجرا بشن ولی در MyISAM هر عملیات باید منتظر اتمام عملیات قبلی بمونه.
• جداول InnoDB در هنگام کرش کردن احتیاجی به repair ندارند ولی جداول MyISAM در صورت کرش کردن باید repair بشن.
  عملیات repair عملیات فوق العاده زمان بری هست. برای همین استفاده از MyISAM برای جداول حجیم مناسب نیست. من یک جدول MyISAM با حدود 60 میلیون رکورد رو خواستم repair کنم که بعد از گذشت دو روز و تموم نشدن عملیات مجبور شدم kill کنمش و چاره‌ای دیگه بیاندیشم.
• از جداول InnoDB میشه در حالیکه دیتابیس فعاله Hot Backup تهیه کرد(با استفاده از XtraBackup) ولی برای تهیه Hot Backup از جداول MyISAM باید جداول Lock بشن که باعث میشه در زمان تهیه بکاپ readonly باشند (مگر اینکه از LVM یا replication برای بکاپ گیری استفاده کنیم).

InnoDB و MyISAM تفاوت‌های دیگه ای هم دارند مثلا اینکه InnoDB از Transaction و Foreign Key پشتیبانی میکنه ولی MyISAM پشتیبانی نمی‌کنه. همینطور MyISAM از Full-text index پشتیبانی میکنه ولی InnoDB پشتیبانی نمی‌کنه.

یکسری نکات دیگه در مورد InnoDB هست که کمتر توی اینترنت در موردش صحبت شده که عدم آگاهی از اونها میتونه دردسر ساز بشه.

مثلا اینکه با حذف رکورد از جداول InnoDB حجم جداول کاهش پیدا نمیکنه و فضای مربوط به رکوردهای حذف شده آزاد نمیشه. البته این فضا بعدا برای رکوردهای جدید مصرف خواهد شد.
ضمنا InnoDB در حالت پیشفرض داده‌های همه جداول InnoDB مربوط به همه دیتابیس ها رو در یک فایل مشترک(ibdata1) ذخیره می‌کنه در این حالت حتی با حذف جدول هم فضای مصرفی اون جدول آزاد نمیشه. برای همین اگر قصد استفاده از InnoDB رو دارید حتما تنظیم innodb_file_per_table رو در فایل my.cnf قرار بدید تا داده‌های هر جدول در شاخه‌ی دیتابیس مربوطه و با نام متناظر با جدول ذخیره بشه. البته یادتون باشه که حتی در صورت استفاده از این گزینه باز هم جداول InnoDB مستقل نیستند و وابسته به فایلهای ibdata1 و ib_logfile ها هستند. به عبارتی با کپی گرفتن از شاخه یک دیتابیس و منتقل کردنش به یک سرور دیگه نمیشه جداول InnoDB رو منتقل کرد و همچنین برای تهیه پشتیبان کپی کردن شاخه دیتابیس تنها کافی نیست. در حالی که اینکارها برای جداول MyISAM براحتی امکان پذیره.
البته برای انتقال یک جدول InnoDB بین دو دیتابیس روی یک سرور می‌تونید از RENAME TABLE استفاده کنید.

InnoDB بر خلاف MyISAM تعداد رکوردها رو ذخیره نمی‌کنه. که باعث میشه COUNT گرفتن بدون استفاده از WHERE روی جداول InnoDB خیلی زمان‌بر باشه و برای جداول حجیم عملا غیر ممکن باشه. در واقع نیاز به استفاده از COUNT بدون WHERE به ندرت پیش میاد که احتمالا قابل صرف نظر کردن هست. البته اگر رکوردی از جدول حذف نمی‌کنید می‌تونید به جای COUNT از MAX(id)‎ استفاده کنید. یک راه دیگه استفاده از Trigger هست که توصیه نمیشه چون performance رو پایین میاره.

در ضمن برای استفاده بهینه از InnoDB در صورتی که از سرور اختصاصی برای mysql استفاده می‌کنید می‌تونید مقدار innodb_buffer_pool_size را حدود 70-80 درصد از حافظه RAM قرار بدین