MYSQL--表分区、查看分区
⼀、      mysql分区简介
数据库分区
数据库分区是⼀种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要⽬的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应⽤来说分区完全是透明的。
苹果出现importantMYSQL的分区主要有两种形式：⽔平分区和垂直分区
⽔平分区（HorizontalPartitioning）
这种形式的分区是对根据表的⾏进⾏分区，通过这样的⽅式不同分组⾥⾯的物理列分割的数据集得以组合，从⽽进⾏个体分割（单分区）或集体分割（1个或多个分区）。
所有在表中定义的列在每个数据集中都能到，所以表的特性依然得以保持。⽔平分区⼀定要通过某个属性列来分割。常见的⽐如年份，⽇期等。
垂直分区（VerticalPartitioning）
这种分区⽅式⼀般来说是通过对表的垂直划分来减少⽬标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有⾏。
可以⽤  showvariables like '%partition%';
命令查询当前的mysql数据库版本是否⽀持分区。
分区的作⽤：数据库性能的提升和简化数据管理
在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提⾼。
新人适合学什么编程语言分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不⽤⼿⼯⼲预。
nodejs有什么框架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分区
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)
);
以部门作为分区依据，每个部门做⼀分区。
3)      创建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 partition by range(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”来修补该分区。
【mysql分区表的局限性】
1.      在5.1版本中分区表对唯⼀约束有明确的规定，每⼀个唯⼀约束必须包含在分区表的分区键（也包括主键约束）。
CREATE TABLE emptt (
empno varchar(20) NOT NULL  ,
empname varchar(20)，
deptno int,
birthdate date NOT NULL,
salary int ,
primary key (empno)
)
PARTITION BY range (salary)
(
PARTITION p1 VALUES less than (100),
PARTITION p2 VALUES less than (200)
mysql查看所有存储过程);
这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table's partitioning function；
CREATE TABLE emptt (
empno varchar(20) NOT NULL  ,
empname varchar(20) ,
deptno int(11),
birthdate date NOT NULL,
salary int(11) ,
primary key (empno，salary)
)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。