Distributed database index recommendation monitoring system and method

Abstract

The application discloses a distributed database index recommendation monitoring system and method, belongs to the field of distributed databases, and aims to solve the technical problem of how to perform real-time feedback on analysis recommendation functions, establish a closed loop index, and guarantee the health and efficiency of database indexes. The technical scheme adopted is as follows: the structure comprises an index analysis recommendation module, an index suggestion modification application module, an index usage monitoring module, an index usage analysis module, an index report generation module, and a self-adaptive index suggestion optimization module; the index analysis recommendation module is used to start from unhealthy statements, check related tables, access patterns, predicate categories and related fields, and propose candidate indexes based on each SQL statement according to a candidate index generation algorithm; after the generation of the candidate indexes that need to be created, the effectiveness of the candidate indexes is verified through two steps; the two steps specifically refer to verifying the benefits of the indexes on a single SQL statement and verifying the benefits of the indexes on all affected statements under the overall application load.

Description

Technical Field

[0001] This invention relates to the field of distributed databases, and more specifically to a distributed database index recommendation and monitoring system and method. Background Technology

[0002] In existing technologies, some databases, such as Capital One, use automatic index management for database index monitoring. Their index management system also includes index monitoring, but their main focus is on tracking and analyzing query logs. By analyzing the tables, columns, join methods, predicate matching, and physical execution information accessed by the application load query statements, they determine whether there is room for optimization in the access methods. The selection of monitoring metrics still focuses on application load, lacking specificity for newly recommended indexes. Some database systems use reinforcement learning for index recommendation, integrating index performance monitoring into the training process as feedback. However, this lacks a clear view of database index usage for users, and neither of these methods allows user intervention, preventing them from manually influencing the recommended index information and posing a certain risk to application load. Other databases, such as Oracle, use index monitoring systems that track index usage frequency, which statements use indexes, and index status (e.g., WATCH, KEEP, NEW, DROPPED, NEVER USED) to help determine more ideal indexing methods, locate statements performing full table scans, study statement execution behavior, and remove unused or relatively useless indexes. This method ignores the relationship between indexes and SQL statements, thus failing to focus on SQL statements with high weight in application load and examine the benefits of indexes.

[0003] In the application load index recommendation function, after users create indexes based on the index recommendation suggestions, they also need to know the actual usage of the indexes in order to track their effects and make timely adjustments. Therefore, how to provide real-time feedback to the analysis and recommendation function, create closed-loop indexes, and ensure the health and efficiency of database indexes is a technical problem that urgently needs to be solved. Summary of the Invention

[0004] The technical objective of this invention is to provide a distributed database index recommendation monitoring system and method to address the problem of how to provide real-time feedback on the analysis and recommendation functions, establish closed-loop indexes, and ensure the health and efficiency of the database index.

[0005] The technical objective of this invention is achieved as follows: a distributed database index recommendation and monitoring system, which includes an index analysis and recommendation module, an index suggestion modification application module, an index usage monitoring module, an index usage analysis module, an index report generation module, and an adaptive index suggestion optimization module.

[0006] The index analysis and recommendation module is used to start from unhealthy statements, check related tables, access patterns, predicate types and related fields, and propose candidate indexes based on each SQL statement according to the candidate index generation algorithm. After generating the candidate indexes to be created, their effectiveness will be verified in two steps. Specifically, the two steps of verification refer to verifying the benefit of the index on a single SQL statement and verifying the benefit of the index on all affected statements in the overall application load.

[0007] The index is recommended to be modified in the application module to modify the recommended index according to your own needs and apply the index; adjustments can be made later based on the index usage report, such as disabling the index;

[0008] The index usage monitoring module is used to obtain existing index information and usage status from the monitored cluster when Index Advisor starts. Indexes generated based on Index Advisor's recommendations are also tracked. Existing indexes and recommended indexes are displayed separately. Users can view the usage status of all indexes (mainly those generated by Index Advisor) on the monitoring UI and intuitively understand the recommendation effect of Index Advisor through specific metrics.

[0009] The index usage analysis module is used to detect whether there are any indexes in the monitored cluster that users may want to know which indexes have not been used for a long time, which indexes have failed to achieve the expected results or even have a negative impact after creation, and which indexes may seriously affect performance and need to be dealt with in a timely manner. It helps users to locate the indexes that need attention or treatment more quickly and accurately, and sends alerts to users when necessary, and suggests that users resolve the performance problems caused by the indexes in a timely manner.

[0010] The index report generation module sorts the index usage report page in descending order according to the percentage change in total revenue for each index, and allows users to choose the viewing method on the interface;

