Mysql 如何分区 分区的局限性 PARTITION

一: 分区简介

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象碧昂城一些小对象。

MySQL分区即可以对数据进行分区也可以对索引进行分区。

分区类型

• range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
• list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区
• hash分区：基于给定的分区个数，把数据分配到不同的分区
• key分区：类似于hash分区

注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。

MySQL分区的优点主要包括以下4个方面：

1. 和单个磁盘或者文件系统分区相比，可以存储更多数据
2. 优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
3. 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
4. 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

分区和水平分表功能类似，将一个大表的数据分割到多张小表中去，由于查询不需要全表扫描了，只需要扫描某些分区，所以分区能提高查询速度。

四：分区

MySQL有五种分区类型 range、list、hash、key、子分区，其中最常用的是range和list分区

range分区：

给定一个连续区间的范围值进行分区，某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段，如 日期范围, 连续的数字

list分区：

设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列。list分区使用in表示一些固定的值的列表

-- 语法
create table <table> (
    // 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
  partition <分区名称> values IN (Value1，Value2, Value3),
  ...
  partition <分区名称> values IN (Value4, Value5),
);

Hash分区：

主要用来分散热点读，确保数据在预先确定个数的分区中可能的平均分布。对一个表执行Hash分区时，mysql会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区。

mysql支持两种hash分区，

    常规hash分区和线性hash分区(linear hash分区)，常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)
    线性hash分区使用的是一个线性的2的幂运算法则。

对指定的字段(整型字段)进行哈希，将记录平均的分配到分区中，使得所有分区的数据比较平均。 hash分区只需要指定要分区的字段和要分成几个分区,
expr是一个字段值或者基于某列值云散返回的一个整数，expr可以是mysql中有效的任何函数或者其它表达式，只要它们返回一个即非常熟也非随机数的整数。
常规hash分区方式看上去挺不错的，通过取模的方式来数据尽可能平均分布在每个分区，让每个分区管理的数据都减少，提高查询效率，可是当我们要增加分区时或者合并分区，问题就来了，假设原来是5个常规hash分区，现在需要增加一个常规分区，原来的取模算法是MOD(expr, 5), 根据余数0~4分布在5个分区中，现在新增一个分区后，取模算法变成MOD(expr, 6),根据余数0~6分区在6个分区中，原来5个分区的数据大部分都需要通过重新计算进行重新分区。

常规hash分区在管理上带来了的代价太大，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡。

-- LINEAR HASH
create table <table> (
    // 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR HASH(expr)
PARTITIONS <num>;

Key 分区

按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式，而key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键。

和hash功能一样，不同的是分区的字段可以是非int类型,如字符串、日期等类型。

可以使用partition by key(expr)子句来创建一个key分区表，expr是零个或者多个字段名的列表。key分区也支持线性分区linear key

partition by key(expr) partitions num;

-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键
partition by key() partitions num;

-- linear key
partition by linear key(expr)

create table <table> (
    // 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(分区字段名)
PARTITIONS <count>;

子分区

子分区(subpartition)：是分区表中对每个分区的再次分割，又被称为复合分区，支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录。

管理分区

mysql不禁止在分区键值上使用null,分区键可能是一个字段或者一个用户定义的表达式，一般情况下，mysql的分区把null值当做零值或者一个最小值进行处理。range分区中，null值会被当做最小值来处理；list分区中null值必须出现在枚举列表中，否则不被接受；hash/key分区中,null值会被当做领值来处理。

mysql提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过alter table 命令来实现

-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;

-- 新增分区
-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);

-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));

-- hash重新分区
alter table <table> add partition partitions 4;

-- key重新分区
alter table <table> add partition partitions 4;

-- 子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的
alter table <table> add partition(partition p3 values less than MAXVALUE);

-- range重新分区
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);

-- list重新分区
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

分区优点

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

查看更多：https://blog.csdn.net/vbirdbest/article/details/82461109

MySQL分区表的局限和限制

https://blog.csdn.net/xiaohai1232/article/details/63687740

注意事项

https://blog.csdn.net/ljl890705/article/details/78490819