MySql表分区介绍

最新推荐文章于 2023-09-16 00:43:11 发布

lichenruozi

最新推荐文章于 2023-09-16 00:43:11 发布

阅读量281

点赞数

分类专栏： MySql 文章标签：表分区

本文链接：https://blog.csdn.net/lichenruozi/article/details/86481624

版权

MySql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、什么是表分区

表分区就是就是把表的数据切分成不同的块进行存储，可以存储在不同的文件系统。而用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。

1）未分区表存储结构

执行脚本：

创建表employees ,查看存储如下图所示：

2）分区后表存储结构

为表employees 添加分区，执行脚本：
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
)

查看存储如下图所示：

通过SELECT * FROM employees;解释执行，结果如下：

通过结果可以看出，数据分布在p0,p1,p2,p3上，通过解释执行SELECT * FROM employees WHERE store_id=10;结果如下：

通过结果可以看出数据存储在p1上

二、分区的类型

MySql表分区共分为以下几种类型：

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

1）RANGE 分区

按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
)

备注：store_id小于6的数据存放在p0,store_id大于6小于11的数据存放在p1,store_id大于11小于16的数据存放在p2,store_id大于16存放在p3,MAXVALUE表示可能大于16的值。

2）LIST 分区

MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION p0 VALUES IN (3,5,6,9,17),
PARTITION p1 VALUES IN (1,2,10,11,19,20),
PARTITION p2 VALUES IN (4,12,13,14,18),
PARTITION p3 VALUES IN (7,8,15,16)
)

备注：store_id值属于(3,5,6,9,17)的数据位于p0分区中，store_id值属于(1,2,10,11,19,20)的数据位于p1分区中，store_id值属于(4,12,13,14,18)的数据位于p2分区中，store_id值属于(7,8,15,16)的数据位于p3分区中.

3）HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

备注：如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。

4）LINEAR HASH 分区

MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字，如下面所示：

5）KEY 分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。

6）子分区

子分区是分区表中每个分区的再次分割。

三、分区的管理

分区的管理就是对分区的增加，删除，修改及获取分区信息等的管理。所有这些操作都可以通过使用ALTER TABLE 命令的分区扩展来实现，下面分别举例说明：

1）从一个按照RANGE或LIST分区的表中删除一个分区，可以使用带一个DROP PARTITION子句的ALTER TABLE命令来实现。例如：

INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('1', '谢家华1', '梁书辉1', '2019-01-15', '2019-01-15', '1', '1');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('2', '谢家华2', '梁书辉2', '2019-01-15', '2019-01-15', '1', '1');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('3', '谢家华3', '梁书辉3', '2019-01-15', '2019-01-15', '2', '2');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('4', '谢家华4', '梁书辉4', '2019-01-15', '2019-01-15', '3', '3');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('5', '谢家华5', '梁书辉5', '2019-01-15', '2019-01-15', '4', '4');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('6', '谢家华6', '梁书辉6', '2019-01-15', '2019-01-15', '5', '5');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('7', '谢家华7', '梁书辉7', '2019-01-15', '2019-01-15', '6', '6');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('8', '谢家华8', '梁书辉8', '2019-01-15', '2019-01-15', '7', '7');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('9', '谢家华9', '梁书辉9', '2019-01-15', '2019-01-15', '8', '8');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('10', '谢家华10', '梁书辉10', '2019-01-15', '2019-01-15', '9', '9');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('13', '谢家华13', '梁书辉13', '2019-01-15', '2019-01-15', '12', '12');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('14', '谢家华14', '梁书辉14', '2019-01-15', '2019-01-15', '13', '13');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('15', '谢家华15', '梁书辉15', '2019-01-15', '2019-01-15', '14', '14');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('16', '谢家华16', '梁书辉16', '2019-01-15', '2019-01-15', '15', '15');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('12', '谢家华12', '梁书辉12', '2019-01-15', '2019-01-15', '19', '19');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('17', '谢家华17', '梁书辉17', '2019-01-15', '2019-01-15', '16', '16');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('18', '谢家华18', '梁书辉18', '2019-01-15', '2019-01-15', '17', '17');
INSERT INTO ``.`` (`id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id`) VALUES ('19', '谢家华19', '梁书辉19', '2019-01-15', '2019-01-15', '18', '18');

通过SELECT * FROM employees WHERE store_id < 6解释执行，可以看出，store_id<6的数据分布在p0分区，如图所示：

删除p0分区可通过执行脚本ALTER TABLE employees DROP PARTITION p0;再次查看数据，你会发现数据已被删除。

lichenruozi

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql表分区介绍

一、什么是表分区表分区就是就是把表的数据切分成不同的块进行存储，可以存储在不同的文件系统。而用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多...
复制链接

扫一扫