کوئری کند در MySQL؛ از slow query log تا ایندکس درست

کوئری کند در MySQL بیشتر وقت ها یعنی دیتابیس برای پیدا کردن چند ردیف مجبور شده کل جدول رو بخونه. دلیلش هم معمولاً اینه که ایندکس مناسبی نیست یا کوئری جوری نوشته شده که MySQL نمی تونه ازش استفاده کنه. رفعش سه قدم داره: با slow query log کوئری های کند رو پیدا می کنی، با EXPLAIN می فهمی چرا کندن و بعد ایندکس درست می سازی. آخرش هم دوباره EXPLAIN می گیری تا مطمئن بشی جواب داده.

نشانه ها: اول مطمئن شو مشکل از دیتابیسه

قبل از دست بردن توی دیتابیس، مطمئن شو مقصر همون جاست. نشانه های رایج:

• فقط صفحه های خاصی کندن، مثلاً دسته بندی محصولات، جستجو یا پنل سفارش ها. بقیه صفحه ها نرمالن.
• زمان انتظار برای اولین بایت (TTFB) بالاست، ولی عکس و CSS و JS سریع میان. معمولاً یعنی وب سرور زود جواب می ده و برنامه منتظر دیتابیسه.
• سایت با زیاد شدن داده کندتر شده. اوایل سریع بود، ولی بعد از چند ماه ثبت سفارش و کاربر کند شده.
• توی ساعت های اوج، مثلاً شب ها یا موقع کمپین فروش، کندی چند برابر می شه، چون چند کوئری سنگین همزمان اجرا می شن.
• مصرف CPU پروسه mysqld بالاست، بدون اینکه ترافیک عجیبی ببینی.

یه چک سریع: وقتی سایت کنده، این رو اجرا کن:

SHOW FULL PROCESSLIST;

اگه چند تا کوئری رو می بینی که ستون Time شون چند ثانیه یا بیشتره، یا یه کوئری مشابه بارها تکرار شده، سرنخ رو گرفتی. اگه لیست تقریباً خالیه و دیتابیس بیکاره، مشکل جای دیگه ست (کد برنامه، کش، شبکه) و بقیه این مطلب کمکت نمی کنه.

پنج علت رایج کوئری کند در MySQL

ترتیب این فهرست تجربیه، نه آماری. از بالا شروع کن، چون احتمال اینکه مشکلت توی همون چند مورد اول باشه بیشتره.

۱. اصلاً ایندکس نیست

رایج ترین حالت. ستونی که توی WHERE یا JOIN استفاده می کنی ایندکس نداره، پس MySQL ردیف به ردیف جدول رو می خونه (full table scan). با ۵۰۰ ردیف حس نمی کنی. با چند صد هزار ردیف، هر صفحه چند ثانیه طول می کشه. مثل اینه که توی یه دفترچه تلفن نامرتب دنبال یه اسم بگردی و مجبور باشی از صفحه اول ورق بزنی.

۲. ایندکس هست، ولی MySQL ازش استفاده نمی کنه

این یکی اعصاب خردکنه، چون فکر می کنی کار درسته. چند الگوی معمول:

• تابع روی ستون: WHERE DATE(created_at) = '2025-03-01' ایندکس created_at رو بی استفاده می کنه. به جاش بنویس created_at >= '2025-03-01' AND created_at < '2025-03-02'.
• LIKE با درصد اول: LIKE '%کفش%' از ایندکس معمولی استفاده نمی کنه، چون MySQL نمی دونه از کجا شروع کنه. LIKE 'کفش%' استفاده می کنه.
• نوع داده ناسازگار: شماره موبایل رو VARCHAR ذخیره کردی (که صفر اولش نپره) ولی توی کوئری بدون کوتیشن نوشتی WHERE mobile = 9121234567. MySQL ستون رو تبدیل می کنه و ایندکس رو کنار می ذاره. بنویس WHERE mobile = '09121234567'.
• ترتیب ستون ها توی ایندکس چندستونی: ایندکس (a, b) برای فیلتر روی b تنها معمولاً کمکی نمی کنه. اول باید a توی شرط باشه.

۳. ایندکس ناقصه و مرتب سازی گرون تموم می شه

فرض کن روی user_id ایندکس داری، ولی کوئری ORDER BY created_at DESC هم داره. MySQL ردیف ها رو پیدا می کنه و بعد خودش مرتب شون می کنه. توی EXPLAIN می بینی Using filesort. با ردیف کم مهم نیست، ولی برای کاربری با هزاران سفارش دردسر می شه.

صفحه بندی با OFFSET بزرگ هم همین مشکل رو داره. LIMIT 20 OFFSET 100000 یعنی MySQL صد هزار ردیف رو می خونه و دور می ریزه تا بیست تا ردیف برگردونه.

۴. کوئری ها سبکن، ولی تعدادشون زیاده

اگه برنامه ات از ORM استفاده می کنه، ممکنه برای هر محصول یه کوئری جدا بزنه تا دسته بندی اش رو بگیره. صفحه ای با ۴۰ محصول می شه ۴۱ کوئری. هر کدوم چند میلی ثانیه، ولی زیر ترافیک شب روی هم بار سنگینی می شن. اینجا ایندکس کمکی نمی کنه. باید کد رو اصلاح کنی و داده ها رو با JOIN یا یه کوئری IN (...) یک جا بگیری.

۵. منابع یا تنظیم سرور

اگه کوئری ها درستن و باز هم کندن، ممکنه innodb_buffer_pool_size خیلی کوچیک باشه و دیتابیس مدام از دیسک بخونه، یا سرور رو با چند تا سایت دیگه شریک شده باشی. این رو آخر چک کن، چون اول باید مطمئن بشی کوئری ها درست نوشته شدن.

قدم اول: slow query log رو روشن کن

بدون لاگ فقط حدس می زنی. slow query log هر کوئری رو که از یه حد مشخص طولانی تر بشه ثبت می کنه. بدون ری استارت، موقت فعالش کن:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

long_query_time = 1 یعنی هر کوئری بالای یک ثانیه ثبت بشه. برای شروع خوبه. اگه لاگ خیلی شلوغ شد، عدد رو بالاتر ببر. اگه چیزی ثبت نشد، پایین تر بیار، مثلاً 0.5.

این تنظیم ها بعد از ری استارت MySQL از بین می رن، و تغییر long_query_time با SET GLOBAL فقط روی اتصال های جدید اثر داره. برای ماندگار شدن، توی فایل تنظیمات (معمولاً زیر [mysqld] در my.cnf یا یکی از فایل های /etc/mysql/) اضافه کن:

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

مسیر فایل روی توزیع ها فرق می کنه. اگه مطمئن نیستی، با SHOW VARIABLES LIKE 'slow_query%'; ببین الان کجاست.

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

بعد از چند ساعت (ترجیحاً با ساعت اوج ترافیک) لاگ رو خلاصه کن:

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

-s t یعنی بر اساس مجموع زمان مرتب کن و -t 10 یعنی ده تای اول. -s c (بر اساس تعداد دفعات) هم گاهی به کار میاد. کوئری ۲ ثانیه ای که هزار بار اجرا شده، از یه کوئری ۱۰ ثانیه ای که روزی یه بار اجرا می شه بیشتر اذیتت می کنه.

خروجی EXPLAIN رو بخون

حالا یکی از کوئری های لاگ رو بردار. مثلاً این، که سفارش های اخیر یه کاربر رو نشون می ده:

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE user_id = 4821
ORDER BY created_at DESC
LIMIT 20;

فرض کن جدول orders چند صد هزار ردیف داره و ایندکسی فقط روی id هست. خروجی چیزی شبیه این می شه (ستون های مهمش):

• type: ALL
• possible_keys: NULL
• key: NULL
• rows: عددی نزدیک به کل ردیف های جدول
• Extra: Using where; Using filesort

چهار ستون رو اول نگاه کن:

type نشون می ده MySQL چطور به جدول می رسه. تقریباً از خوب به بد: const، eq_ref، ref، range، index و آخر ALL (خوندن کل جدول). دیدن ALL روی جدول بزرگ یعنی احتمالاً مشکل همین جاست.

