A distributed database recommended index verification method and device

Abstract

The application relates to the field of distributed databases in computer science, and particularly provides a distributed database recommended index verification method and device, which comprises a database node, a candidate index verification module and a data warehouse; the database node performs index verification analysis on a database system where an environment is located; the candidate index verification module finds relevant SQL statements according to indexes generated by an index recommendation module, calculates the benefits of a single index and the benefits of an index combination, verifies the effectiveness of the recommended indexes, and eliminates indexes that are useless to application load; and the data warehouse is used to collect and store running data in the database. Compared with the prior art, the application greatly reduces the calculation amount of index verification, and enhances the reliability of index verification, so as to avoid recommending indexes that are useless or even harmful to application load.

Description

Technical Field

[0001] This invention relates to the field of distributed databases in computer science, specifically providing a method and apparatus for verifying recommended indexes in distributed databases. Background Technology

[0002] Modern database applications are characterized by diverse and highly variable workloads. The complex workloads on distributed cluster databases make preventing or repairing problems particularly difficult. Different workloads have different requirements for system resource allocation, and failure to intervene and handle them promptly may lead to problems of varying degrees, such as system downtime. Therefore, after detecting unhealthy application loads, the system needs to provide further solutions.

[0003] Slow SQL queries that take too long to execute and consume excessive memory are a significant factor contributing to increased database pressure. Missing indexes and improperly created indexes are common reasons affecting the health of SQL statements. Therefore, index optimization is a crucial part of database optimization; a well-designed indexing scheme can improve the overall query efficiency of an instance. However, index optimization is an NP-hard problem. Besides using various algorithms to generate recommended indexes, a highly reliable verification process is necessary to ensure that the recommended indexes benefit the application load and prevent redundant or erroneous indexes from harming system performance.

[0004] There are various recommended index verification solutions available on the market. In terms of the verification environment, one approach involves creating, deleting, and verifying the recommended index directly on the monitored cluster. This method may impact the performance of the monitored cluster. Another approach is to clone a cluster identical to the monitored cluster, isolate it from the monitored cluster, and perform index creation, deletion, and verification on the cloned cluster. This approach does not affect the normal operation of the cluster but introduces additional hardware requirements. From a verification method perspective, the simplest method uses simple verification rules: if the application load after creating the recommended index is less than before its creation, the index is considered a beneficial index. This method has low reliability and does not consider the mutual influence between different indexes, potentially leading to redundant indexes.

[0005] Furthermore, some algorithms use machine learning models when recommending indexes. This approach often does not use verification methods and directly applies the results recommended by the model, making its credibility difficult to assess and its interpretability poor. Another relatively complete verification method recommends only one candidate index generated from the same statement. The advantage of this approach is that it avoids redundancy as much as possible, but the disadvantage is that it may miss some indexes that are more beneficial to the overall result. Then, using these indexes that win in each statement, multiple sets of recommended indexes are generated by random replacement when calculating the overall benefit, and the overall benefit is calculated separately. Although this approach considers optimizing the index combination as much as possible, it brings greater computational overhead, and the random algorithm cannot guarantee finding the optimal solution.

[0006] Therefore, we need to build a lightweight, interpretable, highly reliable index verification module with minimal impact on the system, which can assist users in making more accurate index verification judgments while being able to make relatively accurate predictions and saving resources. Summary of the Invention

[0007] This invention addresses the shortcomings of the prior art by providing a highly practical method for verifying recommended indexes in distributed databases.

[0008] A further technical objective of this invention is to provide a reasonably designed, safe, and applicable distributed database recommendation index verification device.

[0009] The technical solution adopted by this invention to solve its technical problem is:

[0010] A method for verifying recommended indexes in a distributed database includes database nodes, a candidate index verification module, and a data warehouse.

[0011] The database node performs index verification and analysis on the database system of the environment in which it resides.

[0012] The candidate index verification module searches for relevant SQL statements based on the indexes generated by the index recommendation module, calculates the benefit of a single index and the benefit of index combinations, verifies the effectiveness of the recommended indexes, and eliminates indexes that are not beneficial to the application load.

[0013] The data warehouse is used to collect and store operational data from the database.

