Power test database management analysis method and system

Abstract

The invention relates to the technical field of database structures, in particular to an electric power test database management analysis method and system.The electric power test database management analysis method comprises the following steps that all test data are collected and arranged in an electric power test database, and data access frequency and response time records are generated by calculating the query frequency and response time of all data columns; and based on the data access frequency and the response time record, evaluating a data access mode, and generating an index performance monitoring result. According to the method, accurate perception of the data access performance is realized through acquisition and analysis of the data column query frequency and the response time, a mode stability value is introduced into evaluation of the index performance, a multi-dimensional evaluation system of the data access performance is established, and index structure optimization is promoted to be more targeted. In the index structure reconstruction process, the CPU utilization rate, the memory occupancy rate and the I / O throughput are monitored and recorded in real time, and the resource use condition and the adjustment effect feedback condition in the index adjustment process are controlled.

Description

Technical Field

[0001] The present invention relates to the technical field of database structures, and particularly to a method and system for managing and analyzing a power test database. Background Art

[0002] The technical field to which the method for managing and analyzing a power test database belongs is the database management technology field, specifically related to the related technologies of database performance optimization, index structure adjustment, data access mode evaluation, and resource occupancy monitoring. By analyzing and adjusting multiple basic parameters such as the internal data storage structure, query response performance, data access frequency, and data processing efficiency of the database, with database log recording, structure reconstruction, performance monitoring, and resource management as the specific execution contents, and using the analysis results of the system performance parameters, the database background index structure is dynamically adjusted to achieve the automatic optimization of the database structure. In the process of adjusting the database index structure in the prior art, the specific structural characteristics are not fully considered, resulting in a lack of data support in the index optimization process and a large blindness in the execution of index reconstruction, reducing the pertinence of the index optimization effect. Therefore, improvements are needed. Summary of the Invention

[0003] The purpose of the present invention is to solve the deficiencies existing in the prior art and propose a method and system for managing and analyzing a power test database.

[0004] To achieve the above purpose, the present invention adopts the following technical solutions. A method for managing and analyzing a power test database includes the following steps: Collect and organize various test data in the power test database, generate data access frequency and response time records by calculating the query frequency and response time of each data column; based on the data access frequency and response time records, evaluate the data access mode and generate index performance monitoring results; According to the index performance monitoring results, compare the performance of each index with a preset efficiency threshold, identify the indexes with performance lower than the efficiency threshold, and generate inefficient index identification results; for the inefficient index identification results, adjust the index structure to generate an index structure adjustment plan; According to the index structure adjustment plan, execute the index reconstruction process in the background of the power test database, monitor the resource occupancy and execution time during the reconstruction process, generate index reconstruction execution records; based on the index reconstruction execution records, verify the adjustment effect of the index structure, conduct effect comparison analysis, and generate index adjustment effect analysis results; Through the index adjustment effect analysis results, count and mark the data columns that have not improved or have a performance decline after index reconstruction, and record and notify the engineer.

[0005] Preferably, the steps for obtaining the data access frequency and response time records are as follows: Call the various test data stored in the power test database, count the number of query operations for each data column, extract the timestamps of the queries in combination with the log information of the access requests, count the total number of times each data column is queried within a set time range, and generate the query frequency of each data column; Based on the query frequencies of the various data columns, call the response time consumption records corresponding to each data column when it is accessed through the database background log. After calculating the sum of the response times generated by all access requests for the same data column and dividing it by the total number of queries, count and calculate the average value of the response time of each data column to obtain the average response time of each data column; Based on the query frequencies of the various data columns and the average response times of the various data columns, by calling the frequency values and average response time values of each data column one by one, and associating the two data contents with the data column name as the unique identifier, generate the data access frequency and response time records.

[0006] Preferably, the steps for obtaining the index performance monitoring results are as follows: Based on the data access frequency and response time records, calculate the pattern stability value of each data column. The calculation formula is: ; Wherein, represents the pattern stability value of the th data column, represents the sum of all query response times of the th data column, represents the standard deviation of all query response times of the th data column, represents the time interval between two adjacent accesses of the th data column, represents the average interval time between two adjacent accesses of the th data column, represents the number of accesses of the th data column; Based on the pattern stability values of the various data columns, integrate and generate the index performance monitoring results.

[0007] Preferably, the steps for obtaining the inefficient index identification results are as follows: According to the index performance monitoring results, calculate the index performance evaluation value of the data column. The calculation formula is: ; Wherein, represents the index performance evaluation value of the th data column, is the pattern stability value of the th data column, is the Query frequency of a data column is the preset efficiency threshold of the data column; Based on the index performance evaluation values of each data column, determine whether it is marked as an inefficient index, and generate an inefficient index recognition result.

[0008] Preferably, the steps for obtaining the index structure adjustment scheme are as follows: Call the inefficient index recognition result, extract the index tree structure corresponding to each inefficient index, analyze the node distribution state and the association relationship between nodes of the corresponding index tree, calculate the node branch balance degree and node density one by one, and generate a record of the inefficient index tree structure characteristic parameters; According to the record of the inefficient index tree structure characteristic parameters, calculate the index structure reconstruction adaptability value, and the calculation formula is: ; Among them, is the index structure reconstruction adaptability value of the th inefficient index, is the node branch balance degree of the th inefficient index tree, is the node density of the th inefficient index tree, is the number of path conflicts of the th inefficient index tree, is the number of redundant nodes of the th inefficient index tree, is the average number of scans of the th inefficient index tree; Select the inefficient index tree according to the index structure reconstruction adaptability value, perform structural parameter rearrangement and node optimization reconstruction, and generate an index structure adjustment scheme.

[0009] Preferably, the steps for obtaining the index reconstruction execution record are as follows: According to the index structure adjustment scheme, perform node rearrangement, redundant node removal, and index depth adjustment of the index tree one by one, and record the start and end times of each operation to generate an execution time record of the index reconstruction process; According to the execution time record of the index reconstruction process, monitor and record the changes in CPU usage rate, memory occupancy rate, and I / O throughput during the node rearrangement and redundant node removal of the index tree in real time, and generate a resource occupancy record of the index reconstruction process; According to the execution time record of the index reconstruction process and the resource occupancy record during the index reconstruction process, perform timestamp matching and resource call relationship correspondence, and count the execution time and the maximum resource occupancy of a single index structure reconstruction process to generate an index reconstruction execution record.

[0010] Preferably, the step of obtaining the analysis result of the index adjustment effect is as follows: Based on the execution record of the index reconstruction, calculate the index adjustment effect quantization value, and the calculation formula is: ; where is the th index adjustment effect quantization value, is the average response time after the th index structure reconstruction, is the average response time before the th index structure reconstruction, is the query success rate after the th index structure reconstruction, is the query success rate before the th index structure reconstruction, is the database throughput after the th index structure reconstruction, is the database throughput before the th index structure reconstruction; According to the index adjustment effect quantization value, perform sorting and grading to generate the index adjustment effect analysis result.

