MySQL 分区

不能只对索引分区而不对表分区，mysql的分区表上创建的索引一定是本地local索引。查看当前版本mysql是否支持分区：
mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql5.1版本中，range分区、list分区、hash分区都要求分区键必须是int类型，或者通过表达式返回int类型；key分区可以是其它类型。
mysql5.5版本中，已经支持非整数的range分区、list分区。
无论是哪种mysql分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段以外的其他字段分区。
mysql不禁止在分区键值上使用null。range分区中，null值会被当作最小值来处理；list分区中，null值必须出现在枚举列表中；hash/key分区中，null值会被当作零值来处理。

1、range分区：基于一个给定连续区间范围，把数据分配到不同的分区。
例子：
create table emp_date(
 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 range (year(separated)) (
  partition p0 values less than (2013),
  partition p1 values less than (2014),
  partition p2 values less than (2015),
  partition p3 values less than (MAXVALUE)
);

create table emp_date(
 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 range columns (separated) (
  partition p0 values less than ('2013-01-01'),
  partition p1 values less than ('2014-01-01'),
  partition p2 values less than ('2015-01-01'),
  partition p3 values less than (MAXVALUE)
);

2、list分区：类似range分区，区别在list分区时基于枚举出的值列表分区，range是基于给定的连续区间范围分区。

create table emp_date(
 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 list (store_id) (
  partition p0 values in (3,5),
  partition p1 values in (1,10),
  partition p2 values in (4,9),
  partition p3 values in (2),
  partition p4 values in (6)
);

注意：list分区不存在类似values less than MAXVALUE这样包含其他值的定义方式。

3、columns分区：引入columns分区解决了mysql5.5版本之前range分区和list分区只支持整数分区的问题。columns分区可以细分为range columns分区和list columns分区，range columns分区和list columns分区都支持整数、日期时间、字符串三大数据类型。

create table emp_date(
 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 range columns (separated) (
  partition p0 values less than ('2013-01-01'),
  partition p1 values less than ('2014-01-01'),
  partition p2 values less than ('2015-01-01'),
  partition p3 values less than (MAXVALUE)
);

mysql5.5中，columns分区仅支持一个或者多个字段作为分区键，不支持表达式作为分区键，区别于range分区和list分区。
对比range分区和list分区，columns分区的亮点除了支持数据类型增加之外，另外一大亮点是columns分区还支持多列分别：

create table rc3(
 a int,
 b int
)
partition by range columns (a,b) (
  partition p0 values less than (0,10),
  partition p1 values less than (10,10),
  partition p2 values less than (10,20),
  partition p3 values less than (10,35),
  partition p4 values less than (10,MAXVALUE),
  partition p5 values less than (MAXVALUE,MAXVALUE)
);

4、hash分区：基于给定的分区个数，把数据分配到不同的分区。

常规hash分区：使用的是取模算法，假设将要保存记录的分区编号为N，那么N=mod(expr,num)。

create table emp(
 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 5;

线性hash分区：常规hash分区在管理上带来的代价太大了，不适合需要灵活变动分区的需求。为了降低分区管理的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。

create table emp(
 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 linear hash (store_id) partitions 5;

5、key分区：类似于hash分区。

create table emp(
 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 key (job) partitions 5;

按照key进行分区非常类似于按照hash进行分区，在key分区中使用关键字linear具有同样的作用。

6、子分区：mysql 5.1开始支持对已经通过range或者list分区了的表再进行子分区，子分区既可以使用hash分区，也可以使用key分区。

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有3个range分区，这3个分区中的每个分区（p0、p1、p2）又被进一步分成2个子分区。

7、分区管理

7.1 range & list分区管理
##删除分区
alter table emp_date drop partition p2;

##添加分区
##只能从range分区列表最大端增加分区。
alter table emp_date add partition (partition p4 values less than (2017));

##重新定义分区时，只能够重新定义相邻的分区，不能跳过分区进行重新定义。
##合并分区
alter table emp_date reorganize partition p0,p1,p2 into(
 partition p1 values less than(2015)
);

##拆分分区
alter table emp_date reorganize partition p3 into(
 partition p2 values less than(2016),
 partition p3 values less than(2017)
);

例子1：
mysql> drop table emp_date;
Query OK, 0 rows affected (0.04 sec)

mysql> create table emp_date(
    ->  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 range (year(separated)) (
    ->   partition p0 values less than (2013),
    ->   partition p1 values less than (2014),
    ->   partition p2 values less than (2015),
    ->   partition p3 values less than (2017)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `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=gbk
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table emp_date drop partition p2;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `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=gbk
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table emp_date add partition (partition p4 values less than (2018));
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `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=gbk
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2018) ENGINE = InnoDB) */
1 row in set (0.01 sec)

例子2：
mysql> drop table emp_date;
Query OK, 0 rows affected (0.05 sec)

mysql> create table emp_date(
    ->  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 range (year(separated)) (
    ->   partition p0 values less than (2013),
    ->   partition p1 values less than (2014),
    ->   partition p2 values less than (2015),
    ->   partition p3 values less than (2017)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql>  show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `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=gbk
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table emp_date reorganize partition p0,p1,p2 into(
    ->  partition p1 values less than(2015)
    -> );
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `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=gbk
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table emp_date reorganize partition p3 into(
    ->  partition p2 values less than(2016),
    ->  partition p3 values less than(2017)
    -> );
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `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=gbk
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2017) ENGINE = InnoDB) */
1 row in set (0.00 sec)

7.2 hash & key分区管理
##合并分区
alter table emp coalesce partition 2;

##增加分区
##通过alter table emp add partition partitions n;语句新增hash分区或者key分区时，其实是对原表新增n个分区，而不是增加到n个分区。
alter table emp add partition partitions 8;

例子：
mysql> drop table emp;
Query OK, 0 rows affected (0.15 sec)

mysql> create table emp(
    ->  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.09 sec)

mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `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=gbk
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 4 */
1 row in set (0.00 sec)

mysql> alter table emp coalesce partition 2;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> alter table emp add partition partitions 8;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `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=gbk
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 10 */
1 row in set (0.00 sec)