Partition concept in MySQL database

Range partition:It is allocated based on the column values that belong to a given continuous interval ..


Create Table employees (
Id int not null,
Fname varchar (30 ),
Lname varchar (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-12-31 ',
Job_code int not null,
Store_id int not null
)
Partition by range (store_id )(
Partition P0 values less than (6 ),
Partition P1 values less than (11 ),
Partition P2 values less than (16 ),
Partition P3 values less than maxvalue
);
List partition:Similar to range partitions, the main difference between them is that the definition and selection of each partition in list partitions belong to a set based on the values of a column, the range partition is a set of values in a continuous interval.

Create Table employees (
Id int not null,
Fname varchar (30 ),
Lname varchar (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-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 ),
Partition pwest values in (4, 12, 13, 14, 18 ),
Partition pcentral values in (7, 8, 15, 16)
);
If you try to insert a column value (or the return value of the partition expression) not in a row in the partition Value List, the "insert" query will fail and report an error.
Hash partition:Partitions are selected based on the return values of user-defined expressions. The expressions are calculated using the column values of these rows to be inserted into the table. This function can contain any expressions that are valid in MySQL and generate non-negative integer values.
Hash partitions are mainly used to ensure that data is evenly distributed in pre-defined partitions. In range and list partitions, you must specify the partition in which a given column value or column value set should be stored. In hash partitions, MySQL automatically completes theseWorkAll you need to do is specify a column value or expression based on the column value to be hashed, and specify the number of partitions to be split into in the partitioned table.
To use Hash partitions to split a table, add a "partition by hash (expr)" clause to the create table statement. "expr" is an expression that returns an integer. It can only be the name of a column whose field type is MySQL integer. In addition, you may need to add a "partitions num" clause later. Num is a non-negative integer, which indicates the number of partitions to be split in the table.
Create Table employees (
Id int not null,
Fname varchar (30 ),
Lname varchar (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-12-31 ',
Job_code int,
Store_id int
)
Partition by hash (Year (hired ))
Partitions 4;
MySQL also supports linear hash, which is different from conventional hash in that the linear hash function uses a linear power (powers-of-two) algorithm, the general hash uses the modulus of the hash function value.
The only difference in syntax between a linear hash partition and a conventional hash partition is that the "linear" keyword is added to the "partition by" clause, as shown below:
Create Table employees (
Id int not null,
Fname varchar (30 ),
Lname varchar (30 ),
Hired date not null default '2017-01-01 ',
Separated date not null default '2017-12-31 ',
Job_code int,
Store_id int
)
Partition by linear Hash (Year (hired ))
Partitions 4;
The advantage of linear hash partitioning is that adding, deleting, merging, and splitting partitions will become faster, facilitating processing tables containing extremely large amounts of (1000 GB) data. Its disadvantage is that the distribution of data in each shard is unlikely to be balanced compared with the data distribution obtained by conventional hash partitions.

Key partition:Partitioning by key is similar to partitioning by hash. Except for the User-Defined expression used by hash partition, the hash function of key partition is provided by the MySQL server. MySQL cluster uses the MD5 () function to implement key partitioning;
For tables using other storage engines, the server uses its own internal hash functions based on the same algorithm as password ().
It is also possible to split a table using a linear key. The following is a simple example:
Create Table TK (
Col1 int not null,
Col2 char (5 ),
Col3 date
)
Partition by linear key (col1)
Partitions 3;
Subpartition:Sub-partitions are the re-division of each partition in the partition table.

Create Table TS (ID int, purchased date)
Partition by range (Year (purchased ))
Subpartition by hash (to_days (purchased ))
(
Partition P0 values less than (1990)
(
Subpartition S0,
Subpartition S1
),
Partition P1 values less than (2000)
(
Subpartition S2,
Subpartition S3
),
Partition P2 values less than maxvalue
(
Subpartition S4,
Subpartition S5
)
);
Subpartitions can be used to allocate data and indexes between multiple disks for a particularly large table. Assume that there are 6 disks, including/disk0,/disk1, And/disk2. Consider the following example:
Create Table TS (ID int, purchased date)
Partition by range (Year (purchased ))
Subpartition by hash (to_days (purchased ))
(
Partition P0 values less than (1990)
(
Subpartition S0
Data directory = '/disk0/data'
Index directory = '/disk0/idx ',
Subpartition S1
Data directory = '/disk1/data'
Index directory = '/disk1/idx'
),
Partition P1 values less than (2000)
(
Subpartition S0
Data directory = '/disk2/data'
Index directory = '/disk2/idx ',
Subpartition S1
Data directory = '/disk3/data'
Index directory = '/disk3/idx'
),
Partition P2 values less than maxvalue
(
Subpartition S0
Data directory = '/disk4/data'
Index directory = '/disk4/idx ',
Subpartition S1
Data directory = '/disk5/data'
Index directory = '/disk5/idx'
)
);Partition Management

You can delete a partition from a table partitioned by range or list by using the alter table command with a drop partition clause.

Alter table tr drop partition P2;

The number of rows deleted from the table caused by the "alter table... drop partition" statement is not reported by the server.

If you want to change the table partition without losing data, use the "alter table... reorganize partition" statement.
A reorganize partition statement can also be used to merge adjacent partitions. You can use the following statement to restore a member table to its previous partition:
Alter table members reorganize partition S0, S1 (
Partition P0 values less than (1970)
);
Use "reorganize partition" to split or merge partitions without data loss. In the execution of the preceding statement, MySQL moves all data stored in the S0 and S1 partitions to The P0 partitions.

The basic syntax of "reorganize Partition" is:
Alter table tbl_name reorganize partition partition_list into (partition_definitions );

You cannot delete partitions from hash or key partitions in the same way as deleting partitions from tables partitioned by range or list. However, you can use the "alter table... coalesce partition" command to merge hash or key partitions.

Coalesce cannot be used to increase the number of partitions. To increase the number of partitions in the customer table from 12 to 18, use "alter table... add partition" as follows:
Alter table clients add partition partitions 18;

MySQLPartitions are supported in analyticdb 5.1. Although not production-ready is still supported, it is also a new feature worth looking forward to. The MySQL manual on partitions has been described in detail, the following is an excerpt from the manual:
Partition advantages:

1) more data can be stored than a single disk or a file system partition.

2) for data that has lost its meaning, you can easily Delete the data by deleting the partitions related to the data. On the contrary, in some cases, the process of adding new data can be easily implemented by adding a new partition for those new data.

3) Some queries can be greatly optimized. This is mainly because data that satisfies a given where statement can be saved in only one or more partitions, in this way, you do not need to find other remaining partitions. Because partitions can be modified after the partition table is created, data can be re-organized when the partition scheme is not configured for the first time to improve the efficiency of common queries.

4) the query involving Aggregate functions such as sum () and count () can be easily processed in parallel. A simple example of this query is "select salesperson_id, count (orders) as order_total from sales group by salesperson_id ;". Through "Parallel", this means that the query can be performed on each partition at the same time, and the final result is only obtained through the total of all partitions.
5) distribute data queries across multiple disks to increase query throughput.