대규모 데이터셋을 위한 MySQL 파티셔닝

소개

이 랩에서는 대규모 데이터셋에 대한 MySQL 파티셔닝을 구현하여 쿼리 성능 및 데이터 관리를 개선하는 방법을 배웁니다. 이 랩은 sales 테이블의 sale_date 열을 사용하여 범위를 기준으로 테이블을 파티셔닝하는 데 중점을 둡니다.

MySQL 서버에 연결하고 sales_data 데이터베이스를 생성하는 것으로 시작합니다. 그런 다음 sales 테이블을 생성하고 sale_date의 연도를 기준으로 2020 년, 2021 년, 2022 년, 2023 년 및 향후 파티션으로 분할합니다. 후속 단계에서는 특정 파티션에서 데이터를 쿼리하고, ALTER TABLE을 사용하여 파티션을 재구성하고, 쿼리 속도에 대한 파티셔닝의 영향을 확인하는 방법을 다룹니다.

참고: 이 랩에서는 처음에 MySQL 셸에 한 번만 연결하고 마지막에 종료하면 됩니다. 다음 단계의 모든 SQL 명령은 동일한 MySQL 세션 내에서 실행해야 합니다.

파티셔닝된 테이블 생성

이 단계에서는 MySQL 에 데이터베이스와 파티션된 테이블을 생성합니다. 파티셔닝은 지정된 규칙에 따라 테이블을 더 작고 관리하기 쉬운 부분으로 분할하여 대규모 데이터셋을 관리하는 데 도움이 됩니다. 특히 파티셔닝 키를 기준으로 데이터를 필터링하는 쿼리의 성능을 크게 향상시킬 수 있습니다.

먼저 LabEx VM 에서 터미널을 엽니다. 이미 ~/project 디렉토리에 있어야 합니다.

루트 사용자로 MySQL 서버에 연결합니다 (랩 시작 시 한 번만 수행):

sudo mysql -u root

이제 MySQL 셸에 접속했습니다. 랩이 끝날 때까지 후속 SQL 명령은 이 세션에서 실행해야 합니다.

테이블을 저장할 sales_data라는 데이터베이스를 생성해 보겠습니다.

CREATE DATABASE sales_data;

새로 생성된 데이터베이스로 전환합니다.

USE sales_data;

이제 sales라는 테이블을 생성하고 sale_date 열의 연도를 기준으로 파티셔닝합니다. 2020 년, 2021 년, 2022 년, 2023 년 및 향후 날짜에 대한 캐치올 (catch-all) 파티션을 생성합니다.

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)): sale_date 열에 적용된 YEAR() 함수의 반환 값 범위를 기준으로 테이블이 파티셔닝됨을 지정합니다.
• PARTITION p2020 VALUES LESS THAN (2021): p2020이라는 파티션을 생성합니다. sale_date의 연도가 2021 년 미만 (즉, 2020 년) 인 모든 행은 이 파티션에 저장됩니다.
• PARTITION p2021 VALUES LESS THAN (2022): 2021 년 데이터에 대한 p2021이라는 파티션을 생성합니다.
• PARTITION p2022 VALUES LESS THAN (2023): 2022 년 데이터에 대한 p2022이라는 파티션을 생성합니다.
• PARTITION p2023 VALUES LESS THAN (2024): 2023 년 데이터에 대한 p2023이라는 파티션을 생성합니다.
• PARTITION pFuture VALUES LESS THAN MAXVALUE: sale_date의 연도가 2024 년 이상인 모든 데이터를 저장할 pFuture라는 파티션을 생성합니다. 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 의 쿼리 옵티마이저는 WHERE 절을 기반으로 어떤 파티션이 관련 있는지 파악할 만큼 충분히 똑똑한 경우가 많습니다.

