MySQL分区-分区类型（RANG LIST HASH KEY）-分区管理（RANGE&LIST HASH&KEY管理）

表分区类型：

RANGE：基于一个给定连续区间范围 把数据分配到不同的分区

LIST分区：类似RANGE分区 区别在LIST分区是基于枚举出的值列表分区 RANGE是基于给定的连续区间范围分区

HASH分区：基于给定的分区个数，把数据分配到不同的分区

KEY分区：类似HASH分区

 

无论哪种MySQL分区类型，要么分区表上没有主键/唯一键 要么分区表的主键/唯一键必须包含分区键 ，不能使用主键/唯一键字段之外的其他字段分区

CREATE TABLE emp (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (10),

    PARTITION p1 VALUES LESS THAN (20),

    PARTITION p2 VALUES LESS THAN (30)

);

--出现BUG 超过30的就会报错了

mysql> insert into emp(id,ename,hired,job,store_id) values ('7934','MILLER','1982-07-07','CLERK',50);

 

--alter语句

ALTER TABLE emp ADD PARTITION (partition p3 VALUES LESS THAN MAXVALUE);

 

--以日期作为RANGE分区的分区列

CREATE TABLE emp_date (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE(YEAR(separated)) (

    PARTITION p0 VALUES LESS THAN (1995),

    PARTITION p1 VALUES LESS THAN (2000),

    PARTITION p2 VALUES LESS THAN (2005)

);

 

--提供了RANGE COLUMNS分区支持非整数分区 创建日期分区就不需要通过函数进行转换

CREATE TABLE emp_date1 (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE COLUMNS(separated) (

    PARTITION p0 VALUES LESS THAN ('1996-01-01'),

    PARTITION p1 VALUES LESS THAN ('2001-01-01'),

    PARTITION p2 VALUES LESS THAN ('2006-01-01')

);

--RANGE分区功能适合使用的情况

1.当需要删除过期的数据时，只需要简单的ALTER TABLE emp DROP PARTITION p0删除p0分区中的数据。对于大数据来说 删除一个分区比运行一个delete语句有效的多

2.经常运行包含分区键的查询 MySQL可以很快确定只有某个或者某些分区需要扫描 因为其他分区不可能包含有该符合该where子句的任何记录

 

 

--LIST分区

LIST分区建立离散的值列表告诉数据库特定的值属于哪个分区 LIST分区在很多方面类似于RANGE分区

 区别在

LIST分区是从属于枚举列表值集合

RANGE分区是从属于一个连续区间集合

CREATE TABLE expenses (

    expense_date DATE NOT NULL,

    category INT,

    amount DECIMAL(10,3)

)PARTITION BY LIST(category)(

    PARTITION p0 VALUES IN (3,5),

    PARTITION p1 VALUES IN (1,10),

    PARTITION p2 VALUES IN (4,9),

    PARTITION p3 VALUES IN (2),

    PARTITION p4 VALUES IN (6)

);

 

--COLUNMS支持字符串

CREATE TABLE expenses1 (

    expense_date DATE NOT NULL,

    category VARCHAR(30),

    amount DECIMAL(10,3)

)PARTITION BY LIST COLUMNS(category)(

    PARTITION p0 VALUES IN ('lodging','food'),

    PARTITION p1 VALUES IN ('flights','ground transportation'),

    PARTITION p2 VALUES IN ('leisure','customer entertainment'),

    PARTITION p3 VALUES IN ('communications'),

    PARTITION p4 VALUES IN ('fees')

);

--columns分区

RANGE COLUMNS 

LIST COLUMNS支持整数 日期时间  字符串三大数据类型

所有整数类型 日期时间类型（date datetime） 字符串类型（char varchar binary varbinary 不支持text blob）

--examples:

CREATE TABLE rc3 (

    a INT,

    b INT

)PARTITION BY RANGE COLUMNS(a,b)(

    PARTITION p0 VALUES LESS THAN (0,10),

    PARTITION p1 VALUES LESS THAN (10,10),

    PARTITION p2 VALUES LESS THAN (10,20),

    PARTITION p3 VALUES LESS THAN (10,35),

    PARTITION p4 VALUES LESS THAN (10,MAXVALUE),

    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)

);

 

insert into rc3(a,b) values (1,10);

 

select (1,10) < (10,10) from dual;

 

SELECT

  partition_name part,

  partition_expression expr,

  partition_description descr,

  table_rows

FROM

  INFORMATION_SCHEMA.partitions

WHERE

  TABLE_SCHEMA = schema()

  AND TABLE_NAME = 'rc3';

insert into rc3(a,b) values (10,9);

--HASH分区

主要用来分散热点读 确保数据在预先确定个数的分区中尽可能平均分布

对一个表执行HASH分区时候 MySQL会对分区键应用一个散列函数 确定数据应当放在N个分区中的哪个分区中

支持两种HASH分区：常规HASH分区 线性HASH分区（LINEAR HASH分区） 

CREATE TABLE emp (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL

)PARTITION BY HASH (store_id) PARTITIONS 4;

 

 

insert into emp values (1,'Tom','2010-10-10','9999-12-31','Clerk',234);

insert into emp values (1,'Smith','2018-10-10','9999-12-31','waiter',229);

分区按照MOD(store_id,4) 分区名p0-3

常规HASH会出现的问题：如果增加分区或者合并分区的时候 取模算法需要改变 所有数据需要重新计算 为了降低分区管理的代价 提供线性HASH分区 分区函数是一个线性2的幂运算

 

计算保存的分区位置：

num:分区个数

N:最后分区位置

V=POWER(2,Ceiling(LOG(2,num)))

N=F(column_id) & (V-1)

当N>=num

N=N&(N-1)

 

CREATE TABLE emp (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL

)PARTITION BY LINEAR HASH (store_id) PARTITIONS 4;

 

insert into emp values (1,'Tom','2010-10-10','9999-12-31','Clerk',234);

advantages:在分区维护（增加 删除 合并 拆分分区）MySQL能够处理的更加迅速

disadvantage：对比常规HASH时候 线性HASH各个分区间数据分布不太均衡

 

KEY分区：类似HASH分区 HASH分区允许使用用户自定义的表达式  Key分区不允许使用用户自定义的表达式 需要使用Mysql服务器提供的HASH函数 同时HASH分区只支持整数分区 KEY 分区支持除了BLOB/text类型外其他类型的列作为分区键

(也可以使用LINEAR同KEY一样)

CREATE TABLE emp (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL

)PARTITION BY KEY (job) PARTITIONS 4;

--也可以不指定分区列 默认为主键 >非空唯一键做分区键>error

CREATE TABLE emp1 (

    id INT NOT NULL,

    ename VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT NOT NULL,

    primary key (id)

)PARTITION BY KEY () PARTITIONS 4;

 

--子分区（复合分区）

 

CREATE TABLE ts (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

);

 

3*2个分区 (p0 p1 p2)分区进一步被分区为2个子分区 

 

--处理NULL 将值看成是最小的值 或者0进行处理

在LIST分区中需要添加NULL才能够添加进去

 

--分区管理

1.RANGE/LIST分区管理

--删除分区表

alter table xxxx drop partition p2;

--查询分区表

SELECT

  partition_name part,

  partition_expression expr,

  partition_description descr,

  table_rows

FROM

  INFORMATION_SCHEMA.partitions

WHERE

  TABLE_SCHEMA = schema()

  AND TABLE_NAME = 'rc3';

 

show create table xxxx\G

mysql> alter table emp_date drop partition p2;

 

mysql> alter table emp_date add partition (partition p3 values less than (2020));

--只能从RANGE区分列表最大端增加分区

--拆分区：

--一个拆分成多个分区

alter table emp_date reorganize partition p3 into (

partition p3 values less than (2015),

partition p4 values less than (2020)

);

--多个分区合并成一个分区

alter table emp_date reorganize partition p1,p2,p3 into (

partition p1 values less than (2015)

);

--LIST分区：ALTER TABLE REORGANZIE PARTITION INTO

--EXAMPLE 想要添加(6,11)的分区

 

ALTER TABLE expenses add partition (PARTITION p5 VALUES IN (11));

ALTER TABLE expenses REORGANIZE PARTITION p4,p5 into

(

  PARTITION p4 VALUES IN (6,11)

);

--HASH/KEY分区管理

--使用ALTER TABLE COALESCE PARTITION语句来合并HASH分区或者KEY分区

 

--减少分区数量

mysql> alter table emp coalesce partition 2;

--增加分区数量

ALTER TABLE ADD PARTITION 语句实现

mysql> alter table emp add partition partitions 8;