[MySQL] Table Partitioning - 소개

■ 파티셔닝 소개

비 네이티브 파티셔닝이 있는 테이블을 사용하면 ER_WARN_DEPRECATED_SYNTAX경고가 발생합니다. MySQL 5.7.17에서 5.7.20까지, 서버는 시작시 비 기본 파티션을 사용하는 테이블을 식별하기 위해 자동으로 검사를 수행합니다. 발견된 파티션은 오류 로그에 메시지를 기록합니다. 이 검사를 비활성화하려면 --disable-partition-engine-check 옵션을 사용합니다. MySQL 5.7.21 이상에서는 이 검사가 수행되지 않습니다. 이 버전에서 서버가 일반 파티셔닝 핸들러를 사용하여 테이블을 확인하려면 --disable-partition-engine-check=false로 서버를 시작해야합니다.

 

MySQL 8.0으로의 마이그레이션을 준비하려면 비 네이티브 파티셔닝이있는 테이블을 네이티브 파티셔닝을 제공하는 엔진을 사용하도록 변경하거나 비 파티셔닝해야합니다. 예를 들어, 테이블을 InnoDB로 변경하려면 다음 명령문을 실행합니다.

ALTER TABLE table_name ENGINE=INNODB;

 

다음과 같이 SHOW PLUGINS 문의 출력을 확인하여 MySQL 서버가 파티셔닝을 지원하는지 확인할 수 있습니다.

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)

 

다음과 유사한 쿼리로 INFORMATION_SCHEMA.PLUGINS 테이블을 확인할 수도 있습니다.

mysql> SELECT
    ->     PLUGIN_NAME as Name,
    ->     PLUGIN_VERSION as Version,
    ->     PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+--------+
| Name               | Version | Status |
+--------------------+---------+--------+
| binlog             | 1.0     | ACTIVE |
| CSV                | 1.0     | ACTIVE |
| MEMORY             | 1.0     | ACTIVE |
| MRG_MYISAM         | 1.0     | ACTIVE |
| MyISAM             | 1.0     | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE |
| BLACKHOLE          | 1.0     | ACTIVE |
| ARCHIVE            | 3.0     | ACTIVE |
| InnoDB             | 5.7     | ACTIVE |
| partition          | 1.0     | ACTIVE |
+--------------------+---------+--------+
10 rows in set (0.00 sec)

 

두 경우 모두 출력에서 ​​상태열에 대해 파티션 플러그인 값이 ACTIVE로 표시되지 않으면(방금 제공된 각 예제에서 굵은 텍스트로 표시됨), MySQL 버전은 파티션 지원으로 빌드되지 않은 것입니다. 또한 MySQL 5.7 커뮤니티 바이너리에는 파티셔닝 지원이 포함됩니다.

 

소스에서 MySQL 5.7을 컴파일하는 경우 파티셔닝을 활성화하려면 빌드가 -DWITH_PARTITION_STORAGE_ENGINE 옵션으로 구성되어야합니다.

 

파티션 지원을 비활성화하려면 --skip-partition옵션을 사용하여 MySQL 서버를 시작할 수 있습니다. 파티션 지원이 비활성화되면 기존 파티션 테이블을 보고 삭제할 수 있지만(권장하지는 않지만) 테이블을 조작하거나 데이터에 액세스 할 수는 없습니다.

 

▶︎ 추가 자료.

MySQL의 사용자 정의 파티셔닝에 대한 기타 정보 소스는 다음과 같습니다.

+ MySQL Partitioning Forum

https://forums.mysql.com/list.php?106

이것은 MySQL Partitioning 기술에 관심이 있거나 테스트를하는 사람들을 위한 공식 토론 포럼입니다. MySQL개발자 및 다른 사람들의 발표 및 업데이트 기능이 있습니다. 파티션 개발 및 문서화 팀의 구성원이 모니터합니다.

 

+ Mikael Ronström's Blog

http://mikaelronstrom.blogspot.com/

