Database data cleaning method and device, storage medium and electronic equipment

By obtaining the cleanup parameters of the database, determining the cleaning conditions for historical data and invalid data, and deleting data in batches, the problem of uncontrollable cleaning time when the data volume is large, ensuring database service stability and business continuity.

CN119066052APending Publication Date: 2024-12-03BEIJING JINGDONG QIANSHITECHNOLOGY CO LTD
View PDF 10 Cites 0 Cited by

Patent Information

Application Number
CN202310637221.9
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2023-05-31
Publication Date
2024-12-03

AI Technical Summary

Technical Problem

When the data volume is large, the performance of the data cleaning platform will decline, resulting in uncontrollable cleaning time, affecting the stability of the database service, and the data cleaning cannot be completed without affecting the use of the production system business.

Method used

By obtaining the database cleaning parameters, including data retention time and rolling cleaning cycle, determining the cleaning conditions for historical data and invalid data, deleting data in the database in batches, controlling the data cleaning time, and ensuring the stability of database services.

Benefits of technology

It realizes that without affecting the use of production system business, effectively control the data cleaning time, ensure the stability of database services, and complete data cleaning work within the rolling cleaning cycle.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119066052A_ABST
    Figure CN119066052A_ABST
Patent Text Reader

Abstract

The invention provides a data cleaning method and device for a database, a storage medium and electronic equipment, and relates to the technical field of computers. The method comprises the following steps: acquiring cleaning parameters of a database, wherein the cleaning parameters of the database comprise data retention time of the database and a rolling cleaning period of the database; determining a historical data cleaning condition corresponding to the database according to the data retention time; determining data increment time by using the data retention time and the rolling cleaning period, and determining an invalid data cleaning condition corresponding to the database according to the data increment time; and deleting the data in the database based on the historical data cleaning condition and the invalid data cleaning condition. According to the method, the data cleaning time can be controlled, the stability of database service is ensured, and data cleaning is completed on the premise of not influencing service use of a production system.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present disclosure relates to the field of computer technology, and in particular to a database data cleaning method, a database data cleaning device, a computer-readable storage medium, and an electronic device. Background Art

[0002] There are scenarios where data expansion occurs in production systems, and in these scenarios, a high percentage of invalid data causes data expansion. In this case, with the help of the data transfer platform, invalid data in the production system database can be deleted in small batches.

[0003] However, when the data volume reaches a certain scale, the performance of the data transfer platform will decline linearly, and there is a risk of uncontrollable cleanup time, which affects the stability of the database service and makes it impossible to complete data cleanup without affecting the business use of the production system.

[0004] It should be noted that the information disclosed in the above background technology section is only used to enhance the understanding of the background of the present disclosure, and therefore may include information that does not constitute the prior art known to ordinary technicians in the field. Summary of the invention

[0005] The purpose of the present disclosure is to provide a database data cleaning method, a database data cleaning device, a computer-readable storage medium and an electronic device, which can control the data cleaning time, ensure the stability of the database service, and complete the data cleaning without affecting the business use of the production system.

[0006] Other features and advantages of the present disclosure will become apparent from the following detailed description, or may be learned in part by the practice of the present disclosure.

[0007] According to one aspect of the present disclosure, a method for cleaning up database data is provided, comprising: obtaining cleaning parameters of a database, wherein the cleaning parameters of the database include a data retention time of the database and a rolling cleaning cycle of the database; determining a historical data cleaning condition corresponding to the database according to the data retention time; determining a data increment time by using the data retention time and the rolling cleaning cycle, and determining an invalid data cleaning condition corresponding to the database according to the data increment time; and deleting data in the database based on the historical data cleaning condition and the invalid data cleaning condition.

[0008] In an optional embodiment of the present disclosure, the data retention time and the rolling cleanup cycle are used to determine the data increment time, and the invalid data cleanup condition corresponding to the database is determined according to the data increment time, including: calculating the ratio of the data retention time and the rolling cleanup cycle, and determining the data increment time according to the calculated ratio; setting the invalid data cleanup condition to clean up invalid data in the database within the data increment time range; wherein the invalid data is data that meets the preset invalidity condition.

[0009] In an optional embodiment of the present disclosure, the invalid data cleaning condition includes one or more sub-conditions; wherein, the invalid data cleaning condition is set to clean up invalid data in the database within the data incremental time, including: splitting the data incremental time into one or more incremental time segments; for each of the incremental time segments, setting the sub-condition corresponding to the incremental time segment; wherein the sub-condition corresponding to the incremental time segment is used to clean up invalid data in the incremental time segment.

[0010] In an optional embodiment of the present disclosure, splitting the data incremental time into one or more incremental time segments includes: obtaining the total amount of data in the database and the amount of invalid data in the database; calculating the ratio of the amount of invalid data to the total amount of data, and determining the calculated ratio as the invalid data ratio; determining the number of segments according to the invalid data ratio and a preset data cleaning time; the number of segments is directly proportional to the invalid data ratio, and the number of segments is inversely proportional to the preset data cleaning time; splitting the data incremental time according to the number of segments to obtain the one or more incremental time segments.

[0011] In an optional embodiment of the present disclosure, determining the historical data cleaning condition corresponding to the database based on the data retention time includes: setting the historical data cleaning condition to clean up the data in the database that is outside the data retention time range.

[0012] In an optional embodiment of the present disclosure, deleting the data in the database based on the historical data cleaning condition and the invalid data cleaning condition includes: obtaining a preset batch size; and batch deleting the data in the database based on the historical data cleaning condition and the invalid data cleaning condition according to the preset batch size.

[0013] In an optional embodiment of the present disclosure, the data in the database is batch deleted according to the preset batch size and based on the historical data cleaning condition and the invalid data cleaning condition, including: when executing the data deletion operation of the current batch, querying the database for data identifiers corresponding to the data that meets the historical data cleaning condition or the invalid data cleaning condition according to the preset batch size; deleting the data corresponding to the queried data identifiers in the database to obtain the data deletion result of the current batch; if the number of deleted data in the data deletion result of the current batch is less than the preset batch size, determining that the data cleaning of the database is completed; if the number of deleted data in the data deletion result of the current batch is greater than or equal to the preset batch size, executing the data deletion operation of the next batch according to the preset batch size.

[0014] According to another aspect of the present disclosure, there is provided a data cleaning device for a database, comprising: a parameter acquisition module, used to acquire cleaning parameters of a database, wherein the cleaning parameters of the database include the data retention time of the database and the rolling cleaning cycle of the database; a first condition determination module, used to determine the historical data cleaning condition corresponding to the database according to the data retention time; a second condition determination module, used to determine the data increment time by using the data retention time and the rolling cleaning cycle, and determine the invalid data cleaning condition corresponding to the database according to the data increment time; and a data deletion module, used to delete the data in the database based on the historical data cleaning condition and the invalid data cleaning condition.

[0015] In an optional embodiment of the present disclosure, the second condition determination module is also used to: calculate the ratio of the data retention time and the rolling cleanup cycle, and determine the data increment time based on the calculated ratio; set the invalid data cleanup condition to clean up invalid data in the database within the data increment time range; wherein the invalid data is data that meets the preset invalidity condition.

[0016] In an optional embodiment of the present disclosure, the invalid data cleaning condition includes one or more sub-conditions; wherein, the second condition determination module is also used to: split the data incremental time into one or more incremental time segments; for each incremental time segment, set the sub-condition corresponding to the incremental time segment; wherein, the sub-condition corresponding to the incremental time segment is used to clean up invalid data within the incremental time segment.

[0017] In an optional embodiment of the present disclosure, the second condition determination module is also used to: obtain the total amount of data in the database and the amount of invalid data in the database; calculate the ratio of the amount of invalid data to the total amount of data, and determine the calculated ratio as the invalid data ratio; determine the number of segments according to the invalid data ratio and a preset data cleaning time; the number of segments is directly proportional to the invalid data ratio, and the number of segments is inversely proportional to the preset data cleaning time; split the data incremental time according to the number of segments to obtain the one or more incremental time segments.

[0018] In an optional embodiment of the present disclosure, the first condition determination module is further used to: set the historical data cleaning condition to clean up data in the database that is outside the data retention time range.

[0019] In an optional embodiment of the present disclosure, the data deletion module is further used to: obtain a preset batch size; and batch delete the data in the database according to the preset batch size based on the historical data cleaning condition and the invalid data cleaning condition.

[0020] In an optional embodiment of the present disclosure, the data deletion module is also used to: when executing the data deletion operation of the current batch, query the data identifier corresponding to the data in the database that meets the historical data cleaning condition or the invalid data cleaning condition according to the preset batch size; delete the data corresponding to the data identifier queried in the database to obtain the data deletion result of the current batch; if the number of deleted data in the data deletion result of the current batch is less than the preset batch size, determine that the data cleaning of the database is completed; if the number of deleted data in the data deletion result of the current batch is greater than or equal to the preset batch size, perform the data deletion operation of the next batch according to the preset batch size.

[0021] According to another aspect of the present disclosure, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the above-mentioned method for cleaning database data is implemented.

[0022] According to another aspect of the present disclosure, there is provided an electronic device, comprising: a processor; and a memory for storing executable instructions of the processor; wherein the processor is configured to execute the above-mentioned database data cleaning method by executing the executable instructions.

[0023] The data cleaning method of the database of the disclosed embodiment sets historical data cleaning conditions according to the data retention time of the database, so as to use the historical data cleaning conditions to delete the data in the database that is outside the data retention time range; determines the data increment time according to the data retention time of the database and the rolling cleaning cycle, and then sets invalid data cleaning conditions according to the data increment time, so as to use the invalid data cleaning conditions to delete the invalid data in the database within the data increment time range. It can be seen that the historical invalid data in the database is cleaned by the historical data cleaning conditions, and the newly added invalid data in the database is cleaned by the invalid data cleaning conditions, so as to achieve the continuous reduction of invalid data in the rolling cleaning cycle and complete the database cleaning work within the rolling cleaning cycle, control the data cleaning time, ensure the stability of the database service while cleaning the data, and achieve the completion of data cleaning without affecting the business use of the production system.

[0024] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] The accompanying drawings herein are incorporated into the specification and constitute a part of the specification, illustrate embodiments consistent with the present disclosure, and together with the specification are used to explain the principles of the present disclosure. Obviously, the accompanying drawings described below are only some embodiments of the present disclosure, and for ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without creative work.

[0026] Figure 1 is a schematic diagram of a system architecture of a method for cleaning database data according to an exemplary embodiment;

[0027] Figure 2 is a flow chart of a method for cleaning database data according to an exemplary embodiment;

[0028] Figure 3 is a schematic diagram of performing data cleaning on a database according to an exemplary embodiment;

[0029] Figure 4 is a flow chart showing a data deletion operation according to a preset batch size according to an exemplary embodiment;

[0030] Figure 5 is a schematic diagram showing segmenting of data increment time according to an exemplary embodiment;

[0031] Figure 6 is a flow chart of a method for cleaning database data according to yet another exemplary embodiment;

