深入分析mysql数据库表分区

表分区我在很久以前有听说过,意思就是讲表分区是将一大表,根据条件分割成若干个小表,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. createtablefoo(
2. idintnotnullauto_increment,
3. createdDATETIME,
4. primarykey(id,created)
5. )engine=innodbpartitionbyrange(TO_DAYS(created))(
6. PARTITIONfoo_1VALUESLESSTHAN(TO_DAYS('2009-01-01')),
7. PARTITIONfoo_2VALUESLESSTHAN(TO_DAYS('2010-01-01'))
8. )
9. //增加表分区
10. ALTERTABLEfooADDPARTITION(
11. PARTITIONfoo_3VALUESLESSTHAN(TO_DAYS('2011-01-01'))
12. )
13. //插入一条数据
14. insertinto`foo`(`id`,`created`)values(1,'2008-01-02'),(2,'2009-01-02');
15. //分析确认分区是否生效
16. explainpartitionsselect*fromfoowherecreated='2008-01-02';
19. //创建list分区
21. createtableemp
22. (empnovarchar(20)notnull,
23. empnamevarchar(20),
24. deptnoint,
25. birthdatedatenotnull,
26. salaryint
27. )
28. partitionbylist(deptno)
29. (
30. partitionp1valuesin(10),
31. partitionp2valuesin(20),
32. partitionp3valuesin(30)
33. );

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

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

1. createtableemp
2. (empnovarchar(20)notnull,
3. empnamevarchar(20),
4. deptnoint,
5. birthdatedatenotnull,
6. salaryint
7. )
8. partitionbyhash(year(birthdate))
9. partitions4;

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

1. createtableemp
2. (empnovarchar(20)notnull,
3. empnamevarchar(20),
4. deptnoint,
5. birthdatedatenotnull,
6. salaryint
7. )
8. partitionbykey(birthdate)
9. partitions4;

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

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

1. createtableemp
2. (empnovarchar(20)notnull,
3. empnamevarchar(20),
4. deptnoint,
5. birthdatedatenotnull,
6. salaryint
7. )
8. partitionbyrange(salary)
9. subpartitionbyhash(year(birthdate))
10. subpartitions3
11. (
12. partitionp1valueslessthan(2000),
13. partitionp2valueslessthanmaxvalue
14. );

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

1. createtableemp
2. (empnovarchar(20)notnull,
3. empnamevarchar(20),
4. deptnoint,
5. birthdatedatenotnull,
6. salaryint
7. )
8. partitionbyrange(salary)
9. subpartitionbykey(birthdate)
10. subpartitions3
11. (
12. partitionp1valueslessthan(2000),
13. partitionp2valueslessthanmaxvalue
14. );//开源软件:phpfensi.com

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

1. CREATETABLEemp(
2. empnovarchar(20)NOTNULL,
3. empnamevarchar(20),
4. deptnoint,
5. birthdatedateNOTNULL,
6. salaryint
7. )
8. PARTITIONBYlist(deptno)
9. subpartitionbyhash(year(birthdate))
10. subpartitions3
11. (
12. PARTITIONp1VALUESin(10),
13. PARTITIONp2VALUESin(20)
14. );

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

1. CREATETABLEempk(
2. empnovarchar(20)NOTNULL,
3. empnamevarchar(20),
4. deptnoint,
5. birthdatedateNOTNULL,
6. salaryint
7. )
8. PARTITIONBYlist(deptno)
9. subpartitionbykey(birthdate)
10. subpartitions3
11. (
12. PARTITIONp1VALUESin(10),
13. PARTITIONp2VALUESin(20)
14. );

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. altertablete
2. reorganizepartitionp1into
3. (
4. partitionp1valueslessthan(100),
5. partitionp3valueslessthan(1000)
6. );

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

1. altertablete
2. reorganizepartitionp1,p3into
3. (partitionp1valueslessthan(1000));

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

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

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

1. Altertableemppartitionbyrange(salary)
2. (
3. partitionp1valueslessthan(2000),
4. partitionp2valueslessthan(4000)
5. );

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

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”来修补该分区.