MySQL sub-table and table partitioning detailed

Transfer from https://www.2cto.com/database/201503/380348.html

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.

?

12345

Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。Composite（复合模式） –以上模式的组合使用

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.

?

123456789101112131415161718192021

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< 2012) {        tablename += 2011; // account_2011    } elseif (year< 2013) {        tablename += 2012; // account_2012    } elseif (year< 2014) {        tablename += 2013; // account_2013    } elseif (year< 2015) {        tablename += 2014; // account_2014    } else{        tablename += 2015; // account_2015    }    returntablename;}

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

?

1234567891011121314

CREATETABLE`account_2011` (`id`  int(11) NOTNULLAUTO_INCREMENT ,`name`  varchar(50) CHARACTERSETutf8 COLLATEutf8_general_ci NULLDEFAULTNULL,`money`  floatNOTNULL,`tradeDate`  datetime NOTNULLPRIMARYKEY(`id`))ENGINE=MyISAMDEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=2CHECKSUM=0ROW_FORMAT=DYNAMICDELAY_KEY_WRITE=0;

Child Table 2012

?

1234567891011121314

CREATETABLE`account_2012` (`id`  int(11) NOTNULLAUTO_INCREMENT ,`name`  varchar(50) CHARACTERSETutf8 COLLATEutf8_general_ci NULLDEFAULTNULL,`money`  floatNOT NULL,`tradeDate`  datetime NOTNULLPRIMARYKEY(`id`))ENGINE=MyISAMDEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=2CHECKSUM=0ROW_FORMAT=DYNAMICDELAY_KEY_WRITE=0;

Master table, all year

?

12345678910111213

CREATETABLE`account_all` (`id`  int(11) NOTNULLAUTO_INCREMENT ,`name`  varchar(50) CHARACTERSETutf8 COLLATEutf8_general_ci NULLDEFAULTNULL,`money`  float NOTNULL,`tradeDate`  datetime NOTNULLPRIMARYKEY(`id`))ENGINE=MRG_MYISAMDEFAULTCHARACTERSET=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:

?

12345678910

createtablerange(　　id int(11),　　money int(11) unsigned notnull,　　datedatetime　　)partition byrange(year(date))(　　partition p2007 valuesless than (2008),　　partition p2008 valuesless than (2009),　　partition p2009 valuesless than (2010)　　partition p2010 valuesless than maxvalue)；

List:

?

1234567

createtable list(　　a int(11),　　b int(11)　　)(partition by list (b)　　partition p0 values in (1,3,5,7,9),　　partition p1 values in(2,4,6,8,0)　);

Hash:

?

12345

createtable hash(　　a int(11),　　b datetime　　)partition byhash (YEAR(b)　　partitions 4;

Key:

?

12345

createtable t_key(　　a int(11),　　b datetime)　　partition by key(b)　　partitions 4;

Partition Management

New Partition

?

12	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

?

123456789

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));

MySQL sub-table and table partitioning detailed