When the database is divided horizontally, such as using the UID Library, uname on the query what to do?

Reprinted from: http://mp.weixin.qq.com/s/_HB7Iq9chDLk2z_m_btq9w

Origin

User Center is essential for almost every company basic services, user registration, login, information query and modification are inseparable from the user center.

When the amount of data is increasing, a multi-user center is required for horizontal segmentation. The most common horizontal slicing method, according to the UID modulo sub-Library:

Through the UID modulo, the data is distributed to multiple database instances, the number of service instances is increased, and the data volume of single database is reduced to achieve the purpose of expansion.

After horizontal segmentation:

The query on the UID attribute can be routed directly to the library, as shown above, assuming access to the uid=124 data, and can directly locate the db-user1 after the modulo.

For a query on uname, you can't be so lucky:

Uname on the query, such as the above, assuming access to Uname=shenjian data, because do not know where the data falls on which library, often need to traverse all the library "sweep the whole library method", when the number of libraries, the performance will be significantly reduced.

It is the problem that this article will discuss how to implement the query efficiently with the UID sub-Library.

"Index Table Method"

Ideas: UID can be directly located to the library, uname can not directly locate the library, if through uname can query to the UID, problem solving

Solution:

1) Establish an index table to record the mapping relationship of Uname->uid

2) when using uname to access, first through the Index Table query to the UID, and then locate the corresponding library

3) The Index table properties are small, can hold very much data, generally do not need to divide the library

4) If the amount of data is too large, can be uname to the library

Potential shortfall: One-time database query, performance degradation

"Cache Mapping Method"

Idea: Access Index table performance is low, the mapping relationship in the cache performance better

Solution:

1) uname query first to the cache to query the UID, and then locate the database according to the UID

2) Assuming the cache miss, use the Sweep library method to obtain the uname corresponding UID, put in the cache

3) uname to UID mapping relationship does not change, once the mapping relationship is put into the cache, will not change, no elimination, cache hit rate is super high

4) If the amount of data is too large, you can use the name for cache level segmentation

Potential shortfalls: Cache queries more than once

"Uname Generate UID"

Idea: Do not make remote query, by uname directly get UID

Solution:

1) When the user registers, the design function uname generates UID,UID=F (uname) and inserts the data by the UID Sub-Library

2) using uname to access, first calculate the UID through the function, namely uid=f (uname) again, by the UID routed to the corresponding library

Potential deficiency: This function design requires a very technical skill, with UID generation conflict risk

"Uname gene into UID"

Idea: Cannot use uname to generate UID, can extract "gene" from uname, integrate into UID

Assuming 8 libraries, using UID%8 routing, the subtext is that the last 3 bits of the UID determine which library the data falls on, and the 3 bits are called "genes".

Solution:

1) When the user registers, the design function uname generates 3BIT gene, UNAME_GENE=F (uname), as above the pink part

2) at the same time, generate a global unique ID of 61bit, as the user's identity, as shown in the green section above

3) Then the 3bit Uname_gene also as part of the UID, such as the upper picture of the yellow part

4) Generate 64bit UID, assembled by ID and uname_gene, and insert data according to the UID Sub-Library

5) using uname to access, first through the function by the uname re-restore 3bit gene, uname_gene=f (uname), through the uname_gene%8 directly to the library

Summary

Business Scenario: User Center, the data volume is large, through the UID library, through the uname route not to the library

Solution: