Mid💻Практика5 min

MySQL

InnoDB, репликация, partitioning, JSON в MySQL. Сравнение с PostgreSQL. Работа через PDO/MySQLi

Data storageSysdesign

MySQL

MySQL в экосистеме

MySQL -- одна из самых популярных РСУБД, основа LAMP-стека. InnoDB (default storage engine) обеспечивает ACID-транзакции, MVCC и внешние ключи.

InnoDB Architecture

┌─────────────────────────────────────────┐
│              InnoDB Engine              │
│                                         │
│  ┌──────────────────────────────────┐   │
│  │        Buffer Pool (RAM)         │   │
│  │  - Data pages                    │   │
│  │  - Index pages                   │   │
│  │  - Change buffer                 │   │
│  │  - Adaptive hash index           │   │
│  └──────────────┬───────────────────┘   │
│                 │                       │
│  ┌──────────────┴───────────────────┐   │
│  │          Redo Log (WAL)          │   │
│  │  - Write-Ahead Logging           │   │
│  │  - Crash recovery                │   │
│  └──────────────────────────────────┘   │
│                                         │
│  ┌──────────────────────────────────┐   │
│  │        Tablespace (Disk)         │   │
│  │  - .ibd files (tables + indexes) │   │
│  │  - Undo tablespace               │   │
│  └──────────────────────────────────┘   │
└─────────────────────────────────────────┘

Ключевые особенности InnoDB

Свойство Описание
Кластерный индекс Данные хранятся в порядке Primary Key
MVCC Конкурентные чтения без блокировок
Row-level locking Блокировка на уровне строк
Buffer Pool Кэш данных и индексов в RAM
Crash Recovery Автоматическое восстановление через redo log 
<?php

declare(strict_types=1);

/**
 * InnoDB-specific behavior through PHP
 */
final class InnoDbExamples
{
    public function __construct(
        private readonly \PDO $db,
    ) {}

    /**
     * InnoDB stores data in Primary Key order (clustered index).
     * Auto-increment PK is optimal for sequential inserts.
     * UUID PK causes random writes and page splits.
     */
    public function createOptimizedTable(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS orders (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                uuid CHAR(36) NOT NULL,
                user_id BIGINT UNSIGNED NOT NULL,
                total_amount DECIMAL(12,2) NOT NULL,
                status ENUM('pending','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                UNIQUE KEY uk_uuid (uuid),
                KEY idx_user_status (user_id, status),
                KEY idx_created (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        SQL);
    }

    /**
     * Deadlock handling: retry pattern
     */
    public function transferBalance(int $fromId, int $toId, float $amount): void
    {
        $maxRetries = 3;

        for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
            try {
                $this->db->beginTransaction();

                // Always lock in consistent order to prevent deadlocks
                $ids = [$fromId, $toId];
                sort($ids);

                $stmt = $this->db->prepare(
                    'SELECT id, balance FROM accounts WHERE id IN (?, ?) FOR UPDATE'
                );
                $stmt->execute($ids);
                $accounts = $stmt->fetchAll(\PDO::FETCH_ASSOC);

                // Perform transfer
                $this->db->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
                    ->execute([$amount, $fromId]);
                $this->db->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
                    ->execute([$amount, $toId]);

                $this->db->commit();
                return;
            } catch (\PDOException $e) {
                $this->db->rollBack();

                // Error code 40001 = deadlock, 1213 = MySQL deadlock
                if ($attempt < $maxRetries && str_contains($e->getMessage(), '1213')) {
                    usleep(random_int(10_000, 100_000)); // Random backoff
                    continue;
                }

                throw $e;
            }
        }
    }
}

Репликация

Типы репликации MySQL

Тип Описание Задержка
Async Мастер не ждёт подтверждения от реплики Низкая для мастера
Semi-sync Мастер ждёт хотя бы одно подтверждение Чуть выше
Group Replication Multi-primary, consensus Выше 
                 ┌─────────────┐
    Writes ────> │   Primary   │
                 │  (Master)   │
                 └──────┬──────┘
                        │ binlog
              ┌─────────┼─────────┐
              ▼         ▼         ▼
        ┌──────────┐ ┌──────────┐ ┌──────────┐
 Reads  │ Replica 1│ │ Replica 2│ │ Replica 3│
  <──── │          │ │          │ │          │
        └──────────┘ └──────────┘ └──────────┘

<?php

declare(strict_types=1);

/**
 * Read/write splitting with MySQL replication
 */
final class ReplicaAwareConnection
{
    private \PDO $primary;
    /** @var \PDO[] */
    private array $replicas;
    private int $replicaIndex = 0;

    public function __construct(
        array $primaryConfig,
        array $replicaConfigs,
    ) {
        $this->primary = $this->connect($primaryConfig);
        $this->replicas = array_map(
            fn(array $config) => $this->connect($config),
            $replicaConfigs
        );
    }

    /**
     * Write operations always go to primary
     */
    public function write(): \PDO
    {
        return $this->primary;
    }

    /**
     * Read operations go to replicas (round-robin)
     */
    public function read(): \PDO
    {
        if (empty($this->replicas)) {
            return $this->primary;
        }

        $replica = $this->replicas[$this->replicaIndex % count($this->replicas)];
        $this->replicaIndex++;

        return $replica;
    }

    /**
     * For reads that need latest data (after write)
     */
    public function readFromPrimary(): \PDO
    {
        return $this->primary;
    }

    private function connect(array $config): \PDO
    {
        $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['dbname']};charset=utf8mb4";

        return new \PDO($dsn, $config['user'], $config['password'], [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }
}

// Usage
$conn = new ReplicaAwareConnection(
    primaryConfig: ['host' => 'mysql-primary', 'port' => 3306, 'dbname' => 'app', 'user' => 'root', 'password' => 'secret'],
    replicaConfigs: [
        ['host' => 'mysql-replica1', 'port' => 3306, 'dbname' => 'app', 'user' => 'readonly', 'password' => 'secret'],
        ['host' => 'mysql-replica2', 'port' => 3306, 'dbname' => 'app', 'user' => 'readonly', 'password' => 'secret'],
    ],
);

// Write to primary
$conn->write()->prepare('INSERT INTO orders ...')->execute([...]);

// Read from replica
$conn->read()->query('SELECT * FROM orders WHERE ...')->fetchAll();

Partitioning

MySQL поддерживает партиционирование таблиц для управления большими объёмами данных.

<?php

declare(strict_types=1);

/**
 * Table partitioning for large tables
 */
final class PartitioningExample
{
    public function __construct(
        private readonly \PDO $db,
    ) {}

    /**
     * Range partitioning by date
     */
    public function createPartitionedTable(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS events (
                id BIGINT UNSIGNED AUTO_INCREMENT,
                event_type VARCHAR(50) NOT NULL,
                payload JSON NOT NULL,
                created_at DATETIME NOT NULL,
                PRIMARY KEY (id, created_at)
            )
            PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
                PARTITION p2024_01 VALUES LESS THAN (202402),
                PARTITION p2024_02 VALUES LESS THAN (202403),
                PARTITION p2024_03 VALUES LESS THAN (202404),
                PARTITION p_future VALUES LESS THAN MAXVALUE
            )
        SQL);
    }

    /**
     * Add partition for new month
     */
    public function addPartition(int $year, int $month): void
    {
        $partName = sprintf('p%d_%02d', $year, $month);
        $nextValue = $year * 100 + $month + 1;

        $this->db->exec(<<<SQL
            ALTER TABLE events REORGANIZE PARTITION p_future INTO (
                PARTITION {$partName} VALUES LESS THAN ({$nextValue}),
                PARTITION p_future VALUES LESS THAN MAXVALUE
            )
        SQL);
    }

    /**
     * Drop old partition (faster than DELETE)
     */
    public function dropPartition(string $partitionName): void
    {
        $this->db->exec("ALTER TABLE events DROP PARTITION {$partitionName}");
    }
}

JSON в MySQL

MySQL 8+ поддерживает JSON-тип данных с индексацией через generated columns.

<?php

declare(strict_types=1);

/**
 * MySQL JSON support
 */
final class MySqlJsonExample
{
    public function __construct(
        private readonly \PDO $db,
    ) {}

    public function createTable(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS products (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                metadata JSON NOT NULL,
                -- Generated column for indexing JSON field
                brand VARCHAR(100) GENERATED ALWAYS AS (metadata->>'$.brand') STORED,
                price DECIMAL(10,2) GENERATED ALWAYS AS (metadata->>'$.price') STORED,
                KEY idx_brand (brand),
                KEY idx_price (price)
            )
        SQL);
    }

    /**
     * JSON query examples
     */
    public function findByBrand(string $brand): array
    {
        $stmt = $this->db->prepare(<<<SQL
            SELECT
                id,
                name,
                metadata->>'$.brand' as brand,
                metadata->>'$.price' as price,
                JSON_EXTRACT(metadata, '$.specs') as specs
            FROM products
            WHERE metadata->>'$.brand' = :brand
        SQL);

        $stmt->execute(['brand' => $brand]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * JSON aggregation
     */
    public function getProductsByCategory(): array
    {
        return $this->db->query(<<<SQL
            SELECT
                metadata->>'$.category' as category,
                COUNT(*) as count,
                JSON_ARRAYAGG(name) as product_names
            FROM products
            GROUP BY metadata->>'$.category'
        SQL)->fetchAll(\PDO::FETCH_ASSOC);
    }
}

MySQL vs PostgreSQL

Критерий MySQL 8+ PostgreSQL 16+
JSON JSON тип, generated columns для индексов JSONB, GIN-индекс, больше операторов
Полнотекстовый поиск FULLTEXT index (ограниченный) tsvector + GIN (мощный)
CTE Поддержка Полная поддержка + recursive
Window Functions Полная Полная
Расширения Нет Огромная экосистема (PostGIS, pgvector)
Репликация Встроенная, проверенная Logical + physical
Partitioning Range, List, Hash, Key Range, List, Hash + declarative
Default isolation REPEATABLE READ READ COMMITTED
Производительность Быстрее на простых reads Лучше на complex queries
Экосистема Широкая (WordPress, Laravel) Растёт (Symfony, Django)

Вывод: PostgreSQL мощнее по возможностям. MySQL проще в начальной настройке и имеет более широкую экосистему в PHP-мире. Для новых проектов рекомендуется PostgreSQL.

Итоги

  • InnoDB -- основной движок MySQL с ACID, MVCC, row-level locking
  • Кластерный индекс по PK определяет физический порядок данных
  • Репликация позволяет масштабировать чтение через read replicas
  • Partitioning помогает управлять большими таблицами
  • JSON в MySQL 8+ работает через generated columns для индексации
  • Для новых проектов PostgreSQL предпочтительнее MySQL по возможностям

Связанные темы

Внутренности баз данных

Связанная тема