אופטימיזציית MySQL לאתרים עם מאות מיליוני רשומות

Scaling Architecture מקצועי לאתרי תוכן, פורטלים, וידאו, נדל״ן ומאגרים גדולים

כאשר אתר גדל ממאות אלפי רשומות לעשרות ומאות מיליונים, האתגר כבר אינו רק “לשפר שאילתה”. בשלב הזה צריך לחשוב על ארכיטקטורה, על חלוקת עומסים, על הפרדת קריאות מכתיבות, על אחסון חכם, על ייבוא נתונים מאסיבי, ועל תכנון שיאפשר למערכת לגדול בלי שכל גידול בדאטה יהפוך את האתר לאיטי יותר.

באתרי תוכן גדולים מאוד – כמו מאגרי מומחים, פורטלי נדל״ן, ספריות וידאו, אינדקסים, אתרי חדשות, מערכות משפטיות, מערכות חיפוש פנימיות או אתרי קהילה – MySQL או MariaDB יכולים לשרת היטב גם בקנה מידה עצום, אבל רק אם בונים אותם נכון.

המדריך הזה מיועד בדיוק לשלב שבו טבלה אחת כבר לא “עוד טבלה”, אלא נכס תפעולי כבד של מאות מיליוני שורות.

---

1. מתי אתה כבר בבעיה ארכיטקטונית ולא רק בבעיה של שאילתה

יש כמה סימנים ברורים לכך שהמערכת כבר עברה את שלב האופטימיזציה הבסיסית:

• שאילתות שפעם רצו ב־50ms רצות עכשיו ב־2–10 שניות
• טעינת עמודי ארכיון, חיפוש או דשבורד נהיית איטית מאוד
• ייבוא מאסיבי של CSV או API update “נועל” את האתר
• כתיבות רבות פוגעות בקריאות
• גיבויים נהיים כבדים מדי
• ALTER TABLE על טבלה גדולה נהיה סיכון תפעולי
• OFFSET גדול בפייג׳ינציה הורס ביצועים
• האינדקסים עצמם הופכים עצומים
• יש טבלאות audit/logs/events שגדלות בלי סוף
• השרת לא נופל בגלל CPU בלבד, אלא בגלל I/O, locks, temp tables ו־buffer pressure

בשלב הזה לא מספיק רק להוסיף אינדקס. צריך לעבור לחשיבה של Scaling Architecture.

---

2. העקרונות החשובים באמת ב־Scaling

לפני הקוד, חשוב להבין את העקרונות:

להקטין סריקות

כל שאילתה צריכה לגעת בכמה שפחות שורות וכמה שפחות עמודות.

להקטין random I/O

לא כל עומס הוא CPU. בהרבה מקרים הבעיה היא גישה אקראית לדיסק, בנייה של temp tables, sorts ו־filesorts.

להפריד בין קריאה לכתיבה

מערכת עם הרבה קריאות ציבוריות ומעט יחסית כתיבות צריכה לרוב ארכיטקטורה של primary + replicas.

להימנע מ־hot tables

טבלה אחת שעושה הכל – גם תוכן, גם סטטיסטיקות, גם counters, גם status, גם audit – תהפוך מהר מאוד לצוואר בקבוק.

לא לבנות מערכת שתלויה ב־OFFSET

ככל שהעמוד עמוק יותר, כך השאילתה נהיית יקרה יותר.

לא לבצע שינויים כבדים בפרודקשן בלי תכנון

על טבלאות ענק, שינוי סכמה לא נכון יכול לגרום downtime ארוך.

---

3. תכנון טבלאות נכון לקנה מידה גדול

בטבלאות ענק כל בחירת טיפוס נתונים משפיעה.

דוגמה לטבלה לא טובה

