MySQL 分區表 partition線上修改分區字段，後續進一步學習partition (2) --> 子分區以及對錄入Null值的處理情況.
- 黄舟原創
- 2017-02-17 11:58:182130瀏覽
-- MySQL分割區、子分割區以及對輸入Null值的處理情形。觀看官方文件做的筆記.
-- KEY 分區
按鍵分區與按哈希分區類似，不同之處在於哈希分區採用用戶定義的表達式，用於鍵分區的哈希函數由MySQL伺服器.此內部哈希函數基於
與 PASSWORD() 相同的演算法。
使用KEY而不是HASH。
KEY 僅接受一個或多個列名稱的清單。用作分區鍵的一列或多列必須包含表格的部分或全部主鍵（如果表有主鍵）。
KEY 接受零個或多個列名的清單。如果未指定列名作為分區鍵，則使用表的主鍵（如果有）。例如，以下 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，則前面的語句將失敗。
在這兩種情況下，分區鍵是 id 列，即使它沒有顯示在 SHOW CREATE TABLE 的輸出中或 INFORMATION_SCHEMA.PARTITIONS 表的 PARTITION_EXPRESSION 列中。
如下：
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 語句是有效的：
主鍵，沒有在定義時指定分區字段，會抱錯：
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>
子分區子分區
沒有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
)
);
子分區）是分區表中每個分區的進一步劃分。
例如，考慮以下 CREATE TABLE 語句： 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> (1) 每個分區必須具有相同數量的子分區。如果沒有，失敗
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;
(2) 每個 SUBPARTITION 子句必須（至少）包含子分區的名稱。
否則，您可以為子分割區設定任何所需的選項或允許它假設該選項的預設設定。
(3) 子分區名稱在整個表中必須是唯一的。
(4) 子分割區可以與特別大的表一起使用，以便在多個磁碟上分佈資料和索引。
假設您有 6 個磁碟安裝為 /disk0、/disk1、/disk2 等。現在考慮以下範例：
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)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)
將來，當從 2000 年開始的十年內的購買數量增長到預設位置不再提供足夠空間時，可以使用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的分割區 實作將 NULL 視為小於任何非 NULL 值，就像 ORDER BY 一樣。
既然如此，我們在本節中討論每種MySQL分割區類型在決定應儲存行的分割區時如何處理NULL 值，
並為每個分割區提供範例。
使用 RANGE 處理 NULL分區。 如果將一行插入按 RANGE 分區的表中，使得用於確定分區的列值為 NULL，則該行將插入到最低分區。例如，考慮名為p 的資料庫中的這兩個表，建立如下：
(1) Rang Partition,OK
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
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）！