MYSQL [ 优化 --- 分区 ]
MySQL分区概述
在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的 物理路径。分区(partion)更进一步，它允许你通过设置各种规则将一个表的各个分区跨文件系统存储。实际上，不同位置的不同表分区是作为一个单独的表来存储的。用户所选择的、实现数据分割的规则被称为分区函数(partioning function)，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。
最常见是的水平分区(horizontal partitioning)，也就是将表的不同的元组分配到不同的物理分区上。目前，MySQL5.1还不支持垂直分区(vertical partitioning)，即将表的不同列分配到不同的物理分区。你可以使用MySQL支持的大多数存储引擎来创建表的分区，在MySQL 5.1中，同一个表的各个分区必须使用相同的存储引擎，比如，你不能对一个分区使用MyISAM，而对另一个分区使用InnoDB。但是，你可以对同一个数据库的不同的表使用不同的存储引擎。
要为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE选项，这如同为非分区表配置存储引擎一样。但是，必须记住[STORAGE] ENGINE(和其他的表选项)必须列在用在CREATE TABLE语句中的其他任何分区选项之前。
下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：
CREATE TABLE ti (id INT, amountDECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
注：分区必须对一个表的所有数据和索引;不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。
分区对数据库管理系统实现并行处理有着重要的影响，如果对数据进行分区，则很容易进行并行处理，但是，MySQL还没有充分利用分区的这种并行优势，而这也是它改进的方向 (这种分治思想深深的影响着并行计算，而且在并行计算方面具有天然优势)。MySQL的分区，会给系统带来以下一些优点：
1．与单个磁盘或文件系统分区相比，单个表可以存储更多的数据。
2．对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
3．对于带Where的条件查询语句，可以得到更大的优化;只需要查询某些分区，而不用扫描全部分区。
还有其它一些优点，不过MySQL 5.1还不支持：
1．一些聚合函数，比如SUM() 和COUNT()，能够很容易的并行执行;
2．通过并行I/O，可以大大提高查询的吞吐量。
注：实际上，分区不论是对I/O并行，还是查询内并行，都有着重要的影响。只不过MySQL在这方面做得还不够多(不过，正在改进)，而Oracle对于查询内并行，做了很多工作。
2.2、分区类型
MySQL 5.1中可用的分区类型包括：
1．RANGE分区(portioning)：根据列值所属的范围区间，将元组分配到各个分区。
2．LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
3．HASH分区：根据用户定义的函数的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
4．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的情况，所以服务器会报错。你可以通过如下方式来处理这种情况：
CREATETABLE employees (
id INT NOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30),
hired DATENOT NULL DEFAULT '1970-01-01',
separatedDATE NOT NULL DEFAULT '9999-12-31',
job_code INTNOT NULL,
store_id INTNOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0VALUES LESS THAN (6),
PARTITION p1VALUES LESS THAN (11),
PARTITION p2VALUES LESS THAN (16),
PARTITION p3VALUES LESS THAN MAXVALUE
);
MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多 ，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区
RANGE分区在如下场合特别有用：
(1) 当需要删除“旧的”数据时。 在上面的例子中，你只需简单地使用 “ALTER TABLEemployees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如“DELETE FROM employees WHERE YEAR(separated) <= 1990;”这样的一个DELETE查询要有效得多。
(2) 经常依赖于分区属性进行查询。例如，当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUPBY 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 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONSnum”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。比如：
CREATE TABLEemployees (
id INT NOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30),
hired DATENOT NULL DEFAULT '1970-01-01',
separatedDATE 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 TABLEts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0VALUES LESS THAN (1990),
PARTITION p1VALUES LESS THAN (2000),
PARTITION p2VALUES 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 TABLEts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITIONs0,
SUBPARTITIONs1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITIONs2,
SUBPARTITIONs3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITIONs4,
SUBPARTITIONs5
)
);
一些注意点：
(1) 每个分区的子分区数必须相同;
(2) 如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区;
(3) 每个SUBPARTITION子句必须包含一个子分区的名称;
(4) MySQL 5.1.7及之前的版本，每个分区的子分区的名称必须唯一，但是在整个表中，没有必要唯一。从MySQL 5.1.8开始，子分区的名称在整个表中都必须唯一。
子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘，分别为/disk0，/disk1， /disk2等，对于如下例子：
CREATE TABLEts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITIONs0
DATADIRECTORY = '/disk0/data'
INDEXDIRECTORY = '/disk0/idx',
SUBPARTITIONs1
DATADIRECTORY = '/disk1/data'
INDEXDIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITIONs2
DATADIRECTORY = '/disk2/data'
INDEXDIRECTORY = '/disk2/idx',
SUBPARTITIONs3
DATADIRECTORY = '/disk3/data'
INDEXDIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITIONs4
DATADIRECTORY = '/disk4/data'
INDEXDIRECTORY = '/disk4/idx',
SUBPARTITIONs5
DATADIRECTORY = '/disk5/data'
INDEXDIRECTORY = '/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 LESSTHAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESSTHAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESSTHAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESSTHAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESSTHAN (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,'testingpartitions',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(*) fromno_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语句：
代码
1 CREATE TABLE part_tab
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_tabwhere
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);