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

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

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

 

다음과 같은 SELECT 문에서 결과를 가져오려고 합니다.

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

리턴될 행이 파티션 p0또는 p3중 하나에 있지 않음을 쉽게 알 수 있습니다. 즉, 파티션 p1과 p2에서만 검색하여 일치하는 행을 찾아야 합니다. 이렇게 하면 테이블의 모든 파티션을 스캔하는데 필요한 것보다 일치하는 행을 찾는데 훨씬 적은 시간과 노력이 소비됩니다. 불필요한 파티션의 "잘라 내기"를 프루닝(Pruning : 정리)라고합니다. 옵티마이저가 이 쿼리를 수행 할 때 파티션 프루닝를 사용할 수있는 경우, 동일한 컬럼 정의 및 데이터를 포함하는 파티션되지 않은 테이블에 대해 동일한 쿼리보다 쿼리 실행 속도가 훨씬 빠릅니다.

 

노트

분할된 MyISAM테이블에서 제거를 수행하면 MyISAM스토리지 엔진의 설계로 인해 검사 여부에 관계없이 모든 파티션이 열립니다. 이는 테이블의 모든 파티션을 포괄할 수 있는 충분한 수의 파일 디스크립터가 있어야 함을 의미합니다.

이 제한은 InnoDB와 같은 다른 MySQL스토리지 엔진을 사용하는 파티션된 테이블에는 적용되지 않습니다.

 

옵티마이저 프로그램은 WHERE 조건을 다음 두 경우 중 하나로 줄일 수 있을 때마다 제거를 수행할 수 있습니다.

+ partition_column = 상수

+ partition_column IN (상수 1, 상수 2, ..., 상수 N)

 

첫 번째 경우, 옵티마이저는 제공된 값에 대한 파티셔닝 표현식을 평가하고 해당 값이 포함된 파티션을 판별 한 후 이 파티션만 스캔합니다. 대부분의 경우 등호는 <,>, <=,> = 및 <>을 포함한 다른 산술 비교로 대체 될 수 있습니다. WHERE 절에서 BETWEEN을 사용하는 일부 쿼리도 파티션 정리를 활용할 수 있습니다. 이 섹션의 뒷부분에 나오는 예를 참조합니다.

 

두 번째 경우, 옵티마이저는 목록의 각 값에 대한 파티셔닝 표현식을 평가하고 일치하는 파티션 목록을 작성한 후 이 파티션 목록의 파티션만 스캔합니다.

 

MySQL은 파티션 정리를 SELECT, DELETE 및 UPDATE 문에 적용 할 수 있습니다. INSERT 문은 삽입 된 행당 하나의 파티션에만 액세스합니다. 이것은 현재 EXPLAIN의 출력에 표시되지 않지만 HASH 또는 KEY로 파티션된 테이블의 경우에도 마찬가지입니다.

 

잘라내기는 단거리에 적용할 수 있으며 최적화 프로그램은 동등한 값 목록으로 변환할 수 있습니다. 예를 들어, 이전 예에서 WHERE 절은 WHERE region_code IN (126, 127, 128, 129)으로 변환될 수 있습니다. 그런 다음 옵티마이 저는 목록의 처음 두 값이 파티션 p1에서 발견되고 나머지 두 값은 파티션 p2에서 발견되고 다른 파티션에는 관련 값이 없으므로 일치하는 행을 검색할 필요가 없음을 판별할 수 있습니다.

 

옵티마이저는 또한 RANGE COLUMNS 또는 LIST COLUMNS 파티셔닝을 사용하는 테이블의 여러 열에서 이전 유형의 비교를 포함하는 WHERE 조건에 대해 프룬(prune : 정리)을 수행 할 수 있습니다.

 

이러한 유형의 최적화는 파티셔닝 표현식이 동등성 또는 동등성 세트로 감소 될 수있는 범위로 구성될 때마다 또는 파티셔닝 표현식이 증가 또는 감소하는 관계를 나타내는 경우에 적용될 수 있습니다. 분할식이 YEAR() 또는 TO_DAYS() 함수를 사용하는 경우 DATE 또는 DATETIME 열에 분할된 테이블에 제거를 적용 할 수도 있습니다. 또한 MySQL 5.7에서는 파티셔닝 표현식이 TO_SECONDS() 함수를 사용할때 이러한 테이블에 프루닝을 적용 할 수 있습니다.

 

여기에 표시된대로 정의된 테이블 t2가 DATE 열에서 분할되었습니다.

CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION d0 VALUES LESS THAN (1970),
    PARTITION d1 VALUES LESS THAN (1975),
    PARTITION d2 VALUES LESS THAN (1980),
    PARTITION d3 VALUES LESS THAN (1985),
    PARTITION d4 VALUES LESS THAN (1990),
    PARTITION d5 VALUES LESS THAN (2000),
    PARTITION d6 VALUES LESS THAN (2005),
    PARTITION d7 VALUES LESS THAN MAXVALUE
);

 

