Intelligent large meter cleaning method, device and equipment and medium

Abstract

The invention relates to an intelligent large table cleaning method, device and equipment and a medium, and belongs to the technical field of database management.The method comprises the following steps that large tables in a preset scanning range are scanned regularly; entering an intelligent cleaning rule judgment sub-process, and generating a cleaning rule; and judging whether a cleaning condition is met or not according to the cleaning rule, if the cleaning condition is met, automatically generating a large table cleaning SQL script according to the cleaning rule, and respectively executing corresponding large table cleaning sub-processes according to whether the partition table exists or not. Compared with the prior art, the method has the advantages of intelligent detection, self-adaption, high compatibility, high automation degree and the like.

Description

Technical Field

[0001] The present invention relates to database management technology, and in particular to an intelligent large table cleaning method, device, equipment and medium. Background Art

[0002] When choosing a database cleaning tool, you need to consider the type of data, tool compatibility, supported data formats, and whether it can meet specific database cleaning requirements. To meet the needs of large table cleaning, it is necessary to have three main functions: identification and determination of large tables, automatic generation of cleaning scripts, support for multiple database types (MySQL, Oracle) and multiple data sources, and automatic data backup. At present, there are many types of database performance diagnostic tools, such as MySQLTuner, a widely used database performance diagnostic tool that can check the rationality of parameter settings, provide improvement suggestions, and support multiple MySQL versions, but it is not suitable for Oracle databases, and users need to have certain database knowledge to understand the diagnostic results and suggestions. There are also database cleaning tools such as OpenRefine, Trifacta Wrangler, WinPure, etc., which support interactive cleaning, but most of them need to be deployed locally and require professional database knowledge for configuration. They lack the function of multiple data sources based on cloud services, and their security cannot meet the needs of autonomous control. There are also software for data transmission and backup, such as various database dump tools and data synchronization tools such as DataX, but they are only suitable for data backup and transmission.

[0003] To sum up, although there are many existing database cleaning tools and systems, there is a lack of integrated diagnosis and prediction, automatic execution and automatic backup. Various tools can be connected in series through manual execution, but it requires very professional DBA knowledge, and manual execution is inefficient, costly, and there is a risk of human error, which makes it difficult to meet the security and stability requirements of Internet companies. Summary of the invention

[0004] The purpose of the present invention is to provide an intelligent large table cleaning method, device, equipment and medium in order to overcome the defects of the above-mentioned prior art.

[0005] The purpose of the present invention can be achieved by the following technical solutions:

[0006] According to a first aspect of the present invention, there is provided an intelligent large table cleaning method, the method comprising the following steps:

[0007] Regularly scan large tables within a preset scan range;

[0008] Enter the intelligent judgment cleaning rule sub-process and determine the cleaning rules;

[0009] Determine whether the cleanup conditions are met according to the cleanup rules. If the cleanup conditions are met, automatically generate a large table cleanup SQL script according to the cleanup rules, and execute the corresponding large table cleanup sub-process according to the generated SQL script.

[0010] As a preferred technical solution, the intelligent judgment cleaning rule sub-process is specifically as follows:

[0011] Determine whether there are existing cleaning rules. If so, directly use the existing cleaning rules. If not, perform the following steps:

[0012] Read the table creation information to determine whether a partition table exists;

[0013] If a partition table exists, the query conditions in the logs within the most recent time range are scanned, and the query range with the largest usage is retrieved based on the fields used by the partition table;

[0014] If there is no partition table, find the most used query SQL in the recent time range through clustering and identify the most used fields in the query conditions. Then retrieve the most used query range based on the fields.

[0015] Estimate the amount of data that needs to be retained in the data table based on the maximum query scope and generate cleanup rules.

[0016] As a preferred technical solution, the query range with the largest usage is the range for query based on timestamp or ID. If it is the range for query based on timestamp, the generated cleaning rule is to retain the data of the first time range. If it is the range for query based on ID, the generated cleaning rule is to retain the data of the first number of data items. The first time range and the first number of data items are determined according to the amount of data to be retained.

[0017] As a preferred technical solution, the intelligent judgment cleaning rule sub-process also includes the following steps:

[0018] After the cleanup rule is generated, it is determined whether manual confirmation is required based on the preset value. If manual confirmation is required, the newly generated cleanup rule is saved and updated through manual confirmation. If manual confirmation is not required, the cleanup rule is directly saved.

[0019] As a preferred technical solution, when there is a partition table, the large table cleaning sub-process is specifically as follows:

[0020] Determine the partitions that need to be cleaned according to the cleanup rules, back up the partitions that need to be cleaned to the archive library, delete the partitions that need to be cleaned, and add the same number of partitions based on the number of partitions cleaned.

[0021] As a preferred technical solution, when there is no partition table, the large table cleaning sub-process is specifically as follows:

[0022] Rename the large table to a temporary table;

[0023] A new table with partitions is created, wherein the number of partitions in the new table is determined according to the cleaning rules generated by the intelligent judgment cleaning sub-rule process;

[0024] Import the data to be retained from the temporary table to the corresponding partition of the new table according to the corresponding relationship, and back up the remaining data in the temporary table to the archive library;

[0025] Delete the temporary table.

[0026] As a preferred technical solution, each step of the large table cleanup sub-process execution process is recorded in the database and the execution status is updated. During the execution process, it supports step-by-step execution according to business needs. When the execution process is interrupted, it supports continuing the unfinished steps from the interrupted steps according to business needs.

[0027] According to a second aspect of the present invention, there is provided an intelligent large table cleaning device, the device comprising:

[0028] Periodic scanning module, used to periodically scan large tables within a preset scanning range;

[0029] An intelligent judgment cleaning rule determination module is used to execute the intelligent judgment cleaning rule sub-process and determine the cleaning rules;

[0030] The large table cleanup module is used to determine whether the cleanup conditions are met according to the cleanup rules. If the cleanup conditions are met, the large table cleanup SQL script is automatically generated according to the cleanup rules, and the corresponding large table cleanup sub-process is executed according to the generated SQL script.

[0031] According to a third aspect of the present invention, there is provided an electronic device, comprising a memory and a processor, wherein a computer program is stored in the memory, and the method described above is implemented when the processor executes the program.

[0032] According to a fourth aspect of the present invention, there is provided a computer-readable storage medium having a computer program stored thereon, wherein the program implements the method described when executed by a processor.

[0033] Compared with the prior art, the present invention has the following beneficial effects:

[0034] (1) When a large amount of temporary data is generated during the program processing every day, such data is only used for temporary storage (such as log tables, record tables, and behavior tables), and can be cleaned up after a period of time after the business process is completed. A large amount of processing data is generated. The present invention cleans up such data through automatic execution, and can intelligently generate cleaning rules based on the data itself, which can improve the efficiency of execution. It is more efficient than manual execution, and can automatically generate cleaning rules, which is more adaptable to each data table and has a higher degree of automation.

[0035] (2) Automatically triggering execution through scheduled tasks can ensure timely execution as planned. Automated execution effectively avoids human errors that may occur during manual execution. It can also be set during non-business hours (such as early morning or weekends), reducing overtime for operation and maintenance personnel and lowering the company's operation and maintenance expenses.

[0036] (3) The present invention transforms the large table cleanup function from traditional manual operation to platform automated operation, which can effectively standardize the operation and maintenance steps, improve the operation and maintenance efficiency, and lay the foundation for further improvement of automated operation and maintenance, becoming one of the important tools for operation and maintenance guarantee. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 is a flow chart of the method of the present invention;

[0038] Figure 2 It is a flow chart of the intelligent judgment cleaning rule sub-process of the present invention. DETAILED DESCRIPTION

[0039] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0040] Unless otherwise defined, the technical terms or scientific terms involved in this application should be understood by people with ordinary skills in the technical field to which this application belongs. The words "one", "a", "a", "the" and the like involved in this application do not indicate a quantitative limitation, and may represent the singular or plural. The terms "include", "comprise", "have" and any of their variations involved in this application are intended to cover non-exclusive inclusions; for example, a process, method, system, product or device that includes a series of steps or modules (units) is not limited to the listed steps or units, but may also include steps or units that are not listed, or may also include other steps or units inherent to these processes, methods, products or devices. The words "connect", "connected", "coupled" and the like involved in this application are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. The "multiple" involved in this application refers to two or more. "And / or" describes the association relationship of associated objects, indicating that there may be three relationships, for example, "A and / or B" can represent: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the objects before and after are in an "or" relationship. The terms "first", "second", "third", etc. involved in this application are only used to distinguish similar objects and do not represent a specific ordering of the objects.

[0041] This embodiment provides an intelligent large table cleaning method. Figure 1 As shown, the method comprises the following steps:

[0042] S1, regularly scans the large table within the preset scanning range;

[0043] S2, enter the intelligent judgment cleaning rule sub-process to determine the cleaning rules;

[0044] S3: Determine whether the cleaning condition is met according to the cleaning rule. If the cleaning condition is met, automatically generate a large table cleaning SQL script according to the cleaning rule, and execute the corresponding large table cleaning sub-process according to the generated SQL script.

[0045] like Figure 2 As shown in the figure, the sub-process of intelligent judgment and cleaning rules is as follows:

[0046] Determine whether there are existing cleaning rules. If so, directly use the existing cleaning rules. If not, perform the following steps:

[0047] Read the table creation information to determine whether a partition table exists;

[0048] If a partition table exists, the query conditions in the logs within the most recent time range are scanned, and the query range with the largest usage is retrieved based on the fields used by the partition table;

[0049] If there is no partition table, find the most used query SQL in the recent time range through clustering and identify the most used fields in the query conditions. Then retrieve the most used query range based on the fields.

[0050] Estimate the amount of data that needs to be retained in the data table based on the maximum query scope and generate cleanup rules.

[0051] In this embodiment, the query range with the largest usage is the range that is queried based on the timestamp or ID. If it is a range that is queried based on the timestamp, the generated cleaning rule is to retain the data of the first time range. If it is a range that is queried based on the ID, the generated cleaning rule is to retain the data of the first number of data items. The first time range and the first number of data items are determined according to the amount of data to be retained. In one embodiment, the generated cleaning rule can be: retain the data of the last 3 months or 6 months, or retain the last 1 million data records, or delete the first 20,000 data items in the historical records, etc.

[0052] In a preferred embodiment, after the cleaning rules are generated, it is determined whether manual confirmation is required based on a preset value. If manual confirmation is required, the newly generated cleaning rules are saved and updated through manual confirmation. If manual confirmation is not required, the cleaning rules are directly saved.

[0053] In the actual large table cleanup process, the table creation statements and partition information of the data table to be cleaned are read from the database and a logical inventory is performed. The large table cleanup SQL script is automatically generated according to the cleanup rules, and then a large table cleanup sub-process is executed.

[0054] In this embodiment, different large table cleanup sub-processes are executed according to whether the table is partitioned. The purpose of partitioning is to store data of different ranges separately. Different logical partitions at the bottom of the database are stored as different files. Since directly deleting data will not release storage space, it is necessary to defragment to determine whether there is redundant space. The time for defragmentation is often long and uncontrollable. Therefore, when deleting data, this embodiment uses the method of deleting partitions to directly clean up partition files, so that it can be quickly cleaned up and storage space can be released. Therefore, if there is no partition, it is necessary to create a new table with partitions, import some of the retained data into the new table, and then delete the entire old table directly to free up storage space. Specifically:

[0055] When a partition table exists, the large table cleanup sub-process is specifically as follows:

[0056] S301, determine the partitions that need to be cleaned according to the cleaning rules, back up the partitions that need to be cleaned to the archive library, delete the partitions that need to be cleaned, and add the same number of partitions according to the number of cleaned partitions, so as to keep the total number of partitions in the table unchanged.

[0057] When there is no partition table, the large table cleanup sub-process is specifically as follows:

[0058] S311, rename the large table to a temporary table;

[0059] S312, creating a new table with partitions, where the number of partitions in the new table is determined according to the cleaning rules generated by the intelligent judgment cleaning sub-rule process;

[0060] S313, import the data to be retained from the temporary table into the corresponding partition of the new table according to the corresponding relationship, and back up the remaining data in the temporary table to the archive library;

[0061] S314, delete the temporary table.

[0062] Each step of the large table cleanup sub-process execution process is recorded in the database and the execution status is updated. During the execution process, it supports step-by-step execution according to business needs. When the execution process is interrupted, it supports continuing the unfinished steps from the interrupted steps according to business needs.

[0063] The present invention can be used in the database of the steel e-commerce trading platform. When a large amount of temporary data is generated during the program processing every day, such data is only used for temporary storage (such as log tables, record tables, and behavior tables), and can be cleaned up after a period of time after the business process is completed. A large amount of processing data is generated, and such data is manually cleaned up regularly by the DBA, thereby improving the database disk utilization and query efficiency.

[0064] The above is an introduction to a method embodiment. The following is a further explanation of the solution of the present invention through an apparatus embodiment.

[0065] An intelligent large table cleaning device, the device comprising:

[0066] Periodic scanning module, used to periodically scan large tables within a preset scanning range;

[0067] An intelligent judgment cleaning rule determination module is used to execute the intelligent judgment cleaning rule sub-process and determine the cleaning rules;

[0068] The large table cleanup module is used to determine whether the cleanup conditions are met according to the cleanup rules. If the cleanup conditions are met, the large table cleanup SQL script is automatically generated according to the cleanup rules, and the corresponding large table cleanup sub-process is executed according to the generated SQL script.

[0069] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the described module can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.

[0070] The electronic device of the present invention includes a central processing unit (CPU), which can perform various appropriate actions and processes according to computer program instructions stored in a read-only memory (ROM) or loaded from a storage unit into a random access memory (RAM). In the RAM, various programs and data required for device operation can also be stored. The CPU, ROM and RAM are connected to each other via a bus. An input / output (I / O) interface is also connected to the bus.

[0071] Multiple components in the device are connected to the I / O interface, including: input units, such as keyboards, mice, etc.; output units, such as various types of displays, speakers, etc.; storage units, such as disks, optical disks, etc.; and communication units, such as network cards, modems, wireless communication transceivers, etc. The communication unit allows the device to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunication networks.

[0072] The processing unit performs the various methods and processes described above, such as methods S1 to S3. For example, in some embodiments, methods S1 to S3 may be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as a storage unit. In some embodiments, part or all of the computer program may be loaded and / or installed on the device via a ROM and / or a communication unit. When the computer program is loaded into the RAM and executed by the CPU, one or more steps of methods S1 to S3 described above may be performed. Alternatively, in other embodiments, the CPU may be configured to execute methods S1 to S3 in any other appropriate manner (e.g., by means of firmware).

[0073] The functions described above herein may be performed at least in part by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), and the like.

[0074] The program code for implementing the method of the present invention can be written in any combination of one or more programming languages. These program codes can be provided to a processor or controller of a general-purpose computer, a special-purpose computer or other programmable data processing device, so that the program code, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The program code can be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.

[0075] In the context of the present invention, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0076] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed by the present invention, and these modifications or replacements should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be based on the protection scope of the claims.

Claims

1. An intelligent large table cleaning method, characterized in that: The method comprises the following steps: Regularly scan large tables within a preset scan range; Enter the intelligent judgment cleaning rule sub-process and determine the cleaning rules; Determine whether the cleanup conditions are met according to the cleanup rules. If the cleanup conditions are met, automatically generate a large table cleanup SQL script according to the cleanup rules, and execute the corresponding large table cleanup sub-process according to the generated SQL script.

2. The intelligent large table cleaning method according to claim 1, characterized in that: The intelligent judgment cleaning rule sub-process is specifically as follows: Determine whether there are existing cleaning rules. If so, directly use the existing cleaning rules. If not, perform the following steps: Read the table creation information to determine whether a partition table exists; If a partition table exists, the query conditions in the logs within the most recent time range are scanned, and the query range with the largest usage is retrieved based on the fields used by the partition table; If there is no partition table, find the most used query SQL in the recent time range through clustering and identify the most used fields in the query conditions. Then retrieve the most used query range based on the fields. Estimate the amount of data that needs to be retained in the data table based on the maximum query scope and generate cleanup rules.

3. The intelligent large table cleaning method according to claim 2 is characterized in that: The query range with the largest usage is the range for query based on timestamp or ID. If it is the range for query based on timestamp, the generated cleaning rule is to retain the data of the first time range. If it is the range for query based on ID, the generated cleaning rule is to retain the data of the first number of data items. The first time range and the first number of data items are determined according to the amount of data to be retained.

4. The intelligent large table cleaning method according to claim 2, characterized in that: The intelligent judgment cleaning rule sub-process also includes the following steps: After the cleanup rule is generated, it is determined whether manual confirmation is required based on the preset value. If manual confirmation is required, the newly generated cleanup rule is saved and updated through manual confirmation. If manual confirmation is not required, the cleanup rule is directly saved.

5. The intelligent large table cleaning method according to claim 1, characterized in that: When a partition table exists, the large table cleanup sub-process is specifically as follows: Determine the partitions that need to be cleaned according to the cleanup rules, back up the partitions that need to be cleaned to the archive library, delete the partitions that need to be cleaned, and add the same number of partitions based on the number of partitions cleaned.

6. The intelligent large table cleaning method according to claim 1, characterized in that: When there is no partition table, the large table cleanup sub-process is specifically as follows: Rename the large table to a temporary table; A new table with partitions is created, wherein the number of partitions in the new table is determined according to the cleaning rules generated by the intelligent judgment cleaning sub-rule process; Import the data to be retained from the temporary table to the corresponding partition of the new table according to the corresponding relationship, and back up the remaining data in the temporary table to the archive library; Delete the temporary table.

7. The intelligent large table cleaning method according to claim 1, characterized in that: Each step of the large table cleanup sub-process execution process is recorded in the database and the execution status is updated. During the execution process, it supports step-by-step execution according to business needs. When the execution process is interrupted, it supports continuing the unfinished steps from the interrupted steps according to business needs.

8. An intelligent large table cleaning device, characterized in that: The device includes: Periodic scanning module, used to periodically scan large tables within a preset scanning range; An intelligent judgment cleaning rule determination module is used to execute the intelligent judgment cleaning rule sub-process and determine the cleaning rules; The large table cleanup module is used to determine whether the cleanup conditions are met according to the cleanup rules. If the cleanup conditions are met, the large table cleanup SQL script is automatically generated according to the cleanup rules, and the corresponding large table cleanup sub-process is executed according to the generated SQL script.

9. An electronic device comprising a memory and a processor, wherein a computer program is stored in the memory, wherein: When the processor executes the program, the method according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images