MySQL Partition summary

Introduction: Partitioning refers to a database that breaks down a table into smaller, easier-to-manage parts according to certain rules. Partitions are completely transparent to the application and do not affect the business logic of the application.

Advantages of MySQL Partitioning:

1, compared with a single disk or file system partition, can store more data;

2, optimize the query. When you include a partition condition in the WHERE clause, you can scan only one or more of the necessary partitions to improve the efficiency of the query, and when querying for aggregate functions such as SUM () and COUNT (), you can easily parallelize on each partition, and ultimately just summarize the results from all partitions.

3. For data that has expired or does not need to be saved, you can quickly delete the data by deleting the partitions associated with the data.

4. Distribute data queries across multiple disks for greater query throughput.

One: Overview

MySQL supports the creation of partitioned tables using most of the storage engines (e.g. MyISAM, InnoDB, memory, etc.) and does not support the use of merge and CSV. MySQL partition types mainly include: Range partition, list partition, hash partition, key partition, either the type of MySQL partition, or the partition table does not have a primary key/unique key, or the partition table's primary key/Unique key must contain the partition key, that is, cannot use the primary key/ A field partition other than the Unique key field. Such as:

Mysql> CREATE TABLE emp (    -     ID int not NULL,     ename varchar (),     hired date Not null default ' 1970-01-01 ',    -     separated date not null default ' 9999-12-31 ',     job varchar (30 ) not NULL,     store_id int not NULL,     primary key (ID)--    partition by Range (store_id) (     partition p0 values less than),     partition P1 values less than (20),     -partition P2 values less than (+)    ); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table ' s partitioning function

When the primary key constraint is removed, creating the table succeeds:

Mysql> CREATE TABLE EMP (-    ID int not NULL,    ename varchar (),-    hired date NOT null default ' 1970-01-01 ',-    separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,    store _id int NOT null-    partition by Range (store_id) (-    partition p0 values less than),    -& Gt Partition P1 values less than (a),    partition P2 values less than (())    ; Query OK, 0 rows affected (0.02 sec)

The name of the partition follows the principle of the MySQL identifier. The name of the partition is not case-sensitive, and if the partition name is Mypart and Mypart, it will be the same partition as MySQL.

Two: Range partition

A table that is partitioned by range uses a range of values to partition data, to be contiguous and not to overlap each other, and to use the values less than operator for partitioning definitions.

Mysql> CREATE TABLE EMP (-    ID int not NULL,    ename varchar (),-    hired date NOT null default ' 1970-01-01 ',-    separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,    store _id int NOT null-    partition by Range (store_id) (-    partition p0 values less than),    -& Gt Partition P1 values less than (a),    partition P2 values less than (())    ; Query OK, 0 rows affected (0.02 sec)

If you increase a row with a store ID greater than or equal to 30, the partition range is exceeded and an error occurs.

mysql> INSERT INTO EMP (id,ename,hired,job,store_id) VALUES (' 7934 ', ' MILLER ', ' 1982-01-23 ', ' clerk ', 50); ERROR 1526 (HY000): Table have no partition for value 50

You can use the values less than MaxValue to set the partition, which is stored in the partition if it exceeds the explicitly specified partition value, such as:

Mysql> ALTER TABLE EMP ADD partition (partition P3 values less than maxvalue); Query OK, 0 rows affected (0.25 sec) records:0  duplicates:0  warnings:0mysql> INSERT INTO EMP (id,ename,hired,j ob,store_id) VALUES (' 7934 ', ' MILLER ', ' 1982-01-23 ', ' clerk ', 50); Query OK, 1 row affected (0.05 sec)

MySQL supports the use of the expression partition by range (year (separated)) in the values less than sentence, which can be tested on its own. Similar functions have to_days (), To_seconds (), and the Date field can be used as the partition key after version 5.5.

The range partition is especially used in two situations:

1. When you need to delete outdated data, simply ALTER table emp drop PARTITION p0 To delete the data in the P0 partition, and for tables with millions records, it is much more efficient to delete a partition than to run a DELETE statement.

2. Frequently running queries that contain partitions, MySQL can quickly determine that only one or some of the partitions need to be scanned, because other partitions cannot contain any records that have the WHERE clause.

Three: List partition

A list partition is a set of discrete values that tells the database which partition a particular value belongs to, and the list partition is similar to a Range partition in many ways, except that the list partition is a worthwhile collection of values belonging to an enumerated list, and that the range partition belongs to a contiguous interval worth collecting.

Mysql> CREATE TABLE expenses (    expense_date date not NULL,    category int,    amount Decimal (1 0,3)    partition by list (category)    , partition P0 values in (3, 5),    partition P1 values in (1 ,    partition P2 values in (4, 9), and    partition P3 values in (2), and    partition P4 values in (6)    ); Query OK, 0 rows affected (0.05 sec)

If the inserted column value does not contain a partition worth the list, the insert operation fails with an error. Note: The List partition does not have a definition such as the values less than MaxValue, which contains other values. Any values that will be matched must be in the list of values.

Four: Hash partition

Hash partitioning is primarily used to disperse hot-spot readings, ensuring that data is distributed as evenly as possible in predetermined numbers of partitions. MySQL supports two kinds of hash partitions: regular hash partition, linear hash partition (Linear hash partition). The regular hash partition uses a modulo algorithm, and the linear hash partition uses a linear 2-power arithmetic.

1. Regular Hash partition

Use the PARTITION by HASH (expr) partitions num sentence to define the partition type, partition key, and number of partitions, where expr is a column value or an expression that is returned based on a column value. Num is a non-negative integer that represents the number of partitions and defaults to 1.

Mysql> CREATE TABLE Emp_hash (-    ID int not NULL,    ename varchar (),-    hired date NOT null Def Ault ' 1970-01-01 ',    separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,    s tore_id int NOT null    ),    partition by hash (store_id) partitions 4; Query OK, 0 rows affected (0.03 sec)

Assuming the partition number to save the record is N, then n=mod (expr, num), such as the Emp_hash table, has 4 partitions, inserting a record with a store_id column value of 234 into the table:

mysql> INSERT INTO Emp_hash values (1, ' Tom ', ' 2010-10-10 ', ' 9999-12-31 ', ' clerk ', 234); Query OK, 1 row affected (0.01 sec)

MOD (234, 4) =2 the partition that holds this record should be P2, starting with p0, saving to P0, and so on.

mysql> Explain partitions select * from Emp_hash where store_id =234\g*************************** 1. Row ***************************           id:1  select_type:simple        table:emp_hash   partitions:p2         type:  Allpossible_keys:null          key:null      key_len:null          ref:null         rows:2        extra:using where1 row in set (0.05 Sec

It is not recommended to use hash expressions involving multiple columns, and complex expressions can cause performance problems. Conventional hashing requires high cost in partition management and is not suitable for needs of flexible zoning.

2, linear hash partition (Linear hash)

The only grammatical difference between a linear hash partition and a regular hash partition is the addition of the keyword "Linear" in the partition by phrase, such as:

Mysql> CREATE TABLE Emp_linear (-    ID int not NULL,    ename varchar (+),    hired date not null D Efault ' 1970-01-01 ',    separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,    -> ; store_id int NOT null    ),    partition by linear hash (store_id) partitions 4; Query OK, 0 rows affected (0.03 sec)

The advantage of linear hash partitioning is that MySQL can be processed more quickly when partitioning is maintained (adding, deleting, merging, splitting partitions), and the disadvantage is that when comparing the regular hash partition (modulo), the distribution of the data between each partition of the linear hash is not very balanced.

Five: Key partition

The key partition is similar to a hash partition, but the key partition does not allow user-defined expressions, requires the use of the hash function provided by the MySQL server, and the hash partition supports only integer partitioning, while the key partition supports the use of other types of columns outside the Blob or Text type as Partition key

Mysql> CREATE TABLE Emp_key (-    ID int not NULL,    ename varchar (),-    hired date not null DEFA Ult ' 1970-01-01 ',-    separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,--    St ore_id int NOT null    ),    partition by key (job) partitions 4; Query OK, 0 rows affected (0.05 sec)

When creating a key partition table, You can not specify a partition key, the default is to first select using primary key as the partition key

Mysql> CREATE TABLE Emp_key1 (-    ID int not NULL,    ename varchar (+),    hired date not null D Efault ' 1970-01-01 ',    -separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,    - > store_id int NOT null    ),    partition by key () partitions 4; Query OK, 0 rows affected (0.03 sec)

In the absence of a primary key, the non-null unique key is selected as the partition key, the unique key of the partition key must be non-null, if not non-null will be an error:

Mysql> CREATE TABLE Emp_key2 (-    ID int not NULL,    ename varchar (),-    hired date NOT null Def Ault ' 1970-01-01 ',    separated date not null default ' 9999-12-31 ',    job varchar (+) NOT NULL,    s tore_id int not null,-> unique key (ID),    partition by key () partitions 4; Query OK, 0 rows affected (0.03 sec)

Similar to the Hash partition, the key partition in the use of the keyword Linear has the same effect, Linear key partition, the partition number is obtained by the Power algorithm 2, not by the modulo obtained.

Attached: null value handling for MySQL partition

1, MySQL partition does not prohibit the use of NULL on the partition key value

2, the Range partition, the null value will be treated as the minimum value

3, the list partition, the null value must appear in the enumeration list, otherwise it is not accepted

4. In the Hash/key partition, null values are treated as 0 values

5, in order to avoid error when dealing with null values, it is recommended to bypass MySQL default handling of Null values by setting field non-null and default values

MySQL Partition summary