mysql 파티션 테이블의 유의점 들

mysql 파티션 테이블 

# 일반테이블의 파티션 전환시 유의점 

1. 인덱스 제약:
   - MySQL의 파티션 테이블에서는 모든 인덱스가 로컬 인덱스로 처리된다.
   - 글로벌 인덱스를 지원하지 않으므로, 기존 인덱스 구조를 재검토해야 함.

2. 유니크 키와 프라이머리 키 제약:
   - 파티션 키는 모든 유니크 인덱스(프라이머리 키 포함)의 일부 또는 모든 칼럼을 포함해야 함. 아닐경우 에러남
   - 기존 테이블의 키 구조가 이 요구사항을 충족하는지 확인 필수 

3. 외래 키 제약:
   - 파티션 테이블에는 외래 키를 설정 불가 (성능, 관리적 측면의 설계결정)
   - 기존 테이블에 외래 키가 있다면 이를 제거하거나 대체 방안을 마련 필요.

4. 파일 제한:
   - 파티션 테이블은 각 파티션마다 별도의 파일을 사용하므로, 서버의 open-files-limit 설정을 적절히 조정필요.

5. 성능 고려:
   - 파티션 개수가 많아질수록 오히려 성능이 저하될 수 있다.
   - 특히 INSERT 작업의 경우, 파티션 테이블이 일반 테이블보다 느려질 수 있다.

6. 잠금 처리:
   - 파티션 테이블에 쿼리 실행 시 모든 파티션에 대해 잠금이 걸린다.
   - 이는 동시성 처리에 영향을 줄 수 있으므로 주의 필요.

7. 파티션 키 선택:
   - 효과적인 파티션 프루닝을 위해 적절한 파티션 키를 선택해야 함.
   - 주로 조회 조건에 자주 사용되는 칼럼을 파티션 키로 선택하는 것이 유리 

8. 데이터 마이그레이션:
   - 기존 데이터를 새로운 파티션 구조로 마이그레이션하는 과정에서 서비스 중단이 필요한지 확인 필요
   - 대량의 데이터 이동이 필요하므로 충분한 시간과 리소스를 확보해야 함.

9. 쿼리 최적화:
   - 기존 쿼리들이 파티션 구조에서도 효율적으로 동작하는지 검토, 필요시 최적화 필요.

이러한 점들을 고려하여 신중하게 파티션 전환을 계획하고 실행해야 한다. 또한 전환 전후로 충분한 테스트를 수행하여 성능과 기능에 문제가 없는지 확인해야 합니다.

 

# 파티션 테이블 샘플 스크립트 

-- 1. 날짜 기반 레인지 파티션 테이블 생성
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- 2. 파티션 관리: 새 파티션 추가
ALTER TABLE sales ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

-- 3. 파티션 최적화
ALTER TABLE sales OPTIMIZE PARTITION p2021, p2022, p2023;

-- 4. 특정 파티션 데이터 삭제
ALTER TABLE sales TRUNCATE PARTITION p2021;

-- 5. 파티션 삭제
ALTER TABLE sales DROP PARTITION p2021;

-- 6. 파티션 분할
ALTER TABLE sales REORGANIZE PARTITION p2023 INTO (
    PARTITION p2023_h1 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023_h2 VALUES LESS THAN (2024)
);

-- maxvalue 파티션 분할 

ALTER TABLE sales
REORGANIZE PARTITION future INTO (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- 7. 파티션 정보 확인
SELECT PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'sales';

-- 8. 파티션을 고려한 쿼리 예시
SELECT * FROM sales PARTITION (p2023)
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

 

# 파티션 키의 제약 

파티션 키가 반드시 프라이머리 키(PK)를 포함해야 하는 것은 아닙니다. 하지만 MySQL의 파티션 테이블에는 중요한 제약사항이 있습니다:

1. 유니크 키 제약:
   - 테이블에 유니크 인덱스(프라이머리 키 포함)가 있는 경우, 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 함. (일부만 포함도 가능)

2. 설계 고려사항:
   - 파티션 키 선택 시 데이터 분포, 쿼리 패턴, 성능 요구사항 등을 고려해야 합니다.

결론적으로, 파티션 키가 반드시 프라이머리 키 전체를 포함할 필요는 없지만, 프라이머리 키를 포함한 모든 유니크 키의 일부 또는 전체 칼럼을 포함하며, 이는 데이터 무결성과 쿼리 성능을 위함이다.