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

In this article, the editor introduces in detail "what are the ways of MySQL sub-library and sub-table", the content is detailed, the steps are clear, and the details are handled properly. I hope that this "MySQL sub-library sub-table" article can help you solve your doubts.

First, why is it necessary to divide the database and table

If the business of a website develops rapidly, the traffic of the website will also increase, and the pressure of data will follow. For example, in the e-commerce system, the Singles Day promotion has a great pressure on the order data, with more than one hundred thousand Tps concurrent. If the traditional architecture (one master and multiple slaves), the capacity of the main library will certainly not be able to meet such a high Tps, the business is getting larger and larger, and the single table data exceeds the capacity supported by the database. Persistent disk IO, the traditional database performance bottleneck, product manager business must do, change the program, database knife segmentation optimization. The number of database connections is not enough to divide the database, the amount of data in the table is large, and the query performance after optimization is still very low.

2. What is a sub-database and sub-table

The scheme of sub-database and sub-table is a supplement to the data storage and access mechanism of relational database.

Sub-library: split the data of a library into multiple identical libraries, and access a library when accessing

Sub-table: put the data of one table into multiple tables, and operate a corresponding table.

III. Several ways of dividing the database and table.

1. Vertical split

(1) Vertical split of database

According to the business split, such as figure, e-commerce system, split into order library, membership library, commodity library

(2) Vertical split of the table

Split the table according to the business, as shown in the figure, split the user table into the user_ base table and the user_ info table, use_base is responsible for storing the login, and user_info is responsible for storing basic user information

Vertical split feature:

Each library (table) has a different structure.

Each library (table) has at least one column of the same data.

The union of each library (table) is full data.

Advantages and disadvantages of vertical split

Advantages:

After the split, the business is clear (dedicated database is split by business)

Data maintenance is simple, according to the business, the business is placed on different machines

Disadvantages:

If there is a large amount of data in a single table, there is a lot of writing and reading pressure.

Subject to some kind of business decision, or limited, that is to say, a business will often affect the bottleneck of the database (performance problems, such as Singles Day rush to buy)

Some businesses can not be associated with join, so they can only be called through java program interface, which increases the complexity of development.

two。 Horizontal split

(1) split the database horizontally

As shown in the picture, split according to the membership library, split into member 1 library, member 2 library, split by userId, userId tail number 0-5 for 1 library 6-9 for 2 libraries, there are other ways to take models, even numbers into 1 library, odd numbers into 2 libraries

(2) split table horizontally

As shown in the figure, split the users table into the users1 table and the users2 table, split it with userId, take the module, put the even number on the users1 table, and the odd number on the users2 table.

Other ways to split horizontally:

Range divides each database into a continuous piece of data, which is generally based on, for example, a time range, but this is generally less used, because it is easy to generate hot issues and a large amount of traffic is based on the latest data. Advantages: when you expand capacity, it is easy, because as long as you are ready, you can prepare a database every month, and when it comes to a new month, it will be natural. A new library will be written with a drawback: most requests are to access the latest data. Range is used in actual production, depending on the scenario, your users not only access the latest data, but evenly access current data as well as historical data

Hash distribution, advantages: can evenly distribute the amount of data and request pressure of each library. Disadvantages: it is troublesome to expand capacity, and there will be such a process of data migration.

(3) characteristics of horizontal resolution

The structure of each library (table) is the same

The data of each library (table) is different

The union of each library (table) is full data.

(4) advantages and disadvantages of horizontal split.

Advantages:

Keep the data of single database / single table at a certain amount (reduction), which helps to improve performance.

Improve the stability and load capacity of the system.

The structure of the split table is the same, and the program modification is less.

Disadvantages:

The expansion of data is very difficult to maintain.

The split rules are difficult to abstract.

Consistency of sharding transaction part of business cannot be associated with join, so it can only be called through java program interface.

IV. Problems caused by sub-database and sub-table.

Distributed transaction

Cross-library join query

Distributed globally unique id

The development cost is high for programmers.

5. How to select the technology of sub-database and sub-table

(1) Open source framework of sub-library and sub-table.

Jdbc direct connection layer: shardingsphere, tddl

Proxy proxy layer: mycat,mysql-proxy

Jdbc directly connected layer

The jdbc direct connection layer is also called the jdbc application layer because of all the sharding rules, all sharding logic, including dealing with distributed transactions, all these problems are in the application layer, all projects are made up of war packages, all shards are written into jar packages and put into war packages, java needs a virtual machine to run, and when the virtual machine runs, the byte files in the war package will be classLoder loaded into jvm memory. All sharding logic is operated on the memory side.

(2) proxy proxy layer

As shown in the figure, proxy proxy layer, all sharding rules, all sharding logic, including handling distributed transactions, are written in mycat, and all sharding logic is operated on the basis of mycat party.

(3) advantages and disadvantages of jdbc direct connection layer and proxy proxy layer.

Jdbc Direct connection layer has high performance, only supports java language, and supports cross-database.

Proxy proxy layer has low development cost, supports cross-language, and does not support cross-database.

Read here, this article "what are the ways of MySQL sub-library sub-table" article has been introduced, want to master the knowledge of this article still need everyone to practice and use to understand, if you want to know more related articles, welcome to follow the industry information channel.

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.