当前位置： 首页 > news >正文

mysql8分区_mysql8中的表分区

news 来源：原创 2024/4/29 14:45:33

本文基于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';

1390080a670517e5335b2e6c396ee048.png

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

ALTER TABLE orders3 add PARTITION partitions 4;

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

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

daa8c477bb8b8a42e2f679a61b6f957f.png

0f2ab9067a9ea83695605ec29a6e2037.png

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

插入2笔数据后

485578e8e52cd48e958e8dcc854bbdf9.png

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

缩减分区

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

ALTER TABLE orders3 COALESCE PARTITION 3;

fb8716a7b972372117cc5d29c424e1eb.png

移除表的分区

ALTER TABLE orders3 REMOVE PARTITIONING;

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

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

a5c39671e467512f637e3db88266fc16.png

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))

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

ALTER TABLE orders REORGANIZE PARTITION part0 INTO (

PARTITION p2018 VALUES LESS THAN (201901),

PARTITION p2019 VALUES LESS THAN (202001)

);

e9be5948252e3b868121e4620dc22194.png

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

现在来看下数据查询:

54c5a0e408281dd9bec14d63d0f986ef.png

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

ALTER TABLE orders REORGANIZE PARTITION p2018,p2019 INTO (

PARTITION p0 VALUES LESS THAN (202001)

);

e1f21f737398f90fa4f25c3553c79f6f.png

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

参考:

MySQL HASH分区

MySQL RANGE分区

相关文章：

  • ztree 后台异步加载_Ztree 分批异步加载
  • linux执行mysql表_linux shell命令执行sql(mysql入门)
  • python 数组逆序重放_OpenJudge计算概论-找最大数序列
  • python爬虫处理滑块验证_python爬虫基础（9：验证识别之滑块验证）
  • qmediaplayer进度_QMediaPlayer的duration问题
  • java timer定时执行一次_Java Timer（定时调用、实现固定时间执行）
  • java字串数组_java字符串数组
  • java swing 拖拽文件夹_Java Swing 鼠标拖放文件 代码1
  • java treemap 降序排序_Java TreeMap 升序|降序排列
  • java 流关闭顺序_JAVA的节点流和处理流以及流的关闭顺序
  • java 多层结构故障_多层构架在实践中一些问题
  • java项目提高安全性_Java线程安全与程序性能
  • mysql 获取真是执行计划_Oracle 从缓存里面查找真实的执行计划
  • mysql 越文_mysql数据库乱码之保存越南文乱码解决方法_MySQL
  • java发布_java项目发布的方式
  • 收藏网友的 源程序下载网
  • 【EOS】Cleos基础
  • 【vuex入门系列02】mutation接收单个参数和多个参数
  • CODING 缺陷管理功能正式开始公测
  • django开发-定时任务的使用
  • gitlab-ci配置详解(一)
  • js ES6 求数组的交集，并集，还有差集
  • LeetCode算法系列_0891_子序列宽度之和
  • Node + FFmpeg 实现Canvas动画导出视频
  • Selenium实战教程系列（二）---元素定位
  • TypeScript迭代器
  • 极限编程 (Extreme Programming) - 发布计划 (Release Planning)
  • 入手阿里云新服务器的部署NODE
  • 三分钟教你同步 Visual Studio Code 设置
  • 走向全栈之MongoDB的使用
  • ​创新驱动，边缘计算领袖：亚马逊云科技海外服务器服务再进化
  • ​一文看懂数据清洗：缺失值、异常值和重复值的处理
  • #AngularJS#$sce.trustAsResourceUrl
  • #HarmonyOS：基础语法
  • $ is not function 　　和JQUERY 命名 冲突的解说 Jquer问题 (
  • $.extend({},旧的,新的)；合并对象，后面的覆盖前面的
  • $var=htmlencode(“‘)；alert(‘2“)； 的个人理解
  • %@ page import=%的用法
  • （JSP）EL——优化登录界面，获取对象，获取数据
  • (NO.00004)iOS实现打砖块游戏(十二):伸缩自如,我是如意金箍棒(上)!
  • （Redis使用系列） Springboot 在redis中使用BloomFilter布隆过滤器机制 六
  • (TipsTricks)用客户端模板精简JavaScript代码
  • （附源码）计算机毕业设计SSM基于java的云顶博客系统
  • (原創) 如何安裝Linux版本的Quartus II? (SOC) (Quartus II) (Linux) (RedHat) (VirtualBox)
  • （转)IIS6 ASP 0251超过响应缓冲区限制错误的解决方法
  • (转)shell中括号的特殊用法 linux if多条件判断
  • (转)大型网站架构演变和知识体系
  • .bat批处理（四）：路径相关%cd%和%~dp0的区别
  • .NET Core 实现 Redis 批量查询指定格式的Key
  • .NET 中创建支持集合初始化器的类型
  • .net6使用Sejil可视化日志
  • .netcore 获取appsettings
  • .NET设计模式（2）：单件模式（Singleton Pattern）
  • @Autowired和@Resource的区别
  • @cacheable 是否缓存成功_Spring Cache缓存注解