MySQL table partition (four types of partition from MySQL table)

First, what is table partitioning
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.

Second, why do you want to partition the table
To improve scalability, manageability, and database efficiency for large tables and tables with various access patterns.

Some of the benefits of partitioning include:

More data can be stored than a single disk or file system partition.
For data that has lost its meaning, it is often possible to delete those data by deleting the partitions associated with those data. Conversely, in some cases, the process of adding new data can be easily implemented by adding a new partition specifically for those new data. Other benefits that are typically related to partitioning include those listed below. These features in the MySQL partition are not yet implemented, but we have high priority in our priority list, and we want to include these features in the 5.1 production version.
Some queries can be greatly optimized, mainly by the fact that the data that satisfies a given where statement can be guaranteed to exist in only one or more partitions, so that no other remaining partitions are found at the time of the lookup. Because partitions can be modified after the partition table has been created, the data can be re-organized when the partitioning scheme is first configured, to improve the efficiency of those common queries.
Queries that involve aggregate functions such as SUM () and count () can be processed easily in parallel. A simple example of such a query such as "Select Salesperson_id, COUNT (orders) as Order_total from sales GROUP by salesperson_id;". By "parallelism," this means that the query can be performed concurrently on each partition, and the final result will only be obtained by totaling all partitions.
Gain greater query throughput by dispersing data queries across multiple disks.
Iii. type of partition
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.

Range partition
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.

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 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 (16),
Partition P3 VALUES less THAN (21)
);
According to this partitioning scheme, all the lines corresponding to employees working in stores 1 to 5 are saved in the partition P0, 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 ', ', ', '-') ', ' It's easy to make sure it will be inserted into the P2 partition, but what happens if you add a store 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, which provides all values that are greater than the explicitly specified highest value:

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 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 (16),
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, and you can create the partition table using the following statement:

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 9999-12-31 ',
Job_code INT not NULL,
store_id INT not NULL
)

PARTITION by RANGE (Job_code) (
PARTITION p0 VALUES less THAN (100),
PARTITION p1 VALUES less THAN (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 splitting 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:

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 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 P2 VALUES Less THAN (2001),
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.

The range partition is particularly useful when you need to delete the "old" data on a partition, just delete the partition. If you use the partitioning scheme given in the recent example above, you simply use the "ALTER TABLE employees DROP PARTITION p0;" To delete all rows that correspond to all employees who have stopped working 1991 years ago. For tables with a large number of rows, this is more than running one such as "DELETE from employees where year (separated) <= 1990;" Such a delete query is much more effective. 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 executing a "select COUNT (*) from employees where year (separated) = a GROUP by store_id;" When such a query is available, MySQL can quickly determine that only the partition P2 needs to be scanned, because the remaining partitions cannot contain any records that conform to the WHERE clause.

Note: This optimization is not yet 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.

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 9999-12-31 ',
Job_code INT,
store_id INT
)；
It is assumed that there are 20 video stores, distributed in 4 distribution areas, as shown in the following table:

====================

Region Store ID Number

North District 3, 5, 6, 9, 17

Eastern 1, 2, 10, 11, 19, 20

West Side 4, 12, 13, 14, 18

Central District 7, 8, 15, 16

====================

To split a table in a way that is stored in the same partition as a row belonging to the same region store, you can use the following "CREATE TABLE" statement:

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated 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)
);
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:

SQL code:
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.

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.

To use a hash partition to split a table, add a PARTITION by HASH clause to the CREATE TABLE statement, where "expr" is an expression that returns an integer. It can just be the name of a column with the field type of MySQL integer. In addition, you will probably need to add a "partitions num" clause later, where NUM is a non-negative integer that represents the number of partitions the table will be partitioned into.

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 9999-12-31 ',
Job_code INT,
store_id INT
)
PARTITION by HASH (store_id)
Partitions 4;
If you do not include a partitions clause, the number of partitions will default to 1. Exception: For NDB Cluster (cluster) tables, the default number of partitions will be the same as the number of cluster data nodes, which may be to consider any max_rows settings to ensure that all rows are properly inserted into the partition.

LINER HASH
MySQL also supports the linear hashing feature, which differs from regular hashing in that the linear hash function uses a linear 2 power (powers-of-two) algorithm, whereas a regular hash uses the modulus of the hash function value. The only syntactic difference between a linear hash partition and a regular hash partition is that the "LINEAR" keyword is added to the "PARTITION by" clause.

SQL code:
CREATE TABLE Employees (
ID INT not NULL,
FName VARCHAR (30),
LName VARCHAR (30),
Hired DATE not NULL DEFAULT ' 1970-01-01 ',
Separated DATE not NULL DEFAULT ' 9999-12-31 ',
Job_code INT,
store_id INT
)
PARTITION by LINEAR HASH (year (hired))
Partitions 4;
Suppose an expression expr, when using the linear hash function, records the partition to be saved to partition n in num partitions, where n is obtained according to the following algorithm: 1. To find the next power greater than NUM, 2, we call this value V, which can be obtained by the following formula: 2. V = POWER (2, CEILING (LOG (2, num))) (for example, assume that NUM is 13. Then log (2,13) is 3.7004397181411. CEILING (3.7004397181411) is 4, then V = POWER (2,4), which is equal to 16). 3. Set N = F (column_list) & (v–1). 4.    when n >= num:   Set V = Ceil (V/2)   Set n = N & (v–1) For example, suppose the table T1, using a linear hash partition and having 4 partitions, is through the The statement created by the polygon: Create TABLE T1 (col1 INT, col2 CHAR (5), col3 DATE) PARTITION by LINEAR HASH (year (COL3)) partitions 6; Now suppose you want to insert two rows of records into table T1, one record col3 column value is ' 2003-04-14′, another record col3 column value is ' 1998-10-19′. The partition to which the first record will be saved is determined as follows: V = POWER (2, CEILING (2,7)) = 8 N = year (' 2003-04-14′) & (8–1)     = 2003 & 7    = 3 (3 >= 6 false): The record will be saved to the # # partition) The second record is to be saved to the partition ordinal calculated as follows: V = 8 N = year (' 1998-10-19′) & (8-1 )    = 1998 & 7   = 6 (6 >= 4 is True (TRUE): Additional steps are required) N = 6 & CEILING (5/2)    = 6 &am P 3   = 2   (2 >= 4 is False (FALSE): Records will be saved to the # # partition the advantage of the linear hash partitioning is that adding, deleting, merging, and splitting partitions will be faster and facilitates processing of tables with extremely large amounts of data (1000 ringgit). Its disadvantage is that the distribution of data between partitions is unlikely to be evenly balanced compared to the distribution of data using regular hash partitioning.

Ksy Partition
Like 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.

SQL code:
CREATE TABLE tk (
Col1 INT not NULL,
Col2 CHAR (5),
Col3 DATE
)
PARTITION by LINEAR KEY (col1)
Partitions 3;
Using the keyword linear in the key partition and using it in the hash partition has the same effect, the partition number is obtained by the Power of the 2 (powers-of-two) algorithm, not by the modulus algorithm.

MySQL table partition (four types of partition from MySQL table)