Data deletion method, device and equipment for dynamically adjusting single-batch deletion amount

Abstract

The invention discloses a data deletion method, device and equipment for dynamically adjusting single-batch deletion quantity. Comprising the steps of averagely dividing to-be-deleted data in a data table into M1 batches; in the process of deleting the to-be-deleted data in batches, after N batches of to-be-deleted data are deleted every time, the single-batch deletion amount is updated based on the current CPU usage rate and the current memory usage rate of the server, batches of the remaining to-be-deleted data are divided again based on the latest single-batch deletion amount, and batch deletion operation is executed. The data to be deleted are deleted completely; specifically, based on the maximum index value in the current CPU usage rate and the current memory usage rate, a pressure state interval corresponding to the server is determined, and a pressure adjustment coefficient is determined based on an interval adjustment coefficient associated with the pressure state interval; and multiplying the pressure adjustment coefficient by the single-batch deletion quantity of the latest deletion operation to obtain the latest single-batch deletion quantity.

Description

Technical Field

[0001] The present application relates to the field of Internet technology, in particular to the field of big data processing technology, and in particular to a data deletion method, device, equipment and medium for dynamically adjusting the deletion amount of a single batch. Background Art

[0002] At present, in the process of using the database, with the continuous application and update of the business system, it is often necessary to delete the specified data from a large-capacity data table. The specified data may be data that is no longer needed or data that needs to be archived to other data tables. When performing data deletion operations on the database and deleting large table data, a common method is to delete the data in the table directly. However, when deleting data, the data table is locked. When the amount of data to be deleted is large, the traditional deletion method will inevitably occupy the data table for a long time, which will increase the waiting time for writing business data, resulting in long-term system freezes and response delays.

[0003] Based on this, in the related art, the data to be deleted can be simply deleted in batches, and each batch deletion operation is used to delete a fixed amount of data to be deleted. However, when the fixed data amount is set too high, a single batch deletion will put pressure on the server and affect the server operation. When the fixed data amount is set too small, multiple batch deletions will still occupy the data table for a long time, and the data deletion efficiency is low. Summary of the invention

[0004] In view of this, the embodiments of the present application provide a data deletion method, device, equipment, medium and product for dynamically adjusting the deletion amount of a single batch, which can dynamically adjust the deletion amount of a single batch during batch deletion, thereby improving data deletion efficiency while reducing the pressure on the server.

[0005] In a first aspect, an embodiment of the present application provides a data deletion method for dynamically adjusting a single-batch deletion amount, the method comprising: dividing the data to be deleted in a data table into M1 batches on average; in the process of deleting the data to be deleted in batches, after deleting each N batches of the data to be deleted, updating the single-batch deletion amount based on the current CPU usage and current memory usage of the server, and re-dividing the batches of the remaining data to be deleted based on the latest single-batch deletion amount, and performing batch deletion operations until the deletion of the data to be deleted is completed; wherein, updating the single-batch deletion amount based on the current CPU usage and current memory usage of the server comprises: determining the pressure state interval corresponding to the server based on the maximum index value of the current CPU usage and the current memory usage, and determining the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval, wherein the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, the interval adjustment coefficient is negatively correlated with the interval value, and M1 is greater than N; multiplying the pressure adjustment coefficient by the single-batch deletion amount of the most recent deletion operation to obtain the latest single-batch deletion amount.

[0006] In the second aspect, an embodiment of the present application provides a data deletion device for dynamically adjusting the single-batch deletion amount, the device comprising: a batch division module, for evenly dividing the data to be deleted in the data table into M1 batches; a batch deletion module, for updating the single-batch deletion amount based on the current CPU usage and current memory usage of the server after deleting each N batches of data to be deleted in the process of deleting the data to be deleted in batches, and re-dividing the batches of the remaining data to be deleted based on the latest single-batch deletion amount, and performing batch deletion operations until the deletion of the data to be deleted is completed; wherein the batch deletion module is specifically used to: determine the pressure state interval corresponding to the server based on the maximum indicator value of the current CPU usage and the current memory usage, and determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval, wherein the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, the interval adjustment coefficient is negatively correlated with the interval value, and M1 is greater than N; multiply the pressure adjustment coefficient by the single-batch deletion amount of the most recent deletion operation to obtain the latest single-batch deletion amount.

[0007] In a third aspect, an embodiment of the present application provides an electronic device, comprising: a processor and a memory storing computer program instructions; when the processor executes the computer program instructions, the steps of the data deletion method for dynamically adjusting the single batch deletion amount as in the first aspect are implemented.

[0008] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having computer program instructions stored thereon. When the computer program instructions are executed by a processor, the steps of the data deletion method for dynamically adjusting the single batch deletion amount as in the first aspect are implemented.

[0009] In a fifth aspect, an embodiment of the present application provides a computer program product, which is stored in a non-volatile storage medium. When the computer program product is executed by a processor, it implements the steps of the data deletion method for dynamically adjusting the single batch deletion amount as in the first aspect.

[0010] In a sixth aspect, an embodiment of the present application provides a chip comprising a processor and a communication interface, wherein the communication interface and the processor are coupled, and the processor is used to run a program or instruction to implement the steps of the data deletion method for dynamically adjusting the single batch deletion amount as in the first aspect.

