三十二、分区表

喵叔

发布于 2021-06-29 14:39:48

6040

发布于 2021-06-29 14:39:48

文章被收录于专栏：喵叔's 专栏

一、什么是分区表

分区表就是按照某种规则将同一张表的数据分段划分到多个存储位置。对数据的分区存储提高了数据库的性能，被分去存储的数据在物理上是多个文件，但在逻辑上仍然是一个表，对表的任何操作都和没有分区之前一样。在执行增删改查等操作时，数据库会自动通过找到对应的分区，然后执行操作。

Tip：

MySQL 从5.1.3开始支持分区；
在 MySQL 8.0 中只有 InnoDB 和 NDB 两个存储引擎支持分区。

二、分区表的好处

存储更多与单个磁盘或文件系统分区相比，可以存储更多的数据。
提高查询吞吐通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
并行处理设计到 sum()、count() 等聚合函数的查询，可以很容易进行并行处理。
提升查询效率一些查询可以极大的优化，查询仅从某个或几个分区中获取数据。
便于管理很容易根据分区删除失去保护意义的历史数据。

三、分区表的四种类型

MySQL 支持的分区类型包括 Range、List、Hash 和 Key，最常用的是 Range。

Range 分区允许将数据划分不同范围，是基于属于一个给定连续区间的列值把多行分配个分区。例如将学生表按照出生年划分成若干个分区。创建 Range 分区例子如下：

create table student(
    id int not null auto_increment,
    name varchar(30),
    year int,
    province int,
    primary key(id,year)
)
partition by RANGE(year)(
    partition p1 VALUES LESS THAN (1990) DATA DIRECTORY='d:/data/p1',
     partition p2 VALUES LESS THAN (1995) DATA DIRECTORY='d:/data/p2',
      partition p3 VALUES LESS THAN (2000) DATA DIRECTORY='d:/data/p3',
       partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY='d:/data/p4',
)

代码中首先创建了一个 student 表，然后以 year 字段分区，在 SQL 中 p1、p2、p3、p4 是分区名称，紧跟在 VALUES 后面的是分区条件，DATA DIRECTORY 是分区文件存储的位置。这里需要注意的是如果想要使用某个字段进行 Range 分区，则逐渐必须包含分区字段。

List 分区预序系统通过预定义的列表的值来对数据进行分割，是基于列值匹配一个离散集合中的某个值进行选择。例如学生表按照年龄进行分区，示例代码如下：

create table student(
    id int not null auto_increment,
    name varchar(30),
    age int,
    province int,
    primary key(id,age)
)
partition by RANGE(age)(
    partition p1 VALUES IN (20,21,22,23,24),
     partition p2 VALUES IN (25,26,27,28,29),
      partition p3 VALUES IN (30,31,32,33,34)
)

Hash 分区允许通过对表的一个或多个列的 Hash Key 进行计算，最后通过这个 Hash 码不同数值对应的数据区域进行分区。是基于用户定义的表达式返回的值来进行选择分区。例如根据学生的年龄分10个区。示例代码如下：

create table student(
    id int not null auto_increment,
    name varchar(30),
    age int,
    province int,
    primary key(id,age)
)
partition by HASH(age)
partition 10;

Key 分区对 Hash 模式的一种延申，这里的 Hash Key 是 MySQL 系统产生的。例如按照学生的年龄分10个区。示例代码如下：

create table student(
    id int not null auto_increment,
    name varchar(30),
    age int,
    province int,
    primary key(id,age)
)
partition by key(age)
partition 10;

四、常用分区其他操作

新增分区

# 语法
alter table 'table_name' add partition(分区条件)
# 例子
alter table 'student' add partition(paetition p5 VALUES LESS THAN MAXVALUE);

对已存在的表进行分区

# 语法
alter table 'table_name' partition by 分区类型(分区条件列)(
    分区条件
)
# 例子
alter table 'student' partition by RANGE(age)(
    partition p1 VALUES LESS THAN (20) DATA DIRECTPRY='d:/data/p1',
    partition p2 VALUES LESS THAN (30) DATA DIRECTPRY='d:/data/p2',
    partition p3 VALUES LESS THAN (40) DATA DIRECTPRY='d:/data/p3',
    partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTPRY='d:/data/p4',
)

删除分区

# 语法
alter table 'table_name' drop partition 分区名称；
# 例子
alter table 'student' drop partition p1;

移除分区

# 语法
alter table 'table_name' remove partitioning
# 例子
alter table 'student' remove partitioning

Tip：删除分区会删除分区下的数据，移除分区数据不会被移除，另外删除分区可以指定要删除的分区，但移除分区是移除整个表的分区。删除分区不适用于使用 HASH分区的表。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/06/26 ，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

数据库

sql

存储

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

三十二、分区表

三十二、分区表

一、什么是分区表

二、分区表的好处

三、分区表的四种类型

四、常用分区其他操作

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