MySql分区策略：HASH/RANGE/LIST/KEY

爱被打了一巴掌 2021-12-18 13:01 366阅读 0赞

# 一、分区的原理 #

表的分区的原理理解起来比较简单，其实就是把一张大数据量的表，根据分区策略进行分区，分区设置完成之后，由**数据库自身的储存引擎来实现分发数据到指定的分区中**去，一张数据表被分成了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**：

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

# 三、表分区的实施 #

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

##  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条