MySQL5.6.17分区表(二)

    示例代码：
    RANGE - HASH复合分区
        略
    RANGE- KEY复合分区
        略
    LIST - HASH复合分区
        略
    LIST - KEY 复合分区

        略

MySQL分区如何处理NULL

    http://dev.mysql.com/doc/refman/5.6/en/partitioning-handling-nulls.html

    MySQL不禁止在分区键上使用NULL，不论它是一个字段值或者一个用户定义的表达式。一般情况下，MySQL分区实现中认为NULL小于任何non-NULL值，类似于ORDER BY语句。

    注意：RANGE分区中，NULL值会被当作最小值来处理；LIST分区中，NULL值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，NULL值会被当作零值来处理。

    Handling of NULL with RANGE partitioning

        RANGE分区中NULL值将插入到最小的分区中。

mysql> CREATE TABLE t1_range_part_with_null (
         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_range_part_with_null (
         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)

-- 查看INFORMATION_SCHEMA数据库中的分区表定义PARTITIONS
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
 >   FROM INFORMATION_SCHEMA.PARTITIONS
 >   WHERE TABLE_SCHEMA = ‘big_data‘ AND TABLE_NAME LIKE ‘%_range_part_with_null‘;

-- 插入带有NULL值的数据
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

-- 查看添加的数据数据位于哪个分区：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
 >   FROM INFORMATION_SCHEMA.PARTITIONS
 >   WHERE TABLE_SCHEMA = ‘big_data‘ AND TABLE_NAME LIKE ‘%_range_part_with_null‘;

-- 同样可以通过删除最小分区的形式验证数据存储于哪个分区：
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)

-- NULL同样可以用于使用了SQL函数的分区表达式：
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
);
-- 可以插入数据验证添加到了哪个分区

    Handling of NULL with LIST partitioning

        LIST分区中必须明确指定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

-- 创建包含NULL的List分区：
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)

-- 再次添加包含空值的数据
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)

-- 查看数据添加到了哪个分区
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_‘;

-- 同样可以通过删除分区后查询的方式验证数据添加到了哪个分区。

    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)

-- 查询分区情况：
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 |
+------------+----------------+------------+----------------+-------------+

-- 能看到每个分区的TABLE_ROWS均为0，插入两条测试数据，验证数据存储于哪个分区。

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)

-- 由于NULL对N取MOD同样得到的值为0，HASH分区或者KEY分区均将NULL值作为0存储到相应分区。

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 KEY而非PARTITION BY HASH，
-- 同样可以发现在该分区类型NULL被作为0值处理。

分区管理
    对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。
    1、从RANGE或List分区表中删除一个分区

ALTER TABLE table_name DROP PARTITION partition_name;

        注意：删除分区的命令执行之后，并不显示实际从表中删除的行数，并不是真的没有记录被删除

-- 如：
ALTER TABLE tr DROP PARTITION p2;

    2、为RANGE或List分区表增加分区

ALTER TABLE table_name ADD PARTITION partition_define;
-- 如：
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12));

    3、RANGE或List分区表重新定义分区
        MySQL也提供了在不丢失数据的情况下，通过重新定义分区的语句ALTER TABLE REORGANIZE PARTITION INTO重定义分区。
        语法如下：       

CREATE TABLE emp_date_range_part (  
  `id` int(11) NOT NULL,  
  `ename` varchar(30) DEFAULT NULL,  
  `hired` date NOT NULL DEFAULT ‘1970-01-01‘,  
  `separated` date NOT NULL DEFAULT ‘9999-12-31‘,  
  `job` varchar(30) NOT NULL,  
  `store_id` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘重定义分区测试‘ 
PARTITION BY RANGE (YEAR(separated))  
(
    PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,  
    PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,  
    PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,  
    PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB
)  
-- 重定义分区：
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

      注意：重新定义RANGE分区时，只能够重新定义相邻的分区，不能跳过某个RANGE分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型，例如，不能把RANGE分区变为HASH分区，也不能把HASH分区变成RANGE分区。
        合并分区：

alter table tt reorganize partition p4,p6 into (partition p4 values in (6,11));

        注意：只能合并相邻的分区。
    4、管理Hash分区和Key分区

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

-- 合并分区(从12到4个)
ALTER TABLE clients COALESCE PARTITION 4;

-- COALESCE不能用来增加分区的数量，否则会出现以下错误：
mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

-- 但可以通过如下语句增加分区
ALTER TABLE clients ADD PARTITION PARTITIONS 6;

 
    5、交换分区及子分区
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
    
    6、分区维护
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-maintenance.html
    
    7、查看分区信息
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-info.html
    
分区裁剪
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-pruning.html

分区选择
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
    
分区表的限制及局限性
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html
    Partitioning Keys, Primary Keys, and Unique Keys
    若分区表有unique key约束，那么分区表达式中必须包括unique key约束列的一部分。

本文出自 “鲁春利的工作笔记” 博客，请务必保留此出处http://luchunli.blog.51cto.com/2368057/1688020

MySQL5.6.17分区表(二)

标签：mysql5.6.17   partitioning