سرعت کوئری‌های MySQL را ۱۰ برابر بالا ببرید

شاید برای شما هم پیش آمده باشد که APIهایتان با تاخیری آزاردهنده جواب دهند، صفحات دیر باز شوند و مصرف CPU به‌طور ناگهانی بالا رود و بی‌دلیل داغ کند. وقتی من با این مشکلات مواجه بودم، اول همه این مشکلات را به گردن Node.js انداختم اما بعد متوجه شدم مشکل از جای دیگری است: کوئری‌های MySQL من.در این مقاله همگام با هم پیش می‌رویم و به شما نشان می‌دهم چگونه با انجام چند تغییر خیلی ساده، زمان پاسخ‌دهی بعضی از بخش‌های برنامه‌ام را از ۵۰۰ میلی‌ثانیه به کمتر از ۵۰ میلی‌ثانیه رساندم. در این مقاله روش‌های تضمینی برای افزایش سرعت کوئری در MySQL را معرفی می‌کنم و به شما خواهم گفت با چه روش‌هایی به افزایش ده برابری سرعت کوئری ها رسیدم.

۱. استفاده از SELECT * را کنار گذاشتم

این رایج‌ترین اشتباه من بود که قبلاً تقریبا در همه‌جا از SELECT * استفاده می‌کردم.

کد قدیمی من:

SELECT * FROM users;

کد جدید من:

SELECT id, name, email FROM users;

هرچند که این، تغییر کوچکی به‌نظر می‌رسد اما نتایج بزرگ و مهمی دارد:

1. داده کمتری بین پایگاه داده و برنامه جابجا می‌شود.
2. افزایش سرعت کوئری ها را تجربه خواهیم کرد.
3. ایندکس‌ها بهتر استفاده می‌شوند.

۲. ایندکس‌ها همه‌چیز را تغییر دادند

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

بگذارید با یک مثال ساده، بحث را ادامه دهیم. فرض کنید می‌خواهید کاربری با یک ایمیل خاص را پیدا کنید:

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

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

CREATE INDEX idx_email ON users(email);

نکته کلیدی: اگر یک ستون شامل WHERE، ORDER BY یا JOIN باشد، حتما آن را ایندکس کنید.

۳. از EXPLAIN استفاده کردم

یکی از مهم‌ترین ابزارهایی که با آن‌ها آشنا شدم EXPLAIN بود. این دستور به شما نشان می‌دهد که MySQL کوئری شما را چگونه اجرا می‌کند.

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

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

خروجی EXPLAIN برای شما شرح می‌دهد که Query Plan (نقشه اجرای کوئری) چیست، آیا از ایندکس استفاده شده یا نه و اینکه چند ردیف (row) اسکن شده است.

می‌توان گفت که EXPLAIN واقعا مثل یک عکس رادیولوژی برای پایگاه داده است و تمام حقایق را برملا می‌کند. گاهی کوئری‌هایی که از نظر ظاهری خوب به‌نظر می‌رسند، عملکرد خیلی بدی دارند که EXPLAIN این موضوع را آشکار می‌کند.

۴. مشکل N+1 Queries را رفع کردم

این مشکل به صورت کاملا نامحسوس و اصطلاحا زیرپوستی برنامه شما را نابود می‌کند. بگذارید با یک مثال در این مورد صحبت کنیم:

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

const users = await getUsers();

for (const user of users) {
  const orders = await getOrders(user.id);
}

این کد ساده که در ظاهر بی‌آزار به‌نظر می‌رسد، ۱ کوئری را به +۱۰۰ تبدیل می‌کند. یعنی به ازای هر کاربر، صد درخواست به پایگاه داده ارسال شده است. اما در چنین شرایطی راه‌ حل چیست؟ باید به جای درخواست‌های جداگانه، از JOIN در یک کوئری واحد استفاده کنید:

SELECT * FROM users
JOIN orders ON users.id = orders.user_id;

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

۵. از صفحه‌بندی (Pagination) به جای لودکردنِ همه‌چیز استفاده کردم

اشتباهی که قبلا مرتکب می‌شدم این بود که همه داده‌ها را یکجا دریافت می‌کردم که واقعا ایده افتضاحی بود!

قبلا اینطور می‌نوشتم:

SELECT * FROM products;

بعدا کد خود را به اینصورت بهبود دادم:

SELECT * FROM products LIMIT 20 OFFSET 0;

این روش مصرف حافظه را به میزان چشمگیری کاهش می‌دهد، کوئری‌ها را با سرعت بیشتری اجرا می‌کند و در نهایت اینکه روی تجربه کاربری (User Experience یا UX) تاثیر فوق‌العاده‌ای دارد زیرا لازم نیست کاربر منتظر بماند که کل داده‌ها بارگذاری شوند.

۶. از توابع در عبارت WHERE فرار کردم!

این موضوع کمی ظرافت دارد اما اهمیت آن در افزایش پرفورمنس و سرعت پایگاه داده بسیار زیاد است.

استفاده از تابع DATE() روی ستون created_at باعث می‌شود ایندکس (index) آن ستون کاملاً بی‌اثر شود و این، به معنای آن است که پایگاه داده دیگر نمی‌تواند از ایندکس برای جستجوی سریع استفاده کند.

کد اشتباه من در گذشته:

SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

کد اصلاح‌شده:

SELECT * FROM users
WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02';

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

۷. نوع داده یا Data Type در افزایش سرعت کوئری مهم است

من قبلا فیلدهایی به این شکل داشتم:

phone VARCHAR(255)

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

phone VARCHAR(15)

هرچقدر نوع داده کوچک‌تر باشد، سرعت کوئری‌ها بالاتر می‌رود. کوچک بودن نوع داده‌ها منجر به ایندکس‌گذاری بهتر و درست‌تر می‌شود و فضای کمتری از پایگاه داده اشغال می‌شود که همه این موارد در کل منجر به پرفورمنس بالای پایگاه داده می‌شود.

یادتان باشد که همیشه از کوچک‌ترین نوع داده‌ای که نیازتان را تامین می‌کند استفاده کنید. یک شماره تلفن هیچ وقت به ۲۵۵ کاراکتر نیاز ندارد.

۸. کوئری‌های سنگین و پرتکرار را کش (Cache) کردم

بعضی از داده‌ها زیاد تغییر نمی‌کند. پس عاقلانه بنظر نمی‌رسد که هربار که کاربر درخواست می‌دهد، آن را به پایگاه داده بفرستیم.

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

const cached = await redis.get("top_products");
if (cached) return JSON.parse(cached);

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

داده‌هایی که کمتر تغییر می‌کنند و کمتر آپدیت می‌شوند (مثل لیست محصولات پرفروش) بهترین گزینه برای کش کردن هستند.

۹. JOINها را بهینه یا حذف کردم

هر JOIN مثل یک حلقه درون حلقه عمل می‌کند و JOINهای زیاد به کندی کوئری‌ها منجر می‌شود.

نکاتی که باید در نظر بگیرید، شامل موارد زیر است:

1. روی ستون‌هایی که در JOIN استفاده می‌شوند، ایندکس بگذارید.
2. JOINهای غیرضروری را حذف کنید.
3. در صورت نیاز، از غیرنرمال‌سازی (Denormalization) استفاده کنید؛ یعنی ذخیره تکراری بعضی از داده‌ها به قیمت سرعت بیشتر

۱۰. همه‌چیز را اندازه گرفتم

جمله مشهوری وجود دارد که «چیزی را که اندازه نگیری، نمی‌توانی بهینه کنی».

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

• زمان اجرای کوئری (query execution time)
• لاگ کوئری‌های کند (slow query logs)
• پروفایلینگ (profiling): بررسی دقیق مراحل اجرای هر کوئری

نتیجه نهایی من از روش‌های افزایش سرعت کوئری

پس از اعمال ۱۰ موردی که در این مقاله به آن‌ها اشاره کردم، به نتایج زیر رسیدم:

سخن پایانی

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

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

بزرگ‌ترین درسی که من در این مسیر گرفتم این است:
سرعت بک‌اند (backend) شما به اندازه سرعت کندترین کوئری شماست.

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

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

منبع مورد استفاده در این مقاله: Medium