A database partition table processing method, device, system and medium

Abstract

The application discloses a database partition table processing method, device and system and a medium, comprising: allocating a unique identifier to each partition in a partition table according to a preset identifier allocation strategy, and saving the unique identifier of each partition in the metadata of the partition table; constructing a global index of the partition table according to the primary key of the partition table and the unique identifier of each partition; when receiving a data removal operation for all data in a specified partition in the partition table, obtaining the current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata; and updating the corresponding record in the global index according to the old identifier in the background. The unique identifier of each partition is introduced in the metadata and the global index of the partition table, and the old identifier is recorded when the partition data removal operation is performed to distinguish between old and new history, so that the global index remains effective while the related operation is efficiently performed, the uniqueness of the data is ensured not to be affected, and the efficiency and availability of the partition table operation are improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and in particular, to a database partition table processing method, device, system and medium. BACKGROUND

[0002] The database has the function of partition table, which divides a large table into multiple smaller sub-tables according to certain rules, and each sub-table is called a partition. The partition table in the database can realize the functions of improving query performance, reducing query range, data archiving and cleaning, online maintenance and the like. Since the uniqueness cannot be guaranteed when the partition key is different from the primary key or unique key on the table, it is necessary to ensure the uniqueness of the data in the partition table through a global index.

[0003] When the partition table has a corresponding global index, if the drop partition (delete partition), truncate partition (truncate partition) and other DDL operations are performed on the partition table, the global index also needs to be updated. In the prior art, the way to update the global index when performing the above DDL operations usually includes two kinds:

[0004] One way is to let the global index be invalid after performing the drop partition, truncate partition and other DDL operations, and then set it to be effective again after the global index is rebuilt according to the drop partition, truncate partition and other DDL operations. But this rebuilding operation will affect the execution of DML operations, for example, when inserting a new record into the partition table, the uniqueness cannot be correctly judged because the global index is invalid.

[0005] Another way is to update the global index synchronously when performing the drop partition, truncate partition and other DDL operations. For example, for the drop partition DDL, all records in the partition that are dropped need to be deleted in the corresponding records in the global index, and when the number of original records in this partition is huge, deleting the corresponding records in the global index will be very time-consuming. Therefore, this will cause a large amount of time to be required for executing these DDL operations, so that the business cannot operate normally.

[0006] Therefore, the above way of updating the global index when performing the DDL operation is difficult to ensure the operation efficiency and the effectiveness of the global index at the same time, thereby affecting the normal business operation of the database. SUMMARY

[0007] In view of the above shortcomings of the prior art, the purpose of the present application is to provide a database partition table processing method, device, system and medium applicable to the field of financial technology or other related fields, which aims to quickly and efficiently execute partition table related operations while maintaining the effectiveness of the global index, and ensuring that the data uniqueness is not affected.

[0008] The technical solutions of the present application are as follows:

[0009] A database partition table processing method comprises:

[0010] According to a preset identification allocation strategy, a unique identification is allocated to each partition in the partition table, and the unique identification of each partition is saved to the metadata of the partition table.

[0011] A global index of the partition table is constructed according to the primary key of the partition table and the unique identification of each partition.

[0012] When a full data removal operation on a specified partition in the partition table is received, the current unique identification of the specified partition is obtained from the metadata, the current unique identification is recorded as an old identification, and the metadata is updated.

[0013] In the background, the corresponding record in the global index is updated according to the old identification.

[0014] In one embodiment, according to a preset identification allocation strategy, a unique identification is allocated to each partition in the partition table, and the unique identification of each partition is saved to the metadata of the partition table, comprising:

[0015] When a partition is created, a unique value is generated according to at least one attribute of the currently created partition according to a preset algorithm, which is used as the unique identification of the currently created partition.

[0016] The steps of generating and allocating the unique value are repeatedly executed until a unique identification is allocated to each partition in the partition table.

[0017] The unique identification of each partition is saved to the metadata of the partition table.

[0018] In one embodiment, according to a preset identification allocation strategy, a unique identification is allocated to each partition in the partition table, and the unique identification of each partition is saved to the metadata of the partition table, comprising:

[0019] A partition identification field is created and maintained at the table level, and the value of the partition identification field is used to represent the next available identification of the partition table.

[0020] The current value of the partition identification field is allocated to a partition in the partition table as the unique identification of the partition.

[0021] The value of the partition identification field is updated according to a preset rule, and the updated value of the partition identification field is allocated to the next partition as the unique identification of the next partition.

[0022] The steps of updating and allocating the field value are repeatedly executed until a unique identification is allocated to each partition in the partition table.

[0023] The unique identifier of each partition is saved to the metadata of the partition table as the partition identifier field.

[0024] In one embodiment, when receiving a full data removal operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as an old identifier, and the metadata is updated, including:

[0025] When receiving a truncate operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata according to the partition name of the specified partition;

[0026] According to the preset identifier allocation strategy, a new identifier of the specified partition is determined;

[0027] All data in the specified partition is removed, the current unique identifier of the specified partition is recorded as an old identifier, and the new identifier is allocated to the specified partition;

[0028] The metadata after the truncate partition is updated according to the new identifier of the specified partition;

[0029] According to the table name of the partition table and the currently recorded old identifier, a corresponding to-be-cleaned task is generated and added to a to-be-cleaned identifier task list.

[0030] In one embodiment, when receiving a full data removal operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as an old identifier, and the metadata is updated, including:

[0031] When receiving a drop operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata according to the partition name of the specified partition;

[0032] All data in the specified partition is removed, and the specified partition is deleted, the current unique identifier of the specified partition is recorded as an old identifier;

[0033] The metadata after the drop partition is updated;

[0034] According to the table name of the partition table and the currently recorded old identifier, a corresponding to-be-cleaned task is generated and added to a to-be-cleaned identifier task list.

[0035] In one embodiment, the updating, in the background, of the corresponding record in the global index according to the old identifier includes:

[0036] The control background cleaning thread executes a cleaning task at a specified time, and sequentially extracts each table name and old identifier from the to-be-cleaned identifier task list, wherein the specified time includes a time when the to-be-cleaned task is generated and / or a preset time interval.

[0037] According to the table name and old identifier extracted each time, corresponding index records in the global index with a unique identifier equal to the extracted old identifier are deleted until all index records corresponding to the old identifier are deleted.

[0038] In one embodiment, after the corresponding records in the global index are updated according to the old identifier in the background, the method further comprises:

[0039] When a new data insertion operation on the partition table is received, an index record to be inserted is constructed according to a unique identifier of a partition corresponding to the new data to be inserted;

[0040] The to-be-inserted index record is inserted into the global index, and whether there is an index conflict is detected;

[0041] If there is an index conflict, whether the current index conflict is a negligible conflict is determined according to a unique identifier of a partition in a conflict index record;

[0042] If it is a negligible conflict, the to-be-inserted data is inserted into the partition table.

[0043] A database partition table processing apparatus, comprising:

[0044] A partition identifier allocation module configured to allocate a unique identifier to each partition in a partition table according to a preset identifier allocation strategy, and save the unique identifier of each partition in metadata of the partition table;

[0045] An index construction module configured to construct a global index of the partition table according to a primary key of the partition table and the unique identifier of each partition;

[0046] A partition identifier processing module configured to, when receiving a data removal operation on all data in a specified partition in the partition table, acquire a current unique identifier of the specified partition from the metadata, record the current unique identifier as an old identifier, and update the metadata;

[0047] An index updating module configured to update corresponding records in the global index according to the old identifier in the background.

[0048] A database partition table processing system, the system comprising at least one processor; and

[0049] A memory in communication connection with the at least one processor; wherein

[0050] The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the database partition table processing method.

[0051] A non-volatile computer readable storage medium stores computer executable instructions that, when executed by one or more processors, cause the one or more processors to perform the database partition table processing method.

[0052] Beneficial effects: The present application discloses a database partition table processing method, device, system and medium, compared with the prior art, the preset identifier allocation strategy is used to allocate a unique identifier to each partition in the partition table, and the unique identifier of each partition is saved to the metadata of the partition table; the global index of the partition table is constructed according to the primary key of the partition table and the unique identifier of each partition; when receiving the all-data removal operation of the specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as an old identifier and the metadata is updated; the corresponding record in the global index is updated according to the old identifier in the background. By introducing the unique identifier of each partition in the metadata and the global index of the partition table, and recording the old identifier when performing the partition data removal operation to distinguish the new and old history, the global index remains effective while efficiently performing related operations, ensures the uniqueness of data is not affected, and improves the efficiency and availability of the partition table operation. BRIEF DESCRIPTION OF DRAWINGS

[0053] The present application will be further described below in conjunction with the drawings and embodiments, wherein:

[0054] Figure 1 A flowchart of the database partition table processing method provided by the embodiment of the present application is provided;

[0055] Figure 2 A flowchart of step S101 in the database partition table processing method provided by the embodiment of the present application is provided;

[0056] Figure 3 Another flowchart of step S101 in the database partition table processing method provided by the embodiment of the present application is provided;

[0057] Figure 4 A flowchart of step S103 in the database partition table processing method provided by the embodiment of the present application is provided;

[0058] Figure 5 Another flowchart of step S103 in the database partition table processing method provided by the embodiment of the present application is provided;

[0059] Figure 6 A flow chart of step S104 in the database partition table processing method provided by the embodiment of the present application;

[0060] Figure 7 A functional module schematic diagram of the database partition table processing device provided by the embodiment of the present application;

[0061] Figure 8 A hardware structure schematic diagram of the database partition table processing system provided by the embodiment of the present application. DETAILED DESCRIPTION

[0062] To make the objectives, technical solutions and effects of the present application clearer and more explicit, the present application is further described in detail below. It should be understood that the specific embodiments described herein are only used to explain the present application and do not limit the present application. The embodiments of the present application are introduced below with reference to the drawings.

[0063] When the partition table has a corresponding global index, the DML operations such as increment, deletion and modification on the table need to be updated synchronously, and when the DDL operations such as drop partition and truncate partition on the partition table are performed, the global index also needs to be updated.

[0064] A general processing scheme is to invalidate the global index after performing the DDL operations such as drop partition and truncate partition, and the global index is reestablished after the DDL operations such as drop partition and truncate partition, and then the global index is set to be valid again. This reestablishment operation will affect the execution of the DML operation, for example, when a new record is inserted into the partition table, the uniqueness cannot be correctly judged due to the invalidation of the global index. Another scheme is to update the global index synchronously when performing the DDL operations such as drop partition and truncate partition, for example, for the drop partition DDL, all the records in the dropped partition are deleted in the corresponding records in the global index, and when the number of original records in the dropped partition is huge, the deletion of the corresponding records in the global index will be time-consuming. This will cause a large amount of time to be required for performing these DDL operations, resulting in that the business cannot be normally operated.

[0065] In order to solve the above problems, the present application provides a database partition table processing method, as shown in Figure 1 The database partition table processing method provided by the embodiment includes the following steps:

[0066] S101, a unique identifier is assigned to each partition in the partition table according to a preset identifier assignment strategy, and the unique identifier of each partition is saved to the metadata of the partition table.

[0067] In this embodiment, when creating each partition in the partitioned table, a unique identifier partition uuid is assigned to each partition in the partitioned table based on a preset identifier assignment strategy. Specifically, the preset identifier assignment strategy refers to a rule or method for generating and assigning a unique identifier, and the identifier assignment strategy can be flexibly set according to different identifier forms. Moreover, the unique identifier is table-level, that is, the unique identifier of each partition in the table is different, while the unique identifier between tables is allowed to be the same, which ensures the independence of the partition identifier and simplifies the allocation and management of the partition identifier. When performing a partition operation, a new partition uuid can be quickly allocated without global synchronization. The unique identifier can be a numerical value or a string, for example, a timestamp or a randomly generated string, which can ensure table-level independence. This embodiment does not limit this. After assigning a unique identifier to each partition, the unique identifier of each partition is saved to the metadata of the partition table. The metadata can be a system table or a configuration file, which is used to store the structure and partition information of the partition table. By saving the partition uuid of all partitions in the metadata as the basis for subsequent data management and query, the management of the partition is more accurate and efficient.

[0068] S102, constructing a global index of the partitioned table according to the primary key of the partitioned table and the unique identifier of each partition.

[0069] In this embodiment, when constructing the global index of the partitioned table, the primary key (PK) column of the partitioned table and the unique identifier partition uuid of each partition are used as the record structure of the global index to first construct an empty global index. The primary key is a field or field combination in the table used to uniquely identify records. The constructed global index can be a B-tree, hash table or other index structure, etc. Then each record in the partitioned table is traversed, and the primary key and the corresponding unique identifier partition uuid of each record are inserted into the global index as index items, thereby obtaining an index covering the entire partitioned table, which can be used for fast record lookup and ensures the uniqueness of the primary key or unique key.

[0070] S103, when receiving a data removal operation for all data in a specified partition in the partitioned table, obtaining the current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata.

[0071] In the embodiment, when a data removal operation on a specified partition in a partition table is received, such as a truncate partition or drop partition DDL operation, the current unique identifier partition uuid of the specified partition is obtained from the metadata of the partition table according to a database operation statement, and the current unique identifier partition uuid is recorded as an old identifier old_partition_uuid, so as to clean all old_partition_uuid. That is, after the truncate partition or drop partition operation is performed, the old identifier old_partition_uuid of the specified partition is no longer used in the partition of the current partition table, and the partition uuid used in the current and history of all partitions of the table is not reused, so as to ensure that the new and old data records can be distinguished by the new and old unique identifiers, so that the data removal operation is quickly completed, and the global index is always valid.

[0072] After the data removal operation is completed and the old identifier of the partition is recorded, the metadata of the partition table is updated. For example, if the truncate partition operation is performed, the new unique identifier of the partition in the metadata is updated, and if the drop partition operation is performed, the related information of the partition is deleted. Since the old and new data are distinguished by the identifier of the partition in the embodiment, the data removal operation is quickly completed, and the global index does not need to be updated synchronously. The amount of data updated in the metadata is at the partition level, which greatly saves the operation time compared with the time consumed for synchronously deleting the global index in the prior art, and ensures the database operation efficiency and the validity of the global index.

[0073] S104, updating the corresponding record in the global index according to the old identifier in the background.

[0074] In the embodiment, the old identifier recorded when the data removal operation is performed is used to update the corresponding record in the global index in the background. Specifically, the background thread or process can be notified to perform the cleaning operation immediately, so as to clean the old data in the global index record in time; or the cleaning operation can be configured to be performed every preset time, so as to avoid frequent calling of the cleaning thread, and the embodiment is not limited in this regard.

[0075] The embodiment optimizes the management of the global index in the partition table by introducing the unique identifier partition_uuid of the partition. When performing a data removal operation such as drop partition or truncate partition, the records in the global index are not immediately deleted synchronously, but the unique identifier partition_uuid of the partition is marked as the old identifier old_partition_uuid. The old identifier old_partition_uuid is processed asynchronously by a background thread, and the records related to the old identifier old_partition_uuid in the global index are gradually cleaned up. This process does not block the main DDL operation, and the global index remains valid before the background cleanup is completed, ensuring that data uniqueness is not affected, and therefore other DML operations are not affected. By means of asynchronous processing and background cleanup, the time-consuming global index update operation is separated from the main DDL operation, thereby effectively avoiding the blocking problem caused by synchronous update, and improving the efficiency and availability of the partition table operation.

[0076] In one embodiment, as shown in FIG. 1, step S101 comprises: Figure 2

[0077] S201, when creating a partition, generating a unique value as the unique identifier of the current created partition according to at least one attribute of the current created partition by a preset algorithm;

[0078] S202, repeating the steps of generating and assigning the unique value until each partition in the partition table is assigned a unique identifier;

[0079] S203, saving the unique identifier of each partition to the metadata of the partition table.

[0080] In the embodiment, if the unique identifier of each partition is in the form of a string, when creating a partition in the partition table, a unique value is generated according to at least one attribute of the current created partition by a preset algorithm. The specific attribute can be one or more of, for example, partition name, table name, creation timestamp, etc. Furthermore, a random number can be combined with the at least one attribute to generate a unique value as the unique identifier of the current created partition. Specifically, a hash algorithm (such as SHA-256) can be used to combine the partition name or other attributes to generate a unique identifier. For example, the table name + partition name + time string + random number string of the current created partition are combined to calculate the corresponding hash value as the unique identifier partition_uuid of the current created partition. Of course, in other embodiments, the unique identifier can be generated in other ways, as long as it can ensure that the partition identifier is unique at the table level, which is not limited in the embodiment.

[0081] ​The above steps are repeated until all partitions in the partition table are assigned with unique partition uuids, i.e. a unique string is generated according to the attributes of the partition to be assigned with an identifier each time until the assignment of unique identifiers to all partitions is completed, ensuring that each partition in the partition table has a unique partition uuid in the form of a string, avoiding omissions.

[0082] In one embodiment, as shown in FIG. 1, step S101 comprises: Figure 3

[0083] S301, creating and maintaining a partition identifier field at the table level, the value of the partition identifier field being used to represent the next available identifier of the partition table;

[0084] S302, assigning the current value of the partition identifier field to one partition in the partition table as the unique identifier of the partition;

[0085] S303, updating the value of the partition identifier field according to a preset rule, and assigning the updated value of the partition identifier field to the next partition as the unique identifier of the next partition;

[0086] S304, repeatedly performing the above steps of field value updating and assignment until each partition in the partition table is assigned with a unique identifier;

[0087] S305, saving the unique identifier of each partition and the partition identifier field to the metadata of the partition table.

[0088] In this embodiment, if the unique identifier of each partition is in the form of a numerical value, a partition identifier field partition_next_uuid is created and maintained when the partition table is created, the value of the partition identifier field being used to represent the next available identifier of the partition table. When a partition is created in the partition table, a unique identifier partition uuid is assigned to each partition according to the partition identifier field, i.e. the partition identifier field is used to assign a corresponding unique identifier partition uuid to each partition in the partition table, and the field is maintained and updated after each assignment to ensure that the unique identifiers partition uuid of each partition in the table are different.

[0089] ​When creating partitions and assigning unique identifiers, based on the currently maintained partition identifier field, the current value of the field is read, for example, N, N is assigned to a partition in the partition table as the unique identifier of the partition, partition uuid. Then update the value of partition_next_uuid according to the preset rule, assign the updated value of the partition identifier field to the next partition as the unique identifier of the next partition. Specifically, the current value of the partition identifier field is incremented by 1 as the updated value of the partition identifier field, that is, if the current value is N, the value of the field is updated to N+1 after being assigned to a partition, which represents the next available identifier of the partition table as N+1, ensuring that the identifier of each partition is unique.

[0090] The above steps are repeated until all partitions in the partition table are assigned unique identifiers partition uuid. In each loop, the value of the field partition_next_uuid is updated and assigned to the next partition, and during the assignment process, it is checked whether all partitions have been assigned partition uuid. If there are still unassigned partitions, the loop continues, otherwise the loop ends. Through the loop operation, the unique identifier assignment of all partitions is efficiently completed, ensuring that each partition in the partition table has a unique partition uuid, avoiding omissions.

[0091] The partition uuid and the current partition_next_uuid of each partition are saved to the metadata of the partition table, which can be a system table or a configuration file, etc. Ensure the persistence of metadata so that the structure and state of the partition table can be correctly restored after system restart or fault recovery. That is, when the unique identifier of the partition is a numerical value, a field, namely the partition identifier field partition_next_uuid, is added to the metadata to represent the next available partition uuid of the partition table, so that the unique identifier of the partition is quickly assigned when performing partition operations, ensuring operation efficiency.

[0092] In one embodiment, as shown in Figure 4 Step S103 includes:

[0093] S401, when receiving a truncate operation on a specified partition in the partition table, obtaining the current unique identifier of the specified partition from the metadata according to the partition name of the specified partition;

[0094] S402, according to the preset identifier allocation strategy to confirm the new identifier of the specified partition;

[0095] S403, remove all data in the specified partition, record the current unique identifier of the specified partition as an old identifier, and assign the new identifier to the specified partition;

[0096] S404, update the metadata after truncate partition according to the new identifier of the specified partition;

[0097] S405, generate a corresponding cleaning task according to the table name of the partition table and the old identifier recorded currently, and add it to the cleaning identifier task list.

[0098] In this embodiment, when the data removal operation is a truncate operation, all data in the partition needs to be emptied, but the structure of the partition is retained. At this time, the first SQL statement for executing the truncate partition is received, and based on the partition table to be truncated in the first SQL statement, the partition name and the corresponding unique identifier partition uuid of each partition are obtained from the metadata of the partition table.

[0099] And based on different preset identifier allocation strategies to confirm the new identifier of the specified partition, if the current identifier allocation strategy is in the form of a string as a unique identifier, a new unique value is generated according to the specified partition attribute according to the preset algorithm in the budget identifier allocation strategy, as the new identifier of the specified partition, and the specific preset algorithm is the same as the algorithm used in the initial allocation stage. If the current identifier allocation strategy is in the form of a numerical value as a unique identifier, in addition to obtaining the current unique identifier of the specified partition from the metadata, the next available partition uuid of the partition table, that is, the current value of the partition identifier field partition next uuid, is also obtained from the metadata.

[0100] The partition name of the specified partition is extracted from the first SQL statement, and the current unique identifier partition uuid of the specified partition is obtained by querying the metadata of the partition table based on the partition name of the specified partition. The truncate operation is executed to empty all data in the specified partition, but the structure of the partition is retained, the current unique identifier partition uuid of the specified partition is recorded as an old identifier old partition uuid, the old identifier is no longer used in the partition table, and the new identifier confirmed currently is assigned to the specified partition as a result, so that the old and new of the partition data before and after the truncate operation are distinguished by the new and old identifiers, so that the truncate operation can be quickly executed without synchronously updating the global index and maintaining data uniqueness.

[0101] After the current value of the partition identification field is taken as the new identification of the specified partition, the metadata after truncating the partition is updated according to the new identification of the specified partition, so that the latest partition information is stored in the metadata. In particular, if the current unique identification is in numerical form, the value of the partition identification field partition_next_uuid needs to be updated according to a preset rule (for example, incrementing by 1) for the next partition identification allocation. The new identification of the specified partition and the updated partition identification field partition_next_uuid are saved to the metadata of the partition table, so that the latest partition information can be synchronized in the metadata in time, and the structure and state of the partition table can be correctly restored after system restart or fault recovery.

[0102] In addition, when the truncate partition is executed, the table name of the partition table and the old identification recorded when the truncate partition is executed are used to generate a corresponding cleaning task to be added to the cleaning identification task list, so that the background can be notified to accurately clean the old records based on the task list.

[0103] In one embodiment, as shown in Figure 5 Step S103 includes:

[0104] S501, when receiving a drop operation on a specified partition in the partition table, obtaining the current unique identification of the specified partition from the metadata according to the partition name of the specified partition;

[0105] S502, removing all data in the specified partition and deleting the specified partition, and recording the current unique identification of the specified partition as an old identification;

[0106] S503, updating the metadata after the drop partition;

[0107] S504, generating a corresponding cleaning task according to the table name of the partition table and the recorded old identification, and adding the cleaning task to the cleaning identification task list.

[0108] In this embodiment, when the data removal operation is a drop operation, i.e., deleting the partition, all data in the partition needs to be emptied, and the structure of the partition needs to be deleted. Similarly, at this time, a second SQL statement for executing the drop partition will be received, and based on the partition table to be dropped in the second SQL statement, the partition name and the corresponding unique identification partition_uuid of each partition are obtained from the metadata of the partition table, and the next available partition_uuid of the partition table, i.e., the current value of the partition identification field partition_next_uuid, is also obtained.

[0109] The partition name of the specified partition is extracted from the second SQL statement, the metadata of the partition table is queried based on the partition name of the specified partition, and the current unique identifier partition_uuid of the specified partition is obtained. The drop operation is performed to empty all data in the specified partition and delete the specified partition, the current unique identifier partition_uuid of the specified partition is recorded as an old identifier old_partition_uuid, the old identifier is no longer used in the partition of the partition table, and even if a new partition is added in the partition table subsequently, any old identifier is not reused, but the allocation of the unique identifier is based on the current value of the partition identifier field, so that the new and old data are not confused.

[0110] Similarly, in the drop partition, the metadata of the partition table is updated, specifically, the partition_uuid of the specified partition is removed from the metadata of the partition table, so that the structure and state information of the partition table are consistent, and the update of the global index is processed by a background thread or process, so that the drop partition is quickly executed without affecting the effectiveness of the global index. And when executing the drop partition, the table name and the old identifier of the partition table recorded when the drop partition is executed are also used to generate a corresponding cleaning task to be added to the cleaning identifier task list, so as to notify the background to accurately clean the old records based on the task list.

[0111] In one embodiment, as shown in FIG. 1, step S104 includes: Figure 6

[0112] S601, control the background cleaning thread to execute the cleaning task at a specified time, and sequentially extract each table name and old identifier from the cleaning identifier task list, wherein the specified time includes the time when the cleaning task is generated and / or every preset time.

[0113] S602, according to the table name and old identifier extracted each time, the index record with the unique identifier equal to the extracted old identifier in the global index is deleted, until all index records corresponding to the old identifier are deleted.

[0114] ​In this embodiment, the background cleaning thread is an independent thread running in the background to perform cleaning operations, so as not to affect the response speed of the main operation. Specifically, the background cleaning thread is controlled to perform a cleaning task at a specified time, which includes the time when the to-be-cleaned task is generated and / or every preset time, that is, the background cleaning thread is woken up to perform the cleaning task after the to-be-cleaned task is generated when the truncate partition or drop partition is executed; or the background cleaning thread can also be controlled to perform the cleaning task periodically every preset time (such as every 1 second); or based on the generation of the to-be-cleaned task and the waking up of the background cleaning thread, to avoid the loss of the wake-up signal, the background cleaning thread also actively checks whether there is a task in the to-be-cleaned task list periodically, so that the background cleaning thread can clean the old index record as soon as possible.

[0115] The background cleaning thread extracts task items from the to-be-cleaned identification task list one by one, and each task item contains a table name and an old identification old_partition_uuid, that is, the task items in the task list are processed one by one to ensure the integrity of the cleaning operation. According to the extracted table name, the corresponding global index is opened, the global index is traversed, and all index records with partition_uuid equal to the extracted old identification old_partition_uuid are deleted. After the records are deleted, a commit operation is performed to ensure the persistence of the cleaning operation. By deleting the index records corresponding to the old identification in the global index, the consistency of the global index and the actual data of the partition table is ensured, and the background cleaning operation does not affect the response speed of the main operation, thereby improving the stability of the system.

[0116] In one embodiment, after step S104, the method further comprises:

[0117] When receiving a new data insertion operation on the partition table, a to-be-inserted index record is constructed according to the unique identification of the partition corresponding to the to-be-inserted new data;

[0118] The to-be-inserted index record is inserted into the global index, and it is detected whether there is an index conflict;

[0119] If there is an index conflict, it is determined whether the current index conflict is a negligible conflict according to the unique identification of the partition in the conflict index record;

[0120] If it is a negligible conflict, the to-be-inserted data is inserted into the partition table.

[0121] In the embodiment, in the DML operation, when a new data is inserted into the partition table (such as insert and update), an index record inserted into the global index is first constructed for the new data to be inserted, specifically, the data to be inserted and the corresponding partition name are extracted based on the third SQL statement for executing the DML operation, and the unique identifier of the partition name to be inserted is extracted from the metadata of the partition table, and the index record to be inserted is constructed based on the primary key of the data to be inserted, the unique identifier of the partition to be inserted, and other fields.

[0122] The constructed index record is inserted into the global index, and it is checked whether there is a conflict on the primary key or the unique key, that is, it is checked whether there is a record with the same primary key (or unique key) as the index record to be inserted in the global index. The conflict is detected in time to avoid data inconsistency problem. If it is detected that there is no index conflict, it is ensured that the data is unique, and the new data specified by the third SQL statement, that is, the data to be inserted, is directly inserted into the partition table.

[0123] If it is detected that there is an index conflict, that is, there is a conflict record with the same primary key (or unique key) as the index record to be inserted in the current global index, it is further judged whether the conflict is valid according to whether the unique identifier of the partition in the conflict record is the old identifier old_partition_uuid. Specifically, the set of all latest unique identifiers of each partition can be obtained from the metadata of the partition table, and it is confirmed whether the unique identifier of the partition in the conflict record is in the set. If yes, it is indicated that the unique identifier of the partition in the conflict index record is the current valid identifier, which means that the conflict is real, and thus the conflict cannot be ignored, and the insertion operation will fail and return an error. If no, it is indicated that the unique identifier of the partition in the conflict record is the old identifier, which means that the conflict is caused by the removed partition. The conflict index record may be temporarily not deleted due to the background asynchronous processing, and thus the conflict can be ignored. At this time, the data to be inserted is inserted into the partition table.

[0124] The embodiment enables the global index to distinguish the data of different partitions through the unique identifier of the partition. Even during the background cleaning, the data of the old partition has not been completely cleaned, and the newly inserted data will not conflict with the data of the old partition, which realizes that when the new data insertion operation on the partition table is received, the index conflict is correctly handled, and the data is inserted when it is confirmed that the conflict is ignorable.

[0125] Further, the database partition table processing method provided by the application can also realize accurate backup and recovery. In the backup process, the backup partition table and the corresponding global index are backed up, and in the recovery process, the previously backed up partition table and the corresponding global index are directly recovered. Since the unique identifier partition_uuid of each partition in the partition table is based on the table level, the problem of mutual influence between the partition tables does not occur. Even if the partition structure of the partition table changes (for example, the partition is dropped or truncated), the table level independence and persistence of the unique identifier ensure that the records in the global index table are not confused with the records of other partition tables, and the mutual dependence of the unique identifiers between the partition tables is avoided, so that the global index table is still valid after the partition table is recovered, and the unique identifier of the partition does not need to be regenerated, thereby solving the problem of invalid global index table after the table data is recovered.

[0126] It should be noted that the above steps do not necessarily have a certain order. Those skilled in the art can understand from the description of the embodiments of the application that the above steps can have different execution orders in different embodiments, that is, they can be executed in parallel, or they can be exchanged and executed, and the like.

[0127] Another embodiment of the application provides a database partition table processing device, as shown in Figure 7 The device 1 comprises:

[0128] A partition identifier allocation module 11 is configured to allocate a unique identifier to each partition in the partition table according to a preset identifier allocation strategy, and save the unique identifier of each partition in the metadata of the partition table.

[0129] An index construction module 12 is configured to construct a global index of the partition table according to the primary key of the partition table and the unique identifier of each partition.

[0130] A partition identifier processing module 13 is configured to, when receiving an operation of removing all data in a specified partition in the partition table, obtain the current unique identifier of the specified partition from the metadata, record the current unique identifier as an old identifier, and update the metadata.

[0131] An index updating module 14 is configured to update the corresponding record in the global index according to the old identifier in the background.

[0132] The module referred to in the application refers to a series of computer program instruction segments capable of completing a specific function. The module is more suitable for describing the execution process of the database partition table processing than the program. The specific implementation of each module is described in the above method embodiments, and will not be described here.

[0133] Another embodiment of the application provides a database partition table processing system, as shown in Figure 8As shown, the system 10 includes:

[0134] one or more processors 110 and memory 120, Figure 8 In an example embodiment, the processor 110 and the memory 120 can be connected by a bus or other means, Figure 8 In an example embodiment, the connection by a bus is taken as an example.

[0135] The processor 110 is configured to complete various control logics of the system 10, and can be a general processor, a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any conventional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, such as a combination of a DSP and a microprocessor, multiple microprocessors, one or more microprocessors in combination with a DSP and / or any other such configuration.

[0136] The memory 120 is a non-volatile computer readable storage medium, which can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the processing method of the database partition table in the embodiment of the present application. The processor 110 executes the non-volatile software programs, instructions and units stored in the memory 120, thereby performing various functional applications and data processing of the system 10, i.e. implementing the processing method of the database partition table in the above method embodiment.

[0137] The memory 120 can include a program storage area and a data storage area, wherein the program storage area can store an operating system and application programs required by at least one function; the data storage area can store data created according to the use of the system 10, etc. In addition, the memory 120 can include a high-speed random access memory, and can also include a non-volatile memory, such as at least one magnetic disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 can optionally include a memory remotely arranged with respect to the processor 110, and these remote memories can be connected to the system 10 through a network. Examples of the above network include but are not limited to the Internet, an intranet, a local area network, a mobile communication network and a combination thereof.

[0138] One or more units are stored in the memory 120, and when executed by the one or more processors 110, the following steps are implemented:

[0139] According to a preset identification allocation strategy, a unique identification is allocated to each partition in the partition table, and the unique identification of each partition is saved in the metadata of the partition table.

[0140] According to the primary key of the partition table and the unique identification of each partition, a global index of the partition table is constructed.

[0141] When a full data removal operation on a specified partition in the partition table is received, the current unique identification of the specified partition is obtained from the metadata, the current unique identification is recorded as an old identification, and the metadata is updated.

[0142] In the background, the corresponding record in the global index is updated according to the old identification.

[0143] In one embodiment, according to a preset identification allocation strategy, a unique identification is allocated to each partition in the partition table, and the unique identification of each partition is saved in the metadata of the partition table, comprising:

[0144] When a partition is created, a unique value is generated according to at least one attribute of the currently created partition according to a preset algorithm, as the unique identification of the currently created partition;

[0145] The above steps of generating and allocating a unique value are repeatedly executed until a unique identification is allocated to each partition in the partition table.

[0146] The unique identification of each partition is saved in the metadata of the partition table.

[0147] In one embodiment, according to a preset identification allocation strategy, a unique identification is allocated to each partition in the partition table, and the unique identification of each partition is saved in the metadata of the partition table, comprising:

[0148] A partition identification field is created and maintained at the table level, and the value of the partition identification field is used to represent the next available identification of the partition table;

[0149] The current value of the partition identification field is allocated to a partition in the partition table as the unique identification of the partition;

[0150] The value of the partition identification field is updated according to a preset rule, and the updated value of the partition identification field is allocated to the next partition as the unique identification of the next partition;

[0151] The above steps of updating and allocating the value of the field are repeatedly executed until a unique identification is allocated to each partition in the partition table.

[0152] The unique identification of each partition and the partition identification field are saved in the metadata of the partition table.

[0153] In one embodiment, when receiving a full data removal operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as an old identifier, and the metadata is updated, including:

[0154] When receiving a truncate operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata according to the partition name of the specified partition;

[0155] According to the preset identifier allocation strategy, a new identifier of the specified partition is determined;

[0156] All data in the specified partition is removed, the current unique identifier of the specified partition is recorded as an old identifier, and the new identifier is allocated to the specified partition;

[0157] The metadata after the truncate partition is updated according to the new identifier of the specified partition;

[0158] According to the table name of the partition table and the currently recorded old identifier, a corresponding to-be-cleaned task is generated and added to a to-be-cleaned identifier task list.

[0159] In one embodiment, when receiving a full data removal operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as an old identifier, and the metadata is updated, including:

[0160] When receiving a drop operation on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata according to the partition name of the specified partition;

[0161] All data in the specified partition is removed, and the specified partition is deleted, the current unique identifier of the specified partition is recorded as an old identifier;

[0162] The metadata after the drop partition is updated;

[0163] According to the table name of the partition table and the currently recorded old identifier, a corresponding to-be-cleaned task is generated and added to a to-be-cleaned identifier task list.

[0164] In one embodiment, the updating, in the background, of the corresponding record in the global index according to the old identifier includes:

[0165] According to the table name of the partition table and all the currently recorded old identifiers, a to-be-cleaned identifier task list is generated;

[0166] The control background cleaning thread executes a cleaning task at a specified time, and extracts each table name and old identifier from the to-be-cleaned identifier task list in sequence, wherein the specified time includes the time of generating the to-be-cleaned task and / or every preset time.

[0167] According to the table name and old identifier extracted each time, the index record with the unique identifier equal to the extracted old identifier in the global index is deleted correspondingly until all index records corresponding to the old identifier are deleted.

[0168] In an embodiment, after the corresponding record in the global index is updated according to the old identifier in the background, the method further comprises:

[0169] When receiving a new data insertion operation on the partition table, an index record to be inserted is constructed according to the unique identifier of the partition corresponding to the new data to be inserted;

[0170] The to-be-inserted index record is inserted into the global index, and whether there is an index conflict is detected;

[0171] If there is an index conflict, whether the current index conflict is a negligible conflict is determined according to the unique identifier of the partition in the conflict index record.

[0172] If it is a negligible conflict, the to-be-inserted data is inserted into the partition table.

[0173] The embodiment of the present application provides a non-volatile computer readable storage medium, and the computer readable storage medium stores computer executable instructions, the computer executable instructions are executed by one or more processors, for example, the method steps S101 to S104 in the above description Figure 1 are executed.

[0174] As an example, the non-volatile storage medium can include a read-only memory (ROM), a programmable ROM (PROM), an electrically programmable ROM (EPROM), an electrically erasable ROM (EEPROM) or a flash memory. The volatile memory can include a random access memory (RAM) as an external cache memory. By way of illustration and not limitation, the RAM can be in many forms such as synchronous RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM) and direct Rambus (Rambus) RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable type of memory.

[0175] In conclusion, in the database partition table processing method, device, system and medium disclosed by the application, a unique identifier is allocated to each partition in the partition table according to a preset identifier allocation strategy, and the unique identifier of each partition is saved in the metadata of the partition table; a global index of the partition table is constructed according to the primary key of the partition table and the unique identifier of each partition; when a full data removal operation on a specified partition in the partition table is received, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as an old identifier and the metadata is updated; and the corresponding record in the global index is updated according to the old identifier in the background. By introducing the unique identifier of each partition in the metadata and the global index of the partition table, and recording the old identifier when performing the partition data removal operation to distinguish the new and old history, the global index remains effective while the related operation is efficiently performed, the uniqueness of the data is ensured not to be affected, and the efficiency and usability of the partition table operation are improved.

[0176] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by a computer program instructing related hardware (such as a processor, a controller, etc.) to complete, and the computer program can be stored in a non-volatile computer readable storage medium. When the computer program is executed, the processes of the above-mentioned embodiment methods can be included. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage, etc.

[0177] It should be understood that the application of the present application is not limited to the above examples, and those skilled in the art can improve or change it according to the above description, and all these improvements and changes should belong to the protection scope of the appended claims of the present application.

Claims

1. A method for processing database partitioned tables, characterized in that, include: When creating each partition in the partition table, a unique identifier is assigned to each partition in the partition table according to a preset identifier allocation strategy, and the unique identifier of each partition is saved to the metadata of the partition table. The unique identifier is at the table level; each partition within a table has a unique identifier, while tables are allowed to have the same unique identifier. A global index of the partition table is constructed based on the primary key of the partition table and the unique identifier of each partition; When a data removal operation for a specified partition in the partition table is received, the current unique identifier of the specified partition is obtained from the metadata, the current unique identifier is recorded as the old identifier, and the metadata is updated; the data removal operation is either a truncate partition operation or a drop partition operation. In the background, the corresponding record in the global index is updated based on the old identifier; The step of assigning a unique identifier to each partition in the partition table according to a preset identifier allocation strategy and saving the unique identifier of each partition to the metadata of the partition table includes: When creating a partition, a unique value is generated according to a preset algorithm based on at least one attribute of the currently created partition, which serves as the unique identifier of the currently created partition; specifically, the at least one attribute is combined with a random unique number to calculate the corresponding hash value, and a unique string is generated as the unique identifier. Repeat the steps of generating and assigning unique values ​​as described above until a unique identifier has been assigned to each partition in the partition table; The unique identifier of each partition is saved to the metadata of the partition table; When a data removal operation for a specified partition in the partition table is received, the step of obtaining the current unique identifier of the specified partition from the metadata, recording the current unique identifier as the old identifier, and updating the metadata includes: When a truncate operation is received on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata based on the partition name of the specified partition; The new identifier for the specified partition is determined according to the preset identifier allocation strategy; Remove all data from the specified partition, record the current unique identifier of the specified partition as the old identifier, and assign the new identifier to the specified partition; the new identifier and the old identifier are used to distinguish the old and new partition data before and after the truncate operation; Update the metadata of the truncated partition based on the new identifier of the specified partition; Based on the table name of the partition table and the old identifier of the current record, generate corresponding cleanup tasks and add them to the cleanup identifier task list; When a drop operation is received for a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata based on the partition name of the specified partition; Remove all data from the specified partition and delete the specified partition, then record the current unique identifier of the specified partition as the old identifier; Update the metadata after dropping a partition by removing the unique identifier of the specified partition from the metadata of the partition table. Based on the table name of the partition table and the old identifier of the current record, generate corresponding cleanup tasks and add them to the cleanup identifier task list; After updating the corresponding record in the global index in the background based on the old identifier, the method further includes: When a new data insertion operation is received for the partition table, an index record to be inserted is constructed based on the unique identifier of the partition corresponding to the data to be inserted. Insert the index record to be inserted into the global index and check for index conflicts; If an index conflict exists, the unique identifier of the partition in the conflicting index record is used to determine whether the current index conflict is an ignorable conflict. Specifically, the set of all the latest unique identifiers of each partition is obtained from the metadata of the partition table, and it is confirmed whether the unique identifier of the partition in the conflicting index record is in the set. If it is not, it is an ignorable conflict. If the conflict is negligible, then insert the data to be inserted into the partition table.

2. The method for processing database partitioned tables according to claim 1, characterized in that, The step of assigning a unique identifier to each partition in the partition table according to a preset identifier allocation strategy and saving the unique identifier of each partition to the metadata of the partition table includes: Create and maintain a partition identifier field at the table level, the value of which is used to indicate the next available identifier for the partitioned table; Assign the current value of the partition identifier field to a partition in the partition table as the unique identifier of the partition; The value of the partition identifier field is updated according to a preset rule, and the updated value of the partition identifier field is assigned to the next partition as the unique identifier of the next partition. Repeat the above steps of updating and assigning field values ​​until a unique identifier has been assigned to each partition in the partition table; The unique identifier of each partition and the partition identifier field are saved to the metadata of the partition table.

3. The method for processing database partitioned tables according to claim 1, characterized in that, The step of updating the corresponding record in the global index in the background based on the old identifier includes: Control the background cleanup thread to execute cleanup tasks at a specified time, and extract each table name and old identifier sequentially from the list of tasks to be cleaned. The specified time includes the time when the task to be cleaned was generated and / or every preset time. Based on the table name and old identifier extracted each time, delete the corresponding index records in the global index whose unique identifier is equal to the extracted old identifier, until all index records corresponding to the old identifier are deleted.

4. A processing apparatus for a database partitioned table, characterized in that, include: The partition identifier allocation module is used to assign a unique identifier to each partition in the partition table according to a preset identifier allocation strategy when creating each partition in the partition table, and to save the unique identifier of each partition to the metadata of the partition table. The unique identifier is at the table level; each partition within a table has a unique identifier, while tables are allowed to have the same unique identifier. An index building module is used to build a global index of the partition table based on the primary key of the partition table and the unique identifier of each partition; The partition identifier processing module is used to, when receiving a data removal operation for a specified partition in the partition table, obtain the current unique identifier of the specified partition from the metadata, record the current unique identifier as the old identifier, and update the metadata; the data removal operation is either a truncate partition operation or a drop partition operation. The index update module is used to update the corresponding record in the global index in the background based on the old identifier; It also includes a data insertion module, which, when a new data insertion operation is received for the partition table, constructs an index record to be inserted based on the unique identifier of the partition corresponding to the data to be inserted; inserts the index record to be inserted into the global index and detects whether there is an index conflict; if there is an index conflict, it confirms whether the current index conflict is an ignorable conflict based on the unique identifier of the partition in the conflicting index record. Specifically, it retrieves the set of all the latest unique identifiers for each partition from the metadata of the partition table, and confirms whether the unique identifier of the partition in the conflicting index record is in the set. If it is not, then it is an ignoreable conflict. If the conflict is negligible, then insert the data to be inserted into the partition table; The step of assigning a unique identifier to each partition in the partition table according to a preset identifier allocation strategy and saving the unique identifier of each partition to the metadata of the partition table includes: When creating a partition, a unique value is generated according to a preset algorithm based on at least one attribute of the currently created partition, which serves as the unique identifier of the currently created partition; specifically, the at least one attribute is combined with a random unique number to calculate the corresponding hash value, and a unique string is generated as the unique identifier. Repeat the steps of generating and assigning unique values ​​as described above until a unique identifier has been assigned to each partition in the partition table; The unique identifier of each partition is saved to the metadata of the partition table; When a data removal operation for a specified partition in the partition table is received, the step of obtaining the current unique identifier of the specified partition from the metadata, recording the current unique identifier as the old identifier, and updating the metadata includes: When a truncate operation is received on a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata based on the partition name of the specified partition; The new identifier for the specified partition is determined according to the preset identifier allocation strategy; Remove all data from the specified partition, record the current unique identifier of the specified partition as the old identifier, and assign the new identifier to the specified partition; the new identifier and the old identifier are used to distinguish the old and new partition data before and after the truncate operation; Update the metadata of the truncated partition based on the new identifier of the specified partition; Based on the table name of the partition table and the old identifier of the current record, generate corresponding cleanup tasks and add them to the cleanup identifier task list; When a drop operation is received for a specified partition in the partition table, the current unique identifier of the specified partition is obtained from the metadata based on the partition name of the specified partition; Remove all data from the specified partition and delete the specified partition, then record the current unique identifier of the specified partition as the old identifier; Update the metadata after dropping a partition by removing the unique identifier of the specified partition from the metadata of the partition table. Based on the table name of the partition table and the old identifier of the current record, generate corresponding cleanup tasks and add them to the cleanup identifier task list.

5. A database partition table processing system, characterized in that, The system includes at least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the database partition table processing method according to any one of claims 1-3.

6. A non-volatile computer-readable storage medium, characterized in that, The non-volatile computer-readable storage medium stores computer-executable instructions that, when executed by one or more processors, cause the one or more processors to perform the database partition table processing method according to any one of claims 1-3.

Images