[0032] Figure 7is a block diagram of a data cleaning device for a database according to an exemplary embodiment; and

[0033] Figure 8 It is a structural block diagram of an electronic device according to an exemplary embodiment. DETAILED DESCRIPTION

[0034] In order to enable ordinary persons in the art to better understand the technical solutions of the present disclosure, the technical solutions in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the accompanying drawings.

[0035] It should be noted that the terms "first", "second", etc. in the specification and claims of the present disclosure and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present disclosure described herein can be implemented in an order other than those illustrated or described herein.

[0036] The following example embodiments can be implemented in a variety of forms and should not be construed as being limited to the examples set forth herein; on the contrary, these embodiments are provided so that the present disclosure will be more comprehensive and complete, and the concepts of the example embodiments are fully conveyed to those skilled in the art. The described features, structures, or characteristics may be combined in one or more embodiments in any suitable manner. In the following description, many specific details are provided to provide a full understanding of the embodiments of the present disclosure. However, those skilled in the art will appreciate that the technical solutions of the present disclosure may be practiced while omitting one or more of the specific details, or other methods, components, devices, steps, etc. may be adopted. In other cases, known technical solutions are not shown or described in detail to avoid obscuring various aspects of the present disclosure.

[0037] In addition, the accompanying drawings are only schematic illustrations of the present disclosure and are not necessarily drawn to scale. The same reference numerals in the figures represent the same or similar parts, and their repeated description will be omitted. Some of the block diagrams shown in the accompanying drawings are functional entities and do not necessarily correspond to physically or logically independent entities. These functional entities can be implemented in software form, or implemented in one or more hardware modules or integrated circuits, or implemented in different networks and / or processor devices and / or microcontroller devices.

[0038] It should be noted that the collection, collection, updating, analysis, processing, use, transmission, storage and other aspects of user personal information involved in the technical solution of this disclosure are in compliance with the provisions of relevant laws and regulations, are used for legitimate purposes, and do not violate public order and good morals. Necessary measures are taken for user personal information to prevent illegal access to user personal information data and maintain the security of user personal information, network security and national security.

[0039] Figure 1 1 is a schematic diagram of a system architecture of a method for cleaning a database according to an exemplary embodiment, the system architecture includes a server 101, a network 102, and a client 103. The network 102 is used to provide a medium for a communication link between the client 103 and the server 101. The network 102 may include various connection types, such as wired, wireless communication links, or optical fiber cables, etc.

[0040] Among them, server 101 can be an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms. In some practical applications, server 101 can also be a server of a network platform, and the network platform can be, for example, a trading platform, a live broadcast platform, a social platform, or a music platform, etc., which is not limited in the embodiments of the present disclosure. Among them, the server can be a single server or a cluster formed by multiple servers, and the present disclosure does not limit the specific architecture of the server.

[0041] The client 103 may include, but is not limited to, electronic devices such as smart phones, desktop computers, tablet computers, laptop computers, smart speakers, digital assistants, AR (Augmented Reality) devices, VR (Virtual Reality) devices, and smart wearable devices, or the client 103 may also be a personal computer, such as a laptop computer and a desktop computer, etc. Optionally, the operating system running on the electronic device may include, but is not limited to, Android, IOS, Linux, Windows, etc.

[0042] In the disclosed embodiment, the server 101 can: obtain database cleaning parameters, which include the database data retention time and the database rolling cleaning cycle; determine the historical data cleaning conditions corresponding to the database based on the data retention time; determine the data increment time using the data retention time and the rolling cleaning cycle, and determine the invalid data cleaning conditions corresponding to the database based on the data increment time; and delete the data in the database based on the historical data cleaning conditions and the invalid data cleaning conditions.

[0043] It should be understood that Figure 1 The number of servers 101, networks 102 and clients 103 is merely illustrative, and any number of terminal devices, networks and servers may be provided according to actual needs.

[0044] In order to enable ordinary persons in the art to better understand the technical solution of the present disclosure, the data cleaning technical solution of the database of the embodiment of the present disclosure will be described in more detail below with reference to the accompanying drawings and embodiments.

[0045] Figure 2 FIG. 1 is a flowchart of a method for cleaning database data according to an exemplary embodiment. Figure 2 , this method is applied to Figure 1 The server shown in the figure specifically includes the following steps.

[0046] Step S210: Acquire database cleaning parameters, where the database cleaning parameters include the database data retention time and the database rolling cleaning cycle.

[0047] The database cleaning parameters may be understood as parameters related to database cleaning, and may include the database data retention time and the database rolling cleaning cycle.

[0048] The data retention time of the database refers to the effective time of the data stored in the database. For example, if the data retention time is 10 days, the database stores the data of the past 10 days. The rolling cleanup cycle of the database can be understood as the cycle to complete the cleanup of all invalid data. It is an expected value. For example, the rolling cleanup cycle is 10 days.

[0049] Step S220: Determine historical data cleanup conditions corresponding to the database according to the data retention time.

[0050] The historical data cleaning condition is used to clean up the data in the database that is outside the data retention time range. For example, if the data retention time is 10 days, the historical data cleaning condition can be used to clean up the data 10 days before the current time.

[0051] In an optional embodiment of the present disclosure, determining the historical data cleaning condition corresponding to the database according to the data retention time may include: setting the historical data cleaning condition to clean up the data in the database that is outside the data retention time range.

[0052] For example, if the data retention period is 10 days, and the historical data cleaning condition is used to clean up all data before 10 days from the current time, then on the 11th day of system operation, all data from the 1st day can be cleaned up through the historical data cleaning condition, and on the 15th day of system operation, all data from days 1 to 5 can be cleaned up through the historical data cleaning condition.

[0053] Step S230: Determine the data increment time by using the data retention time and the rolling cleanup cycle, and determine the invalid data cleanup conditions corresponding to the database according to the data increment time.

[0054] In this step, the data retention time and the rolling cleanup cycle can be used to calculate the data increment time, and then the invalid data cleanup conditions corresponding to the database can be set according to the calculated data increment time.

[0055] Among them, the invalid data cleaning condition is used to clean up invalid data in the database within the data increment time range, so that no invalid data will be added within the data increment time range. For example, if the data increment time is 1 day, the invalid data cleaning condition is used to clean up invalid data in the newly added 1 day data. In other words, if the data increment time is 1 day, on the 11th day of system operation, the invalid data added on the 11th day can be cleaned up through the invalid data cleaning condition, and on the 15th day of system operation, the invalid data added on the 15th day can be cleaned up through the invalid data cleaning condition.

[0056] Step S240: based on the historical data cleaning condition and the invalid data cleaning condition, delete the data in the database.

[0057] After determining the historical data cleaning conditions and invalid data cleaning conditions corresponding to the database, you can query the database based on the historical data cleaning conditions and invalid data cleaning conditions, and delete the data that meets the historical data cleaning conditions or invalid data cleaning conditions from the database to complete the database cleaning.

[0058] Figure 3 FIG. 1 is a schematic diagram of performing data cleaning on a database according to an exemplary embodiment. Figure 3For example, the database data retention time is 10 days, that is, the database needs to store 10 days of data, and the database rolling cleanup cycle is 10 days. Set the historical data cleanup condition to delete the data 10 days before the current time; use the data retention time and rolling cleanup cycle to calculate the data increment time to be 1 day, set the invalid data cleanup condition to delete the invalid data in the newly added data for every additional day.

[0059] Figure 3 In the example, on the 10th day, the database stores the data from the 1st to the 10th day. On the 11th day, the database needs to retain the data for 10 days. The data of the 1st day can be deleted by using the historical data cleaning condition, and the database retains the data of the 2nd to the 11th day; and the invalid data cleaning condition can be used to delete the invalid data in the data of the 11th day.

[0060] On the 12th day, the database needs to retain 10 days of data. The data of the 1st and 2nd days can be deleted using the historical data cleaning conditions, and the database retains the data of the 3rd to 12th days; and, using the invalid data cleaning conditions, the invalid data in the 12th day data can be deleted.

[0061] On the 19th day, the database needs to retain 10 days of data. The historical data cleaning condition can be used to delete the data from the 1st to the 9th day, and the database retains the data from the 10th to the 19th day; and, the invalid data cleaning condition can be used to delete the invalid data in the 19th day data.

[0062] On the 20th day, the database needs to retain 10 days of data. The historical data cleaning condition can be used to delete the data from the 1st to the 10th day, and the database retains the data from the 11th to the 20th day; and, using the invalid data cleaning condition, the invalid data in the 20th day data can be deleted.

[0063] from Figure 3 It can be seen that the data retention time of the database is set to 10 days. Starting from the 11th day after the database is running, the data older than 10 days from the current date will be deleted every day. At the same time, the invalid data in the newly added data of 1 day will be deleted, so that the effect of no new invalid data will be added within 1 day of data increment time, and finally the data cleaning work of the database will be completed.

[0064] The data cleaning method of the database provided by the embodiment of the present disclosure sets historical data cleaning conditions according to the data retention time of the database, so as to use the historical data cleaning conditions to delete the data in the database that is outside the data retention time range; determines the data increment time according to the data retention time of the database and the rolling cleaning cycle, and then sets invalid data cleaning conditions according to the data increment time, so as to use the invalid data cleaning conditions to delete the invalid data in the database within the data increment time range. It can be seen that the historical invalid data in the database is cleaned by the historical data cleaning conditions, and the newly added invalid data in the database is cleaned by the invalid data cleaning conditions, so as to achieve the continuous reduction of invalid data in the rolling cleaning cycle and complete the database cleaning work within the rolling cleaning cycle, control the data cleaning time, ensure the stability of the database service while cleaning the data, and realize the completion of data cleaning without affecting the business use of the production system.

[0065] In an optional embodiment of the present disclosure, deleting data in a database based on historical data cleaning conditions and invalid data cleaning conditions may include: obtaining a preset batch size; and batch deleting data in the database based on the historical data cleaning conditions and invalid data cleaning conditions according to the preset batch size.

[0066] After setting the historical data cleaning conditions and invalid data cleaning conditions, you can delete the data in the database in batches according to the set historical data cleaning conditions and invalid data cleaning conditions. In this way, you can avoid deletion failures caused by large transactions being deleted at one time in scenarios with a large amount of data.

[0067] The preset batch size is a preset threshold value of the number of data to be deleted in each batch. Take the SQL statement as an example, in "select id from t where condition=1 limit 200", the value 200 after limit is the batch size, which can be preset according to actual needs or according to statistical analysis.

[0068] Furthermore, according to a preset batch size, based on historical data cleaning conditions and invalid data cleaning conditions, batch deletion is performed on the data in the database, including: when executing the data deletion operation of the current batch, according to the preset batch size, querying the database for data identifiers corresponding to the data that meets the historical data cleaning conditions or the invalid data cleaning conditions; deleting the data corresponding to the queried data identifiers in the database to obtain the data deletion results of the current batch; if the number of deleted data in the data deletion results of the current batch is less than the preset batch size, it is determined that the data cleaning of the database is completed; if the number of deleted data in the data deletion results of the current batch is greater than or equal to the preset batch size, the data deletion operation of the next batch is performed according to the preset batch size.

[0069] In the process of batch deleting data in a database, when executing the current batch, first query the data identifiers corresponding to the data of the preset batch size that meet the historical data cleaning condition or the invalid data cleaning condition. Then, according to the queried data identifiers, delete the data corresponding to these data identifiers in the database to obtain the data deletion result of the current batch. According to the data deletion result of the current batch, obtain the number of deleted data in the current batch. If the number of deleted data in the current batch is less than the preset batch size, it means that all data in the database that meet the historical data cleaning condition or the invalid data cleaning condition have been deleted, and it is determined that the data cleaning of the database is completed. If the number of deleted data in the current batch is greater than or equal to the preset batch size, it means that all data in the database that meet the historical data cleaning condition or the invalid data cleaning condition may be deleted, that is, the current batch is the last batch of data deletion, and the current batch deletes the number of the preset batch size. However, if the number of deleted data in the current batch is greater than or equal to the preset batch size, it is also possible that all data in the database that meet the historical data cleaning condition or the invalid data cleaning condition may not be deleted. In this cleaning, the data deletion operation of the next batch is performed according to the preset batch size.

[0070] For example, the preset batch size is 200. When the 10th data deletion operation is performed, the data identifiers corresponding to the 200 data that meet the historical data cleaning condition or the invalid data cleaning condition in the database are queried, and then the data is deleted according to the queried data to obtain the 10th data deletion result. If the number of deleted data in the 10th data deletion result is less than 200, it means that all data in the database that meet the historical data cleaning condition or the invalid data cleaning condition have been deleted. If the number of deleted data in the 10th data deletion result is equal to 200, the 11th data deletion operation is performed.

[0071] Figure 4 FIG. 1 is a flow chart showing a method of performing a data deletion operation according to a preset batch size according to an exemplary embodiment. Figure 4 As shown, the following steps may be specifically included.

[0072] Step S401: Obtaining a preset batch size;

[0073] Step S402: querying the database for data identifiers corresponding to data that meets historical data cleaning conditions or invalid data cleaning conditions according to a preset batch size;

[0074] Step S403: Delete the data corresponding to the data identifier queried in the database to obtain the data deletion result of the current batch;

[0075] Step S404: determine whether the amount of deleted data in the data deletion result of the current batch is less than the preset batch size, if so, execute step S405, if not, execute step S402;

[0076] Step S405: Determine that the data cleaning of the database is completed.

[0077] In the process of deleting data in the database based on historical data cleaning conditions and invalid data cleaning conditions, a preset batch size can be obtained, and then the data in the database that meets the historical data cleaning conditions or invalid data cleaning conditions can be deleted in batches according to the preset batch size, which can avoid deletion failure caused by too large a transaction being deleted at one time in a scenario with a large amount of data. In addition, when executing each batch of data deletion operations, the number of deleted data in the data deletion results of the batch is compared with the preset batch size, and it is determined based on the comparison result whether all data in the database that meets the historical data cleaning conditions or invalid data cleaning conditions has been deleted, thereby completing the data cleaning work of the database.

[0078] In an optional embodiment of the present disclosure, the data retention time and the rolling cleanup cycle are used to determine the data increment time, and the invalid data cleanup condition corresponding to the database is determined based on the data increment time. This may include: calculating the ratio of the data retention time and the rolling cleanup cycle, and determining the data increment time based on the calculated ratio; and setting the invalid data cleanup condition to clean the invalid data in the database within the data increment time range.

[0079] Invalid data refers to data that meets the preset invalid conditions. For example, the price of an item is set to A, and then 5 minutes later the price A set in the task is not appropriate, so the price of the item is set to B. In this case, the data corresponding to price A is invalid data, and the invalid condition is price A.

[0080] Further, the invalid data cleaning condition includes one or more sub-conditions. And, setting the invalid data cleaning condition to clean invalid data in the data increment time in the database includes: splitting the data increment time into one or more incremental time segments; for each incremental time segment, setting a sub-condition corresponding to the incremental time segment; wherein the sub-condition corresponding to the incremental time segment is used to clean invalid data in the incremental time segment.

[0081] When querying invalid data in the database within the data increment time based on the invalid data cleaning condition, if the amount of data to be traversed is large, it will take longer, resulting in uncontrollable time, and long-term execution of read and write threads will affect the availability of the database.

[0082] In the disclosed embodiment, the data increment time can be divided into one or more increment time segments, and then the sub-conditions corresponding to each increment time segment are set to query the invalid data in the increment time segment in the database through the sub-conditions. In this way, querying the invalid data in the data increment time segment in the database based on the sub-conditions can reduce the amount of data that needs to be traversed, ensure that the time of data query is controllable, and reduce time consumption.

[0083] Optionally, splitting the data incremental time into one or more incremental time segments may include: obtaining the total amount of data in the database and the amount of invalid data in the database; calculating the ratio of the amount of invalid data to the total amount of data, and determining the calculated ratio as the invalid data ratio; determining the number of segments based on the invalid data ratio and a preset data cleaning time; the number of segments is directly proportional to the invalid data ratio, and the number of segments is inversely proportional to the preset data cleaning time; splitting the data incremental time according to the number of segments to obtain one or more incremental time segments.