예를 들어, 2021 년의 모든 판매를 검색하려면 다음 쿼리를 사용할 수 있습니다. sale_date에 직접 범위 조건을 사용하고 있음에 유의하십시오. WHERE 절에서 YEAR(sale_date)와 같은 함수를 사용하면 MySQL 이 파티션 프루닝 (partition pruning) 을 사용하지 못하게 되어 모든 파티션을 스캔하게 될 수 있습니다.

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 파티션에 있습니다. pFuture 파티션이 VALUES LESS THAN MAXVALUE로 정의되어 항상 마지막 파티션이어야 하므로 ADD PARTITION으로 새 파티션을 추가할 수 없습니다.

대신 pFuture 파티션을 REORGANIZE하여 분할해야 합니다. pFuture를 두 개의 새 파티션으로 분할합니다. 하나는 2024 년용 (p2024) 이고 다른 하나는 그 이후의 모든 것을 위한 새 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 이 필요한 파티션만 스캔하는 파티션 프루닝 (partition pruning) 의 원리를 여전히 관찰할 수 있습니다. 데이터셋이 더 클수록 이 효과는 훨씬 더 두드러집니다.

알림: 여전히 MySQL 셸에 있고 sales_data 데이터베이스를 사용 중이어야 합니다. 그렇지 않은 경우 다음을 사용하십시오.

USE sales_data;

파티셔닝의 영향을 관찰하기 위해 쿼리의 실행 계획을 보여주는 EXPLAIN 문을 사용할 수 있습니다. 특히 EXPLAIN PARTITIONS는 어떤 파티션에 액세스하는지 보여줍니다.

파티셔닝 키 (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 은 일치하는 행을 찾기 위해 여러 파티션 또는 모든 파티션을 스캔해야 할 수 있습니다. EXPLAIN PARTITIONS 출력의 partitions 열은 어떤 파티션이 고려되었는지 보여줍니다. 작은 데이터셋에서는 여전히 모든 파티션을 스캔할 수 있습니다.

쿼리 실행 프로세스와 소요 시간에 대한 더 자세한 정보를 얻으려면 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];

SHOW PROFILES 출력에서 분석하려는 쿼리의 ID 로 [Query_ID]를 바꾸십시오. 다양한 실행 단계와 각 단계에서 소요된 시간을 확인하십시오.

작은 데이터셋에서는 시간 차이가 미미할 수 있지만, 수백만 행이 있는 실제 시나리오에서는 파티션 프루닝을 활용할 수 있는 쿼리 (예: YEAR(sale_date)로 필터링하는 쿼리) 가 여러 파티션 또는 모든 파티션을 스캔해야 하는 쿼리보다 훨씬 빠릅니다.

마지막으로 프로파일링을 비활성화합니다.

SET profiling = 0;

이 단계에서는 EXPLAIN PARTITIONS와 프로파일링을 사용하여 파티셔닝이 쿼리 실행 및 성능에 어떻게 영향을 미치는지 이해하는 방법을 시연했습니다.

요약

이 실습에서는 MySQL 파티셔닝을 대규모 데이터셋에 구현하여 쿼리 성능과 데이터 관리를 개선하는 방법을 배웠습니다. 데이터 열의 연도를 기준으로 범위별로 파티셔닝된 데이터베이스와 테이블을 생성하는 것으로 시작했습니다. 그런 다음 특정 파티션에서 데이터를 쿼리하는 연습을 하고 MySQL 이 파티션 프루닝을 사용하여 쿼리를 최적화하는 방법을 관찰했습니다. 마지막으로 ALTER TABLE 문을 사용하여 파티션을 추가, 분할 및 병합하여 재구성하는 방법을 배우고 EXPLAIN PARTITIONS 및 프로파일링을 사용하여 파티셔닝이 쿼리 속도에 미치는 영향을 이해하는 방법을 탐구했습니다. 파티셔닝은 MySQL 에서 대규모 테이블을 효율적으로 관리하고 쿼리하는 강력한 기술입니다.

모든 단계를 완료하면 다음을 입력하여 MySQL 셸을 종료할 수 있습니다.

exit;