توی پروژه های کوچیک و متوسط معمولا طراحی دیتابیس زیاد جدی گرفته نمیشه ولی توی پروژه های بزرگ با دیتابیس‌های حجیم دقت در جزئیات اهمیت زیادی داره و طراحی و نگهداری دیتابیس یک تخصص جداگانه محسوب میشه.

امروز قصد دارم اطلاعات و تجاربی رو که از سر و کله زدن با دیتابیس های حجیم بدست آوردم رو با شما سهیم بشم. بدست آوردن  بعضی از این تجربه ها  ممکنه واقعا گرون تموم بشه.

ایندکس‌ها

وقتی روی یک یا چند ستون ایندکس بذارید mysql یک فهرست از رکوردها که بر اساس اون یک یا چند ستون مرتب شده باشند تهیه و ذخیره میکنه که بعد از این فهرست میتونه برای جستجو و مرتب سازی سریع رکورد ها استفاده کنه.

استفاده از ایندکس ها برای دیتابیس های حجیم اجتناب ناپذیره. ولی یادتون باشه در استفاده از ایندکس ها  زیاده روی نکنید. ایندکس ها سرعت نوشتن و آپدیت کردن رو کند میکنند و حجم جداول رو هم افزایش میدن.
برای اینکه ببینید کجا لازمه از ایندکس استفاده کنید میتونید از تنظیم log-slow-queries استفاده کنید تا کوئری هایی که زمان اجراشون بیش از مقداری که در تنظیم long_query_time تعریف شده باشه لاگ بشه. از تنظیم log-queries-not-using-indexes هم میتونید استفاده کنید تا کوئری هایی که از ایندکس استفاده نمیکنند رو پیدا کنید (توضیحات بیشتر). بعد میتونید با استفاده از EXPLAIN کوئری‌های مربوطه رو بررسی کنید و در صورت لزوم روی فیلدهایی که لازمه ایندکس بذارید.
البته همه کندی ها مربوط به ایندکس نداشتن نیست گاهی اوقات هم میشه با تغییر در برنامه و بازنگری در کوئری ها بدون افزودن ایندکس  مشکلات رو رفع کرد.

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 قرار بدین. (توضیحات بیشتر)

ALTER

زمانی که بخواهید تغییری در ستون های جداول ایجاد کنید یا ایندکس حذف یا اضافه کنید و یا engine جدول رو تغییر بدید روش معمول استفاده از دستور ALTER هست که خیلی  زمانبر هست. یک جایگزین سریعتر برای Alter اینه که یک جدول خالی با تغییراتی که مد نظرتون هست بسازید و بعد با استفاده از INSERT INTO new_table SELECT * FROM old_table  اطلاعات رو به جدول جدید منتقل کنید. و یا در صورت کمبود حافظه ابتدا با استفاده از SELECT INTO OUTFILE خروجی تهیه کنید و سپس با LOAD DATA INFILE اطلاعات رو به جدول جدید منتقل کنید. این روش حدود 50 درصد از ALTER سریعتر هست. (توضیحات بیشتر)

تغییر در جداول حتی با استفاده از روشهای فوق هم عملیات زمانبری است و اگر بخواهید بدون Downtime روی جداول حجیم تغییرات ایجاد کنید تنها راه استفاده از Replication است که در ادامه مطلب در موردش توضیح میدم.

Replication

برای اینکه میرور لحظه‌ای از دیتابیس خود داشته باشید باید از replication استفاده کنید.
در replication ما یک سرور master داریم که با استفاده از قابلیتی به نام Binary log همه عملیات هایی که منجر به تغییرمیشه نظیر Insert و Update وDelete  و Alter و … رو لاگ میکنه. بعدا سرور یا سرورهای slave که حاوی میروری از دیتابیس های سرور master هستند از این binlog ها برای اعمال همان تغییرات روی میرورها و بروز نگهداشتن میرورها استفاده میکنند. در این حالت باید همه‌ی عملیات هایی که منجر به تغییر میشن فقط روی سرور master اجرا بشن و از سرورهای slave فقط برای خوندن استفاده بشه. در غیر اینصورت میرورها از سینک خارج میشن.

استفاده از replication و binary log کاربردهای زیادی داره از جمله:

• Load Balancing
  وقتی یک سرور جوابگوی پاسخ به همه حجم ترافیک نباشه باید از چند تا سرور استفاده کرد. خوب در مرحله اول میشه وبسرور و سرور mysql رو جدا کرد. اما وقتی یک سرور اختصاصی برای mysql هم جوابگو نبود باید از replication استفاده کرد تا بار پردازشی بین چند تا سرور توزیع بشه. با توجه به اینکه معمولا حجم عملیات نوشتن کمتر از حجم خواندن از دیتابیس هست میشه برنامه رو طوری طراحی کرد که از سرور master فقط برای نوشتن استفاده کنه و از سرورهای slave برای خوندن.
• Failover
  تصور کنید دیتابیس حجیمتون به خاطر یک اشکال سخت افزاری یا نرم افزاری از کار بیافته طوریکه مجبور باشید دیتابیس رو از روی بکاپ ها بازیابی کنید. این کار بسته به حجم دیتابیس ممکنه ساعتها طول بکشه و در مورد یک سرویس اینترنتی ممکنه ثانیه ها هم ارزش داشته باشند. اینجاست که از replication برای failover استفاده میشه. به این صورت که به محض مشکل دار شدن سرور master یکی از سرورهای slave تبدیل به master میشه. و از اون لحظه به بعد همه عملیات های نوشتن اون رو انجام میشه و سایر slave ها هم خودشون رو از اون رو بروز میکنند. بعد از رفع مشکل سرور master قدیمی به عنوان یک سرور slave وارد مدار میشه.
  
• Backup
  بعضی ها replication رو با بکاپ اشتباه می‌گیرند. برای روشن شدن موضوع یک مثال میزنم. فرض کنید روی سرور اصلی یک نفر اشتباها یک کوئری drop اجرا کنه و یک جدول مهم رو پاک کنه. در این صورت این کوئری روی سرورهای slave هم اجرا میشه و دیگه نمیشه از slave ها به عنوان پشتیبان استفاده کرد و اطلاعات رو برگردوند. بنابراین replication به تنهایی راه حلی برای بکاپ گیری نیست.
  اما ببینیم چطور میشه از replication برای بکاپ گیری استفاده کرد.
  در روش اول میشه از binary log به تنهایی (بدون استفاده از replication) برای تهیه بکاپ افزایشی (Incremental) استفاده کرد. به این صورت که یکبار از کل دیتابیس پشتیبان تهیه می‌کنید بعد از اون هر روز binary log های اون روز رو روی پشتیبان اجرا می‌کنید تا همیشه یک پشتیبان بروز داشته باشید بدون اینکه نیاز باشه هر روز از کل دیتابیس پشتیبان تهیه کنید. و در صورت بروز مشکل میتونید از روی binary log ها پشتیبان خود را تا لحظه قبل از وقوع مشکل بروز کنید. (کل این عملیات با استفاده از ابزار XtraBackup به راحتی قابل انجامه).
  روش دوم اینه که از یک سرور slave استفاده کنیم تا هیچ downtime و بار پردازشی روی سرور اصلی ایجاد نشه. در این حالت با خیال راحت میتونیم سرور slave رو stop کنیم و از دیتابیس بکاپ تهیه کنیم و مجددا سرور رو استارت کنیم. بعد از استارت شدن سرور مجددا شروع به بروز شدن از همان لحظه توقف میکند.
• ALTER بدون downtime
  یکی دیگه از کاربردهای replication تغییر در ساختار جداول بزرگ هست. همون طور که قبلا گفتم Alter دستور زمان بری هست. طوری که ممکنه برای یک جدول حجیم یک تغییر نظیر افزودن ایندکس روی یک فیلد یا اضافه کردن یک ستون جدید به جدول ساعت ها یا حتی روزها طول بکشه. توی این حالت برای اینکه downtime نداشته باشیم میتونیم از یک سرور slave استفاده کنیم به این صورت که ابتدا روی سرور slave دستور STOP SLAVE رو اجرا میکنیم تا عملیات بروز شدن از master متوقف بشه. بعد تغییرات رو روی جداول مورد نظر می‌دیم. بعد از اتمام تغییرات دستور START SLAVE رو اجرا میکنیم تا مجددا سرور ما از سرور اصلی بروز بشه. بعد از اینکه مطمئن شدیم سرور slave ما با سرور اصلی sync هست جای سرور slave و master رو عوض می‌کنیم. حالا میتونیم سرور master قدیمی(که الان slave هست) رو هم تغییرات رو روش اعمال کنیم و به عنوان slave ازش استفاده کنیم.
• کاربردهای خاص
  از replication برای کاربردهای خاص دیگه ای هم میشه استفاده کرد. مثلا فرض کنید ما به Full-text search روی یکی از فیلدهای یک جدول InnoDB احتیاج داشته باشیم. و نخواهیم به دلایلی که گفته شد از MyISAM استفاده کنیم. در این حالت میتونیم از replication استفاده کنیم. به این صورت که جدول ما روی سرور اصلی InnoDB هست ولی روی یکی از سرورهای slave که مخصوص full-text search ایجاد کردیم همان جدول را از نوع MyISAM داریم. حالا برنامه رو طوری طراحی میکنیم که برای کوئری هایی که احتیاج به full-text search هست از این slave استفاده کنه.

حرف آخر

در نهایت یادتون باشه خودتون رو محدود به mysql نکنید شاید اصلا mysql گزینه مناسبی برای کار شما نباشه. این روزها تب دیتابیس های NoSQL هم داغ هست یک سری به اونها هم بزنید. برای نمونه mongodb دیتابیس فوق العاده ای برای کارهای بزرگ هست. بنچمارک ها نشون میده سرعتش از mysql خیلی بیشتره و قابلیت های فوق‌العاده ای نظیر Map-Reduce و پشتیبانی خیلی خوب از Replication و Sharding داره. اگر فرصت کنم در یک مطلب جداگانه در موردش مفصل توضیح میدم.