分区表

基于`mariadb`分区表

Partition key , unique key, primary key建立分区表遵循规则

通过information_schema.PARTITIONS表查看表分区状态select * from information_schema.partitions where table_schema='demo' and table_name='test1';。

按整数分区：

sql

# 创建实验数据库
CREATE DATABASE IF NOT EXISTS demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 创建实验数据表
USE demo;

DROP TABLE IF EXISTS test1;

CREATE TABLE IF NOT EXISTS test1(
    partition_field     INT NOT NULL COMMENT '分区字段',
    `uuid`          	VARCHAR(64) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
partition by range columns(partition_field) (
	partition p0 values less than(1),
	partition p1 values less than(2),
	partition p2 values less than(3),
	partition p3 values less than(4),
	partition p4 values less than(5)
);

delimiter |

drop procedure if exists proc_temp|

create procedure proc_temp()
begin
	declare var_counter int default 0;
    while var_counter<50000 do
		insert into test1(partition_field,`uuid`) values(FLOOR(0+RAND()*5),uuid());
        set var_counter = var_counter + 1;
    end while;
end|

delimiter ;

call proc_temp();

按照日期分区示例脚本：

bash

# 创建表
create table if not exists gameReportFLSeq(
	id bigint not null auto_increment,
    ruleCode varchar(16) not null,
    seqNumber varchar(16) not null,
    userIdHy bigint not null,
    `type` int not null,
    `date` datetime not null,
    primary key(id, date)
);

# 创建分区
alter table gameReportFLSeq partition by range columns(date)(
	partition p20150512 values less than ('2015-05-12 07:30:00'),
	partition pr values less than maxvalue
);

# 重新分区
ALTER TABLE gameReportFLSeq REORGANIZE PARTITION pr INTO ( 
    partition p20150513 VALUES LESS THAN ('2015-05-13 07:30:00'), 
    partition pr VALUES LESS THAN MAXVALUE 
);

# 取消所有分区
alter table gameReportFLSeq remove partitioning;

分区表 ​

基于mariadb分区表 ​

分区表

基于`mariadb`分区表