CREATE TABLE content_items (
    id VARCHAR(255) NOT NULL,
    site_id VARCHAR(255) NOT NULL,
    category_id VARCHAR(255) NOT NULL,
    title LONGTEXT,
    slug LONGTEXT,
    status VARCHAR(255),
    views BIGINT,
    created_at DATETIME,
    updated_at DATETIME,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

הבעיה כאן היא לא רק “לא יפה”. הבעיה היא גודל, אינדקסים מנופחים, השוואות מחרוזת כבדות, ורוחב שורה מיותר.

דוגמה טובה יותר

CREATE TABLE content_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    author_id BIGINT UNSIGNED DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(191) NOT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    views INT UNSIGNED NOT NULL DEFAULT 0,
    published_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY ux_site_slug (site_id, slug),
    KEY idx_category_published (category_id, published_at, id),
    KEY idx_status_published (status, published_at, id),
    KEY idx_author_created (author_id, created_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

כאן יש מבנה מדויק יותר, טיפוסים חסכוניים יותר, אינדקסים שמתאימים לדפוסי שאילתות, ויכולת scaling הרבה יותר טובה.

---

4. נורמליזציה, דה־נורמליזציה, ומה נכון באמת

במערכות גדולות אין “תמיד נכון”. לפעמים נורמליזציה מלאה נכונה, ולפעמים דה־נורמליזציה מבוקרת עדיפה.

נורמליזציה טובה כאשר:

• המידע משתנה תדיר
• חשוב לשמור על מקור אמת יחיד
• יש הרבה joins אבל כל טבלה יחסית קטנה

דה־נורמליזציה טובה כאשר:

• יש עמודי listing כבדים מאוד
• יש צורך בטעינה מהירה במיוחד
• אותו מידע מוצג שוב ושוב
• join יקר מאוד מתבצע כל הזמן

לדוגמה, באתר תוכן גדול לעיתים נכון להחזיק בטבלת content_items גם שדות תצוגה מחושבים כמו:

• primary_category_name
• author_display_name
• thumbnail_url
• comments_count
• search_title
• is_video

למרות שחלק מהם ניתנים לגזירה מטבלאות אחרות.

דוגמה לדה־נורמליזציה מחושבת

ALTER TABLE content_items
ADD COLUMN comments_count INT UNSIGNED NOT NULL DEFAULT 0,
ADD COLUMN primary_category_name VARCHAR(150) NOT NULL DEFAULT '',
ADD COLUMN thumbnail_url VARCHAR(255) NOT NULL DEFAULT '';

כמובן, זה דורש מנגנון sync מסודר.

---

5. חלוקת טבלאות לפי תפקיד ולא “טבלה אחת שעושה הכל”

טעות נפוצה באתרים גדולים היא לשמור הכל בטבלה אחת: תוכן, counters, audit, flags, metadata, import status.

במקום זה נכון לפצל:

טבלת תוכן ראשית

CREATE TABLE content_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(191) NOT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    published_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_category_published (category_id, published_at, id)
) ENGINE=InnoDB;

טבלת סטטיסטיקות

CREATE TABLE content_stats (
    content_id BIGINT UNSIGNED NOT NULL,
    views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    likes INT UNSIGNED NOT NULL DEFAULT 0,
    shares INT UNSIGNED NOT NULL DEFAULT 0,
    comments_count INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (content_id)
) ENGINE=InnoDB;

טבלת מטא גמישה

CREATE TABLE content_meta (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    content_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(100) NOT NULL,
    meta_value LONGTEXT,
    PRIMARY KEY (id),
    KEY idx_content_key (content_id, meta_key),
    KEY idx_key (meta_key)
) ENGINE=InnoDB;

טבלת audit/import logs

CREATE TABLE import_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_name VARCHAR(100) NOT NULL,
    item_external_id VARCHAR(100) NOT NULL,
    status TINYINT UNSIGNED NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_source_status_created (source_name, status, created_at),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB;

כך התוכן הראשי נשאר קל יותר, והטבלאות הכבדות באמת לא פוגעות בכל שאילתה רגילה.

---

6. אינדקסים בקנה מידה גדול – איך לחשוב נכון

בטבלאות עם מאות מיליוני רשומות, אינדקסים הם גם הצלה וגם מחיר.

אינדקס לא נכון:

• תופס הרבה מקום
• מאט insert/update
• לא באמת עוזר לשאילתות

אינדקס נכון:

• תואם WHERE
• תואם ORDER BY
• תואם LIMIT
• ומאפשר covering index כשהדבר אפשרי

דוגמה לשאילתה נפוצה באתר תוכן

SELECT id, title, published_at
FROM content_items
WHERE category_id = 15
  AND status = 1
  AND published_at <= NOW()
ORDER BY published_at DESC, id DESC
LIMIT 20;

האינדקס המתאים:

CREATE INDEX idx_category_status_published_id
ON content_items (category_id, status, published_at, id);

למה הסדר חשוב?

כי MySQL משתמש במפתחות משמאל לימין. הסדר חייב להתאים לדפוסי הסינון והמיון.

דוגמה לאינדקס פחות טוב

CREATE INDEX idx_bad
ON content_items (published_at, title, category_id, status);

כאן הכנסה של title באמצע יכולה לפגוע בשימוש היעיל באינדקס לשאילתה הספציפית.

---

7. Covering indexes

כאשר כל העמודות הדרושות נמצאות באינדקס, MySQL יכול לענות על השאילתה בלי לגשת לשורת הטבלה עצמה.

דוגמה

SELECT id, category_id, published_at
FROM content_items
WHERE status = 1
ORDER BY published_at DESC
LIMIT 50;

אינדקס מתאים:

CREATE INDEX idx_status_published_cover
ON content_items (status, published_at, id, category_id);

זה יכול לשפר משמעותית ביצועים בשאילתות listing.

---

8. פייג׳ינציה נכונה – Keyset pagination במקום OFFSET

OFFSET ענק הוא אחת הבעיות הכי נפוצות במאגרים גדולים.

גרוע

SELECT id, title, published_at
FROM content_items
WHERE status = 1
ORDER BY published_at DESC, id DESC
LIMIT 100000, 20;

השרת עדיין צריך לעבור על כמות עצומה של שורות.

נכון יותר – seek pagination

SELECT id, title, published_at
FROM content_items
WHERE status = 1
  AND (
      published_at < '2026-03-01 12:00:00'
      OR (published_at = '2026-03-01 12:00:00' AND id < 987654321)
  )
ORDER BY published_at DESC, id DESC
LIMIT 20;

כך כל עמוד ממשיך מהעמוד הקודם לפי ערכי מפתח, בלי OFFSET.

דוגמת PHP 8.3

<?php
// Author: pablo guides

declare(strict_types=1);

$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'database');
$mysqli->set_charset('utf8mb4');

