经验分享|MySQL分区实战(RANGE)
概述
分区概述
在 MySQL 中, InnoDB存储引擎长期以来一直支持表空间的概念。在 MySQL 8.0 中,同一个分区表的所有分区必须使用相同的存储引擎。但是,也可以为同一 MySQL 服务器甚至同一数据库中的不同分区表使用不同的存储引擎。
通俗地讲表分区是将一大表,根据条件分割成若干个小表。MySQL 5.1开始支持数据表分区操作。为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率,我们做出了表分区的概念。表分区有如下优点:
存储更多的数据
对于失去保存意义的数据,删除有关的分区,很容易地删除那些数据,相比较delete语句和truncate
语句,删除分区更容易。
查询可以得到极大优化,虽然可以增加索引、主键和外键。
很容易地进行并行处理聚合函数SUM()和COUNT()
。
增加查询吞吐量。
重要:分区适用于表的所有数据和索引;您不能只对数据进行分区而不对索引进行分区,反之亦然,也不能只对表的一部分进行分区。
分区类型
MySQL 8.0
中可用的分区类型。其中包括此处列出的类型:
范围分区。 这种类型的分区根据落在给定范围内的列值将行分配给分区。
LIST 分区。 类似于分区 by RANGE
,不同之处在于分区是根据与一组离散值中的一个匹配的列来选择的。
哈希分区。 使用这种类型的分区,根据用户定义的表达式返回的值选择分区,该表达式对要插入表的行中的列值进行操作。
KEY分区。 这种类型的分区类似于分区 by HASH,只是只提供了一个或多个要评估的列,并且 MySQL 服务器提供了自己的散列函数。
列分区。包含列范围分区(RANGE COLUMNS partitioning)和列集合分区(LIST COLUMNS partitioning)。
子分区。子分区(也称为 复合分区(Subpartitioning))是对分区表中每个分区的进一步划分。
数据库分区的一个非常常见的用途是按日期分隔数据。一些数据库系统支持显式日期分区,而 MySQL 在 8.0 中没有实现。但是,在 MySQL 中创建基于[DATE]、 [TIME]、 或 [DATETIME]列或基于使用这些列的表达式的分区方案并不困难 。MySQL的分区是采用最优化 [TO_DAYS()], [YEAR()]和 [TO_SECONDS()]功能,也可以使用其他日期和时间函数返回一个整数或者NULL。
重要:要记住——无论您使用哪种分区类型——分区总是在创建时自动按顺序编号,从 0. 当新行插入到分区表中时,这些分区号用于标识正确的分区。
1 分区管理
RANGE 分区和 LIST 分区的管理的新增和删除差不多,下面我就用比较常用的RANGE 分区作为实战。
1.1 创建带有分区的表
可以在命令行执行,也可以在工具Navicat界面工具里面执行下面的语句,下面我将展示我在Navicat界面工具里面执行情况以及执行后返回的结果。
【插入数据脚本】
CREATE TABLE tb_tr (id INT COMMENT "ID编号", name VARCHAR(50) COMMENT "名称", purchased DATE COMMENT "购买时间",PRIMARY KEY (`id`, `purchased`) USING BTREE COMMENT "主键",INDEX `idx_id`(id) COMMENT "索引-id",INDEX `idx_name`(name) COMMENT "索引-名称",INDEX `idx_purchased`(purchased) COMMENT "索引-购买"
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic COMMENT '购买日志'
PARTITION BY RANGE( YEAR(purchased) ) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDBPARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);OK, Time: 0.055000s
【插入数据结果展示】
这里我创建了名称为p0、p1、p2、p3、p4、p5、pmax 六个分区
。其中p0和pmax分区
比较特别,这里可以理解为数学上面的分区函数或分段函数,从函数上,很好理解分区到底是什么概念。
1.2 插入几条数据
【插入数据脚本】
INSERT INTO tb_tr VALUES(1, 'desk organiser', '2003-10-15'),(2, 'alarm clock', '1997-11-05'),(3, 'chair', '2009-03-10'),(4, 'bookcase', '1989-01-10'),(5, 'exercise bike', '2014-05-09'),(6, 'sofa', '1987-06-05'),(7, 'espresso maker', '2011-11-22'),(8, 'aquarium', '1992-08-04'),(9, 'study desk', '2006-09-16'),(10, 'lava lamp', '1998-12-25');Affected rows: 10, Time: 0.004000s
【插入数据结果展示】
1.3 查看分区内的数据
下面的结果是一样的,但是效果不一样。可以使用EXPLAIN查看下执行计划。
SELECT * FROM tb_tr PARTITION (p2);SELECT * FROM tb_tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
1.4 删除分区
删除分区时,也会删除该分区中存储的所有数据。必须先拥有该[DROP](表的 权限,然后才能 ALTER TABLE ... DROP PARTITION
对该表执行。
ALTER TABLE tb_tr DROP PARTITION p2;
OK, Time: 0.022000s
那有没有可以在不删除数据的情况下,删除分区呢?答案是有的,请使用ALTER TABLE ... REORGANIZE PARTITION
改用。对于按范围分区的表,您只能 ADD PARTITION
将新分区添加到分区列表的高端。这就意味着,需要从pmax
分区再次分出来一张表空间,例如
1.5 增加分区
1.5.1 增加分区
如果使用这种方式增加分区,那么你得到的将会是错误的提示
1481 - MAXVALUE can only be used in last partition definition, Time: 0.002000s
1.5.2 重新组织为两个新分区
ALTER TABLE tb_trREORGANIZE PARTITION pmax INTO (PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB, PARTITION n1 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
在增加分区时,新的RANGE分区方案不能有任何重叠范围;新的LIST 分区方案不能有任何重叠的值集。分区的表RANGE,您只能重组相邻的分区;您不能跳过范围分区。分区也不是无限制的分区下去,不使用[NDB]存储引擎的给定表的最大可能分区数是 8192。
1.6 查询已经创建分区
【查询数据脚本展示】
SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema = 【dbName】 and table_name = "【tableName】";
【查询数据结果展示】
2 分区限制条件
2.1 禁止的构造。
分区表达式中不允许使用以下结构:
存储过程、存储函数、可加载函数或插件。
声明的变量或用户变量。
当然下面自带的函数除外:
ABS() CEILING() DATEDIFF() DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() EXTRACT() FLOOR() HOUR() MICROSECOND() MINUTE() MOD() MONTH() 常用 QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() 常用 TO_SECONDS() UNIX_TIMESTAMP() (with TIMESTAMP columns) WEEKDAY() YEAR() YEARWEEK()
上面的函数可以在分区中使用,这样子就可以按照需求来制定自己的分区。
2.2 允许在分区中使用运算符
算术、逻辑运算符
允许在分区表达式中 使用算术运算符 +、 -和 *
。但是,结果必须是整数值或NULL
位运算符 | 、&、 ^、 <<、 >>、 ~
不允许在分区表达式中使用。
分区 InnoDB 表不支持外键
全文索引。 分区表不支持FULLTEXT 索引或搜索。
空间列。 具有空间数据类型(例如POINT 或GEOMETRY不能在分区表中使用)的列。
临时表。 临时表不能分区。
日志表。 无法对日志表进行分区;[ALTER TABLE ... PARTITION BY ...]
此类表上的 语句因错误而失败。
分区键的数据类型。 分区键必须是整数列或解析为整数的表达式。[ENUM]不能使用使用列的表达式 。列或表达式值也可能是NULL; 例外情况参考官网。
F&Q
有些时候,写着,写着,也会翻车,例如,我在实际操作过程中遇到很多问题,但是有了网络之后,就开始搜罗,一点点解决。
Q1:定时处理这些数据
需求描述:
我搜索了一番,将上述的表分区整理成为了按照月度来进行调度分区,然后根据月度来将3个月前的数据迁移到第三张表history表,history是基本上不使用的表,结构同业务表。
具体的思路:
1:创建相同结构的表;
2:创建一个函数,这个函数用于分区
3:创建一个事件,在每月的1号调用分区函数,创建分区,这个分区是两张表(业务表和业务_history表)
4:创建一个函数,用于查询业务数据插入到业务历史表,并删除业务表的数据和分区
5:创建一个事件,用于每月1号调用处理数据函数,迁移数据、删除分区
Q2:SQL语句、单词拼写错误
问题描述:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘NULL’ at line 1
多数情况下,应该是SQL脚本写错了,单词拼写错了,或者SQL语句中的分割、拼接有错误,导致不能执行,这个需要多检查下,然后再次执行即可。我就是在拼接SQL时忘记写了空格,导致被执行的SQL。例如我下面的的拼接SQL的where前面么有空格,导致SQL是一个不可以执行的语句
CONCAT('INSERT INTO ', TO_TABLE, ' SELECT * FROM ', FROM_TABLE, 'WHERE data_date < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);');
Q3:权限问题
问题描述:
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
这个很明显,是权限的问题。我当时在一个库执行脚本,提示我这个权限问题,换到一个连接权限大一些即可,或给当前连接用户权限大一些。
相关文章:

经验分享|MySQL分区实战(RANGE)
概述 分区概述 在 MySQL 中, InnoDB存储引擎长期以来一直支持表空间的概念。在 MySQL 8.0 中,同一个分区表的所有分区必须使用相同的存储引擎。但是,也可以为同一 MySQL 服务器甚至同一数据库中的不同分区表使用不同的存储引擎。 通俗地讲…...

Arrays.asList() 和 Collections.singletonList()
Arrays.asList() 和 Collections.singletonList() 概述 List 是我们使用Java时常用的集合类型。众所周知,我们可以轻松地在一行中初始化列表。例如,当我们想要初始化一个只有一个元素的List时,我们可以使用Arrays.asList(&#…...

Firmware Analysis Plus (Fap)固件模拟安装教程(最新)
最近在搞IoT的研究,但是难在设备比较难弄,只有固件,而没有设备,买吧,又太费钱,不划算。好在有很多项目可以在模拟环境中运行固件。但是几乎没有一个平台能够模拟所有硬件设备。IoT产品的架构也不尽相同。 …...

使用包、Crate 和模块管理项目(上)
目录 1、包和Crate 2、定义模块来控制作用域与私有性 2.1 在模块中对相关代码进行分组 3、引用模块项目的路径 3.1 使用 pub 关键字暴露路径 二进制和库 crate 包的最佳实践 3.2 super 开始的相对路径 3.3 创建公有的结构体和枚举 Rust 有许多功能可以让你管理代码的组…...
【Kotlin】
Lambda 就是一小段可以作为参数传递的代码。 因为正常情况下,我们向某个函数传参时只能传入变量,而借助Lambda 却允许传入一小段代码。 Lambda 表达式的语法结构: {参数名1: 参数类型, 参数名2: 参数类型 -> 函数体}首先,最外…...

JavaDay17
创建不可变集合 import java.util.Iterator; import java.util.List;public class Test {public static void main(String[] args) {/*创建不可变的List集合* "张三" "李四" "王五" "赵六*///一旦创建之后 是无法进行修改的 在下面的代码…...

Python爬取酷我音乐
🎈 博主:一只程序猿子 🎈 博客主页:一只程序猿子 博客主页 🎈 个人介绍:爱好(bushi)编程! 🎈 创作不易:喜欢的话麻烦您点个👍和⭐! 🎈…...
项目实战第四十七讲:易宝支付对接详解(保姆级教程)
易宝支付对接(保姆级教程) 为了实现项目的支付需求,公司选择了易宝支付进行对接,本文是项目实战第四十七讲,详解易宝支付对接。 文章目录 易宝支付对接(保姆级教程)1、需求背景2、流程图3、技术方案4、相关接口4.1、入驻相关(商户入网)4.2、账户相关接口(充值、提现、…...

python的websocket方法教程
WebSocket是一种网络通信协议,它在单个TCP连接上提供全双工的通信信道。在本篇文章中,我们将探讨如何在Python中使用WebSocket实现实时通信。 websockets是Python中最常用的网络库之一,也是websocket协议的Python实现。它不仅作为基础组件在…...
Qt处理焦点事件(获得焦点,失去焦点)
背景: 我只是想处理焦点动作,由于懒,上网一搜,排名靠前的一位朋友,使用重写部件的方式实现。还是因为懒,所以感觉复杂了。于是又花了一分钟解决了一下。 所以记录下来,以免以后忘了。 思路&a…...

SiteGround如何设置WordPress网站自动更新
SiteGround Autoupdate功能会自动帮我们更新在他们这里托管的所有WordPress网站,这样做是为了保证网站安全,并且让它们一直保持最新状态。他们会根据我们选择的设置自动更新不同版本的WordPress,包括主要版本和次要版本。在每次自动更新之前&…...
http代理和SOCK5代理谁更安全?
在这个网络化的时代,我们常常听到HTTP代理和SOCKS5代理这两个名词,不过很多人并不了解是什么意思。今天,我们将揭开这两种代理的神秘面纱,看看到底HTTP代理和SOCKS5代理哪个更安全? HTTP代理:高效通信的“枢…...
Kotlin关键字二——constructor和init
在关键字一——var和val中最后提到了构造函数,这里就学习下构造函数相关的关键字: constructor和init。 主要构造(primary constructor) kotlin和java一样,在定义类时就自动生成了无参构造 // 会生成默认的无参构造函数 class Person{ }与java不同的是…...

java的long类型超过9位报错:the literal 987654321000 of type int is out of range
java的long类型超过9位报错 1、报错提示2、报错截图3、解决办法4、参考文章 1、报错提示 the literal 987654321000 of type int is out of range 2、报错截图 3、解决办法 long类型是一种用于表示较大整数的数据类型,范围比int类型更广泛。然而,即使…...
【Java期末复习资料】(4)模拟卷
有不会的题可以后台问我的哦,看见了就会回。 本文章主要是模拟卷,知识点例题简答题已经发过了,可以在主页专栏Java中找一下 一、单项选择题 1. 编译 Java Application 源程序文件将产生相应的字节码文件,这些字节码文件的扩展名为…...

【计算机网络】UDP报文详解
目录 一. UDP协议概述 二. UDP报文格式 首部 三. UDP的缓冲区 一. UDP协议概述 UDP——用户数据报协议,是传输层的一个重要协议 基于UDP的应用层协议有:DNS,TFTP,SNMP,NTP 协议全称默认端口号DNSDomain Name Se…...
排序算法——归并排序
归并排序(Merge Sort)是计算机科学中非常重要的排序算法之一。它不仅高效、稳定,而且是许多高级排序技术和算法思想的基础。在本文中,我们将深入探讨归并排序的原理、实现方法,以及它的优缺点。 1. 归并排序的原理 归…...
2023 年安徽省职业院校技能大赛高职组“软件测试”赛项样题
2023 年安徽省职业院校技能大赛 高职组“软件测试”赛项样题 目录 任务一:功能测试(45 分) 1、测试计划(5 分) 2、测试用例(15 分) 3、Bug 清单(20 分) 4、测试报告&…...
Mysql8和Oracle实际项目中递归查询树形结构
背景: 项目升级,引入MySQL数据库,之前一直用的是Oracle数据,在做用户登录单位维护的时候,需要返回该用户所属单位下的所有子单位。下边是模拟项目数据实践的过程。 数据准备: 准备一张单位表,…...

docker mysql8 设置不区分大小写
docker安装Mysql8.0的坑之lower_case_table_names_docker mysql lower_case_table_names-CSDN博客https://blog.csdn.net/p793049488/article/details/108365929 docker run ‐di ‐‐nametensquare_mysql ‐p 33306:3306 ‐e MYSQL_ROOT_PASSWORD123456 mysql...

MPNet:旋转机械轻量化故障诊断模型详解python代码复现
目录 一、问题背景与挑战 二、MPNet核心架构 2.1 多分支特征融合模块(MBFM) 2.2 残差注意力金字塔模块(RAPM) 2.2.1 空间金字塔注意力(SPA) 2.2.2 金字塔残差块(PRBlock) 2.3 分类器设计 三、关键技术突破 3.1 多尺度特征融合 3.2 轻量化设计策略 3.3 抗噪声…...

(十)学生端搭建
本次旨在将之前的已完成的部分功能进行拼装到学生端,同时完善学生端的构建。本次工作主要包括: 1.学生端整体界面布局 2.模拟考场与部分个人画像流程的串联 3.整体学生端逻辑 一、学生端 在主界面可以选择自己的用户角色 选择学生则进入学生登录界面…...

【人工智能】神经网络的优化器optimizer(二):Adagrad自适应学习率优化器
一.自适应梯度算法Adagrad概述 Adagrad(Adaptive Gradient Algorithm)是一种自适应学习率的优化算法,由Duchi等人在2011年提出。其核心思想是针对不同参数自动调整学习率,适合处理稀疏数据和不同参数梯度差异较大的场景。Adagrad通…...

CentOS下的分布式内存计算Spark环境部署
一、Spark 核心架构与应用场景 1.1 分布式计算引擎的核心优势 Spark 是基于内存的分布式计算框架,相比 MapReduce 具有以下核心优势: 内存计算:数据可常驻内存,迭代计算性能提升 10-100 倍(文档段落:3-79…...

[ICLR 2022]How Much Can CLIP Benefit Vision-and-Language Tasks?
论文网址:pdf 英文是纯手打的!论文原文的summarizing and paraphrasing。可能会出现难以避免的拼写错误和语法错误,若有发现欢迎评论指正!文章偏向于笔记,谨慎食用 目录 1. 心得 2. 论文逐段精读 2.1. Abstract 2…...
反射获取方法和属性
Java反射获取方法 在Java中,反射(Reflection)是一种强大的机制,允许程序在运行时访问和操作类的内部属性和方法。通过反射,可以动态地创建对象、调用方法、改变属性值,这在很多Java框架中如Spring和Hiberna…...

从零实现STL哈希容器:unordered_map/unordered_set封装详解
本篇文章是对C学习的STL哈希容器自主实现部分的学习分享 希望也能为你带来些帮助~ 那咱们废话不多说,直接开始吧! 一、源码结构分析 1. SGISTL30实现剖析 // hash_set核心结构 template <class Value, class HashFcn, ...> class hash_set {ty…...
稳定币的深度剖析与展望
一、引言 在当今数字化浪潮席卷全球的时代,加密货币作为一种新兴的金融现象,正以前所未有的速度改变着我们对传统货币和金融体系的认知。然而,加密货币市场的高度波动性却成为了其广泛应用和普及的一大障碍。在这样的背景下,稳定…...

【Redis】笔记|第8节|大厂高并发缓存架构实战与优化
缓存架构 代码结构 代码详情 功能点: 多级缓存,先查本地缓存,再查Redis,最后才查数据库热点数据重建逻辑使用分布式锁,二次查询更新缓存采用读写锁提升性能采用Redis的发布订阅机制通知所有实例更新本地缓存适用读多…...

vulnyx Blogger writeup
信息收集 arp-scan nmap 获取userFlag 上web看看 一个默认的页面,gobuster扫一下目录 可以看到扫出的目录中得到了一个有价值的目录/wordpress,说明目标所使用的cms是wordpress,访问http://192.168.43.213/wordpress/然后查看源码能看到 这…...