Partitioning و Sharding در MySQL

۱۰.۱ Partitioning – چه زمانی، چرا؟

Partitioning یعنی تقسیم یک جدول منطقی به چند بخش فیزیکی. هر partition مثل یک sub-table است که MySQL می‌تواند جداگانه مدیریت کند.

چه زمانی Partition کنیم؟

• جدول‌های با ۱۰+ میلیون رکورد
• کوئری‌ها معمولاً روی یک محدوده مشخص (تاریخ، عددی) اجرا می‌شوند
• نیاز به حذف سریع داده‌های قدیمی (DROP PARTITION به‌جای DELETE)
• توزیع I/O روی چند دیسک

چه زمانی Partition نکنیم؟

• جدول کوچک (کمتر از میلیون)
• کوئری‌ها روی همه پارتیشن‌ها (هیچ سودی ندارد)
• نیاز به Foreign Key (Partition محدودیت دارد)

۱۰.۲ RANGE Partitioning – بر اساس محدوده

پر کاربردترین نوع. ایده‌آل برای جدول‌های با ستون تاریخ:

SQL📋 کپی
CREATE TABLE orders_log (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id INT,
    total BIGINT,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)  -- created_at باید در PK باشد
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- یا با RANGE COLUMNS برای تاریخ کامل
CREATE TABLE events (
    id BIGINT,
    event_date DATE NOT NULL,
    PRIMARY KEY (id, event_date)
)
PARTITION BY RANGE COLUMNS(event_date) (
    PARTITION p_q1_2026 VALUES LESS THAN ('2026-04-01'),
    PARTITION p_q2_2026 VALUES LESS THAN ('2026-07-01'),
    PARTITION p_q3_2026 VALUES LESS THAN ('2026-10-01'),
    PARTITION p_q4_2026 VALUES LESS THAN ('2027-01-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

عملیات روی پارتیشن‌ها

SQL📋 کپی
-- اضافه کردن پارتیشن جدید
ALTER TABLE orders_log
ADD PARTITION (PARTITION p2027 VALUES LESS THAN (2028));

-- حذف یک پارتیشن (سریع‌تر از DELETE!)
ALTER TABLE orders_log DROP PARTITION p2023;

-- بازسازی
ALTER TABLE orders_log REORGANIZE PARTITION p_future INTO (
    PARTITION p2027 VALUES LESS THAN (2028),
    PARTITION p2028 VALUES LESS THAN (2029),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- فقط روی پارتیشن خاص query
SELECT * FROM orders_log PARTITION (p2026)
WHERE user_id = 5;

-- TRUNCATE فقط یک پارتیشن
ALTER TABLE orders_log TRUNCATE PARTITION p2024;

۱۰.۳ LIST Partitioning – بر اساس لیست

وقتی مقادیر گسسته‌اند:

SQL📋 کپی
CREATE TABLE customers (
    id INT,
    name VARCHAR(100),
    province_id TINYINT NOT NULL,
    PRIMARY KEY (id, province_id)
)
PARTITION BY LIST (province_id) (
    PARTITION p_tehran VALUES IN (1, 2, 3),         -- استان‌های منطقه تهران
    PARTITION p_north VALUES IN (4, 5, 6, 7),        -- استان‌های شمال
    PARTITION p_south VALUES IN (10, 11, 12, 13),    -- استان‌های جنوب
    PARTITION p_central VALUES IN (8, 9, 14, 15)     -- مرکزی
);

-- LIST COLUMNS برای رشته
PARTITION BY LIST COLUMNS(country) (
    PARTITION p_iran VALUES IN ('IR'),
    PARTITION p_neighbors VALUES IN ('TR', 'IQ', 'AF'),
    PARTITION p_other VALUES IN ('US', 'GB', 'DE')
);

۱۰.۴ HASH و KEY Partitioning – توزیع مساوی

برای توزیع تقریباً یکسان داده روی پارتیشن‌ها:

SQL📋 کپی
-- HASH: hash(expression) % تعداد پارتیشن
CREATE TABLE messages (
    id BIGINT,
    user_id INT NOT NULL,
    body TEXT,
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH(user_id)
PARTITIONS 8;

-- KEY: مشابه HASH اما با MD5 داخلی، روی هر نوع ستون
CREATE TABLE sessions (
    sid CHAR(64) NOT NULL,
    user_id INT,
    data BLOB,
    PRIMARY KEY (sid)
)
PARTITION BY KEY(sid)
PARTITIONS 16;

HASH/KEY برای جدول‌هایی عالی است که می‌خواهید بار write/read را پخش کنید، اما هیچ سود از partition pruning نمی‌گیرند مگر در WHERE برابری روی ستون partition.

۱۰.۵ Sub-Partitioning

پارتیشن‌بندی دوسطحی:

SQL📋 کپی
CREATE TABLE order_items (
    id BIGINT,
    order_id INT,
    product_id INT,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at, product_id)
)
PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH(product_id)
SUBPARTITIONS 4 (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p2027 VALUES LESS THAN (2028)
);
-- نتیجه: 3 سال × 4 hash = 12 پارتیشن فیزیکی

۱۰.۶ Partition Pruning – بهره‌گیری از پارتیشن

وقتی WHERE شامل ستون partition باشد، MySQL فقط پارتیشن‌های مرتبط را می‌خواند:

SQL📋 کپی
-- بررسی pruning
EXPLAIN PARTITIONS
SELECT * FROM orders_log
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- ستون partitions: p2026 ✅ فقط یک پارتیشن

EXPLAIN PARTITIONS
SELECT * FROM orders_log WHERE user_id = 5;
-- ستون partitions: p2023,p2024,p2025,p2026,p_future
-- ❌ همه پارتیشن‌ها (چون user_id ستون partition نیست)

-- ❌ همه پارتیشن
WHERE user_id = 5

-- ✅ فقط پارتیشن مرتبط
WHERE user_id = 5 AND created_at >= '2026-01-01'

۱۰.۷ نگهداری خودکار پارتیشن‌ها

برای جدول‌های بر اساس تاریخ، باید به‌صورت ماهانه/سالانه پارتیشن جدید بسازید:

SQL📋 کپی
DELIMITER $$

CREATE PROCEDURE add_next_year_partition()
BEGIN
    DECLARE next_year INT;
    DECLARE partition_name VARCHAR(20);
    DECLARE less_than INT;

    SET next_year = YEAR(NOW()) + 1;
    SET partition_name = CONCAT('p', next_year);
    SET less_than = next_year + 1;

    SET @sql = CONCAT(
        'ALTER TABLE orders_log REORGANIZE PARTITION p_future INTO (',
        'PARTITION ', partition_name, ' VALUES LESS THAN (', less_than, '),',
        'PARTITION p_future VALUES LESS THAN MAXVALUE',
        ')'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- اجرا با Event در پایان هر سال
CREATE EVENT ev_add_partition
ON SCHEDULE EVERY 1 YEAR
STARTS '2026-12-15 02:00:00'
DO CALL add_next_year_partition();

۱۰.۸ Sharding - تقسیم بین چند سرور

Partitioning تقسیم یک جدول روی یک سرور است. Sharding تقسیم بین چند سرور.

استراتژی‌های Sharding

• Range Sharding: shard 1 برای user_id 1-1M، shard 2 برای 1M-2M
• Hash Sharding: shard = hash(user_id) % N
• Geographic: shard بر اساس کشور یا منطقه
• Lookup-based: یک جدول مرکزی mapping

ابزارهای Sharding برای MySQL

• Vitess: سیستم production-grade YouTube، تقسیم خودکار، scale لینوکس
• ProxySQL: middleware برای routing query‌ها
• MariaDB Spider: storage engine که داده را در سرورهای مختلف نگه می‌دارد
• Application-level: اپلیکیشن خودش shard را انتخاب می‌کند

Python - sharding ساده📋 کپی
SHARDS = {
    0: 'mysql://shard1.example.com',
    1: 'mysql://shard2.example.com',
    2: 'mysql://shard3.example.com',
    3: 'mysql://shard4.example.com',
}

def get_shard_for_user(user_id):
    shard_num = user_id % 4
    return SHARDS[shard_num]

def get_user(user_id):
    db = connect(get_shard_for_user(user_id))
    return db.query("SELECT * FROM users WHERE id = %s", user_id)

۱۰.۹ خلاصه فصل

• Partitioning برای جدول‌های ۱۰+ میلیونی با range query
• RANGE برای تاریخ، LIST برای دسته‌بندی، HASH/KEY برای توزیع یکنواخت
• PK باید شامل ستون(های) partition باشد
• EXPLAIN PARTITIONS را برای بررسی pruning استفاده کنید
• DROP PARTITION سریع‌تر از DELETE است
• Sharding چند سرور است؛ پیچیدگی زیاد دارد