MySQL Partition

最新推荐文章于 2024-05-05 13:31:50 发布

lcz523

最新推荐文章于 2024-05-05 13:31:50 发布

阅读量219

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 java Powered by 金山文档

本文链接：https://blog.csdn.net/lcz523/article/details/128533073

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前言：分区是指根据一定的规则，数据库把一个表分解成多个更小的，更容易管理的部分。分区对应用来说是完全透明的，不影响应用的业务逻辑。

MySQL分区的优点：

和单个磁盘或者文件系统分区相比，可以存储更多数据；

优化查询。在Where字句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及 SUM() 和 COUNT() 等聚合函数的查询时，可以容易地在每个分区上并行处理，最终只需要汇总所有分区得到的结果。

对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

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

分区表的数据可以分布在不同的物理设备上,从而高效的利用多个硬件设备

和单个磁盘或者文件系统相比,可以存储更多数据

优化查询,在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果

分区表更容易维护,例如 : 想批量删除大量数据可以清楚整个分区

可以使用分区表来避免某些特殊的瓶颈,例如InnoDb的单个索引的互斥访问,ext3文件系统的竞争等

分区表的限制因素 :

1)一个表最多只能有1024个分区

2) Mysql5.1中,分区表达式必须是整数,或者返回整数的表达式,在Mysql5.5中提供了非整数表达式分区的支持

3)如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来,即 : 分区字段要么不包含主键或者索引列, 要么包含全部主键和索引列

4)分区表中无法使用外键约束

5) Mysql的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区

一：概述

MySQL 支持使用大部分存储引擎（如：MyISAM、InnoDB、Memory等）创建分区表，不支持使用MERGE和CSV。

MySQL分区类型主要包括：range分区、list分区、hash分区、key分区；

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

分区的名字遵循MySQL标识符的原则。

分区的名字不区分大小写。

二：RANGE分区

按照range分区的表是利用取值范围将数据分区，区间要连续并且不能互相重叠，使用 values less than 操作符进行分区定义。

可以使用 values less than maxvalue 设置分区，超出明确指定的分区值时，数据会存储在该分区，如：

MySQL支持在 values less than 字句中使用表达式 partition by range (year(separated))，可自行测试。类似的函数有 to_days()、to_seconds()；5.5版本后可直接使用日期字段作为分区键。

Range分区特别使用的两种情况：

1、当需要删除过期的数据时，只需要简单的 ALTER TABLE emp DROP PARTITION p0 来删除p0分区中的数据，对于具有上百万条记录的表来说，删除分区要比运行一个 DELETE 语句有效得多。

2、经常运行包含分区间的查询，MySQL 可以很快地确定只有一个或者某些分区需要扫描，因为其他分区不可能包含有该 WHERE 字句的任何记录。

三：List 分区

List 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，List 分区在很多方面类似于 Range 分区，区别在于 List 分区是从属于一个枚举列表的值得集合，Range 分区从属于一个连续区间值得集合。

如果插入的列值不包含分区值得列表中，insert 操作会失败报错。注意：List 分区不存在类似 values less than maxvalue 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中。

四：Hash 分区

Hash 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

MySQL 支持两种 Hash 分区：常规 Hash 分区、线性 Hash 分区(Linear Hash 分区)。常规 Hash 分区使用的是取模算法，线性 Hash 分区使用的是一个线性的2的幂的运算法则。

1、常规 Hash 分区

使用 PARTITION BY HASH(expr) PARTITIONS num 字句对分区类型、分区键和分区个数进行定义，其中 expr 是某列值或一个基于某列值返回的表达式。num 是一个非负整数，表示分区数量，默认为1。

假设将要保存记录的分区编号为N，那么 N=MOD(expr, num) 如，emp_hash表中有4个分区，插入一个 store_id列值为234的记录到表中：

MOD(234, 4)=2 保存这条记录的分区应该是p2；从p0 开始，模为 0 时保存到 p0 依次类推。

不推荐使用涉及多列的哈希表达式，复杂的表达式可能会引起性能问题。常规 Hash 在分区管理上需要的代价高，不适合需要灵活变动分区的需求。

2、线性 Hash 分区(Linear Hash)

线性 Hash 分区和常规 Hash 分区在语法上的唯一区别是在 partition by 字句中添加关键字 "Linear"，

线性Hash分区的优点是：在分区维护(增加、删除、合并、拆分分区时)，MySQL能够处理得更加迅速；缺点是：对比常规的Hash分区(取模)的时候，线性Hash各个分区之间数据的分布不太均衡。

五：Key 分区

Key 分区类似于 Hash 分区，不过 Key 分区不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 Hash 函数；同时 Hash 分区只支持整数分区，而 Key 分区支持使用 Blob 或 Text 类型外其他类型的列作为分区键

创建 Key 分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键

在没有主键的情况，会选择非空唯一键作为分区键，分区键的唯一键必须是非空的，如果不是非空会报错：

和 Hash 分区类似，在 Key 分区中使用关键字 Linear 具有同样的作用，Linear Key 分区时，分区的编号是通过2的幂算法得到的，不是通过取模得到的。

附：MySQL 分区的 null 值处理

MySQL 分区不禁止在分区键值上使用 null

Range 分区中，null 值会被当做最小值来处理

List 分区中，null 值必须出现在枚举列表中，否则不被接受

Hash/Key 分区中，null 值会被当做零值来处理

为了避免在处理 null 值时出现误判，推荐通过设置字段非空和默认值来绕开 MySQL 对 null 值的默认处理