Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about how to achieve horizontal segmentation in mysql, many people may not know much about it. In order to make you understand better, the editor summarized the following content for you. I hope you can get something according to this article.

Method 1: use MD5 hash

The way to do this is to encrypt the UID with md5, then take the first few bits (we take the first two here), and then you can hash different UID into different user tables (user_xx).

Php code

Function getTable ($uid) {

$ext = substr (md5 ($uid), 0,2)

Return "user_". $ext

}

Through this technique, we can distribute different UID into 256user tables, namely user_00,user_01. User_ff . Because UID is numeric and incremented, according to md5's algorithm, user data can be almost evenly divided into different user tables.

But the problem here is that if there are more and more users in our system, there will be more and more data in a single table, and the table cannot be extended according to this algorithm, which will return to the problem at the beginning of the article.

Method 2: use shift

The specific methods are:

Php code

Public function getTable ($uid) {

Return "user_". Sprintf ("d", ($uid > > 20))

}

Here, we move uid 20 bits to the right so that we can put about 1 million of the user data in the first table user_0000 and the second 1 million user data in the second table user_0001, so that if we have more and more users, we can just add the user table. Since the table suffix we keep is four digits, here we can add 10, 000 user tables, namely user_0000,user_0001...

User_9999 . Ten thousand tables with 1 million data in each table, we can save 10 billion user records. Of course, if you have more user data than this, it doesn't matter, all you have to do is change the reserved table suffix to increase the number of expandable tables, for example, if you have 100 billion pieces of data, each table stores 1 million, then you need 100000 tables. We just need to keep the table suffix as 6 digits.

The above algorithm can also be written flexibly:

Php code

/ * *

* according to the UID sub-table algorithm

* @ param int $uid / / user ID

* @ param int $bit / / Table suffix retains several digits

* @ param int $seed / / move the number of bits to the right

* /

Function getTable ($uid, $bit, $seed) {

Return "user_". Sprintf ("0 {$bit} d", ($uid > > $seed))

}

Summary:

Both of the above methods need to estimate the maximum amount of user data in our current system and the maximum capacity of a single table in the database.

For example, in the second scenario, if we estimate that the number of users of our system is 10 billion and the optimal amount of data for a single table is 1 million, then we need to move the UID 20 to ensure that each table is 1 million of the data, leaving four bits of the user table (user_xxxx) to expand 10, 000 tables.

Also like the first scheme, each table 1 million, md5 after the first two, there can only be 256tables, the total database of the system is: 256 * 1 million; if the total amount of data in your system is more than this, then you must MD5 to take the first three or four or even more.

After reading the above, do you have any further understanding of how to achieve horizontal segmentation in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.