MySQL :: MySQL 8.4 Reference Manual :: 26.4 Partition Pruning

Back-End•2025.11.21

MySQL 공식 문서 번역

원문: https://dev.mysql.com/doc/refman/8.4/en/partitioning-pruning.html

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에서만 검색하면 됩니다. 검색을 제한함으로써 테이블의 모든 파티션을 스캔하는 것보다 일치하는 행을 찾는 데 훨씬 적은 시간과 노력을 소비할 수 있습니다. 이러한 불필요한 파티션의 **"제거"**를 Pruning이라고 합니다. 옵티마이저가 이 쿼리를 수행할 때 Partition Pruning을 활용할 수 있으면, 쿼리 실행이 동일한 열 정의와 데이터를 포함하는 분할되지 않은 테이블에 대한 동일한 쿼리보다 한 자릿수 빠를 수 있습니다.

옵티마이저는 WHERE 조건을 다음 두 경우 중 하나로 축소할 수 있을 때마다 Pruning을 수행할 수 있습니다:

partition_column = constant

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

첫 번째 경우, 옵티마이저는 단순히 주어진 값에 대한 파티셔닝 표현식을 평가하고, 해당 값을 포함하는 파티션을 결정한 후, 이 파티션만 스캔합니다. 많은 경우, 등호는 <, >, <=, >=, <> 등의 다른 산술 비교로 대체될 수 있습니다. WHERE 절에서 BETWEEN을 사용하는 일부 쿼리도 Partition Pruning을 활용할 수 있습니다. 이 섹션의 뒷부분에 있는 예제를 참조하세요.

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

SELECT, DELETE, UPDATE 명령문은 Partition Pruning을 지원합니다. INSERT 명령문도 삽입된 행당 하나의 파티션에만 액세스합니다. 이는 HASH 또는 KEY로 분할된 테이블의 경우에도 마찬가지이지만, 현재 EXPLAIN의 출력에는 표시되지 않습니다.

Pruning은 옵티마이저가 동등한 값 목록으로 변환할 수 있는 짧은 범위에도 적용될 수 있습니다. 예를 들어, 이전 예제에서 WHERE 절을 WHERE region_code IN (126, 127, 128, 129)로 변환할 수 있습니다. 그러면 옵티마이저는 목록의 처음 두 값이 파티션 p1에서 발견되고, 나머지 두 값이 파티션 p2에서 발견되며, 다른 파티션에는 관련 값이 없으므로 일치하는 행을 검색할 필요가 없다고 결정할 수 있습니다.

옵티마이저는 RANGE COLUMNS 또는 LIST COLUMNS 파티셔닝을 사용하는 테이블의 경우 여러 열에 대한 이전 유형의 비교를 포함하는 WHERE 조건에 대해서도 Pruning을 수행할 수 있습니다.

이러한 유형의 최적화는 파티셔닝 표현식이 동등성 또는 동등성 집합으로 축소될 수 있는 범위로 구성되거나, 파티셔닝 표현식이 증가 또는 감소 관계를 나타낼 때마다 적용될 수 있습니다. Pruning은 파티셔닝 표현식이 YEAR() 또는 TO_DAYS() 함수를 사용할 때 DATE 또는 DATETIME 열에서 분할된 테이블에도 적용될 수 있습니다. Pruning은 파티셔닝 표현식이 TO_SECONDS() 함수를 사용할 때 이러한 테이블에도 적용될 수 있습니다.

DATE 열에서 분할된 테이블 t2가 다음과 같이 생성되었다고 가정해봅시다:

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를 사용하는 다음 명령문은 Partition Pruning을 활용할 수 있습니다:

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 파티셔닝을 사용하는 예제만 살펴봤지만, Pruning은 다른 파티셔닝 유형에도 적용될 수 있습니다.

파티셔닝 표현식이 증가 또는 감소하는 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 절이 파티셔닝 표현식에 사용되는 열에 대해 간단한 = 관계를 사용할 때도 Partition Pruning이 가능합니다. 다음과 같이 생성된 테이블을 고려해봅시다:

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;

열 값을 상수와 비교하는 명령문은 Pruning될 수 있습니다:

UPDATE t4 WHERE region_code = 7;

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

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)로 변환됩니다.

중요 이 최적화는 범위 크기가 파티션 수보다 작을 때만 사용됩니다. 다음 명령문을 고려해봅시다: WHERE 절의 범위는 9개의 값(4, 5, 6, 7, 8, 9, 10, 11, 12)을 포함하지만, t4는 8개의 파티션만 있습니다. 이는 DELETE를 Pruning할 수 없음을 의미합니다.

테이블이 HASH 또는 [LINEAR] KEY로 분할될 때, Pruning은 정수 열에만 사용될 수 있습니다. 예를 들어, dob가 DATE 열이므로 다음 명령문은 Pruning을 사용할 수 없습니다:

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

그러나 테이블이 연도 값을 INT 열에 저장하는 경우, WHERE year_col >= 2001 AND year_col <= 2005를 포함하는 쿼리는 Pruning될 수 있습니다.

MySQL Cluster에서 사용되는 NDB 스토리지 엔진과 같이 자동 파티셔닝을 제공하는 스토리지 엔진을 사용하는 테이블은 명시적으로 분할된 경우 Pruning될 수 있습니다.

아직 댓글이 없습니다.

첫 번째 댓글을 작성해보세요!