A power test database management and analysis method and system

Abstract

The present invention relates to the technical field of database structures, and specifically to a power test database management and analysis method and system, which includes the following steps: collecting and organizing various test data in the power test database, generating data access frequency and response time records by calculating the query frequency and response time of each data column; evaluating the data access pattern based on the data access frequency and response time records, and generating an index performance monitoring result. 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 a pattern 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 control the resource usage situation and the adjustment effect feedback situation during the index adjustment process.

Description

Technical Field

[0001] The present invention relates to the technical field of database structure, and in particular to a method and system for managing and analyzing an electric power test database. Background Art

[0002] The technical field to which the power test database management and analysis method belongs is the field of database management technology, specifically involving technologies related to database performance optimization, index structure adjustment, data access pattern evaluation, and resource occupancy monitoring. By analyzing and adjusting a number of basic parameters such as the database's internal data storage structure, query response performance, data access frequency, and data processing efficiency, with database logging, structure reconstruction, performance monitoring, and resource management as specific execution contents, and utilizing the analysis results of system performance parameters, the database background index structure is dynamically adjusted to achieve automatic optimization of the database structure. In the process of adjusting the database index structure, the existing technology does not fully consider the specific structural characteristics, resulting in a lack of data support for the index optimization process, a high degree of blindness in the execution of index reconstruction, and reduced 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 shortcomings of the prior art and to propose a power test database management and analysis method and system.

[0004] In order to achieve the above objectives, the present invention adopts the following technical solution, a method for managing and analyzing a power test database, comprising the following steps:

[0005] 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;

[0006] Based on the index performance monitoring results, the performance of each index is compared with a preset efficiency threshold, indexes with performance below the efficiency threshold are identified, and an inefficient index identification result is generated; based on the inefficient index identification result, the structure of the index is adjusted to generate an index structure adjustment plan;

[0007] According to the index structure adjustment plan, the index reconstruction process is executed in the background of the power test database, the resource usage 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 a comparative analysis of the effects is performed to generate an index adjustment effect analysis result;

[0008] Based on the results of the index adjustment effect analysis, count and mark the data columns that have not been improved or have experienced performance degradation after the index reconstruction, record them, and notify the engineer.

[0009] 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;

[0010] Based on the query frequencies of the respective data columns, 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 then divide by the total number of queries, and count and calculate the average value of the response time of each data column to obtain the average response time of each data column;

[0011] Based on the query frequencies of the respective data columns and the average response times of the respective data columns, call the frequency value and the average response time value of each data column one by one, and associate the two data contents with the data column name as the unique identifier to generate the data access frequency and response time records.

[0012] 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:

[0013] ;

[0014] Among them, 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;

[0015] Based on the pattern stability values of the respective data columns, integrate and generate the index performance monitoring results.

[0016] 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:

[0017] ;

[0018] 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 query frequency of the th data column, is the preset efficiency threshold of the th data column;

[0019] 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.

[0020] Preferably, the steps for obtaining the index structure adjustment scheme are as follows: call the inefficient index identification result, 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 a record of the characteristic parameters of the inefficient index tree structure;

[0021] According to the record of the characteristic parameters of the inefficient index tree structure, calculate the index structure reconstruction adaptability value, and the calculation formula is:

[0022] ;

[0023] Wherein, 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;

[0024] Select an inefficient index tree according to the index structure reconstruction adaptability value, perform rearrangement of structure parameters and optimization and reconstruction of nodes, and generate an index structure adjustment scheme.

[0025] Preferably, the steps for obtaining the index reconstruction execution record are as follows: according to the index structure adjustment scheme, execute 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;

[0026] Execute time recording according to the index reconstruction process, and monitor and record the changes in CPU usage, memory occupancy, and I / O throughput during the rearrangement of index tree nodes and the clearing of redundant nodes in real time, and generate a resource occupancy record for the index reconstruction process;

[0027] 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, and generate an index reconstruction execution record.

[0028] Preferably, the steps for obtaining the index adjustment effect analysis result are as follows: Based on the index reconstruction execution record, calculate the index adjustment effect quantization value, and the calculation formula is:

[0029] ;

[0030] Among them, 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;