[0084] Specifically, the ratio of the number of invalid data in the database to the total amount of data in the database is calculated to obtain the invalid data ratio. According to the invalid data ratio and the preset data cleaning time, the number of segments is determined, that is, the data increment time is divided into several segments. Then, the data increment time is segmented according to the number of segments to obtain the incremental time segments included in the data increment time. Among them, the preset data cleaning time can be the data increment time.

[0085] For example, if the data increment time is 1 day and the calculated number of segments is 4, the data increment time can be divided into 4 increment time segments: 0:00-6:00, 6:00-12:00, 12:00-18:00, and 18:00-24:00. Set the sub-conditions corresponding to each increment time segment. Specifically, sub-condition 1 is used to clean up invalid data from 0:00 to 6:00, sub-condition 2 is used to clean up invalid data from 6:00 to 12:00, sub-condition 3 is used to clean up invalid data from 12:00 to 18:00, and sub-condition 4 is used to clean up invalid data from 18:00 to 24:00.

[0086] When the proportion of invalid data is high, the more segments there are, the smaller the time window corresponding to each incremental time segment, the less data in the incremental time segment, and the data cleaning in a single incremental time segment can be completed in less time. The inference from this is that the more segments the better, but each data cleaning may introduce adjustments to the data structure, so the preset data cleaning time is introduced as a reverse control item, and the preset data cleaning time is used to reduce the number of segments and ensure the stability of the data structure. For example, set the number of segments F(R,D) = aR / (bD+c), where R is the proportion of invalid data, D is the preset data cleaning time, and a, b, and c are parameters.

[0087] Figure 5 FIG. 1 is a schematic diagram showing segmenting of data increment time according to an exemplary embodiment. Figure 5 As shown, to determine the data incremental time offset, it is necessary to delete the four invalid data within the data incremental time offset range; segment the data incremental time offset to obtain the three incremental time segments offset1, offset2, and offset3 contained in the data incremental time offset. There is one invalid data in the incremental time segment offset1, one invalid data in the incremental time segment offset2, one invalid data in the incremental time segment offset2, and two invalid data in the incremental time segment offset3. Assuming that the preset batch size bathSize is 2, for the incremental time segment offset1, one iteration can delete the invalid data in the incremental time segment offset1; for the incremental time segment offset2, one iteration can delete the invalid data in the incremental time segment offset2; for the incremental time segment offset3, two iterations can delete the invalid data in the incremental time segment offset3.

[0088] In the disclosed embodiment, the number of segments is calculated based on the invalid data ratio and the preset data cleaning time, and then the data incremental time is split according to the number of segments to obtain the incremental time segments contained in the data incremental time. By using the invalid data ratio as a positive control item for the number of segments and the preset data cleaning time as a negative control item for the number of segments, the stability of the data structure can be ensured while the number of segments calculated is increased, so that the time window corresponding to each incremental time segment is smaller, and the data cleaning within a single incremental time segment can be completed in less time.

[0089] Figure 6 FIG. 1 is a flow chart of a method for cleaning database data according to another exemplary embodiment. Figure 6 As shown, the specific implementation of the database data cleaning method is:

[0090] Step S601: pre-configure the effective retention time T and rolling cleanup cycle P of the database.

[0091] Step S602: Obtain the effective retention time T and rolling cleanup cycle P of the database.

[0092] Step S603: Calculate the data increment time offset according to the effective retention time T and the rolling cleanup period P. The specific calculation method is offset = T / P.

[0093] Step S604: Obtain the total amount of data C and the amount of invalid data c in the database, and calculate the invalid data ratio R=c / C.

[0094] Step S605: Obtain a preset data cleaning time D.

[0095] Step S606: Calculate the number of segments F(R, D) using the invalid data ratio R and the preset data cleaning time D.

[0096] Step S607: segment the data incremental time offset according to the segment quantity F(R, D) to obtain one or more incremental time segments included in the data incremental time offset.

[0097] Step S608: Condition 1 is set according to the effective retention time T, where condition 1 is to clean up data in the database that is outside the effective retention time T from the current time.

[0098] Step S609: according to each incremental time segment included in the data incremental time offset, set the conditions corresponding to each incremental time segment to obtain an offset condition list, wherein the conditions corresponding to the incremental time segment are used to clean up invalid data in the incremental time segment.

[0099] Step S610: Determine that the data cleaning condition of the database is condition 1 or the offset condition list.

[0100] Step S611: According to the preset batch size bathSize, query the database for a list of data identifiers corresponding to the data that meets the data cleaning conditions.

[0101] Step S612: Delete the data corresponding to the data identification list queried in the database to obtain the data deletion result of the current batch.

[0102] Step S613: determine whether the amount of deleted data in the current batch of data deletion results is less than the preset batch size bathSize, if so, execute step S614, if not, execute step S611;

[0103] Step S614: Determine that the data cleaning of the database is completed.

[0104] Among them, condition 1 can be understood as the historical data cleaning condition, and the offset condition list can be understood as the invalid data cleaning condition. The historical invalid data in the database is cleaned up through the historical data cleaning condition, and the newly added invalid data in the database is cleaned up through the invalid data cleaning condition, so that the invalid data is continuously reduced during the rolling cleaning cycle, and the database cleaning work is completed during the rolling cleaning cycle, the data cleaning time is controlled, the stability of the database service is guaranteed while the data is being cleaned, and the data cleaning is completed without affecting the business use of the production system.

[0105] Figure 7 FIG. 1 is a block diagram of a data cleaning device for a database according to an exemplary embodiment. Figure 7 As shown, the data cleaning device 700 of the database includes: a parameter acquisition module 710, a first condition determination module 720, a second condition determination module 730 and a data deletion module 740.

[0106] Among them, the parameter acquisition module 710 can be used to: obtain the cleaning parameters of the database, the cleaning parameters of the database include the data retention time of the database and the rolling cleaning cycle of the database; the first condition determination module 720 can be used to: determine the historical data cleaning conditions corresponding to the database according to the data retention time; the second condition determination module 730 can be used to: use the data retention time and the rolling cleaning cycle to determine the data increment time, and determine the invalid data cleaning conditions corresponding to the database according to the data increment time; the data deletion module 740 can be used to: delete the data in the database based on the historical data cleaning conditions and the invalid data cleaning conditions.

[0107] In an optional embodiment of the present disclosure, the second condition determination module 730 can also be used to: calculate the ratio of the data retention time and the rolling cleanup cycle, and determine the data increment time based on the calculated ratio; set the invalid data cleanup condition to clean up invalid data in the database within the data increment time range; wherein the invalid data is data that meets the preset invalidity condition.

[0108] In an optional embodiment of the present disclosure, the invalid data cleaning condition includes one or more sub-conditions; wherein, the second condition determination module 730 can also be used to: split the data incremental time into one or more incremental time segments; for each incremental time segment, set the sub-condition corresponding to the incremental time segment; wherein, the sub-condition corresponding to the incremental time segment is used to clean up invalid data within the incremental time segment.

[0109] In an optional embodiment of the present disclosure, the second condition determination module 730 can also be used to: obtain the total amount of data in the database and the amount of invalid data in the database; calculate the ratio of the amount of invalid data to the total amount of data, and determine the calculated ratio as the invalid data ratio; determine the number of segments based on the invalid data ratio and the preset data cleaning time; the number of segments is directly proportional to the invalid data ratio, and the number of segments is inversely proportional to the preset data cleaning time; split the data incremental time according to the number of segments to obtain one or more incremental time segments.

[0110] In an optional embodiment of the present disclosure, the first condition determination module 720 may also be used to: set a historical data cleaning condition to clean up data in the database that is outside the data retention time range.

[0111] In an optional embodiment of the present disclosure, the data deletion module 740 may also be used to: obtain a preset batch size; and perform batch deletion of data in the database according to the preset batch size based on historical data cleaning conditions and invalid data cleaning conditions.

[0112] In an optional embodiment of the present disclosure, the data deletion module 740 may also be used for: when executing the data deletion operation of the current batch, querying the data identifier corresponding to the data that meets the historical data cleaning condition or the invalid data cleaning condition in the database according to the preset batch size; deleting the data corresponding to the queried data identifier in the database to obtain the data deletion result of the current batch; if the number of deleted data in the data deletion result of the current batch is less than the preset batch size, determining that the data cleaning of the database is completed; if the number of deleted data in the data deletion result of the current batch is greater than or equal to the preset batch size, executing the data deletion operation of the next batch according to the preset batch size.

[0113] Regarding the device in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0114] Figure 8 is a structural block diagram of an electronic device according to an exemplary embodiment. Figure 8 800 according to this embodiment of the present disclosure is described. Figure 8 The electronic device 800 shown is merely an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.

[0115] like Figure 8 As shown, the electronic device 800 is in the form of a general computing device. The components of the electronic device 800 may include, but are not limited to: the at least one processing unit 810, the at least one storage unit 820, a bus 830 connecting different system components (including the storage unit 820 and the processing unit 810), and a display unit 840.

[0116] The storage unit stores program codes, which can be executed by the processing unit 810, so that the processing unit 810 performs the steps according to various exemplary embodiments of the present disclosure described in the above “Exemplary Method” section of this specification. For example, the processing unit 810 can perform the following steps: Figure 2Step S210 shown in: obtaining the cleaning parameters of the database, the cleaning parameters of the database include the data retention time of the database and the rolling cleaning cycle of the database; step S220: determining the historical data cleaning conditions corresponding to the database according to the data retention time; step S230: determining the data increment time by using the data retention time and the rolling cleaning cycle, and determining the invalid data cleaning conditions corresponding to the database according to the data increment time; step S240: deleting the data in the database based on the historical data cleaning conditions and the invalid data cleaning conditions.

[0117] The storage unit 820 may include a readable medium in the form of a volatile storage unit, such as a random access memory unit (RAM) 821 and a cache memory unit 822 , and may further include a read-only memory unit (ROM) 823 .

[0118] The storage unit 820 may also include a program / utility 824 having a set (at least one) of program modules 825, such program modules 825 including but not limited to: an operating system, one or more application programs, other program modules, and program data, each of which or some combination may include an implementation of a network environment.

[0119] Bus 830 may represent one or more of several types of bus structures, including a memory unit bus or memory unit controller, a peripheral bus, an accelerated graphics port, a processing unit, or a local bus using any of a variety of bus architectures.

[0120] The electronic device 800 may also communicate with one or more external devices 870 (e.g., keyboards, pointing devices, Bluetooth devices, etc.), may also communicate with one or more devices that enable a user to interact with the electronic device 800, and may communicate with any device that enables the electronic device 800 to communicate with one or more other computing devices (e.g., routers, modems, etc.). Such communication may be performed via an input / output (I / O) interface 850. Furthermore, the electronic device 800 may also communicate with one or more networks (e.g., local area networks (LANs), wide area networks (WANs), and public networks, such as the Internet) via a network adapter 860. As shown, the network adapter 860 communicates with other modules of the electronic device 800 via a bus 830. It should be understood that, although not shown in the figure, other hardware and software modules may be used in conjunction with the electronic device 800, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems, etc.

[0121] Through the description of the above embodiments, it is easy for those skilled in the art to understand that the example embodiments described here can be implemented by software, or by software combined with necessary hardware. Therefore, the technical solution according to the embodiment of the present disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (which can be a CD-ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, and includes several instructions to enable a computing device (which can be a personal computer, a server, a terminal device, or a network device, etc.) to execute the method according to the embodiment of the present disclosure.

[0122] In an exemplary embodiment, a computer-readable storage medium including instructions is also provided, such as a memory including instructions, and the above instructions can be executed by a processor of the device to complete the above method. Optionally, the computer-readable storage medium can be a ROM, a random access memory (RAM), a CD-ROM, a magnetic tape, a floppy disk, an optical data storage device, etc.

[0123] In an exemplary embodiment, a computer program product is also provided, including a computer program and instructions. When the computer program and the instructions are executed by a processor, the online file processing method in the above embodiment is implemented.

[0124] Those skilled in the art will readily appreciate other embodiments of the present disclosure after considering the specification and practicing the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of the present disclosure that follow the general principles of the present disclosure and include common knowledge or customary techniques in the art that are not disclosed in the present disclosure. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present disclosure are indicated by the following claims.

[0125] It should be understood that the present disclosure is not limited to the exact structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present disclosure is limited only by the appended claims.

Claims

1. A method for cleaning data in a database, characterized in that: include: Acquire database cleaning parameters, wherein the database cleaning parameters include database data retention time and database rolling cleaning cycle; Determine historical data cleaning conditions corresponding to the database according to the data retention time; Determine the data increment time by using the data retention time and the rolling cleanup cycle, and determine the invalid data cleanup condition corresponding to the database according to the data increment time; Based on the historical data cleaning condition and the invalid data cleaning condition, the data in the database is deleted.

2. The method according to claim 1, characterized in that The method of determining the data increment time by using the data retention time and the rolling cleanup cycle, and determining the invalid data cleanup condition corresponding to the database according to the data increment time, includes: Calculating the ratio of the data retention time to the rolling cleanup period, and determining the data increment time according to the calculated ratio; The invalid data cleaning condition is set to clean up invalid data in the database within the data increment time range; wherein the invalid data is data that meets the preset invalid condition.

3. The method according to claim 2, characterized in that The invalid data cleaning condition includes one or more sub-conditions; The setting of the invalid data cleaning condition is to clean up the invalid data in the database within the data increment time, including: Splitting the data incremental time into one or more incremental time segments; For each of the incremental time segments, a sub-condition corresponding to the incremental time segment is set; wherein the sub-condition corresponding to the incremental time segment is used to clean up invalid data in the incremental time segment.

4. The method according to claim 3, characterized in that The step of splitting the data incremental time into one or more incremental time segments comprises: Acquire the total amount of data in the database and the amount of invalid data in the database; Calculate the ratio of the amount of invalid data to the total amount of data, and determine the calculated ratio as the invalid data ratio; Determine the number of segments according to the invalid data ratio and the preset data cleaning time; the number of segments is directly proportional to the invalid data ratio, and the number of segments is inversely proportional to the preset data cleaning time; The data incremental time is split according to the number of segments to obtain the one or more incremental time segments.

5. The method according to claim 1, characterized in that Determining the historical data cleaning condition corresponding to the database according to the data retention time includes: The historical data cleaning condition is set to clean up the data in the database that is outside the data retention time range.

6. The method according to claim 1, characterized in that The deleting the data in the database based on the historical data cleaning condition and the invalid data cleaning condition includes: Get the preset batch size; According to the preset batch size, based on the historical data cleaning condition and the invalid data cleaning condition, the data in the database are deleted in batches.

7. The method according to claim 6, characterized in that Deleting the data in the database in batches according to the preset batch size and based on the historical data cleaning condition and the invalid data cleaning condition includes: When executing the data deletion operation of the current batch, querying the database for data identifiers corresponding to data that meets the historical data cleaning condition or the invalid data cleaning condition according to the preset batch size; Delete the data corresponding to the data identifier queried in the database to obtain the data deletion result of the current batch; If the amount of deleted data in the data deletion result of the current batch is less than the preset batch size, determining that the data cleaning of the database is completed; If the amount of deleted data in the data deletion result of the current batch is greater than or equal to the preset batch size, the data deletion operation of the next batch is performed according to the preset batch size.

8. A data cleaning device for a database, characterized in that: include: A parameter acquisition module, used to acquire database cleaning parameters, wherein the database cleaning parameters include the database data retention time and the database rolling cleaning cycle; A first condition determination module, used to determine the historical data cleaning condition corresponding to the database according to the data retention time; A second condition determination module is used to determine the data increment time by using the data retention time and the rolling cleanup cycle, and determine the invalid data cleanup condition corresponding to the database according to the data increment time; The data deletion module is used to delete the data in the database based on the historical data cleaning condition and the invalid data cleaning condition.

9. A computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the method for cleaning database data according to any one of claims 1 to 7.

10. An electronic device, characterized in that: include: processor; and a memory for storing executable instructions for the processor; The processor is configured to execute the database data cleaning method according to any one of claims 1 to 7 by executing the executable instructions.

Citation Information

Patent Citations

  • Expired data cleaning method and device and electronic equipment

    CN112486966A

  • Table data backup cleaning method and device based on life cycle

    CN112905386A

  • Data detection method and device, storage medium and electronic device

    CN113434492A

  • Data processing method and device, equipment, storage medium and product

    CN114090580A

  • Method for cleaning historical data in unstructured database and terminal

    CN114116685A