A database primary key generation method, sub-table positioning method and device

By adding subtable identification fields and hash processing to the database primary key generation, a globally unique subtable primary key is generated, which solves the problem of low primary key repetition and generation efficiency in the existing technology, and realizes the stability of primary keys when expanding and shrinking capacity and high concurrency support.

CN113704245BActive Publication Date: 2024-12-03BEIJING WODONG TIANJUN INFORMATION TECH CO LTD +1
View PDF 1 Cites 0 Cited by

Patent Information

Application Number
CN202010441717.5
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2020-05-22
Publication Date
2024-12-03
Estimated Expiration
2040-05-22

AI Technical Summary

Technical Problem

In the prior art, the database primary key generation method has problems such as self-increasing Id duplication and low generation efficiency of snowflake algorithms, and it is impossible to flexibly expand and shrink.

Method used

By adding subtable identification fields during primary key generation, using hash function to process business field values ​​and current time instance identification, generate globally unique subtable primary keys, and set timestamps, subtable identifications and instance identifications in primary key generation rules Field to ensure that the primary key is stable and unchanged when expanding and shrinking.

Benefits of technology

It realizes that the primary key is stable and unchanged and globally unique when expanding and shrinking, improves the primary key generation efficiency, meets high concurrency requirements, and can accurately reverse position the records of the subtable.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN113704245B_ABST
    Figure CN113704245B_ABST
Patent Text Reader

Abstract

The present invention discloses a database primary key generation method, a sub-table positioning method and a device, and relates to the field of database technology. A specific implementation of the method includes: obtaining a business field value of a target business field in an original data table, performing hash processing on the business field value using a set hash function to obtain a hash value; using the hash value to perform a modulo operation on a maximum value corresponding to a set sub-table identifier length to obtain a sub-table identifier; obtaining a current time and an instance identifier, and splicing and generating a sub-table primary key according to the current time, the sub-table identifier and the instance identifier. The database primary key generation method adds a sub-table identifier field during the primary key generation process, so that the sub-table identifier is independent of the sub-table implementation, thereby ensuring that the primary key is stable and unchanged, and globally unique when expanding or shrinking.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a database primary key generation method, a sub-table positioning method and a device. Background Art

[0002] After a database is created, as time goes by and the business volume increases, the data tables in the database and the amount of data in the data tables will increase. Since the storage resources of the database are limited, the accumulation of a large amount of data will lead to a decrease in its processing capacity and an increase in processing overhead. In order to improve storage capacity and query efficiency, the data stored in one database is usually distributed to multiple databases, and the data stored in one data table is distributed to multiple data tables with the same structure according to certain rules. This method is called database and table sharding (such as Figure 1 ).

[0003] There are many rules for table partitioning, such as partitioning by data business time dimension, partitioning by single table data volume, hash partitioning by one or more business fields, etc. In order to uniquely identify each row of records in the partition table, a primary key needs to be set for the partition table. In the prior art, the primary key can be an auto-increment identifier (ID) or generated using a snowflake-like algorithm.

[0004] In the process of implementing the present invention, the inventors found that there are at least the following problems in the prior art:

[0005] If the primary key is an auto-increment ID, it will cause duplication of auto-increment IDs between different tables, and it will be impossible to reversely locate the sub-table to which the record belongs; if the primary key is generated by a snowflake-like algorithm, the generation efficiency is low, and when calculating the sub-library and table identifiers, the number of sub-libraries and tables required must be determined in advance. Once the sub-library and table identifiers are determined, the number of libraries and tables cannot be changed, and the capacity cannot be flexibly expanded or reduced. Summary of the invention

[0006] In view of this, an embodiment of the present invention provides a database primary key generation method, a sub-table positioning method and a device. The database primary key generation method adds a sub-table identification field in the primary key generation process, so that the sub-table identification is independent of the sub-table implementation, thereby ensuring that the primary key is stable and unchanged, and globally unique during expansion or reduction.

[0007] To achieve the above objective, according to one aspect of an embodiment of the present invention, a method for generating a database primary key is provided.

[0008] A method for generating a database primary key according to an embodiment of the present invention includes: obtaining a business field value of a target business field in an original data table, performing hash processing on the business field value using a set hash function to obtain a hash value; using the hash value to perform a modulo operation on a maximum value corresponding to a set sub-table identifier length to obtain a sub-table identifier; obtaining a current time and an instance identifier, and generating a sub-table primary key by splicing the current time, the sub-table identifier and the instance identifier.

[0009] Optionally, according to the current time, the sub-table identifier and the instance identifier, a sub-table primary key is generated by splicing, including: according to a set primary key generation rule, the timestamp of the current time, the sub-table identifier and the instance identifier are spliced ​​to generate the sub-table primary key; wherein the primary key generation rule is provided with an actual digit interval occupied by a time series field, a sub-table identifier field and an instance identifier field in the sub-table primary key.

