MySQL Partition Table-range Partition

Author: skate
Time: 2012/11/28

 

MySQL Partition Table --- range Partition

Range Partition

Create a range partition:
Create Table 'ployees1 '(
'Id' int (11) not null,
'Fname' varchar (30) default null,
'Lname' varchar (30) default null,
'Hired' date not null default '2017-01-01 ',
'Separated 'date not null default '2017-12-31 ',
'Job _ Code' int (11) not null,
'Store _ id' int (11) default null
) Engine = InnoDB default charset = Latin1
/*! 50100 partition by range (store_id)
(Partition P0 values less than (6) engine = InnoDB,
Partition P1 values less than (11) engine = InnoDB,
Partition P2 values less than (16) engine = InnoDB,
Partition P3 values less than (21) engine = InnoDB )*/

 

Use a function expression to create a range Partition
Create Table 'ployees2 '(
'Id' int (11) not null,
'Fname' varchar (30) default null,
'Lname' varchar (30) default null,
'Hired' date not null default '2017-01-01 ',
'Separated 'date not null default '2017-12-31 ',
'Job _ Code' int (11) default null,
'Store _ id' int (11) default null
) Engine = InnoDB default charset = Latin1
/*! 50100 partition by range (Year (separated ))
(Partition PM1 values less than (2006) engine = InnoDB,
Partition PM2 values less than (2011) engine = InnoDB )*/
 
 
Maintenance command:

Add Partition
Alter table employees2 add partition (partition P0 values less than (1991); // only partitions greater than the partition key can be added

Delete Partition
Alter table employees2 drop partition P0; // you can delete any partition.

Delete partition data
Alter table employees2 truncate partition P1, P2;
Alter table employees2 truncate partition all;
Or
Delete from employees2 where separated <'2017-01-01 'or (separated> = '2017-01-01' and separated <'2017-01-01 ');

Redefinition of partitions (including renaming a partition, moving data along with it, and merging partitions)
Alter table employees2 reorganize partition P1, P3, P4 into (partition PM1 values less than (2006 ),
Partition PM2 values less than (2011 ));
 
Rebuild and re-partition
Alter table employees2 rebuild partition PM1/All; // equivalent to dropping all records, and then reinsert; can solve disk fragments

Optimization table
Alter table TT2 optimize partition PM1; // after a large number of Delete table data, space and fragment can be reclaimed. However, it is supported after 5.5.30. You can use recreate + analyze Before 5.5.30.

Analzye table
Alter table employees2 analyze partition PM1/all;

Check table
Alter table employees2 check partition PM1/all;
 

Show create table employees2; // view Partition Table Definitions
Show table status like 'ployees2' \ G; // when viewing a table, the partition table is shown as "create_options: partitioned"
Select * From information_schema.key_column_usage where table_name = 'ployees2'; // view the index
Select * From information_schema.partitions where table_name = 'ployees2' // view the Partition Table
Explain partitions select * From employees2 where separated <'2017-01-01 'or separated> '2017-01-01'; // check whether the partition is used by the SELECT statement.
 
 
 
Note:
1. Primary Key and unique key must be included in part of the partition key. Otherwise, an error 1503 (hy000) "error will be reported when the primary key and unique index are created.

Mysql> create unique index idx_employees‑job_code on employees1 (job_code );
Error 1503 (hy000): A unique index must include all columns in the table's partitioning Function
Or
Mysql> alter table 'skate'. 'ployees1 'add primary key ('id ');
Error 1503 (hy000): a primary key must include all columns in the table's partitioning Function

2. You can only append a partition to a range partition after the maximum value.
3. the engine of all partitions must be the same
4. Range partition fields: integer, value expression, date column, date function expression (such as year (), to_days (), to_seconds (), unix_timestamp ())

 

 

 

------ End ----