MySQL之分区表

MySQL的分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，MyISAM、NDB等也都支持。但也并不是所有的存储引擎都支持，如CSV就不支持。

MySQL数据库支持的分区类型是水平分区，并不支持垂直分区。此外MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。

有些人有这样一个误区：只要启用了分区，数据库就会运行的更快。这个结论不一定对的，分区可能会给某些SQL语句性能带来提升，但是分区主要用于数据库高可用性的管理。我们应该先理解分区是怎么工作的。

MySQL支持以下几种类型的分区：

Range分区：列值属于一个给定连续区间的被放入分区。
List分区：和Range分区类似，只是List分区面向的是离散的值。
Hash分区：根据自定义的表达式的返回值进行分区，返回值不能为负数。
Key分区：根据MySQL提供的哈希函数来进行分区。

无论哪种分区，如果表中存在主键或唯一索引时，分区列不许是唯一索引的组成部分。例如下面：

create table t1 (
    col1 int null,
    col2 int null,
    col3 int null,
    col4 date null,
    unique key (col1, col2, col3)
)
partition by hash(col3)
partitions 4;

唯一索引是允许NULL值的，并且分区的列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。

但是如果建表时没有指定主键、唯一索引，那么可以指定任何一列作为分区列：

create table t2(
    col1 int null,
    col2 int null,
    col3 date null
) engine=innodb
partition by hash(col2)
partitions 4;

Range分区

我们创建一个id列的区间分区表，当id<10时数据放入p0分区。当20>id>=10时，数据放入p1分区。

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

分区后数据是根据id值的范围存放在不同的物理文件中的。

由于我们定义了分区，因此对插入的值会严格遵守分区的定义，当插入一个不存在的分区中定义的值时，MySQL就会抛出异常：ERROR 1526(HY000):Table has no partition for value xx;

对于上述问题，我们可以对分区添加一个MAXVALUE值的分区，可以理解为正无穷数，下面我们添加一个分区p2,将id>=20的数都放入p2分区：

mysql> alter table t add partition(
    partition p2 values less than maxvlue);

Range分区注意用于日期列的分区，如对一些记录表，可以按年进行分区存放：

create table sales(
    id int auto_increcement,
    user_id int not null,
    money int unsigned not null,
    date datetime not null,
    primary key(id)
)engine=innodb
partition by range(year(date)) (
    partition p2020 values less than (2020),
    partition p2021 values less than (2021)
);

这样，如果当我们想要删除某一年的历史记录时，可以直接删除这个年份所在的分区即可：

mysql>alter table sales drop partition p2020;

当查询记录时候，尽量不要跨多个分区去查找数据，如果查询某年份下的数据时候，直接根据时间指定这个年份下的时间段就可以，这样就只会搜索这一个分区下的数据，查询书单会得到大幅度提升。所以对于启用分区的表，应该尽量按照分区的特性来编写合适的SQL语句进行查询。

还需要注意的是，对于Range分区，查询优化器只能对year()、to_days()、to_seconds(),unix_timestamp()这类函数进行优化选择，对复杂的计算表达式，查询时是无效的，如：partition by range(year(date)*100+month(date))。

List分区

List分区和Range分区非常相似，只是List分区列的值是离散的，而非连续的。

create table t(
    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)
);

不同于Range分区中定义的 values less than 语句，List分区使用 values in。因为每个分区的值是离散的，因此只能定义值。

同样如果插入的值不再分区定义中，MySQL会抛出异常。

List分区适合用于分区的列是固定某些值的情况。

Hash分区

Hash分区是基于哈希分区的列值指定一个列值或表达式，它的目的是将数据均匀的分布到预先定义的各个分区中，保证各个分区的数据量大致是一样的。

子句partition bu hash(expr)中，expr是返回一个整数的表达式，也可以是字段为整型的列名。

子句partitions <num>中，num是一个非负的整数，若没有包括该子句，则分区数量默认为1。

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

