Shulou(Shulou.com)05/31 Report--

This article mainly explains "Mysql table partition operation method", the explanation content in the article is simple and clear, easy to learn and understand, please follow the idea of Xiaobian slowly in-depth, together to study and learn "Mysql table partition operation method"!

Mysql table partitioning operations and related operations

Set ID to self-growing ID

alter table pw_trade_record_temp change ID ID int(4) auto_increment;

Partition field must be primary key. Modify primary key

ALTER TABLE `pw_trade_record`

DROP PRIMARY KEY,

ADD PRIMARY KEY (`ID`, `TRADE_TIME`);

Create table partitions directly as you build tables

Create a range partition

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

);

Scope zoning based on employee salaries.

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

);

Range partitioning is based on the year(birthdate) expression, which calculates the employee's birth date. The most notable thing here is that the expression must have a return value download address.

Create list partition

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)

);

The division is based on the department, and each department is divided into a division.

Create hash partition

HASH partitions are primarily used to ensure that data is evenly distributed across a predetermined number of partitions. In RANGE and LIST partitions, you must specify explicitly in which partition a given column value or set of column values should be stored; in HASH partitions, MySQL does this automatically, all you have to do is specify a column value or expression based on the column value to be hashed, and specify the number of partitions into which the partitioned table will be divided.

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;

Create key partition

Partitioning by KEY is similar to partitioning by HASH, except that HASH partitions use user-defined expressions, whereas hash functions for KEY partitions are provided by MySQL servers, which use their own internal hash functions, which are based on the same algorithm as PASSWORD(). "CREATE TABLE ... The syntax rules for PARTITION BY KEY are similar to the rules for creating a table partitioned by HASH. The only difference between them is that the keyword used is KEY instead of HASH, and the KEY partition takes only one list download address with one or more column names.

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;

Create a composite partition

range-hash compound partition

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 compound partition

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 composite partition

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 composite partition

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)

)

Modify an existing unpartitioned table and partition the table

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);

Add partition

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

Delete partitions (multiple partitions can be deleted at once)

alter table pw_trade_record drop partition p20140101,p20160501,...;

Delete all partitions of a table

Alter table emp removepartitioning;--No data loss

merge table partition

Example: Merge table partitions p20140101,p20160501 into partition 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

);

decomposition table partition

Example: Split table partition pmax into two partitions p20160518 and 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

);

View partition table status

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';

Redefine hash partition table:

Alter table emp partition by hash(salary)partitions 7; ---No data loss

Redefine range partition table:

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); ----No data loss

Reconstruction of zones:

This has the same effect as deleting all records saved in the partition and then reinserting them. It can be used to defragment partitions.

ALTER TABLE emp rebuild partitionp1,p2;

Optimized partition:

If you delete a large number of rows from a partition, or if you make many modifications to a row with variable length (that is, columns of type VARCHAR, BLOB, or TEXT), you can use "ALTER TABLE... OPTIMIZE PARTITION"to reclaim unused space and defragment partitioned data files.

ALTER TABLE emp optimize partition p1,p2;

Analysis partition:

Read and save the key distribution for the partition.

ALTER TABLE emp analyze partition p1,p2;

Patch partition:

Repair damaged partitions.

ALTER TABLE emp repairpartition p1,p2;

Inspection zone:

Partitions can be checked in much the same way as CHECK TABLE is used for non-partitioned tables.

ALTER TABLE emp CHECK partition p1,p2;

This command tells you if the data or index in partition p1,p2 of table emp has been corrupted. If this happens, use ALTER TABLE... REPAIR PARTITION"to patch the partition.

Thank you for reading, the above is the "Mysql table partition operation method" content, after the study of this article, I believe we have a deeper understanding of Mysql table partition operation method this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.