闲碎记事本
Yan
2023-03-12
目录

MySQL分区

# 概念

分区是指根据一定的规则将一个大表分解成多个更小的部分，这里的规则一般就是利用分区规则将表进行水平切分；

逻辑上没有发生变化但实际上表已经被拆分成了多个物理对象，每个分成被划分成了一个独立的对象。

相对于没有分区的当个表而言分区的表有很多的优势包括： 并发统计查询、快速归档删除分区数据、分散存储、查询性能更佳。

mysql5.7以后查询语句支持指定分区例如：

SELECT * FROM t PARTITION (p0,p1) WHERE c < 5

指定分区同样适用DELETE,INSERT,REPLACE,UPDATE, andLOAD DATA,LOAD XML.

# 分区类型

# RANGE分区

  • 基于一个给定的连续区间范围，RANGE主要是基于整数的分区，对于非整形的字段需要利用表达式将其转换成整形。
  • 分区连续不能重复

# RANGE

# 建表语法
CREATE TABLE partition_test_table (
      id_card varchar(20)  NOT NULL COMMENT '身份证',
      name varchar(12)  NOT NULL COMMENT '名字',
      sex varchar(30) DEFAULT NULL COMMENT '性别',
      profession_id int(10) DEFAULT NULL COMMENT '职业ID',
      birthday datetime  DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '生日',
      version int(10)  DEFAULT 0  NOT NULL COMMENT '版本',
      logic_state datetime  DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '逻辑状态',
      PRIMARY KEY (id_card,logic_state,profession_id),
      KEY INDEX_NAME (name)
) ENGINE = InnoDB AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8 COMMENT = 'range分区测试表'
    partition by range  (profession_id) (
        PARTITION JOB1 VALUES LESS THAN (1) ENGINE = InnoDB,
        PARTITION JOB2 VALUES LESS THAN (2) ENGINE = InnoDB,
        PARTITION JOB3 VALUES LESS THAN (3) ENGINE = InnoDB,
        partition JOB_OTHER values less than maxvalue
        ) ;

# RANGE COLUMNS:

# 建表语法
CREATE TABLE partition_test_table (
     id bigint(20)  AUTO_INCREMENT NOT NULL COMMENT '自增ID',
     log_type varchar(12)  NOT NULL COMMENT '日志类型',
     log_content varchar(30) DEFAULT NULL COMMENT '日志类型',
     log_time datetime  DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '日志发生时间',
     PRIMARY KEY (id,log_time),
     KEY INDEX_NAME (log_time)
) ENGINE = InnoDB AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8 COMMENT = '模块监控记录'
    partition by range columns (log_time) (
        PARTITION P20210501 VALUES LESS THAN ('2021-05-01') ENGINE = InnoDB,
        PARTITION P20210601 VALUES LESS THAN ('2021-06-01') ENGINE = InnoDB,
        PARTITION P20210701 VALUES LESS THAN ('2021-07-01') ENGINE = InnoDB
        ) ;

#

# 操作分区

  • 拥有partition JOB_OTHER values less than maxvalue 的表,不能新增分区
  • 新增分区列必须大于现有分区
#新增分区
ALTER TABLE 表名 ADD PARTITION (PARTITION 分区名 VALUES LESS THAN (20100403));
#设置分区最大上限
alter table 表名 add PARTITION  (PARTITION 分区名 VALUES LESS THAN MAXVALUE);

#拆分分区 ,拆分的值必须严格按照 value值的顺序
#拆分分区不能改变原有分区范围，除非是最后一个分区
ALTER TABLE 表名 REORGANIZE PARTITION 分区名 INTO (
    PARTITION 拆分后分区1 VALUES LESS THAN (2),
    PARTITION 拆分后分区2 VALUES LESS THAN (3)
);

#删除分区 并删除数据
ALTER TABLE 表名 DROP PARTITION 分区名

#移除表分区的定义，不删除数据
ALTER TABLE 表名 REMOVE PARTITIONING ;

# LIST

  • 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  • LIST是枚举值列表的集合
  • LIST分区列是非null列，否则插入null值如果枚举列表里面不存在null值会插入失败

# LIST

# 建表语法
create table  partition_test_table(
    id int AUTO_INCREMENT COMMENT 'ID',
		id_card	varchar(20)  COMMENT '身份证',
	  user_name	varchar(12)  NOT NULL COMMENT '名字',
	  sex varchar(30) DEFAULT NULL COMMENT '性别',
    lv  int  COMMENT '级别',
		PRIMARY KEY (id,lv)
) partition by LIST (lv)(
    partition lv1 values in (1,2,3),
		partition lv2 values in (4,5,6),
		partition lv3 values in (7,8,9)
);
# LIST COLUMNS
create table  partition_test_table(
    id int AUTO_INCREMENT COMMENT 'ID',
		id_card	varchar(20)  COMMENT '身份证',
	  user_name	varchar(12)  NOT NULL COMMENT '名字',
	  sex varchar(30) DEFAULT NULL COMMENT '性别',
    lv  int  COMMENT '级别',
		PRIMARY KEY (id,lv)
) partition by LIST columns(lv)(
    partition lv1 values in (1,2,3),
		partition lv2 values in (4,5,6),
		partition lv3 values in (7,8,9)
);

