Что такое партиционирование таблиц в базах данных?

Question

Что такое партиционирование таблиц в базах данных?

Тип вопроса: Базы данных Вероятность: 24% Категория: PHP Разработчик

Ответ

Партиционирование таблиц — это метод физического разделения одной логической таблицы на меньшие, более управляемые части (партиции) на основе значения определённого столбца (ключа партиционирования). Каждая партиция может храниться отдельно, что оптимизирует запросы и управление данными.

Основные типы партиционирования в MySQL/PostgreSQL:

По диапазону (RANGE): Для упорядоченных данных (даты, ID).
По списку (LIST): Для группировки по категориям (регион, статус).
По хешу (HASH): Для равномерного распределения.

Пример: Партиционирование таблицы логов по месяцу (MySQL RANGE):

CREATE TABLE application_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    log_level VARCHAR(10),
    message TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at) -- Ключ партиционирования ДОЛЖЕН входить в PRIMARY KEY
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p_2023_10 VALUES LESS THAN ('2023-11-01'),
    PARTITION p_2023_11 VALUES LESS THAN ('2023-12-01'),
    PARTITION p_2023_12 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Преимущества:

Повышение производительности запросов: При условии WHERE created_at >= '2023-11-15' СУБД будет сканировать только партицию p_2023_11 (partition pruning).
Упрощение управления данными: Удаление старых данных выполняется мгновенно удалением всей партиции: ALTER TABLE application_logs DROP PARTITION p_2023_10;
Параллельное выполнение: Запросы могут выполняться параллельно по разным партициям.

Недостатки и ограничения:

Сложность выбора оптимального ключа.
Ограничения на уникальные индексы (уникальность должна обеспечиваться в рамках ключа партиционирования).
Не все операции одинаково эффективны (например, JOIN с неправильно партиционированными таблицами).

Ответ 18+ 🔞

Давай разберём эту штуку, а то народ часто путается, как будто на дворе 2002-й год и все только про индексы слышали.

Партиционирование — это, грубо говоря, когда ты свою здоровенную, блядь, таблицу, которая уже овердохуища данных содержит, режешь на куски, как торт на дне рождения. Не просто так, а по какому-то правилу. Логически она одна, а физически — несколько отдельных файлов. Зачем? Ну, представь, у тебя журнал логов за пять лет. И ты ищешь запись за вчера. Без партиций СУБД будет перерывать хуй с горы — все пять лет записей. А с партициями она сразу пойдёт в тот кусок, где лежат данные за этот месяц, и поиск станет в разы быстрее. Это называется partition pruning, если по-умному.

Какие бывают способы нарезки этого самого торта:

По диапазону (RANGE). Самый понятный. «Всё, что с января по март — в эту кучку. С апреля по июнь — в эту». Идеально для дат или порядковых номеров.
По списку (LIST). Тут ты сам говоришь: «Вот записи из Москвы и Питера — в эту партицию. Из Воронежа и Ростова — в эту». Для категорий.
По хешу (HASH). Автоматическое распределение, чтобы равномерно раскидать. Берёшь значение столбца, считаешь от него хеш — и в какую партицию выпало, туда и запись. Чтобы не было так, что одна партиция пустая, а другая — пиздопроебибна.

Вот, смотри, как это выглядит на практике, чтобы не быть голословным. Допустим, логи приложения:

CREATE TABLE application_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    log_level VARCHAR(10),
    message TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at) -- ВАЖНО! Ключ партиционирования ДОЛЖЕН быть частью первичного ключа. Иначе — **доверия ебать ноль**.
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p_2023_10 VALUES LESS THAN ('2023-11-01'),
    PARTITION p_2023_11 VALUES LESS THAN ('2023-12-01'),
    PARTITION p_2023_12 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE -- Сюда всё непредвиденное свалится.
);

Чем это, блядь, хорошо?

Скорость. Как я уже сказал, запрос WHERE created_at >= '2023-11-15' не будет шариться везде. Он сразу полезет в партицию p_2023_11. Удивление пиздец, но работает.
Управление. Хочешь удалить логи за октябрь 2023-го? Вместо муторного DELETE FROM ... WHERE ..., который будет ебать колотить таблицу часами, просто пишешь: ALTER TABLE application_logs DROP PARTITION p_2023_10;. И всё. Партиция накрылась медным тазом моментально.
Параллелизм. Запросы могут одновременно лазить в разные партиции, если им надо.

А теперь ложка дёгтя, без неё никуда:

Выбрать ключ партиционирования — это надо головой думать. Выбрал криво — получишь хитрую жопу, где все данные в одной партиции, а остальные пустые. Пользы — ноль ебать.
Ограничения по уникальности. Уникальный индекс должен включать в себя столбец, по которому партиционируешь. Иначе как система проверит уникальность по всем кускам сразу? Чёрта в душу поймёшь.
Не панацея. Если делать JOIN с другой таблицей, которая партиционирована по-другому, можно получить такой хуй в пальто по производительности, что мало не покажется. Это не волшебная таблетка, а инструмент, который нужно применять с умом.

Короче, штука мощная, но если бездумно тыкать — будет тебе хиросима, а не оптимизация.