MySQL分区表

分区功能并不是在存储引擎层完成的，因此不是只有innodb存储引擎支持分区。
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。每个分区都是独立的对象，可以独自处理，也可以作为一个更大的对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引存放在一个对象中。
分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库高可用性的管理。
当前MySQL支持以下几种类型的分区：

• RANGE分区
• LIST分区
• HASH分区
• KEY分区

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。

RANGE分区

行数据基于属于一个给定连续区间的列值被放入分区。
示例：

create table t(
id int
)engine=innodb
partition by range (id) (
partition p0 values less than (10),
partition p1 values less than (20)
);

查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成如t#p#p0.ibd、t#p#p1.ibd。
接着插入数据：

insert into t values(1);
insert into t values(9);
insert into t values(10);
insert into t values(15);

可以通过查询information_schema数据库中的partitions表来查看每个分区的具体信息：

select * from information_schema.partitions
where table_schema='test_partition'and table_name='t'\G;

TABLE_ROWS列反映了每个分区中记录的数量PARTITION_METHOD表示分区的类型，这里显示的是RANGE。

对于表t，由于定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出了一个异常。
为解决上述问题，可以对分区添加一个MAXVALUE值的分区，MAXVALUE可以理解为正无穷：

alter table t add partition(partition p2 values less than maxvalue);

使用explain查看SQL语句的执行计划：

explain
select * from t
where id<10 and id>0\G;

可以看出该SQL语句只查询了p0分区。

LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的。
示例：

create table r (
a int,
b int)engine=innodb
partition by list (b) (
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8));

插入一些数据：

insert into r select 1,1;
insert into r select 1,2;
insert into r select 1,3;
insert into r select 1,4;

查看分区情况：

select table_name,partition_name,table_rows 
from information_schema.partitions 
where table_name='r' and table_schema='test_partition'\G;

HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的。
在RANGE分区和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户要做的只是基于将要进行的哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
示例：

create table t_hash(
a int,
b datetime
)engine=innodb
partition by hash (year(b))
partitions 4;

插入一条记录：

 insert into t_hash values(1,'2010-04-01');

分区算法如下：

MOD(YEAR('2010-04-01'),4)
=MOD(2010,4)
=2

查看分区情况：

select table_name,partition_name,table_rows 
from information_schema.partitions 
where table_name='t_hash' and table_schema='test_partition'\G;

MySQL还支持一种称为LINEAR HASH的分区，它使用一个更复杂的算法来确定新行插入到已分区的表中的位置。它的语法和HASH分区的语法相似，只是将HASH改为LINEAR HASH。
示例：

create table t_l_hash(
a int,
b datetime
)engine=innodb
partition by linear hash (year(b))
partitions 4;

插入一条记录：

insert into t_l_hash values(1,'2010-04-01');

MySQL根据以下方法进行分区判断：

• 取大于分区数量4的下一个2的幂值V，V=POWER(2,CEILING(LOG(2,num)))=4
• 所在分区N=YEAR(‘2010-04-01’)&(V-1)=2

LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据可能不大均衡。

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL提供的函数进行分区。

create table t_key (
a int,
b datetime
)engine=innodb
partition by key(b)
partitions 4;

COLUMNS分区

前面介绍的RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须是整型，如果不是整型，那应该需要通过函数将其转换为整型，如year()等函数。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转换为整型。此外RANGE COLUMNS分区可以对多个列的值进行分区。
COLUMNS分区支持以下的数据类型：

• 所有的整型类型
• 日期类型，如date和datetime。其余日期类型不予支持。
• 字符串类型。blob和text类型不予支持。
  示例：

-- 对日期类型的分区，不再需要year()函数了，而直接使用columns
create table t_columns(
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')
);

-- 可以直接使用字符串的分区
create table t_columns2(
a int,
b datetime,
city varchar(15)
)engine=innodb
partition by list columns(city) (
partition p1 values in ('a','b','c'),
partition p2 values in ('d','e','f'),
partition p3 values in ('g','h','k'),
);

-- 可以使用多个列进行分区：
create table rcx (
a int,
b int,
c char(3)
)engine=innodb
partition by range columns(a,b,c) (
partition p0 values 
);

子分区

子分区是在分区的基础上再进行分区，也叫复合分区。MySQL允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

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

表ts先根据b列进行了RANGE分区，然后又进行了一次HASH分区，所以分区的数量应该为6个。

我们也可以通过使用subpartition语法来显式地指出各个子分区的名字：

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 (1990) (
subpartition s0,
subpartition s1
),
partition p1 values less than (2000) (
subpartition s2,
subpartition s3
),
partition p0 values less than MAXVALUE (
subpartition s4,
subpartition s5
));

子分区的建立需要注意以下几个问题：

• 每个子分区的数量必须相同
• 要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有子分区。
• 每个subpartition子句必须包括子分区的一个名字。
• 子分区的名字必须是唯一的。