파티션
파티션 개요와 실습
파티션은 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것을 말한다. 예로 수십억 건의 테이블에 쿼리를 수행할 때, 비록 인덱스를 사용한다고 해도 테이블의 댜용량으로 인해서 MySQL에 상당한 부담이 될 수밖에 없다.
이럴 때, 하나의 테이블이 10개의 파티션으로 나눠서 저장되어 있다. 경우에 따라서는 그 부담이 1/10로 줄어들 수도 있다.
- 물론 무조건 파티션으로 나눈다고 효율적이 되는 것은 아니며, 데이터의 분포 특성이나 자주 사용되는 쿼리문이 무엇인지에 따라서 효율에 큰 차이가 있을 수 있다.
테이블의 행 데이터가 무척 많은 대용량의 데이터베이스를 생각해 보면 INSERT, UPDATE 등의 작업이 갈수록 느려질 수밖에 없다. 이럴 경우, 파티션으로 나누면 시스템 성능에 큰 도움이 될 것이다.
테이블을 분할할 때는 테이블의 범위에 따라서 서로 다른 파티션에 저장하는 것이 가장 보편적이다.
예로, 10년간의 데이터가 저장된 테이블이라면 아마도 과거의 데이터들은 주로 조회만 할 뿐 거의 변경이 되지 않을 것이다. 그러므로, 작년 이전의 데이터와 올해의 데이터를 서로 다른 파티션에 저장한다면 효과적일 수 있다.
또, 다른 예로는 각 월별로 업데이트가 잦은 대용량 데이터라면 각 월별로 파티션 테이블을 구성할 수도 있다.
- MySQL 8.0은 최대 8192개의 파티션을 지원하는데, 파티션을 나누면 물리적으로는 파일로 분리된다. 그렇기 때문에 파티션 테이블은 파일이 동시에 여러 개 열린다. MySQL은 동시에 열 수 있는 파일의 개수가 시스템 변수 open_file_limit(기본 값 5000)에 지정되어 있다. 그러므로 파티션을 5000개 이상 나눌 경우에는, 시스템 변수 open_file_limit 값을 파티션 개수에 맞게 변경시켜줄 필요가 있다.
[그림 11-12]는 sqlDB의 회원 테이블을 출생년도 별로 3개의 파티션으로 구분하기 위한 개념도다.
[그림 11-12] 파티션 개념도
테이블을 생성할 때, 파티션 키를 함께 지정한다. 그러면 데이터를 입력할 때 지정된 파티션 키에 의해서 데이터가 각각의 파티션에 입력되는 개념이다.
물론 사용자는 파티션이 몇 개이든 관계없이 테이블 하나에만 접근한다고 생각하면 된다. 즉, 파티션을 나눴던 그렇지 않든 MySQL내부적인 문제일 뿐 사용자는 신경쓰지 않아도 된다.
<실습>
[그림 11-12] 파티션 개념도
CREATE DATABASE IF NOT EXISTS partDB;
USE partDB;
DROP TABLE IF EXISTS partTBL;
CREATE TABLE partTBL (
userID CHAR(8) NOT NULL, -- primary Key로 지정하면 안됨
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL )
PARTITION BY RANGE(birthYear) (
PARTITION part1 VALUES LESS THAN (1971),
PARTITION part2 VALUES LESS THAN (1979),
PARTITION part3 VALUES LESS THAN MAXVALUE
);
INSERT INTO partTBL
SELECT userID, name, birthYear, addr FROM sqlDB.userTbl;
SELECT * FROM partTBL;
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'parttbl';
SELECT * FROM partTBL WHERE birthYear <= 1965;
EXPLAIN SELECT * FROM partTBL WHERE birthYear <= 1965; 
ALTER TABLE partTBL
REORGANIZE PARTITION part3 INTO(
PARTITION part3 VALUES LESS THAN (1986),
PARTITION part4 VALUES LESS THAN MAXVALUE
);
OPTIMIZE TABLE partTBL;
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'parttbl';
ALTER TABLE partTBL
REORGANIZE PARTITION part1, part2 INTO(
PARTITION part23 VALUES LESS THAN (1979),
);
OPTIMIZE TABLE partTBL;
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'parttbl'; 
ALTER TABLE partTBL DROP PARTITION part12;
OPTIMIZE TABLE partTBL;
SELECT * FROM partTBL;
파티션을 지우면 내용까지 지워지기 때문에 주의해서 지워야 한다.
파티션의 정리
파티션은 대량의 테이블을 물리적으로 분리하기 때문에 상당히 효율적일 수 있지만, 몇 가지 제한사항도 고려해야 한다. 파티션에서 부가적으로 기억해야 할 내용은 다음과 같다.
- 파티션 테이블에 외래 키를 설정할 수 없다. 그러므로, 단독으로 사용되는 테이블에만 파티션을 설정할 수 있다.
- 스토어드 프로시저, 스토어드 함수, 사용자 변수 등을 파티션 함수나 식에 사용할 수 없다.
- 임시 테이블은 파티션 기능을 사용할 수 없다.
- 파티션 키에는 일부 함수만 사용할 수 있다.
- 파티션 개수는 최대 8192개까지 지원된다.
- 레인지 파티션은 숫자형의 연속된 범위를 사용하고, 리스트 파티션은 숫자형 또는 문자형의 연속되지 않은 하나하나씩 파티션 키 값을 지정한다.
- 리스트 파티션에는 MAXVALUE를 사용할 수 없다. 즉, 모든 경우의 파티션 키 값을 지정해야 한다.
이상으로 전체 텍스트 검색과 파티션에 대해서 살펴봤다. 두 기능은 MySQL의 성능 향상을 위해서 상당히 유용하게 사용될 수 있으므로, 실무에서 적용 가능하다면 적극 활용하는 것을 권장한다.