[0010] Optionally, the primary key generation rule is further provided with an actual digit interval of the serial number field in the sub-table primary key; and the sub-table primary key is generated by splicing according to the current time, the sub-table identifier and the instance identifier, including: generating a serial number based on the current time; and splicing the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number according to the primary key generation rule to generate the sub-table primary key.

[0011] Optionally, the method also includes: configuring a base digit range occupied by each field in the primary key generation rule based on a set total number of digits of the primary key; adjusting the base digit range occupied by each field in the primary key generation rule based on business needs to obtain an actual digit range occupied by each field.

[0012] Optionally, according to the primary key generation rule, the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number are spliced ​​to generate the sub-table primary key, including: generating a first bit sequence with a corresponding bit length according to the timestamp of the current time and a first actual bit number interval occupied by the time series field; generating a second bit sequence with a corresponding bit length according to the sub-table identifier and a second actual bit number interval occupied by the sub-table identifier field; generating a third bit sequence with a corresponding bit length according to the instance identifier and a third actual bit number interval occupied by the instance identifier field; generating a fourth bit sequence with a corresponding bit length according to the serial number and a fourth actual bit number interval occupied by the serial number field; splicing the first to fourth bit sequences, adding a sign bit before the highest bit of the splicing result, and obtaining the sub-table primary key.

[0013] To achieve the above objective, according to another aspect of an embodiment of the present invention, a database primary key generation device is provided.

[0014] A database primary key generation device according to an embodiment of the present invention includes: a first processing module, used to obtain a business field value of a target business field in an original data table, and use a set hash function to perform hash processing on the business field value to obtain a hash value; a second processing module, used to use the hash value to perform a modulo operation on a maximum value corresponding to a set sub-table identifier length to obtain a sub-table identifier; a primary key splicing module, used to obtain a current time and an instance identifier, and splice and generate a sub-table primary key according to the current time, the sub-table identifier and the instance identifier.

[0015] Optionally, the primary key splicing module is also used to: splice the timestamp of the current time, the sub-table identifier and the instance identifier according to the set primary key generation rule to generate the sub-table primary key; wherein the primary key generation rule is provided with the actual digit range occupied by the time series field, the sub-table identifier field and the instance identifier field in the sub-table primary key.

[0016] Optionally, the primary key generation rule is also provided with an actual digit range of the serial number field in the sub-table primary key; the primary key splicing module is further used to: generate a serial number based on the current time; and according to the primary key generation rule, splice the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number to generate the sub-table primary key.

[0017] Optionally, the device also includes: a configuration adjustment module, which is used to configure the base number of digits occupied by each field in the primary key generation rule according to the set total number of digits of the primary key; and adjust the base number of digits occupied by each field in the primary key generation rule according to business needs to obtain the actual number of digits occupied by each field.

[0018] Optionally, the primary key splicing module is also used to: generate a first bit sequence with a corresponding bit length according to the timestamp of the current time and a first actual bit number interval occupied by the time series field; generate a second bit sequence with a corresponding bit length according to the sub-table identifier and the second actual bit number interval occupied by the sub-table identifier field; generate a third bit sequence with a corresponding bit length according to the third actual bit number interval occupied by the instance identifier and the instance identifier field; generate a fourth bit sequence with a corresponding bit length according to the fourth actual bit number interval occupied by the serial number and the serial number field; splice the first to fourth bit sequences, add a sign bit before the highest bit of the splicing result, and obtain the sub-table primary key.

[0019] To achieve the above objective, according to another aspect of an embodiment of the present invention, a method for locating a sub-table is provided.

[0020] A sub-table locating method according to an embodiment of the present invention includes: obtaining a sub-table identifier from a sub-table primary key according to a set primary key generation rule; wherein the sub-table primary key is generated according to a database primary key generation method; obtaining the number of sub-tables, and performing a modulo operation on the number of sub-tables using the sub-table identifier to obtain a target sub-table.

[0021] Optionally, the primary key generation rule is provided with an actual digit interval occupied by the sub-table identification field in the sub-table primary key; obtaining the sub-table identification from the sub-table primary key according to the set primary key generation rule includes: determining the actual digit interval occupied by the sub-table identification field in the sub-table primary key according to the set primary key generation rule; obtaining a bit sequence corresponding to the actual digit interval from the sub-table primary key, wherein the bit sequence is the sub-table identification.

[0022] To achieve the above objective, according to another aspect of an embodiment of the present invention, a sub-meter positioning device is provided.

[0023] A sub-table locating device according to an embodiment of the present invention includes: an acquisition module, which is used to acquire a sub-table identifier from a sub-table primary key according to a set primary key generation rule; wherein the sub-table primary key is generated according to a database primary key generation method; and a calculation module, which is used to acquire the number of sub-tables, and use the sub-table identifier to perform a modulo operation on the number of sub-tables to obtain a target sub-table.

