Detailed explanation of MySql database partitioning and table partitioning methods and introduction to partitioning and table partitioning

This article mainly introduces MySql database partitioning and table partitioning methods in detail, telling you what table partitioning and partitioning are, and what is the connection between mysql table partitioning and partitioning. It has a certain reference value. Interested friends can refer to it

1. Why should we divide tables and partitions

We often encounter big problems in daily development In the case of tables, the so-called large tables refer to tables that store millions or even tens of millions of records. Such a table is too large, causing the database to take too long to query and insert, resulting in low performance. If joint query is involved, the performance will be even worse. The purpose of table partitioning and table partitioning is to reduce the burden on the database and improve the efficiency of the database. Generally speaking, it is to improve the efficiency of adding, deleting, modifying, and querying tables.

2. What are sub-tables and partitions

2.1 Sub-tables

Sub-tables are Decompose a large table into multiple entity tables with independent storage space according to certain rules. We can call them sub-tables. Each table corresponds to three files, MYD data file, .MYI index file, and .frm table structure file. . These subtables can be distributed on the same disk or on different machines. When the app reads and writes, it gets the corresponding subtable name according to the predefined rules, and then operates it.

2.2 Partition

Partitioning is similar to table partitioning, both of which decompose tables according to rules. The difference is that table splitting decomposes a large table into several independent entity tables, while partitioning divides data into segments and stores them in multiple locations, which can be on the same disk or on different machines. After partitioning, there is still one table on the surface, but the data is hashed to multiple locations. When the app reads and writes, it still operates on the big table name, and the db automatically organizes the partitioned data.

The main purpose of partitioning is to reduce the total amount of data read and write in a specific SQL operation to reduce response time.

2.3 What is the connection between mysql tables and partitions?

1), can improve the performance of mysql, and have a good performance under high concurrency

state. 2) Table subdivision and partitioning are not contradictory and can cooperate with each other. For those tables with large access volume and relatively large table data, we can combine table subdivision and partitioning. The access volume is not large, but the table For tables with a lot of data, we can partition them.
3) The sub-table technology is more troublesome. You need to manually create sub-tables, and the app server needs to calculate the sub-table names when reading and writing. It is better to use merge, but you must also create the
union relationship between the subtables and configure the subtables. 4) Compared with sub-tables, table partitioning is easy to operate and does not require the creation of sub-tables.

3. Several ways to divide tables

3.1 mysql cluster

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

3.2 Customized rule table splitting

A large table can be decomposed into multiple sub-tables according to business rules. Usually there are the following types, you can also define your own rules.

Range (Range) – This mode 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 calculation of the Hash Key of one or more columns of the table, and finally uses this Hash Partition the data areas corresponding to different code values. For example, you can create a table that partitions the table's primary key.
Key (key value) –An extension of the above Hash mode, the Hash Key here is generated by the MySQL system.
List (Predefined List) – This mode allows the system to split data by the value of a predefined list.
Composite (composite mode) –The combination of the above modes uses the

table splitting rules As with partitioning rules, they are described in detail in the partitioning module.

The following uses Range to briefly introduce how to divide the table (according to the year table).

Assume that the table structure has 4 fields: auto-increment id, name, deposit amount, deposit date
Use the deposit date as a rule to divide the tables and create several tables respectively
2011: account_2011
2012 :account_2012
......
2015: account_2015
The app searches for the corresponding table name based on the date when reading and writing, 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 < 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 Use the merge storage engine to implement table splitting

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

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

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
;

Main 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 Insertion mode, the value can be: 0 does not allow insertion; FIRST inserts into the first table in UNION; LAST inserts into the last table in UNION.

When querying through the main table, it is equivalent to querying all sub-tables together. This does not reflect the advantages of sub-tables. 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 Add new partition

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

4.5.2 Delete partition

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

ALTER TABLE sale_data DROP PARTITION p201010;

4.5.3 Merge partitions

The following SQL merges p201001 - p201009 into 3 partitions p2010Q1 - p2010Q3

ALTER TABLE sale_data
REORGANIZE 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)
);

The above is the detailed content of Detailed explanation of MySql database partitioning and table partitioning methods and introduction to partitioning and table partitioning. For more information, please follow other related articles on the PHP Chinese website!