[0011] The adaptive index suggestion optimization module is used to adaptively adjust relevant analysis parameters based on index usage and its impact on the load, generating better candidate indexes. Parameters include the single SQL benefit ratio threshold, the overall load benefit ratio threshold, the index retention time, and the maximum number of indexes per table.

[0012] As a preferred approach, the front-end will use the data obtained from the index usage monitoring module to display the index ID, name, table name, field order, creation time, last usage time, number of times used, and cumulative revenue of the suggested index and the self-built index on the monitoring interface. For the suggested index, there will also be a revenue detail, which includes a list of each related SQL statement, also sorted in descending order of revenue.

[0013] The index usage monitoring module allows users to visually observe the index usage and also marks existing indexes on the UI interface that they suggest users modify or delete, making it easier for users to determine whether to perform deletion operations based on the actual effect of the indexes.

[0014] More preferably, the index usage monitoring module obtains the usage information of all indexes from the autonomous data warehouse, and monitors the recommended indexes and self-built indexes that are applied;

[0015] For recommended indexes generated by Index Advisor, the creation time of the index is taken, and the execution time of the last SQL statement that used the index before the creation time is found. The difference in execution time before and after creation is the benefit of the index in this execution (calculated in combination with the importance weight of the statement). The total benefit of the index and the cumulative benefit of each related SQL statement within the analysis period configured by Index Advisor are calculated in descending order. At the same time, information on the usage of other indexes in Index Usage is collected. The information on the usage of other indexes in Index Usage includes the number of times they are used and the last time they are used. Finally, the results are displayed on the index monitoring UI interface in descending order of the total index benefit.

[0016] For self-built indexes, the index usage information is displayed in descending order based on index importance.

[0017] Recommended indexes and self-built indexes are displayed in list form on the monitoring UI.

[0018] More specifically, the benefits of indexing are as follows:

[0019] When calculating index benefits, the last analysis before the index creation time is used as the raw time calculation data. For SQL statements appearing on the table where the index resides, the average execution time is calculated using the total execution time, total CPU consumption, and total number of executions corresponding to that SQL statement, as shown in the following formula:

[0020]

[0021]

[0022] Where i1 represents the index; s1 represents the SQL statement; and elapsedtime... org The original execution time of the SQL statement is given by: totallapsed (total execution time), totalexec (total execution count), and cpudtime. org The original CPU consumption of the SQL statement is represented by `totalcpu`, which represents the total CPU time consumed.

[0023] To calculate the average time elapsed after index creation, iterate through the SQL statements related to the table containing the index, and use the corresponding total execution time, total CPU consumption, and total number of executions to calculate the average elapsed time. new and time new ;

[0024] The rate of change in the index's benefit on a single statement is the difference between the average execution time of that statement in the last analysis period before its creation and the average execution time of that statement in the current analysis period, divided by the average execution time of that statement in the last analysis period before its creation, as shown in the following formula:

[0025]

[0026]

[0027] The total benefit of index i1 is the sum of the products of the average benefit of all n SQL statements related to i1 (i.e., all SQL statements on the table containing index i1) and the statement importance weights in this analysis, as shown in the following formula:

[0028]

[0029]

[0030] Wherein, weight(sj0 = number of executions * average execution time or cumulative execution time or CPU time).

[0031] More preferably, the index usage analysis module is used to analyze the index usage table, find all existing indexes, determine their benefit status, identify indexes that do not significantly improve benefits and indexes that have a significant negative impact on performance, and promptly handle poor-performing indexes. The front-end UI will send a message to prompt the user to pay attention to the index.

[0032] More preferably, the index usage analysis module generates an actual usage report based on the usage frequency of each index, the statements that use the index and their corresponding revenue, total revenue, creation time, and last usage time. The actual usage report includes the basic information of the index and the actual revenue status.

[0033] Preferably, the index report generation module is used to view the index status of each table or directly view the index by default. Each record in the index report generation module displays the usage of the index, including the index number, table name, creation time, last usage time, cumulative usage count, number of related SQL statements, percentage change in total revenue, revenue of related SQL statements (execution time, CPU time), and weight.

[0034] Even better, for each index-related statement, the overall percentage of benefit, a pie chart showing the percentage change in SQL statement benefit, changes in space usage, the number of affected SQL statements, and the recommended index type will be generated;

[0035] The relevant SQL statements are displayed in a list, showing the content of each statement, the rate of change in overhead, the number of executions, the average execution time, and the average CPU time.

[0036] A method for monitoring distributed database index recommendations is described below:

