当前位置:首页 > Mysql教程 > 列表

深入分析mysql数据库表分区

发布:smiling 来源: PHP粉丝网  添加日期:2014-09-23 21:40:05 浏览: 评论:0 

表分区我在很久以前有听说过,意思就是讲表分区是将一大表,根据条件分割成若干个小表,mysql5.1开始支持数据表分区了了,下面我来给各位深入分析mysql表分区的一些相关知识点,希望文章对各位有帮助.

什么是表分区:通俗地讲表分区是将一大表,根据条件分割成若干个小表,mysql5.1开始支持数据表分区了.

如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区,当然也可根据其他的条件分区.

为什么要对表进行分区:为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率.

分区的一些优点包括:

1)、与单个磁盘或文件系统分区相比,可以存储更多的数据。

2)、对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。

3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。

4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。

5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

mysql分区类型:根据所使用的不同分区规则可以分成几大分区类型.

RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。

LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

复合分区:基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

例子RANGE 分区:创建表分区range方式,也可以使用hash,list,key,代码如下:

  1. create table foo ( 
  2. id int not null auto_increment, 
  3. created DATETIME, 
  4. primary key (id, created) 
  5. ) engine = innodb partition by range (TO_DAYS(created))( 
  6. PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')), 
  7. PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01')) 
  8. //增加表分区 
  9. ALTER TABLE foo ADD PARTITION( 
  10. PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01')) 
  11. //插入一条数据 
  12. insert into `foo` (`id`, `created`) values (1, '2008-01-02'),(2, '2009-01-02'); 
  13. //分析确认分区是否生效 
  14. explain partitions select  * from foo where created = '2008-01-02'
  15.  
  16.  
  17. //创建list分区 
  18.  
  19.  create table emp 
  20. (empno  varchar(20) not null , 
  21. empname varchar(20), 
  22. deptno  int
  23. birthdate date not null
  24. salary int 
  25. partition by list(deptno) 
  26. partition p1 values in  (10), 
  27. partition p2 values in  (20), 
  28. partition p3 values  in  (30) 
  29. ); 

以部门作为分区依据,每个部门做一分区.

创建hash分区:HASH分区主要用来确保数据在预先确定数目的分区中平均分布,在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中,而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量,代码如下:

  1. create table emp 
  2. (empno varchar(20) not null , 
  3. empname varchar(20), 
  4. deptno int
  5. birthdate date not null
  6. salary int 
  7. partition by hash(year(birthdate)) 
  8. partitions 4; 

4) 创建key分区:按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL 服务器提供,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则,“CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则,它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表,代码如下:

  1. create table emp 
  2. (empno varchar(20) not null , 
  3. empname varchar(20), 
  4. deptno int
  5. birthdate date not null
  6. salary int 
  7. partition by key(birthdate) 
  8. partitions 4; 

5) 创建复合分区,代码如下:

range - hash(范围哈希)复合分区

  1. create table emp 
  2. (empno varchar(20) not null , 
  3. empname varchar(20), 
  4. deptno int
  5. birthdate date not null
  6. salary int 
  7. partition by range(salary) 
  8. subpartition by hash(year(birthdate)) 
  9. subpartitions 3 
  10. partition p1 values less than (2000), 
  11. partition p2 values less than maxvalue 
  12. ); 

range- key复合分区,代码如下:

  1. create table emp 
  2. (empno varchar(20) not null , 
  3. empname varchar(20), 
  4. deptno int
  5. birthdate date not null
  6. salary int 
  7. partition by range(salary) 
  8. subpartition by key(birthdate) 
  9. subpartitions 3 
  10. partition p1 values less than (2000), 
  11. partition p2 values less than maxvalue 
  12. );//开源软件:phpfensi.com 

list - hash复合分区,代码如下:

  1. CREATE TABLE emp ( 
  2. empno varchar(20) NOT NULL
  3. empname varchar(20) , 
  4. deptno int
  5. birthdate date NOT NULL
  6. salary int 
  7. PARTITION BY list (deptno) 
  8. subpartition by hash(year(birthdate)) 
  9. subpartitions 3 
  10. PARTITION p1 VALUES in  (10), 
  11. PARTITION p2 VALUES in  (20) 
  12. ); 

list - key 复合分区,代码如下:

  1. CREATE TABLE empk ( 
  2. empno varchar(20) NOT NULL
  3. empname varchar(20) , 
  4. deptno int
  5. birthdate date NOT NULL
  6. salary int 
  7. PARTITION BY list (deptno) 
  8. subpartition by key(birthdate) 
  9. subpartitions 3 
  10. PARTITION p1 VALUES in  (10), 
  11. PARTITION p2 VALUES in  (20) 
  12. ); 

6) 分区表的管理操作,删除分区,代码如下:

alter table emp drop partition p1;

不可以删除hash或者key分区,一次性删除多个分区,alter table emp drop partition p1,p2;

增加分区,代码如下:

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

分解分区:Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据,分解前后分区的整体范围应该一致,代码如下:

  1. alter table te 
  2. reorganize partition p1 into 
  3. partition p1 values less than (100), 
  4. partition p3 values less than (1000) 
  5. ); 

不会丢失数据,合并分区,Merge分区,把2个分区合并为一个,代码如下:

  1. alter table te 
  2. reorganize partition p1,p3 into 
  3. (partition p1 values less than (1000)); 

不会丢失数据,重新定义hash分区表:

Alter table emp partition by hash(salary)partitions 7;

不会丢失数据,重新定义range分区表,代码如下:

  1. Alter table emp partitionbyrange(salary) 
  2. partition p1 values less than (2000), 
  3. partition p2 values less than (4000) 
  4. ); 

不会丢失数据,删除表的所有分区,代码如下:

Alter table emp removepartitioning;--不会丢失数据

重建分区:这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果,它可用于整理分区碎片.

ALTER TABLE emp rebuild partitionp1,p2;

优化分区:如果从分区中删除了大量的行,或者对一个带有可变长度的行,也就是说,有VARCHAR,BLOB,或TEXT类型的列,作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片,代码如下:

ALTER TABLE emp optimize partition p1,p2;

分析分区:读取并保存分区的键分布,代码如下:

ALTER TABLE emp analyze partition p1,p2;

修补分区,修补被破坏的分区,代码如下:

ALTER TABLE emp repairpartition p1,p2;

检查分区:可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区.

ALTER TABLE emp CHECK partition p1,p2;

这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏,如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区.

Tags: mysql数据库表分区 mysql分区

分享到: