Data cleaning method and terminal of a database

Abstract

The application discloses a data cleaning method and a terminal of a database, wherein slow query logs in a first database are monitored, and when the slow query logs exceed a preset number to generate an abnormal database table list, it indicates that there is a possibility of abnormal data in the database. A test database table is created in a second database according to the abnormal database table list, and test data corresponding to the data amount of the abnormal database table is added to the test database table. In this way, the deviation value of the size of the abnormal list can be determined according to the size of the abnormal list and the test list. When the deviation value reaches a threshold value, it is considered that there is a data hole in the list, and data cleaning is automatically performed for the abnormal list, thereby reducing the abnormal data stored in the database table and improving the overall system performance.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database data processing, in particular to a database data cleaning method and terminal. BACKGROUND

[0002] At present, most of the projects need to use a database to store data, and most of the systems commonly use a relational database. The common operation is to add, delete, modify and query. When the data volume reaches a certain level, some useless data will be deleted.

[0003] However, after deleting the database table data, the database does not immediately perform data file cleaning. At this time, the internal logic of the database should only make a soft deletion mark for the data. When certain conditions are met, manually trigger the internal cleaning function to perform the real data cleaning operation. For example, the alter table statement execution condition will trigger the internal cleaning function. At this time, the problem is that the internal cleaning function cannot be automatically triggered by the program, and triggering the cleaning function will cause the table to be locked, which will have a certain impact on the database. SUMMARY

[0004] The technical problem to be solved by the present application is to provide a database data cleaning method and terminal, which can automatically clean the hole data in the database table and improve the database performance.

[0005] In order to solve the above technical problems, the technical scheme adopted by the present application is:

[0006] A database data cleaning method, comprising the steps of:

[0007] Timingly obtaining the slow query log in the first database, parsing the database table corresponding to the slow query log, and if the number of slow query logs reaches a preset number, obtaining an abnormal database table list according to the slow query log;

[0008] According to the abnormal database table list, a corresponding test database table is created in the second database, and test data corresponding to the data volume of the corresponding abnormal database table is added in the test database table.

[0009] According to the file size of the abnormal database table and the file size of the test database table, the deviation value of the abnormal database table size is calculated, and if the deviation value reaches a threshold value, the data with a soft deletion mark in the abnormal database table is cleaned.

[0010] In order to solve the above technical problems, another technical scheme adopted by the present application is:

[0011] A data cleaning terminal of a database comprises a memory, a processor and a computer program stored on the memory and executable on the processor, and the processor implements the following steps when executing the computer program:

[0012] Slow query logs in a first database are acquired in time, database tables corresponding to the slow query logs are parsed, and if the number of slow query logs reaches a preset number, an abnormal database table list is obtained according to the slow query logs;

[0013] Corresponding test database tables are created in a second database according to the abnormal database table list, and test data corresponding to the data amount of the corresponding abnormal database tables is added to the test database tables;

[0014] The deviation value of the abnormal database table size is calculated according to the file size of the abnormal database table and the file size of the test database table, and if the deviation value reaches a threshold value, the data with a soft deletion identifier in the abnormal database table is cleaned.

[0015] The present application has the beneficial effects that: by monitoring the slow query logs in the first database, when the slow query logs exceed the preset number to generate the abnormal database table list, it indicates that there is a possibility of abnormal data in the database. The test database tables are created in the second database according to the abnormal database table list, and the test data corresponding to the data amount of the abnormal database tables is added to the test database tables. In this way, the deviation value of the abnormal list size can be determined according to the size of the abnormal list and the test list, and when the deviation value reaches the threshold value, it is considered that there is a data hole in the list. The data cleaning for the abnormal list is automatically performed, thereby reducing the abnormal data stored in the database table and improving the overall system performance. BRIEF DESCRIPTION OF DRAWINGS

[0016] Figure 1 A flowchart of a database data cleaning method according to an embodiment of the present application;

[0017] Figure 2 A schematic diagram of a database data cleaning terminal according to an embodiment of the present application;

[0018] Label explanation:

[0019] 1. A database data cleaning terminal; 2. Memory; 3. Processor. DETAILED DESCRIPTION

[0020] To explain the technical content, purposes and effects of the present application in detail, the following embodiments are described in conjunction with the drawings.

[0021] Glossary:

[0022] Slow query log: the slow query log is a log record provided by MySQL, which is used to record the statements with response time exceeding a threshold in MySQL;

[0023] Soft deletion: the deletion mode is not actually deleting the record from the database, but filtering out the record through a specific marking method when querying.

[0024] Please refer to Figure 1 The embodiment of the application provides a database data cleaning method, comprising the steps of:

[0025] Slow query logs in a first database are acquired in a timely manner, database tables corresponding to the slow query logs are parsed, and if the number of slow query logs reaches a preset number, an abnormal database table list is obtained according to the slow query logs;

[0026] Corresponding test database tables are created in a second database according to the abnormal database table list, and test data corresponding to the data volume of the corresponding abnormal database tables is added to the test database tables;

[0027] The deviation value of the abnormal database table size is calculated according to the file size of the abnormal database table and the file size of the test database table, and if the deviation value reaches a threshold value, the data with a soft deletion identifier in the abnormal database table is cleaned.

[0028] From the above description, the beneficial effects of the application are that: by monitoring the slow query logs in the first database, when the slow query logs exceed the preset number to generate the abnormal database table list, it indicates that there is a possibility of abnormal data in the database. The test database tables are created in the second database according to the abnormal database table list, and the test data corresponding to the data volume of the abnormal database tables is added to the test database tables. In this way, the deviation value of the abnormal list size can be determined according to the size of the abnormal list and the test list, and when the deviation value reaches the threshold value, it is considered that there is a data hole in the list. The data cleaning for the abnormal list is automatically performed, thereby reducing the abnormal data stored in the database table and improving the overall system performance.

[0029] Further, creating corresponding test database tables in the second database according to the abnormal database table list and adding test data corresponding to the data volume of the corresponding abnormal database tables in the test database tables comprises:

[0030] Creating test database tables consistent with the format type of the abnormal database tables in the abnormal database table list in the second database;

[0031] According to the data volume of the corresponding abnormal database table of the test database table, test data with a preset multiple of the data volume is added to the test database table, and the preset multiple does not exceed 1.

[0032] As can be known from the above description, the abnormal library table created in the same format as the test library table of the second database is convenient for subsequent simulation evaluation of the abnormal library table based on the test library table; and the test data of the preset multiple data quantity added in the test library table can also reduce the storage and calculation pressure of the test library table and improve the overall data cleaning efficiency.

[0033] Further, the calculation of the deviation value of the abnormal library table size according to the file size of the abnormal library table and the file size of the test library table comprises:

[0034] The file size of the test library table is divided by the preset multiple to obtain the theoretical size of the abnormal library table, and the difference between the file size of the abnormal library table and the theoretical size is taken as the deviation value of the abnormal library table size.

[0035] As can be known from the above description, the number of possible data holes can be determined by calculating the deviation value of the abnormal library table size, which is convenient for subsequent data cleaning.

[0036] Further, the cleaning of the data with the soft deletion identifier in the abnormal library table comprises:

[0037] It is judged whether the current time is a preset non-active time or whether the current time is in the idle period of the abnormal library table, and if so, the data with the soft deletion identifier in the abnormal library table is cleaned.

[0038] As can be known from the above description, the data hole is cleaned in the preset non-active time or the idle period, which can realize automatic cleaning of data and does not affect the normal use of the database table.

[0039] Further, the judgment of whether the current time is in the idle period of the abnormal library table comprises:

[0040] The historical access data in the abnormal library table is obtained, and the idle period of the abnormal library table is determined according to the historical access data.

[0041] As can be known from the above description, the idle period of the database table is determined in combination with the historical access data, which can dynamically determine the idle period and improve the accuracy of the idle period determination, and further ensures the normal use of the database table.

[0042] Please refer to Figure 2 Another embodiment of the present application provides a database data cleaning terminal, comprising a memory, a processor and a computer program stored in the memory and executable on the processor, and the processor implements the following steps when executing the computer program:

[0043] acquire the slow query log in the first database in time, parse the database table corresponding to the slow query log, if the number of slow query logs reaches a preset number, obtain an abnormal database table list according to the slow query log;

[0044] According to the abnormal database table list, a corresponding test database table is created in the second database, and test data corresponding to the data amount of the corresponding abnormal database table is added in the test database table.

[0045] According to the file size of the abnormal database table and the file size of the test database table, the deviation value of the abnormal database table size is calculated, if the deviation value reaches a threshold value, the data with a soft deletion identifier in the abnormal database table is cleaned.

[0046] From the above description, the beneficial effects of the present application are that by monitoring the slow query log in the first database, when the slow query log exceeds the preset number to generate an abnormal database table list, it indicates that there may be abnormal data in the database. According to the abnormal database table list, a test database table is created in the second database, and test data corresponding to the data amount of the abnormal database table is added in the test database table. In this way, the size of the abnormal list and the test list can be determined according to the size of the abnormal list and the test list. When the deviation value reaches the threshold value, it is considered that there is a data hole in the list, and the data cleaning for the abnormal list is automatically performed, thereby reducing the abnormal data stored in the database table and improving the overall system performance.

[0047] Further, according to the abnormal database table list, a corresponding test database table is created in the second database, and test data corresponding to the data amount of the corresponding abnormal database table is added in the test database table.

[0048] A test database table consistent with the format type of the abnormal database table in the abnormal database table list is created in the second database.

[0049] According to the data amount of the corresponding abnormal database table of the test database table, test data with a preset multiple of the data amount is added in the test database table, and the preset multiple does not exceed 1.

[0050] From the above description, the format type of the abnormal database table of the first database and the test database table of the second database is created, which is convenient for subsequent simulation evaluation of the abnormal database table based on the test database table. Adding test data with a preset multiple of the data amount in the test database table can also reduce the storage and calculation pressure of the test database table and improve the overall data cleaning efficiency.

[0051] Further, the deviation value of the abnormal database table size is calculated according to the file size of the abnormal database table and the file size of the test database table.

[0052] Divide the file size of the test library table by the preset multiple to obtain a theoretical size of the abnormal library table, and subtract the difference between the file size of the abnormal library table and the theoretical size as a deviation value of the abnormal library table size.

[0053] From the above description, by calculating the deviation value of the abnormal library table size, the number of possible data holes can be determined, facilitating subsequent data cleaning.

[0054] Further, the cleaning of the data identified by the soft deletion in the abnormal library table comprises:

[0055] Judging whether the current time is a preset non-active time or whether the current time is in the idle period of the abnormal library table, if yes, cleaning the data identified by the soft deletion in the abnormal library table.

[0056] From the above description, the data hole cleaning is performed in the preset non-active time or the idle period, which can realize automatic cleaning of data and does not affect the normal use of the database table.

[0057] Further, the judgment of whether the current time is in the idle period of the abnormal library table comprises:

[0058] Obtaining historical access data in the abnormal library table, and determining the idle period of the abnormal library table according to the historical access data.

[0059] From the above description, the idle period of the database table is determined in combination with the historical access data, which can dynamically determine the idle period, improve the accuracy of the idle period determination, and further ensure the normal use of the database table.

[0060] The above-mentioned database data cleaning method and terminal are suitable for automatically cleaning the hole data in the database table, improving the database performance, and the following will be described through specific embodiments:

[0061] Embodiment one

[0062] Please refer to Figure 1 A database data cleaning method comprises the following steps:

[0063] S1, obtain the slow query log in the first database at a regular time, parse the database table corresponding to the slow query log, and if the number of slow query logs reaches a preset number, obtain an abnormal library table list according to the slow query log.

[0064] In this embodiment, a general relational database will have a slow log switch, which needs to be turned on, at which time the slow query log will be recorded. And set the slow log over 1 second to output.

[0065] There is an asynchronous program that can pull slow log information at a certain time and parse the corresponding library table and the time required for slow query in the slow log. When the number of library table slow queries reaches a certain proportion or quantity, an abnormal library table list can be compiled.

[0066] S2, create a corresponding test library table in the second database according to the abnormal library table list, and add test data corresponding to the data volume of the corresponding abnormal library table in the test library table.

[0067] Specifically, a test library table consistent with the format type of the abnormal library table in the abnormal library table list is created in the second database; according to the data volume of the corresponding abnormal library table of the test library table, test data with a preset multiple of the data volume is added in the test library table, and the preset multiple does not exceed 1.

[0068] In this embodiment, for the list in the abnormal library table, a corresponding library table is created in another new database instance, and a certain amount of data, such as 1000 or 10000, is added in the library table.

[0069] The format of the newly created database and the old database must be the same, such as database encoding type, character type, etc. The main purpose is to create a new database of the same type to evaluate and simulate data.

[0070] The step of adding data to the new database is the normal data writing function. 1000 or 10000 data can be exported from the old library table and written into the new library. The purpose of this step is to verify that under the same conditions, a certain amount of data has a corresponding data file size, which serves as a standard for subsequent data file evaluation.

[0071] S3, calculate the deviation value of the abnormal library table size according to the file size of the abnormal library table and the file size of the test library table, and if the deviation value reaches a threshold value, clean up the data with a soft delete mark in the abnormal library table.

[0072] Specifically, the file size of the test library table is divided by the preset multiple to obtain the theoretical size of the abnormal library table, and the difference between the file size of the abnormal library table and the theoretical size is taken as the deviation value of the abnormal library table size.

[0073] If the deviation value reaches a threshold value, the historical access data in the abnormal library table is obtained, the idle period of the abnormal library table is determined according to the historical access data, it is judged whether the current time is a preset non-active time or whether the current time is in the idle period of the abnormal library table, and if so, the data with a soft delete mark in the abnormal library table is cleaned up.

[0074] In the embodiment, according to the file size of the normal library table and the file size of the test library table, the deviation value of the size of the abnormal library table can be calculated, for example, in the new library, under the same condition, the data file size of 10,000 data is 1G, and in the old library, there are 1,000,000 data, so theoretically the size of the data file should be about 100G, if there is a large gap, for example, 150G, then there may be a data hole.

[0075] When the deviation value reaches a certain threshold, it is judged that the table has a data hole, at this time, the trigger operation of the internal cleaning function can be selected in the non-active time, that is, selected as the night 23:00 to 5:00, or the idle period dynamically calculated according to the historical access data condition.

[0076] Since the soft deletion method is not really deleted from the database, but through a specific marking method, the record is filtered out when querying. Although the data cannot be seen on the interface, the database still exists, so the data cleaning cleans the data with the soft deletion mark in the library table.

[0077] Embodiment two

[0078] Please refer to Figure 2 A database data cleaning terminal 1, comprising a memory 2, a processor 3 and a computer program stored in the memory 2 and executable on the processor 3, wherein the processor 3 implements each step of the database data cleaning method of embodiment one when executing the computer program.

[0079] In summary, the database data cleaning method and terminal provided by the application can monitor the slow query log in the first database, and when the slow query log exceeds the preset number to generate an abnormal library table list, it indicates that there may be abnormal data in the database. According to the abnormal library table list, a test library table is created in the second database, and test data corresponding to the data volume of the abnormal library table is added to the test library table. In this way, the deviation value of the size of the abnormal list can be determined according to the size of the abnormal list and the test list, and when the deviation value reaches the threshold value, it is considered that there is a data hole in the list. The abnormal list is automatically cleaned, thereby reducing the abnormal data stored in the library table and improving the overall system performance.

[0080] The above is only an embodiment of the application, and does not limit the patent range of the application, and any equivalent transformation or direct or indirect application in related technical fields according to the content of the application specification and drawings is also included in the patent protection range of the application.

Claims

1. A data cleaning method of a database, characterized by, The method comprises the steps of: acquiring, in a timely manner, slow query logs in a first database, parsing database tables corresponding to the slow query logs, and obtaining an abnormal database table list according to the slow query logs if the number of slow query logs reaches a preset number; creating corresponding test database tables in a second database according to the abnormal database table list, and adding test data corresponding to the data volume of the corresponding abnormal database tables in the test database tables; creating test database tables consistent with the format types of the abnormal database tables in the abnormal database table list in the second database; adding test data of a preset multiple of the data volume of the corresponding abnormal database tables in the test database tables, the preset multiple being not more than 1; calculating an abnormal database table size deviation value according to the file size of the abnormal database tables and the file size of the test database tables, and cleaning the data with soft deletion marks in the abnormal database tables if the deviation value reaches a threshold value; 2. The data cleaning method of a database according to claim 1, wherein, the method of calculating the abnormal database table size deviation value according to the file size of the abnormal database tables and the file size of the test database tables comprises: dividing the file size of the test database tables by the preset multiple to obtain the theoretical size of the abnormal database tables, and taking the difference between the file size of the abnormal database tables and the theoretical size as the abnormal database table size deviation value. the method of cleaning the data with soft deletion marks in the abnormal database tables comprises:

3. The method of claim 2, wherein, judging whether the current time is a preset non-active time or whether the current time is before the idle period of the abnormal database tables, and cleaning the data with soft deletion marks in the abnormal database tables if the current time is the preset non-active time or the current time is before the idle period of the abnormal database tables. the method of judging whether the current time is before the idle period of the abnormal database tables comprises:

4. A data cleaning terminal of a database, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, acquiring historical access data in the abnormal database tables, and determining the idle period of the abnormal database tables according to the historical access data. The processor executes the computer program to implement the following steps: acquiring, in a timely manner, slow query logs in a first database, parsing database tables corresponding to the slow query logs, and obtaining an abnormal database table list according to the slow query logs if the number of slow query logs reaches a preset number; creating corresponding test database tables in a second database according to the abnormal database table list, and adding test data corresponding to the data volume of the corresponding abnormal database tables in the test database tables; creating test database tables consistent with the format types of the abnormal database tables in the abnormal database table list in the second database; adding test data of a preset multiple of the data volume of the corresponding abnormal database tables in the test database tables, the preset multiple being not more than 1; 5. The data cleaning terminal of a database according to claim 4, wherein, calculating an abnormal database table size deviation value according to the file size of the abnormal database tables and the file size of the test database tables, and cleaning the data with soft deletion marks in the abnormal database tables if the deviation value reaches a threshold value; the method of calculating the abnormal database table size deviation value according to the file size of the abnormal database tables and the file size of the test database tables comprises: dividing the file size of the test database tables by the preset multiple to obtain the theoretical size of the abnormal database tables, and taking the difference between the file size of the abnormal database tables and the theoretical size as the abnormal database table size deviation value. the method of cleaning the data with soft deletion marks in the abnormal database tables comprises: Determine whether the current time is a preset non-active time or whether the current time is in the idle period of the exception library table, and if so, clean up the data with a soft deletion identifier in the exception library table.

6. The data cleaning terminal of a database according to claim 5, wherein, Before determining whether the current time is in the idle period of the exception library table, the method further includes: Obtain historical access data in the exception library table, and determine the idle period of the exception library table according to the historical access data.

Images