Java Interview 10| database related

1. ID Assignment Single point problem

The system uses the self-increment of a table to get the order number, and all order generation must first insert a piece of data here to get the order number. After the library, the number of libraries become more, the corresponding failure times more, but because of the existence of a single point, the scope of failure is not correspondingly reduced, so that the whole year downtime rise, the availability of decline.

According to the problem of assigning single points for ID, considering the insufficiency of database table allocation performance, the Tair, Redis, Snowflake and other ID allocators are investigated, and the ID interval is segmented and multi-point allocation is also considered.

But in the end, these scenarios are not used, mainly because the ID assignment is strongly dependent on the system, and in the distributed system, the overall availability of the service will inevitably decrease. We still start with the database and make improvements, as follows.

For example, by assigning a table assignment to 100 tables at a time, the business logic performs a simple operation based on a different table name to get the final order number.

ID and User binding: For the order system, each user has a unique userid, we can according to the last 2 bits of the userid to the corresponding id_x table to take the order number, such as UserID 10086 to the user to id_86 table to the value of 42, the order number 42*100+86 = 4286.

The contents of the order according to the UserID Model 100 after the table, such as:

By looking at the techniques above, we find that the orders are the same according to the "UserID modulo" table and according to "order number modulo", because the latter two digits are the same. In this case, the sub-Library operation is quite simple and easy, the limit is divided into 100 libraries, two tables per library. The same user's request must be completed in the same library, achieving a complete split.

Note : In general, the order data sub-table is based on the userid, because we want the same user's data stored in a table, easy to query. When given an order number, we can not determine which sub-table of the order, so most of the order system at the same time maintain an order number and UserID relationship, first based on the order number to find the UserID, and then based on the UserID to determine the sub-table and then query to get the content. Here, we found through the previous technique, the order number at the end of the two-bit and userid, given the order number, we directly know the location of the table, do not need to maintain the association table. Given the order number of the case, a single query from the original 2 SQL into 1, the query volume decreased by 50%, greatly improving the system high concurrency performance.

A typical generated unique ID might have some meaning, such as:

Timestamp + user identification code + random number

The timestamp can be sorted, the user identification code can be used for table positioning, the generation is relatively simple

can also use the Application Layer primary key scheme, so that can be database segmentation, resource positioning and so on.

Take a look at how to generate a globally unique ID, refer to:

(1) http://www.cnblogs.com/baiwa/p/5318432.html

(2) http://blog.csdn.net/houkai6/article/details/17713845

2.

3. How does the database implement the transaction?

4, database and cache inconsistency problem

Resolution of the DB-to-cache mismatch problem:

(1) "Two elimination law" adds a record to the cache while adding data to the database. When updating, you can either retire the cache first, write the data, then retire the cached policy, or update the cache after the database is updated, thus not causing the cache misses.

(2) When adding data, insert it in the database before saving one copy in the cache. You can update your cache when you update your data frequently, such as when you update Redis, you need to record the updated key value, and then flush to the database asynchronously.

Reference:

(1) Redis and DB data synchronization problem http://blog.csdn.net/seapeak007/article/details/53410553

(2) Cache architecture design details two or three things http://www.jianshu.com/p/a38b26b55696

(3) Master-slave db and Cache conformance HTTP://WWW.JIANSHU.COM/P/E3F5FF55F0FD

Java Interview 10| database related