vertical scale
Vertical table is a table containing a lot of columns split into multiple tables, such as table A contains 20 fields, now split into tables A1 and A2, two tables of ten fields each (specific how to split according to the business to choose).
Advantage: reduces the number of table locks and row locks in highly concurrent scenarios.
Disadvantage: In the case of very large data records, read and write speeds can still be a bottleneck.
horizontal scale
If a website, a table in its database has reached hundreds of millions of records, then at this time if you select to query, in the absence of indexes, his query will be very slow, then the hash algorithm can be divided into 10 sub-tables (at this time, each table's data volume of only 10 million).
At the same time to generate a master table, record the information of each sub-table, such as querying a record id = 100, it no longer need to scan the whole table, but through the master table to find the record in which the corresponding sub-table, and then go to the corresponding table to do the search, which reduces the IO pressure.
Disadvantage: It will bring a lot of trouble to the maintenance of the SQL code of the front-end program application, which can use MySQL's Merge storage engine to realize the split table.
--------------------------------------- I'm an egghead splitter ----------------------------------------------------
Splitting tables with the Merge storage engine 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;
An error may be reported when creating the TOTAL table:
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
In fact, merge storage engine is a virtual table, corresponding to the actual table must be myisam type of table, if your mysql is 5.1 or above, the default database using the InnoDB storage engine, so in the creation of total, t1 and t2 table must be myisam storage engine.
If you need to add sub-tables periodically, just 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 partitioning
For example: If there are 100W data, divided into ten parts, the first 10W data is put into the first partition, the second 10W data is put into the second partition, and so on. When you take out a piece of data, it contains all the fields in the table structure, and the horizontal partitioning does not change the structure of the table.
vertical subdivision
Example: in the design of the user table, the beginning of the time did not take into account, and all the information of the individual are put into a table inside to go, the table will have a relatively large field, such as personal profile, and these profiles, perhaps not many people to see, so wait until someone wants to look at the time, and then go to look for the sub-table, you can be such a large field, separate
A complete table corresponds to three files, a .MYD data file, .MYI index file, and .frm table structure file.