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

This article mainly explains "what are the sub-table and sub-database algorithms of the database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's study and learn "what are the sub-table and sub-database algorithms of the database?"

Here are several common sub-table algorithms.

1. Divide tables / libraries according to natural time

If the data of an application will reach about 200w in a year, then we can consider using one year's data as a table or library to store, for example, the table is called app, then the data in 2010 is app_2010,app_2011;. If the data volume reaches 200W in a month, then we can divide it by month, app_2010_01,app_2010_02.

two。 Sub-table / sub-library by numerical type hash

If we want to store the user's information, the registration volume of our application is very large, and we can't meet the storage needs with a single table, then we can use the user's number to hash. It is common to use the residual operation. If we want to store the user's information in 30 tables, then the user with the user number 1 is 1% 30. 1, then we store it in the user_01 table. If the user's number is 500, then 500% 30 is 20. Then we store the user's information in the user_20 table.

3. Divide tables / libraries according to MD5 value

We assume that if we want to store the files uploaded by users, if the uploading volume is large, it will also bring the bottleneck of the system. We have done experiments that if there are more than 200 files in a folder, the browsing efficiency of the files will be reduced, of course. This is outside the scope of this article, this piece also needs to do hashing operation. We can use the user name of the file to md5 or use the file's md5 check value to do it, and we can use the first five bits of md5 to do hash, so that we can get up to 5 ^ 5 = 3125 tables. Each time we store a file, we can use the first five digits of the MD5 value of the file name to determine which table the file should store.

4. Example: conjecture of a Weibo's url encryption algorithm and storage strategy.

Now many Weibo use this kind of url to access, if their domain name is www.example.com, then if you post Weibo, you will find that your url has become http://t.cn/Mx4ja1, in this form, how do they carry out such conversion? I guess that is to use the md5 storage and search rules we mentioned above, use the url you sent to md5, and after you get the MD5 value, as in our example, we will use the first 6 bits to divide the table.

5. The problems caused by the subtable.

Table sorting will also bring a series of problems, such as the implementation of paging and statistics. If we want to do a paging of all the data, then we have to traverse every table, so the access efficiency will be very inefficient. I tried to use the proxy of mysql before, but finally I realized it with tcsql.

6. The choice of sub-table algorithm.

If the amount of data in your application is not very large, it is best not to use a sub-table.

Thank you for your reading. The above is the content of "what are the algorithms for dividing tables and databases in the database?" after the study of this article, I believe you have a more profound understanding of the algorithm of dividing tables and databases in the database. The specific use of the situation also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.