表分区

表分区类型

什么是表分区

表分区是将一个表的数据按照一定的规则水平划分成不同的逻辑块，并分别进行物理存储，这个规则就叫做分区幻术，可以有不同的分区规则。
MySQL5.6、5.7可以通过show plugins语句来查看当前MySQL是否支持表分区功能。

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| AUDIT_CDB                  | ACTIVE   | AUDIT              | audit_cdb.so       | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+

# 这一列表示支持表分区功能：
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |

MySQL8.0之后移除了show plugins对partition的显示，但社区版本的表分区功能是默认开启的。

创建表分区

普通创建方式

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',
	jod_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 (21)
);

# show 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',
  `jod_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!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 (21) ENGINE = InnoDB) */ |

# 插入数据
insert into employees values
(1, 'a', 'aa', now(), now(), 1, 1);
insert into employees values
(2, 'b', 'bb', now(), now(), 2, 6),(7, 'c', 'cc', now(), now(), 3, 7),
(4, 'd', 'dd', now(), now(), 4, 11),(5, 'e', 'ee', now(), now(), 5, 21);

# 查看表分区数据
mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | jod_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | aa    | 2020-04-08 | 2020-04-08 |        1 |        1 |
|  2 | b     | bb    | 2020-04-08 | 2020-04-08 |        2 |        6 |
|  7 | c     | cc    | 2020-04-08 | 2020-04-08 |        3 |        7 |
|  4 | d     | dd    | 2020-04-08 | 2020-04-08 |        4 |       11 |
|  5 | e     | ee    | 2020-04-08 | 2020-04-08 |        5 |       20 |
+----+-------+-------+------------+------------+----------+----------+
5 rows in set (0.00 sec)

# 指定表分区查看分区下所有
mysql> select * from employees partition(p1);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | jod_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  2 | b     | bb    | 2020-04-08 | 2020-04-08 |        2 |        6 |
|  7 | c     | cc    | 2020-04-08 | 2020-04-08 |        3 |        7 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

# 通过where条件指定，自动走合适的分区去检索
mysql> select * from employees where store_id=11;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | jod_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  4 | d     | dd    | 2020-04-08 | 2020-04-08 |        4 |       11 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.01 sec)

mysql> explain select * from employees where store_id=11;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from employees where store_id in (6,7);
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from employees;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

含主键、唯一键创建分区

表中含有主键或者唯一键时，则每个被用过分区函数的字段必须是表中唯一键和主键的全部或一部分。否则就无法创建分区表

比如下面的表由于唯一键和主键没有在相同的字段，所以无法创建

mysql> CREATE TABLE tnp (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (id)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function'
    
mysql> CREATE TABLE tnp (id INT NOT NULL AUTO_INCREMENT,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (ref)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function'

上述例子中删除唯一键，确保主键中的字段包含分区函数中的所有字段，创建成功

mysql> CREATE TABLE tnp (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id))
    -> PARTITION BY RANGE (id)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11));
Query OK, 0 rows affected (0.02 sec)

或者将主键扩展为包含ref字段

mysql> CREATE TABLE tnp2 (id INT NOT NULL ,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id,ref),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (ref)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11));
Query OK, 0 rows affected (0.03 sec)

表分区的优势

表分区的优势在于：

可以允许在一个表里存储更多的数据，突破磁盘限制或者文件系统限制
对于从标记将过期或历史的数据移除，在表分区很容易实现，只要将对应的分区移除即可
对某些查询和修改语句来说，可以自动将数据范围缩小到一个或者几个表分区上，优化语句执行效率。而且可以通过显式指定表分区来执行语句，比如select * from tb partition (p0,p0) where id<5;

表分区的类型

range表分区

范围表分区，按照一定的范围值来确定每个分区包含的数据。
分区函数使用的字段必须只能是整数范围

分区的定义范围必须是连续的，且不能有重叠部分，通过使用VALUES LESS THAN来定义分区范围，表分区的范围定义是从小到大定义的

例如：

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 (21) );
  	
mysql> insert into employees values
    -> (5, 'e', 'ee', now(), now(), 5, 22);
ERROR 1526 (HY000): Table has no partition for value 22

Store_id<6的数据被放在p0分区里，6<=store_id<10之间的数据被放在p1分区里，以此类推
当新插入的数据为(72, ‘Mitchell’, ‘Wilson’, ‘1998-06-25’, NULL, 13) 时，则新数据被插入到p2 分区里

但当插入的数据的store_id为21时，由于没有分区去容纳此数据，所以会报错Table has no partition for value 22，我们需要修改一下表的定义

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);

MAXVALUE关键词的作用是表示可能的最大值，所以任何store_id>=16的数据都会被写入到p3分区里

分区函数中也可以使用表达式，比如：

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, store_id INT )
    PARTITION BY RANGE ( YEAR(separated) )
    ( PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE );

对timestamp字段类型可以使用的表达式目前仅有unix_timestamp,其他的表达式都不允许

CREATE TABLE quarterly_report_status (
	report_id INT NOT NULL, 
	report_status VARCHAR(20) NOT NULL, 
	report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
    PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) )
    ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE) );

mysql> create table temp(tstamp timestamp)
    -> partition by range(year(tstamp))
    -> (partition p0 values less than(2017));
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
# 非unix时间戳 不被允许

LIST表分区

List表分区：列表表分区，按照一个一个确定的值来确定每个分区包含的数据

通过PARTITION BY LIST(expr)分区函数表达式必须返回整数，取值范围通过VALUES IN (value_list)定义

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, 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) );

非整数创建list表分区报错

mysql> 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, store_name varchar(20) )
    ->     PARTITION BY LIST(store_name)
    ->     ( PARTITION pNorth VALUES IN ('a','b'),
    ->     PARTITION pEast VALUES IN ('c','d'));
ERROR 1697 (HY000): VALUES value for partition 'pNorth' must have type INT
mysql>

对List表分区来说，没有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含

mysql> CREATE TABLE h2 (c1 INT, c2 INT )
    ->   PARTITION BY LIST(c1)
    ->   (PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8));
Query OK, 0 rows affected (0.02 sec)
  
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1526 (HY000): Table has no partition for value 3
values 3 不在分区定义中，所以插入失败

同样，当有主键或者唯一键存在的情况下，分区函数字段需要包含在主键或唯一键中

mysql> CREATE TABLE employees2 ( id INT NOT NULL primary key,
    -> 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 (1,3),
    ->     PARTITION pEast VALUES IN (2,4));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

分区函数包含多个字段

对range和list表分区来说，分区函数包含多个字段
分区多字段函数(column partition)所涉及的字段类型可以包括
- TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
- DATE and DATETIME.
- CHAR, VARCHAR, BINARY, and VARBINARY.
- 其他字段类型都不支持
范围多字段分区函数与普通的范围分区函数的区别在于
- 字段类型多样化
- 范围多字段分区函数不支持表达式，只能用字段名
- 范围多字段分区函数支持一个或多个字段
每个column_list里的字段和value_list里的数值必须一一对应，数据类型也要一致
对范围多字段分区来说，有时一行数据的分区列表的第一个元素等于VALUES LESS THAN的值列表的第一个元素是会被插入到相应的分区

范围多字段表分区

mysql> CREATE TABLE rcx (a INT, b INT,
    -> c CHAR(3), d INT)
    -> PARTITION BY RANGE COLUMNS(a,b,c)
    -> (PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    -> PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    -> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));
Query OK, 0 rows affected (0.04 sec)

mysql>  insert into rcx values(4,5,'abc',1),(5,9,'abc',1),(4,11,'ggg',1),(5,11,'abc',1),(6,2,'abc',1);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>  select * from rcx；
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    4 |    5 | abc  |    1 |
|    5 |    9 | abc  |    1 |
|    4 |   11 | ggg  |    1 |
|    5 |   11 | abc  |    1 |
|    6 |    2 | abc  |    1 |
+------+------+------+------+
5 rows in set (0.00 sec)

mysql> select * from rcx partition (p0);
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    4 |    5 | abc  |    1 |
|    5 |    9 | abc  |    1 |
|    4 |   11 | ggg  |    1 |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql>  select * from rcx partition (p1);
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    5 |   11 | abc  |    1 |
|    6 |    2 | abc  |    1 |
+------+------+------+------+
2 rows in set (0.00 sec)

查看表分区相关信息-INFORMATION_SCHEMA.PARTITIONS

mysql> CREATE TABLE rc1(a INT,b INT)
    -> PARTITION BY RANGE COLUMNS(a, b)
    -> ( PARTITION p0 VALUES LESS THAN (5, 12),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE) );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql>  INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          1 |
+----------------+------------+
2 rows in set (0.02 sec)

mysql>

多列对比情况

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

只要保证取值范围是增长的，表分区就能创建成功

但如果取值范围不是增长的，就会返回错误

mysql> CREATE TABLE rc4 (a INT,b INT,c INT)
    -> PARTITION BY RANGE COLUMNS(a,b,c)
    -> ( PARTITION p0 VALUES LESS THAN (0,25,50),
    -> PARTITION p1 VALUES LESS THAN (10,20,100),
    -> PARTITION p2 VALUES LESS THAN (10,30,50),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) );
Query OK, 0 rows affected (0.04 sec)
  
mysql>
mysql>
mysql> CREATE TABLE rcf (a INT, b INT, c INT)
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    -> PARTITION p0 VALUES LESS THAN (0,25,50),
    -> PARTITION p1 VALUES LESS THAN (20,20,100),
    -> PARTITION p2 VALUES LESS THAN (10,30,50),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

列表多字段表分区

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', 'Högsby',	'Mönsterås'),
	PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
	PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
	PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo') );

HASH表分区

hash表分区：哈希表分区。按照一个自定义的函数返回值来确定每个分区包含的数据，这个自定义函数也

可以仅仅是一个字段名字
通过PARTITION BY HASH (expr)子句来表达哈希表分区，其中的expr表达式必须返回一个整数，基于分区个数的取模(%)运算。根据余数插入到指定的分区

对哈希表分区来说只需要定义分区的个数，其他的事情由内部完成

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, store_id INT )
	PARTITION BY HASH(store_id)
	PARTITIONS 4;

如果没有写明PARTITIONS字段，则默认为1

表达式可以是整数类型字段，也可以是一个函数

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, store_id INT )
	PARTITION BY HASH( YEAR(hired) )
	PARTITIONS 4;

例如
```
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
	PARTITION BY HASH( YEAR(col3) )
	PARTITIONS 	4；