$lastPublishedAt = $_GET['last_published_at'] ?? null;
$lastId = isset($_GET['last_id']) ? (int) $_GET['last_id'] : null;

$sql = "
    SELECT id, title, published_at
    FROM content_items
    WHERE status = 1
";

$params = [];
$types = '';

if ($lastPublishedAt !== null && $lastId !== null) {
    $sql .= "
      AND (
          published_at < ?
          OR (published_at = ? AND id < ?)
      )
    ";
    $types .= 'ssi';
    $params[] = $lastPublishedAt;
    $params[] = $lastPublishedAt;
    $params[] = $lastId;
}

$sql .= " ORDER BY published_at DESC, id DESC LIMIT 20";

$stmt = $mysqli->prepare($sql);
if (!$stmt) {
    throw new RuntimeException('שגיאה בהכנת השאילתה: ' . $mysqli->error);
}

if ($types !== '') {
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['id'] . ' | ' . $row['title'] . ' | ' . $row['published_at'] . PHP_EOL;
}

$stmt->close();
$mysqli->close();

---

9. Partitioning – מתי כן ומתי לא

Partitioning לא “מאיץ הכל”. הוא שימושי כאשר יש דפוס גישה ברור, בדרך כלל לפי זמן או טווחים.

דוגמה קלאסית – logs/events

CREATE TABLE activity_logs (
    id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    action_type TINYINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    payload JSON DEFAULT NULL,
    PRIMARY KEY (id, created_at),
    KEY idx_created_at (created_at),
    KEY idx_user_created (user_id, created_at)
)
ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

היתרונות

• סריקה של partitions רלוונטיים בלבד
• מחיקה מהירה של data ישן
• תחזוקה קלה יותר על נתונים היסטוריים

החסרונות

• לא מתאים לכל טבלה
• דורש תכנון מפתחות נכון
• יכול להקשות על חלק מהשאילתות והניהול

מתי כן?

• logs
• analytics
• events
• imports history
• audit tables
• נתונים “זמניים” או time-series

מתי פחות?

• טבלת תוכן ראשית אם רוב השאילתות אינן לפי זמן בלבד
• מערכות עם הרבה joins מורכבים שחוצים partitions

---

10. שארדינג – כשהשרת הבודד כבר לא מספיק

Sharding הוא חלוקה של הנתונים בין כמה מסדי נתונים פיזיים או לוגיים.

זה כבר לא “טריק ביצועים קטן”, אלא שינוי ארכיטקטוני עמוק.

סוגי sharding נפוצים

לפי site_id

מתאים לריבוי אתרים/מותגים/טננטים.

לפי region / country

מתאים למערכות גיאוגרפיות גדולות.

לפי hash של user_id או content_id

מתאים לחלוקה פחות תלויה בדומיין עסקי.

דוגמה רעיונית

נניח שיש לך 4 shards:

• shard_1 – site_id 1-250
• shard_2 – site_id 251-500
• shard_3 – site_id 501-750
• shard_4 – site_id 751-1000

מימוש PHP פשוט של router

<?php
// Author: pablo guides

declare(strict_types=1);

function getShardConfigBySiteId(int $siteId): array
{
    if ($siteId <= 250) {
        return ['host' => '10.0.0.11', 'db' => 'content_shard_1'];
    }

    if ($siteId <= 500) {
        return ['host' => '10.0.0.12', 'db' => 'content_shard_2'];
    }

    if ($siteId <= 750) {
        return ['host' => '10.0.0.13', 'db' => 'content_shard_3'];
    }

    return ['host' => '10.0.0.14', 'db' => 'content_shard_4'];
}

