آموزش mysql
1401-04-06
618
0

بهینه سازی دیتابیس mysql

زبان پرس و جو ساختاریافته (SQL) یک زبان برنامه نویسی است که برای ذخیره، دستکاری و بازیابی داده ها از پایگاه داده استفاده می شود. این برنامه در بسیاری از سیستم های پایگاه داده رابطه ای از جمله MySQL، Postgres، Oracle، SQL Server و غیره کاربرد پیدا کرده است.
با استفاده از دستورات SQL، توسعه دهندگان به راحتی عملیات های مختلف پایگاه داده عملکردی مانند ایجاد، به روز رسانی و حذف داده ها را انجام می دهند.
با افزایش حجم داده‌ها و پیچیده‌تر شدن فناوری، بهینه‌سازی پایگاه‌های داده MySQL برای ارائه تجربه کاربر نهایی و کاهش هزینه‌های زیرساخت اهمیت بیشتری پیدا می‌کند. ابزار تنظیم عملکرد MySQL می تواند به متخصصان پایگاه داده کمک کند تا به سرعت گلوگاه ها را شناسایی کنند، عملیات ناکافی را از طریق بررسی طرح های اجرای پرس و جو مورد هدف قرار دهند و هر گونه بازی حدس زدن را حذف کنند.

1. Query Cache
از امکانات موجود در دیتابیس MySQL که سرعت دریافت اطلاعات را افزایش می ‌دهد Query Cache است. کارکرد آن به این صورت است که نتیجه دستور Select در دیتابیس ذخیره می ‌شود و در صورت درخواست مجدد، بدون اجرای مجدد دستور، نتیجه به سرعت ارسال می ‌شود. مزیت آن این است که با عدم درگیری منابع و MySQL و عدم اجرای مجدد، سرعت بسیار افزایش می ‌یابد.
همچنین به این دلیل که Query Cache از RAM به جای هارد استفاده می‌ کند و سرعت خواندن اطلاعات در آن بسیار بالاتر از هارد است، سرویس MySQL سرعت بسیار بالایی خواهد داشت.
چند متغیر برای تنظیم Query Cache در زیر لیست می شود.

query_cache_limit: این متغیر، مشخص می‌ کند که فضای مورد نظر برای نتیجه یک کوئری چقدر است. میزان پیش فرض تعیین شده برای آن، یک مگابایت است.
query_cache_min_res_unit: در دیتابیس MySQL اطلاعات به جای ذخیره شدن در یک فضای بزرگ، در بلاک ‌های کوچک ذخیره می ‌شوند. مقدار این بلاک ‌ها با این متغیر مشخص می ‌شود و مقدار پیش فرض آن 4 کیلوبایت است.
query_cache_size: با استفاده از این متغیر قادر خواهید بود که کل فضای مربوط به Query Cache بر روی دیتابیس را کنترل کنید. اگر مقدار آن را روی صفر قرار دهید این قابلیت غیر فعال خواهد شد. مقداری که به صورت پیش فرض برای آن تعیین شده است 16 مگابایت است و حداقل فضایی که Query Cache برای فعالیت به آن نیاز دارد و در نتیجه حداقل مقدار این متغیر برای فعالیت Query Cache، 40 کیلو بایت است.
query_cache_type: این متغیر نوع کوئری ‌هایی که باید Cache شود را تشخیص می ‌دهد. اگر مقدار آن روی 1 قرار داده شود، تمام کوئری‌ ها به جز آن ‌هایی که جز SELECT SQL NO CACHE باشند، Cache می ‌شوند. اگر روی 2 قرار بگیرد فقط کوئری ‌های این دسته Cache می‌شوند و در صورتی که مقدار آن صفر باشد، هیچ کوئری cache نمی‌شود.
query_cache_wlock_invalidate: این متغیر که در حالت پیش فرض روی OFF قرار دارد، مشخص می ‌کند که کوئری‌ های قفل شده cache می‌ شوند یا خیر.

2. بهینه سازی عملکرد MYSQL با استفاده از MYSQLTuner

اسکریپت MSQLTuner پیشنهاداتی را جهت بهبود عملکرد دیتابیس ارائه می‌دهد. MYSQLTuner یکی از ایمن‌ترین روش‌ها برای بهبود عملکرد دیتابیس است.

با رعایت موارد زیر، بهینه سازی عملکرد MYSQL با استفاده از MYSQLTuner را می‌توانید به‌سادگی انجام دهید.

Key_buffer

تغییر Key_buffer باعث می‌شود تا حافظه بیشتری به MYSQL اختصاص یابد و در نتیجه منجر به افزایش سرعت دیتابیس شما خواهد شد. اندازه key_buffer معمولاً نباید بیشتر از ۲۵ درصد حافظه سیستم هنگام استفاده از موتور MyISAM باشد. همچنین این اندازه برای InnoDB باید حداکثر ۷۰ درصد باشد. اگر اندازه بیش از حد بالا باشد، باعث هدر رفتن منابع خواهد شد.

