Method for reducing database update amount based on updating invalid fields alone

Abstract

The application discloses a method for reducing database update amount based on separately updating invalidity of marked fields, and has the characteristics that the method comprises the following steps: step S1: through database management software, a new database table is created or modified, and the newly created or modified database table is set as a variable T, and a database table T is obtained; and the like steps. By marking the database field with the attribute of "separately updating invalidity" in advance when the table is created, it can be effectively judged whether the update statement submitted to the database is valid, so that the invalid update statement is not executed by the database, the data amount of binlog is reduced, the network flow of binlog replication between the master database and the slave database is reduced, the database update amount and the database execution pressure are effectively reduced, the IT system service capability is improved, and thus the application can well solve the problem of the large update amount of the IT system database.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database updating, and particularly relates to a method for reducing database updating amount based on single updating invalidity of marked fields. BACKGROUND

[0002] With the increasing complexity of modern IT systems, the amount of data managed by the systems is also increasing, and data updating is also more and more frequent. When data is updated, a large amount of update logs will be generated at the database level, and a certain amount of invalid statements will exist in the update logs, so that the data amount of Binlog is too large, data synchronization delay occurs, the timeliness of writing and reading is affected, and the usability of the system is affected. In addition, if an IT developer analyzes the cause only from the perspective of reading codes, a large amount of manpower will be consumed.

[0003] Therefore, the existing IT system cannot determine the validity of the updating statement, so that the database of the IT system has the problem of large updating amount, data synchronization delay occurs, and the writing and reading of data are not timely. Therefore, a database updating amount processing method is needed, which can effectively reduce the database updating amount, improve the data synchronization rate, and save manpower and material resources for the maintenance and management of the update logs. SUMMARY

[0004] The present application aims to solve the above problems, and provides a method for reducing database updating amount based on single updating invalidity of marked fields, which can effectively reduce the database updating amount, improve the data synchronization rate, and save manpower and material resources for the maintenance and management of the update logs.

[0005] The purpose of the present application is achieved by the following technical solutions.

[0006] A method for reducing database updating amount based on single updating invalidity of marked fields comprises the following steps.

[0007] Step S1: A database table is newly created or modified by using a database management software, and the newly created or modified database table is set as a variable T, so that the database table T is obtained.

[0008] Step S2: An "single updating invalidity" field constraint option is added to each field of the database table T, an "single updating invalidity" attribute is configured for each field of the database table T, and a corresponding relationship between the field and the "single updating invalidity" attribute is established.

[0009] Step S3: The corresponding relationship between the field and the "single updating invalidity" attribute is stored.

[0010] Step S4: Extract the fields with the attribute Fy=0 of the flag "invalid for individual update" in the storage, and set the extracted fields as the field set M.

[0011] Step S5: When the database executes the update statement of the database table T, extract the new value and the original value of the field of the update statement.

[0012] Step S6: Traverse and compare the new value and the original value of the field in the update statement, and after the traversal, check whether there is a difference between the new value and the original value of the field in the update statement. If yes, generate the field set Y, add the field of the update statement to the field set Y, and execute step S7; if no, the database does not execute the statement and discards it, and does not generate the binlog.

[0013] Step S7: Determine whether the field set Y is a subset of the field set M. If yes, the database does not execute the statement and discards it, and does not generate the binlog; if no, the database executes the statement and generates the binlog.

[0014] As a preferred technical solution of the present application, in step S2, the "invalid for individual update" field constraint option is used to record the corresponding relationship between the field and the indication of whether the "invalid for individual update" is checked, and the attribute of the "invalid for individual update" is set as Fy, and the default value of Fy is 1; wherein Fy=0 indicates that the "invalid for individual update" is checked, and Fy=1 indicates that the "invalid for individual update" is not checked.

[0015] As a preferred technical solution of the present application, in step S2, the fields are F1, F2, …, Fn, "n" represents that the number of fields is a positive integer, and n≧1; F1 represents the first field, F2 represents the second field, and Fn represents the nth field; the "invalid for individual update" attributes configured for each field are Fy1, Fy2, …, Fyn in turn, and F1, F2, …, Fn and Fy1, Fy2, …, Fyn are in one-to-one correspondence.

[0016] As a preferred technical solution of the present application, in step S4, the field set M is F1, F2, …, Fm, "m" represents that the number of fields is a positive integer, and n≧m≧1.

[0017] As a preferred technical solution of the present application, in step S5, the new value and the original value of the field of the update statement are as follows:

