A sop database index optimization method and system

Abstract

This invention provides a SOP database index optimization method and system, belonging to the field of database management technology. The method comprises the following steps: First, historical query logs of the database are collected and query fields are extracted. Data table distribution characteristics are analyzed, and a mapping library relating query patterns to data characteristics is established. Next, based on the mapping library, the contribution weight of fields is quantitatively evaluated. Combined with query execution frequency and response time, a priority sequence of indexes to be created is generated. Existing and candidate indexes are matched, and the overlap of field coverage and the database load generated by index maintenance are analyzed to identify redundant indexes. A prediction model is trained based on historical query performance indicators. Performance gain prediction is performed on high-priority index schemes. After index changes are executed, actual performance indicators are collected and compared with the prediction results. The model parameters are adjusted through a feedback loop mechanism, iteratively outputting the optimal index configuration scheme that matches the current database workload characteristics.

Description

Technical Field

[0001] This invention relates to the field of database management technology, specifically to a SOP database index optimization method and system. Background Technology

[0002] In today's enterprise applications and big data environments, databases, as core data storage and processing components, directly impact the responsiveness of the entire business and the user experience. Database indexes are one of the most critical technical means to improve query performance; however, the creation, maintenance, and optimization of indexes are complex and highly challenging tasks. Traditional optimization methods largely rely on the personal experience of database administrators (DBAs), manually analyzing slow query logs, monitoring system performance metrics to identify potential optimization points, and manually executing index adjustment commands.

[0003] However, this highly manual, discrete operation mode has inherent technical flaws: on the one hand, it is extremely inefficient and struggles to cope with the real-time performance demands of massive data and complex business scenarios, and the human resource costs of senior DBAs are high; on the other hand, due to the lack of unified standard operating procedures (SOPs), different administrators have different optimization strategies and operating habits, resulting in inconsistent optimization results that are difficult to replicate and predict, while manual operation also introduces a high risk of misoperation; more importantly, most existing automation tools only provide optimization suggestions and fail to link analysis, decision-making, execution, and effect verification into a complete closed-loop process, resulting in a lack of continuous feedback and self-evolution capabilities in the optimization process, and failing to fundamentally achieve adaptive and intelligent management of index resources. Summary of the Invention

[0004] The main objective of this invention is to provide a method and system for optimizing SOP database indexes, thereby solving the problems mentioned in the background section.

[0005] To solve the above-mentioned technical problems, the technical solution adopted by the present invention is to provide a SOP database index optimization method, comprising the following steps: S10. Collect historical query logs from the database, extract the filter fields, sorting fields, and related fields from the query conditions, and analyze the data distribution characteristics of the corresponding data tables to establish a mapping library between query patterns and data characteristics. S20. Based on the association mapping library, quantitatively evaluate the contribution weight of each field in query performance optimization, and generate a priority sequence of indexes to be created by combining the execution frequency and response time of the query statement. S30. Perform pattern matching analysis between the existing index structure and the candidate indexes in the priority sequence to analyze the field coverage overlap and the database load generated by index maintenance, and automatically identify and mark redundant indexes with functional overlap. S40. Train a prediction model based on historical query performance metrics, predict the performance gain of high-priority index schemes, collect actual performance metrics after index changes are executed, compare and analyze the prediction results, adjust model parameters through a feedback loop mechanism, and iteratively output the optimal index configuration scheme that matches the current database workload characteristics.

[0006] Furthermore, the detailed process of step S10 is as follows: S11. Start the database query log collection process to obtain historical query logs of the target database instance within a set period; clean the collected log data, filter out non-business query statements, and retain valid query statements containing select, update, and delete operations; extract the structured information of the valid query statements through the SQL parser; at the same time, normalize the parameterized queries, replace the specific parameter values ​​with uniform placeholders to form standardized query templates, and record the execution frequency, average response time, and peak response time of each query template. S12. Extract features from all data tables involved in the query log, collect the basic features of each data table, and analyze the data distribution characteristics of each field; for existing indexes in the data tables, calculate the selectivity index of the index fields in combination with the field data distribution characteristics; at the same time, identify fields with skewed value distribution based on the proportion of field values. When the proportion of a certain value or a group of values ​​in a field exceeds a preset threshold, the field is a field with skewed value distribution. The basic features include: table size features and field attribute features; among which, table size features include: total number of records, data file space occupied, and average daily data growth; field attribute features include: data type of the field, whether it is a primary key, whether it is a foreign key, and whether null values ​​are allowed. The data distribution characteristic analysis process is as follows: For discrete fields, calculate the frequency of occurrence of different values ​​and the number of duplicate values; for continuous fields, determine the distribution range and density distribution characteristics of the values; for fields with update operations, count the update frequency per unit time. S13. Based on the query pattern information and data feature information extracted above, construct an association mapping library; use a structured storage engine to store the association mapping library; set up a timed update mechanism to re-collect query logs and data features according to a preset cycle and incrementally update the association mapping library; The association mapping library uses standardized query templates as the core index to establish the association between query templates and the data tables, field types, field data characteristics, and query execution metrics involved. The storage structure of a storage engine includes a unique identifier for the query template, the name of the associated data table, field names, field types, data selectivity, average daily query count, and average response time.

[0007] Furthermore, the detailed process of step S20 is as follows: S21. Based on the data in the association mapping library, construct a field contribution evaluation model, and calculate the basic weight value from three dimensions for each field involved in the query template: The first dimension is query dependency, which represents the percentage of times a field appears in the query template as a filter field, sorting field, or related field. The second dimension is the performance impact, which is determined by analyzing the ratio of the difference between the query response time and the preset benchmark value when the index for this field is missing. The third dimension is data fit, which is calculated by combining field selectivity indicators and value distribution characteristics. First, the field selectivity indicators are mapped to a preset range as the basic fit score; then, a distribution correction coefficient is set according to the value distribution characteristics; the data fit value is calculated based on the distribution correction coefficient, and the data fit value is normalized to the range of 0 to 1 to obtain the data fit. The expression for data fit is as follows: (1); in, For data adaptation, Based on the adaptation score, This is the distribution correction factor; The basic weight values ​​of the above three dimensions are weighted according to preset weight coefficients to obtain the initial contribution weight of each field; the expression for the initial contribution weight is as follows: (2); in, The initial contribution weight for a certain field. For this field The values ​​of each dimension, For this field Preset weight coefficients for each dimension; S22. Dynamically adjust the initial contribution weight based on the execution metrics of the query statement. Generate candidate indexes according to the field combination rules based on the adjusted contribution weights. Calculate the comprehensive score of each candidate index. Sort the candidate indexes from high to low according to the comprehensive score to generate a priority sequence of indexes to be created. The indicator scenarios are specifically divided into two categories: The first category is high-frequency query template scenarios, which refers to query templates whose daily average number of queries exceeds a set threshold; the contribution weight of the fields involved is multiplied by a frequency adjustment coefficient; the frequency adjustment coefficient is a coefficient used to amplify the contribution weight of the fields involved in high-frequency query templates; The second category is query template scenarios with excessively long response times, which refers to query templates whose average response time exceeds a set threshold; the contribution weight of the fields involved is multiplied by a performance compensation coefficient; the performance compensation coefficient is a coefficient used to amplify the contribution weight of the fields involved in query templates with excessively long response times; The formula for the overall score is as follows: (3) in, A comprehensive score for a candidate index. For this candidate index Adjusted contribution weights for each field For query frequency weighting, To achieve the expected performance improvement, This represents the total number of fields contained in the candidate index. The query frequency weight is related to the frequency of query template execution; the expected performance improvement value represents the estimated degree of performance improvement after the candidate index is created. Candidate indexes can be categorized into single-field indexes and composite indexes based on the number of fields; composite indexes are sorted in descending order of field contribution weight. The priority sequence includes: the field combination of the candidate index, the estimated performance improvement, and the range of applicable query templates.

[0008] Furthermore, the detailed process of step S30 is as follows: S31. Read the existing index structure data from the database system dictionary table and extract the core features of each existing index; at the same time, read the candidate indexes from the priority sequence generated in step S20 and extract their core features; construct a pattern matching algorithm based on field coverage relationship and define field coverage overlap as the core matching index; if the field coverage overlap is not lower than the preset overlap threshold and the index types and inclusion field attributes of the two are consistent, then mark the index pair as a potential functional overlap set; if the existing index field combination is a subset of the candidate index field combination and the inclusion field attributes are consistent, then further mark the existing index as a potential redundant front index of the candidate index; Key features include: the combination of indexed fields, the index type, and whether it contains inclusive fields; Inclusion fields refer to fields in the index that are included in addition to the index key fields and are used to directly return query results; S32. Evaluate the load from three aspects: disk I / O increment, CPU usage increment, and storage space usage. For index pairs with potentially overlapping functions, calculate the comprehensive load value according to the preset weight coefficient. If the comprehensive load value of the candidate index is lower than that of the existing index and can cover all the query templates adapted by the existing index, then mark the existing index as a functionally redundant index. If the existing index is a potential redundant preceding index of the candidate index and its comprehensive load value reaches or exceeds the preset ratio threshold of the comprehensive load value of the candidate index, then mark the existing index as a high-load redundant index. S33. Extract the usage records of potential redundant indexes in the most recent period, identify redundant indexes based on the usage records, and finally generate a redundant index marking report; Potentially redundant indexes refer to existing indexes that are included in a potentially functionally overlapping set or are marked as potentially redundant preceding indexes; The process for determining redundant indexes is as follows: If the average daily number of queries on a potential redundant index is lower than the preset low-frequency threshold and the query hit time is not lower than the estimated time of the candidate index, then the potential redundant index is determined to be a redundant index; if the number of queries on a potential redundant index is high but all compatible query templates can be covered by the candidate index and the estimated response time of the candidate index is better, then after query migration feasibility analysis, it is still marked as a redundant index. The redundant index marking report includes: the field structure of the redundant index, load comparison, migration suggestions for adapting query templates, and deletion priority.

[0009] Furthermore, the detailed process of step S40 is as follows: S41. Extract historical data from the association mapping library to construct a model training dataset; use the performance gain after index changes as the prediction target, and use query frequency, field selectivity, table data volume, and index field combination length as core input features; train the prediction model using gradient boosting regression algorithm, and optimize the model parameters through five-fold cross-validation. Historical data includes: query template execution frequency, average response time, index usage, and data table size characteristics for the corresponding time period. The expression for performance gain is as follows: (4); in, For performance gain, , These are the average response times before and after the change, respectively. S42. From the priority sequence generated in step S20, select the top N% of candidate indexes by comprehensive score, and generate an index change scheme set by grouping by data table; input the index features and historical performance indicators of the corresponding query template in each scheme into the trained prediction model, and output the estimated performance gain value of the scheme. And confidence interval; at the same time, combined with the load assessment results of step S30, if the overall load value increase due to index changes in the scheme does not exceed the load safety preset threshold and If the value is not less than the effective threshold for performance gain, then the solution is marked as a candidate solution to be executed; if there are multiple solutions for the same data table, then the solution with the largest ratio of the estimated performance gain value to the increase in the overall load value is selected as the optimal candidate. S43. Select a low-peak period for the database to execute the candidate solution. Before execution, back up the target table data using a database backup tool. During execution, monitor the database load in real time. If the load exceeds a preset safety threshold, pause the change and trigger a rollback process. After the change is completed, continuously collect actual performance data for a preset period and calculate the performance gain. ; predict input features, , The feedback dataset is composed of... and If the absolute value of the difference is greater than the prediction bias threshold, the feedback data is added to the model training set, the model parameters are readjusted, and one model iteration is completed; if and If the absolute value of the difference is not greater than the prediction bias threshold, the data will only be stored in the model sample library for subsequent periodic iterations. Actual performance data includes: the actual average response time and execution frequency of the query templates involved in the solution; S44. After each feedback loop is completed, based on the current database workload characteristics, steps S20-S40 are re-executed to generate a new round of index optimization schemes; the actual performance gain and overall load increase of the multiple rounds of schemes are compared. If two consecutive rounds of schemes... If the difference is no greater than 5% and the overall load value is stable, then the scheme is marked as the current optimal index configuration scheme and output to the database management system as the benchmark scheme for index maintenance. At the same time, a timed full iteration trigger mechanism is set to automatically re-collect the latest query logs and data features, update the association mapping library and prediction model, so that the optimal scheme always adapts to the dynamically changing workload of the database.

[0010] This invention also provides a SOP database index optimization system to implement the steps in the above method. The system includes: The feature association analysis module is used to collect historical query logs from the database, extract filter fields, sorting fields and association fields from the query conditions, and analyze the data distribution characteristics of the corresponding data tables to establish an association mapping library between the two. The index priority evaluation module is used to quantify the contribution weight of each field in query performance optimization, and generate a priority sequence of indexes to be created by combining the execution frequency and response time of the query statement. The redundant index identification module is used to perform pattern matching analysis between the existing index structure and the candidate indexes in the priority sequence, analyze the field coverage overlap and the database load generated by index maintenance, and automatically identify and mark redundant indexes with functional overlap. The index scheme optimization module is used to predict the performance gain of high-priority index schemes. After the index changes are executed, the actual performance indicators are collected and compared with the prediction results. The model parameters are adjusted through a feedback loop mechanism, and the optimal index configuration scheme that matches the current database workload characteristics is iteratively output.

[0011] Furthermore, the feature association analysis module includes: a query log analysis unit, a data feature extraction unit, and an association mapping library construction unit; The query log analysis unit collects historical query logs from the target database instance within a specified period, filters out non-business query statements, and retains valid query statements. It extracts structured information through an SQL parser, normalizes parameterized queries to form standardized query templates, and records the execution frequency, average response time, and peak response time of each query template. The data feature extraction unit extracts features from the data tables involved in the query log, collects table size features and field attribute features, analyzes field data distribution features, calculates the frequency of occurrence and number of duplicate values ​​of discrete fields, the distribution range and density features of continuous fields, counts field update frequency, calculates index field selectivity index and identifies fields with skewed value distribution. The association mapping library construction unit builds the association mapping library based on query pattern information and data feature information. It uses standardized query templates as the core index to establish the association relationship between query templates and data tables, field types, data features, and execution indicators. It uses a structured storage engine to store the database, which includes fields such as the unique identifier of the query template and the name of the associated data table. The association mapping library is incrementally updated through a timed update mechanism.

