LabEx
Регистрация

Секционирование MySQL для больших наборов данных

MySQLBeginner
Практиковаться сейчас

Введение

В этой лабораторной работе вы научитесь настраивать секционирование MySQL для больших наборов данных, чтобы повысить производительность запросов и упростить управление данными. Вы разделите таблицу на секции по диапазонам, используя столбец sale_date таблицы sales.

Сначала подключитесь к серверу MySQL и создайте базу данных sales_data. Затем создайте таблицу sales и разделите её на секции по году из столбца sale_date: для 2020, 2021, 2022 и 2023 годов, а также отдельную секцию для более поздних дат. Далее вы выполните запросы к отдельным секциям, реорганизуете секции с помощью ALTER TABLE и оцените влияние секционирования на скорость запросов.

Примечание: В этой лабораторной работе достаточно один раз войти в оболочку MySQL в начале и выйти из неё в конце. Все SQL-команды на следующих этапах выполняйте в одном и том же сеансе MySQL.

Создайте секционированную таблицу

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

Сначала откройте терминал в виртуальной машине LabEx. Вы уже должны находиться в каталоге ~/project.

Подключитесь к серверу MySQL от имени пользователя root. Это нужно сделать только один раз в начале лабораторной работы:

sudo mysql -u root

Теперь вы находитесь в оболочке MySQL. До конца лабораторной работы выполняйте все последующие SQL-команды в этом сеансе.

Включите запись команд в файл, чтобы проверки этапа могли считывать SQL-команды, введённые в этом сеансе, пока он открыт:

tee /home/labex/project/partitioning-session.log

Не отключайте запись до конца лабораторной работы. SQL-команды, введённые здесь, не сохраняются в истории команд терминальной оболочки.

Создайте базу данных sales_data для хранения таблицы:

CREATE DATABASE sales_data;

Переключитесь на созданную базу данных:

USE sales_data;

Теперь создайте таблицу sales и разделите её на секции по году из столбца sale_date. Создайте отдельные секции для 2020, 2021, 2022 и 2023 годов, а также секцию для всех последующих дат.

CREATE TABLE sales (
    sale_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

Разберём предложение PARTITION BY RANGE:

  • PARTITION BY RANGE (YEAR(sale_date)): задаёт секционирование таблицы по диапазонам значений, которые возвращает функция YEAR() для столбца sale_date.
  • PARTITION p2020 VALUES LESS THAN (2021): создаёт секцию p2020. В неё попадут строки, в которых год из sale_date меньше 2021, то есть строки за 2020 год.
  • PARTITION p2021 VALUES LESS THAN (2022): создаёт секцию p2021 для данных за 2021 год.
  • PARTITION p2022 VALUES LESS THAN (2023): создаёт секцию p2022 для данных за 2022 год.
  • PARTITION p2023 VALUES LESS THAN (2024): создаёт секцию p2023 для данных за 2023 год.
  • PARTITION pFuture VALUES LESS THAN MAXVALUE: создаёт секцию pFuture для данных, в которых год из sale_date — 2024 или позднее. Специальное значение MAXVALUE больше любого другого значения.

После выполнения команды CREATE TABLE проверьте структуру таблицы и её секции:

SHOW CREATE TABLE sales;

Убедитесь, что в выводе есть предложение PARTITION BY RANGE с заданными секциями.

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

INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2020-12-31', 100.00),
(2, '2021-01-15', 150.00),
(3, '2021-12-25', 200.00),
(4, '2022-06-01', 120.00),
(5, '2022-12-31', 180.00),
(6, '2023-03-10', 250.00),
(7, '2023-09-20', 300.00),
(8, '2024-01-01', 350.00);

Вы создали секционированную таблицу и добавили в неё данные. На следующем этапе вы научитесь выполнять запросы к отдельным секциям.

Выполните запросы к отдельным секциям

На этом этапе вы узнаете, как эффективно извлекать данные из секционированной таблицы, обращаясь к нужным секциям. Это одно из основных преимуществ секционирования: MySQL может просматривать только подходящие секции. Благодаря этому обрабатывается меньше данных и повышается производительность запросов.

Напоминание: Вы должны по-прежнему находиться в оболочке MySQL и использовать базу данных sales_data. Если это не так, выполните команду:

USE sales_data;

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

Например, чтобы получить все данные о продажах за 2021 год, выполните запрос ниже. Здесь используется прямое условие по диапазону значений sale_date. Если применить в условии WHERE функцию, например YEAR(sale_date), MySQL может не выполнить отсечение секций и просмотреть их все.

SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';

Чтобы узнать, к каким секциям MySQL обращается при выполнении запроса, используйте оператор EXPLAIN PARTITIONS:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';

В выводе EXPLAIN PARTITIONS найдите столбец partitions. В нём должно быть указано p2021: это означает, что для выполнения запроса MySQL просматривает только секцию p2021.

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales | p2021      | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

Можно также запросить данные, которые хранятся в нескольких секциях. Например, чтобы получить данные о продажах за 2022 и 2023 годы:

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

Повторно используйте EXPLAIN PARTITIONS, чтобы увидеть, что MySQL обращается к секциям p2022 и p2023:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

В столбце partitions будут указаны p2022,p2023.

+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales | p2022,p2023   | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+

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

Чтобы узнать количество строк в каждой секции, запросите данные из таблицы INFORMATION_SCHEMA.PARTITIONS:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

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

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          1 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          1 |
+----------------+------------+

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

Реорганизуйте секции и управляйте ими

На этом этапе вы научитесь изменять структуру секций существующей таблицы с помощью оператора ALTER TABLE. Это пригодится, если нужно адаптировать схему секционирования по мере роста объёма данных или изменения требований.

Напоминание: Вы должны по-прежнему находиться в оболочке MySQL и использовать базу данных sales_data. Если это не так, выполните команду:

USE sales_data;

Предположим, что вы хотите добавить секцию для 2024 года. Сейчас данные за 2024 год и более поздние даты находятся в секции pFuture. Нельзя добавить новую секцию с помощью ADD PARTITION, поскольку секция pFuture задана условием VALUES LESS THAN MAXVALUE и всегда должна быть последней.

Вместо этого разделите секцию pFuture с помощью REORGANIZE. Создайте две секции: p2024 для данных за 2024 год и новую секцию pFuture для всех более поздних дат.

ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

Команда берёт существующую секцию pFuture, перемещает данные за 2024 год в новую секцию p2024 и задаёт для pFuture границу, соответствующую 2025 году. Строка со значением sale_date '2024-01-01' будет перемещена в p2024.

Проверьте обновлённую структуру секций и количество строк в них:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

В результате должна появиться новая секция p2024. Строка за 2024 год теперь находится в секции p2024.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          0 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          0 |
| pFuture        |          0 |
+----------------+------------+

Теперь объедините секции. Например, можно объединить p2020 и p2021 в одну секцию с именем p2020_2021.

ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
    PARTITION p2020_2021 VALUES LESS THAN (2022)
);

