MySQL 与 Oracle 分区表详解：相同点与不同点

在数据库管理中，随着数据量的不断增长，如何高效地存储和查询数据成为了一个关键问题。分区表技术通过将大型表划分为多个更小、更易于管理的部分，显著提升了数据库的性能和可维护性。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'  -- 指定数据库dw
  AND 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';