t2를 사용하는 다음 명령문은 파티션 프루닝을 사용할 수 있습니다.

SELECT * FROM t2 WHERE dob = '1982-06-23';
UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

 

마지막 문의 경우 최적화 프로그램은 다음과 같이 작동 할 수도 있습니다.

1. 범위의 최저값을 포함하는 파티션을 찾습니다.

YEAR ( '1984-06-21')는 1984 값을 생성하며 이는 파티션 d3에 있습니다.

 

2. 범위의 상한이 포함 된 파티션을 찾습니다.

YEAR ( '1999-06-21')는 파티션 d5에있는 1999로 평가됩니다.

 

3.이 두 파티션과 그 사이에있는 파티션만 스캔합니다.

 

이 경우 파티션 d3, d4 및 d5만 검색됩니다. 나머지 파티션은 안전하게 건너뛰게 됩니다.(그리고 무시됩니다.)

 

중요사항

파티션된 테이블에 대한 명령문의 WHERE 조건에서 참조된 유효하지 않은 DATE 및 DATETIME 값은 NULL로 처리됩니다. 이는 SELECT * FROM partitioned_table WHERE date_column < '2008-12-00'과 같은 쿼리가 값을 반환하지 않음을 의미합니다.

 

지금까지 RANGE 파티셔닝을 사용하는 예만 살펴 보았지만, 프루닝을 다른 파티셔닝 유형에도 적용 할 수 있습니다.

 

LIST로 분할된 테이블이 있습니다. 여기에 표시된 테이블 t3과 같이 분할 표현식이 증가 또는 감소합니다. (이 예에서는 간결성을 위해 region_code 열이 1에서 10 사이의 값으로 제한된다고 가정합니다.)

CREATE TABLE t3 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
    PARTITION r0 VALUES IN (1, 3),
    PARTITION r1 VALUES IN (2, 5, 8),
    PARTITION r2 VALUES IN (4, 9),
    PARTITION r3 VALUES IN (6, 7, 10)
);

 

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3이란 쿼리의 경우, 옵티마이 저는 1, 2 및 3값을 찾은 파티션(r0 및 r1)을 판별하고 나머지 파티션 (r2 및 r3)을 건너 뜁니다.

 

HASH 또는 [LINEAR] KEY로 분할된 테이블의 경우 WHERE절이 분할식에 사용된 열에 대해 단순=관계를 사용하는 경우에도 분할 프루닝이 가능합니다. 다음과 같이 작성된 테이블이 있습니다.

CREATE TABLE t4 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;

 

열 값을 상수와 비교하는 명령문을 제거 할 수 있습니다.

UPDATE t4 WHERE region_code = 7;

 

옵티마이저가 이러한 조건을 IN관계로 전환할 수 있기 때문에 제거는 작은 범위에도 사용할 수 있습니다. 예를 들어, 이전에 정의된 것과 동일한 테이블 t4를 사용하여 다음과 같은 쿼리를 제거할 수 있습니다.

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

위의 두 경우 모두 WHERE 절은 옵티마이저에 의해 WHERE region_code IN (3, 4, 5)으로 변환됩니다.

 

 

 

중요사항

이 최적화는 범위 크기가 파티션 수보다 작은 경우에만 사용됩니다. 다음과 같은 문법이 있습니다 :

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

WHERE 절의 범위는 9 개의 값 (4, 5, 6, 7, 8, 9, 10, 11, 12)을 포함하지만 t4에는 8개의 파티션 만 있습니다. 이는 DELETE를 제거할 수 없음을 의미합니다.

 

테이블이 HASH 또는 [LINEAR] KEY로 분할된 경우 제거는 정수 열에서만 사용할 수 있습니다. 예를 들어, dob가 DATE 컬럼이므로 이 명령문은 프룬을 사용할 수 없습니다.

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

그러나 테이블이 INT열에 연도 값을 저장하면 WHERE year_col> = 2001 AND year_col <= 2005 인 쿼리를 제거할 수 있습니다.

 

MySQL 5.7.1 이전에는 NDB 클러스터에서 사용하는 NDB 스토리지 엔진과 같은 자동 파티셔닝을 제공하는 스토리지 엔진을 사용하는 모든 테이블에 대해 파티션 프루닝이 비활성화되었습니다. MySQL 5.7.1부터는 이러한 테이블이 명시적으로 파티션된 경우 정리할 수 있습니다.

 

□ 테이블 파티셔닝 링크

[MySQL] Table Partitioning - 소개

https://myinfrabox.tistory.com/93

[MySQL] Table Partitioning - 소개

[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/102

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

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

https://myinfrabox.tistory.com/103

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