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

What this article shares with you is about how to solve the only problem of the primary key after the database is divided into databases and tables. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Before this, we introduced the problem of database partition and table partition, which can bring us very good scalability and performance improvement, but it also brings some problems, such as data primary key ID allocation. Let's take Mysql as an example. We usually use the self-increasing primary key of the database, and we try our best to ensure that the business does not need to query data across tables when dividing tables, but we will inevitably encounter such a scenario. If the ID of the primary key on the two tables is duplicated, then the business will go wrong and bring immeasurable consequences.

So how do we solve the only problem with primary keys? Here we introduce several common methods, as well as their advantages and disadvantages.

Method one

First of all, we can use the random method to generate the primary key. Usually, as long as the random algorithm is reasonable and a little salt is added properly, the generated 64-bit primary key ID has a very low probability of repetition (almost without considering the collision). The advantage of this method is that it is very simple to implement, but what is the problem? First of all, the length of ID will be very long, we all know that in the database, the primary key ID is too long will degrade the performance of the index. Secondly, the randomly generated primary key ID is unordered, which means that when inserting new data, you need to insert the index. We all know that the index of Mysql is a B + tree. If the B + tree keeps inserting unordered data, the performance will be greatly reduced, resulting in a decline in insertion performance. Smart students may have thought that we can also generate ordered random numbers, such as using Twitter's SnowFlake, to some extent avoid the impact of B + tree insertion.

Method two

Second, we can still take advantage of the primary key increment feature of the database. In the Mysql database, it is possible to set the step size of the primary key increment, that is, we do not have to increase it in 1, 2, and 3. If we set the step to 10, and start to 10, it will be 10, 20, and 30. If we divide 10 tables, we can make each table start from 0 to 9, and then set the step size to 10, which ensures that the data in the ten tables will not be duplicated. The advantages of this approach are also very simple, requiring only a simple configuration, so what's wrong with it? First of all, it is very inconvenient to expand. What if we want to change the sub-table from 10 to 20? It will bring us some trouble.

Method three

Third, we can maintain an ID allocator. We can use related components such as Redis to put the pre-allocated ID in the queue of Redis. Each time the business is to be inserted, we first take an element out of the pre-allocated queue and then insert it into the database. Of course, this approach requires a certain amount of development, which can relatively guarantee the order of the primary key id, and the expansion is relatively simple.

So which method is better? In computer programming, there is neither absolute good nor absolute difference, as long as it can be close to the business and ensure the smooth operation of the system without large development and maintenance costs, that is a good method. You need to choose according to your own business and current development, operation and maintenance experience. Welcome to follow me, study together and make progress together. Everyone's support is the motivation for me to continue chatting.

The above is the only problem of how to solve the primary key after the database is divided into tables. The editor believes that there are some knowledge points that we may see or use 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.