Настройка MySQL партиционирования таблиц: RANGE, LIST, HASH

Почему партиционирование MySQL перестаёт помогать

Партиционирование MySQL по RANGE по дате — рабочая схема ровно до того момента, пока запросы не начинают ходить по всей таблице. Пока условие WHERE содержит ключ партиционирования, оптимизатор отсекает лишние секции и читает один-два сегмента. Как только фильтр идёт по другому столбцу — по клиенту, статусу, внешнему идентификатору — отсечение не срабатывает, и вместо части данных сканируются все сотни миллионов строк. Именно здесь начинается деградация запросов, которую замечают уже по времени ответа API.

Вторая типовая точка отказа — очистка исторических данных. DELETE FROM ... WHERE created_at < ... на большой таблице выполняется часами, держит блокировки, раздувает undo-лог и отставание реплик. Освобождённое место не возвращается на диск: файлы табличного пространства остаются того же размера, и переполнение диска приходит раньше, чем заканчиваются данные. Отставание реплики в этот момент растёт лавинообразно, потому что каждая удалённая строка пишется в бинарный лог.

Третий сбой — неверная стратегия. HASH и KEY дают равномерное распределение, но не дают отсечения по диапазону дат: очистка старых периодов превращается в ту же массовую операцию. LIST удобен для дискретных значений, однако добавление нового значения требует перестройки секции. Если тип выбран под текущий профиль нагрузки, а не под то, как данные будут удаляться, схема ломается на первом же росте.

Разбираем, как это закрывается: правильный выбор типа секционирования, обслуживание секций вместо удаления строк и контроль отставания реплик.

Что даёт грамотная схема разделения таблиц

Партиционирование по диапазону превращает выборку за период из полного сканирования таблицы в чтение одной-двух партиций: планировщик отсекает лишние секции по условию в запросе, и время ответа падает пропорционально тому, какую долю данных вы отсекли. На таблице с историей за несколько лет типовой отчёт «за последний месяц» ускоряется в разы без единого нового индекса — только за счёт того, что нужные строки лежат компактно.

Удаление старых данных перестаёт быть операцией записи. Вместо DELETE на миллионы строк с последующей репликацией каждого удаления и раздуванием журнала вы отбрасываете партицию целиком — это операция над метаданными, она проходит за секунды и освобождает место сразу. Нагрузка на репликацию при этом падает: ведомые серверы не проигрывают многоминутный поток удалений, лаг не растёт, а окно обслуживания сокращается с часов до минут.

Стабильность записи тоже выигрывает. Вставки идут в «горячую» партицию, индексы в ней компактнее, чем в разросшейся таблице целиком, поэтому дерево индекса остаётся неглубоким и вставка не деградирует со временем. Класс отказов из-за переполнения диска уходит: место освобождается по расписанию, а не в момент, когда заканчивается свободное пространство. Обслуживание таблиц — перестроение индексов, сбор статистики, проверки целостности — выполняется по одной партиции, а не по всей таблице, и не блокирует остальные данные.

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

RANGE, LIST, HASH и составные ключи: что выбрать

Выбор режима определяется характером данных и тем, какие операции по ним выполняются. RANGE режет таблицу по непрерывным диапазонам — датам, идентификаторам, накопленным значениям. LIST работает с дискретными наборами: регион, статус, тип документа. HASH раскладывает строки равномерно по остатку от деления, когда естественного диапазона нет, а нужен предсказуемый размер каждой секции.

Ограничения общие для всех режимов и о них стоит помнить до, а не после. Внешние ключи на партиционированную таблицу не поддерживаются — ссылочную целостность переносят на уровень приложения или триггеров. Уникальные индексы обязаны включать поле разделения целиком: составной ключ (поле_разделения, идентификатор) решает задачу, но меняет логику запросов. Секции нельзя смешивать по типу в одной таблице, а число разделов держат в разумных пределах — сотни секций заметно замедляют открытие таблицы и планирование запросов.

Практический ориентир: если 90% запросов фильтруют по дате — берут RANGE, если по категории — LIST, если фильтра нет и важна равномерность — HASH. Составной ключ добавляют поверх любого режима, когда нужно сохранить уникальность по нескольким полям.

Как мы внедряем партиционирование: этапы работ

Партиционирование не начинается с ALTER TABLE. Сначала нужно понять, по какому ключу реально идёт выборка и какие запросы к таблице вообще живут в проде — иначе разбиение по дате при фильтрации по user_id даст полный перебор всех секций вместо отсечения. Поэтому первые шаги — это разбор схемы и профиля нагрузки, а не правка DDL.

1. Аудит схемы и запросов. Смотрим структуру таблиц, индексы, внешние ключи, триггеры и представления, которые на неё завязаны. Собираем медленный лог и статистику выполнения: какие запросы читают таблицу, с какими условиями в WHERE, есть ли агрегаты по всей таблице.
2. Профиль нагрузки и выбор ключа. Определяем распределение данных по кандидату в ключ партиционирования — обычно это дата или диапазон идентификаторов. Проверяем, что большинство тяжёлых запросов фильтруют именно по этому полю, иначе смысла в разбиении нет.
3. Проектирование схемы секций. Выбираем тип: RANGE по дате для журналов и истории, LIST для фиксированного набора значений, HASH для равномерного распределения без естественной границы. Считаем число секций и границы так, чтобы каждая держала сопоставимый объём.
4. Миграция. Переводим таблицу на новую структуру с минимальным окном недоступности: готовим секции заранее, переносим данные пакетами, синхронизируем изменения, накопленные за время переноса, и только затем переключаем приложение на новую таблицу.
5. Проверка на копии. Прогоняем миграцию на полной копии прода: сверяем контрольные суммы, планы выполнения до и после, время отклика ключевых запросов. Убеждаемся, что отсечение секций действительно работает, а не осталось на бумаге.
6. Настройка обслуживания. Заводим регулярное добавление новых секций и удаление устаревших — через событие планировщика или внешнюю задачу. Старое удаляется отсечением секции, а не массовым DELETE, который держит блокировки и раздувает журнал.
7. Передача регламентов. Отдаём документацию: карта секций, правила добавления и удаления, порядок отката, точки контроля. Команда заказчика дальше ведёт таблицу сама, без нас.

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

Что заказчик получает на руки после работ

После работ у вас остаётся не «набор выполненных команд», а рабочая система, которую можно передать любому инженеру. Все артефакты складываем в ваш репозиторий и вики — без привязки к нам.

• Конфигурации партиционирования — готовые DDL-скрипты создания и перестроения разделов по выбранной стратегии (RANGE по датам, LIST по справочникам, HASH/KEY для равномерного распределения), с комментариями, почему выбран именно этот ключ разделения.
• Схема разделения и карта данных — документ, где видно, какая таблица как разбита, по какому полю идёт отсечение разделов, какие индексы обязаны включать ключ партиционирования.
• Политики хранения — правила жизненного цикла: сколько разделов держим «горячими», когда устаревшие уходят в архив, что делаем с историческими данными вместо удаления.
• Скрипты обслуживания — автоматическое создание будущих разделов заранее, открепление и архивация старых, проверка целостности, плюс обёртки для запуска по расписанию.
• Мониторинг и алерты — метрики по числу разделов, размеру, попаданию запросов в отсечение (partition pruning), оповещения при приближении к границам.
• Регламент эксплуатации — пошаговые инструкции: как добавить раздел вручную, как откатить неудачное перестроение, что делать при росте таблицы быстрее прогноза.
• Отчёт о проверке — замеры до и после на реальных запросах: время выборок, план выполнения, объём сканируемых данных.

Каждый артефакт проверен на вашем стенде, а не написан «по шаблону». Передаём с разбором: инженер вашей команды должен уметь сопровождать схему без нас.

Кейс: ускорение выборок и упрощение очистки данных

Клиент — сервис аналитики событий: таблица логов на сотни миллионов строк, партиционирование не использовалось, старые записи удаляли вручную скриптами. Выборки по периоду «последний месяц» шли десятки секунд и деградировали по мере роста таблицы, а очистка запускалась ночью и всё равно конкурировала с рабочими запросами.

Что изменили

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

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

Результат в цифрах

• Время отклика выборок по периоду сократилось с десятков секунд до долей секунды — запрос читает одну-две секции вместо всей таблицы.
• Очистка старых данных занимает минуты вместо часов и идёт без блокировок рабочих запросов.
• Размер активной части таблицы перестал расти линейно: горячие данные отделены от архива.
• Откат упростился — при ошибке в правилах достаточно вернуть отсечённую секцию из резервной копии, не восстанавливая всю таблицу.

Такой подход мы отрабатывали на проектах с похожим профилем нагрузки — например, при миграции хранилища для ClickHouse и построении контуров аналитики. Ключевое здесь — не сам факт партиционирования, а согласованность схемы секций с реальными запросами: если условия выборки не совпадают с ключом секционирования, выигрыша не будет.

Чем отличается партиционирование от шардирования?

Партиционирование — это логическое разделение одной таблицы на секции внутри одного сервера. С точки зрения приложения таблица остаётся единым объектом: запросы идут по тому же имени, схема не меняется, драйвер и ORM ничего не знают о внутренней структуре. СУБД сама отсекает ненужные секции по ключу (partition pruning), и запрос по диапазону дат читает только свою секцию вместо всей таблицы. Шардирование — это распределение данных по нескольким независимым серверам, где каждый узел хранит свою часть строк и обслуживает свой поток запросов.

Разница проявляется в том, кто отвечает за маршрутизацию. При партиционировании это делает сама СУБД: приложение пишет и читает как раньше, а выигрыш получает за счёт отсечения секций и локальных операций — быстрее удаление старых данных через отсечение секции вместо массового удаления строк, меньше нагрузка на индексы. При шардировании маршрутизацию берёт на себя либо прослойка в приложении, либо промежуточный слой: нужно выбрать ключ шардирования, продумать, как распределятся данные, и что делать с запросами, которые затрагивают несколько узлов.

Эксплуатация отличается не меньше. Партиционированная таблица живёт на одном сервере: резервное копирование, репликация, обновление версии и мониторинг остаются привычными, а добавление новой секции — плановая операция. Шардированный кластер требует оркестрации: перебалансировка при неравномерном росте, согласованность между узлами, отдельная стратегия резервного копирования по каждому шарду и обработка отказов части кластера. Сложность растёт вместе с числом узлов.

Практический вывод: партиционирование решает задачи объёма и обслуживания одной таблицы — рост числа строк, очистка истории, ускорение выборок по ключу секционирования. Шардирование нужно, когда упирается не размер таблицы, а пропускная способность одного сервера: запись, чтение или объём, который физически не помещается на один узел. Часто их применяют вместе — сначала партиционируют внутри узла, а когда ресурс исчерпан, распределяют секции по серверам.

Частые технические сомнения при внедрении

Партиционирование затрагивает не только структуру таблицы, но и то, как планировщик выбирает путь чтения, как реплика переносит изменения и как ведёт себя приложение при откате. Ниже — то, что чаще всего проверяют перед внедрением.

Как это отразится на репликации?

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

Придётся ли переписывать приложение?

В большинстве случаев — нет. Схема остаётся той же таблицей с тем же именем, приложение продолжает писать и читать как раньше. Изменения нужны точечно: если запросы не используют ключ партиционирования, их стоит дополнить условием по этому ключу. Мы отмечаем такие места и предлагаем минимальные правки, а не переписывание слоя доступа к данным.

Можно ли откатить изменения?

Да. Партиционирование обратимо: секции можно объединить обратно в единую таблицу, а перед любыми изменениями снимается полная копия. Мы фиксируем план отката до начала работ и проверяем его на тестовом контуре, чтобы возврат к исходному состоянию занимал предсказуемое время.

Какие риски остаются?

Основной — выбор неудачного ключа: слишком мелкие секции дают много служебных операций, слишком крупные не дают выигрыша. Второй — забытые запросы без ключа, которые продолжают читать всю таблицу. Оба риска снимаются на этапе проектирования схемы и проверки планов выполнения, до того как изменения попадут в рабочую базу.

Обсудим вашу задачу по партиционированию MySQL

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

Что стоит подготовить перед разговором:

• Схемы таблиц-кандидатов: SHOW CREATE TABLE по самым крупным и быстрорастущим.
• Примерные объёмы: число строк и занимаемое место по каждой таблице.
• Профиль запросов: какие выборки идут по диапазону дат, какие по конкретному ключу, где сейчас узкие места.
• Текущие индексы и ограничения: уникальные ключи, внешние связи — они напрямую влияют на выбор стратегии.
• Версию и редакцию сервера, режим репликации, требования к отказоустойчивости.
• Ограничения по окну обслуживания: сколько времени база может быть недоступна для перестроения.
• Целевое поведение: что должно происходить со старыми данными — архивирование, удаление, вынос в холодное хранилище.

По этим данным мы определяем подходящий тип секционирования — RANGE по дате, LIST по категории или HASH по ключу распределения, — и проверяем, не конфликтует ли разбиение с существующими уникальными индексами. Дальше готовим план миграции: перестроение без длительной блокировки, порядок переключения, точки отката.

Опишите задачу — вернёмся с оценкой, планом работ и сроками старта.