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

This article mainly introduces "what are the knowledge points of database sub-tables". In daily operation, I believe many people have doubts about what are the knowledge points of database sub-tables. I have consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "what are the knowledge points of database sub-tables"! Next, please follow the small series to learn together!

I. Database bottleneck

Whether it is an IO bottleneck or a CPU bottleneck, it will eventually lead to an increase in the number of active connections in the database, which will approach or even reach the threshold of the number of active connections that the database can hold. In terms of Service, there are few or no available database connections. And then imagine (concurrency, throughput, crashes).

1. IO bottleneck

The first type: disk read IO bottleneck, too much hot data, database cache can not fit, each query will generate a large number of IO, reduce the query speed-> library and vertical table.

The second type: network IO bottleneck, too much data requested, insufficient network bandwidth-> sub-library.

2. CPU bottleneck

The first type: SQL problems, such as SQL contains join, group by, order by, non-index field conditional query, etc., add CPU operation-> SQL optimization, establish appropriate index, and perform business calculation at the business Service layer.

The second type: the amount of data in a single table is too large, too many rows are scanned during query, SQL efficiency is low, and CPU takes the lead in bottleneck-> horizontal tables.

II. Sub-warehouse and sub-table

1. Horizontal sub-library

Concept: Based on the field, according to a certain strategy (hash, range, etc.), the data in a library is split into multiple libraries.

2. Results:

·Each library has the same structure;

·The data in each library is different and there is no intersection;

The union of all libraries is the full amount of data;

3. Scenario: The absolute concurrency of the system has come up, and it is difficult to fundamentally solve the problem by dividing the table, and there is no obvious business attribution to vertically divide the library.

4, analysis: library more, io and cpu pressure can naturally be multiplied relief.

2. Horizontal sub-table

1. Concept: Based on tables, different tables are split into different libraries according to different business ownership.

2. Results:

·Each library has a different structure;

·The data in each library is also different, and there is no intersection;

The union of all libraries is the full amount of data;

Scenario: The absolute concurrency of the system is up, and separate business modules can be abstracted.

4. Analysis: At this step, service can basically be realized. For example, with the development of business, there are more and more common configuration tables and dictionary tables. At this time, these tables can be disassembled into separate libraries, or even serviced. Furthermore, as the business develops, a set of business patterns is hatched, and the related tables can be disassembled into separate libraries, or even serviced.

4. Vertical sub-table

·Genetic law

·Redundancy method

·Redundancy method

1.

2. Non-partition key cross-database and cross-table paging query problem (horizontal partition and table, splitting strategy is common hash method)

Note: Use NoSQL method to solve (ES, etc.).

3. Capacity expansion problem (horizontal sub-library sub-table, splitting strategy is commonly used hash method)

1. Horizontal expansion library (upgrade from library method)

Step 1:(synchronous double write) application configuration double write, deployment;

Step 2:(synchronous double-write) copy the old data in the old database to the new database;

Step 3:(synchronous double writing) proofread the old data in the new database based on the old database;

Step 4:(synchronous double write) apply remove double write, deploy;

Note: Double-writing is a universal protocol.

At this point, the study of "what are the knowledge points of database sub-database sub-table" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.