[0031] According to the index adjustment effect quantization value, perform sorting and grading to generate an index adjustment effect analysis result.

[0032] Preferably, the steps for regularly updating the data access frequency and response time of the power test database through the index adjustment effect analysis result are as follows:

[0033] Call the index adjustment effect analysis result, and count the query frequency and average response time after the index reconstruction of each data column one by one, and generate a data access frequency and response time record after the index reconstruction;

[0034] 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.

[0035] The present invention provides a database management analysis system, including:

[0036] A 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 records of data access frequency and response time;

[0037] An index performance evaluation module uses the records of data access frequency and response time to evaluate the access patterns of each data column in the database, analyzes the performance monitoring results of each index, and forms the index performance monitoring results;

[0038] An inefficient index identification module compares the performance of each index in the index performance monitoring results with a preset efficiency threshold, identifies the indexes with performance lower than the threshold, and generates the inefficient index identification results;

[0039] An 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 the resource occupancy and execution time during the index reconstruction process, completes the index reconstruction and records the process, and generates the index reconstruction execution record;

[0040] An effect verification and analysis module uses the index reconstruction execution record to verify the effect of the adjusted index structure after reconstruction, compares the performance changes before and after the adjustment, counts the data columns that have not been improved or have a performance decline after reconstruction, and sends a notification to the engineer.

[0041] Compared with the prior art, the advantages and positive effects of the present invention are as follows:

[0042] 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, the resource usage situation and the adjustment effect feedback situation during the index adjustment process are mastered, and the utilization efficiency and adjustment accuracy of the database resources are improved. Description of the Drawings

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

[0044] In order to make the purpose, technical solution 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.

[0045] Please refer to Figure 1 , the present invention provides a technical solution, a power test database management and analysis method, including the following steps:

[0046] Collect and organize various test data in the power test database. By calculating the query frequency and response time of each data column, generate records of data access frequency and response time; Based on the records of data access frequency and response time, evaluate the data access pattern and generate the index performance monitoring results;

[0047] 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 detailed classification by specialty and test type), creating specific test items (such as power transformers) and registering 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 basic information entry, calibration plan and reminder), the creation of test tasks (automatically loading information and parameter forms based on the selected test item and assigning executors), the 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 the test. The entire database system supports general operations such as adding, editing, deleting, submitting, uploading attachments, batch import and export, querying, and time recording.

[0048] According to 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 the index structure adjustment plan;

[0049] 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 the index reconstruction execution record; Based on the index reconstruction execution record, verify the adjustment effect of the index structure, conduct effect comparison analysis, and generate the index adjustment effect analysis results;

[0050] 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.

[0051] The steps for obtaining data access frequency and response time records are as follows: call the test data stored in the power test database, count the number of query operations for each data column, extract the query timestamp based on the access request log information, count the total number of queries for each data column within the set time range, and generate the query frequency for each data column;

[0052] Based on the query frequency of each data column, the database backend log is used to call the corresponding response time record when each data column is accessed. The response time generated by all access requests for the same data column is calculated and divided by the total number of queries. The mean response time of each data column is calculated and the average response time of each data column is obtained.

[0053] Based on the query frequency of each data column and the average response time of each data column, the frequency value and 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.

[0054] Specifically, the test data stored in the power test database is called. During the processing, the access location of each data record is sorted and the corresponding data column name is confirmed. Then, based on the internally set reference threshold, it is decided whether a data column should be regarded as an object that needs to be focused on. The reference threshold can be determined by adding twice the standard deviation to the average number of query operations for all data columns in the past week. Assume that the threshold calculated in the example is When the query times of a data column is 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. Then, a target time period, such as 168 consecutive hours, is selected as the statistical range. The specific number of times each data column is accessed during this time period is accumulated. During this period, the log information of the access request is parsed, and the timestamp containing year, month, day, hour, minute, and second is extracted to record the precise time of each access. Any access records in special circumstances, such as empty queries or abnormal requests, are marked and removed. Finally, the total number of accesses to each data column in the time period is summarized and corresponds to its category to generate the query frequency of each data column.