MySQL Partitioning Architect 및 수석 개발자 Mikael Ronström은 MySQL Partitioning 및 NDB Cluster 관련 작업에 대한 기사를 자주 게시합니다.

 

+ PlanetMySQL

https://planet.mysql.com/

MySQL 관련 블로그가있는 MySQL 뉴스 사이트로, 내 MySQL을 사용하는 모든 사람이 관심을 가질만한 사이트입니다. MySQL Partitioning을 사용하는 사람들이 보유한 블로그 링크를 확인하거나 해당 블로그를 추가해 두시면 좋습니다.

 

최신 파티션 버그 수정 및 기능 추가에 대해서는 GitHub 리포지토리에서 소스를 얻을 수 있습니다. 파티셔닝을 사용하려면 빌드가 -DWITH_PARTITION_STORAGE_ENGINE 옵션으로 구성되어야합니다. 파티셔닝이 가능한 MySQL 5.7 빌드를 컴파일하는 데 문제가있는 경우, MySQL 파티셔닝 포럼을 확인하여 이미 게시 된 문제에 대한 해결책을 찾지 못한 경우 지원을 요청해야합니다.

 

 

 

■ MySQL의 파티셔닝 소개

이 섹션에서는 MySQL 5.7에서의 파티셔닝에 대한 개념적인 개요를 제공합니다.

 

SQL표준은 데이터 스토리지의 물리적 측면과 관련하여 많은 지침을 제공하지 않습니다. SQL언어 자체는 작동하는 스키마, 테이블, 행 또는 열의 기본이되는 모든 데이터 구조 또는 미디어와 독립적으로 작동합니다. 그럼에도 불구하고, 대부분의 고급 데이터베이스 관리 시스템은 파일 시스템, 하드웨어 또는 두 가지 측면에서 특정 데이터 조각을 저장하는데 사용할 물리적 위치를 결정하는 몇가지 수단을 발전 시켰습니다. MySQL에서 InnoDB 스토리지 엔진은 오랫동안 테이블 스페이스 개념을 지원해 왔으며, 파티셔닝을 도입하기 전에도 다른 데이터베이스를 저장하기 위해 다른 물리적 디렉토리를 사용하도록 MySQL 서버를 구성 할 수있었습니다.

 

파티셔닝은 필요에 따라 크게 설정할 수 있는 규칙에 따라 파일 시스템에 개별 테이블의 일부를 분배 할 수있게하여 이 개념을 한 단계 더 발전시킵니다. 실제로, 테이블의 다른 부분은 다른 위치에 별도의 테이블로 저장됩니다. 데이터 분할이 수행되는 사용자 선택 규칙은 파티셔닝 함수로 알려져 있으며, MySQL에서는 모듈러스, 일련의 범위 또는 값 목록에 대한 간단한 일치, 내부 해싱 함수 또는 선형 해싱 함수일 수 있습니다. 함수는 사용자가 지정한 파티셔닝 유형에 따라 선택되며 사용자 제공 표현식의 값을 매개 변수로 사용합니다. 이 표현식은 사용되는 파티셔닝 유형에 따라 열 값, 하나 이상의 열 값에 작용하는 함수 또는 하나 이상의 열 값 세트 일 수 있습니다.

 

RANGE, LIST 및 [LINEAR] HASH 파티셔닝의 경우 파티셔닝 열의 값이 파티셔닝 함수로 전달되어 특정 레코드를 저장해야하는 파티션 수를 나타내는 정수 값을 리턴합니다. 이 기능은 일정하지 않고 무작위가 아니어야 합니다. 쿼리를 포함하지 않을 수도 있지만, 해당 표현식이 NULL 또는 정수 intval을 리턴하여 아래 조건인 경우 MySQL에서 유효한 SQL 표현식을 사용할 수 있습니다.

-MAXVALUE <= intval <= MAXVALUE

(MAXVALUE는 해당 정수 유형의 최소 상한을 나타내는 데 사용됩니다. -MAXVALUE는 가장 큰 하한을 나타냅니다.)

 

