Shulou(Shulou.com)05/31 Report--

How to implement sub-table and partition in MySQL? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Vertical subtable

A vertical split table is a table that contains many columns and is split into multiple tables. For example, Table A contains 20 fields, which are now split into tables A1 and A2, each with ten fields (how to split according to the business).

Advantage: in high concurrency situations, you can reduce the number of table and row locks.

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

Horizontal subtable

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

At the same time, generate a total table, record the information of each child table, if you query an id=100 record, it no longer needs a full table scan, but through the total table to find the record in which the corresponding sub-table, and then go to the corresponding table to do retrieval, thus reducing the pressure of IO.

Disadvantages: it will bring a lot of trouble to the maintenance of the SQL code of the front-end application, so you can use MySQL's Merge storage engine to achieve sub-table.

-- I am the dividing line of egg pain.

Using the Merge storage engine to divide the table is transparent to the application's SQL statements and does not need to modify any code.

CREATE TABLE T1 (an INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR (20)); CREATE TABLE T2 (an 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 (an INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR (20)) ENGINE=MERGE UNION= (T1 INSERT INTO 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, the merge storage engine is a virtual table, and the corresponding actual table must be a 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 subtables on a regular basis, you only need to modify the union of the merge table.

CREATE TABLE T3 (an INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR (20)); ALTER TABLE total UNION= (T1 recorder T2 recorder T3)

Horizontal zoning

For example: if there are 100W pieces of data, divided into ten parts, the first 10W pieces of data will be put on the first partition, the second 10W pieces of data on the second partition, and so on. When you take out a piece of data, the data contains all the fields in the table structure, and horizontal partitioning does not change the table structure.

Vertical zoning

For example: when designing a user table, you did not consider it well at the beginning, but put all your personal information into a table, and there will be larger fields in this table, such as personal profiles, and these profiles may not be read by many people, so when someone wants to read them, you can look for them and divide them into separate tables.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.