Data table partitioning method and usage

Reference website Manual

Check whether partitions are supported:

Show plugins;

If you see a
Partition ACTIVE, this means that the partition is supported

Partition type:
Range: Use the value in the specified column as the interval range
List: Similar to range, the difference is that it matches a specified column value
Hash: is a user-defined function that evaluates the value of a column after the return result determines which partition to save in
Key value: Similar to hash, evaluated based on one or more columns and provided by the MySQL server

Based on range, where the result value in Rang is an integer type
As

CREATE TABLE members (FirstName varchar no null,lastname varchar () not null,username varchar (+) not null,email VAR  CHAR (+), joined DATE not NULL) PARTITION by RANGE (year (joined)) (PARTITION p0 Values less THAN (1960), PARTITION P1 values Less THAN (1970), PARTITION P2 values less THAN (1980), PARTITION P3 values less THAN (1990), PARTITION P4 values less THAN MAXVALUE); #或者: CREATE TABLE employees (ID INT not null,fname VARCHAR (197), lname varchar (+), hired DATE not NULL DEFAULT ' 0-01-01 ', separated DATE not null DEFAULT ' 9999-12-31 ', Job_code int. not null,store_id int not null) PARTITION by RANGE (stor e_id) (PARTITION p0 values less THAN (6), PARTITION P1 values less THAN (one), PARTITION P2 values less THAN (+), PARTITION p 3 VALUES less THAN MAXVALUE);

Based on List: (list result is integer)
Similar to range, the main difference is that the expression value in the list (expression) is a specified set of specific values (that is, the enumeration value).
And the values in the list must be in the partition, and the result in the partition is the integer type:
As

CREATE TABLE Employees (ID INT not null,fname varchar (), lname varchar (+), hired DATE not NULL DEFAULT ' 1970-01-01 ', separ Ated DATE not NULL DEFAULT ' 9999-12-31 ', Job_code int,store_id INT) PARTITION by LIST (store_id) (PARTITION Pnorth VALUES in (3,5,6,9,17), PARTITION peast values in (1,2,10,11,19,20), PARTITION pwest values in (4,12,13,14,18), PARTITION pcentral VALUES in (7,8,15,16));

Based on range columns: (column can be of type string,date,datetime)
Similar to range, but the range column does not accept an expression, only the column name is received
Range columns are compared based on comparison tuples (list values) rather than scalars
The format is as follows:

create TABLE table_namepartitioned by RANGE COLUMNS (column_list) (PARTITION Partition_name values less THAN (value_list) [, partition Partition_name VALUES less THAN (value_list)] [,...]) column_list:column_name[, column_name][, ...] value_list:value[, value][, ...]  --------------------as:create Table RCX (a int,b int,c char (3), d int) partition by range columns (a,b,c) (partition P0 values Less than (4,2,10), partition P1 values less than (10,20, ' mmm '), partition P2 values less than (Maxvalue,maxvalue,maxvalue) ) #as: CREATE TABLE employees_by_lname (id INT not null,fname varchar (197), lname varchar (in), hired DATE not NULL "DEFAULT", "". 0-01-01 ', separated DATE not null DEFAULT ' 9999-12-31 ', Job_code int. not null,store_id int NOT NULL] PARTITION by RANGE Colum NS (lname) (PARTITION p0 values less THAN (' G '), PARTITION P1 values less THAN (' m '), PARTITION P2 values less THAN (' t '), PA Rtition P3 VALUES less THAN (MAXVALUE)); 

Based on List columns: (The type of the value in the expression is: string,date,datetime)
as:

