澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:分区简介

2019-09-16 作者:数据库网络   |   浏览(77)

介绍

LIST分区和RANGE分区非常的相似,主要区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列,否则插入null值如果枚举列表里面不存在null值会插入失败,这点和其它的分区不一样,RANGE分区会将其作为最小分区值存储,HASHKEY分为会将其转换成0存储,主要LIST分区只支持整形,非整形字段需要通过函数转换成整形;5.5版本之后可以不需要函数转换使用LIST COLUMN分区支持非整形字段,在COLUMN分区中有详细的讲解。

 

澳门新萄京官方网站:分区简介。一、创建分区

List各个分区枚举的值只需要不相同即可,没有固定的顺序。

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';

澳门新萄京官方网站 1

 1.插入数据

insert into tblist(id,store_id) values(1,1),(7,7); 

往a、b两个分区中各插入一条记录

澳门新萄京官方网站 2

2.插入不在列表中的值

澳门新萄京官方网站 3 

当往分区中插入不在枚举列表中的值是会插入失败,插入null值如果null值不在枚举列表中也同样失败

澳门新萄京官方网站:分区简介。二、分区管理

1.增加分区

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));

注意:不能增加包含现有任意值的分区。

2.合并分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

将分区a,b合并为分区m

注意:同RANGE分区一样,只能合并相邻的几个分区,不能跨分区合并。例如不能合并a,c两个分区,只能通过合并a,b,c

澳门新萄京官方网站 4

3.拆分分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b,c INTO 
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

ALTER TABLE tblist REORGANIZE PARTITION  n INTO 
    ( PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (3,9,10));

澳门新萄京官方网站 5

经过两轮的拆分,枚举列表(3,9,10)排到了(2,7,8)的前面去了;其实是这样的,一开始合并abc成nm两个分区由于n中的枚举值小于m所以n在m的前面,后面再拆分n分区由于n分区在m分区的前面所以拆分出来的分区也是排在m分区的前面,由于a分区的值小于b分区的值所以a排在b的前面。

注意:1.在5.7.12版本中测试发现,合并和拆分分区重新定义的枚举值可以不是原来的值,如果原来的枚举值包含了数据而新合并或拆分的分区枚举值又不不包含原来的枚举值会造成数据丢失。虽然不知道为什么mysql不会禁止该行为,但是人为的要求无论是合并还是拆分分区枚举值保持不变,或者只能增加不能减少,这样能保证数据不丢失。

2.合并和拆分后的分区由于是相邻的分区进行合并和拆分会根据原本的分区的值新的分区也会在原本的分区的顺序位置。

4.删除分区

ALTER TABLE tblist DROP PARTITION e;

注意:删除分区同时会将分区中的数据删除,同时枚举的list值也被删除,后面无法往表中插入该值的数据。

三、其它分区

1.对时间字段进行分区

CREATE TABLE listdate (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST( YEAR(hired) ) 
(
    PARTITION a VALUES IN (1990),
    PARTITION b VALUES IN (1991),
    PARTITION c VALUES IN (1992),
    PARTITION d VALUES IN (1993)
);

ALTER TABLE listdate ADD INDEX ix_hired(hired);

INSERT INTO listdate() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');

EXPLAIN SELECT * FROM listdate WHERE hired='1990-01-01 10:00:00';

澳门新萄京官方网站 6

 LIST分区也支持对非整形的时间类型字段的转换分区。

四、移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意:使用remove移除分区是仅仅移除分区的定义,并不会删除数据和drop PARTITION不一样,后者会连同数据一起删除

 

 

参考: 

RANGE分区:

COLUMN分区:

HASH分区:

KEY分区:

子分区:

指定各分区路径:

分区索引:

分区介绍总结:

MySQL LIST分区,mysqllist分区

 

介绍

RANGE分区基于一个给定的连续区间范围,早期版本RANGE主要是基于整数的分区。在5.7版本中DATE、DATETIME列也可以使用RANGE分区,同时在5.5以上的版本提供了基于非整形的RANGE COLUMN分区。RANGE分区必须的连续的且不能重叠。使用

“VALUES LESS THAN ()” 来定义分区区间,非整形的范围值需要使用单引号,并且可以使用MAXVALUE作为分区的最高值。

 

 

一、RANGE分区

1.创建分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

alter table employees add index ix_store_id(store_id) ;
alter table employees add index ix_job_code(job_code) ;

p0:指分区p0,这个分区名的取值可以随便取值只要同一个表里面的每个分区名不重复即可,也不需要非得从0开始,你也可以使用a、b、c、d。

THEN():分区的范围值,这个值只能的连续不重叠的从小到大的值。

2.性能分析

插入测试数据

insert into employees(id,job_code,store_id) values(1,1001,1),(2,1002,2),(3,1003,3),(4,1004,4);

澳门新萄京官方网站 7

 从执行计划可以看到两个查询都用到了分区的效果;如果细心估计会发现第二个查询没有走索引,并不是使用小于就不会走索引而且执行计划分析评估任务不走索引的效果会更好,事实却是如果当前查询整个分区的数据时使用索引的话还需要去查询其它的字段还不如直接扫描整个分区来的快。

3.增加分区 

 由于当前分区值的范围是小于21,当向分区表中插入一个超过分区范围的值时会报错。这个时候可以增加一个分区,当你不确定需要给一个多大的上限值时可以使用MAXVALUE

澳门新萄京官方网站 8

alter table employees add PARTITION  (PARTITION p4 VALUES LESS THAN MAXVALUE);

注意:增加分区只能在最大端增加

4.删除分区

alter table employees drop  PARTITION p4;

注意:通过这种删除分区的方式会将分区中的数据也删除,慎用!!!!。但是通过删除分区的方式删除数据会比delete快很多,因为它相当于删除一个数据库一样因为每个分区都是一个独立的数据文件。用来删除历史分区数据是非常好的办法。

5.拆分合并分区

拆分合并分区统称为重新定义分区,拆分分为不会造成数据的丢失,只将会将数据从一个分区移动到另一个分区。

例1:将P0拆分成s1,s2两个分区

ALTER TABLE employees REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (6)
);

