اشتباهاتی که پرفورمنس MySQL را نابود می‌کنند

قبلا فکر می‌‌کردم که کندی بک‌اند سایت من بخاطر Node.js است اما بعدا متوجه شدم که این دیتابیس‌ام است که دارد بی‌سروصدا همه‌چیز را خراب می‌کند! هیچ خطا و Errorای نمی‌دیدم و هیچ Crashای اتفاق نمی‌افتاد ولی کوئری‌های کند، مصرف بالای CPU و APIهای سنگین باعث شده بودند بک‌اند به‌شدت کند باشد.

بعد از بررسی‌هایی که روی پایگاه داده‌ام انجام دادم فهمیدم که نه یک اشتباه بزرگ، بلکه مجموعه‌ای از اشتباهات کوچک در دیتابیس می‌تواند پرفورمنس را نابود کند، درست مثل گلوله‌های برفی کوچکی که به یک بهمن بزرگ تبدیل می‌شوند! در این مقاله قصد داریم مجموعه‌ای از این اشتباهات کوچک که پرفورمنس MySQL را ضعیف می‌کنند و روش‌های رفع آن‌ها را معرفی کنیم. در پایان این مقاله می‌توانید سریعا به این پرسش خود پاسخ دهید که: «چرا دیتابیس کند است؟»

استفاده از SELECT * در تمام کوئری‌ها

فرض کنید در یک فروشگاه هستید و بجای اینکه یک کالای خاص را بخواهید، از فروشنده بخواهید تمام کالاهای فروشگاه را در سبد خرید شما بگذارد! دقیقا همینقدر احمقانه!

وقتی شما در دیتابیس از SELECT * FROM users استفاده می‌کنید، در واقع دارید به دیتابیس می‌گویید تمام ستون‌ها و تمام رکوردهای جدول users را به شما بدهد. حتی اگر شما فقط به id، name و email نیاز داشته باشید، دیتابیس ناگزیر است ستون‌های بزرگ مثل photo_blob (عکس پروفایل به صورت باینری) یا bio (متن بلند توضیحات) را هم بخواند، این حجم عظیم داده را از دیسک به حافظه (RAM) منتقل کند، سپس همین حجم بزرگ داده را از دیتابیس به سرور برنامه (Node.js) بفرستد. نتیجه اینکه مقدار زیادی از حافظه مصرف می‌شود، پهنای باند شبکه بالا می‌رود و زمان پاسخگویی به‌شدت کند می‌شود.

SELECT * FROM users;

راه حل: به دیتابیس دقیقا بگویید که چه چیزی می‌خواهید: SELECT id, name, email .... بدین ترتیب پایگاه داده فقط به سراغ ستون‌های ضروری می‌رود و حتی با کمک ایندکس‌ها، کار را سریع‌تر انجام می‌دهد.

SELECT id, name, email FROM users;

نداشتن ایندکس (قاتل خاموش)

فرض کنید یک لغتنامه هزار صفحه‌ای دارید که در آن کلمات بر اساس الفبا مرتب نشده و بدون هیچ ترتیبی قرار دارند. برای اینکه کلمه «ایران» را پیدا کنید باید از صفحه اول شروع کنید و یکی‌یکی تمام صفحات را بگردید تا به واژه «ایران» برسید. به اینکار در اصطلاحات دیتابیسی می‌گویند: Full Table Scan

در پایگاه داده وقتی می‌نویسید  SELECT * FROM orders WHERE user_id = 10 و روی ستون  user_id ایندکس نباشد، دیتابیس راهی ندارد جز اینکه تک به تک ردیف‌های جدول  orders را اسکن کند تا آن دسته از سفارشاتی را که user_id در آن‌ها برابر ۱۰ است، بیابد. حالا اگر جدول شما ۱ میلیون رکورد داشته باشد، ۱ میلیون عملیات خواندن باید اتفاق بیفتد!

SELECT * FROM orders WHERE user_id = 10;

راه حل: می‌بایست روی ستون user_id یک ایندکس بسازید:  CREATE INDEX .... دیتابیس شما بدون نیاز به اسکن کل جدول (Full Table Scan) می‌رود سراغ ردیف‌های موردنظر.

CREATE INDEX idx_user_id ON orders(user_id);

راهنمایی طلایی: هر ستونی که در WHERE، JOIN و ORDER BY استفاده می‌شود، کاندیدای مناسبی برای ایندکس است.

ایندکس‌های زیاد

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

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

در دیتابیس هم عینا همین اتفاق می‌افتد.  ایندکس‌ها برای عملیات جستجو SELECT فوق‌العاده مفیدند، اما برای عملیات نوشتن (INSERT، UPDATE، DELETE) هزینه دارند. به عبارت دیگر ایندکس‌ها «خواندن- READ» را سریع‌تر ولی «نوشتن-WRITE» را کند می‌کنند. چرا؟ چون هربار که یک ردیف جدید اضافه می‌شود یا یک مقدار عوض می‌شود، دیتابیس مجبور است همه ایندکس‌های مربوط به آن جدول را نیز به‌روزرسانی کند. مثلا اگر روی یک جدول ۱۵ ایندکس مختلف ایجاد نموده باشید، عملیات INSERT روی آن جدول تا ۱۵ برابر کندتر از حالت عادی خواهد شد.

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

