- 浏览: 84705 次
- 性别:
- 来自: 上海
文章分类
最新评论
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”来修补该分区。
将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”来修补该分区。
发表评论
-
MySQL索引原理以及查询优化
2019-02-26 16:39 382转:https://www.cnblogs.com/bypp/ ... -
mysql 常见运维操作
2018-01-21 10:44 0查看I0线程 select a.thd_id,b.THREAD ... -
mysql binlog详解及常用操作
2018-01-21 10:10 378转http://blog.csdn.net/wyzxg/art ... -
Mysql半同步复制
2018-01-18 17:24 355转:https://www.cnblogs.com/ivict ... -
Mysql 导出命令mysqldump
2017-10-13 17:52 302转:http://www.cnblogs.com/chenmh ... -
Mysql 备份/还原方案
2017-08-24 13:38 336Mysql 备份方案 Mysql 的四种备份方案参考: ht ... -
Mysql 备份方案
2017-08-23 17:33 13转:http://blog.csdn.net/hiji ... -
SQL 优化步骤
2016-08-20 13:49 342当一个SQL慢的时候,我们第一步是先看执行计划 explai ... -
: Mysql 监控优化之---MYSQLReport
2016-08-12 16:11 304http://hackmysql.com/ 软件下载: htt ... -
Mysql 监控优化之---MySQLTuner
2016-08-12 16:08 389MySQLTuner 会帮助我们检查mysql当前的系统配置是 ... -
Mysql emoji字符
2016-05-25 11:43 352权声明:本文为博主原创文章,未经博主允许不得转载。 以前在创 ... -
修改timestamp 默认值
2015-05-13 19:58 493case1: ALTER TABLE `mydb`.`cd ... -
mysql主从复制跳过错误
2015-04-19 19:54 543mysql主从复制,经常会遇到错误而导致slave端复制中断, ... -
mysql双主同步
2015-04-19 19:33 339Mysql 5.6.23 CENTOS 6.4 X86 ... -
查看LOGBIN日志
2015-04-19 17:17 2831 在my.ini(window)配置文件里面 [mysql ...
相关推荐
例如,可以先使用DROP TABLE IF EXISTS语句来删除已存在的分区表,然后使用CREATE TABLE语句来创建新的分区表,并在其中定义分区规则。 总的来说,MySQL表分区通过将一个大表的数据分散存储到多个物理位置,从而...
3. **添加数据**:向分区表中插入数据,MySQL会自动根据分区规则分配。 4. **管理分区**:可以添加、删除或重定义分区,以适应数据变化。 **示例:RANGE分区创建** ```sql CREATE TABLE p_range ( id INT(10) NOT...
- 在分区表的上下文中,触发器可能用于在数据插入或更新时自动调整分区,确保数据分布合理。 7. **维护与备份**: - 分区使得大型表的备份和恢复更加高效,因为可以分别处理每个分区,而非整个表。 - 数据迁移和...
详细介绍MYSQL表分区的原理及步骤!什么是数据库分区? 数据库分区是一种物理数据库设计技术,DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的...
MySQL分区表是一种优化大型数据表查询效率的技术,它将一个大表分成多个逻辑上相连但物理上独立的部分,每个部分称为一个分区。分区可以按照不同的策略进行,如范围、哈希、列表或复合分区。这样做有助于提高数据...
### SQL Server 2005新特性之表分区的功能 #### 一、引言 随着企业数据量的不断增加,数据库管理系统的性能优化变得尤为重要。为了应对海量数据带来的挑战,SQL Server 2005引入了表分区功能。通过将表中的数据...
MySQL 5.5.8 版本在数据库领域是一个重要的里程碑,它引入了许多性能改进和新特性,尤其是在分区表方面。分区表是大型数据库系统中优化查询性能的一种策略,通过将大表的数据逻辑上划分为更小、更易管理的部分。在...
实现mysql 每天定时自动给数据库表追加分区,包含存储计划和存储过程
"互联网公司为啥不使用mysql分区表" 在互联网公司中,mysql分区表是一个比较少用的技术,这是因为分区表存在一些缺陷和限制,使得互联网公司更多地选择自己分库分表来水平扩展数据库。 首先,分区表的设计需要考虑...
mysql5.1开始支持数据表分区了。如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。二、为什么要对表进行分区为了改善大型表以及具有各种...
#### 三、MySQL分区表常用操作示例 接下来通过具体的示例来展示如何创建不同类型的分区表: 1. **创建RANGE分区**: - 基于员工工资创建范围分区:`CREATE TABLE emp (empno VARCHAR(20) NOT NULL, empname ...
首先,我们需要理解MySQL分区的概念。分区是将一个大表逻辑上划分为多个较小的部分,每个部分称为一个分区。对于时间序列数据,如日志记录,根据日期或时间进行分区可以显著提高查询速度,因为查询通常针对特定时间...
MySQL分区是一种数据库优化技术,它将大型表分成更小、更易管理的部分,每个部分称为一个分区。这种技术有助于提高查询性能,特别是对于那些需要处理大量数据或执行复杂查询的应用程序。MySQL支持多种分区类型,包括...
【MySQL分区表详解】 分区表是数据库管理中一种高级的数据组织策略,主要目的是为了提高大表的查询效率和数据管理的便利性。在MySQL中,分区表是通过`PARTITION BY`子句来实现的,它允许我们将一个大的表根据特定的...
一共10套教程。深入浅出MySQL生产环境高可用架构MyCat教程 尚硅谷Redis视频 高性能mysql优化 打造扛得住的MySQL数据库架构(5.7 阿里大神讲授MySQL数据库运维...MySQL表分区管理 MYSQL运维DBA实战(5.5,一般,不推荐了)