[0018] UPDATE T SET F1=newF1, F2=newF2, …, Fn=newFn, wherein newF1 represents the new value of the F1 field, newF2 represents the new value of the F2 field, and newFn represents the new value of the Fn field.

[0019] WHERE F1=oldF1, F2=oldF2, ……Fn=oldFn; wherein, oldF1 represents the original value of the F1 field which is not executed by the statement, oldF2 represents the original value of the F2 field which is not executed by the statement, and oldFn represents the original value of the Fn field which is not executed by the statement.

[0020] As a preferred technical solution of the present application, in the step S6, the field set Y is: Fi1, Fi2, ……Fiy.

[0021] As a preferred technical solution of the present application, in the step S7, the field set Y is a subset of the field set M, wherein Y=Fi1, Fi2,...Fiy, and M=Fi1, Fi2,...Fim.

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

[0023] By marking the database field with the attribute of "invalid for individual update" in advance when building a table, the present application can effectively determine whether the update statement submitted to the database is valid, so that the invalid update statement is not executed by the database, the data volume of the binlog is reduced, the network traffic of the binlog replication between the master and slave databases is reduced, the update volume of the database is effectively reduced, the execution pressure of the database is reduced, and the service capability of the IT system is improved, thereby the present application solves the problem of large update volume of the IT system database. DETAILED DESCRIPTION

[0024] The present application will be further described in detail below in combination with embodiments, but the embodiments of the present application are not limited thereto.

[0025] Embodiment

[0026] The method for reducing the database update volume based on the marked field invalid for individual update of the present application comprises the following steps in the specific embodiment:

[0027] Step S1: a database table is newly built or modified through a database management software, and the newly built or modified database table is set as a variable T to obtain the database table T.

[0028] Step S2: a "invalid for individual update" field constraint option is added for each field of the database table T, a "invalid for individual update" attribute is configured for each field of the database table T, and a corresponding relationship between the field and the "invalid for individual update" attribute is established.

[0029] The step S2, the "separate update invalid" field constraint option is used to record the corresponding relationship between the field and the "separate update invalid" check, and the attribute of the "separate update invalid" is set as Fy, and the default value of Fy is 1. When the "separate update invalid" is checked, Fy=0. When the "separate update invalid" is not checked, Fy=1. The field priority in the embodiment is set as F1, F2,..., Fn, "n" represents the number of fields as a positive integer, and n≧1; F1 represents the first field, F2 represents the second field, and Fn represents the nth field; the "separate update invalid" attribute of each field is Fy1, Fy2,..., Fyn, and F1, F2,..., Fn and Fy1, Fy2,..., Fyn are in one-to-one correspondence. For example, the "separate update invalid" attribute of the field set F1 is Fy1, the "separate update invalid" attribute of the field set F2 is Fy2, and the "separate update invalid" attribute of the field set Fn is Fyn.

[0030] Step S3: The corresponding relationship between the field and the "separate update invalid" attribute is stored.

[0031] Step S4: The field with the attribute Fy=0 of the "separate update invalid" is extracted from the stored field, and the extracted field is set as the field set M. The field set M is F1, F2,..., Fm, "m" represents the number of fields as a positive integer, and n≧m≧1.

[0032] Step S5: When the database executes the update statement of the database table T, the new value and the original value of the field of the update statement are extracted.

[0033] The new value and the original value of the field of the update statement are as follows:

[0034] UPDATE T SET F1=newF1, F2=newF2,..., Fn=newFn, wherein newF1 represents the new value of the F1 field, newF2 represents the new value of the F2 field, and newFn represents the new value of the Fn field.

[0035] WHERE F1=oldF1, F2=oldF2,..., Fn=oldFn; wherein oldF1 represents the original value of the F1 field before the execution of the statement, oldF2 represents the original value of the F2 field before the execution of the statement, and oldFn represents the original value of the Fn field before the execution of the statement.

[0036] Step S6: traverse and compare the new value and the original value of the field in the update statement, after the traversal, check whether there is a difference between the new value and the original value of the field in the update statement, if yes, generate a field set Y, add the field in the update statement to the field set Y, and execute step S7; after the traversal, if there is no difference between the new value and the original value of all fields, the database does not execute the statement and discards, and does not generate binlog. Specifically, the field set Y is: F i1, F i2, …, F iy.