[0012] Furthermore, the index priority evaluation module includes: a field contribution calculation unit, a weight dynamic adjustment unit, and a candidate index sorting unit; The field contribution calculation unit builds an evaluation model based on the data in the association mapping library. It calculates the basic weight values ​​from three dimensions: query dependency, performance impact, and data adaptability. The initial contribution weight is obtained by weighting the values ​​with preset weight coefficients. The dynamic weight adjustment unit adjusts the initial weights based on query execution metrics. Fields involved in high-frequency query templates are multiplied by a frequency adjustment coefficient, and fields involved in query templates with extremely long response times are multiplied by a performance compensation coefficient to obtain the adjusted contribution weights. The candidate index sorting unit generates single-field indexes and composite indexes according to field combination rules, calculates a comprehensive score, and generates a priority sequence of indexes to be created based on the score.

[0013] Furthermore, the redundant index identification module includes: an index feature analysis unit, a pattern matching and load assessment unit, and a redundancy verification and report generation unit; The index feature analysis unit reads existing index structure data from the database system dictionary table and extracts core features such as index field combinations, types, and inclusion fields; it also extracts similar features from candidate indexes in the priority sequence. The pattern matching and load assessment unit constructs a pattern matching algorithm based on field coverage relationships, calculates field coverage overlap, marks potential functional overlap sets and potential redundant front indexes; assesses the load from three dimensions: disk I / O increment, CPU usage increment, and storage space usage; calculates the comprehensive load value according to preset weight coefficients; and identifies functionally redundant indexes and high-load redundant indexes. The redundancy verification and report generation unit extracts usage records of potential redundant indexes within a preset period, verifies redundancy attributes by combining query frequency, hit duration, and migration feasibility analysis, and generates a redundant index marking report.

[0014] Furthermore, the indexing scheme optimization module includes: a prediction model training unit, a scheme selection and execution unit, and a feedback iteration unit; The prediction model training unit extracts historical data from the association mapping library to construct a training set. It takes the performance gain after index change as the prediction target, selects core input features, uses gradient boosting regression algorithm to train the model, and optimizes parameters through five-fold cross-validation. The scheme selection and execution unit selects candidate indexes with high comprehensive scores from the priority sequence and generates a set of index change schemes in groups; it outputs the estimated performance gain and confidence interval through the prediction model, and selects the schemes to be executed in combination with load assessment; it executes the schemes during the database's off-peak hours, monitors the load during execution, and supports rollback. The feedback iteration unit collects actual performance data for a preset period after the change, calculates the actual performance gain and forms a feedback dataset; if the prediction deviation exceeds the threshold, the model parameters are updated and the iteration is completed; the performance of multiple rounds of schemes is compared and the optimal configuration scheme is finally output.

[0015] Beneficial effects: (1) This invention constructs a query pattern and data feature association mapping library, collects and parses query logs to extract field information and analyzes data table features, then quantifies and evaluates the contribution of fields and generates a priority sequence of indexes to be created in combination with query execution indicators. It can accurately locate high-value optimization fields, avoid blindly creating indexes, effectively improve the efficiency of indexes to query performance, reduce the system resources occupied by invalid indexes, make index optimization more in line with business query needs, and significantly reduce the cost of manually screening optimization objects; (2) This invention analyzes the field coverage overlap and database load of existing and candidate indexes through pattern matching, identifies redundant indexes, trains a prediction model based on historical data and combines iterative optimization schemes with feedback loops to output the optimal index configuration that adapts to dynamic loads. It can effectively clean up redundant indexes to reduce system maintenance pressure, and through continuous iteration to adapt to changes in business and data, it can effectively control the fluctuation of query response time, ensure database stability in high-concurrency scenarios, and greatly improve operation and maintenance efficiency and resource utilization. Attached Figure Description

[0016] The present invention will be further described below with reference to the accompanying drawings and embodiments: Figure 1 This is a flowchart of the steps of the present invention. Detailed Implementation

[0017] Example 1 like Figure 1 As shown, a SOP database index optimization method includes the following steps: S10. Collect historical query logs from the database, extract the filter fields, sorting fields, and correlation fields from the query conditions, and analyze the data distribution characteristics of the corresponding data tables to establish a correlation mapping library between query patterns and data characteristics. The detailed process of this step is as follows: S11. Start the database query log collection process to obtain historical query logs of the target database instance within a set period; clean the collected log data, filter out non-business query statements, and retain valid query statements containing select, update, and delete operations; extract the structured information of the valid query statements through the SQL parser; at the same time, normalize the parameterized queries, replace the specific parameter values ​​with uniform placeholders to form standardized query templates, and record the execution frequency, average response time, and peak response time of each query template. S12. Extract features from all data tables involved in the query log, collect the basic features of each data table, and analyze the data distribution characteristics of each field; for existing indexes in the data tables, calculate the selectivity index of the index fields in combination with the field data distribution characteristics; at the same time, identify fields with skewed value distribution based on the proportion of field values. When the proportion of a certain value or a group of values ​​in a field exceeds a preset threshold, the field is a field with skewed value distribution. The basic features include: table size features and field attribute features; among which, table size features include: total number of records, data file space occupied, and average daily data growth; field attribute features include: data type of the field, whether it is a primary key, whether it is a foreign key, and whether null values ​​are allowed. The data distribution characteristic analysis process is as follows: For discrete fields, calculate the frequency of occurrence of different values ​​and the number of duplicate values; for continuous fields, determine the distribution range and density distribution characteristics of the values; for fields with update operations, count the update frequency per unit time. S13. Based on the query pattern information and data feature information extracted above, construct an association mapping library; use a structured storage engine to store the association mapping library; set up a timed update mechanism to re-collect query logs and data features according to a preset cycle and incrementally update the association mapping library; The association mapping library uses standardized query templates as the core index to establish the association between query templates and the data tables, field types, field data characteristics, and query execution metrics involved. The storage structure of a storage engine includes a unique identifier for the query template, the name of the associated data table, field names, field types, data selectivity, average daily query count, and average response time.