[0037] Starting with unhealthy SQL statements, we examine the relevant tables, access patterns, predicate types, and related fields. Based on the candidate index generation algorithm, we propose candidate indexes for each SQL statement. After generating the candidate indexes to be created, we verify their effectiveness in two steps. Specifically, the two steps of verification refer to verifying the benefit of the index on a single SQL statement and verifying the benefit of the index on all affected statements in the overall application load.

[0038] Modify the recommended index according to your own needs and apply the index;

[0039] When Index Advisor starts, it retrieves existing index information and usage from the monitored cluster. Indexes generated based on Index Advisor's recommendations are also tracked. Existing indexes and recommended indexes are displayed separately. Users can view the usage of all indexes on the monitoring UI and intuitively understand the effectiveness of Index Advisor's recommendations through specific metrics.

[0040] The system detects which indexes in the monitored cluster are unused for a long time, which indexes have failed to achieve the expected results or even had a negative impact after creation, and which indexes may seriously affect performance and need to be dealt with in a timely manner. This helps users to locate the indexes that need attention or processing more quickly and accurately, and sends alerts to users when necessary, suggesting that users resolve the performance problems caused by the indexes in a timely manner.

[0041] The index usage report page is sorted in descending order by the percentage change in total revenue for each index, and users can choose how to view it on the interface;

[0042] The system adaptively adjusts relevant analysis parameters based on index usage and its impact on load to generate better candidate indexes. These parameters include the single SQL benefit ratio threshold, the overall load benefit ratio threshold, the index retention time, and the maximum number of indexes per table.

[0043] As a preferred approach, the front-end will display the index ID, name, table name, field order, creation time, last usage time, number of uses, and cumulative revenue of the suggested index and the self-built index on the monitoring interface, based on the existing index information and usage status. For the suggested index, there will also be a revenue detail, which includes a list of each related SQL statement, also sorted in descending order of revenue.

[0044] The UI interface displays existing indexes that users are advised to modify or delete, making it easier for users to determine whether to perform the deletion operation based on the actual effect of the index.

[0045] Obtain usage information for all indexes from the autonomous data warehouse, and monitor the applied recommended indexes and self-built indexes;

[0046] Specifically, for recommended indexes generated by Index Advisor, the creation time of the index is taken, and the execution time of the last SQL statement that used the index before the creation time is found. The difference in execution time before and after creation is the benefit of the index in this execution. The total benefit of the index and the cumulative benefit of each related SQL statement within the analysis period configured by Index Advisor are calculated and sorted in descending order. At the same time, information on the usage of other indexes in Index Usage is collected, including the number of times it is used and the last time it is used. Finally, the results are displayed on the index monitoring UI interface in descending order of the total index benefit.

[0047] For self-built indexes, the index usage information is displayed in descending order based on index importance.

[0048] Recommended indexes and self-built indexes are displayed in list form on the monitoring UI.

[0049] The specific benefits of indexing are as follows:

[0050] When calculating index benefits, the last analysis before the index creation time is used as the raw time calculation data. For SQL statements appearing on the table where the index resides, the average execution time is calculated using the total execution time, total CPU consumption, and total number of executions corresponding to that SQL statement, as shown in the following formula:

[0051]

[0052]

[0053] Where i1 represents the index; s1 represents the SQL statement; and elapsedtime... orgThe original execution time of the SQL statement is given by: totallapsed (total execution time), totalexec (total execution count), and cpudtime. org The original CPU consumption of the SQL statement is represented by `totalcpu`, which represents the total CPU time consumed.

[0054] To calculate the average time elapsed after index creation, iterate through the SQL statements related to the table containing the index, and use the corresponding total execution time, total CPU consumption, and total number of executions to calculate the average elapsed time. new and time new ;

[0055] The rate of change in the index's benefit on a single statement is the difference between the average execution time of that statement in the last analysis period before its creation and the average execution time of that statement in the current analysis period, divided by the average execution time of that statement in the last analysis period before its creation, as shown in the following formula:

[0056]

[0057]

[0058] The total benefit of index i1 is the sum of the products of the average benefit of all n SQL statements related to i1 and the statement importance weights in this analysis, as shown in the following formula:

[0059]

[0060]

[0061] Where weight(sj) = number of executions * average execution time or cumulative execution time or CPU time.

[0062] The distributed database index recommendation monitoring system and method of the present invention have the following advantages:

[0063] (i) This invention extracts characteristic indicators of application load related to index usage from distributed databases. Through data analysis, these data are used to generate index monitoring feedback to evaluate the performance of the index and its impact on the load. The feedback is then fed back to the index analysis module to optimize the analysis and generation process. Compared with traditional monitoring methods, this invention provides more accurate application monitoring of recommended indexes and analyzes them in conjunction with relevant SQL statements and their weight in the application load. The conclusions are intuitive, objective and reasonable.

[0064] (II) The effectiveness of the index in this invention is about 50% higher than that of traditional monitoring methods. By analyzing application statements, it effectively reduces the possibility of potential index problems in the database, helps database administrators to discover indexes that have not been used for a long time and have poor performance, and stop the damage in time. At the same time, it reduces the requirements for database administrators and has met the requirements for production applications. This invention is an innovation in distributed database load monitoring, which can reduce the labor costs of enterprises and improve work efficiency.

[0065] (III) This invention utilizes application-oriented multi-dimensional index usage indicators to establish an index monitoring closed loop through data analysis, ensuring the health and efficiency of database indexes. On the one hand, the conclusions of this invention are more accurate and efficient than traditional index monitoring methods, and more flexible and controllable than emerging automatic index management, with lower risks. On the other hand, this invention reduces the knowledge and ability requirements for database administrators, greatly simplifies the monitoring difficulty for database administrators and users, and improves the work efficiency of personnel while reducing labor costs.

[0066] (iv) After the indexing suggestions of this invention are adopted, the index usage monitoring module will continuously track and monitor the actual usage of each index and record its impact on the overall application load. Users can click on the monitoring interface to view the usage of all indexes. The original indexes and the indexes generated according to the Index Advisor suggestions will be displayed to users separately and sorted in descending order of benefit. In addition, the index usage monitoring module will also send inefficient index alerts to users, prompting them to deal with indexes that have not been used for a long time or are detrimental to the application load in a timely manner, so as to facilitate users to deal with and stop losses in a timely manner.

[0067] (v) The impact of this invention on the overall application load and query statements will be fed back to the index usage analysis module. The feedback mechanism will automatically adjust the analysis parameters through positive and negative feedback. Attached Figure Description

[0068] The invention will be further described below with reference to the accompanying drawings.

[0069] Appendix Figure 1 A schematic diagram of the structure of a recommended monitoring system for distributed database indexes. Detailed Implementation

[0070] The distributed database index recommendation monitoring system and method of the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments.

[0071] Example 1:

[0072] As attached Figure 1As shown in the figure, this embodiment provides a distributed database index recommendation and monitoring system. The system includes an index analysis and recommendation module, an index suggestion modification application module, an index usage monitoring module, an index usage analysis module, an index report generation module, and an adaptive index suggestion optimization module.

[0073] The index analysis and recommendation module is used to start from unhealthy statements, check related tables, access patterns, predicate types and related fields, and propose candidate indexes based on each SQL statement according to the candidate index generation algorithm. After generating the candidate indexes to be created, their effectiveness will be verified in two steps. Specifically, the two steps of verification refer to verifying the benefit of the index on a single SQL statement and verifying the benefit of the index on all affected statements in the overall application load.

[0074] The index is recommended to be modified in the application module to modify the recommended index according to your own needs and apply the index; adjustments can be made later based on the index usage report, such as disabling the index;

[0075] The index usage monitoring module is used to obtain existing index information and usage status from the monitored cluster when Index Advisor starts. Indexes generated based on Index Advisor's recommendations are also tracked. Existing indexes and recommended indexes are displayed separately. Users can view the usage status of all indexes (mainly those generated by Index Advisor) on the monitoring UI and intuitively understand the recommendation effect of Index Advisor through specific metrics.

[0076] The index usage analysis module is used to detect whether there are any indexes in the monitored cluster that users may want to know which indexes have not been used for a long time, which indexes have failed to achieve the expected results or even have a negative impact after creation, and which indexes may seriously affect performance and need to be dealt with in a timely manner. It helps users to locate the indexes that need attention or treatment more quickly and accurately, and sends alerts to users when necessary, and suggests that users resolve the performance problems caused by the indexes in a timely manner.

[0077] The index report generation module sorts the index usage report page in descending order according to the percentage change in total revenue for each index, and allows users to choose the viewing method on the interface;

[0078] The adaptive index suggestion optimization module is used to adaptively adjust relevant analysis parameters based on index usage and its impact on the load, generating better candidate indexes. Parameters include the single SQL benefit ratio threshold, the overall load benefit ratio threshold, the index retention time, and the maximum number of indexes per table.

