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

This article to share with you is about Mysql how to achieve horizontal sub-table, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some gains, not much to say, follow Xiaobian to see it.

1. Split tables according to business attributes

The algorithm of this sub-table method is roughly modulo, hash, md5, etc.

If you want to query based on other conditions, other conditions must be associated with this attribute, and query conditions must have this attribute.

Examples:

The user profile table is split horizontally modulo the user ID.

There are groups in communities, there are apps in groups, and there are various types of apps. Group ID and application ID can be used to split the table.

Question:

Unable to obtain attributes of table splitting when querying according to a condition

1)Conditions contain information about subtables

For example, if a user places an order on a website, we divide the order according to the user ID, so that the user can easily query the order associated with him. However, when the user complains, the customer service needs to query the order according to the order number. The order number can contain information about sub-tables. For example, the order is divided into 100 tables. There can be two characters in the order number to indicate which table the order is in.

2)Store the corresponding association with key-value store

The principle is to use key value store as index table

3)data redundancy

Tables that need to be associated can be made redundant. Avoiding queries.

Examples:

Buy gifts. When purchasing virtual gifts, we split the table according to the buyer's ID, and the order number also contains the table information. However, users may also query according to the recipient, in which case we can generate a redundant record for the recipient after the purchase is successful.

4)Cache, NOSQL

Similar to data redundancy. For the example of group application splitting mentioned in the example, we have split the table according to group ID and application type. But when I want to query all recent types of apps, I run into difficulties. We need to query all the app types in the group, and sort, paginate, and so on. In fact, you can store the last few hundred apps in a cache.

2. Disassemble tables according to time

When table relationships are complex, tables cannot be grouped according to a dimension. But there is a clear timeliness.

Examples:

Presumably, everyone used Weibo, and someone's Weibo would be pushed to thousands of households. Therefore, a certain microblogging is unable to query the table according to the user ID. And meager is very time-sensitive. The default dynamic message of a year ago is no longer a concern. We divide the microblogs into time tables, one table every three months. The memcached stores only one month. Users 'microblogging inboxes (lists of microblogging IDs) are generally limited in length. When the cache server restarts or misses, you need to query Mysql, mysql by time table, cache misses, most of the time is to check the meager nearly three months. So nearly a year of meager we can store in the physical resources of better servers.

3. Split the table according to the self-increasing ID

This partitioning method does not take modular points, but stores a specified amount of data per table. If the amount of data arrives, it is stored in a new table. This allows complete control over the amount of data per table. The relationship is very simple and time-sensitive.

4. Ways to migrate data

When some data is very old, it is rarely queried again. For example, employee payroll, we can only save this year's salary situation. The historical data can be migrated to a salary_old table to ensure that the data is not lost. But it can also be used to query.

The above is how to achieve horizontal tables in Mysql. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.