Oracle、MySQL——表分区

Oracle、MySQL——表分区

一、Oracle的分区方式

-- Oracle分区规则
PARTITION BY
   { RANGE (column_list)
        (index_partitioning_clause)
   | HASH (column_list)
        { individual_hash_partitions
        | hash_partitions_by_quantity
        }
   }
   
-- 示例
CREATE TABLE T_PARTITION_TEST(
	ID INT,
	NAME VARCHAR2(20),
	FDATE DATE
)
PARTITION BY RANGE(FDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) -- 按月份自动分区
(
	-- 必须至少定义一个分区
	PARTITION P0 VALUES LESS THAN (TO_DATE('2021-01-01', 'yyyy-MM-dd'))
);


-- 表分区信息
SELECT PARTITION_NAME, SUBPARTITION_COUNT, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T_PARTITION_TEST';

二、MySQL的分区方式

1、利用MySQL分区

-- MySQL分区有限制，分区键必须包含唯一键的一部分，且不支持自动分区，故用定时事件进行创建分区
-- 单表添加分区
delimiter $$
drop procedure if exists add_partition $$
create procedure add_partition(in tableName varchar(100))
begin
	declare pname varchar(10); -- 分区名称
	declare num int default 1; -- 当前执行次数
	declare cyear int; -- 当前年份
	select year(now()) into cyear;
	while num < 13 do
		if num = 1 then
			set pname = concat('p', cyear, '12');
		else
			set pname = concat('p', cyear + 1, lpad(num-1, 2, 0));
		end if;
		set @csql = concat('alter table ', tableName, ' reorganize partition pmax into (partition ', pname,
			' values less than (\'', concat('2022-', lpad(num, 2, 0), '-01'), '\'), partition pmax values less than (maxvalue))');
		PREPARE stmt FROM @csql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		set num = num + 1;
	end while;
end $$
delimiter ;


-- 添加分区的事件
delimiter $$
drop event if exists add_partition_event $$
create event add_partition_event on schedule every 1 year starts '2021-10-04 00:00:00'
	do
	begin
		declare tableName varchar(50);
		declare v_finished int default 0;
		declare table_cursor cursor for (
			select distinct table_name from information_schema.partitions 
			where partition_name is not null and table_schema = (select database())
		);
		declare continue handler for not found set v_finished = 1;
		open table_cursor;
		table_loop:
		loop
			fetch table_cursor into tableName;
			if v_finished = 1 then
				leave table_loop;
			end if;
			call add_partition(tableName);
		end loop;
		close table_cursor;
		
	end $$
delimiter ;

2、利用中间件进行分区，常用的分区中间件：Mycat、ShardingJDBC，具体使用方法可参考官网。