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

经验分享|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 中&#xff0c; InnoDB存储引擎长期以来一直支持表空间的概念。在 MySQL 8.0 中&#xff0c;同一个分区表的所有分区必须使用相同的存储引擎。但是&#xff0c;也可以为同一 MySQL 服务器甚至同一数据库中的不同分区表使用不同的存储引擎。 通俗地讲…...

Arrays.asList() 和 Collections.singletonList()

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

Firmware Analysis Plus (Fap)固件模拟安装教程(最新)

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

使用包、Crate 和模块管理项目(上)

目录 1、包和Crate 2、定义模块来控制作用域与私有性 2.1 在模块中对相关代码进行分组 3、引用模块项目的路径 3.1 使用 pub 关键字暴露路径 二进制和库 crate 包的最佳实践 3.2 super 开始的相对路径 3.3 创建公有的结构体和枚举 Rust 有许多功能可以让你管理代码的组…...

【Kotlin】

Lambda 就是一小段可以作为参数传递的代码。 因为正常情况下&#xff0c;我们向某个函数传参时只能传入变量&#xff0c;而借助Lambda 却允许传入一小段代码。 Lambda 表达式的语法结构&#xff1a; {参数名1: 参数类型, 参数名2: 参数类型 -> 函数体}首先&#xff0c;最外…...

JavaDay17

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

Python爬取酷我音乐

&#x1f388; 博主&#xff1a;一只程序猿子 &#x1f388; 博客主页&#xff1a;一只程序猿子 博客主页 &#x1f388; 个人介绍&#xff1a;爱好(bushi)编程&#xff01; &#x1f388; 创作不易&#xff1a;喜欢的话麻烦您点个&#x1f44d;和⭐&#xff01; &#x1f388;…...

项目实战第四十七讲:易宝支付对接详解(保姆级教程)

易宝支付对接(保姆级教程) 为了实现项目的支付需求,公司选择了易宝支付进行对接,本文是项目实战第四十七讲,详解易宝支付对接。 文章目录 易宝支付对接(保姆级教程)1、需求背景2、流程图3、技术方案4、相关接口4.1、入驻相关(商户入网)4.2、账户相关接口(充值、提现、…...

python的websocket方法教程

WebSocket是一种网络通信协议&#xff0c;它在单个TCP连接上提供全双工的通信信道。在本篇文章中&#xff0c;我们将探讨如何在Python中使用WebSocket实现实时通信。 websockets是Python中最常用的网络库之一&#xff0c;也是websocket协议的Python实现。它不仅作为基础组件在…...

Qt处理焦点事件(获得焦点,失去焦点)

背景&#xff1a; 我只是想处理焦点动作&#xff0c;由于懒&#xff0c;上网一搜&#xff0c;排名靠前的一位朋友&#xff0c;使用重写部件的方式实现。还是因为懒&#xff0c;所以感觉复杂了。于是又花了一分钟解决了一下。 所以记录下来&#xff0c;以免以后忘了。 思路&a…...

SiteGround如何设置WordPress网站自动更新

SiteGround Autoupdate功能会自动帮我们更新在他们这里托管的所有WordPress网站&#xff0c;这样做是为了保证网站安全&#xff0c;并且让它们一直保持最新状态。他们会根据我们选择的设置自动更新不同版本的WordPress&#xff0c;包括主要版本和次要版本。在每次自动更新之前&…...

http代理和SOCK5代理谁更安全?

在这个网络化的时代&#xff0c;我们常常听到HTTP代理和SOCKS5代理这两个名词&#xff0c;不过很多人并不了解是什么意思。今天&#xff0c;我们将揭开这两种代理的神秘面纱&#xff0c;看看到底HTTP代理和SOCKS5代理哪个更安全&#xff1f; HTTP代理&#xff1a;高效通信的“枢…...

Kotlin关键字二——constructor和init

在关键字一——var和val中最后提到了构造函数&#xff0c;这里就学习下构造函数相关的关键字: constructor和init。 主要构造(primary constructor) kotlin和java一样&#xff0c;在定义类时就自动生成了无参构造 // 会生成默认的无参构造函数 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类型是一种用于表示较大整数的数据类型&#xff0c;范围比int类型更广泛。然而&#xff0c;即使…...

【Java期末复习资料】(4)模拟卷

有不会的题可以后台问我的哦&#xff0c;看见了就会回。 本文章主要是模拟卷&#xff0c;知识点例题简答题已经发过了&#xff0c;可以在主页专栏Java中找一下 一、单项选择题 1. 编译 Java Application 源程序文件将产生相应的字节码文件&#xff0c;这些字节码文件的扩展名为…...

【计算机网络】UDP报文详解

目录 一. UDP协议概述 二. UDP报文格式 首部 三. UDP的缓冲区 一. UDP协议概述 UDP——用户数据报协议&#xff0c;是传输层的一个重要协议 基于UDP的应用层协议有&#xff1a;DNS&#xff0c;TFTP&#xff0c;SNMP&#xff0c;NTP 协议全称默认端口号DNSDomain Name Se…...

排序算法——归并排序

归并排序&#xff08;Merge Sort&#xff09;是计算机科学中非常重要的排序算法之一。它不仅高效、稳定&#xff0c;而且是许多高级排序技术和算法思想的基础。在本文中&#xff0c;我们将深入探讨归并排序的原理、实现方法&#xff0c;以及它的优缺点。 1. 归并排序的原理 归…...

2023 年安徽省职业院校技能大赛高职组“软件测试”赛项样题

2023 年安徽省职业院校技能大赛 高职组“软件测试”赛项样题 目录 任务一&#xff1a;功能测试&#xff08;45 分&#xff09; 1、测试计划&#xff08;5 分&#xff09; 2、测试用例&#xff08;15 分&#xff09; 3、Bug 清单&#xff08;20 分&#xff09; 4、测试报告&…...

Mysql8和Oracle实际项目中递归查询树形结构

背景&#xff1a; 项目升级&#xff0c;引入MySQL数据库&#xff0c;之前一直用的是Oracle数据&#xff0c;在做用户登录单位维护的时候&#xff0c;需要返回该用户所属单位下的所有子单位。下边是模拟项目数据实践的过程。 数据准备&#xff1a; 准备一张单位表&#xff0c…...

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. 通过创建唯一性索引&#xff0c;可以保证数据库表中每一行数据的唯一性。2. 可以加快数据的检索速度&#xff08;创建索引的主要原因&#xff09;。3. 可以加速表和表之间的连接&#xff0c;实现数据的参考完整性。4. 可以在查询过程中&#xff0c;…...

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; }//解释这串代码&#xff0c;写上注释 当然可以&#xff01;这段代码是 Qt …...

Opencv中的addweighted函数

一.addweighted函数作用 addweighted&#xff08;&#xff09;是OpenCV库中用于图像处理的函数&#xff0c;主要功能是将两个输入图像&#xff08;尺寸和类型相同&#xff09;按照指定的权重进行加权叠加&#xff08;图像融合&#xff09;&#xff0c;并添加一个标量值&#x…...

C++中string流知识详解和示例

一、概览与类体系 C 提供三种基于内存字符串的流&#xff0c;定义在 <sstream> 中&#xff1a; std::istringstream&#xff1a;输入流&#xff0c;从已有字符串中读取并解析。std::ostringstream&#xff1a;输出流&#xff0c;向内部缓冲区写入内容&#xff0c;最终取…...

Android 之 kotlin 语言学习笔记三(Kotlin-Java 互操作)

参考官方文档&#xff1a;https://developer.android.google.cn/kotlin/interop?hlzh-cn 一、Java&#xff08;供 Kotlin 使用&#xff09; 1、不得使用硬关键字 不要使用 Kotlin 的任何硬关键字作为方法的名称 或字段。允许使用 Kotlin 的软关键字、修饰符关键字和特殊标识…...

AI病理诊断七剑下天山,医疗未来触手可及

一、病理诊断困局&#xff1a;刀尖上的医学艺术 1.1 金标准背后的隐痛 病理诊断被誉为"诊断的诊断"&#xff0c;医生需通过显微镜观察组织切片&#xff0c;在细胞迷宫中捕捉癌变信号。某省病理质控报告显示&#xff0c;基层医院误诊率达12%-15%&#xff0c;专家会诊…...

Java毕业设计:WML信息查询与后端信息发布系统开发

JAVAWML信息查询与后端信息发布系统实现 一、系统概述 本系统基于Java和WML(无线标记语言)技术开发&#xff0c;实现了移动设备上的信息查询与后端信息发布功能。系统采用B/S架构&#xff0c;服务器端使用Java Servlet处理请求&#xff0c;数据库采用MySQL存储信息&#xff0…...

es6+和css3新增的特性有哪些

一&#xff1a;ECMAScript 新特性&#xff08;ES6&#xff09; ES6 (2015) - 革命性更新 1&#xff0c;记住的方法&#xff0c;从一个方法里面用到了哪些技术 1&#xff0c;let /const块级作用域声明2&#xff0c;**默认参数**&#xff1a;函数参数可以设置默认值。3&#x…...

麒麟系统使用-进行.NET开发

文章目录 前言一、搭建dotnet环境1.获取相关资源2.配置dotnet 二、使用dotnet三、其他说明总结 前言 麒麟系统的内核是基于linux的&#xff0c;如果需要进行.NET开发&#xff0c;则需要安装特定的应用。由于NET Framework 是仅适用于 Windows 版本的 .NET&#xff0c;所以要进…...

写一个shell脚本,把局域网内,把能ping通的IP和不能ping通的IP分类,并保存到两个文本文件里

写一个shell脚本&#xff0c;把局域网内&#xff0c;把能ping通的IP和不能ping通的IP分类&#xff0c;并保存到两个文本文件里 脚本1 #!/bin/bash #定义变量 ip10.1.1 #循环去ping主机的IP for ((i1;i<10;i)) doping -c1 $ip.$i &>/dev/null[ $? -eq 0 ] &&am…...