理解MySQL——并行数据库与分区(Partition)
1.1、并行数据库的体系结构
并行机的出现,催生了并行数据库的出现,不对,应该是关系运算本来就是高度可并行的。对数据库系统性能的度量主要有两种方式:(1)吞吐量(Throughput),在给定的时间段里所能完成的任务数量;(2)响应时间(Response time),单个任务从提交到完成所需要的时间。对于处理大量小事务的系统,通过并行地处理许多事务可以提高它的吞吐量。对于处理大事务的系统,通过并行的执行事务的子任务,可以缩短系统晌应时间。
并行机有三种基本的体系结构,相应的,并行数据库的体系结构也可以大概分为三类:
共享内存(share memeory):所有处理器共享一个公共的存储器;
共享磁盘(share disk):所有处理器共享公共的磁盘;这种结构有时又叫做集群(cluster);
无共享(share nothing):所有处理器既不共享内存,也不共享磁盘。
如图所示:
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6; |
注:分区必须对一个表的所有数据和索引;不能只对数据分区而不对索引分区,反之亦然,同时也不能只对表的一部分进行分区。
分区对数据库管理系统实现并行处理有着重要的影响,如果对数据进行分区,则很容易进行并行处理,但是,MySQL还没有充分利用分区的这种并行优势,而这也是它改进的方向 (这种分治思想深深的影响着并行计算,而且在并行计算方面具有天然优势)。MySQL的分区,会给系统带来以下一些优点:
与单个磁盘或文件系统分区相比,单个表可以存储更多的数据。
对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。
对于带Where的条件查询语句,可以得到更大的优化;只需要查询某些分区,而不用扫描全部分区。
还有其它一些优点,不过MySQL 5.1还不支持:
一些聚合函数,比如SUM() 和COUNT(),能够很容易的并行执行;
通过并行I/O,可以大大提高查询的吞吐量。
注:实际上,分区不论是对I/O并行,还是查询内并行,都有着重要的影响。只不过MySQL在这方面做得还不够多(不过,正在改进),而Oracle对于查询内并行,做了很多工作。
2.2、分区类型
MySQL 5.1中可用的分区类型包括:
RANGE分区(portioning):根据列值所属的范围区间,将元组分配到各个分区。
LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区:根据用户定义的函数的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。
2.2.1、范围分区
范围分区是通过计算表达式的值所属的范围区间,对元组进行分区。这些区间要求连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中,假定你创建了一个如下的一个表,该表保存有20家音像店的职员记录,这20家音像店的编号从1到20。
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 ); |
你可以根据需要对该表进行各种分区,比如,你可以通过store_id来进行分区:
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) ); |
很容易确定数据(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)被插入分区p2;但是,如果一条数据的store_id = 21,会怎么样呢?由于没有规则处理大于20的情况,所以服务器会报错。你可以通过如下方式来处理这种情况:
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 ); |
MAXVALUE 表示最大的可能的整数值。现在,store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区
RANGE分区在如下场合特别有用:
(1) 当需要删除“旧的”数据时。 在上面的例子中,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR(separated) <= 1990;”这样的一个DELETE查询要有效得多。
(2) 经常依赖于分区属性进行查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。注:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。
范围分区的缺点就是容易出现执行偏斜,这会影响系统性能。
2.2.2、HASH分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
你可以通过要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。比如:
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; |
如果没有PARTITIONS语句,默认分区数为1。但是,PARTITIONS后面没有数字,系统会报错。
相对于范围分区,HASH分区更可能保证数据均衡分布。
2.2.3、子分区(Subpartitioning)
子分区,也叫做复合分区(composite partitioning),是对分区表的每个分区的进一步分割。例如,
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 ); |
表ts 有3个RANGE分区。这3个分区中的每一个分区——p0, p1, 和 p2 ——又被进一步分成了2个子分区。实际上,整个表被分成了3 * 2 = 6个分区。但是,由于PARTITION BY RANGE子句的作用,这些分区的头2个只保存“purchased”列中值小于1990的那些记录。
在MySQL 5.1中,对于已经通过RANGE或LIST分区了的表再进行分区。子分区既可以使用HASH希分区,也可以使用KEY分区。
为了对个别的子分区指定选项,使用SUBPARTITION 子句来明确定义子分区也是可能的。例如,创建在前面例子中给出的同一个表的、一个更加详细的方式如下:
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 ) ); |
一些注意点:
(1) 每个分区的子分区数必须相同;
(2) 如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区,那么就必须定义所有的子分区;
(3) 每个SUBPARTITION子句必须包含一个子分区的名称;
(4) MySQL 5.1.7及之前的版本,每个分区的子分区的名称必须唯一,但是在整个表中,没有必要唯一。从MySQL 5.1.8开始,子分区的名称在整个表中都必须唯一。
子分区可以用于特别大的表,在多个磁盘间分配数据和索引。假设有6个磁盘,分别为/disk0, /disk1, /disk2等,对于如下例子:
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 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx', SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ) ); |
3、体验分区
下面通过例子来体验分区:
(1)创建如下分区表:
CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL ) engine=myisam PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN MAXVALUE ); |
(2)创建一个不分区的表:
create table no_part_tab (c1 int(11) default NULL, c2 varchar(30) default NULL, c3 date default NULL ) engine=myisam; |
(1) 创建一个生成8000000行数据的存储过程:
delimiter // CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 8000000 do insert into part_tab values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); set v = v + 1; end while; end // |
(2) 调用存储过程,生成数据:
mysql> delimiter ; mysql> call load_part_tab(); Query OK, 1 row affected (6 min 35.39 sec) |
(5)
mysql> insert into no_part_tab select * from part_tab; Query OK, 8000000 rows affected (40.98 sec) Records: 8000000 Duplicates: 0 Warnings: 0 |
数据准备好了,下面开始测试:
(6)
mysql> select count(*) from no_part_tab where -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (4.23 sec)
mysql> select count(*) from part_tab where -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (0.55 sec) |
速度差异很明显;下面看一下查询计划:
(8)
mysql> explain select count(*) from no_part_tab where -> c3 > date '1995-01-01' and c3 < date '1995-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: no_part_tab type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8000000 Extra: Using where 1 row in set (0.00 sec)
mysql> explain partitions select count(*) from part_tab where c3 > date '1995-01 -01' and c3 < date '1995-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: part_tab partitions: p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8000000 #why?? Extra: Using where 1 row in set (0.00 sec)
|
附SQL语句:
2 ( c1 int default NULL ,
3 c2 varchar ( 30 ) default NULL ,
4 c3 date default NULL
5 ) engine = myisam
6 PARTITION BY RANGE ( year (c3))
7 (
8 PARTITION p0 VALUES LESS THAN ( 1995 ),
9 PARTITION p1 VALUES LESS THAN ( 1996 ) ,
10 PARTITION p2 VALUES LESS THAN ( 1997 ) ,
11 PARTITION p3 VALUES LESS THAN ( 1998 ) ,
12 PARTITION p4 VALUES LESS THAN ( 1999 ),
13 PARTITION p5 VALUES LESS THAN ( 2000 ) ,
14 PARTITION p6 VALUES LESS THAN ( 2001 ) ,
15 PARTITION p7 VALUES LESS THAN ( 2002 ) ,
16 PARTITION p8 VALUES LESS THAN ( 2003 ) ,
17 PARTITION p9 VALUES LESS THAN ( 2004 ) ,
18 PARTITION p10 VALUES LESS THAN ( 2010 ),
19 PARTITION p11 VALUES LESS THAN MAXVALUE
20 );
21
22
23 create table no_part_tab
24 (c1 int ( 11 ) default NULL ,
25 c2 varchar ( 30 ) default NULL ,
26 c3 date default NULL
27 ) engine = myisam;
28
29
30 delimiter //
31 CREATE PROCEDURE load_part_tab()
32 begin
33 declare v int default 0 ;
34 while v < 8000000
35 do
36 insert into part_tab(c1,c2,c3)
37 values (v, ' testing partitions ' ,adddate( ' 1995-01-01 ' ,( rand (v) * 36520 ) mod 3652 ));
38 set v = v + 1 ;
39 end while ;
40 end
41 //
42
43 delimiter ;
44 call load_part_tab();
45 explain select count ( * ) from no_part_tab where
46 c3 > date ' 1995-01-01 ' and c3 < date ' 1995-12-31 ' ;
47
48 explain select count ( * ) from part_tab where
49 c3 > date ' 1995-01-01 ' and c3 < date ' 1995-12-31 ' ;
50
51
52
53
54 CREATE TABLE part_tab2
55 (
56 c1 int default NULL
57 ) engine = myisam
58 PARTITION BY RANGE (c1)
59 (
60 PARTITION p0 VALUES LESS THAN ( 5 ),
61 PARTITION p1 VALUES LESS THAN ( 10 ),
62 PARTITION p2 VALUES LESS THAN MAXVALUE
63 );
64
65 insert into part_tab2 values ( 2 ),( 3 );
转载于:https://my.oschina.net/u/656519/blog/137487