function connectShard(int $siteId): mysqli
{
    $cfg = getShardConfigBySiteId($siteId);

    $mysqli = new mysqli($cfg['host'], 'db_user', 'db_pass', $cfg['db']);
    if ($mysqli->connect_error) {
        throw new RuntimeException('שגיאת חיבור לשארד: ' . $mysqli->connect_error);
    }

    $mysqli->set_charset('utf8mb4');
    return $mysqli;
}

$siteId = 340;
$db = connectShard($siteId);

$stmt = $db->prepare("
    SELECT id, title, published_at
    FROM content_items
    WHERE site_id = ?
    ORDER BY published_at DESC
    LIMIT 20
");

$stmt->bind_param('i', $siteId);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['title'] . PHP_EOL;
}

$stmt->close();
$db->close();

מתי sharding מוצדק?

• טבלה אחת עברה את גבול הניהול הסביר
• אי אפשר יותר לגדול אנכית בשרת יחיד
• יש בידול עסקי ברור בין קבוצות נתונים
• אפשר לחלק את הנתונים כמעט בלי cross-shard joins

מתי לא למהר לשם?

כי שארדינג מסבך:

• reporting
• joins
• transactions
• migrations
• backups
• failover
• debugging

בדרך כלל עדיף למצות קודם:

• query optimization
• indexing
• partitioning
• replicas
• caching
• proper schema design

---

11. Read Replicas – הצעד הכי חשוב לפני שארדינג

ברוב אתרי התוכן הגדולים, עיקר העומס הוא קריאות. לכן ההפרדה בין שרת כתיבה לבין שרתי קריאה היא אחד השדרוגים החשובים ביותר.

מבנה טיפוסי

• Primary: כל הכתיבות
• Replica 1: front-end reads
• Replica 2: dashboards / reports
• Replica 3: heavy exports / BI

יתרונות

• הורדת עומס מה־primary
• שיפור זמני תגובה
• אפשרות להפרדת workloads
• גיבויים וניתוחים כבדים על replica

נקודת זהירות

Replication lag. אם כתבת משהו עכשיו, ייתכן שברפליקה זה עדיין לא הופיע.

דוגמת routing ב־PHP

<?php
// Author: pablo guides

declare(strict_types=1);

function dbWrite(): mysqli
{
    $db = new mysqli('10.0.0.10', 'db_user', 'db_pass', 'main_db');
    if ($db->connect_error) {
        throw new RuntimeException('שגיאת חיבור לשרת כתיבה: ' . $db->connect_error);
    }
    $db->set_charset('utf8mb4');
    return $db;
}

function dbRead(): mysqli
{
    $replicas = [
        ['host' => '10.0.0.21'],
        ['host' => '10.0.0.22'],
    ];

    $selected = $replicas[array_rand($replicas)];

    $db = new mysqli($selected['host'], 'db_user', 'db_pass', 'main_db');
    if ($db->connect_error) {
        throw new RuntimeException('שגיאת חיבור לשרת קריאה: ' . $db->connect_error);
    }
    $db->set_charset('utf8mb4');
    return $db;
}

