Mysql database and table scheme, mybatis notes

mysql database table scheme mybatis

We know that no matter how well configured each machine is, it has its own physical upper limit , So when our application can reach or far exceed a certain upper limit of a single machine , We can only look for help from other machines or continue to upgrade our hardware , But the common solution is to add more machines to bear the pressure together .

We also have to consider when our business logic continues to grow , Can our machines meet the demand through linear growth ? therefore , Use the sub database and sub table of the database , Can immediately improve the performance of the system , There are other reasons why you should use the sub database and sub table of the database , Mainly about the specific implementation strategy . Please see the following chapter .

Two 、 Sub table implementation strategy


keyword : user ID、 Table capacity

For most of the database design and business operation are basically related to the user's ID relevant , So use the user ID Is the most commonly used sub database routing strategy . User ID It can be used as an important field throughout the whole system . therefore , Use user's ID We can not only facilitate our inquiry , The data can also be evenly distributed to different databases .( Of course , You can also perform table splitting operations according to categories , There are many other ways to implement the routing strategy of sub table )

Then, the above e-commerce platform assumes , The order sheet order Store the user's order data ,sql The script is as follows ( Just to demonstrate , Omit some details ):

CREATE TABLE order (

order_id bigint(32) primary key auto_increment,

user_id bigint(32),

)

  • 1

  • 2

  • 3

  • 4

  • 5

  • 1

  • 2

  • 3

  • 4

  • 5

When the data is big , Divide the data into tables , First, determine how many tables the data needs to be evenly distributed , That is to say : Table capacity .

Let's assume that there is 100 Tables for storage , When we store data , First of all, the user ID Take the mold , according to  user_id%100  Get the corresponding table for storage and query , The schematic diagram is as follows :

 Picture description here

for example ,user_id = 101  that , What we do when we get the value , You can go through the following sql sentence :

select * from order_1 where user_id= 101

  • 1

  • 1

among ,order_1 It's based on  101%100  It is calculated that , Represents the first chapter after the sub table order surface .

Be careful :

In actual development , If you use MyBatis As a persistence layer ,MyBatis It has provided a good function to support database sub table , For example, the above sql use MyBatis If realized, it should be :

Interface definition :

/**

  • Get user related order details

  • @param tableNum The number of a specific table

  • @param userId user ID

  • @return Order list

*/

public List getOrder(@Param(“tableNum”) int tableNum,@Param(“userId”) int userId);

  • 1

  • 2

  • 3

  • 4

  • 5

  • 6

  • 7

  • 8

  • 1

  • 2

  • 3

  • 4

  • 5

  • 6

  • 7

  • 8

xml Configuration mapping file :

select * from order_${tableNum}

where user_id = #{userId}

  • 1

  • 2

  • 3

  • 4

  • 1

  • 2

  • 3

  • 4

among ${tableNum}  The meaning is to directly add parameters to sql in , This is a MyBatis Supported features .

Be careful :

in addition , In actual development , Our users ID More likely through UUID Generated , In this case , We can start with UUID Conduct hash Get the integer value , Then take the mold .

  • 1

  • 1

3、 ... and 、 Sub database implementation strategy


Database table splitting can solve the efficiency problem of data query when there is a large amount of data in a single table , But it can not improve the efficiency of database concurrent operation , Because the essence of the split table is still the operation on a database , Easily affected by database IO Performance limitations .

therefore , How to put the database IO Performance problems are evenly distributed , Obviously, the performance problem of a single database can be well solved by dividing the data into different databases .

The implementation of sub database strategy is very similar to that of sub table strategy , The simplest way is to route by taking a module .

Or the above , Will the user ID Take the mold , In this way, you can get a specific database , The same keywords are :

user ID、 Storage capacity

The schematic diagram of routing is as follows :

 Picture description here

The storage capacity in the figure above is 100.

Again , If the user ID by UUID Please first hash Then take the mold .

Four 、 Implementation strategy of sub database and sub table


In the above configuration , Database table splitting can solve the query performance problem of single table massive data , The sub database can solve the problem of concurrent access pressure of a single database .

occasionally , We need to consider these two issues at the same time , therefore , We need to split a single table , It also needs to carry out database distribution , In order to expand the concurrent processing ability of the system and improve the query performance of a single table at the same time , This is the sub database and sub table we use .

The strategy of sub database and sub table is relative to the front

《 A big factory Java Analysis of interview questions + Back end development learning notes + The latest architecture explanation video + Practical project source code handout 》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 Full content open source sharing

The two are more complicated , A common routing strategy is as follows :

1、 Intermediate variable = user_id%( Number of Libraries * Number of tables per library );

2、 Library serial number = integer ( Intermediate variable / Number of tables per library );

3、 Table No = Intermediate variable % Number of tables per library ;

  • 1

  • 2

  • 3

  • 1

  • 2

  • 3

for example : The database has 256 individual , Each library has 1024 Data tables , User user_id=262145, According to the above routing strategy , Available :

1、 Intermediate variable = 262145%(256*1024)= 1;

2、 Library serial number = integer (1/1024)= 0;

3、 Table No = 1%1024 = 1;

  • 1

  • 2

  • 3

  • 1

  • 2

  • 3

In this case , about user_id=262145, Will be routed to 0 The second database 1 In a list .

copyright:author[m0_ sixty-four million eight hundred and sixty-seven thousand f],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/01/202201262128089380.html