موتورهای ذخیره سازی در پايگاه داده‌ MySQL ( InnoDB )

موتورهای ذخیره سازی در پايگاه داده‌ MySQL(InnoDB) استاد راهنما: دکتر رهگذر ارائه دهنده: هما برادران هاشمي زمستان 87

فهرست مطالب بررسی موتورهای ذخیره سازی در MySQL سطوح جداسازی در InnoDB قفل گذاري در InnoDB اجراي تراكنش در MySql اضافه كردن SavePointها به تراكنش‌ها دستور AutoCommit جمع‌بندی منابع

مروری بر MySQL محبوب‌ترین پایگاه داده منبع آزاد • MySQL • قابلیت اطمینان بالا • کارآیی مناسب • آسانی استفاده • کم هزینه • معایب نسخه‌های اولیه MySQL • نبود دید (view) • نبود روال‌های ذخیره شده(store procedure) • نبود تریگرها • پشتیبانی از انواع داده‌ فضایی در نسخه‌های جدید

موتور ذخیره سازی چیست؟ (Storage Engine) • معماری نسخه‌های ابتدایی MySQL • لایه SQL: مسئول تمام عملیات سطح بالا • لایه Storage: مسئول ذخیره سازی، تراکنش‌ها و غیره • تغییر معماری در MySQL 5.1 به بعد • وجود یک لایه رابط موتور ذخیره‌سازی پیمانه‌ای • قابلیت کامپایل جداگانه موتور ذخیره سازی و بارگذاری آن در سرور • ترویج توسعه موتورهای ذخیره سازی داخلی و خارجی • بنابراین افزایش تعداد زیادی از آنها

معماریMySQL ماژول‌های مختلف موجود در‌‌MySQLبا نامگذاری براساس وظایفشان

موتور ذخیره سازی چیست؟ (ادامه) • موتورهای ذخیره سازی مسئول ذخیره سازی داده‌ها • توانایی پیاده سازی • مفاهیم ذخیره سازی متفاوت • انواع فایل‌‌ها • ذخیره سازی از راه دور • پیمایش کارآ • عدم توانایی رفتار با توابع سطح بالا • Sorting • Group by • Limit • برآوردن این انتظارات در نسخه‌های آینده MySQL • بعضی از فروشندگان (همانند Kickfire) • دست‌کاری کد • قطع پردازش query • اجرای آن توسط موتور پردازشی جدید

توسعه توسط موتورهای ذخیره سازی • روش غیر عادی MySQL برای توسعه DBMS: • عدم وجود انواع اندیس‌ها و خصوصیات زبان قابل افزودن (pluggable) • اگر چه انواع مختلف موتورهای ذخیره سازی • مزایا • برنامه‌های متفاوت نياز به ویژگی‌های ذخیره سازی متفاوت • به عنوان مثال: انواع روش‌های Persistence, transactions, lockgranularity, compression • معایب • مشکل کارآیی • 2 phase commit هنگام commit تراکنش، لاگ‌های جدا • پیچیدگی • توسعه و تست (اثر متقابل آنها) • انتخاب موتورهای ذخیره سازی • چالش‌های مؤثر (پشتیبانی، موازنه و غیره)

انتخابموتور ذخیره سازی • خاص بودن هر یک از موتورهای ذخیره سازی • دارا بودن طراحی منحصر به فرد • امکان اجرا نشدن یک برنامه که توسط یک موتور ذخیر سازی نوشته شده توسط موتور ذخیره سازی دیگر • دارا بودن روش‌های بهینه سازی مخصوص • بنابراین پیروی از یکسری الگوهای طراحی معین

انواعموتورهای ذخیره سازی • موتورهای ذخیره سازی عمومی • تراکنشی • Innodb, Falcon, PBXT, Maria (future) • غیر تراکنشی • MyISAM, ISAM (dead),Maria(current) • موتورهای ذخیره سازی خوشه بندی • NDB, ScaleDB (CloseSource) • موتورهای ذخیره سازی خاص منظوره • Memory, Federated, Archive,Blackhole, CSV, NitroDB(CS), SphinxSE , InfoBright(CS), Queue, Graph(CS)

