一:什么时候分区
分区针对不同的数据库,具有不同的特性。在这里专门针对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')) )![]()
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=1partition 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=1partition 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()