MySQL - 수직 파티션링

안녕하세요, 데이터베이스 열정가 여러분! 오늘은 MySQL 수직 파티션링의 fascineting 세계로 함께潜入합니다. 여러분의 친절한 이웃 컴퓨터 선생님이자 저는 이 여정을 안내해 드리는 것을 기쁘게 생각합니다. 프로그래밍에 전혀 익숙하지 않으신 분이라도 걱정하지 마세요. 우리는 단계별로 진행하겠습니다. 얼마 지나지 않아 프로처럼 데이터를 파티션링할 수 있을 것입니다!

MySQL - Vertical Partitioning

MySQL 수직 파티션링

자, 큰 상자 안에 많은 장난감(데이터베이스)이 있다고 상상해 봅시다. 이를 더 잘 정리하고 싶다면 어떻게 하겠습니까? 수직 파티션링은 장난감을 그 특성에 따라 다른 작은 상자로 나누는 것과 같습니다. 데이터베이스 용어로는 테이블의 열을 별도의 테이블로 나누는 과정입니다.

간단한 예제로 시작해 보겠습니다:

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

이것이 원래 students 테이블입니다. 이제 수직 파티션링을 적용해 보겠습니다:

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
);

여기서 우리는 원래 테이블을 세 개의 더 작은 테이블로 나누었습니다. 이렇게 하면 성능을 향상시킬 수 있으며, 특정 열에 자주 접근하는 경우 특히 그렇습니다.

수직 파티션링을 사용하는 이유는 무엇인가요?

쿼리 성능 향상
더 나은 데이터 관리
향상된 보안 (민감한 데이터에 대한 접근을 제한할 수 있습니다)

이제 새로운 구조로 데이터를 삽입하고 검색하는 방법을 보겠습니다:

-- 데이터 삽입
INSERT INTO students_main VALUES (1, 'Alice', 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;

이렇게 하면 학생에 대한 모든 정보를 여전히 얻을 수 있지만, 더 효율적으로 저장됩니다.

범위 열 파티션링

이제 한 단계 더 발전하여 범위 열 파티션링에 대해 이야기해 보겠습니다. 이는 책을 출판 연도별로 정리하는 것과 같지만, 여러 열에 대해 적용됩니다.

다음은 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)
);

이 예제에서 우리는 sale_date와 amount에 대해 파티션링을 합니다. 이를 통해 데이터 분포에 대해 더 세밀한 제어가 가능합니다.

데이터를 삽입하고 작동 방식을 확인해 보겠습니다:

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);

-- 각 레코드가 어떤 파티션에 할당되었는지 확인
SELECT *, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sales';

이 쿼리는 범위 기준에 따라 각 레코드가 할당된 파티션을 보여줍니다.

목록 열 파티션링

마지막으로 목록 열 파티션링에 대해 탐구해 보겠습니다. 이는 옷을 색상과 종류에 따라 정리하는 것과 같습니다. - 각 종류의 상자를 가지고 있습니다.

다음은 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');

-- 각 제품이 어떤 파티션에 정렬되었는지 확인
SELECT *, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'products';

이 쿼리는 각 제품이 카테고리와 서브 카테고리에 따라 어떤 파티션에 정렬되었는지 보여줍니다.

파티션링 방법 비교

방법	사용 사례	장점	단점
수직 파티션링	큰 테이블에서 자주 사용되지 않는 열	쿼리 성능 향상, 더 나은 데이터 관리	전체 데이터 검색을 위한 복잡한 조인 필요
범위 열 파티션링	시간 기반 또는 숫자 범위 데이터	범위 기반 쿼리에 효율적, 역사 데이터에 적합	데이터 분포가 불균형할 수 있습니다
목록 열 파티션링	다중 속성을 가진 카테고리 데이터	데이터 배치에 정확한 제어, 복잡한 분류에 적합	많은 카테고리가 있을 때 관리하기 어려울 수 있습니다

이제 여러분은 MySQL 수직 파티션링, 범위 열 파티션링, 그리고 목록 열 파티션링에 대해 배웠습니다. 방이 정리되는 것처럼, 데이터베이스 설계의 열쇠는 가장 합리적이고 쉽게 찾을 수 있는 곳에 것을 두는 것입니다. 계속 연습하면 곧 데이터베이스 정리 마법사가 될 것입니다!

Credits: Image by storyset