注意:原来分区p0的范围是[负无穷-6),所以新拆分的分区也必须是这范围,所以新的分区范围值最大不能超过6。

 澳门新萄京官方网站 9

分区由原来的p0[-6)变成了so[-3),s1[3-6),整个分区的范围还是不变。

例2:将s1,p1,p2合并为a,b两个分区

ALTER TABLE employees REORGANIZE PARTITION s1,p1,p2 INTO (
    PARTITION a VALUES LESS THAN (5),
    PARTITION b VALUES LESS THAN (16)
);

原本的s1,p1,p2分区范围是:[3-16)所以新的分区也必须和原本的分区相同,所以新的分区的值不能低于3不能高于16即可。

澳门新萄京官方网站 10

 

分区由原来的s1[3-6),p1[6-11),p2[11-16)变成了现在的a[3-5),b[5-16),总的范围没有发生变化

注意:无论是拆分还是合并分区都不能改变分区原本的覆盖范围,并且合并分区只能合并连续的分区不能跳过分区合并;并且不能改变分区的类型,例如不能把range分区改成key分区等。

二、日期字段分区方法

注意:RANG分区针对日期字段进行分区可以使用时间类型的函数进行转换成整形,但是如果你的查询语句需要利用分区那么查询语句也需要使用相同的时间函数进行查询。

1.使用YEAR()函数进行分区

CREATE TABLE employees1 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
); 

插入测试数据

insert into employees1(id,separated,job_code,store_id) values(1,'1990-03-04',1001,1),(2,'1995-03-04',1002,2),(3,'1998-03-04',1003,3),(4,'2016-03-04',1004,4);

 澳门新萄京官方网站 11

对于日期字段分区,查询条件使用> 、< 、betnwen、=都会利用分区查询,如果条件使用函数转换则不会走分区,比如使用YEAR()。

2.TIMESTAMP类型的列的分区方法

针对TIMESTAMP的日期类型的字段需要使用专门的UNIX_TIMESTAMP()函数进行转换

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

三、null值处理 

当往分区列中插入null值RANG 分区会将其当作最小值来处理即插入最小的分区中