مشکل کوئری N+1

تصور کنید شما یک فروشگاه اینترنی دارید و قرار است لیست تمام کاربران (مثلا ۱۰۰ کاربر) و سفارش‌های هرکدام را استخراج کنید. یک کاربر مبتدی چه‌کار می‌کند؟ یک کوئری می‌زند تا لیست همه کاربران را بگیرد. سپس با یک حلقه یا لوپ، بر هریک از آن ۱۰۰ کاربر یک کوئری جداگانه می‌زند تا سفارش‌های آن کاربر را دریافت کند. نتیجه اینکه با کوئری اول، یکبار رفت‌وبرگشت به دیتابیس و با ۱۰۰ کوئری داخل حلقه، مجموعا ۱۰۱ بار رفت‌وبرگشت انجام می‌شود. حالا اگر ۱۰۰هزار کاربر داشته باشید چه؟! پدر شبکه، حافظه، پردازنده و سرعت پاسخگویی درمی‌آید!

const users = await getUsers();

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

راه حل: فقط با یک کوئری، همه‌چیز را یکجا دریافت کنید و با استفاده از  JOIN به دیتابیس بگویید جداول users و orders را به هم وصل کند و سفارش‌های هر کاربر را همان لحظه بفرستد. در اینصورت بجای ۱۰۱ کوئری فقط ۱ کوئری زده‌اید.

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

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

عدم استفاده از EXPLAIN

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

مثلا وقتی می‌نویسید SELECT * FROM products WHERE category_id = 5 بدون EXPLAIN، حدس می‌زنید که چه اتفاقاتی می‌افتد اما با EXPLAIN، دیتابیس دقیق و آشکارا می‌گوید برای پیدا کردن ۱۰ محصول، مجبور شده است کل ۵۰۰,۰۰۰ ردیف جدول را اسکن کند چون روی category_id ایندکس وجود ندارد.

EXPLAIN SELECT * FROM products WHERE category_id = 5;

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

استفاده از توابع در WHERE

استفاده از توابع در WHERE ایندکس را از کار می‌اندازد. برای درک بهتر این موضوع فرض کنید یک دفترچه تلفن دارید که بر اساس حروف الفبای نام افراد مرتب شده است (ایندکس روی نام). اگر به شما بگویند شماره همه افرادی را که حرف اول اسمشان «ع» است، پیدا کن، به‌سادگی می‌توانید این کار را انجام دهید. اما اگر از شما بخواهند که شماره تلفن همه افرادی که تعداد حروف اسمشان ۵ حرف است را پیدا کنید، دیگر آسان نیست و مجبورید تک‌تک اسم‌ها را بخوانید و طول هر اسم را محاسبه کنید. اینجا دیگر مرتب بودن دفترچه تلفن بر اساس حروف الفبا به کارتان نمی‌آید.

همین امر در مورد دیتابیس هم صادق است. فرض کنید از دیتابیس می‌خواهید که تابع YEAR را روی فیلد created_at اجرا بعد آن را با ۲۰۲۴ مقایسه کند. در چنین شرایط دیتابیس نمی‌تواند از مقدار اصلی فیلد برای جستجو استفاده کند و مجبور است برای هر ردیف، اول created_at را بخواند، سپس سالش را محاسبه کند و در نهایت مقایسه را انجام دهد.

SELECT * FROM users WHERE YEAR(created_at) = 2024

راه حل: باید به پایگاه داده بگویید مقادیری را که بین این دو تاریخ هستند را پیدا کن. حالا دیتابیس می‌تواند از ایندکس created_at استفاده کند و مستقیما به آن محدوده برود.

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

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

بارگذاری حجم زیاد داده

دریافت همه‌چیز شاید وسوسه‌برانگیز باشد اما خیلی گران تمام می‌شود!

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

مثال بالا واقعا احمقانه به‌نظر می‌رسد اما بعضی‌ها عینا همین کار را انجام می‌دهند.

SELECT * FROM logs

اگر جدول logs ده میلیون رکورد داشته باشد، این کوئری تمام ۱۰ میلیون را به حافظه سرور می‌فرستد. حتی اگر فقط ۲۰ تای آخر را بخواهید نمایش دهید.

راه حل: به‌سادگی به دیتابیس بگویید فقط ۲۰ تا را بدهد، نه همه را!

SELECT * FROM logs LIMIT 20 OFFSET 0

در هیچ صفحه وب یا اپلیکیشنی، کاربر نیاز ندارد ۱۰ میلیون ردیف را یک‌جا ببیند. پس همیشه از LIMIT استفاده کنید. این کار همان صفحه‌بندی (Pagination) است، یعنی داده و اطلاعات را تکه‌تکه و به‌اندازه نیاز دریافت نمایید.

استفاده نکردن از کش کوئری

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

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

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

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

