目前分区规则有四种,分别是RANGE、LIST、HASH和KEY,另外通过DATA DIRECTORY和INDEX DIRECTORY选项可以把不同的分区数据文件分散到不同的磁盘上,从而进步一提高系统的IO吞吐量。因此按照业务逻辑设计好了分区,可以大大提高查询效率,而且删除数据可能也会很容易。但是分区也有一些限制:1、主键或者唯一索引必须包含分区字段;2、只能通过int类型的字段或者返回int类型的表达式来分区;3、单表最多只能有1024个分区。
默认mysql是开启了分区功能的,可以通过下述查询查看结果:
代码如下 | 复制代码 |
show variables like '%partition%'; +-------------------+-------+ |
+-------------------+-------+
//YES 表示开启下面也range规则为例介绍一下分区常用的命令。
1、创建分区。可以在创建表的同时创建,也可以在表创建后追加分区。
代码如下 | 复制代码 |
drop table if exists `netingcn_com`; create table `netingcn_com` ( `id` int(11) unsigned not null auto_increment, `day` int(11) not null default 0, primary key (`id`, `day`) ) engine=innodb default charset=utf8 auto_increment=1; alter table `netingcn_com` partition by range(`day`) ( 或 drop table if exists `netingcn_com`; |
可以explain命令查看分区是否创建成功
代码如下 | 复制代码 |
explain partitions select * from netingcn_com where day = 20130412; +----+-------------+--------------+------------+-------+ | id | select_type | table | partitions | type | +----+-------------+--------------+------------+-------+ | 1 | SIMPLE | netingcn_com | p_2013 | index | +----+-------------+--------------+------------+-------+ |
2、增加或删除分区。注意:删除分区的同时,该分区的所有数据也会别删除。
增加分区
代码如下 | 复制代码 |
alter table netingcn_com add partition ( partition p_2014 values less than (20150000) ); |
删除分区
代码如下 | 复制代码 |
alter table netingcn_com drop partition p_2012; |
3、重新分区。注意:hash和key分区规则不能用REORGANIZE
来重新分区
代码如下 | 复制代码 |
alter table netingcn_com reorganize partition p_2013,p_2014 into (partition p_2014 values less than (20150000)); |
[分区表和未分区表试验过程]
*创建分区表,按日期的年份拆分
代码如下 | 复制代码 |
mysql> 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 ); |
注意最后一行,考虑到可能的最大值
*创建未分区表
代码如下 | 复制代码 |
mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam; |
*通过存储过程灌入800万条测试数据
代码如下 | 复制代码 |
mysql> set sql_mode=''; /* 如果创建存储过程失败,则先需设置此变量, bug? */ mysql> delimiter // /* 设定语句终结符为 //,因存储过程语句用;结束 */ |
* 测试SQL性能
代码如下 | 复制代码 |
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) 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.69 sec) |
结果表明分区表比未分区表的执行时间少90%。
代码如下 | 复制代码 |
* 通过explain语句来分析执行情况 mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'G /* 结尾的G使得mysql的输出改为列模式 */ *************************** 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 select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'G |
explain语句显示了SQL查询要处理的记录数目
代码如下 | 复制代码 |
* 试验创建索引后情况 mysql> create index idx_of_c3 on no_part_tab (c3); Query OK, 8000000 rows affected (1 min 18.08 sec) Records: 8000000 Duplicates: 0 Warnings: 0 mysql> create index idx_of_c3 on part_tab (c3); * 再次测试SQL性能 |
重启mysql ( net stop mysql, net start mysql)后,查询时间降为0.89 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.86 sec) * 更进一步的试验 mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31'; mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
|
= 初步结论 =
* 分区和未分区占用文件空间大致相同 (数据和索引文件)
* 如果查询语句中有未建立索引字段,分区时间远远优于未分区时间
* 如果查询语句中字段建立了索引,分区和未分区的差别缩小,分区略优于未分区。
= 最终结论 =
* 对于大数据量,建议使用分区功能。
* 去除不必要的字段
* 根据手册, 增加myisam_max_sort_file_size 会增加分区性能
[分区命令详解]
代码如下 | 复制代码 |
= 分区例子 = * RANGE 类型 CREATE TABLE users ( PARTITION p1 VALUES LESS THAN (6000000) PARTITION p2 VALUES LESS THAN (9000000) PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' |
在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。
* LIST 类型
代码如下 | 复制代码 |
CREATE TABLE category ( cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY LIST (cid) ( PARTITION p0 VALUES IN (0,4,8,12) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES IN (1,5,9,13) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES IN (2,6,10,14) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES IN (3,7,11,15) DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx' ); |
分成4个区,数据文件和索引文件单独存放。
* HASH 类型
代码如下 | 复制代码 |
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY HASH (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 PARTITION p2 PARTITION p3 |
分成4个区,数据文件和索引文件单独存放。
例子:
代码如下 | 复制代码 |
CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=myisam PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6; CREATE PROCEDURE load_ti2()
|
* KEY 类型
代码如下 | 复制代码 |
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY KEY (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx' ); |
分成4个区,数据文件和索引文件单独存放。
* 子分区
子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如:
代码如下 | 复制代码 |
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) |
对 RANGE 分区再次进行子分区划分,子分区采用 HASH 类型。
或者
代码如下 | 复制代码 |
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) |
对 RANGE 分区再次进行子分区划分,子分区采用 KEY 类型。
= 分区管理 =
* 删除分区
ALERT TABLE users DROP PARTITION p0;
删除分区 p0。
* 重建分区
o RANGE 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
o LIST 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
o HASH/KEY 分区重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
* 新增分区
o 新增 RANGE 分区
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');
新增一个RANGE分区。
o 新增 HASH/KEY 分区
ALTER TABLE users ADD PARTITION PARTITIONS 8;
将分区总数扩展到8个。
[ 给已有的表加上分区 ]
代码如下 | 复制代码 |
alter table results partition by RANGE (month(ttime)) (PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) , PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) , PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) , PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) , PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11), PARTITION p11 VALUES LESS THAN (12), PARTITION P12 VALUES LESS THAN (13) ); |
默认分区限制分区字段必须是主键(PRIMARY KEY)的一部分,为了去除此
限制:
[方法1] 使用ID
代码如下 | 复制代码 |
mysql> ALTER TABLE np_pk -> PARTITION BY HASH( TO_DAYS(added) ) -> PARTITIONS 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function However, this statement using the id column for the partitioning column is valid, as shown here: mysql> ALTER TABLE np_pk |
[方法2] 将原有PK去掉生成新PK
代码如下 | 复制代码 |
mysql> alter table results drop PRIMARY KEY; Query OK, 5374850 rows affected (7 min 4.05 sec) Records: 5374850 Duplicates: 0 Warnings: 0 mysql> alter table results add PRIMARY KEY(id, ttime); |