MySQL - Вертикальное partitioning

Здравствуйте, начинающие энтузиасты баз данных! Сегодня мы погружаемся в fascinierende мир вертикального partitioning в MySQL. Как ваш доброжелательный соседский учитель компьютера, я рад помочь вам в этом путешествии, даже если вы完全不 знакомы с программированием. Не волнуйтесь; мы будем двигаться шаг за шагом, и скоро вы будете partitioning данные как профи!

Вертикальное partitioning в MySQL

Представьте, что у вас есть большая коробка с игрушками (это наша база данных), и вы хотите лучше их организовать. Вертикальное partitioning похоже на то, как вы sort你们的 игрушки по разным маленьким коробкам в зависимости от их характеристик. В терминах базы данных это процесс разделения столбцов таблицы на separate таблицы.

Давайте начнем с простого примера:

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
phone VARCHAR(20),
address TEXT
);

Это наша original таблица students. Теперь давайте применяем вертикальное partitioning:

CREATE TABLE students_main (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

CREATE TABLE students_contact (
id INT PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20)
);

CREATE TABLE students_address (
id INT PRIMARY KEY,
address TEXT
);

Здесь мы split нашу original таблицу на три меньшие таблицы. Это может улучшить производительность, особенно если вы часто accesse только определенные столбцы.

Почему использовать вертикальное partitioning?

1. Улучшенная производительность запросов
2. Лучшая управление данными
3. Усиленная безопасность (вы можете restrict доступ к sensetive данным)

Теперь давайте посмотрим, как мы можем insert и retrieve данные с этой новой структурой:

-- Вставка данных
INSERT INTO students_main VALUES (1, 'Алиса', 20);
INSERT INTO students_contact VALUES (1, '[email protected]', '123-456-7890');
INSERT INTO students_address VALUES (1, '123 Main St, Anytown, USA');

-- Получение данных
SELECT m.id, m.name, c.email, a.address
FROM students_main m
JOIN students_contact c ON m.id = c.id
JOIN students_address a ON m.id = a.id
WHERE m.id = 1;

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

Partitioning по диапазону столбцов

Теперь давайте повысим уровень и поговорим о partitioning по диапазону столбцов. Это как organizzing ваши книги по году их издания, но для нескольких столбцов.

Давайте рассмотрим пример с таблицей sales:

CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE COLUMNS(sale_date, amount) (
PARTITION p0 VALUES LESS THAN ('2023-01-01', 1000),
PARTITION p1 VALUES LESS THAN ('2023-01-01', 5000),
PARTITION p2 VALUES LESS THAN ('2023-07-01', 1000),
PARTITION p3 VALUES LESS THAN ('2023-07-01', 5000),
PARTITION p4 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

В этом примере мы partitioning на основе как sale_date, так и amount. Это позволяет более granular control над тем, как данные распределяются.

Давайте вставим некоторые данные и посмотрим, как это работает:

INSERT INTO sales VALUES
(1, '2022-12-15', 500),
(2, '2022-12-20', 2000),
(3, '2023-03-10', 800),
(4, '2023-03-15', 3000),
(5, '2023-08-01', 1500);

-- Давайте проверим, в какую partition попала каждая запись
SELECT *, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sales';

Этот запрос покажет вам, в какую partition каждая запись была assigned на основе наших критериев диапазона.

Partitioning по списку столбцов

Last but not least, давайте рассмотрим partitioning по списку столбцов. Это как sort ваши衣服 по цвету и типу - у вас может быть ящик для красных рубашек, другой для синих брюк и т.д.

Давайте рассмотрим пример с таблицей products:

CREATE TABLE products (
id INT NOT NULL,
name VARCHAR(50),
category VARCHAR(20),
sub_category VARCHAR(20)
)
PARTITION BY LIST COLUMNS(category, sub_category) (
PARTITION p_electronics VALUES IN (('Electronics', 'Phones'), ('Electronics', 'Laptops')),
PARTITION p_clothing VALUES IN (('Clothing', 'Shirts'), ('Clothing', 'Pants')),
PARTITION p_other VALUES IN (('Books', 'Fiction'), ('Books', 'Non-fiction'))
);

Теперь добавим некоторые продукты:

INSERT INTO products VALUES
(1, 'iPhone 12', 'Electronics', 'Phones'),
(2, 'MacBook Pro', 'Electronics', 'Laptops'),
(3, 'Levi\'s Jeans', 'Clothing', 'Pants'),
(4, 'Harry Potter', 'Books', 'Fiction');

-- Давайте проверим, в какую partition попал каждый продукт
SELECT *, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'products';

Этот запрос покажет вам, как каждый продукт был отсортирован в свою соответствующую partition на основе его категории и подкатегории.

Сравнение методов partitioning

Метод	Случай использования	Преимущества	Недостатки
Вертикальное partitioning	Большие таблицы с редко используемыми столбцами	Улучшенная производительность запросов, лучшее управление данными	Более сложные объединения для полного извлечения данных
Partitioning по диапазону столбцов	Данные на основе времени или числового диапазона	Эффективен для запросов по диапазону, хорош для исторических данных	Потенциальное неравномерное распределение данных
Partitioning по списку столбцов	Категориальные данные с несколькими атрибутами	Точный контроль над размещением данных, хорош для сложных категорий	Может стать непрактичным при большом количестве категорий

И вот оно,folks! Мы прошли через мир вертикального partitioning в MySQL, explored сферы partitioning по диапазону столбцов и进军или территорию partitioning по списку столбцов. Помните, как и при организации вашей комнаты, ключ к хорошему дизайну базы данных - это placing вещи там, где они имеют наибольший смысл и最容易 найти. Продолжайте практиковаться, и скоро вы станете магом по организации баз данных!

Credits: Image by storyset