MySQL分區介紹

不論創建何種類型的分區，如果表中存在主鍵或唯一索引時，分區列必須是唯一索引的一個組成部分
mysql> create table t1(
    -> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2)) partition by hash(col3) partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

mysql> create table t1(
    -> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2,col3)) partition by hash(col3) partitions 4;
Query OK, 0 rows affected (0.49 sec)

mysql> create table t2(
    ->    col1 int null,
    -> col2 date null,
    -> col3 int null,
    -> col4 int null
    -> ) engine=innodb
    -> partition by hash(col3)
    -> partitions 4;
Query OK, 0 rows affected (0.40 sec)
mysql> create table t3(
    ->    col1 int null,
    -> col2 date null,
    -> col3 int null,
    -> col4 int null,
    -> key (col4)
    -> ) engine=innodb
    -> partition by hash(col3)
    -> partitions 4;
Query OK, 0 rows affected (0.23 sec)

--查看數據庫是否支持分區
MariaDB [test]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name                          | Status   | Type               | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
.....
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+

MariaDB [test]> select * from INFORMATION_SCHEMA.plugins where plugin_name='partition'\G
*************************** 1. row ***************************
           PLUGIN_NAME: partition
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 100114.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB
    PLUGIN_DESCRIPTION: Partition Storage Engine Helper
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 1.0
1 row in set (0.00 sec)

--范圍分區
MariaDB [test]> CREATE TABLE members (
    ->     firstname VARCHAR(25) NOT NULL,
    ->     lastname VARCHAR(25) NOT NULL,
    ->     username VARCHAR(16) NOT NULL,
    ->     email VARCHAR(35),
    ->     joined DATE NOT NULL
    -> )
    -> PARTITION BY RANGE COLUMNS(joined) (
    ->     PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    ->     PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    ->     PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    ->     PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    ->     PARTITION p4 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.45 sec)

MariaDB [test]> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT NOT NULL,
    ->     store_id INT NOT NULL
    -> )
    -> PARTITION BY RANGE (store_id) (
    ->     PARTITION p0 VALUES LESS THAN (6),
    ->     PARTITION p1 VALUES LESS THAN (11),
    ->     PARTITION p2 VALUES LESS THAN (16),
    ->     PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.49 sec)

MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(1,'John','Terry',10,100);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(2,'Tom','Carl',10,1);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(2,'Lily','Berg',20,7);
Query OK, 1 row affected (0.03 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(3,'Lucy','Phynix',20,10);
Query OK, 1 row affected (0.13 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(4,'Bill','Jones',20,15);
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(5,'Jill','Deco',30,12);
Query OK, 1 row affected (0.08 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(6,'Emily','Aaron',30,20);
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select * from employees;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname  | hired      | separated  | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
|  2 | Tom   | Carl   | 1970-01-01 | 9999-12-31 |       10 |        1 |
|  2 | Lily  | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |
|  3 | Lucy  | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |
|  4 | Bill  | Jones  | 1970-01-01 | 9999-12-31 |       20 |       15 |
|  5 | Jill  | Deco   | 1970-01-01 | 9999-12-31 |       30 |       12 |
|  1 | John  | Terry  | 1970-01-01 | 9999-12-31 |       10 |      100 |
|  6 | Emily | Aaron  | 1970-01-01 | 9999-12-31 |       30 |       20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)

MariaDB [test]> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

按照年進行分區
mysql> create table sales(
    -> money int unsigned not null,
    -> date datetime
    -> ) engine=innodb
    -> partition by range (year(date)) (
    -> partition p2008 values less than (2009),
    -> partition p2009 values less than (2010),
    -> partition p2010 values less than (2011)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into sales values (100, '2008-01-01'),(100, '2008-02-01'),(200, '2008-01-02'), (100, '2009-03-01'), (200, '2010-03-01');
Query OK, 5 rows affected (0.13 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table sales drop partition p2008;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain partitions
    -> select * from sales
    -> where date>='2009-01-01' and date<='2009-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
   partitions: p2009
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

優化器只能對YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()這類函數進行優化選擇
下面這個例子中的分區創建有問題，在分區掃描的時候會掃描多個分區
按照每年每月來進行分區
mysql> create table sales2(
    -> money int unsigned not null,
    -> date datetime
    -> ) engine=innodb
    -> partition by range (year(date)*100+month(date)) (
    -> partition p201001 values less than (201002),
    -> partition p201002 values less than (201003),
    -> partition p201003 values less than (201004)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> explain partitions select * from sales2 where date>='2010-01-01' and date <= '2010-01-31';
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | partitions              | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales2 | p201001,p201002,p201003 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

下面例子為上面例子的正確創建方法
mysql> create table sales1(
    -> money int unsigned not null,
    -> date datetime) engine=innodb
    -> partition by range(to_days(date)) (
    -> partition p201001
    -> values less than(to_days('2010-02-01')),
    -> partition p201002
    -> values less than(to_days('2010-03-01')),
    -> partition p201003
    -> values less than (to_days('2010-04-01'))
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> explain partitions select * from sales1 where date>='2010-01-01' and date<='2010-01-31';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales1 | p201001    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> create table t(
    -> id int
    -> ) engine=innodb
    -> partition by range (id) (
    -> partition p0 values less than (10),
    -> partition p1 values less than (20));
Query OK, 0 rows affected (0.55 sec)

mysql> system ls -lrt /var/lib/mysql/test
-rw-rw----. 1 mysql mysql     8556 Nov  3 14:22 t.frm
-rw-rw----. 1 mysql mysql       28 Nov  3 14:22 t.par
-rw-rw----. 1 mysql mysql    98304 Nov  3 14:22 t#P#p0.ibd
-rw-rw----. 1 mysql mysql    98304 Nov  3 14:22 t#P#p1.ibd

mysql> select * from information_schema.partitions
    -> where table_schema=database() and table_name='t'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: t
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 10
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-11-03 14:22:00
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: t
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 20
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-11-03 14:22:00
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)

mysql> insert into t values(50);
ERROR 1526 (HY000): Table has no partition for value 50
mysql> alter table t
    -> add partition(
    -> partition p2 values less than maxvalue );
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values(50);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

--LIST分區
MariaDB [test]> CREATE TABLE employees5 (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT,
    ->     store_id INT
    -> )
    -> PARTITION BY LIST(store_id) (
    ->     PARTITION pNorth VALUES IN (3,5,6,9,17),
    ->     PARTITION pEast VALUES IN (1,2,10,11,19,20),
    ->     PARTITION pWest VALUES IN (4,12,13,14,18),
    ->     PARTITION pCentral VALUES IN (7,8,15,16)
    -> );
Query OK, 0 rows affected (5.13 sec)

--COLUMN分區
字段分區是范圍分區和列表分區的一種變體，字段分區可以使用多個字段作為分區鍵。
范圍字段分區和列表字段分區支持非整數字段，支持的數據類型如下：

所有整數類型：TINYINT, SMALLINT, MEDIUMINT, INT，BIGINT。
DATE，DATETIME。
CHAR, VARCHAR, BINARY,VARBINARY。

MariaDB [test]> CREATE TABLE rc2 (
    ->     a INT,
    ->     b INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b) (
    ->     PARTITION p0 VALUES LESS THAN (0,10),
    ->     PARTITION p1 VALUES LESS THAN (10,20),
    ->     PARTITION p2 VALUES LESS THAN (10,30),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
    ->  );
Query OK, 0 rows affected (0.27 sec)

mysql> create table t_columns_range(
    -> a int,
    -> b datetime
    -> )engine=innodb
    -> partition by range columns (b) (
    -> partition p0 values less than ('2009-01-01'),
    -> partition p1 values less than ('2010-01-01')
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> create table customers_1 (
    -> first_name varchar(25),
    -> last_name varchar(25),
    -> street_1 varchar(30),
    -> street_2 varchar(30),
    -> city varchar(15),
    -> renewal date
    -> )
    -> partition by list columns(city) (
    -> partition pRegion_1
    -> values in ('Oskarshamn', 'Hogsby', 'Monsters'),
    -> partition pRegion_2
    -> values in ('Vimmerby', 'Hultsfred', 'Vastervik'),
    -> partition pRegion_3
    -> values in ('Nassjo', 'Eksjo', 'Vetlanda'),
    -> partition pRegion_4
    -> values in ('Uppvidinge', 'Alvesta', 'Vaxjo')
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> create table rcx(
    -> a int,
    -> b int,
    -> c char(3),
    -> d int
    -> )engine=innodb
    -> partition by range columns(a,d,c) (
    -> partition p0 values less than (5,10,'ggg'),
    -> partition p1 values less than (10,20,'mmmm'),
    -> partition p2 values less than (15,30,'sss'),
    -> partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.32 sec)

--哈希分區
哈希分區主要確保分區表中的數據均勻分布在各個分區之中。
mysql> create table t_hash(a int,b datetime)engine=innodb
    -> partition by hash(year(b))
    -> partitions 4;
Query OK, 0 rows affected (7.81 sec)

MariaDB [test]> CREATE TABLE employees7 (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT,
    ->     store_id INT
    -> )
    -> PARTITION BY HASH(store_id)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.22 sec)

MySQL數據庫還支持一種稱為LINEAR HASH的分區，它使用一個更加復雜的算法來確定新行插入到已經分區的表中的位置
LINEAR HASH分區的優點在于增加、刪除、合并和拆分分區將變得更加快捷，這有利于處理含有大量數據的表。LINEAR HASH分區的缺點在于，
與使用HASH分區得到的數據分布相比，各個分區間數據的分布可能不大均衡
mysql> create table t_linear_hash(
    -> a int,
    -> b datetime
    -> )engine=innodb
    -> partition by linear hash(year(b))
    -> partitions 4;
Query OK, 0 rows affected (0.23 sec)

--KEY分區
KEY分區類似哈希分區，除了哈希分區使用用戶自定義的表達式。分區鍵列必須包含部分或所有的表的主鍵。
MariaDB [test]> CREATE TABLE k1 (
    ->     id INT NOT NULL,
    ->     name VARCHAR(20),
    ->     UNIQUE KEY (id)
    -> )
    -> PARTITION BY KEY()
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.11 sec)

--復合分區
MySQL數據庫允許在RANGE和LIST的分區上再進行HASH或KEY的子分區
MariaDB [test]> 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.51 sec)

mysql> create table ts(a int,b date) engine=innodb
    -> partition by range(year(b))
    -> subpartition by hash(to_days(b))
    -> subpartitions 2 (
    -> partition p0 values less than (1990),
    -> partition p1 values less than (2000),
    -> partition p2 values less than MAXVALUE
    -> );
Query OK, 0 rows affected (0.24 sec)
mysql> system ls -lh /var/lib/mysql/test/ts*
-rw-rw----. 1 mysql mysql 8.4K Nov  4 15:44 /var/lib/mysql/test/ts.frm
-rw-rw----. 1 mysql mysql   96 Nov  4 15:44 /var/lib/mysql/test/ts.par
-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts#P#p0#SP#p0sp0.ibd
-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts#P#p0#SP#p0sp1.ibd
-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts#P#p1#SP#p1sp0.ibd
-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts#P#p1#SP#p1sp1.ibd
-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts#P#p2#SP#p2sp0.ibd
-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts#P#p2#SP#p2sp1.ibd

mysql> create table ts (a int, b date)
    -> partition by range (year(b))
    -> subpartition by hash( to_days(b)) (
    -> 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.15 sec)

mysql> create table ts (a int,b date) engine=innodb
    -> partition by range(year(b))
    -> subpartition by hash(to_days(b)) (
    -> partition p0 values less than (2000) (
    -> 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 (2010) (
    -> 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, 6 warnings (0.32 sec)

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
+---------+------+----------------------------------+
6 rows in set (0.00 sec)

--查看分區
MariaDB [test]> select * from INFORMATION_SCHEMA.PARTITIONS where table_name = 'employees'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: employees
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: store_id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 6
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-07-04 00:42:16
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: employees
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: store_id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 11
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-07-04 00:42:16
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: employees
               PARTITION_NAME: p2
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: store_id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 16
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-07-04 00:42:16
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 4. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: employees
               PARTITION_NAME: p3
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: store_id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: MAXVALUE
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-07-04 00:42:16
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
4 rows in set (0.00 sec)

--查看分區表執行計劃
MariaDB [test]> explain partitions select * from employees;
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table     | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | employees | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

MariaDB [test]> explain partitions select * from employees where store_id < 5;
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | employees | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

--增加分區

MariaDB [test]> alter table employees add partition (partition p3 values less than (20));
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> alter table employees add partition (partition p5 values less than maxvalue);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

--TRUNCATE指定分區
MariaDB [test]> alter table employees truncate partition p0;
Query OK, 0 rows affected (0.12 sec)

--刪除指定分區
MariaDB [test]> alter table employees drop partition p0;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

--將一個分區拆分成多個分區
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p1             | store_id             | 11                    |          2 |
| p2             | store_id             | 16                    |          2 |
| p3             | store_id             | 20                    |          0 |
| p5             | store_id             | MAXVALUE              |          0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)

MariaDB [test]> ALTER TABLE employees
    ->     REORGANIZE PARTITION p1 INTO (
    ->         PARTITION n0 VALUES LESS THAN (5),
    ->         PARTITION n1 VALUES LESS THAN (11)
    -> );
Query OK, 2 rows affected (0.49 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| n0             | store_id             | 5                     |          0 |
| n1             | store_id             | 11                    |          2 |
| p2             | store_id             | 16                    |          2 |
| p3             | store_id             | 20                    |          0 |
| p5             | store_id             | MAXVALUE              |          0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.06 sec)

--將多個分區合并成一個分區
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| n0             | store_id             | 5                     |          0 |
| n1             | store_id             | 11                    |          2 |
| p2             | store_id             | 16                    |          2 |
| p3             | store_id             | 20                    |          0 |
| p5             | store_id             | MAXVALUE              |          0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)

MariaDB [test]> ALTER TABLE employees
    -> REORGANIZE PARTITION n0,n1,p2 INTO (
    -> PARTITION p2 VALUES LESS THAN (16));
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p2             | store_id             | 16                    |          4 |
| p3             | store_id             | 20                    |          0 |
| p5             | store_id             | MAXVALUE              |          0 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.03 sec)

--減少哈希分區的數量
MariaDB [test]> create table emp2(id int not null,ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null)
    -> partition by hash(store_id) partitions 4;
Query OK, 0 rows affected (0.60 sec)

MariaDB [test]> alter table emp2 coalesce partition 2;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table emp2\G
*************************** 1. row ***************************
       Table: emp2
Create Table: CREATE TABLE `emp2` (
  `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
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 2 */
1 row in set (0.00 sec)

增加哈希分區的數量
MariaDB [test]> alter table emp2 add partition partitions 5;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table emp2\G
*************************** 1. row ***************************
       Table: emp2
Create Table: CREATE TABLE `emp2` (
  `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
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 7 */
1 row in set (0.00 sec)

在表和分區間交換數據
mysql> create table e (
    -> id int not null,
    -> fname varchar(30),
    -> lname varchar(30)
    -> )
    -> partition by range(id) (
    -> partition p0 values less than (50),
    -> partition p1 values less than (100),
    -> partition p2 values less than (150),
    -> partition p3 values less than (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> insert into e values (1669,"Jim","Smith"),(337,"Mary","Jones"),(16,"Frank","White"),(2005,"Linda","Black");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

創建交換表
mysql> create table e2 like e;
Query OK, 0 rows affected (0.29 sec)

mysql> show create table e2\G
*************************** 1. row ***************************
       Table: e2
Create Table: CREATE TABLE `e2` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

將分區表改成普通表
mysql> alter table e2 remove partitioning;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table e2\G
*************************** 1. row ***************************
       Table: e2
Create Table: CREATE TABLE `e2` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

將e表的分區p0中的數據移動到表e2中，p0分區中的數據被移到表e2中
mysql> alter table e exchange partition p0 with table e2;
Query OK, 0 rows affected (0.17 sec)

mysql> select partition_name,table_rows from information_schema.partitions where table_name='e';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          2 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

--查詢指定分區
MariaDB [test]> select * from employees partition(p1);
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname  | hired      | separated  | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
|  2 | Lily  | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |
|  3 | Lucy  | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |
+----+-------+--------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

--將非分區表轉換成分區表
MariaDB [test]> CREATE TABLE employees2 (
    ->          id INT NOT NULL,
    ->          fname VARCHAR(30),
    ->          lname VARCHAR(30),
    ->          hired DATE NOT NULL DEFAULT '1970-01-01',
    ->          separated DATE NOT NULL DEFAULT '9999-12-31',
    ->          job_code INT NOT NULL,
    ->          store_id INT NOT NULL
    ->      );
Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(1,'John','Terry',10,100);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(2,'Tom','Carl',10,1);
Query OK, 1 row affected (0.03 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(2,'Lily','Berg',20,7);
Query OK, 1 row affected (0.04 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(3,'Lucy','Phynix',20,10);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(4,'Bill','Jones',20,15);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(5,'Jill','Deco',30,12);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(6,'Emily','Aaron',30,20);
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select * from employees2;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname  | hired      | separated  | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
|  1 | John  | Terry  | 1970-01-01 | 9999-12-31 |       10 |      100 |
|  2 | Tom   | Carl   | 1970-01-01 | 9999-12-31 |       10 |        1 |
|  2 | Lily  | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |
|  3 | Lucy  | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |
|  4 | Bill  | Jones  | 1970-01-01 | 9999-12-31 |       20 |       15 |
|  5 | Jill  | Deco   | 1970-01-01 | 9999-12-31 |       30 |       12 |
|  6 | Emily | Aaron  | 1970-01-01 | 9999-12-31 |       30 |       20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)

MariaDB [test]> alter table employees2
    ->      PARTITION BY RANGE (store_id) (
    ->          PARTITION p0 VALUES LESS THAN (6),
    ->          PARTITION p1 VALUES LESS THAN (11),
    ->          PARTITION p2 VALUES LESS THAN (16),
    ->          PARTITION p3 VALUES LESS THAN MAXVALUE
    ->      );
Query OK, 7 rows affected (0.59 sec)               
Records: 7  Duplicates: 0  Warnings: 0

--測試NULL值在分區中的存儲
RANGE分區中，NULL值會被當作最小值來處理；LIST分區中，NULL值必須出現在枚舉列表中；HASH/KEY分區中，NULL值會被當作零值來處理

MariaDB [test]> create table tb_range(id int,name varchar(5))
    -> partition by range(id)
    -> (
    -> partition p0 values less than(-6),
    -> partition p1 values less than(0),
    -> partition p2 values less than(1),
    -> partition p3 values less than maxvalue
    -> );
Query OK, 0 rows affected (0.69 sec)

MariaDB [test]> insert into tb_range values(null,'null');
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_range';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | id                   | -6                    |          1 |
| p1             | id                   | 0                     |          0 |
| p2             | id                   | 1                     |          0 |
| p3             | id                   | MAXVALUE              |          0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)

MariaDB [test]> create table tb_list(id int,name varchar(5))
    -> partition by list(id)
    -> (
    -> partition p1 values in (0),
    -> partition p2 values in (1)
    -> );
Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> insert into tb_list values(null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL

MariaDB [test]> insert into tb_list values(null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL
MariaDB [test]> create table tb_hash(id int,name varchar(5))
    -> partition by hash(id)
    -> partitions 2;
Query OK, 0 rows affected (0.13 sec)

MariaDB [test]> insert into tb_hash values(null, 'null');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_hash';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | id                   | NULL                  |          1 |
| p1             | id                   | NULL                  |          0 |
+----------------+----------------------+-----------------------+------------+
2 rows in set (0.00 sec)