"MYSQL" Partition table

The attitude to MySQL has always been the basic SQL and simple command on the line, recently processed a large data volume of the project, in order to improve efficiency, in the database bottleneck, the use of partitioned tables to improve query efficiency. Here to share with you.

1. Introduction

This article briefly describes how the MySQL database partition table.

2. Environmental requirements

Installation is not installed by default in version 5.1, and installation partition table support is generally selected by default in later versions. You can see whether the current database supports partitioned table operations in the following ways:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/54/2A/wKioL1R6ym7A0CplAABfWiOHn-I850.jpg "title=" Partitions.png "alt=" Wkiol1r6ym7a0cplaabfwiohn-i850.jpg "/>

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

3. Important Concept Description

3.1 Partition fields

1) When there is only a single primary key, there is no unique key, then the partition field must be the primary key field;

2) When there is a composite primary key, there is no unique key, then the partition field must be a part of the primary key combination, one or more fields.

3) When both the primary key and the unique key exist, the partition field must include 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 creating a form, add partitions by range (# partition partition name values less than (threshold 1), # partition partition name values less than (threshold 2) , # ... # partition partition name values less than (threshold N), #)

Examples show:

CREATE TABLE Test_range (ID int auto_increment,description varchar (), primary key (ID)) Engine=innodb auto_increment=1 Default charset=utf8partition by range (ID) (partition P1 values less than (6), #id <6 stored in P1 partition partition P2 values less than (one) #6 <= ID < 11 stored in P2 partition);

To view the partitioning situation:

Show CREATE TABLE Test_range;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/54/2A/wKioL1R6z4qC137DAADITZNS8H8149.jpg "title=" P1png.png "alt=" Wkiol1r6z4qc137daaditzns8h8149.jpg "/>

Notice that the table structure that is displayed adds the information for the partitioned table.

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, and we'll look at the query execution process:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/54/2A/wKioL1R60LDBET1bAADRpczcpSo931.jpg "title=" p2.png "alt=" Wkiol1r60ldbet1baadrpczcpso931.jpg "/>

From the results can be found, it is only in the P1 partition execution of the query, then reduce the amount of data query scanning, thus improving the efficiency of the query.

What happens if we insert the 11th piece of data at this point?

INSERT into test_range values (null, "test11"); Will be wrong: INSERT into test_range values (null, "test11") error code:1526. Table have no partition for value 110.015 sec

The reason is simple, because when we create a form, we only specify 1-10 of the ID value of the partition, when inserting id=11 partition, there is no partition provided at this time, then throw an error, if you solve such a problem, take the following way to modify the table partition method:

ALTER TABLE Test_range ADD partition (partition P3 values less than (MAXVALUE)); # Add a partition, that is, P3 is the storage area of the ID from 11 to MAXVALUE

Insert the id=11 data at this point and perform query parsing:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/54/2B/wKiom1R60nnwSlUUAADL0geAo20618.jpg "title=" p3.png "alt=" wkiom1r60nnwsluuaadl0geao20618.jpg "/> found that it has been assigned to the P3 partition.

It is also important to note that when using the partition by range (partition field), where the partition field can be a form of a partitioned field, but must be the returned integer, in version 5.5, you can use the partition by range Column/columns syntax. Specifies a field. No introduction is made here. You can try it yourself.

4.2 List partition

A list partition can be understood as a collection partition, meaning that a collection is assigned to partition.

Syntax display:

Partition by list (partition field expression) (partition partition name values in (value1, value2,..., Valuen) #分区集合);

Examples show:

CREATE TABLE test_list (ID int auto_increment,description varchar (), primary key (ID)) Engine=innodb auto_increment=1  Default charset=utf8partition by list (ID) (partition P1 values in (1, 3, 5, 7, 9), #id =1,3,5,7,9 assigned to P1 area partition P2 values in (2, 4, 6, 8, ten) #id =2,4,6,8,10 assigned to P2 area); #可以如4.1 Use the show create table Test_list to view the table creation structure.

Data test: Use 4.1 data to test SQL and insert 10 data.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/54/2A/wKioL1R61Y-RjP7aAADWGviW2FI351.jpg "title=" p4.png "alt=" wkiol1r61y-rjp7aaadwgviw2fi351.jpg "/> can find that its query is only the P1 area. If you need to add a partition, you can use the Add partition used in 4.1 to add the partition.

4.3 Hash partition

Use the hash function to get the modulo and assign it to a different partition. The partition expression must return an integer.

Syntax display:

Partition by hash (partition expression) partitions number of tables (modulo).

Examples show:

CREATE TABLE Test_hash (ID int auto_increment,description varchar (), primary key (ID)) Engine=innodb auto_increment=1 Default charset=utf8partition by hash (ID) partitions 3; #以id分区, assigned to 3 tables

Data test: Insert 4.1 Similar 10 data

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/54/2A/wKioL1R615mA8FC8AADLcG2rGPs147.jpg "title=" p5.png "alt=" Wkiol1r615ma8fc8aadlcg2rgps147.jpg "/>

You can also try modifying the ID value to see its assigned partition. Hash partition also has a kind of called linear hash linear partition, here does not introduce,

4.4 Key Partition

In this development, I chose the key partition, because it is for one or more fields as a partition field, does not require a positive integer, its internal call is its own hash function, the calculation of the hash integer value, and then take the model table.

Syntax display:

Partition by key (partition field combination) partitions number of tables (modulo).

The operation is consistent with the hash partition, so there is no cumbersome display.

5. Additional Extensions

5.1 In the actual development, often occurs when the table is already on-line use, then you must dynamically add the partition type.

ALTER TABLE name partition by Hash/key (partition field expression) [Partitions table Count] #如果不加partitions那么默认为1. ALTER TABLE name partition by range/list (partition field expression) (specific partition setting).

5.2 When you find that the previous partition needs to be added a new partition, take the following method:

List/range:alter table name add partition (partition partition name [values in|values less than] [collection | value]); Hash/key:alter table Table Name add partition partitions table number;

Example: Modify the number of partitions above Test_hash

ALTER TABLE Test_hash add partition partitions 6;

5.3 Delete a partition/delete all partitions

# Delete a Partition list/range:alter table table name drop partition partition name 1, partition Name 2, ...   ; #例如: ALTER TABLE test_list drop partition p1; Hash/key: The above syntax does not establish # Delete entire partition ALTER TABLE Test_hash remove partitioning;

There are also some new features, such as merge partitions, and 5.5, List/range increase column,columns support. This article does not do too much elaboration.

This article is from the "Java Program Ghost" blog, please be sure to keep this source http://793404905.blog.51cto.com/6179428/1584785

"MYSQL" Partition table