CREATE TABLE test (
    id INT NOT NULL,
    store_id INT 
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
insert into test(id,store_id) values(1,null);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='test';

澳门新萄京官方网站 12

备注:文章中的示例摘自mysql官方参考手册

 

四、移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意:使用remove移除分区是仅仅移除分区的定义,并不会删除数据和drop PARTITION不一样,后者会连同数据一起删除

 

 

**分区系列文章: 澳门新萄京官方网站,**

RANGE分区:

LIST分区:

COLUMN分区:

HASH分区:

KEY分区:

子分区:

指定各分区路径:

分区建索引:

分区介绍总结:

总结MySQL的分区,总结MySQL分区

前言

     分区是指根据一定的规则将一个大表分解成多个更小的部分,这里的规则一般就是利用分区规则将表进行水平切分;逻辑上没有发生变化但实际上表已经被拆分成了多个物理对象,每个分成被划分成了一个独立的对象。相对于没有分区的当个表而言分区的表有很多的优势包括: 并发统计查询、快速归档删除分区数据、分散存储、查询性能更佳。

mysql5.7以后查询语句支持指定分区例如:“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分区同样适用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

数据库版本:mysql5.7.12 

是否支持分区

SHOW PLUGINS ;

澳门新萄京官方网站 13

查询partition的的状态是active就代表支持分区,如果是源码安装的话在编译的过程中要添加“-DWITH_PARTITION_STORAGE_ENGINE=1 ”。

注意: MERGE, CSV, or FEDERATED存储引擎不支持分区,同一个表所有的分区必须使用相同的存储引擎,不能分区1使用MYISAM分区2又使用INNODB;不同的分区表可以是不同的存储引擎。

分区介绍

目前mysql可用的分区类型主要有以下几种:

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

     LIST分区:是基于列出的枚举值列表进行分区。

     COLUMNS分区:可以无需通过表达式进行转换直接对非整形字段进行分区,同时COLUMNS分区还支持多个字段组合分区,只有RANGELIST存在COLUMNS分区,COLUMNS是RANGE和LIST分区的升级。

      HASH分区:基于给定的分区个数,将数据分配到不同的分区,HASH分区只能针对整数进行HASH,对于非整形的字段只能通过表达式将其转换成整数。

      KEY分区:支持除text和BLOB之外的所有数据类型的分区,key分区可以直接基于字段做分区无需转换成整数。 

说明

      1.注意分区名的大小写敏感问题,和关键字问题。

      2.无论哪种分区类型,要么分区表中没有主键或唯一键,要么主键或唯一键包含在分区列里面,对于存在主键或者唯一键的表不能使用主键或者唯一键之外的字段作为分区字段。

      3.5.7以前的版本显示分区的执行计划使用:explain PARTITIONS;5.7以后直接执行:explain

      4.没有强制要求分区列非空,建议分区的列为NOT NULL的列;在RANGE 分区中如果往分区列中插入NULL值会被当作最小的值来处理,在LIST分区中NULL值必须在枚举列表中否则插入失败,在HASH/KEY分区中NULL值会被当作0来处理。

      5.基于时间类型的字段的转换函数mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()"

      6.拆分合并分区后会导致修改的分区的统计信息失效,没有修改的分区的统计信息还在,不影响新插入的值加入到统计信息;这时需要对表执行Analyze操作.

      7.针对非整形字段进行RANGLIST分区建议使用COLUMNS分区。 

删除增加分区

在每个分区内容介绍中详细介绍了每种分区的用法,但是都是介绍在创建表的时候创建分区和修改删除分区单个,也可以在一张已经存在的表中加入分区,可以一次性删除整个表的分区。

1.移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意:使用remove移除分区是仅仅移除分区的定义,并不会删除数据和drop PARTITION不一样,后者会连同数据一起删除

2.对已经存在记录的表创建分区,以增加range分区为例,和创建表建分区的语法一样。

ALTER TABLE `tb_partition`.`tb_varchar` 
PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5000), PARTITION part1 VALUES LESS THAN (10000), PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;

注意:对已有的表创建分区之后,数据会按照分区的定义分布到各个分区文件当中

总结

以上就是MySQL的分区介绍总结的全部内容,希望本文对大家使用mysql的时候有所帮助。

前言 分区是指根据一定的规则将一个大表分解成多个更小的部分,这里的规则一般就是利用分区规则将表进...

总结

 重新定义LIST分区时只能重新定义相邻的分区,不能跳过分区定义,重新定义的分区列表枚举必须包含原分区的列表枚举,如果丢失某个包含记录的枚举值那么数据也将被删除;重新定义分区不能改变分区的类型。

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

介绍

LIST分区和RANGE分区非常的相似,主要区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列,否则插入null值如果枚举列表里面不存在null值会插入失败,这点和其它的分区不一样,RANGE分区会将其作为最小分区值存储,HASHKEY分为会将其转换成0存储,主要LIST分区只支持整形,非整形字段需要通过函数转换成整形;5.5版本之后可以不需要函数转换使用LIST COLUMN分区支持非整形字段,在COLUMN分区中有详细的讲解。

 

一、创建分区

List各个分区枚举的值只需要不相同即可,没有固定的顺序。

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';

