Database partition table processing method, device and system and medium

Abstract

The invention discloses a database partition table processing method, device and system and a medium, and the method comprises the steps: distributing a unique identifier for each partition in a partition table according to a preset identifier distribution strategy, and storing the unique identifier of each partition in 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 an operation of removing all data of a specified partition in the partition table is received, obtaining a 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 in the background according to the old identifier. The unique identifier of each partition is introduced into the metadata and the global index of the partition table, and the old identifier is recorded to distinguish the new history and the old history when the partition data removal operation is executed, so that the global index is still kept effective while the related operation is efficiently executed, the data uniqueness is ensured not to be influenced, and the efficiency and the usability of the partition table operation are improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, 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: One way is to let the global index be invalid after performing the drop partition, truncate partition and other DDL operations, and the global index is reestablished according to the drop partition, truncate partition and other DDL operations, and then set to be valid again. But this reestablishment operation will affect the execution of DML operation, for example, when inserting a new record into the partition table, the uniqueness cannot be correctly judged because the global index is invalid.

[0004] 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.

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

[0006] 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 validity of the global index, and ensuring that the data uniqueness is not affected.

[0007] The technical scheme of the present application is as follows: A processing method of a database partitioned table, comprising: allocating a unique identifier to each partition in the partitioned table according to a preset identifier allocation strategy, and saving the unique identifier of each partition into metadata of the partitioned table; constructing a global index of the partitioned table according to a primary key of the partitioned table and the unique identifier of each partition; when receiving a whole data removal operation on a specified partition in the partitioned table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata; updating a corresponding record in the global index according to the old identifier in the background.

[0008] In one embodiment, the step of allocating a unique identifier to each partition in the partitioned table according to a preset identifier allocation strategy, and saving the unique identifier of each partition into metadata of the partitioned table, comprises: when creating a partition, generating a unique value as a unique identifier of the created partition according to at least one attribute of the created partition and a preset algorithm; repeating the above steps of generating and allocating a unique value until a unique identifier is allocated to each partition in the partitioned table; saving the unique identifier of each partition into the metadata of the partitioned table.

[0009] In one embodiment, the step of allocating a unique identifier to each partition in the partitioned table according to a preset identifier allocation strategy, and saving the unique identifier of each partition into metadata of the partitioned table, comprises: creating and maintaining a partition identifier field at a table level, a value of the partition identifier field being used to represent a next available identifier of the partitioned table; allocating a current value of the partition identifier field to a partition in the partitioned table as a unique identifier of the partition; updating the value of the partition identifier field according to a preset rule, and allocating the updated value of the partition identifier field to a next partition as a unique identifier of the next partition; repeating the above steps of updating and allocating a value of the field until a unique identifier is allocated to each partition in the partitioned table; saving the unique identifier of each partition and the partition identifier field into the metadata of the partitioned table.

[0010] In one embodiment, the step of, when receiving a whole data removal operation on a specified partition in the partitioned table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata, comprises: When receiving a truncate operation on a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata according to a partition name of the specified partition; allocating a new identifier to the specified partition according to the preset identifier allocation strategy; removing all data in the specified partition, recording the current unique identifier of the specified partition as an old identifier, and allocating the new identifier to the specified partition; updating the metadata after the truncate operation according to the new identifier of the specified partition; generating a corresponding cleaning task according to a table name of the partition table and the recorded old identifier, and adding the cleaning task to a cleaning identifier task list.

[0011] In an embodiment, when receiving a remove-all-data operation on a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier, and updating the metadata, comprises: When receiving a drop operation on a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata according to a partition name of the specified partition; removing all data in the specified partition and deleting the specified partition, and recording the current unique identifier of the specified partition as an old identifier; updating the metadata after the drop operation; generating a corresponding cleaning task according to a table name of the partition table and the recorded old identifier, and adding the cleaning task to a cleaning identifier task list.

[0012] In an embodiment, the updating, in the background, of the corresponding record in the global index according to the old identifier, comprises: controlling a background cleaning thread to execute a cleaning task at a specified time, and extracting a table name and an old identifier from the cleaning identifier task list in sequence, wherein the specified time comprises a time when the cleaning task is generated and / or every preset time; according to the extracted table name and old identifier, deleting an index record 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.

[0013] In an embodiment, after the updating, in the background, of the corresponding record in the global index according to the old identifier, the method further comprises: When receiving a new data insertion operation on the partition table, constructing an index record to be inserted according to a unique identifier of a partition corresponding to the new data to be inserted; inserting the to-be-inserted index record into the global index, and detecting whether there is an index conflict; If there is an index conflict, determining whether the current index conflict is a negligible conflict according to a unique identifier of a partition in the conflict index record; If it is a negligible conflict, inserting the to-be-inserted data into the partition table.

[0014] A processing apparatus of a database partition table, comprising: 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; 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; a partition identifier processing module configured to, when receiving a data removal operation for 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; an index updating module configured to update a corresponding record in the global index according to the old identifier in the background.

[0015] A processing system of a database partition table, the system comprising at least one processor; and, a memory in communication connection with the at least one processor; wherein, 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 execute the above-mentioned processing method of the database partition table.

[0016] A non-volatile computer readable storage medium, the non-volatile computer readable storage medium storing computer executable instructions, the computer executable instructions being executed by one or more processors to enable the one or more processors to execute the above-mentioned processing method of the database partition table.

[0017] Beneficial effects: The application discloses a database partition table processing method, device, system and medium, compared with the prior art, the embodiment of the application allocates a unique identifier to each partition in the partition table according to a preset identifier allocation strategy, and saves the unique identifier of each partition to 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 receiving a whole 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; 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 still remains effective while the related operation is efficiently performed, the uniqueness of data is ensured not to be affected, and the efficiency and usability of the partition table operation are improved. BRIEF DESCRIPTION OF DRAWINGS

[0018] The application will be further described below in combination with the drawings and embodiments, and the drawings are as follows: Figure 1 A flowchart of the database partition table processing method provided by the embodiment of the application; Figure 2 A flowchart of step S101 in the database partition table processing method provided by the embodiment of the application; Figure 3 Another flowchart of step S101 in the database partition table processing method provided by the embodiment of the application; Figure 4 A flowchart of step S103 in the database partition table processing method provided by the embodiment of the application; Figure 5 Another flowchart of step S103 in the database partition table processing method provided by the embodiment of the application; Figure 6 A flowchart of step S104 in the database partition table processing method provided by the embodiment of the application; Figure 7 A functional module schematic diagram of the database partition table processing device provided by the embodiment of the application; Figure 8 A hardware structure schematic diagram of the database partition table processing system provided by the embodiment of the application. DETAILED DESCRIPTION

[0019] For the purposes of the present application, technical solutions and effects, the following will be further described in detail. It should be understood that the specific embodiments described herein are only used to explain the present application, and are not used to limit the present application. The embodiments of the present application are described below with reference to the accompanying drawings.

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

[0021] The 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 it is set to be effective 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 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 executing these DDL operations, resulting in that the business cannot be normally operated.

[0022] In order to solve the above problems, the present application provides a processing method for a database partition table, as shown in Figure 1 The processing method for the database partition table provided by the embodiment includes the following steps: S101, 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.

[0023] 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.

[0024] 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.

[0025] 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.

[0026] 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.

[0027] 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_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 historical partitions 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.

[0028] 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 synchronous deletion of the global index in the prior art, and ensures the database operation efficiency and the validity of the global index.

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

[0030] 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.

[0031] 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 the data uniqueness is not affected, and therefore the execution of other DML operations is 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.

[0032] In one embodiment, as shown in FIG. 1, step S101 comprises: Figure 2 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; S202, repeating the steps of generating and assigning the unique value until each partition in the partition table is assigned a unique identifier; S203, saving the unique identifier of each partition to the metadata of the partition table.

[0033] 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 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.

[0034] ​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 identity each time until the assignment of unique identities 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.

[0035] In one embodiment, as shown in FIG. 1, step S101 comprises: Figure 3 S301, creating and maintaining a partition identification field at the table level, the value of the partition identification field being used to represent the next available identity of the partition table; S302, assigning the current value of the partition identification field to one partition in the partition table as the unique identity of the partition; S303, updating the value of the partition identification field according to a preset rule, and assigning the updated value of the partition identification field to the next partition as the unique identity of the next partition; S304, repeatedly performing the above steps of field value updating and assigning until each partition in the partition table is assigned with a unique identity; S305, saving the unique identity of each partition and the partition identification field to the metadata of the partition table.

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

[0037] ​In creating the partition and assigning the unique identifier, 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, i.e. if the current value is N, then after assigning to a partition, the value of the field is updated to N+1, which represents the next available identifier of the partition table as N+1, ensuring that the identifier of each partition is unique.

[0038] The above steps are repeated until all partitions in the partition table are assigned a unique identifier 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.

[0039] 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. to ensure the persistence of the 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, i.e. 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 during the partition operation, ensuring the operation efficiency.

[0040] In one embodiment, as shown in Figure 4 Step S103 includes: 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; S402, confirming the new identifier of the specified partition according to the preset identifier allocation strategy; S403, removing all data in the specified partition, recording the current unique identifier of the specified partition as the old identifier, and assigning the new identifier to the specified partition; S404, updating the metadata after the truncate partition according to the new identifier of the specified partition; S405, generating a corresponding to-be-cleaned task according to the table name of the partition table and the old identifier of the current record, and adding the to-be-cleaned task to a to-be-cleaned identifier task list.

[0041] In the 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.

[0042] And based on different preset identifier allocation strategies, the new identifier of the specified partition is confirmed, wherein if the current identifier allocation strategy uses a string as the unique identifier, a new unique value is generated according to the preset algorithm in the budget identifier allocation strategy according to the attributes of the specified partition, 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 uses a numerical value as the 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.

[0043] The partition name of the specified partition is extracted from the first SQL statement, the current unique identifier partition uuid of the specified partition is obtained from 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 the old identifier old partition uuid, the old identifier is no longer used in the partition of the partition table, and the newly confirmed new identifier is allocated to the specified partition as the new identifier, thereby distinguishing the old and new partition data before and after the truncate operation through the new and old identifiers, so that the truncate operation can be quickly executed and the data uniqueness can be maintained without synchronously updating the global index.

[0044] 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, so that the structure and state of the partition table can be correctly restored after system restart or fault recovery.

[0045] 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.

[0046] In one embodiment, as shown in FIG. 1, step S103 includes: Figure 5 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; 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; S503, updating the metadata after the drop partition; 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.

[0047] 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 is 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.

[0048] ​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.

[0049] 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 executing the drop partition 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.

[0050] In one embodiment, as shown in FIG. 10, step S104 includes: Figure 6 S601, controlling the background cleaning thread to execute the cleaning task at a specified time, and extracting the table name and the old identifier from the cleaning identifier task list in sequence, wherein the specified time includes the time when the cleaning task is generated and / or every preset time; S602, according to the table name and the 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.

[0051] ​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 cleaning tasks 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 can be 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 on the basis of generating the to-be-cleaned task and waking up the background cleaning thread, to avoid the loss of the wake-up signal, the background cleaning thread will also actively check 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.

[0052] 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.

[0053] In one embodiment, after step S104, the method further comprises: 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; inserting the to-be-inserted index record into the global index and detecting whether there is an index conflict; 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; If it is a negligible conflict, the to-be-inserted data is inserted into the partition table.

[0054] 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.

[0055] 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.

[0056] 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 an error will be returned. 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.

[0057] In the embodiment, the unique identifier of the partition enables the global index to distinguish the data of different partitions. 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.

[0058] 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 that the global index table is invalid after the table data is recovered.

[0059] 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.

[0060] Another embodiment of the application provides a database partition table processing device, as shown in Figure 7 The device 1 comprises: 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. 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. 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. An index updating module 14 is configured to update the corresponding record in the global index according to the old identifier in the background.

[0061] 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 embodiment, which will not be described here.

[0062] Another embodiment of the application provides a database partition table processing system, as shown in Figure 8 The system 10 comprises: One or more processors 110 and a memory 120, Figure 8The processor 110 and the memory 120 can be connected through a bus or other means, Figure 8 The processor 110 and the memory 120 can be connected through a bus or other means,

[0063] 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 devices, discrete gates or transistor logic, discrete hardware components, 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.

[0064] The memory 120 is a non-volatile computer readable storage medium, and can be used to store non-volatile software programs, non-volatile computer executable programs, and modules, such as program instructions corresponding to the database partition table processing method 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 function applications and data processing of the system 10, i.e., implementing the database partition table processing method in the above method embodiment.

[0065] 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; and 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.

[0066] 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: According to the 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; 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; when receiving a full data removal operation on a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata; updating a corresponding record in the global index according to the old identifier in the background.

[0067] In one embodiment, a 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 in the metadata of the partition table, including: When a partition is created, a unique value is generated according to at least one attribute of the current created partition according to a preset algorithm, as the unique identifier of the current created partition; The above steps of generating and allocating a unique value are repeatedly executed until a unique identifier is allocated to each partition in the partition table; The unique identifier of each partition is saved in the metadata of the partition table.

[0068] In one embodiment, a 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 in the metadata of the partition table, including: A partition identifier field is created and maintained at the table level, and the value of the partition identifier field is used to represent the next available identifier of the partition table; The current value of the partition identifier field is allocated 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 allocated to the next partition as the unique identifier of the next partition; The above steps of updating and allocating the value of the field are repeatedly executed until a unique identifier is allocated to each partition in the partition table; The unique identifier of each partition and the partition identifier field are saved in the metadata of the partition table.

[0069] In one embodiment, when receiving a full data removal operation on a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata, including: When receiving a truncate operation on a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata according to the partition name of the specified partition; According to the preset identifier allocation strategy, a new identifier of the specified partition is confirmed; 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; update the metadata after truncating the partition according to the new identifier of the specified partition; generate a corresponding cleaning task according to the table name of the partition table and the old identifier currently recorded, and add the cleaning task to a cleaning identifier task list.

[0070] In one embodiment, when receiving an operation of removing all data in 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: 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; remove all data in the specified partition and delete the specified partition, record the current unique identifier of the specified partition as an old identifier; update the metadata after dropping the partition; generate a corresponding cleaning task according to the table name of the partition table and the old identifier currently recorded, and add the cleaning task to a cleaning identifier task list.

[0071] In one embodiment, the updating of the corresponding record in the global index according to the old identifier in the background includes: generate a cleaning identifier task list according to the table name of the partition table and all old identifiers currently recorded; control the background cleaning thread to execute a cleaning task at a specified time, and sequentially extract each table name and old identifier from the cleaning identifier task list, the specified time including the time of generating the cleaning task and / or every preset time; according to each extracted table name and old identifier, delete the index record 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.

[0072] In one embodiment, after the updating of the corresponding record in the global index according to the old identifier in the background, the method further includes: When receiving a new data insertion operation on the partition table, construct a to-be-inserted index record according to the unique identifier of the partition corresponding to the to-be-inserted new data; insert the to-be-inserted index record into the global index, and detect whether there is an index conflict; If there is an index conflict, determine whether the current index conflict is a negligible conflict according to the unique identifier of the partition in the conflict index record. If the conflict is negligible, the to-be-inserted data is inserted into the partition table.

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

[0074] By way of example, non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM), as external cache memory. By way of illustration, RAM can be available at 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 RAM (DRRAM), etc. The disclosed memory components or memory of the operational environment described herein are intended to encompass one or more of these and / or any other suitable types of memory.

[0075] In summary, in the database partition table processing method, device, system and medium disclosed by the present application, the method allocates a unique identifier to each partition in the partition table according to a preset identifier allocation strategy, and saves the unique identifier of each partition in the metadata of the partition table; constructs 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, obtains the current unique identifier of the specified partition from the metadata, records the current unique identifier as an old identifier and updates the metadata; and updates the corresponding record in the global index according to the old identifier in the background. By introducing the unique identifier of each partition in the metadata and 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 the related operation, ensuring that the data uniqueness is not affected, and improving the efficiency and availability of the partition table operation.

[0076] ​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 instructing relevant hardware (such as a processor, a controller, etc.) through a computer program. The computer program can be stored in a non-volatile computer readable storage medium, and when the computer program is executed, the processes of the above-mentioned method embodiments can be included. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage, etc.

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

Claims

1. A method for processing a database partition table, characterized in that: include: Assign a unique identifier to each partition in the partition table according to a preset identifier assignment strategy, and save 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 an operation to remove all data of a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier and updating the metadata; The corresponding record in the global index is updated in the background according to the old identifier.

2. The method for processing a database partition table according to claim 1, wherein: The step of assigning a unique identifier to each partition in the partition table according to a preset identifier assignment strategy and saving the unique identifier of each partition in 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 partition being created, which serves as the unique identifier of the partition being created. Repeat the steps of generating and assigning the unique value until a unique identifier is assigned to each partition in the partition table; The unique identifier of each partition is saved in the metadata of the partition table.

3. The method for processing a database partition table according to claim 1, wherein: The step of assigning a unique identifier to each partition in the partition table according to a preset identifier assignment strategy and saving the unique identifier of each partition in the metadata of the partition table includes: Creating and maintaining a partition identifier field at the table level, wherein the value of the partition identifier field is used to represent the next available identifier of the partition table; Assigning the current value of the partition identification field to a partition in the partition table as a unique identifier of the partition; Update the value of the partition identification field according to a preset rule, and assign the updated value of the partition identification field to the next partition as the unique identifier of the next partition; The above field value updating and assignment steps are executed cyclically until a unique identifier is assigned to each partition in the partition table; The unique identifier of each partition and the partition identifier field are saved in the metadata of the partition table.

4. The method for processing a database partition table according to claim 1, wherein: When receiving an operation to remove all data of a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier, and updating the metadata, includes: When a truncate operation is received for a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata according to the partition name of the specified partition; Confirming the new identifier of the designated partition according to the preset identifier allocation strategy; Remove all data in the designated partition, record the current unique identifier of the designated partition as the old identifier, and assign the new identifier to the designated partition; Update the metadata of the truncate partition according to the new identifier of the designated partition; According to the table name of the partition table and the old identifier of the current record, a corresponding task to be cleaned up is generated and added to a task list of identifiers to be cleaned up.

5. The method for processing a database partition table according to claim 1, wherein: When receiving an operation to remove all data of a specified partition in the partition table, obtaining a current unique identifier of the specified partition from the metadata, recording the current unique identifier as an old identifier, and updating the metadata, includes: When a drop operation is received for a specified partition in the partition table, a current unique identifier of the specified partition is obtained from the metadata according to the partition name of the specified partition; Remove all data in the specified partition and delete the specified partition, and record the current unique identifier of the specified partition as the old identifier; Update the metadata after dropping the partition; According to the table name of the partition table and the old identifier of the current record, a corresponding task to be cleaned up is generated and added to a task list of identifiers to be cleaned up.

6. The method for processing a database partition table according to claim 4 or 5, characterized in that: Updating the corresponding record in the global index according to the old identifier in the background includes: Control the background cleaning thread to execute the cleaning task at a specified time, and extract each table name and old identifier from the task list of to-be-cleaned identifiers in sequence, wherein the specified time includes the time when the to-be-cleaned task is generated and / or every preset time; According to the table name and old identifier extracted each time, the index record whose unique identifier in the global index is equal to the extracted old identifier is deleted until all index records corresponding to the old identifier are deleted.

7. The method for processing a database partition table according to claim 1, wherein: After updating the corresponding record in the global index according to the old identifier in the background, the method further includes: When a new data insertion operation is received for the partition table, an index record to be inserted is constructed according to the unique identifier of the partition corresponding to the data to be inserted; Inserting the to-be-inserted index record into the global index and detecting whether there is an index conflict; If there is an index conflict, determine whether the current index conflict is ignorable based on the partition's unique identifier in the conflicting index record. If the conflict is negligible, insert the data to be inserted into the partition table.

8. A database partition table processing device, characterized in that: include: A partition identifier allocation module is used 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; An index building module, configured 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; a partition identifier processing module configured to, upon receiving an operation to remove all data from a specified partition in the partition table, obtain a current unique identifier of the specified partition from the metadata, record the current unique identifier as an old identifier, and update the metadata; The index updating module is used to update the corresponding record in the global index according to the old identifier in the background.

9. 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. The instructions are executed by the at least one processor to enable the at least one processor to execute the database partition table processing method according to any one of claims 1 to 7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the database partition table processing method according to any one of claims 1 to 7.

Images