[0079] In this embodiment, the front end will use the data obtained from the index usage monitoring module to display the index ID, name, table name, field order, creation time, last usage time, number of times used, and cumulative revenue of the suggested index and the self-built index on the monitoring interface. For the suggested index, there will also be a revenue detail, which includes a list of each related SQL statement, also sorted in descending order of revenue.

[0080] The index usage monitoring module allows users to visually observe the index usage and also marks existing indexes on the UI interface that they suggest users modify or delete, making it easier for users to determine whether to perform deletion operations based on the actual effect of the indexes.

[0081] In this embodiment, the index usage monitoring module obtains the usage information of all indexes from the autonomous data warehouse. The index usage monitoring module monitors the recommended indexes and self-built indexes that are being applied.

[0082] For recommended indexes generated by Index Advisor, the creation time of the index is taken, and the execution time of the last SQL statement that used the index before the creation time is found. The difference in execution time before and after creation is the benefit of the index in this execution (calculated in combination with the importance weight of the statement). The total benefit of the index and the cumulative benefit of each related SQL statement within the analysis period configured by Index Advisor are calculated in descending order. At the same time, information on the usage of other indexes in Index Usage is collected. The information on the usage of other indexes in Index Usage includes the number of times they are used and the last time they are used. Finally, the results are displayed on the index monitoring UI interface in descending order of the total index benefit.

[0083] For self-built indexes, the index usage information is displayed in descending order based on index importance.

[0084] Recommended indexes and self-built indexes are displayed in list form on the monitoring UI.

[0085] In this embodiment, the index benefits are as follows:

[0086] When calculating index benefits, the last analysis before the index creation time is used as the raw time calculation data. For SQL statements appearing on the table where the index resides, the average execution time is calculated using the total execution time, total CPU consumption, and total number of executions corresponding to that SQL statement, as shown in the following formula:

[0087]

[0088]

[0089] Where i1 represents the index; s1 represents the SQL statement; and elapsedtime... org The original execution time of the SQL statement is given by: totallapsed (total execution time), totalexec (total execution count), and cpudtime. org The original CPU consumption of the SQL statement is represented by `totalcpu`, which represents the total CPU time consumed.

[0090] To calculate the average time elapsed after index creation, iterate through the SQL statements related to the table containing the index, and use the corresponding total execution time, total CPU consumption, and total number of executions to calculate the average elapsed time. new and time new ;

[0091] The rate of change in the index's benefit on a single statement is the difference between the average execution time of that statement in the last analysis period before its creation and the average execution time of that statement in the current analysis period, divided by the average execution time of that statement in the last analysis period before its creation, as shown in the following formula:

[0092]

[0093]

[0094] The total benefit of index i1 is the sum of the products of the average benefit of all n SQL statements related to i1 (i.e., all SQL statements on the table containing index i1) and the statement importance weights in this analysis, as shown in the following formula:

[0095]

[0096]

[0097] Where weight(sj) = number of executions * average execution time or cumulative execution time or CPU time.

[0098] In this embodiment, the index usage analysis module is used to analyze the index usage table, find all existing indexes, determine their benefit status, identify indexes that do not significantly improve benefits and indexes that have a significant negative impact on performance, promptly handle poor-performing indexes, and the front-end UI will send a message to prompt the user to pay attention to the index.

[0099] In this embodiment, the index usage analysis module generates an actual usage report based on the usage frequency of each index, the statements that use the index and their corresponding revenue, total revenue, creation time, and last usage time. The actual usage report includes the basic information of the index and the actual revenue status.

[0100] In this embodiment, the index report generation module is used to view the index status of each table or directly view the index by default. Each record in the index report generation module displays the usage of the index, including the index number, table name, creation time, last usage time, cumulative usage count, number of related SQL statements, percentage change in total revenue, revenue of related SQL statements (execution time, CPU time), and weight.

[0101] In this embodiment, for the benefit of each index-related statement, a pie chart of its overall benefit percentage, the percentage change in SQL statement benefit, the change in space occupied, the number of affected SQL statements, and the index recommendation type will be generated;

[0102] The relevant SQL statements are displayed in a list, showing the content of each statement, the rate of change in overhead, the number of executions, the average execution time, and the average CPU time.

[0103] Example 2:

[0104] This embodiment provides a method for monitoring distributed database index recommendations, as detailed below:

[0105] S1. Starting from unhealthy statements, examine the relevant tables, access patterns, predicate types, and related fields, and propose candidate indexes based on each SQL statement according to the candidate index generation algorithm; after generating the candidate indexes to be created, their effectiveness will be verified in two steps; the two-step verification specifically refers to verifying the benefit of the index on a single SQL statement and verifying the benefit of the index on all affected statements in the overall application load;