[LINEAR] KEY, RANGE COLUMNS 및 LIST COLUMNS파티셔닝의 경우 파티셔닝 표현식은 하나 이상의 컬럼 목록으로 구성됩니다.

 

[LINEAR] KEY 파티셔닝의 경우 파티셔닝 기능은 MySQL에서 제공합니다.

이를 수평 파티셔닝이라고합니다. 즉, 테이블의 다른 행이 다른 물리적 파티션에 할당 될 수 있습니다. MySQL 5.7은 테이블의 다른 컬럼이 다른 물리적 파티션에 할당되는 수직 파티셔닝을 지원하지 않습니다.

참고로 아직은 계속 지원할 생각이 없다고 합니다.

 

파티션 된 테이블을 생성하기 위해 MySQL 서버에서 지원하는 대부분의 스토리지 엔진을 사용할 수 있습니다. MySQL 파티셔닝 엔진은 별도의 계층에서 실행되며 이들과 상호 작용할 수 있습니다. MySQL 5.7에서 동일한 파티션 테이블의 모든 파티션은 동일한 스토리지 엔진을 사용해야합니다. 예를 들어, 한 파티션에는 MyISAM을, 다른 파티션에는 InnoDB를 사용할 수 없습니다. 그러나 동일한 MySQL 서버 또는 동일한 데이터베이스에서 서로 다른 파티션 된 테이블에 대해 다른 스토리지 엔진을 사용하는 데 방해가되지 않습니다.

 

MERGE, CSV 또는 FEDERATED 스토리지 엔진에는 MySQL 파티셔닝을 사용할 수 없습니다.

 

NDB에서는 KEY또는 LINEAR KEY로 파티셔닝할 수 있지만 이 스토리지 엔진을 사용하는 테이블에는 다른 유형의 사용자 정의 파티셔닝이 지원되지 않습니다. 또한 사용자 정의 파티셔닝을 사용하는 NDB 테이블에는 명시적 기본 키가 있어야하며 테이블의 파티셔닝 표현식에서 참조되는 모든 컬럼이 기본 키의 일부 여야합니다. 그러나 사용자 분할 NDB 테이블을 작성하거나 수정하는 데 사용 된 CREATE TABLE 또는 ALTER TABLE 문의 PARTITION BY KEY 또는 PARTITION BY LINEAR KEY절에 열이 나열되지 않으면 테이블에 명시적 기본 키가 필요하지 않습니다.

 

파티션 된 테이블에 특정 스토리지 엔진을 사용하려면 파티션되지 않은 테이블에서와 마찬가지로 [STORAGE]ENGINE 옵션만 사용해야합니다. 그러나 CREATE TABLE 문에서 파티션 옵션을 사용하기 전에 [STORAGE] ENGINE (및 기타 테이블 옵션)을 나열해야합니다. 이 예는 해시로 6개의 파티션으로 분할되고 InnoDB 스토리지 엔진을 사용하는 테이블을 생성하는 방법을 보여줍니다.

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

각 PARTITION 절에는 [STORAGE] ENGINE 옵션이 포함될 수 있지만 MySQL 5.7에서는 적용되지 않습니다.

 

중요사항

파티셔닝은 테이블의 모든 데이터 및 인덱스에 적용됩니다. 데이터만 분할하고 인덱스는 분할할 수 없으며 그 반대도 마찬가지입니다. 또한 테이블의 일부만 분할 할 수도 없습니다.

 

파티션된 테이블을 작성하는데 사용 된 CREATE TABLE 문의 PARTITION절에 대한 DATA DIRECTORY 및 INDEX DIRECTORY 옵션을 사용하여 각 파티션의 데이터 및 인덱스를 특정 디렉토리에 지정할 수 있습니다.

 

DATA DIRECTORY 및 INDEX DIRECTORY는 Windows에서 MyISAM 테이블의 개별 파티션 또는 하위 파티션에 대해 지원되지 않습니다.

 

InnoDB 테이블의 개별 파티션 및 하위 파티션에 대해서는 DATA DIRECTORY 옵션 만 지원됩니다.

 