Эта команда объединяет данные из p2020 и p2021 в новую секцию p2020_2021. Предложение VALUES LESS THAN (2022) задаёт верхнюю границу объединённой секции.

Снова проверьте структуру секций:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

В результате вы увидите, что секции p2020 и p2021 больше нет, а в секции p2020_2021 указано общее количество строк.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021     |          3 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          0 |
| pFuture        |          0 |
+----------------+------------+

Наконец, удалите секцию p2024. Обратите внимание: при этом будут удалены и все данные, которые в ней хранятся.

ALTER TABLE sales DROP PARTITION p2024;

В последний раз проверьте структуру секций:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Секция p2024 больше не должна отображаться в списке.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021     |          3 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          0 |
+----------------+------------+

Вы успешно реорганизовали, объединили и удалили секции с помощью ALTER TABLE. Этот пример показывает, как гибко управлять секционированными таблицами по мере изменения данных.

Оцените влияние секционирования на скорость запросов

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

Напоминание: Вы должны по-прежнему находиться в оболочке MySQL и использовать базу данных sales_data. Если это не так, выполните команду:

USE sales_data;

Чтобы оценить влияние секционирования, используйте оператор EXPLAIN: он показывает план выполнения запроса. Оператор EXPLAIN PARTITIONS дополнительно показывает, к каким секциям обращается MySQL.

Выполните запрос с фильтром по ключу секционирования — году из sale_date:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

Посмотрите на столбец partitions в выводе. В нём должна быть указана только секция p2023.

Теперь выполните запрос с фильтром не по ключу секционирования, а по другому столбцу — amount:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;

В этом случае условие запроса напрямую не связано с ключом секционирования (sale_date), поэтому MySQL может потребоваться просмотреть несколько секций или все секции, чтобы найти подходящие строки. Столбец partitions в выводе EXPLAIN PARTITIONS покажет, какие секции рассматривались. При небольшом объёме данных в этом примере MySQL может просмотреть все секции.

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

Включите профилирование:

SET profiling = 1;

Теперь повторно выполните оба запроса:

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;

Просмотрите результаты профилирования:

SHOW PROFILES;

В выводе будут перечислены выполненные запросы и время их выполнения. Чтобы изучить подробности конкретного запроса, используйте его Query_ID:

SHOW PROFILE FOR QUERY [Query_ID];

Замените [Query_ID] на идентификатор запроса из вывода SHOW PROFILES. Изучите этапы выполнения и время, затраченное на каждый из них.

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

В завершение отключите профилирование:

SET profiling = 0;

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

Итоги

В этой лабораторной работе вы научились настраивать секционирование MySQL для больших наборов данных, чтобы повысить производительность запросов и упростить управление данными. Сначала вы создали базу данных и разделили таблицу на секции по диапазонам, используя год из столбца с датой. Затем вы выполнили запросы к отдельным секциям и увидели, как MySQL использует отсечение секций для оптимизации запросов. Наконец, вы научились добавлять, разделять и объединять секции с помощью оператора ALTER TABLE, а также использовать EXPLAIN PARTITIONS и профилирование, чтобы оценить влияние секционирования на скорость запросов. Секционирование — эффективный способ управлять большими таблицами MySQL и выполнять к ним запросы.

Завершив все этапы, выйдите из оболочки MySQL, введя команду:

exit;
Связанные MySQL Курсы
MySQL для начинающих

MySQL для начинающих

databasemysql
Практические лабораторные работы по продвинутому MySQL

Практические лабораторные работы по продвинутому MySQL

databasemysql