[0018] S20. Based on the relational mapping library, quantitatively evaluate the contribution weight of each field in query performance optimization, and generate a priority sequence of indexes to be created by combining the execution frequency and response time of the query statement; the detailed process of this step is as follows: S21. Based on the data in the association mapping library, construct a field contribution evaluation model, and calculate the basic weight value from three dimensions for each field involved in the query template: The first dimension is query dependency, which represents the percentage of times a field appears in the query template as a filter field, sorting field, or related field. The second dimension is the performance impact, which is determined by analyzing the ratio of the difference between the query response time and the preset benchmark value when the index for this field is missing. The third dimension is data fit, which is calculated by combining field selectivity index and value distribution characteristics. First, the field selectivity index is mapped to the basic fit score according to the preset interval. The specific mapping rule is as follows: the field selectivity index value interval from 0 to 1 is divided into five consecutive preset intervals. Each preset interval corresponds to a basic fit score value range of 0 to 20, 21 to 40, 41 to 60, 61 to 80, and 81 to 100 respectively. Let the field selectivity index value be First, determine the preset interval to which it belongs, and then calculate the specific basic adaptation score within that preset interval using linear interpolation: the lower limit of the interval is... The upper limit is The corresponding basic adaptation score lower limit is The upper limit is The expression for the basic adaptation score is as follows: (1); in, Based on the adaptation score; Next, a distribution correction coefficient is set based on the value distribution characteristics. The specific process is as follows: a value distribution skewness judgment library is pre-constructed, which sets multiple skewness levels. Each skewness level corresponds to a proportion interval and a distribution correction coefficient. The frequency of occurrence of each different value in the current field is counted, and the cumulative proportion of the three most frequent values ​​is calculated. This cumulative proportion is matched with the proportion interval in the distribution skewness judgment library to determine the skewness level to which the cumulative proportion belongs, and the distribution correction coefficient corresponding to this level is read as the final distribution correction coefficient. The data fit value is calculated based on the distribution correction coefficient. The data fit value is then normalized to the range of 0 to 1 to obtain the data fit degree. The expression for data fit is as follows: (2); in, For data adaptation, This is the distribution correction factor; The basic weight values ​​of the above three dimensions are weighted according to preset weight coefficients to obtain the initial contribution weight of each field; the expression for the initial contribution weight is as follows: (3); in, The initial contribution weight for a certain field. For this field The values ​​of each dimension, For this field Preset weight coefficients for each dimension; S22. Dynamically adjust the initial contribution weight based on the execution metrics of the query statement. Generate candidate indexes according to the field combination rules based on the adjusted contribution weights. Calculate the comprehensive score of each candidate index. Sort the candidate indexes from high to low according to the comprehensive score to generate a priority sequence of indexes to be created. The indicator scenarios are specifically divided into two categories: The first category is high-frequency query template scenarios, which refers to query templates whose daily average number of queries exceeds a set threshold; the contribution weight of the fields involved is multiplied by a frequency adjustment coefficient; the frequency adjustment coefficient is a coefficient used to amplify the contribution weight of the fields involved in high-frequency query templates; The second category is query template scenarios with excessively long response times, which refers to query templates whose average response time exceeds a set threshold; the contribution weight of the fields involved is multiplied by a performance compensation coefficient; the performance compensation coefficient is a coefficient used to amplify the contribution weight of the fields involved in query templates with excessively long response times; The formula for the overall score is as follows: (4) in, A comprehensive score for a candidate index. For this candidate index Adjusted contribution weights for each field For query frequency weighting, To achieve the expected performance improvement, The total number of fields included in the candidate index; the query frequency weight is related to the frequency of query template execution; the expected performance improvement value represents the estimated degree of performance improvement after the candidate index is created; The query frequency weight is related to the execution frequency of the query template, and its determination process is as follows: The average daily query count of each query template is extracted from the association mapping library. All query templates are divided into multiple gradient intervals based on the average daily query count, and a corresponding query frequency weight value is assigned to each gradient interval. The specific weight value is determined manually, taking into account the business scenario. For example, in the financial sector, the sensitivity to high-frequency queries is high, so the query frequency weight value for queries with higher execution frequencies should also be higher. In the industrial sector, the sensitivity to high-frequency queries is not as high, so the query frequency weight value for queries with higher execution frequencies will not be set too high. If a candidate index adapts to multiple query templates, the average of the query frequency weights corresponding to the adapted templates is taken as the query frequency weight in the comprehensive score of the candidate index. The expected performance improvement represents the estimated degree of performance improvement to queries after the candidate index is created. The determination process is as follows: First, obtain the current average response time of the candidate index adapted to the query template, as well as the selectivity index and data distribution characteristics of the index fields from the association mapping library. Based on the selectivity of the index fields and the data skew, determine the basic performance improvement. The improvement should meet the following requirements: the higher the field selectivity and the more balanced the data distribution, the greater the corresponding basic improvement. If the candidate index is a composite index, further analyze whether its field order is completely consistent with the order of the filtering or sorting fields in the query conditions. If they are completely consistent, add an additional improvement percentage to the basic improvement. If they are only partially consistent, no additional adjustment is made. Candidate indexes can be categorized into single-field indexes and composite indexes based on the number of fields; composite indexes are sorted in descending order of field contribution weight. The priority sequence includes: the field combination of the candidate index, the estimated performance improvement, and the range of applicable query templates.

[0019] S30. Perform pattern matching analysis between the existing index structure and candidate indexes in the priority sequence to analyze field coverage overlap and database load generated by index maintenance, and automatically identify and mark redundant indexes with functional overlap; the detailed process of this step is as follows: S31. Read the existing index structure data from the database system dictionary table and extract the core features of each existing index; at the same time, read the candidate indexes from the priority sequence generated in step S20 and extract their core features; construct a pattern matching algorithm based on field coverage relationship and define field coverage overlap as the core matching index; if the field coverage overlap is not lower than the preset overlap threshold and the index types and inclusion field attributes of the two are consistent, then mark the index pair as a potential functional overlap set; if the existing index field combination is a subset of the candidate index field combination and the inclusion field attributes are consistent, then further mark the existing index as a potential redundant front index of the candidate index; Key features include: the combination of indexed fields, the index type, and whether it contains inclusive fields; Inclusion fields refer to fields in the index that are included in addition to the index key fields and are used to directly return query results; The field coverage overlap is obtained by counting the number of common fields between the existing index and the candidate index, and then dividing the result by the total number of fields of the index with more common fields. The ratio obtained is the field coverage overlap. S32. Evaluate the load from three aspects: disk I / O increment, CPU usage increment, and storage space usage. For index pairs with potentially overlapping functions, calculate the comprehensive load value according to the preset weight coefficient. If the comprehensive load value of the candidate index is lower than that of the existing index and can cover all the query templates adapted by the existing index, then mark the existing index as a functionally redundant index. If the existing index is a potential redundant preceding index of the candidate index and its comprehensive load value reaches or exceeds the preset ratio threshold of the comprehensive load value of the candidate index, then mark the existing index as a high-load redundant index. Disk I / O increment represents the increase in disk input / output operations due to index maintenance, calculated based on the average daily data growth and index field length; CPU usage increment represents the increase in CPU resource usage due to index maintenance, calculated based on the frequency of index update operations and field computation complexity; storage space usage represents the space occupied by the index on the storage medium, calculated based on the index data volume and compression ratio. S33. Extract the usage records of potential redundant indexes for the most recent month, identify redundant indexes based on the usage records, and finally generate a redundant index marking report; Potentially redundant indexes refer to existing indexes that are included in a potentially functionally overlapping set or are marked as potentially redundant preceding indexes; The process for determining redundant indexes is as follows: If the average daily number of queries on a potential redundant index is lower than the preset low-frequency threshold and the query hit time is not lower than the estimated time of the candidate index, then the potential redundant index is determined to be a redundant index; if the number of queries on a potential redundant index is high but all compatible query templates can be covered by the candidate index and the estimated response time of the candidate index is better, then after query migration feasibility analysis, it is still marked as a redundant index. Query migration feasibility analysis refers to the analysis of query syntax compatibility and performance stability after migrating query templates adapted to the existing index to the candidate index. This analysis aims to determine whether the new index can be executed normally after migration and whether problems such as jitter, timeout, and performance fallback will occur during execution. The redundant index marking report includes: the field structure of the redundant index, load comparison, migration suggestions for adapting query templates, and deletion priority.

