آشنایی با معماری داخلی MySQL

همان طور که در شکل هم مشاهده میکنید، معماری mysql از 3 لایه تشکیل می شود.

لایه اول (Client): اعمالی نظیر connection handling و احراز هویت و امنیت (که توسط ابزار های مبتنی بر شبکه فراهم میشوند) ارایه میشود که مختص فقط mysql هم نیست.

لایه دوم (Server): مغز متفکر mysql است که شامل پارس کردن کویری، آنالیز ، بهینه سازی و کش میشود.

لایه سوم (storage engines): مسولیت ذخیره و نگهداری داده را در mysql دارند.

سرور mysql از طریق API ها با storage engine ها در ارتباط است.

از معروف ترین storage engine ها میتوان به InnoDB و MyISAM اشاره نمود.

مراحل پردازش یک کویری زمانی که به mysql میرسد چیست؟

1. کاربر عبارت sql را به سمت سرور از طریق یک پروتکل مشخص ارسال میکند.

2. سرور کویری کش (query cache) را چک میکند. اگر نتیجه ای یافت، فورا به سمت کاربر بازمیگرداند. در غیر این صورت، sql statement به مراحل بعدی پاس میدهد.

3. سرور sql را بعد از پارس کردن (parse) و پیش پردازش کردن (preprocess) و همچنین بهینه سازی(optimization)، تبدیل به یک برنامه اجرایی کویری (query execution plan) میکند.

4. موتور اجرایی کویری (query execution engine) برنامه (plan) را از طریق صدا زدن (call) API های موتور های ذخیره سازی (storage engine) اجرا میکند.

5. در نهایت سرور نتیجه را به سمت کاربر میفرستد.

مدیریت ارتباط و امنیت (Connection Management and Security)

· هر ارتباط کاربر، نخ (thread) مختص به خود را درون فرایند (process) سرور دریافت میکند.

· کویری های ارتباط (connection query) در درون همان تک نخ که ممکن است بر روی یک هسته یا پردازنده فعالیت کنند، اجرا میشود.

· سرور نخ (thread) ها را کش میکند، بنابراین نیازی به ساختن و از بین بردن هرباره ی نخ ها برای هر اتصال جدید وجود ندارد.

· پایگاه داده mysql ورژن 5.5 و به بعد از پلاگین های thread polling نیز پشتیبانی میکنند.

بهینه سازی و اجرا (Optimization and Execution)

· پایگاه داده Mysql کویری (پرس و جو) ها را پارس میکند تا به یک ساختار داخلی (internal structure) برسد (parse tree). سپس یکسری از بهینه سازی ها را بر روی این درخت انجام میدهد.

· این بهینه سازی ها شامل بازنویسی کویری، در نظر گرفتن اینکه کدام جدول ابتدا خوانده شود، کدام ایندکس ها استفاده شوند و ... میشود.

· ما میتوانیم یکسری hint به بهینه ساز (optimizer) از طریق کلمات کلیدی مخصوصی ارسال کنیم تا بر روی تصمیم گیری آن تاثیر بگذاریم.

· همچنین می توانیم از سرور درخواست کنیم تا جنبه های مختلف بهینه سازی اش را برای ما توضیح دهد.

· بهینه ساز از موتور های ذخیره سازی یکسری سوالات از قبیل هزینه ی اعمال مختلف میپرسد.

· قبل از پارس کردن کویری، سرور mysql کش را که فقط میتواند دستورات SELECT را ذخیره کند چک میکند.

· اگر یک کویری نتیجه اش وجود داشته باشد به سمت کاربر برگردانده می شود.

در شکل زیر میتوانید فرایند سرور mysql را مشاهده کنید.

حافظه نهان (query cache)

· اگر mysql نتیجه ی یک کویری را در کش پیدا کند، باید دسترسی ها (privileges) را نیز چک کند.

· این کار بدون پارس کردن کویری نیز امکان پذیر است. زیرا mysql اطلاعات جداول را در درون کویری های کش شده نگهداری میکند.

· اگر دسترسی ها مشکلی ایجاد نکنند، mysql نتیجه را بازیابی میکند و به سمت کاربر باز میگرداند.

· بنابراین کویری پارس، بهینه سازی و اجرا نمیشود.

