Data storage and query optimization method and system

Abstract

The invention belongs to the technical field of data storage and query, and discloses a data storage and query optimization method, which specifically comprises the following steps: step 1, data pulling, step 2, data change recording, step 3, log record auditing, step 4, quality inspection work, and step 5, global maximum data ID updating. According to the method, through an elaborately designed segmented data storage optimization scheme, the data volume of the log table is remarkably reduced, the specific reduction amplitude reaches about N times, and N represents the number of data pulled in each auditing process; the innovative data storage strategy not only greatly reduces the redundancy of the data, but also significantly slows down the increase speed of the data, thereby ensuring that the query efficiency of the audit log can still be maintained at an extremely high level even after the system continuously operates for a long time.

Description

Technical Field

[0001] The present invention belongs to the technical field of data storage and query, and specifically relates to an optimization method and system for data storage and query. Background Art

[0002] After the system has been running for a period of time, especially when facing a large amount of business data writing, such as operation logs, the amount of data in the database table will grow rapidly. When the system writes a large amount of data, the expansion of the data table is particularly significant. Once the amount of data reaches a certain scale, the query efficiency of the system tends to decrease significantly. Common solutions to this problem include data archiving, table sharding, and database sharding. However, in this solution, we explore how to slow down the growth of the data table by optimizing the data writing method before these routine operations, thereby indirectly improving query efficiency.

[0003] In business scenarios related to data auditing, quality inspection managers need to regularly check the data audit quality of each auditor. To achieve this function, the system needs to record data audit logs. Generally, regardless of whether the audited data has changed, it needs to be recorded in the audit log table. Although this approach ensures the integrity of the data, it will also cause the amount of data in the audit log table to increase rapidly, thereby affecting the efficiency of data retrieval. In general, too fast data growth will lead to too much table data, affecting query efficiency; relatively large data volume will also take up too much disk space.

[0004] The reason why unchanged data is also recorded in the log table is mainly to check whether the auditor has missed any data (for example, there are 3 violations among 100 data, and the auditor only operated on 2). This is also a very critical indicator in quality inspection.

[0005] Therefore, it is necessary to consider how to slow down the growth rate of the audit log table by optimizing the write strategy while ensuring data integrity, so as to maintain the query performance of the system. Summary of the invention

[0006] The object of the present invention is to provide a method and system for optimizing data storage and query, so as to solve the problems raised in the above background technology.

[0007] In order to achieve the above object, the present invention provides the following technical solution: a method for optimizing data storage and query, the specific steps of the optimization method are:

[0008] Step 1: Data extraction: The auditor extracts a fixed number of data (N) at a time for examination in order;

[0009] Step 2: Data change record: During the audit process, for data that has changed, its change information is stored in the operation log table;

[0010] The data changes recorded in the operation log table include the changed data (such as 3, 8, 9, 17, etc. in the example).

[0011] Step 3: Review log records:

[0012] After this round of review is completed, the data pulled this time will be stored in the audit log table in the form of "data fragments".

[0013] In this way, the amount of data written can be significantly reduced and storage efficiency can be improved.

[0014] Step 4: Quality inspection:

[0015] The quality inspector can view all data processed by the customer service (i.e. data that has been changed) through the operation log table. The quality inspector can also view all data segments processed by the customer service through the audit log table; using the start ID and end ID in the data segment, the quality inspector can further view the original data and complete the quality inspection work;

[0016] Step 5: Update the global maximum data ID:

[0017] After each data pull and review, the quality inspector updates the global maximum data ID to record the starting position of the next data pull.

[0018] Preferably, in step three, the data fragment in the audit log record includes the auditor, the start ID, the end ID, the data volume and the operation time, which are used to identify the data scope of this round of audit.

[0019] Preferably, in the step 1, the progress of data review is recorded by the global maximum data ID during data pulling, and this maximum data ID is used as a benchmark each time data is pulled.

[0020] Preferably, the optimization method reduces the amount of log table data by a segmented data storage method at a ratio that is positively correlated with the fixed number of data items pulled in step one.

[0021] Preferably, the optimization system comprises:

[0022] It consists of a data pulling module, a data change recording module, an audit log recording module, a quality inspection module, and a global maximum data ID update module.

[0023] Preferably, the data pulling module also includes a configuration option for setting a fixed number N.

[0024] Preferably, the data change information recorded by the data change recording module includes but is not limited to the changed content of the data entry, the change time, and the customer service identifier of the change operation.

[0025] Preferably, the audit log recording module also includes an additional function for compressing or encrypting stored data fragments to improve data security and storage efficiency.

[0026] Preferably, the quality inspection module also includes a function for generating a quality inspection report, and the quality inspection report includes but is not limited to quality inspection results, a list of problem data, and suggested improvement measures.

[0027] The beneficial effects of the present invention are as follows:

[0028] The present invention achieves a significant reduction in the amount of log table data through a carefully designed segmented data storage optimization scheme, with a specific reduction of about N times (N represents the number of data items pulled during each audit process). This innovative data storage strategy not only greatly reduces the redundancy of data, but also significantly slows down the growth rate of data, thereby ensuring that the query efficiency of the audit log can still be maintained at an extremely high level even after the system has been running for a long time.

[0029] In actual operation, the traditional one-to-one data recording method is abandoned, and the storage method based on "data fragments" is adopted. Each data fragment contains a start ID and an end ID to clearly identify the data range covered by this round of review. This storage method not only greatly reduces the number of records in the log table, but also makes data query and retrieval more efficient and convenient.

[0030] In addition, the segmented data storage solution also brings about a significant reduction in space usage. Since there is no need to create a separate record for each piece of data, the hard disk space occupied can be greatly reduced. This is undoubtedly a huge advantage for those scenarios where storage space is limited or costs need to be strictly controlled.

[0031] More importantly, the optimized data storage solution also significantly improves query efficiency. Since data is stored in the form of fragments, when querying, you only need to locate the required data range based on the start ID and end ID, without traversing the entire log table one by one. This not only greatly shortens the query time, but also improves the overall performance of the system and user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 It is a simplified diagram of the steps of the optimization method of the present invention;

[0033] Figure 2 A data graph with a fixed number of entries in the present invention;

[0034] Figure 3 The operation log table diagram of the present invention contains the change data;

[0035] Figure 4A storage structure diagram for the present invention;

[0036] Figure 5 This is a data segment diagram of the present invention. DETAILED DESCRIPTION

[0037] 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 only 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 are within the scope of protection of the present invention.

[0038] like Figures 1 to 5 As shown, an embodiment of the present invention provides an optimization method for data storage and query, and the specific steps of the optimization method are:

[0039] Step 1: Data extraction: The auditor extracts a fixed number of data (N) at a time for examination in order;

[0040] Step 2: Data change record: During the audit process, for data that has changed, its change information is stored in the operation log table;

[0041] The data changes recorded in the operation log table include the changed data (such as 3, 8, 9, 17, etc. in the example).

[0042] Step 3: Review log records:

[0043] After this round of review is completed, the data pulled this time will be stored in the audit log table in the form of "data fragments".

[0044] The data pulled this time is stored in the audit log table. This process does not store all data details in the table, but in the form of "data fragments", which can greatly reduce the amount of data written

[0045] Step 4: Quality inspection:

[0046] The quality inspector can view all data processed by the customer service (i.e. data that has been changed) through the operation log table. The quality inspector can also view all data segments processed by the customer service through the audit log table; using the start ID and end ID in the data segment, the quality inspector can further view the original data and complete the quality inspection work;

[0047] Step 5: Update the global maximum data ID:

[0048] After each data pull and review, the quality inspector updates the global maximum data ID to record the starting position of the next data pull.

[0049] Among them, in the step three, the data fragment in the audit log record includes the auditor, the start ID, the end ID, the data volume and the operation time, which are used to identify the data scope of this round of audit.

[0050] Among them, in the step 1, the progress of data review is recorded through the global maximum data ID during data pulling, and this maximum data ID is used as a benchmark each time data is pulled.

[0051] Among them, this optimization method uses segmented data storage to reduce the amount of log table data in a positively correlated multiple with the fixed number of data pulled in step one.

[0052] The optimization system comprises:

[0053] It consists of a data pulling module, a data change recording module, an audit log recording module, a quality inspection module, and a global maximum data ID update module.

[0054] The data pulling module further includes a configuration option for setting a fixed number N of entries.

[0055] The data change information recorded by the data change record module includes but is not limited to the changed content of the data entry, the change time, and the customer service identifier of the change operation.

[0056] The audit log recording module also includes an additional function for compressing or encrypting stored data fragments to improve data security and storage efficiency.

[0057] The quality inspection module also includes a function for generating a quality inspection report, which includes but is not limited to quality inspection results, a list of problem data, and suggested improvement measures.

[0058] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device.

[0059] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A method for optimizing data storage and query, characterized in that: The specific steps of this optimization method are: Step 1: Data extraction: Auditors extract a fixed number of data at a time in order for audit; Step 2: Data change record: During the audit process, for data that has changed, its change information is stored in the operation log table; Step 3: Review log records: After this round of review is completed, the data pulled this time will be stored in the audit log table in the form of "data fragments"; Step 4: Quality inspection: Quality inspectors can view all data processed by customer service through the operation log table; they can also view all data segments processed by customer service through the audit log table; using the start ID and end ID in the data segment, quality inspectors can further view the original data and complete quality inspection work; Step 5: Update the global maximum data ID: After each data pull and review, the quality inspector updates the global maximum data ID to record the starting position of the next data pull.

2. The method for optimizing data storage and query according to claim 1, characterized in that: In the step three, the data fragment in the audit log record includes the auditor, the start ID, the end ID, the data volume and the operation time, which are used to identify the data scope of this round of audit.

3. The method for optimizing data storage and query according to claim 1, characterized in that: In the step 1, the progress of data review is recorded through the global maximum data ID during data pulling, and this maximum data ID is used as a benchmark each time data is pulled.

4. The method for optimizing data storage and query according to claim 1, characterized in that: This optimization method uses segmented data storage to reduce the amount of log table data in a positively correlated ratio with the fixed number of data pulled in step one.

5. A data storage and query optimization system, characterized in that: The optimization system consists of: It consists of a data pulling module, a data change recording module, an audit log recording module, a quality inspection module, and a global maximum data ID update module.

6. The data storage and query optimization system according to claim 5, characterized in that: The data pulling module also includes a configuration option for setting a fixed number N of entries.

7. The data storage and query optimization system according to claim 5, characterized in that: The data change information recorded by the data change recording module includes but is not limited to the changed content of the data entry, the change time, and the customer service identifier of the change operation.

8. The data storage and query optimization system according to claim 5, characterized in that: The audit logging module also includes additional functionality for compressing or encrypting stored data segments to improve data security and storage efficiency.

9. The data storage and query optimization system according to claim 5, characterized in that: The quality inspection module also includes a function for generating a quality inspection report, which includes but is not limited to quality inspection results, a list of problem data, and suggested improvement measures.

Images