Mysql database and table scheme, mybatis notes

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 ：

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 ：

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 ：

１、 Intermediate variable ＝ user_id%（ Number of Libraries * Number of tables per library ）;

２、 Library serial number ＝ integer （ Intermediate variable ／ Number of tables per library ）;

３、 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 ：

１、 Intermediate variable ＝ 262145%（256*1024）= 1;

２、 Library serial number ＝ integer （1／1024）= 0;

３、 Table No ＝ 1％1024 = 1;

• 1

• 2

• 3

• 1

• 2

• 3

In this case , about user_id＝262145, Will be routed to ０ The second database １ In a list .