[0014] Furthermore, the database node stably runs the index recommendation service; on the other hand, it connects to the monitored cluster where the application resides to obtain all data objects referenced by SQL statements in the application load and their associated other data objects.

[0015] Furthermore, in the candidate index verification module, the following steps are specifically performed:

[0016] S1. Locate relevant SQL statements in the application load;

[0017] S2. Calculate the change in revenue for each relevant statement;

[0018] S3. Verify the validity of a single index entry;

[0019] S4. Verify the validity of the index set;

[0020] S5. Clean up invalid indexes.

[0021] Furthermore, in step S1, starting from the data relationship structure collected from the data warehouse, all statements related to the table are searched according to the table where the index is located.

[0022] Furthermore, in step S2, based on the statement found in step S1, we first check whether it has been calculated in the recommendation index module by checking the statement's status flag. If the overhead change of the logical plan has been calculated, there is no need to calculate it again.

[0023] If no tags have been calculated, the overhead of the logical plan is recalculated and the overhead of not creating the index is subtracted to obtain the benefit of the index for this statement;

[0024] The revenue calculation uses the overhead of the logical plan. The overhead only needs to be calculated and recorded once for each statement. This is used to calculate the revenue change of each index and the revenue change of the entire candidate index set.

[0025] Furthermore, in step S3, while calculating the change in statement revenue, the candidate index verification module also checks whether the newly added candidate index in the new logical plan can be used by the statement. If the index cannot be used, it is directly marked as a candidate index to be eliminated.

[0026] If it can be used, then based on the cost changes of all related statements and the statement importance weights, check whether the percentage change in revenue exceeds the user-set threshold. If it does not exceed the threshold, then mark it as a candidate index to be eliminated; otherwise, continue the verification process.

[0027] Furthermore, in step S4, after obtaining valid candidate indexes from step S3, the candidate index verification module will sort them in reverse order according to the percentage change in revenue of each index, and select the top K as the candidate index set according to the upper limit of the number of recommended indexes specified by the user.

[0028] After obtaining the set, based on the cost change of each statement calculated previously, the cumulative benefit change of all related SQL statements in the set is calculated to obtain the benefit change percentage of the overall candidate index set. If the benefit change percentage exceeds the optimization threshold set by the user, the index set can be regarded as a valid recommended index set.

[0029] Conversely, if the conditions are not met, the cleanup module will be executed, preparing to end the index recommendation process.

[0030] Furthermore, in step S5, indexes that do not meet the profit target will be marked as invalid indexes and will be deleted in batches after all calculations are completed. For the overall set of indexes that meet the profit target, the candidate index verification module will delete the indexes marked as invalid in batches from memory and each table.

[0031] For index sets that fail to meet overall performance targets, the candidate index verification module will batch delete all recommended indexes from memory and each table.

[0032] A distributed database recommendation index verification device includes: at least one memory and at least one processor;

[0033] The at least one memory is used to store a machine-readable program;

[0034] The at least one processor is configured to invoke the machine-readable program to execute a distributed database recommendation index verification method.

[0035] Compared with the prior art, the distributed database recommendation index verification method and apparatus of the present invention have the following outstanding advantages:

[0036] This invention obtains recommended indexes from the index recommendation module, acquires the SQL statements related to the indexes through the association between the indexes and statements, obtains the logical plan overhead of these statements in a simulated environment, and performs a three-step verification using the CIV index verification algorithm to check the validity of these recommended indexes. This significantly reduces the computational load of index verification and enhances the reliability of index verification, thereby avoiding the recommendation of indexes that are not beneficial or even harmful to the application load.

[0037] When calculating the cost of SQL statements, CIV's algorithm searches for all relevant statements based on the relationship between the recommended index table and the SQL statement, and calculates the benefit using the cost of the logical plan (ignoring statements already calculated in the index recommendation module to avoid redundant calculations and save computational overhead). It also considers the negative impact of transactional statements. This approach avoids irrelevant...

[0038] Obtain other attached diagrams.

[0039] Appendix Figure 1 This is a flowchart illustrating a distributed database recommendation index verification method. Detailed Implementation

[0040] To enable those skilled in the art to better understand the present invention, the present invention will be further described in detail below with reference to specific embodiments. Obviously, the described embodiments are merely some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0041] The following is a preferred embodiment:

