What are the ways to sub-database and table in MySQL?

1. Why should we divide databases and tables?

If a website’s business develops rapidly, the traffic of the website will also increase, and the pressure on the data will also increase. For example, for e-commerce systems, the Double Eleven promotion puts a lot of pressure on order data, with hundreds of thousands of concurrent Tps. If the traditional architecture (one master and multiple slaves) is used, the main database capacity will definitely not be able to meet such a high Tps. , the business is getting bigger and bigger, the single table data exceeds the capacity supported by the database, persistent disk IO, the traditional database performance bottleneck, the product manager business - must do, change the program, database knife segmentation optimization. Due to the insufficient number of database connections and the huge amount of data in the table, query performance is still low even after optimization, so splitting is required.

2. What is sub-database and sub-table

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

• Sub-library: Split the data of one library into multiple identical libraries, and access one library during access

• Table splitting:Put the data of one table into multiple tables and operate the corresponding table

3. Sub-database and sub-table Several ways

1. Vertical split

(1) Vertical split of database

Split according to business, as shown in the figure, the e-commerce system is split into order library, member library, and product library

(2) Table vertical split

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 logins, and user_info is responsible for storing basic user information

Features of vertical splitting:

• The structure of each library (table) is different

• At least one column of data in each database (table) is the same

• The union of each database (table) is the full amount of data

Advantages and Disadvantages of Vertical Splitting

Advantages:

• After the split, the business will be clear (special databases will be split by business)

• Data maintenance is simple, depending on the business, the business is placed on different machines

Disadvantages:

• If the amount of data in a single table is high, the writing and reading pressure is high

• It is determined or restricted by a certain business, which means that a business will often affect the database Bottleneck (performance issues, such as Double Eleven rush sales)

• Some businesses cannot be associated with join and can only be called through the Java program interface, which increases development complexity

2. Horizontal split

(1) Database horizontal split

As shown in the figure, according to the member database Split, split into member 1 database, member 2 database, split by userId, userId tail number 0-5 is 1 database, 6-9 is 2 database, there are other ways, take the modulo, and put the even numbers into 1 database. Put the odd numbers into database 2

(2) Split the table horizontally

##As shown in the figure, split the users table into users1 table and The users2 table is split based on userId, and the modulus is taken. Even numbers are placed in the users1 table, and odd numbers are placed in the users2 table.

Other ways of horizontal splitting:

• Range is used to divide a piece of continuous data in each database. This is usually based on a time range, but this is generally less used because it is easy to cause hot spots and a large amount of traffic is hit by the latest data. Up, advantages: When expanding, it is very easy, because you only need to prepare a library for each month. When a new month comes, you will naturally write a new library. Disadvantages: Most requests access the latest data. The actual production use of range depends on the scenario. Your users do not only access the latest data, but evenly access the current data and historical data.

• hash distribution, advantage: can be evenly distributed Disadvantages of allocating the data volume and request pressure of each database: it is troublesome to expand the capacity, and there will be a process of data migration

(3) Horizontal split features

• The structure of each library (table) is the same

• The data of each library (table) is different

• The union of each database (table) is the full amount of data

(4) Advantages and disadvantages of horizontal split

Advantages:

• The data of a single database/single table is maintained at a certain amount (reduced), which helps improve performance

• Improved system stability and load capacity

• The split table has the same structure and requires less program modification.

Disadvantages:

• Data expansion is very difficult and requires a lot of maintenance

• It is difficult to abstract the splitting rules

• The consistency problem of sharded transactions Some businesses cannot be associated with joins and can only be called through the java program interface

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

• Distributed transactions

• Cross-database join query

• Distributed globally unique id

• Development costs require high programmers

5. Sub-library and sub-table How to select technology

(1) Open source framework for sharding databases and tables

• jdbc direct connection layer: shardingsphere , tddl

• proxy proxy layer: mycat, mysql-proxy (360)

jdbc direct connection Layer

The jdbc direct connection layer is also called the jdbc application layer because of all the sharding rules and all the sharding logic, including all these issues of processing distributed transactions It is all in the application layer. All projects are composed of war packages. All fragments are written as jar packages and placed in the war package. Java requires a virtual machine to run. When the virtual machine runs, the war package will be The byte files inside are loaded into the jvm memory by classLoder. All sharding logic is operated based on the memory side

(2) proxy layer

As shown in the figure, the proxy layer, all sharding rules, all sharding logic, including processing distributed transactions, are all written in mycat, and all sharding logic is operated based on mycat

(3) Advantages and disadvantages of jdbc direct connection layer and proxy layer

• ##jdbc direct connection layer has high performance, only supports Java language, and supports cross-database

• The proxy layer has low development cost, supports cross-language, but does not support cross-database

The above is the detailed content of What are the ways to sub-database and table in MySQL?. For more information, please follow other related articles on the PHP Chinese website!