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

مقدمه

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

MySQL یکی از سیستم‌های مدیریت پایگاه داده است که بسیاری از سایت‌ها، از جمله سایت‌های وردپرسی، برای ذخیره و دریافت اطلاعات از آن استفاده می‌کنند. اطلاعاتی مانند مشخصات کاربران، محصولات، سفارش‌ها، نوشته‌ها و تنظیمات سایت می‌توانند در این پایگاه داده ذخیره شوند.

برای دریافت این اطلاعات، سایت دستورهایی به دیتابیس ارسال می‌کند که به آن‌ها Query یا کوئری گفته می‌شود. اگر اجرای یک کوئری بیشتر از حد معمول طول بکشد، ممکن است باعث کند شدن بخشی از سایت یا افزایش مصرف منابع سرور شود.

اما چگونه می‌توان فهمید کدام کوئری‌ها کند هستند؟ آیا برای پیدا کردن آن‌ها باید تمام کدهای سایت را بررسی کنیم؟

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

چک لیست

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

۱. کوئری کند در MySQL چیست؟

کوئری کند دستوری است که اجرای آن بیشتر از زمان مورد انتظار طول می‌کشد. برای مثال، فرض کنید یک سایت برای نمایش فهرست محصولات، اطلاعات را از دیتابیس دریافت می‌کند.

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

البته زمان اجرای هر کوئری به نوع عملیات، حجم اطلاعات، وضعیت سرور و شرایط اجرای دستور بستگی دارد. بنابراین، هر کوئری که کمی طولانی‌تر اجرا می‌شود، لزوماً مشکل‌دار نیست.

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

۲. فعال کردن Slow Query Log در MySQL

یکی از بهترین روش‌های پیدا کردن کوئری‌های کند، استفاده از قابلیتی به نام Slow Query Log است.

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

برای شروع، وارد محیط خط فرمان MySQL شوید. سپس دستور زیر را اجرا کنید:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

اگر مقدار OFF نمایش داده شود، ثبت گزارش کوئری‌های کند غیرفعال است. اگر مقدار ON باشد، این قابلیت فعال است.

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

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

مقدار پیش‌فرض این متغیر معمولاً ۱۰ ثانیه است. یعنی کوئری‌هایی که بیشتر از این زمان اجرا شوند، در صورت فعال بودن ثبت گزارش، می‌توانند در Slow Query Log ذخیره شوند. این مقدار قابل تغییر است.

برای مثال، اگر بخواهیم کوئری‌هایی را که بیش از یک ثانیه طول می‌کشند بررسی کنیم، می‌توانیم با دسترسی مدیریتی مناسب دستورات زیر را اجرا کنیم:

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

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

نکته مهم این است که تغییر مقدار long_query_time در سطح عمومی معمولاً روی اتصال‌های جدید MySQL اعمال می‌شود. همچنین این تغییرات موقتی هستند و ممکن است پس از راه‌اندازی مجدد سرویس MySQL از بین بروند؛ برای تنظیم دائمی باید پیکربندی سرور بررسی شود.

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

۳. پیدا کردن مسیر فایل گزارش کوئری‌های کند

پس از فعال کردن Slow Query Log، باید مسیر فایل گزارش را پیدا کنیم.

برای این کار دستور زیر را اجرا کنید:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';

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

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

اگر مسیر فایل گزارش برای مثال /var/log/mysql/mysql-slow.log باشد، می‌توانیم با دستور زیر آخرین خطوط آن را مشاهده کنیم:

tail -n 100 /var/log/mysql/mysql-slow.log

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

در گزارش Slow Query Log معمولاً اطلاعاتی مانند زمان اجرای کوئری، زمان انتظار برای قفل‌ها، تعداد ردیف‌های بررسی‌شده و خود دستور SQL وجود دارد. با بررسی این اطلاعات می‌توان سرنخ‌هایی درباره علت کندی پیدا کرد.

۴. شناسایی مهم‌ترین کوئری‌ها با ابزار mysqldumpslow

اگر فایل گزارش کوئری‌های کند بسیار بزرگ باشد، خواندن تمام خطوط آن کار دشواری خواهد بود. برای حل این مشکل می‌توان از ابزار mysqldumpslow استفاده کرد.

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

برای مشاهده ۱۰ مورد مهم بر اساس زمان اجرای ثبت‌شده، می‌توان از دستور زیر استفاده کرد:

mysqldumpslow -s t -t 10 /path/to/mysql-slow.log

در این دستور، عبارت /path/to/mysql-slow.log باید با مسیر واقعی فایل گزارش جایگزین شود.

گزینه -s t گزارش را بر اساس زمان مرتب می‌کند و گزینه -t 10 تعداد نتایج نمایش‌داده‌شده را به ۱۰ مورد محدود می‌کند.

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

اگر ابزار mysqldumpslow در دسترس نباشد، ممکن است نصب MySQL یا بسته‌های مربوط به آن در سیستم متفاوت باشد. در این شرایط، مدیر سرور می‌تواند ابزار مناسب را بررسی کند.

۵. پیدا کردن کوئری‌های پرمصرف با Performance Schema

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

یکی از جدول‌های مهم این بخش، جدول زیر است:

performance_schema.events_statements_summary_by_digest

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

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

SELECT
    SCHEMA_NAME,
    LEFT(DIGEST_TEXT, 120) AS query_text,
    COUNT_STAR AS execution_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS avg_seconds,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

این دستور چند اطلاعات مهم را نمایش می‌دهد.

ستون SCHEMA_NAME نام دیتابیس را نشان می‌دهد. با استفاده از آن می‌توان متوجه شد کوئری مربوط به کدام دیتابیس است.

ستون query_text نمونه‌ای از ساختار دستور SQL را نمایش می‌دهد. در این گزارش، مقادیر مختلف یک کوئری مشابه معمولاً به یک شکل دسته‌بندی می‌شوند تا بررسی آن‌ها آسان‌تر باشد.

ستون execution_count تعداد دفعات ثبت‌شده برای اجرای آن نوع کوئری را نشان می‌دهد.

ستون total_seconds مجموع زمان اجرای ثبت‌شده را بر حسب ثانیه نمایش می‌دهد. این مقدار به‌تنهایی مشخص نمی‌کند هر بار اجرای کوئری چقدر طول کشیده است.

ستون avg_seconds میانگین زمان اجرای کوئری را نشان می‌دهد.

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

در مقابل، ستون rows_sent تعداد ردیف‌های بازگردانده‌شده را نمایش می‌دهد.

برای مثال، ممکن است کوئری‌ای فقط ۲۰ ردیف به سایت برگرداند، اما برای پیدا کردن این ۲۰ ردیف، هزاران ردیف از دیتابیس را بررسی کند. چنین وضعیتی می‌تواند نشانه‌ای باشد که باید ساختار کوئری یا ایندکس‌های جدول بررسی شوند.

توجه داشته باشید که اطلاعات Performance Schema به تنظیمات و وضعیت جمع‌آوری آمار وابسته هستند و لزوماً تمام کوئری‌های اجراشده از ابتدای فعالیت سرور را در بر نمی‌گیرند. برای بررسی نتایج نیز باید دسترسی لازم به این جدول را داشته باشید.

۶. بررسی علت کند بودن کوئری با EXPLAIN

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

یکی از ابزارهای کاربردی در این مرحله، دستور EXPLAIN است. این دستور اطلاعاتی درباره روش اجرای یک کوئری در اختیار ما قرار می‌دهد.

برای مثال، فرض کنید کوئری زیر اطلاعات کاربران را بر اساس نشانی ایمیل پیدا می‌کند:

SELECT *
FROM users
WHERE email = 'user@example.com';

می‌توانیم با افزودن EXPLAIN، نحوه اجرای آن را بررسی کنیم:

EXPLAIN
SELECT *
FROM users
WHERE email = 'user@example.com';

خروجی این دستور اطلاعاتی درباره جدول مورد استفاده، روش دسترسی به اطلاعات، تعداد تخمینی ردیف‌ها و ایندکس انتخاب‌شده ارائه می‌دهد.

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

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

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

در نسخه‌های سازگار MySQL، دستور EXPLAIN ANALYZE اطلاعات دقیق‌تری درباره اجرای واقعی کوئری ارائه می‌دهد؛ اما برخلاف EXPLAIN معمولی، کوئری را اجرا می‌کند. به همین دلیل، اجرای آن روی کوئری‌های سنگین یا در محیط عملیاتی باید با دقت انجام شود.

۷. اگر سایت وردپرسی کند است، چه مواردی را بررسی کنیم؟

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

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

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

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

به همین دلیل، بهتر است نتایج بررسی MySQL را در کنار وضعیت کلی سرور و گزارش‌های مربوط به اجرای سایت تحلیل کنید.

۸. نکات مهم هنگام بررسی کوئری‌های کند در هاست

در هاست اشتراکی، ممکن است کاربران به فایل‌های گزارش MySQL یا متغیرهای مدیریتی آن دسترسی مستقیم نداشته باشند. این محدودیت طبیعی است؛ زیرا تنظیمات عمومی دیتابیس معمولاً در اختیار مدیر سرور قرار دارد.

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

در سرورهای مجازی و اختصاصی، دسترسی بیشتری برای فعال‌سازی گزارش‌ها و بررسی آمار وجود دارد؛ اما پیش از تغییر تنظیمات MySQL، بهتر است از پیکربندی فعلی نسخه پشتیبان داشته باشید و اثر تغییرات را روی عملکرد سرور بررسی کنید.

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

همچنین فعال کردن ثبت تمام کوئری‌ها یا ثبت همه کوئری‌های بدون ایندکس، بدون بررسی شرایط سرور، همیشه توصیه نمی‌شود؛ زیرا ممکن است حجم زیادی از اطلاعات تولید کند و بررسی نتایج را دشوار سازد.

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

جمع‌بندی

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

برای شروع، می‌توان Slow Query Log را فعال کرد و با تعیین زمان مناسب برای ثبت کوئری‌ها، دستورهای زمان‌بر را در فایل گزارش مشاهده کرد. ابزار mysqldumpslow نیز برای خلاصه‌سازی گزارش‌ها مفید است. علاوه بر آن، Performance Schema امکان بررسی تعداد دفعات اجرا، میانگین زمان و مجموع زمان مصرفی کوئری‌ها را فراهم می‌کند.

پس از شناسایی کوئری‌های مشکوک، با استفاده از EXPLAIN می‌توان روش اجرای آن‌ها را بررسی کرد و در صورت نیاز، ایندکس‌ها یا ساختار کوئری را بهینه کرد.

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