MySQL分區(qū)介紹

不論創(chuàng)建何種類型的分區(qū)，如果表中存在主鍵或唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分
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)

--查看數(shù)據(jù)庫是否支持分區(qū)
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)

--范圍分區(qū)
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)

按照年進(jìn)行分區(qū)
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)

優(yōu)化器只能對(duì)YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()這類函數(shù)進(jìn)行優(yōu)化選擇
下面這個(gè)例子中的分區(qū)創(chuàng)建有問題，在分區(qū)掃描的時(shí)候會(huì)掃描多個(gè)分區(qū)
按照每年每月來進(jìn)行分區(qū)
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)

下面例子為上面例子的正確創(chuàng)建方法
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分區(qū)
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分區(qū)
字段分區(qū)是范圍分區(qū)和列表分區(qū)的一種變體，字段分區(qū)可以使用多個(gè)字段作為分區(qū)鍵。
范圍字段分區(qū)和列表字段分區(qū)支持非整數(shù)字段，支持的數(shù)據(jù)類型如下：

所有整數(shù)類型：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)

--哈希分區(qū)
哈希分區(qū)主要確保分區(qū)表中的數(shù)據(jù)均勻分布在各個(gè)分區(qū)之中。
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數(shù)據(jù)庫還支持一種稱為L(zhǎng)INEAR HASH的分區(qū)，它使用一個(gè)更加復(fù)雜的算法來確定新行插入到已經(jīng)分區(qū)的表中的位置
LINEAR HASH分區(qū)的優(yōu)點(diǎn)在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，這有利于處理含有大量數(shù)據(jù)的表。LINEAR HASH分區(qū)的缺點(diǎn)在于，
與使用HASH分區(qū)得到的數(shù)據(jù)分布相比，各個(gè)分區(qū)間數(shù)據(jù)的分布可能不大均衡
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分區(qū)
KEY分區(qū)類似哈希分區(qū)，除了哈希分區(qū)使用用戶自定義的表達(dá)式。分區(qū)鍵列必須包含部分或所有的表的主鍵。
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)

--復(fù)合分區(qū)
MySQL數(shù)據(jù)庫允許在RANGE和LIST的分區(qū)上再進(jìn)行HASH或KEY的子分區(qū)
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)

--查看分區(qū)
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)

--查看分區(qū)表執(zhí)行計(jì)劃
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)

--增加分區(qū)

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指定分區(qū)
MariaDB [test]> alter table employees truncate partition p0;
Query OK, 0 rows affected (0.12 sec)

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

--將一個(gè)分區(qū)拆分成多個(gè)分區(qū)
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)

--將多個(gè)分區(qū)合并成一個(gè)分區(qū)
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)

--減少哈希分區(qū)的數(shù)量
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)

增加哈希分區(qū)的數(shù)量
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)

在表和分區(qū)間交換數(shù)據(jù)
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)

創(chuàng)建交換表
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)

將分區(qū)表改成普通表
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表的分區(qū)p0中的數(shù)據(jù)移動(dòng)到表e2中，p0分區(qū)中的數(shù)據(jù)被移到表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)

--查詢指定分區(qū)
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)

--將非分區(qū)表轉(zhuǎn)換成分區(qū)表
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

--測(cè)試NULL值在分區(qū)中的存儲(chǔ)
RANGE分區(qū)中，NULL值會(huì)被當(dāng)作最小值來處理；LIST分區(qū)中，NULL值必須出現(xiàn)在枚舉列表中；HASH/KEY分區(qū)中，NULL值會(huì)被當(dāng)作零值來處理

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)