CREATE TABLE customers_1 (first_name varchar), last_name varchar (+), street_1 varchar (+), street_2 varchar (+), city VARCHAR (Renewal DATE) PARTITION by LIST COLUMNS (city) (PARTITION pregion_1 VALUES in (' Oskarshamn ', ' Högsby ', ' Mönsterås '), PARTITION pregion_2 values in (' Vimmerby ', ' hultsfred ', ' Västervik '), PARTITION pregion_3 values in (' Nässjö ' , ' Eksjö ', ' Vetlanda '), PARTITION pregion_4 VALUES in (' Uppvidinge ', ' Alvesta ', ' Växjo ')); ############# #CREATE TABLE Customers_2 (first_name varchar), last_name varchar (+), street_1 varchar (+), street_2 varchar (15), City VARCHAR , renewal DATE) PARTITION by the LIST COLUMNS (renewal) (PARTITION pweek_1 VALUES in (' 2010-02-01 ', ' 2010-02-02 ', ' 2010-02-03 ', ' 2010-02-04 ', ' 2010-02-05 ', ' 2010-02-06 ', ' 2010-02-07 '), PARTITION pweek_2 VALUES in (' 2010-02-08 ', ' 2010-02-09 ', ' 2010-02-10 ', ' 2010-02-11 ', ' 2010-02-12 ', ' 2010-02-13 ', ' 2010-02-14 '), PARTITION pweek_3 VALUES in (' 2010-02-15 ', ' 2010-02-16 ', ' 2010-02-17 ', ' 2010-02-18 ', ' 2010-02-19 ', ' 2010-02-20 ', ' 2010-02-+ '), PARTITION pweek_4 VALUES in (' 2010-02-22 ', ' 2010-02-23 ', ' 2010-02-24 ', ' 2010-02-25 ', ' 2010-02-26 ', ' 2010-02-27 ', ' 2010-02-28 '));

The above can be replaced with range columns:

CREATE TABLE Customers_3 (first_name varchar), last_name varchar (+), street_1 varchar (+), street_2 varchar (+), city VARCHAR (Renewal DATE) PARTITION by RANGE COLUMNS (renewal) (PARTITION pweek_1 VALUES less THAN (' 2010-02-09 '), PARTITION pweek_2 values less THAN (' 2010-02-15 '), PARTITION pweek_3 values less THAN (' 2010-02-22 '), PARTITION pweek_4 VALUES less THAN (' 2010-03-01 '));

Hash partition:
It is mainly used to guarantee the uniform distribution of the data among the predetermined number of partitions, and the hash returns an integer type
Partitioning with modulo method (hash result mod)/partition number)
As

CREATE TABLE Employees (ID INT not null,fname varchar (), lname varchar (+), hired DATE not NULL DEFAULT ' 1970-01-01 ', separ Ated DATE not NULL DEFAULT ' 9999-12-31 ', Job_code int,store_id INT) PARTITION by HASH (store_id) partitions 4;

At this point, the store_id will be divisible by 4, at this time equivalent to partition 0,1,2,3, that is, the remainder,
The default starts with 1, which is the partition 1,2,3,4, with a value of 0 o'clock, in zone 1, and 1 o'clock, in zone 2,...

As

CREATE TABLE Employees (ID INT not null,fname varchar (), lname varchar (+), hired DATE not NULL DEFAULT ' 1970-01-01 ', separ Ated DATE not NULL DEFAULT ' 9999-12-31 ', Job_code int,store_id INT) PARTITION by HASH (year (hired)) partitions 4;

Linear hash (linear hash):
Similar to ordinary hash, the difference is that the Linera hash is used, and the partitioning algorithm is used (the arithmetic of the power of the Linear 2)

CREATE TABLE Employees (ID INT not null,fname varchar (), lname varchar (+), hired DATE not NULL DEFAULT ' 1970-01-01 ', separ Ated DATE not NULL DEFAULT ' 9999-12-31 ', Job_code int,store_id INT) PARTITION by LINEAR HASH (year (hired)) partitions 4;

Key partition:
Similar to hash, but does not allow user-defined expressions, you can not manually specify the partition key,
The default option is to use the primary key/unique key as the partition key, without the primary key/Unique key, to specify the partition key,
Key is used like a hash
As

CREATE TABLE K1 (id INT not NULL PRIMARY key,name VARCHAR) PARTITION by KEY () partitions 2;# #as: CREATE table TM1 (S1 CHA R (+) PRIMARY KEY) PARTITION by KEY (S1) partitions 10;

In fact, like a hash, you can use Linear key,
As

CREATE TABLE tk (col1 INT not null,col2 CHAR (5), col3 DATE) PARTITION by LINEAR KEY (col1) partitions 3;

Sub-partition: Is further divided in each partition.

CREATE TABLE ts (id INT, purchased DATE) PARTITION by RANGE (year (purchased)) Subpartition by HASH (To_days (purchased)) SUB Partitions 2 (PARTITION p0 values less THAN (1990), PARTITION P1 values less THAN (+), PARTITION p2 values less THAN MAXV Alue);