· پایگاه داده mySql مقدار حافظه ی مورد نیاز برای query cache را درون متغیر 'query_cache_size' در هنگام شروع به کار سرور مشخص و مقداردهی میکند.

· اگر بخواهیم این مقدار را تغیر دهیم، mysql بلافاصله تمامی کویری های کش شده را حذف میکند و مقدار حافظه را دوباره تخصیص میدهد.

· این فرایند ممکن است مقداری طول بکشد زیرا mysql کویری ها را تک به تک پاک میکند و نه یکجا، که این کار ممکن است مدتی سرور را مختل کند.

فرآیند بهینه سازی پرس و جو (The Query Optimization Process)

· پایگاه داده Mysql یک sql query را به یک برنامه اجرایی (execution plan) تبدیل میکند تا به موتور اجرایی پرس و جو (query exection engine) تحویل دهد.

· این فرایند یکسری بخش های مختلف دارد: نظیر پارس کردن(parsing)، پیش پردازش کردن (preprocessing) و بهینه سازی.

· خطا ها (error)، میتوانند در هر کدام از این مراحل رخ دهند. (نظیر خطاهای سینتکسی)

در شکل زیر میتوانید این فرایند و زیربخش های آن را مشاهده نمایید.

تجزیه کننده و پیش پردازنده (The parser and the preprocessor)

· پارسر sql syntax را با استفاده از چک کردن تمامی کاراکتر های sql query چک میکند و برای هر کویری یک SQL_ID تولید میکند.

· پارسر کویری را به یکسری توکن (token) میشکند و یک درخت پرس و جو (pars tree) را از آن ها میسازد.

· پارسر از گرامر اس‌کیو‌ال mysql استفاده میکند تا کویری را تفسیر و ارزیابی کند.

· برای مثال، مطمعن میشود تا توکن ها معتبر هستند و در یک ترتیب صحیح قرار گرفته اند ، و همچنین مشکلاتی نظیر quoted string ها را چک میکند تا مطمعن شود تا به درستی بسته شده اند.

· سپس پیش پردازنده درخت پرس و جوی نهایی را چک میکند و یکسری اطلاعات اضافی به آن اضافه میکند که پارسر از انجام آن ناتوان است.

· برای مثال، وجود داشتن جداول و ستون ها را چک میکند تا در ادامه خطایی رخ ندهد.

· سپس دسترسی ها را چک میکند. این کار به سرعت انجام میگیرد مگر این که شما دسترسی های زیادی را ایجاد کرده باشید.

بهینه ساز پرس و جو (query optimizer)

· درخت پرس و جو هم اکنون معتبر (valid) و آماده است تا تبدیل به یک برنامه اجرایی پرس و جو (query execution plan) شود.

· یک پرس و جو ممکن است از طریق روش های مختلفی اجرا شود و در نهایت نتیجه ی یکسانی به دست دهد. کار بهینه ساز این این است که بهترین روش را انتخاب کند.

· پایگاه داده MySql از یک بهینه ساز مبتنی بر هزینه استفاده میکند. بدین معنی که سعی میکند تا هزینه ی اجرای برنامه های اجرایی (execution plan) را تخمین بزند و ارزان ترین آن ها را انتخاب کند.

ما می توانیم هزینه ی اجرای یک دستور را از طریق اجرای فرمان SHOW STATUS LIKE 'Last_query_cost' مشاهده کنیم.

· همان طور که در تصویر بالا هم مشاهده میشود، هزینه ی دستور قبل برگردانده شده است. مقدار آن بدین معنی است که 10 صفحه داده (data page) تصادفی (random) طول میکشد تا دستور اجرا شود.

· یک صفحه (Page) شامل رکورد ها و همچنین header و trailer ها نیز می باشد.

· موتور ذخیره سازی InnoDB تمام رکورد ها را درون یک واحد با اندازه ثابت که معمولاً "یک صفحه" نامیده می شود ، ذخیره می کند (گرچه InnoDB گاهی اوقات آن را "بلوک" نیز می نامند). در حال حاضر تمام صفحات به یک اندازه ، 16KB هستند.

· بهینه ساز، تاثیرات ناشی از کش کردن را داخل تخمین های خود در نظر نمیگیرد و هر عملیات خواندن (read) را عملیات دیسک (disk I/O) در نظر میگیرد.

· بهینه ساز همیشه نمی تواند هر برنامه اجرایی ممکن را تخمین بزند ، بنابراین ممکن است یک برنامه بهینه را از دست دهد.

برنامه اجرایی (The execution plan)

· پایگاه داده MySQL مانند دیگر محصولات بانک اطلاعاتی ، برای اجرای یک پرس و جو کد بایت ایجاد نمی کند.

· در عوض، برنامه اجرایی پرس و جو (query execution plan) در واقع یک درختی از دستور العمل ها (instruction) است که موتور اجرا کننده پرس و جو آن ها را اجرا میکند تا در نهایت به جواب برسد.

· در تصویر زیر دو نمونه plan مختلف را مشاهده میکنیم.

موتورهای ذخیره سازی MySQL (MySQL’s Storage Engines)

· پایگاه داده MySQL، هر پایگاه داده (که به عنوان یک Schema نیز خوانده می شود) را به عنوان زیرمجموعه دایرکتوری داده های خود در سیستم فایلهای زیر ذخیره می کند.

· وقتی که یک جدول ایجاد می کنیم ، MySQL تعریف جدول را در یک پرونده .frm با همان نام جدول ذخیره می کند.

· بنابراین ، هنگامی که شما یک جدول با نام MyTable ایجاد می کنید ، MySQL تعریف جدول را در MyTable.frm ذخیره می کند.

· از آنجا که MySQL از سیستم فایل استفاده می کند تا نام بانک اطلاعاتی و تعاریف جدول را ذخیره کند ، کوچک و بزرگ بودن حروف مهم است.

· برای نمایش اطلاعات در مورد جداول می توانید از دستور SHOW TABLE STATUS استفاده کنید.

بازگشت نتایج به کاربر (Returning Results to the Client)

· آخرین مرحله برای اجرای یک پرس و جو پاسخ به کاربر است. حتی زمانی که پرس و جو ها قرار نیست که نتیجه را برگردانند، پاسخی را به سمت کاربر بر میگردانند. مانند این که "چند رکورد تاثیر یافتند".

· اگر پرس و جو قابل کش کردن باشد ، MySQL نیز نتایج را در این مرحله در حافظه نهان (cache) قرار می دهد.

· کش کردن نیز مزایای زیادی دارد.

همزمانی (Concurrency)

هر زمانی که بیش از یک پرس و جو نیاز به تغییر داده ی یک سان را در یک زمان داشته باشد ، مشکل کنترل همزمانی پیش می آید.

راهبردهای قفل (lock strategies)

· قفل سطح جدول

· قفل سطح ردیف

قفل جدولی (Table locks)

· اساسی ترین و پایه ای ترین استراتژی قفل کردن موجود در mysql است که کمترین سربار (overhead) را نیز به همراه دارد.

· کل جدول را قفل میکند.

· زمانی که یک کاربر بخواهد بر روی یک جدول بنویسد، قفل جدول را در اختیار میگیرد.

قفل ردیفی (Row Locks)

· استراتژی قفلی است که بالاترین همزمانی (concurrency) را به ارمغان میاورد ولی بیشترین سربار را نیز به دنبال دارد.

· این استراتژی در موتور های ذخیره سازی InnoDB و XtraDB موجود است.

· این استراتژی در سطح موتور های ذخیره سازی پیاده سازی می شود و نه در سطح سرور.

· سرور کاملا بی اطلاع از قفل های پیاده سازی شده در storage engine هاست.

تراکنش (Transaction)

یک تراکنش (transaction) گروهی از پرس و جو های اس‌کیو‌ال است که به صورت خودکار به عنوان یک واحد کار در نظر گرفته می شوند.

پشتیبانی سیستم های مدیریت پایگاه داده ها از transaction ها کافی نیست و پایگاه داده ها باید تست ACID را با موفقیت پشت سر بگذارند.

1. یکپارچگی (Atomicity) : به این معنی است که یا تمام دستورات یک تراکنش انجام میشود یا هیچکدام از دستورات اجرا نمیشوند. این خاصیت به همه یا هیج موسوم است. ( مثلا تراکنش انتقال مبلغی از یک حساب به حساب دیگر)