مطابق با مستندات MYSQL، برای سرورهایی که ۲۵۶ مگابایت حافظه دارند، مقداری Key_buffer باید برابر با ۶۴M باشد. سرورهایی که دارای حافظه‌ی ۱۲۸ مگابایتی یا کمتر از آن هستند، این مقدار ۱۶M پیشنهاد می‌شود.

max_allowed_packet

این پارامتر به شما اجازه می دهد حداکثر اندازه یک بسته قابل انتقال را تنظیم کنید. بسته تنها یک حالت از SQL است. بسته در حقیقت یک سطر است که برای کاربر فرستاده می‌شود. چنان‌چه سرور MYSQL شما قرار است بسته‌های بزرگی را پردازش کند، بهتر است تا اندازه بزرگ‌ترین بسته خود را افزایش دهید. اگر این مقدار کوچک باشد با خطا مواجه خواهید شد.

thread_stack

این مقدار حاوی اندازه دسته برای هر موضوع است. MYSQL مقدار پیش‌فرض متغیر thread_stack را برای استفاده عادی در نظر می‌گیرد، با این حال اگر در این رابطه خطایی رخ دهد، این مقدار می‌تواند افزایش یابد.

thread_cache_size

چنان‌چه گزینه thread_cache_size خاموش است (یا مقدار آن صفر است)، هر اتصال جدیدی که برقرار شود نیازمند thread جدید خواهد بود. اگر اتصال برقرار نشود، این thread ازبین خواهد رفت. در غیر این صورت، thread ها در یک انبار ذخیره می‌شوند تا زمانی که لازم باشد برای اتصال استفاده شوند. به‌طور کلی این تنظیمات تاثیر کمی بر عملکرد دارند، مگر این‌که شما صدها اتصال در دقیقه دریافت کنید که در این صورت این مقدار باید افزایش پیدا کند و با افزایش این مقدار، اکثر اتصالات را می‌توان بر روی threadها بارگزاری کرد.

max_connections

این پارامتر حداکثر مقدار ارتباطات همزمان را تعیین می‌کند. بهتر است که حداکثر میزان ارتباطاتی که در گذشته داشته‌اید را در نظر بگیرید تا بتوانید این عدد را به‌درستی تنظیم کنید. توجه داشته باشید که این شامل حداکثر تعداد کاربران در وب سایت به‌صورت همزمان نمی‌شود؛ بلکه حداکثر تعداد کاربرانی را نشان می‌دهد که در یک زمان درخواست خود را ثبت می‌کنند.

table_cache

این مقدار باید بالاتر از مقدار open_tables شما باشد. برای تعیین این مقدار از دستور زیر استفاده کنید:

SHOW STATUS LIKE ‘open%’;

منبع:
https://sindad.com/blog/learning/optimize-mysql-performance-using-mysqltuner/

3. تعمیر دیتابیس MySQL

یک پایگاه داده:

mysqlcheck -o <db_schema_name>

تمامی پایگاه های داده:

mysqlcheck -o --all-databases

4.بهینه سازی پرس و جو با دستورالعمل های بهینه سازی پرس و جو MySQL

از استفاده توابع در گزاره ها خودداری کنید
اگر پایگاه داده دارای یک تابع از پیش تعریف شده در ستون باشد، از شاخص استفاده نمی کند.

SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'Copy

از ستون های غیر ضروری در SELECT اجتناب کنید
به جای استفاده از «SELECT *»، همیشه ستون‌هایی را در بند SELECT برای بهبود عملکرد MySQL مشخص کنید. از آنجا که ستون های غیر ضروری باعث بار اضافی بر روی پایگاه داده می شوند و عملکرد آن و همچنین کل فرآیند سیستماتیک را کاهش می دهند.

در صورت امکان به جای outer join از inner join استفاده کنید
از outer join فقط در مواقع ضروری استفاده کنید. استفاده بیهوده از آن نه تنها عملکرد پایگاه داده را محدود می کند، بلکه گزینه های بهینه سازی پرس و جو MySQL را نیز محدود می کند، که منجر به اجرای کندتر دستورات SQL می شود.

فقط در صورت لزوم از DISTINCT و UNION استفاده کنید
استفاده از عملگرهای UNION و DISTINCT بدون هدف اصلی باعث مرتب‌سازی ناخواسته و کاهش سرعت اجرای SQL می‌شود. به جای UNION، استفاده از UNION ALL کارایی بیشتری را در فرآیند به ارمغان می آورد و عملکرد MySQL را با دقت بیشتری بهبود می بخشد.

