ساعت ۹ شب است، کمپین فروش شروع شده و صفحه پرداخت فقط برای بعضی کاربران کند میشود. CPU سرور پایگاه داده بالا رفته، تیم زیرساخت پیشنهاد RAM بیشتر میدهد، توسعهدهنده میگوید «یک Index اضافه کنیم» و مدیر محصول میپرسد چرا همین سایت دیروز سریع بود. اگر بدون شواهد یکی از این نسخهها را اجرا کنید، شاید علامت را موقتاً پنهان کنید؛ اما ممکن است نوشتن سفارش را کندتر، Lock را طولانیتر یا هزینه زیرساخت را بیشتر کنید.
بهینهسازی پایگاه داده یعنی کوتاهکردن زمان و هزینه مسیرهای واقعی کاربر با یک چرخه قابلاندازهگیری: Query مسئلهدار را پیدا کنید، Execution Plan و الگوی بار را بخوانید، کمریسکترین فرضیه را آزمایش کنید و نتیجه را در Production بسنجید. این راهنما همین چرخه را برای MySQL، PostgreSQL، اپلیکیشنهای وب و WordPress/WooCommerce توضیح میدهد؛ از Slow Query و EXPLAIN تا Index، N+۱، Lock، Connection Pool، Cache، نگهداری و Rollback.
بهینهسازی پایگاه داده چیست و چه چیزی نیست؟
Database Optimization مجموعهای از تصمیمها در سطح Query، Schema، Index، تراکنش، Connection، حافظه، ذخیرهسازی و معماری اپلیکیشن است که یک نتیجه کسبوکاری مشخص را بهتر میکند. «بهتر» فقط کمشدن میانگین زمان Query نیست؛ ممکن است هدف، کاهش p95 پرداخت، جلوگیری از Timeout موجودی، کمکردن هزینه CPU یا پایدارماندن سامانه در ترافیک کمپین باشد.
| برداشت ناقص | تعریف عملی | شاهد موفقیت |
|---|---|---|
| هرچه Index بیشتر، بهتر | کمترین مجموعه Index که Read حیاتی را پوشش دهد و Write را خفه نکند | Plan بهتر، latency کمتر و هزینه Write قابلقبول |
| CPU بالا یعنی سرور ضعیف است | CPU فقط یک علامت است؛ Query پرتکرار، Plan بد یا ازدحام میتواند علت باشد | تفکیک DB time، calls، rows و wait |
| Cache مشکل دیتابیس را حل میکند | Cache برای داده مناسب، با TTL و Invalidation روشن | Hit ratio مفید بدون Stale یا خطای صحت |
| پاکسازی دیتابیس یعنی Optimization | نگهداری یکی از لایههاست، نه جایگزین Query و Schema درست | کاهش Bloat/حجم همراه با اثر واقعی بر Journey |
| یک عدد برای همه سایتها کافی است | SLO متناسب با مسیر، بار، موتور و زیرساخت | p50/p95/p99 و Error تحت بار نماینده |
پایگاه داده بخشی از زنجیره درخواست است. DNS، شبکه، PHP/Node، سرویس بیرونی، Cache و مرورگر نیز زمان میگیرند. اگر سهم دیتابیس از درخواست کم باشد، تیونکردن آن اثر محسوسی بر کاربر ندارد. برای پیوند نتیجه فنی با تجربه و تبدیل، راهنمای سرعت سایت، UX، سئو و نرخ تبدیل را کنار این مقاله ببینید.
نیت جستوجو: کاربر این راهنما چه تصمیمی دارد؟
عبارتهایی مثل «بهینهسازی پایگاه داده»، «افزایش سرعت دیتابیس»، «Query کند»، «بهینهسازی MySQL» و «بهینهسازی دیتابیس وردپرس» چند نیاز متفاوت را پنهان میکنند. پاسخ خوب باید مرز آنها را روشن کند:
| نیاز | پرسش واقعی | خروجی لازم |
|---|---|---|
| تشخیص | کندی واقعاً از دیتابیس است؟ | Trace، DB time، Top Query و Wait |
| توسعه | Query یا ORM را چگونه اصلاح کنم؟ | Plan قبل/بعد، Index و تست صحت |
| عملیات | چرا در پیک کند یا ناپایدار میشود؟ | Connection، Lock، I/O، Replica lag و ظرفیت |
| WordPress | آیا Transient و Revision را پاک کنم؟ | Backup، شناسایی جدول/Plugin و Runbook کمریسک |
| تصمیم معماری | Cache، Replica یا NoSQL لازم است؟ | شواهد بار، Consistency و TCO؛ نه مد فناوری |
نشانههای دیتابیس کند؛ علامت را با علت اشتباه نگیرید
TTFB بالا یا صفحه کند بهتنهایی اثبات نمیکند دیتابیس مقصر است. یک Trace میتواند نشان دهد زمان در Query، صف Connection، API پرداخت یا رندر اپلیکیشن مصرف شده است. در مقابل، متوسط مناسب هم ممکن است p99 بسیار بد را پنهان کند.
| علامت | علتهای محتمل | شاهد بعدی |
|---|---|---|
| DB CPU بالا | Query پرتکرار، Sort/Hash بزرگ، Plan بد، Compaction یا Vacuum | Top SQL بر اساس total time و CPU |
| Latency بالا و CPU عادی | Lock، I/O، شبکه، Connection wait یا سرویس ذخیرهسازی | Wait events، lock graph، disk latency |
| فقط در پیک کند است | Pool اشباع، Hot row، Cache miss storm، Queue buildup | همبستگی concurrency با p95 و wait |
| پس از Deploy کند شد | Query جدید، N+۱، تغییر Cardinality، Migration یا Cache purge | مقایسه release، query fingerprint و Plan |
| فقط گزارشها کندند | Scan حجیم، Sort روی دیسک، رقابت OLTP و Analytics | temp I/O، rows scanned و schedule گزارش |
| Replica عقب میماند | Write burst، Query بلند روی Replica، شبکه یا I/O | lag زمانی/بایتی و نرخ WAL/binlog |
تریاژ ۱۰دقیقهای در Incident
- دامنه اثر را مشخص کنید: کدام Journey، Region، نسخه و بازه زمانی؟ پرداخت کند است یا همه صفحات؟
- خط زمانی را قفل کنید: Deploy، Migration، کمپین، Import، Cron یا تغییر شبکه همزمان بوده است؟
- چهار Signal را ببینید: latency، traffic، errors و saturation؛ سپس سهم Database span را جدا کنید.
- Query fingerprintهای پرهزینه را رتبهبندی کنید: بر اساس زمان تجمعی، p95، تعداد فراخوانی و Lock time.
- اول مهار، بعد درمان: Feature flag، محدودکردن گزارش سنگین، کاهش concurrency کنترلشده یا Rollback؛ DDL عجولانه در اوج Incident اجرا نکنید.
برای طراحی Trace، Metric، Log، SLO و Alert به راهنمای Observability و مانیتورینگ لحظهای مراجعه کنید. پایش بیرونیِ مسیرهای حیاتی نیز در راهنمای مانیتورینگ آپتایم سایت توضیح داده شده است.
Baseline بسازید؛ قبل از تغییر دقیقاً چه چیزی را ثبت کنیم؟
Baseline باید یک بازه نماینده از روز عادی و یک بازه پیک را پوشش دهد. Queryها را بر اساس Fingerprint یا Query ID گروهبندی کنید تا تغییر مقادیر پارامتر، یک Query واحد را به هزار ردیف تبدیل نکند. داده حساس را در لاگ Mask کنید.
| لایه | حداقل معیار | چرا مهم است؟ |
|---|---|---|
| Journey | p50/p95/p99، نرخ خطا، Throughput | اثر قابلدیدن برای کاربر و کسبوکار |
| Query | calls، total/mean/max time، rows | تفکیک Query نادرِ کند از Query پرتکرار |
| Plan | estimated vs actual rows، loops، scan، sort | کشف خطای تخمین و کار تکرارشونده |
| Wait | lock، I/O، CPU، connection، network | فهمیدن اینکه زمان دقیقاً کجا منتظر میماند |
| منابع | CPU، memory، disk latency/IOPS، temp space | تشخیص Saturation و Spill |
| صحت | تعداد/مجموع/نمونه نتیجه و invariant | جلوگیری از سریعترشدنِ پاسخ اشتباه |
یک فرمول ساده برای اولویت اولیه مفید است:
بار تجمعی Query ≈ تعداد فراخوانی × میانگین زمان اجرا
Query صد میلیثانیهای که در دقیقه ۲۰هزار بار اجرا میشود، ممکن است مهمتر از گزارش ۱۲ثانیهای باشد که روزی یک بار اجرا میشود. اما این فرمول بهتنهایی کافی نیست؛ مسیر پرداخت، Lock گسترده یا Query دارای ریسک ازدسترفتن داده وزن بیشتری میگیرد.
ابزار مشاهده در MySQL و PostgreSQL
در MySQL، Slow Query Log شروع قابلفهمی است و Performance Schema امکان تجمیع Statementها را میدهد. مستندات رسمی MySQL توضیح میدهد که Slow Log بر پایه آستانه long_query_time کار میکند و جدولهای خلاصه Performance Schema معیارهای تجمیعی ارائه میکنند: Slow Query Log در MySQL و Statement Summary Tables.
در PostgreSQL، افزونه pg_stat_statements آمار Planning و Execution را بر اساس Query نرمالشده جمع میکند. فعالسازی آن به تنظیمات و معمولاً Restart نیاز دارد؛ سربار و دسترسی به متن Query را نیز باید مدیریت کنید. جزئیات را در مستند رسمی pg_stat_statements ببینید.
-- نمونه رتبهبندی در PostgreSQL؛ نام ستونها را با نسخه خود تطبیق دهید
SELECT queryid, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;نکته امنیتی: متن Query و Bind value ممکن است ایمیل، موبایل یا شناسه سفارش را آشکار کند. Retention، Redaction، RBAC و دسترسی تیم پشتیبانی به Telemetry باید بخشی از طراحی باشد.
چگونه EXPLAIN و Execution Plan را بخوانیم؟
EXPLAIN نقشهای است که Optimizer برای اجرای Query انتخاب کرده؛ EXPLAIN ANALYZE معمولاً Query را واقعاً اجرا میکند و زمان و تعداد ردیف واقعی میدهد. در نتیجه روی Queryهای نوشتنی یا Query سنگین Production بدون شناخت رفتار نسخه و موتور اجرا نکنید. ابتدا Replica/Staging با داده نزدیک به واقعیت، سپس اجرای کنترلشده و Read-only.
مستندات رسمی EXPLAIN در MySQL ۸.۴ و Using EXPLAIN در PostgreSQL تفاوت Estimate و Actual را شرح میدهند.
| نشانه در Plan | پرسش تشخیصی | فرضیه اصلاح |
|---|---|---|
| Actual rows بسیار بیشتر از Estimate | Statistics کهنه یا توزیع داده نامتوازن است؟ | ANALYZE، آمار گستردهتر یا بازنویسی Predicate |
| Full/Seq Scan | جدول کوچک است یا Selectivity پایین؟ | Scan ممکن است درست باشد؛ Index را کورکورانه تحمیل نکنید |
| Loops زیاد | Nested Loop یا N+۱ کار را تکرار میکند؟ | Join/Batch، Index سمت inner یا کاهش ردیف ورودی |
| Sort/Hash روی دیسک | حجم میانی و حافظه کافی است؟ | کاهش داده، Index مرتب، تنظیم حافظه پس از اندازهگیری |
| Rows removed by filter زیاد | فیلتر دیر اعمال میشود؟ | Predicate قابل Index، Index مناسب یا بازنویسی |
| زمان کم هر Loop اما Loops بسیار | هزینه تجمعی کجا ضرب شده؟ | Batching و حذف Round-trip |
دیدن Table Scan بهخودیخود خطا نیست. برای جدول کوچک یا Queryای که بخش بزرگی از جدول را میخواهد، Scan میتواند از رفتوبرگشت تصادفی Index ارزانتر باشد. به Actual time، rows، loops و Buffer/I/O با هم نگاه کنید.
نمونه فروشگاه ایرانی: لیست سفارشها
SELECT id, total_amount, status, created_at
FROM orders
WHERE merchant_id = ?
AND status = ?
AND created_at >= ?
ORDER BY created_at DESC, id DESC
LIMIT 50;اگر این Query مسیر اصلی پنل فروشنده است، یک Index ترکیبی مانند (merchant_id, status, created_at, id) میتواند فرضیه خوبی باشد؛ اما فقط پس از دیدن Plan، Cardinality و الگوی واقعی فیلتر. اگر اغلب Status حذف میشود یا چند Status انتخاب میشود، ترتیب مطلوب ممکن است فرق کند. Plan قبل و بعد، هزینه Write و اندازه Index را ثبت کنید.
بهینهسازی Query؛ ابتدا کار غیرضروری را حذف کنید
سریعترین عملیات، عملیاتی است که اصلاً انجام نمیشود. پیش از افزایش منابع، ببینید آیا Query باید اجرا شود، آیا همه ردیفها و ستونها لازماند و آیا نتیجه را میتوان در یک درخواست Batch کرد.
| الگوی مسئلهدار | چرا هزینهساز است؟ | اصلاح محتمل |
|---|---|---|
SELECT * | انتقال و Decode ستونهای غیرلازم؛ دشوارشدن Covering | فقط ستونهای قرارداد پاسخ |
LIKE '%عبارت%' | B-tree معمولاً Prefix نامعلوم را خوب استفاده نمیکند | Full-text/Search engine یا نیاز دقیقتر |
| تابع روی ستون فیلتر | ممکن است Predicate قابل Index نباشد | Range صریح یا Functional Index متناسب با موتور |
OFFSET 500000 | ردشدن تعداد زیادی ردیف پیش از پاسخ | Keyset/Cursor pagination |
| Query داخل Loop | Round-trip و اجرای تکراری | Batch، eager loading یا Join کنترلشده |
| گزارش Aggregate روی OLTP | رقابت CPU/I/O با تراکنش کاربر | Read model، replica یا pipeline تحلیلی |
Pagination با Cursor بهجای Offset عمیق
SELECT id, total_amount, status, created_at
FROM orders
WHERE merchant_id = ?
AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;Cursor باید ترتیب قطعی داشته باشد؛ افزودن id از ابهام بین رکوردهای همزمان جلوگیری میکند. رفتار درج جدید، حذف و حرکت بین صفحات را با تست Contract بررسی کنید.
طراحی Index؛ ترتیب ستونها از نام ستون مهمتر است
Index یک کپی مرتب و هزینهدار از بخشی از داده است. سرعت Read را میخرد، اما فضا، Cache و Write amplification مصرف میکند. مستندات Optimization and Indexes در MySQL نیز هشدار میدهد Indexهای غیرضروری فضای ذخیرهسازی و هزینه Insert/Update/Delete را افزایش میدهند.
| پرسش | آنچه باید ثبت شود |
|---|---|
| کدام Query مالک این Index است؟ | Fingerprint، Journey و فراوانی |
| ترتیب ستونها چرا این است؟ | Equality، Range، Sort و Selectivity واقعی |
| آیا Index مشابه داریم؟ | Prefixهای همپوشان و Usage |
| هزینه نوشتن چیست؟ | Latency و Throughput قبل/بعد روی INSERT/UPDATE/DELETE |
| ساخت Index چه Lockی میگیرد؟ | نسخه موتور، الگوریتم DDL، زمان و فضای موقت |
| Rollback چیست؟ | حذف امن، بازگشت Query و معیار توقف |
قاعده «ستون با بیشترین Selectivity همیشه اول» عمومی و کافی نیست. ترتیب Index باید با Prefix قابلاستفاده، شروط Equality/Range، Sort، نوع موتور و Queryهای واقعی سنجیده شود. Index پوشاننده نیز ممکن است Read را بهتر کند، ولی با ستونهای اضافی بزرگتر و گرانتر میشود.
چگونه Index اضافی را حذف کنیم؟
- Usage را در بازهای شامل چرخههای ماهانه، گزارشها و کمپین ثبت کنید.
- Indexهای Duplicate یا Prefix همپوشان را کاندید کنید، نه اینکه فوراً حذف کنید.
- وابستگی Constraint، Foreign Key و Queryهای نادر اما حیاتی را بررسی کنید.
- در صورت پشتیبانی، با سازوکار Invisible/Hypothetical یا محیط آزمایش اثر را بسنجید.
- در Maintenance window با Backup، مانیتورینگ و Rollback تغییر دهید.
مشکل N+۱ در ORM؛ یک صفحه، صدها Query
N+۱ وقتی رخ میدهد که یک Query فهرست N شیء را میگیرد و سپس برای هر شیء Query دیگری اجرا میشود. هر Query شاید سریع باشد، اما مجموع Round-trip و Connection time صفحه را کند میکند. این مشکل در ORMها، Resolverهای GraphQL، قالبهای WordPress و Serializers رایج است.
| روش | مزیت | ریسک |
|---|---|---|
| Eager loading | کاهش تعداد Query | Join انفجاری یا داده اضافه |
| Batch loader | تجمیع شناسهها در یک Query | پیچیدگی Cache و ترتیب پاسخ |
| Join | یک Round-trip | تکرار ردیف و Cardinality بالا |
| Precomputed read model | Read سریع و ساده | تأخیر همگامسازی و Consistency |
عدد Query بهازای هر Request را در تست Integration ثبت کنید. یک Assertion ساده میتواند بازگشت N+۱ را پیش از Production متوقف کند. برای APIهای چندلایه، راهنمای طراحی، امنیت و قابلیت اطمینان API به قرارداد Timeout، Idempotency و Observability کمک میکند.
Schema و نوع داده؛ صحت قبل از سرعت
نرمالسازی و Denormalization دو مذهب رقیب نیستند. مدل تراکنشی معمولاً از مرزهای روشن و یک منبع حقیقت سود میبرد؛ Read model یا ستون مشتقشده وقتی ارزش دارد که هزینه Join/Aggregation اثبات شده و سازوکار همگامسازی و Reconciliation مشخص باشد.
- نوع داده متناسب: پول را با نوع دقیق و واحد روشن ذخیره کنید؛ Float برای مبلغ تراکنش مناسب نیست.
- زمان: Instant را با Time zone قراردادشده ذخیره و تقویم شمسی را در لایه نمایش مدیریت کنید؛ مرز روز تهران را صریح تست کنید.
- شناسه: نوع کلید بر اندازه Index، Locality و توزیع اثر دارد؛ انتخاب UUID یا عدد ترتیبی باید آگاهانه باشد.
- قیدها: Unique، Foreign Key و Check فقط هزینه نیستند؛ بخشی از دفاع صحت دادهاند.
- JSON: برای انعطاف مفید است، اما فیلتر پرتکرار و رابطه حیاتی را بدون برنامه Index و Validation در Blob پنهان نکنید.
در سامانه فروش ایرانی، Constraint جلوگیری از ثبت دوباره مرجع پرداخت یا Reservation بیشازموجودی ممکن است ارزشمندتر از چند میلیثانیه کاهش Write باشد. Performance هیچوقت مجوز شکستن Invariant نیست.
تراکنش و Lock؛ کندیای که با Index تنها حل نمیشود
تراکنش بلند، ترتیب متفاوت قفلگیری و تماس با سرویس بیرونی داخل Transaction میتواند زنجیره انتظار بسازد. Query قربانی شاید ساده باشد؛ مشکل، Query یا Job دیگری است که Lock را نگه داشته.
| الگو | پیامد | راه اصلاح |
|---|---|---|
| درخواست درگاه داخل Transaction | Lock تا پایان شبکه خارجی باز میماند | مرز تراکنش کوتاه، state machine و Idempotency |
| بهروزرسانی منابع با ترتیب متفاوت | Deadlock | ترتیب قفلگیری یکسان و Retry محدود |
| Batch عظیم | Lock/WAL/binlog و Lag زیاد | Chunk کوچک با checkpoint |
| تراکنش idle | نگهداری Snapshot/Lock و اختلال نگهداری | Timeout و مدیریت lifecycle Connection |
| Hot row شمارنده | صف سریالی روی یک ردیف | Sharding منطقی، تجمیع یا طراحی رویداد |
Deadlock را فقط با Retry بینهایت پنهان نکنید. Retry باید فقط برای خطای موقت، با سقف تلاش، Backoff/Jitter و عملیات Idempotent باشد. Root cause را از Lock graph و ترتیب عملیات استخراج کنید.
Connection Pool؛ بیشتر همیشه بهتر نیست
Pool اتصال، هزینه ساخت اتصال را کم و همزمانی را محدود میکند. اگر اندازه هر Instance را مستقل بالا ببرید، مجموع اتصالها ممکن است از ظرفیت دیتابیس عبور کند:
حداکثر اتصال بالقوه = تعداد Instance × Pool هر Instance + Jobها + ابزارهای عملیاتی + حاشیه
| معیار | تفسیر | اقدام محتمل |
|---|---|---|
| Pool wait بالا، DB آرام | Pool کوچک یا Connection leak | Trace checkout، timeout و leak detection |
| Connection زیاد، Throughput ثابت | ازدحام و Context switching | کاهش concurrency و صف کنترلشده |
| Idle زیاد | Pool بیشازنیاز یا autoscaling ناسازگار | min/max و idle timeout |
| Timeout در Deploy | Connection storm | Warm-up پلکانی و startup jitter |
Pool را با تست بار واقعی تیون کنید. صف کوتاه و قابلمشاهده اغلب از ورود concurrency نامحدود به دیتابیس سالمتر است.
حافظه، Buffer و Storage؛ تنظیمات را از اینترنت کپی نکنید
Buffer pool و Shared buffers میتوانند Read فیزیکی را کم کنند، اما حافظه کل باید با Connectionها، Sort/Hash، Cache سیستمعامل، Replica و سایر Processها جمع شود. درصد ثابت مثل «همیشه ۸۰٪ RAM» بدون شناخت محیط، Container و Managed Database نسخه قابلاعتماد نیست.
| Signal | فرضیه | آزمایش |
|---|---|---|
| Read I/O و latency بالا | Working set در حافظه جا نمیشود یا Query Scan دارد | Plan، cache miss، storage latency |
| Temp file زیاد | Sort/Hash از حافظه عبور میکند | Query-specific temp و حجم میانی |
| Swap/OOM | جمع تخصیصها بیشتر از RAM | Peak memory زیر concurrency واقعی |
| fsync latency | محدودیت storage یا burst credit | Metric سرویس ذخیرهسازی و Write latency |
SSD بدیهی به نظر میرسد، اما نوع Volume، IOPS تضمینشده، Throughput، latency دمبلند، Queue depth و Backup هم مهماند. ارتقای Storage بدون اصلاح Scan ممکن است فقط هزینه خطا را بیشتر کند.
Cache؛ کاهش بار با قرارداد Freshness و Invalidation
Cache برای دادهای مفید است که زیاد خوانده، کمتر تغییر و در برابر کمی کهنگی تحملپذیر باشد. قیمت لحظهای، موجودی قابلفروش و وضعیت پرداخت قرارداد متفاوتی از فهرست دستهبندی دارند. قبل از Redis، این پنج پرسش را پاسخ دهید: مالک داده کیست؟ Cache key چیست؟ TTL چقدر است؟ چه رویدادی Invalidate میکند؟ در Cache miss یا خرابی چه میشود؟
مستندات رسمی Redis توضیح میدهد که maxmemory و Eviction policy باید متناسب با نقش Cache تنظیم شوند؛ بدون محدودیت و سیاست روشن، حافظه یا Write میتواند مسئلهساز شود: Redis key eviction.
| الگو | مناسب برای | ریسک اصلی |
|---|---|---|
| Cache-aside | Read پرتکرار با کنترل اپلیکیشن | Stale و Stampede هنگام Miss |
| Write-through | نیاز به Cache گرم پس از Write | Latency و پیچیدگی خطا |
| Event invalidation | تغییراتی با رویداد دامنه روشن | گمشدن Event و نیاز به Reconciliation |
| Short TTL | داده کمریسک و تغییرپذیر | Miss زیاد یا کهنگی کوتاه |
برای معماری کامل Browser/CDN/Application/Redis، مقاله کش چیست و چگونه طراحی میشود را بخوانید. اگر سایت WordPress است، Runbook تخصصی کش WordPress و رفع نسخه قدیمی مرز Page Cache و Object Cache را توضیح میدهد.
نگهداری MySQL و PostgreSQL؛ دستور یکسان ندارند
عبارت کلی «هر هفته دیتابیس را Optimize کن» میتواند گمراهکننده باشد. موتور، Storage engine، الگوی Update/Delete و نسخه تعیین میکنند چه نگهداری لازم است. Job نگهداری نیز باید SLO، مدت، Lock، فضای آزاد و اثر Replica داشته باشد.
MySQL/InnoDB
- Slow Log و Performance Schema را برای رتبهبندی Queryها به کار ببرید، نه ثبت بیهدف همه چیز با Retention نامحدود.
- Statistics و Plan را پس از تغییر بزرگ داده یا Index بررسی کنید؛
ANALYZE TABLEرا با شناخت اثر نسخه اجرا کنید. OPTIMIZE TABLEنسخه عمومی برای هر کندی نیست؛ ممکن است Table rebuild، فضا و زمان قابلتوجه بخواهد.- رشد Undo/Redo، Buffer pool، History و Replication lag را همراه با بار Write ببینید.
PostgreSQL
در PostgreSQL، MVCC باعث میشود نسخههای مرده ردیفها تا Vacuum باقی بمانند. Autovacuum بخشی حیاتی از سلامت است؛ خاموشکردن آن برای رفع بار کوتاهمدت معمولاً بدهی خطرناک میسازد. مستندات رسمی تفاوت VACUUM عادی و VACUUM FULL را روشن میکند: نوع FULL جدول را بازنویسی میکند، کندتر است و Lock انحصاری میخواهد. منبع: VACUUM در PostgreSQL.
- Dead tuples، زمان آخر Vacuum/Analyze و جدولهای دارای Bloat را پایش کنید.
- Long transaction و Replication slot رهاشده میتواند پاکسازی را عقب بیندازد.
- تنظیم Autovacuum را برای جدول پرWrite بهصورت Table-specific و مبتنی بر شواهد انجام دهید.
VACUUM FULLرا کار روزمره تلقی نکنید؛ قفل و فضای لازم را در Runbook بیاورید.
| کار نگهداری | شرط اجرا | Guardrail |
|---|---|---|
| Update statistics | تغییر معنادار توزیع یا Plan نامناسب | Plan قبل/بعد و بار محدود |
| پاکسازی/Archive | Retention مصوب و داده قابلحذف | Legal hold، Backup و حذف Chunked |
| Reindex/Rebuild | Bloat/Corruption/شاهد موتورمحور | Lock، فضای موقت، Replica و Rollback |
| Upgrade موتور | نسخه پشتیبانیشده و نیاز امنیت/کارایی | Compatibility، restore rehearsal و canary |
بهینهسازی دیتابیس WordPress و WooCommerce
در WordPress، بزرگشدن جدول لزوماً علت کندی نیست. ابتدا Query کند و Plugin/Route مالک آن را پیدا کنید. جدولهای wp_options، wp_postmeta، Scheduler/Queue، Session، Log و Revision میتوانند رشد کنند؛ اما حذف مستقیم ردیفها بدون شناخت مالکیت Plugin ممکن است داده یا قابلیت بازیابی را از بین ببرد.
| ناحیه | ریسک رایج | اقدام امن |
|---|---|---|
wp_options | autoload حجیم یا گزینه یتیم | اندازه و مالک Plugin را بسنجید؛ حذف فقط با Backup |
| Post meta | Query روی meta_value و Join زیاد | Query Monitor/APM، بازطراحی داده در نیاز پرتکرار |
| Revisions | رشد ذخیرهسازی | Retention آینده را تنظیم؛ حذف گذشته با تأیید محتوا |
| Transients | Expired یا Invalidation ناقص | مالکیت و Object Cache را بفهمید؛ پاکسازی کنترلشده |
| Action Scheduler | Job عقبافتاده یا Log انباشته | علت Failure، Retention و Queue health |
| WooCommerce sessions | رشد و پاکسازی ناقص | نسخه/تنظیمات رسمی و تست سبد/پرداخت |
دستور wp db optimize در WP-CLI از ابزارهای خود دیتابیس استفاده میکند و باید با Backup و شناخت موتور اجرا شود؛ مستندات رسمی: WP-CLI db optimize. محدودکردن Revisionهای آینده نیز در راهنمای wp-config وردپرس مستند شده است.
Runbook امن برای سایت WordPress
- Backup بگیرید و Restore را روی محیط جدا واقعاً تمرین کنید.
- Slow Query/APM را با Route، Plugin، Theme و Cron مرتبط کنید.
- اندازه جدول، رشد روزانه، autoload و Job backlog را ثبت کنید.
- یک علت و یک تغییر کوچک انتخاب کنید؛ پاکسازی انبوه چند جدول نکنید.
- روی Staging با کپی Sanitized داده، صحت Checkout، Login، Search و Admin را تست کنید.
- در بازه کمترافیک اجرا و Error، p95، Lock، Disk و Queue را مانیتور کنید.
- نتیجه، مالک و تاریخ بازبینی بعدی را ثبت کنید.
ملاحظات سایت ایرانی؛ شبکه، تقویم، پرداخت و زیرساخت
معماری برای کاربران ایران باید فرضیات خود را صریح کند. فاصله شبکه بین App و Database، کیفیت Route، محدودیت سرویس خارجی، تغییر IP، تقویم و ساعت تهران، پیامک و درگاه پرداخت میتوانند در Trace شبیه «کندی دیتابیس» دیده شوند.
- هممکانی: App و Primary Database را بدون دلیل در دو شبکه دور قرار ندهید؛ RTT در N+۱ ضرب میشود.
- درگاه پرداخت: تماس شبکه را داخل تراکنش دیتابیس نگه ندارید؛ State و Callback را Idempotent طراحی کنید.
- موجودی: Cache را منبع حقیقت موجودی قابلفروش نکنید مگر قرارداد Consistency و Reservation روشن باشد.
- تقویم و Time zone: بازه گزارش «امروز» را با مرز تهران و DST تاریخی تست کنید؛ نمایش شمسی را از Instant ذخیرهشده جدا نگه دارید.
- داده حساس: شماره موبایل، کد ملی و Reference پرداخت را در Query log و APM Mask کنید.
- Backup خارج از Failure domain: نسخه بازیابیشدنی را جدا از همان حساب/سرور نگه دارید و RPO/RTO را تست کنید.
اگر رشد تیم و سیستم بحث جداسازی دیتابیس یا سرویسها را ایجاد کرده، مقاله مونولیت یا میکروسرویس کمک میکند از ساخت Distributed Monolith جلوگیری کنید. Database per Service بدون مرز دامنه، مالکیت داده، Observability و عملیات بالغ معمولاً هزینه را چندبرابر میکند.
امنیت پایگاه داده بخشی از Performance Engineering است
Query سریع اما آسیبپذیر خروجی قابلقبول نیست. Parameterized Query، حداقل دسترسی، تفکیک کاربر Migration از Runtime، TLS، Secret rotation و Audit باید همراه Optimization باقی بمانند. OWASP ساخت Query با String concatenation را ناامن میداند و Prepared/Parameterized Query و Least Privilege را توصیه میکند: SQL Injection Prevention Cheat Sheet.
| میانبر خطرناک | پیامد | جایگزین |
|---|---|---|
| حذف Validation برای سرعت | داده ناسالم و Injection | Validation مرزی + Parameterization |
| دادن DBA به اپلیکیشن | Blast radius بسیار بزرگ | Role حداقلی و جدا برای Migration |
| ثبت Bind value کامل | نشت PII در Log | Fingerprint و Redaction |
| غیرفعالکردن TLS | ریسک شنود/دستکاری | کاهش Round-trip با Pool، نه حذف امنیت |
استقرار امن تغییرات دیتابیس در Production
تغییر Query کوچک ممکن است ساده Rollback شود؛ ساخت Index یا Migration جدول بزرگ چنین نیست. قبل از DDL، رفتار دقیق نسخه موتور، Lock level، Disk headroom، Replica lag و زمان اجرا را بررسی کنید.
| مرحله | خروجی اجباری | معیار توقف |
|---|---|---|
| Baseline | Window، load، p95/p99، calls، plan | داده نماینده نیست |
| فرضیه | علت، تغییر، انتظار عددی | علت با شاهد ناسازگار است |
| آزمایش | داده/بار نزدیک Production و تست صحت | Result mismatch یا Regression نوشتن |
| Canary | درصد محدود Route/User/Instance | Error/lock/lag از Guardrail عبور کند |
| Rollout | گامهای تدریجی و مالک حاضر | SLO یا ظرفیت تهدید شود |
| بازبینی | Plan و معیار ۲۴ ساعت/۷ روز بعد | سود پایدار نیست |
الگوی Migration سازگار با نسخههای همزمان
- Expand: ستون/جدول جدید را بهشکل سازگار اضافه کنید.
- Dual-read یا Dual-write کنترلشده: فقط اگر لازم است و با Telemetry/مقایسه صحت.
- Backfill Chunked: با Rate limit، checkpoint و توقفپذیری.
- Switch: خواندن را با Feature flag منتقل کنید.
- Verify: Count، checksum یا invariantهای کسبوکار را تطبیق دهید.
- Contract: وابستگی قدیمی را پس از عبور یک بازه امن حذف کنید.
این کار نوعی مدیریت بدهی و ریسک تغییر است؛ راهنمای شناسایی و مدیریت بدهی فنی برای ثبت مالک، بهره و زمان بازپرداخت مفید است.
ماتریس اولویتبندی؛ اول کدام مشکل را حل کنیم؟
فهرست Queryها را فقط با Latency مرتب نکنید. یک امتیاز قابلتوضیح بسازید:
Priority = User impact × Frequency × Risk × Confidence ÷ Effort
| کاندید | اثر | اطمینان | هزینه/ریسک | اولویت |
|---|---|---|---|---|
| N+۱ در صفحه محصول پرترافیک | زیاد | زیاد؛ Trace روشن | کم تا متوسط | فوری |
| Index برای گزارش ماهانه | کم | متوسط | هزینه Write/فضا | بعدی |
| Migration کل دیتابیس به NoSQL | نامعلوم | کم | بسیار زیاد | رد تا اثبات |
| کوتاهکردن Transaction پرداخت | بسیار زیاد | زیاد؛ Lock graph | متوسط | فوری با تست |
نقشه ۳۰روزه بهینهسازی پایگاه داده
| بازه | کار | تحویلدادنی |
|---|---|---|
| روز ۱ تا ۳ | Journey/SLO، Trace و Baseline | داشبورد p95/p99 و سهم DB |
| روز ۴ تا ۷ | Top Query و Wait analysis | ۱۰ Fingerprint با مالک و اثر |
| هفته دوم | Plan، N+۱، Query/Index آزمایشی | Benchmark و تست صحت قبل/بعد |
| هفته سوم | Lock، Pool، Cache و Jobها | Guardrail ظرفیت و Runbook Incident |
| هفته چهارم | Canary، Rollout و مستندسازی | نتیجه عددی، Rollback و Backlog بعدی |
اشتباهات رایج در بهینهسازی دیتابیس
- شروع با ابزار بهجای سؤال: داشبورد زیاد بدون Journey و SLO تصمیم نمیسازد.
- بهینهکردن میانگین: p95/p99 و کاربران پیک نادیده میمانند.
- ساخت Index بر اساس حدس: Write، Disk و Planهای دیگر Regression میگیرند.
- اجرای EXPLAIN ANALYZE بیمحابا: Query واقعاً اجرا میشود و ممکن است سنگین یا نوشتنی باشد.
- کشکردن داده حساس به صحت: موجودی و پرداخت Stale میشود.
- پاکسازی مستقیم جدول WordPress: مالکیت Plugin و Recovery نادیده میماند.
- افزایش بیحد Connection: صف را از App به داخل دیتابیس منتقل میکند.
- مهاجرت فناوری بهعنوان درمان: Query بد در موتور جدید هم Query بد میماند.
- نداشتن Rollback: تغییر ظاهراً سریع به Incident طولانی تبدیل میشود.
چکلیست نهایی بهینهسازی پایگاه داده
- Journey حیاتی و SLO آن مشخص است.
- سهم دیتابیس از latency با Trace یا اندازهگیری معتبر جدا شده است.
- Queryها با Fingerprint و بر اساس total time، p95، calls و risk رتبهبندی شدهاند.
- Plan قبل و بعد با داده و بار نماینده ثبت شده است.
- تست صحت نتیجه در کنار Benchmark وجود دارد.
- هزینه Read، Write، Disk، Lock و Replica برای Index سنجیده شده است.
- N+۱، Pagination عمیق و Queryهای Loop بررسی شدهاند.
- Transaction کوتاه است و تماس خارجی داخل آن انجام نمیشود.
- مجموع Connection همه Instanceها از ظرفیت برنامهریزیشده عبور نمیکند.
- Cache key، TTL، Invalidation، Stampede و Failure mode مستند است.
- Backup قابلبازیابی و Restore rehearsal وجود دارد.
- DDL/Migration دارای Canary، Guardrail، مالک و Rollback است.
- Query log و APM داده حساس را Mask میکنند.
- نتیجه ۲۴ ساعت و ۷ روز بعد دوباره سنجیده میشود.
پرسشهای متداول
از کجا بفهمیم کندی سایت از پایگاه داده است؟
با Trace درخواست یا APM، زمان Database span را از App، شبکه و سرویس بیرونی جدا کنید. سپس همان بازه را با Top Query، Wait، CPU، I/O و Connection تطبیق دهید. TTFB بالا بهتنهایی مدرک کافی نیست.
اول Query را اصلاح کنیم یا Index بسازیم؟
Plan و الگوی فراخوانی تعیین میکند. حذف N+۱، ستون و ردیف اضافی یا Pagination بد معمولاً پیش از Index ارزش بررسی دارد. اگر Access path مناسب وجود ندارد، Index با معیار قبل/بعد و سنجش هزینه Write بسازید.
آیا EXPLAIN ANALYZE روی Production امن است؟
همیشه نه. این دستور معمولاً Query را واقعاً اجرا میکند؛ روی Query سنگین یا نوشتنی میتواند داده یا بار را تغییر دهد. رفتار دقیق نسخه موتور را بخوانید، ابتدا Staging/Replica و سپس Read-only کنترلشده با Timeout و دسترسی محدود استفاده کنید.
آیا Redis جایگزین بهینهسازی دیتابیس است؟
خیر. Redis میتواند Read مناسب را از Primary دور کند، اما Query بد، Invalidation ناقص و Stampede را حل نمیکند. ابتدا قرارداد صحت و الگوی دسترسی را مشخص کنید؛ سپس Cache را با Hit ratio، stale rate و بار Database بسنجید.
بهینهسازی دیتابیس WordPress را هر چند وقت انجام دهیم؟
تقویم ثابت عمومی وجود ندارد. رشد جدول، Slow Query، autoload، Queue backlog و Retention را پایش کنید و فقط با علت روشن اقدام کنید. پیش از حذف Revision، Transient، Session یا جدول Plugin، Backup و تست Restore داشته باشید.
جمعبندی
بهینهسازی پایگاه داده پروژه خرید RAM یا افزودن Index نیست؛ یک روش تصمیمگیری است. مسیر حیاتی را انتخاب کنید، Baseline بسازید، Query و Wait پرهزینه را پیدا کنید، Plan را با Actual data بخوانید، یک فرضیه کمریسک را آزمایش و با Canary منتشر کنید. موفقیت وقتی ثابت میشود که Journey کاربر سریعتر و پایدارتر شده، صحت داده حفظ شده و هزینه یا ریسک به لایه دیگری منتقل نشده باشد.
