مرکز آموزش میهن وب هاست

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

راهکارهای بهینه سازی:

هر گاه مشاهده نمودید که حجم دیتابیس سایت به صورت نامعقول افزایش یافته یا مصرف منابع زیاد است، احتمالا مشکلی در عملکرد آن وجود دارد. بهینه سازی دیتابیس در بحث کنترل مصرف منابع عموما از سه جهت مورد بررسی قرار می گیرد:

1. افزایش امنیت صفحات داخلی وب سایت>> "قابل اجرا توسط وبمستران مبتدی و نیمه حرفه ای"

2. بهینه سازی ریکوئست های mysql>> "قابل اجرا توسط وبمستران حرفه ای"

3. بررسی دلیل ارجاعات زیاد >> "قابل اجرا توسط وبمستران مبتدی و نیمه حرفه ای"

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

1. افزایش امنیت صفحات داخلی وب سایت

برای بالا بردن امنیت دیتابیس وب سایت خود در برابر تهدیدات آنلاین توجه به موارد زیر الزامی است:

- فعال نمودن بروزرسانی خودکار برای افزونه‌ها و نرم‌افزارها: این مورد یکی از ساده‌ترین و موثرترین راهکارها است؛ چرا که نسخه‌های قدیمی معمولاً دارای آسیب‌پذیری‌های شناخته‌شده هستند.

- رمز عبور قوی و احراز هویت: در نظر گرفتن رمز عبور قوی و منحصر به‌فرد و همچنین استفاده از احراز هویت دو مرحله‌ای (2FA) می‌تواند از دسترسی غیرمجاز به پنل مدیریتی سایت جلوگیری کند.

- نصب ssl: گواهینامه‌ SSL نیز رمزنگاری ارتباط کاربران با سایت را تضمین می‌کند و نقش مثبتی در سئوی سایت دارد.

- افزایش امنیت صفحاتی که با کاربران تعامل زیادی دارند: صفحاتی مانند «تماس با ما»، «ارسال نظرات توسط کاربران»، «ثبت‌نام در سایت»، «ورود» و «ثبت‌نام در خبرنامه» به‌دلیل تعامل مستقیم با داده‌های ورودی، هدف خوبی برای حملاتی چون SQL Injection، Cross-Site Scripting (XSS)، Brute Force و Spam Injection هستند. برای جلوگیری از این تهدیدات، رعایت اصول امنیتی مانند استفاده از CAPTCHA، محدودسازی تعداد تلاش‌های ورود، اعتبار سنجی ورودی و پاکسازی (Input Sanitization) و تأیید اعتبار کاربران امری ضروری است. همچنین، استفاده از افزونه‌های معتبر امنیتی و بروزرسانی منظم هسته وردپرس و افزونه‌ها، نقش مهمی در مقاوم‌سازی این صفحات ایفا می‌کند.

2. بهینه سازی ریکوئست ها از طریق اجرای دستورات

بهینه‌سازی کوئری‌های SQL یکی از مهارت‌های ضروری برای توسعه‌دهندگان و مدیران پایگاه‌داده است. این فرآیند می‌تواند سرعت اجرا، مصرف منابع و تجربه‌ کاربران را به شدت بهبود دهد. در ادامه به ۶ تکنیک کاربردی بهینه‌سازی همراه با مثال عملی پرداخته شده است:

2.1. استفاده از ایندکس‌ها (Indexes)

ایندکس‌ها مانند فهرست کتاب عمل می‌ نمایند؛ به پایگاه داده کمک می‌کنند تا داده‌ها را سریع‌تر پیدا نماید.

مثال بدون ایندکس:

تصویر(1)

در جدول‌های بزرگ، این جستجو زمان‌بر است.

مثال با ایندکس:

تصویر(2)

2.2. استفاده از LIMIT و OFFSET

در بازیابی داده‌ها، همیشه نیازی به نمایش همه نتایج نیست. استفاده از LIMIT و OFFSET، حجم داده‌ بازگشتی را مدیریت می‌کند.

تصویر(3)

این دستور 10 سفارش بین ردیف‌های مشخص شده را نمایش می دهد.

2.3. استفاده از جدول‌های موقت (Temporary Tables)

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

تصویر(4)

حالا می‌توان بارها از temp_sales بدون اجرای مجدد کوئری استفاده کرد.

2.4. بهینه‌سازی نوع داده‌ها (Data Types)

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

داده نادرست:

داده درست:

تصویر(6)

2.5. عدم استفاده از * SELECT

* SELECT تمام ستون‌ها را باز می‌گرداند که معمولاً غیرضروری است و مصرف منابع را بالا می‌برد.

