MYSQL分区表测试MYSQL分区表测试一、mysql分区简介数据库分区数据库分区是一种物理数据库设计技术。
虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。
MYSQL的分区主要有两种形式：水平分区和垂直分区水平分区（Horizontal Partitioning）这种形式的分区是对根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。
所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
水平分区一定要通过某个属性列来分割。
常见的比如年份，日期等。
垂直分区（Vertical Partitioning）这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。
可以用show variables like '%partition%';命令查询当前的mysql数据库版本是否支持分区。
分区的作用：数据库性能的提升和简化数据管理在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。
分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不用手工干预。
mysql从5.1版本开始支持分区。
每个分区的名称是不区分大小写。
同个表中的分区表名称要唯一。
二、mysql分区类型根据所使用的不同分区规则可以分成几大分区类型。
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。
必须有一列或多列包含整数值。
复合分区：基于RANGE/LIST 类型的分区表中每个分区的再次分割。
子分区可以是HASH/KEY 等类型。
三、mysql分区表常用操作示例以部门员工表为例子：1) 创建range分区create table emp(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date,salary int)partition by range(salary)(partition p1 values less than (1000),partition p2 values less than (2000),partition p3 values less than maxvalue);以员工工资为依据做范围分区。
create table emp(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)partition by range(year(birthdate))(partition p1 values less than (1980),partition p2 values less than (1990),partition p3 values less than maxvalue);以year(birthdate)表达式（计算员工的出生日期）作为范围分区依据。
这里最值得注意的是表达式必须有返回值。
2) 创建list分区(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));以部门作为分区依据，每个部门做一分区。
3) 创建hash分区HASH分区主要用来确保数据在预先确定数目的分区中平均分布。
在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
(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 V ALUES in (10), PARTITION p2 V ALUES 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 V ALUES in (10),PARTITION p2 V ALUES 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));分解分区:Reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。
分解前后分区的整体范围应该一致。
alter table tereorganize partition p1 into(partition p1 values less than (100),partition p3 values less than (1000)); ----不会丢失数据合并分区:Merge分区：把2个分区合并为一个。
alter table tereorganize partition p1,p3 into(partition p1 values less than (1000));----不会丢失数据重新定义hash分区表:Alter table emp partition by hash(salary) partitions 7; ----不会丢失数据重新定义range分区表:Alter table emp partition by range(salary)(partition p1 values less than (2000),partition p2 values less than (4000)); ----不会丢失数据删除表的所有分区:Alter table emp remove partitioning;--不会丢失数据重建分区：这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。