澳门新萄京官方网站 14

 1.插入数据

insert into tblist(id,store_id) values(1,1),(7,7); 

往a、b两个分区中各插入一条记录

澳门新萄京官方网站 15

2.插入不在列表中的值

澳门新萄京官方网站 16 

当往分区中插入不在枚举列表中的值是会插入失败,插入null值如果null值不在枚举列表中也同样失败

二、分区管理

1.增加分区

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));

注意:不能增加包含现有任意值的分区。

2.合并分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

将分区a,b合并为分区m

注意:同RANGE分区一样,只能合并相邻的几个分区,不能跨分区合并。例如不能合并a,c两个分区,只能通过合并a,b,c

澳门新萄京官方网站 17

3.拆分分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b,c INTO 
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

ALTER TABLE tblist REORGANIZE PARTITION  n INTO 
    ( PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (3,9,10));

澳门新萄京官方网站 18

经过两轮的拆分,枚举列表(3,9,10)排到了(2,7,8)的前面去了;其实是这样的,一开始合并abc成nm两个分区由于n中的枚举值小于m所以n在m的前面,后面再拆分n分区由于n分区在m分区的前面所以拆分出来的分区也是排在m分区的前面,由于a分区的值小于b分区的值所以a排在b的前面。

注意:1.在5.7.12版本中测试发现,合并和拆分分区重新定义的枚举值可以不是原来的值,如果原来的枚举值包含了数据而新合并或拆分的分区枚举值又不不包含原来的枚举值会造成数据丢失。虽然不知道为什么mysql不会禁止该行为,但是人为的要求无论是合并还是拆分分区枚举值保持不变,或者只能增加不能减少,这样能保证数据不丢失。

2.合并和拆分后的分区由于是相邻的分区进行合并和拆分会根据原本的分区的值新的分区也会在原本的分区的顺序位置。

4.删除分区

ALTER TABLE tblist DROP PARTITION e;

注意:删除分区同时会将分区中的数据删除,同时枚举的list值也被删除,后面无法往表中插入该值的数据。

三、其它分区

1.对时间字段进行分区

