26.1 Overview of Partitioning in MySQL

26.1 Overview of Partitioning in MySQL

이 섹션에서는 MySQL 9.5에서 partitioning에 대한 개념적 개요를 제공합니다.

partitioning에 대한 제한 사항과 기능상의 제약에 대한 정보는 Section 26.6, “Restrictions and Limitations on Partitioning”을 참고하십시오.

SQL 표준은 데이터 저장의 물리적 측면에 대해 많은 지침을 제공하지 않습니다. SQL 언어 자체는 자신이 다루는 스키마, 테이블, 행, 열 아래에 있는 어떤 데이터 구조나 매체와도 독립적으로 동작하도록 의도되었습니다.

그럼에도 불구하고, 대부분의 고급 데이터베이스 관리 시스템은 파일 시스템, 하드웨어, 혹은 둘 다의 관점에서 특정 데이터 조각을 저장하는 데 사용할 물리적 위치를 결정하는 수단을 발전시켜 왔습니다. MySQL에서 InnoDB 스토리지 엔진은 오랫동안 테이블스페이스 개념을 지원해 왔습니다(Section 17.6.3, “Tablespaces” 참조). 또한 MySQL Server는 partitioning이 도입되기 이전에도 서로 다른 데이터베이스를 저장하기 위해 서로 다른 물리적 디렉터리를 사용하도록 설정할 수 있었습니다(이 방법에 대한 설명은 Section 10.12.2, “Using Symbolic Links” 참조).

partitioning은 이 개념을 한 단계 더 나아가서, 사용자가 필요에 따라 설정할 수 있는 규칙에 따라 개별 테이블의 일부를 파일 시스템 전반에 분산할 수 있게 해 줍니다. 사실상, 테이블의 서로 다른 부분이 서로 다른 위치에 있는 별도의 테이블로 저장됩니다.

데이터 분할이 수행되는 데 사용자가 선택한 규칙을 partitioning function이라고 하며, MySQL에서는 modulus, 단순한 범위 또는 값 목록 집합에 대한 매칭, 내부 해싱 함수, 혹은 선형 해싱 함수가 될 수 있습니다. 이 함수는 사용자가 지정한 partitioning type에 따라 선택되며, 매개변수로 사용자가 제공한 식의 값을 받습니다. 이 식은 사용되는 partitioning type에 따라 열 값, 하나 이상의 열 값에 작용하는 함수, 또는 하나 이상의 열 값들의 집합이 될 수 있습니다.

RANGE, LIST, [LINEAR] HASH partitioning의 경우, partitioning 열의 값이 partitioning function에 전달되며, 이 함수는 해당 레코드가 저장되어야 하는 partition 번호를 나타내는 정수 값을 반환합니다. 이 함수는 상수여서는 안 되고 랜덤이어서는 안 됩니다. 이 함수는 어떠한 쿼리도 포함할 수 없지만, 해당 식이 NULL 또는 다음을 만족하는 정수 _intval_을 반환하는 한, MySQL에서 유효한 SQL 식을 사용할 수 있습니다.

1-MAXVALUE <= intval <= MAXVALUE

(MAXVALUE는 해당 정수 타입에 대한 최소 상한을 나타냅니다. -MAXVALUE는 최대 하한을 나타냅니다.)

[LINEAR] KEY, RANGE COLUMNS, LIST COLUMNS partitioning의 경우, partitioning 식은 하나 이상의 열 목록으로 구성됩니다.

[LINEAR] KEY partitioning의 경우, partitioning function은 MySQL에서 제공합니다.

허용되는 partitioning 열 타입 및 partitioning function에 대한 자세한 내용은 Section 26.2, “Partitioning Types”과 partitioning 구문 설명 및 추가 예제를 제공하는 Section 15.1.24, “CREATE TABLE Statement”를 참고하십시오. partitioning function에 대한 제한 사항 정보는 Section 26.6.3, “Partitioning Limitations Relating to Functions”을 참고하십시오.