```
- 如果插入一条数据对应的col3为‘2005-09-15’时，则插入数据的分区计算方法为
- MOD(YEAR(‘2005-09-01’),4)
- = MOD(2005,4)
- = 1

KEY表分区

key表分区：与哈希表分区类似，只是用mysql自己的hash来确定每个分区包含的数据。
CREATE TABLE ... PARTITION BY KEY ()创建key表分区，括号里面可以包含0个或者多个字段，所引用的字段必须是主键或者主键的一部分，如果括号里面没有字段，则代表使用主键
```
CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) )
	PARTITION BY KEY()
	PARTITIONS 2;
```
如果表中没有主键但有唯一键，则使用唯一键，但唯一键字段必须定义为not null，否则报错
所引用的字段未必必须是整数类型，其他的类型也可以使用

子表分区

子表分区，是在表分区的基础上再创建表分区的概念，每个表分区下的子表分区个数必须一致

CREATE TABLE ts (id INT, purchased DATE)
	PARTITION BY RANGE( YEAR(purchased) )
	SUBPARTITION BY HASH( TO_DAYS(purchased) )
	SUBPARTITIONS 2
	( PARTITION p0 VALUES LESS THAN (1990),
	PARTITION p1 VALUES LESS THAN (2000),
	PARTITION p2 VALUES LESS THAN MAXVALUE );

ts表有是三个分区，每个分区有两个自分区。所以总共有6个分区

子表分区必须是范围/列表分区+哈希/key子表分区的组合

子表分区也可以显示的指定子表分区的名字

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 ) );	

表分区对Null值的处理

不同的表分区对NULL值的处理方式不同

对范围表分区来说，如果插入的是NULL值，则将数据放到最小的分区表里

mysql> CREATE TABLE t1 ( 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.02 sec)
  
mysql>
mysql>  INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
  
mysql>
mysql>  select * from t1 partition(p0);
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
  
mysql>

对list表分区来说，支持NULL值的唯一情况就是某个分区的允许值中包含 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.02 sec)
  
mysql>
mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1526 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1526 (HY000): Table has no partition for value NULL
  
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.03 sec)
  
mysql>
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.03 sec)
  
mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.01 sec)
  
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.01 sec)
  

对哈希表分区和Key表分区来说，NULL值会被当成0值对待

mysql> CREATE TABLE th ( c1 INT, c2 VARCHAR(20) )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)
  
mysql>
mysql>  INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
  
mysql>
mysql>  select * from th partition(p0);
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
|    0 | gigan  |
+------+--------+
2 rows in set (0.00 sec)

表分区管理

alert table 管理表分区

通过alter table命令可以执行增加，删除，重新定义，合并或者拆分表分区的管理动作

删除表分区

对范围表分区和列表表分区来说，删除一个表分区命令如下

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    -> PARTITION BY RANGE( YEAR(purchased) )
    -> ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN
    -> (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005), PARTITION p4 VALUES LESS THAN (2010), PARTITION p5 VALUES LESS THAN (2015) );
Query OK, 0 rows affected (0.03 sec)
  
mysql>
mysql> insert into tr values(1, 'abc','1999-12-21');
Query OK, 1 row affected (0.01 sec)
  
mysql>
mysql> select * from tr partition(p2);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | abc  | 1999-12-21 |
+------+------+------------+
1 row in set (0.00 sec)
  
mysql>
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> select * from tr partition(p2);
ERROR 1735 (HY000): Unknown partition 'p2' in table 'tr'

删除表分区的动作不光会把分区删掉，也会把表分区里原来的数据给删除掉

mysql> show create table tr;
  
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
   
mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12- 31';
Empty set, 1 warning (0.00 sec)

增加表分区

在原分区上增加一个表分区可以通过alter table … add partition语句来完成

mysql> CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25),
    -> dob DATE ) PARTITION BY RANGE( YEAR(dob) )
    -> ( PARTITION p0 VALUES LESS THAN (1980),
    -> PARTITION p1 VALUES LESS THAN (1990),
    -> PARTITION p2 VALUES LESS THAN (2000) );
Query OK, 0 rows affected (0.03 sec)
  
mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

但对范围表分区来说，增加的表分区必须在尾部增加，在头部或者在中间增加都会失败

mysql> ALTER TABLE members ADD PARTITION ( PARTITION n VALUES LESS THAN
    -> (1970));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

解决这个问题，可以使用REORGANIZE命令

mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO
    -> ( PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1980) );
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> show create table members;
| members | CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |

对列表表分区来说，只要新增加的分区对应的值在之前的表分区中没有出现过，就可以通过alter table… add partition来增加

CREATE TABLE tt ( id INT, data INT ) PARTITION BY LIST(data) 
	( PARTITION p0 VALUES IN (5, 10, 15),
	PARTITION p1 VALUES IN (6, 12, 18) );
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

重组表分区

可以通过REORGANIZE命令将之前的多个分区合并成一个或几个分区，但要保持分区值一致

mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS THAN (1970) );
ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
mysql>
mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS THAN (1985) );
ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
  
mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES
    -> LESS THAN (1980) );
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

将多个分区重组成多个分区

mysql> SHOW CREATE TABLE MEMBERS;
+---------+--------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| MEMBERS | CREATE TABLE `MEMBERS` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION p0 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |
+---------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  
mysql> ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2010));
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> SHOW CREATE TABLE MEMBERS;                                                                                                      +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MEMBERS | CREATE TABLE `MEMBERS` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION m0 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION m1 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  
mysql>

对列表分区来说，重新组织的分区必须是相邻的分区
如果表里已有的数据在新重组的分区中没有指定的值，则数据会丢失

对哈希表分区和KEY表分区的管理手段与范围和列表表分区完全不同，比如不能删除表分区，但可以通过ALTER TABLE ... COALESCE PARTITION语句合并表分区，其partition后面的数字代表缩减的个数，而不是缩减到的个数

mysql> CREATE TABLE clients(id INT,fname VARCHAR(30),lname VARCHAR(30),signed DATE) PARTITION BY HASH ( MONTH( signed))
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.05 sec)
  
mysql> show create table clients;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| clients | CREATE TABLE `clients` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `signed` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH ( MONTH( signed))
PARTITIONS 12 */ |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
  
mysql> insert into clients values(1,'a','a','2017-01-01'),(2,'a','a','2017-02-01'),(3,'a','a','2017-03-01'),(4,'a','a','2017-04-01');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
  
mysql> select * from clients;
+------+-------+-------+------------+
| id   | fname | lname | signed     |
+------+-------+-------+------------+
|    1 | a     | a     | 2017-01-01 |
|    2 | a     | a     | 2017-02-01 |
|    3 | a     | a     | 2017-03-01 |
|    4 | a     | a     | 2017-04-01 |
+------+-------+-------+------------+
4 rows in set (0.00 sec)
  
mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 4 rows affected (0.43 sec)
  
mysql> show create table clients;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                   |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| clients | CREATE TABLE `clients` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `signed` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH ( MONTH( signed))
PARTITIONS 8 */ |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  
mysql> select partition_name,table_rows from information_schema.partitions where table_name='clients';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          0 |
| p4             |          0 |
| p5             |          0 |
| p6             |          0 |
| p7             |          0 |
+----------------+------------+
8 rows in set (0.01 sec)
  

对于哈希表分区和key表分区，如果是增加表分区，则可以使用add partition语句

  mysql> ALTER TABLE clients ADD PARTITION PARTITIONS 6;
  Query OK, 4 rows affected (0.27 sec)
  Records: 4  Duplicates: 0  Warnings: 0
  
  mysql> select partition_name,table_rows from information_schema.partitions where table_name='clients';
  +----------------+------------+
  | partition_name | table_rows |
  +----------------+------------+
  | p0             |          0 |
  | p1             |          0 |
  | p2             |          0 |
  | p3             |          0 |
  | p4             |          0 |
  | p5             |          0 |
  | p6             |          0 |
  | p7             |          0 |
  | p8             |          0 |
  | p9             |          0 |
  | p10            |          0 |
  | p11            |          0 |
  | p12            |          0 |
  | p13            |          0 |
  +----------------+------------+
  14 rows in set (0.01 sec)

分区数据交换

对分区表可以通过ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt命令将一个分区或者是子分区的数据与普通的表的数据相互交换，其本身的表结构不会变化

交换的分区表和目标表必须结构完全相同，包括字段，类型，索引，存储引擎必须完全一样

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.03 sec)
  
mysql> CREATE TABLE E2 LIKE E;
Query OK, 0 rows affected (0.03 sec)
  
mysql> insert into e(id,fname) values(10,'a'),(20,'b'),(170,'c'),(180,'d'),(190,'e');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
  
mysql> SELECT * FROM E;
+-----+-------+-------+
| id  | fname | lname |
+-----+-------+-------+
|  10 | a     | NULL  |
|  20 | b     | NULL  |
| 170 | c     | NULL  |
| 180 | d     | NULL  |
| 190 | e     | NULL  |
+-----+-------+-------+
5 rows in set (0.00 sec)
  
mysql> SHOW CREATE TABLE E2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| E2    | 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.01 sec)
  
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> SHOW CREATE TABLE E2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| E2    | 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)
  
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.03 sec)
  
mysql> SELECT * FROM E;
+-----+-------+-------+
| id  | fname | lname |
+-----+-------+-------+
| 170 | c     | NULL  |
| 180 | d     | NULL  |
| 190 | e     | NULL  |
+-----+-------+-------+
3 rows in set (0.00 sec)
  
mysql> SELECT * FROM E2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 10 | a     | NULL  |
| 20 | b     | NULL  |
+----+-------+-------+
2 rows in set (0.00 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             |          3 |
+----------------+------------+
4 rows in set (0.01 sec)
  
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; ##再次执行后数据 交换回来
Query OK, 0 rows affected (0.03 sec)
  
mysql> SELECT * FROM E;
+-----+-------+-------+
| id  | fname | lname |
+-----+-------+-------+
|  10 | a     | NULL  |
|  20 | b     | NULL  |
| 170 | c     | NULL  |
| 180 | d     | NULL  |
| 190 | e     | NULL  |
+-----+-------+-------+
5 rows in set (0.01 sec)
  
mysql> SELECT * FROM E2;
Empty set (0.00 sec)
  
mysql> create table e3(id int, fname varchar(30),lname2 varchar(30));
Query OK, 0 rows affected (0.02 sec)
  
mysql>  alter table e exchange partition p3 with table e3;
ERROR 1736 (HY000): Tables have different definitions
  
mysql> create table e4(id int not null, fname varchar(30),lname varchar(32));
Query OK, 0 rows affected (0.02 sec)
  
mysql> alter table e exchange partition p3 with table e4;
ERROR 1736 (HY000): Tables have different definitions

执行exchange命令时，表里不一定是空数据，如果有数据需要保证里面的数据符合白表分区的条件，负责只能用without validation 来条跳过验证环节

在MySQL 5.6 版本中，该问题无法解决；而在MySQL 5.7.5版本中，增加了一个选项 WITHOUT VALIDATION ，可以解决上面的报错。操作如下：

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.01 sec)
    
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1737 (HY000): Found a row that does not match the partition
    
    
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;

对子表分区也可以执行exchange命令
- 当表有子表分区时，只能exchange一个子表分区，而不能交换整个分区

分区整理与修复

去除碎片
回收未使用空间和重新获取统计资料
修复异常的分区
检查分区中数据或者索引数据是否损坏

mysql> # 当需要去除分区碎片是，可以执行rebuild命令，相当于删除数据之后重新插入
mysql> ALTER TABLE t1 REBUILD PARTITION p0, p1;
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # 也可以执行OPTIMIZE命令回收分区中未使用的空间和重新获取统计资料
mysql> ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
+---------+----------+----------+---------------------------------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                                                    |
+---------+----------+----------+---------------------------------------------------------------------------------------------+
| test.t1 | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| test.t1 | optimize | status   | OK                                                                                          |
+---------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

mysql> # Analyzing partitions命令重新收集分区统计资料
mysql> ALTER TABLE t1 ANALYZE PARTITION p3;
+---------+---------+----------+----------------------------------------+
| Table   | Op      | Msg_type | Msg_text                               |
+---------+---------+----------+----------------------------------------+
| test.t1 | analyze | Error    | Error in list of partitions to test.t1 |
| test.t1 | analyze | status   | Operation failed                       |
+---------+---------+----------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> # Repairing partitions命令修复异常的分区
mysql> ALTER TABLE t1 REPAIR PARTITION p0,p1;
+---------+--------+----------+----------+
| Table   | Op     | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status   | OK       |
+---------+--------+----------+----------+
1 row in set (0.01 sec)

mysql> # Checking partitions命令检查分区中数据或者索引数据是否损坏
mysql> ALTER TABLE t1 CHECK PARTITION p1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from e partition(p0);
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 10 | a     | NULL  |
| 20 | b     | NULL  |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> # 删除分区中的所有数据
mysql> alter table e truncate partition p0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from e partition(p0);
Empty set (0.01 sec)

获取表分区信息的几种方式

通过show create table命令
通过show table status命令来查看表是否是分区表

通过information_schema.partitions系统表来查看分区表的具体信息

select * from information_schema.partitions where table_name='e';
select * from information_schema.partitions where table_name='e' limit 1\G

表分区修建

表分区修剪是MySQL优化的一种，其核心就是只扫描需要的分区

CREATE TABLE t1 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) 
	PARTITION BY RANGE( region_code ) 
	( PARTITION p0 VALUES LESS THAN (64), 
	PARTITION p1 VALUES LESS THAN (128), 
	PARTITION p2 VALUES LESS THAN (192), 
	PARTITION p3 VALUES LESS THANMAXVALUE );
  
# 查看语句执行计划
mysql> explain SELECT fname, lname, region_code, dob from t1  WHERE region_code>125 AND region_code<130;

此查询只需要扫描P1和P2两个分区就能得到结果，从而获得额外的性能提升
不光是select语句可以被使用表分区修剪，update和delete语句也可以使用

表分区选择

表分区选择和表分区修剪类似，只不过修剪是自动实现的，而表分区选择是现实的指定分区范围
表分区选择不仅支持select语句，也支持update，insert，delete等语句

MySQl表分区

mysql表分区

表分区