테이블의 파티셔닝 표현식에 사용 된 모든 컬럼은 기본 키를 포함하여 테이블이 가질 수있는 모든 고유 키의 일부 여야합니다. 이는 다음 SQL 문으로 작성된 이와 같은 테이블을 분할 할 수 없음을 의미합니다.

CREATE TABLE tnp (
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name VARCHAR(255),
    PRIMARY KEY pk (id),
    UNIQUE KEY uk (name)
);

 

pk 및 uk키에는 공통 컬럼이 없으므로 파티셔닝 표현에 사용할 수있는 열이 없습니다. 이 상황에서 가능한 해결 방법은 테이블의 기본키에 이름컬럼 추가, uk에 id컬럼 추가 또는 고유키 제거를 포함합니다.

 

또한 MAX_ROWS 및 MIN_ROWS를 사용하여 각 파티션에 저장할 수있는 최대 및 최소행 수를 각각 결정할 수 있습니다.

 

MAX_ROWS 옵션은 추가 파티션이있는 NDB 클러스터 테이블을 생성하는 데 유용할 수 있으므로 해시 인덱스를 더 많이 저장할 수 있습니다.

 

파티셔닝의 장점은 다음과 같습니다.

+ 파티셔닝은 단일 디스크 나 파일 시스템 파티션에 보관할 수있는 것보다 더 많은 데이터를 하나의 테이블에 저장할 수 있게 합니다.

+ 유용성을 잃은 데이터는 종종 해당 데이터만 포함된 파티션을 삭제하여 파티션된 테이블에서 쉽게 제거 할 수 있습니다. 반대로, 새 데이터를 추가하는 프로세스는 경우에 따라 해당 데이터를 구체적으로 저장하기 위해 하나 이상의 새 파티션을 추가하여 크게 촉진 될 수 있습니다.

+ 주어진 WHERE절을 만족하는 데이터가 하나 이상의 파티션에만 저장될 수 있기 때문에 일부 쿼리는 크게 최적화될 수 있으며, 이는 검색에서 나머지 파티션을 자동으로 제외합니다. 파티션된 테이블을 작성한 후에 파티션을 변경할 수 있으므로, 파티션 구성표를 처음 설정할때 자주 사용되지 않았던 빈번한 조회를 향상시키기 위해 데이터를 재구성 할 수 있습니다. 일치하지 않는 파티션(및 이에 포함 된 행)을 제외하는 이 기능을 종종 파티션 프루닝이라고합니다.

 

또한 MySQL은 쿼리에 대한 명시적 파티션 선택을 지원합니다. 예를 들어, SELECT * FROM t PARTITION (p0, p1) WHERE c<5는 파티션 p0 및 p1에서 WHERE 조건과 일치하는 행만 선택합니다. 이 경우 MySQL은 테이블 t의 다른 파티션을 확인하지 않습니다. 검사할 파티션을 이미 알고 있으면 쿼리 속도를 크게 높일 수 있습니다. 데이터 수정 명령문 DELETE, INSERT, REPLACE, UPDATE 및 LOAD DATA, LOAD XML에 대해서도 파티션 선택이 지원됩니다.

 

□ 테이블 파티셔닝 링크

[MySQL] Table Partitioning-파티셔닝 타입

https://myinfrabox.tistory.com/94

[MySQL] Table Partitioning-파티셔닝 타입

[MySQL] Table Partitioning - 파티셔닝 관리

https://myinfrabox.tistory.com/98

[MySQL] Table Partitioning - 파티셔닝 관리

[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)

https://myinfrabox.tistory.com/100

[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)

[MySQL] Table Partitioning-파티셔닝 선택

https://myinfrabox.tistory.com/102

[MySQL] Table Partitioning-파티셔닝 선택

[MySQL] Table Partitioning-파티셔닝에 대한 제한 사항

https://myinfrabox.tistory.com/103

[MySQL] Table Partitioning-파티셔닝에 대한 제한 사항