Table partition for MySQL

What is a table partition
In layman's terms, a table partition is a large table, divided into several small tables according to the conditions. mysql5.1 started supporting data table partitioning.
For example, if a user table has more than 6 million records, you can partition the table according to the date of storage, or partition the table according to the location. Of course, you can also partition according to other conditions.

Partition type

· Range Partition: Assigns multiple rows to a partition based on column values that belong to a given contiguous interval.
· List partitioning: Similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value.
· Hash partition: A partition that is selected based on the return value of a user-defined expression that is evaluated using the column values of those rows that will be inserted into the table. This function can contain any expression that is valid in MySQL that produces a non-negative integer value.
· Key partitioning: Similar to partitioning by hash, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

1.RANGE Partitioning

Assigns multiple rows to a partition based on a column value that belongs to a given contiguous interval. These intervals are contiguous and cannot overlap each other, and are defined using the values less than operator. The following is an example.

CREATE TABLE Employees (    ID INT not NULL,    fname varchar (),    lname varchar (),    ' 1970-01-01 ',    ' 9999-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 (+),    partition P3 values less THAN (+));

According to this partitioning scheme, all rows corresponding to employees working in stores 1 to 5 are saved in partition P0, and stores 6 to 10 of employees are saved in P1, and so on. Note that each partition is defined sequentially, from lowest to highest. This is required by the partition by RANGE syntax, which is similar to the "switch ... case" statement in C or Java.
       for a new line that contains data (Widenius, ' Michael ', ' 1998-06-25 ', ' a ', ' NULL, 13 '), It is easy to make sure that it will be inserted into the P2 partition, but what happens if you add a store that is numbered 21st? In this scenario, the server will not know where to save the row because there is no rule to include the store with store_id greater than 20, which will result in an error. To avoid this error, you can use a "catchall" values less than clause in the CREATE TABLE statement that provides all values that are greater than the explicitly specified highest value:

CREATE TABLE Employees (    ID INT not NULL,    fname varchar (),    lname varchar (),    ' 1970-01-01 ',    ' 9999-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 (+),    PARTITION P3 values less THAN MAXVALUE);

The

MAXVALUE represents the largest possible integer value. All rows with a store_id column value greater than or equal to 16 (the highest defined value) are now saved in the partition P3. At some point in the future, when the number of stores has grown to 25, 30, or more, you can add new partitions using the ALTER TABLE statement for stores 21-25, 26-30, and so on.
       in almost the same structure, you can also split the table based on the employee's work code, that is, a continuous interval based on the Job_code column values. For example--suppose a 2-digit work code is used to represent a normal (in-store) worker, three numeric codes represent offices and support staff, and four numeric codes represent management layers, you can create the partition table using the following statement:

CREATE TABLE Employees (    ID INT not NULL,    fname varchar (),    lname varchar (),    ' 1970-01-01 ',    ' 9999-12-31 ',    job_code int not NULL,    store_id int not null)   PARTITION by RANGE (Job_code) (    PARTITION p0 values less THAN (+),    PARTITION p1 values less THAN ( c14>1000),    PARTITION p2 VALUES less THAN (10000));

In this example, all the rows that are related to the store worker are saved in the partition P0, and all the office and support staff are saved in the partition P1, and all management-related rows are saved in the partition P2.
       It is also possible to use an expression in the values less THAN clause. The most notable limitation here is that MySQL must be able to calculate the return value of an expression as part of the comparison of less THAN (<), so the value of an expression cannot be null. For this reason, the hired, separated, Job_code, and store_id columns of the employee table have been defined as non-null (NOT NULL).
       In addition to dividing table data by store number, you can use an expression based on one of the two date (dates) to split the table data. For example, suppose you want to split a table based on the year in which each employee leaves the company, that is, the value of separated. An example of a CREATE TABLE statement that implements this partitioning pattern is as follows:

CREATE TABLE Employees (    ID INT not NULL,    fname varchar (),    lname varchar (),    ' 1970-01-01 ',    ' 9999-12-31 ',    job_code int,    store_id int)   PARTITION by RANGE ( Year (separated)) (    PARTITION p0 values less THAN (1991),    PARTITION p1 values less THAN (1996
   
    ),    PARTITION P3 values less THAN MAXVALUE);
   

In this scenario, records of all employees employed 1991 years ago are kept in the partition P0, records of all employees employed during the period from 1991 to 1995 are kept in partition P1, and records of all employees employed during 1996 through 2000 are kept in the partition P2. Information on all workers employed after 2000 years is kept in P3.
Range partitioning is particularly useful in the following situations:
1), when you need to delete the "old" data on a partition, delete only the partition. If you use the partitioning scheme given in the recent example above, you simply use "ALTER TABLE employees drop PARTITION p0;" To remove all rows corresponding to all employees who have stopped working 1991 years ago. For a table with a large number of rows, this is much more effective than running a delete query such as "Delete from Employees WHERE year (separated) <= 1990;"
2), want to use a column that contains a date or time value, or that contains a value that begins to grow from some other series.
3), frequently run queries that depend directly on the columns used to split the table. For example, when you perform a query such as "SELECT COUNT (*) from Employees WHERE Year (separated) = $ GROUP by store_id;", MySQL can quickly determine that only the partitions P2 need to be scanned, This is because the remaining partitions cannot contain any records that conform to the WHERE clause.
Note: This optimization has not been enabled in the MySQL 5.1 source program, but the work is in progress

List partition

Similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value.
The LIST partition is implemented by using PARTITION by LIST, where "expr" is a column value or an expression that is based on a column value, returns an integer value, and then defines each partition by means of "values in (value_list)". where "Value_list" is a comma-delimited list of integers.
Note: In MySQL 5.1, when using the list partition, it is possible to match only the list of integers.

CREATE TABLE Employees (    ID INT not NULL,    fname varchar (),    lname varchar (),    ' 1970-01-01 ',    ' 9999-12-31 ',    job_code int,    store_id int);

The

assumes that there are 20 video stores, distributed in 4 distribution areas, as shown in the following table:
====================
Region       Store ID number
--------- ---------------------------
North       3, 5, 6, 9,
East       1 , 2, ten, one, 7,
West End       4,,,,, and 8, central    =======
to split the table in the same partition as the rows belonging to the same region store, you can use the following CREATE table statement:

CREATE TABLE Employees (    ID INT not NULL,    fname varchar (),    lname varchar (),    ' 1970-01-01 ',    ' 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 ( /c18>7,8,15,16));

This makes it easier to add or remove employee records from a specified region in a table. For example, assume that all video stores in the west side are sold to other companies. Then all records (lines) related to the job employee in the West End video store can be deleted using the query "ALTER TABLE employees DROP PARTITION pwest;", which has the same effect as delete query DELETE from Employees WHERE store_id in (4,12,13,14,18); "Much more effective than that."
Important: If you attempt to insert a column value (or the return value of a partition expression) without a row in the partition value list, the "INSERT" query fails with an error. For example, assuming that the list partition takes the above scenario, the following query will fail:

INSERT INTO employees VALUES(224, ‘Linus‘, ‘Torvalds‘, ‘2002-05-01‘, ‘2004-10-12‘, 42, 21);

This is because the "store_id" column value 21 cannot be found in the list of values used to define the partition Pnorth, Peast, Pwest, or pcentral. It is important to note that the list partition does not have a definition like "values less THAN MAXVALUE" that contains other values. Any values that will be matched must be found in the list of values.

The list partition can be combined with a range partition to produce a composite sub-partition, which is also possible to combine hash and key partitioning to produce a composite sub-partition.

Table partition for MySQL