[0055] Based on the query frequency of each data column, the response time information corresponding to each access is compared item by item in the database background log. In the specific operation, each access request is matched with its response time. The time taken for a single query can be calculated by recording the start time and completion time of the query statement executed by the server. Assuming that in order to more accurately determine the response time, it is necessary to exclude the fluctuation caused by network delay. A delay correction value can be set in advance based on the historical average delay between the local and server. , when the measured query time exceeds a certain preset reference value it can be considered that there is an abnormal response. The 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, they are first marked and then reconfirmed. Finally, by accumulating the durations of all normal requests and dividing by the corresponding total number of queries, the average response time of each data column can be obtained, and the average response time of each data column is obtained.

[0056] Based on the query frequency of each data column and the average response time of each data column, the frequency value corresponding to each data column and the previously calculated average response duration can be read item by item. In actual execution, a comparison table containing the data column identifier and the two indicators needs to be established. This comparison table can be corresponding through the unique name or index allocated within the system. Then, a one-to-one logical association is made between the frequency value and the average response duration. If it is necessary to distinguish different access modes, 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 duration is 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.

[0057] 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:

[0058] ;

[0059] Among them, 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;

[0060] Integrate and generate index performance monitoring results based on the pattern stability values of each data column.

[0061] Specifically, the advantage of the formula lies in generating a comprehensive value that reflects the access stability characteristics of the data column by combining various information such as the dispersion of response time, the number of accesses, and the adjacent access time intervals. It takes into account the query concentration and interval rules, facilitating the evaluation of the access pattern of the corresponding data column based on this value in subsequent steps.

[0062] The acquisition steps of are as follows: First, separate all query records corresponding to the data column from the monitored access logs, then integrate them in chronological order, and sum up all response durations in sequence to form an accumulated value. In this process, the entries with abnormal timeouts in the access logs 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 for the data column forms milliseconds. If the accumulated effective access response time of the data column within a week is 14800 milliseconds, it is recorded as milliseconds. If within a week, the accumulated effective access response time of the data column is 14800 milliseconds, it is recorded as milliseconds.

[0063] The acquisition steps of are as follows: After obtaining the response time series of all effective requests for the data column, the standard deviation operation needs to be performed on this sequence, that is, first calculate the deviation of each response duration from the average response duration separately, and accumulate the squares of the deviations, then divide by the result of the number of effective requests minus 1, and finally take the square root to obtain the standard deviation. For example, for the aforementioned data column, if there are 93 response durations of effective requests, forming a sequence in milliseconds, its average value can be recorded 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 recorded as . milliseconds, and finally this result is recorded as .

[0064] The acquisition steps are as follows: For the records of the time intervals between two adjacent accesses of the th data column, which are sourced from the access logs within the same time span, it is necessary to retrieve the timestamps of each access in sequence according to the access order, calculate the time difference from the previous access, then collect all the adjacent interval values into a list, and then, according to requirements, the most common interval value can be selected or a certain number of weighted calculations can be performed to obtain the specific value. When the access pattern is relatively concentrated, most of the 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 can be recorded as 32 seconds. If the system requires weighting of the interval values, a weight factor needs to be set in advance to distinguish the access characteristics during the day or at night. This weight factor can be extracted from the frequency distribution of access time periods over 60 consecutive days, and finally, the = stable output value is obtained. For example, in the monitoring process, the access time periods are counted in segments of 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 [[ID=!]]= .

[0065] The acquisition steps are as follows: This parameter represents the average interval time between two adjacent accesses of the th data column. After obtaining all the adjacent access time differences, a simple average calculation can be performed first, or fluctuations within a small range can be excluded in the intermediate steps. For example, it is statistically found that a total of 95 access requests for the th data column are retrieved 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 all the access intervals is 2525 seconds, then .

[0066] The acquisition steps 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 the query statements. For example, when the system statistics show that the th data column has been accessed 314 times within the same week, then record .

[0067] Calculation process:

[0068] Substitute the parameter values obtained above into this formula for calculation. Taking a specific example, assume , , , , , first calculate , then calculate , multiplying the two gives , taking the absolute value is still 1,182,847.12, and then performing the cube root operation , finally divide by , that is , so for this example, we can get .

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

[0070] Based on the pattern stability value of each data column, the corresponding values of each data column are first integrated and filled into a centrally managed reference table in the aggregation stage. The reference table needs to be accompanied by a detailed index name for each data column and related data such as the access frequency and average response time obtained previously. In order to achieve a complete integration process, the identifiers of all data columns and their status tags in the system can be read first on the basis of uniformly collecting the pattern stability values. At the same time, a special field is set up in the reference table to place the pattern stability value, thereby forming a multi-column parallel data set. In this process, an upper and lower range can be set to determine the relative interval of the pattern stability value. For example, 0.0 to 0.5 is defined as a low stability interval, 0.5 to 1.5 is defined as a medium stability interval, and 1.5 to 3.0 is defined as a high stability interval. The specific values of the upper and lower ranges can be evaluated in combination with the statistical results of the past three months, for example By accumulating at least 10,000 query records, the global pattern stability value distribution is calculated, and the numerical segment with the largest proportion is found and expanded. When the pattern stability value of a data column is detected to be greater than 3.0, it can be individually identified and placed in a special management queue, because in actual analysis, it often corresponds to a unique distribution of access time intervals or response times. After that, all identified pattern stability values are matched one by one with access frequencies. For those data columns that are both in a high stability range and have an access frequency exceeding a certain threshold, the detailed request records and timestamps under the data column name can be further retrieved and compared with the interval range of 0 seconds to 72 hours as required. For data columns with low stability values, the focus can be on checking whether access intervals that are too short or too long occur in a concentrated manner. Finally, after all comparisons are completed, the related information is integrated into a summary record, which is merged into the specified location of the system to generate index performance monitoring results.

[0071] The steps to obtain the inefficient index identification results are as follows: Based on the index performance monitoring results, calculate the data column index performance evaluation value. The calculation formula is:

[0072] ;

[0073] 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;

[0074] 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.

[0075] Specifically, the advantage of the formula lies in integrating factors such as the pattern stability value of the data column and the query frequency, concentrating the impacts of various factors on the index performance within a quantifiable evaluation value for comparison. After sorting the data columns based on this evaluation value, the pros and cons of the index performance can be intuitively displayed, facilitating the further judgment of inefficient indexes and corresponding processing in subsequent steps.

[0076] The obtaining step of is as follows: This parameter represents the pattern stability value of the th data column, which is used to depict 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. The obtaining step of is as follows: This parameter represents the pattern stability value of the th data column, which is used to depict the distribution of the data column access interval and response time within a specific period, and it is sourced from the results already calculated in the previous step.

[0077] The obtaining step of is as follows: This parameter is the preset efficiency threshold of the th data column, indicating 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: The obtaining step of is as follows: 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 after calculating all the query-related data, obtain a comprehensive score value to form the specific value of .

[0078] The obtaining step of is as follows: This parameter refers to the query frequency of the th data column, which is mainly reflected in the number of times this data column is accessed within a certain time window. When obtaining it, first retrieve all query statements in the database log, filter out the requests for this data column, and count the total access volume. If it is found that some automatic scripts initiate queries frequently, they are also included. Then divide the statistically obtained access volume 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 . .

[0079] Calculation process:

[0080] Substitute it into the formula for calculation. For example, for a certain data column, the previously calculated , , , then the absolute value of the difference between the two can be calculated first as and , and the result is . After that, calculate , and then perform a multiplication operation to get . For the denominator part , and finally calculate , that is, obtain .

[0081] 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, thus providing an auxiliary reference for judging whether the index of the data column needs to be adjusted in the subsequent steps.

[0082] 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, it can first be checked whether there are necessary conditions to meet the subsequent calculation requirements. For example, check whether the previous monitoring session covers a complete duration, such as taking a two - week cycle as the statistical basis, to ensure that each data column has sufficient access counts to support subsequent evaluations. When it is confirmed that the data is complete, aggregate analysis is performed according to 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 multi - segment intervals. For example, label as no obvious deviation in the interval from 0.0 to 0.3, label as moderately deviated in the interval from 0.3 to 0.7, label as highly deviated in the interval from 0.7 to 1.0, and mark separately as strongly deviated when exceeding 1.0. At the same time, according to this grouping result, assign each data column to the corresponding set and summarize the list. Then, combined with other information records such as the index tree level, query success rate, or response delay 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, their corresponding query time distributions can be traced item by item and check whether there is an abnormal surge in requests within the range from 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, judge 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.

[0083] The steps for obtaining the index structure adjustment plan are as follows: Call the inefficient index recognition result, 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 a record of the characteristic parameters of the inefficient index tree structure;

[0084] According to the record of the characteristic parameters of the inefficient index tree structure, calculate the index structure reconstruction adaptability value. The calculation formula is:

[0085] ;

[0086] where, 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;

[0087] 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 plan.

[0088] Specifically, call the inefficient index recognition results, 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 each node 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 status 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 in each branch can be compared from 1 to 20. If it exceeds 20, it is considered that the branch is congested, and if it is less than 1, it is considered that the branch is vacant. 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 a maximum of 30 keywords. If the statistical results show that the number of keywords in the node is close to 30 multiple times, it is recorded as high density. If the number of keywords is concentrated between 10 and 20, it is recorded as medium density, and if it is less than 10, it is recorded as low density. Finally, integrate parameters such as node branch balance and node density to generate a record of the structural feature parameters of the inefficient index tree.

[0089] The benefit of the formula is that it simultaneously introduces multiple important elements such as node branch balance, node density, the number of path conflicts, and the number of redundant nodes. It differentially amplifies or reduces each influencing dimension by using exponentiation and cube roots, and combines the influence brought by the average scan times at the denominator, thereby comprehensively measuring the importance degree of index tree reconstruction in one expression.

[0090] The acquisition steps are as follows: This parameter represents the node branch balance of the th inefficient index tree. It is necessary to first count the number of branches of all nodes in the index tree structure and judge whether their distribution in the entire tree is balanced. The specific method is to record the number of branches of each node, compare the distribution of the number of branches in the entire tree range, and then use the median or mean as the central reference, calculate the dispersion from this reference value to obtain a preliminary score of branch balance, and then map the score to a more refined scale to form the final value. For example, when scanning an index tree layer by layer and collecting the number of branches of all nodes, the distribution of the number of branches obtained is , where the average value is approximately 3.625. Then, the deviation of the number of branches of each node from 3.625 is calculated. The degree of dispersion is statistically analyzed based on the deviation range between -1.625 and 1.375. A balance quantization value is obtained by summarizing the variance or absolute deviation. After comparing similar data of more than 100 index trees, this quantization value is linearly transformed to [0, 1] to obtain .

[0091] The steps to obtain are: This parameter represents the The node density of an inefficient index tree is used to measure the compactness of the nodes in the tree in terms of actual storage 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 to obtain a utilization rate. For example, in a type of B+ tree index structure, if a node at a certain level can accommodate a maximum of 30 keywords, and it is found that there are an average of 25 keywords in the current node, then the utilization rate of the node can be calculated as .

[0092] The steps to obtain are: This parameter represents the The number of path conflicts in an inefficient index tree refers to the cumulative record of conflicts between nodes when establishing or querying paths. It can be obtained by replaying each query path to see if there are redundant jumps or repeated backtracking at the same level, or by observing the situation where the node pointer is occupied, and then counting 1 for each conflict event. Finally, the sum of all conflict events is obtained. ; Jumping back to the same node twice or more is considered a conflict. Index pointer circular pointing is also recorded as a conflict. After counting 200 queries of an index tree in the system, it is found that there are 12 repeated backtracking paths and 2 additional circular pointings are recorded. .

[0093] The steps to obtain are: This parameter is To determine the number of redundant nodes in an inefficient index tree, we need to examine whether there are invalid or idle nodes from a structural perspective. During the recording process, we first list all the nodes and confirm whether they can actually bear the keyword storage or index pointer reference. We count irrelevant nodes as redundant. We also need to search for nodes with too many branches or almost no keywords at deep levels, merge these nodes into a set, and finally count the total number of the set to obtain For example, in an index tree structure, 14 nodes are counted, of which 3 nodes have only one keyword and no branch extension, and there is another completely empty node. If they are all considered redundant nodes, then .

[0094] The acquisition steps 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, 300 queries are performed, and the number of nodes passed through each retrieval is recorded . Assuming the total value obtained after summation is 1440, then .

[0095] Calculation process:

[0096] Combined with the aforementioned parameters, substitute them into for calculation. For example, substitute , , , , . First, calculate = . Then, take the square root of it . After that, calculate = 10, take its cube root . Multiply the two to get . The denominator part is . Finally, calculate the overall to obtain .

[0097] This result indicates that in the current example, the index structure reconstruction fitness value is at the level of 0.261. This value, combined with other comparison data, can reflect the status of the 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 structure reconstruction. If it is lower than 0.3, it means that the current conflict and redundancy situation is not too serious. This provides a basis for locating and rearranging nodes and optimizing the index level in the subsequent steps.

[0098] Reconstruct the adaptation degree 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. Then combine all the marked nodes into an optimization processing pool, and analyze whether the associated 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. First, perform branch merging or pointer reallocation on the nodes involving a high number of conflicts. 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 limit 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.

[0099] 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;

[0100] According to the execution time record of the index reconstruction process, monitor and record the changes in CPU usage, memory occupancy, 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;

[0101] 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.

[0102] 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 are required. This threshold can be obtained by referring to the statistical results of one hundred index trees. For example, after collecting the node branch number distributions 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 a 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 statistical level distribution value. When it is detected that the index depth is higher than a certain fixed standard, trigger a 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 a record of the execution time of the index reconstruction process.

[0103] 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% is the acceptable range, 60% to 85% is the tight range, and exceeding 85% is recorded 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 in half a year of operation is 35% and the average memory occupancy rate is 50%, 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, the peak period of each rearrangement operation needs to be matched 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 500 MB / s, it is marked as a concern point. During the whole process, the matched CPU usage rate, memory occupancy rate, and I / O throughput values need to be continuously recorded, and at the end, the occupancy data of each period is aggregated into a single record block and written into the resource occupancy record of the index reconstruction process.

[0104] 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 5 minutes is selected as an intermediate reference value. Finally, merge all the records of operations and the highest occupancy values to form the index reconstruction execution record.

[0105] The steps to obtain 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:

[0106] ;

[0107] Among them, 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;

[0108] According to the quantization value of index adjustment effects, perform sorting and grading to generate the analysis results of index adjustment effects.

[0109] Specifically, The steps to obtain 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 value 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 during network disconnection or severe CPU impact, 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 reconstruction. After data cleaning, 3980 valid entries were retained. The total response duration of all valid entries was 358900 milliseconds, then .

[0110] The steps to obtain are as follows: This parameter is the The average response time before 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. It is also necessary to 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, according to the same weekly cycle length as before, and calculate the average value after excluding abnormal accesses, then sum and divide by the number of accesses. For example, if there are 4120 valid queries before the index change and the total response duration is about 382400 milliseconds, then 。

[0111] 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 normal query return results within a certain period after the reconstruction is completed and the system 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 is observed that there are 3980 queries, and 3955 of them return normal results, then 。

[0112] 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 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 are successful queries, then 。

[0113] 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 query completions 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 are completed during the one-week observation period after the reconstruction, the throughput can be written as 。

[0114] 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 divide it by the number of hours to obtain the average throughput. For example, if 4120 queries were successfully completed within 7 days before the change, then .

[0115] Calculation process:

[0116] 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 . Thus, we can obtain .

[0117] This result indicates that in this instance , the value is used to indicate the comprehensive change degree of the response time, success rate, and throughput after the index reconstruction compared to before. When is close to or greater than 1, it indicates that the difference is relatively obvious. When is lower than 0.01, it means that the changes in relevant indicators are limited. We can compare the sizes or perform grading based on this value to further promote the analysis of the index adjustment effect.

[0118] Adjust the effect quantization value according to the index. After extracting the key indicators 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 indicators such as the previously recorded average response time and query success rate correspond to this 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 indicators. If the quantization value of an individual index is always close to 0.10 in the fine-tuning grading, then in the later stage, it can be further concerned whether this index has volatility indicators. If there are indexes with a high concentration in the large-tuning or extremely changed grading, then re-trace its original query log to check whether there is a situation of too long access delay in the interval of 0 seconds to 10 seconds, or whether there are obvious periodic fluctuations in the success rate. In this way, it 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 listed 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.

