따봉 구걸남's 블로그

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

 

MySQL의 innodb_read_only와 read_only 파라미터는 비슷한 목적으로 사용되지만 몇 가지 중요한 차이점이 있다

1. 적용 범위:
   - innodb_read_only: InnoDB 스토리지 엔진을 사용하는 테이블에만 적용됩니다.
   - read_only: 모든 스토리지 엔진에 적용됩니다.

2. 권한 처리:
   - innodb_read_only: 모든 사용자(SUPER 권한 포함)의 쓰기 작업을 금지합니다.
   - read_only: SUPER 권한(또는 CONNECTION_ADMIN)을 가진 사용자는 여전히 쓰기 작업이 가능합니다.

3. 설정 변경:
   - innodb_read_only: 서버 시작 시에만 설정 가능한 정적 변수입니다.
   - read_only: 동적 변수로, 서버 운영 중에도 변경 가능합니다.

4. 작업 제한:
   - innodb_read_only: InnoDB 관련 모든 쓰기 작업을 금지합니다.
   - read_only: 일반적인 데이터 변경 작업을 금지하지만, TEMPORARY 테이블 작업이나 복제 관련 작업 등은 허용합니다.

5. 사용 목적:
   - innodb_read_only: 주로 읽기 전용 미디어에서 MySQL을 실행하거나, 데이터 무결성을 위해 사용됩니다.
   - read_only: 주로 복제 설정에서 슬레이브 서버를 읽기 전용으로 만들기 위해 사용됩니다.

6. 시스템 테이블 영향:
   - innodb_read_only: mysql 시스템 데이터베이스의 InnoDB 테이블에도 영향을 미칩니다.
   - read_only: 일부 시스템 작업(예: FLUSH TABLES WITH READ LOCK)은 여전히 허용됩니다.

따라서 innodb_read_only는 더 엄격한 읽기 전용 모드를 제공하며, read_only는 일반적인 운영 상황에서 더 유연한 읽기 전용 모드를 제공합니다.