[0011] The present application provides a data deletion method, device, equipment, medium and product for dynamically adjusting the single batch deletion amount, which divides the data to be deleted in the data table into M1 batches evenly. In the process of deleting the data to be deleted in batches, after deleting each N batches of data to be deleted, the single batch deletion amount is updated based on the current CPU usage and current memory usage of the server, and the remaining data to be deleted are re-divided into batches based on the latest single batch deletion amount, and the batch deletion operation is performed until the deletion of the data to be deleted is completed. That is, N batch deletion operations are a round, and each round of batch deletion operations is performed, and the single batch deletion amount of the next round of batch deletion operations is updated once. The specific update method is to determine the pressure state interval corresponding to the server based on the maximum index value of the current CPU usage and the current memory usage, and then determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval. Since the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, and the interval adjustment coefficient is negatively correlated with the interval value, it can be seen that the pressure adjustment coefficient is negatively correlated with the interval value, that is, the pressure adjustment coefficient is negatively correlated with the current CPU usage and the current memory usage. On this basis, the pressure adjustment coefficient is multiplied by the single batch deletion amount of the most recent deletion operation. Based on the real-time pressure status of the server, the single batch deletion amount during the batch deletion operation is dynamically adjusted. When the current pressure on the server is high, the single batch deletion amount of the next round of batch deletion operations can be appropriately reduced to alleviate the server pressure. When the current pressure on the server is low, the single batch deletion amount of the next round of batch deletion operations can be appropriately increased. More data to be deleted can be deleted in batches at one time, improving data deletion efficiency and shortening the data deletion cycle, thereby shortening the time occupied by the data table and avoiding long system freezes and response delays due to long waiting times when writing business data. BRIEF DESCRIPTION OF THE DRAWINGS

[0012] In order to more clearly illustrate the technical solution of the embodiments of the present application, the following briefly introduces the drawings in the embodiments of the present application.

[0013] Figure 1 It is a flowchart of a data deletion method for dynamically adjusting a single batch deletion amount provided by an embodiment of the present application;

[0014] Figure 2 is a flowchart of a data deletion method for dynamically adjusting a single batch deletion amount provided by another embodiment of the present application;

[0015] Figure 3 It is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0016] The principles and spirit of the present application will be described below with reference to several exemplary embodiments. It should be understood that the purpose of providing these embodiments is to make the principles and spirit of the present application clearer and more thorough, so that those skilled in the art can better understand and implement the principles and spirit of the present application. The exemplary embodiments provided herein are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments herein, all other embodiments obtained by ordinary technicians of the art without creative work are within the scope of protection of this application.

[0017] In this document, terms such as first, second, third, etc. are only used to distinguish one entity (or operation) from another entity (or operation), but not to require or imply any order or relationship between these entities (or operations).

[0018] The data deletion method for dynamically adjusting the single batch deletion amount provided in the embodiment of the present application is described in detail below with reference to the accompanying drawings through specific embodiments and their application scenarios.

[0019] Figure 1 It is a flow chart of a data deletion method for dynamically adjusting the deletion amount in a single batch provided by an embodiment of the present application. The executor of the data deletion method for dynamically adjusting the deletion amount in a single batch may be a database server in a business system, which is hereinafter referred to as the server for the sake of simplicity.

[0020] The following takes the execution subject of the data deletion method for dynamically adjusting the single batch deletion amount as an example, and describes the data deletion method for dynamically adjusting the single batch deletion amount of the present application. It should be noted that the above execution subject and application scenario do not constitute a limitation on the present application.

[0021] like Figure 1 As shown, the data deletion method for dynamically adjusting the single batch deletion amount provided in the embodiment of the present application may include steps 110 to 120.

[0022] Step 110, divide the data to be deleted in the data table into M1 batches evenly;

[0023] Step 120, in the process of deleting the data to be deleted in batches, after deleting each N batches of data to be deleted, update the single batch deletion amount based on the current CPU usage and current memory usage of the server, and re-divide the remaining data to be deleted into batches based on the latest single batch deletion amount, and perform batch deletion operations until the data to be deleted is deleted.

[0024] The data deletion method for dynamically adjusting the single batch deletion amount provided in the embodiment of the present application divides the data to be deleted in the data table into M1 batches on average. In the process of deleting the data to be deleted in batches, after deleting each N batches of data to be deleted, the single batch deletion amount is updated based on the current CPU usage and current memory usage of the server, and the remaining data to be deleted are re-divided into batches based on the latest single batch deletion amount, and the batch deletion operation is performed until the deletion of the data to be deleted is completed. That is, N batch deletion operations are a round, and each time a round of batch deletion operations is performed, the single batch deletion amount of the next round of batch deletion operations is updated once. The specific update method is to determine the pressure state interval corresponding to the server based on the maximum index value of the current CPU usage and the current memory usage, and then determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval. Since the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, and the interval adjustment coefficient is negatively correlated with the interval value, it can be seen that the pressure adjustment coefficient is negatively correlated with the interval value, that is, the pressure adjustment coefficient is negatively correlated with the current CPU usage and the current memory usage. On this basis, the pressure adjustment coefficient is multiplied by the single batch deletion amount of the most recent deletion operation. Based on the real-time pressure status of the server, the single batch deletion amount during the batch deletion operation is dynamically adjusted. When the current pressure on the server is high, the single batch deletion amount of the next round of batch deletion operations can be appropriately reduced to alleviate the server pressure. When the current pressure on the server is low, the single batch deletion amount of the next round of batch deletion operations can be appropriately increased. More data to be deleted can be deleted in batches at one time, improving data deletion efficiency and shortening the data deletion cycle, thereby shortening the time occupied by the data table and avoiding long system freezes and response delays due to long waiting times when writing business data.

[0025] Furthermore, during the data deletion process, when batch deletion operations occupy more resources of the database server, the present application can reduce the single batch deletion amount of the next round of batch deletion operations, thereby avoiding situations where the CPU continues to be at a high level and memory usage continues to increase. This can thereby avoid system performance degradation caused by failure to release the CPU and memory in a timely manner, as well as system response delays due to increased I / O waiting time, thereby reducing the impact of data batch deletion operations on the overall database performance.

[0026] The specific implementation of the above steps will be described in detail below in conjunction with specific embodiments.

