معماری MySQL و مقدمه پیشرفته

۱.۱ مقدمه‌ای بر MySQL در سطح حرفه‌ای

اگر به این آموزش رسیده‌اید، احتمالاً با اصول SQL آشنا هستید: می‌توانید جدول بسازید، SELECT، INSERT، UPDATE بنویسید و حتی JOIN‌های ساده انجام دهید. اما تفاوت یک توسعه‌دهنده Junior با یک توسعه‌دهنده Senior یا DBA حرفه‌ای، در درک درون MySQL است؛ اینکه چطور query شما را اجرا می‌کند، داده را کجا نگه می‌دارد، و چرا گاهی یک query یک‌ثانیه‌ای ناگهان ۳۰ ثانیه طول می‌کشد.

این فصل پایه‌ای است برای ۱۴ فصل بعدی. بدون فهم معماری MySQL، تمام تکنیک‌های بهینه‌سازی فقط «دستور‌العمل کور» می‌شوند که گاهی کار می‌کنند و گاهی نه.

۱.۲ معماری کلی MySQL Server

MySQL یک معماری لایه‌ای دارد. وقتی یک query می‌فرستید، از این لایه‌ها عبور می‌کند:

┌─────────────────────────────────────────┐
│   Client (PHP, Python, mysql CLI)       │
└──────────────────┬──────────────────────┘
                   │ TCP/3306 یا Unix Socket
┌──────────────────▼──────────────────────┐
│   Connection Layer                      │
│   • Authentication                      │
│   • Connection Pool / Threads           │
└──────────────────┬──────────────────────┘
┌──────────────────▼──────────────────────┐
│   SQL Layer (Server)                    │
│   • Parser → Syntax Tree                │
│   • Optimizer → Execution Plan          │
│   • Query Cache (تا MySQL 5.7)          │
│   • Caches & Buffers                    │
└──────────────────┬──────────────────────┘
┌──────────────────▼──────────────────────┐
│   Storage Engine API (پلاگین)           │
│   ┌─────────┐ ┌────────┐ ┌──────────┐   │
│   │ InnoDB  │ │ MyISAM │ │ Memory   │   │
│   └─────────┘ └────────┘ └──────────┘   │
│   ┌──────────┐ ┌─────────┐ ┌─────────┐  │
│   │ Aria(MD) │ │ CSV     │ │ Archive │  │
│   └──────────┘ └─────────┘ └─────────┘  │
└──────────────────┬──────────────────────┘
                   ▼
              Disk / Memory
    

۱.۲.۱ Connection Layer

هر اتصال یک thread جداگانه می‌گیرد. در MySQL پیش‌فرض حداکثر max_connections=151 است. اگر اپلیکیشن شما بدون connection pool هزاران کانکشن باز کند، MySQL با خطای Too many connections پاسخ می‌دهد.

SQL📋 کپی
-- بررسی تعداد کانکشن‌ها
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

-- بررسی کانکشن‌های فعلی
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

۱.۲.۲ SQL Layer (مغز سرور)

این لایه چهار وظیفه اصلی دارد:

• Parser: تبدیل SQL به Abstract Syntax Tree (AST)
• Preprocessor: چک کردن وجود جدول‌ها، ستون‌ها، privilege‌ها
• Optimizer: انتخاب بهترین Execution Plan (مهم‌ترین قسمت!)
• Executor: اجرای plan با کمک Storage Engine

۱.۲.۳ Storage Engine Layer

این بخش منحصر به فرد MySQL است. در PostgreSQL یا Oracle، storage engine یکی است. در MySQL، شما می‌توانید برای هر جدول storage engine متفاوت انتخاب کنید:

SQL📋 کپی
-- لیست engine‌های نصب‌شده
SHOW ENGINES;

-- ساخت جدول با engine مشخص
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE temp_session (
    sid VARCHAR(64) PRIMARY KEY,
    data BLOB
) ENGINE=MEMORY;

-- تغییر engine یک جدول موجود
ALTER TABLE old_table ENGINE=InnoDB;

۱.۳ مقایسه عمیق Storage Engine‌ها

InnoDB – انتخاب پیش‌فرض و درست

• Transaction-safe (ACID)
• Row-level locking (همزمانی بالا)
• Foreign Keys
• Crash recovery
• Clustered Index (داده فیزیکاً بر اساس Primary Key مرتب است)
• Buffer Pool برای cache در RAM

MyISAM – منسوخ شده، اما هنوز در سیستم‌های قدیمی

• سریع برای read-heavy workload‌های بدون transaction
• Table-level locking (مشکل در concurrency)
• بدون foreign key
• بدون crash recovery (داده می‌تواند corrupt شود)
• توصیه: برای پروژه جدید استفاده نکنید

سایر Engine‌های مفید

۱.۴ InnoDB از نزدیک: Buffer Pool و Log Files

InnoDB قلب MySQL مدرن است. سه ساختار حیاتی دارد:

۱. Buffer Pool

یک حافظه RAM بزرگ که صفحات (page) دیتا و ایندکس InnoDB در آن cache می‌شوند. اگر داده در Buffer Pool باشد، query از RAM جواب می‌دهد (نانوثانیه)؛ اگر نباشد، از دیسک می‌خواند (میلی‌ثانیه = ۱۰۰۰ برابر کندتر).

SQL📋 کپی
-- بررسی اندازه Buffer Pool
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- بررسی hit rate (باید نزدیک ۱۰۰٪ باشد)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- محاسبه: 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

۲. Redo Log (ib_logfile)

قبل از اعمال تغییرات روی Buffer Pool، InnoDB آنها را در redo log می‌نویسد. این تضمین می‌کند که اگر سرور crash کند، تغییرات commit شده از دست نروند (D در ACID).

۳. Undo Log

برای ROLLBACK و MVCC (Multi-Version Concurrency Control). به همین خاطر می‌توانید SELECT بزنید همزمان با اینکه کس دیگری UPDATE می‌کند، بدون lock شدن.

۱.۵ Query Cache – چرا حذف شد؟

تا MySQL 5.7، یک Query Cache وجود داشت که نتیجه SELECT‌های یکسان را cache می‌کرد. اما در MySQL 8.0 کاملاً حذف شد. چرا؟

• Invalidation: هر INSERT/UPDATE/DELETE روی جدول، تمام cache مربوط به آن جدول را پاک می‌کرد
• Mutex Contention: در workload با concurrency بالا، خود cache تبدیل به bottleneck می‌شد
• عدم سود واقعی: در workload‌های real-world معمولاً hit rate زیر ۲۰٪ بود

MariaDB 10.4 (موجود در XAMPP) هنوز Query Cache دارد، اما توصیه این است که آن را خاموش کنید:

my.cnf / my.ini📋 کپی
[mysqld]
query_cache_type = 0
query_cache_size = 0

به‌جای آن، از cache در سطح اپلیکیشن استفاده کنید (Redis، Memcached، یا حتی WordPress object cache). این روش هم سریع‌تر است و هم انعطاف بیشتری می‌دهد.

۱.۶ MySQL در مقابل MariaDB – خلاصه عملی

محمدعلی، چون شما با XAMPP کار می‌کنید (که MariaDB دارد) این موضوع برایتان مهم است:

۱.۷ ابزارهای ضروری توسعه‌دهنده MySQL

• mysql CLI: ابزار رسمی command-line. ضروری برای DBA
• phpMyAdmin: در XAMPP موجود است؛ خوب برای کارهای روزمره، نه برای performance tuning
• MySQL Workbench: ابزار رسمی Oracle، ER Diagram خوبی دارد
• HeidiSQL: سبک، Windows-native، رایگان
• DBeaver: چندپلتفرمی، رایگان، با MySQL/MariaDB/PostgreSQL کار می‌کند
• Percona Toolkit: مجموعه ابزار CLI برای DBA‌های حرفه‌ای (pt-query-digest، pt-online-schema-change، …)
• mysqldumpslow / pt-query-digest: تحلیل Slow Query Log

۱.۸ خلاصه و گام بعدی

در این فصل یاد گرفتیم:

• MySQL سه لایه دارد: Connection، SQL، Storage Engine
• InnoDB انتخاب پیش‌فرض است؛ ACID، Row Lock، MVCC
• Buffer Pool مهم‌ترین عامل سرعت است (۵۰-۷۵٪ RAM)
• Query Cache منسوخ شده؛ از Redis/Memcached استفاده کنید
• MariaDB و MySQL در ۹۵٪ موارد سازگار هستند

در فصل بعد، نصب و پیکربندی حرفه‌ای را یاد می‌گیریم؛ از تنظیم my.cnf گرفته تا حل مشکل encoding فارسی که در XAMPP خیلی شایع است.