在数据库管理中，随着数据量的不断增长，如何高效地存储和查询数据成为了一个关键问题。分区表技术通过将大型表划分为多个更小、更易于管理的部分，显著提升了数据库的性能和可维护性。MySQL 和 Oracle 作为两款主流的关系型数据库管理系统，都提供了分区表功能，但在实现和使用上存在一些差异。本文将深入探讨 MySQL 和 Oracle 分区表的相关内容，以及它们的相同点和不同点。

一、分区表

（一）基本概念

分区表是指根据一定的规则，将一张表的数据划分到多个不同的物理文件中进行存储。这些物理文件可以分布在不同的磁盘上，从而提高数据的 I/O 性能。分区对于应用程序来说是透明的，应用程序在访问分区表时，不需要额外的代码修改，就像访问普通表一样。

（二）分区表分类

水平分区

        水平分区是指将单个数据表中的行分成一些较小的表。这种方法需要根据特定属性对数据表进行分割，例如：用户id、日期、城市等。在水平划分中，相似的片段被分配给相同的表。这种方法带来的优势是可以提高数据备份/恢复等操作的效率，同时提高了查询速度，因为只有一部分数据被读取。

垂直分区

        垂直分区是指按列将数据表划分成较小的表，每个表都具有不同的列并存储记录。这种方法可以帮助优化查询性能，减少读取磁盘的数据量。在垂直分区中，将经常访问的列分为一组，并将不常用的列分为另一组。最常见的实现方法是将经常使用的列（如：ID、名称、日期等）分为一个表，而较少使用的列（如：大文本、高清图片等）则分为另一个表。

（三）分区类型

  1. RANGE分区：基于列的值范围将数据分配到不同的分区。
  2. LIST分区：类似于RANGE分区，但LIST分区是基于列的离散值集合来分配数据的。
  3. HASH分区：基于用户定义的表达式的哈希值来分配数据到不同的分区。
  4. KEY分区：类似于HASH分区，但KEY分区支持计算一列或多列的哈希值来分配数据。

注：range分区、list分区、hash分区 MySQL和Oracle都有，key分区仅MySQL有

注：

  1. 建立分区表时，必须至少指定一个分区
  2. range、list、hash分区的分区字段必须是整型（小数或字符串类型不可以）。
  3. 分区字段可以是表中原有字段，也可以是字段计算后的表达式
  4. 分区表插入数据前，数据所属分区必须存在
  5. 分区字段建议设置为not null
  6. 各种分区对null值的处理方式：

            range分区：会将null划分到最小的分区里

            list分区：不允许插入，必须显式定义

            hash分区：null值结果为0，会放到第0个分区

            key分区：null被视为0，会放到第0个分区

  7. 多列分区：加 columns可以多列分区，不限制数据类型，故可以创建单个字段的非整数类型的表分区
  8. 对比方式为从左到右，第一个字段值小于第一个字段分区值则放入第一个分区，等于或大于第一个字段分区值则对比第二个字段值与第一个字段分区值的大小，以此类推

range分区

       范围分区，一个分区的数据，是某个字段的某一个范围的数据。

       分区字段：连续分区的字段。

