بهینه‌سازی MySQL و MariaDB؛ از کوئری کند تا تنظیمات InnoDB

در بیشتر سایت‌ها و برنامه‌های کند، دیتابیس گلوگاه اصلی است: کوئری‌هایی بدون ایندکس، تنظیمات پیش‌فرضی که برای سرور امروزی کوچک‌اند و جدول‌هایی که سال‌ها بزرگ شده‌اند. در این راهنما بهینه‌سازی MySQL و MariaDB را به ترتیب اثرگذاری توضیح می‌دهیم: اندازه‌گیری، پیدا کردن کوئری‌های کند، ایندکس، تنظیمات InnoDB، اتصال‌ها و سخت‌افزار.

ابتدا اندازه بگیرید

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

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW PROCESSLIST;

ابزار mysqltuner پس از چند روز کار سرور، خلاصه‌ای از وضعیت و پیشنهادها ارائه می‌دهد. پیشنهادهایش را نقطه شروع بدانید، نه دستور قطعی. همزمان مصرف CPU، RAM و I/O دیسک سرور را هم بررسی کنید.

پیدا کردن کوئری‌های کند

Slow Query Log مهم‌ترین ابزار است:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

پس از جمع شدن داده، کوئری‌ها را بر اساس مجموع زمان مرتب کنید:

mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log | less

یک کوئری ۰٫۲ ثانیه‌ای که ده هزار بار در ساعت اجرا می‌شود، از یک کوئری ۵ ثانیه‌ای روزانه مهم‌تر است.

EXPLAIN و ایندکس‌ها

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

• type = ALL یعنی اسکن کامل جدول؛ معمولاً نشانه نبود ایندکس مناسب.
• ستون rows تعداد ردیف‌های بررسی‌شده را تخمین می‌زند؛ هر چه نزدیک‌تر به تعداد نتیجه، بهتر.
• Using filesort و Using temporary در جدول‌های بزرگ هزینه‌برند.

ایندکس ترکیبی مناسب برای کوئری بالا:

ALTER TABLE orders ADD INDEX idx_customer_status_created (customer_id, status, created_at);

اصول ایندکس‌گذاری:

• ستون‌های شرط برابری اول، سپس ستون‌های بازه یا مرتب‌سازی.
• ایندکس اضافه رایگان نیست: نوشتن را کند و فضا مصرف می‌کند. ایندکس‌های بلااستفاده را حذف کنید.
• توابع روی ستون (WHERE DATE(created_at) = ...) استفاده از ایندکس را مختل می‌کنند؛ شرط بازه‌ای بنویسید.
• LIKE '%word%' از ایندکس معمولی استفاده نمی‌کند؛ برای جستجوی متنی از FULLTEXT یا موتور جستجو استفاده کنید.
• SELECT * را با ستون‌های لازم جایگزین کنید.

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

تنظیمات InnoDB

مهم‌ترین تنظیمات در فایل /etc/mysql/mysql.conf.d/mysqld.cnf (یا مسیر معادل در MariaDB):

[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
max_connections = 200
tmp_table_size = 64M
max_heap_table_size = 64M

• innodb_buffer_pool_size: مهم‌ترین تنظیم. روی سرور اختصاصی دیتابیس حدود ۶۰ تا ۷۰ درصد RAM؛ روی سروری که وب‌سرور و PHP هم دارد، کمتر. در حالت ایده‌آل کل داده‌های پرکاربرد در آن جا شود.
• اندازه لاگ Redo: برای بار نوشتن زیاد بزرگ‌تر شود. در MySQL ۸.۰.۳۰ به بعد با innodb_redo_log_capacity تنظیم می‌شود.
• innodb_flush_log_at_trx_commit: مقدار ۱ امن‌ترین است. مقدار ۲ سرعت نوشتن را بالا می‌برد اما در قطعی برق ممکن است تراکنش‌های حدود یک ثانیه آخر از دست بروند.
• Query Cache: در MySQL ۸ حذف شده و در MariaDB بهتر است در بار همزمان زیاد خاموش بماند؛ به‌جای آن از کش در سطح برنامه (Redis) استفاده کنید.

پس از تغییر، سرویس را ری‌استارت کنید و اثر را اندازه بگیرید. هر بار یک تغییر بدهید.

اتصال‌ها و حافظه هر اتصال

هر اتصال بافرهای خودش را دارد (sort_buffer_size، join_buffer_size و…). افزایش بی‌رویه این بافرها یا max_connections می‌تواند در اوج ترافیک RAM را تمام کند و سرور را از کار بیندازد. به‌جای افزایش اتصال‌ها، تعداد پردازش‌های PHP-FPM یا Pool اتصال برنامه را متناسب تنظیم کنید. پیام «Too many connections» معمولاً نشانه کوئری‌های کند است که اتصال‌ها را نگه می‌دارند، نه کمبود اتصال. ارتباط آن با خطاهای وب‌سرور در رفع خطای ۵۰۲ و ۵۰۴.

نکات وردپرس و ووکامرس

• Autoload: حجم گزینه‌های autoload در wp_options را بررسی کنید؛ این داده در هر درخواست خوانده می‌شود.

  SELECT SUM(LENGTH(option_value))/1024/1024 AS mb FROM wp_options WHERE autoload IN ('yes','on','auto-on','auto');

• جدول wp_postmeta در فروشگاه‌های بزرگ عظیم می‌شود؛ HPOS ووکامرس سفارش‌ها را به جدول‌های بهینه منتقل می‌کند.
• Transientهای منقضی، بازبینی‌ها و لاگ افزونه‌ها را پاک کنید.
• Object Cache با Redis تعداد کوئری‌ها را به‌شدت کم می‌کند.

جزئیات در افزایش سرعت ووکامرس و افزایش سرعت وردپرس.

سخت‌افزار و سیستم‌عامل

• دیسک NVMe: بار تصادفی خواندن و نوشتن دیتابیس به IOPS بالا حساس است؛ تفاوت NVMe با HDD چند ده برابر است. مقایسه در NVMe، SSD یا HDD.
• RAM کافی تا داده‌های پرکاربرد در Buffer Pool جا شوند.
• Swap: سرور دیتابیس نباید دائماً Swap کند؛ vm.swappiness را پایین (مثلاً ۱ تا ۱۰) تنظیم کنید.
• جداسازی: با رشد بار، دیتابیس را روی سرور جداگانه ببرید و از طریق شبکه خصوصی به سرور برنامه وصل کنید.
• Replica خواندنی: برای بارهای سنگین خواندن و گزارش‌گیری.

پایش مداوم

معیارهای کلیدی را پیوسته پایش کنید: تعداد اتصال‌ها، کوئری در ثانیه، کوئری‌های کند، نرخ Hit بافر، تأخیر Replication و فضای دیسک. ابزارهایی مثل Zabbix قالب آماده MySQL دارند؛ راهنما در مانیتورینگ با Zabbix. پیش از هر تغییر بزرگ، بکاپ تست‌شده داشته باشید؛ روش‌ها در بکاپ و انتقال دیتابیس.

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