소개
이 실습에서는 대규모 데이터셋의 쿼리 성능과 데이터 관리를 개선하기 위해 MySQL 파티셔닝을 구현하는 방법을 배웁니다. sales 테이블의 sale_date 열을 기준으로 범위 파티셔닝을 수행합니다.
먼저 MySQL 서버에 연결하고 sales_data 데이터베이스를 만듭니다. 다음으로 sales 테이블을 만들고 sale_date의 연도를 기준으로 2020년, 2021년, 2022년, 2023년 파티션과 미래 날짜를 위한 파티션을 구성합니다. 이어서 특정 파티션의 데이터를 조회하고, ALTER TABLE로 파티션을 재구성한 다음, 파티셔닝이 쿼리 속도에 미치는 영향을 확인합니다.
참고: 이 실습에서는 처음에 MySQL 셸에 한 번 접속하고 마지막에 종료하면 됩니다. 이후 단계의 모든 SQL 명령은 동일한 MySQL 세션에서 실행해야 합니다.
파티션 테이블 만들기
이 단계에서는 MySQL에서 데이터베이스와 파티션 테이블을 만듭니다. 파티셔닝은 지정한 규칙에 따라 테이블을 더 작고 관리하기 쉬운 부분으로 나누므로 대규모 데이터셋을 관리하는 데 유용합니다. 특히 파티션 키를 기준으로 데이터를 필터링하는 쿼리의 성능을 크게 개선할 수 있습니다.
먼저 LabEx VM에서 터미널을 엽니다. 현재 작업 디렉터리는 ~/project여야 합니다.
root 사용자로 MySQL 서버에 연결합니다. 실습을 시작할 때 한 번만 실행하면 됩니다.
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)):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이 파티션 프루닝을 적용하지 못해 모든 파티션을 스캔할 수 있습니다.
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이 필요한 파티션만 스캔하는 파티션 프루닝의 원리는 확인할 수 있습니다. 데이터셋이 클수록 그 효과는 훨씬 뚜렷해집니다.
확인: 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];
[Query_ID]를 SHOW PROFILES 출력에서 분석할 쿼리의 ID로 바꿉니다. 실행 단계별 내용과 각 단계에 걸린 시간을 확인합니다.
작은 데이터셋에서는 실행 시간 차이가 거의 없을 수 있습니다. 하지만 실제 환경에서 행이 수백만 개라면 파티션 프루닝을 활용할 수 있는 쿼리(예: YEAR(sale_date)로 필터링하는 쿼리)가 여러 파티션 또는 모든 파티션을 스캔해야 하는 쿼리보다 훨씬 빠르게 실행됩니다.
마지막으로 프로파일링을 비활성화합니다.
SET profiling = 0;
이 단계에서는 EXPLAIN PARTITIONS와 프로파일링을 사용해 파티셔닝이 쿼리 실행 및 성능에 미치는 영향을 확인했습니다.
요약
이 실습에서는 대규모 데이터셋의 쿼리 성능과 데이터 관리를 개선하기 위해 MySQL 파티셔닝을 구현하는 방법을 배웠습니다. 날짜 열의 연도를 기준으로 범위 파티셔닝을 적용한 데이터베이스와 테이블을 만들었습니다. 그런 다음 특정 파티션의 데이터를 조회하고 MySQL이 쿼리 최적화를 위해 파티션 프루닝을 사용하는 것을 확인했습니다. 마지막으로 ALTER TABLE 문을 사용해 파티션을 추가하고 나누고 병합하여 재구성하는 방법을 배웠으며, EXPLAIN PARTITIONS와 프로파일링으로 파티셔닝이 쿼리 속도에 미치는 영향을 살펴봤습니다. 파티셔닝은 MySQL에서 대규모 테이블을 효율적으로 관리하고 조회하는 데 유용한 기법입니다.
모든 단계를 마쳤다면 다음을 입력해 MySQL 셸을 종료합니다.
exit;