موتورهای ذخیره سازی در MySQL(انواع جداول) عدم پشتیبانی از Transaction و کلید خارجی سرعت بیشتر پشتیبانی از Transaction و کلید خارجی تراکنش امن در سال 2001 به همراه نسخه MySQL-3.23.34a ISAM MyISAM BDB (BerkeleyDB) InnoDB Falcon

موتور ذخیره سازی MyISAM CREATE TABLE articles (articleIDint not null auto_increment primary key,title varchar (2MERGE),body text,fulltext (title,body)); SELECT titleFROM articleWHERE MATCH (title, body) AGAINST ('miniprogrammer'); • بهینه سازی برای • افزایش سرعت • قابلیت اطمینان • ذخیره سازی سریع • ارائه تراکنش‌های غیر امن • امکان FullText Searching • جستجو برای یک کلمه یا رشته در یک متن • پشتیبانی از stemming

موتور ذخیره سازیInnoDB • شروع نوشتن InnoDB 10 سال پیش • هدف نویسنده: • جستجوی راهی برای توسعه کارآیی پایگاه داده‌های سنتی • پشتیبانی کامل از سطح‌های جداسازی SQL • براي تراكنش هاي ACID • بهتر كردن زمان پاسخ توسط • MVCC(multi-version concurrency control ) • قفل گذاري سطح پايين (بر روي سطر) • Fuzzy Checkpointing • كلاستر كردن توسط كليد اصلي و نوشتن بر روي صفحات يكسان

مزایای موتورهای ذخیره سازی InnoDB • امن‌تر • هنگام خرابی MySQL و یا سخت‌افزار • روش ترمیم خودکار از طریق داده‌های پشتیبان به همراه logهای تراکنش‌ها • پشتیبانی از کلید‌های خارجی • افزایش همزمانی • قفل در سطح سطر • خواند‌ن‌های بدون قفل در دستورات SELECT (مشابه Oracle) • برگشت تغییرات انجام شده توسط دستور ROLLBACK نیاز به امن بودن تراکنش موجب افزایش زمان اجرا، فضای دیسک و حافظه

فهرست مطالب بررسی موتورهای ذخیره سازی در MySQL سطوح جداسازی در InnoDB قفل گذاري در InnoDB اجراي تراكنش در MySql اضافه كردن SavePointها به تراكنش‌ها دستور AutoCommit

سطوح جداسازي در InnoDB پدیده فانتوم • READ UNCOMMITTED • Dirty Read • برگرداندن مقادیر نوشته شده حتی اگر commit نشده باشند • خواندن‌ها پایدار نیستند • READ COMMITTED • تراکنشی در حال خواندن چندین سطر باشد • تراکنش دیگری یک یا چند سطر آن را تغییر دهد • اگر تراکنش اول دوباره کوئری را اجرا کند جواب متفاوتی درمی‌گیرد • REPEATABLE READ • تمام خواندن‌هاي ثابت مربوط به يك تراكنش از يك تصوير يكسان از پايگاه داده براي خواندن داده‌ها استفاده مي‌کنند • SERIALIZABLE • تمام دستورات SELECT ساده تلويحا بصورت SELECT... LOCK IN SHARE MODE تبديل مي‌شوند

تنظیم سطوح جداسازي تراکنش SET [SESSION | GLOBAL] TRANSACTIONISOLATIONLEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} • معنای دو کلمه کلیدی Session و Global • انتخاب Global: اعمال عبارت Set Trancation بر روی تمای Connectionهای جدید پایگاه داده به غیر از Connection جاری • انتخاب Session : اعمال عبارت Set Trancation بر روی تمامی تراکنش‌های جدیدی که در طول Connection جاری شروع شده • انتخاب هیچکدام: اعمال عبارت Set Transaction فقط برای تراکنش بعدی

تنظیم سطوح جداسازي تراکنش (ادامه)

خواندن پایدار(Consistent Read) • خواندن پایدار : به معنای استفاده InnoDB از ویژگی چند-نسخه‌ای خود برای ارائه یک تصویر لحظه‌ای از پایگاه داده در یک نقطه از زمان به یک پرس و جو • مد پیش فرض در دستور SELECT در سطوح • READ COMMITTED • REPEATABLE READ • قرار ندادن هیچقفليتوسط خواندن پایدار بر روي جداول قابل دسترسی • توانایی تغییر آن جدول توسط كاربران ديگر در همان زمان

قفل‌ها در InnoDB • SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK SHARE MODE; • مناسب نبودن خواندن پايدار در بعضی مواقع • بعنوان مثال: • تراکنش يك سطر به جدول فرزند اضافه كند و در ضمن از وجود پدر اين سطر در جدول پدر نيز مطمئن باشد. • استفاده از خواندن پایدار برای خواندن جدول پدر • در این حین کاربر دیگری سطر پدر را از جدول پدر حذف کرده • راه حل :

مجموعه قفل‌ها برای دستورات متفاوت SQL در InnoDB • SELECT ... FROM ... • یک خواندن پایدار • خواندن یک تصویر لحظه‌ای از پایگاه داده‌ها • قفلی نمی‌گذارد • SELECT ... FROM ... LOCK IN SHARE MODE • قفل اشترکیبر روی تمام کلیدهای بعدی می‌گذارد (shared next-key locks) • SELECT ... FROM ... FOR UPDATE • قفل انحصاری بر روی تمام کلیدهای بعدی می‌گذارد(exclusive next-key locks) • INSERT INTO ... VALUES (...) • قفل انحصاری بر روی سطر اضافه شده می‌گذارد

مجموعه قفل‌ها برای دستورات متفاوت SQL در InnoDB (ادامه) • INSERT INTO T SELECT ... FROM S WHERE ... • بر روی هر سطر درج شده در T قفل انحصاری می‌گذارد • CREATE TABLE ... SELECT ... • دستور SELECT را بصورت خواندن سازگار و يا همراه با قفل‌هاي اشتراكي انجام مي‌دهد. • REPLACE • در صورتيكه ركوردي با كليد يكتاي مشابه يافت نشود، مانند يك دستور INSERT عمل مي‌كند. • در غير اين صورت يك قفل كليد-بعدي انحصاري بر روي سطري كه بايد تغيير كند، قرار داده مي‌شود.

مجموعه قفل‌ها برای دستورات متفاوت SQL در InnoDB(ادامه) • UPDATE ... SET ... WHERE ... • يك قفل كليد-بعدي انحصاري بر روي هر ركوردي كه عمل جستجو با آن برخورد مي‌كند، قرار داده مي‌شود. • LOCK TABLES... • جدول‌‌هاي مشخص شده را قفل مي‌كند. • در پياده‌سازي لايه MySQL مسئول قرار دادن اين قفل‌ها است. • بهمين علت مكانيزم تشخيص خودكار بن‌بست InnoDB در صورتيكه اين قفل‌ها در ايجاد بن‌بست دخيل باشند، قادر به تشخيص بن‌بست نمي‌باشد.

قفل گذاری بر روی جداول غیر تراکنشی LOCK {TABLE | TABLES} <table name> [AS <alias>] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [{, <table name> [AS <alias>] {READ [LOCAL] | [LOW_PRIORITY] WRITE}}...] • قفل Read: • با این قفل هر Connection می‌تواند از جدول بخواند ولی هیچ Connectionای نمی‌تواند در جدول بنویسد. • اگر Read Local را انتخاب نمایید دستور insert می‌تواند به صورت Nonconflicting به وسیله هر Connectionای اجرا گردد.

ادامه قفل گذاری و باز کردن قفل‌ها • قفل Write: • خواندن ونوشتن Connectionجاری می‌تواند از جدول • اما Connectionهای دیگر نمی‌توانند به جدول دسترسی داشته باشند تا زمانی که قفل آن آزاد شود • مثال: • بازکردن قفل‌ها

مثال

