اگر از MySQL استفاده میکنید حتما باید بدونید که چه نوع انجینی رو برای ذخیره کردن داده ها استفاده کنید.
قبل از اینکه از بدونید چرا باید به انتخاب Innodb و یا MyIsam اهمیت بدهیم بهتره در مورد نحوه ایجاد تغییر و دستکاری در جدول (table) ها چیزهایی بدونیم.
برای اینکه دو کوئری با هدف تغییر به طور همزمان به سراغ یک سطر نروند باید اجازه دسترسی از کوئری های دیگر گرفته شود.
فرض کنید در یک پایگاه داده، چندین تراکنش با یکدیگر در حال اجرا هستند. خاصیت isolation به ما میگوید تراکنش ها نباید از اجرای یک دیگر مطلع شوند. یعنی تراکنش ها باید طوری اجرا شوند که انگار، فقط همین تراکنش در حال اجرا در کل پایگاه داده است. برای این عمل فرایندها باید اجازه اتصال و تغییر را از بقیه بگیرند به این عمل قفل (lock) شدن میگویند. این عمل قفل شدن به دو صورت میتواند ایجاد شود.
قفل شدن جدول Table Locking:
زمانیکه برای یک یا چند سطر از جدول درخواست تغییر (update , delete) فرستاده میشود کل آن جدول تا زمان انجام تغییرات قفل میشود. این روش تنها اجازه یک کوئری تغییر در یک زمان را میدهد و بقیه درخواست های تغییر منتظر میمانند تا کار تغییر قبلی تمام و جدول مورد نظر آزاد شود که بیشتر مناسب برای دیتابیس هایی است که بیشتر عمل خواندن (select) از آنها صورت میگیرد. MyIsam از این روش استفاده میکند.
این قفل شدن و آزادسازی جداول به شیوه ی زیر انجام میشود:
دو صف درخواست قفل شدن برای تغییر (update , delete) و خواندن (select) در نظر بگیرید.
اگر میخواهید برای نوشتن در جدول آنرا قفل کنید:
اگر میخواهید برای خواندن در جدول آنرا قفل کنید:
توجه کنید که ابتدا صف نوشتن بعد از هر بار آزادسازی خوانده میشود چرا که به آن اولویت بالاتری داده شده.
برای اینکه نسبت قفل شدن به منتظر ماندن در صف را ببنید میتوانید:
SHOW STATUS LIKE 'Table%'
+-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Table_locks_immediate | 150 | | Table_locks_waited | 11 | +-----------------------+---------+
در این روش اگر از Group By استفاده میکنید میتواند سریع تر عمل کند.
قفل شدن سطر Row-level locking:
این روش که innodb از آن برای کارایی حداک‍ثری دیتابیس استفاده میکند و همزمانی بالا و چندکاربره بودن را پشتیبانی میکند عمل قفل شدن به صورت سطر به سطر اتفاق میفتد.
دو کانکشن را در نظر بگیرید که هر کدام روی یک سطر میخواهند تغییر ایجاد کنند:
UPDATE customers SET name = NEW NAME WHERE id=1; DELETE FROM customers WHERE id=1;
کوئری دوم منتظر جواب یا خطایی از کوئری اول میماند تا بتواند اعمال شود. متغیر innodb_lock_wait_timeout منتظر ماندن برای اعمال کوئری تغییر و سپس برگرداندن transaction به حالت اولیه را تنظیم میکند.
برای جلوگیری از اینکه دو کوئری یک سطر را قفل کنند و منتظر آزادسازی یکدیگر شوند و بن بست (deadlock) پیش بیاید هر کوئری تغییر قبل از اضافه شدن به صف، لیست همه ی قفل شده ها را چک میکند و این انتظار روند را کند میکند.
همینطور با غیر فعال کردن innodb_deadlock_detect در سیستم های که همزمانی بالایی دارند میتواند از تشخیص جلوگیری کند. و از همان innodb_lock_wait_timeout برای آزاد سازی کوئری ها استفاده کنیم.
برای تغییر این دو متغیر میتوانید :
SET GLOBAL innodb_deadlock_detect = 1000;
همچنین برای دیدن وضعیت و جزییات بیشتر از قفل شدن :
SHOW ENGINE INNODB STATUS;
روش MyIsam از transaction پشتیبانی نمیکند یعنی اگر در بین کوئری مشکلی پیش بیاد تا آنجایی که مشکلی پیش آمده تغییر را اعمال میکند ولی در InnoDB خاصیت تجزیه ناپذیری یا atomicity برقرار است یعنی بعد از پیش آمدن خطا در میانه اعمال کوئری همه چیز به حالت قبل برمیگردد.
اما InnoDB از خواص Relational پیروی میکند و update و delete آبشاری شما را در بین جداول پشتیبانی میکند. از طرفی در InnoDB همه داده های جداول (از جمله index) رو بر روی cache ذخیره میکند و به مموری زیادی احتیاج دارد در حالیکه MyIsam فقط index ها را ذخیره میکند.
اما Myisam عملکردی درخشان تر از innodb خواهد داشت چرا که وقتی تغییرات زیاد نیست قفل شدن کل جدول سرعت بالاتری نسبت به هر سطر برای تغییرات زیاد دارد. و اگر تغییرات سرعت بالاتری از خواندن داشته باشند innodb عملکرد بهتری دارد زیرا میتواند زودتر تغییرات را واگذار فرایند بعدی کند.
