Партіціонірованіе таблиць в mysql black box

Партіціонірованіе таблиць в mySQL

Починаючи з версії 5.1 mySQL підтримує горизонтальне партіцірованіе таблиць. Що це таке? Партіціонірованіе (partitioning) - це розбиття великих таблиць на логічні частини за обраними критеріями. На нижньому рівні для myISAM таблиць, це фізично різні файли, по 3 на кожну партіціі (опис таблиці, файл індексів, файл даних). Для innoDB таблиць в конфігурації за замовчуванням - різні простору таблиць в файлах innoDB (не забуваємо, що innoDB дозволяє налаштовувати індивідуальні сховища на рівні баз даних або навіть конкретних таблиць).

Як це виглядає?

Найсмачніше - запити при цьому абсолютно не треба переписувати / оптимізувати:

І ось що при цьому відбувається:

Більш того, прискорення досягається навіть у разі виконання запитів, які охоплюють усі дані у всіх партіціі - адже в цьому випадку спочатку відбувається первинна «обробка» таблиць по менше, потім дані об'єднуються і виробляються фінальні обчислення. Так ось як раз «перші» етапи, в даному випадку будуть відбуватися набагато швидше.

Які ще є переваги?

Головною перевагою я б назвав той факт, що розбиття з «оперативними» даними (тобто останніми, за якими найбільш часто відбувається вибірка) мають мінімальний розмір, і як наслідок, можуть постійно знаходиться в оперативній пам'яті.

Якщо у вас є таблиця логів, в яку безперервно йде запис і жорсткі диски не встигають, а ставити рейд вам не дозволяє релігія, ви можете налаштувати партіціонірованіе по хеш-функції, і вказати по одній партіціі на кожен доступний вам жорсткий диск. В такому випадку, нові дані будуть рівномірно писатися на все жорсткі диски.

Які способи «поділу» даних надає mySQL?

За діапазону значень

PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30)
);

За влучним списку значень

PARTITION BY LIST (store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20)
)

Навіщо, запитаєте ви? Розбивати на партіціі необхідно або виходячи з міркувань оптимізації вибірки (що частіше) або виходячи з міркувань оптимізації записи (рідше). Відповідно, ідеальний варіант - це коли ви розбиваєте таблицю на максимально можливу кількість партіцій так, що б 90% всіх вибірок відбувалося в межах однієї партіціі. І якщо у вас складна логіка вибірки (наприклад, об'єкти розташовані в північних кварталах міста, ID яких йдуть в різнобій) то іноді є сенс перераховувати їх примусово.

PARTITION BY HASH (store_id)
PARTITIONS 4;

Ви ніяк не керуєте партіцірованіем, просто вказуєте, по якому полю будувати хеш і скільки «підтаблиць» створювати. Навіщо? Набагато швидше відбувається вибірка за вказаною полю. У деяких випадках дозволяє досягти «рівномірного розкиду» і прискорення запису даних.

Майже те ж саме що і HASH, але більш логічно - по ключу.

PARTITION BY KEY (s1)
PARTITIONS 10;

Тобто вибірка за вказаною ключовому полю відбувається максимально ефективно.

Але тут так само слід визначитися зі способом партіцірованія. Добре підходить для лічильника відвідувачів, коли його логін є єдиним ідентифікатором, за яким необхідно вибирати всі інші дані.

Немає вертикального партіцірованія. Це коли різні стовпці (поля) знаходяться в різних «підтаблицях». Оскільки іноді це буває корисно, ви можете досягти цього самостійно, нехай навіть не так прозоро: розділити таблицю на дві, зв'язавши їх по первинному ключу. Якщо вам дуже хочеться краси - можете додатково створити по ним VIEW, наприклад для того що б не переписувати старі частини коду.

І закінчуючи статтю приведу приклад більш «реального» партіцірованія таблиць - помісячно. Так як LIST / RANGE приймають тільки цілочисельні значення, то треба трохи схитриться:

PS: У mysql завжди доводиться трохи «примудритися», так що нудно з нею не буде ніколи, а ми в свою чергу ніколи не залишимося без роботи :)

Чи був ця відповідь? Та ні

На жаль, ми не змогли допомогти вам у вирішенні проблеми. Ваш відгук дозволить нам поліпшити цю статтю.