[0106] S2. Modify the recommended index according to your own needs and apply the index;

[0107] S3. When Index Advisor starts, it retrieves existing index information and usage from the monitored cluster. Indexes generated based on Index Advisor's recommendations are also tracked. Existing indexes and recommended indexes are displayed separately. Users can view the usage of all indexes on the monitoring UI and intuitively understand the recommendation effect of Index Advisor through specific metrics.

[0108] S4 detects whether the monitored cluster has any indexes that users may want to know which have not been used for a long time, which have failed to achieve the expected results or even have a negative impact after creation, and which indexes may seriously affect performance and need to be dealt with in a timely manner. This helps users to locate the indexes that need attention or processing more quickly and accurately, and sends alerts to users when necessary, suggesting that users resolve the performance problems caused by the indexes in a timely manner.

[0109] S5. Sort the index usage report page in descending order of the percentage change in total revenue for each index, and allow users to select the viewing method on the interface;

[0110] S6. Adaptively adjust relevant analysis parameters based on index usage and its impact on load to generate better candidate indexes; parameters include single SQL benefit ratio threshold, overall load benefit ratio threshold, index retention time, and maximum number of indexes per table.

[0111] In this embodiment, the front end will display the index ID, name, table name, field order, creation time, last usage time, number of times used, and cumulative revenue of the suggested index and the self-built index on the monitoring interface based on the existing index information and usage status. For the suggested index, there will also be a revenue detail, which includes a list of each related SQL statement, also sorted in descending order of revenue.

[0112] The UI interface displays existing indexes that users are advised to modify or delete, making it easier for users to determine whether to perform the deletion operation based on the actual effect of the index.

[0113] Obtain usage information for all indexes from the autonomous data warehouse, and monitor the applied recommended indexes and self-built indexes;

[0114] Specifically, for recommended indexes generated by Index Advisor, the creation time of the index is taken, and the execution time of the last SQL statement that used the index before the creation time is found. The difference in execution time before and after creation is the benefit of the index in this execution. The total benefit of the index and the cumulative benefit of each related SQL statement within the analysis period configured by Index Advisor are calculated and sorted in descending order. At the same time, information on the usage of other indexes in Index Usage is collected, including the number of times it is used and the last time it is used. Finally, the results are displayed on the index monitoring UI interface in descending order of the total index benefit.

[0115] For self-built indexes, the index usage information is displayed in descending order based on index importance.

[0116] Recommended indexes and self-built indexes are displayed in list form on the monitoring UI.

[0117] The specific benefits of indexing are as follows:

[0118] When calculating index benefits, the last analysis before the index creation time is used as the raw time calculation data. For SQL statements appearing on the table where the index resides, the average execution time is calculated using the total execution time, total CPU consumption, and total number of executions corresponding to that SQL statement, as shown in the following formula:

[0119]

[0120]

[0121] Where i1 represents the index; s1 represents the SQL statement; and elapsedtime... org The original execution time of the SQL statement is given by: totallapsed (total execution time), totalexec (total execution count), and cpudtime. org The original CPU consumption of the SQL statement is represented by `totalcpu`, which represents the total CPU time consumed.

[0122] To calculate the average time elapsed after index creation, iterate through the SQL statements related to the table containing the index, and use the corresponding total execution time, total CPU consumption, and total number of executions to calculate the average elapsed time. new and time new ;

[0123] The rate of change in the index's benefit on a single statement is the difference between the average execution time of that statement in the last analysis period before its creation and the average execution time of that statement in the current analysis period, divided by the average execution time of that statement in the last analysis period before its creation, as shown in the following formula:

[0124]

[0125]

[0126] The total benefit of index i1 is the sum of the products of the average benefit of all n SQL statements related to i1 and the statement importance weights in this analysis, as shown in the following formula:

[0127]

[0128]

[0129] Where weight(sj) = number of executions * average execution time or cumulative execution time or CPU time.

[0130] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A distributed database index recommendation monitoring system, characterized in that, The system includes an index analysis and recommendation module, an index suggestion modification and application module, an index usage monitoring module, an index usage analysis module, an index report generation module, and an adaptive index suggestion optimization module; The index analysis and recommendation module is used to start from unhealthy statements, check related tables, access patterns, predicate types and related fields, and propose candidate indexes based on each SQL statement according to the candidate index generation algorithm. After generating the candidate indexes to be created, their effectiveness will be verified in two steps. Specifically, the two steps of verification refer to verifying the benefit of the index on a single SQL statement and verifying the benefit of the index on all affected statements in the overall application load. The indexing suggestion allows you to modify the recommended index according to your own needs and apply the index. The index usage monitoring module is used to obtain existing index information and usage status from the monitored cluster when Index Advisor starts. Indexes generated based on Index Advisor's recommendations are also tracked. Existing indexes and recommended indexes are displayed separately. Users can view the usage status of all indexes on the monitoring UI and intuitively understand the recommendation effect of Index Advisor through specific metrics. The index usage analysis module is used to detect whether there are any indexes in the monitored cluster that users want to know which have not been used for a long time, which have failed to achieve the expected results or even had a negative impact after creation, and which have seriously affected performance and need to be dealt with. It helps users to locate the indexes that need attention or treatment more quickly and accurately, and sends alerts to users and suggests that users resolve the performance problems caused by the indexes. The index report generation module sorts the index usage report page in descending order according to the percentage change in total revenue for each index, and allows users to choose the viewing method on the interface; The adaptive index suggestion optimization module is used to adaptively adjust relevant analysis parameters based on index usage and its impact on the load, generating better candidate indexes. Parameters include the single SQL benefit ratio threshold, the overall load benefit ratio threshold, the index retention time, and the maximum number of indexes per table.

2. The distributed database index recommendation monitoring system of claim 1, wherein, The front end will use the data obtained from the index usage monitoring module to display the index ID, name, table name, field order, creation time, last usage time, number of times used, and cumulative revenue of the suggested index and the self-built index on the monitoring interface. For the suggested index, there will also be a revenue detail, which includes a list of each related SQL statement, also sorted in descending order of revenue. The index usage monitoring module marks existing indexes on the UI interface that users are advised to modify or delete, making it easier for users to determine whether to perform deletion operations based on the actual effect of the indexes.

3. The distributed database index recommendation monitoring system of claim 1 or 2, wherein, The index usage monitoring module obtains the usage information of all indexes from the autonomous data warehouse, and monitors the recommended indexes and self-built indexes that are being applied. Specifically, for recommended indexes generated by Index Advisor, the creation time of the index is taken, and the execution time of the last SQL statement that used the index before the creation time is found. The difference in execution time before and after creation is the benefit of the index in this execution. The total benefit of the index and the cumulative benefit of each related SQL statement within the analysis period configured by Index Advisor are calculated and sorted in descending order. At the same time, information on the usage of other indexes in Index Usage is collected, including the number of times it is used and the last time it is used. Finally, the results are displayed on the index monitoring UI interface in descending order of the total index benefit. For self-built indexes, the index usage information is displayed in descending order based on index importance. Recommended indexes and self-built indexes are displayed in list form on the monitoring UI.

4. The distributed database index recommendation monitoring system of claim 3, wherein, The specific benefits of indexing are as follows: When calculating index benefits, the last analysis before the index creation time is used as the raw time calculation data. For SQL statements appearing on the table where the index resides, the average execution time is calculated using the total execution time, total CPU consumption, and total number of executions corresponding to that SQL statement, as shown in the following formula: ； ； Wherein, i1 represents index; s1 represents SQL statement; Total execution time, Total execution time, Total execution time, Total CPU consumption, Total CPU consumption; The average time consumption after the index is created is calculated, the SQL statements related to the table where the index is located are traversed, the average time consumption is calculated using the corresponding total execution time, total CPU consumption and total execution times, and the average time consumption is obtained and ; The rate of change in the index's benefit on a single statement is the difference between the average execution time of that statement in the last analysis period before its creation and the average execution time of that statement in the current analysis period, divided by the average execution time of that statement in the last analysis period before its creation, as shown in the following formula: ； ； The total benefit of index i1 is the sum of the products of the average benefit of all n SQL statements related to i1 and the statement importance weights in this analysis, as shown in the following formula: ； ； in, =Number of executions * Average execution time OR Cumulative execution time OR CPU time.

5. The distributed database index recommendation monitoring system of claim 4, wherein, The index usage analysis module is used to analyze the index usage table, find all existing indexes, determine their benefit status, identify indexes that do not improve benefits and indexes that have a negative impact on performance, process poor-performing indexes, and the front-end UI will send a message to prompt the user to pay attention to the index.

6. The distributed database index recommendation monitoring system of claim 5, wherein, The index usage analysis module generates an actual usage report based on the usage frequency of each index, the statements that use the index and their corresponding revenue, total revenue, creation time, and last usage time. The actual usage report includes the basic information of the index and the actual revenue status.

7. The distributed database index recommendation monitoring system of claim 1, wherein, The index report generation module is used to view the index status of each table or directly view the index by default. Each record in the index report generation module displays the usage of the index, including the index number, table name, creation time, last usage time, cumulative usage count, number of related SQL statements, percentage change in total revenue, revenue status and weight of related SQL statements.

8. The distributed database index recommendation monitoring system of claim 7, wherein, For the benefit of each index-related statement, a pie chart of its overall benefit percentage, the percentage change in SQL statement benefit, the change in space occupied, the number of affected SQL statements, and the recommended index type will be generated; The relevant SQL statements are displayed in a list, showing the content of each statement, the rate of change in overhead, the number of executions, the average execution time, and the average CPU time. 9.A method for monitoring index recommendation in a distributed database, the method comprising: The method is as follows: Starting with unhealthy SQL statements, we examine the relevant tables, access patterns, predicate types, and related fields. Based on the candidate index generation algorithm, we propose candidate indexes for each SQL statement. After generating the candidate indexes to be created, we verify their effectiveness in two steps. Specifically, the two steps of verification refer to verifying the benefit of the index on a single SQL statement and verifying the benefit of the index on all affected statements in the overall application load. Modify the recommended index according to your own needs and apply the index; When Index Advisor starts, it retrieves existing index information and usage from the monitored cluster. Indexes generated based on Index Advisor's recommendations are also tracked. Existing indexes and recommended indexes are displayed separately. Users can view the usage of all indexes on the monitoring UI and intuitively understand the effectiveness of Index Advisor's recommendations through specific metrics. The system detects which indexes in the monitored cluster have been unused for a long time, which indexes have failed to achieve the expected results or even had a negative impact after creation, and which indexes are seriously affecting performance and need to be addressed. This helps users to locate the indexes that need attention or processing more quickly and accurately, and sends alerts to users, suggesting that they resolve the performance issues caused by the indexes. The index usage report page is sorted in descending order by the percentage change in total revenue for each index, and users can choose how to view it on the interface; The system adaptively adjusts relevant analysis parameters based on index usage and its impact on load to generate better candidate indexes. These parameters include the single SQL benefit ratio threshold, the overall load benefit ratio threshold, the index retention time, and the maximum number of indexes per table.

10. The method of claim 9, wherein, The front end will display the index ID, name, table name, field order, creation time, last usage time, number of times used, and cumulative revenue of the suggested index and the self-built index on the monitoring interface based on the existing index information and usage status. For the suggested index, there will also be a revenue detail, which includes a list of each related SQL statement, also sorted in descending order of revenue. The UI interface displays existing indexes that users are advised to modify or delete, making it easier for users to determine whether to perform the deletion operation based on the actual effect of the index. Obtain usage information for all indexes from the autonomous data warehouse, and monitor the applied recommended indexes and self-built indexes; Specifically, for recommended indexes generated by Index Advisor, the creation time of the index is taken, and the execution time of the last SQL statement that used the index before the creation time is found. The difference in execution time before and after creation is the benefit of the index in this execution. The total benefit of the index and the cumulative benefit of each related SQL statement within the analysis period configured by Index Advisor are calculated and sorted in descending order. At the same time, information on the usage of other indexes in Index Usage is collected, including the number of times it is used and the last time it is used. Finally, the results are displayed on the index monitoring UI interface in descending order of the total index benefit. For self-built indexes, the index usage information is displayed in descending order based on index importance. Recommended indexes and self-built indexes are displayed in list form on the monitoring UI. The specific benefits of indexing are as follows: When calculating index benefits, the last analysis before the index creation time is used as the raw time calculation data. For SQL statements appearing on the table where the index resides, the average execution time is calculated using the total execution time, total CPU consumption, and total number of executions corresponding to that SQL statement, as shown in the following formula: ； ； Wherein, i1 represents index; s1 represents SQL statement; Total execution time, Total execution time, Total execution time, Total CPU consumption, Total CPU consumption; To calculate the average time taken after index creation, iterate through the SQL statements related to the table containing the index, and use the corresponding total execution time, total CPU consumption, and total number of executions to calculate the average time. and ; The rate of change in the index's benefit on a single statement is the difference between the average execution time of that statement in the last analysis period before its creation and the average execution time of that statement in the current analysis period, divided by the average execution time of that statement in the last analysis period before its creation, as shown in the following formula: ； ； The total benefit of index i1 is the sum of the products of the average benefit of all n SQL statements related to i1 and the statement importance weights in this analysis, as shown in the following formula: ； ； in, =Number of executions * Average execution time OR Cumulative execution time OR CPU time.

Images