日常开发记录分享-SQL中的partition分区功能使用

🕗 发布于 2024-07-25 03:08 sql java 数据库

需求来源

今天甲方这边要查看一个机车的周时数据（就是一个机车从到我的管辖范围内，到出我的管辖内所用的时间），那这个它会跑很多次，我们要查询这一天的周时数据，锚定一个点比如出管辖区的时间，那么根据查询到今天所有这个时间范围内出去的车信息，然后去数据表里找这个机车进来的数据且时最新的一条就行了。

实现思路

分两次查询的第一次查询出来所有的当天出管辖区的机车信息，第二个查询是根据第一个查询小小的改动，把时间范围去掉就好，然后根据机车信息进行组取时间每个机车时间最新的数据就好。直接使用group by，但是这个并不能取出其它的信息所以就pass掉了。GPTl了一下给的方案是使用partition这个功能。

实施

就不看项目数据了就看一下我写的小demo的结果吧。我有一个student表，这个表里有10个班的学生，每个班的学生有20个，我现在要取出每个班的学生id最大的这个记录，就可以使用这个partition了。
在这里插入图片描述

select * from (SELECT *, ROW_NUMBER() over (partition by classes_id order by id desc) as rn FROM `student`) a where rn=1

在这里插入图片描述
解释一下这个啊。

这条 SQL 语句使用了窗口函数 ROW_NUMBER() 来为每个 classes_id 组中的行编号，并在外部查询中只选择每个 classes_id 组中的最新一行（根据 id 倒序排序）。以下是对这条 SQL 语句的详细解释：

SQL 语句结构

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
    FROM `student`
) a 
WHERE rn = 1;

内部查询（子查询）

SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
FROM `student`

SELECT *：
- 选择 student 表中的所有列。
ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn：
- ROW_NUMBER() 是一个窗口函数，它为结果集中的每一行分配唯一的行号。
- OVER 子句定义了窗口的分区和排序规则：
  - PARTITION BY classes_id：将结果集按 classes_id 列进行分组。对于每个 classes_id，将重新开始编号。
  - ORDER BY id DESC：在每个 classes_id 分区中，按照 id 列的降序排序。
- AS rn：将生成的行号列命名为 rn。
这部分查询为每个 classes_id 组中的行编号，编号从1开始，按照 id 倒序排列。因此，rn 为1的行是每个 classes_id 组中 id 最大的行。

外部查询

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
    FROM `student`
) a 
WHERE rn = 1;

FROM (...) a：
- 将内部查询的结果作为一个临时表 a。
WHERE rn = 1：
- 筛选出临时表 a 中 rn 等于 1 的行，即每个 classes_id 组中 id 最大的行。

结果

整个查询的作用是：

对 student 表进行分组（按 classes_id）。
在每个 classes_id 组中，按 id 倒序排列，并为每行分配一个行号 rn。
选择每个 classes_id 组中 rn 等于 1 的行（即每个 classes_id 组中 id 最大的行）。

partition的升级使用

partition不仅仅可以在日常查询中使用，还可以在表的数据结构上进行优化，比如在建表的时候创建分区或者后期添加分区，这个分区操作是在物理上的操作，可以看我下面这张表的结构，有一部分注释说明就是分区的设置，

在这里插入图片描述

对表进行分区可以提升查询性能和数据管理的效率。由于 ENGINE=MyISAM 不支持分区，我们需要将表的存储引擎更改为 InnoDB，因为 InnoDB 支持分区。

假设我们要根据 id 列进行范围分区，将数据划分为四个分区：

p0：包含 id 小于 10000的数据。
p1：包含 id 小于 20000的数据。
p2：包含 id 小于 50000的数据。
p3：包含其余的数据。


PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN (50000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

解释

PARTITION BY RANGE (id): 根据 id 列进行范围分区。
PARTITION p0 VALUES LESS THAN (10000): 第一个分区，包含 id 小于 10000的数据。
PARTITION p1 VALUES LESS THAN (20000): 第二个分区，包含 id 小于 20000的数据。
PARTITION p2 VALUES LESS THAN (50000): 第三个分区，包含 id 小于 50000的数据。
PARTITION p3 VALUES LESS THAN MAXVALUE: 第四个分区，包含 id 大于等于 50000的数据。

这样，表 products 就被划分为四个分区，每个分区包含一定范围的 id 值的数据。

验证一下看看分区

上面说了创建分区了，但是怎么才能确定我们的查询sql使用到了分区呢？使用explain来查看执行的sql有没有在分区的范围呢，
下面是使用了explain查看执行的sql有没有用到分区，partition的值为p0对应了上面设置的分区。

在这里插入图片描述

分区的一些操作

创建分区后，数据库管理系统会自动处理分区的数据存储和检索，用户在日常操作中并不需要特殊处理分区。不过，你可以通过一些特定的查询和操作来利用分区的优势。以下是一些常见的用法示例：

1. 普通查询

普通的查询不需要特别处理分区，数据库管理系统会自动根据分区优化查询：

SELECT * FROM student WHERE id < 50;

2. 分区表上的查询优化

当你的查询条件包含分区键时，数据库会自动选择相关的分区进行查询，从而提高查询性能。例如：

SELECT * FROM student WHERE id BETWEEN 50 AND 100;

3. 插入数据

插入数据时，数据库会根据分区键自动将数据插入到相应的分区：

INSERT INTO student (name, classes_id) VALUES ('Alice', 1);

4. 删除分区中的数据

可以通过分区键删除特定分区中的数据：

DELETE FROM student WHERE id < 50;

5. 分区维护操作

你可以进行一些特定的分区维护操作，例如合并分区、拆分分区、删除分区等：

添加新的分区

ALTER TABLE student ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (200)
);

删除分区

ALTER TABLE student DROP PARTITION p0;

重组分区

可以将多个分区合并为一个分区：

ALTER TABLE student REORGANIZE PARTITION p1, p2 INTO (
    PARTITION p1_2 VALUES LESS THAN (150)
);

6. 检查分区信息

你可以使用 SHOW 语句查看表的分区信息：

SHOW CREATE TABLE student;

总结

综合示例展示了如何创建分区表、插入数据以及进行查询和维护操作：

-- 创建分区表
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `classes_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK4l5dnicegnvpmu0pv6vdvrmb6` (`classes_id`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb3
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (50),
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (150),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- 插入数据
INSERT INTO student (name, classes_id) VALUES ('Alice', 1);
INSERT INTO student (name, classes_id) VALUES ('Bob', 2);

-- 查询数据
SELECT * FROM student WHERE id < 50;

-- 删除分区中的数据
DELETE FROM student WHERE id < 50;

-- 添加新分区
ALTER TABLE student ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (200)
);

-- 删除分区
ALTER TABLE student DROP PARTITION p0;

-- 检查分区信息
SHOW CREATE TABLE student;

目前先整理这么多，以后有深入学习使用了再继续！！！

原文地址：https://blog.csdn.net/pengjun_ge/article/details/140673797

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：vite+react+ts+Rust来进行前后端web开发(hello world)
下一篇：LeetCode/NowCoder-二叉树OJ练习

局域网视频
从上面的网址下载代码，纯前端启动服务，浏览器访问服务，需要设置浏览器的权限才可以出现视频画面：打开浏览器设置地址chrome://flags,按照下图进行设置。缺点是免费试用期只有15天，收费版按人收
阅读更多2024-09-20
语音识别与语音控制的原理介绍
当人依次在麦克风旁边说出“地平线你好”、“向左转”、“向右转”、“向前走”、“向后退”命令词，语音算法sdk经过智能处理后输出识别结果，log显示如下。机器硬件：OriginBot(导航版/视觉版
阅读更多2024-09-20
在OpenWrt上安装USB-Wifi驱动（Realtek 802.11n）
OpenWrt是一个基于Linux的路由器操作系统，它允许用户自定义和优化路由器的功能。Realtek 802.11n驱动是为支持Realtek无线芯片组的设备设计的，这些芯片组通常用于许多路由器和U
阅读更多2024-09-20
搜维尔科技：Unity中的A.R.T.测量工具
搜维尔科技：Unity中的A.R.T.测量工具。
阅读更多2024-09-20
华为AR100-S路由器恢复出厂设置
Here's a summary of the steps to reset the Huawei AR100-S router using both the console cable and th
阅读更多2024-09-20
IM项目-----语音识别子服务
语音转换子服务，用于调用语音识别 SDK，进行语音识别，将语音转为文字后返回给网关。语音消息的文字转换：客户端进行语音消息的文字转换。
阅读更多2024-09-20
Redis实践之缓存：设置缓存过期策略
是用于配置缓存项的过期策略和其他选项的类。在使用接口时，可以通过这个类来设置缓存项的过期时间、滑动过期时间等。以下是。
阅读更多2024-09-20
【论文阅读】Slim Fly: A Cost Effective Low-Diameter Network Topology 一种经济高效的小直径网络拓扑
Slim Fly 一种高性能、经济高效的网络拓扑，它接近理论上的最佳网络直径。Slim Fly网络拓扑是基于一种图论方法，这种方法试图近似解决度-直径问题（degree-diameter proble
阅读更多2024-09-20
gma 2.0.13 (2024.09.16) 更新日志
链接：https://pan.baidu.com/s/1P0nmZUPMJaPEmYgixoL2QQ?：修复克里金插值VariogramParameters参数异常的问题！：现在，gma内部所有的矢量
阅读更多2024-09-20
R语言中的shiny框架
Shiny 是一个 R 语言的框架，用于构建交互式的网页应用，可以让用户以最少的 HTML、CSS 和 JavaScript 代码开发动态的Web 应用程序，Shiny 主要优点是能够将 R 的分析和
阅读更多2024-09-20