Mysql分区表
Mysql分区表
1 分区概念
分区针对不同的数据库，具有不同的特性。在这里专门针对MySQL数据库而言。在MySQL数据库里，分区这个概念是从mysql 5.1才开始提供的。不过目前只有在mysql advanced版本里才提供。
分区是把数据库、或它的组成部分(比如表)分成几个小部分。而且专门介绍的都是’水平分区’，即对表的行进行划分。
2 分区的优点
- 可以提高数据库的性能；
- 对大表(行较多)的维护更快、更容易，因为数据分布在不同的逻辑文件上；
- 删除分区或它的数据是容易的，因为它不影响其他表。
注意：pruning，即截断。意思是说当你查询时，只扫描所需要查询的分区。。其他部分不会扫描。。这就大大地提高了性能。
3 分区类型
分区具有如下4种类型：
Range分区：是对一个连续性的行值，按范围进行分区；比如：id小于100；id大于100小于200；
List分区：跟range分区类似，不过它存放的是一个离散值的集合。
Hash分区：对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目)，或直接使用分区语句,比如partition p0 values in……。
Key分区：与hash分区类似，只不过分区支持一列或多列，并且MySQL服务器自身提供hash函数。
分区语法：
create table t(id int,name varchar(20)) engine=myisam partition by range(id);
按range范围进行分区：
create table orders_range
(
id int auto_increment primary key,
customer_surname varchar (30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500)
) engine=myisam
partition by range(id)
(
partition p0 values less than(5),
partition p1 values less than(10),
partition p3 values less than(15)
);
其实上面的分区创建，我们可知道，它的表类型为myisam，而每个分区的引擎也是myisam，这个可以通过show create table tablename查看。当我们插入数据到表里时，如果要查看小于8的信息，它之后检索p0和p12个分区。这样就非常快速了。
按list进行分区：
create table orders_list
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500),
index idx(id)
) engine=myisam partition by list(store_id)
(
partition p0 values in(1,3),
partition p1 values in2,4,6),
partition p3 values in(10)
);
list 分区只能把你插入的值放在某个已定的分区里，若没有那个值，，就显示不能插入。
按hash进行分区：
create table orders_hash
(
id int auto_increment primary key,
cutomer_surname varchar(30),
store_id int,
salesperon_id int,
order_date date,
note varcahr(500)
) engine=myisam partition by hash(id) partitions 4;
如果分为4个分区，那当我插入数据时，哪些数据是放在哪些分区里呢？ 当我对某个id值进行检索时，它明确说放到哪个分区里？或者说是有什么内部机制？
使用hash分区，最主要就是确保数据的分配，它是基于create table时提供的表达式。不必定义单独的分区，只要使用partitions关键字和所需要分多少个区的数字。语句如上所述。
按key进行分区：
create table orders_key
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
alesperson_id int,
order_Date date,
note varcahr(500),
index_idx(id)
) engine=myisam partition by key(order_date) partitions 4;
这个分区类似于hash分区，除了MySQL服务器使用它本身的hash表达式，不像其他类型的分区，不必要求使用一个int或null的表达式。
4 子分区
按子分区进行分区：
create table orders_range
(
id int auto_increment primary key,
customer_surname varchar(30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500)
) engine=myisam partition by range(id)
subpartition by hash(store_id) subpartitions 2
(
partition p0 values less than(5),
partition p1 values less than(10),
partition p3 values less than(15)
);
当把数据插入到表中时，那什么数据是放在子分区里呢？
5 分区的查看和修改
MySQL可以通过如下方式来获取分区表的信息:
Show create tabe table; //表详细结构
show table status; //表的各种参数状态
select * from information_schema.partitions；//通过数据字典来查看表的分区信息
explain partitions select * from table; // 通过此语句来显示扫描哪些分区，及他们是如何使用的.
对分区进行修改 (修改、合并、重定义分区)
修改部分分区：
由于我们平常使用的数据库大都是动态运行的，所以只对某个表分区进行修改就OK了。
可以对range或list表分区进行add或drop，也可以对hash或key分区表进行合并或分解。这些动作都在alter table语句里进行。
使用add partition 关键字来对已有分区表进行添加。
Alter
table
orders_range
add
partition
(
Partition p5 values less than(maxvalue)
)
Reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。
Splitting即分解一个已有分区：
Alter table orders_range
reorganize partition p0 into
(
partition n0 values less than(5000),
partition n1 values less than(10000)
);
Merge分区：像上面把p0分成n0和n1，现在在把2个合并为一个。
Alter table orders_range reorganize partition n0,n1 into
(
Partition p0 values less than(10000)
);
修改所有的分区：在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)
);
Coalesce 合并分区：
Merge分区的另一种方法就是alter table….coalesce partition语句，你不能对hash或key分区进行删除
Alter table orders_key coalesce partition1;
Redefine重定义分区
Alter table orders_range partition by hash(id) partitions 4;
对分区进行删除 (删除、删除所有分区)
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;
删除所有分区:
通过如下命令语句删除表中所有分区，最后是一个正规表.
Alter table orders_range remove partitioning;
6 分区的影响
当进行分区操作，了解对性能所产生的影响是非常有帮助的：
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没有要求哪个文件来替代行，即使是移动数据。
7 各种存储引擎的分区
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引擎的管理是不同的。 分区的限制性 下面讲到的是一些关于MySQL分区的限制性约束 常见的限制： 所有的分区必须使用同种引擎； 批量装载很慢； 每个表的最大分区数为1024； 不支持三维数据类型(GIS); 不能对临时表进行分区； 不可能对日志表进行分区； 外键和索引方面： 不支持外键； 不支持全文表索引； 不支持load cache和load index into cache； 子分区方面： 只允许对range和list类型的分区再进行分区； 子分区的类型只允许是hash或key. 分区表达式方面： Range，list, hash分区必须是int类型； Key分区不可以有text，blob类型； 不允许使用UDF,存储函数，变量，操作符(|,^,<<,>>,~)和一些内置的函数； 在表创建之后sql mode不可以改变； 在分区表达式中，不允许子查询； 分区表达式中必须包括至少一个列的引用，唯一索引列也可以(包括主键)