AI智能
改变未来
当前位置：爱站程序员基地 > 全栈知识 > MySQL > 正文

mysql基础（四）表分区

2021-06-09 分类：MySQL 评论(0)

表分区就是把一张表分成若干小表，管理起来更方便。
分区类型有四种：
1.range(范围分区)，例如：
建立表的同时按区域类型（range）分区，以字段age做为分区键，共三个分区，年龄范围20以内的为年轻，年龄40以内为中年，最大年龄以内为老年。
输入语句执行

CREATE TABLE rg(id INT,age INT)PARTITION BY RANGE (age)(PARTITION middle VALUES LESS THAN(40),PARTITION young VALUES LESS THAN(20),PARTITION OLD VALUES LESS THAN maxvalue)

结果报错：

根据错误提示必须要根据取值范围递增，也就是要按顺序取值。上面的语句是先取40以内，再取20以内，就是这两句:

PARTITION middle VALUES LESS THAN(40),PARTITION young VALUES LESS THAN(20),

顺序改过来再执行：

CREATE TABLE rg(id INT,age INT)PARTITION BY RANGE (age)(PARTITION young VALUES LESS THAN(20),PARTITION middle VALUES LESS THAN(40),PARTITION OLD VALUES LESS THAN maxvalue)

执行成功：

查看分区：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'rg\'

结果如下图：

貌似系统自动根据分区名排序了，可以加上ORDER BY partition_ordinal_position按分区顺序排序：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'rg\' ORDER BY partition_ordinal_position

结果如图：

三个分区一目了然。
分别查看指定分区：

SELECT * FROM rg PARTITION(young)SELECT * FROM rg PARTITION(middle)SELECT * FROM rg PARTITION(OLD)

结果分别如下图：



删除old分区：

ALTER TABLE rg DROP PARTITION OLD

再次查看表分区：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'rg\' ORDER BY partition_ordinal_position

查询结果可知删除成功：

添加分区：

ALTER TABLE rg ADD PARTITION(PARTITION laonian VALUES LESS THAN(60))

再次查看表的分区信息：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'rg\' ORDER BY partition_ordinal_position


添加成功。
删除young分区中的数据（不是删除分区）：

ALTER TABLE rg TRUNCATE PARTITION young

查看：

对young区进行拆分：

ALTER TABLE rg REORGANIZE PARTITION young INTO(PARTITION s1 VALUES LESS THAN(10),PARTITION s2 VALUES LESS THAN(20))

执行结果：

对middle和laonian两个分区进行合并：

ALTER TABLE rg REORGANIZE PARTITION middle,laonian INTO(PARTITION adult VALUES LESS THAN(60))

输入查询分区语句：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'rg\' ORDER BY partition_ordinal_position

结果：

range中less than(x)是取值范围，所以括号中的数字x不能为空否则会报错。range还支持日期型的字段，这里不做演示。
2.list（列表分区）：
list和range极其类似，不同的是list是从枚举列表中取值，而range是从连续区间集合中取值。下面创建一张表以YEAR(days)为分区键进行list分区，将日期转换成年后分为单年、双年和未知三个分区。

CREATE TABLE lt(id INT,days DATE)PARTITION BY LIST(YEAR(days))(PARTITION dannian VALUES IN(2011,2013,2015,2017,2019),PARTITION shuangnian VALUES IN(2012,2014,2016,2018,2020),PARTITION weizhi VALUES IN(NULL))

输入查询分区语句：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'lt\' ORDER BY partition_ordinal_position

结果如图：


可以看出List分区是没有顺序的，不像range分区从上往下按顺序递增。
插入数据：

INSERT INTO lt VALUES(1,\'2011-11-8\'),(2,NULL),(3,\'2018-10-9\'),(4,\'2016-01-05\'),(7,\'2015-03-15\'),(8,\'2020-01-03\')

查看分区数据：

SELECT * FROM lt PARTITION(dannian)SELECT * FROM lt PARTITION(shuangnian)SELECT * FROM lt PARTITION(weizhi)

依次执行结果如下：




list不同于range，是支持空值的。但是字段值不能在所有分区枚举值之外，例如执行如下语句：

INSERT INTO lt VALUES(9,\'2021-1-3\')

执行结果：

报错提示没有表中没有值为2021的分区，因为枚举值是2011-2020,还有空值Null，不包含2021，所以报错。这一点要注意。
list支持字段类型也与range相同，整形和日期型。
3.hash
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
创建过程如下：

CREATE TABLE hs(id INT,days DATE)PARTITION BY HASH(YEAR(days))PARTITIONS 4;

创建分区时不用指定具体取值，只要指定分区数量就行，如果不指定，默认值分区数量为1。
查询分区：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'hs\' ORDER BY partition_ordinal_position


结果是按指定分区数4分区的。
插入数据：

INSERT INTO hs VALUES(1,\'2011-11-8\'),(2,NULL),(3,\'2018-10-9\'),(4,\'2016-01-05\'),(7,\'2015-03-15\'),(8,\'2020-01-03\')

依次查询4个分区：

SELECT * FROM hs PARTITION(p0)SELECT * FROM hs PARTITION(p1)SELECT * FROM hs PARTITION(p2)SELECT * FROM hs PARTITION(p3)





hash的分区原理是mod函数对分区键对应的字段或表达式值与分区数量的求余运算，即mod(分区键对应列值（表达式值），分区数量)，也就是mod（year(days),4）。可以拿分在p0区的days=\’2016-01-05’测试，
执行如下语句：

SELECT MOD(YEAR(\'2016-01-15\'),4)

注：因为YEAR(‘2016-01-15’)=2015，所以MOD(YEAR(‘2016-01-15’),4)语句等同于mod(2016,4)。
结果：

再拿分在p2区的2018-10-9进行测试：

SELECT MOD(YEAR(\'2018-10-9\'),4)

返回结果：
null值取余运算也是null当成0分配在p0区也是理所当然了。
尝试删除分区：

ALTER TABLE hs DROP PARTITION p3

结果报错：

删除分区只能在range和list分区使用。
以上是常规哈希，还有线性哈希分区。这是官方文档说明：MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使用的是求哈希函数值的模数。

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字，如下所示：

CREATE TABLE line(id INT,days DATE)PARTITION BY LINEAR HASH(YEAR(days))PARTITIONS 4;

执行后查看：

SELECT * FROM information_schema.`PARTITIONS` WHERE table_name=\'line\'


插入数据：

INSERT INTO line VALUES(1,\'2011-11-8\'),(2,NULL),(3,\'2018-10-9\'),(4,\'2016-01-05\'),(7,\'2015-03-15\'),(8,\'2020-01-03\')

依次查询：

SELECT * FROM line PARTITION(p0)SELECT * FROM line PARTITION(p1)SELECT * FROM line PARTITION(p2)SELECT * FROM line PARTITION(p3)

结果分别如下：




线性哈希算法：

  1. 找到下一个大于num.的、2的幂，我们把这个值称为V ，它可以通过下面的公式得到：

  2. V = POWER(2, CEILING(LOG(2, num)))

（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。

  1. 设置 N = F(column_list) & (V – 1).

  2. 当 N >= num:

· 设置 V = CEIL(V / 2)

· 设置 N = N & (V – 1)
注：num是分区数量
拿p0中的days=2016-01-05进行测试：
执行

SELECT POWER(2, CEILING(LOG(2,4)))

求得V=4，N=F(column_list) & (V – 1)=year(2015-03-15) & (4-1)=2016 & 3，执行：

SELECT 2016 & 7

返回结果0：

再拿p3区的2015进行测试，直接执行：

SELECT 2015 & 3

结果为3：

以上几张分区表都没有主键或者唯一约束，不妨建一张测试效果：

CREATE TABLE NEW(id INT PRIMARY KEY,days DATE)PARTITION BY LINEAR HASH(YEAR(days))PARTITIONS 4;

结果报错：

分区改成常规hash：

CREATE TABLE NEW(id INT PRIMARY KEY,days DATE)PARTITION BY  HASH(YEAR(days))PARTITIONS 4;

还是报同样的错，再试试range分区：

CREATE TABLE NEW(id INT PRIMARY KEY,days DATE)PARTITION BY  RANGE(YEAR(days))(PARTITION p1 VALUES LESS THAN(2015),PARTITION p2 VALUES LESS THAN(2010))

依然报错：A PRIMARY KEY must include all columns in the table’s partitioning function，难道是主键的问题？
再试试List分区，把主键约束改成唯一约束：

CREATE TABLE NEW(id INT UNIQUE,days DATE)PARTITION BY  LIST(YEAR(days))(PARTITION p1 VALUES IN(2015),PARTITION p2 VALUES IN(2010))

还是报错：

这是为什么呢？根据报错信息：
A PRIMARY KEY must include all columns in the table’s partitioning
主键必须包括表的分区函数中的所有列。
A UNIQUE INDEX must include all columns in the table’s partitioning function
惟一的索引必须包括表的分区函数中的所有列。
接下来分别以主键和唯一约束字段做为分区键：

CREATE TABLE NEW(id INT PRIMARY KEY,days DATE)PARTITION BY  LIST(id)(PARTITION p1 VALUES IN(2,4),PARTITION p2 VALUES IN(1,3))
CREATE TABLE new1(id INT UNIQUE,days DATE)PARTITION BY  LIST(id)(PARTITION p1 VALUES IN(2,4),PARTITION p2 VALUES IN(1,3))

两张表都创建成功，原来在表中有主键约束的时候必须以主键字段为分区键，有唯一约束的时候同样以唯一约束字段做为分区键。那么假设一张表中主键约束和唯一约束同时存在如何分区呢？继续测试：
先以主键约束字段为分区键

CREATE TABLE new2(id INT PRIMARY KEY,cid INT UNIQUE,days DATE)PARTITION BY  LIST(id)(PARTITION p1 VALUES IN(2,4),PARTITION p2 VALUES IN(1,3))

执行结果：

那么试下用唯一约束字段做为分区键：

CREATE TABLE new2(id INT PRIMARY KEY,cid INT UNIQUE,days DATE)PARTITION BY  LIST(cid)(PARTITION p1 VALUES IN(2,4),PARTITION p2 VALUES IN(1,3))

执行结果：

可以这样讲，当有主键约束、唯一约束等唯一性索引存在于表中的时候，分区键必须是它们的代表（被约束的字段），两者同时存在的情况下代表两者。如下例：

CREATE TABLE new2(id INT,cid INT,days DATE,PRIMARY KEY(id),UNIQUE(id,cid))PARTITION BY  LIST(id)(PARTITION p1 VALUES IN(2,4),PARTITION p2 VALUES IN(1,3))

分区键id既是主键，又属于唯一约束中的一个字段，可以说它能代表两者，执行成功：

4.key分区
与hash类似，区别在于key可以不用指定分区键，在表中主键和唯一键同时存在的情况下，会自动选用兼具两种约束的字段（也就是在前面所说的代表）做为分区键，如下图：
id是主键也是唯一键

CREATE TABLE new4(id INT,cid INT NOT NULL,days DATE,PRIMARY KEY(id),UNIQUE(cid,id))PARTITION BY KEY()PARTITIONS 4;

上述情况只有唯一键是复合键，如果主键和唯一键都是复合键并且里面字段多的情况下不手动指定分区键容易报错。
表中只存在主键情况下会自动选择主键做为分区键：

CREATE TABLE new5(id INT,cid INT NOT NULL,days DATE,PRIMARY KEY(id))PARTITION BY KEY()PARTITIONS 4;

没有主键情况下，选择唯一键做为分区键：

CREATE TABLE new6(id INT,cid INT NOT NULL,days DATE,UNIQUE(cid))PARTITION BY KEY()PARTITIONS 4;

但是唯一键必须是非空，不然报错，如下所示：

CREATE TABLE new7(id INT,cid INT,days DATE,UNIQUE(cid))PARTITION BY KEY()PARTITIONS 4;


只是少了个not null就建表失败，这种情况下要么在唯一键字段加上not null，要么手动指定分区键，如下：

CREATE TABLE new7(id INT,cid INT,days DATE,UNIQUE(cid))PARTITION BY KEY(cid)PARTITIONS 4;

手动指定了分区键cid，执行成功。
5.子分区
子分区是分区表中每个分区的再次分割，可以用于特别大的表，在多个磁盘间分配数据和索引。创建过程如下：

CREATE TABLE new9(id INT,days DATE)PARTITION BY RANGE(YEAR(days))SUBPARTITION BY HASH(TO_DAYS(days))SUBPARTITIONS 2(PARTITION p1 VALUES LESS THAN(2010),PARTITION p2 VALUES LESS THAN(2015),PARTITION p3 VALUES LESS THAN(2020))

查看分区：

查看结果显示共有三个大分区p1、p2、p3，6个小分区，也就是建立了三个range分区，而每个range分区下有2个hash小分区（小分区只指定了数量自动生成的所以名字默认），就像一个二维数组，int[3][2].
也可以指定具体子分区表名，如下：

CREATE TABLE new10(id INT,days DATE)PARTITION BY RANGE(YEAR(days))SUBPARTITION BY HASH(TO_DAYS(days))(PARTITION p1 VALUES LESS THAN(2010)(SUBPARTITION s1,SUBPARTITION s2),PARTITION p2 VALUES LESS THAN(2015)(SUBPARTITION s3,SUBPARTITION s4),PARTITION p3 VALUES LESS THAN(2020)(SUBPARTITION s5,SUBPARTITION s6))

查询分区：

注意每个大分区里的小分区数量必须是相同的，所以指定具体的小分区时必须要写完整，像下面这样是会报错的：
CREATE TABLE new11(
id INT,
days DATE
)
PARTITION BY RANGE(YEAR(days))
SUBPARTITION BY HASH(TO_DAYS(days))
(
PARTITION p1 VALUES LESS THAN(2010)
(
SUBPARTITION s1,
SUBPARTITION s2
),
PARTITION p2 VALUES LESS THAN(2015)(
SUBPARTITION s3,
SUBPARTITION s4
),
PARTITION p3 VALUES LESS THAN(2020)
)

还有用来分小区的是subpartition,指定小区数量的是subpartitions，不要拼错。

赞(0) 打赏
未经允许不得转载：爱站程序员基地 » mysql基础（四）表分区
标签：

相关推荐

热门文章

热门标签

源码 (2093)Java开发 (1438)Python开发 (1301)vps商家 (1289)夸克网盘 (1273)阿里云盘 (1267)Linux (1189)JavaScript (1119)PHP开发 (1058)计算机网络 (1055)MySQL (1015)Go语言 (985)UI/UE (975)CSS (970)人工智能 (933)Shell (930)安卓 (891)Android开发 (873)C# (861)ASP (815)网站架构 (798)IOS开发 (782)JQuery (759)Oracle (684)网络资源 (575)实用工具 (267)微信 (250)C语言/C++ (245)百度网盘 (208)github (132)