[0027] Involving step 110, the data to be deleted in the data table is evenly divided into M1 batches.

[0028] In step 110, M1 is a positive integer. M1 can be a custom value entered by the user each time according to the amount of data to be deleted or a pre-set fixed value, for example, M1 is set to 60, 80 or 100. Alternatively, M1 is not pre-specified, and the amount of data in a single batch divided for the first time is the preset single batch deletion amount (fixed value). For example, the preset single batch deletion amount is set to 5000 rows, then M1 is obtained after all the data to be deleted are divided into batches of 5000 rows of data.

[0029] In an example, the server may be a B-end database server or a C-end database server of an online recruitment platform. The B-end may be an online talent recruitment system, an online application recruitment system, a training system, etc. used by recruiting users. The C-end may be a job search system for fresh graduates, an online application job search system, a job recommendation system, etc. used by job seekers. The data table may be a table for storing recruitment and job search data, and the data to be deleted may be recruitment and job search data.

[0030] In some embodiments of the present application, in order to make the single batch deletion amount in the first batch division compatible with the occupation status of the data to be deleted in the data table, the above step 110 evenly divides the data to be deleted in the data table into M1 batches, which may specifically include:

[0031] Obtaining the proportion of the data to be deleted in the data table, and multiplying the proportion by a preset single batch deletion amount to obtain a first data amount;

[0032] The first data volume is used as the single batch deletion volume during the first division, and the data to be deleted is divided into batches to obtain M1 batches of data to be deleted.

[0033] Specifically, the preset single batch deletion amount can be the maximum amount of data to be deleted in a single batch, which can be set according to specific needs, for example, 5000 rows. If the proportion of the data to be deleted in the data table is 50%, the first data amount is 2500 rows, and every 2500 rows of data to be deleted are regarded as a batch during the first batch division.

[0034] In an embodiment of the present application, when all data to be deleted are divided into batches for the first time, the deletion amount of a single batch is related to the proportion of all data to be deleted in the data table. If the proportion is high, the single batch deletion amount can be appropriately increased by multiplying the proportion with the preset single batch deletion amount, so as to avoid a large number of batches obtained due to a single batch deletion amount that is too low, thereby avoiding occupying the data table for a long time when deleting the data to be deleted batch by batch subsequently.

[0035] Involving step 120, in the process of deleting the data to be deleted in batches, after deleting each N batches of data to be deleted, the single batch deletion amount is updated based on the current CPU usage and current memory usage of the server, and the remaining data to be deleted is re-divided into batches based on the latest single batch deletion amount, and the batch deletion operation is performed until the data to be deleted is deleted.

[0036] In step 120, the single batch deletion amount is updated based on the current CPU usage and the current memory usage of the server, specifically including: determining the pressure state interval corresponding to the server based on the maximum index value of the current CPU usage and the current memory usage, and determining the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval; multiplying the pressure adjustment coefficient by the single batch deletion amount of the most recent deletion operation to obtain the latest single batch deletion amount.

[0037] Among them, the pressure state interval where the maximum indicator value is located is the pressure state interval corresponding to the server. The pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, and the interval adjustment coefficient is negatively correlated with the interval value, so the pressure adjustment coefficient is also negatively correlated with the interval value. That is, the smaller the interval value of the pressure state interval corresponding to the current CPU usage and the current memory usage, the larger the interval adjustment coefficient, and accordingly, the larger the pressure adjustment coefficient. Therefore, the pressure adjustment coefficient is also negatively correlated with the current CPU usage and the current memory usage. M1 is greater than N, M1 and N are both positive integers, N is a pre-set value, N is positively correlated with M1, and the larger the M1 value, the larger the N value.

[0038] Specifically, after the server completes deletion of the first N batches of data to be deleted among M1 batches, when the latest single-batch deletion amount is obtained, the remaining data to be deleted is re-divided into batches based on the latest single-batch deletion amount, and batch deletion operations are performed from batch N+1 to batch 2N. The single-batch deletion amount is updated based on the current CPU usage and current memory usage of the server, and the remaining data to be deleted is re-divided into batches based on the latest single-batch deletion amount, and batch deletion operations are performed from batch 2N+1 to batch 3N, until the deletion of the data to be deleted is completed.

[0039] Exemplarily, M1 is 60, N is 5, and initially each batch of data to be deleted has 5,000 rows. After deleting the first five batches of data to be deleted, the single batch deletion amount is updated once. Since the current CPU usage and current memory usage of the server are both greater than 50%, after obtaining the pressure adjustment coefficient of 0.8, the single batch deletion amount is updated to 4,000 rows, and the remaining data to be deleted is divided into batches of the latest 4,000 rows. Based on this, the re-divided data to be deleted is deleted in batches, and after deleting five batches (batches 6 to 10) of data to be deleted, the single batch deletion amount is updated based on the latest acquired current CPU usage and current memory usage of the server.

[0040] It should be noted that the pressure state interval and its associated interval adjustment coefficient can be pre-set according to specific needs. For example, the pressure state interval can be set to 2, 3 or other numbers, and the interval adjustment coefficient can also be set to different values ​​according to different interval values. This application does not specifically limit the interval value and the interval adjustment coefficient.

[0041] The present application can set a preset pressure threshold, a pressure state interval and a specific value of its associated interval adjustment coefficient, as long as the pressure adjustment coefficient is greater than 1 when the maximum index value is less than the preset pressure threshold, and the pressure adjustment coefficient is less than 1 when the maximum index value is greater than or equal to the preset pressure threshold. Under the premise of not reaching the preset pressure threshold, the single batch deletion amount is increased as much as possible. The preset pressure threshold can be set to 50%, 60% or other values, for example, and the present application does not make any specific limitations on this.

