[MySQL] 파티셔닝(Partitioning)

1. MySQL 파티셔닝(Partitioning)

파티셔닝은 테이블을 작은 조각으로 나누는 기법으로, MySQL에서는 테이블을 파티션하는 방식으로 데이터를 더 효과적으로 처리할 수 있습니다. MySQL에서 지원하는 파티셔닝 방법은 다음과 같습니다.

• RANGE 파티셔닝: 지정된 범위에 따라 데이터를 나눕니다.
• LIST 파티셔닝: 지정된 값 목록에 따라 데이터를 나눕니다.
• HASH 파티셔닝: 해시 함수를 사용하여 데이터를 나눕니다.
• KEY 파티셔닝: MySQL에서 제공하는 내부 키 해시 함수를 사용하여 파티셔닝을 수행합니다.

파티셔닝 테이블 생성 예제 

CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2021),
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

 

위 쿼리는 order_date 로 파티셔닝 테이블을 생성하는 예제 

 

그럼 이제 스톡데이터를 저장하던 테이블을 파티셔닝 테이블로 새로 생성하고 성능을 비교해보잣

기존 테이블 생성 쿼리 

CREATE TABLE `stock_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company` varchar(100) NOT NULL,
  `company_code` varchar(20) NOT NULL,
  `trading_date` date NOT NULL,
  `open` float NULL,
  `high` float NULL,
  `low` float NULL,
  `close` float NULL,
  `adj_close` float NULL,
  `volume` bigint NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_code`, `trading_date`),
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

파티셔닝 테이블로 변경 

CREATE TABLE `stock_data_partition` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company` varchar(100) NOT NULL,
  `company_code` varchar(20) NOT NULL,
  `trading_date` date NOT NULL,
  `open` float DEFAULT NULL,
  `high` float DEFAULT NULL,
  `low` float DEFAULT NULL,
  `close` float DEFAULT NULL,
  `adj_close` float DEFAULT NULL,
  `volume` bigint DEFAULT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_code`,`trading_date`),
  UNIQUE KEY `unique_id` (`id`, `trading_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (YEAR(trading_date)) (
    PARTITION p0 VALUES LESS THAN (2021),
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023),
    PARTITION p3 VALUES LESS THAN (2024),
    PARTITION p4 VALUES LESS THAN (2025),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

 

MySQL에서는 파티셔닝된 테이블에서 UNIQUE 인덱스가 정의될 때, 해당 인덱스에 포함된 모든 컬럼이 파티셔닝 함수에서 사용된 컬럼도 포함되어야 한다는 규칙이 있음

 

즉, 파티셔닝을 할 때 trading_date 컬럼을 기준으로 파티셔닝을 하고 있는데, UNIQUE KEY unique_id에 사용된 id 컬럼은 파티셔닝 함수(YEAR(trading_date))와 관련이 없어. 이 때문에 MySQL에서 이 제약 조건 위반으로 인해 오류가 발생

 

UNIQUE KEY `unique_id` (`id`, `trading_date`)

 

이렇게 해준다.