文档章节

Mysql 表分区技术

w
 wertyiiiop2
发布于 2016/05/27 10:18
字数 1329
阅读 7
收藏 0

Mysql 表分区操作及相关操作 

将ID设为自增长ID 
alter table pw_trade_record_temp change ID ID int(4) auto_increment; 

分区字段必需为主键,修改主键 

ALTER TABLE `pw_trade_record` 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (`ID`, `TRADE_TIME`); 

直接在建表时创建表分区 

创建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)表达式(计算员工的出生日期)作为范围分区依据。这里最值得注意的是表达式必须有返回值下载地址   。 

创建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; 

创建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; 


创建复合分区 


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) 




修改现有未分区的表,给表做分区 

ALTER TABLE `pw_trade_record` 
PARTITION BY RANGE(UNIX_TIMESTAMP(TRADE_TIME)) 

PARTITION p20160501 VALUES LESS THAN (UNIX_TIMESTAMP('2016-05-01')), 
PARTITION pmax VALUES LESS THAN MAXVALUE); 


增加分区 
alter table pw_trade_record add partition (partition p20160518 values in (UNIX_TIMESTAMP('2016-05-18'))); 

删除分区(一次性可删除多个分区) 
alter table pw_trade_record drop partition p20140101,p20160501,...; 

删除表的所有分区 
Alter table emp removepartitioning;--不会丢失数据 

合并表分区 
示例:将表分区p20140101,p20160501合并到p20160501分区中 
alter table pw_trade_record reorganize partition p20140101,p20160501 into 
(partition p20160501 values less than (UNIX_TIMESTAMP('2016-05-18')), 
PARTITION pmax VALUES LESS THAN MAXVALUE 
); 

分解表分区 
示例:将表分区pmax分解成p20160518和pmax两个分区 
alter table pw_trade_record reorganize partition pmax into 
(partition p20160518 values less than (UNIX_TIMESTAMP('2016-05-18')), 
PARTITION pmax VALUES LESS THAN MAXVALUE 
); 

查看分区表状态 
select table_schema,table_name,partition_name,partition_ordinal_position,partition_method,partition_expression,partition_description,table_rows,avg_row_length,data_length from INFORMATION_SCHEMA.PARTITIONS where table_name='pw_trade_record'; 


重新定义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 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”来修补该分区。 

© 著作权归作者所有

w
粉丝 1
博文 3
码字总数 3929
作品 0
杭州
私信 提问
MySql,Sql Server分区技术浅析

一.MySql MySql在5.1以后的版本中加入了分区技术,其不同于以往的分表技术,之前的分表技术是把一张大的表水平(按照一定的逻辑)分成多张表,比如如果我们的User表中有1000万条数据,那如果...

wbf961127
2017/11/12
0
0
MySQL表分区优势及分类简析 MySQL DBA学习

表分区是将一个表的数据按照一定规则水平划分成不同的逻辑块,并分别进行物理存储,这个规则就叫做分区函数,可以有不同的分区规则。通过show plugins语句查看当前MySQL是否支持表分区功能。...

mo默瑶
2018/05/24
0
0
MySQL表分区类型简析 数据库周末提升

  表分区是将一个表的数据按照一定规则水平划分成不同的逻辑块,并分别进行物理存储,这个规则就叫做分区函数,可以有不同的分区规则。通过show plugins语句查看当前MySQL是否支持表分区功...

zhouzhou2018
2018/05/24
0
0
mysql 表分区技术

,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。 今天,我通过查阅相关资料与动手操作,学习了一下数...

qweasfzxc880
2016/02/25
84
0
mysql —— 分表分区

面对当今大数据存储,设想当mysql中一个表的总记录超过1000W,会出现性能的大幅度下降吗? 答案是肯定的,一个表的总记录超过1000W,在操作系统层面检索也是效率非常低的 解决方案: 目前针对...

zyt_1978
2016/07/06
19
0

没有更多内容

加载失败,请刷新页面

加载更多

gradle grovvy中的闭包

1. 无参数的闭包 //这b1就是一个闭包def b1={ println "hello b1"}//定义方法,包含闭包类型的参数def method1(Closure closure){closure()}//执行method1method1(b1) 执行结果 ...

edison_kwok
29分钟前
3
0
基于Spring Boot + Dubbo的全链路日志追踪(一)

一、 概要 当前公司后端整体架构为:Spring Boot + Dubbo。由于早期项目进度等原因,对日志这块没有统一的规范,基本上是每个项目自己管自己的日志。这也对后面的问题排查带来了很大的困难,...

明天以后
今天
7
0
安装fastdfs文件服务器步骤

1、安装libfastcommon wget https://github.com/happyfish100/libfastcommon/archive/master.zip 解压后安装 cd fastcommon-master ./make.sh ./make.sh install 2、安装 FastDFS,从sourcef......

lsjlgo
今天
3
0
MySQL 5.7 免安装版配置

下载地址:https://dev.mysql.com/downloads/mysql/ 安装步骤 1.下载zip解压到目录下 2.配置环境变量 新建系统变量:MYSQL_HOME,值:D:\DevelopmentTool\Mysql-5.7.26-winx64 修改path变量:...

华山猛男
今天
7
0
java map的遍历

//从大的角度可以分为两类Set<String> set=map.keySet();这里面还可以分为3类, 从set的角度来分 //Set<Map.Entry<String, String>> entery=map.entrySet(); public class Test { public sta......

南桥北木
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部