"MySQL" MySQL sub-table and table partition detailed

Why divide tables and partitions?

We often encounter large tables in daily development, so-called large tables are those that store millions or even tens records. Such tables are too large to cause the database to take too long to query and insert, with poor performance and worse performance if it involves federated queries. The purpose of partition table and table is to reduce the burden of database, improve the efficiency of database, usually point is to improve the efficiency of adding and deleting tables.

What is a sub-table?

The Sub-table is a large table according to a certain rule into a number of independent storage space of the entity table, we can be called a child table, each table corresponds to three files, myd data file,. Myi index file,. frm table structure file. These sub-tables can be distributed on the same disk or on different machines. When the app reads and writes, it gets the corresponding sub-table name according to the predefined rules and then goes to manipulate it.

What is a partition?

partitions and sub-tables are similar, and are broken down by rules. The difference is that the table divides the large table into several separate entity tables, while partitioning divides the data into multiple locations, which can be the same disk or on different machines. After partitioning, the surface is still a table, but the data is hashed to multiple locations. When the app reads or writes a large table name, DB automatically organizes the data into the partition.

What is the connection between MySQL sub-tables and partitions?
1. All can improve MySQL's sex high, in the high concurrency state has a good performance.
2. sub-table and partition are not contradictory, can cooperate with each other, for those large traffic, and table data more tables, we can take the table and partition combination of the way (if the merge table, not with the partition, you can use other sub-table test), access is not large, but the table data a lot of tables, We can take the partitioning method and so on.
3. The Sub-table technology is more troublesome, need to manually create a child table, the app server read and write when the child table name needs to be computed. It is better to use merge, but also to create the union relationship between the child table and the Configuration child table.
4. Table partitioning is easy to operate and does not require creating child tables, relative to the table.

Several ways to divide the table:

1. mysql Cluster

It is not a sub-table, but it plays the same role as the sub-table. The cluster can share the number of operations on the database and divide the tasks into multiple databases. The cluster can read and write separation, reduce read and write pressure. thereby improving database performance.

2. Custom Rule Sub-table

Large tables can be decomposed into multiple sub-tables according to the rules of the business. Typically there are several types, and you can define the rules yourself.

Table rules are described in detail in the partitioning module, as are the partitioning rules.

Below is a brief introduction to how to divide the table (by year table).

Suppose the table structure has 4 fields: self-increment ID, name, deposit amount, deposit date

Divide the deposit date as a regular table and create several tables separately

2011: account_2011

2012: account_2012

......

2015: account_2015

When the app reads and writes, it finds the corresponding table name according to the date and needs to be determined manually.

var gettablename = function () {    var data = {        name: ' Tom ',        money:2800.00,        date: ' 201410013059 '    };
   var tablename = ' Account_ ';    var year = parseint (data.date.substring (0, 4));    if (Year <) {        TableName + +;//account_2011    } else if (year <) {        TableName + +;//A ccount_2012    } else if (year <) {        TableName + =,//account_2013    } else if (year <) {
   tablename + = 2014; account_2014    } else {        tablename + +;//account_2015    }    return tablename;}

3, using the merge storage engine to achieve the sub-table

The merge sub-table, divided into the main table and the child table, the main table resembles a shell, logically encapsulates the child table, in fact, the data are stored in the child table.

We can insert and query data through the main table, and if we clear the rules of the table, we can also manipulate the sub-table directly.

Child Table 2011

CREATE TABLE ' account_2011 ' (' id '  int (one) not NULL auto_increment, ' name '  varchar () CHARACTER SET UTF8 COLLATE u TF8_GENERAL_CI null DEFAULT null, ' money ' float is not null, ' tradedate ' datetime NOT NULL PRIMARY KEY (' id ')) engine=myisam DEFAULT CHARACTER Set=utf8 collate=utf8_general_ciauto_increment=2checksum=0row_format=dynamicdelay_key_write=0;

Child Table 2012

CREATE TABLE ' account_2012 ' (' id '  int (one) not NULL auto_increment, ' name '  varchar () CHARACTER SET UTF8 COLLATE u TF8_GENERAL_CI null DEFAULT null, ' money ' float is not null, ' tradedate ' datetime NOT NULL PRIMARY KEY (' id ')) engine=myisam DEFAULT CHARACTER Set=utf8 collate=utf8_general_ciauto_increment=2checksum=0row_format=dynamicdelay_key_write=0;

Master table, all year

CREATE TABLE ' Account_all ' (' id '  int (one) not NULL auto_increment, ' name '  varchar () CHARACTER SET UTF8 COLLATE ut F8_GENERAL_CI null DEFAULT null, ' money ' float is not null, ' tradedate ' datetime NOT NULL PRIMARY KEY (' id ')) engine=mrg_myi Samdefault CHARACTER Set=utf8 collate=utf8_general_ciunion= (' account_2011 ', ' account_2012 ') insert_method=lastrow_ format=dynamic;

When creating the main table, there is a insert_method, indicating the insertion method, the value can be: 0 is not allowed to insert, first inserted into the Union, the last inserted into the final table in the Union.

When querying through a primary table, it is equivalent to querying all the child tables together. This does not reflect the advantages of the table, it is recommended to query the child table.

Several ways of partitioning

Range:

CREATE table range (id int (one), Money Int (one) unsigned not NULL, date datetime) partition by range (date) ( Partition p2007 values less than (+), partition p2008 values less than ($), partition p2009 values less than (20 Partition p2010 values less than maxvalue);

List:

CREATE table list (a int (one), B Int (one)) (Partition by list (b) partition p0 values in (1,3,5,7,9), partition P1 Values in (2,4,6,8,0));

Hash:

CREATE TABLE hash (a int (one), b datetime) partition by hash (year (b) partitions 4;

Key:

CREATE TABLE T_key (a int (one), b datetime) partition by key (b) partitions 4;

Partition Management

New Partition

ALTER TABLE sale_dataadd PARTITION (PARTITION p201010 VALUES less THAN (201011));

Delete Partition
--When a partition is deleted, all the data in that partition is also deleted.
ALTER TABLE sale_data DROP PARTITION p201010;

Merging of partitions
The following SQL, merges p201001-p201009 into 3 partitions p2010q1-p2010q3

ALTER TABLE sale_datareorganize PARTITION p201001,p201002,p201003,p201004,p201005,p201006,p201007,p201008,p201009  Into (PARTITION p2010q1 values less THAN (201004), PARTITION p2010q2 values less THAN (201007), PARTITION p2010q3 values Less THAN (201010));

Transferred from: http://www.2cto.com/database/201503/380348.html

"MySQL" MySQL sub-table and table partition detailed