MySQL partition range partition tutorial

MySQL partition range partition tutorial

The tables in the range partition are partitioned in one of the following ways, and each partition contains the rows of those partition expressions whose values are in a given contiguous interval. These intervals are contiguous and cannot overlap, and are defined using values less than operators. In the following few examples, suppose you create a table that holds the staff records of 20 video stores, numbering from 1 to 20 for 20 video stores.

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

);

Depending on your needs, the table can be partitioned in a number of ways. One way is to use the store_id column. For example, you might decide to split the table into 4 intervals by adding a partition by range clause, as follows:

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 rows of employees working in stores 1 to 5 are saved in the partition P0, and the store 6 to 10 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 row containing data ("Michael", "Widenius", "1998-06-25", "NULL, 13"), it is easy to determine that it will be inserted into the P2 partition, but what happens if a store numbered 21st is added? Under such a scheme , because there is no rule to include a store with a store_id greater than 20, the server will not know where to store the row, resulting in an error. To avoid this error, you can use a "catchall" value less than clause in the CREATE TABLE statement to provide all values that are greater than the specified highest value:

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 value defined) 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 use the ALTER TABLE statement to add new partitions for stores 21-25, 26-30, and so on.

In almost the same structure, you can also split a table based on the employee's work code, that is, a contiguous interval based on the Job_code column value. For example--suppose a 2-digit work code is used to represent a common (shop) worker, three digital codes represent offices and support personnel, and four digital codes represent management, and you can create the partition table using the following statement:

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 rows related to the shop worker are saved in the partition P0, and all the lines related to the office and support staff are saved in the partition P1, and all the 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 evaluate the return value of an expression as part of the less THAN (<) comparison; therefore, 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 the table data according to the store number, you can divide the table data using an expression based on one of the two date (dates). For example, suppose you want to split a table based on the year in which each employee left 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 (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 P0, and records of all employees employed from 1991 to 1995 are kept in the partition P1, and records of all employees employed during 1996 to 2000 are kept in the partition P2. Information on all workers employed after 2000 years is kept in P3.

Range partitions are particularly useful in the following situations:

· When you need to delete the "old" data. If you use the partitioning scheme given in the last example above, you simply use "ALTER TABLE employees DROP PARTITION p0". To delete all the lines that correspond to all employees who have stopped working 1991 years ago. For tables with a large number of rows, this is better than running one such as "DELETE from employees where year (separated) <= 1990;" Such a delete query is much more effective.

· You want to use a column that contains a date or time value, or a value that contains values that start with some other series.

· Frequently run queries that depend directly on the columns used to partition the table. For example, when executing one such as "SELECT COUNT (*) from employees where year (separated) = + GROUP by store_id;" In such a query, 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.