[0037] Step S7: determine whether the field set Y is a subset of the field set M, if yes, the database does not execute the statement and discards, and does not generate binlog; if no, the database executes the statement, and generates binlog.

[0038] Wherein, the field set Y is a subset of the field set M, wherein Y = F i1, F i2, …, F iy, M = F i1, F i2, …, F im.

[0039] In specific implementation, by newly creating or modifying the database table, and setting the "separate update invalid" field constraint option in the newly created or modified database table, and by labeling the "separate update invalid" attribute to the database field in advance when creating the table for the variable T, it can be effectively judged whether the update statement submitted to the database is valid, so that the invalid update statement is not executed by the database, the data amount of binlog is reduced, the network traffic of binlog replication between the master and slave databases is reduced, the update amount of the database is effectively reduced, and the execution pressure of the database is reduced, the service capability of the IT system is improved, and thus the present application can well solve the problem of large update amount of the IT system database.

[0040] As described above, the present application can be well implemented.

Claims

1. A method for reducing the amount of database updates based on invalid updates of individual update fields, characterized in that, Includes the following steps: Step S1: Using database management software, create or modify a database table, and set the newly created or modified database table as variable T to obtain database table T; Step S2: Add a "Invalid for Individual Updates" field constraint option to each field of database table T, configure a "Invalid for Individual Updates" attribute for each field of database table T, and establish the correspondence between fields and "Invalid for Individual Updates" attributes; Step S3: Store the correspondence between the fields and the attributes that are "invalid for individual updates"; Step S4: Extract the fields in storage that have the attribute Fy=0 marked as "Invalid update alone", and set the extracted fields as the field set M; Step S5: When the database executes the update statement for database table T, it extracts the new and original values ​​of the fields in the update statement. Step S6: Iterate through and compare the new and original values ​​of the fields in the update statement. After the iteration is complete, check if there is a difference between the new and original values ​​of the fields in the update statement. If there is, generate a field set Y, add the fields of the update statement to the field set Y, and execute step S7. After the iteration is complete, if there is no difference between the new and old values ​​of all fields, the database will not execute the statement and will discard it without generating binlog. Step S7: Determine whether field set Y is a subset of field set M. If yes, the database will not execute the statement and will discard it without generating binlog; otherwise, the database will execute the statement and generate binlog.

2. The method for reducing the amount of database update based on the separate update of the invalidation field according to claim 1, characterized in that, In step S2, the "Invalid Individual Update" field constraint option is used to record the correspondence between the field and whether the field is checked for "Invalid Individual Update". The attribute of "Invalid Individual Update" is set to Fy, and the default value of Fy is 1. When Fy = 0, it means that "Invalid Individual Update" is checked, and when Fy = 1, it means that "Invalid Individual Update" is not checked.

3. The method for reducing the amount of database updates based on updating individual fields of invalidation tags according to claim 2, wherein, In step S2, the fields are: F1, F2, ..., Fn, where "n" indicates that the number of fields is a positive integer and n ≥ 1; F1 represents the first field, F2 represents the second field, and Fn represents the nth field; the "invalid update for each field" attribute is configured as Fy1, Fy2, ..., Fyn, and F1, F2, ..., Fn corresponds one-to-one with Fy1, Fy2, ..., Fyn.

4. The method for reducing database update volume based on invalid updates of labeled fields according to claim 3, characterized in that, In step S4, the field set M is: F1, F2……Fm, where "m" indicates that the number of fields is a positive integer and n≧m≧1.

5. The method for reducing database update volume based on invalid updates of labeled fields according to claim 4, characterized in that, In step S5, the new and original values ​​of the fields in the update statement are as follows: UPDATE TSET F1 = newF1, F2 = newF2, ..., Fn = newFn, where newF1 represents the new value of field F1, newF2 represents the new value of field F2, and newFn represents the new value of field Fn. WHERE F1 = oldF1, F2 = oldF2, ..., Fn = oldFn; where oldF1 represents the original value of field F1 before the statement was executed, oldF2 represents the original value of field F2 before the statement was executed, and oldFn represents the original value of field Fn before the statement was executed.

6. The method for reducing database update volume based on invalid updates of labeled fields according to claim 5, characterized in that, In step S6, the field set Y is: Fi1, Fi2, ..., Fiy.

7. The method for reducing database update volume based on invalid updates of labeled fields according to claim 6, characterized in that, In step S7, when the field set Y is a subset of the field set M, Y = Fi1, Fi2, ... Fiy, and M = Fi1, Fi2, ... Fim.