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

인프라쟁이 DBA
Databases/MySQL
2020. 8. 20.

■ 파티셔닝 타입

이 섹션에서는 MySQL 5.7에서 사용할 수있는 파티셔닝 유형에 대해 설명합니다. 여기에 나열된 유형이 포함됩니다.

+ RANGE 파티셔닝.

이 유형의 파티셔닝은 주어진 범위 내에 속하는 열 값을 기반으로 파티션에 행을 할당합니다. 22.2.1 절“RANGE 파티션”을 참조하십시오. 이 유형의 RANGE COLUMNS에 대한 확장에 대한 정보는 22.2.3.1 절“RANGE COLUMNS 파티셔닝”을 참조하십시오.

+ LIST 파티셔닝.

파티션이 개별 값 세트 중 하나와 일치하는 열을 기반으로 선택된다는 점을 제외하고 RANGE에 의한 파티션과 유사합니다. 22.2.2 절“LIST 분할”을 참조하십시오. 이 유형의 LIST COLUMNS 확장에 대한 내용은 22.2.3.2 절“LIST COLUMNS 파티셔닝”을 참조하십시오.

+ 해시 파티셔닝.

이 유형의 파티셔닝을 사용하면 테이블에 삽입 할 행의 열 값에서 작동하는 사용자 정의 표현식이 리턴 한 값을 기반으로 파티션이 선택됩니다. 이 함수는 음수가 아닌 정수 값을 생성하는 MySQL에서 유효한 식으로 구성 될 수 있습니다. 이 유형의 LINEAR HASH 확장도 사용할 수 있습니다. 22.2.4 절“HASH 파티셔닝”을 참조하십시오.

+ 키 분할.

이 유형의 파티셔닝은 평가할 열이 하나 이상 제공되고 MySQL 서버가 자체 해싱 기능을 제공한다는 점을 제외하고 HASH의 파티셔닝과 유사합니다. MySQL에서 제공하는 해싱 함수는 열 데이터 유형에 관계없이 정수 결과를 보장하므로 이러한 열에는 정수 값 이외의 값이 포함될 수 있습니다. 이 유형의 선형 키 (LINEAR KEY)도 사용할 수 있습니다. 22.2.5 절.“키 분할”을 참조하십시오.

데이터베이스 파티셔닝의 가장 일반적인 용도는 날짜별로 데이터를 분리하는 것입니다. 일부 데이터베이스 시스템은 명시적인 날짜 파티셔닝을 지원하며 MySQL은 5.7에서 구현하지 않습니다. 그러나 MySQL에서는 DATE, TIME 또는 DATETIME 열 또는 이러한 열을 사용하는 식을 기반으로 파티션 구성표를 만드는 것이 어렵지 않습니다.

KEY 또는 LINEAR KEY로 파티셔닝할 때 컬럼 값을 수정하지 않고 DATE, TIME 또는 DATETIME 컬럼을 파티셔닝 컬럼으로 사용하여 아래 예제처럼 생성할 수 있습니다.

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

MySQL 5.7에서는 RANGE COLUMNS 및 LIST COLUMNS 파티셔닝을 사용하여 DATE 또는 DATETIME 열을 파티셔닝 열로 사용할 수도 있습니다.

그러나 MySQL의 다른 파티셔닝 유형에는 정수값 또는 NULL을 생성하는 파티셔닝 표현식이 필요합니다. RANGE, LIST, HASH 또는 LINEAR HASH별로 날짜 기반 파티셔닝을 사용하려면 DATE, TIME 또는 지 DATETIME열에서 작동하는 함수를 간단히 사용하여 다음과 같이 해당 값을 리턴 할 수 있습니다.

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

MySQL 파티셔닝은 TO_DAYS(), YEAR() 및 TO_SECONDS() 함수와 함께 사용하도록 최적화되었습니다. 그러나 WEEKDAY(), DAYOFYEAR() 또는 MONTH()와 같이 정수 또는 NULL을 리턴하는 다른 날짜 및 시간 함수를 사용할 수 있습니다.

파티션은 0부터 시작하여 항상 자동으로 순서대로 번호가 매겨 짐을 기억해야합니다(사용하는 파티셔닝 유형에 관계없이). 새로운 행이 파티션 된 테이블에 삽입되면 올바른 파티션을 식별하는데 사용되는 파티션 번호입니다. 예를들어, 테이블에서 4개의 파티션을 사용하는 경우, 이 파티션의 번호는 0, 1, 2, 3입니다. RANGE 및 LIST 파티션 유형의 경우 각 파티션 번호에 대해 정의된 파티션이 있는지 확인해야합니다. HASH파티셔닝의 경우 사용자 제공 표현식은 0보다 큰 정수 값으로 평가되어야합니다. KEY파티셔닝의 경우 이 문제는 MySQL 서버가 내부적으로 사용하는 해싱 함수에 의해 자동으로 처리됩니다.

파티션 이름은 일반적으로 테이블 및 데이터베이스와 같은 다른 MySQL 식별자를 관리하는 규칙을 따릅니다. 그러나 파티션 이름은 대소 문자를 구분하지 않습니다. 예를 들어 다음과 같이 CREATE TABLE은 실패합니다.

mysql> CREATE TABLE t2 (val INT)
    -> PARTITION BY LIST(val)(
    ->     PARTITION mypart VALUES IN (1,3,5),
    ->     PARTITION MyPart VALUES IN (2,4,6)
    -> );
ERROR 1488 (HY000): Duplicate partition name mypart

MySQL은 파티션 이름 mypart와 MyPart은 대소문자 구별없이 같은 이름이기 때문에 실패합니다.

테이블의 파티션 수를 지정하는 경우 앞에 0으로 시작하지 않고 0이 아닌 양의 정수 리터럴로 표현해야하며, 정수 값으로 평가 되더라도 0.8E + 01 또는 6-2와 같은 표현식이 아닐 수 있습니다. 소수는 허용되지 않습니다.

■ RANGE Partitioning

범위별로 파티션된 테이블은 각 파티션에 파티션 표현식 값이 지정된 범위 내에있는 행을 포함하는 방식으로 파티션됩니다. 범위는 연속적이지만 겹치지 않아야 하며 VALUES LESS THAN연산자를 사용하여 정의됩니다. 다음 몇 가지 예의 경우 1-20으로 번호가 매겨진 20개의 비디오 상점 체인에 대한 직원 레코드를 보유하기 위해 다음과 같은 테이블을 작성한다고 가정합니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

노트

여기에 사용된 직원 테이블에는 기본 또는 고유키가 없습니다. 예제는 현재 논의의 목적과 같이 작동하지만, 테이블은 실제로 프리머리키, 유니크키 또는 둘 다 있을 가능성이 매우 높으며, 파티셔닝 컬럼에 대한 허용 가능한 선택은 이러한 키에 사용되는 컬럼에 따라 결정된다는 점을 유념해야 합니다.

이 테이블은 필요에 따라 다양한 방법으로 범위별로 파티션 될 수 있습니다. 한 가지 방법은 store_id 열을 사용하는 것입니다. 예를 들어, 여기에 표시된대로 PARTITION BY RANGE 절을 추가하여 테이블을 4가지 방법으로 파티션 할 수 있습니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

이 파티셔닝 체계에서, 상점 1-5에서 일하는 직원에 해당하는 모든 행은 파티션 p0에 저장되고, 상점 6-10에서 일하는 직원해 해당되는 모든 행은 파티션 p1에 저장됩니다. 각 파티션은 가장 낮은 순서에서 높은 순서로 정의됩니다. 이것은 PARTITION BY RANGE 구문의 요구 사항입니다. 이와 관련하여 C 또는 Java의 if ... elseif ... 문과 유사합니다.

데이터를 포함하는 새로운 행 (72, 'Mitchell', 'Wilson', '1998-06-25', NULL, 13)이 파티션 p2에 삽입되었는지는 쉽게 알 수 있지만 체인이 21번가 상점을 추가하면 어떻게됩니까? 이 스키마에서는 store_id가 20보다 큰 행을 다루는 규칙이 없으므로 서버가 어디에 배치 할 것인지 알지 못하므로 오류가 발생합니다. CREATE TABLE 문에서 "catchall"VALUES LESS THAN 절을 사용하여 명시 적으로 명명된 가장 높은 값보다 큰 모든 값을 제공하여 이를 방지 할 수 있습니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

노트

일치하는 값을 찾을 수 없을때 오류를 피하는 또 다른 방법은 INSERT 문의 일부로 IGNORE 키워드를 사용하는 것입니다.이렇게 하면 오류를 발생시키지 않고 문제가 되는 해당 행을 무시합니다.

MAXVALUE는 파티션의 조건값으로 지정된 값보다 큰, 즉 가장 큰 정수 값을 나타냅니다 (수학적 언어에서는 최소 상한값으로 사용됨). 이제 store_id 컬럼값이 16보다 크거나 같은 행 (정의된 가장 높은 값)이 있는 행은 파티션 p3에 저장됩니다. 나중에 상점수가 25, 30이상으로 증가한 시점에서 ALTER TABLE문을 사용하여 상점 21-25, 26-30등의 새 파티션을 추가할 수 있습니다.

거의 같은 방식으로 직원 작업 코드, 즉 job_code컬럼 값의 범위를 기반으로 테이블을 분할할 수 있습니다. 예를 들어, 두 자리의 작업 코드가 일반(점내) 작업자에 사용되고, 세자리 코드는 사무실 및 지원 담당자에 사용되고, 네 자리 코드는 관리 위치에 사용된다고 가정하면 다음과 같은 문법으로 파티션 테이블을 만들 수 있습니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

이 경우, 점포 작업자와 관련된 모든 행은 p0파티션, 사무실 및 지원 직원은 p1, 관리자 직원은 p2로 관련된 모든 행들이 값에 따라 자기 파티션에 맞게 저장됩니다.

VALUES LESS THAN 절에서 표현식을 사용할 수도 있습니다. 그러나 MySQL은 LESS THAN (<) 비교의 일부로 식의 반환 값을 평가할 수 있어야 합니다.

상점 번호에 따라 테이블 데이터를 분할하는 대신 두개의 DATE열 중 하나를 기반으로하는 표현식을 대신 사용할 수 있습니다. 예를 들어, 각 직원이 회사를 떠난 연도를 기준으로 파티션을 나누고 싶다고 가정해 봅니다. 즉, YEAR (분리) 값입니다. 이러한 파티셔닝 구성표를 구현하는 CREATE TABLE 문의 예는 다음과 같습니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

이 체계에서 1991년 이전에 떠난 모든 직원의 경우 행이 파티션 p0에 저장됩니다. 1991년부터 1995년까지 떠난 사람들은 p1; 1996 년부터 2000 년까지 떠난 사람들은 p2; 2000 년 이후에 떠난 근로자의 경우 p3 순으로 각 열들이 파티션에 저장됩니다.

이 예제에 표시된대로 UNIX_TIMESTAMP() 함수를 사용하여 TIMESTAMP열의 값을 기반으로 RANGE별로 테이블을 분할할 수도 있습니다.

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

TIMESTAMP 값과 관련된 다른 표현식은 허용되지 않습니다. (버그 # 42849 참조)

Range Partition은 다음 조건 중 하나 이상에 해당 할 때 특히 유용합니다.

+ 오래된 데이터를 삭제하거나 삭제할때 유리합니다. 직원 테이블에 대해 이전에 표시된 분할 구성표를 사용하는 경우 ALTER TABLE employee DROP PARTITION p0을 사용하면됩니다. 1991년 이전에 회사에서 근무를 중단한 직원과 관련된 모든 행을 삭제합니다. 많은 행에서, 이것은 직원이 DELETE FROM emplyee와 같은 DELETE쿼리를 실행하는 것보다 훨씬 효율적일 수 있습니다.(바이너리 로그가 활성화 되어 있다면 그에 따른 추가 용량도 필요하게 되고 트랜잭션으로 수행되기 때문에 오래 걸립니다.)

+ 날짜 또는 시간 값을 포함하거나 다른 계열에서 발생하는 값을 포함하는 열을 사용하려고합니다.

+ 테이블 분할에 사용된 컬럼에 직접 의존하는 쿼리를 자주 실행합니다. 예를 들어, '2000-01-01'과 '2000-12-31'GROUP BY store_id 사이를 구분한 직원들로부터 EXPLAIN SELECT COUNT (*)와 같은 쿼리를 실행할 때 MySQL은 파티션 p2만 필요하다는 것을 신속하게 결정할 수 있습니다. 나머지 파티션은 WHERE 절을 만족하는 레코드를 포함할 수 없으므로 스캔됩니다.

이 유형의 파티셔닝에 대한 변형은 RANGE COLUMNS 파티셔닝입니다. RANGE COLUMNS에 의한 파티셔닝은 파티션의 행 배치에 적용되는 파티션 범위를 정의하고 파티션 프루닝을 수행할 때 특정 파티션의 포함 또는 제외를 결정하는데 여러 컬럼을 사용할 수 있습니다.

▶︎ 시간 간격을 기준으로하는 파티션 스키마.

MySQL 5.7에서 범위 또는 시간 간격을 기반으로 파티션 스키마를 구현하려는 경우 두가지 옵션이 있습니다.

1. 테이블을 RANGE로 분할하는 파티션 표현식의 경우 DATE, TIME 또는 DATETIME 열에서 작동하고 다음과 같이 정수 값을 반환하는 함수를 사용합니다.

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35)
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

MySQL 5.7에서는 다음 예제와 같이 UNIX_TIMESTAMP() 함수를 사용하여 TIMESTAMP열의 값을 기반으로 RANGE로 테이블을 분할할 수도 있습니다.

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

MySQL 5.7에서는 TIMESTAMP 값과 관련된 다른 표현식이 허용되지 않습니다. (버그 # 42849 참조)

노트

MySQL 5.7에서는 UNIX_TIMESTAMP (timestamp_column)을 LIST로 파티션된 테이블에 대한 파티션 표현식으로 사용할 수도 있습니다. 그러나 일반적으로 그렇게하는 것은 실용적이지 않습니다.

2. DATE 또는 DATETIME 컬럼을 파티션 컬럼으로 사용하여 RANGE COLUMNS로 테이블을 파티션합니다. 예를 들어 다음과 같이 조인 된 열을 사용하여 멤버 테이블을 직접 정의할 수 있습니다.

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

노트

RANGE COLUMNS에서는 DATE 또는 DATETIME 이외의 날짜 또는 시간 유형을 사용하는 파티션 컬럼 사용이 지원되지 않습니다.

■ LIST 파티셔닝

MySQL의 리스트 파티셔닝은 여러 가지면에서 범위(RANGE) 파티셔닝과 유사합니다. RANGE에 의한 파티셔닝에서와 같이 각 파티션을 명시적으로 정의해야합니다. 두 가지 파티션 유형의 주요한 차이점은 LIST 파티셔닝에서 각 파티션이 연속적인 값의 범위 중 하나가 아니라 값 목록 집합 중 하나에 있는 열 값의 멤버십에 기초하여 정의되고 선택된다는 것입니다. 이는 expr이 열 값 또는 열 값에 기반한 식인 PARTITION BY LIST(expr)를 사용하여 정수값을 반환한 다음 VALUES IN(value_list)을 통해 각 파티션을 정의하며, 여기서 value_list는 쉼표로 구분된 정수 목록입니다.

노트

MySQL 5.7에서는 LIST로 파티셔닝 할 때 정수 목록만 일치시킬 수 있습니다. NULL또한 가능합니다.

그러나 LIST COLUMN 파티셔닝을 사용할때 다른 컬럼 유형이 값 목록에 사용될 수 있습니다. 이 섹션의 뒷부분에서 설명합니다.

범위로 정의된 파티션의 경우와 달리 목록 파티션은 특정 순서로 선언할 필요가 없습니다.

다음 예에서는 분할될 테이블의 기본 정의가 여기에 표시된 CREATE TABLE 문에 의해 제공되는 것으로 가정합니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
);

다음 표와 같이 4 개의 프랜차이즈에 20 개의 비디오 상점이 분산되어 있다고 가정합니다.

Region Store ID Numbers

North 3, 5, 6, 9, 17

East 1, 2, 10, 11, 19, 20

West 4, 12, 13, 14, 18

Central 7, 8, 15, 16

동일한 지역에 속하는 상점의 행이 동일한 파티션에 저장되도록 이 테이블을 분할하려면 다음과 같이 CREATE TABLE 문을 사용할 수 있습니다.(위의 테이블 생성 예제를 파티셔닝으로 만드는 방법입니다.)

