一:什么时候分区

     分区针对不同的数据库,具有不同的特性。在这里专门针对MySQL数据库而言。在MySQL数据库里,分区这个概念是从mysql 5.1才开始提供的。不过目前只有在mysql advanced版本里才提供。
     分区是把数据库、或它的组成部分(比如表)分成几个小部分。而且专门介绍的都是’水平分区’,即对表的行进行划分。

二:分区带来的好处

     1. 可以提高数据库的性能;
     2. 对大表(行较多)的维护更快、更容易,因为数据分布在不同的逻辑文件上;
     3. 删除分区或它的数据是容易的,因为它不影响其他表。

三:分区类型

     1.Range分区:是对一个连续性的行值,按范围进行分区;比如:id小于100;id大于100小于200;
     2.List分区:跟range分区类似,不过它存放的是一个离散值的集合。
     3.Hash分区:对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目),或直接使用分区语句,比如partition p0 values in…..。
     4.Key分区:与hash分区类似,只不过分区支持一列或多列,并且MySQL服务器自身提供hash函数。

四:实现分区

1.按range范围分区:

 CREATE TABLE `core_sys_log_dev` (
 `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
 `log_time` datetime DEFAULT NULL,
 `log_type` varchar(50) DEFAULT NULL,
 `log_info` varchar(1000) DEFAULT NULL,
 `client_ip` varchar(20) DEFAULT NULL,
 `server_ip` varchar(20) DEFAULT NULL,
 `server_host` varchar(100) DEFAULT NULL,
 `request_uri` varchar(200) DEFAULT NULL,
 `file_name` varchar(100) DEFAULT NULL,
 `file_line` int(11) DEFAULT NULL,
 `function` varchar(1000) DEFAULT NULL,
 PRIMARY KEY (`log_id`),
 KEY `log_type` (`log_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 PARTITION by RANGE(log_id) //注意分区的时候包含的必须是主键
( partition p0 values less than(100000),
   partition p1 values less than(300000),
  partition p3 values less than(600000)
);//按id的值划分

2.按list分区:

 CREATE TABLE `core_sys_log_dev` (
 `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
 `log_time` datetime DEFAULT NULL,
 `log_type` int(20) DEFAULT NOT NULL,
 `log_info` varchar(1000) DEFAULT NULL,
 `client_ip` varchar(20) DEFAULT NULL,
 `server_ip` varchar(20) DEFAULT NULL,
 `server_host` varchar(100) DEFAULT NULL,
 `request_uri` varchar(200) DEFAULT NULL,
 `file_name` varchar(100) DEFAULT NULL,
 `file_line` int(11) DEFAULT NULL,
 `function` varchar(1000) DEFAULT NULL,
 PRIMARY KEY (`log_id`,`log_type`),
 KEY `log_type` (`log_type`) USING BTREE ) 
ENGINE=InnoDB AUTO_INCREMENT=1 PARTITION by list(log_type) //注意分区的时候包含的必须是主键
( partition p0 values less than(1,3),
   partition p1 values less than(2,4),
  partition p3 values less than(5,6)
);
相当于命中集合

3.按hash分区:

 CREATE TABLE `core_sys_log_dev` (
 `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
 `log_time` datetime DEFAULT NULL,
 `log_type` int(20) DEFAULT NOT NULL,
 `log_info` varchar(1000) DEFAULT NULL,
 `client_ip` varchar(20) DEFAULT NULL,
 `server_ip` varchar(20) DEFAULT NULL,
 `server_host` varchar(100) DEFAULT NULL,
 `request_uri` varchar(200) DEFAULT NULL,
 `file_name` varchar(100) DEFAULT NULL,
 `file_line` int(11) DEFAULT NULL,
 `function` varchar(1000) DEFAULT NULL,
 PRIMARY KEY (`log_id`,`log_time`),
 KEY `log_type` (`log_type`) USING BTREE ) 
ENGINE=InnoDB AUTO_INCREMENT=1 PARTITION by hash(to_days(log_time)) partitions 4 //注意分区的时候包含的必须是主键,可以实现按日期分区
不过如果这样按日期分区的话,查询的时候mysql识别不出这种日期,会全表扫描
所以需要修改下
Alter table core_sys_log_dev partition by RANGE(to_days(log_time)) (
 PARTITION p0 VALUES less than (to_days('2015-02-25')), 
 PARTITION p1 VALUES less than (to_days('2015-03-01')),
 PARTITION p2 VALUES less than (to_days('2015-03-05')),
 PARTITION p3 VALUES less than (to_days('2015-03-10')),
 PARTITION p4 VALUES less than (to_days('2015-03-15'))
)
NoteBlog

4.按key分区:

 CREATE TABLE `core_sys_log_dev` (
 `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
 `log_time` datetime DEFAULT NULL,
 `log_type` varchar(50) DEFAULT NULL,
 `log_info` varchar(1000) DEFAULT NULL,
 `client_ip` varchar(20) DEFAULT NULL,
 `server_ip` varchar(20) DEFAULT NULL,
 `server_host` varchar(100) DEFAULT NULL,
 `request_uri` varchar(200) DEFAULT NULL,
 `file_name` varchar(100) DEFAULT NULL,
 `file_line` int(11) DEFAULT NULL,
 `function` varchar(1000) DEFAULT NULL,
 PRIMARY KEY (`log_id`),
 KEY `log_type` (`log_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1  partition by key(log_time) partitions 4 //注意分区的时候包含的必须主键
这个分区类似于hash分区,除了MySQL服务器使用它本身的hash表达式,不像其他类型的分区,不必要求使用一个int或null的表达式。

5.按子分区进行分区:

 CREATE TABLE `core_sys_log_dev` (
 `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
 `log_time` datetime DEFAULT NULL,
 `log_type` int(20) DEFAULT NOT NULL,
 `log_info` varchar(1000) DEFAULT NULL,
 `client_ip` varchar(20) DEFAULT NULL,
 `server_ip` varchar(20) DEFAULT NULL,
 `server_host` varchar(100) DEFAULT NULL,
 `request_uri` varchar(200) DEFAULT NULL,
 `file_name` varchar(100) DEFAULT NULL,
 `file_line` int(11) DEFAULT NULL,
 `function` varchar(1000) DEFAULT NULL,
 PRIMARY KEY (`log_id`),
 KEY `log_type` (`log_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1  partition by range(log_id) 
subpartition by hash(log_type) subpartitions 2
(
partition p0 values less than(5),
partition p1 values less than(10),
partition p3 values less than(15)
); //注意分区的时候包含的必须主键
这个分区类似于hash分区,除了MySQL服务器使用它本身的hash表达式,不像其他类型的分区,不必要求使用一个int或null的表达式。

五:获取分区信息

MySQL可以通过如下方式来获取分区表的信息:
Show create tabe table;      //表详细结构
show table status;     //表的各种参数状态
select * from information_schema.partitions;//通过数据字典来查看表的分区信息
explain partitions select * from table;   // 通过此语句来显示扫描哪些分区,及他们是如何使用的.

六:修改分区

1.由于我们平常使用的数据库大都是动态运行的,所以只对某个表分区进行修改就OK了。
可以对range或list表分区进行add或drop,也可以对hash或key分区表进行合并或分解。这些动作都在alter table语句里进行。
使用add partition 关键字来对已有分区表进行添加。
Alter table orders_range add partition(Partition p5 values less than(maxvalue))

2.Reorganize partition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。
Splitting即分解一个已有分区:
Alter table orders_range reorganize partition p0 into (partition n0 values less than(5000),partition n1 values less than(10000));

3.Merge分区:像上面把p0分成n0和n1,现在在把2个合并为一个。
Alter table orders_range reorganize partition n0,n1 into (Partition p0 values less than(10000));

4.修改所有的分区:在into关键字之前或之后都指定多个分区
Alter table orders_range reorganize partition p0,p1,p2,p3,p4,p5 into (Partition r0 values less than(25000),Partition r1 values less than(50000),Partition r2 values less than(maxvalue));

5.Coalesce 合并分区:
Merge分区的另一种方法就是alter table….coalesce partition语句,你不能对hash或key分区进行删除
Alter table orders_key coalesce partition1;

6.Redefine重定义分区
Alter table orders_range partition by hash(id) partitions 4;

7.Drop 分区:
可以对range或list类型的分区通过drop partition 关键字进行删除
Alter table orders_range drop partition p0;
注意:
1.对这个分区进行删除时,你会把这个分区的所有数据进行删除,与delete语句相等;
2.在做alter table..drop partition时,必须有drop权限;
3.运行这个删除命令,它不会返回删除了的行,可以通过select count()语句查看。
如果想对多个分区进行删除,可以使用如下命令语句:Alter table orders_range drop partition p1,p2;

8.删除所有分区
通过如下命令语句删除表中所有分区,最后是一个正规表.
Alter table orders_range remove partitioning;

七:分区性能

1.创建分区表比无分区的正规表要稍微慢些;
2.通过alter table….drop partition语句进行删除比delete语句要快些;
3.在range或list分区类型上添加分区(alter table…add partition语句)是相当快的,因为没有移动数据到新分区里。
4.当在一个key或hash类型的分区上执行alter table….add partition语句,要依赖表中已有多少行记录,数据越多,它添加一个新分区的时间就越长。当创建一个表时,使用线性hash或线性key分区是相当快的。
5.对成百上千的行记录,进行alter table …coalesce partition, alter table …reorganize partition, alter table…partition by操作命令时,是相当慢的。
6.当使用add partition命令时,线性hash和线性key分区会使coalesce partition操作更快, alter table …remove partitioning比其他都要快,因为mysql没有要求哪个文件来替代行,即使是移动数据。

八:存储引擎和分区

     MySQL分区可以对所有MySQL支持的存储引擎进行分区,比如:myisam, innodb, archive, NDBcluster(只可以线性key),falcon, 不支持分区的引擎:merge, federated, csv, blackhole
注意:所有分区和子分区的表类型要一致;
      索引维护要依赖表类型;
      锁住某些行,也依赖于存储引擎;
      分区也属于存储引擎的顶层,所以进行update和insert时,性能不会产生很大的影响。
各种存储引擎使用分区时的限制:
MyISAM引擎:
Myisam引擎允许在使用分区时,把表的不同部分存储在不同地方,包括索引目录和数据目录。
下面是一个关于把数据分布到4个不同的物理磁盘上的myisam分区。
Create table orders_hash2
(
Id int auto_increment primary key, ……
) engine=myisam

Partition by hash(id)
(
Partition p0 index directory=’/data0/orders/idx’
data directory=’ /data0/orders/data’,
Partition p1 index directory=’/data1/orders/idx’
data directory=’ /data1/orders/data’,
Partition p2 index directory=’/data2/orders/idx’
data directory=’ /data2/orders/data’,
Partition p3 index directory=’/data3/orders/idx’
data directory=’ /data3/orders/data’,
);

注意:上面的具体4个分布,在windows系统上目前还不支持。
InnoDB引擎:
Innodb的分区管理与myisam引擎的管理是不同的。

九:分区的限制

     所有的分区必须使用同种引擎;
     批量装载很慢;
     每个表的最大分区数为1024;
     不支持三维数据类型(GIS);
     不能对临时表进行分区;
     不可能对日志表进行分区;
     不支持外键;
     不支持全文表索引;
     不支持load cache和load index into cache;
     只允许对range和list类型的分区再进行分区;
     子分区的类型只允许是hash或key.
     Range,list, hash分区必须是int类型;
     Key分区不可以有text,blob类型;
     不允许使用UDF,存储函数,变量,操作符(|,,^,<<,>>,~)和一些内置的函数;
     在表创建之后sql mode不可以改变;
     在分区表达式中,不允许子查询;
     分区表达式中必须包括至少一个列的引用,唯一索引列也可以(包括主键)
    MERGE引擎不支持分区,分区表也不支持merge。
    FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。
    CSV引擎不支持分区
    BLACKHOLE引擎不支持分区
    在NDBCLUSTER引擎上使用分区表,分区类型只能是KEY(or LINEAR KEY) 分区。
    当升级MYSQL的时候,如果你有使用了KEY分区的表(不管是什么引擎,NDBCLUSTER除外),那么你需要把这个表dumped在reloaded。
    分区表的所有分区或者子分区的存储引擎必须相同,这个限制也许会在以后的版本取消。
    不指定任何引擎(使用默认引擎)。
    所有分区或者子分区指定相同引擎。

2.限制函数
   DAY();DAYOFMONTH();DAYOFWEEK();DAYOFYEAR();DATEDIFF();EXTRACT();HOUR();MICROSECOND();MINUTE();MOD();MONTH();QUARTER();SECOND();TIME_TO_SEC();TO_DAYS();WEEKDAY();YEAR();YEARWEEK()