What are table partitioning and partitioning MySql database partitioning and table partitioning methods

1. Why do you want to divide tables and partitions

In our daily development, we often encounter the situation of large tables, which refer to the tables that store millions or even tens of millions of records. Such tables are too large, resulting in a database that takes too long to query and insert, and poor performance, especially in the case of federated queries. The purpose of sub-table and table partition is to reduce the burden of database and improve the efficiency of database. Generally speaking, it is to improve the efficiency of adding, deleting, modifying and checking tables.

2. What are sub-tables and partitions

2.1 Sub-table

Sub-table is to decompose a large table into several entity tables with independent storage space according to the rule set by 1. We can call them sub-tables, and each table corresponds to three files, namely MYD data file, MYI index file and frm table structure file. These sub-tables can be distributed on the same disk or on different machines. When app reads and writes, it gets the corresponding sub-table name according to the predefined rules, and then operates it.

2.2 Partition

Partitioning is similar to table partitioning in that tables are decomposed according to rules. The difference is that partitioning divides a large table into several independent entity tables, while partitioning divides data into multiple locations, either on the same disk or on different machines. After partition, it is still a table on the surface, but the data is hashed to multiple locations. When app reads and writes, the operation is still a large table name, and db automatically organizes the partitioned data.
The primary purpose of partitioning is to reduce the total amount of data read and write in a particular SQL operation to reduce response time.

2.3 What is the connection between mysql sub-tables and partitions?

1), can improve the performance of mysql, in high concurrency state have a good performance.
2), sub-table and partition are not contradictory, and can cooperate with each other. For those tables with large visits and more table data, we can adopt the combination of sub-table and partition. For tables with small visits but a lot of table data, we can adopt partition.
3), sub-table technology is more troublesome, need to manually create sub-table, app server read and write time need to calculate sub-table name. merge better 1, but also to create sub-table and configure union relationship between sub-table.
4) Compared with sub-table, table partition is easy to operate and does not need to create sub-table.

3. Several ways of dividing tables

3.1 mysql Cluster

It is not a sub-table, but it plays the same role as a sub-table. Cluster can share the number of database operations and share tasks among multiple databases. Cluster can separate reading and writing and reduce reading and writing pressure. Thereby improving database performance.

3.2 Custom Rule Sub-table

Large tables can be decomposed into multiple sub-tables according to business rules. Generally, it is of the following types, or you can define your own rules.

Range (Range) This pattern allows data to be divided into different ranges. For example, a table can be divided into several partitions by year.
Hash (hash) This mode allows the data region to be partitioned by calculating the ES50Key of one or more columns of the table and finally corresponding to different values of the Hash code. For example, you can create a table that partitions the primary key of the table.
Key (key value) is an extension of Hash mode above, where Hash Key is generated by MySQL system.
List (predefined list) This pattern allows the system to partition data by the values of a predefined list.
Composite (Compound Mode) Combination of the above modes

Sub-table rules and partition rules are described in detail in the partition module.

The following is a brief introduction to how to divide tables (according to the year table) in Range.

The hypothesis table structure has four fields: self-added id, name, deposit amount and deposit date
Take the deposit date as a rule sub-table and create several tables respectively
2011: account_2011
2012: account_2012
...
2015: account_2015
When app reads and writes, it looks up the corresponding table name according to the date, which needs to be judged 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 < 2012) {
    tablename += 2011; // account_2011
  } else if (year < 2013) {
    tablename += 2012; // account_2012
  } else if (year < 2014) {
    tablename += 2013; // account_2013
  } else if (year < 2015) {
    tablename += 2014; // account_2014
  } else {
    tablename += 2015; // account_2015
  }
  return tablename;
}

3.3 Using merge storage engine to realize table splitting

merge is divided into main table and sub-table. The main table is similar to a shell, which logically encapsulates the sub-table. In fact, the data are stored in the sub-table.

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

Sub-table 2011

CREATE TABLE `account_2011` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

Sub-table 2012

CREATE TABLE `account_2012` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

Master table, all years

CREATE TABLE `account_all` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MRG_MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
UNION=(`account_2011`,`account_2012`)
INSERT_METHOD=LAST
ROW_FORMAT=DYNAMIC
;

When creating the main table, there is an INSERT_METHOD, indicating the insertion mode, and the value can be: 0 does not allow insertion; FIRST is inserted into the first table in UNION; LAST is inserted into the last table in UNION.

When querying through the main table, it is equivalent to combining all the sub-tables into one query. This does not reflect the advantages of sub-tables, so it is recommended to query sub-tables.

4. Several ways of partitioning

4.1 Range

create table range( 
 　　 id int(11), 
 　　 money int(11) unsigned not null, 
 　　 date datetime 
 　　 )partition by range(year(date))( 
 　　 partition p2007 values less than (2008), 
 　　 partition p2008 values less than (2009), 
 　　 partition p2009 values less than (2010) 
 　　 partition p2010 values less than maxvalue 
);

4.2 List

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

4.3 Hash

create table hash( 
 　　 a int(11), 
 　　 b datetime 
 　　 )partition by hash (YEAR(b) 
 　　 partitions 4;

4.4 key

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

4.5 Partition management

4.5. 1 New partition

ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

4.5. 2 Delete partition

When 1 partition is deleted, all the data in the partition is also deleted.

ALTER TABLE sale_data DROP PARTITION p201010;

4.5. 3 Merge partitions

SQL below merges p201001-p201009 into three partitions p2010Q1-p2010Q3

CREATE TABLE `account_2011` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

0