key ایندکسیه که واقعاً انتخاب شده. اگه NULL باشه، هیچ ایندکسی استفاده نشده. اگه possible_keys پر باشه ولی key خالی، برگرد به بخش «ایندکس هست، ولی MySQL ازش استفاده نمی کنه».

rows تخمین MySQL از تعداد ردیف هایی که باید بخونه. عدد دقیق نیست، ولی برای مقایسه قبل و بعد عالیه. کوئری ای که ۲۰ ردیف برمی گردونه ولی rows اش صدها هزاره، الکی کار می کنه.

Extra جاییه که هشدارها میاد. Using filesort و Using temporary یعنی مرتب سازی یا جدول موقت. همیشه بد نیستن، ولی روی جدول بزرگ باید بررسی شون کنی.

توی MySQL 8.0.18 به بعد EXPLAIN ANALYZE هم داری که کوئری رو واقعاً اجرا می کنه و زمان واقعی هر مرحله رو نشون می ده. روی کوئری های سنگین، توی ساعت اوج اجراش نکن.

ایندکس درست بساز

برای کوئری بالا، شرط برابری روی user_id داریم و مرتب سازی روی created_at. ایندکس چندستونی با همین ترتیب لازمه:

ALTER TABLE orders
  ADD INDEX idx_user_created (user_id, created_at);

چرا این ترتیب؟ قاعده کلی اینه: ستون هایی که با = فیلتر می شن اول میان، بعد ستونی که برای ORDER BY یا شرط بازه ای (>، <، BETWEEN) استفاده می شه. این جوری MySQL اول با user_id مستقیم به ردیف های اون کاربر می رسه، و چون داخل ایندکس بر اساس created_at مرتبن، دیگه مرتب سازی جدا لازم نیست.

بعد از ساخت، همون EXPLAIN رو دوباره بگیر. باید این ها رو ببینی:

• type: ref
• key: idx_user_created
• rows خیلی کمتر از قبل
• Using filesort دیگه نیست

اگه key هنوز NULL بود، یا کوئری یکی از الگوهای بخش قبل رو داره، یا ایندکس رو روی ستون اشتباه ساختی.

قبل از اجرا روی جدول بزرگ

ساخت ایندکس روی جدولی با میلیون ها ردیف زمان می بره و فشار I/O میاره. چند احتیاط:

1. اول بکاپ بگیر. تغییر ساختار جدول روی داده واقعی بدون پشتیبان ریسکیه. اگه برنامه منظمی نداری، بکاپ خودکار سرور مجازی؛ چه چیزی، کجا و هر چند وقت یک بار؟ رو ببین.
2. وقت کم ترافیک رو انتخاب کن. مثلاً نیمه شب، نه ساعت های اوج خرید.
3. صریح بنویس که قفل نکنه:

ALTER TABLE orders
  ADD INDEX idx_user_created (user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

اگه MySQL نتونه این کار رو بدون قفل انجام بده، به جای اینکه جدول رو قفل کنه و سایت رو بخوابونه، خطا می ده. بهتره اول روی یه کپی تست کنی.

اشتباه های رایج

• ایندکس روی همه چی: هر ایندکس موقع INSERT و UPDATE باید به روز بشه و دیسک هم می گیره. فقط برای کوئری هایی بساز که توی لاگ اذیتت می کنن.
• ایندکس روی ستونی با مقدار کم: ستونی مثل status که فقط چند مقدار داره، تنهایی معمولاً فایده ای نداره. کنار ستون دیگه توی ایندکس چندستونی مفیده.
• ایندکس تکراری: اگه (user_id) و (user_id, created_at) هر دو هست، اولی معمولاً اضافه ست. با SHOW INDEX FROM orders; ببین چی داری.
• انتظار معجزه برای جستجوی متنی: LIKE '%کلمه%' با ایندکس معمولی درست نمی شه. اگه جستجوی متنی سنگین داری، FULLTEXT یا یه موتور جستجوی جدا رو بررسی کن.

ایندکس رو ساختی و هنوز کنده؟

گاهی ایندکس رو می سازی و کندی ادامه داره. این چند مورد رو چک کن.

فقط ستون های لازم رو بخون. SELECT * روی جدولی با ستون های TEXT و BLOB کلی داده جابه جا می کنه. فقط ستون هایی رو بخون که صفحه لازم داره.

صفحه بندی رو عوض کن. به جای OFFSET بزرگ، از آخرین id یا تاریخ صفحه قبل شروع کن: WHERE id < 98000 ORDER BY id DESC LIMIT 20. این جوری صفحه هزارم هم مثل صفحه اول سریع می مونه.

کش بذار، ولی بعد از اصلاح. کش روی کوئری کند فقط مشکل رو قایم می کنه و اولین کاربری که کش خالی رو می بینه همچنان صبر می کنه. اول کوئری رو درست کن، بعد کش بذار.

منابع سرور رو هم نگاه کن. اگه innodb_buffer_pool_size کمه یا RAM تنگه، حتی کوئری های خوب هم کند می شن. اگه نمی دونی از کجا شروع کنی، بهینه سازی سرعت هم کد و دیتابیس رو بررسی می کنه، هم سرور رو.

پیشگیری: بذار مشکل قبل از مشتری خودش رو نشون بده

چند عادت کوچیک جلوی بیشتر این ماجراها رو می گیره:

• slow query log رو همیشه روشن بذار، با آستانه منطقی مثل یک ثانیه. هزینه اش ناچیزه و اولین جایی می شه که مشکل رو می بینی.
• هفته ای یه بار لاگ رو نگاه کن. mysqldumpslow با ده خط خروجی، پنج دقیقه بیشتر وقت نمی بره.
• قبل از انتشار قابلیت جدید، EXPLAIN بگیر. مخصوصاً برای جستجو، گزارش و فیلتر. اگه می شه روی داده ای هم حجم با محیط واقعی تست کن، چون روی دیتابیس تست با ۱۰۰ ردیف همه چیز سریعه.
• قبل از کمپین های فروش، سایت رو زیر بار امتحان کن. کوئری ای که توی روز عادی رد می شد، شب کمپین می تونه سایت رو بخوابونه. مانیتورینگ هم کمک می کنه کندی رو زودتر از مشتری ها ببینی (مانیتورینگ و پایداری).
• ایندکس های استفاده نشده رو هرس کن. هر چند ماه یه بار مرورشون کن.

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

long_query_time رو روی چه عددی بذارم؟

برای شروع یک ثانیه معقوله. اگه سایتت شلوغه و لاگ زیادی پر می شه، اول روی ۲ بذار و بدترین کوئری ها رو درست کن، بعد کم کم پایین بیار. وقتی مشکل های بزرگ رفتن، می تونی روی 0.5 یا کمتر هم بذاری تا کوئری های متوسط رو هم ببینی.

روشن بودن slow query log سرور رو کند نمی کنه؟

با آستانه یک ثانیه یا بیشتر، سربارش معمولاً ناچیزه، چون فقط کوئری های کند نوشته می شن. ولی با long_query_time = 0 همه کوئری ها لاگ می شن و ممکنه هم دیسک پر بشه هم سرور کند بشه. این حالت فقط برای تحلیل موقت و کوتاهه.

روی هاست اشتراکی که به my.cnf دسترسی ندارم چه کنم؟

می تونی از phpMyAdmin یا هر ابزار مدیریتی که داری EXPLAIN اجرا کنی و ایندکس بسازی. برای slow query log از پشتیبانی هاستینگ بخواه فعالش کنه یا کوئری های کند رو برات بفرسته. اگه کسب و کارت رشد کرده و این محدودیت ها اذیتت می کنه، میزبانی وب و سرور مجازی ایران با دسترسی کامل تر انتخاب بهتریه.

یه قاعده ساده برای دفعه بعد

کوئری کند در MySQL رو حدسی درمان نکن. لاگ رو روشن کن، بدترین کوئری رو بردار، EXPLAIN بگیر و ببین type و key چی می گن. بعد یه ایندکس هدفمند بساز و دوباره بسنج. اگه rows پایین اومد و Using filesort رفت، درست رفتی. اگه نه، سراغ علت بعدی فهرست برو.