مقایسه موتور های ذخیره سازی Innodb و MyISAM

[Mr.Te0]

http://www.rezaseyf.com/wp-content/uploads/2014/08/mysql-big2.gif

انجین‌های دیتابیس مای‌اس‌کیو‌ال – MyISAM و Innodb



دیتابیس MySQL از Table Engineهای متفاوتی پشتیبانی میکند که با عنوان “Table Type” هم شناخته میشوند.
یک دیتابیس میتواند مخلوطی از table های مختلف با engineهای متفاوت ویا یکسان باشد. برای مشاهده لیست کاملی از انجین‌های موردپشتیبانی دیتابیس MySQL به این صفحه (http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html) مراجعه کنید.

اما در بین این انجین‌های مختلف، در سایت‌های طراحی شده بصورت کلاود (cloud) ، از دیتابیس MySQL و دو انجین استفاده میشود: InnoDB و MyISAM.
توجه داشته باشید در این مقاله قصد دارم بصورت کامل مطالب مرتبط با موارد کاربرد این دو انجین در وبسایت‌های کلاود را پوشش دهم و کاری با performance نخواهم داشت. اگر نیاز به مطالعه مطالب مرتبط با performance دارید, اینجا (http://www.rezaseyf.com/www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/) و اینجا (http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB#comment-115) را مطالعه کنید.
در دیتابیس MySQL 5.0 به بعد ، انجین MyISAM ، بصورت table engine پیش‌فرض استفاده میگردد اما سایت‌های کلاود از انجین InnoDB بصورت پیش‌فرض در طراحی‌هایشان استفاده میکنند. به عبارت دیگر وبسایت‌های کلاود، اگر شما بصورت کاملا واضح توسط DDL نوع انجین خودتان را مشخص نکرده باشید، از InnoDB در DDL* تیبل شما استفاده میکند. با استفاده از InnoDB ، performance نرم‌افزارتان نیز بصورت کاملا محسوسی افزایش خواهد یافت.


مقایسه:



MyISAM
Innodb


عدم سازگاری با ACID* و دیتا قابل برگشت نیست (به عبارتی non-transactional* است)
پشتیبانی کامل از ACID و دیتای قابل برگشت . انجین transactional* است.
پشتیبانی کامل از commit و rollback در foreign keyها


انجین پیش‌فرض در MySQL 5.0 به بالا
انجین پیش‌فرض در بعضی سرورهای هاستینگ کلاود و توزیع‌های خاص مانند CloudLinux


قابلیت فشرده‌سازی داده‌ها را دارد
قابلیت فشرده‌سازی داده‌ها را دارد


هرازگاهی نیاز به repair/rebuild کلیه‌ی indexها و tableها دارد
با استفاده از فناوری Auto recovery در صورت کراش کردن، توسط لاگ‌ها خودش را تعمیر میکند


تغییرات در داده‌ها بصورت لحظه‌ای روی دیسک سرور نوشته‌ میشود.
قبل از commit و flush کردن داده‌ها روی دیسک سرور، از یک فاصله رندم یا تعیین‌شده استفاده میکند تا لود سرور بیش از حد بالا نرود.


هیچ نظمی در هنگام ذخیره‌سازی برروی دیتاهای شما اعمال نمیشود
تمامی ردیف‌ها (rowها) در صفحات مختلف توسط primary keyها مرتب میشوند.


قفل کردن Table هنگام نوشتن داده‌ها
قفل کردن ردیف‌ها (rowها) هنگام نوشتن داده‌ها




مفاهیم مقدماتی بانک اطلاعاتی (برای دوستانی که آشنایی ندارند)

* transactional و non-transactional چیست؟

— transactional یعنی سیستم دیتابیس شما از عملیات بازگردانی خودکار rollback و commit پشتیبانی میکند و اگر مشکلی پیش بیاید خودش بصورت اتوماتیک repiar میکند.
— non-transactional از سیستم rollback و commit اتوماتیک پشتیبانی نمیکند،بخاطر همین پرفرمنس برنامه‌ را افزایش میدهد. اما اگر مشکلی پیش بیاید باید خودتان آستین بالا بزنید.

* DDL چیست؟
مخفف Data Definition Language که در واقع شامل عملیات اولیه راه‌اندازی دیتابیس است . این عملیات عبارتند از CREATE , ALTER , DROP , TRUNCATE , COMMENT و RENAME
* DML چیست؟
Data Manipulation Language که به اختصار DML نامیده میشود ،‌نامی است که به عملیات دستکاری در داده‌ها اطلاق میشود. این عملیات عبارتند از: SELECT , INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN و LOCK TABLE
* ACID چیست؟
مخفف یکتایی، قابلیت اطمینان، انزوا و پایداری (Atomicity, Consistency, Isolation, Durability)، خصوصیتی است که در اکثر پایگاه داده‌ها و در انجام تراکنش‌ها باید پیاده‌سازی شود.این مشخصه در تراکنش‌های بانک‌ها بیشتر مورد توجه قرار می‌گیرد زیرا یک مجموعه عملیات تراکنش باید به شکل یک عملیات در نظر گرفته شود تا تراکنش با موفقیت صورت گیرد به طور مثال تراکنش انتقال وجه از یک حساب به حساب دیگر،باید دو عملیات به شکل کامل انجام شود. شما میتوانید اطلاعات بیشتری از اینجا (http://en.wikipedia.org/wiki/ACID) کسب کنید
مقایسه‌های بیشتری نیاز دارید؟ به مستندات MySQL نگاهی بیندازید! مستندات InnoDB (http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html) و مستندات MyISAM (http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html)


چه زمانی از MyISAM استفاده کنیم؟

دلایل مختلفی میتواند وجود داشته باشد که شما MyISAM را بعنوان انجین پیش‌فرض یک table استفاده کنید. اول از همه بخاطر داشته باشید که MyISAM طراحی شد تا متن خام ذخیره کند و برای برنامه‌هایی درنظر گرفته شد که دیتاهایشان به ندرت تغییر میکرد و بیشتر عملیات SELECT روی table انجام میگرفت تا insert ویا update. پس مطمین باشید اگر قرار است دیتای شما به ندرت تغییر کند ولی بسیار خوانده شود، MyISAM سریعترین پیشنهاد موجود برای شماست!
دلیل دیگری که شاید شما بخواهید بخاطرش از خیر InnoDB بگذرید و MyISAM استفاده کنید ، همین پیشتیبانی نکردن از ACID باعث میشود که سیستم دیتابیس شما عملیات کمتری روی دیتابیس شما انجام دهد و این یعنی پرفرمنس بیشتر.
بهرحال بهترین راه برای انتخاب انجین دیتابیس، فقط انجام BENCHMARK توسط شخص خودتان میباشد و باید امتحان کنید تا اندازه خمیر دستتان بیاید!

چه زمانی تغییر انجین از MYISAM به INNODB جزو اوجب واجبات محسوب میشود؟

خوب! جواب همه در جدول بالا قید شده! هروقت که به امکانات InnoDB نیاز داشتید! امکاناتی مثل رول‌بک‌های اتوماتیک و سازگاری با اسید و تعمیرات اتوماتیک!
و یا زمانیکه شما بصورت خیلی سخت همانقدر که عملیات SELECT دارید ، برنامه‌تان از سایر گزینه‌های *DML استفاده میکند. مثل INSERT و UPDATE. بهرحال بیاد داشته باشید LOAD سرور شما بالاخواهد رفت پس زیرساخت سخت‌افزاری خود را درنظر بگیرید.
وقتی هم حس کردید کاربرانتان زیاد شده و دیگر TABLE LOCK گزینه‌ی خوبی نیست و بهتر است در سطح بالاتری مراقب race condition باشید، InnoDB پیشنهاد میشود!


چطور TABLE هایم را از MYISAM به INNODB تبدیل کنم؟

به سادگی! با یک خط کد در mysqld یا phpmyadmin:





ALTER TABLE ENGINE=INNODB;





چطور یک table با انجین پیش‌فرض innoDB بسازم؟



CREATE TABLE mytable (
`pk_id` int(11) NOT NULL,

`name` varchar(128) DEFAULT NULL,
PRIMARY KEY (`pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




منبع (http://www.rezaseyf.com)

[raminramz]

تشکر خیلی مفید بود

3 تا سوال

ما وقتی os رو به کلودلینوکس تغییر میدیم خود به خود innodb میشه؟
2 تا سرور جدید گرفتیم جفتش myisam است اما قدیمیه روکه به کلودلینوکس منتقل کردیم innodb (دقت نکردم قبلش چی بود)
چون اول Cpanel و mysql اینارو نصب کرده بودم بعد انتقال دادم به کلودلینوکس

سوال دوم

ما یک سری سایت رو از سروری که innodb بوده انتقال میدیم به سروری که myisam است

اینا قبلا innodb ذخیره شده موقع ترنسفر یا ریستور جدول هاش میاد تبدیل به myisam میشه؟ یا برعکس
همچنین هنگام ایمپورت توی phpmyadmin ؟
یا بعد از انتقال دونه دونه باید تغییر داد؟

یکی از دوستان میگفت بعد از انتقال با خطای دیتابیس مواجه میشن


اگه وسط کار بخوایم تغییر انجین بدیم
مثلا در my.cnf به جای myisam بیام innodb رو انتخاب کنم
mysql رو ریستارت کنم مشکلی پیش نمیاد؟

اونایی که myisam نوشته شده توسط mysql به همون صورت خونده میشه
اونایی که innodb هم به همون سرعت؟

این که ما تایین میکنیم فقط نحوه ذخیره سازیه؟

[git]

MariaDB با mysql چه فرقی داره؟

[Marketer]

MariaDB با mysql چه فرقی داره؟



موتور های پایگاه داده myISAM و InnoDB ، تفاوت ها و نقاط ضعف و قدرت هر کدام !
بخش بسیار زیادی از کاربران امروزی در پایگاه داده های معروف با این سوال مواجه شده اند که ایا innoDB ارزش استفاده را دارد یا خیر ؟
من همیشه برای موتور های ذخیره سازی خودم از innoDB استفاده می کنم . کاربری آسان ، قدرت کنترل دوره نقاهت در سیستم های پایگاه داده ، پشتیبان گیری آنلاین آسان تر و رفع مشکل قفل شدن کل جدول در سیستم های myISAM و بسیاری موارد دیگر که به تفکیک در ادامه مقاله امده است .
ولی با تمام این موارد هنوز هم باید نکاتی را در نظر گرفت .
دلیل استفاده از myISAM ؟ فقط به خاطر پیش فرض بودن در سیستم یا قابل انتخاب بودن ان است ؟ خیلی وقت ها این سوال مهمی برای یک معمار دیتابیس می باشد ! مدیران پایگاه داده متاسفانه در بسیاری از موارد به دلیل پیش فرض بودن myISAM از آن استفاده می کنند ولی در مواردی هم از این موتور ذخیره سازی به شکل کاملا هوشمندانه و بهینه شده استفاده می شود .
حالا اگر شما هم می خواهید به صورت هوشمندانه از سیستم استفاده کنید پیشنهاد می کنم حتما موارد زیر را مطالعه کنید :
مقایسه از جوانب مثبت و منفی
در دو نوع اصلی موتور های ذخیره سازی در پایگاه داده ، myISAM & InnoDB می توان به موارد زیر اشاره کرد :
innoDB موتور ذخیره ساز جدید تری است در حالی که myISAM گسترده تر است .
innoDB پیچیدگی های بیشتری نسبت به MyISAM ساده دارد .
innoDB در نگهداشت داده ها پاسخگو تر است به نسبت myisam
Innodb از transaction ها پشتیبانی می کند در حالی که myISAM این قابلیت را ندارد .
innoDB دارای قابلیت کلید خارجی برای ارتباط های محدود در حالی که myisam ان را پشتیبانی نمی کند .
myISAM قابلیت full-text در ایندکس ها را دارد ولی InnoDB پشتیبانی نمی کند ( توضیح مختصری در مورد fulltext در انتهای مقاله آمده است )
درست است در بسیاری از موارد بالا Innodb نقاط قوتی دارد که مطرح شده است ولی هر کدام از این دوتا موتور ذخیره سازی توانایی و ضعف هایی را دارند که معمار دیتابیس پروژه بر اساس نیاز میتواند یکی را انتخاب کند .
به طور خلاصه به مواردی که در ادامه مقاله آمده است توجه فرمایید :
معایب استفاده از Innodb :
به دلیل اینکه در Innodb شما نیاز به مراقبت از تمام روابط بین جداول درون پایگاه داده را دارید لذا معمار دیتابیس شما نیاز به گذاشتن زمان بیشتری برای طراحی به نسبتmyisam می باشد .
استفاده از فضا اصلی سیستم بیشتری نسبت به Myisam می باشد . که البته وقتی در سایت خود mysql یا اوراکل شما مطالعه می کنید اکیدا بر این نکته اشاره دارد که در صورتی که ساختار پایگاه داده شما در حالت Innodb نمی باشد موتور Innodb نصب شده ( سرویس ) ان را خاموش کنید .
ناتوانی در پشتیبانی از قابلیت های transaction برای پایگاه داده های حساس
مزایای استفاده از Myisam :
طراحی و پیاده سازی بسیار ساده ، بنابراین بهترین راهکار برای مبتدیان در پروژه های پایگاه داده می باشد .
عدم نیاز به طراحی برای روابط خارجی در بین جداول
در پایگاه داده های سبک ، بسیار سریع تر از innodb می باشد ، پس هزینه نگهداری سرور هم نیز پایین تر است .
امکان فشرده سازی داده ها برای سرعت بیشتر در هنگام select گرفتن از جداول
معایب myISAM :
عدم مدیریت تمامیت داده ها ، ارتباط بین جداول ، هنگامی که این عیب وجود داشته باشد در واقع مسئولیت درست کردن رابطه بین جداول فرآیندی سربار می شود برای برنامه نویس های پروژه .
توانایی پشتیبانی از transaction ها که در برنامه های کاربردی و حساس مانند بانکداری بسیار مورد توجه می باشند را ندارد
در حالتی که قرار است رکورد ها بالا مدیریت شوند توسط پایگاه داده ، بسیار کند تر از InnoDB می باشد مثلا برای اضافه کردن یک رکورد خاص ، کل جدول را قفل می کند و بعد از اتمام کار آزاد سازی جدول را انجام می دهد .
نتیجه : مقایسه را می توان ساده کرد اگر بخواهیم در سیستم به روز رسانی ها مکرر داشته باشیم باید از Innodb حتما استفاده کنیم و اگر از سوی دیگر ، فقط در سیستم نیاز کلی به انتخاب و نمایش و اضافه کردن رکورد ها در حد پروژه های ساده تر می باشد حتما از myISAM استفاده کنید .
پ . ن : Full Text Search امکان index کردن و جستجوی دیتای متنی را بر مبنای لغات کلیدی در SQL Server فراهم می کند . بر خلاف LIKE که محدوده عمل آن روی کاراکترهاست ، Full Text Search بر روی کلمات بر مبنای زبان متن ، پرس و جو انجام می دهد . اختلاف سرعت Full Text Search و LIKE در زمانی که می خواهیم یک Query را بر روی چند میلیون رکورد اجرا کنیم بسیار مشهود است . اگر با LIKE دنبال یک ترکیب بگردیم ، تهیه گزارش ممکن است چندین دقیقه طول بکشد در حالی که استفاده از Full Text Search آن را به چند ثانیه کاهش می دهد .