[0119] The steps to regularly update the data access frequency and response time of the power test database through the analysis result of the index adjustment effect are as follows:

[0120] 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;

[0121] 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.

[0122] 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 counted 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 this statistic 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 and add up 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 large 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, that is, 180 milliseconds, can be used as an upper limit of an 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, when the itemized statistics of the total number of queries and the cumulative response duration of all data columns within the specified period are completed, organize the obtained query frequencies and average response times with the data columns as identifiers to generate the data access frequency and response time records after index reconstruction.

[0123] 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 means and standard deviations. 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 the response duration is significantly higher than the original mean, it is marked as suspected performance degradation. When marking performance degradation, a threshold can be considered, such as at 1.2 times or 1.5 times the original mean for demarcation. The specific multiple can be determined by the test data during 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 can be 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, recording and notifying the engineer.

[0124] The above are only the preferred embodiments of the present invention and do not 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 an electric power test database, characterized in that, Including the following steps: Collect and organize various test data in the power test database. By calculating the query frequency and response time of each data column, generate records of data access frequency and response time; Based on the records of data access frequency and response time, evaluate the data access pattern and generate index performance monitoring results; According to 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 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, and 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 performance degradation after index reconstruction, record and notify the engineer; The steps for obtaining the index performance monitoring results are: Based on the records of data access frequency and response time, calculate the mode stability value of each data column, and the calculation formula is: ； Among them, 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 mode stability values of each data column, integrate and generate index performance monitoring results.

2. The power test database management and analysis method according to claim 1, characterized in that, The steps for obtaining the records of data access frequency and response time are: Call the various test data stored in the power test database, count the number of query operations for each data column, extract the time stamps 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 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-consuming 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 then 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, call the frequency value and the average response time value of each data column one by one, and associate the two data contents with the data column name as the only identifier to generate records of data access frequency and response time.

3. The power test database management and analysis method according to claim 1, characterized in that, The steps for obtaining the inefficient index identification results are: According to the index performance monitoring results, calculate the index performance evaluation value of the data column, and 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 query frequency of the th data column, is the efficiency threshold of the th preset data column; Based on the index performance evaluation values of each data column, determine whether it is marked as an inefficient index to generate inefficient index identification results.

4. The power test database management and analysis method according to claim 1, characterized in that The steps for obtaining the index structure adjustment plan are: Call the inefficient index identification results, 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 records of inefficient index tree structure characteristic parameters; According to the records of 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 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; Reconstruct an inefficient index tree according to the fitness value of the index structure reconstruction, perform structural parameter rearrangement and node optimization reconstruction, and generate an index structure adjustment plan.

5. The power test database management and analysis method according to claim 1, characterized in that, The steps for obtaining the execution record of the 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 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, memory occupancy, 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. Based on 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 maximum resource occupancy of a single index structure reconstruction process to generate an index reconstruction execution record.

6. The power test database management and analysis method according to claim 1, characterized in that The steps for obtaining the analysis result of the index adjustment effect are as follows: Based on the index reconstruction execution record, calculate the index adjustment effect quantization value, and 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 is reconstructed, For the The database throughput after the index structure is reconstructed, For the Database throughput before index structure reconstruction; According to the index adjustment effect quantization value, perform sorting and grading to generate an index adjustment effect analysis result.

7. The power test database management and analysis method according to claim 1, wherein The steps for periodically updating the data access frequency and response time of the power test database through 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 of each data column one by one, and generate a 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 a performance decline after the index reconstruction, and record and notify the engineer.

8. The database management and analysis system of the power test database management and analysis method according to any one of claims 1-7, characterized in that Including: A 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 a data access frequency and response time record. An index performance evaluation module 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 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. An index structure adjustment module adjusts the index structure identified as inefficient according to the inefficient index identification result, formulates an index structure adjustment plan, monitors the resource occupancy and execution time during the index reconstruction process, completes the reconstruction of the index and records the process, and generates an index reconstruction execution record. An effect verification and analysis module uses the index reconstruction execution record to verify the index structure adjustment effect after the reconstruction, compares the performance changes before and after the adjustment, counts the data columns that have not been improved or have a performance decline after the reconstruction, and sends a notice to the engineer.

Images