水平分区,就是不同的行可以放在不同的物理分区,mysql5.1还不支持垂直分区,就是不同的列放在不同的物理分区。
mysql分区支持主要在5.1以上版本,如果你的mysql是5.1以上版本,查看是否支持分区的方式是:
mysql> show variables like 'h%';
+-------------------------+-----------------------+
| Variable_name | Value |
+-------------------------+-----------------------+
| have_community_features | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_partitioning | YES | -->>此项为yes说明支持表分区功能,如果为No则需要重新编译
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | NO |
| have_symlink | YES |
| hostname | localhost.localdomain |
+-------------------------+-----------------------+
15 rows in set (0.02 sec)
实例:
RANGE分区:根据列值所属的范围区间,将元组分配到各个分区。
//建立一个表
CREATE TABLE part_tab
(
c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) //根据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) , //1999年的数据存放在P4分区
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 ); //其他数据存放在P11分区
HASH分区:根据用户定义的函数的返回值来进行选择的分区
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )//根据实际中的月份来HASH分区
PARTITIONS 6;//分6个分区
4.分区简单实例,以range分区类型为例:
create table users00 (
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)
( partition p0 values less than (10000) data directory = "/data00/" index directory = "/data00/",
partition p1 values less than (20000) data directory = "/data00/" index directory = "/data00/",
partition p2 values less than (30000) data directory = "/data00/" index directory = "/data00/",
partition p3 values less than maxvalue data directory = "/data00/" index directory = "/data00/"
);
该表分区类型为range,分为4个区,uid为0~10000在p0区,10001~20000在p1区 ...
查看users00表所在的数据目录:
....
-rw-rw---- 1 root root 8620 Nov 22 14:47 users00.frm
-rw-rw---- 1 root root 32 Nov 22 14:47 users00.par
lrwxrwxrwx 1 root root 24 Nov 22 14:47 users00#P#p0.MYD -> /data00/users00#P#p0.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:47 users00#P#p0.MYI -> /data00/users00#P#p0.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYD -> /data00/users00#P#p1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYI -> /data00/users00#P#p1.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYD -> /data00/users00#P#p2.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYI -> /data00/users00#P#p2.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYD -> /data00/users00#P#p3.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYI -> /data00/users00#P#p3.MYI
....
此时表数据已经达到了物理分区,这样可以减少同一目录的IO,同时由单表的3w记录下降到了单表1w的数据量,这样查询起来效率更高
5.分区的修改和合并
以下是将p0分区再分割成2分区s0,s1的例子:
mysql> alter table users00 reorganize partition p0 into( partition s0 values less than (3999) data directory='/data00/' index directory='/data00/', partition s1 values less than (10000) data directory='/data00/' index directory='/data00/' );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
分割成功,查看/data00/数据目录:
.....
-rw-rw---- 1 root root 8620 Nov 22 15:01 users00.frm
-rw-rw---- 1 root root 40 Nov 22 15:01 users00.par
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYD -> /data00/users00#P#p1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYI -> /data00/users00#P#p1.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYD -> /data00/users00#P#p2.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYI -> /data00/users00#P#p2.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYD -> /data00/users00#P#p3.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYI -> /data00/users00#P#p3.MYI
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s0.MYD -> /data00/users00#P#s0.MYD
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s0.MYI -> /data00/users00#P#s0.MYI
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s1.MYD -> /data00/users00#P#s1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 15:01 users00#P#s1.MYI -> /data00/users00#P#s1.MYI
......
以下是合并s0,s1分区为p0分区
mysql> alter table users00 reorganize partition s0,s1 into( partition p0 values less than(10000) data directory="/data00/" index directory="/data00/");
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
此时s0,s1又合并成了p0分区
....
-rw-rw---- 1 root root 8620 Nov 22 15:04 users00.frm
-rw-rw---- 1 root root 32 Nov 22 15:04 users00.par
lrwxrwxrwx 1 root root 24 Nov 22 15:04 users00#P#p0.MYD -> /data00/users00#P#p0.MYD
lrwxrwxrwx 1 root root 24 Nov 22 15:04 users00#P#p0.MYI -> /data00/users00#P#p0.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYD -> /data00/users00#P#p1.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p1.MYI -> /data00/users00#P#p1.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYD -> /data00/users00#P#p2.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p2.MYI -> /data00/users00#P#p2.MYI
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYD -> /data00/users00#P#p3.MYD
lrwxrwxrwx 1 root root 24 Nov 22 14:39 users00#P#p3.MYI -> /data00/users00#P#p3.MYI
....
详细的分区信息及功能介绍请参考mysql参考手册
http://www.cnblogs.com/hustcat/archive/2009/12/24/1631674.html
//---------------------------------一个按月分区,按日期分区的表----------------------------//
#请保证文件夹已经建立
CREATE TABLE testparkingmonth (
id INT NOT NULL,
separated DATE NOT NULL DEFAULT '9999-12-31',
primary key(id,separated)
)
PARTITION BY RANGE (YEAR(separated)) #根据月份分区
SUBPARTITION BY HASH(YEAR(separated))
(
PARTITION p0 VALUES LESS THAN (2010)#早于2010年的放置在p0分区
(
SUBPARTITION s0
DATA DIRECTORY = '/var/testmysql' #子分区的数据文件存放地址
INDEX DIRECTORY = '/var/testmysql'#子分区的索引文件存放地址
),
PARTITION p1 VALUES LESS THAN (2011)#早于2011年的放置在p1分区
(
SUBPARTITION s1
DATA DIRECTORY = '/var/testmysql'
INDEX DIRECTORY = '/var/testmysql'
),
PARTITION p2 VALUES LESS THAN (2012) #早于2012年的放置在p2分区
(
SUBPARTITION s2
DATA DIRECTORY = '/var/testmysql'
INDEX DIRECTORY = '/var/testmysql'
),
PARTITION p3 VALUES LESS THAN MAXVALUE #其他的放置在p3分区
(
SUBPARTITION s3
)
);
#LIST表分区(LIST分区表现在只能够给INT等数字类型分区,不支持字符串!!!)
CREATE TABLE testparkingip (
id INT NOT NULL,
ip INT NOT NULL,
primary key(id,ip)
)
PARTITION BY LIST(ip) #根据ip分区
(
PARTITION test1 VALUES IN (1,4),
PARTITION test2 VALUES IN (2,5),
PARTITION test3 VALUES IN (3,6)
);
修改表分区的存储位置:
ALTER table testparkingmonth REORGANIZE PARTITION p0 into(
PARTITION p0 VALUES LESS THAN (2010)#早于2010年的放置在p0分区
(
SUBPARTITION s0
DATA DIRECTORY = '/var/testmysql/haha' #子分区的数据文件存放地址
INDEX DIRECTORY = '/var/testmysql/haha'#子分区的索引文件存放地址
)
);