بهینه سازی دیتابیس؛ جایی که ۸۰٪ سرعت سایت شما در آن نهفته است

سرعت سایت فقط «کش و CDN» نیست. در بسیاری از پروژه های واقعی، گلوگاه اصلی جایی است که همه چیز به آن ختم می شود: دیتابیس. هر صفحه، هر جستجو، هر فیلتر محصول، هر گزارش مدیریتی و حتی لاگین کاربران در نهایت به کوئری ها، ایندکس ها، قفل ها و I/O دیسک دیتابیس وابسته است. اگر دیتابیس کند باشد، بقیه بهینه سازی ها فقط مُسکن هستند.

در این مقاله، به صورت جامع و عملی بررسی می کنیم چگونه دیتابیس را برای سرعت، پایداری و مقیاس پذیری بهینه کنید؛ از علائم و روش تشخیص مشکل تا تکنیک های سطح Query، Schema، Index، Cache و زیرساخت.

چرا دیتابیس معمولا عامل اصلی کندی است؟

1) بیشتر درخواست ها به دیتابیس ختم می شوند

حتی اگر HTML کش شده باشد، بسیاری از بخش ها دینامیک اند: سبد خرید، موجودی، پروفایل، نتایج جستجو، پنل مدیریت، پیشنهادها و…

2) افزایش داده، خطی رشد نمی کند؛ نمایی مشکل می سازد

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

3) دیتابیس هم CPU دارد، هم RAM، هم Disk و هم Lock

کندی ممکن است از هر کدام باشد:

• CPU بالا: کوئری های سنگین، Sort/Group زیاد
• RAM کم: کش دیتابیس کم، خواندن از دیسک
• Disk کند: I/O بالا، لاگ های زیاد، tempdb (در SQL Server)
• Lock/Blocking: تراکنش های طولانی، همزمانی بالا

علائم کلاسیک مشکل دیتابیس (که باید جدی بگیرید)

• TTFB بالا (زمان شروع پاسخ از سرور زیاد است)
• افزایش ناگهانی زمان بارگذاری در ساعات شلوغ
• CPU یا Disk سرور دیتابیس نزدیک 100٪
• تایم اوت شدن درخواست ها
• گزارش ها و لیست های پنل مدیریت کندتر از قبل
• کندی فقط در برخی صفحات (مثل جستجو/فیلتر/گزارش)

روش درست شروع بهینه سازی: اول اندازه گیری، بعد تغییر

اگر بدون اندازه گیری تغییر دهید، احتمال دارد مشکل را جابجا کنید یا بدتر کنید.

چک لیست اندازه گیری

1. کندترین endpointها/صفحات را پیدا کنید (از APM یا لاگ زمان پاسخ)
2. کندترین کوئری ها را لیست کنید:
   • میانگین زمان
   • بیشترین زمان
   • بیشترین تعداد اجرا
3. برای هر کوئری، این ها را بررسی کنید:
   • Execution Plan
   • تعداد ردیف های واقعی vs تخمینی
   • استفاده از Index (Seek) یا Scan
   • میزان Sort/Hash/Spill به دیسک
4. Bottleneck را مشخص کنید: CPU؟ I/O؟ Lock؟ Network؟

بخش ۱: بهینه سازی Query (بیشترین اثر با کمترین هزینه)

1) N+1 Query (قاتل پنهان سرعت)

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

راه حل:

• Join / Include صحیح
• Batch query
• Preload کردن داده های وابسته

2) فقط ستون های لازم را SELECT کنید

SELECT * در سیستم های واقعی یعنی:

• I/O بیشتر
• انتقال دیتا بیشتر
• کش کمتر مفید
• احتمال استفاده نکردن از Covering Index

3) Pagination درست

بدترین حالت: OFFSET ... FETCH روی دیتای بزرگ بدون ایندکس مناسب.

راه بهتر (Keyset Pagination):

• بر اساس کلید مرتب سازی (مثلا Id یا CreatedAt) صفحه بندی کنید.
• مثال منطقی: «بعد از آخرین Id قبلی» به جای OFFSET بزرگ.

4) فیلترهای قابل ایندکس بنویسید

برخی الگوها باعث می شوند دیتابیس نتواند از ایندکس استفاده کند:

• استفاده از تابع روی ستون در WHERE (مثل WHERE YEAR(CreatedAt)=2026)
• تبدیل نوع داده در شرط

راه حل:

• شرط را طوری بنویسید که ستون خام قابل مقایسه باشد (Range query).

5) جستجو با LIKE

LIKE '%term%' معمولا ایندکس را بی اثر می کند. راه حل ها:

• Full-Text Search (در SQL Server / MySQL)
• موتور جدا مثل Elasticsearch برای جستجوی حرفه ای
• اگر فقط Prefix است: LIKE 'term%' با ایندکس مناسب

6) Aggregate های سنگین را از مسیر کاربر خارج کنید

گزارش های سنگین را:

• Precompute کنید (جدول خلاصه / Materialized View)
• یا Async بسازید (job + cache)
• یا از replica خواندنی استفاده کنید