[0024] Optionally, the primary key generation rule is provided with an actual digit interval occupied by the sub-table identification field in the sub-table primary key; the acquisition module is further used to: determine the actual digit interval occupied by the sub-table identification field in the sub-table primary key according to the set primary key generation rule; obtain a bit sequence corresponding to the actual digit interval from the sub-table primary key, and the bit sequence is the sub-table identification.

[0025] To achieve the above objective, according to another aspect of an embodiment of the present invention, an electronic device is provided.

[0026] An electronic device according to an embodiment of the present invention includes: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement a database primary key generation method according to an embodiment of the present invention.

[0027] To achieve the above objective, according to another aspect of the embodiments of the present invention, a computer readable medium is provided.

[0028] A computer-readable medium according to an embodiment of the present invention stores a computer program, and when the program is executed by a processor, a method for generating a database primary key according to an embodiment of the present invention is implemented.

[0029] To achieve the above objective, according to another aspect of an embodiment of the present invention, an electronic device is provided.

[0030] An electronic device according to an embodiment of the present invention includes: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement a sub-table positioning method according to an embodiment of the present invention.

[0031] To achieve the above objective, according to another aspect of the embodiments of the present invention, a computer readable medium is provided.

[0032] A computer-readable medium according to an embodiment of the present invention stores a computer program, and when the program is executed by a processor, a method for locating a sub-table according to an embodiment of the present invention is implemented.

[0033] An embodiment of the above invention has the following advantages or beneficial effects: in the process of generating a primary key, a sub-table identification field is added so that the sub-table identification is independent of the sub-table implementation, ensuring that the primary key is stable and unchanged and globally unique during expansion or reduction; combining the primary key generation rule with the business field can not only generate a globally unique primary key, but also use the primary key to locate a specific table for query, thereby speeding up the query speed; by setting the actual number of digits occupied by the serial number in the primary key, it is ensured that a single instance can generate tens of thousands of primary keys per second to meet high concurrency requirements; adjusting the actual number of digits occupied by each field according to business requirements can efficiently generate a globally unique primary key that supports sub-library and sub-table; splicing each field according to the primary key generation rule to generate the sub-table primary key, thereby improving the efficiency of primary key generation, and performing data cleaning after the sub-table is expanded or reduced in capacity, the primary key will not change.

[0034] An embodiment of the above invention has the following advantages or beneficial effects: when using the primary key to locate a sub-table, the target sub-table can be determined according to the sub-table identifier and the number of sub-tables, and the sub-table to which the record belongs can be accurately located in reverse, and then the addition, deletion, modification and query operations can be performed according to the primary key; based on the actual number of digits occupied by the sub-table identifier field in the sub-table primary key in the primary key generation rule, the sub-table identifier is obtained, which further ensures the accuracy of reverse positioning of the sub-table to which the record belongs.

[0035] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with specific implementation examples. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0037] Figure 1 It is a schematic diagram of the principle of sub-library and sub-table in the prior art;

[0038] Figure 2is a schematic diagram of the main steps of a method for generating a database primary key according to an embodiment of the present invention;

[0039] Figure 3 It is a main flow chart of a method for generating a database primary key according to an embodiment of the present invention;

[0040] Figure 4 It is a schematic diagram of the structure of each field of a 64-bit primary key generated based on the primary key generation rule in an embodiment of the present invention;

[0041] Figure 5 is a schematic diagram of the main steps of the method for locating a sub-table according to an embodiment of the present invention;

[0042] Figure 6 is a schematic diagram of main modules of a database primary key generating device according to an embodiment of the present invention;

[0043] Figure 7 is a schematic diagram of main modules of a sub-meter positioning device according to an embodiment of the present invention;

[0044] Figure 8 is an exemplary system architecture diagram to which embodiments of the present invention may be applied;

[0045] Fig. 9 It is a schematic diagram of the structure of a computer device suitable for implementing an electronic device of an embodiment of the present invention. DETAILED DESCRIPTION

[0046] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0047] As described in the background technology, if the primary key of the sub-table is an auto-increment ID, since the auto-increment IDs between the data tables are independent of each other, the auto-increment IDs of different data tables are repeated and cannot be used as a universally unique identifier (UUID), and the original data table cannot be reversely located through the auto-increment ID. If you need to expand or shrink the capacity, you need to clean all the data, recalculate the hash value, and then migrate the data to the new target table.

[0048] If the primary key of the sub-table is generated using a snowflake-like algorithm, since a single instance in the snowflake-like algorithm can only generate a maximum of 1,000 IDs per second, many business scenarios cannot be met, and the ID generation efficiency is low; when calculating the sub-library and sub-table identifiers, it is necessary to predetermine the number of sub-libraries and sub-tables required. Once the sub-library and sub-table identifiers are determined, the number of libraries and tables cannot be changed. However, in actual applications, developers cannot accurately estimate the number of sub-tables required. As the business volume grows, the original number of sub-tables may no longer meet the existing data volume, and the sub-tables need to be expanded. However, after the actual number of libraries or tables changes, the calculated ID will also change. In order to solve the above problems, this embodiment proposes a method for generating a database primary key, which is as follows.

[0049] Figure 2 FIG. 1 is a schematic diagram of the main steps of a method for generating a database primary key according to an embodiment of the present invention. Figure 2 As shown, the method for generating a database primary key in an embodiment of the present invention mainly includes the following steps:

[0050] Step S201: Obtain the business field value of the target business field in the original data table, and use the set hash function to hash the business field value to obtain a hash value. The target business field of the original data table can be one or more. Assuming that the original data table is an order flow table, the target business field may include: item name, item type, item price, etc. The business field value of the target business field is mapped to an integer through the hash function as the hash value.

[0051] Step S202: Use the hash value to perform a modulo operation on the maximum value corresponding to the set sub-table identifier length to obtain the sub-table identifier. Set the sub-table identifier length according to business requirements, such as 1 bit, 10 bits, or 12 bits, and use the hash value to perform a modulo operation on the maximum value corresponding to the sub-table identifier length, that is, hash value % (the maximum value corresponding to the sub-table identifier length) to obtain the sub-table identifier.

[0052] When the length of the subtable identifier is 1 bit, the value range is only positive numbers from 0 to 2 1 , the maximum value is 2 1 , supports up to 2 1 = 2 sub-tables; when the sub-table identifier length is 10 bits, the value range is only positive numbers from 0 to 2 10 , the maximum value is 2 10 , supports up to 2 10 = 1024 sub-tables; when the sub-table identifier length is 12 bits, the value range is only positive numbers from 0 to 2 12 , the maximum value is 2 12 , supports up to 2 12 = 4096 sub-tables. If the database is divided, the above number of sub-tables is the upper limit of the sum of the number of sub-tables of all sub-databases.

[0053] Step S203: Get the current time and instance ID, and generate the sub-table primary key by splicing according to the current time, the sub-table ID and the instance ID. Based on the characteristic of automatic time growth, adding the time attribute to the sub-table primary key can make the data generated in different seconds on the same machine different. In addition, the distributed system includes multiple machines. In order to prevent the sub-table primary keys generated at the same time point on different machines from being the same, the embodiment uses instance IDs to identify different machines.

[0054] The timestamp of the current time, the table ID and the instance ID are concatenated into the table primary key, ensuring that the table primary keys generated on different machines at the same time are different. Since the table ID is added during the primary key generation process, the primary key is guaranteed to be stable and unchanged during expansion and contraction, and globally unique. The primary key here is the unique identifier corresponding to a record in the database table, and the record can be quickly found through the primary key; globally unique refers to the unique key shared by all sub-databases and sub-tables.

[0055] Figure 3 FIG. 1 is a schematic diagram of the main flow of the method for generating a database primary key according to an embodiment of the present invention. Figure 3 As shown, the method for generating a database primary key in an embodiment of the present invention mainly includes the following steps:

[0056] Step S301: Get the business field value of the target business field in the original data table, and use the set hash function to hash the business field value to obtain a hash value. Assuming that the target business field is the name field, and the business field value of the name field is Jane, "Jane" is mapped to the integer 1123010 through the hash function. The hash function can be an MD algorithm (Message-Digest Algorithm), a SHA algorithm (Secure Hash Algorithm), etc.

[0057] Step S302: Determine the sub-table identification length of the sub-table identification field according to the set primary key generation rule. The primary key generation rule is provided with the fields included in the primary key to be generated, and the actual number of digits interval occupied by each field. The fields included in the primary key can be set according to actual needs, but at least include a time series field, a sub-table identification field, and an instance identification field. In an embodiment, the primary key generation rule includes a time series field, a sub-table identification field, an instance identification field, and a serial number field, as well as the actual number of digits interval occupied by each field. The sub-table identification length is the bit length corresponding to the actual number of digits interval occupied by the sub-table identification field.

[0058] The time series field is used to store the timestamp of the current time, the sub-table identification field is used to store the sub-table identification, the instance identification field is used to store the instance identification, and the sequence number field is used to store the sequence number. After determining the total number of digits of the primary key, the reference digit interval occupied by each field is configured based on the total number of digits of the primary key, and then the reference digit interval is adjusted according to business needs to obtain the actual digit interval occupied by each field. Since the current server is basically 64 bits, in the embodiment, the total number of digits of the primary key is set to 64 bits.

[0059] The primary key is set to 64 bits, which has natural support for long type (64 bits). If the primary key is larger than 64 bits, such as directly using 128 bits, the data needs to be encapsulated, and both the execution efficiency and storage efficiency are lower than 64 bits. If a string is used as the primary key, there will be efficiency issues in terms of storage space, disk IO, and sequential storage.

[0060] Figure 4 Schematic diagram of the structure of each field of a 64-bit primary key generated based on the primary key generation rule according to an embodiment of the present invention. Figure 4 As shown, the first bit is the sign bit, which is set to 0 to ensure that the generated primary key is a positive number; the time series field occupies the 2nd to 32nd bits, a total of 31 bits, in seconds, and 2 31 Seconds ≈ 68 years, so it can be guaranteed that the generated primary key will not be repeated within 68 years; the sub-table identification field occupies 33rd to 42nd bits, a total of 10 bits, which can support 2 10 = 1024 sub-tables; the instance identification field occupies the 43rd to 50th bits, a total of 8 bits, and can support 256 instances at the same time; the sequence number field occupies the 51st to 64th bits, a total of 14 bits, and each instance can generate a maximum of 2 per second 14 = 16384 primary keys, which can meet the needs of most systems and improve the efficiency of primary key generation compared to the snowflake algorithm.

[0061] Among them, instance refers to the application of the database. The instance identifier in the primary key can ensure that the primary keys generated between different instances will never be repeated. The number of digits is divided into the base digit interval of each field. The division can be used directly for subsequent primary key generation, or it can be adjusted appropriately according to the actual application scenario. For example, if the distributed system needs to support 1,000 instance applications, the sub-table identification field or the serial number field needs to be reduced by 1 bit. For example, if the sub-table identification field is reduced by 1 bit, a maximum of 512 sub-tables can be supported; if the serial number field is reduced by 1 bit, 4,000 sub-table primary keys are generated per second for a single instance.

[0062] Step S303: Use the hash value to perform a modulo operation on the maximum value corresponding to the length of the sub-table identifier to obtain the sub-table identifier. For a certain original data table, the length of the sub-table identifier remains unchanged; for two or more different sets of original data tables, the length of the sub-table identifier of each original data table can be different. Still taking the name field as an example of the target business field of a certain original data table, the business field value is Jane, and the maximum value corresponding to the sub-table identifier length is 2 10 , then hash(Jane) = 1123010, subtable identifier = 1123010%2 10 =706. If the maximum value corresponding to the length of the subtable identifier is 2 9 , then the sub-table identification = 1123010%2 9 =194.

[0063] Step S304: Get the current time and instance ID, and generate a serial number based on the current time. Get the current time and instance ID of the system. The current time is in seconds; the instance ID needs to be pre-set in the distributed system, which can be an IP number + a random number, such as 192168011234 (192.168.0.1+1234). The serial number counts up from 0 every second. If the count limit is exceeded, wait for the next second.

[0064] Step S305: According to the primary key generation rule, the current time stamp, sub-table identifier, instance identifier and serial number are concatenated to generate the sub-table primary key. Figure 4 As shown, after converting the sign bit, the timestamp of the current time (such as the seconds of the current epoch timestamp), the sub-table identifier, the instance identifier and the serial number into a bit sequence of corresponding bit length, they are sequentially concatenated to obtain the sub-table primary key.

[0065] Specifically, a first bit sequence with a corresponding bit length is generated according to a first actual bit number interval occupied by a timestamp of the current time and a time series field; a second bit sequence with a corresponding bit length is generated according to a second actual bit number interval occupied by a sub-table identifier and a sub-table identifier field; a third bit sequence with a corresponding bit length is generated according to a third actual bit number interval occupied by an instance identifier and an instance identifier field; a fourth bit sequence with a corresponding bit length is generated according to a fourth actual bit number interval occupied by a sequence number and a sequence number field; finally, the first to fourth bit sequences are concatenated, a sign bit is added before the highest bit of the concatenated result, and a sub-table primary key is obtained.

[0066] It can be seen from the database primary key generation method of the embodiment of the present invention that a sub-table identifier calculated based on the sub-table identifier length is added in the primary key generation stage, which is independent of the number of sub-libraries and sub-tables, and ensures that the primary key remains stable when expanding or shrinking. For example, when expanding from 10 data tables to 100 data tables, data cleaning is required to reallocate all records of the original 10 data tables to 100 data tables, but the sub-table primary key of each record can remain unchanged.

[0067] Figure 5 FIG. 1 is a schematic diagram of the main steps of the method for locating a sub-table according to an embodiment of the present invention. Figure 5 As shown, the method for locating a sub-table in an embodiment of the present invention mainly includes the following steps:

[0068] Step S501: Obtain a sub-table identifier from a sub-table primary key according to a set primary key generation rule. The primary key generation rule sets the actual digit interval of the sub-table identifier field in the sub-table primary key. The sub-table primary key is generated according to the database primary key generation method described above.

[0069] In the embodiment, the actual digit interval of the sub-table identification field in the sub-table primary key can be determined according to the primary key generation rule; then the bit sequence corresponding to the actual digit interval is obtained from the sub-table primary key, and the bit sequence is the sub-table identification. Figure 4 The structure of each field of the primary key is shown. By taking the 33rd to 42nd bits from the sub-table primary key, you can get the sub-table identifier.

