MySql从一窍不通到入门（七）分区策略：HASH/RANGE/LIST/KEY

转载：数据表分区策略及实现（一）

一、分区的原理

 

表的分区的原理理解起来比较简单，其实就是把一张大数据量的表，根据分区策略进行分区，分区设置完成之后，由数据库自身的储存引擎来实现分发数据到指定的分区中去，正如上图所示，一张数据表被分成了n个分区，并且分区被放入到不同的介子disk中，每个disk中包含自少一个分区，这就实现了数据的均衡以及通过跨分区介子检索提高了整体的数据操作IO吞吐率。

 

三、表分区的策略

目前在MySql中支持四种表分区的方式，分别为HASH、RANGE、LIST及KEY，当然在其它的类型数据库中，分区的实现方式略有不同，但是分区的思想原理是相同，具体如下：

 1、HASH

HASH分区主要用来确保数据在预先确定数目的分区中平均分布，而在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。比如：

CREATE TABLE t_product_item (
      id int(7) not null,
      title varchar(40) not null,
      subtitle varchar(60) null,
      price double not null,
      imgurl varchar(70) not null,
      producttype int(2) not null,
      createtime datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY HASH(YEAR(createtime))
 PARTITIONS 10
;

上面的例子，使用HASH函数对createtime日期进行HASH运算，并根据这个日期来分区数据，这里共分为10个分区。

 

NOTE：

可以通过在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回整数的表达式。它可以是字段类型为MySQL 整型的一列的名字，也可以是返回非负数的表达式。另外，可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

 

2、RANGE

基于属于一个给定连续区间的列值，把多行分配给同一个分区，这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。比如：

CREATE TABLE t_product_item (
      id int(7) not null,
      title varchar(40) not null,
      subtitle varchar(60) null,
      price double not null,
      imgurl varchar(70) not null,
      producttype int(2) not null,
      createtime datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE(producttype) (
      PARTITIONP0 VALUES LESS THAN(2),
      PARTITIONP1 VALUES LESS THAN(4),
      PARTITIONp2 VALUES LESS THAN(6),
      PARTITIONp3 VALUES LESS THAN MAXVALUE
);

上面的例子，使用了范围RANGE函数对产品类型进行分区，共分为4个分区，产品类别为0,1的对应在分区P0中，2,3类别在分区P1中，依次类推即可。那么类别编号大于6的怎么分区呢？我们可以使用MAXVALUE来将大于6的数据统一存放在分区P3中即可。

 

3、LIST

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择分区的。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。比如：

DROP TABLE  IF EXISTS t_product_item;
CREATE TABLE t_product_item (
      id int(7) not null,
      title varchar(40) not null,
      subtitle varchar(60) null,
      price double not null,
      imgurl varchar(70) not null,
      producttype int(2) not null,
      createtime datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY LIST(producttype) (
      PARTITIONP0 VALUES IN (0,1),
      PARTITIONP1 VALUES IN (2,3),
      PARTITIONP2 VALUES IN (4,5),
      PARTITIONP3 VALUES IN (6,7,8,9,10,11,12)
 )

上面的例子，使用了列表匹配LIST函数对产品类型进行分区，共分为4个分区，产品类别为0,1的对应在分区P0中，2,3类别在分区P1中，依次类推即可。那么类别编号大于12的怎么分区呢？这里不同于RANGE，LIST分区的数据必须匹配列表中的产品类别才能进行分区。

 4、KEY

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。比如：

CREATE TABLE t_product_item (
      id int(7) not null,
      title varchar(40) not null,
      subtitle varchar(60) null,
      price double not null,
      imgurl varchar(70) not null,
      producttype int(2) not null,
      createtime datetime not null
)
 ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY KEY(producttype)
 PARTITIONS 10;

NOTE：

此种分区算法目前使用的比较少，大家知道其存在和怎么使用即可。

 

四、表分区的实施

这里我以HASH分区算法为例，进行数据表分区的实现，具体如下：

 1、建分区表

sql：

DROP TABLE  IF EXISTS t_product_item;
CREATE TABLE t_product_item (
      id int(7) not null,
      title varchar(40) not null,
      subtitle varchar(60) null,
      price double not null,
      imgurl varchar(70) not null,
      producttype int(2) not null,
      createtime datetime not null
      DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP
)
 ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE(producttype) (
      PARTITIONP0 VALUES LESS THAN(2),
      PARTITIONP1 VALUES LESS THAN(4),
      PARTITIONp2 VALUES LESS THAN(6),
      PARTITIONp3 VALUES LESS THAN MAXVALUE
)

NOTE：

这里使用了range算法分区，目前分为4个分区，分区是按照产品的类别进行划分，具体说明请查看上面的range讲解说明。

 2、插入数据

 A、插入产品类型为0,1 共1条

insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
 values(0,'A','A-title',99.99,'http://null',0);

B、插入产品类型为2,3 共1条

insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
 values(1,'A','A-title',99.99,'http://null',3);

C、插入产品类型为4,5 共1条

insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
 values(2,'A','A-title',99.99,'http://null',5);

D、插入产品类型为6,7，8 共3条

insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
 values(3,'A','A-title',99.99,'http://null',6);
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
 values(4,'A','A-title',99.99,'http://null',7);
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
 values(5,'A','A-title',99.99,'http://null',8);

3、验证分区

首先，查看下各个分区信息及数据是否正确：

select
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
from information_schema.partitions where
  table_schema = schema() 
  andtable_name='t_product_item';   

执行结果：

 从上图知道，我们根据RNAGE算法，通过产品类型producttype进行RNAGE运算，结果插入数据的结果都已经正确的插入到对应的分区位置。

 其次，使用EXPLAIN分析查询sql：

explain select * from t_product_item where producttype=3;

执行结果：

 从上图知道，这次的查询数据是从partions=P0中直接查询的，而不是全表查询，所以如果数据量很大时会明显提高检索效率。