19.2.7 MySQL Partitioning NULL 처리
MySQL의 파티셔닝은 파티셔닝 식의 값 (컬럼 값 또는 사용자 정의 식의 값에 관계없이)로 NULL 을 거부 수단은 없습니다. 식의 값으로 NULL 을 사용하는 것은 허용되고 있지만 (그렇지 않은 경우는 정수를 반환해야합니다), NULL 은 수치가 아닌 것을 인식하는 것이 중요합니다. MySQL의 파티셔닝 구현은 ORDER BY 와 같이 NULL 이 아닌 값보다 작은 값으로 NULL 을 처리합니다.
이것은 NULL 처리는 각 유형의 파티셔닝 달리이를 준비하지 않으면 예기치 않은 동작이 될 가능성이 있다는 것을 의미합니다. 이러한 상황이 있기 때문에,이 섹션에서는 각 MySQL 파티셔닝 타입이 줄을 어떤 파티션에 저장해야 하는지를 결정할 때 NULL 값을 어떻게 처리 할 것인지를 설명하고 각각의 예 합니다.
RANGE 파티셔닝의 NULL 처리 파티션을 결정하는 데 사용되는 컬럼 값이 NULL 인 행을 RANGE 로 파티션 된 테이블에 삽입 한 경우 행은 가장 낮은 파티션에 삽입됩니다. p 라는 데이터베이스에 다음과 같이 생성 된 2 개의 테이블이 있다고합니다.
mysql>CREATE TABLE t1 (->c1 INT,->c2 VARCHAR(20)->)->PARTITION BY RANGE(c1) (->PARTITION p0 VALUES LESS THAN (0),->PARTITION p1 VALUES LESS THAN (10),->PARTITION p2 VALUES LESS THAN MAXVALUE->);Query OK, 0 rows affected (0.09 sec) mysql>CREATE TABLE t2 (->c1 INT,->c2 VARCHAR(20)->)->PARTITION BY RANGE(c1) (->PARTITION p0 VALUES LESS THAN (-5),->PARTITION p1 VALUES LESS THAN (0),->PARTITION p2 VALUES LESS THAN (10),->PARTITION p3 VALUES LESS THAN MAXVALUE->);Query OK, 0 rows affected (0.09 sec)
이러한 2 개의 CREATE TABLE 문에 의해 생성 된 파티션 내용은 다음의 쿼리를 INFORMATION_SCHEMA 데이터베이스의 PARTITIONS 테이블에 사용하여 확인할 수 있습니다.
mysql>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH>FROM INFORMATION_SCHEMA.PARTITIONS>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';+------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | t1 | p0 | 0 | 0 | 0 | | t1 | p1 | 0 | 0 | 0 | | t1 | p2 | 0 | 0 | 0 | | t2 | p0 | 0 | 0 | 0 | | t2 | p1 | 0 | 0 | 0 | | t2 | p2 | 0 | 0 | 0 | | t2 | p3 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 7 rows in set (0.00 sec)
(이 테이블에 대한 자세한 내용은 섹션 21.13 "INFORMATION_SCHEMA PARTITIONS 테이블" 을 참조하십시오.) 여기서 이러한 각 테이블 파티셔닝 키로 사용되는 컬럼에 NULL 이 포함 된 단일 행을 채 2 하나의 SELECT 문을 사용하여 이러한 행이 삽입 된 것을 확인합니다.
mysql>INSERT INTO t1 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec) mysql>INSERT INTO t2 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec) mysql>SELECT * FROM t1;+------+--------+ | id | name | +------+--------+ | NULL | mothra | +------+--------+ 1 row in set (0.00 sec) mysql>SELECT * FROM t2;+------+--------+ | id | name | +------+--------+ | NULL | mothra | +------+--------+ 1 row in set (0.00 sec)
삽입 된 행을 저장하는 데 어떤 파티션이 사용되었는지에 대해서는 이전 쿼리를 INFORMATION_SCHEMA.PARTITIONS 에 대해 다시 실행하고 출력을 검사하여 확인할 수 있습니다.
mysql>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH>FROM INFORMATION_SCHEMA.PARTITIONS>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';+------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | t1 | p0 | 1 | 20 | 20 | | t1 | p1 | 0 | 0 | 0 | | t1 | p2 | 0 | 0 | 0 | | t2 | p0 | 1 | 20 | 20 | | t2 | p1 | 0 | 0 | 0 | | t2 | p2 | 0 | 0 | 0 | | t2 | p3 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 7 rows in set (0.01 sec)
이러한 행이 각 테이블의 가장 낮은 파티션에 저장된 대해 이러한 파티션을 삭제하고 SELECT 문을 다시 실행하여 확인할 수 있습니다.
mysql>ALTER TABLE t1 DROP PARTITION p0;Query OK, 0 rows affected (0.16 sec) mysql>ALTER TABLE t2 DROP PARTITION p0;Query OK, 0 rows affected (0.16 sec) mysql>SELECT * FROM t1;Empty set (0.00 sec) mysql>SELECT * FROM t2;Empty set (0.00 sec)
( ALTER TABLE ... DROP PARTITION 에 대한 자세한 내용은 섹션 13.1.7 "ALTER TABLE 구문" 을 참조하십시오.)
SQL 함수를 사용하여 분할 식의 경우에도 NULL 이 같이 처리됩니다. 다음과 같은 CREATE TABLE 문을 사용하여 테이블을 정의합니다.
CREATE TABLE tndate (
id INT,
dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
다른 MySQL 함수와 마찬가지로 YEAR(NULL) 은 NULL 을 반환합니다. dt 컬럼 값이 NULL 인 행은 분할 식이 다른 값보다 작은 값으로 평가 된 것처럼 취급 파티션 p0 에 삽입됩니다.
LIST 파티셔닝의 NULL 처리 LIST 로 파티션 된 테이블에 NULL 값이 허용되는 것은 NULL 이 포함 된 값 목록을 사용하여 하나의 파티션이 정의되어있는 경우입니다. 이와는 반대로, LIST 에 의해 분할 된 테이블은 값 목록에서 NULL 을 명시 적으로 사용하지 않는 경우에는 다음 예와 같이 분할 식으로 NULL 값으로 평가되는 행을 거부합니다 합니다.
mysql>CREATE TABLE ts1 (->c1 INT,->c2 VARCHAR(20)->)->PARTITION BY LIST(c1) (->PARTITION p0 VALUES IN (0, 3, 6),->PARTITION p1 VALUES IN (1, 4, 7),->PARTITION p2 VALUES IN (2, 5, 8)->);Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO ts1 VALUES (9, 'mothra');ERROR 1504 (HY000): Table has no partition for value 9 mysql>INSERT INTO ts1 VALUES (NULL, 'mothra');ERROR 1504 (HY000): Table has no partition for value NULL
ts1 에 삽입 할 수있는 것은 c1 값이 0 이상 8 다음 줄뿐입니다. NULL 은 숫자 9 와 같이 범위를 벗어난 있습니다. NULL 이 포함 된 값 목록을 가진 테이블 ts2 및 ts3 는 다음과 같이 만들 수 있습니다.
mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
파티셔닝의 값 목록을 정의 할 때 NULL 을 다른 값과 같이 처리 할 수 있습니다 (이렇게해야한다). 예를 들어, VALUES IN (NULL) 및 VALUES IN (1, 4, 7, NULL) 모두 유효하며 VALUES IN (1, NULL, 4, 7) , VALUES IN (NULL, 1, 4, 7) 등 도 마찬가지입니다. 컬럼 c1 이 NULL 인 행을 테이블 ts2 및 ts3 에 각각 삽입 할 수 있습니다.
mysql>INSERT INTO ts2 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec) mysql>INSERT INTO ts3 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)
INFORMATION_SCHEMA.PARTITIONS 에 대해 적절한 쿼리를 실행하여 방금 삽입 한 행을 저장하기 위해 어떤 파티션이 사용되었는지를 확인할 수 있습니다 (앞의 예와 마찬가지로 분할 된 테이블이 p 데이터베이스 생성 된 것을 상정하고 있습니다).
mysql>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH>FROM INFORMATION_SCHEMA.PARTITIONS>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';+------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | ts2 | p0 | 0 | 0 | 0 | | ts2 | p1 | 0 | 0 | 0 | | ts2 | p2 | 0 | 0 | 0 | | ts2 | p3 | 1 | 20 | 20 | | ts3 | p0 | 0 | 0 | 0 | | ts3 | p1 | 1 | 20 | 20 | | ts3 | p2 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 7 rows in set (0.01 sec)
이 섹션에서 이미 설명했듯이, 행을 저장하는 데 어떤 파티션이 사용되었는지에 대해서는 해당 파티션을 삭제하고 SELECT 를 실행하여 확인할 수 있습니다.
HASH 및 KEY 파티셔닝의 NULL 처리 HASH 또는 KEY 로 파티션 된 테이블의 경우 NULL 처리는 조금 다릅니다. 이러한 경우 NULL 값을 반환 파티셔닝 식은 반환 값이 0 인 것처럼 처리됩니다. 이 동작은 HASH 로 파티션 된 테이블을 작성하고 해당 값이 포함 된 레코드를 삽입하는 것으로, 파일 시스템에 어떤 영향이 있는지를 검사하여 확인할 수 있습니다. 다음 문을 사용하여 작성된 테이블 th (이것도 p 데이터베이스)가 있다고합니다.
mysql>CREATE TABLE th (->c1 INT,->c2 VARCHAR(20)->)->PARTITION BY HASH(c1)->PARTITIONS 2;Query OK, 0 rows affected (0.00 sec)
이 테이블에 속한 파티션은 다음의 쿼리를 사용하여 볼 수 있습니다.
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 0 |
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
각 파티션의 TABLE_ROWS 은 0입니다. 여기에서 다음과 같이 c1 컬럼 값이 NULL 과 0 인 두 행을 th 에 삽입하고 그 행이 삽입 된 것을 확인합니다.
mysql>INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');Query OK, 1 row affected (0.00 sec) mysql>SELECT * FROM th;+------+---------+ | c1 | c2 | +------+---------+ | NULL | mothra | +------+---------+ | 0 | gigan | +------+---------+ 2 rows in set (0.01 sec)
정수 N 대해 NULL MOD 의 값은 항상 NNULL 임을 기억하십시오. HASH 또는 KEY 로 파티션 된 테이블의 경우이 결과는 올바른 파티션을 판별하기 위해 0 으로 처리됩니다. INFORMATION_SCHEMA.PARTITIONS 테이블을 다시 확인하면 두 행이 파티션 p0 에 삽입 된 것을 알 수 있습니다.
mysql>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH>FROM INFORMATION_SCHEMA.PARTITIONS>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';+------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | th | p0 | 2 | 20 | 20 | | th | p1 | 0 | 0 | 0 | +------------+----------------+------------+----------------+-------------+ 2 rows in set (0.00 sec)
테이블의 정의 PARTITION BY HASH 대신 PARTITION BY KEY 를 사용하여이 예를 반복하면이 유형의 파티셔닝도 NULL 이 0처럼 취급되는 것을 쉽게 확인할 수 있습니다.