Партиционирование таблиц в MySQL (partition by) кратко

Привет, Вы узнаете о том , что такое партиционирование, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое партиционирование, partition , настоятельно рекомендую прочитать все из категории MySql (Maria DB).

Начиная с версии 5.1 mySQL поддерживает горизонтальное партицирование таблиц. Что это такое? партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.. На нижнем уровне для myISAM таблиц, это физически разные файлы, по 3 на каждую партицию (описание таблицы, файл индексов, файл данных). Для innoDB таблиц в конфигурации по умолчанию – разные пространства таблиц в файлах innoDB (не забываем, что innoDB позволяет настраивать индивидуальные хранилища на уровне баз данных или даже конкретных таблиц).

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

Партицирование может принести следующие преимущества:

1. Увеличение производительности запросов: Партиционирование позволяет распараллеливать выполнение запросов на разных разделах таблицы, что может значительно ускорить выполнение запросов.

2. Улучшение управления данными: Благодаря партиционированию можно эффективно управлять большими объемами данных. Например, можно легко добавлять или удалять отдельные партиции без значительного влияния на остальные данные.

3. Улучшение доступности данных: Партицирование позволяет распределить данные по разным физическим устройствам или файловым системам. Это может уменьшить риск потери данных в случае сбоя или отказа одного из устройств.

Как это использовать и создать?

CREATE TABLE orders_range (
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = MYISAM
PARTITION BY RANGE( YEAR(order_date) ) (
PARTITION p_old VALUES LESS THAN(2038),
PARTITION p_2038 VALUES LESS THAN(2039),
PARTITION p_2039 VALUES LESS THAN(MAXVALUE)
);

Что мы получаем? Первая «таблица» будет хранить данные за «архивный» период, до 2038го года, вторая — за 2038й год, и «третья» — все остальное.

Самое вкусное — запросы при этом совершенно не надо переписывать/оптимизировать:

select * from orders_range where order_date='20039-08-01';

И вот что при этом происходит:



Мы видим, что при выполнении этого запроса работа будет идти исключительно с «подтаблицей» p_2038.

Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления. Так вот как раз «первые» этапы, в данном случае будут происходить гораздо быстрее.

Какие еще есть преимущества?

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

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

Какие способы «разделения» данных предоставляет MySQL?

1. RANGE

По диапазону значений

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

2. LIST

По точному списку значений

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 которых идут в разнобой) то иногда есть смысл перечислять их принудительно.

3. HASH

PARTITION BY HASH(store_id)
PARTITIONS 4;

Вы никак не управляете партицированием, просто указываете, по какому полю строить хеш и сколько «подтаблиц» создавать. Зачем? Гораздо быстрее происходит выборка по указанному полю. В некоторых случаях позволяет достигнуть «равномерного разброса» и ускорения записи данных.

4. KEY

Почти то же самое что и HASH, но более логично — по ключу.

PARTITION BY KEY(s1)
PARTITIONS 10;

Т.е. выборка по указанному ключевому полю происходит максимально эффективно.

Но тут так же следует определиться со способом партицирования. Хорошо подходит для счетчика посетителей, когда его логин является единственным идентификатором, по которому необходимо выбирать все остальные данные.

Чтобы создать партицию в MySQL, необходимо выполнить следующие шаги:

1. Создать таблицу с определением партиций. Например:

В данном примере таблица "mytable" партиционируется по году создания "created_date". Об этом говорит сайт https://intellect.icu . Таблица разбивается на 3 партиции: p0 (до 2010 года), p1 (до 2020 года) и p2 (все остальные значения).

2. Вставить данные в таблицу. При вставке данных MySQL автоматически размещает их в соответствующих партициях в соответствии с заданными правилами разбиения.

3. Выполнить запросы к таблице. MySQL будет автоматически оптимизировать выполнение запросов, используя доступные партиции.

4. Можно также управлять существующими партициями, добавлять новые или удалять существующие. Например:

В первом примере добавляется новая партиция "p3" для значений, меньших чем 2030 год. Во втором примере удаляется партиция "p0".

Важно отметить, что партиционирование поддерживается не всеми версиями MySQL. Версия MySQL 5.1 и более поздние версии поддерживают партиционирование InnoDB. Партиционирование также доступно в MySQL Cluster и MariaDB.

Какого партичирования еще нет?

Нет вертикального партицирования. Это когда разные столбцы (поля) находятся в разных «подтаблицах». Поскольку иногда это бывает полезно, вы можете достичь этого самостоятельно, пусть даже не так прозрачно: разделить таблицу на две, связав их по первичному ключу. Если вам совсем хочется красоты — можете дополнительно создать по ним VIEW, например для того что бы не переписывать старые части кода.

Зачем это делать? Например, в таблице, где у вас в основном числа и даты, есть одно поле VARCHAR (255) для комментариев, которое используется на порядок реже чем остальные поля. В случае если его вынести в другую таблицу, то мы получим фиксированный размер строки (mySQL сможет совершенно точно вычислять позицию нужной строки по индексу в файле данных). Таблица станет более устойчивой к сбоям в случае внештатных ситуаций (опять же, из-за фиксированного размера строки). Ну и существенно уменьшится сам размер таблицы.

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

PARTITION BY RANGE( TO_DAYS(order_date) ) (
PARTITION y2039m1 VALUES LESS THAN( TO_DAYS('20039-02-01') ),
PARTITION y2039m2 VALUES LESS THAN( TO_DAYS('2039-03-01') ),
PARTITION y2039m3 VALUES LESS THAN( TO_DAYS('2039-04-01') )
);

PS: В mysql всегда приходится немного «исхитриться», так что скучно с ней не будет никогда, а мы в свою очередь никогда не останемся без работы

Прочтение данной статьи про партиционирование позволяет сделать вывод о значимости данной информации для обеспечения качества и оптимальности процессов. Надеюсь, что теперь ты понял что такое партиционирование, partition и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории MySql (Maria DB)