이것은 horizontal partitioning이라고 알려져 있습니다. 즉, 테이블의 서로 다른 행이 서로 다른 물리적 partition에 할당될 수 있습니다. MySQL 9.5는 테이블의 서로 다른 열이 서로 다른 물리적 partition에 할당되는 vertical partitioning을 지원하지 않습니다. 현재로서는 MySQL에 vertical partitioning을 도입할 계획이 없습니다.

partitioned 테이블을 생성하려면, 이를 지원하는 스토리지 엔진을 사용해야 합니다. MySQL 9.5에서는 동일한 partitioned 테이블의 모든 partition이 동일한 스토리지 엔진을 사용해야 합니다. 그러나 동일한 MySQL 서버, 심지어 동일한 데이터베이스 내에서도 서로 다른 partitioned 테이블에 대해 서로 다른 스토리지 엔진을 사용하는 것은 아무런 제약이 없습니다.

MySQL 9.5에서 partitioning을 지원하는 유일한 스토리지 엔진은 InnoDB와 NDB입니다. partitioning은 이를 지원하지 않는 스토리지 엔진과는 함께 사용할 수 없습니다. 여기에는 MyISAM, MERGE, CSV, FEDERATED 스토리지 엔진이 포함됩니다.

KEY 또는 LINEAR KEY에 의한 partitioning은 NDB에서 가능하지만, 이 스토리지 엔진을 사용하는 테이블에 대해서는 그 밖의 타입의 사용자 정의 partitioning은 지원되지 않습니다. 추가로, 사용자 정의 partitioning을 사용하는 NDB 테이블은 명시적인 primary key를 가져야 하며, 테이블의 partitioning 식에 참조되는 모든 열은 primary key의 일부여야 합니다.

그러나 user-partitioned NDB 테이블을 생성 또는 수정하는 데 사용되는 CREATE TABLE 또는 ALTER TABLE statement의 PARTITION BY KEY 또는 PARTITION BY LINEAR KEY 절에 어떤 열도 나열되지 않은 경우, 해당 테이블은 명시적인 primary key를 가질 필요가 없습니다. 자세한 내용은 Section 25.2.7.1, “Noncompliance with SQL Syntax in NDB Cluster”를 참고하십시오.

partitioned 테이블을 생성할 때, 기본 스토리지 엔진은 다른 테이블을 생성할 때와 마찬가지로 사용됩니다. 이 동작을 재정의하려면 partitioning되지 않은 테이블에 대해 사용하는 것과 동일하게 [STORAGE] ENGINE 옵션만 사용하면 됩니다. 대상 스토리지 엔진은 네이티브 partitioning 지원을 제공해야 하며, 그렇지 않으면 statement는 실패합니다.

또한 CREATE TABLE statement에서 partitioning 옵션을 사용하기 전에 [STORAGE] ENGINE(및 기타 테이블 옵션)이 나열되어야 한다는 점에 유의해야 합니다. 다음 예제는 6개의 partition으로 hash partitioning되고, (default_storage_engine 값과 관계없이) InnoDB 스토리지 엔진을 사용하는 테이블을 생성하는 방법을 보여 줍니다.

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

각 PARTITION 절에는 [STORAGE] ENGINE 옵션을 포함할 수 있지만, MySQL 9.5에서는 이것이 아무런 효과도 가지지 않습니다.

별도로 명시되지 않는 한, 이 설명의 나머지 예제에서는 default_storage_engine이 InnoDB라고 가정합니다.

주의

partitioning은 테이블의 모든 데이터와 인덱스에 적용됩니다. 데이터만 partitioning하고 인덱스는 partitioning하지 않거나, 그 반대로 하는 것은 불가능하며, 테이블의 일부만 partitioning하는 것도 불가능합니다.

각 partition에 대한 데이터와 인덱스는, partitioned 테이블을 생성하는 데 사용되는 CREATE TABLE statement의 PARTITION 절에 대해 DATA DIRECTORY 및 INDEX DIRECTORY 옵션을 사용하여 특정 디렉터리에 할당할 수 있습니다.