const cached = await redis.get("top_posts");

اصل کلیدی: اگر یک کوئری سنگین است و داده‌هایش دائما تغییری نمی‌کنند، آن‌ را کش کنید.

استفاده از Data Type نامناسب

انتخاب نوع داده مناسب در MySQL مسئله مهمی است. به عنوان مثال اگر شما می‌خواهید سن افراد را ذخیره کنید، سن همیشه عددی بین صفر تا ۱۵۰ است و اگر شما از یک فیلدمتنی با ۲۵۵ کاراکتر استفاده کنید، اشتباه است.

age VARCHAR(255)

همیشه کوچک‌ترین نوع داده‌ای که نیاز شما را برآورده می‌کند، انتخاب کنید:

age INT

انتخاب نوع داده اشتباه نتایج زیر را در پی دارد:

۱. فضای ذخیره‌سازی: وقتی جدول شما ۱۰ میلیون رکورد دارد، VARCHAR(255) به جای INT، ده‌ها مگابایت فضای اضافی مصرف می‌کند.
۲. ایندکس‌ها: ایندکس روی ستون‌های عددی بسیار کوچک‌تر و سریع‌تر از ایندکس روی ستون‌های متنی است.
۳. مقایسه: دیتابیس اعداد را خیلی سریع‌تر از رشته‌ها با هم مقایسه می‌کند.

Joinهای بهینه نشده

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

مشکل اول - نبودن ایندکس روی ستون‌های Join: فرض کنید قصد دارید لیستی از سفارش‌های هریک از مشتریانتان را مشاهده کنید. دو جدول دارید:  users (مشتریان) و orders (سفارشات). می‌دانید که JOIN دقیقاً برای همین کار ساخته شده است. حال اگر روی orders.user_id ایندکس نباشد، برای پیدا کردن سفارش‌های هر مشتری، باید تمام جدول orders را اسکن کنید.

مشکل دوم - جوین زیاد: فرض کنید سایت شما یک صفحه داشبورد دارد که اطلاعات متعددی شامل اطلاعات کاربر، سفارشات کاربر، محصولات هر سفارش، نظرات روی هر محصول و تصاویر هر محصول را نشان می‌دهد. اگر نمایش این اطلاعات را با ۵ جوین پشت‌سرهم انجام دهید دیتابیس ناچار است یک جدول موقتی عظیم در حافظه ایجاد کند. اگر تعداد کاربران زیاد باشد، ممکن است تعداد ردیف‌های این جدول موقتی، میلیونی شود.

راه‌حل‌ها:
۱. روی ستون‌هایی که در ON استفاده می‌شوند (معمولاً کلیدهای خارجی) حتما ایندکس‌گذاری کنید.
۲. تعداد جوین‌ها را تا حد امکان کم کنید. گاهی زدن دو کوئری، بهتر از زدن یک کوئری با ۵ جوین است.
۳. اگر نیاز به جوین مکرر دارید، می‌توانید از «نرمال‌زدایی یا غیرنرمال‌سازی» استفاده کنید (یعنی بعضی فیلدها را تکراری ذخیره کنید تا نیاز به جوین نباشد).

نداشتن Connection Pooling

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

باز کردن یک اتصال (connection) به دیتابیس هم شامل ایجاد سوکت شبکه، احراز هویت، تخصیص حافظه در دیتابیس و تنظیمات session است که مجموعه این عملیات می‌تواند از ۱۰ تا ۱۰۰ میلی‌ثانیه به‌طول بینجامد. پس اگر برای هر کوئری ساده (مثلاً گرفتن نام یک کاربر) یک اتصال باز و بسته کنید، یک عملیات ۱ میلی‌ثانیه‌ای تبدیل به ۵۰ میلی‌ثانیه می‌شود.

راه‌حل: یک استخر از اتصال‌های از پیش باز شده (Connection Pooling) درست کنید. وقتی برنامه نیاز به دیتابیس دارد یک اتصال را از استخر برمی‌دارد، کارش را انجام می‌دهد و سپس بدون قطع اتصال، آن را به استخر برمی‌گرداند.

در Node.js:

const pool = mysql.createPool({
  connectionLimit: 10  // حداکثر ۱۰ اتصال همزمان
});

در مثال فوق ۱۰۰۰ درخواست می‌آید، اما فقط ۱۰ اتصال باز است که بارها استفاده می‌شوند.

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

بی‌اعتنایی به لاگ‌های کوئری‌های کند

دیتابیس قادر است تمام کوئری‌هایی را کند هستند و اجرای آن‌ها بیش از یک زمان مشخص (مثلاً ۲ ثانیه) طول می‌کشد، در یک فایل ثبت کند. Slow Query Logs در دیتابیس این کار را انجام می‌دهد. برای فعال کردن این قابلیت، این اقدام را انجام دهید:

SET GLOBAL slow_query_log = 'ON';

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

نتیجه

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

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

توصیه می‌کنیم این مقاله را هم بخوانید: در بهینه‌سازی پرفورمنس SQL حرفه‌ای شوید!

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