[0070] Step S502: Get the number of sub-tables, use the sub-table identifier to perform a modulo operation on the number of sub-tables, and get the target sub-table. Get the current actual number of sub-tables, use the sub-table identifier to perform a modulo operation on the number of sub-tables, that is, sub-table identifier % number of sub-tables, and get the target sub-table. This embodiment can directly locate a specific sub-library sub-table from the sub-table primary key, and then implement database deletion, modification, and query operations based on the sub-table primary key.

[0071] The above embodiment adopts a two-stage hashing method (i.e., step S303 and step S502), and places the sub-table identifier and the target sub-table in the database primary key generation stage and the sub-table primary key reverse routing stage respectively, so that the sub-table identifier is independent of the specific sub-table implementation. Regardless of expansion or reduction, the sub-table primary key can remain stable and can be accurately routed to the target sub-table.

[0072] Figure 6 Schematic diagram of the main modules of the database primary key generation device according to an embodiment of the present invention. Figure 6 As shown, the database primary key generation device 600 of the embodiment of the present invention mainly includes:

[0073] The first processing module 601 is used to obtain the business field value of the target business field in the original data table, and use the set hash function to hash the business field value to obtain a hash value. The target business field of the original data table can be one or more. Assuming that the original data table is an order flow table, the target business field may include: item name, item type, item price, etc. The business field value of the target business field is mapped to an integer through the hash function as the hash value.

[0074] The second processing module 602 is used to use the hash value to perform a modulo operation on the maximum value corresponding to the set sub-table identifier length to obtain the sub-table identifier. The sub-table identifier length is set according to business requirements, such as 1 bit, 10 bits, and 12 bits. The hash value is used to perform a modulo operation on the maximum value corresponding to the sub-table identifier length, that is, the hash value % (the maximum value corresponding to the sub-table identifier length) to obtain the sub-table identifier.

[0075] When the length of the subtable identifier is 1 bit, the value range is only positive numbers from 0 to 2 1 , the maximum value is 2 1 , supports up to 2 1 = 2 sub-tables; when the sub-table identifier length is 10 bits, the value range is only positive numbers from 0 to 2 10 , the maximum value is 2 10 , supports up to 2 10 = 1024 sub-tables; when the sub-table identifier length is 12 bits, the value range is only positive numbers from 0 to 2 12 , the maximum value is 2 12 , supports up to 2 12 = 4096 sub-tables. If the database is divided, the above number of sub-tables is the upper limit of the sum of the number of sub-tables of all sub-databases.

[0076] The primary key concatenation module 603 is used to obtain the current time and the instance identifier, and concatenate and generate the primary key of the sub-table according to the current time, the sub-table identifier and the instance identifier. Based on the characteristic of automatic time growth, adding the time attribute to the primary key of the sub-table can make the data generated in different seconds on the same machine different. In addition, the distributed system includes multiple machines. In order to prevent the primary keys of the sub-tables generated at the same time point on different machines from being the same, the embodiment uses the instance identifier to identify different machines.

[0077] The timestamp of the current time, the table ID and the instance ID are concatenated into the table primary key to ensure that the table primary keys generated on different machines at the same time are different. In addition, since the table ID is added during the primary key generation process, the primary key is stable and unchanged when the capacity is expanded or reduced, and is globally unique. The primary key here is the unique identifier corresponding to a record in the database table, and the record can be quickly found through the primary key; globally unique refers to the unique key shared by all sub-databases and sub-tables.

