26.4 Partition Pruning

26.4 Partition Pruning

partition pruning으로 알려진 최적화는 “일치하는 값이 존재할 수 없는 partition은 scan하지 않는다” 라는 비교적 단순한 개념을 기반으로 합니다. partitioned table t1이 다음과 같은 statement로 생성되었다고 가정합니다:

1CREATE TABLE t1 (
2    fname VARCHAR(50) NOT NULL,
3    lname VARCHAR(50) NOT NULL,
4    region_code TINYINT UNSIGNED NOT NULL,
5    dob DATE NOT NULL
6)
7PARTITION BY RANGE( region_code ) (
8    PARTITION p0 VALUES LESS THAN (64),
9    PARTITION p1 VALUES LESS THAN (128),
10    PARTITION p2 VALUES LESS THAN (192),
11    PARTITION p3 VALUES LESS THAN MAXVALUE
12);

다음과 같은 SELECT statement를 사용해서 결과를 얻고자 한다고 가정합니다:

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

반환되어야 할 row들 중 어느 것도 partition p0나 p3에는 존재하지 않는다는 것을 쉽게 알 수 있습니다; 즉, 일치하는 row를 찾기 위해서는 partition p1과 p2만 검색하면 됩니다.

검색 범위를 제한함으로써, table의 모든 partition을 scan하는 것보다 매칭되는 row를 찾기 위해 훨씬 더 적은 시간과 노력을 들일 수 있습니다. 이와 같이 불필요한 partition을 “잘라내는 것”을 pruning이라고 합니다. optimizer가 이 query를 수행할 때 partition pruning을 사용할 수 있으면, 동일한 column 정의와 data를 가진 nonpartitioned table에 대해 같은 query를 실행하는 것보다 query 실행 속도가 수배 이상 빨라질 수 있습니다.

optimizer는 WHERE condition이 다음 두 가지 경우 중 하나로 축약될 수 있을 때마다 pruning을 수행할 수 있습니다:

• partition_column = constant

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

첫 번째 경우에, optimizer는 주어진 값에 대해 partitioning expression을 단순히 평가하고, 그 값을 포함하는 partition을 결정한 다음 이 partition만 scan합니다. 많은 경우에, 등호는 <, >, <=, >=, <>를 포함하는 다른 산술 비교로 대체될 수 있습니다. WHERE 절에서 BETWEEN을 사용하는 일부 query 또한 partition pruning을 활용할 수 있습니다. 예제는 이 절의 뒷부분을 참조하십시오.

두 번째 경우에, optimizer는 list에 있는 각 값에 대해 partitioning expression을 평가하고, 일치하는 partition들의 list를 생성한 다음 이 partition list에 있는 partition만 scan합니다.

SELECT, DELETE, UPDATE statement는 partition pruning을 지원합니다. INSERT statement 또한 insert되는 각 row마다 단 하나의 partition만 access합니다; 이는 table이 HASH 또는 KEY로 partition되어 있는 경우에도 사실이지만, 현재 EXPLAIN의 output에는 이 사실이 표시되지 않습니다.

pruning은 optimizer가 짧은 범위를 동등한 값들의 list로 변환할 수 있는 경우에도 적용될 수 있습니다. 예를 들어, 앞의 예제에서 WHERE 절은 WHERE region_code IN (126, 127, 128, 129)로 변환될 수 있습니다. 그러면 optimizer는 list의 처음 두 값은 partition p1에, 나머지 두 값은 partition p2에 있고, 다른 partition에는 관련된 값이 없으므로 매칭되는 row를 찾기 위해 검색할 필요가 없다는 것을 판단할 수 있습니다.

optimizer는 또한 RANGE COLUMNS 또는 LIST COLUMNS partitioning을 사용하는 table에서, 여러 column에 대해 앞에서 설명한 유형의 비교를 포함하는 WHERE condition에 대해서도 pruning을 수행할 수 있습니다.

이러한 유형의 최적화는 partitioning expression이 equality나 equality 집합으로 축약될 수 있는 범위로 구성되거나, partitioning expression이 증가 또는 감소 관계를 나타내는 경우에 언제든지 적용될 수 있습니다. partitioning expression이 YEAR() 또는 TO_DAYS() function을 사용할 때, DATE 또는 DATETIME column으로 partition된 table에도 pruning이 적용될 수 있습니다. partitioning expression이 TO_SECONDS() function을 사용하는 이러한 table에 대해서도 pruning이 적용될 수 있습니다.

다음과 같은 statement를 사용하여 DATE column으로 partition된 table t2가 생성되었다고 가정합니다:

1CREATE TABLE t2 (
2    fname VARCHAR(50) NOT NULL,
3    lname VARCHAR(50) NOT NULL,
4    region_code TINYINT UNSIGNED NOT NULL,
5    dob DATE NOT NULL
6)
7PARTITION BY RANGE( YEAR(dob) ) (
8    PARTITION d0 VALUES LESS THAN (1970),
9    PARTITION d1 VALUES LESS THAN (1975),
10    PARTITION d2 VALUES LESS THAN (1980),
11    PARTITION d3 VALUES LESS THAN (1985),
12    PARTITION d4 VALUES LESS THAN (1990),
13    PARTITION d5 VALUES LESS THAN (2000),
14    PARTITION d6 VALUES LESS THAN (2005),
15    PARTITION d7 VALUES LESS THAN MAXVALUE
16);

다음과 같이 t2를 사용하는 statement들은 partition pruning을 활용할 수 있습니다:

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

마지막 statement의 경우, optimizer는 다음과 같이 작동할 수도 있습니다:

1. range의 하한을 포함하는 partition을 찾습니다.

   YEAR('1984-06-21')는 값 1984를 반환하며, 이는 partition d3에 있습니다.

2. range의 상한을 포함하는 partition을 찾습니다.

   YEAR('1999-06-21')는 1999로 평가되며, 이는 partition d5에 있습니다.

3. 이 두 partition과 그 사이에 있을 수 있는 partition만 scan합니다.

   이 경우, partition d3, d4, d5만 scan된다는 의미입니다. 나머지 partition은 안전하게 무시될 수 있으며 (그리고 실제로 무시됩니다).

주의

partitioned table에 대한 statement의 WHERE condition에서 참조되는 잘못된 DATE 및 DATETIME 값은 NULL로 처리됩니다. 이는 SELECT * FROM partitioned_table WHERE date_column < '2008-12-00'과 같은 query는 어떤 값도 반환하지 않는다는 것을 의미합니다(Bug #40972 참조).

지금까지는 RANGE partitioning을 사용하는 예제만 살펴보았지만, pruning은 다른 partitioning type에도 적용될 수 있습니다.

partitioning expression이 증가 또는 감소 관계를 갖는 LIST partitioning table을 생각해 보겠습니다. 예를 들어, 다음에 보이는 table t3가 있습니다. (이 예제에서는 설명을 간략히 하기 위해 region_code column의 값이 1에서 10까지(포함)로 제한된다고 가정합니다.)

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

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3과 같은 statement의 경우, optimizer는 값 1, 2, 3이 어떤 partition에 있는지 (r0과 r1)를 결정하고, 나머지(r2와 r3)는 건너뜁니다.

HASH 또는 [LINEAR] KEY로 partition된 table의 경우에도, WHERE 절이 partitioning expression에 사용된 column에 대해 단순한 = 관계를 사용하는 경우에는 partition pruning이 가능합니다. 다음과 같이 생성된 table을 고려해 봅니다:

1CREATE TABLE t4 (
2    fname VARCHAR(50) NOT NULL,
3    lname VARCHAR(50) NOT NULL,
4    region_code TINYINT UNSIGNED NOT NULL,
5    dob DATE NOT NULL
6)
7PARTITION BY KEY(region_code)
8PARTITIONS 8;

column 값과 constant를 비교하는 statement는 pruning될 수 있습니다:

1UPDATE t4 WHERE region_code = 7;

pruning은 optimizer가 이러한 condition을 IN relation으로 변환할 수 있기 때문에, 짧은 범위에 대해서도 사용될 수 있습니다. 예를 들어, 앞에서 정의한 동일한 table t4를 사용하여, 다음과 같은 query는 pruning될 수 있습니다:

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

이 두 경우 모두, optimizer는 WHERE 절을 WHERE region_code IN (3, 4, 5)로 변환합니다.

주의

이 최적화는 범위 크기가 partition 수보다 작을 때만 사용됩니다. 다음 statement를 고려해 보십시오:

1DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

WHERE 절의 범위는 9개의 값 (4, 5, 6, 7, 8, 9, 10, 11, 12)을 포함하지만, t4는 partition이 8개뿐입니다. 이는 이 DELETE가 pruning될 수 없음을 의미합니다.

table이 HASH 또는 [LINEAR] KEY로 partition된 경우, pruning은 integer column에만 사용할 수 있습니다. 예를 들어, dob이 DATE column이기 때문에, 다음 statement는 pruning을 사용할 수 없습니다:

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

그러나 table이 year 값을 INT column에 저장한다면, WHERE year_col >= 2001 AND year_col <= 2005를 가진 query는 pruning될 수 있습니다.

NDB storage engine(예: MySQL Cluster에서 사용하는 storage engine)처럼 자동 partitioning을 제공하는 storage engine을 사용하는 table도, 명시적으로 partition되어 있는 경우에는 pruning될 수 있습니다.