mysql基础（四）表分区

程序员文章站 2022-03-16 23:06:48

表分区就是把一张表分成若干小表，管理起来更方便。分区类型有四种：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 VAL...

表分区就是把一张表分成若干小表，管理起来更方便。
分区类型有四种：
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
)

结果报错：
mysql基础（四）表分区
根据错误提示必须要根据取值范围递增，也就是要按顺序取值。上面的语句是先取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
)

执行成功：
mysql基础（四）表分区
查看分区：

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

结果如下图：
mysql基础（四）表分区
貌似系统自动根据分区名排序了，可以加上ORDER BY partition_ordinal_position按分区顺序排序：

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

结果如图：
mysql基础（四）表分区
三个分区一目了然。
分别查看指定分区：

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

结果分别如下图：
mysql基础（四）表分区

删除old分区：

ALTER TABLE rg DROP PARTITION OLD

再次查看表分区：

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

查询结果可知删除成功：
mysql基础（四）表分区
添加分区：

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

mysql基础（四）表分区
添加成功。
删除young分区中的数据（不是删除分区）：

ALTER TABLE rg TRUNCATE PARTITION young

查看：
mysql基础（四）表分区
对young区进行拆分：

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

执行结果：
mysql基础（四）表分区
对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

结果：
mysql基础（四）表分区
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

结果如图：

mysql基础（四）表分区
可以看出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)

依次执行结果如下：

mysql基础（四）表分区

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

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

执行结果：
mysql基础（四）表分区
报错提示没有表中没有值为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

mysql基础（四）表分区
结果是按指定分区数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)

mysql基础（四）表分区

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)。
结果：
mysql基础（四）表分区
再拿分在p2区的2018-10-9进行测试：

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

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

ALTER TABLE hs DROP PARTITION p3

结果报错：
mysql基础（四）表分区
删除分区只能在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'

mysql基础（四）表分区
插入数据：

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)

结果分别如下：
mysql基础（四）表分区

线性哈希算法：

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

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

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

设置 N = F(column_list) & (V - 1).
当 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：
mysql基础（四）表分区
再拿p3区的2015进行测试，直接执行：

SELECT 2015 & 3

结果为3：
mysql基础（四）表分区

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

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

结果报错：
mysql基础（四）表分区
分区改成常规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)
)

还是报错：
mysql基础（四）表分区
这是为什么呢？根据报错信息：
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)

)

执行结果：
mysql基础（四）表分区
那么试下用唯一约束字段做为分区键：

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)

)

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

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既是主键，又属于唯一约束中的一个字段，可以说它能代表两者，执行成功：
mysql基础（四）表分区
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;

mysql基础（四）表分区
只是少了个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)
)

查看分区：
mysql基础（四）表分区
查看结果显示共有三个大分区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
	)
)

查询分区：
mysql基础（四）表分区
注意每个大分区里的小分区数量必须是相同的，所以指定具体的小分区时必须要写完整，像下面这样是会报错的：
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)
)
mysql基础（四）表分区
还有用来分小区的是subpartition,指定小区数量的是subpartitions，不要拼错。

本文地址：https://blog.csdn.net/worilb/article/details/107615091

mysql基础（四）表分区

通过实例学习MySQL分区表原理及常用操作

MySQL分区表的局限和限制详解

MySQL入门(四) 数据表的数据插入、更新、删除

MySQL基础篇(02)：从五个维度出发，审视表结构设计

什么是mysql的分区表？什么是mysql的分库分表？为什么不建议mysql分区表？

MySQL分区表的正确使用方法

Mysql两表联合查询的四种情况总结

MySQL分表和分区的具体实现方法

Oracle表分区分为四种：范围分区，散列分区，列表分区和复合分区（转载）

Oracle实践--PL/SQL基础之表分区