# 操作分区

#新增分区
ALTER TABLE 表名 ADD PARTITION (PARTITION lv4 VALUES  IN(11,12));

#拆分分区
ALTER TABLE 表名 REORGANIZE PARTITION  现有分区1,现有分区2 INTO  
(PARTITION 拆分后分区1 VALUES IN (1,2,3),PARTITION 拆分后分区2 VALUES IN (4,5,6));

#合并分区
ALTER TABLE 表名 REORGANIZE PARTITION  现有分区1,现有分区2 
INTO (PARTITION 合并后的分区 VALUES IN (1,5,6,2,7,8))

# HASH

  • 基于用户定义的表达式的返回值来进行选择的分区
  • 基于给定的分区个数，将数据分配到不同的分区,
  • HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数

# HASH

CREATE TABLE partition_test_table (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY  HASH( YEAR(hired) )
PARTITIONS 6;

# LINEAR HASH

CREATE TABLE partition_test_table (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY  LINEAR HASH( YEAR(hired) )
PARTITIONS 6;

#

# 操作分区

#合并删除分区
ALTER TABLE 表名 COALESCE PARTITION 3;
#增加分区
ALTER TABLE 表名 add PARTITION partitions 4;

#

# KEY

  • KEY分区支持除text和BLOB之外的所有数据类型的分区
  • KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区
  • 表中存在主键或者唯一键时，如果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。

# KEY

CREATE TABLE partition_test_table (
    id INT NOT NULL,
    var CHAR(5)
)
PARTITION BY  KEY (var)
PARTITIONS 3;

# LINEAR KEY

CREATE TABLE partition_test_table (
    id INT NOT NULL,
    var CHAR(5)
)
PARTITION BY LINEAR KEY (var)
PARTITIONS 3;

# 操作分区

#合并删除分区
ALTER TABLE 表名 COALESCE PARTITION 3;
#增加分区
ALTER TABLE 表名 add PARTITION partitions 4;

#

# 子分区

•对每个分区表的每个分区进行再次分隔， •目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。 •子分区可以将原本的数据进行再次的分区划分。

#不定义每个子分区子分区的名字和路径由分区决定
CREATE TABLE partition_test_table (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
    
 #定义每个子分区的分区名和各自的路径
 CREATE TABLE partition_test_table (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

# 操作子分区

•合并分区的子分区也必须等于创建是数量，因为必须和创建分区时每个分区只有两个子分区保持一致， 合并分区不会造成数据的丢失 拆分同理。

# 合并子分区

ALTER TABLE 表名 REORGANIZE PARTITION p0,p1 INTO (
    PARTITION m1 VALUES LESS THAN (2000)
    ( SUBPARTITION n0,
      SUBPARTITION n1
     ) 
);

# 拆分子分区

ALTER TABLE 表名 REORGANIZE PARTITION m1 INTO (
     PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        )
    
);

#

# 查看分区信息

# 查看分区
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = '表名';

# 查看分区 包含子分区
SELECT PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'partition_test_table';


#看表是不是分区表,分区表:Create_options = partitioned 
show table status like '%表名%';

#分区表 执行计划 mysql 8.0 后无需添加 partitions 关键字
explain partitions select * from 表名;

# 移除分区表的定义

# 查看分区
ALTER TABLE 表名 REMOVE PARTITIONING ;

# 注意事项

  • 存在主键或者唯一键的表不能使用主键或者唯一键之外的字段作为分区字段。
  • 分区表中无法使用外键约束。
  • *.5.7以前的版本显示分区的执行计划使用：explain PARTITIONS；5.7以后直接执行：explain*
  • 注意分区名的大小写敏感问题，和关键字问题。
  • 无论哪种分区类型，要么分区表中没有主键或唯一键，要么主键或唯一键包含在分区列里面
  • 没有强制要求分区列非空，建议分区的列为NOT NULL的列；在RANGE 分区中如果往分区列中插入NULL值会被当作最小的值来处理，在LIST分区中NULL值必须在枚举列表中否则插入失败，在HASH/KEY分区中NULL值会被当作0来处理。
  • 基于时间类型的字段的转换函数mysql提供了YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()
  • 拆分合并分区后会导致修改的分区的统计信息失效,没有修改的分区的统计信息还在,不影响新插入的值加入到统计信息；这时需要对表执行Analyze操作.
  • 针对非整形字段进行RANG\LIST分区建议使用COLUMNS分区。

# 参考资料

# 指定子分区路径

#

上次更新: 2024/11/05, 08:29:31
Myshell常用函数
跨域处理

← 跨域处理→

最近更新
01
opssl命令
10-31
02
gitlab迁移
10-31
03
JAVA系统API
09-26
更多文章>
Theme by Vdoing | Copyright © 2022-2024 YAN | MIT License