function createContent(string $title): int
{
    $db = dbWrite();

    $stmt = $db->prepare("
        INSERT INTO content_items (site_id, category_id, title, slug, status, created_at, updated_at)
        VALUES (1, 1, ?, ?, 1, NOW(), NOW())
    ");

    $slug = strtolower(trim(preg_replace('/[^a-z0-9]+/i', '-', $title), '-'));
    $stmt->bind_param('ss', $title, $slug);
    $stmt->execute();

    $id = $db->insert_id;

    $stmt->close();
    $db->close();

    return (int) $id;
}

function listLatestContent(): array
{
    $db = dbRead();

    $sql = "
        SELECT id, title, published_at
        FROM content_items
        WHERE status = 1
        ORDER BY published_at DESC, id DESC
        LIMIT 20
    ";

    $result = $db->query($sql);
    if (!$result) {
        throw new RuntimeException('שגיאת שאילתה: ' . $db->error);
    }

    $rows = $result->fetch_all(MYSQLI_ASSOC);
    $db->close();

    return $rows;
}

כלל מעשי

כל מסך ציבורי שלא חייב “read after write” מיידי, עדיף שיקרא מרפליקה.

---

12. ProxySQL / MaxScale – שכבת תיווך חכמה

כאשר יש הרבה replicas, failover או routing rules מורכבים, כדאי לשים שכבה כמו ProxySQL או MariaDB MaxScale.

היתרונות:

• ניתוב אוטומטי בין read/write
• connection pooling
• query rules
• failover logic
• ניטור מצב שרתים

זה שימושי במיוחד באתרים גדולים שבהם לא רוצים לפזר לוגיקת בחירת שרתים בכל הקוד.

---

13. Caching – בלי זה לא באמת תגדל

גם MySQL מושלם לא אמור לענות על כל בקשה מההתחלה.

באתרי תוכן גדולים צריך לפחות חלק מהשכבות הבאות:

Query result cache באפליקציה

למשל Redis.

Object cache

במיוחד בוורדפרס.

Page cache

אם העמוד ציבורי ולא תלוי משתמש.

Materialized views / precomputed tables

עבור דשבורדים, top lists, counters, related content.

דוגמה ל־Redis caching ב־PHP 8.3

<?php
// Author: pablo guides

declare(strict_types=1);

$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$categoryId = 15;
$cacheKey = 'category_latest:' . $categoryId;

$cached = $redis->get($cacheKey);
if ($cached !== false) {
    header('Content-Type: application/json; charset=utf-8');
    echo $cached;
    exit;
}

$db = new mysqli('127.0.0.1', 'db_user', 'db_pass', 'main_db');
if ($db->connect_error) {
    throw new RuntimeException('שגיאת חיבור: ' . $db->connect_error);
}

$db->set_charset('utf8mb4');

$stmt = $db->prepare("
    SELECT id, title, published_at
    FROM content_items
    WHERE category_id = ?
      AND status = 1
    ORDER BY published_at DESC, id DESC
    LIMIT 20
");

$stmt->bind_param('i', $categoryId);
$stmt->execute();

$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);

$json = json_encode($rows, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

$redis->setex($cacheKey, 60, $json);

header('Content-Type: application/json; charset=utf-8');
echo $json;

$stmt->close();
$db->close();

---

14. Hot counters – למה views שוברים מערכות

אחת הטעויות הנפוצות באתרים גדולים היא לעדכן views = views + 1 בכל טעינת עמוד.

זה יוצר:

• write amplification
• row contention
• עומס מיותר
• replication lag

לא מומלץ

UPDATE content_stats
SET views = views + 1
WHERE content_id = 123;

בכל page view.

נכון יותר

לאסוף counters בזיכרון/Redis ולעדכן בבאצ׳ים.

דוגמת PHP עם Redis aggregation

<?php
// Author: pablo guides

declare(strict_types=1);

$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$contentId = 123;
$redis->hIncrBy('content_views_buffer', (string) $contentId, 1);

echo "נספר צפייה לבאפר" . PHP_EOL;

Cron flush למסד

<?php
// Author: pablo guides

declare(strict_types=1);

$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$db = new mysqli('127.0.0.1', 'db_user', 'db_pass', 'main_db');
$db->set_charset('utf8mb4');

$viewsMap = $redis->hGetAll('content_views_buffer');

if (!empty($viewsMap)) {
    $db->begin_transaction();

    try {
        $stmt = $db->prepare("
            UPDATE content_stats
            SET views = views + ?
            WHERE content_id = ?
        ");

        foreach ($viewsMap as $contentId => $count) {
            $increment = (int) $count;
            $id = (int) $contentId;
            $stmt->bind_param('ii', $increment, $id);
            $stmt->execute();
        }

        $db->commit();
        $redis->del('content_views_buffer');
        $stmt->close();
    } catch (Throwable $e) {
        $db->rollback();
        throw $e;
    }
}

$db->close();

---

15. ייבוא נתונים ענק – מאות אלפים עד עשרות מיליונים

ייבוא הוא אחד המקומות שבהם אתרים גדולים נופלים.

עקרונות חשובים

• לעבוד בבאצ׳ים
• לא לבצע insert אחד לכל שורה אם לא חייבים
• להפריד ingest משלב enrichment
• להימנע מ־per-row expensive validation בפרודקשן אם אפשר
• לעקוב אחרי progress
• לתמוך ב־resume
• לשמור external_id ייחודי

טבלת staging

CREATE TABLE import_stage_content (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    external_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    payload JSON DEFAULT NULL,
    imported_at DATETIME DEFAULT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY ux_external_id (external_id),
    KEY idx_status_id (status, id)
) ENGINE=InnoDB;

למה staging?

כי לא תמיד נכון לטעון ישר לטבלאות הראשיות. לעיתים צריך:

• ניקוי
• נרמול
• dedupe
• map לקטגוריות
• enrich של metadata
• בדיקות תקינות

טעינה מהירה עם LOAD DATA

LOAD DATA INFILE '/tmp/huge_import.csv'
INTO TABLE import_stage_content
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(external_id, title, category_id);

upsert מה־stage לטבלה ראשית

INSERT INTO content_items (
    site_id,
    category_id,
    title,
    slug,
    status,
    created_at,
    updated_at
)
SELECT
    1,
    s.category_id,
    s.title,
    LOWER(REPLACE(REPLACE(TRIM(s.title), ' ', '-'), '--', '-')),
    1,
    NOW(),
    NOW()
FROM import_stage_content s
WHERE s.status = 0
ON DUPLICATE KEY UPDATE
    title = VALUES(title),
    category_id = VALUES(category_id),
    updated_at = NOW();

בפועל עדיף לעיתים לבצע זאת בבאצ׳ים.

דוגמת batch importer ב־PHP 8.3

<?php
// Author: pablo guides

declare(strict_types=1);

$db = new mysqli('127.0.0.1', 'db_user', 'db_pass', 'main_db');
if ($db->connect_error) {
    throw new RuntimeException($db->connect_error);
}

$db->set_charset('utf8mb4');

$batchSize = 5000;

while (true) {
    $result = $db->query("
        SELECT id, external_id, title, category_id
        FROM import_stage_content
        WHERE status = 0
        ORDER BY id ASC
        LIMIT {$batchSize}
    ");

    if (!$result) {
        throw new RuntimeException($db->error);
    }

    $rows = $result->fetch_all(MYSQLI_ASSOC);
    if (empty($rows)) {
        echo "הייבוא הסתיים" . PHP_EOL;
        break;
    }

    $db->begin_transaction();

    try {
        $insertStmt = $db->prepare("
            INSERT INTO content_items (
                site_id, category_id, title, slug, status, created_at, updated_at
            ) VALUES (
                1, ?, ?, ?, 1, NOW(), NOW()
            )
        ");

        $updateStageStmt = $db->prepare("
            UPDATE import_stage_content
            SET status = 1, imported_at = NOW()
            WHERE id = ?
        ");

        foreach ($rows as $row) {
            $categoryId = (int) $row['category_id'];
            $title = $row['title'];
            $slug = strtolower(trim((string) preg_replace('/[^a-z0-9]+/i', '-', $title), '-'));

            $insertStmt->bind_param('iss', $categoryId, $title, $slug);
            $insertStmt->execute();

            $stageId = (int) $row['id'];
            $updateStageStmt->bind_param('i', $stageId);
            $updateStageStmt->execute();
        }

        $db->commit();
        echo "יובאו " . count($rows) . " רשומות" . PHP_EOL;

        $insertStmt->close();
        $updateStageStmt->close();
    } catch (Throwable $e) {
        $db->rollback();
        throw $e;
    }
}

---

16. huge imports בלי לשבור את האתר

כאשר הייבוא גדול מאוד, עדיף:

להפעיל אותו ב־CLI ולא דרך HTTP

לא דרך request רגיל של דפדפן.

להגביל concurrency

יותר מדי workers יכולים להאט במקום להאיץ.

להפריד בין ingest ל־post-processing

שלב 1: טעינה
שלב 2: שיוך קטגוריות
שלב 3: בניית חיפושים / thumbnails / counters / SEO fields

לעבוד עם checkpoints

למשל לפי last_processed_id

לשמור טבלת jobs

CREATE TABLE import_jobs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_name VARCHAR(100) NOT NULL,
    source_name VARCHAR(100) NOT NULL,
    last_processed_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_processed BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status TINYINT UNSIGNED NOT NULL DEFAULT 0,
    started_at DATETIME DEFAULT NULL,
    finished_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_status_started (status, started_at)
) ENGINE=InnoDB;

כך הייבוא הופך ל־resume-safe.

---

17. טרנזקציות גדולות מדי – טעות נפוצה

טרנזקציה אחת של מיליון שורות עלולה ליצור:

• locks ארוכים
• binlog כבד
• rollback כואב
• עומס על replicas

לכן לרוב עדיף batch commits.

טוב יותר

commit כל 1000–10000 שורות, תלוי בנתונים ובשרת.

---

18. InnoDB tuning לשרתים גדולים

הגדרות לדוגמה. צריך להתאים לפי RAM, CPU, SSD/NVMe וסוג עומס.

[mysqld]
# Author: pablo guides

bind-address = 127.0.0.1
default_storage_engine = InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

max_connections = 300
thread_cache_size = 100
table_open_cache = 8000
table_definition_cache = 4000
open_files_limit = 65535

innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1

tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /var/log/mysql/slow-query.log

performance_schema = ON

skip-name-resolve = 1
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

הערות חשובות

innodb_buffer_pool_size הוא כנראה הפרמטר החשוב ביותר למסדי InnoDB גדולים.
בשרת ייעודי למסד נתונים נהוג לעיתים להקדיש לו 60%–80% מה־RAM.

innodb_flush_log_at_trx_commit = 2 נותן שיפור ביצועים, אבל הוא פשרה מסוימת על durability לעומת 1.
במערכות קריטיות במיוחד צריך לבדוק אם נכון יותר להשאיר 1.

sort_buffer_size, join_buffer_size ושאר buffers per connection לא כדאי לנפח סתם, כי הם מוכפלים במספר connections.

---

19. MariaDB לעומת MySQL – איפה לשים לב

ברוב השימושים העקרונות דומים, אבל יש הבדלים בפיצ׳רים, באופטימייזר, ב־replication, ב־tooling וב־storage internals.

כלל מעשי

אל תניח שמה שעובד טוב ב־MySQL 8 יתנהג זהה ב־MariaDB, ולהפך.
במערכות גדולות צריך לבדוק בפועל:

• EXPLAIN
• EXPLAIN ANALYZE אם זמין
• plan stability
• index usage
• lock behavior
• optimizer choices

---

20. שינויים בסכמה בלי downtime

על טבלאות גדולות, ALTER TABLE נאיבי הוא מסוכן.

בעיה

הוספת אינדקס לטבלה של מאות מיליוני שורות יכולה לקחת זמן רב, לצרוך I/O כבד, ולפגוע באתר.

כלים מקובלים

• pt-online-schema-change
• gh-ost (ב־MySQL)
• שינויים מדורגים עם shadow table
• rolling migrations

דוגמה רעיונית ל־pt-online-schema-change

pt-online-schema-change \
  --alter "ADD INDEX idx_status_published_id (status, published_at, id)" \
  D=main_db,t=content_items \
  --execute

כמובן, קודם בודקים על staging.

---

21. חיפוש באתרי תוכן גדולים – לא תמיד צריך להעמיס על MySQL

אם יש חיפוש בסיסי, FULLTEXT יכול להספיק.
אבל אם מדובר בחיפוש מתקדם, פילטרים רבים, relevance, autocomplete, facets, typo tolerance או מיליוני מסמכים – לעיתים נכון להעביר את החיפוש למנוע ייעודי כמו Elasticsearch / OpenSearch / Meilisearch / Sphinx.

MySQL טוב מאוד להרבה דברים, אבל הוא לא תמיד צריך להיות מנוע החיפוש הראשי של פורטל ענק.

---

22. Aggregation tables ו־materialized snapshots

דוחות, top categories, top tags, top regions, ספירות מומחים לפי תחום, ספירות נכסים לפי עיר – אלה דברים שלא חייבים לחשב live בכל עמוד.

טבלת סיכום לדוגמה

CREATE TABLE category_daily_stats (
    stat_date DATE NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    items_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (stat_date, category_id),
    KEY idx_category_date (category_id, stat_date)
) ENGINE=InnoDB;

בנייה לילית

INSERT INTO category_daily_stats (stat_date, category_id, items_count, total_views)
SELECT
    CURDATE() AS stat_date,
    c.category_id,
    COUNT(*) AS items_count,
    COALESCE(SUM(s.views), 0) AS total_views
FROM content_items c
LEFT JOIN content_stats s ON s.content_id = c.id
WHERE c.status = 1
GROUP BY c.category_id
ON DUPLICATE KEY UPDATE
    items_count = VALUES(items_count),
    total_views = VALUES(total_views);

הגישה הזאת שווה זהב במסכים כבדים.

---

23. ניטור – בלי זה אתה עיוור

מערכות גדולות לא מנהלים רק לפי “מרגיש איטי”.

צריך לעקוב אחרי:

• slow queries
• rows examined
• temp tables
• replication lag
• CPU
• IOPS
• buffer pool hit ratio
• deadlocks
• lock waits
• connections
• threads running
• disk latency
• growth per table/index

כלים נפוצים

• MySQL slow log
• Performance Schema
• Percona Monitoring and Management
• Grafana + Prometheus
• pt-query-digest
• MySQLTuner

דוגמה לניתוח טבלאות כבדות

SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'main_db'
ORDER BY (data_length + index_length) DESC;

זיהוי שאילתות יקרות

SHOW FULL PROCESSLIST;

ובניתוח עמוק יותר עדיף slow log + pt-query-digest.

---

24. ארכיטקטורת דוגמה לאתר תוכן ענק

נניח אתר תוכן גדול מאוד עם:

• מאות מיליוני רשומות תוכן/מטא/לוגים
• חיפוש
• ייבוא חיצוני
• ארכיונים כבדים
• API
• ממשק ניהול

ארכיטקטורה סבירה יכולה להיות:

שכבת אפליקציה

כמה שרתי web/app

שכבת cache

Redis ל־object cache, query cache, counters, queues קלים

שכבת DB

• Primary לכתיבות
• Replica ציבורית לקריאות front-end
• Replica לדוחות ו־exports
• Replica לגיבויים/ETL

שכבת חיפוש

מנוע חיפוש ייעודי אם צריך

import pipeline

• קבצים ל־staging
• workers ב־CLI
• batch processing
• resume jobs
• post-processing async

analytics / logs

טבלאות נפרדות, לעיתים partitioned, לא בתוך טבלת התוכן הראשית

---

25. טעויות נפוצות מאוד

שימוש ב־SELECT *

מעמיס I/O ויוצר queries פחות יעילים.

מטא אינסופי בסגנון EAV בלי גבול

גמיש מאוד, אבל יכול להפוך סיוט ביצועים.

OFFSET גדול

רע מאוד בקנה מידה גדול.

counters בזמן אמת בטבלת תוכן

מיותר וכבד.

imports דרך wp-admin או request HTTP

לא יציב, לא resume-safe.

הוספת אינדקסים בלי למדוד

כל אינדקס עולה מקום וביצועי כתיבה.

ערבוב workloads

front-end, imports, reports ו־cron על אותו DB בלי הפרדה.

חוסר ב־archiving

לוגים ישנים ממשיכים לנפח את המערכת לנצח.

---

26. דוגמה לתכנון טבלאות עבור אתר תוכן כבד מאוד

CREATE TABLE content_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    author_id BIGINT UNSIGNED DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(191) NOT NULL,
    excerpt VARCHAR(500) NOT NULL DEFAULT '',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    content_type TINYINT UNSIGNED NOT NULL DEFAULT 1,
    published_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY ux_site_slug (site_id, slug),
    KEY idx_category_status_published (category_id, status, published_at, id),
    KEY idx_author_status_created (author_id, status, created_at, id),
    KEY idx_type_status_published (content_type, status, published_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_stats (
    content_id BIGINT UNSIGNED NOT NULL,
    views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    comments_count INT UNSIGNED NOT NULL DEFAULT 0,
    likes_count INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (content_id)
) ENGINE=InnoDB;

CREATE TABLE content_search (
    content_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    searchable_text MEDIUMTEXT NOT NULL,
    PRIMARY KEY (content_id),
    FULLTEXT KEY ftx_title_text (title, searchable_text)
) ENGINE=InnoDB;

CREATE TABLE content_import_map (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_name VARCHAR(100) NOT NULL,
    external_id VARCHAR(100) NOT NULL,
    content_id BIGINT UNSIGNED NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY ux_source_external (source_name, external_id),
    KEY idx_content_id (content_id)
) ENGINE=InnoDB;

---

27. אסטרטגיית scaling מומלצת לפי שלבים

שלב 1 – עד כמה מיליוני רשומות

• אופטימיזציית שאילתות
• אינדקסים
• InnoDB tuning
• Redis cache
• batch imports
• keyset pagination

שלב 2 – עשרות מיליונים

• הפרדת טבלאות כבדות
• read replicas
• materialized aggregates
• proper staging tables
• online schema change tools
• archiving policy

שלב 3 – מאות מיליונים

• partitioning על logs/events/history
• הפרדת workloads מלאה
• dedicated reporting replica
• search engine חיצוני במידת הצורך
• routing read/write חכם
• שקילת sharding רק אם באמת נדרש

שלב 4 – scale קיצוני

• sharding לפי tenant/region/hash
• distributed ingest pipeline
• asynchronous enrichment
• advanced proxy layer
• capacity planning רציף

---

28. סיכום מקצועי

אופטימיזציה של MySQL לאתרים עם מאות מיליוני רשומות היא כבר לא משימה של “להוסיף אינדקס ולסיים”.
זו עבודה מערכתית שכוללת:

• תכנון סכמות מדויק
• אינדקסים נכונים
• צמצום scans
• פייג׳ינציה חכמה
• הפרדת read/write
• replicas
• caching
• import pipelines חזקים
• online schema changes
• partitioning במקומות הנכונים
• ובמקרים מסוימים גם sharding

הטעות הכי גדולה היא לקפוץ מוקדם מדי לפתרון הכי מסובך.
ברוב המקרים, לפני שארדינג, אפשר לקבל שיפור עצום רק מ:

• redesign של טבלאות
• keyset pagination
• read replicas
• Redis
• staging imports
• summary tables
• והפרדה אמיתית בין workloads