이를 통해 특정 지역과 관련된 직원 레코드를 테이블에 쉽게 추가하거나 삭제할 수 있습니다. 예를 들어 서부 지역의 모든 상점이 다른 회사에 판매되었다고 가정합니다. MySQL 5.7에서는 ALTER TABLE employee TRUNCATE PARTITION pWest 쿼리를 사용하여 해당 지역의 매장에서 근무하는 직원과 관련된 모든 행을 삭제할 수 있습니다. MySQL 5.7에서는 ALTER TABLE employee TRUNCATE PARTITION pWest 쿼리를 사용하여 해당 지역의 매장에서 근무하는 직원과 관련된 모든 행을 삭제할 수 있습니다. ALTER TABLE....TRUNCATE PARTITION문은 다음과 같은 DELETE문법과 같습니다. DELETE FROM employee WHERE store_id IN (4,12 , 13,14,18);. 그러나 결과는 같아도 더 효율적인것은 TRUNCATE PARTITION이 더 효율적입니다.(ALTER TABLE employee DROP PARTITION pWest를 사용하면 이러한 행도 모두 삭제되지만 테이블 정의에서 pWest파티션도 제거됩니다. ALTER TABLE ... ADD PARTITION문을 사용하여 테이블의 원래 파티션을 복원해야 할수도 있습니다.)

RANGE 파티셔닝과 마찬가지로 LIST 파티셔닝을 해시 또는 키로 파티셔닝과 결합하여 복합 파티셔닝 (하위 파티셔닝)을 생성 할 수 있습니다.

RANGE 파티셔닝의 경우와 달리 MAXVALUE와 같은 catch-all은 없습니다. 파티셔닝 표현식에 필요한 모든 값은 PARTITION ... VALUES IN (...) 절에서 다루어야합니다. 다음 예와 같이 일치하지 않는 파티셔닝 컬럼 값을 포함하는 INSERT 문은 오류와 함께 실패합니다.

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    -> )
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3

단일 INSERT문을 사용하여 여러행을 삽입할 때 테이블이 트랜잭션 스토리지 엔진을 사용하는지 여부에 따라 동작이 달라집니다. InnoDB 테이블의 경우, 명령문은 단일 트랜잭션으로 간주되므로 일치하지 않는 값이 있으면 명령문이 완전히 실패하고 행이 삽입되지 않습니다. MyISAM과 같은 비 트랜잭션 스토리지 엔진을 사용하는 테이블의 경우 일치하지 않는 값을 포함하는 행 앞에 오는 행은 삽입되지만 그 뒤에 오는 행은 삽입되지 않습니다.

IGNORE 키워드를 사용하여 이 유형의 오류를 무시할 수 있습니다. 그렇게하면 일치하지 않는 파티셔닝 컬럼 값을 포함하는 행은 삽입되지 않지만 일치하는 값을 가진 행은 삽입되며 오류는 보고되지 않습니다.

mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> SELECT * FROM h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

MySQL 5.7은 LIST COLUMNS 파티셔닝을 지원합니다. 이것은 LIST 파티셔닝의 변형으로, 여러 컬럼을 파티셔닝 키로 사용할뿐만 아니라 컬럼을 파티션하기 위해 정수 유형 이외의 유형의 컬럼을 사용할 수 있습니다.

■ 컬럼 분할

□ LIST COLUMNS 파티셔닝

다음 두 섹션에서는 RANGE및 LIST파티셔닝의 변형인 COLUMNS 파티셔닝에 대해 설명합니다. COLUMNS 파티셔닝은 파티셔닝 키에서 여러 열을 사용할 수 있게합니다. 이러한 모든 열은 파티션에 행을 배치 할 목적과 파티션 프루닝에서 일치하는 행에 대해 어떤 파티션을 점검해야하는지 판별하기 위해 모두 고려됩니다.

또한 RANGE COLUMNS 파티셔닝 및 LIST COLUMNS 파티셔닝은 값 범위 또는 목록 멤버를 정의하기 위해 정수가 아닌 컬럼 사용을 지원합니다. 허용된 데이터 유형은 다음 목록에 표시됩니다.

+ 모든 정수 유형 : TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER) 및 BIGINT. (RANGE 및 LIST에 의한 파티셔닝과 동일합니다.)

다른 숫자 데이터 유형 (예 : DECIMAL 또는 FLOAT)은 파티션 열로 지원되지 않습니다.

+ DATE 및 DATETIME

날짜 또는 시간과 관련된 다른 데이터 형식을 사용하는 열은 분할 열로 지원되지 않습니다.

+ CHAR, VARCHAR, BINARY 및 VARBINARY 문자열 유형.

TEXT 및 BLOB 열은 분할 열로 지원되지 않습니다.

다음 두 섹션에서 RANGE COLUMNS 및 LIST COLUMNS 파티셔닝에 대한 논의는 MySQL 5.1 이상에서 지원되는 범위 및 목록을 기반으로 한 파티셔닝에 이미 익숙하다고 가정합니다.

□ RANGE COLUMNS 파티셔닝

Range 컬럼 파티셔닝은 범위 파티셔닝과 유사하지만 여러 컬럼 값을 기반으로 범위를 사용하여 파티션을 정의할 수 있습니다. 또한 정수 유형이 아닌 다른 유형의 컬럼을 사용하여 범위를 정의할 수 있습니다.

RANGE COLUMNS 파티셔닝은 다음과 같은 방식으로 RANGE 파티셔닝과 크게 다릅니다.

+ RANGE COLUMNS는 표현식을 허용하지 않으며 열 이름 만 허용합니다.

RANGE COLUMNS는 하나 이상의 열 목록을 허용합니다.

RANGE COLUMNS 파티션은 스칼라 값 간의 비교보다는 튜플 (열 값 목록) 간의 비교를 기반으로 합니다. RANGE COLUMNS 파티션에서 행 배치는 튜플간의 비교를 기반으로합니다. 이에 대해서는 이 섹션의 뒷부분에서 자세히 설명합니다.

+ RANGE COLUMNS 파티셔닝 컬럼은 정수 컬럼으로 제한되지 않습니다. 문자열, DATE 및 DATETIME 열은 분할 열로도 사용할 수 있습니다.

RANGE COLUMNS로 파티션 된 테이블을 작성하는 기본 구문은 다음과 같습니다.

방금 표시된 구문에서 column_list는 하나 이상의 컬럼(파티셔닝 컬럼 목록이라고도 함) 목록이며 value_list는 값 목록입니다 (즉, 파티션 정의 값 목록). 각 파티션 정의에 대해 value_list를 제공해야 하며 각 value_list는 column_list에 열과 동일한 수의 값을 가져야합니다. 일반적으로 COLUMNS절에서 N개의 열을 사용하는 경우 각 VALUES LESS THAN 절에도 N개의 값 목록이 제공되어야합니다.

파티션 컬럼 목록과 각 파티션 영역을 정의하는 값 목록의 요소는 동일한 순서로 발생해야합니다. 또한 값 목록의 각 요소는 컬럼 목록의 해당 요소와 동일한 데이터 유형이어야 합니다. 그러나 파티셔닝 컬럼 목록 및 값 목록의 컬럼 이름 순서는 CREATE TABLE문의 주요 부분에 있는 테이블 컬럼 정의순서와 같을 필요는 없습니다. RANGE로 파티션된 테이블과 같이 MAXVALUE를 사용하여 주어진 열에 삽입된 유효한 값이 항상 이 값보다 작도록 값을 표시 할 수 있습니다. 다음은 이러한 모든 사항을 설명하는 데 도움이되는 CREATE TABLE 문의 예입니다.

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

테이블 rcx는 열 a, b, c, d를 포함합니다. COLUMNS 절에 제공된 파티션 컬럼 목록은 a, d, c 순서로 이들 컬럼 중 3개를 사용합니다. 파티션을 정의하는 데 사용되는 각각의 값 목록에는 동일한 순서로 3개의 값이 있습니다. 즉, 각각 값 목록 튜플의 형식은 (INT, INT, CHAR (3))이며, 열 a, d 및 c에서 사용하는 데이터 형식(순서대로)에 해당합니다.

COLUMNS 절의 컬럼 목록과 일치하는 삽입될 행의 튜플을 VALUES LESS THAN 절에 사용된 튜플과 비교하여 테이블의 파티션을 정의하여 파티션에 행을 배치합니다. 스칼라 값 대신 튜플(즉, 값 목록 또는 값 집합)을 비교하기 때문에 RANGE COLUMNS 파티션에 사용되는 VALUES LESS THAN의 의미는 간단한 RANGE 파티션의 경우와 약간 다릅니다. RANGE 파티셔닝에서 VALUES LESS THAN의 제한값과 동일한 표현식 값을 생성하는 행은 해당 파티션에 배치되지 않습니다. 그러나 RANGE COLUMNS 파티셔닝을 사용하는 경우 파티셔닝 컬럼 목록의 첫 번째 요소가 VALUES LESS THAN값 목록의 첫 번째 요소와 동일한 행이 해당 파티션에 배치 될 수 있습니다.

이 명령문으로 작성된 RANGE 파티션 된 테이블을 있습니다.

CREATE TABLE r1 (
    a INT,
    b INT
)
PARTITION BY RANGE (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

If we insert 3 rows into this table such that the column value for a is 5 for each row, all 3 rows are stored in partition p1 because the a column value is in each case not less than 5, as we can see by executing the proper query against the INFORMATION_SCHEMA.PARTITIONS table:

a에 대한 컬럼 값이 각 행에 대해 5가지가 되도록 3개의 행을 이 테이블에 삽입하면 컬럼 값이 각 경우에 5보다 작지 않기 때문에 3개의 행이 모두 파티션 p1에 저장됩니다. INFORMATION_SCHEMA.PARTITIONS 테이블을 조회해 확인해 봅니다:

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS

□ 테이블 파티셔닝 링크

[MySQL] Table Partitioning - 소개

https://myinfrabox.tistory.com/93

[MySQL] Table Partitioning - 소개

■ 파티셔닝 소개 비 네이티브 파티셔닝이 있는 테이블을 사용하면 ER_WARN_DEPRECATED_SYNTAX경고가 발생합니다. MySQL 5.7.17에서 5.7.20까지, 서버는 시작시 비 기본 파티션을 사용하는 테이블을 식별하��

myinfrabox.tistory.com

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

https://myinfrabox.tistory.com/98

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

■ 파티션 관리 MySQL 5.7은 분할된 테이블을 수정하는 여러 가지 방법을 제공합니다. 기존 파티션을 추가, 삭제, 재정의, 병합 또는 분할할 수 있습니다. 이러한 모든 조치는 ALTER TABLE 문에 대한 파

myinfrabox.tistory.com

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

https://myinfrabox.tistory.com/100

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

이 섹션에서는 파티션 정리(Partition 라고하는 최적화에 대해 설명합니다. 파티션 정리의 기본 개념은 비교적 간단하며“일치하는 값이 없는 파티션은 스캔하지 않습니다”라고 설명 할 수 있습�

myinfrabox.tistory.com

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

https://myinfrabox.tistory.com/102

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

■ 파티션 선택 MySQL 5.7은 명령문을 실행할때 주어진 WHERE 조건과 일치하는 행을 검사해야하는 파티션 및 하위 파티션의 명시적 선택을 지원합니다. 파티션 선택은 특정 파티션 만 일치하는지 검

myinfrabox.tistory.com

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

https://myinfrabox.tistory.com/103

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

■ 파티셔닝에 대한 제한 사항 이 글에서는 MySQL 파티셔닝 지원에 대한 현재 제한 사항에 대해 설명합니다. ▶︎ 금지된 문법. 파티셔닝 표현식에서는 다음 구성이 허용되지 않습니다. 저장 프로

myinfrabox.tistory.com

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※

'Databases > MySQL' 카테고리의 다른 글

[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리) (0)	2020.08.30
[MySQL] Table Partitioning - 파티셔닝 관리 (0)	2020.08.22
[MySQL] Table Partitioning - 소개 (0)	2020.08.17
[MySQL] Query Optimizer 제어 (0)	2020.08.14
[MySQL] 서브 쿼리, 파생(Derived) 테이블 및 뷰 참조 최적화 (0)	2020.08.13

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

'Databases > MySQL' 카테고리의 다른 글

Copyright © MyInfraBox All Rights Reserved

Designed by JB FACTORY

'Databases > MySQL' 카테고리의 다른 글

'Databases/MySQL' 관련 글

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

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

[MySQL] Table Partitioning - 소개

[MySQL] Query Optimizer 제어

Copyright © MyInfraBox All Rights Reserved

Designed by JB FACTORY

티스토리툴바