Налаштування партиціонування таблиць 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 за ключем розподілу, — і перевіряємо, чи не конфліктує розбиття з наявними унікальними індексами.

Далі готуємо план міграції: перебудова без тривалого блокування, порядок перемикання, точки відкату.

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