A statistical analysis method for invalid update statements based on binlog analysis

Abstract

The present invention discloses a statistical analysis method for invalid update statements based on binlog analysis, characterized in that the method comprises the following steps: step S1: reading the binlog in the system database, parsing the binlog into a plain text SQL file F, etc. The present invention analyzes the binlog log file of the database, divides the binlog into a plurality of small files according to the SQL statement, collects the update statement string of each small file, and accurately obtains the number of invalid update statements in the binlog by determining and counting whether the updated fields of the update statement string are valid or invalid update statements individually and in combination, thereby well solving the problem that the existing statistical analysis method of invalid update statements cannot determine and count the invalid update statements.

Description

Technical Field

[0001] The present invention relates to the technical field of binlog update statement analysis, and in particular to a statistical analysis method based on binlog analysis of invalid update statements. Background Art

[0002] As modern IT systems become more and more complex, the amount of data they manage is also increasing, and data updates are becoming more and more frequent. Excessive updates will generate more and more update logs at the database level, such as Binlog. The maintenance and management of update logs will consume more and more manpower and material resources. At the same time, in the master-slave structure of the database, if the database IO capacity is certain, data synchronization delays will occur, affecting the timeliness of writing and reading, and thus affecting the availability of the system. For IT developers, if they only analyze the cause from the perspective of flipping through the code, it will cost a lot of manpower.

[0003] It can be seen that the existing statistical analysis method of invalid update statements has the problem of being unable to determine and count invalid update statements, resulting in excessive database updates, resulting in a large amount of manpower and material resources being consumed in the maintenance and management of update logs, seriously affecting the efficiency and stability of system database management. Summary of the invention

[0004] The object of the present invention is to solve the above-mentioned problem and provide a statistical analysis method based on binlog analysis of invalid update statements, which can accurately determine and count invalid update statements in binlog update statements.

[0005] The purpose of the present invention is achieved through the following technical solutions:

[0006] A statistical analysis method for analyzing invalid update statements based on binlog includes the following steps:

[0007] Step S1: Read the Binlog in the system database and parse the Binlog into a plain text SQL file F.

[0008] Step S2: dividing the SQL file F into a number of small files according to the SQL statements, and establishing a file set of a number of the small files.

[0009] Step S3: Read the update statement character string in each small file in the file set, and obtain the database table name in the character string.

[0010] Step S4: Compare the fields whose values ​​are actually updated in the update statement string, and record the updated field set.

[0011] Step S5: Determine whether the update of each field in the updated field set is meaningful to the business, where when each field in the updated field set is no, the string of the update statement is meaningless to the business and is an invalid update; when one of the fields in the updated field set is yes, the string of the update statement is meaningful to the business and is a valid update.

[0012] Step S6: Count the valid updates and invalid updates obtained in step 5, and obtain the final number of invalid update statements based on the statistical results.

[0013] Furthermore, the several small files divided in step S2 are labeled as:

[0014] f1,f2,f3...fn, where each small file has only one update SQL statement; n is equal to the number of update statements, and n>1.

[0015] The file set of the plurality of small files in step S2 is Fset.

[0016] Among them, Fset=f1+f2+f3……fn.

[0017] In step S4, the fields whose values ​​are updated are marked as: c1, c2, ... Cy. According to the binglog generation logic, the number of updated fields with actual values ​​in each update SQL statement is greater than or equal to 1, and y>1.

[0018] In step S4, the updated field set is marked as Qx, and Qx=any combination of C1, C2, ...Cy.

[0019] Furthermore, in step S5, it is determined whether each field C in the updated field set is meaningful to the business.

[0020] Furthermore, in step S6, the method for counting the number of occurrences of the database table name and the corresponding update field set is as follows:

[0021] (1) Check whether each field in the database table name is valid for individual update and mark the valid values ​​respectively. The valid value of the individual update is 1, and the valid value of the individual update is 0.

[0022] (2) Calculate each updated field set value based on the valid values ​​marked by the individual updated fields, where the updated field set value is: the sum of the valid values ​​of the updated fields.

[0023] (3) Determine whether each set of field sets is an invalid update statement based on the obtained value of each set of field sets, wherein if the updated field set value is equal to 0, it is an invalid update statement, and if the updated field set value is greater than 1, it is a valid update statement.

[0024] (4) Count the number of times each updated field set appears to obtain the final number of invalid update statements.

[0025] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0026] The present invention analyzes the binlog log file of the database, divides the binlog into several small files according to SQL statements, collects the update statement character string of each small file, and determines and counts whether the updated fields of the update statement character string are valid or invalid update statements individually and in combination, so as to accurately obtain the number of invalid update statements in the binlog. Therefore, the present invention well solves the problem that the existing statistical analysis method of invalid update statements cannot determine and count the invalid update statements. DETAILED DESCRIPTION

[0027] The present invention is further described in detail below in conjunction with examples, but the embodiments of the present invention are not limited thereto.

[0028] Example

[0029] A statistical analysis method for analyzing invalid update statements based on binlog of the present invention comprises the following steps:

[0030] Step S1: Read the Binlog in the system database and parse the Binlog into a plain text SQL file F.

[0031] Step S2: Divide the SQL file F into several small files according to the SQL statements, and establish a file set of several small files. The several small files are marked as: f1, f2, f3...fn. At the same time, each small file has only one update SQL statement, n is equal to the number of update statements, and n>1. The file set is Fset, where Fset=f1+f2+f3...fn.

[0032] Step S3: Read the update statement string in each small file in the file set, and obtain the database table name in the string. In actual implementation, each update statement string in the file set is marked as Sx, and the database table name is marked as Tx, and the value of x is the same as n.

[0033] Step S4: Compare the fields whose values ​​are actually updated in the update statement string, and record the updated field set. The fields whose values ​​are updated are marked as: c1, c2, ... Cy. According to the binglog generation logic, the number of fields whose values ​​are actually updated in each update SQL statement is greater than or equal to 1, and y>1.

[0034] The updated field set is marked as Qx, and Qx=any combination of C1, C2...Cy.

[0035] Step S5: Determine whether the update of each field in the updated field set is meaningful to the business, wherein when each field in the updated field set is negative, the string of the update statement is meaningless to the business and is an invalid update; when one of the fields in the updated field set is positive, the string of the update statement is meaningful to the business and is a valid update. In specific implementation, determine whether each field C in the updated field set is meaningful to the business.

[0036] Step S6: Count the valid updates and invalid updates obtained in step 5, and obtain the final number of invalid update statements based on the statistical results. The method for counting the number of occurrences of the database table name and the corresponding update field set is as follows:

[0037] (1) Check whether each field in the database table name is valid for separate update and mark the valid value respectively. The valid value of separate update is 1, and the valid value of separate update is 0;

[0038] (2) calculating each updated field set value based on the valid values ​​marked by the individual updated fields, wherein the updated field set value is: the sum of the valid values ​​of the updated fields;

[0039] (3) determining whether each set of field sets is an invalid update statement based on the obtained value of each set of field sets, wherein if the updated field set value is equal to 0, it is an invalid update statement, and if the updated field set value is greater than 1, it is a valid update statement;

[0040] (4) Count the number of times each updated field set appears to obtain the final number of invalid update statements.

[0041] The present invention analyzes the binlog log file of the database, divides the binlog into several small files according to SQL statements, collects the update statement character string of each small file, and determines and counts whether the updated fields of the update statement character string are valid or invalid update statements individually and in combination, so as to accurately obtain the number of invalid update statements in the binlog. Therefore, the present invention well solves the problem that the existing statistical analysis method of invalid update statements cannot determine and count the invalid update statements.

[0042] As described above, the present invention can be well implemented.

Claims

1. A statistical analysis method for invalid update statements based on binlog analysis, characterized in that: The following steps are involved: Step S1: Read the Binlog in the system database and parse the Binlog into a plain text SQL file F; Step S2: dividing the SQL file F into a number of small files according to the SQL statements, and establishing a file set of a number of the small files; Step S3: Read the update statement string in each small file in the file set, and obtain the database table name in the string; Step S4: Compare the fields whose values ​​are actually updated in the update statement string, and record the updated field set; Step S5: respectively determine whether the update of each field in the updated field set is meaningful to the business, wherein when each field in the updated field set is negative, the string of the update statement is meaningless to the business and is an invalid update; when one of the fields in the updated field set is positive, the string of the update statement is meaningful to the business and is a valid update; Step S6: Count the valid updates and invalid updates obtained in step 5, and obtain the final number of invalid update statements based on the statistical results; In step S6, the method for counting the number of occurrences of the database table name and the corresponding update field set is as follows: (1) Check whether each field in the database table name is valid for separate update and mark the valid value respectively. The valid value of separate update is 1, and the valid value of separate update is 0; (2) Calculate the field set value of each update based on the valid values ​​marked by the individual updated fields, where the updated field set value is: the sum of the valid values ​​of the updated fields; (3) Determine whether each set of field sets is an invalid update statement based on the value of each set of field sets obtained, where if the value of the updated field set is equal to 0, it is an invalid update statement, and if the value of the updated field set is greater than 1, it is a valid update statement; (4) Count the number of times each updated field set appears to obtain the final number of invalid update statements.

2. According to claim 1, the statistical analysis method based on binlog analysis of invalid update statements is characterized in that: The several small files divided in step S2 are labeled as: f1, f2, f3...fn, where each small file has only one update SQL statement; n is equal to the number of update statements, and n>1; The file set of the plurality of small files in step S2 is Fset; Among them, Fset=f1+f2+f3……fn.

3. The statistical analysis method based on binlog analysis of invalid update statements according to claim 2, characterized in that: In step S4, the fields whose values ​​have been updated are marked as: c1, c2, ... Cy. According to the binglog generation logic, the number of fields actually updated with values ​​in each update SQL statement is greater than or equal to 1, and y>1; In step S4, the updated field set is marked as Qx, and Qx=any combination of C1, C2...Cy.

4. The statistical analysis method based on binlog analysis of invalid update statements according to claim 3 is characterized in that: In step S5, it is determined whether each field C in the updated field set is meaningful to the business. In step S5, if it is not meaningful, there are two situations: if all fields are no, the update statement is an invalid update; if one field is yes, the update statement is a valid update.