mysql数据库怎么管理表?mysql数据库分区表管理,二
概述
续昨天对mysql分区表相关原理介绍的内容,今天主要介绍一下如何创建各类型的分区以及分区日常维护的一些命令,仅供参考。
mysql分区理论:https://mp.toutiao.com/profile_v3/graphic/preview?pgc_id=6829658074215612941
一、创建各类型分区
1、创建range分区
--以员工工资为依据做范围分区create table emp1(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);--以year(birthdate)表达式(计算员工的出生日期)作为范围分区依据。这里最值得注意的是表达式必须有返回值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 );
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、创建复合分区
5.1、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);
5.2、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);
5.3、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));
5.4、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));
二、分区表日常维护常用命令
1、数据库分区表概览
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS, DATA_LENGTH / 1024 / 1024 "DATA(MB)", INDEX_LENGTH / 1024 / 1024 "INDEX(MB)", CREATE_TIME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE PARTITION_NAME IS NOT NULL
2、查看创建分区表的create语句
show create table 表名
3、删除分区:
--不可以删除hash或者key分区alter table emp drop partition p1;-- 一次性删除多个分区alter table emp drop partition p1,p2;
4、增加分区
alter table emp add partition (partition p3 values less than (4000));alter table empl add partition (partition p3 values in (40));
5、分解分区
Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。
alter table emp reorganize partition p1 into(partition p1 values less than (100),partition p3 values less than (1000)); --不会丢失数据
6、合并分区
Merge分区:把2个分区合并为一个。
--不会丢失数据alter table emp reorganize partition p1,p3 into (partition p1 values less than (1000));
7、重新定义hash分区表
--不会丢失数据Alter table emp partition by hash(salary) partitions 7;
8、删除表的所有分区:
alter table emp remove partitioning; --不会丢失数据
9、重建分区
这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。
ALTER TABLE emp rebuild partitionp1,p2;
10、优化分区
如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。
ALTER TABLE emp optimize partition p1,p2;
11、分析分区
读取并保存分区的键分布。
ALTER TABLE emp analyze partition p1,p2;
12、检查分区
可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。
ALTER TABLE emp CHECK partition p1,p2;
这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
- 01-02一帘幽梦紫菱个人简介 紫菱是我弱我有理的绿茶本人
- 12-13未来一个月的运势周公解梦,周公解梦梦里有这几个场景
- 12-09睡觉时从高处掉下来就惊醒,感觉从高空坠落被惊醒
- 01-11欧派不锈钢炒锅用什么锅铲好?不粘锅最完美CP,BergHOFFLeo硅胶锅铲
- 12-10春风450sr跟凯越450rr缺点:春风,450SR与凯越450RR应该如何选择
- 09-20声优表白男闺蜜视频 惊呆,扮男主播与闺蜜
- 01-12值得长期不换的可爱微信头像卡通,一张张可爱又迷人的卡通微信头像
- 04-23尼康z30固件升级后效果,尼康Z30相机1.10版固件更新
- 12-10fila是贴标的安踏吗?FILA不是安踏唯一的烦恼
- 02-12七叶一枝花多少钱一斤才算好,七叶一枝花价格多少钱一斤
- 05-10红楼梦所有人物诗词,红楼梦百字诗第十回作者
- 12-03纳米喷雾补水仪充电使用方法 纳米喷雾仪补水仪USB充电
- 11-10全民k歌粉丝总量排行榜:月活跃用户数亿全民K歌七年进阶之路
- 09-26人生感悟100个小故事 读完这三个关于人生成长的小故事
- 04-01北方常吃的鱼有什么?冬天最爱吃的鱼全身发白还特别软嫩
- 03-28郭敬明有几部电影被下架了:青春粮不好卖了最世三大刊面临
热门
推荐
- 1吸收的英文短语425
- 2围棋胜负判别的方法是什么399
- 3为什么着色作文500字118
- 4黄酒泡阿胶有什么功效244
- 5英语口语练习材料315
- 6大学生创业励志语句141
- 7大学生个人检讨书115
- 8三十年老同学聚会祝词435