mysql - 分库分表、分区、读写分离 这些都是用在什么场景下 ，会带来哪些效率或者其他方面的好处

Answer 1

Use/Function

Read and write separation: improve the read performance of the database.
Sub-database and vertical table sharding: disperse the system load, so that what was originally done by one machine can be done by several machines.
Horizontal table splitting and partitioning: Reduce the index size to make searches faster.

Usage scenarios

Separation of reading and writing: To open a post content page, you need to select the post table and post comment table, each of which takes 10ms. 1,000 queries per second is the limit of this database. In other words, this forum can only carry 500 visits per second. Then we can separate reading and writing on this database to exponentially improve the reading performance of the database.

Horizontal sub-table: Transaction history, this kind of table. Because information from 1 year ago is rarely queried. Therefore, we can divide the tables horizontally by year. Make this year’s table a priority. This reduces the size of the table to be queried.

Sub-database: If our system is very large and has many functions, there will be many types of database tables, such as marketing activity tables, which are not related to user account tables, then we can divide them into Into two databases, and then placed in different independent database servers, the database throughput can be doubled.

Vertical table splitting: The typical application scenario is in the article list. Generally speaking, our article table will have fields such as title, userId, Content, etc. The Content field is usually of Text or LongText type, and the other The fields are all fixed-length data types. We know that a database optimization rule is:

If all fields of a table are of fixed length type, then it is a fixed-length table. Fixed-length tables have higher query performance than dynamic-length tables

Then, we can use vertical table division to divide the article table into an article table and an article content table. Therefore, the query required on the article list page only needs to query a fixed-length table.

To be continued

Answer 2

The performance of a database table will degrade seriously after exceeding a certain number of records. Therefore, it is necessary to keep the data volume of a single table not too high and divide it into separate tables.

Database programs access data through a large amount of IO. In order to use multiple cheap machines to serve simultaneously to improve performance without purchasing a single super machine, separate databases are used.

Also because of IO, there are generally more reads than writes. Providing reads alone reduces the pressure of writes, and also provides a certain degree of redundancy and disaster resistance.