[MYSQL] Partition Table

The attitude towards MYSQL has always been to use basic SQL and simple commands. Recently, a project with a large amount of data has been processed. To improve efficiency, the database bottleneck has been solved, the partition table is selected to improve the query efficiency. So far

The attitude towards MYSQL has always been to use basic SQL and simple commands. Recently, a project with a large amount of data has been processed. To improve efficiency, the database bottleneck has been solved, the partition table is selected to improve the query efficiency. So far

The attitude towards MYSQL has always been to use basic SQL and simple commands. Recently, a project with a large amount of data has been processed. To improve efficiency, the database bottleneck has been solved, the partition table is selected to improve the query efficiency. So far, I would like to share with you.

1. Introduction

This section describes how to partition a table in a mysql database.

2. environment requirements

In version 5.1, partition table support is not installed by default. In later versions, partition table support is installed by default. You can check whether the current database supports Partition Table operations as follows:

Use show variables like '% partition %'. If partition is not supported, the value field is No.

3. Important Concepts

3.1 partition Field

1) if only a single primary key exists and there is no unique key, the partition field must be a primary key field;

2) if a compound primary key exists and there is no unique key, the partition field must be one or more fields in the primary key combination.

3) When both the primary key and the unique key exist, the partition field must contain both the primary key field and the unique key field.

4. Partition Table type

4.1 range Partition

1) syntax display:

# Syntax # At the end of the form creation, add partitions by range (partition field) (# partition name values less than (threshold value 1 ), # partition name values less than (Threshold Value 2 ),#... # partition name values less than (threshold value n ),#)

Example:

Create table test_range (id int auto_increment, description varchar (50), primary key (id) ENGINE = InnoDB auto_increment = 1 default charset = utf8partition by range (id) (partition p1 values less than (6), # id <6 is stored in p1 partition p2 values less than (11) #6 <= id <11 is stored in p2 partition );

View partition information:

Show create table test_range;

Note that the partition table information is added to the displayed table structure.

Data test:

Insert into test_range values (null, "test1"); insert into test_range values (null, "test2"); insert into test_range values (null, "test3 "); insert into test_range values (null, "test4"); insert into test_range values (null, "test5"); insert into test_range values (null, "test6 "); insert into test_range values (null, "test7"); insert into test_range values (null, "test8"); insert into test_range values (null, "test9 "); insert into test_range values (null, "test10 ");

Insert 10 Data Records. Now we can view the query execution process:

From the results, we can find that it is only the query executed in the p1 partition. This reduces the amount of data scanned by the query and improves the query efficiency.

What happens if we insert 11th data records at this time?

Insert into test_range values (null, "test11"); an Error occurs: insert into test_range values (null, "test11") Error Code: 1526. Table has no partition for value 110.015 sec

The reason is very simple, because when we create a form, we only specify the id value partition of 1-10. When we insert a partition with id = 11, no partitions are provided at this time, an error occurs. To solve this problem, modify the table partition method as follows:

Alter table test_range add partition (partition p3 values less than (MAXVALUE); # add a partition, that is, p3 is the region where IDs are stored from 11 to maxValue.

Insert the data with id = 11 and execute query parsing:

It is found that it has been allocated to the p3 partition.

You must also pay special attention to the use of partition by range (partition field), where the partition field can be a form of the partition field, but must be the returned integer, in version 5.5, you can use the partition by range column/columns syntax to specify a field. We will not introduce it here. You can try it yourself.

4.2 list partitions

List partitions can be understood as the set partitioning method, which means to specify a set for partitioning.