استفاده نادرست:

تصویر(7)

استفاده درست:

تصویر(8)

در این مثال دقیقاً فیلدهای مورد نیاز انتخاب می شوند.

2.6. استفاده بهینه از Subqueryها

Subqueries پیچیده ممکن است عملکرد را کاهش دهند. در بسیاری از موارد می‌توان آن‌ها را با JOIN جایگزین کرد.

غیر بهینه:

تصویر(9)

بهینه:

تصویر(10)

نکته 1: اجرای درست و اصولی این ۶ تکنیک، موجب بهینه‌سازی محسوس عملکرد پایگاه داده می شود. اما بررسی این موارد بر عهده افرادی است که دانش کامپیوتری لازم را دارند. از همین روی اگر در خصوص برنامه نویسی و مخصوصا کد نویسی اطلاعات و تجربه کافی ندارید، این پروسه را به یک برنامه نویس کاربلد بسپارید.

3. بررسی دلیل ارجاعات زیاد 

افزایش ارجاعات به دیتابیس در سایت‌های وردپرسی معمولاً نشانه‌ای از وجود کوئری‌های ناکارآمد یا افزونه‌های سنگین است. در ادامه عواملی که می توانند باعث افزایش ارجاعات به دیتابیس سایت شوند معرفی خواهند شد.

3.1. ترافیک زیاد ربات ها

ترافیک زیاد ربات‌ها (Bot Traffic) می‌تواند عملکرد سایت را به شدت تحت تأثیر قرار دهد، چرا که بسیاری از این ربات‌ها نه تنها مفید نیستند، بلکه سرعت بارگذاری سایت را کاهش می‌دهند. برای شناسایی این نوع ترافیک، پیشنهاد می شود از لاگ‌های موجود در هاست استفاده شود.

ابتدا وارد هاست سی پنلی خود شده و سپس روی گزینه "Visitors" کلیک نمایید تا ارجاعات اخیر را مشاهده کنید.

تصویر(11)

اگر مانند تصویر زیر در لیست ارجاعات، نام بات ها را مشاهده کردید (مانند OpenAI، GPTBot، AhrefsBot, SemrushBot ,spider و…) و این موارد زیاد هستند، نشان دهنده  آن است که ارجاعاتی ناخواسته از این botها به سایت شما انجام می شود.

تصویر(12)

برای دریافت اطلاعات و آمار دقیق تر طبق تصویر زیر روی "Awstats" کلیک نمایید.

تصویر(13)

در بخش "Robots/Spiders visitors" می توانید بات هایی که در سایت شما خزش کرده اند را مشاهده کنید.

تصویر(14)

برای جلوگیری از تأثیر منفی ربات‌ها، روش‌هایی مانند پیکربندی دقیق فایل robots.txt می توانند مفید باشند. در آموزش زیر می توانید نحوه تهیه یک فایل robots.txt مناسب را مطالعه نمایید:

آشنایی با روال ساخت فایل robots.txt بهینه

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

در مورد مسدود کردن ربات های هوش مصنوعی چه می دانید

نکته 2: با توجه به اینکه اکثر مدیران سایت به فایل robots.txt توجه لازم را ندارند یا آن را به‌صورت پیش‌فرض کانفیگ می‌کنند، سایت‌های آنان اغلب به‌دلیل مشکلی عدم بهینگی MYSQL، با ارجاعات زیاد و مصرف بالای منابع مواجه می‌شوند.

3.2. افزونه ها و ابزارهای نامعتبر

استفاده از افزونه‌ها و ابزارهای مطمئن و بروز در سایت‌های وردپرسی نقش مهمی در بهینه‌سازی عملکرد سرور و مصرف منابع دارد. از همین رو توصیه می‌شود تنها از افزونه‌هایی استفاده گردد که توسط توسعه‌دهندگان معتبر ارائه شده‌اند، تعداد نصب بالا دارند و بروزرسانی مداوم برای آنان منتشر می شود.

3.3. استفاده از افزونه Query Monitor برای ردیابی کوئری های مشکوک

افزونه Query Monitor یکی از ابزارهای کاربردی برای تحلیل مشکلات عملکردی سایت‌های وردپرسی است. با این ابزار می‌توانید افزونه های دارای مشکل و قالب هایی که کوئری های زیاد و کند را ایجاد می کنند، شناسایی نمایید.

برای آشنایی بیشتر با افزونه Query Monitor به آموزش های زیر مراجعه کنید:

اشکال زدایی و بهبود عملکرد سایت وردپرسی با افزونه Query Monitor

 و

کار با افزونه Query Monitor برای بررسی ریکوئست های mysql