Mysql分区主要分四类:
- RANGE分区 基于属于一个给定连续区间的列值,把多行分配给分区
- LIST分区 类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
- HASH分区 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式
- KEY分区 类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值
一、 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)
);
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 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
)
二、LIST分区
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)
);
要点
:
1. 如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“
INSERT”查询将失败并报错
2.
要重点注意的是,
LIST
分区没有类似如
“VALUES LESS THAN MAXVALUE”
这样的包含其他值在内的定义
三、HASH分区
1. 普通hash分区,根据hash函数为指定表达式值取余获取分区号
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;
2. Liner Hash分区
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;
假设一个表达式expr, 当使用线性哈希功能时,记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到:
1. 找到下一个大于num.的、2的幂,我们把这个值称为V ,它可以通过下面的公式得到:
2. V = POWER(2, CEILING(LOG(2, num)))
(例如,假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4,则V = POWER(2,4), 即等于16)。
3. 设置 N = F(column_list) & (V - 1).
4. 当 N >= num:
· 设置 V = CEIL(V / 2)
· 设置 N = N & (V - 1)
例如,假设表t1,使用线性哈希分区且有4个分区,是通过下面的语句创建的:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设要插入两行记录到表t1中,其中一条记录col3列值为'2003-04-14',另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下:
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 为假(FALSE): 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下:
V = 8
N = YEAR('1998-10-19') & (8-1)
= 1998 & 7
= 6
(6 >= 4 为真(TRUE): 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
= 6 & 3
= 2
(2 >= 4 为假(FALSE): 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000吉)数据的表。它的缺点在于,与使用常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。
四、KEY分区
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法
-
CREATE TABLE tk ( -
col1 INT NOT NULL,
-
col2 CHAR(5),
-
col3 DATE
-
)
-
PARTITION BY LINEAR KEY (col1)
-
PARTITIONS 3;
欢迎关注