Realization Method of MySQL Partition Table and Partition

Vertical sub-table

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

Advantages: In the context of high concurrency, the number of table locks and row locks can be reduced.

Disadvantages: In the case of very large data records, the reading and writing speed will still encounter bottlenecks.

Horizontal sub-table

If a website, a table in its database has reached hundreds of millions of records, then if you query through select at this time, his query will be very slow without index, so you can divide this table into 10 sub-tables through hash algorithm (at this time, the data volume of each table is only 10 million).

At the same time, a summary table is generated to record the information of each sub-table. If a record of id=100 is queried, it no longer needs to scan the whole table, but finds out which corresponding sub-table the record is on through the summary table, and then goes to the corresponding table for retrieval, thus reducing the pressure of IO.

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

-------------------------------------------------------

Using Merge to store the engine sub-table is transparent to the SQL statement of the application program, and does not need to modify any code.

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;

Errors may be reported when creating total table:

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

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

If you need to add sub-tables periodically, 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 zoning

For example: If there are 100W data, divided into 10 parts, the first 10W data into the first partition, the second 10W data into the second partition, and so on. When a piece of data is fetched, it contains all the fields in the table structure, and horizontal partitioning does not change the table structure.

Vertical zoning

For example: When designing the user table, At the beginning, I didn't consider it well, but put all my personal information into a table, and this table will have relatively large fields, such as personal profiles, which may not be read by many people, so wait until someone wants to see it, and then look it up. When dividing the table, you can separate such large fields

Complete 1 table, corresponding to 3 files, 1. MYD data file,. MYI index file,. frm table structure file.