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

This article introduces the relevant knowledge of "database: divide for a long time, divide for a long time". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Open source, high-performance and ecologically mature MySQL is the most widely used database in China. It is no exaggeration to say that MySQL has witnessed the growth of the Internet in China.

Ali built OceanBase; JD.com based on MySQL, and Tencent is also using MySQL on a large scale today. Therefore, it also deserves to become the core knowledge point that the interviewer must ask and love to ask.

In addition to having doubts about the principle of indexing and storage, when the amount of data reaches a certain scale, MySQL will also involve an almost inevitable core point-sub-database and sub-table.

Voiceover: MySQL is the most popular database selected by DB-Engines in 2019 and has been hovering in the top three over the years.

Question 1: what is the problem of sub-database and sub-table?

The performance bottleneck MySQL is the Btree index. When the amount of data is too large, the index consumes more and more disk IO, and the query performance degrades. In the case of high concurrency, the large amount of data in a single table leads to poor SQL performance, and the high load on the database server leads to performance degradation again, which simply makes things worse.

High availability: under the micro-service architecture, the service stateless type will lead to the pressure point on the database, the stand-alone database and master-slave structure can no longer meet the demand, and the maintenance costs such as data disaster recovery are getting higher and higher.

Security: all different types of data are stored in a database, when the database is down or physical damage occurs, it is easy to cause inestimable losses.

Voiceover: put eggs in different baskets.

Question 2: what is the logic of sub-library and sub-table?

The core of sub-database and sub-table is data split, sub-database is not necessarily sub-table, sub-table is not necessarily sub-database.

For example, the limit of MySQL single-table data is about 50 million. When the amount of data exceeds 50 million, we need to store the data in separate tables.

To put it simply, it is to divide a table structure into multiple tables, or to slice the data of a table into multiple tables. These tables can be placed in the same database, in different databases, or even in different database instances.

Question 3: the interviewer asked, what are the plans for sub-database and sub-table?

Data split method:

Horizontal split

Vertical split

Common scenarios:

Client slicing

Proxy slicing

Distributed database supporting transactions

This is the end of the content of "Database: it must be together for a long time, it must be divided for a long time". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.