mysql8分区_mysql8中的表分区

最新推荐文章于 2022-11-24 15:39:01 发布

我甜死了

最新推荐文章于 2022-11-24 15:39:01 发布

阅读量2.5k

点赞数 2

文章标签： mysql8分区

本文链接：https://blog.csdn.net/weixin_35867979/article/details/113684407

版权

本文基于Mysql for windows 8.0.12版本

分表与表分区的区别：

分表

直接按照预定的规则，把表拆分。业务操作时需要计算完成的表名。

分区

MySQL从5.1开始支持表的水平分区的。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。对于开发人员来说，这是无感知的，不需要关心数据库到底如何操作的。在ORM、Model中操作时比较方便。

mysql本身支持的主要的分区方式

有很多中间件，比如mycat, 支持分区, 分表, 读写分离, 故障转移等功能。

MySQL支持HASH分区、RANGE分区、LIST分区、KEY分区。

Hash分区：对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目)，或直接使用分区语句,比如partition p0 values in…..。

Range分区：是对一个连续性的行值，按范围进行分区；比如：id小于100；id大于100小于200；

List分区：跟range分区类似，不过它存放的是一个离散值的集合。

Key分区：与hash分区类似，只不过分区支持一列或多列，并且MySQL服务器自身提供hash函数。

如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

HASH分区

MYSQL支持两种HASH分区，常规HASH(HASH)和线性HASH(LINEAR HASH) 。

常规HASH

常规hash是基于分区个数的取模(%)运算。根据余数插入到指定的分区。

CREATE TABLE orders2(

order_id int unsigned NOT NULL AUTO_INCREMENT,

shop_id int unsigned NOT NULL,

amount decimal(10,2) NOT NULL,

primary key(order_id, shop_id) -- 主键必须包含分区键

) engine=InnoDB PARTITION BY HASH(shop_id) PARTITIONS 10;

这个表中我们按店铺id对10取模来规划分区。

填充模拟数据:

-- 填充模拟数据

DROP PROCEDURE IF EXISTS orders2_fill;

DELIMITER $$

CREATE PROCEDURE orders2_fill(in shop_id_from int, in count int)

BEGIN

DECLARE i int DEFAULT 0;

DECLARE shop_id1 int;

DECLARE shop_id2 int;

DECLARE shop_id3 int;

DECLARE shop_id4 int;

DECLARE shop_id5 int;

DECLARE amount1 decimal(10,2);

DECLARE amount2 decimal(10,2);

DECLARE amount3 decimal(10,2);

DECLARE amount4 decimal(10,2);

DECLARE amount5 decimal(10,2);

WHILE i < count DO

SET shop_id1 = shop_id_from + i * 5 + 0;

SET shop_id2 = shop_id_from + i * 5 + 1;

SET shop_id3 = shop_id_from + i * 5 + 2;

SET shop_id4 = shop_id_from + i * 5 + 3;

SET shop_id5 = shop_id_from + i * 5 + 4;

SET amount1 = 1000 + Round(rand() * 10000,2);

SET amount2 = 1100 + Round(rand() * 10000,2);

SET amount3 = 1200 + Round(rand() * 10000,2);

SET amount4 = 1300 + Round(rand() * 10000,2);

SET amount5 = 1400 + Round(rand() * 10000,2);

INSERT INTO orders2(shop_id, amount)VALUES

(shop_id1,amount1),(shop_id2,amount2),(shop_id3,amount3),(shop_id4,amount4),(shop_id5,amount5);

SET i = i + 1;

END WHILE;

END$$

DELIMITER ;

CALL orders2_fill(1, 1000); # 1-5000

查看分区情况:

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM `information_schema`.`PARTITIONS`

WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='orders2';

可以看到上面插入的数据比较均匀的分布在各个partition中。

也支持根据公式计算分区规则

CREATE TABLE `orders` (

`id` INT NOT NULL,

`order_date` DATE NOT NULL DEFAULT '1970-01-01' COMMENT '下单日期',

)

PARTITION BY HASH( YEAR(order_date) )

PARTITIONS 10;

查看sql查询使用的分区

EXPLAIN SELECT * FROM orders2 WHERE shop_id=201;

msyql8中移除了 EXTENDED, PARTITIONS 关键字, 不需要再写: EXPLAIN PARTITIONS

线性HASH(LINEAR HASH)

LINEAR HASH和HASH的唯一区别就是PARTITION BY LINEAR HASH.

CREATE TABLE orders3 (

id INT NOT NULL AUTO_INCREMENT,

order_date DATE NOT NULL DEFAULT '2020-01-01' COMMENT '下单日期',

PRIMAEY KEY(id, order_date)

) engine=InnoDB PARTITION BY LINEAR HASH( YEAR(order_date) ) PARTITIONS 6;

线性HASH的计算原理比较复杂, 这里忽略.

分区管理

常规hash的分区非常的简便，通过取模的方式可以让数据非常平均的分布每一个分区，但是由于分区在创建表的时候已经固定了。如果新增或者收缩分区的数据迁移比较大。

常规HASH和线性HASH的增加收缩分区的原理是一样的。增加和收缩分区后原来的数据会根据现有的分区数量重新分布。HASH分区不能删除分区，所以不能使用DROP PARTITION操作进行分区删除操作；

只能通过ALTER TABLE ... COALESCE PARTITION num来合并分区，这里的num是减去的分区数量；