[0042] In this way, during the deletion of data from a large database table, the present application intermittently detects the pressure on the database server by obtaining the current CPU usage and current memory usage of the server every N batches, and monitors and determines the fluctuations in CPU usage, memory usage, and I / O in real time during the large table deletion operation to prevent the server's operating indicators from becoming abnormal or being in a long-term peak range due to large-scale data deletion operations.

[0043] In some embodiments of the present application, each pressure state interval may be associated with an interval adjustment coefficient, and the above-mentioned determination of the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval may specifically include:

[0044] The interval adjustment coefficient associated with the pressure state interval is determined as the pressure adjustment coefficient.

[0045] Among them, for the pressure state interval whose maximum interval value is less than the preset pressure threshold, the associated interval adjustment coefficient is greater than 1, and for the pressure state interval whose minimum interval value is greater than or equal to the preset pressure threshold, the associated interval adjustment coefficient is less than 1.

[0046] Exemplarily, the preset pressure threshold can be 50%, the pressure state interval can be divided into (0, 30%), [30%, 50%), [50%, 100%), and the interval adjustment coefficients associated with the three intervals can be 2, 1.5, and 0.8, respectively. Then, when the maximum index value is less than 50%, the pressure adjustment coefficient is greater than 1, and when the maximum index value is greater than or equal to 50%, the pressure adjustment coefficient is less than 1.

[0047] In this way, when the pressure state interval in which the maximum indicator value falls reaches the preset pressure threshold, the interval adjustment coefficient less than 1 associated with the pressure state interval is used as the pressure adjustment coefficient of the current single batch deletion amount. By multiplying the two, the single batch deletion amount is reduced, effectively alleviating the CPU and memory pressure of the server. When the pressure state interval in which the maximum indicator value falls does not reach the preset pressure threshold, the interval adjustment coefficient greater than 1 associated with the pressure state interval is used as the pressure adjustment coefficient of the current single batch deletion amount. By multiplying the two, the single batch deletion amount is increased. On the premise that the server can operate normally, the single batch deletion amount of the next round of batch deletion operations is appropriately increased, and more data to be deleted is deleted in one batch, which improves data deletion efficiency and shortens the data deletion cycle, thereby shortening the time occupied by the data table and avoiding long system freezes and response delays caused by long waiting time when writing business data.

[0048] In some embodiments of the present application, each pressure state interval is associated with an interval adjustment coefficient, and the above-mentioned determination of the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval may specifically include the following steps:

[0049] Obtain the minimum interval value of the pressure state interval, and calculate the target difference between the maximum index value and the minimum interval value;

[0050] The ratio of the interval adjustment coefficient to the target difference is multiplied by the preset adjustment coefficient to obtain the pressure adjustment coefficient, wherein the pressure adjustment coefficient is negatively correlated with the target difference.

[0051] Specifically, the more the value exceeds the minimum interval, the larger the target difference is, and the smaller the corresponding pressure adjustment coefficient is. Based on this, for different maximum indicator values ​​falling into the same pressure state interval, a smaller pressure adjustment coefficient can be assigned to a larger maximum indicator value. That is, when the server pressure is high, a smaller single batch deletion amount is assigned, which helps to alleviate the server pressure.

[0052] For example, for the same state pressure interval [30%, 50%), the minimum interval value is 30%, the associated interval adjustment coefficient is 1.5, and the preset adjustment coefficient is 98. If the maximum index value is 40%, the target difference is 40%-30%=10%, and the pressure adjustment coefficient is 1.5÷0.1*98=1.53; if the maximum index value is 45%, the target difference is 45%-30%=15%, and the pressure adjustment coefficient is 1.5÷0.15*98=1.02.

[0053] In an embodiment of the present application, the pressure adjustment coefficient can be determined based on the target difference between the maximum index value and the minimum interval value of the pressure state interval in which it is located. The larger the target difference, the more the maximum index value exceeds the minimum interval value. Therefore, for different maximum index values ​​falling into the same state interval, by dividing the interval adjustment coefficient by the target difference, and multiplying the ratio by the preset adjustment coefficient, a smaller pressure adjustment coefficient can be allocated to a larger maximum index value, and a larger pressure adjustment coefficient can be allocated to a smaller maximum index value, so as to achieve a reasonable allocation of the pressure adjustment coefficient, and then the subsequent single batch deletion amount can be reasonably allocated for different maximum index values ​​falling into the same state interval. Even if the CPU usage and memory usage of the server at different times fall into the same pressure state interval one after another, it is possible to distinguish the server pressure at different times, allocate less single batch deletion amount for the time when the server pressure is greater, and allocate more single batch deletion amount for the time when the server pressure is less.

[0054] In some embodiments of the present application, after obtaining the pressure adjustment coefficient as described above, the method may further include the following steps:

[0055] When the pressure adjustment coefficient is less than 1, the preset sleep time is divided by the pressure adjustment coefficient to obtain the target sleep time;

[0056] After the target dormancy time has passed, the remaining data to be deleted will be deleted in batches.

[0057] Specifically, the preset sleep duration can be set according to specific needs, for example, set to 8s, 10s or other durations, and this application does not make specific limitations on this.

[0058] In this way, when the pressure adjustment coefficient is less than 1, it means that the server is under great pressure at this time. The smaller the pressure adjustment coefficient, the greater the pressure on the server. Based on this, the preset sleep time can be divided by the pressure adjustment coefficient. On the basis of the preset sleep time, the sleep time can be appropriately extended according to the pressure adjustment coefficient. The greater the current pressure on the server, the longer the target sleep time. The server is given enough sleep time to relieve the pressure caused by the excessive amount of single batch deletions. At the same time, it can prevent insufficient database resources caused by excessive submissions, and prepare for the next round of N batches of data deletion operations.

