分区表 - 简书

分区表

水平分区和局部分区索引

InnoDB支持分区表的水平分区，不支持垂直分区

水平分区，指将同一个表中不同行的记录分配到不同的物理文件中
垂直分区，指将同一个表中不同列的记录分配到不同的物理文件中

同时，MySQL数据库支持的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在同一个对象中。

查看数据库是否启用了分区功能
show variables like '%partition%'

启动了分区后，一个表的数据会存放到多个ibd文件中（而不是只有一个）。
可以通过information_schema架构下的PATITIONS表来查看每个分区的具体信息，table_rows表示每个区包含多少行数据。

select * from information_schema.PARTITIONS
where table_schema=database() and table_name='t'

分区类型

当前Mysql数据库支持以下几种类型的分区:

Range分区:行数据基于一个给定连续区间的列值放入分区。Mysql数据库5.5开始支持Range Columns分区。
List分区:和Range分区类似，只是List分区面向的是离散的值。Mysql数据库5.5开始支持List Columns分区。
Hash分区:根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
Key分区:根据Mysql数据库提供的哈希函数来进行分区。

range/list/hash/key这四种分区方式，分区数据都必须是整型，如果不是整型，应该通过函数将其转化为整型，如YEAR()、TO_DAYS()、MONTH()等。

Range分区

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

如上图分区定义，如果现在插入一个id=30的数据，会抛出异常，因为30不能被分配到任何分区中。
使用Range分区，用来管理日期数据可以方便很多，比如

create table sales(
  money int unsigned not null,
  date datetime
)engine=innodb
partition by range (year(date)) (
  partition p2008 value less than (2009),
  partition p2009 value less than (2010),
  partition p2010 value less than (2011)
);

当要删除2008年的数据，不用使用delete语句，只需要删除p2008这个分区即可。

alter table sales drop partition p2008;

并且，如果只需要搜索2008年的数据时（在where语句中写明条件），通过explain可以看到，只会去搜索p2008这个分区，而不会看其他分区，提高了查询速度。这种优化只对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()有效。

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

Hash分区

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

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

KEY分区

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

子分区

在分区的基础上再进行分区。

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

表ts先根据b列进行了Range分区，然后又进行了一次Hash分区，所以分区的数量是3*2=6。
子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引，在创建分区时，可以指定存放磁盘目录。

处理NULL值

对于Range分区，MySQL数据库会将该值放到最左边的分区。
在List分区下，要使用NULL值，则必须显式指出在哪个分区放入NULL值。
HASH和KEY分区，任何分区函数都会将NULL值的记录返回为0。

性能

对于一张大表，一般的b+树需要2~3次的磁盘IO，因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。

最后编辑于：2019.02.28 23:17:30