MySQL5.6 Partition Pruning

19.4 Partition Pruning

이 섹션에서는 Partition Pruning라는 최적화에 대해 설명합니다. Partition Pruning 뒤의 중심 개념은 비교적 단순하고 "일치하는 값이없는 파티션은 스캔하지 않는다"고 표현할 수 있습니다. 다음 문에 의해 정의 된 파티션 된 테이블 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 만을 검색하여 일치하는 행을 찾아야합니다. 이렇게하면 일치하는 행을 검색하는 데 소비하는 시간과 노력이 테이블의 모든 파티션을 스캔하는 데 필요한 것보다 적습니다. 필요없는 파티션을 이처럼 '생략'을 정리라고합니다. 최적화 프로그램이 쿼리 실행 파티션 가지 치기를 사용할 수있는 경우에는 동일한 컬럼 정의 및 데이터를 포함하는 분할되지 않은 테이블에서 같은 쿼리를 실행하는 것보다 속도가 크게 향상시킬 수 있습니다.

WHERE 조건을 다음의 두 가지 경우 중 하나에 정리 될 때, 최적화 정리를 실행할 수 있습니다.

partition_column = constant
partition_column IN ( constant1 , constant2 , ..., constantN )

첫 번째 경우, 최적화는 지정된 값에 대해 분할 식을 간단하게 평가하고 어떤 파티션에 값이 포함되는지를 판별하여이 파티션 만 검사합니다. 종종이 등호를 다른 산술 비교 ( < , > , <= , > >= 및 <> 포함)으로 대체 할 수 있습니다. WHERE 절 BETWEEN 을 사용하는 쿼리도 파티션 가지 치기의 이점을 활용할 수 있습니다. 이 섹션의 후속 예제를 참조하십시오.

두 번째 경우에서 최적화 목록의 각 값에 대해 분할 식을 평가하여 일치하는 파티션의 목록을 만든 후이 파티션 목록 파티션 만 검사합니다.

MySQL은 파티션 가지 치기를 SELECT , DELETE 및 UPDATE 문에 적용 할 수 있습니다. INSERT 문은 현재 정리 할 수 없습니다.

짧은 범위에 정리를 적용 할 수 있습니다 (최적화가 동등한 값 목록에 변환 할 수있는). 예를 들어 앞의 예에서는 WHERE 절을 WHERE region_code IN (126, 127, 128, 129) 로 변환 할 수 있습니다. 그러면 최적화 프로그램은 목록의 첫 번째 3 개의 값은 파티션 p1 찾을 나머지 3 개의 값은 파티션 p2 찾을 다른 파티션에 대한 값은 포함되지 않기 때문에 일치하는 행을 검색하기 필요가 없다고 판단 할 수 있습니다.

MySQL 5.6의 최적화는 RANGE COLUMNS 또는 LIST COLUMNS 파티셔닝을 사용하는 테이블의 여러 열에서 상기 유형의 비교를 사용하는 WHERE 조건의 정리를 수행 할 수 있습니다.

이 유형의 최적화는 파티셔닝식이 동일성과 범위로 구성되어 있고 그것을 동일성 세트에 정리 될 때, 또는 파티셔닝식이 증감 관계를 나타낼 때 적용 할 수 있습니다. 파티셔닝식이 YEAR() 또는 TO_DAYS() 함수를 사용하면 DATE 컬럼 또는 DATETIME 컬럼에서 분할 된 테이블에 가지 치기를 적용 할 수 있습니다. 또한 MySQL 5.6에서는 파티셔닝식이 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'

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

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

YEAR('1984-06-21') 는 값 1984 를 돌려 주어, 그 파티션 d3 찾을 수 있습니다.
범위의 상한이 포함 된 파티션을 찾습니다.

YEAR('1999-06-21') 은 1999 평가되며 그것은 파티션 d5 찾을 수 있습니다.
이러한 두 개의 파티션 및 그들 사이에있을 수있는 파티션 만 검사합니다.

이 경우 이것은 파티션 d3 , d4 및 d5 만 검색되는 것을 의미합니다. 나머지 파티션은 무시해도 안전합니다 (그리고 무시됩니다).

중요

파티션 된 테이블에 대한 문의 WHERE 조건에서 참조되는 잘못된 DATE 및 DATETIME 값은 NULL 로 처리됩니다. 이것은 SELECT * FROM partitioned_table WHERE date_column < '2008-12-00' 등의 쿼리는 값을 돌려주지 않는 것을 의미합니다 (Bug # 40972를 참조하십시오).

여기까지 RANGE 파티셔닝을 사용하는 예만보고 왔지만 가지 치기는 다른 파티션 유형에도 적용 할 수 있습니다.

다음 테이블 t3 처럼 분할 식 증가 또는 감소하고있는 LIST 로 파티션 된 테이블이 있다고합니다. (이 예제에서는 간단하게하기 위해 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.6.8 이후에서는 자동 파티셔닝을 제공하는 스토리지 엔진 (MySQL Cluster가 사용하는 NDB 스토리지 엔진 (현재 MySQL 5.6에서 지원되지 않습니다) 등)를 사용하는 모든 테이블에서 파티션 가지 치기 은 무효입니다 (Bug # 14672885). MySQL 5.6.10 이상에서 명시 적으로 분할되어있는 경우에는 그러한 테이블을 정리합니다 (Bug # 14827952).