[0059] In some embodiments of the present application, the above step 110 of equally dividing the data to be deleted in the data table into M1 batches may specifically include:

[0060] When the data to be deleted is obtained, the primary key IDs of all the data to be deleted are saved in the intermediate temporary table; the primary key IDs of the single batch deletion amount are queried batch by batch from the intermediate temporary table by executing SQL statements;

[0061] The above-mentioned batch deletion operation may specifically include:

[0062] Based on the queried primary key ID of the single batch deletion quantity, after associating and deleting the data to be deleted corresponding to the primary key ID in the data table, query the primary key ID of the next batch and perform the data deletion operation on the next batch of data to be deleted.

[0063] Specifically, by executing SQL statements, the primary key IDs of the single batch deletion quantity can be queried batch by batch according to the primary key values ​​from the intermediate temporary table. Each time the primary key IDs of the single batch deletion quantity are queried, a primary key ID set is constructed, and based on the primary key ID set, a single batch deletion operation is performed on the data to be deleted in the data table. After the deletion is completed, the primary key IDs of the next batch are queried for associated deletion operations until all the data to be deleted are deleted.

[0064] It should be noted that during the cyclic deletion process of the data to be deleted, the deletion amount of a single batch is still updated at a frequency of N batches / times.

[0065] As a specific example, the t_order table stores 32 days of data, from 2024-09-01 to 2024-10-02. The automatic deletion operation is started at the early morning of 2024-10-03, and the data generated on 2024-09-01 is determined as the data to be deleted, and the deletion operation is performed in batches. The primary key ID of the data on 2024-09-01 can be written into the intermediate temporary table tmp_order_20240901, and the minimum and maximum primary key values ​​of all primary key IDs are written into variables. For example, the maximum primary key value on 2024-09-01 is 3029456, the minimum primary key value is 329456, the amount of data to be deleted is 3 million rows, and the first single batch deletion amount is 50,000 rows. Then, a primary key ID set is constructed by querying 50,000 primary key IDs through SQL statements, and 50,000 rows of data to be deleted corresponding to 50,000 primary key IDs are deleted. After the deletion operation is completed, 50,000 primary key IDs are cyclically queried from the remaining primary key IDs in the intermediate temporary table, and the single batch deletion operation is cyclically executed until all 3 million rows of data are deleted.

[0066] In this way, after the primary key ID data is written into an intermediate temporary table for storage, the primary key ID of the single batch deletion amount is queried from the intermediate temporary table batch by batch by executing SQL statements. Compared with directly querying the data to be deleted in the data table and dividing it into batches, the amount of primary key ID data is smaller. Therefore, directly querying and batching the primary key ID in the intermediate temporary table can effectively reduce the number of data queries and operations and reduce resource consumption. In the actual deletion process, each time a batch of primary key ID sets is queried, the data to be deleted corresponding to the primary key ID set is deleted from the data table in batches. After the deletion is completed, the next batch of ID data sets is entered. In this way, the corresponding CPU, memory, and I / O resource consumption of the entire database are relatively low, and there is basically no long-term resource occupation and table lock state. In addition, the SQL statement used for the deletion operation is based on the table primary key, which is efficient and fast, and the impact on the database is controllable, which can reduce the business impact of the data deletion operation on the data table.

[0067] In addition, by copying the primary key ID of the data to be deleted to the intermediate temporary table, the data can be deleted in association. In the event of a database failure, the deletion script operation can be directly restarted after the database is restored. The intermediate temporary table records the primary key ID information of the most recent deletion operation.

[0068] In some embodiments of the present application, when the data to be deleted is obtained, the primary key IDs of all the data to be deleted are saved in the intermediate temporary table, which may specifically include the following steps:

[0069] Get the proportion of data to be deleted in the data table;

[0070] When the proportion is greater than the preset proportion threshold, the primary key ID of all data to be deleted is saved in the intermediate temporary table.

[0071] Specifically, the preset proportion can be set according to specific needs, for example, set to 30%, 50% or other values, and this application does not make specific limitations on this.

[0072] In this way, the primary key ID is copied and saved only when the proportion of the data to be deleted in the data table is high, and the intermediate temporary table is used to effectively alleviate the data search pressure when deleting data in a single batch. When the proportion is low, batch deletion can be performed directly in the data table without the help of an intermediate temporary table, simplifying the data deletion operation process.

[0073] In some embodiments of the present application, the method may further include the following steps:

[0074] By counting the amount of data written to the server in the historical period, determine the low peak period of data writing to the server in a day;

[0075] Execute batch deletion of data to be deleted during the off-peak period of data writing.

[0076] Exemplarily, the data table can be the order table t_order. The t_order order table will have a large amount of order data every day. The amount of new order data generated every day is about 3 million rows. The amount of order data stored in the entire order table for 30 days is about 90 million rows. The initial design of disk storage data cannot store more than 100 million data, so the basic storage days are 32 days. After 32 days, data deletion operations are required every day, and the order data before 32 days is used as data to be deleted. Based on this, the application can count the order volume of 24 hours a day, and count the peak and valley values ​​of the number of orders. If the order valley time range is counted between 4 and 6 a.m., multiple batches of data deletion operations for the data to be deleted can be performed from 4 to 6 a.m. every day.

[0077] In this way, performing batch deletion operations on the data to be deleted during the off-peak period of data writing can reduce the impact on data writing and avoid long system freezes and response delays caused by long waiting times when writing business data.

[0078] In some embodiments of the present application, a distribution key can be set in the server, and the distribution key is used to partition the data table using reasonable dates and establish reasonable indexes to improve execution efficiency. In addition, the present application evaluates the amount of data in the data table for expired data, archived data, and data waiting to be deleted, determines the proportion of the number of rows to be deleted in the entire data table, uses deletion condition SQL statements to estimate the amount of data, and uses a perfect execution plan to determine whether the deletion is reasonable.

