mysql表分区

       最近在做订单历史数据的迁移，由于订单表数据量太大，再加上这些历史数据还要被查询，为了提高查询速度，对历史表进行了分区，做 了一些了解。简单来说表分区就是能够通过一定规则把表的不同部分在不同的位置存储为单独的表，这个分割表数据的规则叫做分区函数，在mysql中可以是求 余，也可以是一个数值区间，还可以是线性的hash函数，如果还记得数据结构中的hash函数处理key冲突时所用的方法，那么对这个就不陌生了。分区函数根据用户提供的参数，计算出记录所在的分区。

分区的优点：

1.可以把分区建立在多个磁盘上，这样相比单个文件系统或者单个磁盘，可以存储更多数据。（这个还可以通过分库分表，这样也能更好的满足业务需求）也便于管理。

2.一些查询可以等到极大的优化，如果条件的数据只保存在一个分区内，那么在查找数据的时候，只需要在这个分区内查找，能够快速定位到记录。

3.如果分区分布在不同的磁盘上，可以同步查询数据，这样可以获取更大的吞吐量。涉级聚合函数时候，可以很容易的进行并行处理。

mysql 分区主要有以下几个类型：

1.range分区

     基于一个给定的连续区间的列值，通过分区函数分配到各自分区，

  语法:partition range by(expr) 

 

create table foo(
  id int not null,
  fname VARCHAR(20),
  created Date NOT NULL,
  modified Date NOT NULL,
  PRIMARY KEY (id)
)COMMENT='foo 表分区 使用range方式'
PARTITION by range(id)(
  PARTITION P0 VALUES LESS TAN (100), #P0为分区名字 必须唯一的 
  PARTITION P1 VALUES LESS THAN (200),
  PARTITION P2 VALUES LESS THAN (300)
)

 上面小于100的记录都在分区P0中，而100-200的记录在P2中，以此类推，每个分区都是按顺序定义的，从最低到最高，类似java中的switch..case，如果id大于300那么库就不知道保存在什么地方了。这时候可以指定最 大值，如下：

create table foo(
  id int not null,
  fname VARCHAR(20),
  created Date NOT NULL,
  modified Date NOT NULL,
  PRIMARY KEY (id)
)COMMENT='foo 表分区 使用range方式'
PARTITION by range(id)(
  PARTITION P0 VALUES LESS THAN (100), #P0为分区名字 必须唯一的 
  PARTITION P1 VALUES LESS THAN (200),
  PARTITION P2 VALUES LESS THAN (300),
  PARTITION P3 VALUES LESS THAN MAXVALUE  #指定了最大值
)

 

以上是一个简单的range方式分区，但是需要注意的是如果我们使用created也就是创建日期分区这时候会出问题。如图sql

 

从错误中可以看出，分区key必须有PRIMARY KEY里的字段,而这次使用的是created字段，按年分区， 并没有包含在primary key，所以报错

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

 如果一个表没有唯一键，或者主键，那么使用分区key就没有这个限制，这是时候可以使用任何key，如果表有主键那么分区使用的key必须是包含在primary key中，可以使用联合主键来解决这个冲突

create table foo(
  id int not null,
  fname VARCHAR(20),
  created Date NOT NULL,
  modified Date NOT NULL,
  PRIMARY KEY (id,created) #这次加上了 created联合主键 这时候创建分区能够成功的 
)COMMENT='foo 表分区 使用range方式'
PARTITION by range(YEAR(created))(
  PARTITION P0 VALUES LESS THAN (2011), #P0为分区名字 必须唯一的 
  PARTITION P1 VALUES LESS THAN (2012),
  PARTITION P2 VALUES LESS THAN (2013),
  PARTITION P3 VALUES LESS THAN MAXVALUE
)

 

这样是能够成功的，同样的问题在接下的几种分区方式中也会存在。

2.list分区,

 这种类型的分区，主要针对某些列的值从属一个列表的中的一个值，而range是从属一个连续区间的集合。

语法：partition by list(expr)

创建一个list分区表：

部门	团队编号teamId
研发部	1，2，7
财务部	3，5，8
营销部	4，6，9，10

 

create table foo(
  id int not null,
  fname VARCHAR(20),
  teamId int not null,
  created Date NOT NULL,
  modified Date NOT NULL
	
)COMMENT='foo 表分区 使用LIST方式'
PARTITION by LIST(teamId)(
  PARTITION P0 VALUES IN  (1,2,7), #P0为分区名字 必须唯一的 
  PARTITION P1 VALUES IN  (3,5,8),
  PARTITION P2 VALUES IN  (4,6,9,10)
)

 

 正如之前说所说的，表不存在主键的情况下，我们可以使用任何字段作为分区key，执行成功，

 

3.hash分区

 hash分区是让记录在确定数目的分区中平均分布，可以看到在list,和range分区方式中是需要明确指定给定列值保存的分区，而在hash分区中，mysql自动分配记录所在的分区。

语法：partition by hash(expr) expr可以是任何表达式

创建一个订单表demo

 

create table order_table(
  order_id int not null,
  created Date NOT NULL,
  modified Date NOT NULL,
  PRIMARY KEY (order_id)
)COMMENT='foo 表分区 使用hash方式'
PARTITION BY HASH(order_id)
PARTITIONS 10 #定义分区数

执行结果

 

      该表被分成了10个分区，这里如果PARTITIONS 10不写，默认的分区数量就是1。如果分区函数符合y=nx,那么这种就越适合hash分区，可以理解数据在各个分区上会很均匀，确定分区实际上就是MOD(expr,num)其中  num为分区数。这是很普通的hash分区。

LINEAR HASH分区

     LINEAR HASH和HASH分区类似，语法上有一点区别，partition by linear  hash(expr),确定分区上使用的：POWER(2.CELING(LOG(2,NUM)))

 

4.key分区

  key分区和hash分区类似,只是分区函数由mysql提供。

语法：partition by key(expr)

 

create table order_table(
  order_id int not null,
  created Date NOT NULL,
  modified Date NOT NULL,
  PRIMARY KEY (order_id)
	
)COMMENT='foo 表分区 使用hash方式'
PARTITION BY KEY(order_id)
PARTITIONS 10 #定义分区数

 

 

5.子分区

    子分区是对每个分区进行再次分割，

    这次我是用的是按年分区，然后年下面按月分子分区。range加hash分区方式

 

CREATE TABLE order_table (
  order_id bigint NOT NULL DEFAULT 0,
  created datetime NOT NULL COMMENT '创建时间',
  #中间省掉了很多字段
  modified datetime NOT NULL COMMENT '修改时间',
  PRIMARY KEY (order_id,created)
) COMMENT='订单表 创建子分区 range hash'
PARTITION BY RANGE(YEAR(created))
SUBPARTITION BY HASH(MONTH(created))
#按月分区
SUBPARTITIONS 12
(
	PARTITION P11 VALUES LESS THAN (2011),
	PARTITION p12 VALUES LESS THAN (2012),
	PARTITION P13 VALUES LESS THAN (2013),
	PARTITION P14 VALUES LESS THAN (2014),
	PARTITION p15 VALUES LESS THAN (2015),
	PARTITION P16 VALUES LESS THAN (2016),
	PARTITION P17 VALUES LESS THAN (2017),
  	PARTITION p18 VALUES LESS THAN (2018),
	PARTITION p19 VALUES LESS THAN MAXVALUE
)

 

 

 

创建成功。这里直接只指定数量，让mysql去找相应的分区，当然也可以直接定义子分区。也可以指定数据存储节点和索引存储节点,由于项目需要就纪录下。

参考文档：

http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

 

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html