Summarize the strategy and application of MySQL sub-table sub-database

Last month before interviewing a company, the idea of MySQL sub-table, then briefly said the next hash algorithm sub-table, as well as the idea of discuz sub-table,
But for the new data self-ID storage of the design idea is not very good answer (the test + interview the whole process is OK, because with the personal expectations of the salary is not ideal and reluctantly give up.), and then delve into the MySQL sub-table optimization and other design ideas. The purpose of this is:

Why divide tables and partitions?

We often encounter large tables in daily development, so-called large tables are those that store millions or even tens records. Such tables are too large to cause the database to take too long to query and insert, with poor performance and worse performance if it involves federated queries. The purpose of partition table and table is to reduce the burden of database, improve the efficiency of database, usually point is to improve the efficiency of adding and deleting tables.

What is a sub-table?

The Sub-table is a large table according to a certain rule into a number of independent storage space of the entity table, we can be called a child table, each table corresponds to three files, myd data file,. Myi index file,. frm table structure file. These sub-tables can be distributed on the same disk or on different machines. When the app reads and writes, it gets the corresponding sub-table name according to the predefined rules and then goes to manipulate it.

What is a partition?

partitions and sub-tables are similar, and are broken down by rules. The difference is that the table divides the large table into several separate entity tables, while partitioning divides the data into multiple locations, which can be the same disk or on different machines. After partitioning, the surface is still a table, but the data is hashed to multiple locations. When the app reads or writes a large table name, DB automatically organizes the data into the partition.

What is the connection between MySQL sub-tables and partitions?
1. All can improve MySQL's sex high, in the high concurrency state has a good performance.
2. sub-table and partition are not contradictory, can cooperate with each other, for those large traffic, and table data more tables, we can take the table and partition combination of the way (if the merge table, not with the partition, you can use other sub-table test), access is not large, but the table data a lot of tables, We can take the partitioning method and so on.
3. The Sub-table technology is more troublesome, need to manually create a child table, the app server read and write when the child table name needs to be computed. It is better to use merge, but also to create the union relationship between the child table and the Configuration child table.
4. Table partitioning is easy to operate and does not require creating child tables, relative to the table.

We know that for large-scale Internet applications, the amount of data in a single database can reach tens of millions or even billions of dollars, while facing this high level of concurrency pressure. Master-slave structure can only extend the read ability of the database, write operation or centralized in master, Master does not have the unrestricted hook slave Library, if need to further expand the throughput capacity of the database, you can consider the strategy of using the Sub-Library table.

1. Sub-table

Before dividing the table, the first thing to do is to select the appropriate table strategy (which dictionary is the table field and how many tables to divide the data into) so that the data can be distributed evenly across multiple tables without affecting the normal query. In enterprise applications, org_id (the primary key of the organization) is often used as a table field, which is often a userid in Internet applications. After determining the sub-table strategy, when the data is stored and queried, you need to determine which table to find the data,

Data stored in data table = table field content% number of tables

2. Sub-Library

The Sub-table can solve the problem that the query efficiency of the single-table data is too large, but can not bring the quality improvement to the concurrent access of the database, face high concurrent write access, when Master can not bear the high concurrent write request, no matter how to extend the slave server, there is no meaning. By splitting the database, we improve the ability of the database to write, the so-called sub-Library. The database is routed by using the method of the key word modulo in the sub-Library.

Database in which the data is stored = number of contents of the Library field% database

3. Sub-table and sub-Library

When the database faces both massive data storage and high concurrent access, it is necessary to take both the table and the sub-Library strategy. The General Sub-table sub-library strategy is as follows:

Intermediate variable = keyword% (number of databases * Number of single-Library data tables)

Library = rounding (number of intermediate variables/library data tables)

Table = (middle variable% single-Library data table number)
To be continued .......

Summarize the strategy and application of MySQL sub-table sub-database