26.2.7 How MySQL Partitioning Handles NULL

26.2.7 How MySQL Partitioning Handles NULL

MySQL에서 partitioning은 partitioning expression의 값이 NULL이 되는 것을, 그 값이 column 값이든 사용자가 제공한 expression의 값이든 간에, 전혀 허용하지 않는 방향으로 처리하지 않습니다. 정수 값을 반환해야 하는 expression의 값으로 NULL을 사용하는 것이 허용되기는 하지만, NULL은 숫자가 아니라는 점을 염두에 두는 것이 중요합니다. MySQL의 partitioning 구현은 ORDER BY가 그러하듯이, NULL을 어떤 non-NULL 값보다 작은 것으로 취급합니다.

이는 NULL에 대한 처리 방식이 partitioning 유형에 따라 달라지며, 이에 대비하지 않았을 경우 예상치 못한 동작을 만들어낼 수도 있음을 의미합니다. 이런 이유로, 이 절에서는 각 MySQL partitioning type이 행을 저장할 partition을 결정할 때 NULL 값을 어떻게 처리하는지 설명하고, 각 경우에 대한 예제를 제공합니다.

RANGE partitioning에서의 NULL 처리.

RANGE로 partitioning된 테이블에, partition을 결정하는 데 사용되는 column 값이 NULL이 되도록 행을 삽입하면, 그 행은 가장 낮은 partition에 삽입됩니다. 데이터베이스 p 안에 다음과 같이 생성된 두 개의 테이블을 고려해 보겠습니다:

1mysql> CREATE TABLE t1 (
2    ->     c1 INT,
3    ->     c2 VARCHAR(20)
4    -> )
5    -> PARTITION BY RANGE(c1) (
6    ->     PARTITION p0 VALUES LESS THAN (0),
7    ->     PARTITION p1 VALUES LESS THAN (10),
8    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
9    -> );
10Query OK, 0 rows affected (0.09 sec)
11
12mysql> CREATE TABLE t2 (
13    ->     c1 INT,
14    ->     c2 VARCHAR(20)
15    -> )
16    -> PARTITION BY RANGE(c1) (
17    ->     PARTITION p0 VALUES LESS THAN (-5),
18    ->     PARTITION p1 VALUES LESS THAN (0),
19    ->     PARTITION p2 VALUES LESS THAN (10),
20    ->     PARTITION p3 VALUES LESS THAN MAXVALUE
21    -> );
22Query OK, 0 rows affected (0.09 sec)

다음과 같은 query를 INFORMATION_SCHEMA 데이터베이스 안의 PARTITIONS 테이블에 대해 실행하면, 이 두 개의 CREATE TABLE statement에 의해 생성된 partition들을 볼 수 있습니다:

1mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
2     >   FROM INFORMATION_SCHEMA.PARTITIONS
3     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
4+------------+----------------+------------+----------------+-------------+
5| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6+------------+----------------+------------+----------------+-------------+
7| t1         | p0             |          0 |              0 |           0 |
8| t1         | p1             |          0 |              0 |           0 |
9| t1         | p2             |          0 |              0 |           0 |
10| t2         | p0             |          0 |              0 |           0 |
11| t2         | p1             |          0 |              0 |           0 |
12| t2         | p2             |          0 |              0 |           0 |
13| t2         | p3             |          0 |              0 |           0 |
14+------------+----------------+------------+----------------+-------------+
157 rows in set (0.00 sec)

(이 테이블에 대한 더 많은 정보는 Section 28.3.26, “The INFORMATION_SCHEMA PARTITIONS Table”을 참조하십시오.) 이제 partitioning key로 사용되는 column에 NULL을 포함하는 단일 행을 각 테이블에 채워 넣고, 한 쌍의 SELECT statement를 사용해 행이 삽입되었는지 확인해 보겠습니다:

1mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
2Query OK, 1 row affected (0.00 sec)
3
4mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
5Query OK, 1 row affected (0.00 sec)
6
7mysql> SELECT * FROM t1;
8+------+--------+
9| id   | name   |
10+------+--------+
11| NULL | mothra |
12+------+--------+
131 row in set (0.00 sec)
14
15mysql> SELECT * FROM t2;
16+------+--------+
17| id   | name   |
18+------+--------+
19| NULL | mothra |
20+------+--------+
211 row in set (0.00 sec)

앞서의 query를 INFORMATION_SCHEMA.PARTITIONS에 대해 다시 실행하고 output을 확인하면, 삽입된 행들이 어떤 partition에 저장되었는지 알 수 있습니다:

1mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
2     >   FROM INFORMATION_SCHEMA.PARTITIONS
3     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
4+------------+----------------+------------+----------------+-------------+
5| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6+------------+----------------+------------+----------------+-------------+
7| t1         | p0             |          1 |             20 |          20 |
8| t1         | p1             |          0 |              0 |           0 |
9| t1         | p2             |          0 |              0 |           0 |
10| t2         | p0             |          1 |             20 |          20 |
11| t2         | p1             |          0 |              0 |           0 |
12| t2         | p2             |          0 |              0 |           0 |
13| t2         | p3             |          0 |              0 |           0 |
14+------------+----------------+------------+----------------+-------------+
157 rows in set (0.01 sec)

이들 행이 각 테이블의 가장 낮은 번호의 partition에 저장되었음을, 이 partition들을 drop한 다음 SELECT statement를 다시 실행해 보는 방식으로도 확인할 수 있습니다:

1mysql> ALTER TABLE t1 DROP PARTITION p0;
2Query OK, 0 rows affected (0.16 sec)
3
4mysql> ALTER TABLE t2 DROP PARTITION p0;
5Query OK, 0 rows affected (0.16 sec)
6
7mysql> SELECT * FROM t1;
8Empty set (0.00 sec)
9
10mysql> SELECT * FROM t2;
11Empty set (0.00 sec)

(ALTER TABLE ... DROP PARTITION에 대한 더 많은 정보는 Section 15.1.11, “ALTER TABLE Statement”를 참조하십시오.)

NULL은 SQL 함수을 사용하는 partitioning expression에 대해서도 이런 방식으로 처리됩니다. 예를 들어 다음과 같은 CREATE TABLE statement로 테이블을 정의한다고 가정해 보겠습니다:

1CREATE TABLE tndate (
2    id INT,
3    dt DATE
4)
5PARTITION BY RANGE( YEAR(dt) ) (
6    PARTITION p0 VALUES LESS THAN (1990),
7    PARTITION p1 VALUES LESS THAN (2000),
8    PARTITION p2 VALUES LESS THAN MAXVALUE
9);

다른 MySQL 함수들과 마찬가지로, YEAR(NULL)은 NULL을 반환합니다. dt column 값이 NULL인 행은, partitioning expression이 다른 어떤 값보다 작은 값으로 평가된 것처럼 취급되며, 따라서 partition p0에 삽입됩니다.

LIST partitioning에서의 NULL 처리.

LIST로 partitioning된 테이블은, 그 partition들 가운데 하나가 value-list를 정의할 때 NULL을 포함하여 정의된 경우에만 NULL 값을 허용합니다. 그 역으로, value list에서 NULL을 명시적으로 사용하지 않은 LIST partitioned 테이블은, 다음 예제에서 볼 수 있듯이, partitioning expression이 NULL 값을 결과로 내는 행들을 거부합니다:

1mysql> CREATE TABLE ts1 (
2    ->     c1 INT,
3    ->     c2 VARCHAR(20)
4    -> )
5    -> PARTITION BY LIST(c1) (
6    ->     PARTITION p0 VALUES IN (0, 3, 6),
7    ->     PARTITION p1 VALUES IN (1, 4, 7),
8    ->     PARTITION p2 VALUES IN (2, 5, 8)
9    -> );
10Query OK, 0 rows affected (0.01 sec)
11
12mysql> INSERT INTO ts1 VALUES (9, 'mothra');
13ERROR 1504 (HY000): Table has no partition for value 9
14
15mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
16ERROR 1504 (HY000): Table has no partition for value NULL

c1 값이 0 이상 8 이하인 행만 ts1에 삽입될 수 있습니다. NULL은 숫자 9와 마찬가지로 이 범위 밖에 속합니다. 다음과 같이 value list에 NULL을 포함하는 테이블 ts2와 ts3를 생성할 수 있습니다:

1mysql> CREATE TABLE ts2 (
2    ->     c1 INT,
3    ->     c2 VARCHAR(20)
4    -> )
5    -> PARTITION BY LIST(c1) (
6    ->     PARTITION p0 VALUES IN (0, 3, 6),
7    ->     PARTITION p1 VALUES IN (1, 4, 7),
8    ->     PARTITION p2 VALUES IN (2, 5, 8),
9    ->     PARTITION p3 VALUES IN (NULL)
10    -> );
11Query OK, 0 rows affected (0.01 sec)
12
13mysql> CREATE TABLE ts3 (
14    ->     c1 INT,
15    ->     c2 VARCHAR(20)
16    -> )
17    -> PARTITION BY LIST(c1) (
18    ->     PARTITION p0 VALUES IN (0, 3, 6),
19    ->     PARTITION p1 VALUES IN (1, 4, 7, NULL),
20    ->     PARTITION p2 VALUES IN (2, 5, 8)
21    -> );
22Query OK, 0 rows affected (0.01 sec)

partitioning을 위한 value list를 정의할 때, NULL을 다른 값처럼 (그리고 그래야만) 취급할 수 있습니다. 예를 들어 VALUES IN (NULL)과 VALUES IN (1, 4, 7, NULL)은 모두 유효하며, VALUES IN (1, NULL, 4, 7), VALUES IN (NULL, 1, 4, 7) 등도 마찬가지로 유효합니다.

