什么是表分区
通俗地讲表分区是将一大表，根据条件分割成若干个小表。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
| 代码如下 | 复制代码 |
|
create table foo ( id int not null auto_increment, created DATETIME, primary key (id, created) ) engine = innodb partition by range (TO_DAYS(created))( PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')), PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01')) ) 增加表分区 ALTER TABLE foo ADD PARTITION( PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01')) ) 插入一条数据 insert into `foo` (`id`, `created`) values (1, '2008-01-02'),(2, '2009-01-02'); 分析确认分区是否生效 explain partitions select * from foo where created = '2008-01-02'; |
|
创建list分区
| 代码如下 | 复制代码 |
|
create table emp (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by list(deptno) ( partition p1 values in (10), partition p2 values in (20), partition p3 values in (30) ); |
|
以部门作为分区依据，每个部门做一分区。
创建hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
| 代码如下 | 复制代码 |
|
create table emp (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by hash(year(birthdate)) partitions 4; |
|
4) 创建key分区
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。
| 代码如下 | 复制代码 |
|
create table emp (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by key(birthdate) partitions 4;
|
|
5) 创建复合分区
| 代码如下 | 复制代码 |
|
range - hash(范围哈希)复合分区
create table emp (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by range(salary) subpartition by hash(year(birthdate)) subpartitions 3 ( partition p1 values less than (2000), partition p2 values less than maxvalue ); |
|
range- key复合分区
| 代码如下 | 复制代码 |
|
create table emp (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by range(salary) subpartition by key(birthdate) subpartitions 3 ( partition p1 values less than (2000), partition p2 values less than maxvalue ); |
|
list - hash复合分区
| 代码如下 | 复制代码 |
|
CREATE TABLE emp ( empno varchar(20) NOT NULL, empname varchar(20) , deptno int, birthdate date NOT NULL, salary int ) PARTITION BY list (deptno) subpartition by hash(year(birthdate)) subpartitions 3 ( PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) ) ; |
|
list - key 复合分区
| 代码如下 | 复制代码 |
|
CREATE TABLE empk ( empno varchar(20) NOT NULL, empname varchar(20) , deptno int, birthdate date NOT NULL, salary int ) PARTITION BY list (deptno) subpartition by key(birthdate) subpartitions 3 ( PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) ) ; |
|
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关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。
| 代码如下 | 复制代码 |
|
alter table te reorganize partition p1 into ( partition p1 values less than (100), partition p3 values less than (1000) ); ----不会丢失数据
|
|
合并分区:
Merge分区：把2个分区合并为一个。
| 代码如下 | 复制代码 |
|
alter table te reorganize partition p1,p3 into (partition p1 values less than (1000)); |
|
----不会丢失数据
重新定义hash分区表:
Alter table emp partition by hash(salary)partitions 7;
----不会丢失数据
重新定义range分区表:
| 代码如下 | 复制代码 |
|
Alter table emp partitionbyrange(salary) ( partition p1 values less than (2000), partition p2 values less than (4000) ); ----不会丢失数据
|
|
删除表的所有分区:
| 代码如下 | 复制代码 |
| 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”来修补该分区。