[0011] Preferably, the steps of regularly updating the data access frequency and response time of the power test database through the analysis result of the index adjustment effect are as follows: Call the analysis result of the index adjustment effect, and count the query frequency and average response time after the index reconstruction of each data column one by one to generate the data access frequency and response time record after the index reconstruction; Based on the data access frequency and response time record after the index reconstruction, count and mark the data columns that have not been improved or have performance degradation after the index reconstruction, and record and notify the engineer.

[0012] The present invention provides a database management analysis system, including: A data collection and frequency analysis module, which extracts test data from the power test database, calculates the query frequency and response time of each data column, and generates a data access frequency and response time record; An index performance evaluation module, which uses the data access frequency and response time record to evaluate the access patterns of each data column in the database, analyzes and obtains the performance monitoring results of each index, and forms an index performance monitoring result; An inefficient index identification module, which compares the performance of each index in the index performance monitoring result with a preset efficiency threshold, identifies the indexes with performance lower than the threshold, and generates an inefficient index identification result; The index structure adjustment module adjusts the index structures identified as inefficient according to the results of inefficient index identification, formulates an index structure adjustment plan, monitors the resource occupancy and execution time during the index reconstruction process, completes the index reconstruction and records the process, and generates an index reconstruction execution record; The effect verification and analysis module uses the index reconstruction execution record to verify the adjustment effect of the reconstructed index structure, compares the performance changes before and after the adjustment, counts the data columns that have not improved or have a performance decline after the reconstruction, and sends a notification to the engineer.

[0013] Compared with the prior art, the advantages and positive effects of the present invention are as follows: Through the collection and analysis of the query frequency and response time of data columns, the present invention realizes the accurate perception of data access performance, introduces the mode stability value into the evaluation of index performance, establishes a multi-dimensional evaluation system for data access performance, and promotes the more targeted optimization of the index structure. During the index structure reconstruction process, the CPU usage rate, memory occupancy rate, and I / O throughput are monitored and recorded in real time to master the resource usage situation and the feedback of the adjustment effect during the index adjustment process, and improve the utilization efficiency and adjustment accuracy of database resources. Description of the Drawings

[0014] Figure 1 It is a schematic diagram of the steps of the present invention. Detailed Embodiment

[0015] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0016] Please refer to Figure 1 , the present invention provides a technical solution, a method for managing and analyzing a power test database, including the following steps: Collect and organize various test data in the power test database, calculate the query frequency and response time of each data column, and generate data access frequency and response time records; based on the data access frequency and response time records, evaluate the data access mode and generate index performance monitoring results; The above-mentioned power test database is specifically a system for managing the information of the entire process of power tests. Its core functions are divided into two major parts: the master data center and test management. In the master data center, the system supports multi-level classification of test items (such as subdivision by specialty and test type), creation of specific test items (such as power transformers) and registration of their basic attributes and inspection standards. At the same time, it provides detailed parameter configuration functions, including defining the parameter hierarchy structure, creating specific test detection parameters (such as factory values, measured values, calculated values) under the hierarchy, maintaining parameter calculation formulas and standard value rules, and assigning these parameters or parameter combinations to specific test items to form test templates. The test management module covers the management of test equipment (including entry of basic information, calibration plan and reminder), creation of test tasks (automatically loading information and parameter forms based on selected test items and assigning executors), entry of test data (permission verification, generating forms according to configuration, over-standard reminder, automatic calculation, selecting equipment, result saving and locking, querying and exporting), and the revocation function for completed test tasks, allowing authorized users to re-execute tests. The entire database system supports general operations such as addition, editing, deletion, submission, attachment upload, batch import and export, querying, and time recording.

[0017] Based on the index performance monitoring results, compare the performance of each index with the preset efficiency threshold, identify the indexes with performance lower than the efficiency threshold, and generate the identification results of inefficient indexes; for the identification results of inefficient indexes, adjust the index structure and generate an index structure adjustment plan; According to the index structure adjustment plan, execute the index reconstruction process in the background of the power test database, monitor the resource occupancy and execution time during the reconstruction process, and generate an index reconstruction execution record; based on the index reconstruction execution record, verify the adjustment effect of the index structure, conduct effect comparison and analysis, and generate an index adjustment effect analysis result; Through the index adjustment effect analysis result, count and mark the data columns that have not improved or have a performance decline after index reconstruction, and record and notify the engineer.

[0018] The steps for obtaining the data access frequency and response time records are as follows: call the stored test data in the power test database, count the number of query operations for each data column, extract the query timestamp in combination with the log information of the access request, count the total number of times each data column is queried within the set time range, and generate the query frequency of each data column; Based on the query frequency of each data column, call the response time consumption records corresponding to each data column when it is accessed through the database background log, calculate the sum of the response times generated by all access requests for the same data column and divide it by the total number of queries, and statistically calculate the average value of the response time of each data column to obtain the average response time of each data column; Based on the query frequency of each data column and the average response time of each data column, by successively calling the frequency value and the average response time value of each data column, and associating the two data contents with the data column name as the unique identifier, a record of data access frequency and response time is generated.

[0019] Specifically, call each item of test data stored in the power test database. During the processing, first sort out the access location of each data record and confirm its corresponding data column name, and then determine whether a certain data column is regarded as an object that needs to be key - statistically analyzed according to the internally set reference threshold. This reference threshold can be determined by adding twice the standard deviation to the average number of query operations of all data columns in the past week. Suppose the threshold calculated in the example is , when the query times of a certain data column are greater than 80, it is classified as a high - access column; if the query times are less than or equal to 80, it is classified as a low - access column. Subsequently, select a target time period, such as 168 consecutive hours, as the statistical range, and accumulate the specific number of times each data column is accessed within this time period. During this period, by parsing the log information of the access requests, extract the timestamp containing year, month, day, hour, minute, and second, record the precise time point of each access, and mark and exclude any access records in special cases, such as empty queries or abnormal requests. Finally, summarize the total number of accesses of each data column in this time period and correspond it to its category to generate the query frequency of each data column.

[0020] Based on the query frequency of each data column, compare the response time consumption information corresponding to each access item by item in the database background log. In the specific operation, match each access request with its response time. The time consumption of a single query can be calculated by recording the start time and the completion time of the server executing the query statement. Suppose that in order to more accurately determine the response time consumption, the fluctuation caused by network latency needs to be excluded. A latency correction amount can be set in advance based on the historical average latency between the local and the server. When the measured query time consumption is greater than a certain preset reference value , it can be considered that there is an abnormal response. This preset reference value can be obtained by adding an empirical correction amount to the average duration of all queries in the past month. For example, if the average value is 120 milliseconds and the empirical correction amount is 30 milliseconds, then milliseconds. For the access entries in the actual record that exceed 150 milliseconds, mark them first and then conduct a second confirmation. Finally, by accumulating the time consumption of all normal requests and dividing it by the total number of corresponding queries, the average response time of each data column can be obtained, and the average response time of each data column is obtained.

[0021] Based on the query frequency of each data column and the average response time of each data column, the frequency values corresponding to each data column and the previously calculated average response time can be read one by one. In actual execution, a comparison table containing data column identifiers and the two metrics needs to be established. This comparison table can be corresponded through a unique name or index allocated within the system. Then, a one-to-one logical association is made between the frequency values and the average response time. If different access modes need to be distinguished, a distinguishing field can be added to the comparison table. For example, when the frequency is lower than a certain reference value and the response time is relatively large, it is marked as possibly having a configuration problem. This reference value can be determined by the median of the access frequencies of all data columns and deviation analysis. If it is found during the statistical process that some data columns do not have any query records, they are regarded as non-access columns and recorded separately. Then, the names of the data columns that have completed the association, their corresponding frequencies, and average response times are summarized, and finally, a unified data structure description is completed to generate data access frequency and response time records.

[0022] The steps to obtain the index performance monitoring results are as follows: Based on the data access frequency and response time records, calculate the mode stability value of each data column. The calculation formula is: ; where represents the mode stability value of the th data column, represents the sum of all query response times of the th data column, represents the standard deviation of all query response times of the th data column, represents the time interval between two adjacent accesses of the th data column, represents the average interval time between two adjacent accesses of the th data column, represents the number of accesses of the th data column; Based on the mode stability values of each data column, the index performance monitoring results are integrated and generated.

[0023] Specifically, the advantage of the formula is that by combining various information such as the dispersion degree of response time, the number of accesses, and the adjacent access time interval, a comprehensive value reflecting the access stability characteristics of the data column is generated, which contains considerations of query concentration and interval regularity, facilitating the evaluation of the access pattern of the corresponding data column according to this value in subsequent steps.

[0024] The steps to obtain All query records corresponding to a data column are then integrated in chronological order, and all response durations are added up in sequence to form an accumulated value. In this process, entries with abnormal timeouts in the access log need to be separately counted and excluded. Whether it is determined as an abnormal timeout can be set with a maximum allowable duration according to the performance metrics of the device or system. This maximum allowable duration can be obtained by referring to the average response time of all queries in the previous 30 days plus three times the standard deviation. For example, after parsing 1500 access records, the average response time is 120 milliseconds and the standard deviation is 25 milliseconds, then the maximum allowable duration can be set to milliseconds. Entries with access times exceeding 195 milliseconds are directly classified as abnormal. At this time, the sum of all normal requests in the th data column forms . If the accumulated effective access response time of the th data column within a week is 14800 milliseconds, it is recorded as milliseconds.

[0025] is obtained as follows: After obtaining the response time sequence of all valid requests in the th data column, the standard deviation operation needs to be performed on this sequence, that is, first calculate the deviation between each response duration and the average response duration respectively, and accumulate the squares of the deviations, then divide by the result of the number of valid requests minus 1, and finally take the square root to obtain the standard deviation. For example, for the th data column mentioned above, if there are 93 valid request response durations forming a sequence in milliseconds , its average value can be denoted as , then . If among them milliseconds, and the accumulated value of the squared deviations of each is 132480, then the standard deviation can be calculated as milliseconds, and finally this result is denoted as .

[0026] is obtained as follows: The records of the time intervals between two adjacent accesses in the th data column are from the access log within the same time span. It is necessary to retrieve the timestamps of each access in sequence according to the access order and calculate the time difference from the previous access. Then, all adjacent interval values are collected into a list, and according to the requirements, the most common interval value among them can be selected or a certain number of weighted calculations can be performed to obtain specific value. When the access pattern is relatively concentrated, most interval values will be close to a certain range. For example, after statistics, it is found that the most common adjacent access time interval is 32 seconds, then Recorded as 32 seconds. If the system requires weighting the interval value, a weight factor needs to be set in advance to distinguish the access characteristics during the day or night. This weight factor can be extracted from the frequency distribution of access time periods over 60 consecutive days, and finally obtain a stable output value. For example, during the monitoring process, the access time period is segmented and counted in 0 - 24 hours, and the average access interval during the day is obtained as 24 seconds, and the average access interval at night is 40 seconds. If the actual statistics show that the daytime access volume accounts for 60% and the nighttime access volume accounts for 40%, then the weighted result of the two intervals is used as = 。

[0027] The acquisition steps of are as follows: This parameter represents the average interval time between two adjacent accesses of the th data column. After obtaining all the differences in adjacent access times, a simple average calculation can be performed first, or small - range fluctuations can be excluded in the intermediate steps. For example, it is statistically found that a total of 95 access requests are retrieved for the th data column within the same period of time, and 94 adjacent access intervals are obtained respectively. Adding these interval values and dividing by 94 can obtain . If the sum of the individual access intervals is 2525 seconds, then 。

[0028] The acquisition steps of are as follows: In the access log, the access count can be directly obtained according to the number of occurrences of the th data column. This count is obtained by summarizing and aggregating all query statements. For example, when the system statistics show that the th data column is accessed 314 times within the same week, then record 。

[0029] Calculation process: Substitute the parameter values obtained previously into this formula for calculation. Taking a specific example, let , , , , , first calculate , then calculate , multiply the two to get . After taking the absolute value, it is still 1182847.12. Then perform the cube - root operation , and finally divide by , that is . Therefore, for this example, 。

[0030] This result indicates It shows the integration with factors such as access interval and response time at the level of 0.338. The smaller the value, the more volatile the adjacent access intervals or the more dispersed the response time characteristics. When is close to or greater than 1, it represents that the th data column has a relatively consistent access pattern. When is higher, more detailed sub - processing can be carried out in combination with specific scenarios.

[0031] Based on the pattern stability values of each data column, during the summary stage, first integrate the values corresponding to each data column and fill them into a centrally managed reference table. This reference table needs to attach detailed index names for each data column and associated data such as the access frequency and average response time obtained previously. To achieve a complete integration process, on the basis of uniformly collecting the pattern stability values, first read the identifiers of all data columns and their status tags in the system, and at the same time set up a special field in the reference table to place the pattern stability values, thus forming a multi - column parallel data set. During this process, a range can be set to judge the relative interval of the pattern stability values. For example, define the range from 0.0 to 0.5 as the low - stability interval, the range from 0.5 to 1.5 as the medium - stability interval, and the range from 1.5 to 3.0 as the high - stability interval. The specific values of this range can be evaluated in combination with the statistical results of the past three months. For example, calculate the global distribution of pattern stability values by accumulating at least ten thousand query records, and expand the value segment with the largest proportion after finding it. When it is detected that the pattern stability value of a certain data column exceeds 3.0, it can be separately marked and put into a special management queue because it often corresponds to a unique distribution of access time intervals or response durations in actual analysis. Then, match all the marked pattern stability values with the access frequencies one by one. For those data columns that are both in the high - stability interval and exceed a certain threshold in terms of access frequency, continue to retrieve the detailed request records and timestamps under the data column name, and compare them with the interval range from 0 seconds to 72 hours as required. For data columns with low stability values, key inspections can be carried out on whether accesses with too short or too long intervals appear concentrated. Finally, after all the comparisons are completed, integrate the associated information to form a summary record and merge it into the designated location in the system to generate the index performance monitoring result.

[0032] The steps to obtain the inefficient index identification result are as follows: According to the index performance monitoring result, calculate the index performance evaluation value of the data column. The calculation formula is: ; Among them, represents the index performance evaluation value of the th data column, is the pattern stability value of the th data column, is the The query frequency of a data column is the preset efficiency threshold of the th data column; Based on the index performance evaluation values of each data column, determine whether it is marked as an inefficient index, and generate an inefficient index identification result.

[0033] Specifically, the advantage of the formula is that it comprehensively considers factors such as the pattern stability value and query frequency of the data column, and concentrates the impacts of various factors on the index performance within a quantifiable evaluation value for comparison. When the data columns are sorted according to this evaluation value, the pros and cons of the index performance can be intuitively displayed, which is convenient for further judging inefficient indexes and carrying out corresponding processing in the subsequent steps.

[0034] The acquisition step of is: This parameter represents the pattern stability value of the th data column, which is used to describe the distribution of the data column access interval and response time within a specific period, and it is derived from the results calculated in the previous step.

[0035] The acquisition step of is: This parameter is the preset efficiency threshold of the th data column, which indicates the expected efficiency baseline of this data column in the current database environment and is used to evaluate whether this data column is at a level consistent with the overall performance goal. First, monitor the access status of this data column for at least two months, collect the average response duration, successful query ratio, and the completeness of the results returned by each query, and obtain a comprehensive score based on these monitoring data. The following example shows the steps of segmented weighted calculation: , where the response duration index, successful query ratio, and result completeness can all be obtained through actual measurement or statistics. represents the weights of each item. Each weight can be set according to the database load and user experience requirements during the evaluation process. For example, when testing an enterprise business database, give , , , and obtain a comprehensive score value after calculating all query-related data to form the specific value of.

[0036] The acquisition step of is: This parameter refers to the The query frequency of a data column is mainly reflected in the number of times the data column is accessed within a certain time window. When obtaining it, all query statements need to be retrieved from the database log first, requests for the data column are filtered out, and the total access volume is counted. If it is found that some automated scripts initiate queries frequently, they are also included. Then, the counted access volume is divided by the time length to form the query frequency value. When the time length within the statistical interval is one week and the total access volume is 1890, then 。

[0037] Calculation process: Substitute it into the formula for calculation. For example, if the previously calculated value of a certain data column is , , , then first calculate and , and the absolute value of the difference between the two is , then calculate , and then perform the multiplication operation to get , for the denominator part , finally calculate , that is, get 。

[0038] This result indicates that shows a certain degree of deviation at the order of magnitude around 0.14, that is, there is a difference from the efficiency threshold . Combining the foregoing formula, it can be inferred that when and are closer, will tend to a smaller value. In practice, a discrimination criterion can usually be given. For example, when , it is regarded as a more obvious deviation, and when it is lower than 0.2, it means that the data column is relatively close to the expectation in terms of efficiency, thereby providing an auxiliary reference for judging whether the index of the data column needs to be adjusted in the subsequent steps.

[0039] Based on the index performance evaluation values of each data column, read the stored index performance monitoring results and extract values such as the pattern stability and query frequency of each data column. During the entire execution process, first check whether there are necessary conditions to meet the subsequent calculation requirements. For example, check whether the previous monitoring session covers a complete duration. For instance, use a two-week cycle as the statistical basis to ensure that each data column has a sufficient number of accesses to support subsequent evaluations. When it is confirmed that the data is complete, perform aggregation analysis based on the index performance evaluation values of each data column. Arrange all the values in descending or ascending order and group them. During the grouping process, divide them according to the set multiple intervals. For example, label as no obvious deviation within the interval of 0.0 to 0.3, label as moderately deviated within the interval of 0.3 to 0.7, label as significantly deviated within the interval of 0.7 to 1.0, and separately mark as strongly deviated when exceeding 1.0. At the same time, allocate each data column to the corresponding set and summarize the list according to the grouping results. Then, combined with other information records such as the index tree level, query success rate, or response latency fluctuation, particularly identify the cases with high index performance evaluation values. For example, if the evaluation values of multiple data columns all exceed 1.0, then trace the corresponding query time distribution item by item and check whether there is an abnormal surge in requests within the range of 0 seconds to 30 seconds. For the data columns with evaluation values in a relatively small range, focus on the stability during the query process. Finally, after all aggregations and groupings are completed, determine which data columns' index performance evaluation values have significantly deviated from the requirements, mark these data columns as inefficient indexes, and then generate the inefficient index identification results.

[0040] The steps to obtain the index structure adjustment plan are as follows: Call the inefficient index identification results, extract the index tree structure corresponding to each inefficient index, analyze the node distribution status and the association relationship between nodes of the corresponding index tree, calculate the node branch balance degree and node density one by one, and generate the record of the inefficient index tree structure characteristic parameters; According to the record of the inefficient index tree structure characteristic parameters, calculate the index structure reconstruction adaptability value. The calculation formula is: ; Among them, is the index structure reconstruction adaptability value of the th inefficient index, is the node branch balance degree of the th inefficient index tree, is the node density of the th inefficient index tree, is the number of path conflicts of the th inefficient index tree, is the number of redundant nodes of the th inefficient index tree, is the Average number of scans of an inefficient index tree; Select an inefficient index tree according to the reconstruction fitness value of the index structure, perform structural parameter rearrangement and node optimization and reconstruction to generate an index structure adjustment plan.

[0041] Specifically, call the inefficient index recognition result, read the index tree structure corresponding to each inefficient index in the sorted inefficient index list, count the number of nodes at all levels for each index tree one by one and confirm the key value range of each node. At the same time, retrieve the mutual pointing records between nodes to detect whether there are circular pointers or missing links between nodes. Then, distinguish the nodes with multiple branch paths and record the number of sub-paths of these nodes as the number of branches. For the case where the number of branches is less than 2 or greater than 10, its abnormal state can be additionally marked and it can be included in the key comparison objects for the subsequent node branch balance. During this period, the average width of each branch is divided by counting the parallel scale of all child nodes. For example, when a node has 4 branches, the number of keywords of each branch can be compared from 1 to 20. If it exceeds 20, it is considered that the branch is congested. If it is less than 1, it is regarded as a vacant branch. While recording these branch data, retrieve the free pointers and other unused links under the node, and further summarize the overall space utilization of the node. Subsequently, map the keywords stored at all branch positions to the number of lower-level nodes, and compare the density of the actual number of keywords with the range that can be accommodated at this level. For example, in the second layer of an index tree, each node can accommodate up to 30 keywords. If the statistical result shows that the number of keywords in the node is close to 30 many times, it is recorded as high density. If the number of keywords is concentrated between 10 and 20, it is recorded as medium density. When it is less than 10, it is low density. Finally, integrate parameters such as node branch balance and node density to generate a record of the structural characteristic parameters of the inefficient index tree.

[0042] The benefit of the formula is that it simultaneously introduces multiple important elements such as node branch balance, node density, path conflict times, and redundant node quantity, differentiates and amplifies or reduces each influencing dimension by means of exponentiation and cube root, and combines the influence brought by the average number of scans at the denominator, thereby comprehensively measuring the importance degree of index tree reconstruction in one expression.

[0043] The acquisition step of is: This parameter represents the For the node branch balance of an inefficient index tree, it is necessary to first count the number of branches of all nodes in the index tree structure and determine whether their distribution in the entire tree is balanced. The specific approach is to record the number of branches of each node, compare the distribution of the number of branches across the entire tree, and then use the median or mean as the central reference to calculate the dispersion from this reference value and obtain a preliminary score for the branch balance. After that, the score is mapped to a more refined scale to form the final value. For example, by scanning an index tree layer by layer and collecting the number of branches of all nodes, the distribution of the number of branches is obtained as , where the average value is approximately 3.625. Then, calculate the deviation of the number of branches of each node from 3.625, and count the degree of dispersion based on the range where the deviation is between -1.625 and 1.375. Use the method of accumulating variance or absolute deviation to obtain a quantification value of the balance. After comparing the similar data of more than 100 index trees, linearly transform this quantification value to [0, 1] to obtain .

[0044] The obtaining steps of are as follows: This parameter represents the node density of the th inefficient index tree, which is used to measure the compactness of the nodes in the tree in terms of the actual stored content. When counting, it is necessary to first record the number of keywords or reference links contained in each node and compare it with the maximum capacity that the node can accommodate, so as to obtain a utilization rate. For example, in a certain type of B+ tree index structure, if a node at a certain layer can accommodate at most 30 keywords, and it is actually found that there are 25 keywords on average in the current node, then the utilization rate of this node can be calculated as

[0045] The obtaining steps of are as follows: This parameter represents the number of path conflicts of the th inefficient index tree, which refers to the cumulative record of conflicts that occur between nodes when establishing or querying paths. To obtain it, first replay each query path to check whether there are redundant jumps or repeated backtracking at the same level, or observe the situation where the node pointers are crowded. Then, count 1 for each conflict event, and finally sum up all the conflict events to get ; Jumping back to the same node twice or more can be regarded as one conflict, and a circular pointer in the index is also recorded as one conflict. After counting 200 queries of a certain index tree in the system, it is found that there are 12 paths with repeated backtracking, and 2 circular pointers are additionally recorded, then

[0046] The obtaining steps of The number of redundant nodes in an inefficient index tree requires examining from a structural perspective whether there are invalid or idling nodes. During the recording process, first list all the nodes and confirm whether they can actually bear keyword storage or index pointer references. Count the irrelevant nodes as redundant, and also retrieve nodes with excessive branches or few keywords at deep levels. Combine these nodes into a set, and finally count the total number of this set as , for example, in an index tree structure, count 14 nodes. Among them, 3 nodes have only one keyword and no branch expansion, and there is another completely empty node. Considering them all as redundant nodes, then .

[0047] The acquisition steps of are as follows: This parameter refers to the average number of scans of the th inefficient index tree. It is necessary to traverse the index query paths at all levels and record the number of nodes involved in each index retrieval. Then, sum up all the retrieved node numbers and divide by the total number of queries to obtain the average scan depth or scan steps. For example, for a certain index tree, conduct 300 queries and record the number of nodes passed through each retrieval .

[0048] Calculation process: Combined with the aforementioned parameters, substitute them into for calculation. For example, substitute , , , , , first calculate = , then take the square root of it , then calculate = 10, take its cube root , multiply the two to get , the denominator part is , and finally calculate the overall , thus obtaining .

[0049] This result shows that in the current example, the index structure reconstruction adaptability value is at the level of 0.261. This value, combined with other comparison data, can reflect the status of this inefficient index tree in terms of node balance and conflict and other indicators. For example, on-site, the threshold can be set at 0.5. When is greater than 0.5, it is considered that the tree requires more urgent structural reconstruction. If it is lower than 0.3, it means that the current conflict and redundancy situation is not too serious, thus providing a basis for locating and rearranging nodes and optimizing the index level in the subsequent steps.

[0050] Reconstruct the fitness value according to the index structure. After summarizing all the index trees marked as inefficient indexes, it is necessary to review the recorded node branch information one by one, compare the number of keywords of all nodes with the connection situation of the upper index layer, aggregate the nodes with particularly deep levels or particularly high keyword redundancy rates, and then check whether the branch balance degree of the paths where these nodes are located seriously deviates. For example, 2 to 6 branches can be set as the general interval, more than 6 as too large, and less than 2 as too small. Add the corresponding nodes to the annotation list. After that, combine all the marked nodes into an optimization processing pool, analyze whether the association pointers of the nodes in the horizontal same layer and the vertical upper and lower layers are consistent. If there are repeated cross-pointing or invalid null links, they will be included in the rearrangement step. For the nodes involved in a high number of conflicts, first perform branch merging or pointer reallocation. After the inspection, select the list of index trees worthy of node optimization and reconstruction. Finally, perform sequential scheduling and parameter rearrangement on the structure parameters of these index trees. When it is confirmed that all node allocation information meets the keyword capacity reference standard obtained in advance and no situation of exceeding the specified branch maximum or having null pointers is found, the parameter rearrangement process of this index structure can be ended, and an index structure adjustment plan is generated accordingly.

[0051] The steps to obtain the execution record of index reconstruction are as follows: According to the index structure adjustment plan, perform node rearrangement, redundant node removal, and index depth adjustment of the index tree one by one, and record the start and end times of each operation to generate the execution time record of the index reconstruction process; According to the execution time record of the index reconstruction process, monitor and record the changes in CPU usage rate, memory occupancy rate, and I / O throughput during the node rearrangement and redundant node removal of the index tree in real time to generate the resource occupancy record of the index reconstruction process; According to the execution time record of the index reconstruction process and the resource occupancy record during the index reconstruction process, perform timestamp matching and resource call relationship correspondence, and count the execution time and the maximum resource occupancy of a single index structure reconstruction process to generate the index reconstruction execution record.

[0052] Specifically, according to the index structure adjustment plan, retrieve the previously obtained node rearrangement list one by one and lock the target nodes that need to reallocate the number of keywords in each index tree. For these target nodes, read their current branch information and keyword occupancy status item by item. Nodes with the number of branches between 2 and 6 are marked as the general range. When the number of branches exceeds 6 or is less than 2, record the corresponding quantity. If the recorded value exceeds the preset threshold, branch merging or splitting operations need to be performed. This threshold can be obtained by referring to the statistical results of one hundred index trees. For example, after collecting the node branch quantity distribution of one hundred index trees, it is found that 3 to 5 is the common range, and exceeding 7 will lead to excessive branch expansion. Therefore, 6 is set as the upper limit of the index range. Subsequently, when evaluating redundant nodes, check whether there are null pointers or nodes with less than 3 keywords, and then judge whether upward merging or downward splitting is required according to the level where the node is located. If the node depth is higher than 4 layers and the number of keywords is less than 3, perform the deletion operation. At the same time, record the corresponding moments before and after each node rearrangement action, mark the start time and end time in the format of hours, minutes, and seconds and store them in the execution record. Adjusting the index depth also requires reading the previously counted level distribution value. When it is detected that the index depth is higher than a certain fixed standard, trigger the level compression operation. For example, reallocate the index exceeding 5 layers to 3 or 4 layers according to the primary key interval. This standard can be set by evaluating the current database table volume and query volume. During the execution of the compression operation, record the start and end moments of the action and incorporate them into the same time series. After completing all node processing and redundant deletion, archive the start and end times of each operation, and finally generate the execution time record of the index reconstruction process.

[0053] According to the execution time record of the index reconstruction process, read the start and end times of each period and retrieve the CPU usage rate, memory occupancy rate, and I / O throughput information that matches the period from the monitoring tool. First, compare the specific minutes and seconds information in the time record to match the corresponding monitoring data entries. For the CPU usage rate, a warning range can be set. For example, the range from 0% to 50% is the low-load interval, 50% to 80% is the medium-load interval, and exceeding 80% is recorded as the high-load interval. Similarly, for the memory occupancy rate, the range from 0% to 60% can be used as the acceptable range, 60% to 85% as the tense range, and exceeding 85% is marked as the overflow risk. These thresholds can be set based on the overall monitoring data of the system operation in the previous year. For example, if the average CPU usage rate of a certain server is 35% and the average memory occupancy rate is 50% within half a year of operation, and there is usually a certain degree of increase in occupancy during the index rearrangement operation, so referring to the results of multiple stress tests, 80% and 85% are used as the division criteria. Then, when processing the I / O throughput, it is necessary to match the peak period of each rearrangement operation in combination with the timestamp. When it is found that the I / O throughput exceeds a certain limit value within a unit time, such as being greater than 500MB / s, it is marked as a concern point. During the whole process, it is necessary to continuously record the matched CPU usage rate, memory occupancy rate, and I / O throughput values, and at the end, aggregate the occupancy data of each period into a single record block and write it into the resource occupancy record of the index reconstruction process.

[0054] According to the execution time record of the index reconstruction process and the resource occupancy record during the index reconstruction process, first calibrate each rearrangement action on the time axis. The specific method is to round down the start time and end time in the execution time record to the nearest second or millisecond level, so as to be consistent with the sampling time in the resource occupancy record. Then, compare the monitoring values of CPU, memory, and I / O in this interval item by item. If the CPU usage rate exceeds the previously set 80% threshold multiple times in the same operation interval, extract its peak value and store it as the CPU maximum value of this operation. Similarly, find the maximum value of the memory occupancy rate and I / O throughput in this interval, classify these results to generate the maximum resource occupancy corresponding to each operation. After processing all intervals, associate and store the operation duration with the three maximum resource occupancies, and generate a list in the order of operation. In the list, operations with an operation time greater than 5 minutes or resource occupancy exceeding their respective indicators can be marked in bold. For how to set 5 minutes as a threshold, the historical records of multiple rearrangement operations can be referred to and the average operation duration and deviation range can be determined. If the operation duration distribution of some operations is concentrated between 3 minutes and 10 minutes, then select 5 minutes as an intermediate reference value. Finally, merge all the operation and maximum occupancy records to form the index reconstruction execution record.

[0055] The steps for obtaining the analysis results of index adjustment effects are as follows: Based on the execution records of index reconstruction, calculate the quantization value of index adjustment effects. The calculation formula is: ; where is the quantization value of the th index adjustment effect, is the average response time after the th index structure reconstruction, is the average response time before the th index structure reconstruction, is the query success rate after the th index structure reconstruction, is the query success rate before the th index structure reconstruction, is the database throughput after the th index structure reconstruction, is the database throughput before the th index structure reconstruction; According to the quantization value of index adjustment effects, perform sorting and grading to generate the analysis results of index adjustment effects.

[0056] Specifically, The steps for obtaining are as follows: This parameter is the average response time after the th index structure reconstruction. It is necessary to conduct access monitoring for a fixed period of time after the index reconstruction is completed, record the response duration of each query, and calculate the average after summarizing all response durations. When obtaining this average value, it is required to conduct continuous monitoring for at least several days under the same environment and without changing the main load characteristics of the database. Then, abnormal values can be removed and summarized for all access durations. For example, set an extreme value threshold to filter out abnormal entries when the network is disconnected or the CPU is severely impacted. Add up the remaining normal response durations and divide by the number of requests to obtain this parameter. For example, during a one-week analysis period, a total of 4170 query records were captured for a certain index after the reconstruction was completed. After data cleaning, 3980 valid entries were retained. The total response duration of all valid entries is 358900 milliseconds. Then .

[0057] The steps for obtaining are as follows: This parameter is the The average response time before the index structure reconstruction. Before making changes, extract the query response duration data of the corresponding index when it was not reconstructed from the historical monitoring records of the same database. Similarly, ensure that the monitoring data is from continuous monitoring at the same load level, keep the statistical duration consistent with the previous and subsequent data. For example, follow the same weekly cycle length as before, exclude abnormal accesses, and then calculate the average value by summing and dividing by the number of accesses. For example, if there were 4120 valid queries before the index change and the total response duration was approximately 382400 milliseconds, then 。

[0058] The acquisition steps of are as follows: This parameter is the query success rate after the reconstruction of the index structure, which represents the proportion of queries that return normal results within a certain period after the reconstruction is completed and runs stably. It is necessary to combine the database access logs to determine whether each query operation returns available data within the specified time limit. If the query returns a normal result within the specified time period, it is counted as a successful query. After the statistical period ends, divide the number of successful queries by the total number of queries to obtain the query success rate. For example, during the same one-week monitoring after the reconstruction, it was observed that there were 3980 queries, and 3955 of them returned normal results. Then 。

[0059] The acquisition steps of are as follows: This parameter is the query success rate before the reconstruction of the index structure, which also needs to be confirmed in the same monitoring period or historical data before the change. The steps include collecting the execution results of each query and determining whether normal data is returned, and then dividing the number of successful queries by the total number of queries to obtain the success rate. To ensure the comparability of statistics, this period should also meet the principle of not less than several days or the corresponding request volume not less than several thousand. For example, among the 4120 queries monitored before, 4080 were successful queries. Then 。