column c1에 대해 NULL을 갖는 행을 ts2와 ts3 각각에 삽입할 수 있습니다:

1mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
2Query OK, 1 row affected (0.00 sec)
3
4mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
5Query OK, 1 row affected (0.00 sec)

INFORMATION_SCHEMA.PARTITIONS에 대해 적절한 query를 실행하면 (이전 예제들에서와 마찬가지로 partitioned 테이블들이 p 데이터베이스에 생성되었다고 가정합니다), 방금 삽입한 행들을 저장하는 데 사용된 partition이 어떤 것인지 알 수 있습니다:

1mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
2     >   FROM INFORMATION_SCHEMA.PARTITIONS
3     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
4+------------+----------------+------------+----------------+-------------+
5| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6+------------+----------------+------------+----------------+-------------+
7| ts2        | p0             |          0 |              0 |           0 |
8| ts2        | p1             |          0 |              0 |           0 |
9| ts2        | p2             |          0 |              0 |           0 |
10| ts2        | p3             |          1 |             20 |          20 |
11| ts3        | p0             |          0 |              0 |           0 |
12| ts3        | p1             |          1 |             20 |          20 |
13| ts3        | p2             |          0 |              0 |           0 |
14+------------+----------------+------------+----------------+-------------+
157 rows in set (0.01 sec)

이 절의 앞부분에서 보여준 것처럼, 이러한 partition들을 삭제한 다음 SELECT를 수행하는 방식으로도, 행들을 저장하는 데 어떤 partition이 사용되었는지 확인할 수 있습니다.

HASH 및 KEY partitioning에서의 NULL 처리.

HASH나 KEY로 partitioning된 테이블의 경우에는 NULL이 다소 다르게 처리됩니다. 이 경우, NULL 값을 반환하는 어떤 partition expression도, 그 반환 값이 0인 것처럼 취급됩니다. 이 동작은, HASH로 partitioning된 테이블을 생성하고 적절한 값들을 포함하는 레코드로 채운 뒤 파일 시스템에 미치는 영향을 살펴봄으로써 검증할 수 있습니다. 예를 들어 (마찬가지로 p 데이터베이스 안에) 다음 statement를 사용해 생성된 테이블 th가 있다고 가정해 보겠습니다:

1mysql> CREATE TABLE th (
2    ->     c1 INT,
3    ->     c2 VARCHAR(20)
4    -> )
5    -> PARTITION BY HASH(c1)
6    -> PARTITIONS 2;
7Query OK, 0 rows affected (0.00 sec)

이 테이블에 속한 partition들은 다음에 보이는 query를 사용해 조회할 수 있습니다:

1mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
2     >   FROM INFORMATION_SCHEMA.PARTITIONS
3     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
4+------------+----------------+------------+----------------+-------------+
5| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6+------------+----------------+------------+----------------+-------------+
7| th         | p0             |          0 |              0 |           0 |
8| th         | p1             |          0 |              0 |           0 |
9+------------+----------------+------------+----------------+-------------+
102 rows in set (0.00 sec)

각 partition의 TABLE_ROWS는 0입니다. 이제 c1 column 값이 NULL과 0인 두 개의 행을 th에 삽입하고, 다음과 같이 이 행들이 삽입되었는지 확인해 봅니다:

1mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
2Query OK, 1 row affected (0.00 sec)
3
4mysql> SELECT * FROM th;
5+------+---------+
6| c1   | c2      |
7+------+---------+
8| NULL | mothra  |
9+------+---------+
10|    0 | gigan   |
11+------+---------+
122 rows in set (0.01 sec)

임의의 정수 _N_에 대해, NULL MOD N의 값은 항상 NULL임을 상기하십시오. HASH나 KEY로 partitioning된 테이블의 경우, 올바른 partition을 결정할 때 이 결과는 0으로 취급됩니다. Information Schema의 PARTITIONS 테이블을 다시 확인해 보면, 두 행 모두 partition p0에 삽입된 것을 알 수 있습니다:

1mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
2     >   FROM INFORMATION_SCHEMA.PARTITIONS
3     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
4+------------+----------------+------------+----------------+-------------+
5| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6+------------+----------------+------------+----------------+-------------+
7| th         | p0             |          2 |             20 |          20 |
8| th         | p1             |          0 |              0 |           0 |
9+------------+----------------+------------+----------------+-------------+
102 rows in set (0.00 sec)

이 마지막 예제에서 테이블 정의의 PARTITION BY HASH를 PARTITION BY KEY로 바꾸어 반복해 보면, 이 유형의 partitioning에 대해서도 NULL이 0처럼 취급된다는 것을 확인할 수 있습니다.