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

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

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

۲. بهینه سازی عملکرد 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%’;

۳.  تعمیر دیتابیس MySQL
یک پایگاه داده:mysqlcheck -o <db_schema_name>
تمامی پایگاه های داده:mysqlcheck -o –all-databases

۴.بهینه سازی پرس و جو با دستورالعمل های بهینه سازی پرس و جو 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 می شود.

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

۶. چهار منبع اساسی را درک کنیدبرای ایجاد توابع پایگاه داده به چهار منبع اساسی نیاز دارید. CPU، دیسک، حافظه و شبکه. اگر هر یک از اینها به درستی کار نکند، در نهایت روی سرور پایگاه داده تاثیر می گذارد و منجر به عملکرد ضعیف می شود. ما اغلب دیده‌ایم که سازمان‌ها تمایل دارند سرورهایی با پردازنده‌های مرکزی سریع و دیسک‌های بزرگ انتخاب کنند، اما با حافظه ضعیف اشتباه می‌شوند که در نهایت عملکرد را از بین می‌برد.وقتی نوبت به عیب یابی می رسد، همیشه عملکرد هر چهار منبع اساسی را بررسی کنید.
۷. استفاده از Eager Loading
با Eager Loading می‌تونیم به جای N + 1 کوئری، فقط ۲ تا کوئری داشته باشیم.
۸. بررسی و رفع MySQL slow query MySQL slow query log جایی است که سرور پایگاه داده MySQL تمام پرس و جوهایی را که از آستانه معینی از زمان اجرا فراتر می روند، ثبت می کند. این اغلب می‌تواند نقطه شروع خوبی باشد برای اینکه ببینید کدام پرس‌وجوها کندتر هستند و چقدر کند هستند. MySQL روی سرور شما طوری پیکربندی شده است که تمام پرس و جوهایی که بیش از ۰.۱ ثانیه طول می کشد را ثبت کند.
۹. انتخاب نوع داده مناسب برای فیلدهای دیتابیس

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

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

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

مزیت ایندکس:

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

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

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

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

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

بررسی حجم داده‌های ستون Autoload

اولین کاری که باید انجام دهید ، بررسی حجم داده های ستون autoload در حال مصرف در وب سایت وردپرسی شماست. برای این‌کار ، وارد phpMyAdmin شوید.

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

بعد از انجام این کار دستور زیر را در بخش ادیتور وارد کنید و روی کلید GO کلیک کنید.

SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';

در دستور زیر حجم autoload_size بر حسب کیلوبایت ، تعداد کوئری های autoload و ۱۰ دستور autoload اول دیتابیس به شما نمایش داده می‌شود.

SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
UNION
SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
UNION
(SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)

مرحله بعدی بهینه سازی ، مرتب‌ کردن پر مصرف ترین ها در داده های autoload شده می‌باشد. شما می‌توانید با دستور SQL زیر به سرعت لیست ۱۰ داده پرمصرف را  به دست آورید.

SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 10;

شما می‌توانید از دستور زیر برای مشاهده رکورد‌های transient خودکار بارگیری شده استفاده کنید :

SELECT * 
FROM `wp_options` 
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%'

آموزش تغییر زمان ذخیره خودکار نوشته در وردپرس

اما اگر دوست ندارید تا آخرین ذخیره تغییرات روی نوشته رو از دست بدین و از طرفی هم زحمت دوباره نوشتن را به دوش نکشید یک راه دیگه هم برای شما وجود داره که با استفاده از اون میتونید تا مدت زمان ذخیره خودکار نوشته را بیشتر کنید. در این صورت تعداد رونوشت‌هاتون تا حد قابل توجهی برای یک نوشته کمتر شده و دیتابیس شما حجیم‌تر از حالت قبل نخواهد شد. برای این کار هم کافیه تا وارد هاست خودتون شده و سپس در بخش File Manager ّاست و درست در مسیر public_html به دنبال فایلی با نام wp-config.php بگردید و کدهای زیر را در مکان مناسبی از این فایل قرار دهید.

define('AUTOSAVE_INTERVAL', 300 ); // secondsdefine('WP_POST_REVISIONS', false );

آموزش حذف رونوشت وردپرس و بهینه سازی دیتابیس

برای حذف رونوشت ها از وردپرس حالا که این آموزش را مشاهده می‌کنید شاید تعداد بسیار زیادی رونوشت در سایت خودتون داشته باشید که همین رونوشت‌ها علت کند بودن سایت شما باشند. بنابراین راهی هم وجود داره تا شما رونوشت‌های سایت خودتون را در هر چند ماه یک بار بسته به تعداد مطالبی که در هر ماه منتشر می‌کنید انجام بدین. با این کار میتونید هر از گاهی که حس کردید سایت شما کند شده و به درستی پاسخگو نیست میتونید رونوشت های سایت را حذف کرده و دیتابیس خودتون را بهینه کنید. برای این کار وارد هاست خود شده و سپس روی گزینه File Manager کلیک کنید. سپس بعد از اینکه وارد محیط مدیریت فایل‌ها در هاست شدید به مسیر /public_html/wp-content/themes/ شده و سپس وارد پوشه قالبی که از اون در سایت خودتون استفاده می‌کنید شده و به دنبال فایل functions.php بگردید. در نهایت روی فایل راست کلیک کرده و با انتخاب گزینه Edit وارد صفحه ویرایش این فایل شده و کدهای زیر را در انتهای فایل قرار بدین.

DELETE FROM wp_posts WHERE post_type = "revision";

۵