Specific implementation methods of MySQL table sharding and partitioning

Vertical table

Vertical table splitting means splitting a table with many columns into multiple tables. For example, table A contains 20 fields, and now it is split into tables A1 and A2, with ten fields in each table (the specific splitting method depends on the business).

Advantage: In high-concurrency scenarios, the number of table locks and row locks can be reduced.

Disadvantages: When the data records are very large, the reading and writing speeds will still encounter bottlenecks.

Horizontal table

If a certain website has a table in its database with hundreds of millions of records, then if you query it through select, the query will be very slow without an index. In this case, you can use the hash algorithm to divide the table into 10 sub-tables (at this time, the amount of data in each table is only 10 million records).

At the same time, a general table is generated to record the information of each sub-table. If you query a record with id=100, it no longer needs to scan the entire table. Instead, it uses the general table to find the corresponding sub-table where the record is located, and then searches the corresponding table, which reduces IO pressure.

Disadvantages: It will cause great trouble to the maintenance of SQL code of front-end program application. At this time, you can use MySQL Merge storage engine to implement table partitioning.

---------------------------------------I am the dividing line that makes me feel pain----------------------------------------------------

Using the Merge storage engine to partition tables is transparent to the application's SQL statements and does not require any code changes.

CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

When creating the total table, an error may occur:

Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

In fact, the merge storage engine is a virtual table, and the corresponding actual table must be a MyISAM type table. If your MySQL version is 5.1 or above, the default database uses the InnoDB storage engine, so when creating total, the t1 and t2 tables must be MyISAM storage engines.

If you need to add sub-tables regularly, you only need to modify the union of the merge table.

CREATE TABLE t3( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
ALTER TABLE total UNION=(t1,t2,t3)

Horizontal partition

For example, if there are 1 million pieces of data, they are divided into ten parts. The first 100,000 pieces of data are placed in the first partition, the second 100,000 pieces of data are placed in the second partition, and so on. When a piece of data is retrieved, it contains all the fields in the table structure, and horizontal partitioning does not change the table structure.

Vertical partition

For example, when designing a user table, you may not think carefully at the beginning and put all personal information into one table. This table will have relatively large fields, such as personal profiles. However, these profiles may not be viewed by many people, so when someone wants to view them, they will search for them. When splitting the table, you can separate such large fields.

A complete table corresponds to three files: a .MYD data file, a .MYI index file, and a .frm table structure file.