MySQL系列：10 分区--分区类型篇

最新推荐文章于 2022-10-18 14:38:40 发布

原创最新推荐文章于 2022-10-18 14:38:40 发布 · 373 阅读

1 ·

CC 4.0 BY-SA版权

DataBases 专栏收录该内容

38 篇文章

订阅专栏

本文介绍了MySQL的四种分区类型：RANGE、LIST、HASH和KEY，以及它们的变体和子分区。通过举例说明了每种分区类型的建表语句和数据分布特点，强调了分区对于大表查询速度提升和存储分散的优势。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开门见山

通俗而言，分区即将某个表(通常都是大表)分为几个部分，一般商用数据库均支持分区，用于突破OS对文件大小的限制，也为了加速对大表的查询。如Oracle分区表可以将表的不同部分存储到不同的表空间。同样，MySQL因默认开启innodb_file_per_table选项，其每张表对应一个数据文件（即为表空间），因而MySQL的分区也是表的不同部分存储到不同数据文件（表空间）中。

MySQL分区又称水平分区，即水平的将表行数据划分到不同子集，不同子集的划分取决于分区函数。MySQL支持四种分区类型，分别为：RANGE、LIST、HAS、KEY，不同分区类型还支持不同的变体。下面将详细描述此四种分区，并给出相关建表语法示例。

range分区

range分区使用连续而而不重叠的范围对表进行分区。如序号1~1000、1001~2000、2001~maxvalue等；

建表语句

create table range_par1 (id int(11) not null) partition by range(id) (partition pt1000 values less than (1000) );

如上SQL语句创建表range_par1通过range划分ID小于1000的行到pt1000分区。当向表中插入ID为1001的记录时，报找不到分区错误。

变体columns

range分区支持column变体，可以对多个列进行分区。如：

mysql> create table range_col_par1 (id int(11) not null, id2 int(11) not null) partition by range columns(id,id2) (partition pt1000 values less than (1000,1000) );

Query OK, 0 rows affected (0.55 sec)

list分区（离散型）

与range分区类似，list也是通过列的范围进行分区，只不过list分区需要自行指定范围，也即成为一种离散型分区。

建表语句

create table list_par1 (id int(11) not null) partition by list(id) (partition p0 values in (1,3));

如上，id值为1或3的记录为p0分区。插入ID为2的记录报找不到分区错误；

变体columns

同样，list分区支持columns变体，可用于对一个或多个列进行分区划分，如下：

hash分区

与上述range和list分区不同，hash分区通过对指定列进行HASH运算，可使相关行记录均衡的分布到各个分区。

建表语句

create table hash_par1 (id int(3) not null) partition by hash(id) partitions 4;

如上，创建了hash分区表hash_part1,以ID列作为hash变量分为四个分区，插入0~9十行数据库，通过information_schema元数据表查看行分布，可知p0/p1分区分布3行，p2/p3分区分布2行。

变体LINEAR

HASH分区支持linear变体，即线性hash分区，其与常规hash不同的是，linear hash采用线性power-of-two算法计算hash key。有关power-of-two算法笔者将酌情在后续算法专题论述，创建liner hash分区表语句如下(在hash关键词前面假设linear)：

 create table hash_linear_par1 (id int(3) not null) partition by linear hash(id) partitions 4;

key分区

key分区与hash分区类似，不过HASH分区只支持整数，KEY分区支持除BLOB ,Text类型外的其他类型作为分区键。

建表语句

create table key_par1 (name varchar(11)) partition by key(name) partitions 4;

变体LINEAR

key分区的linear变体与hash分区类似-此处从略；

子分区

RANGE和LIST类型的表可以进一步进行子分区。子分区类似可以为hash 或 key。如下：

建表语句

CREATE TABLE sub_test (id INT, id2 INT)
PARTITION BY RANGE( id )
SUBPARTITION BY HASH( id2 )
SUBPARTITIONS 2 (
   PARTITION p0 VALUES LESS THAN (1990),
   PARTITION p1 VALUES LESS THAN (2000),
   PARTITION p2 VALUES LESS THAN MAXVALUE
    );

如上，首先通过id进行range分区为3个分区，之后同id2列对分区再进行子hash分区，这样，整个表就形成六个分区。