[0020] S40. Train a prediction model based on historical query performance metrics, predict performance gains for high-priority index schemes, collect actual performance metrics after index changes and compare them with the prediction results, adjust model parameters through a feedback loop mechanism, and iteratively output the optimal index configuration scheme that matches the current database workload characteristics; the detailed process of this step is as follows: S41. Extract historical data from the past 6 months from the association mapping library to construct the model training dataset; use the performance gain after index changes as the prediction target, and use query frequency, field selectivity, table data volume, and index field combination length as core input features; use gradient boosting regression algorithm to train the prediction model, and optimize the model parameters through five-fold cross-validation. Historical data includes: query template execution frequency, average response time, index usage, and data table size characteristics for the corresponding time period. The expression for performance gain is as follows: (5); in, For performance gain, , These are the average response times before and after the change, respectively. The optimization process is as follows: Set the learning rate to The number of decision trees is Maximum tree depth is The parameter values ​​are adaptively set based on the database load and the amount of historical data. The model training set is randomly divided into 5 mutually exclusive subsets with balanced data distribution. One subset is used as the validation set and the remaining 4 subsets are used as the training set. This process is repeated 5 times to complete the model training and validation. With the goal of minimizing prediction error, training is performed by iterating through different combinations of hyperparameters, and the prediction error on the validation set is calculated in each round. The combination of hyperparameters that minimizes the error on the validation set is retained as the optimal parameters for this optimization. The optimal parameter model was validated using an independent test set, and the error rate between the predicted values ​​and the actual performance gains was calculated. If the error rate is not greater than the preset error rate threshold, the model accuracy is determined to be up to standard, and training ends. If the error rate is greater than the preset error rate threshold, the training set is expanded by adding incremental data from the most recent preset period, and parameter optimization and training are re-executed. This process is repeated iteratively until the model accuracy meets the requirements. Once the model meets the criteria, save the structure and optimal parameters for performance gain prediction of subsequent indexing schemes. S42. From the priority sequence generated in step S20, select the top N% of candidate indexes by comprehensive score, and generate an index change scheme set by grouping by data table; input the index features and historical performance indicators of the corresponding query template in each scheme into the trained prediction model, and output the estimated performance gain value of the scheme. And confidence interval; at the same time, combined with the load assessment results of step S30, if the overall load value increase due to index changes in the scheme does not exceed the load safety preset threshold and If the value is not less than the effective threshold for performance gain, then the solution is marked as a candidate solution to be executed; if there are multiple solutions for the same data table, then the solution with the largest ratio of the estimated performance gain value to the increase in the overall load value is selected as the optimal candidate. S43. Select a low-peak period for the database to execute the candidate solution. Before execution, back up the target table data using a database backup tool. During execution, monitor the database load in real time. If the load exceeds a preset safety threshold, pause the change and trigger a rollback process. After the change is completed, continuously collect actual performance data for a preset period and calculate the performance gain. ; predict input features, , The feedback dataset is composed of... and If the absolute value of the difference is greater than the prediction bias threshold, then the feedback data is added to the model training set, the model parameters are readjusted, and one model iteration is completed; if and If the absolute value of the difference is not greater than the prediction bias threshold, the data will only be stored in the model sample library for subsequent periodic iterations. Actual performance data includes: the actual average response time and execution frequency of the query templates involved in the solution; S44. After each feedback loop is completed, based on the current database workload characteristics, steps S20-S40 are re-executed to generate a new round of index optimization schemes; the actual performance gain and overall load increase of the multiple rounds of schemes are compared. If two consecutive rounds of schemes... If the difference is no greater than 5% and the overall load value is stable, then the scheme is marked as the current optimal index configuration scheme and output to the database management system as the benchmark scheme for index maintenance. At the same time, a full iteration trigger mechanism is set up once a month to automatically re-collect the latest query logs and data features, update the association mapping library and prediction model, so that the optimal scheme always adapts to the dynamically changing workload of the database.

[0021] Example 2 The present invention also provides a SOP database index optimization system for implementing the steps in the method of embodiment 1, the system comprising: The feature association analysis module is used to collect historical query logs from the database, extract filter fields, sorting fields, and association fields from the query conditions, and analyze the data distribution characteristics of the corresponding data tables to establish an association mapping library between the two. The feature association analysis module includes: a query log analysis unit, a data feature extraction unit, and an association mapping library construction unit. The query log analysis unit collects historical query logs from the target database instance within a specified period, filters out non-business query statements, and retains valid query statements. It extracts structured information through an SQL parser, normalizes parameterized queries to form standardized query templates, and records the execution frequency, average response time, and peak response time of each query template. The data feature extraction unit extracts features from the data tables involved in the query log, collects table size features and field attribute features, analyzes field data distribution features, calculates the frequency of occurrence and number of duplicate values ​​of discrete fields, the distribution range and density features of continuous fields, counts field update frequency, calculates index field selectivity index and identifies fields with skewed value distribution. The association mapping library construction unit builds the association mapping library based on query pattern information and data feature information. It uses standardized query templates as the core index to establish the association relationship between query templates and data tables, field types, data features, and execution indicators. It uses a structured storage engine to store the database, which includes fields such as the unique identifier of the query template and the name of the associated data table. The association mapping library is incrementally updated through a timed update mechanism.

[0022] The index priority evaluation module is used to quantify the contribution weight of each field in query performance optimization. It combines the execution frequency and response time of the query statement to generate a priority sequence of indexes to be created. The index priority evaluation module includes: field contribution calculation unit, weight dynamic adjustment unit, and candidate index sorting unit. The field contribution calculation unit builds an evaluation model based on the data in the association mapping library. It calculates the basic weight values ​​from three dimensions: query dependency, performance impact, and data adaptability. The initial contribution weight is obtained by weighting the values ​​with preset weight coefficients. The dynamic weight adjustment unit adjusts the initial weights based on query execution metrics. Fields involved in high-frequency query templates are multiplied by a frequency adjustment coefficient, and fields involved in query templates with extremely long response times are multiplied by a performance compensation coefficient to obtain the adjusted contribution weights. The candidate index sorting unit generates single-field indexes and composite indexes according to field combination rules, calculates a comprehensive score, and generates a priority sequence of indexes to be created based on the score.

[0023] The redundant index identification module is used to perform pattern matching analysis between the existing index structure and candidate indexes in the priority sequence, analyze the field coverage overlap and the database load generated by index maintenance, and automatically identify and mark redundant indexes with functional overlap. The redundant index identification module includes: an index feature analysis unit, a pattern matching and load evaluation unit, and a redundancy verification and report generation unit. The index feature analysis unit reads existing index structure data from the database system dictionary table and extracts core features such as index field combinations, types, and inclusion fields; it also extracts similar features from candidate indexes in the priority sequence. The pattern matching and load assessment unit constructs a pattern matching algorithm based on field coverage relationships, calculates field coverage overlap, marks potential functional overlap sets and potential redundant front indexes; assesses the load from three dimensions: disk I / O increment, CPU usage increment, and storage space usage; calculates the comprehensive load value according to preset weight coefficients; and identifies functionally redundant indexes and high-load redundant indexes. The redundancy verification and report generation unit extracts usage records of potential redundant indexes within a preset period, verifies redundancy attributes by combining query frequency, hit duration, and migration feasibility analysis, and generates a redundant index marking report.

