Mysql分区
来源:互联网 发布:网络棋牌平台杀分 编辑:程序博客网 时间:2024/05/17 23:41
- mysql> show variables like '%partition%'\G
- *************************** 1. row ***************************
- Variable_name: have_partitioning
- Value: YES
- 1 row in set (0.00 sec)
- mysql> CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
- -> ENGINE=INNODB
- -> PARTITION BY HASH(MONTH(tr_date))
- -> PARTITIONS 6;
- Query OK, 0 rows affected (0.29 sec)
- RANGE分区
- LIST分区
- HASH分区
- KEY分区
- 子分区
- mysql> 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
- -> );
- Query OK, 0 rows affected (0.08 sec)
- mysql> 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)
- -> );
- Query OK, 0 rows affected (0.08 sec)
- mysql> 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
- -> );
- Query OK, 0 rows affected (0.09 sec)
- mysql> 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 (job_code) (
- -> PARTITION p0 VALUES LESS THAN (100),
- -> PARTITION p1 VALUES LESS THAN (1000),
- -> PARTITION p2 VALUES LESS THAN (10000)
- -> )
- -> ;
- Query OK, 0 rows affected (0.06 sec)
在这个例子中, 店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。
在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分;因此,表达式的值不能为NULL 。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经被定义为非空(NOT NULL)。
除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE (日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示:
- mysql> 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
- -> );
- Query OK, 0 rows affected (0.23 sec)
- ALTER TABLE employees DROP PARTITION p0;
- DELETE FROM employees WHERE YEAR(separated) <= 1990;
地区
商店ID 号
北区
3, 5, 6, 9, 17
东区
1, 2, 10, 11, 19, 20
西区
4, 12, 13, 14, 18
中心区
7, 8, 15, 16
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:
- mysql> 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)
- -> );
- Query OK, 0 rows affected (0.53 sec)
- mysql> ALTER TABLE employees DROP PARTITION pWest;
- Query OK, 0 rows affected (0.20 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)
- INSERT INTO employees VALUES (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21)
- mysql> 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;
- Query OK, 0 rows affected (0.28 sec)
- mysql> 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;
- Query OK, 0 rows affected (0.10 sec)
“expr”还可以是MySQL 中有效的任何函数或其他表达式,只要它们返回一个既非常数、也非随机数的整数。(换句话说,它既是变化的但又是确定的)。但是应当记住,每当插入或更新 (或者可能删除)一行,这个表达式都要计算一次;这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时 候。
最有效率的哈希函数是只对单个表列进行计算,并且它的值随列值进行一致地增大或减小,因为这考虑了在分区范围上的“修剪”。也就是说,表达式值和它所基于的列的值变化越接近,MySQL就可以越有效地使用该表达式来进行HASH分区。
例如,“date_col” 是一个DATE(日期)类型的列,那么表达式TO_DAYS(date_col)就可以说是随列“date_col”值的变化而发生直接的变化,因为列 “date_col”值的每个变化,表达式的值也将发生与之一致的变化。而表达式YEAR(date_col)的变化就没有表达式 TO_DAYS(date_col)那么直接,因为不是列“date_col”每次可能的改变都能使表达式YEAR(date_col)发生同等的改变。 即便如此,表达式YEAR(date_col)也还是一个用于 哈希函数的、好的候选表达式,因为它随列date_col的一部分发生直接变化,并且列date_col的变化不可能引起表达式 YEAR(date_col)不成比例的变化。
作为对照,假定有一个类型为整型(INT)的、列名为“int_col”的列。现在考虑表达式“POW(5-int_col,3) + 6”。这对于哈希函数就是一个不好的选择,因为“int_col”值的变化并不能保证表达式产生成比例的变化。列 “int_col”的值发生一个给定数目的变化,可能会引起表达式的值产生一个很大不同的变化。例如,把列“int_col”的值从5变为6,表达式的值将产生“-1”的改变,但是把列“int_col”的值从6变为7时,表达式的值将产生“-7”的变化。
换句话说,如果列值与表达式值之比的曲线图越接近由等式“y=nx(其中n为非零的常数)描绘出的直线,则该表达式越适合于 哈希。这是因为,表达式的非线性越严重,分区中数据产生非均衡分布的趋势也将越严重。
理论上讲,对于涉及到多列的表达式,“修剪(pruning)”也是可能的,但是要确定哪些适于 哈希是非常困难和耗时的。基于这个原因,实际上不推荐使用涉及到多列的哈希表达式。
当使用了“PARTITION BY HASH”时,MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。换句话,对于一个表达式“expr”,将要保存记录的分区编号为N ,其中“N = MOD(expr, num)”。例如,假定表t1 定义如下,它有4个分区:
- mysql> CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
- -> PARTITION BY HASH( YEAR(col3) )
- -> PARTITIONS 4;
- Query OK, 0 rows affected (0.06 sec)
如果插入一个col3列值为'2005-09-15'的记录到表t1中,那么保存该条记录的分区确定如下:
MOD(YEAR('2005-09-01'),4) = MOD(2005,4) = 1MySQL 5.1 还支持一个被称为“linear hashing(线性哈希功能)”的变量,它使用一个更加复杂的算法来确定新行插入到已经分区了的表中的位置。
每当插入或更新一条记录,用户函数都要计算一次。当删除记录时,用户函数也可能要进行计算,这取决于所处的环境。
注释:如果将要分区的表有一个唯一的键,那么用来作为HASH用户函数的自变数或者主键的column_list的自变数的任意列都必须是那个键的一部分。
LINEAR HASH分区
MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字,如下面所示:
- mysql> 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;
- Query OK, 0 rows affected (0.02 sec)
假设一个表达式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个分区,是通过下面的语句创建的:
- mysql> CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
- -> PARTITION BY LINEAR HASH( YEAR(col3) )
- -> PARTITIONS 6;
- Query OK, 0 rows affected (0.05 s
现在假设要插入两行记录到表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进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇(Cluster)使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的 哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表。
通过线性KEY分割一个表也是可能的。下面是一个简单的例子:
- mysql> CREATE TABLE tk (
- -> col1 INT NOT NULL,
- -> col2 CHAR(5),
- -> col3 DATE
- -> )
- -> PARTITION BY LINEAR KEY (col1)
- -> PARTITIONS 3;
- Query OK, 0 rows affected (0.04 sec)
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。
子分区
子分区是分区表中每个分区的再次分割。例如,考虑下面的CREATE TABLE 语句:
- mysql> 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
- -> );
- Query OK, 0 rows affected (0.08 sec)
表ts 有3个RANGE分区。这3个分区中的每一个分区——p0, p1, 和 p2 ——又被进一步分成了2个子分区。实际上,整个表被分成了3 * 2 = 6个分区。但是,由于PARTITION BY RANGE子句的作用,这些分区的头2个只保存“purchased”列中值小于1990的那些记录。
在MySQL 5.1中,对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。子分区既可以使用HASH希分区,也可以使用KEY分区。这也被称为复合分区(composite partitioning)。
为了对个别的子分区指定选项,使用SUBPARTITION 子句来明确定义子分区也是可能的。例如,创建在前面例子中给出的同一个表的、一个更加详细的方式如下:
- mysql> CREATE TABLE ts (id INT, purchased DATE)
- -> PARTITION BY RANGE(YEAR(purchased))
- -> SUBPARTITION BY HASH(TO_DAYS(purchased))
- -> (
- -> PARTITION p0 VALUES LESS THAN (1990)
- -> (
- -> SUBPARTITION s0,
- -> SUBPARTITION s1
- -> ),
- -> PARTITION p1 VALUES LESS THAN (2000)
- -> (
- -> SUBPARTITION s2,
- -> SUBPARTITION s3
- -> ),
- -> PARTITION p2 VALUES LESS THAN MAXVALUE
- -> (
- -> SUBPARTITION s4,
- -> SUBPARTITION s5
- -> )
- -> );
- Query OK, 0 rows affected (0.05 sec)
二. 创建分区
分区目前有4个类型 RANGE, LIST, HASH, KEY, 这篇博客要讲的是RANGE类型. 手册里如下描述.
RANGE : 基于属于一个给定连续区间的列值,把多行分配给分区。
注意:RANGE的值只能从小到大,而且,标识列必须包含在主键里.
创建range分区有2种方式,1种是创建表时就创建分区,另1种是创建表后再创建分区.
1.创建表时就创建分区
CREATE
TABLE
members (
id
INT
,
fname
VARCHAR
(25),
entry_time DATETIME,
PRIMARY
KEY
(`id`,`entry_time`)
)
PARTITION
BY
RANGE(entry_time) (
PARTITION p0
VALUES
LESS THAN (
'2013-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p1
VALUES
LESS THAN (
'2013-12-31 23:59:59'
) ENGINE = InnoDB,
PARTITION p2
VALUES
LESS THAN (
'2014-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p3
VALUES
LESS THAN (MAXVALUE) ENGINE = InnoDB
);
2.创建表后再创建分区
ALTER
TABLE
members PARTITION
BY
RANGE(entry_time) (
PARTITION p0
VALUES
LESS THAN (
'2013-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p1
VALUES
LESS THAN (
'2013-12-31 23:59:59'
) ENGINE = InnoDB,
PARTITION p2
VALUES
LESS THAN (
'2014-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p3
VALUES
LESS THAN (MAXVALUE) ENGINE = InnoDB
);
分区后, Mysql 将会 members 分成 4个表, 将 '2013-06-30 23:59:59' 以前注册的都放在p0里, 将 '2013-12-31 23:59:59'以前注册的都放在p1里,以此类推,
2014年7月份和以后的都放在p3里.
三. 测试分区
使用 explain partitions 测试查询语句是否精确到单个分区.
explain partitions
select
count
(*)
from
members
where
entry_time =
'2013-03-01 23:59:59'
;
+
----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | school | p0 |
index
|
PRIMARY
| id | 8 |
NULL
| 2 | Using
where
; Using
index
|
+
----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row
in
set
(0.00 sec)
四. 获取分区信息
mysql> SHOW
CREATE
TABLE
members\G
显示如下
CREATE
TABLE
members (
id
INT
,
fname
VARCHAR
(25),
entry_time DATETIME
)
PARTITION
BY
RANGE(entry_time) (
PARTITION p0
VALUES
LESS THAN (
'2013-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p1
VALUES
LESS THAN (
'2013-12-31 23:59:59'
) ENGINE = InnoDB,
PARTITION p2
VALUES
LESS THAN (
'2014-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p3
VALUES
LESS THAN (MAXVALUE) ENGINE = InnoDB
);
五. 修改分区(拆分,合并)
拆分
上边 member 的分区,只分到了2014年6月份.现在我们将p3分区重拆分为多个分区.
ALTER
TABLE
members REORGANIZE PARTITION p3
INTO
(
PARTITION p4
VALUES
LESS THAN (
'2014-12-31 23:59:59'
) ENGINE = InnoDB,
PARTITION p5
VALUES
LESS THAN (
'2015-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p6
VALUES
LESS THAN (
'2015-12-31 23:59:59'
) ENGINE = InnoDB,
PARTITION p7
VALUES
LESS THAN (
'2016-06-30 23:59:59'
) ENGINE = InnoDB,
PARTITION p8
VALUES
LESS THAN (MAXVALUE) ENGINE = InnoDB
);
合并
再次声明:RANGE的值只能从小到大,而且,标识列必须包含在主键里.
现在将p0,p1,p2合并到m1里.
ALTER
TABLE
members REORGANIZE PARTITION p0,p1,p2
INTO
(
PARTITION m1
VALUES
LESS THAN (
'2014-06-31 23:59:59'
) ENGINE = InnoDB
);
六. 重新分区
语法与创建分区一样, 直接 alter by 就可以了.Mysql会将旧分区逻辑删除掉.
比如更改分区 标志列.
ALTER
TABLE
members PARTITION
BY
RANGE(id) (
PARTITION p0
VALUES
LESS THAN (10000) ENGINE = InnoDB,
PARTITION p1
VALUES
LESS THAN (20000) ENGINE = InnoDB,
PARTITION p2
VALUES
LESS THAN (30000) ENGINE = InnoDB,
PARTITION p3
VALUES
LESS THAN (MAXVALUE) ENGINE = InnoDB
);
还可以更改为其他分区类型 如 LIST,HASH等. 举一个HASH的例子, 将id平均分布到5分区表.
ALTER
TABLE
members PARTITION
BY
HASH (id) PARTITIONs 5;
七. 删除分区以及分区数据
如非必要,尽量不要用,使用 拆分合并更好一点.
ALTER
TABLE
tr
DROP
PARTITION p1;
- MySQL分区--列表分区
- MySQL分区--列分区
- Mysql分区?
- mysql分区
- mysql分区
- MySQL分区
- mysql分区
- MYSQL分区
- mysql 分区
- Mysql分区
- mysql分区
- MySQL分区
- Mysql 分区
- Mysql分区
- mysql分区
- mysql分区
- MySQL分区
- mysql分区
- 2010年北京航空航天大学上机题
- 简单的用c++实现的八数码问题
- Tomcat + 数字证书 部署webservice (客户端调用https webService)
- ibatis中iterate问题用于in查询
- SQL语句:group by having
- Mysql分区
- JAVA 继承基本类、抽象类、接口
- linux USB子系统之我的见解。
- Android Dialog和adapter关联,列表形式显示对话框
- 易错问题
- 我的新书《HTML5 Canvas游戏开发实战》
- 在IOS中实现导入&导出文档
- 对apk进行签名的方法
- C++ 多线程开发技巧 4