MySQL学习17_分区表

分区表

概念

• 按照某种规则将同一张表的数据分段划分到多个位置存储；
• 被分区存储的数据在物理上是多个文件，但在逻辑上仍然是一个表，对表的任何操作都跟没分区之前一样；
• 在执行增、删、改、查等操作时，数据库会自动找到对应的分区，然后执行操作；
• 只有在数据量很大的时候才建议进行分区；
• 在MySQL 8.0中，只有InnoDB和NDB两个存储引擎支持分区。

作用

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

类型

类型	说明
Range分区	允许将数据划分不同范围；例如可以将一个表通过年份划分成若干个分区
List分区	允许系统通过预定义的列表的值来对数据进行分割
Hash分区	允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区
Key分区	对Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的

分区操作

• 根据字段分区时，需要将该字段添加为主键

创建表时创建分区

Range分区

• 根据给定连续区间的列值分区

create table 表名(
id int not null auto_increment,
...,
字段A  类型,
primary key(id, 字段A)
)
partition by range(字段A) (
partition 分区名1 values less than (比较值) [data directory = '分区存储路径'],
partition 分区名2 values less than (比较值) [data directory = '分区存储路径'],
...,
partition 分区名n values less than maxvalue [data directory = '分区存储路径']
);

• 不指定data directory，则使用默认路径
  
  
  
  

List分区

• 根据一个离散值集合来分区

create table 表名(
id int not null auto_increment,
...,
字段A  类型,
primary key(id, 字段A)
)
partition by list(字段A) (
partition 分区名1 values in (值1_1, 值1_2,...) [data directory = '存储路径'],
partition 分区名2 values in (值2_1, 值2_2,...) [data directory = '存储路径'],
...,
partition 分区名n values in (值n_1, 值n_2】,...) [data directory = '存储路径']
);

Hash分区

• 根据用户定义的表达式的返回值来分区

create table 表名(
id int not null auto_increment,
...,
字段A  类型,
primary key(id, 字段A)
)
partition by hash(表达式(字段A)) 
partitions 模值;

• 对字段A进行表达式处理后得到一个值X，将X对模值进行取模运算得到一个值P，P就是分区编号；
• 示例：

create table tbl_hash (
id int not null auto_increment,
create_time date
)
partition by hash(year(create_time ))
partitions 4;

# 插入一条记录create_time为"2020-07-30"，那么根据以下计算来选择插入的分区：
mod(year('2020-07-30'),4)
    = MOD(2020, 4)
    = 0
# 这条记录将被保存到0号分区

Key分区

• 类似于Hash分区，但这里的Hash Key是由MySQL系统产生的

create table 表名(
id int not null auto_increment,
...,
字段A  类型,
primary key(id, 字段A)
)
partition by key(字段A) 
partitions 模值;

已存在表创建分区

alter table 表名 partiton by 分区类型(分区字段)(
);

新增分区

alter table 表名 add partition(partition 分区名 分区判断条件);

删除分区

# 删除指定分区，不会丢失数据
alter table 表名 drop partition 分区名1, 分区名2, ...;

# 删除所有分区，不会丢失数据
alter table 表名 remove partitioning;

分析分区：

• 读取并保存分区的键分布

# 删除指定分区，不会丢失数据
alter table 表名 analyze partition 分区名1, 分区名2, ...;

分解与合并分区

# 分解分区
# reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据;
# 分解前后分区的整体范围应该一致
alter table 表名 reorganize partition 分区名 into(
partition 分区名1 values 分区判断条件,
partition 分区名2 values 分区判断条件 
);

# 合并分区,不会丢失数据
alter table 表名 reorganize partition 分区名1, 分区名2 into(
partition 分区名 values 分区判断条件
);

---

知识总结，交流学习，不当之处敬请指正，谢谢！