[0060] The acquisition steps of are as follows: This parameter represents the database throughput after the reconstruction of the index structure, which needs to be measured by monitoring the number of requests successfully completed or the amount of data returned by the database per unit time. For example, the total number of queries successfully executed per hour can be counted, or the number of bytes transmitted back can be measured. In this embodiment, the throughput is defined as the number of queries completed per unit time. When collecting, first set a continuous statistical period, sum up the number of requests completed in each hour, and then divide by the number of hours to form the average throughput per hour. If a total of 3980 queries were completed during the one-week observation period after the reconstruction, the throughput can be written as 。

[0061] The acquisition steps are as follows: This parameter is the database throughput before the reconstruction of the th index structure and needs to be extracted from historical monitoring of the same length. For example, within a week of the same period, count the total number of successfully completed queries and obtain the average throughput by dividing it by the number of hours. For example, if 4120 queries were successfully completed within 7 days before the change, then

[0062] Calculation process: Let milliseconds, milliseconds, , , queries per hour, queries per hour. First, calculate milliseconds, then calculate , and , multiply the three to get , take the cube root of it , for the denominator part , and thus obtain .

[0063] This result indicates that in this instance , the magnitude of the value is used to indicate the comprehensive degree of change in the response time, success rate, and throughput compared to before the index reconstruction. When is close to or greater than 1, it indicates that the difference is relatively obvious, while when is below 0.01, it means that the changes in the relevant indicators are limited. Size comparison or grading processing can be carried out based on this value to further promote the analysis of the index adjustment effect.

[0064] Adjust the quantization value according to the index. After extracting the key metrics before and after each analyzed data column, it is necessary to first determine a list covering all indexes affected by the reconstruction. For each piece of data in the list, read the previously calculated quantization value and form an integrated record. When performing the integration, pay attention to the index identifier and the corresponding monitoring time period, and ensure that metrics such as the previously recorded average response time and query success rate correspond to each index one by one. Then, sort the quantization values in descending or ascending order. When setting the grading criteria, multiple intervals can be introduced to distinguish different change ranges. For example, it is marked as fine-tuning in the interval of 0.00 to 0.10, medium-tuning in the interval of 0.10 to 0.30, large-tuning in the interval of 0.30 to 0.60, and extremely changed if it exceeds 0.60. These grading thresholds can be determined by viewing the monitoring samples of the database in the previous three months. If it is found in the sample statistics that the corresponding quantization values are concentrated between 0.05 and 0.15, then a value around 0.10 or 0.15 can be used as the specific segmentation point. After grading all the data according to this rule, it is necessary to connect the results with the subsequent evaluation metrics. If the quantization value of an individual index is always close to 0.10 in the fine-tuning grading, then in the later stage, further attention can be paid to whether the index has volatility metrics. If there are indexes with a high concentration in the large-tuning or extremely changed grading, then trace back to its original query log to check whether there is a situation of excessive access delay in the interval of 0 seconds to 10 seconds, or whether there are obvious periodic fluctuations in the success rate. This can be combined with other data information for comprehensive evaluation. At the same time, mark the corresponding grading and the final quantization value for each index in the record, and the indexes with high grading can be included in the observation list first during subsequent operations. Finally, summarize the quantization values of all indexes and their grading identifiers to generate the analysis result of the index adjustment effect.

[0065] The steps for regularly updating the data access frequency and response time of the power test database through the analysis result of the index adjustment effect are as follows: Call the analysis result of the index adjustment effect, count the query frequency and average response time after the index reconstruction of each data column one by one, and generate the record of the data access frequency and response time after the index reconstruction; Based on the record of the data access frequency and response time after the index reconstruction, count and mark the data columns that have not been improved or have performance degradation after the index reconstruction, and record and notify the engineer.

[0066] Specifically, based on the analysis results of index adjustment effects, during the data monitoring phase, first collect the query request information of each data column within a certain time window starting from the previously recorded reconstruction completion time. Compare and count the total number of accesses to the data column by combining the timestamp of each access with the request identifier. Subsequently, divide the total number of queries during this period by the statistical duration to obtain the query frequency. To make the statistics more accurate, a range benchmark can be set first. For example, determine whether to exclude extremely low or high abnormal accesses based on the normal access level of the previous month. Requests with an occurrence rate lower than 1% or a duration longer than three standard deviations can be marked as abnormal and deleted. Then, accumulate the response durations of all normal requests and divide by the corresponding total number of queries to obtain the average response time for each data column. When recording these statistical values, carry the data column name and the key index information after reconstruction to ensure that it can be associated with the previous analysis results. At the same time, set an alarm range for data columns with high query frequencies but long response durations. For example, if the historical mean is 120 milliseconds and the standard deviation is 30 milliseconds based on statistics, then 120 plus twice the standard deviation, i.e., 180 milliseconds, can be used as an upper limit of the interval for judgment. Queries exceeding 180 milliseconds are recorded as key entries for summary. If it is found during this phase that the access to certain data columns surges only at night or on weekends, their usage characteristics can be further explained. Finally, after completing the breakdown statistics of the total number of queries and the cumulative response duration for all data columns within the specified period, organize the obtained query frequency and average response time with the data column as the identifier to generate the data access frequency and response time records after index reconstruction.

[0067] Based on the data access frequency and response time records after index reconstruction, it is necessary to compare the query frequency and average response duration of each data column in the new statistical period with the previously recorded reference range one by one. For example, a comparison benchmark can be set from the accumulated access mean and standard deviation. If the query count of a data column increases after reconstruction but the average response time still fluctuates around 120 milliseconds, it can be determined that the state is stable. If there is an obviously higher response duration than the original mean, it is marked as suspected performance degradation. When marking performance degradation, a threshold can be considered, such as demarcating at 1.2 times or 1.5 times the original mean. The specific multiple can be determined by the test data in the operation and maintenance process. If the response time in the new statistical period exceeds this multiple, it is recorded as out of bounds. If the query frequency of a data column does not increase significantly and the average response time does not decrease, it is marked as no improvement. In the specific implementation, the ratio of the query frequency after reconstruction to the original frequency and the difference in the average response time of each data column can be compared item by item. If the ratio is greater than or less than a certain range and the difference exceeds a set value, it is included in the list. For example, if the change in query frequency is less than 5% and there is no significant difference in the average response time, it can be regarded as no improvement. All the marked entries are summarized into a list for storage. If the number of items in the list is greater than the pre-agreed threshold, a notification can be triggered as a whole. This threshold can be set by the engineer according to the system scale. For example, 5 or 10 data columns are regarded as a first-level reminder. Then, this list and the detailed marking information are directly sent to the engineer through internal messages or interface reminders, and the engineer is recorded and notified.

[0068] The above is only the preferred embodiment of the present invention, and it is not intended to limit the present invention in other forms. Any person skilled in the art may use the disclosed technical content to make changes or modifications into equivalent embodiments with equivalent changes and apply them to other fields. However, as long as it does not depart from the technical solution content of the present invention, any simple modification, equivalent change and modification made to the above embodiments based on the technical essence of the present invention still fall within the protection scope of the technical solution of the present invention.

Claims

1. A method for managing and analyzing a power test database, characterized in that: The following steps are involved: Collect and organize various test data in the power test database, generate data access frequency and response time records by calculating the query frequency and response time of each data column; evaluate data access patterns based on the data access frequency and response time records, and generate index performance monitoring results; According to the index performance monitoring result, the performance of each index is compared with a preset efficiency threshold, the index whose performance is lower than the efficiency threshold is identified, and an inefficient index identification result is generated; according to the inefficient index identification result, the structure of the index is adjusted, and an index structure adjustment plan is generated; According to the index structure adjustment scheme, the index reconstruction process is executed in the background of the electric power test database, the resource occupancy and execution time during the reconstruction process are monitored, and an index reconstruction execution record is generated; based on the index reconstruction execution record, the adjustment effect of the index structure is verified, and an effect comparison analysis is performed to generate an index adjustment effect analysis result; Through the index adjustment effect analysis results, count and mark the data columns that have not been improved or have degraded performance after the index reconstruction, record and notify the engineer.

2. The power test database management and analysis method according to claim 1, characterized in that: The steps for obtaining the data access frequency and response time record are as follows: calling various test data stored in the power test database, counting the number of query operations for each data column, extracting the query timestamp in combination with the log information of the access request, counting the total number of queries for each data column within a set time range, and generating the query frequency for each data column; Based on the query frequency of each data column, the response time record corresponding to each data column being accessed is called through the database background log, the total response time generated by all access requests for the same data column is calculated and divided by the total number of queries, and the mean response time of each data column is counted and calculated to obtain the average response time of each data column; Based on the query frequency of each data column and the average response time of each data column, the frequency value and the average response time value of each data column are called one by one, and the two data contents are associated with each other using the data column name as a unique identifier to generate data access frequency and response time records.

3. The method for managing and analyzing the electric power test database according to claim 1, characterized in that: The steps for obtaining the index performance monitoring results are: based on the data access frequency and response time records, the mode stability value of each data column is calculated, and the calculation formula is: ； in, Representative The mode stability value of the data series, Representative The sum of all query response times for a data column, Representative The standard deviation of all query response times in a data column, Representative The time interval between two consecutive accesses to a data column is Representative The average interval between two consecutive visits to a data column, Representative The number of times a data column is accessed; Based on the pattern stability value of each data column, the index performance monitoring results are integrated and generated.

4. The method for managing and analyzing the electric power test database according to claim 1, characterized in that: The step of obtaining the inefficient index identification result is: according to the index performance monitoring result, calculating the data column index performance evaluation value, the calculation formula is: ； in, Representative The index performance evaluation value of the data column, For the The mode stability value of the data series, For the The query frequency of each data column, For the preset Data column efficiency threshold; Based on the index performance evaluation value of each data column, determine whether to mark it as an inefficient index and generate an inefficient index identification result.

5. The method for managing and analyzing the electric power test database according to claim 1, characterized in that: The steps of obtaining the index structure adjustment scheme are as follows: calling the inefficient index identification result, extracting the index tree structure corresponding to each inefficient index, analyzing the node distribution state and the relationship between nodes of the corresponding index tree, calculating the node branch balance and node density one by one, and generating a record of the inefficient index tree structure characteristic parameters; According to the inefficient index tree structure characteristic parameter record, the index structure reconstruction fitness value is calculated, and the calculation formula is: ； in, For the The index structure reconstruction fitness value of the inefficient index, For the The node branch balance of an inefficient index tree, For the The node density of an inefficient index tree, For the Number of inefficient index tree path conflicts, For the The number of redundant nodes in an inefficient index tree, For the The average number of scans of an inefficient index tree; According to the index structure reconstruction fitness value, an inefficient index tree is selected, structural parameters are rearranged and nodes are optimized and reconstructed, and an index structure adjustment plan is generated.

6. The method for managing and analyzing electric power test database according to claim 1, characterized in that: The steps of obtaining the index reconstruction execution record are: according to the index structure adjustment scheme, performing node reordering, redundant node removal and index depth adjustment of the index tree one by one, and recording the start and end time of each operation to generate an index reconstruction process execution time record; According to the execution time record of the index reconstruction process, real-time monitoring and recording of CPU usage, memory occupancy and I / O throughput changes during the index tree node rearrangement and redundant node removal process are performed to generate resource occupancy records of the index reconstruction process; According to the execution time record of the index reconstruction process and the resource usage record during the index reconstruction process, timestamp matching and resource call relationship correspondence are performed, and the execution time and maximum resource usage of a single index structure reconstruction process are counted to generate an index reconstruction execution record.

7. The method for managing and analyzing the electric power test database according to claim 1, characterized in that: The step of obtaining the index adjustment effect analysis result is: based on the index reconstruction execution record, calculating the index adjustment effect quantization value, the calculation formula is: ； in, For the The quantitative value of the index adjustment effect, For the The average response time after the index structure is reconstructed. For the The average response time before the index structure is rebuilt. For the The query success rate after the index structure is reconstructed. For the The query success rate before the index structure reconstruction, For the The database throughput after the index structure is reconstructed. For the The database throughput before the index structure is restructured; According to the index adjustment effect quantization value, sorting and grading are performed to generate an index adjustment effect analysis result.

8. The method for managing and analyzing a power test database according to claim 1, characterized in that: The steps of regularly updating the data access frequency and response time of the power test database according to the index adjustment effect analysis result are as follows: Call the index adjustment effect analysis result, count the query frequency and average response time after the index reconstruction one by one, and generate data access frequency and response time records after the index reconstruction; Based on the data access frequency and response time records after index reconstruction, count and mark the data columns that have not improved or have degraded performance after index reconstruction, record and notify the engineer.

9. The database management and analysis system of the power test database management and analysis method according to any one of claims 1 to 8, characterized in that: include: The data collection and frequency analysis module extracts test data from the power test database, calculates the query frequency and response time of each data column, and generates data access frequency and response time records; The index performance evaluation module uses data access frequency and response time records to evaluate the access pattern of each data column in the database, analyzes the performance monitoring results of each index, and forms index performance monitoring results; An inefficient index identification module compares the performance of each index in the index performance monitoring result with a preset efficiency threshold, identifies the index whose performance is lower than the threshold, and generates an inefficient index identification result; The index structure adjustment module adjusts the index structure identified as inefficient according to the inefficient index identification results, formulates an index structure adjustment plan, monitors resource usage and execution time during the index reconstruction process, completes the index reconstruction and records the process, and generates an index reconstruction execution record; The effect verification and analysis module uses the index reconstruction execution records to verify the effect of the index structure adjustment after reconstruction, compares the performance changes before and after the adjustment, counts the data columns that have not improved or whose performance has deteriorated after reconstruction, and sends notifications to engineers.

Images