可以通过ALTER TABLE ... ADD PARTITION PARTITIONS num来增加分区，这里是null是在原先基础上再增加的分区数量。

CREATE TABLE orders3(

id int NOT NULL AUTO_INCREMENT,

shop_id int NOT NULL,

primary key(id, shop_id)

) engine=InnoDB PARTITION BY HASH(shop_id) PARTITIONS 5;

INSERT INTO orders3(shop_id)

VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM `information_schema`.`PARTITIONS`

WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='orders3';

新增分区: 我们给表orders3增加4个分区

ALTER TABLE orders3 add PARTITION partitions 4;

因为牵涉到数据重新分区, 执行会比较耗时

调整分区后, 数据都还在, 但是查看分区表信息没有看到统计数据

INSERT INTO orders3(shop_id)VALUES(20),(21);

插入2笔数据后

目前测试的windows版本的mysql(8.0.12)是有这个统计数据更新的bug

缩减分区

我们再给表orders3减少3个分区, 使用 COALESCE:

ALTER TABLE orders3 COALESCE PARTITION 3;

移除表的分区

ALTER TABLE orders3 REMOVE PARTITIONING;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

移除分区后, 分区文件会被移除, 合并到一个文件里面去.

RANGE 分区

RANGE分区模式下, 行数据基于属于一个给定的连续区间的列值被放入分区。

当插入的数据不在一个分区中定义的值的时候，会抛出异常。

RANGE分区主要用于日期列的分区，比如交易表，销售表等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。

CREATE TABLE `orders` (

`order_id` INT NOT NULL AUTO_INCREMENT COMMENT '订单号',

`order_month` INT NOT NULL COMMENT '下单月份, 格式:yyyymm',

`amount` DECIMAL(10,2) NULL,

PRIMARY KEY (`order_id` , `order_month`)

) engine = InnoDB

PARTITION BY RANGE (order_month) PARTITIONS 6 (

PARTITION part0 VALUES LESS THAN (202001) ,

PARTITION part1 VALUES LESS THAN (202002) ,

PARTITION part2 VALUES LESS THAN (202003) ,

PARTITION part3 VALUES LESS THAN (202004) ,

PARTITION part4 VALUES LESS THAN (202005),

PARTITION part5 VALUES LESS THAN (202006)

);

插入数据:

INSERT INTO `orders`(order_month, amount)

VALUES(202001, 500+round(rand()*1000,0)),(202001, 500+round(rand()*1000,0)),

(202002, 500+round(rand()*1000,0)),(202002, 500+round(rand()*1000,0)),

(202003, 500+round(rand()*1000,0)),(202003, 500+round(rand()*1000,0)),

(202004, 500+round(rand()*1000,0)),(202004, 500+round(rand()*1000,0)),

(202005, 500+round(rand()*1000,0)),(202005, 500+round(rand()*1000,0));

上面的数据顺利插入.

插入数据时注意: 如果插入了分区未包含的下单日期, 比如 202006, 则会报错: "Error Code: 1526. Table has no partition for value 202006", 因为分区定义中的"LESS THEN 2006" 并不包含 202006。这种情况下，如果我们不知道上限是多少, 可以在最后添加一个 MAXVALUE 分区，如下：

ALTER TABLE `orders` ADD PARTITION (PARTITION p_max VALUES LESS THAN MAXVALUE);

分区操作

增加分区

我们现在要增加一个202006月份的数据分区, 按如下操作即可:

ALTER TABLE `orders` ADD PARTITION (PARTITION p6 VALUES LESS THAN (202006));

这种range的分区方式，不会像前面一部分的HASH分区那样需要进行数据的移动。我们只要这样简单的增加一个分区，即可实现即时扩展来满足业务需求。

删除分区

注意: 使用 drop删除分区, 会直接删除分区对应的数据库文件, 相关的数据也会丢失, 必须慎重操作。

可能对于在转移旧数据后, 删除旧数据比较适用, 速度比delete from table快太多了。

alter table employees drop PARTITION p4;

拆分合并分区

拆分与合并, 都使用 REORGANIZE PARTION p_name INTO () .

我们准备把202001之前的数据拆分成2018和2019两个年份, 先插入这2个年份的数据:

INSERT INTO `orders`(order_month, amount)

VALUES(201801, 500+round(rand()*1000,0)),(201802, 500+round(rand()*1000,0)),

(201901, 500+round(rand()*1000,0)),(201902, 500+round(rand()*1000,0))

我可以看到数据都在p0分区下. 现在我来拆分p0:

ALTER TABLE orders REORGANIZE PARTITION part0 INTO (

PARTITION p2018 VALUES LESS THAN (201901),

PARTITION p2019 VALUES LESS THAN (202001)

);

我可以看到分区p0已经不存在了.

现在来看下数据查询:

那我们现在再把p2018和p2019合并为p0:

ALTER TABLE orders REORGANIZE PARTITION p2018,p2019 INTO (

PARTITION p0 VALUES LESS THAN (202001)

);

查询结果证明已经合并成功.

参考:

MySQL HASH分区

MySQL RANGE分区

我甜死了

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql8分区_mysql8中的表分区

本文基于Mysql for windows 8.0.12版本分表与表分区的区别：分表直接按照预定的规则，把表拆分。业务操作时需要计算完成的表名。分区MySQL从5.1开始支持表的水平分区的。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。对于开发人员来说，这是无感知的，不...
复制链接

扫一扫