CN113704245A - Database main key generation method, sub-table positioning method and device - Google Patents
Database main key generation method, sub-table positioning method and device Download PDFInfo
- Publication number
- CN113704245A CN113704245A CN202010441717.5A CN202010441717A CN113704245A CN 113704245 A CN113704245 A CN 113704245A CN 202010441717 A CN202010441717 A CN 202010441717A CN 113704245 A CN113704245 A CN 113704245A
- Authority
- CN
- China
- Prior art keywords
- sub
- field
- primary key
- identifier
- key generation
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 69
- 238000012545 processing Methods 0.000 claims abstract description 27
- 238000004590 computer program Methods 0.000 claims description 11
- 238000004364 calculation method Methods 0.000 claims description 2
- 230000008569 process Effects 0.000 abstract description 11
- 230000009467 reduction Effects 0.000 abstract description 11
- 238000010586 diagram Methods 0.000 description 21
- 230000006870 function Effects 0.000 description 17
- 238000004422 calculation algorithm Methods 0.000 description 9
- 238000004891 communication Methods 0.000 description 6
- 230000004048 modification Effects 0.000 description 5
- 238000012986 modification Methods 0.000 description 5
- 230000008901 benefit Effects 0.000 description 4
- 230000003287 optical effect Effects 0.000 description 4
- 238000004140 cleaning Methods 0.000 description 2
- 238000012217 deletion Methods 0.000 description 2
- 230000037430 deletion Effects 0.000 description 2
- 239000000835 fiber Substances 0.000 description 2
- 238000013507 mapping Methods 0.000 description 2
- 230000000644 propagated effect Effects 0.000 description 2
- 239000004065 semiconductor Substances 0.000 description 2
- 238000009825 accumulation Methods 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000010276 construction Methods 0.000 description 1
- 230000008602 contraction Effects 0.000 description 1
- 238000013461 design Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005538 encapsulation Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 239000004973 liquid crystal related substance Substances 0.000 description 1
- 239000013307 optical fiber Substances 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2255—Hash tables
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a database primary key generation method, a sub-table positioning method and a sub-table positioning device, and relates to the technical field of databases. One embodiment of the method comprises: acquiring a service field value of a target service field in an original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value; performing modular operation on the maximum value corresponding to the set sub-table identification length by using the hash value to obtain a sub-table identification; and acquiring the current time and the instance identification, and splicing and generating the sub-table main key according to the current time, the sub-table identification and the instance identification. According to the database main key generation method, the sub-table identification field is added in the main key generation process, so that the sub-table identification is realized independently of the sub-table, and the main key is stable and unchanged and is globally unique during capacity expansion and capacity reduction.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a database primary key generation method, a sub-table positioning method and a sub-table positioning device.
Background
When a database is created, the data tables in the database and the data volume of the data tables are increased along with the increase of the service volume. Due to the limited storage resources of the database, the processing capacity of the database is reduced due to the large accumulation of data, and the processing overhead is increased. In order to improve the storage capacity and the query efficiency, the data stored in one database is generally distributed into a plurality of databases, and the data stored in one data table is distributed into a plurality of data tables with the same structure according to a certain rule, which is called as a database-based sub-table (as shown in fig. 1).
There are various rules for table division, such as performing table division according to data service time dimension, performing table division according to single table data volume, performing hash (hash) table division according to one or more service fields, and the like. In order to uniquely identify each row record of the sublist, a primary key needs to be set for the sublist, which in the prior art can be a self-increment identification (Id) and can also be generated by using a snowflake-like algorithm.
In the process of implementing the invention, the inventor finds that at least the following problems exist in the prior art:
if the primary key is the self-increment Id, the self-increment Id among different tables is repeated, and the sublist to which the record belongs cannot be reversely positioned; if the primary key is generated by a snowflake-like algorithm, the generation efficiency is low, and when the sub-library and sub-table identification is calculated, the number of the sub-libraries and the sub-tables required to be determined in advance is needed, and once the sub-library and sub-table identification is determined, the number of the libraries and the tables cannot be changed, and the capacity expansion and the capacity reduction cannot be flexibly performed.
Disclosure of Invention
In view of this, embodiments of the present invention provide a database primary key generation method, a sub-table positioning method, and a device, in which a sub-table identification field is added in a primary key generation process, so that a sub-table identification is implemented independently of a sub-table, and it is ensured that a primary key is stable and unchanged and is globally unique during capacity expansion and capacity reduction.
To achieve the above object, according to an aspect of an embodiment of the present invention, a database primary key generation method is provided.
The embodiment of the invention provides a database primary key generation method, which comprises the following steps: acquiring a service field value of a target service field in an original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value; performing modular operation on the maximum value corresponding to the set sub-table identification length by using the hash value to obtain a sub-table identification; and acquiring the current time and the instance identification, and splicing and generating the sub-table main key according to the current time, the sub-table identification and the instance identification.
Optionally, generating a sub-table primary key by splicing according to the current time, the sub-table identifier, and the instance identifier, includes: according to a set main key generation rule, splicing the timestamp of the current time, the sub-table identifier and the instance identifier to generate a sub-table main key; the main key generation rule is provided with a time sequence field, a sub-table identification field and an actual digit interval occupied by the example identification field in the sub-table main key.
Optionally, the primary key generation rule further sets an actual digit interval occupied by the sequence number field in the sub-table primary key; according to the current time, the sub-table identifier and the instance identifier, splicing to generate a sub-table main key, comprising: generating a sequence number based on the current time; and according to the primary key generation rule, splicing the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number to generate the sub-table primary key.
Optionally, the method further comprises: configuring a reference digit interval occupied by each field in the primary key generation rule according to the set total digits of the primary keys; and adjusting the reference digit interval occupied by each field in the primary key generation rule according to the service requirement to obtain the actual digit interval occupied by each field.
Optionally, according to the primary key generation rule, concatenating the timestamp of the current time, the sub-table identifier, the instance identifier, and the sequence number to generate the sub-table primary key, including: generating a first bit sequence with corresponding bit length according to the timestamp of the current time and a first actual bit interval occupied by the time sequence field; generating a second bit sequence with corresponding bit length according to the sub-table identification and a second actual digit interval occupied by the sub-table identification field; generating a third bit sequence with corresponding bit length according to the example identifier and a third actual digit interval occupied by the example identifier field; generating a fourth bit sequence with corresponding bit length according to the sequence number and a fourth actual bit interval occupied by the sequence number field; and splicing the first bit sequence to the fourth bit sequence, and adding a sign bit in front of the highest bit of a splicing result to obtain the branch table main key.
To achieve the above object, according to another aspect of the embodiments of the present invention, there is provided a database primary key generation apparatus.
The embodiment of the invention provides a database primary key generation device, which comprises: the first processing module is used for acquiring a service field value of a target service field in an original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value; the second processing module is used for carrying out modular operation on the maximum value corresponding to the set sub-table identifier length by using the hash value to obtain the sub-table identifier; and the primary key splicing module is used for acquiring the current time and the instance identifier and splicing and generating the sub-table primary key according to the current time, the sub-table identifier and the instance identifier.
Optionally, the primary key splicing module is further configured to: according to a set main key generation rule, splicing the timestamp of the current time, the sub-table identifier and the instance identifier to generate a sub-table main key; the main key generation rule is provided with a time sequence field, a sub-table identification field and an actual digit interval occupied by the example identification field in the sub-table main key.
Optionally, the primary key generation rule further sets an actual digit interval occupied by the sequence number field in the sub-table primary key; the primary key splicing module is further configured to: generating a sequence number based on the current time; and according to the primary key generation rule, splicing the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number to generate the sub-table primary key.
Optionally, the apparatus further comprises: the configuration adjusting module is used for configuring a reference digit interval occupied by each field in the primary key generation rule according to the set total digits of the primary keys; and adjusting the reference digit interval occupied by each field in the primary key generation rule according to the service requirement to obtain the actual digit interval occupied by each field.
Optionally, the primary key splicing module is further configured to: generating a first bit sequence with corresponding bit length according to the timestamp of the current time and a first actual bit interval occupied by the time sequence field; generating a second bit sequence with corresponding bit length according to the sub-table identification and a second actual digit interval occupied by the sub-table identification field; generating a third bit sequence with corresponding bit length according to the example identifier and a third actual digit interval occupied by the example identifier field; generating a fourth bit sequence with corresponding bit length according to the sequence number and a fourth actual bit interval occupied by the sequence number field; and splicing the first bit sequence to the fourth bit sequence, and adding a sign bit in front of the highest bit of a splicing result to obtain the branch table main key.
To achieve the above object, according to still another aspect of an embodiment of the present invention, a table-splitting positioning method is provided.
The invention provides a sub-table positioning method, which comprises the following steps: according to a set main key generation rule, acquiring a sub-table identifier from a sub-table main key; the sub-table main key is generated according to a database main key generation method; and acquiring the quantity of the sub-tables, and performing modular operation on the quantity of the sub-tables by using the sub-table identification to obtain the target sub-tables.
Optionally, the primary key generation rule is provided with an actual digit interval occupied by a sub-table identification field in the sub-table primary key; according to a set primary key generation rule, obtaining a sub-table identifier from a sub-table primary key, comprising: determining the actual digit interval occupied by the sub-table main key in the sub-table identification field according to a set main key generation rule; and acquiring a bit sequence corresponding to the actual digit interval from the sub-table main key, wherein the bit sequence is a sub-table identifier.
To achieve the above object, according to another aspect of the embodiments of the present invention, a table-dividing positioning device is provided.
The invention provides a sub-table positioning device, comprising: the acquisition module is used for acquiring the sub-table identification from the sub-table main key according to the set main key generation rule; the sub-table main key is generated according to a database main key generation method; and the calculation module is used for acquiring the quantity of the sub-tables, and performing modular operation on the quantity of the sub-tables by using the sub-table identification to obtain the target sub-table.
Optionally, the primary key generation rule is provided with an actual digit interval occupied by a sub-table identification field in the sub-table primary key; an acquisition module further configured to: determining the actual digit interval occupied by the sub-table main key in the sub-table identification field according to a set main key generation rule; and acquiring a bit sequence corresponding to the actual digit interval from the sub-table main key, wherein the bit sequence is a sub-table identifier.
To achieve the above object, according to still another aspect of embodiments of the present invention, there is provided an electronic apparatus.
An electronic device of an embodiment of the present invention includes: one or more processors; a storage device, configured to store one or more programs, which when executed by the one or more processors, cause the one or more processors to implement a database primary key generation method according to an embodiment of the present invention.
To achieve the above object, according to still another aspect of embodiments of the present invention, there is provided a computer-readable medium.
A computer-readable medium of an embodiment of the present invention has a computer program stored thereon, which, when executed by a processor, implements a database primary key generation method of an embodiment of the present invention.
To achieve the above object, according to still another aspect of embodiments of the present invention, there is provided an electronic apparatus.
An electronic device of an embodiment of the present invention includes: one or more processors; the storage device is used 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 realize the table-based positioning method of the embodiment of the invention.
To achieve the above object, according to still another aspect of embodiments of the present invention, there is provided a computer-readable medium.
A computer-readable medium of an embodiment of the present invention stores thereon a computer program, which when executed by a processor implements a table-splitting positioning method of an embodiment of the present invention.
One embodiment of the above invention has the following advantages or benefits: in the process of generating the main key, adding a sub-table identification field to enable the sub-table identification to be realized independently of the sub-table, and ensuring that the main key is stable and unchanged and is globally unique during capacity expansion and capacity reduction; the primary key generation rule is combined with the service field, so that a globally unique primary key can be generated, the primary key can be positioned in a specific table for query, and the query speed is increased; by setting the sequence number in the actual digit interval occupied by the primary key, the single instance can generate tens of thousands of primary keys per second, and high concurrency requirements are met; the actual digit interval of each field is adjusted according to the business requirement, and a global unique main key supporting sub-base sub-tables can be efficiently generated; and the fields are spliced according to the main key generation rule to generate the sub-table main keys, so that the main key generation efficiency is improved, and the main keys cannot be changed by performing data cleaning after the sub-tables are subjected to capacity expansion and contraction.
One embodiment of the above invention has the following advantages or benefits: when the main key is used for sub-table positioning, the target sub-table can be determined according to the sub-table identification and the sub-table number, the sub-table to which the record belongs can be accurately positioned in the reverse direction, and then the operation of increasing, deleting, modifying and checking is carried out according to the main key; and the sub-table identification is obtained based on the actual digit interval occupied by the sub-table main key in the sub-table identification field in the main key generation rule, so that the accuracy of the sub-table to which the reverse positioning record belongs is further ensured.
Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.
Drawings
The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:
FIG. 1 is a schematic diagram of a database and a table in the prior art;
FIG. 2 is a schematic diagram of the main steps of a database primary key generation method according to an embodiment of the present invention;
FIG. 3 is a schematic diagram of a main flow of a database primary key generation method according to an embodiment of the present invention;
FIG. 4 is a diagram illustrating the structure of each field of a 64-bit primary key generated based on a primary key generation rule according to an embodiment of the present invention;
FIG. 5 is a schematic diagram of the main steps of a sub-table locating method according to an embodiment of the present invention;
FIG. 6 is a schematic diagram of the main blocks of a database primary key generation apparatus according to an embodiment of the present invention;
FIG. 7 is a schematic diagram of the main modules of a sub-table locating apparatus according to an embodiment of the present invention;
FIG. 8 is an exemplary system architecture diagram in which embodiments of the present invention may be employed;
FIG. 9 is a schematic diagram of a computer apparatus suitable for use in an electronic device to implement an embodiment of the invention.
Detailed Description
Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.
As described in the background art, if the primary key of the sub-table is the self-increment Id, the self-increment ids of different data tables are mutually independent, so that the self-increment ids of different data tables are duplicated and cannot be used as Universal Unique Identifiers (UUIDs), and the original data table cannot be positioned in the reverse direction through the self-increment ids. If capacity expansion and capacity reduction are needed, all data need to be cleaned, the hash value is recalculated, and then the data are migrated to a new target table.
If the primary key of the sublist is generated by using a snowflake-like algorithm, because a single instance in the snowflake-like algorithm can only generate 1000 Ids at most every second, a plurality of service scenes can not be satisfied, and the Id generation efficiency is low; when the sub-database sub-table identifiers are calculated, the number of the required sub-databases and the number of the tables cannot be changed once the sub-database sub-table identifiers are determined, but development personnel cannot accurately estimate the number of the required sub-tables in practical application, the original number of the sub-tables possibly does not meet the existing data volume any more along with the increase of the traffic volume, the capacity of the tables needs to be expanded, and the calculated Id can be changed after the actual number of the databases or the tables is changed. In order to solve the above problem, the present embodiment proposes a database primary key generation method, which is as follows.
Fig. 2 is a schematic diagram of the main steps of a database primary key generation method according to an embodiment of the present invention. As shown in fig. 2, the method for generating a primary key of a database according to an embodiment of the present invention mainly includes the following steps:
step S201: and acquiring the service field value of the target service field in the original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value. The target service field of the original data table may be one or more. Assuming the original data table is a subscription flow meter, the target service field may include: item name, item type, item price, etc. And mapping the service field value of the target service field into an integer through a hash function to be used as a hash value.
Step S202: and performing modular operation on the maximum value corresponding to the set sub-table identification length by using the hash value to obtain the sub-table identification. Setting the length of the sub-table identifier according to the service requirement, such as 1bit, 10bit and 12bit, and performing modulo operation on the maximum value corresponding to the length of the sub-table identifier by using the hash value, namely the hash value (the maximum value corresponding to the length of the sub-table identifier) to obtain the sub-table identifier.
When the length of the sub-table identifier is 1bit, the value range only calculates the positive number to be 0-21Maximum value of 21At most support 212 branch tables; when the length of the sub-table identifier is 10 bits, the value range only calculates the positive number to be 0-210Maximum value of 210At most support 2101024 branch tables; when the length of the sub-table identifier is 12 bits, the value range only calculates the positive number to be 0-212Maximum value of 212At most support 2124096 sub-tables. If the database division is carried out, the number of the branch tables is the upper limit of the sum of the number of the branch tables of all the database division.
Step S203: and acquiring the current time and the instance identification, and splicing and generating the sub-table main key according to the current time, the sub-table identification and the instance identification. The addition of a time attribute to the sub-table primary key, based on the nature of automatic time growth, can cause data generated in different seconds on the same machine to be different. In addition, the distributed system includes multiple machines, and 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 instance identification to identify different machines.
The time stamp, the sub-table identification and the instance identification of the current time are spliced into the sub-table main key, so that the sub-table main keys generated on different machines at the same time point are different, and the main key is ensured to be stable and unchanged during capacity expansion and capacity reduction and to be globally unique due to the addition of the sub-table identification in the main key generation process. The primary key is a unique identifier corresponding to one record in the database table, and the record can be quickly searched through the primary key; globally unique refers to a unique key common to all the sublibraries.
Fig. 3 is a schematic main flow chart of a database primary key generation method according to an embodiment of the present invention. As shown in fig. 3, the method for generating a primary key of a database according to an embodiment of the present invention mainly includes the following steps:
step S301: and acquiring the service field value of the target service field in the original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value. Assuming that the target service field is a name field and the service field value of the name field is Jane, "Jane" is mapped to 1123010 by a hash function. The Hash function may be an MD Algorithm (Message-Digest Algorithm), an SHA Algorithm (Secure Hash Algorithm), or the like.
Step S302: and determining the sub-table identification length of the sub-table identification field according to the set main key generation rule. The primary key generation rule is provided with fields contained in the primary key to be generated and an actual digit interval occupied by each field. The fields contained in the primary key can be set according to actual requirements, but at least comprise a time sequence field, a sub-table identification field and an instance identification field. In an embodiment, the primary key generation rule includes a time sequence field, a sub-table identification field, an instance identification field, a sequence number field, and an actual digit interval occupied by each field. The sub-table identification length is the bit length corresponding to the actual bit number interval occupied by the sub-table identification field.
The time sequence field is used for storing the time stamp of the current time, the sub-table identification field is used for storing the sub-table identification, the example identification field is used for storing the example identification, and the sequence number field is used for storing the sequence number. After the total number of the main keys is determined, a reference digit interval occupied by each field is configured based on the total number of the main keys, and then the reference digit interval is adjusted according to business requirements, so that an actual digit interval occupied by each field can be obtained. Since the current server is basically 64 bits, in the embodiment, the total number of the primary keys is set to 64 bits.
The primary key is set to 64 bits, which is naturally supported by long type (64 bits). If the primary key is larger than 64 bits, such as 128 bits directly, then one layer of encapsulation of the data is required, which is less than 64 bits in both execution efficiency and storage efficiency. If a character string is used as a primary key, there is a problem in efficiency with respect to the storage space, the disk IO, and the sequential storage.
Fig. 4 is a schematic diagram of the structure of each field of the 64-bit primary key generated based on the primary key generation rule according to the embodiment of the present invention. As shown in fig. 4, the 1 st bit is a sign bit and is set to 0, so that the generated primary key is guaranteed to be a positive number; the time series field is composed of bits 2-32, 31 bits in seconds, and 231The second is approximately equal to 68 years, so that the generated main key is not repeated within 68 years; the sub-table identification field occupies 33 th to 42 th bits, has 10 bits in total and can support 2101024 branch tables; instance identification field takes the place ofThe number of the bits is 43-50, and the total number of the bits is 8, so that 256 instances can be simultaneously supported; the sequence number field is composed of bits 51-64 and 14 bits, and each example can generate 2 at most per second1416384 main keys can meet the requirements of most systems, and compared with a snowflake-like algorithm, the main key generation efficiency is improved.
Wherein an instance refers to an application of a database. The primary key with instance identification ensures that the primary key generated between different instances never repeats. The digit is divided into reference digit intervals of each field, and the division can be directly used for subsequent primary key generation, or the primary key generation can be performed after appropriate adjustment according to the actual application scene. For example, if the distributed system needs to support 1000 instance applications, the sub-table identification field or the sequence number field needs to be reduced by 1 bit. For example, if the sub-table identification field is reduced by 1bit, only 512 sub-tables can be supported at most; the sequence number field is decremented by 1bit, then a single instance per second yields 4000 sub-table primary keys.
Step S303: and carrying out modular operation on the maximum value corresponding to the length of the sub-table identifier by using the hash value to obtain the sub-table identifier. For a certain original data table, the length of the sub-table identifier is kept unchanged; for two or more different sets of original data tables, the length of the sub-table identifier of each original data table may be different. Still take the target service field of some original data table as name field, the service field value is Jane, the maximum value corresponding to the sub-table identification length is 210Then hash (jane) 1123010, sub-table id 1123010% 210706. If the maximum value corresponding to the sub-table identification length is 29If the sub-table identifier is 1123010% 29＝194。
Step S304: and acquiring the current time and the instance identifier, and generating a serial number based on the current time. And acquiring the current time and the instance identification of the system. Wherein the current time is in seconds; the instance id needs to be preset in the distributed system, and may be IP number + random number, such as 192168011234(192.168.0.1+ 1234). The sequence number is counted up from 0 every second, and if the upper limit of the count is exceeded, the next second is waited.
Step S305: and according to the main key generation rule, splicing the timestamp, the sub-table identifier, the instance identifier and the serial number of the current time to generate a sub-table main key. As shown in fig. 4, the sign bit, the timestamp of the current time (e.g., seconds of the current epoch timestamp), the sub-table identifier, the instance identifier, and the serial number are converted into a bit sequence with a corresponding bit length, and then sequentially concatenated to obtain the sub-table primary key.
Specifically, a first bit sequence with a corresponding bit length is generated according to a timestamp of the current time and a first actual bit interval occupied by a time sequence field; generating a second bit sequence with corresponding bit length according to the sub-table identification and a second actual digit interval occupied by the sub-table identification field; generating a third bit sequence with corresponding bit length according to the example identifier and a third actual bit interval occupied by the example identifier field; generating a fourth bit sequence with corresponding bit length according to the sequence number and a fourth actual digit interval occupied by the sequence number field; and finally, splicing the first bit sequence, the second bit sequence, the third bit sequence and the fourth bit sequence, and adding a sign bit in front of the highest bit of a splicing result to obtain a sub-table main key.
According to the database main key generation method, the branch table identification calculated based on the branch table identification length is added in the main key generation stage, the method is irrelevant to the number of the branch tables and the number of the branch tables, and the main key is ensured to be stable and unchanged during capacity expansion or capacity reduction. For example, when the capacity of 10 data tables is expanded to 100 data tables, data cleaning is needed, all records of the original 10 data tables are redistributed to 100 data tables, but the main sub-table key of each record can be kept unchanged.
Fig. 5 is a schematic diagram of the main steps of the sub-table locating method according to the embodiment of the invention. As shown in fig. 5, the sub-table positioning method of the embodiment of the present invention mainly includes the following steps:
step S501: and acquiring the sub-table identification from the sub-table main key according to a set main key generation rule. The main key generation rule is provided with an actual digit interval occupied by the sub-table identification field in the sub-table main key. The sub-table primary key is generated according to the database primary key generation method described above.
In the embodiment, the actual digit interval occupied by the sub-table main key in the sub-table identification field can be determined according to the main key generation rule; and then acquiring a bit sequence corresponding to the actual digit interval from the primary key of the sublist, wherein the bit sequence is the sublist identifier. As shown in FIG. 4, the 33 rd to 42 th bits are taken out from the primary key of the sub-table to obtain the sub-table identifier.
Step S502: and acquiring the quantity of the sub-tables, and performing modular operation on the quantity of the sub-tables by using the sub-table identification to obtain the target sub-tables. And acquiring the current actual sub-meter quantity, and performing modular operation on the sub-meter quantity by using the sub-meter identification, namely, the sub-meter identification accounts for% of the sub-meter quantity, so as to obtain the target sub-meter. According to the embodiment, specific sub-database sub-tables can be directly positioned from the sub-table main keys, and further database deletion, modification and check operations are performed according to the sub-table main keys.
In the embodiment, a two-stage hash manner (i.e., step S303 and step S502) is adopted, and the sub-table identifier and the target sub-table are respectively placed in the database main key generation stage and the routing stage according to the sub-table main key inverse solution, so that the sub-table identifier is independent of the specific sub-table implementation, and the sub-table main key can be kept stable and unchanged no matter capacity expansion or capacity reduction is performed, and can be accurately routed to the target sub-table.
Fig. 6 is a schematic diagram of main blocks of a database primary key generation apparatus according to an embodiment of the present invention. As shown in fig. 6, a database primary key generation apparatus 600 according to an embodiment of the present invention mainly includes:
the first processing module 601 is configured to obtain a service field value of a target service field in an original data table, and perform hash processing on the service field value by using a set hash function to obtain a hash value. The target service field of the original data table may be one or more. Assuming the original data table is a subscription flow meter, the target service field may include: item name, item type, item price, etc. And mapping the service field value of the target service field into an integer through a hash function to be used as a hash value.
The second processing module 602 is configured to perform modulo operation on a maximum value corresponding to the set length of the sub-table identifier by using the hash value to obtain the sub-table identifier. Setting the length of the sub-table identifier according to the service requirement, such as 1bit, 10bit and 12bit, and performing modulo operation on the maximum value corresponding to the length of the sub-table identifier by using the hash value, namely the hash value (the maximum value corresponding to the length of the sub-table identifier) to obtain the sub-table identifier.
When the length of the sub-table identifier is 1bit, the value range only calculates the positive number to be 0-21Maximum value of 21At most support 212 branch tables; when the length of the sub-table identifier is 10 bits, the value range only calculates the positive number to be 0-210Maximum value of 210At most support 2101024 branch tables; when the length of the sub-table identifier is 12 bits, the value range only calculates the positive number to be 0-212Maximum value of 212At most support 2124096 sub-tables. If the database division is carried out, the number of the branch tables is the upper limit of the sum of the number of the branch tables of all the database division.
And a primary key splicing module 603, configured to obtain 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. The addition of a time attribute to the sub-table primary key, based on the nature of automatic time growth, can cause data generated in different seconds on the same machine to be different. In addition, the distributed system includes multiple machines, and 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 instance identification to identify different machines.
The time stamp, the sub-table identification and the instance identification of the current time are spliced into the sub-table main key, so that the sub-table main keys generated on different machines at the same time point are different, and the sub-table identification is added in the main key generation process, so that the main key is stable and unchanged during capacity expansion and capacity reduction and is globally unique. The primary key is a unique identifier corresponding to one record in the database table, and the record can be quickly searched through the primary key; globally unique refers to a unique key common to all the sublibraries.
In addition, the database primary key generation apparatus 600 according to the embodiment of the present invention may further include: a configuration adjustment module (not shown in fig. 6) configured to configure a reference digit interval occupied by each field in the primary key generation rule according to a set total number of primary keys; and adjusting the reference digit interval occupied by each field in the primary key generation rule according to the service requirement to obtain the actual digit interval occupied by each field.
According to the description, the sub-table identification field is added in the main key generation process, so that the sub-table identification is realized independently of the sub-table, and the main key is stable and unchanged and is globally unique during capacity expansion and capacity reduction.
FIG. 7 is a schematic diagram of the main modules of a sub-meter positioning device according to an embodiment of the invention. As shown in fig. 7, a sub-table positioning apparatus 700 according to an embodiment of the present invention mainly includes:
an obtaining module 701, configured to obtain a sub-table identifier from a sub-table primary key according to a set primary key generation rule. The main key generation rule is provided with an actual digit interval occupied by the sub-table identification field in the sub-table main key. The sub-table primary key is generated according to the database primary key generation method described above.
In the embodiment, the actual digit interval occupied by the sub-table main key in the sub-table identification field can be determined according to the main key generation rule; and then acquiring a bit sequence corresponding to the actual digit interval from the primary key of the sublist, wherein the bit sequence is the sublist identifier. As shown in FIG. 4, the 33 rd to 42 th bits are taken out from the primary key of the sub-table to obtain the sub-table identifier.
And the calculating module 702 is configured to obtain the number of sub-tables, and perform a modular operation on the number of sub-tables by using the sub-table identifier to obtain a target sub-table. And acquiring the current actual sub-meter quantity, and performing modular operation on the sub-meter quantity by using the sub-meter identification, namely, the sub-meter identification accounts for% of the sub-meter quantity, so as to obtain the target sub-meter. According to the embodiment, specific sub-database sub-tables can be directly positioned from the sub-table main keys, and further database deletion, modification and check operations are performed according to the sub-table main keys.
Fig. 8 shows an exemplary system architecture 800 of a database primary key generation method or a database primary key generation apparatus to which an embodiment of the present invention may be applied.
As shown in fig. 8, the system architecture 800 may include terminal devices 801, 802, 803, a network 804, and a server 805. The network 804 serves to provide a medium for communication links between the terminal devices 801, 802, 803 and the server 805. Network 804 may include various types of connections, such as wire, wireless communication links, or fiber optic cables, to name a few.
A user may use the terminal devices 801, 802, 803 to interact with a server 805 over a network 804 to receive or send messages or the like. The terminal devices 801, 802, 803 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.
The server 805 may be a server that provides various services, such as a background management server that processes a primary key generation request transmitted by an administrator using the terminal apparatuses 801, 802, 803. The background management server may obtain a corresponding service field value in response to the primary key generation request, perform hash processing, modulo operation, and splicing processing on the service field value, and feed back a processing result (e.g., a generated sub-table primary key) to the terminal device.
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 apparatus is generally disposed in the server 805.
It should be understood that the number of terminal devices, networks, and servers in fig. 8 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
The invention also provides an electronic device and a computer readable medium according to the embodiment of the invention.
The electronic device of the present invention includes: one or more processors; a storage device, configured to store one or more programs, which when executed by the one or more processors, cause the one or more processors to implement a database primary key generation method according to an embodiment of the present invention.
The computer-readable medium of the present invention has stored thereon a computer program which, when executed by a processor, implements a database primary key generation method of an embodiment of the present invention.
Referring now to FIG. 9, shown is a block diagram of a computer system 900 suitable for use in implementing an electronic device of an embodiment of the present invention. The electronic device shown in fig. 9 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.
As shown in fig. 9, the computer system 900 includes a Central Processing Unit (CPU)901 that can perform various appropriate actions and processes in accordance with a program stored in a Read Only Memory (ROM)902 or a program loaded from a storage section 908 into a Random Access Memory (RAM) 903. In the RAM 903, various programs and data necessary for the operation of the computer system 900 are also stored. The CPU 901, ROM 902, and RAM 903 are connected to each other via a bus 904. An input/output (I/O) interface 905 is also connected to bus 904.
The following components are connected to the I/O interface 905: an input portion 906 including a keyboard, a mouse, and the like; an output section 907 including components such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage portion 908 including a hard disk and the like; and a communication section 909 including a network interface card such as a LAN card, a modem, or the like. The communication section 909 performs communication processing via a network such as the internet. The drive 910 is also connected to the I/O interface 905 as necessary. A removable medium 911 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 910 as necessary, so that a computer program read out therefrom is mounted into the storage section 908 as necessary.
In particular, the processes described above with respect to the main step diagrams may be implemented as computer software programs, according to embodiments of the present disclosure. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program containing program code for performing the method illustrated in the main step diagram. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 909, and/or installed from the removable medium 911. The above-described functions defined in the system of the present invention are executed when the computer program is executed by a Central Processing Unit (CPU) 901.
It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present invention, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The modules described in the embodiments of the present invention may be implemented by software or hardware. The described modules may also be provided in a processor, which may be described as: a processor includes a first processing module, a second processing module, and a primary key stitching module. For example, the first processing module may be further described as a module that obtains a service field value of a target service field in an original data table, and performs hash processing on the service field value using a set hash function to obtain a hash value.
As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to comprise: acquiring a service field value of a target service field in an original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value; performing modular operation on the maximum value corresponding to the set sub-table identification length by using the hash value to obtain a sub-table identification; and acquiring the current time and the instance identification, and splicing and generating the sub-table main key according to the current time, the sub-table identification and the instance identification.
According to the technical scheme of the embodiment of the invention, the sub-table identification field is added in the generation process of the main key, so that the sub-table identification is realized independently of the sub-table, and the main key is stable and unchanged and is globally unique during capacity expansion and capacity reduction.
The product can execute the method provided by the embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method. For technical details that are not described in detail in this embodiment, reference may be made to the method provided by the embodiment of the present invention.
The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (12)
1. A database primary key generation method is characterized by comprising the following steps:
acquiring a service field value of a target service field in an original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value;
performing modular operation on the maximum value corresponding to the set sub-table identification length by using the hash value to obtain a sub-table identification;
and acquiring the current time and the instance identification, and splicing and generating the sub-table main key according to the current time, the sub-table identification and the instance identification.
2. The method of claim 1, wherein generating a sub-table primary key according to the current time, the sub-table identifier and the instance identifier by splicing comprises:
according to a set main key generation rule, splicing the timestamp of the current time, the sub-table identifier and the instance identifier to generate a sub-table main key; the main key generation rule is provided with a time sequence field, a sub-table identification field and an actual digit interval occupied by the example identification field in the sub-table main key.
3. The method according to claim 2, characterized in that the primary key generation rule further sets the actual bit interval occupied by the sequence number field in the sublist primary key;
according to the current time, the sub-table identifier and the instance identifier, splicing to generate a sub-table main key, comprising:
generating a sequence number based on the current time;
and according to the primary key generation rule, splicing the timestamp of the current time, the sub-table identifier, the instance identifier and the serial number to generate the sub-table primary key.
4. A method according to claim 2 or 3, characterized in that the method further comprises:
configuring a reference digit interval occupied by each field in the primary key generation rule according to the set total digits of the primary keys;
and adjusting the reference digit interval occupied by each field in the primary key generation rule according to the service requirement to obtain the actual digit interval occupied by each field.
5. The method of claim 3, wherein generating the sub-table primary key by concatenating the timestamp of the current time, the sub-table identifier, the instance identifier, and the sequence number according to the primary key generation rule comprises:
generating a first bit sequence with corresponding bit length according to the timestamp of the current time and a first actual bit interval occupied by the time sequence field;
generating a second bit sequence with corresponding bit length according to the sub-table identification and a second actual digit interval occupied by the sub-table identification field;
generating a third bit sequence with corresponding bit length according to the example identifier and a third actual digit interval occupied by the example identifier field;
generating a fourth bit sequence with corresponding bit length according to the sequence number and a fourth actual bit interval occupied by the sequence number field;
and splicing the first bit sequence to the fourth bit sequence, and adding a sign bit in front of the highest bit of a splicing result to obtain the branch table main key.
6. A method for table based positioning, comprising:
according to a set main key generation rule, acquiring a sub-table identifier from a sub-table main key; wherein the sub-table primary key is generated according to the method of any one of claims 1 to 5;
and acquiring the quantity of the sub-tables, and performing modular operation on the quantity of the sub-tables by using the sub-table identification to obtain the target sub-tables.
7. The method according to claim 6, wherein the primary key generation rule is provided with a sub-table identification field in the actual digit range occupied by the sub-table primary key;
according to a set primary key generation rule, obtaining a sub-table identifier from a sub-table primary key, comprising:
determining the actual digit interval occupied by the sub-table main key in the sub-table identification field according to a set main key generation rule;
and acquiring a bit sequence corresponding to the actual digit interval from the sub-table main key, wherein the bit sequence is a sub-table identifier.
8. A database primary key generation apparatus, comprising:
the first processing module is used for acquiring a service field value of a target service field in an original data table, and performing hash processing on the service field value by using a set hash function to obtain a hash value;
the second processing module is used for carrying out modular operation on the maximum value corresponding to the set sub-table identifier length by using the hash value to obtain the sub-table identifier;
and the primary key splicing module is used for acquiring the current time and the instance identifier and splicing and generating the sub-table primary key according to the current time, the sub-table identifier and the instance identifier.
9. The apparatus of claim 8, wherein the splicing module is further configured to:
according to a set main key generation rule, splicing the timestamp of the current time, the sub-table identifier and the instance identifier to generate a sub-table main key; the main key generation rule is provided with a time sequence field, a sub-table identification field and an actual digit interval occupied by the example identification field in the sub-table main key.
10. A meter positioning apparatus, comprising:
the acquisition module is used for acquiring the sub-table identification from the sub-table main key according to the set main key generation rule; wherein the sub-table primary key is generated according to the method of any one of claims 1 to 5;
and the calculation module is used for acquiring the quantity of the sub-tables, and performing modular operation on the quantity of the sub-tables by using the sub-table identification to obtain the target sub-table.
11. An electronic device, comprising:
one or more processors;
a storage device for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-7.
12. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010441717.5A CN113704245A (en) | 2020-05-22 | 2020-05-22 | Database main key generation method, sub-table positioning method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010441717.5A CN113704245A (en) | 2020-05-22 | 2020-05-22 | Database main key generation method, sub-table positioning method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113704245A true CN113704245A (en) | 2021-11-26 |
Family
ID=78646272
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010441717.5A Pending CN113704245A (en) | 2020-05-22 | 2020-05-22 | Database main key generation method, sub-table positioning method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113704245A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114900497A (en) * | 2022-05-09 | 2022-08-12 | 上海极豆科技有限公司 | Identification serial number generation method and device, electronic equipment and storage medium |
| CN115033618A (en) * | 2022-06-17 | 2022-09-09 | 阿波罗智联(北京)科技有限公司 | Processing method, counting method, device, equipment and storage medium |
| WO2023103338A1 (en) * | 2021-12-06 | 2023-06-15 | 深圳前海微众银行股份有限公司 | Data processing method and apparatus, and device and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103399945A (en) * | 2013-08-15 | 2013-11-20 | 成都博云科技有限公司 | Data structure based on cloud computing database system |
| CN110442642A (en) * | 2019-06-19 | 2019-11-12 | 北京航天智造科技发展有限公司 | Data processing method, device and the storage medium of distributed data base |
| CN110866002A (en) * | 2018-08-27 | 2020-03-06 | 北京京东尚科信息技术有限公司 | Method and device for processing sub-table data |
| CN110928912A (en) * | 2018-09-20 | 2020-03-27 | 北京京东尚科信息技术有限公司 | Method and device for generating unique identifier |
-
2020
- 2020-05-22 CN CN202010441717.5A patent/CN113704245A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103399945A (en) * | 2013-08-15 | 2013-11-20 | 成都博云科技有限公司 | Data structure based on cloud computing database system |
| CN110866002A (en) * | 2018-08-27 | 2020-03-06 | 北京京东尚科信息技术有限公司 | Method and device for processing sub-table data |
| CN110928912A (en) * | 2018-09-20 | 2020-03-27 | 北京京东尚科信息技术有限公司 | Method and device for generating unique identifier |
| CN110442642A (en) * | 2019-06-19 | 2019-11-12 | 北京航天智造科技发展有限公司 | Data processing method, device and the storage medium of distributed data base |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2023103338A1 (en) * | 2021-12-06 | 2023-06-15 | 深圳前海微众银行股份有限公司 | Data processing method and apparatus, and device and storage medium |
| CN114900497A (en) * | 2022-05-09 | 2022-08-12 | 上海极豆科技有限公司 | Identification serial number generation method and device, electronic equipment and storage medium |
| CN114900497B (en) * | 2022-05-09 | 2023-09-26 | 上海极豆科技有限公司 | Identification sequence number generation method and device, electronic equipment and storage medium |
| CN115033618A (en) * | 2022-06-17 | 2022-09-09 | 阿波罗智联(北京)科技有限公司 | Processing method, counting method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11640474B2 (en) | Method and apparatus for operating database | |
| CN110019125B (en) | Database management method and device | |
| CN113704245A (en) | Database main key generation method, sub-table positioning method and device | |
| CN107704202B (en) | Method and device for quickly reading and writing data | |
| CN105072160A (en) | Serial number generating method and device, and a server | |
| CN111061680A (en) | Data retrieval method and device | |
| CN112835904A (en) | Data processing method and data processing device | |
| CN111858586A (en) | Data processing method and device | |
| CN111241189A (en) | Method and device for synchronizing data | |
| CN113449488A (en) | Method and device for generating unique identifier | |
| CN112862613A (en) | Transaction data processing method and device | |
| CN110109919B (en) | Method and device for determining logic information | |
| CN109144991B (en) | Method and device for dynamic sub-metering, electronic equipment and computer-storable medium | |
| CN111062682A (en) | Work order processing method and device | |
| CN112148705A (en) | Data migration method and device | |
| CN112306984A (en) | Data source routing method and device | |
| CN112115206A (en) | Method and device for processing object storage metadata | |
| JP6233846B2 (en) | Variable-length nonce generation | |
| CN110705935B (en) | Logistics document processing method and device | |
| CN110866002B (en) | Method and device for processing sub-table data | |
| CN114756173A (en) | Method, system, device and computer readable medium for file merging | |
| CN112711588B (en) | Method and device for multi-table connection | |
| CN112711572A (en) | Online capacity expansion method and device suitable for sub-warehouse and sub-meter | |
| CN113556370A (en) | Service calling method and device | |
| CN111984616A (en) | Method, device and system for updating shared file |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |