mysql数据库的分区

 水平分区，就是不同的行可以放在不同的物理分区，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'#子分区的索引文件存放地址               
    )
     
);