[0079] Corresponding to the method embodiment of the present application, the present application also provides a data deletion device for dynamically adjusting the deletion amount of a single batch.

[0080] Figure 2 Schematic diagram of a data deletion device for dynamically adjusting the single batch deletion amount provided by an embodiment of the present application. Figure 2 As shown, the data deletion device 200 for dynamically adjusting the single batch deletion amount may include: a batch division module 210 and a batch deletion module 220 .

[0081] Among them, the batch division module 210 is used to divide the data to be deleted in the data table into M1 batches on average; the batch deletion module 220 is used to update the single batch deletion amount based on the current CPU usage and current memory usage of the server after deleting N batches of data to be deleted in the process of deleting the data to be deleted in batches, and re-divide the batches of the remaining data to be deleted based on the latest single batch deletion amount, and perform batch deletion operations until the data to be deleted is deleted; wherein, the batch deletion module 220 is specifically used to: determine the pressure state interval corresponding to the server based on the maximum indicator value of the current CPU usage and the current memory usage, and determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval, wherein the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, and the interval adjustment coefficient is negatively correlated with the interval value, and M1 is greater than N; multiply the pressure adjustment coefficient by the single batch deletion amount of the most recent deletion operation to obtain the latest single batch deletion amount.

[0082] The data deletion device for dynamically adjusting the single batch deletion amount provided in the embodiment of the present application divides the data to be deleted in the data table into M1 batches on average. In the process of deleting the data to be deleted in batches, after deleting each N batches of data to be deleted, the single batch deletion amount is updated based on the current CPU usage and current memory usage of the server, and the remaining data to be deleted are re-divided into batches based on the latest single batch deletion amount, and the batch deletion operation is performed until the deletion of the data to be deleted is completed. That is, N batch deletion operations are a round, and each time a round of batch deletion operations is performed, the single batch deletion amount of the next round of batch deletion operations is updated once. The specific update method is to determine the pressure state interval corresponding to the server based on the maximum index value of the current CPU usage and the current memory usage, and then determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval. Since the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, and the interval adjustment coefficient is negatively correlated with the interval value, it can be seen that the pressure adjustment coefficient is negatively correlated with the interval value, that is, the pressure adjustment coefficient is negatively correlated with the current CPU usage and the current memory usage. On this basis, the pressure adjustment coefficient is multiplied by the single batch deletion amount of the most recent deletion operation. Based on the real-time pressure status of the server, the single batch deletion amount during the batch deletion operation is dynamically adjusted. When the current pressure on the server is high, the single batch deletion amount of the next round of batch deletion operations can be appropriately reduced to alleviate the server pressure. When the current pressure on the server is low, the single batch deletion amount of the next round of batch deletion operations can be appropriately increased. More data to be deleted can be deleted in batches at one time, improving data deletion efficiency and shortening the data deletion cycle, thereby shortening the time occupied by the data table and avoiding long system freezes and response delays due to long waiting times when writing business data.

[0083] In some embodiments of the present application, each pressure state interval is associated with an interval adjustment coefficient, and the batch deletion module is specifically used to: obtain the minimum interval value of the pressure state interval, and calculate the target difference between the maximum index value and the minimum interval value; multiply the ratio of the interval adjustment coefficient to the target difference by the preset adjustment coefficient to obtain the pressure adjustment coefficient, wherein the pressure adjustment coefficient is negatively correlated with the target difference.

[0084] In some embodiments of the present application, the batch deletion module is also used to: after obtaining the pressure adjustment coefficient, when the pressure adjustment coefficient is less than 1, divide the preset sleep time by the pressure adjustment coefficient to obtain the target sleep time; after the target sleep time is exceeded, perform batch deletion operations on the remaining data to be deleted.

[0085] In some embodiments of the present application, each pressure state interval is associated with an interval adjustment coefficient, and the batch deletion module is specifically used to: determine the interval adjustment coefficient associated with the pressure state interval as the pressure adjustment coefficient. Among them, for a pressure state interval whose maximum interval value is less than a preset pressure threshold, its associated interval adjustment coefficient is greater than 1, and for a pressure state interval whose minimum interval value is greater than or equal to the preset pressure threshold, its associated interval adjustment coefficient is less than 1.

[0086] In some embodiments of the present application, the batch partitioning module is specifically used to: obtain the proportion of the data to be deleted in the data table, and multiply the proportion by the preset single-batch deletion amount to obtain a first data amount; use the first data amount as the single-batch deletion amount during the first partitioning, and perform batch partitioning on the data to be deleted to obtain M1 batches of data to be deleted.

[0087] In some embodiments of the present application, the batch division module is specifically used for: when the data to be deleted is obtained, saving the primary key ID of all the data to be deleted in an intermediate temporary table; querying the primary key ID of the single batch deletion quantity from the intermediate temporary table batch by batch by executing SQL statements; the batch deletion module is specifically used for: based on the queried primary key ID of the single batch deletion quantity, after associating and deleting the data to be deleted corresponding to the primary key ID in the data table, querying the primary key ID of the next batch and executing the data deletion operation for the next batch of data to be deleted.

[0088] In some embodiments of the present application, the batch partitioning module is specifically used to: obtain the proportion of the data to be deleted in the data table; when the proportion is greater than a preset proportion threshold, save the primary key ID of all the data to be deleted in an intermediate temporary table.

[0089] In some embodiments of the present application, it also includes: a determination module, which is used to determine the low-peak period of data writing of the server within a day by counting the data writing volume of the server in a historical period; and a batch deletion module is specifically used to execute batch deletion operations on the data to be deleted during the low-peak period of data writing.