1. همخوانی (Consistency) : یعنی هر تراکنش اگر به تنهایی اجرا شود بانک اطلاعاتی را از حالتی صحیح به حالتی صحیح دیگیر منتقل میکند

2. انزوا (Isolation) : یعنی اثر تراکنش های همروند روی یکدیگر چنان باشد که ظاهرا هر کدام به طور مجزا و در انزوا انجام شود.

3. پایایی (Durability) : به این معنی است که که اثر تراکنش هایی که به مرحله انجام (Commit) میرسند ماندنی است و به طور تصادفی از بین نمیرود.

یک سرور پایگاه داده که از تراکنش های ACID پشتیبانی میکند، نیاز به قدرت پردازنده و حافظه و رم بیشتری نسبت به پایگاه داده ای که از ACID پشتیبانی نمیکند، دارد.

به همین دلیل است که معماری mysql یعنی پشتیبانی از انواع موتور ذخیره سازی به کار ما می آید.

ما میتوانیم تصمیم بگیریم که از چه نوع موتور ذخیره سازی استفاده کنیم.

ممکن است در شرایطی نیاز به پشتیبانی از تراکنش های ACID نداشته باشیم و از موتور های ذخیره سازی not-transactional استفاده کنیم تا کارایی (performance) بیشتری به دست آوریم.

سطح های ایزوله سازی (Isolation Levels)

استاندارد SQL چهار سطح انزوا را تعریف می کند ، با قوانین خاص که تغییرات در داخل و خارج از تراکنش قابل مشاهده نیستند. سطح انزوای پایین به طور معمول امکان همزمانی بالاتری را به همراه دارد و سربار کمتری نیز دارد.

ما در پیاده سازی پایگاه های داده از 3 مشکل رنج میبریم.

1. Dirty read

2. Non-repeatable Reads

3. Phantom reads

برای حل کردن چالش های قبل نیاز به پیاده سازی سطوح مختلف ایزوله سازی داریم که در ادامه به آن میپردازیم منتها در ابتدا باید با این 3 چالش آشنا شویم.

فرض کنید تراکنشی باعث تغییر اطلاعات در بدنه خود شده است ولی هنوز آن تراکنش Commit نشده است. اگر شرایط به گونه ای باشد که با استفاده از دستور Select داده های تغییر یافته معلق را که هنوز تایید نشده است را استخراج نماییم و آن ها را مشاهده کنیم اصطلاحا به آن Dirty Read گفته می شود.

Non-repeatable Reads

این خطا زمانی رخ میدهد که ما در یک تراکنش از یک موجودیت بیش از یکبار SELECT کنیم و هر بار پاسخ های متفاوتی دریافت کنیم.

Phantom reads

اگر حین انجام Select داخل یک تراکنش، اطلاعات جدیدی در دیتابیس درج شود و این درج اطلاعات روی نتیجه Select ما تاثیر بگذارد (سطرهای جدید به نتیجه Select اضافه شود) اصطلاحا گفته می شود که Phantom Read اتفاق افتاده است.

حال برای این که بر مشکلات گفته شده چیره شویم، 4 راه حل در پیش رو داریم که عبارتند از:

1. READ UNCOMMITTED

2. READ COMMITTED

3. REPEATABLE READ

4. SERIALIZABLE

که در ادامه به تشریح این موارد میپردازیم.

Read UnCommitted

این سطح ارزیابی به نوعی پایین ترین سطح ارزیابی می باشد به طوری که هیچ قفلی بر روی رکوردها اعمال نمی شود. مثلا اگر اطلاعاتی را داخل یک تراکنش به روزرسانی می نمایید یک تراکنش دیگر می تواند این اطلاعات را تغییر دهد. یا اگر در حال اجرای تراکنش، یک دستور Select بنویسید اطلاعاتی را دریافت کنید که هنوز تایید و نهایی نشده است.

با این اوصاف می توان گفت اطلاعاتی که در این Selectها به دست می آید به احتمال زیاد نامعتبر خواهد بود. همان Dirty Read در اینجا اتفاق خواهد افتاد.

Read Committed

این سطح ارزیابی سطح پیش فرض اغلب سیستم های مدیریت پایگاه داده می باشد (ولی سطح پیش mysql نیست). در این حالت اطلاعاتی که داخل یک تراکنش باشد تا لحظه اتمام آن تراکنش به حالت قفل در خواهد آمد. یعنی نه می توان آن ها را Select کرد و نه می توان آن ها را تغییر داد یا حذف کرد. چنانچه دستور SELECT شما همزمان با اجرای تراکنش باشد اجرای دستور SELECT منتظر اتمام تراکنش خواهد ماند. با یک مثال بهتر متوجه خواهید شد.

Reapeatable Read

این سطح ارزیابی شبیه Read Committed می باشد ولی با این تفاوت که Reapeatable Read این تضمین را به ما می دهد که تمامی دستورات تغییر اطلاعاتی، که شما آن ها را Select کرده اید منتظر خواهد ماند تا Select شما به اتمام برسد و بدین معنی است که هر چندبار که از یک موجودیت SELECT کنیم هر بار نتایج یکسانی دریافت خواهیم کرد. (این سطح، سطح پیش فرض mysql است)

Serializable

این سطح ارزیابی شبیه Reapeatable Read می باشد منتها Serializable یک تضمین اضافی را نیز به ما می دهد که اجازه درج رکورد جدید در اطلاعات مربوطه را تا اتمام تراکنش نخواهد داد. بنابر این جلوی Phantom Read نیز گرفته خواهد شد.

در جدول زیر میتوانیم این 4 راه حل گفته شده را با یکدیگر مقایسه کنیم.

تراکنش ها در MySQL

· پایگاه داده MySQL دو موتور ذخیره سازی که از تراکنش ها پشتیبانی میکنند را برای ما فراهم کرده است: InnoDB و NDB Cluster

· پایگاه داده Mysql به صورت پیشفرض در حالت AUTOCOMMIT عمل میکند. بدین معنی که تمامی پرس و جو ها را به عنوان یک تراکنش جدا از هم انجام میدهد.

· ما میتوانیم این قابلیت را فعال و یا غیرفعال کنیم.

موتور های ذخیره سازی (Storage Engines)

همان طور که در ابتدای گزارش هم اشاره شد، موتور های ذخیره سازی مسئول ذخیره و بازیابی داده در mysql هستند.

در اینجا به تشریح دو موتور ذخیره سازی رایج mysql میپردازیم.

موتور Innodb

از نسخه 5.5 به بعد به عنوان موتور پیشفرض mysql انتخاب شد.
پیچیدگی بیشتری نسبت به myIASM دارد.
بر خلاف MyISAM از Transactions به خوبی پشتیبانی می کند.
از کلید خارجی برای ارتباط میان جداول پشتیبانی میکند.
قابلیت crash-recovery دارد یعنی بعد از اینکه crash اتفاق می افتد , جداول InnoDB خیلی سریع و به صورت خودکار به آخرین وضعیت پایداری که قبلا داشتند بر می گردند .
حافظه بیشتری نسبت به MyISAM مصرف میکند و از نظر فیزیکی فایل های حجیم تری دارد. (بنابراین اگر محدودیت در منابع وجود داشته باشد استفاده از این موتور منطقی نیست.)
از Row-level Lock پشتیبانی میکند و به همین دلیل در سطح رکورد از سرعت بالایی جهت اجرای query های مربوط به delete/update/insert برخوردار می‌باشد.
از rollback و commit پشتیبانی میکند.
تشخیص بن‌بست (Deadlock Detection) : بن بست ها بیشتر در transactions ها اتفاق می افتد که دو یا چند transaction منابعی را برای خود نگه داشتند و رها نکرده اند. برای رفع این بن بست MYSQL یکی از transaction ها را با استفاده از الگوریتم خود انتخاب و آن را rollback می کند . با این کار بقیه transaction ها می توانند به کار خود ادامه دهند.
کارایی (performance) پایین تر نسبت به MyISAM : جداول MyISAM معمولا نسبت به InnoDB سریع تر هستند زیرا خیلی از موارد مانند کلید خارجی و Transactions ها را چک نمیکند.
پشتیبانی از MVCC
سطح ایزوله سازی آن به صورت پیشفرض repetable read است. و استراتژی هایی برای جلوگیری از phantom read دارد.

موتور MyISAM

سه مشخصه ی مهم دارد: