MyBatis realization of MySQL database Sub-Library table operation and summary

Read Catalogue

• Objective
• MyBatis The simplest step of implementing a table
• The way of separation
• Separated policy
• The problem of separation
• Principles of separation
• Ways to achieve separation
• Summarize

Objective

As a database, as a table in the database, as the number of users over time, one day, the amount of data will be very difficult to deal with the point. At this point only a table of data has been more than tens of millions, whether it is a query or modification, for its operation will be very time-consuming, then need to do database slicing operation.

MyBatis The simplest step of implementing a table

Since the title of the article is so written, instead of directly on the dry goods to the more practical, we will first look at how to achieve the simplest sub-table.

1, we simulate the user table data volume more than tens of millions (although the actual is not likely)

2, the original name of the user table is called User_tab, we cut into User_tab_0 and user_tab_1 (the actual may not be so random name), so that the original tens of thousands of data separated into a Parina of data volume of two tables.

3, how to operate these two tables? We use UserID, the user's unique identity, to differentiate.

4, userid%2 = = 0 User Operation table User_tab_0, similarly userid%2 = = 1 User Action table user_tab_1

5. How do SQL statements be implemented in MyBatis? The following is an example of an SQL statement that queries a user

<select id= "GetUser" parametertype= "Java.util.Map" resulttype= "Userdo" >
SELECT UserId, Name
From User_tab_#{tabindex}
WHERE userId = #{userid}
</select>

Where we pass in two parameters TabIndex and Userid,tabindex is the indicator value (0 or 1) needed to manipulate the table, so if you need to query a user with UserID 5, then the resulting SQL statement will be:

SELECT UserId, Name
From User_tab_1
WHERE userId = 5

Other redundant DAO services and implementations I don't have much to show here, I'm sure you'll be wise.

The above is the simplest implementation, do not need redundant framework, do not need any plug-ins to meet the requirements of the table.

Above is basically all the content of the implementation, the following will begin to elaborate on the details of the separation, see the lively basic can be scattered.

I'll say it from the following angles, respectively. I try to use the simplest vernacular.

The way of separation

There are two main ways of slicing, horizontal slicing and vertical slicing.

1. Horizontal segmentation

Simply put, a table is separated into a few identical tables, and the table's name is different. Just like the simplest example above.

This segmentation is suitable for situations where the data volume of a table is too large to slow down the operation time, such as some saved record tables.

2. Vertical slicing

Separate business modules into different databases, these business modules are directly preferably 0-coupled (simply to say nothing of the relationship).

This is mainly suitable for large data volume, and business scenarios are more dispersed, there is no logical relationship between the situation.

Separated policy

There are a number of specific strategies, you can also design your own, the general strategy has the following several, but the list is not specifically launched.

1, "%" to take the mold, that is, the above example is implemented, is also the simplest one.

2, MD5 Hash

3. Shift

4, Date time (according to the different date of the table, such as one months a table, this month to operate this form, next month on the next chart)

5, enumeration scope (user 1-10000 operation of the first table, user 10001-20000 operation of the second table)

The problem of separation

Let's talk about the final point, cause the problem.

The database must not be divided by what you say. (Others have feelings, how can say points on it?) ）

To be honest, I've listed some of the problems that can only result from the following separations.

1, the primary key to add the uniqueness of the problem; After separating a number of tables, it will lead to the original self-growth of the primary key is not unique, so there is no way to self-growth, causing problems, the solution is also some, such as a separate maintenance of a primary key table dedicated to storing the current primary key, or other middleware.

2, the efficiency of the new issue, although not a big problem, but the new will certainly be more computational, the problem can be ignored.

3, the query brought about by the paging problem, separated into multiple tables, paged query is very difficult, which also takes into account the different separation with different solutions, in short, will produce problems.

4, the same, the association query, the original table associated with another table or another table associated with a table, are very simple, but now after the separation is difficult.

5, transaction problems, multiple tables need to use a distributed transaction to complete the original operation with the transaction. Because the original transaction just locks a table and now it's probably going to lock up more than one.

6, the expansion of the problem, some of the segmentation strategy, the scalability of the data is actually not good, then if there is more data to come, is it possible to create a new table to expand it?

Principles of separation

The following summarizes the principles of separation, mainly refers to the network, there is no practical basis (I am not a yearly salary of the DBA can not touch so big data to the actual test), so if there are any problems also please point out.

1, can not be divided into

2, can be divided into less than the number of

3, multi-redundant, not related

4, avoid the use of distributed transactions, mainly is too difficult I will not AH

5, single-table tens of thousands of records will not be divided

6, now can not be divided after the time

7. Expansion, coupling, careful consideration

Ways to achieve separation

Finally say the way of separation, now popular use of the DAO framework is MyBatis, there are many other frameworks. The implementation of the separation is mainly in the following ways.

1, the original implementation, and the above example, do not need anything else, using the original framework, to control the implementation of their own.

Advantages are: Easy to control, master the initiative.

The disadvantage is: The amount of code, you need to be very clear, modify inconvenient, do not support complex segmentation, such as the need to do some paging after slicing, as well as the above-mentioned primary key issues.

2, plug-in implementation, using the framework itself developed some plug-ins, to implement these plug-ins, and then use the plug-in to access the database, directly realize the separation.

The advantages are: less code, simple implementation, and good extensibility.

Disadvantages are: Difficult to control, the separation method is limited, there are problems difficult to solve. No particularly mature plugins found.

3, middleware implementation. Using some of the database access middleware, before accessing the database to do some operations so that SQL changes accordingly to achieve separation.

The advantages are: The coupling is small, the scalability is good, the problem of distributed transaction can be solved.

Sure is: the implementation is more complex, need to learn middleware, the cost is large. Maintenance is also a big problem, in case of hanging off.

In short, the way is different, but considering the cost above, the first is almost 0 cost, you can get started, and relatively easy to control, as the example given above, and the current I deal with the data has not reached the point of separation, so I choose the first kind. Also recommended for use. If you find more useful plugins or middleware can also be recommended in the comments.

Summarize

In the actual project, I was due to the user's account record too much so I had to separate, and because the account record more just add no modification and deletion, query is also a few, so use the simplest way to separate, also chose the simplest strategy. I hope the above principles and strategies and the summary of the problem can help you, some reference.

MyBatis realization of MySQL database Sub-Library table operation and summary