如何对oracle和mysql进行分区分表

weijx_

已于 2023-08-07 22:14:46 修改

阅读量1.9k

点赞数 2

分类专栏：数据库文章标签： oracle mysql

于 2023-08-07 21:34:14 首次发布

本文链接：https://blog.csdn.net/weijx_/article/details/132154938

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

前提：使用自带的分区和分表机制进行操作

oracle,mysql分区分表

分区

分区是一种将一个大的表或索引分割成多个小的部分的技术，每个部分称为一个分区。分区可以提高数据的管理和查询效率，因为可以根据不同的条件对不同的分区进行操作，而不需要扫描整个表或索引。例如，可以根据日期对表进行分区，然后只查询或删除某个时间范围内的数据，而不影响其他时间范围的数据。

Oracle和MySQL都支持分区的功能，但是实现方式和使用方法有所不同。
下面我会详细地介绍一下Oracle和MySQL的分区的特点和差异。

Oracle支持四种类型的分区：

范围分区、列表分区、散列分区和复合分区1。范围分区是根据分区键的值落在某个范围内来划分分区，例如按照年份或月份划分。列表分区是根据分区键的值匹配某个列表中的值来划分分区，例如按照国家或地区划分。散列分区是根据分区键的值经过一个散列函数计算出一个散列值来划分分区，例如按照用户ID或订单号划分。复合分区是将两种或以上的类型的分区组合起来使用，例如按照年份和国家划分。

Oracle还支持一些扩展功能，例如间隔分区、参考分区、虚拟列分区等。

MySQL支持五种类型的分区：

范围分区、列表分区、散列分区、线性散列分区和键值分区。范围分区和列表分区与Oracle类似，但是MySQL不支持复合分区。散列分区和线性散列分区也与Oracle类似，但是MySQL不允许用户自定义散列函数，而是使用内置的函数。
键值分区是根据一组列或表达式的值经过一个内置的散列函数计算出一个散列值来划分分区，

例如按照用户名或邮箱划分。要创建一个带有分区的表，

Oracle和MySQL都需要在创建表时指定PARTITION BY子句，并为每个分区指定一个名称和一个选项。
例如，以下语句可以在Oracle中创建一个按照年份范围划分的表：