The above implementations have 6 partitions.

The following is the management of the partition, namely the so-called additions and deletions:

Clear all the partitions in the table:

ALTER TABLE name remove partitioning;

Range/list type Partitioning (REORGANIZE PARTITION only for partitions of type Range/list):
Add Partition:
When a table is created, there is no record
As

#range分区类型CREATE TABLE tr (id INT, name VARCHAR (), purchased DATE)         PARTITION by RANGE (year (purchased)) (             parti tion P0 Values less THAN (1990),             PARTITION p1 values less THAN (1995),             PARTITION P2 values less THAN,             P Artition P3 VALUES less THAN (2005)         ); #list分区类型: CREATE TABLE TT (    ID int.,    data INT) PARTITION by LIST (data) (
   partition P0 values in (5, ten, 12),    PARTITION P1 values in (6,, 18));

When a partition is added, a record exists or a table structure is already defined:
When there is no previous partition:

ALTER table name    PARTITION by partitioning method    (definition)

As

CREATE TABLE members (ID INT, fname varchar, lname varchar, DOB DATE) #增加分区 (not previously partitioned): Alter TABLE MEMB Erspartition by Range (year (DOB)) (PARTITION p0 values less THAN (1990), PARTITION P1 values less T HAN (1995), PARTITION P2 values less THAN (+), PARTITION P3 values less THAN (2005)) #已经存在分区时: AL TER table name add partition (partition definition): #range type: #as: CREATE table members (ID INT, fname varchar), lname varchar (2 5), DOB DATE) PARTITION by RANGE (year (DOB)) (PARTITION p0 Values less THAN (1970), PARTITION P1 values less THA N (1980), PARTITION P2 values less THAN (1990)); #如果有分区, then: ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES less TH An (+)); #list类型: #没有分区时: Alter TABLE members PARTITION by list (DOB) (PARTITION P0 VALUES in (5, N), part Ition P1 values in (6, a)) #有分区时, then: ALTER TABLE tt ADD PARTITION (PARTITION p2 values in (7, +)); #当用explain检索是否使用索 #explain Partiti is available when you draw or partitionONS SQL statement; #删除分区, delete record alert table name drop partition partition name; as:alter table TR drop partition P2; #把一个分区分割成多个分区alter table name REO Rganize partition partition name into (partition definition statement, partition definition statement,....) #range类型: #as: ALTER TABLE members REORGANIZE PARTITION p0 to (PARTITION n0 VALUES less THAN (1960), PAR Tition N1 VALUES less THAN (1970)); #合并分区, merge multiple partitions into one partition or multiple partitions: (data not lost) ALTER TABLE table name REORGANIZE PARTITION partition name 1, partition name 2,... into (Partition definition statement, partition definition statement,...) #range类型: ALTER TABLE members REORGANIZE PARTITION s0,s1 to (PARTITION p0 VALUES less THAN (1970)); #或如: Alter TABLE me Mbers REORGANIZE PARTITION p0,p1,p2,p3 into (PARTITION M0 values less THAN (1980), PARTITION M1 values less THAN (2 ); #list type: ALTER TABLE tt ADD PARTITION (PARTITION np VALUES in (4, 8));   ALTER TABLE tt REORGANIZE PARTITION p1,np into (PARTITION P1 values in (6), PARTITION NP values in (4, 8, 12)); #hash/key Partition Type # Create a partition, such as a hash created as 12 partitions: Create TABLE clients (ID INT, fname varchar (), lname varchar (), SIG Ned DATE) PARTITION by Hash (MONTH (signed)) partitions, #当给没有创建过分区的表进行分区时 (HASH): ALTER TABLE members PARTITION by hash (year    (DOB)) Partitions 8; #当从12个修改为8个分区时 (that is, the equivalent of removing 4): ALTER TABLE clients COALESCE PARTITION 4, #当把 12 partitions are split back into 18 (equivalent to 6 partitions): ALTER TABLE clients ADD PARTITION partitions 6;

　　

　　

　　

　　

　　

　

　　

　　

　　

　　

　　

　　

　　

Data table partitioning method and usage