欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql分区表

程序员文章站 2022-06-02 09:22:14
...

一、分区表的原理:

分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个相同的索引,从存储引擎的角度来看,底层表和一个普通表没有任何不同,存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

MySQL支持5种分区方式:RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区。每种分区都有自己的使用场景。

二、分区表的限制:

1.一个表最多只能有1024个分区(mysql5.6之后支持8192个分区) 2.在mysql5.1中分区表达式必须是整数,或者是返回整数的表达式,在5.5之后,某些场景可以直接使用字符串列和日期类型列来进行分区(使用varchar字符串类型列时,一般还是字符串的日期作为分区)。 3.如果分区字段中有主键或者唯一索引列,那么所有主键列和唯一索引列都必须包含进来,如果表中有主键或唯一索引,那么分区键必须是主键或唯一索引 4.分区表中无法使用外键约束 5.mysql数据库支持的分区类型为水平分区,并不支持垂直分区,因此,mysql数据库的分区中索引是局部分区索引,一个分区中既存放了数据又存放了索引,而全局分区是指的数据库放在各个分区中,但是所有的数据的索引放在另外一个对象中 6.目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

三、分区表的使用语法:

创建分区表:

range分区


CREATE TABLE table_name (

id INT AUTO_INCREMENT,

fieldX VARCHAR(100) NOT NULL,

order_day DATETIME NOT NULL,

PRIMARY KEY(id, order_day)

) ENGINE=Innodb

PARTITION BY RANGE(YEAR(order_day)) (

PARTITION p_2015 VALUES LESS THAN (2015),

PARTITION p_2016 VALUES LESS THAN (2016),

PARTITION p_2017 VALUES LESS THAN (2017),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

这段语句表示将表内数据按照order_day的年份范围进行分区,2015年一个区,2016一个,2017一个,剩下的一个;注:MAXVALUE为最大值

注意:如果要这么做,则order_day必须包含在主键中,如果没有设置MAXVALUE 这个分区,会产生一个问题,就是当年份超过了最大值,到了2018、2019时,需要手动创建这些分区,否则插入数据时会报错。

如果你不愿意这么做,可以尝试使用HASH,如:

CREATE TABLE table_name (
    
id INT PRIMARY KEY AUTO_INCREMENT,
    
order_day DATETIME NOT NULL,
    
PRIMARY KEY(id)
    
)ENGINE=Innodb

PARTITION BY HASH (id DIV 1000000);

这种分区表示每 100W 条数据建立一个分区,且没有阈值范围的影响

新增分区:

ALTER TABLE table_name ADD PARTITION (PARTITION p_2018 VALUES LESS THAN (2018));

删除分区:

ALTER TABLE table_name DROP PARTITION p_2018;

这里需要注意的是,一旦删除了一个分区,该分区的所有数据也将被删除

分区的合并:

ALTER TABLE table_name

REORGANIZE PARTITION p_201701,p_201702,p_201703 INTO

(PARTITION p_2017Q1 VALUES LESS THAN (201704));

分区的分解:

ALTER TABLE table_name

REORGANIZE PARTITION p_2017Q1 INTO(
    
PARTITION p_201701 VALUES LESS THAN (201702),
    
PARTITION p_201702 VALUES LESS THAN (201703),
    
PARTITION p_201703 VALUES LESS THAN (201704))

重建分区:

ALTER TABLE table_name rebuild PARTITION p_2017;

重建分区的操作和先删除保存在分区中的所有记录,然后重新插入它们具有同样的效果,它可用于整理分区的碎片

优化分区:

ALTER TABLE table_name OPTIMIZE PARTITION p_2017;

如果从分区中删除了大量的行,或者对一个带有可变长度的行(有VARCHAR、BLOB、TEXT类型的列)做了许多修改,可以使用 "ALTER TABLE ... OPTIMIZE PARTITION" 来回收没有使用的空间,并整理分区数据文件的碎片。

分析分区:

ALTER TABLE table_name ANALYZE PARTITION p_2017;

此操作可以读取并保存分区的键分布

修补分区:

ALTER TABLE table_name REPAIR PARTITION p_2017;

用来修补被破坏的分区

检查分区:

ALTER TABLE table_name CHECK PARTITION p_2017;

这个命令可以告诉你表table_name的分区p_2017中的数据或索引是否已经被破坏。如果发生了这种情况,可使用 "ALTER TABLE ... REPAIR PARTITION" 来修补该分区。

对已有表进行分区

一般的,我们可以用如下两种方式办到:

1.可以使用ALTER TABLE来进行更改表为分区表,这个操作会创建一个分区表,然后自动进行数据copy然后删除原表。

ALTER TABLE table_name PARTITION BY RANGE (YEAR(order_day)) (
PARTITION p_2018 VALUES LESS THAN (2018))

2.新建一个和原来表一样的分区表,然后把数据从原表导出,接着导入新表。

查看数据在每个分区中的分布情况

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='table_name';(不是很准确)

注意事项:

1.创建分区之后 想要新增分区 分区的value不能小于原来的分区value 否则报错 VALUES LESS THAN value must be strictly increasing for each partition

2.如果想要在分区中间插入一个分区 可以分解分区 3.对于 RANGE 分区的查询,优化器只能对 YEAR(), TO_DAYS(),TO_SECONDS(), UNIX_TIMESTAMP()这类函数进行优化