بررسی پشتیبانی MySQLاز InnoDB • پاسخ پرس و جوی مقابل • YES : جداول InnoDB فعال است و دست کم دارای پیکربندی ابتدایی است. • NO: جداول InnoDB کامپایل نشده‌اند. • DISABLED جداول InnoDB کامپایل شده است اما توسط گزینه “—skip-innodb”غیر فعال شده‌اند.

اجرای یک تراکنش ساده

اجرای یک تراکنش ساده (ادامه)

دستوراتی که خودکار تراکنش را Commit می‌کنند هیچ کدام از عبارات بالا نمی‌توانند RollBack گردند و از این عبارات نباید در داخل تراکنش استفاده شود چون باعث Commit شدن تراکنش می‌گردد. ❑ ALTER TABLE:Modifies a table definition. ❑ CREATE INDEX: Creates an index on a table. ❑ DROP DATABASE: Removes a database from a MySQL server. ❑ DROP INDEX: Removes an index on a table. ❑ DROP TABLE: Removes a table from a database. ❑ LOCK TABLES: Prevents concurrent access to tables. ❑ RENAME TABLES: Renames a table. ❑ SET AUTOCOMMIT=1: Sets the autocommit mode to on. ❑ START TRANSACTION: Begins a transaction. ❑ TRUNCATE TABLE: Removes data from a table. ❑ UNLOCK TABLES: Unlocks locked tables.

اضافه SavePoint به تراکنش SAVEPOINT <savepoint name> ROLLBACK TO SAVEPOINT <savepoint name>

اضافه SavePoint به تراکنش (ادامه)

دستورAutoCommit • برای Commitیک عبارت به صورت خودکار • این خاصیت به مقدار یکset • برای جلوگیری از این عمل به صفر • در حالت پیش فرض این خاصیت مقدار برابر یک • SET AUTOCOMMIT = 0; • SET ATOCOMMIT = 1; • SELECT @@autocommit;

مثال در این مثال به دلیل اینکه AUTOCOMMIT = 0 بوده است و در انتهای session به دلیل اینکه کاربراطلاعات را Commit نکرده رکورد درج شده RollBack می‌گردد به همین دلیل مشاهده می‌نمایید که رکورد درج نشده است

نتیجه گیری و جمع‌بندی • نقاط قوت InnoDB • کارآیی خوب (استفاده yahoo از این نوع موتور ذخیره سازی) • پایداری خوب • محافظت داده‌ای خوب • تطبیق‌پذیری با اندیس گذاری hash و دیگر روش‌های پیشرفته • اخیراInnoDB پیش فرض MySQL شده است • دلیل تاخیر: پیمایش کل index برای دستور SELCET COUNT(*)

نتیجه گیری و جمع‌بندی (ادامه) • نقاط ضعف InnoDB • سرعت توسعه کم در سال‌های اخیر • هنوز داشتن مشكل با چندین CPU • هنوز داشتن مشكل در یکپارچه شدن آن با MySQL

منابع • Bannon, Chin, Kassam, and Roszko, “InnoDB Concrete Architecture”. Waterloo, Ontario: Software Architecture Group, University of Waterloo, 2002 • Bannon, Chin, Kassam, and Roszko, “MySQL Conceptual Architecture”. Waterloo, Ontario: Software Architecture Group, University of Waterloo, 2002 • M Di Giacomo, “MySQL: lessons learned on a digital library”, Software, IEEE, - ieeexplore.ieee.org, 2005. • Leon Atkinson, “Core MySQL”, Prentice Hall PTR,ISBN 0-13-00661902, 2002. • Silberschatz, H. Korth and S. Sudarshan, “Database system concepts”, Mc Graw Hill 4th Edition, ISBN 0-07-112268-0, 2002. • MySQL reference manual, available at http://www.mysql.org. • Atkinson, Leon. Core, “MySQL: The Serious Developer’s Guide.” New Jersey: Prentice Hall Publishing, 2002. • http:// www.innoDB.com

با تشكرسؤال

موتورهای ذخیره سازی در پايگاه داده‌ MySQL ( InnoDB )