بخش ۲: ایندکس گذاری اصولی (Index مثل میانبر است)

اصول کلیدی ایندکس

• ایندکس روی ستون هایی که زیاد WHERE / JOIN / ORDER BY می شوند.
• ایندکس زیاد = سرعت خواندن بهتر، اما:
  • سرعت نوشتن بدتر
  • حجم بیشتر
  • نگهداری سخت تر

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

• ایندکس روی ستون هایی که Selectivity پایین دارند (مثل boolean) بدون ترکیب مناسب
• نداشتن ایندکس ترکیبی برای فیلترهای چندتایی
• ناهماهنگی بین ORDER BY و ترتیب ستون های ایندکس

Covering Index (کاهش نیاز به Lookup)

اگر کوئری همیشه 3-4 ستون را می خواند، ایندکس را طوری بسازید که همان ستون ها را پوشش دهد تا دیتابیس مجبور نشود به جدول اصلی برگردد.

بخش ۳: طراحی Schema و مدل داده (ریشه ای ترین بهینه سازی)

1) نوع داده درست انتخاب کنید

• INT به جای BIGINT وقتی لازم نیست
• DATETIME2 مناسب (در SQL Server)
• طول varchar منطقی

این ها روی I/O، کش و ایندکس مستقیم اثر دارند.

2) نرمال سازی vs دنرمال سازی

• برای تراکنش و صحت داده: نرمال سازی خوب است
• برای سرعت خواندن های پرتکرار: دنرمال سازی کنترل شده (مثلا ذخیره شمارنده ها، یا snapshot) می تواند مفید باشد

3) تاریخچه و لاگ ها را جدا کنید

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

• ایندکس ها سنگین می شوند
• Backup/Restore کند می شود
• کوئری های روزمره کند می شوند

راه حل:

• جدول جدا
• آرشیو دوره ای
• پارتیشن بندی (اگر امکانش باشد)

بخش ۴: Cache؛ اگر درست پیاده شود، معجزه می کند

چه چیزی را کش کنیم؟

• داده های تقریبا ثابت: تنظیمات، دسته بندی ها
• نتایج محاسباتی: تعدادها، خلاصه ها
• نتایج جستجوهای پرتکرار (با TTL کوتاه)

کجا کش کنیم؟

• Application cache (in-memory)
• Redis / Memcached
• CDN برای محتوای استاتیک (کمک می کند، ولی جای دیتابیس را نمی گیرد)

نکته مهم: Invalidation

کش بدون سیاست invalidation یعنی «نمایش داده اشتباه».

پس:

• TTL منطقی
• Cache key استاندارد
• پاکسازی هنگام تغییرات مهم

بخش ۵: Concurrency، Lock و تراکنش ها (کندی فقط کوئری نیست)

علائم Lock/Blocking

• کندی شدید فقط در ساعات شلوغ
• کوئری ها در حالت waiting

راه حل های معمول:

• کوتاه کردن تراکنش ها
• جلوگیری از آپدیت های دسته ای در ساعات پیک
• ایندکس مناسب برای کاهش اسکن و قفل گسترده
• سطح ایزولیشن مناسب (بسته به دیتابیس)

بخش ۶: نگهداری (Maintenance)؛ چیزی که معمولا فراموش می شود

• به روز بودن آمار (Statistics): برای پلن درست حیاتی است (خصوصا SQL Server)
• Fragmentation: ایندکس های به هم ریخته روی I/O اثر دارند
• Cleanup: حذف داده های قدیمی، آرشیو
• Backup strategy: بکاپ سنگین در ساعت پیک می تواند کندی ایجاد کند

بخش ۷: زیرساخت دیتابیس (وقتی Query درست است ولی هنوز کند است)

موارد مهم

• Disk سریع (NVMe) برای دیتابیس حیاتی است
• RAM کافی برای buffer pool
• جدا کردن دیسک دیتا و لاگ (در برخی سناریوها)
• تنظیم کانکشن پولینگ (Application side)
• Replica برای خواندن (Read scaling)
• Sharding فقط وقتی لازم است (پیچیده و پرهزینه)

نقشه راه پیشنهادی (عملی و مرحله ای)

مرحله 1: تشخیص سریع (Quick Wins)

• 10 کوئری کند اول را پیدا کنید
• N+1 را حذف کنید
• SELECT * را حذف کنید
• ایندکس های بدیهی روی WHERE/JOIN های پرتکرار بگذارید

مرحله 2: اصلاح ساختاری

• بازطراحی جداول مشکل دار
• آرشیو/پارتیشن بندی داده های حجیم
• کش برای داده های پرتکرار

مرحله 3: مقیاس و پایداری

• Replica خواندنی
• Job های async برای گزارش
• مانیتورینگ دائمی + بودجه نگهداری

جمع بندی

بهینه سازی دیتابیس معمولا بیشترین بازده را در سرعت سایت دارد، چون:

• مستقیم روی زمان پاسخ سرور اثر می گذارد
• با افزایش داده خودش را نشان می دهد
• با چند اصلاح درست (Query + Index + Cache) می تواند چند برابر بهبود ایجاد کند

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