大家好,又见面了,我是你们的朋友全栈君。
前言:分区是指根据一定的规则,数据库把一个表分解成多个更小的,更容易管理的部分。分区对应用来说是完全透明的,不影响应用的业务逻辑。
MySQL分区的优点:
1、和单个磁盘或者文件系统分区相比,可以存储更多数据;
2、优化查询。在Where字句中包含分区条件时,可以只扫描必要的一个或多个分区来提高查询效率;同时在涉及 SUM() 和 COUNT() 等聚合函数的查询时,可以容易地在每个分区上并行处理,最终只需要汇总所有分区得到的结果。
3、对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据。
4、跨多个磁盘来分散数据查询,以获得更大的查询吞吐量。
一:概述
MySQL 支持使用大部分存储引擎(如:MyISAM、InnoDB、Memory等)创建分区表,不支持使用MERGE和CSV。
MySQL分区类型主要包括:range分区、list分区、hash分区、key分区;
无论是那种MySQL分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,也就是说不能使用主键/唯一键字段之外的其他字段分区。
如:
去掉主键约束后,创建表会成功:
分区的名字遵循MySQL标识符的原则。分区的名字不区分大小写,如果分区名分别为 mypart 和 MyPart 将会被MySQL认为是同一个分区而报错。
二:RANGE分区
按照range分区的表是利用取值范围将数据分区,区间要连续并且不能互相重叠,使用 values less than 操作符进行分区定义。
如果增加商店ID大于等于30的行,超出分区范围,会出现错误。
可以使用 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 值处理
1、MySQL 分区不禁止在分区键值上使用 null
2、Range 分区中,null 值会被当做最小值来处理
3、List 分区中,null 值必须出现在枚举列表中,否则不被接受
4、Hash/Key 分区中,null 值会被当做零值来处理
5、为了避免在处理 null 值时出现误判,推荐通过设置字段非空和默认值来绕开 MySQL 对 null 值的默认处理
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/131832.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...