[0078] In addition, the database primary key generation device 600 of the embodiment of the present invention may also include: a configuration adjustment module ( Figure 6 (not shown in the figure), this module is used to configure the reference digit interval occupied by each field in the primary key generation rule according to the set total number of digits of the primary key; and to adjust the reference digit interval occupied by each field in the primary key generation rule according to business needs to obtain the actual digit interval occupied by each field.

[0079] From the above description, we can know that in the process of generating the primary key, a sub-table identification field is added so that the sub-table identification is independent of the sub-table implementation, ensuring that the primary key is stable and unchanged and globally unique when the capacity is expanded or reduced.

[0080] Figure 7 Schematic diagram of the main modules of the sub-meter positioning device according to an embodiment of the present invention. Figure 7 The sub-table positioning device 700 of the embodiment of the present invention mainly includes:

[0081] The acquisition module 701 is used to acquire the sub-table identifier from the sub-table primary key according to the set primary key generation rule. The primary key generation rule is set with the actual digit interval of the sub-table identifier field in the sub-table primary key. The sub-table primary key is generated according to the database primary key generation method described above.

[0082] In the embodiment, the actual digit interval of the sub-table identification field in the sub-table primary key can be determined according to the primary key generation rule; then the bit sequence corresponding to the actual digit interval is obtained from the sub-table primary key, and the bit sequence is the sub-table identification. Figure 4 The structure of each field of the primary key is shown. By taking the 33rd to 42nd bits from the sub-table primary key, you can get the sub-table identifier.

[0083] The calculation module 702 is used to obtain the number of sub-tables, and use the sub-table identifier to perform a modulo operation on the number of sub-tables to obtain the target sub-table. The current actual number of sub-tables is obtained, and the sub-table identifier is used to perform a modulo operation on the number of sub-tables, that is, sub-table identifier % sub-table number, to obtain the target sub-table. This embodiment can directly locate a specific sub-library sub-table from the sub-table primary key, and then implement database deletion, modification and query operations based on the sub-table primary key.

[0084] Figure 8 An exemplary system architecture 800 is shown to which the method or device for generating a database primary key according to an embodiment of the present invention can be applied.

[0085] like Figure 8As shown, system architecture 800 may include terminal devices 801, 802, 803, a network 804, and a server 805. Network 804 is used to provide a medium for communication links between terminal devices 801, 802, 803 and server 805. Network 804 may include various connection types, such as wired, wireless communication links, or optical fiber cables, etc.

[0086] Users can use terminal devices 801, 802, 803 to interact with server 805 through network 804 to receive or send messages, etc. Terminal devices 801, 802, 803 can be various electronic devices with display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, etc.

[0087] The server 805 may be a server that provides various services, such as a background management server that processes the primary key generation request sent by the administrator using the terminal devices 801, 802, and 803. The background management server may obtain the corresponding business field value in response to the primary key generation request, perform hash processing, modulus operation, and concatenation processing on the business field value, and feed back the processing result (such as the generated sub-table primary key) to the terminal device.

[0088] It should be noted that the database primary key generation method provided in the embodiment of the present application is generally executed by the server 805 , and accordingly, the database primary key generation device is generally set in the server 805 .

[0089] It should be understood that Figure 8 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0090] According to an embodiment of the present invention, the present invention also provides an electronic device and a computer-readable medium.

[0091] The electronic device of the present invention comprises: one or more processors; a storage device for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement a database primary key generation method of an embodiment of the present invention.

[0092] The computer-readable medium of the present invention stores a computer program, and when the program is executed by a processor, a method for generating a database primary key according to an embodiment of the present invention is implemented.

[0093] Reference below Fig. 9 , which shows a schematic diagram of the structure of a computer system 900 suitable for implementing an electronic device of an embodiment of the present invention. Fig. 9The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0094] like Fig. 9 As shown, the computer system 900 includes a central processing unit (CPU) 901, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 902 or a program loaded from a storage part 908 into a random access memory (RAM) 903. In the RAM 903, various programs and data required for the operation of the computer system 900 are also stored. The CPU 901, the ROM 902, and the RAM 903 are connected to each other via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0095] The following components are connected to the I / O interface 905: an input section 906 including a keyboard, a mouse, etc.; an output section 907 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 908 including a hard disk, etc.; and a communication section 909 including a network interface card such as a LAN card, a modem, etc. The communication section 909 performs communication processing via a network such as the Internet. A drive 910 is also connected to the I / O interface 905 as needed. A removable medium 911, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 910 as needed, so that a computer program read therefrom is installed into the storage section 908 as needed.

[0096] In particular, according to the embodiments disclosed in the present invention, the process described in the main step diagram above can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the main step diagram. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 909, and / or installed from the removable medium 911. When the computer program is executed by the central processing unit (CPU) 901, the above-mentioned functions defined in the system of the present invention are executed.

[0097] It should be noted that the computer-readable medium shown in the present invention may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device. In the present invention, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0098] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0099] The modules involved in the embodiments of the present invention may be implemented by software or hardware. The modules described may also be set in a processor. For example, they may be described as: a processor includes a first processing module, a second processing module, and a primary key concatenation module. The names of these modules do not, in some cases, constitute limitations on the modules themselves. For example, the first processing module may also be described as a module that "obtains the business field value of the target business field in the original data table, performs hash processing on the business field value using a set hash function, and obtains a hash value."

[0100] As another aspect, the present invention also provides a computer-readable medium, which may be included in the device described in the above embodiment; or it may exist independently without being assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device includes: obtaining the business field value of the target business field in the original data table, using a set hash function to hash the business field value to obtain a hash value; using the hash value to perform a modulo operation on the maximum value corresponding to the set sub-table identifier length to obtain a sub-table identifier; obtaining the current time and the instance identifier, and splicing and generating a sub-table primary key according to the current time, the sub-table identifier and the instance identifier.

[0101] According to the technical solution of the embodiment of the present invention, in the process of generating the primary key, a sub-table identification field is added so that the sub-table identification is independent of the sub-table implementation, thereby ensuring that the primary key is stable and unchanged and globally unique during expansion or reduction.

