经验分享|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...

【力扣数据库知识手册笔记】索引
索引 索引的优缺点 优点1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。2. 可以加快数据的检索速度(创建索引的主要原因)。3. 可以加速表和表之间的连接,实现数据的参考完整性。4. 可以在查询过程中,…...
Qt Widget类解析与代码注释
#include "widget.h" #include "ui_widget.h"Widget::Widget(QWidget *parent): QWidget(parent), ui(new Ui::Widget) {ui->setupUi(this); }Widget::~Widget() {delete ui; }//解释这串代码,写上注释 当然可以!这段代码是 Qt …...

Opencv中的addweighted函数
一.addweighted函数作用 addweighted()是OpenCV库中用于图像处理的函数,主要功能是将两个输入图像(尺寸和类型相同)按照指定的权重进行加权叠加(图像融合),并添加一个标量值&#x…...
C++中string流知识详解和示例
一、概览与类体系 C 提供三种基于内存字符串的流,定义在 <sstream> 中: std::istringstream:输入流,从已有字符串中读取并解析。std::ostringstream:输出流,向内部缓冲区写入内容,最终取…...

Android 之 kotlin 语言学习笔记三(Kotlin-Java 互操作)
参考官方文档:https://developer.android.google.cn/kotlin/interop?hlzh-cn 一、Java(供 Kotlin 使用) 1、不得使用硬关键字 不要使用 Kotlin 的任何硬关键字作为方法的名称 或字段。允许使用 Kotlin 的软关键字、修饰符关键字和特殊标识…...

AI病理诊断七剑下天山,医疗未来触手可及
一、病理诊断困局:刀尖上的医学艺术 1.1 金标准背后的隐痛 病理诊断被誉为"诊断的诊断",医生需通过显微镜观察组织切片,在细胞迷宫中捕捉癌变信号。某省病理质控报告显示,基层医院误诊率达12%-15%,专家会诊…...
Java毕业设计:WML信息查询与后端信息发布系统开发
JAVAWML信息查询与后端信息发布系统实现 一、系统概述 本系统基于Java和WML(无线标记语言)技术开发,实现了移动设备上的信息查询与后端信息发布功能。系统采用B/S架构,服务器端使用Java Servlet处理请求,数据库采用MySQL存储信息࿰…...
es6+和css3新增的特性有哪些
一:ECMAScript 新特性(ES6) ES6 (2015) - 革命性更新 1,记住的方法,从一个方法里面用到了哪些技术 1,let /const块级作用域声明2,**默认参数**:函数参数可以设置默认值。3&#x…...

麒麟系统使用-进行.NET开发
文章目录 前言一、搭建dotnet环境1.获取相关资源2.配置dotnet 二、使用dotnet三、其他说明总结 前言 麒麟系统的内核是基于linux的,如果需要进行.NET开发,则需要安装特定的应用。由于NET Framework 是仅适用于 Windows 版本的 .NET,所以要进…...
写一个shell脚本,把局域网内,把能ping通的IP和不能ping通的IP分类,并保存到两个文本文件里
写一个shell脚本,把局域网内,把能ping通的IP和不能ping通的IP分类,并保存到两个文本文件里 脚本1 #!/bin/bash #定义变量 ip10.1.1 #循环去ping主机的IP for ((i1;i<10;i)) doping -c1 $ip.$i &>/dev/null[ $? -eq 0 ] &&am…...