[0090] The data deletion device for dynamically adjusting the single batch deletion amount provided in the embodiment of the present application can achieve Figure 1 The various processes implemented by the service platform in the method embodiment can achieve the same technical effect. To avoid repetition, they will not be described here.

[0091] Figure 3 It is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present application.

[0092] like Figure 3 As shown, the electronic device 300 includes a memory 301 , a processor 302 , and a computer program stored in the memory 301 and executable on the processor 302 .

[0093] In an example, the processor 302 may include a central processing unit (CPU), or an application specific integrated circuit (ASIC), or may be configured to implement one or more integrated circuits of the embodiments of the present application.

[0094] The memory 301 may include a read-only memory (ROM), a random access memory (RAM), a disk storage medium device, an optical storage medium device, a flash memory device, an electrical, optical or other physical / tangible memory storage device. Therefore, generally, the memory includes one or more tangible (non-transitory) computer-readable storage media (e.g., a memory device) encoded with software including computer executable instructions, and when the software is executed (e.g., by one or more processors), it can be operated to perform the operations described with reference to the card opening method in the embodiment according to the first aspect of the present application.

[0095] The processor 302 runs a computer program corresponding to the executable program code by reading the executable program code stored in the memory 301, so as to implement the card activation method in the embodiment of the first aspect.

[0096] In some examples, the electronic device 300 may further include a communication interface 303 and a bus 310. Figure 3 As shown, the memory 301, the processor 302, and the communication interface 303 are connected via a bus 310 and communicate with each other.

[0097] The communication interface 303 is mainly used to realize the communication between the modules, devices, units and / or equipment in the embodiment of the present application. The communication interface 303 can also be used to access input devices and / or output devices.

[0098] The bus 310 includes hardware, software, or both, coupling the components of the electronic device 300 to each other. By way of example and not limitation, the bus 310 may include an Accelerated Graphics Port (AGP) or other graphics bus, an Enhanced Industry Standard Architecture (EISA) bus, a Front Side Bus (FSB), a Hyper Transport (HT) interconnect, an Industry Standard Architecture (ISA) bus, an InfiniBand interconnect, a Low Pin Count (LPC) bus, a Memory bus, a Micro Channel Architecture (MCA) bus, a Peripheral Component Interconnect (PCI) bus, a PCI-Express (PCI-E) bus, a Serial Advanced Technology Attachment (SATA) bus, a Video Electronics Standards Association Local Bus (VLB) bus, or other suitable buses or a combination of two or more of these. Where appropriate, the bus 310 may include one or more buses. Although embodiments of the present application describe and illustrate a particular bus, the present application contemplates any suitable bus or interconnect.

[0099] The electronic device provided in the embodiment of the present application can realize Figure 1 The various processes implemented by the electronic device in the method embodiment can achieve the same technical effect, and to avoid repetition, they will not be described here.

[0100] In combination with the data deletion method for dynamically adjusting the single batch deletion amount in the above embodiment, the embodiment of the present application can provide a computer storage medium for implementation. The computer storage medium stores computer program instructions; when the computer program instructions are executed by the processor, the steps of any one of the data deletion methods for dynamically adjusting the single batch deletion amount in the above embodiment are implemented.

[0101] In combination with the data deletion method for dynamically adjusting the single batch deletion amount in the above embodiment, the present application embodiment can provide a computer program product for implementation. The (computer) program product is stored in a non-volatile storage medium, and when the program product is executed by at least one processor, it implements the steps of any one of the data deletion methods for dynamically adjusting the single batch deletion amount in the above embodiment.

[0102] An embodiment of the present application further provides a chip, which includes a processor and a communication interface, wherein the communication interface and the processor are coupled, and the processor is used to run programs or instructions to implement the various processes of the above-mentioned data deletion method embodiment for dynamically adjusting the single batch deletion amount, and can achieve the same technical effect. To avoid repetition, it will not be repeated here.

[0103] It should be understood that the chip mentioned in the embodiments of the present application can also be called a system-level chip, a system chip, a chip system or a system-on-chip chip, etc.

[0104] It should be clear that the present application is not limited to the specific configuration and processing described above and shown in the figures. For the sake of simplicity, a detailed description of the known method is omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of the present application is not limited to the specific steps described and shown, and those skilled in the art can make various changes, modifications and additions, or change the order between the steps after understanding the spirit of the present application.

[0105] The functional blocks shown in the above-described block diagram can be implemented as hardware, software, firmware or a combination thereof. When implemented in hardware, it can be, for example, an electronic circuit, an application-specific integrated circuit (Application Specific Integrated Circuit, ASIC), appropriate firmware, plug-in, function card, etc. When implemented in software, the elements of the present application are programs or code segments used to perform the required tasks. The program or code segment can be stored in a machine-readable medium, or transmitted on a transmission medium or a communication link by a data signal carried in a carrier. "Machine-readable medium" can include any medium capable of storing or transmitting information. Examples of machine-readable media include electronic circuits, semiconductor memory devices, ROM, flash memory, erasable ROM (EROM), floppy disks, CD-ROMs, optical disks, hard disks, optical fiber media, radio frequency (RF) links, etc. The code segment can be downloaded via a computer network such as the Internet, an intranet, etc.

[0106] It should also be noted that the exemplary embodiments mentioned in this application describe some methods or systems based on a series of steps or devices. However, this application is not limited to the order of the above steps, that is, the steps can be performed in the order mentioned in the embodiment, or in a different order from the embodiment, or several steps can be performed simultaneously.

[0107] Aspects of the present disclosure are described above with reference to the flowchart and / or block diagram of the method, device (system) and computer program product according to the embodiment of the present disclosure. It should be understood that each box in the flowchart and / or block diagram and the combination of each box in the flowchart and / or block diagram can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device to produce a machine so that these instructions executed by the processor of the computer or other programmable data processing device enable the implementation of the function / action specified in one or more boxes of the flowchart and / or block diagram. Such a processor can be, but is not limited to, a general-purpose processor, a special-purpose processor, a special application processor, or a field programmable logic circuit. It can also be understood that each box in the block diagram and / or flowchart and the combination of boxes in the block diagram and / or flowchart can also be implemented by dedicated hardware that performs a specified function or action, or can be implemented by a combination of dedicated hardware and computer instructions.

[0108] The above is only a specific implementation of the present application. Those skilled in the art can clearly understand that for the convenience and simplicity of description, the specific working processes of the systems, modules and units described above can refer to the corresponding processes in the aforementioned method embodiments, and will not be repeated here. It should be understood that the protection scope of the present application is not limited to this. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed in this application, and these modifications or replacements should be included in the protection scope of this application.

Claims

1. A data deletion method for dynamically adjusting the single batch deletion amount, characterized in that: include: Divide the data to be deleted in the data table into M1 batches evenly; In the process of deleting the data to be deleted in batches, after deleting each N batches of data to be deleted, the single batch deletion amount is updated based on the current CPU usage and current memory usage of the server, and the remaining data to be deleted is re-divided into batches based on the latest single batch deletion amount, and the batch deletion operation is performed until the data to be deleted is completely deleted; The updating of the single batch deletion amount based on the current CPU usage and current memory usage of the server includes: Based on the maximum index value of the current CPU usage rate and the current memory usage rate, determine the pressure state interval corresponding to the server, and determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval, wherein the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, the interval adjustment coefficient is negatively correlated with the interval value, and M1 is greater than N; The pressure adjustment coefficient is multiplied by the single batch deletion amount of the most recent deletion operation to obtain the latest single batch deletion amount.

2. The method according to claim 1, characterized in that Each pressure state interval is associated with an interval adjustment coefficient, and determining the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval includes: Obtaining a minimum interval value of the pressure state interval, and calculating a target difference between the maximum index value and the minimum interval value; The ratio of the interval adjustment coefficient to the target difference is multiplied by a preset adjustment coefficient to obtain the pressure adjustment coefficient, wherein the pressure adjustment coefficient is negatively correlated with the target difference.

3. The method according to claim 2, characterized in that After obtaining the pressure adjustment coefficient, the method further includes: When the pressure adjustment coefficient is less than 1, the preset sleep duration is divided by the pressure adjustment coefficient to obtain the target sleep duration; After the target dormancy time has elapsed, the remaining data to be deleted are deleted in batches.

4. The method according to claim 1, characterized in that: Each pressure state interval is associated with an interval adjustment coefficient, and determining the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval includes: determining the interval adjustment coefficient associated with the pressure state interval as the pressure adjustment coefficient; Among them, for the pressure state interval whose maximum interval value is less than the preset pressure threshold, the associated interval adjustment coefficient is greater than 1, and for the pressure state interval whose minimum interval value is greater than or equal to the preset pressure threshold, the associated interval adjustment coefficient is less than 1.

5. The method according to claim 1, characterized in that: The step of evenly dividing the data to be deleted in the data table into M1 batches includes: Obtaining a proportion of the data to be deleted in the data table, and multiplying the proportion by a preset single batch deletion amount to obtain a first data amount; The first data volume is used as the single batch deletion volume during the first division, and the data to be deleted is divided into batches to obtain M1 batches of data to be deleted.

6. The method according to claim 1, characterized in that The step of evenly dividing the data to be deleted in the data table into M1 batches includes: When the data to be deleted is obtained, the primary key IDs of all the data to be deleted are saved in the intermediate temporary table; Query the primary key ID of the single batch deletion quantity batch by batch from the intermediate temporary table by executing SQL statements; The performing of the batch deletion operation includes: Based on the queried primary key ID of the single batch deletion amount, after the data to be deleted corresponding to the primary key ID in the data table is associated and deleted, the primary key ID of the next batch is queried and the data deletion operation of the next batch of data to be deleted is performed.

7. The method according to claim 6, characterized in that When the data to be deleted is obtained, the primary key IDs of all the data to be deleted are saved in the intermediate temporary table, including: Obtaining the proportion of the data to be deleted in the data table; When the proportion is greater than a preset proportion threshold, the primary key IDs of all the data to be deleted are saved in an intermediate temporary table.

8. The method according to claim 1, characterized in that Also includes: By counting the amount of data written to the server in the historical period, determine the low peak period of data writing to the server in a day; The batch deletion operation of the data to be deleted is performed during the data writing off-peak period.

9. A data deletion device for dynamically adjusting the deletion amount of a single batch, characterized in that: include: The batch division module is used to divide the data to be deleted in the data table into M1 batches evenly; The batch deletion module is used to update the single batch deletion amount based on the current CPU usage and current memory usage of the server after deleting each N batches of data to be deleted during the process of deleting the data to be deleted in batches, and re-divide the remaining data to be deleted into batches based on the latest single batch deletion amount, and perform batch deletion operations until the data to be deleted is completely deleted; Among them, the batch deletion module is specifically used for: Based on the maximum index value of the current CPU usage rate and the current memory usage rate, determine the pressure state interval corresponding to the server, and determine the pressure adjustment coefficient based on the interval adjustment coefficient associated with the pressure state interval, wherein the pressure adjustment coefficient is positively correlated with the interval adjustment coefficient, the interval adjustment coefficient is negatively correlated with the interval value, and M1 is greater than N; The pressure adjustment coefficient is multiplied by the single batch deletion amount of the most recent deletion operation to obtain the latest single batch deletion amount.

10. An electronic device, characterized in that: The electronic device comprises: a processor and a memory storing computer program instructions; when the electronic device executes the computer program instructions, the method according to any one of claims 1 to 8 is implemented.

Images