数据表分区方法与使用

参考官网手册

检查是否支持分区:

show plugins;

如果显示有
partition ACTIVE,则表示是支持分区

分区类型：
范围(range)：通过指定的列中的值作为区间范围
列表(list):与range相似,区别是匹配一个指定的列值
哈希(hash):是基于用户定义的函数对列的值计算后返回结果决定保存在哪一个分区中
键值(key):与hash类似，基于一个或多个列来评估，并且由mysql服务器提供函数

基于 range,其中的rang中结果值为 整型
as:

CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
#或者：
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 MAXVALUE
);

基于 list:(list列表结果为整型)
与range类似，主要区别在于，list(表达式)中的表达式值为指定的一组具体值(即枚举值),
并且list中的值一定要在分区中，并且分区中的结果为整型:
as:

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,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);

基于 range columns:(列的类型可为 string,date,datetime)
与range类似，但范围列不接受表达式，只接收列名称
范围列是基于比较元组(列表值)而不是标量之间比较
格式如下：

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
PARTITION partition_name VALUES LESS THAN (value_list)[,
PARTITION partition_name VALUES LESS THAN (value_list)][,
...]
)
column_list:
column_name[, column_name][, ...]
value_list:
value[, value][, ...]
--------------------
as:
create table rcx(
a int,
b int,
c char(3),
d int
)
partition by range columns(a,b,c)(
partition p0 values less than (4,2,10),
partition p1 values less than (10,20,'mmm'),
partition p2 values less than (maxvalue,maxvalue,maxvalue)
)

#as:
CREATE TABLE employees_by_lname (
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 COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

基于list columns:(表达式中值的类型为:string,date,datetime)
as:

CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
##############
CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

以上可以用 range columns代替：

CREATE TABLE customers_3 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY RANGE COLUMNS(renewal) (
PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
);

hash分区：
主要用于保证预定数量分区间数据分布均匀，hash返回的类型为整数,
用取模方法分区，即 (hash结果mod)/分区数)
as:

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,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

此时，store_id会被4整除,此时相当于分区为 0,1,2,3,即余数,
默认时用1开始，即分区 1,2,3,4,当值为0时，在1区，值为1时，在2区,...

as:

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,
store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

线性hash(linear hash):
与 普通hash类似，不同之处是用了 linera hash,还有就是分区算法用的是(线性的2的幂的运算法则)

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,
store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;　

key分区：
与hash类似，但是不允许用用户自定义的表达式，可以不用手动指定分区键，
默认会选择使用主键/唯一键作为分区键，没有主键/唯一键，则要指定分区键,
key的使用类似 hash
as:

CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

##as:
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

其实如 hash一样，可以使用 linear key,
as:

CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区：是在每一个分区进一步划分。

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
);

以上实现就有6个分区了　　

以下为分区的管理，即所谓的增删改查:

清除表中所有的分区：　

alter table 表名 remove PARTITIONING;

range/list类型分区(REORGANIZE PARTITION 只用于 range/list类型的分区):
增加分区：
当建立表时，还没有记录，则
as:

#range分区类型
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
         PARTITION BY RANGE( YEAR(purchased) ) (
             PARTITION p0 VALUES LESS THAN (1990),
             PARTITION p1 VALUES LESS THAN (1995),
             PARTITION p2 VALUES LESS THAN (2000),
             PARTITION p3 VALUES LESS THAN (2005)
         );

#list分区类型：
CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

当增加分区时，已经存在记录或已定义好表结构，则：
当之前没有分区时：

ALTER TABLE 表名
    PARTITION BY 分区方法
    (定义)

as:

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
#增加分区(之前没有分区),则：
alter table members
PARTITION by range(year(dob))(
             PARTITION p0 VALUES LESS THAN (1990),
             PARTITION p1 VALUES LESS THAN (1995),
             PARTITION p2 VALUES LESS THAN (2000),
             PARTITION p3 VALUES LESS THAN (2005)
)

#已经存在分区时：
alter table 表名 add partition (分区定义):
#range 类型:
#as:
CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1970),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990)
);

#如果有分区,则:
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));


#list类型：
#没有分区时：
alter table members PARTITION by list(year(dob))(
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
)

#有分区时，则:
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

#当用explain检索是否使用索引或分区时，可用
#explain PARTITIONS sql语句;


#删除分区，同时删除记录
alert table 表名 drop partition 分区名;
as:
ALTER TABLE tr DROP PARTITION p2;


#把一个分区分割成多个分区
alter table 表名
	reorganize partition 分区名 into(
		分区定义语句,
		分区定义语句,
		....
	)
#range类型：
#as:
ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1960),
        PARTITION n1 VALUES LESS THAN (1970)
);


#合并分区，把多个分区合并成一个分区或多个分区:(数据不会丢失)
alter table 表名 REORGANIZE PARTITION 分区名1,分区名2,... into(
	分区定义语句,
	分区定义语句,
	...
) 
#range类型：
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);
#或如：
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

#list 类型：
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);
  

#hash/key分区类型
#创建分区，如用 hash 创建为12个分区:
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

#当给没有创建过分区的表进行分区时(hash)：
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
 
#当从12个修改为8个分区时(即相当于移除4个)：
ALTER TABLE clients COALESCE PARTITION 4;

#当把 12个分区重新分成18个时(相当于增6个分区)：
ALTER TABLE clients ADD PARTITION PARTITIONS 6;