CREATE TABLE sales (
  order_id NUMBER(10) PRIMARY KEY,
  customer_id NUMBER(10) NOT NULL,
  product_id NUMBER(10) NOT NULL,
  order_date DATE NOT NULL,
  amount NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (order_date) (
    PARTITION sales_2019 VALUES LESS THAN (2020),
    PARTITION sales_2020 VALUES LESS THAN (2021),
    PARTITION sales_2021 VALUES LESS THAN (2022),
    PARTITION sales_2022 VALUES LESS THAN (2023),
    PARTITION sales_2023 VALUES LESS THAN (2024)
);


以下语句可以在MySQL中创建一个类似的表：
CREATE TABLE sales (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION sales_2019 VALUES LESS THAN (2020),
    PARTITION sales_2020 VALUES LESS THAN (2021),
    PARTITION sales_2021 VALUES LESS THAN (2022),
    PARTITION sales_2022 VALUES LESS THAN (2023),
    PARTITION sales_2023 VALUES LESS THAN (2024)
);

要查询一个带有分区的表，

Oracle和MySQL都可以在查询中使用普通的SQL语句，数据库会自动选择需要扫描的分区，并优化查询计划。
例如，以下语句可以在Oracle和MySQL中查询2020年的销售数据：

SELECT * FROM sales
WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';

要管理一个带有分区的表，Oracle和MySQL都提供了一些分区相关的语句，例如添加、删除、合并、拆分、重建、交换等。
例如，以下语句可以在Oracle中添加一个新的分区：

ALTER TABLE sales
ADD PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01');

以下语句可以在MySQL中删除一个旧的分区：

ALTER TABLE sales
DROP PARTITION sales_2019;

如何对一个已经有数据的Oracle表进行时间分区的方法有以下几种：

使用ALTER TABLE … MOVE … PARTITION …语句，将原来的表移动到一个新的分区表中，然后删除原来的表。这种方法的优点是比较简单和直接，但是缺点是需要额外的空间和时间，而且会导致索引和约束失效，需要重新创建。
使用ALTER TABLE … SPLIT PARTITION …语句，将原来的表分割成多个分区，然后删除不需要的分区。这种方法的优点是可以保留索引和约束，但是缺点是需要指定每个分区的范围，而且可能会影响性能和并发。
使用DBMS_REDEFINITION包，将原来的表在线重定义为一个新的分区表，然后同步数据和元数据。这种方法的优点是可以在线进行，不影响用户访问，而且可以自动处理索引和约束，但是缺点是比较复杂和耗时，而且需要满足一些先决条件。

比如使用ALTER TABLE … SPLIT PARTITION …语句来对 sales 表进行分区，

需要先确定要按照什么字段和条件来划分分区。例如，假设想按照日期字段date_field来划分分区，并且每个月划分一个分区。
那么可以使用以下语句来创建一个新的分区表：

CREATE TABLE sales_new (
  -- 原表的字段定义
  ...
)
PARTITION BY RANGE (date_field) (
  -- 按照月份划分分区
  PARTITION p_202101 VALUES LESS THAN (DATE '2021-02-01'),
  PARTITION p_202102 VALUES LESS THAN (DATE '2021-03-01'),
  PARTITION p_202103 VALUES LESS THAN (DATE '2021-04-01'),
  ...
);

然后可以使用以下语句将原来的表的数据移动到新的分区表中：

INSERT INTO sales_new
SELECT * FROM sales;

最后可以使用以下语句删除原来的表，并将新的表重命名为原来的表名：

DROP TABLE sales;
RENAME sales_new TO eval_sum_organization;

那么，当插入一条2023年的数据时，例如：

INSERT INTO sales VALUES (1001, 101, 201, '2023-01-01', 100.00);

Oracle会自动地将这条数据存储在sales_2023分区中，而不会影响其他分区。同样地，当查询2023年的数据时，例如：

SELECT * FROM sales
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Oracle会自动地只扫描sales_2023分区，而不会扫描其他分区。这样可以大大减少数据的读写和传输量，提高数据的访问速度和性能。

想查看数据落在哪个分区

可以使用以下几种方法：
使用ROWID来获取数据的物理位置，然后使用DBMS_ROWID包来解析ROWID的组成部分，
包括数据对象ID、文件号、块号和行号。然后可以使用数据对象ID来查询用户对象视图，获取分区的名称。
例如，以下语句可以查询order_id为1001的数据落在哪个分区：

SELECT subobject_name FROM user_objects
WHERE data_object_id = (
  SELECT dbms_rowid.rowid_object (ROWID) FROM sales
  WHERE order_id = 1001
);

使用分区扩展语法来指定分区的名称，然后使用EXISTS子句来判断数据是否存在于该分区。

例如，以下语句可以查询order_id为1001的数据是否存在于sales_2023分区：

SELECT CASE WHEN EXISTS (
  SELECT 1 FROM sales PARTITION (sales_2023)
  WHERE order_id = 1001
) THEN 'YES' ELSE 'NO' END AS result FROM dual;

统计使用

想统计一个分区表的总量，可以使用COUNT函数来计算表中的记录数，或者使用SUM函数来计算表中的某个字段的总和。
例如，以下语句可以统计eval_sum_organization表中的总记录数：

SELECT COUNT(*) FROM eval_sum_organization;

以下语句可以统计eval_sum_organization表中的amount字段的总和：

SELECT SUM(amount) FROM eval_sum_organization;

如果想对一个分区表进行排名，可以使用RANK函数或ROW_NUMBER函数来给每个记录分配一个排名或序号，
然后根据排名或序号进行排序或过滤。
例如，以下语句可以按照amount字段对eval_sum_organization表进行降序排名，并只显示前十名：

SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY amount DESC) AS rank
  FROM eval_sum_organization
) WHERE rank <= 10;

以下语句可以按照date_field字段对eval_sum_organization表进行升序序号，并只显示第十行：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY date_field) AS rn
  FROM eval_sum_organization
) WHERE rn = 10;

如果想按照单位下所有部门进行排序，可以使用PARTITION BY子句来将表按照单位字段进行分组，
然后在每个分组内按照部门字段进行排序。
例如，以下语句可以按照单位和部门对eval_sum_organization表进行升序排序：

SELECT * FROM eval_sum_organization
ORDER BY unit, department;

分表

那么可以使用动态SQL来根据日期拼接子表的名称，然后执行查询。
例如，假设有一个按照天分割成多个子表的表，名为table，子表的名称是tableYYYYMMDD，
那么可以使用以下语句来查询2022年10月1日的数据：

-- Oracle
DECLARE
  v_sql VARCHAR2(1000);
  v_date DATE := DATE '2022-10-01';
BEGIN
  v_sql := 'SELECT * FROM table' || TO_CHAR(v_date, 'YYYYMMDD');
  EXECUTE IMMEDIATE v_sql;
END;

-- MySQL
SET @sql = CONCAT('SELECT * FROM table', DATE_FORMAT('2022-10-01', '%Y%m%d'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;