[0024] The index scheme optimization module is used to predict the performance gain of high-priority index schemes. After executing index changes, it collects actual performance indicators and compares them with the prediction results for analysis. It adjusts model parameters through a feedback loop mechanism and iteratively outputs the optimal index configuration scheme that matches the current database workload characteristics. The index scheme optimization module includes: a prediction model training unit, a scheme selection and execution unit, and a feedback iteration unit. The prediction model training unit extracts historical data from the association mapping library to construct a training set. It takes the performance gain after index change as the prediction target, selects core input features, uses gradient boosting regression algorithm to train the model, and optimizes parameters through five-fold cross-validation. The scheme selection and execution unit selects candidate indexes with high comprehensive scores from the priority sequence and generates a set of index change schemes in groups; it outputs the estimated performance gain and confidence interval through the prediction model, and selects the schemes to be executed in combination with load assessment; it executes the schemes during the database's off-peak hours, monitors the load during execution, and supports rollback. The feedback iteration unit collects actual performance data for a preset period after the change, calculates the actual performance gain and forms a feedback dataset; if the prediction deviation exceeds the threshold, the model parameters are updated and the iteration is completed; the performance of multiple rounds of schemes is compared and the optimal configuration scheme is finally output.

[0025] Example 3 This embodiment takes the power load monitoring database of the power dispatch center as an example. First, the log collection process is started to obtain the query logs of the database for the past 45 days, covering the complete power consumption cycle from weekday morning peak to evening peak, weekend and holiday. Non-business statements such as system inspection and data backup are filtered out, and valid statements containing select (load query) and update (real-time load update) are retained. The SQL parser extracts the user number, power consumption period, transformer area number and other filtering fields in the where clause of the statement, and the load value sorting field in the order by clause. The parameterized query is normalized into a user load query template, and its daily execution frequency is recorded as 220,000 times, the average response time is 1200ms, the peak response time is 2500ms, and the morning peak is from 9:00 to 11:00 am. Synchronously extract the characteristics of the user_load table: the total number of records in the table is 120 million, the data file occupies 900GB of space, and the daily growth is 8GB; the user number is the primary key (char (20)), the transformer area number is the foreign key (varchar (15)), the electricity consumption period is the timestamp type, and the load value is the float type; calculate 50,000 duplicate values ​​for the transformer area number, and the selectivity index is 50,000 / 120 million≈0.004; the distribution of the electricity consumption period value is concentrated between 6:00 and 24:00, which is a non-skewed field, and the 1-5kW range accounts for 68% of the load value. At this time, it exceeds the 60% preset threshold and is judged as a skewed field; build an association mapping library with the standardized query template as the core, and store the association relationship such as user load query template-user_load table-transformer area number field-selectivity 0.004-daily average query 220,000 times, and set the daily incremental update at 3:00 am; Based on the association mapping library, the query dependency of the transformer area number field is calculated as follows: it appears in 92% of the user load query template; performance impact: the average response time is 2800ms when the index is missing, the baseline is 800ms, and the difference ratio is (2800-800) / 800=2.5; data fit: the selectivity index of 0.004 falls in the interval [0,0.2], and the basic fit score is calculated by linear interpolation. =0.4, distribution correction factor The initial contribution weight is 0.68, calculated as follows: 75 = 0.4 × 75 / 100 = 0.3; Since the user load query template is queried an average of 220,000 times per day, exceeding the 150,000-times threshold, it is a high-frequency template. Multiplied by a frequency adjustment coefficient of 1.7, the final weight value is 1.156; A combined index of transformer area number and electricity consumption period is generated according to the field combination rules, and the comprehensive score is calculated as (1.156 + 0.82) × 1.6. + =3.4616, ranking first in the priority sequence; Read the existing index user ID and transformer area ID, calculate the coverage overlap between the transformer area ID single field index and the candidate transformer area ID + electricity consumption period composite index = 1 / 2 = 50%, which is lower than the 75% preset threshold and does not belong to functional overlap; however, the average daily query count of the transformer area ID single field index is 80,000 times, which is lower than the low frequency threshold of 120,000 times, and the query hit time is 1100ms, which is not less than the estimated time of the candidate index of 650ms, so it is judged as a redundant index; Extract historical data from the past 6 months from the relational mapping library to measure the performance gain after index changes. To achieve the target, we selected query frequency of 220,000 times, selectivity index of 0.004, table data volume of 120 million, and field combination length of 2 as input features. We adopted the LightGBM lightweight gradient boosting machine algorithm and used five-fold cross-validation to optimize the parameters: learning rate. Take 0.12, number of decision trees Take 120, maximum tree depth If the test set error rate is 9%, which is less than the 12% threshold, the model meets the requirements. Selecting the top 30% of transformer area numbers in the priority sequence plus an electricity consumption period index scheme to predict performance gains. The overall load increase was 18%, which is less than the 25% load safety threshold, so it was marked as a plan to be executed. It was executed during the off-peak period from 2 AM to 4 AM. The user_load table data was backed up first, and during execution, disk I / O usage was monitored to ensure it did not exceed the 80% safety threshold, with no abnormalities detected. Data from the past 7 days was collected after the change, and the actual average response time was 580ms. =(1200-580) / 1200≈51.7%, then With prediction performance gain The absolute value of the difference is |51.7%-38%|=13.7%, which is less than the 15% prediction bias threshold. Therefore, the data is stored in the sample database. Comparing the two rounds of solutions, the performance gain is... The difference decreased from 10% to 4.2%, which is now less than 5%, indicating stable load. The combined index of the marked transformer area number and electricity consumption period is the optimal configuration, and the data is output to the dispatch center database management system. It will be automatically updated monthly thereafter to ensure the response efficiency of electricity load queries in high-voltage scenarios such as peak summer and winter.

[0026] The above embodiments are merely preferred technical solutions of the present invention and should not be considered as limitations on the present invention. The scope of protection of the present invention should be limited to the technical solutions described in the claims, including equivalent substitutions of the technical features described in the claims. That is, equivalent substitutions and improvements within this scope are also within the scope of protection of the present invention.

Claims

1. A method for optimizing the index of a SOP database, characterized in that, Includes the following steps: S10. Collect historical query logs from the database, extract the filter fields, sorting fields, and related fields from the query conditions, and analyze the data distribution characteristics of the corresponding data tables to establish a mapping library between query patterns and data characteristics. S20. Based on the association mapping library, quantitatively evaluate the contribution weight of each field in query performance optimization, and generate a priority sequence of indexes to be created by combining the execution frequency and response time of the query statement. S30. Perform pattern matching analysis between the existing index structure and the candidate indexes in the priority sequence to analyze the field coverage overlap and the database load generated by index maintenance, and automatically identify and mark redundant indexes with functional overlap. S40. Train a prediction model based on historical query performance metrics, predict the performance gain of high-priority index schemes, collect actual performance metrics after index changes are executed, compare and analyze the prediction results, adjust model parameters through a feedback loop mechanism, and iteratively output the optimal index configuration scheme that matches the current database workload characteristics.

2. The SOP database index optimization method according to claim 1, characterized in that, The detailed process of step S10 is as follows: S11. Start the database query log collection process to obtain historical query logs of the target database instance within a set period; clean the collected log data, filter out non-business query statements, and retain valid query statements containing select, update, and delete operations; extract the structured information of the valid query statements through the SQL parser; at the same time, normalize the parameterized queries, replace the specific parameter values ​​with uniform placeholders to form standardized query templates, and record the execution frequency, average response time, and peak response time of each query template. S12. Extract features from all data tables involved in the query log, collect the basic features of each data table, and analyze the data distribution characteristics of each field; for existing indexes in the data tables, calculate the selectivity index of the index fields in combination with the field data distribution characteristics; at the same time, identify fields with skewed value distribution based on the proportion of field values. When the proportion of a certain value or a group of values ​​in a field exceeds a preset threshold, the field is a field with skewed value distribution. The basic features include: table size features and field attribute features; among which, table size features include: total number of records, data file space occupied, and average daily data growth; field attribute features include: data type of the field, whether it is a primary key, whether it is a foreign key, and whether null values ​​are allowed. The data distribution characteristic analysis process is as follows: For discrete fields, calculate the frequency of occurrence of different values ​​and the number of duplicate values; for continuous fields, determine the distribution range and density distribution characteristics of the values; for fields with update operations, count the update frequency per unit time. S13. Based on the query pattern information and data feature information extracted above, construct an association mapping library; use a structured storage engine to store the association mapping library; set up a timed update mechanism to re-collect query logs and data features according to a preset cycle and incrementally update the association mapping library; The association mapping library uses standardized query templates as the core index to establish the association between query templates and the data tables, field types, field data characteristics, and query execution metrics involved. The storage structure of a storage engine includes a unique identifier for the query template, the name of the associated data table, field names, field types, data selectivity, average daily query count, and average response time.

3. The SOP database index optimization method according to claim 1, characterized in that, The detailed process of step S20 is as follows: S21. Based on the data in the association mapping library, construct a field contribution evaluation model, and calculate the basic weight value from three dimensions for each field involved in the query template: The first dimension is query dependency, which represents the percentage of times a field appears in the query template as a filter field, sorting field, or related field. The second dimension is the performance impact, which is determined by analyzing the ratio of the difference between the query response time and the preset benchmark value when the index for this field is missing. The third dimension is data fit, which is calculated by combining field selectivity indicators and value distribution characteristics. First, the field selectivity indicators are mapped to a preset range as the basic fit score; then, a distribution correction coefficient is set according to the value distribution characteristics; the data fit value is calculated based on the distribution correction coefficient, and the data fit value is normalized to the range of 0 to 1 to obtain the data fit. The expression for data fit is as follows: （1）； in, For data adaptation, Based on the adaptation score, This is the distribution correction factor; The basic weight values ​​of the above three dimensions are weighted according to preset weight coefficients to obtain the initial contribution weight of each field; the expression for the initial contribution weight is as follows: （2）； in, The initial contribution weight for a certain field. For this field The values ​​of each dimension, For this field Preset weight coefficients for each dimension; S22. Dynamically adjust the initial contribution weight based on the execution metrics of the query statement. Generate candidate indexes according to the field combination rules based on the adjusted contribution weights. Calculate the comprehensive score of each candidate index. Sort the candidate indexes from high to low according to the comprehensive score to generate a priority sequence of indexes to be created. The indicator scenarios are specifically divided into two categories: The first category is high-frequency query template scenarios, which refers to query templates whose daily average number of queries exceeds a set threshold; the contribution weight of the fields involved is multiplied by a frequency adjustment coefficient; the frequency adjustment coefficient is a coefficient used to amplify the contribution weight of the fields involved in high-frequency query templates; The second category is query template scenarios with excessively long response times, which refers to query templates whose average response time exceeds a set threshold; the contribution weight of the fields involved is multiplied by a performance compensation coefficient; the performance compensation coefficient is a coefficient used to amplify the contribution weight of the fields involved in query templates with excessively long response times; The formula for the overall score is as follows: （3） in, A comprehensive score for a candidate index. For this candidate index Adjusted contribution weights for each field For query frequency weighting, To achieve the expected performance improvement, This represents the total number of fields contained in the candidate index. The query frequency weight is related to the frequency of query template execution; the expected performance improvement value represents the estimated degree of performance improvement after the candidate index is created. Candidate indexes can be categorized into single-field indexes and composite indexes based on the number of fields; composite indexes are sorted in descending order of field contribution weight. The priority sequence includes: the field combination of the candidate index, the estimated performance improvement, and the range of applicable query templates.

4. The SOP database index optimization method according to claim 3, characterized in that, The detailed process of step S30 is as follows: S31. Read the existing index structure data from the database system dictionary table and extract the core features of each existing index; Simultaneously, candidate indices are read from the priority sequence generated in step S20, and core features are extracted; A pattern matching algorithm is constructed based on field coverage relationships, and field coverage overlap is defined as the core matching indicator. If the field coverage overlap is not lower than the preset overlap threshold, and the index types and inclusion field attributes of the two are consistent, then the index pair is marked as a potential functional overlap set; if the existing index field combination is a subset of the candidate index field combination and the inclusion field attributes are consistent, then the existing index is further marked as a potential redundant front index of the candidate index. Key features include: the combination of indexed fields, the index type, and whether it contains inclusive fields; Inclusion fields refer to fields in the index that are included in addition to the index key fields and are used to directly return query results; S32. Evaluate the load from three aspects: disk I / O increment, CPU usage increment, and storage space usage. For index pairs with potentially overlapping functions, calculate the comprehensive load value according to the preset weight coefficient. If the comprehensive load value of the candidate index is lower than that of the existing index and can cover all the query templates adapted by the existing index, then mark the existing index as a functionally redundant index. If the existing index is a potential redundant preceding index of the candidate index and its comprehensive load value reaches or exceeds the preset ratio threshold of the comprehensive load value of the candidate index, then mark the existing index as a high-load redundant index. S33. Extract the usage records of potential redundant indexes in the most recent period, identify redundant indexes based on the usage records, and finally generate a redundant index marking report; Potentially redundant indexes refer to existing indexes that are included in a potentially functionally overlapping set or are marked as potentially redundant preceding indexes; The process for determining redundant indexes is as follows: If the average daily number of queries on a potential redundant index is lower than the preset low-frequency threshold and the query hit time is not lower than the estimated time of the candidate index, then the potential redundant index is determined to be a redundant index; if the number of queries on a potential redundant index is high but all compatible query templates can be covered by the candidate index and the estimated response time of the candidate index is better, then after query migration feasibility analysis, it is still marked as a redundant index. The redundant index marking report includes: the field structure of the redundant index, load comparison, migration suggestions for adapting query templates, and deletion priority.

5. The SOP database index optimization method according to claim 4, characterized in that, The detailed process of step S40 is as follows: S41. Extract historical data from the association mapping library to build a model training dataset; use the performance gain after index changes as the prediction target, and use query frequency, field selectivity, table data volume, and index field combination length as core input features. The prediction model is trained using a gradient boosting regression algorithm, and the model parameters are optimized using five-fold cross-validation. Historical data includes: query template execution frequency, average response time, index usage, and data table size characteristics for the corresponding time period. The expression for performance gain is as follows: （4）； in, For performance gain, , These are the average response times before and after the change, respectively. S42. From the priority sequence generated in step S20, select the top N% of candidate indexes by comprehensive score, and generate an index change scheme set by grouping by data table; input the index features and historical performance indicators of the corresponding query template in each scheme into the trained prediction model, and output the estimated performance gain value of the scheme. And confidence interval; at the same time, combined with the load assessment results of step S30, if the overall load value increase due to index changes in the scheme does not exceed the load safety preset threshold and If the value is not less than the effective threshold for performance gain, then the solution is marked as a candidate solution to be executed; if there are multiple solutions for the same data table, then the solution with the largest ratio of the estimated performance gain value to the increase in the overall load value is selected as the optimal candidate. S43. Select a low-peak period for the database to execute the candidate solution. Before execution, back up the target table data using a database backup tool. During execution, monitor the database load in real time. If the load exceeds a preset safety threshold, pause the change and trigger a rollback process. After the change is completed, continuously collect actual performance data for a preset period and calculate the performance gain. ; predict input features, , The feedback dataset is composed of... and If the absolute value of the difference is greater than the prediction bias threshold, the feedback data is added to the model training set, the model parameters are readjusted, and one model iteration is completed; if and If the absolute value of the difference is not greater than the prediction bias threshold, the data will only be stored in the model sample library for subsequent periodic iterations. Actual performance data includes: the actual average response time and execution frequency of the query templates involved in the solution; S44. After each feedback loop is completed, based on the current database workload characteristics, steps S20-S40 are re-executed to generate a new round of index optimization schemes; the actual performance gain and overall load increase of the multiple rounds of schemes are compared. If two consecutive rounds of schemes... If the difference is no greater than 5% and the overall load value is stable, then the scheme is marked as the current optimal index configuration scheme and output to the database management system as the benchmark scheme for index maintenance. At the same time, a timed full iteration trigger mechanism is set to automatically re-collect the latest query logs and data features, update the association mapping library and prediction model, so that the optimal scheme always adapts to the dynamically changing workload of the database.

6. A SOP database index optimization system for implementing the steps of the method according to any one of claims 1 to 5, characterized in that it comprises: The feature association analysis module is used to collect historical query logs from the database, extract filter fields, sorting fields and association fields from the query conditions, and analyze the data distribution characteristics of the corresponding data tables to establish an association mapping library between the two. The index priority evaluation module is used to quantify the contribution weight of each field in query performance optimization, and generate a priority sequence of indexes to be created by combining the execution frequency and response time of the query statement. The redundant index identification module is used to perform pattern matching analysis between the existing index structure and the candidate indexes in the priority sequence, analyze the field coverage overlap and the database load generated by index maintenance, and automatically identify and mark redundant indexes with functional overlap. The index scheme optimization module is used to predict the performance gain of high-priority index schemes. After the index changes are executed, the actual performance indicators are collected and compared with the prediction results. The model parameters are adjusted through a feedback loop mechanism, and the optimal index configuration scheme that matches the current database workload characteristics is iteratively output.

7. The SOP database index optimization system according to claim 6, characterized in that, The feature association analysis module includes: a query log analysis unit, a data feature extraction unit, and an association mapping library construction unit; The query log analysis unit collects historical query logs from the target database instance within a specified period, filters out non-business query statements, and retains valid query statements. It extracts structured information through an SQL parser, normalizes parameterized queries to form standardized query templates, and records the execution frequency, average response time, and peak response time of each query template. The data feature extraction unit extracts features from the data tables involved in the query log, collects table size features and field attribute features, analyzes field data distribution features, calculates the frequency of occurrence and number of duplicate values ​​of discrete fields, the distribution range and density features of continuous fields, counts field update frequency, calculates index field selectivity index and identifies fields with skewed value distribution. The association mapping library construction unit builds the association mapping library based on query pattern information and data feature information. It uses standardized query templates as the core index to establish the association relationship between query templates and data tables, field types, data features, and execution indicators. It uses a structured storage engine to store the database, which includes fields such as the unique identifier of the query template and the name of the associated data table. The association mapping library is incrementally updated through a timed update mechanism.

8. The SOP database index optimization system according to claim 6, characterized in that, The index priority evaluation module includes: a field contribution calculation unit, a weight dynamic adjustment unit, and a candidate index sorting unit; The field contribution calculation unit builds an evaluation model based on the data in the association mapping library. It calculates the basic weight values ​​from three dimensions: query dependency, performance impact, and data adaptability. The initial contribution weight is obtained by weighting the values ​​with preset weight coefficients. The dynamic weight adjustment unit adjusts the initial weights based on query execution metrics. Fields involved in high-frequency query templates are multiplied by a frequency adjustment coefficient, and fields involved in query templates with extremely long response times are multiplied by a performance compensation coefficient to obtain the adjusted contribution weights. The candidate index sorting unit generates single-field indexes and composite indexes according to field combination rules, calculates a comprehensive score, and generates a priority sequence of indexes to be created based on the score.

9. The SOP database index optimization system according to claim 6, characterized in that, The redundant index identification module includes: an index feature analysis unit, a pattern matching and load assessment unit, and a redundancy verification and report generation unit; The index feature analysis unit reads existing index structure data from the database system dictionary table and extracts core features such as index field combinations, types, and inclusion fields; it also extracts similar features from candidate indexes in the priority sequence. The pattern matching and load assessment unit constructs a pattern matching algorithm based on field coverage relationships, calculates field coverage overlap, marks potential functional overlap sets and potential redundant front indexes; assesses the load from three dimensions: disk I / O increment, CPU usage increment, and storage space usage; calculates the comprehensive load value according to preset weight coefficients; and identifies functionally redundant indexes and high-load redundant indexes. The redundancy verification and report generation unit extracts usage records of potential redundant indexes within a preset period, verifies redundancy attributes by combining query frequency, hit duration, and migration feasibility analysis, and generates a redundant index marking report.

10. The SOP database index optimization system according to claim 6, characterized in that, The indexing scheme optimization module includes: a prediction model training unit, a scheme selection and execution unit, and a feedback iteration unit; The prediction model training unit extracts historical data from the association mapping library to construct a training set. It takes the performance gain after index change as the prediction target, selects core input features, uses gradient boosting regression algorithm to train the model, and optimizes parameters through five-fold cross-validation. The scheme selection and execution unit selects candidate indexes with high comprehensive scores from the priority sequence and generates a set of index change schemes in groups; it outputs the estimated performance gain and confidence interval through the prediction model, and selects the schemes to be executed in combination with load assessment; it executes the schemes during the database's off-peak hours, monitors the load during execution, and supports rollback. The feedback iteration unit collects actual performance data for a preset period after the change, calculates the actual performance gain and forms a feedback dataset; if the prediction deviation exceeds the threshold, the model parameters are updated and the iteration is completed; the performance of multiple rounds of schemes is compared and the optimal configuration scheme is finally output.

Images