[0102] The above product can execute the method provided by the embodiment of the present invention, and has the functional modules and beneficial effects corresponding to the execution method. For technical details not described in detail in this embodiment, please refer to the method provided by the embodiment of the present invention.

[0103] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for generating a database primary key, characterized in that: include: Obtain the business field value of the target business field in the original data table, and use a set hash function to perform hash processing on the business field value to obtain a hash value; According to the set primary key generation rule, determine the sub-table identification length of the sub-table identification field; wherein the primary key generation rule is set with the actual digit interval occupied by the time series field, the sub-table identification field and the instance identification field in the sub-table primary key, and the sub-table identification length is the bit length corresponding to the actual digit interval occupied by the sub-table identification field; Using the hash value to perform a modulo operation on the maximum value corresponding to the length of the sub-table identifier, to obtain a sub-table identifier; Obtain the current time and the instance identifier, and generate a sub-table primary key based on the current time, the sub-table identifier and the instance identifier; Generate a sub-table primary key by concatenating the current time, the sub-table identifier, and the instance identifier, including: According to the primary key generation rule, the timestamp of the current time, the sub-table identifier and the instance identifier are concatenated to generate the sub-table primary key.

2. The method according to claim 1, characterized in that: The primary key generation rule also sets the actual digit interval of the serial number field in the primary key of the sub-table; Generate a sub-table primary key by concatenating the current time, the sub-table identifier, and the instance identifier, including: generating a sequence number based on the current time; According to the primary key generation rule, the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number are concatenated to generate the sub-table primary key.

3. The method according to claim 1 or 2, characterized in that: The method further comprises: According to the set total number of digits of the primary key, the reference digit interval occupied by each field is configured in the primary key generation rule; According to business requirements, the reference digit interval occupied by each field in the primary key generation rule is adjusted to obtain the actual digit interval occupied by each field.

4. The method according to claim 2, characterized in that: According to the primary key generation rule, the timestamp of the current time, the sub-table identifier, the instance identifier and the sequence number are concatenated to generate the sub-table primary key, including: Generate a first bit sequence with a corresponding bit length according to the timestamp of the current time and a first actual bit interval occupied by the time series field; Generate a second bit sequence with a corresponding bit length according to the sub-table identifier and a second actual bit interval occupied by the sub-table identifier field; Generate a third bit sequence with a corresponding bit length according to the instance identifier and a third actual bit number interval occupied by the instance identifier field; Generate a fourth bit sequence having a corresponding bit length according to the sequence number and a fourth actual bit number interval occupied by the sequence number field; The first to fourth bit sequences are concatenated, and a sign bit is added before the highest bit of the concatenated result to obtain the sub-table primary key.

5. A method for locating a sub-table, characterized in that: include: According to the set primary key generation rule, the sub-table identifier is obtained from the sub-table primary key; wherein the sub-table primary key is generated according to the method described in any one of claims 1 to 4; The number of sub-tables is obtained, and a modulo operation is performed on the number of sub-tables using the sub-table identifier to obtain a target sub-table.

6. The method according to claim 5, characterized in that The primary key generation rule is provided with an actual digit interval of the sub-table identification field in the primary key of the sub-table; According to the set primary key generation rules, obtain the sub-table identifier from the sub-table primary key, including: According to the set primary key generation rule, determine the actual digit interval occupied by the sub-table identification field in the sub-table primary key; A bit sequence corresponding to the actual digit interval is obtained from the sub-table primary key, where the bit sequence is the sub-table identifier.

7. A database primary key generation device, characterized in that: include: The first processing module is used to obtain the business field value of the target business field in the original data table, and perform hash processing on the business field value using a set hash function to obtain a hash value; The second processing module is used to determine the sub-table identification length of the sub-table identification field according to the set primary key generation rule; wherein the primary key generation rule is set with the actual digit interval occupied by the time series field, the sub-table identification field and the instance identification field in the sub-table primary key, and the sub-table identification length is the bit length corresponding to the actual digit interval occupied by the sub-table identification field; Using the hash value to perform a modulo operation on the maximum value corresponding to the length of the sub-table identifier, to obtain a sub-table identifier; The primary key splicing module is used to obtain the current time and the instance identifier, and to splice and generate the sub-table primary key according to the current time, the sub-table identifier and the instance identifier; the primary key splicing module is also used to: According to the primary key generation rule, the timestamp of the current time, the sub-table identifier and the instance identifier are concatenated to generate the sub-table primary key.

8. A sub-meter positioning device, characterized in that: include: An acquisition module, used to acquire a sub-table identifier from a sub-table primary key according to a set primary key generation rule; wherein the sub-table primary key is generated according to the method of any one of claims 1 to 4; The calculation module is used to obtain the number of sub-tables, and use the sub-table identifier to perform a modulo operation on the number of sub-tables to obtain a target sub-table.

9. An electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 6.

10. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Citation Information

Patent Citations

  • Sub-table data processing method and device

    CN110866002A