-- MySQL
create table user(
id int not null,
name varchar(10)
)
partition by range(id)(
partition p0 values less than (10),        -- p0字段存储id为0到9的字段
partition p1 values less than (20),        -- p1字段存储id为10到19的字段
partition p2 values less than (30),        -- p2字段存储id为20到29的字段
partition p3 values less than maxvalue     -- p3字段存储id>29的字段
)-- Oracle
create table user(
id number not null,
name varchar2(20)
)
partition by range(id)(
partition p0 values less than (10),        -- p0字段存储id为0到9的字段
partition p1 values less than (20),        -- p1字段存储id为10到19的字段
partition p2 values less than (30),        -- p2字段存储id为20到29的字段
partition p3 values less than (maxvalue)   -- p3字段存储id>29的字段
)-- Oracle可以非整型数据 
-- 例：date类型
create table user(
id number,
name varchar2(20),
birthday date
)
partition by range(birthday)(
partition p1 values less than(to_date('2001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) ,
partition p2 values less than(to_date('2010-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) ,
partition p3 values less than(maxvalue) 
)

        可以多个分区字段同时插入，在插入时自己进入相应的分区 

insert into user
values(0,'aa'),(10,'bb'),(20,'cc'),(30,'dd');

        查看user表全表数据及各分区表数据

select * from user;
select * from user partition (p0);
select * from user partition (p1);
select * from user partition (p2);
select * from user partition (p3);

LIST分区

        列表分区，一个分区只能放分区字段固定某几个值的数据。

        分区字段：离散字段。

-- MySQL
create table user1(
id int,
name varchar
)
partition by list(id)(
partition p0 values in (10),    -- p0分区存储id为10的数据
partition p1 values in (20),    -- p1分区存储id为20的数据
partition p2 values in (30)     -- p2分区存储id为30的数据
)-- Oracle可以非整型数据，不需要in
create table user1(
id number,
name varchar2(20),
sex char(1)
)
partition by list(sex)(
partition male values ('M'),
partition female values ('F')
)

注：插入数据和查看表数据同上

HASH分区

        HASH分区基于分区键（如列或表达式）的值计算哈希值，再通过取模运算确定数据所在的分区编号。

注：hash分区和key分区都是让各个分区数据尽可能分布均匀

-- MySQL
-- 系统自动取名
create table u2(
id int,
name varchar(20)
)
partition by hash(id) partitions 3;    -- 对3取模分区-- 数值对3取模结果分别为0，1，2故三个分区-- 自定义分区
create table u3(
id int,
name varchar(20)
)
partition by hash(id)(
partition p11,
partition p12,
partition p13,
partition p14
)
-- 对4取模-- Oracle
CREATE TABLE transactions (trans_id NUMBER,amount NUMBER,trans_date DATE
)
PARTITION BY HASH (trans_id)
PARTITIONS 4;

KEY分区

        利用MySQL内置的hash加密函数得到值后再取模。

        注：Oracle没有KEY分区。

create table u4(
id int,
name varchar(20)
)
partition by key(name) partition 4     -- 对4取模

复合分区 

        MySQL主分区（range，list）+子分区（hash，key）且，，range，list只能作为主分区，hash，key只能作为子分区。

        Oracle中只有range，list，hash分区，没有key分区，其中range和list既可以是主分区也可以是子分区，hash只能是子分区

-- MYSQL
create table u5 (
id int，
name varchar(20)
)partition by range(id)        -- 主分区为range分区subpartition by hash(id)      -- 子分区为hash分区( partition p1 values less than (10)    -- p1主分区存储id<10的字段( subpartition p11 ,subpartition p12 , subpartition p13 ),  -- 子分区对3取模分别存储到三个分区partition p2 values less than (20)( subpartition p21 ,subpartition p22 , subpartition p23 ))
-- ORACLE
CREATE TABLE u5 (id NUMBER,name VARCHAR2(50)
)
-- 主分区采用range分区方式，基于id字段
PARTITION BY RANGE (id)
-- 子分区采用hash分区方式，基于name字段，且子分区数量为4
SUBPARTITION BY HASH (name)
SUBPARTITIONS 4
(-- 定义第一个主分区p1，包含id小于100的数据PARTITION p1 VALUES LESS THAN (100),-- 定义第二个主分区p2，包含id大于等于100且小于200的数据PARTITION p2 VALUES LESS THAN (200),-- 定义第三个主分区p3，包含id大于等于200的数据PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

注：复合分区中hash分区子分区数量必须相同，range，list分区没有限制

（四）分区常见操作

1.将非分区表修改为分区表

-- 创建非分区表
create table a(
id int
)-- MySQL
-- 将非分区表修改为分区表
alter table a
partition by range(id)
(
partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than (30),
partition p3 values less than maxvalue
)-- Oracle
alter table a
partition by range(id)
(
partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than (30),
partition p3 values less than (maxvalue)
)

2.删除分区

        删除某个分区的数据和结构。

-- MySQL Oracle
alter table a drop partition p0;

3.清空分区 

        只删除某个分区的数据，不删除分区结构。

-- MySQL Oracle
alter table a truncate partition p1;

4.添加分区

        range分区只能往后添加，由于a表已经创建maxvalue故不能添加分区。

-- 创建前置数据
create table a1(
id int
)
partition by range(id)
(
partition p0 values less than (10)
)
-- 添加分区
-- MySQL语法，多个分区在括号里面用逗号分隔
alter table a1 add partition
(
partition p1 values less than (20),
partition p2 values less than (30)
)
-- Oracle语法
-- Oracle不能添加多个分区，一次只能添加一个主分区
alter table a1 add partition p1 values less than (10);

        list分区

-- MySQL
alter table a1 add partition
(partition p1 values in (20))-- Oracle
alter table a1 
add partition p2 values(20)

 5.分解分区

        分解分区后数据也会被拆分到对应分区

-- MySQL
alter table a
reorganize partition p0 into
(
partition p01 values less than(5),
partition p02 values less than(10),
)-- Oracle
alter table a1
split partition p0
at(5)  -- 分解点为5
into(partition p01 values less than (5),partition p02 values less than (10)
)

6.合并分区

        合并分区并不会丢失数据。

-- MySQL
alter table a
reorganize partition p01,p02 into
(
partition p0 values less than(10)
)-- Oracle
alter table a1
merge partitions p01,p02
into partition p1

7.删除分区表所有分区，即 将分区表变为非分区表

        不会丢失数据 

-- MYSQL
alter table a remove partitioning;
-- Oracle
-- 方法一：在线重定义
-- 1. 确认表符合在线重定义条件（需主键或唯一约束）
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('schema', 'a1', DBMS_REDEFINITION.CONS_USE_PK);-- 2. 创建非分区表结构（与原表相同，但不包含分区）
CREATE TABLE a1_new (id INT
);-- 3. 开始在线重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(uname          => 'schema',orig_table     => 'a1',int_table      => 'a1_new',col_mapping    => 'id',options_flag   => DBMS_REDEFINITION.CONS_USE_PK
);-- 4. 同步数据（可选，多次执行以减少最终切换时间）
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('schema', 'a1', 'a1_new');-- 5. 完成重定义
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('schema', 'a1', 'a1_new');-- 6. 重建索引、约束和触发器（自动保留，但状态可能为DISABLED）
ALTER INDEX idx_a1 ENABLE;
ALTER TRIGGER trg_a1 ENABLE;-- 方法二：创建新表 + 数据迁移
-- 1. 创建非分区表
CREATE TABLE a1_new (id INT
);-- 2. 插入数据
INSERT /*+ APPEND PARALLEL */ INTO a1_new
SELECT * FROM a1;-- 3. 验证数据
SELECT COUNT(*) FROM a1;
SELECT COUNT(*) FROM a1_new;-- 4. 重命名表（需锁表，建议业务低峰期执行）
ALTER TABLE a1 RENAME TO a1_old;
ALTER TABLE a1_new RENAME TO a1;-- 5. 重建索引、约束和触发器
CREATE INDEX idx_a1 ON a1 (id);
ALTER TABLE a1 ADD CONSTRAINT pk_a1 PRIMARY KEY (id);-- 方法三：使用 CTAS（Create Table As Select）
-- 1. 创建非分区表并导入数据
CREATE TABLE a1_new AS
SELECT * FROM a1;-- 2. 重命名表
ALTER TABLE a1 RENAME TO a1_old;
ALTER TABLE a1_new RENAME TO a1;

8.重建分区

        重建分区可以优化数据分布，修复分区损坏，调整分区策略，释放存储空间。

-- MYSQL
alter table a rebuild partition p0;-- Oracle
alter table a1 move partition p0;

9.优化分区

        优化空间分布

-- MySQL
alter table a optimize partition p1;-- Oracle
alter table a1 move partition p1 compress for oltp; -- 实时压缩分区，减少存储空间
alter table a1 move partition p1;  -- 重建物理存储，消除碎片，提高查询性能

10.修补分区

        当分区结构损坏，打不开分区时需要修补分区。

-- MySQL
alter table a repair partition p2; -- 修补分区p2-- Oracle
alter table a1 validate partition p2 structure cascade;

11.查看表分区

        查看目标表表分区情况。

-- MySQL
-- 查看表结构及分区定义
show create table a; 
-- 查看分区详细信息
SELECT PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_SCHEMA = 'dw'  -- 指定数据库dwAND TABLE_NAME = 'a';  -- Oracle
-- 确认表是否为分区表
SELECT table_name, partitioning_type, partitioning_key_columns
FROM dw.user_part_tables
WHERE table_name = 'A1';   
-- 查看分区定义及表空间
SELECT partition_name, high_value, tablespace_name, num_rows, last_analyzed
FROM dw.user_tab_partitions
WHERE table_name = 'A1';

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/bicheng/86052.shtml
繁体地址，请注明出处：http://hk.pswp.cn/bicheng/86052.shtml
英文地址，请注明出处：http://en.pswp.cn/bicheng/86052.shtml

如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

在MATLAB中绘制阵列天线的散射方向图

在MATLAB中绘制阵列天线的散射方向图

在MATLAB中绘制阵列天线的散射方向图 RCS元因子、RCS阵因子、总的RCS 单基地雷达 文章目录 前言一、雷达散射界面的定义二、阵列天线的雷达散射界面三、MATLAB仿真总结 前言 \;\;\;\;\; 在无线通信、雷达和天线设计中&#xff0c;分析阵列天线的散射特性至关重要。散射方向图&a…
阅读更多...
SaaS+AI架构实战，

SaaS+AI架构实战，

近年来&#xff0c;随着云计算技术的成熟和市场需求的变化&#xff0c;SaaS&#xff08;软件即服务&#xff09;已成为企业数字化转型的核心工具。与传统软件相比&#xff0c;SaaS通过云端按需交付服务&#xff0c;大幅降低了企业的IT部署成本&#xff0c;同时提供了更高的灵活…
阅读更多...
网络安全应急响应实战笔记

网络安全应急响应实战笔记

网络安全应急响应实战笔记 项目介绍 面对各种各样的安全事件&#xff0c;我们该怎么处理&#xff1f; 这是一个关于安全事件应急响应的项目&#xff0c;从系统入侵到事件处理&#xff0c;收集和整理一些案例进行分析。 GitHub 地址&#xff1a;https://github.com/Bypass007…
阅读更多...
国产Linux银河麒麟操作系统安装开源免费Draw.io（diagrams.net）替代Visio

国产Linux银河麒麟操作系统安装开源免费Draw.io（diagrams.net）替代Visio

一、Draw.io&#xff08;diagrams.net&#xff09;与 Microsoft Visio 对比&#xff1a; Draw.io&#xff08;现更名为 diagrams.net&#xff09;是一款流行的免费在线图表工具&#xff0c;可以作为 Microsoft Visio 的替代品。draw.io 支持 UML、流程图、架构图&#xff0c;模…
阅读更多...
asio之socket RAII管理socket_holder

asio之socket RAII管理socket_holder

简介 socket_holder实现对socket的RAII管理 结构 #mermaid-svg-7AbOnlAgmXN8WUnw {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-7AbOnlAgmXN8WUnw .error-icon{fill:#552222;}#mermaid-svg-7AbOnlAgmXN8WUnw .er…
阅读更多...
Python训练营---DAY56

Python训练营---DAY56

DAY 56 时序数据的检验 知识点回顾&#xff1a; 假设检验基础知识 原假设与备择假设P值、统计量、显著水平、置信区间 白噪声 白噪声的定义自相关性检验&#xff1a;ACF检验和Ljung-Box 检验偏自相关性检验&#xff1a;PACF检验 平稳性 平稳性的定义单位根检验 季节性检验 ACF检…
阅读更多...
【python深度学习】Day 56 时序数据的检验

【python深度学习】Day 56 时序数据的检验

知识点&#xff1a; 假设检验基础知识 原假设与备择假设P值、统计量、显著水平、置信区间 白噪声 白噪声的定义自相关性检验&#xff1a;ACF检验和Ljung-Box 检验偏自相关性检验&#xff1a;PACF检验 平稳性 平稳性的定义单位根检验 季节性检验 ACF检验序列分解&#xff1a;趋势…
阅读更多...
搭建网站时用到的技术

搭建网站时用到的技术

jQuery AJAX FLASK框架 要再Python的虚拟环境下部署 接下来创建项目文件夹 /data/demo 进入demo目录中&#xff0c;创建虚拟环境 ​​激活虚拟环境后&#xff0c;所有操作都基于创建时使用的 Python 版本​​ virtualenv venv 成功会生成一个venv文件夹&#xff0c; 接…
阅读更多...
Docker知识点汇总——AI教你学Docker

Docker知识点汇总——AI教你学Docker

Docker & Docker Compose 全面知识点梳理 一、Docker 基础知识 1.1 Docker 概念 什么是容器、镜像、仓库、Docker 引擎容器与虚拟机的区别Docker 的应用场景与优势 1.2 Docker 安装与配置 各操作系统&#xff08;Linux、Windows、macOS&#xff09;上的安装方法配置加…
阅读更多...
Agent轻松通-P1：什么是Agent？

Agent轻松通-P1：什么是Agent？

欢迎来到啾啾的博客&#x1f431;。 记录学习点滴。分享工作思考和实用技巧&#xff0c;偶尔也分享一些杂谈&#x1f4ac;。 有很多很多不足的地方&#xff0c;欢迎评论交流&#xff0c;感谢您的阅读和评论&#x1f604;。 目录 1 引言2 基础概念3 Agent的挑战3.1 复杂度带来的…
阅读更多...
Grafana MySQL监控大盘指标图趋势不连续分析

Grafana MySQL监控大盘指标图趋势不连续分析

问题现象 通过benchmarksql对MySQL数据库做压测完发现Grafana关于该数据库的监控图趋势不连续&#xff0c;监控数据异常。 说明&#xff1a;Prometheusmysqlexpoter都通过容器运行 日志分析 检查了其他数据库节点跟主机节点趋势图均正常&#xff0c;排除 Prometheus 的问题&a…
阅读更多...
Python实例题：基于区块链的去中心化应用平台（区块链、智能合约）

Python实例题：基于区块链的去中心化应用平台（区块链、智能合约）

目录 Python实例题 题目 问题描述 解题思路 关键代码框架 难点分析 扩展方向 Python实例题 题目 基于区块链的去中心化应用平台&#xff08;区块链、智能合约&#xff09; 问题描述 开发一个基于区块链的去中心化应用平台&#xff0c;包含以下功能&#xff1a; 区块…
阅读更多...
接口请求重复触发问题的排查流程：iOS抓包实战中的工具协作

接口请求重复触发问题的排查流程：iOS抓包实战中的工具协作

有时候&#xff0c;Bug 并不体现在程序错误上&#xff0c;而是行为偏差。在一次常规功能测试中&#xff0c;我们发现移动端某个提交请求被触发了两次&#xff0c;虽然后端做了幂等处理&#xff0c;但频繁请求仍可能带来性能问题、错误日志膨胀、以及潜在副作用。 这类问题常被…
阅读更多...
oracle 表空间与实例妙用，解决业务存储与权限处理难题

oracle 表空间与实例妙用，解决业务存储与权限处理难题

oracle 表空间与实例妙用&#xff0c;解决业务存储与权限处理难题 一、方案背景 在同一个研发数仓中&#xff0c;现使用 Oracle 数据库存储生产和质量的数据。为了满足业务发展需求&#xff0c;需要新增财务数据的存储&#xff0c;同时确保不影响现有的生产和质量数据。本方案…
阅读更多...
迅为RK3576开发板NPU环境搭建和使用rknn-toolkit2功能演示模型转换

迅为RK3576开发板NPU环境搭建和使用rknn-toolkit2功能演示模型转换

开发板采用核心板底板结构&#xff0c;在我们的资料里提供了底板的原理图工程以及PCB工程&#xff0c;可以实现真正意义上的裁剪、定制属于自己的产品&#xff0c;满足更多应用场合。 迅为针对RK3576开发板整理出了相应的开发流程以及开发中需要用到的资料&#xff0c;并进行详…
阅读更多...
如何在 Python 中连接 Elasticsearch 并使用 Qwen3 来实现 RAG

如何在 Python 中连接 Elasticsearch 并使用 Qwen3 来实现 RAG

今天的这篇文章是 “在本地电脑中部署阿里 Qwen3 大模型及连接到 Elasticsearch” 的续篇。我们接着上次的文章&#xff0c;继续探索如何使用 Qwen3 来实现 RAG。在本练习中&#xff0c;我们使用 Elastic Stack 9.0.1 版本。 创建 Elasticsearch API key 我们按照如下的步骤来…
阅读更多...
Domain 层完全指南（面向 iOS 开发者）

Domain 层完全指南（面向 iOS 开发者）

目录 为什么需要 Domain 层清晰的三层架构核心概念&#xff1a;Entity / Value Object / Use Case / RepositorySwift 代码实战测试策略在旧项目中落地的步骤结语 1 为什么需要 Domain 层 在传统 MVC / MVVM 中&#xff0c;我们往往把业务规则写进 ViewController 或 ViewMod…
阅读更多...
华为OD机试_2025 B卷_矩形相交的面积（Python，100分）（附详细解题思路）

华为OD机试_2025 B卷_矩形相交的面积（Python，100分）（附详细解题思路）

题目描述 给出3组点坐标(x, y, w, h)&#xff0c;-1000<x,y<1000&#xff0c;w,h为正整数。 (x, y, w, h)表示平面直角坐标系中的一个矩形&#xff1a; x, y为矩形左上角坐标点&#xff0c;w, h向右w&#xff0c;向下h。 (x, y, w, h)表示x轴(x, xw)和y轴(y, y-h)围成…
阅读更多...
17、Rocket MQ快速实战以及核⼼概念详解

17、Rocket MQ快速实战以及核⼼概念详解

⼀ 、MQ简介 MQ&#xff1a;MessageQueue&#xff0c;消息队列。是在互联⽹中使⽤⾮常⼴泛的—系列服务中间件。 这个词可以分两个部分来看&#xff0c; —是Message&#xff1a;消息。消息是在不同进程之间传递的数据。这些进程可以部署在同—台机器上&#xff0c;也可以 分…
阅读更多...
设计模式之手写策略模式实现动态支付（Java实现）

设计模式之手写策略模式实现动态支付（Java实现）

首先&#xff0c;定义一个接口类 import java.util.Map;public interface PayInterface {/*** 支付方法* param amount 支付金额* param paymentInfo 支付信息&#xff08;如卡号、密码等&#xff09;* return 支付结果*/boolean pay(double amount, Map<String, String>…
阅读更多...
最新文章

Copyright @ 2022~2023