اگر انتظار دارید یک نتیجه مرتب شده دریافت کنید، عبارت ORDER BY در SQL اجباری است
کلمه کلیدی ORDER BY مجموعه نتایج را در ستون های بیانیه از پیش تعریف شده مرتب می کند. اگرچه این عبارت برای ادمین های پایگاه داده برای دریافت داده های مرتب شده مزیت دارد، اما تأثیر کمی بر عملکرد در اجرای SQL نیز ایجاد می کند. از آنجا که پرس و جو ابتدا باید داده ها را برای تولید مجموعه نتیجه نهایی مرتب کند، که باعث ایجاد یک عملیات کمی پیچیده در اجرای SQL می شود.

5. از MySQL به عنوان صف استفاده نکنید
صف ها می توانند عملکرد پایگاه داده شما را مستقیماً تحت تأثیر قرار دهند.

صف ها به دو دلیل عمده باعث ایجاد مشکل می شوند. آنها حجم کاری شما را serialize می کنند و از تکمیل موازی وظایف جلوگیری می کنند، و اغلب منجر به جدولی می شوند که شامل کار در حال انجام و همچنین داده های historical از کارهای قبلاً انجام شده است. این نه تنها تأخیر را به برنامه اضافه می کند، بلکه مانعی برای تنظیم عملکرد MySQL می کند.

6. چهار منبع اساسی را درک کنید

برای ایجاد توابع پایگاه داده به چهار منبع اساسی نیاز دارید. CPU، دیسک، حافظه و شبکه. اگر هر یک از اینها به درستی کار نکند، در نهایت روی سرور پایگاه داده تاثیر می گذارد و منجر به عملکرد ضعیف می شود.

ما اغلب دیده‌ایم که سازمان‌ها تمایل دارند سرورهایی با پردازنده‌های مرکزی سریع و دیسک‌های بزرگ انتخاب کنند، اما با حافظه ضعیف اشتباه می‌شوند که در نهایت عملکرد را از بین می‌برد.

وقتی نوبت به عیب یابی می رسد، همیشه عملکرد هر چهار منبع اساسی را بررسی کنید.

7. استفاده از Eager Loading
با Eager Loading می‌تونیم به جای N + 1 کوئری، فقط ۲ تا کوئری داشته باشیم.

8. بررسی و رفع MySQL slow query

MySQL slow query log جایی است که سرور پایگاه داده MySQL تمام پرس و جوهایی را که از آستانه معینی از زمان اجرا فراتر می روند، ثبت می کند. این اغلب می‌تواند نقطه شروع خوبی باشد برای اینکه ببینید کدام پرس‌وجوها کندتر هستند و چقدر کند هستند. MySQL روی سرور شما طوری پیکربندی شده است که تمام پرس و جوهایی که بیش از 0.1 ثانیه طول می کشد را ثبت کند.

9. انتخاب نوع داده مناسب برای فیلدهای دیتابیس

استراتژی اصلی برای انتخاب بهترین نوع داده این است که کوچکترین نوع داده ای را انتخاب کنید که با نوع داده ای که دارید مطابقت داشته باشد و تمام مقادیر امکان پذیر داده های شما را فراهم کند.
به عنوان مثال، برای انتخاب date می توانید از timestamp یا datetime استفاده کنید ولی راه بهتر برای وقتی دیتای زیادی در دیتابیس ذخیره استفاده از timestamp به صورت integer می باشد. یا اینکه برای فیلد موبایل 11 کاراکتر انتخاب کنید و بیش از 11 انتخاب نکنید.

10. ایندکس کردن فیلد های جداول برای سرعت بخشیدن به جستجوهاست

Index (ایندکس) یک مکانیزم برای سرعت بخشیدن به جستجوهاست.

مزیت ایندکس:

افزایش قابل توجه سرعت کوئری های select.

معایب ایندکس:

کاهش سرعت کوئری های insert و update.
اشغال بیشتر فضای حافظه.

انواع ایندکس:

ایندکس index (برای افزایش سرعت در جستجوی مقدار)
ایندکس unique (علاوه بر افزایش سرعت در جستجوی مقدار، یکتا بودن مقدار ستون رو هم چک می کند)
ایندکس full-text (برای افزایش سرعت در جستجوی مقدارهایی از جنس متون نسبتا طولانی)
ایندکس spatial (برای افزایش سرعت در جستجوی مقدارهایی از جنس موقعیت های مکانی)
ایندکس primary (کلید اصلی جدول هست و علاوه بر جلوگیری از ورود مقدار تکراری از ورود null ها هم جلوگیری میکند)

نویسنده :

مجید پورداود
مهندس نرم افزار و تحلیلگر ارشد سیستم های کامپیوتری تحت وب می باشم. از سال 1395 برنامه نویسی را شروع کردم و به زبان های php (فریم ورک laravel -codeigniter) و زبان جاوا اسکریپت (فریم ورک express.js-nest.js) تسلط دارم.

Query Cache

لینک کوتاه :

به اشتراک بگذارید :