بهینه‌سازی دیتابیس برای سرعت سایت

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

در این مقاله یک راهنمای جامع برای بهینه‌سازی دیتابیس ارائه می‌دهیم: از مفاهیم پایه مثل ایندکس‌گذاری تا تکنیک‌های پیشرفته‌تر مثل تنظیم پارامترهای MySQL، شناسایی و بهینه‌سازی کوئری‌های کند، و ابزارهای خودکار برای نگهداری مستمر دیتابیس.

چرا دیتابیس کند می‌شود؟

قبل از هر کاری باید بدانیم مشکل از کجاست. دلایل اصلی کندی دیتابیس عبارتند از:

• کوئری‌های ناکارآمد: کوئری‌هایی که کل جدول را اسکن می‌کنند به جای استفاده از ایندکس
• نبود ایندکس مناسب: مهم‌ترین عامل کندی در اکثر سایت‌ها
• جداول بزرگ و شلوغ: ردیف‌های حذف‌شده فضا می‌گیرند، اجزای جدول پراکنده می‌شوند
• تنظیمات پیش‌فرض نامناسب: MySQL با تنظیماتی که برای سرورهای کم‌منبع طراحی شده نصب می‌شود
• اتصالات زیاد همزمان: مدیریت نادرست connection pool
• دیتای اضافی: revision های قدیمی، لاگ‌های انباشته، و transient های منقضی در وردپرس

ایندکس‌گذاری: مؤثرترین ابزار بهینه‌سازی

ایندکس چیست؟

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

کجا ایندکس بگذاریم؟

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

• شرط WHERE: WHERE user_id = 5 — ستون user_id نیاز به ایندکس دارد
• کلیدهای JOIN: JOIN orders ON orders.user_id = users.id — ستون user_id در orders
• مرتب‌سازی ORDER BY: اگر کوئری‌های زیادی بر اساس یک ستون مرتب می‌شوند
• گروه‌بندی GROUP BY: مشابه ORDER BY

ایندکس‌های ترکیبی (Composite Index)

گاهی یک ایندکس روی چند ستون ترکیبی مؤثرتر است. اگر کوئری‌های شما اغلب WHERE status = 'active' AND created_at > '2024-01-01' دارند، یک ایندکس ترکیبی روی (status, created_at) بسیار بهتر از دو ایندکس جداگانه است. ترتیب ستون‌ها در ایندکس ترکیبی مهم است؛ ستونی که بیشتر در WHERE استفاده می‌شود باید اول باشد.

ایندکس زیاد هم مضر است

هر ایندکس فضای دیسک می‌گیرد و عملیات INSERT، UPDATE و DELETE را کند می‌کند. برای هر سطر جدید، MySQL باید تمام ایندکس‌های آن جدول را به‌روز کند. بنابراین فقط ایندکس‌هایی بسازید که واقعاً استفاده می‌شوند.

تشخیص و بهینه‌سازی کوئری‌های کند

EXPLAIN: ابزار اصلی تشخیص مشکل

دستور EXPLAIN در MySQL نشان می‌دهد که یک کوئری چطور اجرا می‌شود. با نوشتن EXPLAIN قبل از کوئری SELECT، MySQL یک جدول تحلیل برمی‌گرداند که نشان می‌دهد:

• آیا از ایندکس استفاده شده (ستون key)
• چند ردیف بررسی شده (ستون rows)
• نوع اسکن (ستون type — مقدار ALL یعنی کل جدول اسکن شده که بد است)

Slow Query Log

MySQL می‌تواند کوئری‌هایی که بیش از یک آستانه زمانی طول کشیده‌اند را در یک فایل لاگ ثبت کند. با فعال کردن slow_query_log و تنظیم long_query_time (مثلاً ۱ ثانیه)، می‌توانید به تدریج مشکلات را شناسایی و برطرف کنید.

اشتباهات رایج در نوشتن کوئری

• SELECT * نکنید: فقط ستون‌هایی که واقعاً نیاز دارید را انتخاب کنید. کمتر داده یعنی کمتر ترافیک شبکه بین دیتابیس و اپلیکیشن.
• از LIMIT استفاده کنید: اگر ۱۰ ردیف می‌خواهید، LIMIT 10 اضافه کنید. بدون LIMIT، MySQL ممکن است میلیون‌ها ردیف برگرداند.
• Function روی ستون ایندکس‌شده اجتناب کنید: WHERE YEAR(created_at) = 2024 از ایندکس استفاده نمی‌کند. به جای آن از WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31' استفاده کنید.
• N+1 Query Problem: یکی از شایع‌ترین مشکلات در ORM‌ها. به جای اینکه در یک حلقه برای هر آیتم یک کوئری جداگانه بزنید، از JOIN یا eager loading استفاده کنید.

JOIN بهینه

INNER JOIN معمولاً سریع‌تر از subquery است. ولی مهم‌ترین نکته این است که ستون‌های JOIN باید ایندکس داشته باشند. یک JOIN روی ستون بدون ایندکس می‌تواند صدها برابر کندتر از همان کوئری با ایندکس باشد.

نگهداری و تمیز کردن دیتابیس

پاک کردن داده‌های اضافی

دیتابیس‌ها با گذر زمان پر می‌شوند از داده‌هایی که دیگر نیازی به آن‌ها نیست:

• لاگ‌های قدیمی که دیگر بررسی نمی‌شوند
• نسخه‌های قدیمی محتوا (revision در وردپرس)
• کامنت‌های اسپم
• Transient های منقضی در وردپرس
• Session های قدیمی

OPTIMIZE TABLE

این دستور جدول را defragment می‌کند. وقتی ردیف‌هایی حذف می‌شوند، فضای آن‌ها در دیسک خالی می‌ماند ولی آزاد نمی‌شود. OPTIMIZE TABLE این فضا را بازیابی می‌کند و جدول را فشرده می‌کند. برای جداول InnoDB، این دستور در عمل یک rebuild کامل است.

ANALYZE TABLE

این دستور آمار ایندکس‌ها را به‌روز می‌کند. MySQL از این آمار برای تصمیم‌گیری در مورد اینکه کدام ایندکس را استفاده کند استفاده می‌کند. بعد از وارد کردن داده زیاد یا تغییرات بزرگ در جدول، ANALYZE TABLE مفید است.

تنظیمات مهم MySQL/MariaDB

innodb_buffer_pool_size

این مهم‌ترین تنظیم برای موتور InnoDB است. Buffer pool حافظه‌ای است که MySQL برای cache کردن داده‌ها و ایندکس‌ها استفاده می‌کند. هرچه بزرگ‌تر باشد، MySQL کمتر به دیسک مراجعه می‌کند. بر روی یک سرور اختصاصی دیتابیس، معمولاً ۷۰ تا ۸۰ درصد کل RAM به این تنظیم اختصاص داده می‌شود.

max_connections

حداکثر تعداد اتصالات همزمان. اگر خیلی کم باشد، سرور خطای "Too many connections" می‌دهد. اگر خیلی زیاد باشد، هر اتصال حافظه مصرف می‌کند و سرور ممکن است به مشکل بخورد. مقدار مناسب به منابع سرور و نوع اپلیکیشن بستگی دارد.

innodb_log_file_size

اندازه فایل‌های redo log. برای workloadهایی با write زیاد، مقدار بزرگ‌تر می‌تواند performance را بهبود دهد.

tmp_table_size و max_heap_table_size

جداول موقت که در حافظه نگه داشته می‌شوند. اگر یک کوئری نیاز به جدول موقت بزرگ‌تری داشته باشد، MySQL از دیسک استفاده می‌کند که خیلی کندتر است.

کشینگ در سطح اپلیکیشن

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

• Redis: یک key-value store درحافظه که بسیار سریع است. نتایج کوئری‌های گران‌قیمت را برای مدتی در Redis ذخیره کنید.
• Memcached: مشابه Redis اما ساده‌تر. برای کش کردن ساده مناسب است.
• Object Cache در وردپرس: افزونه‌هایی مثل Redis Object Cache به وردپرس اجازه می‌دهند از Redis برای کش کردن نتایج کوئری‌های دیتابیس استفاده کند.

ابزارهای تشخیص و بهینه‌سازی

MySQLTuner

یک اسکریپت Perl که تنظیمات MySQL را آنالیز می‌کند، آمار استفاده را بررسی می‌کند و پیشنهادهای بهینه‌سازی ارائه می‌دهد. اجرای آن ساده است و خروجی خوانا و مفیدی دارد.

pt-query-digest از Percona Toolkit

یکی از قوی‌ترین ابزارها برای تحلیل Slow Query Log. کوئری‌ها را گروه‌بندی می‌کند، آمار زمان اجرا ارائه می‌دهد و مشکلات اصلی را اولویت‌بندی می‌کند.

phpMyAdmin

رابط گرافیکی محبوب برای مدیریت MySQL. امکانات Optimize table، Analyze table و بررسی ایندکس‌ها را دارد. برای اکثر کاربران هاست مشترک در دسترس است.

بهینه‌سازی دیتابیس وردپرس

وردپرس چند جدول دارد که با گذر زمان بسیار بزرگ می‌شوند:

• wp_posts: شامل revisionهای قدیمی می‌شود. یک پست ممکن است ۲۰ نسخه قدیمی داشته باشد که هیچ‌وقت استفاده نمی‌شود.
• wp_options: اگر مقدار autoload آن YES باشد، در هر بار لود صفحه خوانده می‌شود. باید autoload بودن تنظیمات غیرضروری را FALSE کرد.
• wp_postmeta: اکثر افزونه‌ها meta داده‌هایشان را اینجا ذخیره می‌کنند و این جدول می‌تواند بسیار بزرگ شود.

افزونه‌هایی مثل WP-Optimize، Advanced Database Cleaner یا WP Rocket دیتابیس وردپرس را تمیز می‌کنند:

• حذف revision های قدیمی (می‌توان سقف تعداد revision را هم تعیین کرد)
• حذف کامنت‌های spam و trash
• حذف transient های منقضی
• بهینه‌سازی جداول
• شناسایی ردیف‌های orphan در postmeta

سوالات متداول

چطور بفهمم کدام کوئری سایتم کند است؟

اگر از وردپرس استفاده می‌کنید، افزونه Query Monitor یکی از بهترین ابزارهاست. همه کوئری‌هایی که در لود هر صفحه اجرا می‌شوند را نشان می‌دهد با زمان اجرای دقیق هر کدام. برای سایت‌های غیر وردپرس، Slow Query Log MySQL را فعال کنید و از pt-query-digest برای تحلیل استفاده کنید.

آیا OPTIMIZE TABLE روی سایت live خطرناک است؟

برای جداول InnoDB، OPTIMIZE TABLE یک ALTER TABLE است که جدول را rebuild می‌کند. در MySQL 5.6+، InnoDB Online DDL این کار را بدون قفل کردن جدول انجام می‌دهد اما می‌تواند روی سرور بار ایجاد کند. بهتر است در ساعات کم‌ترافیک انجام شود. برای جداول بسیار بزرگ، از pt-online-schema-change استفاده کنید.

چند ایندکس برای یک جدول مناسب است؟

قانون ثابتی وجود ندارد. جداولی که بیشتر خوانده می‌شوند (read-heavy) می‌توانند ایندکس‌های بیشتری داشته باشند. جداولی که بیشتر نوشته می‌شوند (write-heavy) باید ایندکس‌های کمتری داشته باشند. با EXPLAIN بررسی کنید که کدام ایندکس‌ها واقعاً استفاده می‌شوند و کدام‌ها را می‌توان حذف کرد.

آیا تغییر به MariaDB از MySQL بهتر است؟

MariaDB یک fork از MySQL است که در بعضی workloadها کارایی بهتری دارد. برای اکثر سایت‌ها تفاوت قابل توجهی وجود ندارد. بهینه‌سازی ایندکس‌ها و کوئری‌ها تأثیر بسیار بیشتری از تغییر موتور دیتابیس دارد.

جمع‌بندی

بهینه‌سازی دیتابیس یک فرایند مستمر است، نه یک کار یک‌بار. ایندکس‌های درست و کوئری‌های بهینه مهم‌ترین قدم‌ها هستند. تمیز نگه داشتن دیتابیس از داده‌های اضافی، تنظیم صحیح MySQL، و استفاده از کش می‌توانند سرعت سایت را به طور چشمگیری بهبود دهند.

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