如果未指定分区的数量，那么分区数量默认为1。

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分区的条件是：数据必须是整型，如果不是整型，那么可以通过函数将其转换成整型。

MySQL5.5版本开始支持Columns分区，可以将它看作是Range分区和List分区的一种进化。Columns分区可以直接使用非整型的数据进行分区。分区根据类型直接比较而得，不需要转化为整型。此外，Columns分区可以对多个列的值进行分区。

Columns分区支持以下的数据类型：

所有整型，如int、smallint、tinyint、bigint。而float和decimal则不支持。
日期类型，如date和datetime。其它的日期类型不支持。
字符串类型，如char、varchar、binary、varbinary。而text和blob类型不支持。

对于之前的Range和List分区，可以直接使用range columns和list columns分区代替：

create table t_columns_range(
    a int,
    b datetime
)engine=innodb
partition by range columns(b) (
    partition p0 values less than ('2020-01-01'),
    partition p1 values less than ('2021-01-01')
);

create table t_columns_list(
    a varchar(20),
    b datetime
)engine=innodb
partition by list columns(a) (
    partition p0 values in('a', 'b', 'c'),
    partition p1 values in('d', 'e', 'f'),
);

还可以使用多个列：

create table t_columns_range(
    a int,
    b int,
    c char(3),
    d int
)engine=innodb
partition by range columns(a, b, c) (
    partition p0 values less than (5, 10, 'ff'),
    partition p1 values less than (50, 100, 'kk'),
    partition p2 values less than (maxvalue, maxvalue, maxvalue)
);

子分区

子分区是在分区的基础上再进行分区，MySQL允许在Range和List的分区是再进行Hash或Key的子分区。

create table t_p(
    a int,
    b date
) engine=innodb
partition by range(year(b))
subpartition by hash(to_days(b))
subpartitions 2 (
    partition p0 values less than(2020),
    partition p1 values less than(2021),
    partition p2 values less than maxvalue
);

这个表先根据列b进行range分区，然后又进行了一次hash分区，所以分区数量为3x2=6个。

子分区可以用于特别大的表，它可以在多个磁盘上分配数据和索引。

分区中的NULL值

对于Range分区，如果向分区列插入了NULL值，那么MySQL会把该数据放到第一个分区。

在List分区下若要使用NULL值，必须要显式的指出哪个分区中放入NULL值，否则会报错。

create table t_list(
    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,NULL)
);

Hash和Key分区对于NULL的处理：任何分区函数都会将含有NULL值的记录返回为0。

分区性能

对于OLAP应用，分区可以很好的提高查询的性能，因为OLAP应用通常需要扫描一张大表，如果按表中的一个时间戳列进行分区，查询时只需要扫描对应的分区即可。

对于OLTP应用，通常业务中大多少都是通过索引查询返回几条记录。根据B+树索引的原理可以知道，对于一张大表，一般的B+树需要2~3次磁盘IO。因此B+树可以很好的完成操作，分区的帮助不大。如果分区设计的不好还可以能会带来严重的性能问题。

假如有一张含1000W行的表，有些人认为表很大了，所以对它进行了分区，比如对主键做了10个Hash分区，这样每个表就只有100W条数据，通过主键查询应该会更快了。但是100W和1000W行数据构成的B+树的层次可能是一样的，可能都是2层，那么通过主键查询索引并不会带来性能的提升。如果1000W行数据的B+数的高度是3，100W行数据的B+树高度是2，那么通过主键分区后并按索引查询可以避免1次IO。但是如果表中可能还有按其它列作为条件进行查询的语句，那么按照这个列查询就会扫描所有的分区，即使每个分区有两次IO，那么一共需要20次IO，查询会慢好多，而按原来未分区的表仅需要2~3次IO。

所以在OLTP业务的应用中对于分区表应当考虑全面、做好规划并小心使用。

MySQL partition