InnoDB 테이블의 개별 partition 및 subpartition에 대해서는 DATA DIRECTORY 옵션만 지원됩니다. DATA DIRECTORY 절에 지정된 디렉터리는 InnoDB에 의해 인지되고 있어야 합니다. 자세한 내용은 Using the DATA DIRECTORY Clause를 참고하십시오.

테이블의 partitioning 식에 사용되는 모든 열은, primary key를 포함하여 테이블이 가질 수 있는 모든 unique key의 일부여야 합니다. 이는 다음 SQL statement로 생성된 다음과 같은 테이블은 partitioning할 수 없음을 의미합니다.

1CREATE TABLE tnp (
2    id INT NOT NULL AUTO_INCREMENT,
3    ref BIGINT NOT NULL,
4    name VARCHAR(255),
5    PRIMARY KEY pk (id),
6    UNIQUE KEY uk (name)
7);

key pk와 uk는 공통 열이 없으므로, partitioning 식에 사용할 수 있는 열이 없습니다. 이 상황에서 가능한 해결 방법에는 name 열을 테이블의 primary key에 추가하는 것, id 열을 uk에 추가하는 것, 혹은 unique key를 아예 제거하는 것이 포함됩니다. 자세한 내용은 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”를 참고하십시오.

또한 MAX_ROWS와 MIN_ROWS를 사용하여 각각 각 partition에 저장할 수 있는 행의 최대 및 최소 개수를 결정할 수 있습니다. 이러한 옵션에 대한 자세한 정보는 Section 26.3, “Partition Management”를 참고하십시오.

MAX_ROWS 옵션은 hash 인덱스를 더 많이 저장할 수 있도록 추가 partition을 가진 NDB Cluster 테이블을 생성하는 데에도 유용할 수 있습니다. DataMemory 데이터 노드 구성 파라미터에 대한 문서와 Section 25.2.2, “NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions”를 참고하십시오.

partitioning의 몇 가지 장점은 다음과 같습니다.

• partitioning을 사용하면, 하나의 디스크 또는 파일 시스템 partition에 담을 수 있는 것보다 더 많은 데이터를 하나의 테이블에 저장할 수 있습니다.

• 유용성을 상실한 데이터는, 해당 데이터만 포함하는 partition(또는 partition들)을 drop하여 partitioned 테이블에서 쉽게 제거할 수 있는 경우가 많습니다. 반대로, 새로운 데이터를 추가하는 과정은, 특별히 그 데이터를 저장하기 위한 하나 이상의 새로운 partition을 추가함으로써 크게 용이해질 수 있습니다.

• 특정 WHERE 절을 만족하는 데이터가 하나 이상의 partition에만 저장될 수 있다는 사실을 이용해 일부 쿼리를 크게 최적화할 수 있습니다. 이는 자동으로 나머지 partition을 검색 대상에서 제외하게 됩니다.

partitioned 테이블이 생성된 후에도 partition을 변경할 수 있으므로, partitioning scheme이 처음 설정될 때는 자주 사용되지 않았던 빈번한 쿼리를 향상시키기 위해 데이터를 재구성할 수 있습니다. 이처럼 일치하지 않는 partition(및 그들이 포함하는 행)을 제외하는 기능은 종종 partition pruning이라고 불립니다. 자세한 내용은 Section 26.4, “Partition Pruning”을 참고하십시오.

또한, MySQL은 쿼리에 대한 명시적인 partition 선택을 지원합니다. 예를 들어, SELECT * FROM t PARTITION (p0,p1) WHERE c < 5는 WHERE 조건과 일치하는 행 중 partition p0와 p1에 있는 것만 선택합니다. 이 경우, MySQL은 테이블 t의 다른 어떤 partition도 검사하지 않습니다. 이는 어떤 partition을 살펴보고 싶은지 이미 알고 있을 때 쿼리를 크게 빠르게 할 수 있습니다.

partition 선택은 DELETE, INSERT, REPLACE, UPDATE 데이터 변경 statement와 LOAD DATA, LOAD XML에도 지원됩니다. 자세한 정보와 예제는 이들 statement의 설명을 참고하십시오.