[0042] like Figure 1 As shown in this embodiment, a distributed database recommendation index verification method includes a database node, a candidate index verification module, and a data warehouse.

[0043] The database node performs index verification analysis on the database system of the environment it uses. On the one hand, it stably runs the services required for index recommendation, such as application load profiling, index recommendation, and index verification. On the other hand, it connects to the monitored cluster where the application is located to obtain all the data objects referenced by the SQL statements contained in the application load and other related data objects.

[0044] The candidate index verification module searches for relevant SQL statements based on the indexes generated by the index recommendation module, calculates the benefit of a single index and the benefit of index combinations, verifies the effectiveness of the recommended indexes, and eliminates indexes that are not beneficial to the application load.

[0045] It has the following steps:

[0046] S1. Locate relevant SQL statements in the application load;

[0047] Starting from the data relationship structure collected from the data warehouse, all statements related to the indexed table are searched to avoid calculating statements unrelated to the newly added index and reduce computational overhead.

[0048] S2. Calculate the change in revenue for each relevant statement;

[0049] Based on the statement found in step S1, first check whether it has been calculated in the recommended index module by checking the statement's status flag. If the change in the logical plan's cost has been calculated, there is no need to calculate it again to save computational costs. If there is no flag indicating that it has been calculated, then recalculate the cost of its logical plan and subtract the cost when the index was not created to obtain the benefit of the index on this statement.

[0050] The revenue calculation uses the overhead of the logical plan to avoid errors caused by application load fluctuations. The overhead only needs to be calculated and recorded once for each statement, and is used to subsequently calculate the revenue changes for each index and the overall candidate index set.

[0051] S3. Verify the validity of a single index.

[0052] While calculating the change in statement revenue, the candidate index verification module also checks whether the newly added candidate index in the new logical plan can be used by the statement. If the index cannot be used, it is directly marked as a candidate index to be eliminated. If it can be used, it checks whether the percentage change in revenue exceeds the user-set threshold based on the cost changes of all related statements and the statement importance weight.

[0053] If the threshold is not exceeded, it is marked as a candidate index to be eliminated; otherwise, the verification continues.

[0054] S4. Verify the validity of the index set.

[0055] After obtaining valid candidate indices from step S3, the candidate index verification module will sort them in reverse order based on the percentage change in revenue for each index, and select the top K as the candidate index set according to the user-specified upper limit for the number of recommended indices.

[0056] After obtaining the set, based on the cost change of each statement calculated previously (combined with importance weight), the cumulative benefit change of all related SQL statements in the set is calculated (each SQL statement is counted only once), and the percentage of benefit change of the overall candidate index set is obtained. If this percentage exceeds the optimization threshold set by the user, the index set can be regarded as a valid recommended index set; otherwise, the cleanup module is executed to prepare to end the index recommendation process and avoid generating redundant or incorrect indexes.

[0057] S5. Clean up invalid indexes.

[0058] During the verification process, indexes that fail to meet the performance targets will be marked as invalid indexes and will be deleted in batches after all calculations are completed. For the set of indexes that meet the overall performance targets, the candidate index verification module will delete the marked invalid indexes in batches from memory and each table; for the set of indexes that fail to meet the overall performance targets, the candidate index verification module will delete all recommended indexes in batches from memory and each table.

[0059] Finally, the data warehouse is used to collect and store operational data from the database. It receives and stores all data objects referenced by SQL statements within the application workload, as well as other related data objects. It also collects and stores other data used for subsequent index analysis, including application workload information, monitoring metrics, database logs, etc., providing support for index analysis and verification.

[0060] Verifying index performance in a highly isolated simulation environment effectively avoids negative impacts on the monitored cluster.

[0061] Based on the candidate indexes recommended by the index recommendation module, tables that may be affected are identified, and then the SQL statements related to those tables are located, with the changes in benefits calculated one by one. During the verification process, each statement only needs to be calculated once, and statements unrelated to the recommended indexes do not need to be calculated, thus reducing the amount of computation.

[0062] When calculating the changes in the benefits of SQL statements, the overhead of the logical plan is used instead of the execution time to avoid errors caused by fluctuations in system load. In addition, the overhead added by insert, delete, and update statements is deducted during the calculation. The different impacts of indexes on analytical and transactional statements are comprehensively considered to achieve more accurate benefit calculation.

[0063] When verifying benefits, a three-step verification process is used. First, it is confirmed that the recommended indexes can be used by the statements. The benefit of a single recommended index must exceed a threshold percentage (users can adjust the optimization intensity) to be counted as a valid index. Redundant indexes are eliminated. Then, the overall benefit of the index set must be verified in combination with the importance of the statements to ensure that it exceeds the threshold percentage. Finally, an index set that is beneficial to the overall application load is obtained.

[0064] Based on the above method, a distributed database recommendation index verification device in this embodiment includes: at least one memory and at least one processor;

[0065] The at least one memory is used to store a machine-readable program;

[0066] The at least one processor is configured to invoke the machine-readable program to execute a distributed database recommendation index verification method.

[0067] The specific embodiments described above are merely specific examples of the present invention. The patent protection scope of the present invention includes, but is not limited to, the specific embodiments described above. Any appropriate changes or substitutions made by a person skilled in the art that conform to the claims of the distributed database recommendation index verification method and apparatus of the present invention should fall within the patent protection scope of the present invention.

[0068] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for validating a recommended index in a distributed database, characterized in that, This includes database nodes, a candidate index verification module, and a data warehouse; The database node performs index verification and analysis on the database system of the environment in which it resides. The candidate index verification module searches for relevant SQL statements based on the indexes generated by the index recommendation module, calculates the benefit of a single index and the benefit of index combinations, verifies the effectiveness of the recommended indexes, and eliminates indexes that are not beneficial to the application load. The data warehouse is used to collect and store operational data from the database; The database node, on the one hand, stably runs the index recommendation service; on the other hand, it connects to the monitored cluster where the application resides to obtain all data objects referenced by the SQL statements contained in the application load and other related data objects. In the candidate index verification module, the following steps are specifically performed: S1. Locate relevant SQL statements in the application load; Starting from the data relationship structure collected from the data warehouse, find all statements related to the table based on the table where the index is located; S2. Calculate the change in revenue for each relevant statement; Based on the statement found in step S1, first check whether it has been calculated in the recommended index module by checking the statement's status flag. If the overhead change of the logical plan has been calculated, there is no need to calculate it again. If no tags have been calculated, the overhead of the logical plan is recalculated and the overhead of not creating the index is subtracted to obtain the benefit of the index for this statement; The revenue calculation uses the overhead of the logical plan. The overhead only needs to be calculated and recorded once for each statement. This is used to calculate the revenue change of each index and the revenue change of the entire candidate index set in subsequent calculations. S3. Verify the validity of a single index entry; While calculating the changes in statement revenue, the candidate index verification module also checks whether the newly added candidate indexes in the new logical plan can be used by the statement. If the index cannot be used, it is directly marked as a candidate index to be eliminated. If it can be used, then based on the cost changes of all related statements and the statement importance weight, check whether the percentage change in revenue exceeds the user-set threshold. If it does not exceed the threshold, then mark it as a candidate index to be eliminated; otherwise, continue to verify. S4. Verify the validity of the index set; After obtaining valid candidate indices from step S3, the candidate index verification module will sort them in reverse order according to the percentage change in revenue of each index, and select the top K as the candidate index set according to the upper limit of the number of recommended indices specified by the user. After obtaining the set, based on the cost change of each statement calculated previously, the cumulative benefit change of all related SQL statements in the set is calculated to obtain the benefit change percentage of the overall candidate index set. If the benefit change percentage exceeds the optimization threshold set by the user, the index set can be regarded as a valid recommended index set. Conversely, the cleanup module will be executed to prepare for the end of the index recommendation process; S5. Clean up invalid indexes.

2. The distributed database recommendation index verification method according to claim 1, characterized in that, In step S5, indexes that do not meet the profit target will be marked as invalid indexes and will be deleted in batches after all calculations are completed. For the set of indexes that meet the overall profit target, the candidate index verification module will delete the indexes marked as invalid in batches from memory and each table. For index sets that fail to meet overall performance targets, the candidate index verification module will batch delete all recommended indexes from memory and each table.

3. A distributed database recommendation index verification device, characterized in that, include: At least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to invoke the machine-readable program to execute the method according to any one of claims 1 to 2.

Images