MySQL 파티션 테이블 파티션에서 파티션 필드를 온라인으로 수정한 후 파티션 (2) --> 하위 파티션 및 Null 값 입력 처리에 대해 자세히 알아보세요.
- 黄舟원래의
- 2017-02-17 11:58:182130검색
-- MySQL分区, 子分区以及对录入Null值的处理情况. 看完官方文档做的笔记.
-- 키 파티셔닝
키에 의한 파티셔닝은 해시에 의한 파티셔닝과 유사합니다. 단, 해시 파티셔닝에서는 사용자 정의 파티션을 사용합니다. 표현식에서 키 분할을 위한 해싱 함수는 MySQL 서버에서 제공됩니다. 이 내부 해싱 함수는 다음을 기반으로 합니다.
PASSWORD()와 동일한 알고리즘입니다.
HASH 대신 KEY를 사용합니다.
KEY는 하나 이상의 열 이름 목록만 사용합니다. 분할 키로 사용되는 하나 이상의 열은 테이블에 기본 키가 있는 경우 테이블의 기본 키의 일부 또는 전부를 구성해야 합니다.
KEY는 0개 이상의 열 이름 목록을 사용합니다. 분할 키로 열 이름이 지정되지 않은 경우 테이블의 기본 키(있는 경우)가 사용됩니다. 예를 들어 다음 CREATE TABLE 문은 MySQL 5.5에서 유효합니다.
mysql> CREATE TABLE k1 ( -> id INT NOT NULL PRIMARY KEY, -> name VARCHAR(20) -> ) -> PARTITION BY KEY() -> PARTITIONS 2; Query OK, 0 rows affected (0.06 sec) If there is no primary key but there is a unique key, then the unique key is used for the partitioning key: mysql> CREATE TABLE k2 ( -> id INT NOT NULL, -> name VARCHAR(20), -> UNIQUE KEY (id) -> ) -> PARTITION BY KEY() -> PARTITIONS 2; Query OK, 0 rows affected (0.02 sec)
그러나 고유 키 열이 NOT NULL로 정의되지 않은 경우 이전 문이 실패합니다.
이 두 경우 모두 SHOW CREATE TABLE의 출력이나 INFORMATION_SCHEMA.PARTITIONS의 PARTITION_EXPRESSION 열에 표시되지 않더라도 분할 키는 id 열입니다. table.
아래와 같습니다.
mysql> SELECT t.TABLE_NAME, t.PARTITION_NAME,t.TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS t WHERE table_name='k2'; +------------+----------------+------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | +------------+----------------+------------+ | k2 | p0 | 3 | | k2 | p1 | 4 | +------------+----------------+------------+ 2 rows in set (0.01 sec)
다른 파티셔닝 유형과 달리 KEY로 파티셔닝에 사용되는 컬럼은 정수 또는 NULL 값으로 제한되지 않습니다.
예를 들어 다음 CREATE TABLE 문이 유효합니다:
没有primary key，没有재정义时候指定分区字段，会抱错：
mysql> CREATE TABLE tm3 (
-> s1 CHAR(32)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 10;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
在定义中加入分区字段，add the column in define , it is ok
mysql> CREATE TABLE tm3 (
-> s1 CHAR(32)
-> )
-> PARTITION BY KEY(s1)
-> PARTITIONS 10;
Query OK, 0 rows affected (0.07 sec)
mysql>
子分区 하위 분할
복합 분할이라고도 알려진 하위 분할은 분할된 테이블의 각 파티션에 대한 추가 버전입니다.
예를 들어 다음 CREATE TABLE 문을 고려하세요.
mysql> CREATE TABLE ts (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0,
-> SUBPARTITION s1
-> ),
-> PARTITION p1 VALUES LESS THAN (2000) (
-> SUBPARTITION s2,
-> SUBPARTITION s3
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s4,
-> SUBPARTITION s5
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
CREATE TABLE ts3 (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
);
(1) 각 파티션에는 동일한 수의 하위 파티션이 있어야 합니다. 그렇지 않은 경우 실패
mysql> CREATE TABLE ts3 (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0,
-> SUBPARTITION s1
-> ),
-> PARTITION p1 VALUES LESS THAN (2000),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s2,
-> SUBPARTITION s3
-> )
-> );
ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
' at line 8
mysql>
(2) 각 SUBPARTITION 절에는 최소한 하위 파티션의 이름이 포함되어야 합니다.
그렇지 않으면 하위 파티션에 대해 원하는 옵션을 설정하거나 해당 옵션에 대해 기본 설정을 사용하도록 허용할 수 있습니다.
(3) 하위 파티션 이름은 전체 파티션에서 고유해야 합니다. table.
(4) 특히 큰 테이블과 함께 하위 파티션을 사용하여 여러 디스크에 데이터와 인덱스를 배포할 수 있습니다. 6개의 디스크가 /disk0으로 마운트되어 있다고 가정합니다. , /disk1, /disk2 등. 이제 다음 예를 고려해 보십시오.
mysql> CREATE TABLE ts5 (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0
-> DATA DIRECTORY = '/disk0/data'
-> INDEX DIRECTORY = '/disk0/idx',
-> SUBPARTITION s1
-> DATA DIRECTORY = '/disk1/data'
-> INDEX DIRECTORY = '/disk1/idx'
-> ),
-> PARTITION p1 VALUES LESS THAN (2000) (
-> SUBPARTITION s2
-> DATA DIRECTORY = '/disk2/data'
-> INDEX DIRECTORY = '/disk2/idx',
-> SUBPARTITION s3
-> DATA DIRECTORY = '/disk3/data'
-> INDEX DIRECTORY = '/disk3/idx'
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s4
-> DATA DIRECTORY = '/disk4/data'
-> INDEX DIRECTORY = '/disk4/idx',
-> SUBPARTITION s5
-> DATA DIRECTORY = '/disk5/data'
-> INDEX DIRECTORY = '/disk5/idx'
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
In this case, a separate disk is used for the data and for the indexes of each RANGE. Many other variations are possible;another example might be:
mysql> CREATE TABLE ts6 (id INT, purchased DATE)
-> PARTITION BY RANGE(YEAR(purchased))
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0a
-> DATA DIRECTORY = '/disk0'
-> INDEX DIRECTORY = '/disk1',
-> SUBPARTITION s0b
-> DATA DIRECTORY = '/disk2'
-> INDEX DIRECTORY = '/disk3'
-> ),
-> PARTITION p1 VALUES LESS THAN (2000) (
-> SUBPARTITION s1a
-> DATA DIRECTORY = '/disk4/data'
-> INDEX DIRECTORY = '/disk4/idx',
-> SUBPARTITION s1b
-> DATA DIRECTORY = '/disk5/data'
-> INDEX DIRECTORY = '/disk5/idx'
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s2a,
-> SUBPARTITION s2b
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
미래에 2000년부터 10년 동안 구매 횟수가 기본 위치에서 더 이상 충분하지 않을 정도로 증가할 때 공간이 있는 경우 ALTER TABLE ... REORGANIZE PARTITION 문을 사용하여 해당 행을 이동할 수 있습니다. 이 작업을 수행하는 방법에 대한 설명은 섹션 17.3, “파티션 관리”를 참조하십시오.
NO_DIR_IN_CREATE 서버 SQL 모드가 실행 중인 경우 파티션 정의에서 DATA DIRECTORY 및 INDEX DIRECTORY 옵션이 허용되지 않습니다. MySQL 5.5.5부터 하위 파티션을 정의할 때 이러한 옵션도 허용되지 않습니다(Bug#42954).
MySQL 파티셔닝이 NULL을 처리하는 방법
MySQL의 파티셔닝 열 값이든 사용자 제공 표현식의 값이든 관계없이 분할 표현식의 값으로 NULL을 허용하지 않습니다.
정수를 산출해야 하는 표현식의 값으로 NULL을 사용하는 것이 허용되더라도 NULL은 숫자가 아니라는 점을 명심하는 것이 중요합니다. MySQL의 파티셔닝
구현에서는 ORDER BY와 마찬가지로 NULL을 NULL이 아닌 값보다 작은 값으로 처리합니다.
이는 NULL 처리가 다양한 유형의 파티셔닝에 따라 다르며 사용자가 원하는 동작을 생성할 수 있음을 의미합니다. 준비가 되어 있지 않으면 기대하지 마세요.
이런 경우, 이 섹션에서는 행이 저장되어야 하는 파티션을 결정할 때 각 MySQL 파티셔닝 유형이 NULL 값을 처리하는 방법을 논의하고
예를 제공합니다.
RANGE 파티셔닝을 사용하여 NULL을 처리합니다. RANGE로 파티션을 나눈 테이블에 파티션을 결정하는 데 사용된 열 값이 NULL이 되도록 행을 삽입하면
해당 행이 가장 낮은 파티션에 삽입됩니다. 예를 들어 p라는 데이터베이스에서 다음과 같이 생성된 두 테이블을 생각해 보세요.
(1) Rang Partition,OK
파티션을 볼 수 있습니다. INFORMATION_SCHEMA 데이터베이스의 PARTITIONS 테이블에 대해 다음 쿼리를 사용하여 두 개의 CREATE TABLE 문으로 생성되었습니다.
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 16384 |
| t1 | p1 | 0 | 0 | 16384 |
| t1 | p2 | 0 | 0 | 16384 |
| t2 | p0 | 0 | 0 | 16384 |
| t2 | p1 | 0 | 0 | 16384 |
| t2 | p2 | 0 | 0 | 16384 |
| t2 | p3 | 0 | 0 | 16384 |
| ts | p0 | 0 | 0 | 16384 |
| ts | p0 | 0 | 0 | 16384 |
| ts | p1 | 0 | 0 | 16384 |
| ts | p1 | 0 | 0 | 16384 |
| ts | p2 | 0 | 0 | 16384 |
| ts | p2 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
14 rows in set (0.00 sec)
이제 이러한 각 테이블을 다음을 포함하는 단일 행으로 채우겠습니다. 분할 키로 사용된 열에 NULL이 있고
한 쌍의 SELECT 문을 사용하여 행이 삽입되었는지 확인합니다.
You can see which partitions are used to store the inserted rows by rerunning the previous query against INFORMATION_SCHEMA.PARTITIONS and inspecting the output:
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; +------+--------+ | c1 | c2 | +------+--------+ | NULL | mothra | +------+--------+ 1 row in set (0.01 sec) mysql> SELECT * FROM t2; +------+--------+ | c1 | c2 | +------+--------+ | NULL | mothra | +------+--------+ 1 row in set (0.00 sec) mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_'; +------------+----------------+------------+----------------+-------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +------------+----------------+------------+----------------+-------------+ | t1 | p0 | 1 | 16384 | 16384 | | t1 | p1 | 0 | 0 | 16384 | | t1 | p2 | 0 | 0 | 16384 | | t2 | p0 | 1 | 16384 | 16384 | | t2 | p1 | 0 | 0 | 16384 | | t2 | p2 | 0 | 0 | 16384 | | t2 | p3 | 0 | 0 | 16384 | | ts | p0 | 0 | 0 | 16384 | | ts | p0 | 0 | 0 | 16384 | | ts | p1 | 0 | 0 | 16384 | | ts | p1 | 0 | 0 | 16384 | | ts | p2 | 0 | 0 | 16384 | | ts | p2 | 0 | 0 | 16384 | +------------+----------------+------------+----------------+-------------+ 13 rows in set (0.00 sec) You can also demonstrate that these rows were stored in the lowest partition of each table by dropping these partitions,
and then re-running the SELECT statements:
<br/>
(2) Handling of NULL with LIST partitioning. 必须将null在定义中加入才能录入null的分区数据
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)否则insert null的分区数据会抱错: ERROR 1504 (HY000): Table has no partition for value NULL
(3) Handling of NULL with HASH and KEY partitioning.
mysql> CREATE TABLE th (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY HASH(c1)
-> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
There is no data record in beginnig.
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM th;
+------+--------+
| c1 | c2 |
+------+--------+
| NULL | mothra |
| 0 | gigan |
+------+--------+
2 rows in set (0.00 sec)
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 2 | 8192 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)Recall that for any integer N, the value of NULL MOD N is always NULL. For tables that are partitioned by HASH or KEY, this result is treated for determining the correct partition as 0. Checking the INFORMATION_SCHEMA.PARTITIONS table once again, we can see that both rows were inserted into partition p0:
MySQL对分区中null值得处理, rang,key,以及hash中,都是直接放入min的分区中. list分区中则是放入事先定义好的包含null的分区中,如果list分区事先没有定义包含null值的分区,那么录入的时候会抱错
以上就是MySQL 分区表 partition线上修改分区字段，后续进一步学习partition (2) --> 子分区以及对录入Null值的处理情况.的内容，更多相关内容请关注PHP中文网（www.php.cn）！