CREATE TABLE listdate (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST( YEAR(hired) ) 
(
    PARTITION a VALUES IN (1990),
    PARTITION b VALUES IN (1991),
    PARTITION c VALUES IN (1992),
    PARTITION d VALUES IN (1993)
);

ALTER TABLE listdate ADD INDEX ix_hired(hired);

INSERT INTO listdate() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');

EXPLAIN SELECT * FROM listdate WHERE hired='1990-01-01 10:00:00';

澳门新萄京官方网站 19

 LIST分区也支持对非整形的时间类型字段的转换分区。

四、移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意:使用remove移除分区是仅仅移除分区的定义,并不会删除数据和drop PARTITION不一样,后者会连同数据一起删除

 

 

参考: 

RANGE分区:

COLUMN分区:

HASH分区:

KEY分区:

子分区:

指定各分区路径:

分区索引:

分区介绍总结:

MySQL自5.1开始对分区(Partition)有支持。

总结

 有两点非常重要需要注意,第一删除分区时要慎重因为会连同分区里的数据一并删除,拆分合并分区新的分区一定要和原来的分区的范围一致。RANGE COLUMN分区单独用章节进行讲解,。

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

总结

 重新定义LIST分区时只能重新定义相邻的分区,不能跳过分区定义,重新定义的分区列表枚举必须包含原分区的列表枚举,如果丢失某个包含记录的枚举值那么数据也将被删除;重新定义分区不能改变分区的类型。

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

LIST分区,mysqllist分区 介绍 LIST分区和RANGE分区非常的相似,主要区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合。二者在...

 

= 水平分区(根据列属性按行分)=
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。

 水平分区的几种模式:

* Range(范围) –  基于属于一个给定连续区间的列值,把多行分配给分区。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。

RANGE分区基于一个给定的连续区间范围,早期版本RANGE主要是基于整数的分区。在5.7版本中DATE、DATETIME列也可以使用RANGE分区,同时在5.5以上的版本提供了基于非整形的RANGE COLUMN分区。RANGE分区必须的连续的且不能重叠。使用"VALUES LESS THAN ()" 来定义分区区间,非整形的范围值需要使用单引号,并且可以使用MAXVALUE作为分区的最高值。

List(预定义列表) –  类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

* Hash(哈希) –  基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。例如DBA可以建立一个对表主键进行分区的表。

* Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL自身的哈希函数产生的。

* COLUMNS –  MYSQL5.5开始支持COLUMNS分区,可视为RANGE和LIST分区的一个进化。COLUMNS分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得到,不需要转化为整型。COLUMNS分区支持以下数据类型:

  • 所有的整型类型,如INT,TINYINT,SMALLINT,BIGINT。对FLOAT和DECIMAL不支持
  • 日期类型,DATE,DATETIME。其余日期类型不支持。
  • 字符串类型,如CHAR,VARCHAR,BINARY,VARBINARY。不支持BLOB和TEXT。

* Composite(复合模式) - 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。

 

 

= 垂直分区(按列分)=
举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。MySQL目前不支持垂直分区。

 

Range 分区

创建分区

CREATE DATABASE part;
CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES 
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

查看分区和分区的行数:

root@localhost 20:15:26[part]> SELECT PARTITION_NAME, TABLE_ROWS 
    ->     FROM INFORMATION_SCHEMA.PARTITIONS 
    ->     WHERE TABLE_NAME = 'e';
 ---------------- ------------ 
| PARTITION_NAME | TABLE_ROWS |
 ---------------- ------------ 
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
 ---------------- ------------ 

> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'part' AND TABLE_NAME LIKE 'e'; 

删除某个分区,不仅分区结构会被删除,分区里的数据也会被删除:

ALTER TABLE e DROP PARTITION p0;

增加分区:

> alter table e add partition (partition p4 values less than (5600)); 
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

说明有MAXVALUE值后,直接加分区是不可行的,需要使用 reorginize partition() 重新组织分区。且 RANGE 分区在加分区的时候,只能从最大值后面加,而最大值前面不可以添加。

也可以重新组织分区,重组分区的做法是,把原来的分区结构删除,创建新的分区结构,但新的分区结构必须要能容纳原分区的数据,否则会有报错:ERROR 1526 (HY000): Table has no partition for value 1234

# 把原range分区,重组为list分区,此过程不会丢失数据
> alter table e partition by list(id)(PARTITION a VALUES IN (1,5,6,1669,2005),PARTITION b VALUES IN (1234,2,7,8,337,9898));
Query OK, 7 rows affected (0.12 sec)
Records: 7  Duplicates: 0  Warnings: 0

 

 如果在一个没有分区定义的表中增加分区,直接使用 add 添加会报错:

> alter table e add partition (partition p0 values less than(50),PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150)); 
ERROR 1505 (HY000): Partition management on a not partitioned table is not possible

可以使用 partition by 新建:

> alter table e partition by range(id)(PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (1256320));
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0

也可以新建一个具有分区的表,结构一致,然后用insert into 分区表 select * from 原始表;

 

LIST 分区

创建 list 分区:

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME  FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist'; 

增加分区:

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));
# 注意:不能增加包含现有任意值的分区。

合并分区:

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8,13));
# 将分区a,b合并为分区m
# 可以新增值,例如这里增加了 13 值
# 注意:同 RANGE 分区一样,只能合并相邻的几个分区,不能跨分区合并。例如不能合并a,c两个分区,只能通过合并a,b,c

拆分分区:

ALTER TABLE tblist REORGANIZE PARTITION  m,c INTO 
(PARTITION m VALUES IN (1,5,6,3,9,10),
PARTITION n VALUES IN (2,7,8));

注意

1. 在5.7.12版本中测试发现,合并和拆分分区重新定义的枚举值可以不是原来的值,如果原来的枚举值包含了数据而新合并或拆分的分区枚举值又不不包含原来的枚举值会造成数据丢失。虽然不知道为什么mysql不会禁止该行为,但是人为的要求无论是合并还是拆分分区枚举值保持不变,或者只能增加不能减少,这样能保证数据不丢失。

2. 并和拆分后的分区由于是相邻的分区进行合并和拆分会根据原本的分区的值新的分区也会在原本的分区的顺序位置。

  1. 分区的语法基本是一致的,只是定义分区范围略有不同。如 RANGE 分区采用: VALUES LESS THAN (value),而 LIST 分区采用: VALUES IN (list) 。

删除分区:

ALTER TABLE tblist DROP PARTITION e;
# 注意:删除分区同时会将分区中的数据删除,同时枚举的list值也被删除,后面无法往表中插入该值的数据。

  

HASH 分区

基于给定的分区个数,将数据分配到不同的分区,HASH分区只能针对整数进行HASH,对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式,对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作,所以不建议使用复杂的表达式这样会影响性能。

MYSQL支持两种HASH分区,常规HASH(HASH)和线性HASH(LINEAR HASH) 。

1. 常规HASH

常规hash是基于分区个数的取模(%)运算。根据余数插入到指定的分区。

CREATE TABLE tbhash (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

ALTER TABLE tbhash ADD INDEX ix_store_id(store_id);

INSERT INTO tbhash() VALUES(1,100),(1,101),(2,102),(3,103),(4,104);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tbhash';

时间字段类型分区:

 

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

常规hash的分区非常的简便,通过取模的方式可以让数据非常平均的分布每一个分区,但是由于分区在创建表的时候已经固定了。如果新增或者收缩分区的数据迁移比较大。

2. 线性HASH(LINEAR HASH)

LINEAR HASH和HASH的唯一区别就是PARTITION BY LINEAR HASH。

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

线性HASH的计算原理参考:

3. 分区管理

减去3个分区:

ALTER TABLE tblinhash COALESCE PARTITION 3;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

注意:减去两个分区后,数据根据现有的分区进行了重新的分布,以'2003-04-14'为例:POWER(2, CEILING( LOG(2,3) ))=4,2003&(4-1)=3,3>=3,3&(CEILING(3/2)-1)=1,所以现在的'2003-04-14'这条记录由原来的p3变成了p1。

增加4个分区:

ALTER TABLE tblinhash add PARTITION partitions 4;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';

当在3个分区的基础上增加4个分区后,‘2003-04-14’由原来的p1变成了p3,而另一条记录由原来的p2变成了p6。

 

KEY分区

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

创建常规 KEY 分区

CREATE TABLE tb_key (
    id INT ,
    var CHAR(32) 
)
PARTITION BY KEY(var)
PARTITIONS 10;

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tb_key';

创建 LINEAR KEY 分区

同样key分区也存在线性KEY分区,概念和线性HASH分区一样。

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

 

COLUMN 分区 

COLUMN分区是5.5开始引入的分区功能,只有RANGE COLUMN和LIST COLUMN这两种分区;支持整形、日期、字符串;同RANGE和LIST的分区方式非常的相似。

COLUMNS和RANGE和LIST分区的区别

1. 针对日期字段的分区就不需要再使用函数进行转换了,例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。

  1. COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。

COLUMNS支持的类型

整形支持:tinyint,smallint,mediumint,int,bigint;不支持decimal和float

时间类型支持:date,datetime

字符类型支持:char,varchar,binary,varbinary;不支持text,blob

一、RANGE COLUMNS分区

1. 日期字段分区

CREATE TABLE members (
    id INT,
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
);

insert into members(id,joined) values(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='members';

2. 多字段组合分区

CREATE TABLE rcx (
    a INT,
    b INT
    )
PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (15,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

注意:

1)多字段的分区键比较是基于数组的比较。它先用插入的数据的第一个字段值和分区的第一个值进行比较,如果插入的第一个值小于分区的第一个值那么就不需要比较第二个值就属于该分区;如果第一个值等于分区的第一个值,开始比较第二个值同样如果第二个值小于分区的第二个值那么就属于该分区。

2)RANGE COLUMN的多列分区第一列的分区值一定是顺序增长的,不能出现交叉值,第二列的值随便,例如以下分区就会报错。

二、LIST COLUMNS分区

1. 非整形字段分区

CREATE TABLE listvar (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(hired) 
(
    PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),
    PARTITION b VALUES IN ('1992-01-01 10:00:00'),
    PARTITION c VALUES IN ('1993-01-01 10:00:00'),
    PARTITION d VALUES IN ('1994-01-01 10:00:00')
);

ALTER TABLE listvar ADD INDEX ix_hired(hired);

INSERT INTO listvar() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00'),(1,'1993-01-01 10:00:00');

LIST COLUMNS分区对分整形字段进行分区就无需使用函数对字段处理成整形,所以对非整形字段进行分区建议选择COLUMNS分区。

2. 多字段分区

CREATE TABLE listvardou (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(id,hired) 
(
    PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') ),
    PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),
    PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),
    PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') )
);

ALTER TABLE listvardou ADD INDEX ix_hired(hired);

INSERT INTO listvardou() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(2,'1992-01-01 10:00:00'),(3,'1993-01-01 10:00:00');

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='listvardou';

 

移除表分区

ALTER TABLE tablename REMOVE PARTITIONING ;

注意:使用remove移除分区是仅仅移除分区的定义,并不会删除数据和 drop PARTITION 不一样,后者会连同数据一起删除。

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:分区简介

关键词: