What is table partitioning and partitioning? MySql database partitioning and table partitioning method

1. Why do we need to divide tables and partitions?

In daily development, we often encounter large 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 poor performance. If a joint query is involved, the performance will be even worse. The purpose of table sharding 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 checking tables.

2. What are table sharding and partitioning?

2.1 Sub-table

Table sharding is to 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 obtains the corresponding subtable name according to the pre-defined rules and then operates it.

2.2 Partitioning

Partitioning is similar to table sharding, both of which decompose tables according to rules. The difference is that table sharding breaks down a large table into several independent physical tables, while partitioning divides data into segments and stores them in multiple locations, which can be the same disk or on different machines. After partitioning, it is still a 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 reading and writing in a specific SQL operation to shorten the response time.

2.3 What is the connection between MySQL table sharding and partitioning?

1) Both can improve the performance of MySQL and have a good performance under high concurrency conditions.
2) Table sharding and partitioning are not contradictory and can cooperate with each other. For tables with large traffic and a lot of table data, we can combine table sharding and partitioning. For tables with small traffic but a lot of table data, we can adopt partitioning.
3) The table partitioning technology is more troublesome. You need to manually create sub-tables, and the app server needs to calculate the sub-table name when reading and writing. It is better to use merge, but you also need to create sub-tables and configure union relationships between sub-tables.
4) Compared with table sharding, table partitioning is easy to operate and does not require the creation of sub-tables.

3. Several ways to divide the table

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 read and write to reduce read and write pressure. This improves database performance.

3.2 Custom rule table

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

Range – This mode allows you to divide your data into different ranges. For example, a table can be divided into several partitions by year.
Hash – This mode allows you to calculate the Hash Key of one or more columns of the table and then partition the data areas by the different values ​​of the Hash code. For example, you can create a table that is partitioned by its primary key.
Key – An extension of the Hash mode above. The Hash Key here is generated by the MySQL system.
List – This mode allows the system to partition data by a predefined list of values.
Composite – Combination of the above modes

The table splitting rules are the same as the partitioning rules and are introduced in detail in the partitioning module.

The following is a brief introduction on how to divide the table (by year) using Range.

Assume that the table structure has 4 fields: auto-increment id, name, deposit amount, deposit date. Use the deposit date as a rule to split the table and create several tables separately.
2011: account_2011
2012: account_2012
…
2015: account_2015
When reading and writing, the app looks for the corresponding table name based on the date, which 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 Using the merge storage engine to implement table sharding

The merge table is divided into a main table and a sub-table. The main table is like a shell, which logically encapsulates the sub-table. In fact, all 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.

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

Subtable 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 that specifies the insertion method. The values ​​can be: 0: no insertion allowed; FIRST: insert into the first table in the UNION; LAST: insert into the last table in the UNION.

When querying through the main table, it is equivalent to querying all the 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 Adding a new partition

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

4.5.2 Deleting a 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 full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.