Database index adjusting method and device, electronic equipment and storage medium

Abstract

The invention provides a database index adjusting method and device, electronic equipment and a storage medium. The method comprises the following steps: acquiring a data query log and a data modification log generated in a running process of a database; according to the data query log and the data modification log, performance evaluation is carried out on the index in the database to obtain an index performance evaluation result, and the index performance evaluation result comprises parameters of the index in the database influencing the data query performance; generating index adjustment decision information of the database according to the index performance evaluation result; and under the condition that the index adjustment decision information indicates to perform index adjustment on the database, performing index adjustment on the database based on the data query log and the data modification log. The query performance can be optimized, the index maintenance cost is reduced, and the overall efficiency of the database is improved.

Description

Technical Field

[0001] This application relates to the field of index management technology, and in particular to a method, apparatus, electronic device and storage medium for adjusting a database index. Background Technology

[0002] Database indexes are an important mechanism to ensure query efficiency, but with the increase in data volume and the continuous changes in query patterns, they become less effective.

[0003] Traditional index management typically requires manual analysis of query logs and design of index strategies. However, manual intervention is costly and difficult to adapt to rapid changes in database fields, often leading to unstable query performance. Invalid or redundant indexes increase the burden of write operations and affect the overall performance of the database. Summary of the Invention

[0004] The purpose of this application is to provide a method, apparatus, electronic device, and storage medium for adjusting database indexes, so as to automatically analyze changes in query load and data structure, and adjust the index structure accordingly, optimize query performance, reduce index maintenance costs, and improve the overall efficiency of the database. The specific technical solution is as follows: In a first aspect of this application, a method for adjusting a database index is provided, comprising: Retrieve the data query logs and data modification logs generated by the database during its operation; Based on the data query log and the data modification log, the performance of the indexes in the database is evaluated to obtain the index performance evaluation results, which include parameters of the impact of the indexes in the database on data query performance. Based on the index performance evaluation results, generate index adjustment decision information for the database; When the index adjustment decision information indicates that the database should be indexed, the database should be indexed based on the data query log and the data modification log.

[0005] In a second aspect of this application, a database index adjustment device is provided, comprising: The log acquisition module is used to acquire data query logs and data modification logs generated by the database during operation. The performance evaluation module is used to evaluate the performance of the indexes in the database based on the data query logs and the data modification logs, and obtain the index performance evaluation results. The index performance evaluation results include parameters of the impact of the indexes in the database on data query performance. The decision generation module is used to generate index adjustment decision information for the database based on the index performance evaluation results. An index adjustment module is used to adjust the index of the database based on the data query log and the data modification log when the index adjustment decision information indicates that the database should be adjusted.

[0006] In another aspect of this application, an electronic device is also provided, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; The processor, when executing a program stored in memory, implements any of the above-described methods for adjusting the database index.

[0007] In another aspect of this application, a computer-readable storage medium is also provided, wherein instructions are stored therein, which, when executed on a computer, cause the computer to perform any of the above-described methods for adjusting the database index.

[0008] In another aspect of this application, a computer program product containing instructions is also provided, on which a computer program is stored, which, when run on a computer, causes the computer to execute any of the above-described methods for adjusting the database index.

[0009] The solution provided in this application automatically analyzes query load characteristics (such as high-frequency / time-consuming queries and dependent fields) and data structure changes (such as data volume growth and frequently modified fields) by collecting query logs and modification logs from the database. This achieves automatic analysis of query load and data structure changes. Based on log data, the actual performance of the index on queries (utilization, acceleration effect) is evaluated, a quantitative correlation between load and index adaptability is established, and targeted adjustment decisions are generated (adding necessary indexes, deleting redundant indexes, adjusting inefficient structures, etc.). Adjustments are then precisely executed based on the logs, ultimately dynamically balancing the relationship between query performance and maintenance costs, reducing index maintenance costs, and improving the overall efficiency of the database. Attached Figure Description

[0010] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the accompanying drawings used in the description of the embodiments or the prior art will be briefly introduced below.

[0011] Figure 1 A flowchart illustrating the steps of a database index adjustment method provided in this application embodiment; Figure 2 A flowchart illustrating the steps of an index performance evaluation method provided in this application embodiment; Figure 3A flowchart illustrating the steps of another index performance evaluation method provided in this application embodiment; Figure 4 A flowchart illustrating the steps of an adjustment decision generation method provided in this application embodiment; Figure 5 A flowchart illustrating the steps of an index adjustment method provided in this application embodiment; Figure 6 A flowchart illustrating the steps of a candidate index key filtering method provided in this application embodiment; Figure 7 A flowchart illustrating the steps of a target index key filtering method provided in this application embodiment; Figure 8 A flowchart illustrating the steps of another index adjustment method provided in this application embodiment; Figure 9 A block diagram of an index management system provided in an embodiment of this application; Figure 10 A schematic diagram illustrating an adaptive index management process provided in an embodiment of this application; Figure 11 A timing diagram for adaptive index management provided in an embodiment of this application; Figure 12 A schematic diagram of the structure of a database index adjustment device provided in an embodiment of this application; Figure 13 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0012] The technical solutions in the embodiments of this application will now be described with reference to the accompanying drawings.

[0013] Figure 1 A flowchart illustrating the steps of a database index adjustment method provided in this application embodiment is shown below. Figure 1 As shown, the method for adjusting the database index may include steps 101, 102, 103, and 104.

[0014] Step 101: Obtain the data query logs and data modification logs generated by the database during operation.

[0015] In this embodiment, the data query log refers to a log file that records all query operations (such as SELECT statements) during the database operation. It includes information such as query statements, execution time, tables / fields involved, filtering conditions, number of returned rows, and whether indexes are used, which is used to reflect the query load characteristics.

[0016] A data modification log is a log file that records all data change operations (such as INSERT / UPDATE / DELETE statements) in a database. It contains information such as the modified tables / fields, modification frequency, number of rows affected, and values ​​before and after the modification, and is used to reflect the dynamic changes in the data structure.

[0017] During database index management, data query logs and data modification logs generated during database operation can be obtained. In practice, these logs can be collected using the database's built-in logging functionality. Alternatively, third-party tools (such as Percona Toolkit) can be used to capture and parse SQL statements in real time, generating structured logs (containing detailed query / modification attributes). The method for obtaining data query and modification logs depends on business requirements; this embodiment does not impose any limitations on this.

[0018] Step 102: Based on the data query log and the data modification log, perform a performance evaluation on the indexes in the database to obtain the index performance evaluation results. The index performance evaluation results include parameters that indicate the impact of the indexes in the database on data query performance.

[0019] An index is a special data structure (such as a B+ tree or hash table) created in a database to speed up queries. By sorting or mapping specific fields in a table, it reduces the scan range during a query and improves query efficiency, but it increases the maintenance overhead when data is modified (such as index updates).

[0020] Index performance evaluation refers to the process of quantitatively analyzing the "query benefits" and "maintenance costs" of an index based on query logs and modification logs. The purpose is to determine whether the index is suitable for the current query load and data changes.

[0021] Index performance evaluation results can include parameters assessing the impact of indexes on data query performance. These parameters can be used to quantify output parameters of the evaluation process, including index utilization (frequency of use in queries), query speedup ratio (the percentage reduction in query time after using an index), maintenance cost (time / resource consumption of index updates when data is modified), and index redundancy (the percentage of indexes not used in queries), to measure the actual impact of indexes on database performance.

[0022] After obtaining the data query logs and data modification logs, the performance of the indexes in the database can be evaluated based on these logs to obtain the index performance evaluation results. Specifically, the data query logs and data modification logs can be parsed, and the index performance can be evaluated based on the high-frequency query patterns and query fields obtained from the parsing, as well as the frequency and amount of field changes. This implementation process will be combined with the following embodiments. Figure 2A detailed description will not be repeated here.

[0023] Step 103: Based on the index performance evaluation results, generate index adjustment decision information for the database.

[0024] Index adjustment decision information refers to specific adjustment instructions generated based on the index performance evaluation results, such as "add an index for a certain field", "delete a redundant index", "adjust a single-column index to a composite index", etc., which are used to guide subsequent adjustment operations.

[0025] After obtaining the index performance evaluation results, database index adjustment decision information can be generated based on these results. Specifically, specific index adjustment decision information can be generated based on the parameters in the index performance evaluation results: query speedup ratio, space utilization, and write operation overhead. This implementation process will be combined with the following embodiments. Figure 4 A detailed description will not be repeated here.

[0026] Step 104: If the index adjustment decision information indicates that the database should be indexed, the database should be indexed based on the data query log and the data modification log.

[0027] After obtaining the index adjustment decision information, it can be used to determine whether index adjustments are necessary. Specifically, the decision information can be used to determine whether the benefits of index adjustments (such as performance improvements) outweigh the costs (such as write overhead) to decide whether to perform index adjustments on the database.

[0028] In practical applications, specific values ​​can be quantified by combining database query logs and real-time monitoring data: Quantifying returns can include: 1. You can use the MySQL slow log (a log that specifically records SQL query statements whose execution time exceeds a set threshold) to locate high-frequency, inefficient queries (such as unindexed queries that are executed 100,000 times a day and each takes 500ms).

[0029] 2. Simulate or estimate the query time after index adjustment (e.g., adding a suitable index) (e.g., reducing it to 50ms), and calculate the single-query benefit (500ms - 50ms = 450ms) and the total benefit (100,000 queries × 450ms = 45,000 seconds / day). In this example, the benefit of index adjustment refers to the positive technical value brought to the database system by index adjustment, such as performance improvement and resource saving. It is essentially a quantitative evaluation of the effect of index adjustment to determine whether the index is worth adjusting.

[0030] Cost quantification can include: 1. Modify the log to track the write frequency of related tables (e.g., 10,000 INSERTs and 5,000 UPDATEs per day).

[0031] 2. Based on the principle of index maintenance (indexes need to be updated synchronously with data modifications), estimate the increase in write latency (e.g., each UPDATE increases from 10ms to 12ms, increasing the cost per write by 2ms) and the total cost (5,000 writes × 2ms = 10,000ms / day = 10 seconds / day).

[0032] 3. Calculate the storage space requirement for the new index (e.g., based on the table data volume and index type, estimate an additional 10GB of storage).

[0033] When the benefits of index adjustment outweigh the costs, the indexes in the database can be adjusted. In this case, index adjustments can be made based on data query logs and data modification logs. Specifically, the data query logs and data modification logs can be parsed, and index keys can be filtered based on the parsing results. Index adjustments are then performed based on the final filtered index keys. This implementation process will be discussed in the following embodiments. Figure 5 A detailed description will not be repeated here.

[0034] This application's embodiments automatically analyze query load characteristics (such as high-frequency / time-consuming queries and dependent fields) and data structure changes (such as data volume growth and frequently modified fields) by collecting query and modification logs from the database, thus achieving automatic analysis of query load and data structure changes. Based on log data, the actual performance of indexes on queries (utilization, acceleration effect) is evaluated, a quantitative correlation between load and index adaptability is established, and targeted adjustment decisions are generated (adding necessary indexes, deleting redundant indexes, adjusting inefficient structures, etc.). Adjustments are then precisely executed based on the logs, ultimately dynamically balancing the relationship between query performance and maintenance costs, reducing index maintenance costs, and improving the overall efficiency of the database.

[0035] Next, combined Figure 2 The process of index performance evaluation is described in detail.

[0036] Reference Figure 2 The diagram illustrates a flowchart of the steps involved in an index performance evaluation method provided in an embodiment of this application. Figure 2 As shown, the index performance evaluation method may include steps 201, 202 and 203.

[0037] Step 201: Parse the data query log to obtain the query patterns and target query fields with query frequencies higher than the threshold in the historical query statements of the database. The query pattern refers to the set of typical characteristics and patterns presented by the query requests initiated to the database.

[0038] In this embodiment, query frequency refers to the number of times a certain type of query statement is executed per unit of time (such as per hour or per day), which is used to measure the "popularity" of the query (whether it is a high-frequency demand).

[0039] Thresholds refer to set frequency standards (such as "execute ≥100 times per hour"), used to filter "high-frequency queries that need to be focused on" and avoid meaningless low-frequency queries interfering with the analysis.

[0040] A query pattern refers to the set of typical characteristics and patterns presented by query requests initiated against a database. This includes query type (such as single-table queries / multi-table joins), filtering conditions (such as the combination of fields in the WHERE clause), returned fields (such as the list of fields after SELECT), and table join methods (such as JOIN conditions).

[0041] The target query field refers to the field that is frequently used as a filter condition, sorting criterion, or return result in high-frequency queries, and is a key factor in query performance. Examples include `user_id` in `WHERE user_id = ?` and `create_time` in `ORDER BY create_time`.

[0042] After obtaining the data query log, it can be parsed to identify query patterns and target query fields with a frequency exceeding a threshold in the database's historical query statements. Specifically, historical query statements can be exported from the data query log (e.g., using MySQL's `slow_query_log` or PostgreSQL's `pg_log`), filtering out valid queries (excluding syntax errors and test statements). The statements are then categorized based on "query feature similarity" (e.g., "query orders by user ID" or "query sales by date"), and the number of executions per unit time for each query type is counted (e.g., executions per hour). Query categories with a frequency exceeding a set threshold (e.g., "≥200 times per hour") are then marked as "high-frequency queries." The typical characteristics of high-frequency queries are analyzed to obtain high-frequency query patterns (e.g., filtering conditions, table join methods, sorting fields, etc.). Fields that frequently appear (e.g., with a frequency exceeding a set threshold) in the WHERE / JOIN / ORDER BY clauses of high-frequency queries are extracted as target query fields.

[0043] Step 202: Parse the data modification log to obtain the change frequency and change amount of the fields in the database.

[0044] Change frequency refers to the number of times a field is modified (such as UPDATE, INSERT, DELETE) per unit of time. It is used to measure the "activity" of a field (e.g., if a field is modified 500 times per hour, the change frequency is high).

[0045] Change volume refers to the extent to which each modification operation affects the field data. It is usually measured by "the number of rows involved in each modification" or "the magnitude of the data value change" (e.g., if each modification of a field only affects 1 row of data, the change volume is small; if 1000 rows are updated in batches, the change volume is large).

[0046] After obtaining the data modification log, it can be parsed to obtain the change frequency and quantity of fields in the database. Specifically, the data modification log can be parsed to obtain all change operations (such as MySQL's binlog and PostgreSQL's WAL), and associated with specific tables (i.e., data tables) and fields (e.g., UPDATE user SET last_login_time = ... corresponds to the user.last_login_time field). The log can then be grouped by field to count the number of times each field is modified per unit of time (e.g., user.last_login_time is updated 500 times per hour), thus obtaining the change frequency. Simultaneously, the number of rows involved in each modification operation can be recorded (e.g., whether a single UPDATE affects 1 row or 1000 rows), or the magnitude of the data value change (e.g., an inventory field changing from 1000 to 999 is a small change, while changing from 1000 to 0 is a large change), thus obtaining the change quantity.

[0047] Step 203: Based on the query pattern, the target query field, the change frequency, and the change amount, perform a performance evaluation on the index in the database to obtain the index performance evaluation result.

[0048] After the above parsing operations, the query pattern, target query field, change frequency, and change volume can be obtained. Then, the performance of the index in the database can be evaluated based on the query pattern, target query field, change frequency, and change volume to obtain the index performance evaluation result. Specifically, the query speedup ratio, space utilization, and write operation overhead can be calculated based on the parsed parameters, and the index performance can be evaluated based on the query speedup ratio, space utilization, and write operation overhead. This implementation process will be combined with the following embodiments. Figure 3 A detailed description will not be repeated here.

[0049] This application embodiment can accurately identify high-frequency query requirements and field change characteristics by parsing query and modification logs, dynamically evaluate index performance, retain or add high-value indexes, and remove redundant indexes, thereby improving query performance, reducing maintenance costs, allowing the index structure to continuously adapt to business changes, and optimizing database resource utilization and overall efficiency.

[0050] Next, combined Figure 3The specific process for evaluating index performance is described in detail.

[0051] Reference Figure 3 The diagram illustrates a flowchart of another index performance evaluation method provided in an embodiment of this application. Figure 3 As shown, the index performance evaluation method may include steps 301, 302, 303, 304 and 305.

[0052] Step 301: Based on the historical query statements with a query frequency higher than the threshold in the query mode, filter out the target query statements that contain the target query field.

[0053] In this embodiment, the target query statement refers to a specific query statement that belongs to the high-frequency query pattern and contains the target query field.

[0054] After identifying the high-frequency query patterns, we can first filter out specific statements from the historical query statements corresponding to these patterns that contain the target query fields (i.e., the fields used as query conditions in the high-frequency queries). These are the target query statements. For example, in the video file system query log, the pattern "query orders by user_id and create_time" is executed 800 times daily (exceeding the threshold of 500 times), where user_id and create_time are the target query fields. Therefore, from the historical statements of this pattern, we can filter out specific statements such as "SELECT * FROM orders WHERE user_id=123 AND create_timeBETWEEN'2023-01-01'AND'2023-01-31'" as the target query statements.

[0055] Step 302: Simulate the execution of the target query statement in the database, and calculate the query speedup ratio of the index based on the recorded query time when the index is enabled and the query time when the index is disabled.

[0056] Query time when index is enabled / disabled: The execution time when the target query statement is executed with the index enabled, compared to the execution time when the index is disabled (e.g., forced full table scan).

[0057] The query speedup ratio refers to the index's speeding effect on queries. The calculation formula is "time spent when the index is disabled ÷ time spent when the index is enabled" (the larger the value, the more significant the speedup effect).

[0058] After obtaining the target query statement, you can simulate its execution in the database, recording the query time when the index is enabled and disabled during the simulation. The query speedup ratio is then calculated based on these two recorded times. Specifically, in a database test environment, the selected target query statement can be simulated twice: first, with the target index enabled (e.g., a composite index of user_id + create_time), the execution time is recorded. Second, the index is disabled (e.g., by forcing a full table scan using FORCE INDEX (NULL) or temporarily deleting the index), and the execution time is recorded again. Finally, the speedup ratio is calculated as "query time when disabled ÷ query time when enabled". For example, if the target query statement takes 2 seconds to execute with the index enabled and 10 seconds to execute with the index disabled due to a full table scan, then the query speedup ratio = 10 ÷ 2 = 5 (i.e., the index improves query speed by 5 times).

[0059] Step 303: Calculate the space occupancy rate of the index based on the total storage capacity of the data table to which the index belongs and the number of the target query fields.

[0060] The total storage capacity of the table to which the index belongs refers to the storage space occupied by the original data (excluding the index) of the table containing the target query field (such as the size of the table data queried by a database tool).

[0061] Space utilization rate refers to the ratio of the storage capacity of the index itself to the total storage capacity of the corresponding data table (reflecting the storage space consumption of the index).

[0062] After obtaining the target query fields, the index's space utilization rate can be calculated based on the total storage capacity of the table to which the index belongs and the number of target query fields. Specifically, two values ​​can be obtained using built-in database tools (such as MySQL's SHOW TABLE STATUS): 1. The storage capacity of the target index itself (e.g., index file size); 2. The total storage capacity of the table to which the index belongs (table data only, excluding the index). Finally, the index's space utilization rate is calculated as "index storage capacity ÷ total table storage capacity".

[0063] Step 304: Based on the change frequency and the change amount, count the number of write operations involving the target query field per unit time, and calculate the write operation cost of the index based on the index maintenance cost of a single write operation and the number of write operations.

[0064] Write operation overhead refers to the total resource consumption for maintaining an index per unit of time. The calculation formula is "number of write operations per unit of time × index maintenance cost per write operation".

[0065] After parsing the change frequency and change volume, the number of write operations involving the target query field per unit time is counted. The index write operation overhead is then calculated based on the index maintenance cost per write operation and the number of write operations. In specific implementations, this can be based on the total number of INSERT / UPDATE / DELETE write operations performed on the data table containing the target query field per unit time (e.g., hourly, daily) based on data modification log statistics (this must be related to the target query field, i.e., the operation involves modification of that field). The resource cost consumed by the database to synchronously update the index for each write operation is obtained (usually in time, such as CPU time, IO operation time, which can be estimated through database performance logs or tests), resulting in the index maintenance cost per write operation (parameters such as the average (or dynamic) value of node split / merge cost, I / O cost, and lock conflict cost per operation). The formula is: Write operation overhead = Number of write operations involving the target query field per unit time × Index maintenance cost per write operation.

[0066] In this example, the calculation process for the write operation overhead can be as follows: 1. Collect write operation logs: Statistically analyze the number and type of insert, update, and delete operations performed on the target index within a unit of time.

[0067] 2. Structure Restructuring Detection: Utilize database structure maintenance interfaces (such as MySQL's information schema or InnoDBmonitor) to count the number of node splits and merges that occur in the index structure during write operations.

[0068] 3. Disk I / O consumption monitoring: Tracks the number of page writes and the time consumed during the index writing process.

[0069] 4. Measurement of the impact of transaction locks: Collect the number and duration of transaction lock wait events caused by this index field.

[0070] 5. Normalized and weighted composite cost model: Write operation overhead = m × node reconstruction cost + n × I / O cost + p × lock conflict cost. Each sub-cost is calculated using historical data and real-time monitoring, ultimately yielding a comparable quantitative indicator. Here, m, n, and p are weights, and their specific values ​​can be set according to the business scenario.

[0071] Step 305: The query speedup ratio, the space utilization rate, and the write operation overhead are determined as the index performance evaluation results.

[0072] Finally, query speedup ratio, space utilization, and write operation overhead can be used as the results of index performance evaluation to provide a basis for subsequent decisions on "whether to adjust the index".

[0073] This application's embodiments achieve a comprehensive and accurate evaluation of index performance by quantifying three core indicators: query speedup ratio, space utilization, and write operation overhead. Its technical advantages lie in: identifying high-quality indexes that efficiently support high-frequency queries, and clearly defining the cost of indexes in terms of space utilization and write operation maintenance. This helps users scientifically adjust index configurations—retaining cost-effective indexes and eliminating redundant or inefficient indexes—ultimately achieving the dual goals of improving database query performance and balancing resource consumption.

[0074] Next, combined Figure 4 The process of generating index adjustment decision information is described in detail.

[0075] Reference Figure 4 The diagram illustrates a flowchart of the steps involved in an adjustment decision generation method provided in an embodiment of this application. Figure 4 As shown, the adjustment decision generation method may include steps 401, 402 and 403.

[0076] Step 401: If the query speedup ratio is less than the speedup ratio threshold, the space occupancy rate is greater than the occupancy rate threshold, and the write operation overhead is greater than the overhead threshold, generate decision information for deleting the index.

[0077] In this embodiment, the speedup threshold corresponding to the query speedup ratio, the occupancy threshold corresponding to the space occupancy rate, and the overhead threshold corresponding to the write operation overhead can be preset. These thresholds can be dynamically adjusted according to the database load characteristics.

[0078] If the query speedup ratio is less than the speedup ratio threshold, the index is deemed insufficient to improve query performance.

[0079] If the space occupancy rate is greater than the occupancy rate threshold, it is determined that the index storage resources are being consumed too much.

[0080] If the overhead of a write operation exceeds the overhead threshold, the index is deemed to be interfering too much with write operations.

[0081] After obtaining the query speedup ratio, space utilization, and write operation overhead, corresponding index adjustment decision information can be generated based on the relationship between these metrics and their corresponding thresholds. Specifically, if the query speedup ratio is less than the speedup ratio threshold (indicating insufficient index performance improvement), the space utilization rate is greater than the space utilization rate threshold (indicating excessive index storage resource consumption), and the write operation overhead is greater than the overhead threshold (indicating excessive index interference with write operations), then a decision to delete the index is generated. In other words, since all three metrics fail to meet the standards, a "delete index" decision is generated to avoid unnecessary resource consumption.

[0082] Step 402: If the query speedup ratio is greater than or equal to the speedup ratio threshold, the space occupancy rate is greater than the occupancy rate threshold, and the write operation overhead is greater than the overhead threshold, generate decision information for rebuilding the index.

[0083] Rebuilding an index refers to the process of optimizing the adaptability of an existing index in a database to query requirements and reducing maintenance costs by adjusting the fields contained in the index (adding necessary fields or deleting redundant fields) while retaining its core index structure (such as B+ trees, hash tables, and other underlying storage logic).

[0084] If the query speedup ratio is greater than or equal to the speedup ratio threshold, the space utilization rate is greater than the space utilization rate threshold, and the write operation overhead is greater than the overhead threshold, the system can generate decision information to rebuild the index. That is, although the speedup effect meets the target, but the space and overhead exceed the threshold, the system generates a "rebuild index" decision. By rebuilding, fragmentation is eliminated, and space consumption and maintenance costs are reduced.

[0085] Step 403: If the target query field has no corresponding index and the query time when the index is disabled is greater than a preset time threshold, generate decision information for creating an index.

[0086] In practical applications, when the target query field has no corresponding index, it is necessary to evaluate the performance bottleneck of queries related to that field. Specifically, the "target query statement" (high-frequency query statement) containing the target query field can be simulated (or the actual runtime performance data can be recorded). Since the target field has no index, the index is in a "disabled" state when the query is executed (essentially, no index is available). At this time, the actual time taken for the query is recorded, i.e., "query time when the index is disabled".

[0087] If the query time is greater than the preset time threshold when the index is disabled, it indicates that the query efficiency for the target field is too low due to the lack of an index, which has affected business performance. An index needs to be created to accelerate the query. In this case, decision information for creating the index can be generated.

[0088] This application's embodiments accurately generate decisions on deleting, retaining, or rebuilding indexes by comparing index performance metrics with thresholds. This not only eliminates inefficient and redundant indexes to reduce resource waste, but also retains high-quality indexes to ensure query performance. Furthermore, it can rebuild valuable but flawed indexes, ultimately achieving dynamic adaptation of the index structure and precise balance of resource costs, thereby improving the overall operating efficiency of the database.

[0089] Next, combined Figure 5 The implementation process of index adjustment is described in detail.

[0090] Reference Figure 5 The diagram illustrates a flowchart of an index adjustment method provided in an embodiment of this application. Figure 5 As shown, the index adjustment method may include steps 501, 502, 503, 504, and 505.

[0091] Step 501: Parse the data query log and the data modification log to obtain the parsing results.

[0092] In this embodiment, the parsing results refer to the set of key metrics obtained after structured analysis of the query logs and modification logs, used to evaluate the potential of a field as an index key. In this example, the parsing results include, but are not limited to: field usage frequency, field cardinality, change frequency, change volume, query time, index fragmentation parameters, and data distribution characteristics.

[0093] After obtaining the data query log and data modification log, you can parse the data query log and data modification log to get the parsing results.

[0094] Specifically, for data query logs: each SELECT statement in the query log can be parsed to identify the tables and fields involved, and record the position of the fields in the query (WHERE / JOIN / ORDER BY, etc.). Simultaneously, "field usage frequency" can be calculated: grouped by field, the number of occurrences per unit time is accumulated. Furthermore, "query time" can be calculated: for queries containing the target field, the average execution time, total time, and time distribution are statistically analyzed to obtain the query time.

[0095] For data modification logs: INSERT / UPDATE / DELETE statements in the modification logs can be parsed to identify the modified fields and record the operation type and timestamp. "Change Frequency" statistics: Grouped by field, the number of modifications per unit time is accumulated. "Change Quantity" calculation: For a single modification operation, the number of records involving that field is counted (e.g., in UPDATE table SET score=100 WHERE class_id=1, the change quantity for 'score' is the number of records corresponding to 'class_id').

[0096] Calculation of field cardinality and data distribution: The "field cardinality" is obtained through database metadata (such as INFORMATION_SCHEMA.COLUMNS) or sampling queries (such as SELECT COUNT(DISTINCT field) FROM table). Histograms (such as those generated by ANALYZE TABLE) or sampling queries are used to determine whether field values ​​are uniform and whether there are hotspot values ​​(such as SELECT field, COUNT(*) FROM table GROUP BY field ORDER BY COUNT(*) DESC LIMIT 10), thus analyzing the data distribution characteristics.

[0097] Evaluate index fragmentation parameters: For existing indexes, obtain index page utilization, scan efficiency, etc. through database interfaces (such as sys.schema_unused_indexes in MySQL, pg_stat_user_indexes in PostgreSQL) and calculate the degree of fragmentation (e.g., "fragmentation rate = 1 - actual data volume / total index capacity").

[0098] The above indicators are summarized by field to form a structured parsing result (such as a table or JSON format) containing the above parameters.

[0099] Step 502: Based on the parsing results, select candidate index keys from the historical query statements of the database.

[0100] Candidate index keys refer to fields (or combinations of fields) selected from historical query statements that have potential for adjustment; in other words, "fields that may be suitable for creating / adjusting indexes." Examples include user_id, which is frequently used as a filter condition in queries, and create_time, which is used as a sorting field.

[0101] After obtaining the parsing results, candidate index keys can be selected from the database's historical query statements based on these results. Specifically, fields with index adjustment potential can be initially selected from all fields, while fields that are clearly unsuitable as index keys (such as fields used infrequently or with low cardinality) can be excluded. The selection method for candidate indexes will be discussed in the following embodiments. Figure 6 A detailed description will not be repeated here.

[0102] Step 503: Calculate the query performance impact index of the candidate index key.

[0103] The query performance impact index is an indicator that quantifies the potential of candidate index keys to adjust query performance. It takes into account factors such as the query frequency of the field, the current query time, and the index maintenance cost. The higher the index, the greater the improvement in overall query performance after adjustment.

[0104] After obtaining the candidate index keys, the query performance impact index of the candidate index keys can be calculated, that is, the adjustment value of each candidate index key is quantified, and the query benefits and maintenance costs are comprehensively considered to provide a basis for the final selection.

[0105] In this example, the formula for calculating the query performance impact index can be predefined: Query Performance Impact Index = a × Query Benefit - b × Maintenance Cost. Here, a and b are weights that can be adjusted according to the business scenario. For example, in OLTP systems that prioritize query performance, a can be set to 0.7; in write-intensive scenarios that prioritize maintenance costs, b can be set to 0.6, etc.

[0106] Among them, query benefit refers to the total query time reduction after adjustment, which is positively correlated with "field usage frequency × current query time × expected adjustment ratio". For example, "a certain field is used in 100 queries with an average time of 50ms. After index adjustment, the expected time is reduced to 10ms, and the query benefit = 100 × (50 - 10) = 4000ms", etc.

[0107] Maintenance cost: The additional overhead of index write operations, which is positively correlated with "change frequency × change volume × single maintenance time" (refer to the previous calculation logic of "index write operation overhead"). For example, "a certain field changes 30 times / hour, with an average change volume of 100 records, and a single maintenance time of 2ms, the maintenance cost = 30 × 100 × 2 = 6000ms".

[0108] Of course, if the candidate key has a highly fragmented index, the "fragmentation repair benefit" can be increased (e.g., if the query time is reduced by 20% after rebuilding the index, the query benefit will be improved). If the field data distribution is skewed (e.g., hot values ​​account for 30%), the expected adjustment ratio should be reduced (queries for hot values ​​may still require a full table scan, and the index benefit is limited).

[0109] When calculating the query performance impact indicator, an index can be calculated for each candidate index key according to the formula to obtain a quantitative score (e.g., 1-100 points, the higher the score, the greater the adjustment value).

[0110] Step 504: Select the target index key from the candidate index keys based on the query performance impact index.

[0111] The target index key refers to the field (or combination of fields) selected from the candidate index keys that is ultimately determined to require index adjustment. It is usually the candidate key with the highest impact on query performance.

[0112] After obtaining the query performance impact index, the target index key, i.e. the field with the highest value, can be selected from the candidate index keys based on the query performance impact index, and the object that needs to be adjusted is determined.

[0113] In practical implementation, target thresholds can be preset, such as selecting only candidate keys with an index ≥ 60, or selecting Top N (e.g., the top 5) candidate keys.

[0114] During the selection process, if multiple candidate index keys belong to the same table and have overlapping functions (such as user_id and (user_id, name)), the composite index with broader coverage is preferred (such as (user_id, name) which can cover user_id queries alone).

[0115] If the maintenance cost of a candidate index key is too high (e.g., the index is negative, which means the overall performance will decrease after adjustment), it should be excluded even if the query benefit is high.

[0116] Step 505: Based on the target index key and the index adjustment decision information, perform an index adjustment operation on the database.

[0117] Index adjustment operations refer to adjustments made to database indexes based on the target index key, including but not limited to: creating new indexes (such as creating a B+ tree index for user_id), deleting redundant indexes, rebuilding fragmented indexes, and adjusting index types (such as changing a regular index to a covering index).

[0118] After obtaining the target index key through filtering, index adjustment operations can be performed on the database based on the target index key and index adjustment decision information. Specifically, for a target index key that does not have an index: create a new index (e.g., CREATE INDEX idx_user_id ON table(user_id)), and select the index type according to the data distribution (e.g., B+ tree indexes are suitable for range queries, and hash indexes are suitable for equality queries).

[0119] For the target index key of an existing index: 1. If fragmentation is severe, perform a rebuild (REBUILD INDEX) or adjust (OPTIMIZE TABLE). 2. If the index is redundant, delete the redundant index. 3. If the index type does not match, adjust the index type, such as changing a regular index to a covering index.

[0120] This application embodiment extracts key indicators by parsing logs, determines the target index key through candidate screening and performance impact index quantification, and performs adjustments. It can accurately identify high-value index adjustment objects, improve query performance while balancing write operation maintenance costs, and achieve dynamic and efficient index adjustment based on actual load.

[0121] Next, combined Figure 6 The process of filtering candidate index keys is described in detail.

[0122] Reference Figure 6 The diagram illustrates a flowchart of a candidate index key filtering method provided in an embodiment of this application. Figure 6 As shown, the candidate index key filtering method may include steps 601, 602, 603 and 604.

[0123] Step 601: Filter out the first data field from the historical query statements, whose field usage frequency is greater than a first threshold, whose field base is greater than a preset base threshold, and whose change frequency is less than a second threshold, and use the first data field as a candidate index key to be added.

[0124] In this embodiment, the parsing results may include: field usage frequency, field cardinality, change frequency, change amount, query time, index fragmentation parameters, and data distribution characteristics, wherein, Field usage frequency refers to the number of times a field is used in a query statement per unit of time (such as the frequency of its appearance in WHERE, JOIN, ORDER BY, and GROUP BY).

[0125] Cardinality refers to the number of distinct values ​​in a field (i.e., the number of unique values), reflecting the uniqueness of the field. For example, the field "gender" has only two values, 'male' and 'female', so its cardinality is 2; the field "user_id" has 1 million distinct values, so its cardinality is 1 million.

[0126] Change frequency refers to the number of times a field is modified (UPDATE / DELETE) per unit of time.

[0127] Change volume refers to the number of records (or total data volume) involved in a single modification operation for that field.

[0128] Query time refers to the average execution time (or total time) of a query statement that includes this field.

[0129] Index fragmentation parameters refer to the degree of disorder in the storage structure of an existing index due to frequent modifications, and are usually measured by "index page utilization" and "hole rate". For example, "the page utilization of the index idx_user_id is only 60%, and there are 40% holes (fragmentation)".

[0130] Data distribution characteristics refer to the distribution patterns of field values, such as whether they are evenly distributed, whether there are hot spots (frequently occurring values), and whether they conform to a normal distribution. For example, the field order_date may be concentrated in the last 3 days of each month (hot spot distribution), and the field age may be evenly distributed between 18 and 60 years old.

[0131] Historical query statements refer to the SELECT query text and execution information recorded in the database in the past.

[0132] The first threshold and the second threshold refer to manually set filtering thresholds, which are used to determine whether a field is used frequently or changed infrequently.

[0133] The first data field refers to a field that meets the criteria of "high usage frequency, high cardinality, and low change frequency," and is therefore valuable for adding new indexes.

[0134] The candidate index key to be added refers to the first data field for which an index is to be created.

[0135] After obtaining the parsing results, fields can be extracted from historical query statements. The first data field with the following characteristics can be filtered out by the log parsing results: "field usage frequency > first threshold, cardinality > preset cardinality threshold, change frequency < second threshold" and marked as the index key to be added.

[0136] Step 602: Filter out the second data field whose usage frequency is less than the third threshold from the historical query statements, and use the index key corresponding to the second data field that already exists in the database as the candidate index key to be deleted; wherein, the third threshold is less than the first threshold.

[0137] The candidate index key to be deleted refers to the existing index corresponding to the second data field to be deleted.

[0138] The third threshold is also a manually set filtering threshold (the third threshold < the first threshold), used to determine whether the field is used infrequently.

[0139] After obtaining the parsing results, you can filter the second data field whose "field usage frequency < third threshold" based on the parsing results, and mark the corresponding index that already exists in the database as the index key to be deleted.

[0140] Step 603: Based on the index fragmentation parameters and the data distribution characteristics, select candidate index keys to be rebuilt from the historical query statements.

[0141] Candidate index keys to be rebuilt refer to existing indexes whose performance has degraded due to severe fragmentation or deteriorated data distribution.

[0142] After obtaining the parsing results, candidate index keys to be rebuilt can be selected from historical query statements based on the index fragmentation parameters and data distribution characteristics. Specifically, existing indexes can be marked as index keys to be rebuilt based on fields with index fragmentation parameters (B+ tree depth > 4 levels or index page idle rate > 30%) or deteriorating data distribution characteristics (the proportion of high-frequency values ​​changes from ≤10% to ≥50% and the proportion of corresponding query index scan rows is <50%).

[0143] Step 604: Use the candidate index key to be added, the candidate index key to be deleted, and the candidate index key to be rebuilt as the candidate index key.

[0144] After obtaining the candidate index keys to be added, deleted, and rebuilt, these candidate index keys can be used as candidate index keys.

[0145] This application embodiment comprehensively identifies high-value newly added indexes, redundant indexes to be deleted, and indexes to be rebuilt due to performance degradation by screening multi-dimensional indicators and judging precise thresholds, so as to realize the dynamic adjustment of indexes, improve query efficiency, reduce redundant maintenance costs, and adapt to changes in data distribution and query load.

[0146] Next, combined Figure 7 The process of filtering the target index key is described in detail.

[0147] Reference Figure 7 The diagram illustrates a flowchart of a target index key filtering method provided in an embodiment of this application. Figure 7 As shown, the target index key filtering method may include steps 701, 702, 703 and 704.

[0148] Step 701: Select candidate index keys from the candidate index keys to be added whose query performance impact index is greater than the index threshold, and use them as target new index keys.

[0149] In this embodiment, the index threshold is a critical value used to filter high-value indexes to be added. Only candidate keys whose query performance impact index exceeds this value are considered worth adding.

[0150] The target new index key refers to the index key selected from the candidates to be added that meets the query performance impact index and is worth creating.

[0151] After obtaining the candidate index keys to be added, you can filter them to select those with a query performance impact index greater than an index threshold, and use them as the target index keys to be added. For example, if the index threshold is set to 60, only candidates with an index ≥ 60 are retained, and their addition is considered to significantly improve query performance, and these are used as the target index keys to be added.

[0152] Step 702: Select candidate index keys from the candidate index keys to be deleted whose query performance impact index is less than the preset elimination threshold, and use them as target index keys to be deleted.

[0153] The preset elimination threshold is a critical value used to filter redundant indexes to be deleted. Candidate keys whose query performance impact index is lower than this value are considered to have maintenance costs that outweigh benefits and should be eliminated.

[0154] The target index key to be deleted refers to the redundant index key that is selected from the candidates to be deleted, whose query performance impact index is too low and should be eliminated.

[0155] After obtaining the candidate index keys to be deleted, you can filter out the candidate index keys whose query performance impact index is less than a preset elimination threshold, and use them as the target index keys for deletion. For example, if the elimination threshold is set to 20, the candidate indexes to be deleted with an index ≤ 20 are determined to be redundant (high maintenance cost and low query benefit) and used as the target index keys for deletion.

[0156] Step 703: Select candidate index keys from the candidate index keys to be rebuilt whose query performance impact index is greater than the reconstruction threshold, and use them as target index keys for reconstruction.

[0157] The rebuild threshold is a critical value used to filter indexes that need to be rebuilt. Candidate keys whose query performance impact index exceeds this value are considered to have a significant performance improvement after rebuilding.

[0158] The target index key for reconstruction refers to the index key selected from the candidates to be reconstructed that meets the query performance impact index and will yield significant benefits after reconstruction.

[0159] After obtaining the candidate index keys to be rebuilt, you can filter out the candidate index keys whose query performance impact index is greater than the rebuild threshold, and use them as the target index keys for rebuilding. For example, if the rebuild threshold is set to 50, the candidate index keys with an index ≥ 50 are considered to be those that can effectively improve performance after rebuilding (such as reducing fragmentation and adapting to changes in data distribution), and are used as the target index keys for rebuilding.

[0160] Step 704: Use the target new index key, the target delete index key, and the target rebuild index key as the target index key.

[0161] After obtaining the target's newly added index key, target's deleted index key, and target's rebuilt index key, these three keys can be used as the target's index key.

[0162] This application embodiment uses a dual screening method of query performance impact index and precise threshold to accurately identify high-value newly added indexes, low-value redundant deleted indexes, and high-return rebuilt indexes from candidate index keys, thereby achieving quantitative decision-making for index adjustment. This improves query efficiency while eliminating invalid maintenance costs and dynamically adapts to changes in database load and data characteristics.

[0163] Next, combined Figure 8The process of index adjustment is described in detail.

[0164] Reference Figure 8 The diagram illustrates a flowchart of another index adjustment method provided in an embodiment of this application. Figure 8 As shown, the index adjustment method may include steps 801, 802 and 803.

[0165] Step 801: When the index adjustment decision information indicates that an index should be created, perform an index creation adjustment operation on the database based on the target new index key.

[0166] In this embodiment, after obtaining the target newly added index key, the database can be adjusted based on the target newly added index key when the index adjustment decision information indicates that an index should be created. That is, for the target newly added index key, an appropriate index type (such as B+ tree index, hash index, spatial index, etc.) is selected according to its data type and query scenario (such as equality query, range query). An index creation statement is generated, such as `CREATE INDEX idx_target ON table(column)`, which can be executed during off-peak business hours to avoid blocking online services.

[0167] Verify index validity after execution: Confirm that the query on the target field has used the new index and that the query time has been reduced to the expected range by using the execution plan (such as EXPLAIN).

[0168] Step 802: When the index adjustment decision information indicates that an index should be deleted, perform an index deletion adjustment operation on the database based on the target deleted index key.

[0169] After obtaining the target index key to be deleted, when the index adjustment decision information indicates that the index should be deleted, the database can be adjusted by performing index deletion operations based on the target index key. Specifically, for the target index key, an index deletion statement is generated, such as `DROP INDEX idx_target ON table`. The impact of deletion on business operations is first tested on the slave or standby database. After confirming that no queries depend on the index (by checking index usage through query logs or database tools), the deletion operation is performed on the primary database. After monitoring write operation performance (e.g., whether INSERT / UPDATE times decrease) after deletion, the effectiveness of redundant index removal is verified.

[0170] Step 803: When the index adjustment decision information indicates that the index should be rebuilt, perform an index rebuild adjustment operation on the database based on the target rebuilt index key.

[0171] After obtaining the target index key for reconstruction, the database can be restructured based on this key when the index adjustment decision information indicates that the index should be rebuilt. Specifically, for the target index key, the reconstruction method is selected according to the index type: B+ tree indexes can use the `REBUILD INDEX` statement or `ALTER TABLE ... FORCEINDEX`; in cases of severe fragmentation, a "delete first, then rebuild" approach can be used. During the reconstruction process, monitor metrics such as index page utilization and B+ tree depth to ensure that fragmentation parameters (such as idle rate) return to a reasonable range (e.g., ≤10%) after reconstruction. After reconstruction is complete, the effect can be verified: by comparing query time and analyzing the percentage of rows scanned by the index, it can be confirmed that query performance has recovered to the expected level.

[0172] This application embodiment transforms the previously selected target index keys into actual performance tuning results by precisely executing the creation, deletion, and reconstruction of indexes: adding indexes improves the efficiency of high-frequency queries, deleting redundant indexes reduces the maintenance cost of write operations, and rebuilding indexes repairs performance degradation issues, ultimately achieving dynamic balance and continuous adjustment of database read and write performance to adapt to changes in business load and data characteristics.

[0173] Next, combined Figure 9 Provide a detailed description of the index management system. For example... Figure 9 As shown, the index management system may include: a real-time monitor, a data collection module, a query analysis module, a data change tracking module, an index evaluation engine, a user intervention interface, an index optimization decision-maker, and index operation management.

[0174] Among them, the real-time monitor is used to continuously monitor the performance indicators and query execution status of the database system, and capture the real-time usage of system resources and performance fluctuations.

[0175] The data collection module is responsible for collecting database query logs, execution plans, data modification logs, etc., to provide raw data for subsequent analysis.

[0176] The query analysis module analyzes query logs, extracts query templates and parameter patterns, and uses machine learning algorithms to identify query patterns and frequently queried fields. Query templates refer to de-parameterized SQL statement patterns, while parameter patterns refer to the distribution patterns of parameters in the query. For example, value distribution: 80% of the `status` parameter is "paid," and time range: `order_date` is frequently used to query data from the last 7 days.

[0177] The index evaluation engine can comprehensively evaluate the performance of existing indexes by combining query analysis results and data changes.

[0178] The data tracking change module is used to track data addition, deletion, and modification operations, and to count the frequency and amount of field changes, providing data support for evaluating the effectiveness of the index.

[0179] The index optimization decision-maker can generate index adjustment recommendations based on index evaluation and data changes, including creating, deleting, or rebuilding indexes. Data changes refer to records in database tables (such as user information and order details) whose changes are caused by business operations (such as user registration or product listing / delisting) or data corrections (such as error data repair).

[0180] Index operation management is used to automatically perform index creation, deletion, and reconstruction operations, ensuring minimal impact on database performance and maintaining data consistency. Specifically, it can be implemented when query performance is below a threshold (e.g., average latency > 500ms) or when a certain score in the index evaluation results is too low (e.g., space usage / speedup ratio does not meet the standard).

[0181] The user intervention interface allows database administrators to review and modify recommendations made by the index optimization decision-maker, using human experience to assist automated optimization.

[0182] Database systems can receive and execute index operations, and their structure is influenced by optimization decisions.

[0183] Next, combined Figure 10 The process of adaptive index management is described in detail. For example... Figure 10 As shown, the adaptive index management process may include: 1. Collect database query logs: This means collecting the query logs generated by the database during its operation.

[0184] 2. Analyze query patterns: Based on the collected query logs, analyze common query patterns and hot data. Hot data refers to specific data that is frequently accessed, such as product details information of a popular product.

[0185] 3. Track data changes: Continuously monitor data changes, such as data insertion, update, and deletion operations.

[0186] 4. Evaluate existing indexes: Evaluate existing indexes to determine their performance and suitability.

[0187] 5. Generate index optimization decisions: Based on the evaluation results, the system generates targeted index optimization decisions (i.e., the index adjustment decision information in this example).

[0188] 6. Whether to perform optimization: This involves determining whether to perform index optimization (i.e., index adjustment) based on the index optimization decision-maker. Specifically, optimization is performed when the index optimization decision-maker determines that the benefits of adjustment (such as performance improvement) outweigh the costs (such as write overhead). If the benefits of adjustment are not significant or may cause performance risks, the status quo is maintained and no optimization is performed (i.e., the status quo is left unchanged).

[0189] 7. Maintain the status quo, that is, do not perform index optimization and maintain the existing index state.

[0190] 8. Perform index operation management, which means automatically performing index operations when index optimization is needed.

[0191] 9. Continue monitoring: Regardless of whether the index has been optimized, the system continues to monitor for future decision-making.

[0192] For the timing process of adaptive index management, it can be combined with Figure 11 Please describe it as follows. For example... Figure 11 As shown, the time-series process of this adaptive index management includes: 1. Start monitoring: The DBA (Database Administrator) sends a monitoring command to the real-time monitor, which can then start real-time monitoring of the database based on the command.

[0193] 2. Data Collection: The data collection module can collect database-related data from the real-time monitoring system, including data query logs and data modification logs.

[0194] 3. Query Log Delivery: The data collection module sends the collected data to the query analysis module for in-depth analysis. Specifically, the query analysis module can use algorithms such as clustering and pattern recognition to identify high-frequency query patterns and hot fields (i.e., high-frequency query patterns and target query fields), as well as information such as the frequency and amount of changes to fields in the database, and then pass the analyzed information to the index evaluation engine.

[0195] 4. Evaluate index performance: The index evaluation engine can evaluate the performance of existing indexes in the database based on collected data (i.e., high-frequency query patterns and target query fields, as well as information such as the frequency and amount of field changes).

[0196] 5. Determine optimization decisions: The index evaluation engine can send the performance evaluation results to the index optimization decision maker. The decision maker can generate an optimization plan based on the evaluation results, that is, generate decision results for deleting the index, rebuilding the index, or creating the index.

[0197] 6. Index Operation Management: The index optimization decision results can be sent by the index optimization decision maker to the index operation management, which can then execute the creation, deletion, or reconstruction of indexes according to the plan based on the decision results.

[0198] 7. Database System: After the index optimization operation is completed, the results can be fed back to the database administrator by the database system.

[0199] This application's embodiments combine real-time data monitoring, intelligent query analysis, data change tracking, index performance evaluation, and automatic decision optimization to create a self-adjusting and optimizing adaptive index management system. Real-time monitoring provides the system with the latest performance data, enabling data-driven decision-making; intelligent query analysis identifies hot data and query patterns; and automatic decision adjustment reduces manual intervention and improves index management efficiency.

[0200] In this embodiment, a regression model can also be trained to predict the performance of the new index based on historical index performance data (such as index type, field cardinality, and query pattern). The model training process may include: 1. Data Preparation: Collect historical SQL queries, field characteristics, query times, etc. In addition to basic SQL queries and field characteristics, supplement with index lifecycle data (such as index creation / deletion time, performance changes before and after each rebuild), system environment characteristics (such as database version, hardware configuration, cache hit rate), and concurrency scenario data (such as index performance under peak QPS) to avoid poor model generalization due to environmental differences. Data cleaning and enhancement can be performed on the collected data: outlier filtering (such as removing extremely time-consuming queries caused by table locking / network fluctuations), data normalization (unifying the query time unit for different tables / fields), and time slicing (dividing data according to business cycles, such as e-commerce promotional periods vs. daily routines) to ensure the consistency and representativeness of the training data.

[0201] 2. Label Definition: Query speedup ratio is used as the target value of the regression model. However, query speedup ratio alone is insufficient to measure index value; a comprehensive benefit label is needed: 1. Net Benefit = (Reduction in query time × Query frequency) - (Increase in write operation overhead × Change frequency). 2. Resource Efficiency = Net Benefit / Index Storage Space. This is to avoid indexes with "high speedup ratio but high write overhead" being mistakenly identified as high-quality candidates.

[0202] 3. Feature Engineering: Extract field type, cardinality, frequency, and operation type. Time decay features can also be added: For indicators such as field usage frequency and change frequency, an exponential decay function can be used to assign higher weights to recent data (e.g., the weight of data from the last 7 days is twice that of data from 30 days ago) to adapt to changes in business models. Interactive features can also be constructed: such as "field cardinality × query filter condition complexity" (to determine index selectivity and query matching degree) and "index type × data distribution characteristics" (e.g., the adaptability of B+ trees to range queries), capturing non-linear relationships between features. Feature dimensionality reduction can also be performed: Core features are selected based on the importance of features in the tree model (e.g., features with a correlation <0.1 with query performance are removed) to reduce noise interference.

[0203] 4. Model Training: Multi-model fusion can be employed: XGBoost captures structured features, LSTM processes temporal features (such as fluctuations in query frequency over time), and weighted voting is used to output prediction results, improving accuracy in complex scenarios. Enhanced cross-validation: Training and validation sets are hierarchically divided according to "business line + time" (e.g., training with data from business line A and validating with data from business line B; training with data from 2023 and validating with data from 2024) to avoid overfitting. A dynamic update mechanism is also used: model parameters are fine-tuned every period (e.g., weekly) using newly generated index performance data, or a full retraining is triggered (when data distribution drift exceeds a threshold, such as a field cardinality change rate > 50%).

[0204] 5. Deployment and Application: Input new candidate fields for the index → ​​Output their predicted query performance improvement and write cost assessment. During model deployment, real-time monitoring data (such as current system load and cache status) can be integrated to dynamically adjust the model output (e.g., lowering the predicted index write cost under high load). A model explanation module is also included: A partial dependency graph demonstrates the "core reasons why a certain field is recommended" (e.g., "High cardinality + 70% range query ratio → significant benefits from B+ tree index") to assist human decision-making.

[0205] 6. Decision logic: Predict the expected speedup or performance score after adding an index to the new field.

[0206] This application embodiment enhances data dimensions, optimizes feature engineering and model fusion, and combines dynamic thresholds and gray-scale verification mechanisms. This model can accurately predict the query boost and write overhead of the new index, realizing index adjustment and upgrade from experience-driven to data model-driven dual-drive, effectively balancing query acceleration and write performance loss, and adapting to complex business loads and data feature changes.

[0207] Reference Figure 12 The diagram shows a schematic representation of a database index adjustment device according to an embodiment of this application. Figure 12 As shown, the database index adjustment device 1200 may include the following modules: The log acquisition module 1210 is used to acquire the data query logs and data modification logs generated by the database during operation; The performance evaluation module 1220 is used to evaluate the performance of the indexes in the database based on the data query log and the data modification log, and obtain the index performance evaluation result. The index performance evaluation result includes: parameters of the impact of the indexes in the database on data query performance. The decision generation module 1230 is used to generate index adjustment decision information for the database based on the index performance evaluation results. The index adjustment module 1240 is used to adjust the index of the database based on the data query log and the data modification log when the index adjustment decision information indicates that the database should be adjusted.

[0208] Optionally, the performance evaluation module includes: The first parsing unit is used to parse the data query log to obtain the query patterns and target query fields with query frequencies higher than a threshold in the historical query statements of the database. The query pattern refers to the set of typical features and patterns presented by the query requests initiated to the database. The second parsing unit is used to parse the data modification log to obtain the change frequency and change amount of the fields in the database; The performance evaluation unit is used to evaluate the performance of the indexes in the database based on the query pattern, the target query field, the change frequency, and the change amount, and to obtain the index performance evaluation result.

[0209] Optionally, the performance evaluation unit includes: The target statement filtering subunit is used to filter out target query statements that contain the target query field based on historical query statements with a query frequency higher than a threshold in the query mode. The speedup calculation subunit is used to simulate the execution of the target query statement in the database and calculate the query speedup ratio of the index based on the recorded query time when the index is enabled and the query time when the index is disabled. The occupancy calculation subunit is used to calculate the space occupancy rate of the index based on the total storage capacity of the data table to which the index belongs and the number of the target query fields; The cost calculation subunit is used to count the number of write operations involving the target query field per unit time based on the change frequency and the change amount, and to calculate the write operation cost of the index based on the index maintenance cost of a single write operation and the number of write operations. The evaluation result determination sub-unit is used to determine the query speedup ratio, the space utilization rate, and the write operation overhead as the index performance evaluation result.

[0210] Optionally, the decision generation module includes: The first decision generation unit is used to generate decision information for deleting the index when the query speedup ratio is less than the speedup ratio threshold, the space occupancy rate is greater than the occupancy rate threshold, and the write operation overhead is greater than the overhead threshold. The second decision generation unit is used to generate decision information for rebuilding the index when the query speedup ratio is greater than or equal to the speedup ratio threshold, the space occupancy rate is greater than the occupancy rate threshold, and the write operation overhead is greater than the overhead threshold. The third decision generation unit is used to generate decision information for creating an index when the target query field has no corresponding index and the query time when the index is disabled is greater than a preset time threshold.

[0211] Optionally, the indexed adjustment module includes: The log parsing unit is used to parse the data query log and the data modification log to obtain the parsing result; An index key filtering unit is used to filter candidate index keys from historical query statements in the database based on the parsing results. The impact index calculation unit is used to calculate the query performance impact index of the candidate index key; The target key filtering unit is used to filter the candidate index keys to obtain the target index key based on the query performance impact index; An index adjustment unit is used to perform index adjustment operations on the database based on the target index key and the index adjustment decision information.

[0212] Optionally, the parsing results include: field usage frequency, field cardinality, change frequency, change volume, query time, index fragmentation parameters, and data distribution characteristics; The index key filtering unit includes: The first index key acquisition sub-unit is used to filter out the first data field from the historical query statement whose field usage frequency is greater than a first threshold, whose field base is greater than a preset base threshold, and whose change frequency is less than a second threshold, and use the first data field as a candidate index key to be added; The second index key acquisition subunit is used to filter out second data fields whose usage frequency is less than a third threshold from the historical query statements, and to use the index key corresponding to the second data field that already exists in the database as a candidate index key to be deleted; wherein, the third threshold is less than the first threshold; The third index key acquisition subunit is used to filter out candidate index keys to be rebuilt from the historical query statements based on the index fragmentation parameters and the data distribution characteristics. The candidate index key acquisition subunit is used to use the candidate index key to be added, the candidate index key to be deleted, and the candidate index key to be rebuilt as the candidate index key.

[0213] Optionally, the target key filtering unit includes: A new index key acquisition sub-unit is added to filter out candidate index keys whose query performance impact index is greater than the index threshold from the candidate index keys to be added, and use them as the target new index keys; The deletion index key acquisition sub-unit is used to filter out candidate index keys whose query performance impact index is less than a preset elimination threshold from the candidate index keys to be deleted, and use them as target deletion index keys. The index key acquisition sub-unit is used to filter out candidate index keys whose query performance impact index is greater than the reconstruction threshold from the candidate index keys to be rebuilt, and use them as target index keys to be rebuilt. The target index key acquisition subunit is used to obtain the target addition index key, the target deletion index key, and the target reconstruction index key as the target index key.

[0214] Optionally, the index adjustment unit includes: The first adjustment subunit is used to perform an index creation adjustment operation on the database based on the target new index key when the index adjustment decision information indicates that an index should be created. The second adjustment subunit is used to perform an index deletion adjustment operation on the database based on the target deletion index key when the index adjustment decision information indicates that an index should be deleted. The third adjustment subunit is used to perform an index reconstruction adjustment operation on the database based on the target reconstruction index key when the index adjustment decision information indicates that the index should be rebuilt.

[0215] This application's embodiments automatically analyze query load characteristics (such as high-frequency / time-consuming queries and dependent fields) and data structure changes (such as data volume growth and frequently modified fields) by collecting query and modification logs from the database, thus achieving automatic analysis of query load and data structure changes. Based on log data, the actual performance of indexes on queries (utilization, acceleration effect) is evaluated, a quantitative correlation between load and index adaptability is established, and targeted adjustment decisions are generated (adding necessary indexes, deleting redundant indexes, adjusting inefficient structures, etc.). Adjustments are then precisely executed based on the logs, ultimately dynamically balancing the relationship between query performance and maintenance costs, reducing index maintenance costs, and improving the overall efficiency of the database.

[0216] This application also provides an electronic device, such as... Figure 13 As shown, it includes a processor 1301, a communication interface 1302, a memory 1303, and a communication bus 1304. The processor 1301, the communication interface 1302, and the memory 1303 communicate with each other through the communication bus 1304. Memory 1303 is used to store computer programs; When processor 1301 executes a program stored in memory 1303, it performs the following steps: Retrieve the data query logs and data modification logs generated by the database during its operation; Based on the data query log and the data modification log, the performance of the indexes in the database is evaluated to obtain the index performance evaluation results, which include parameters of the impact of the indexes in the database on data query performance. Based on the index performance evaluation results, generate index adjustment decision information for the database; When the index adjustment decision information indicates that the database should be indexed, the database should be indexed based on the data query log and the data modification log.

[0217] The communication bus mentioned above can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into address bus, data bus, control bus, etc. For ease of illustration, only one thick line is used to represent it in the diagram, but this does not mean that there is only one bus or one type of bus.

[0218] The communication interface is used for communication between the aforementioned terminal and other devices.

[0219] The memory may include random access memory (RAM) or non-volatile memory, such as at least one disk storage device. Optionally, the memory may also be at least one storage device located remotely from the aforementioned processor.

[0220] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0221] In another embodiment provided in this application, a computer-readable storage medium is also provided, which stores instructions that, when executed on a computer, cause the computer to perform any of the database index adjustment methods described in the above embodiments.

[0222] In another embodiment provided in this application, a computer program product containing instructions is also provided, on which a computer program is stored, which, when run on a computer, causes the computer to perform any of the above-described database index adjustment methods.

[0223] In the above embodiments, implementation can be achieved entirely or partially through software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented entirely or partially as a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of this application are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that integrates one or more available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid state disk (SSD)).

[0224] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0225] The various embodiments in this specification are described in a related manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the system embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0226] The above description is merely a preferred embodiment of this application and is not intended to limit the scope of protection of this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application are included within the scope of protection of this application.

Claims

1. A method for adjusting a database index, characterized in that, include: Retrieve the data query logs and data modification logs generated by the database during operation; Based on the data query log and the data modification log, the performance of the indexes in the database is evaluated to obtain the index performance evaluation results, which include parameters of the impact of the indexes in the database on data query performance. Based on the index performance evaluation results, generate index adjustment decision information for the database; When the index adjustment decision information indicates that the database should be indexed, the database should be indexed based on the data query log and the data modification log.

2. The method according to claim 1, characterized in that, The step of evaluating the index performance in the database based on the data query log and the data modification log to obtain the index performance evaluation result includes: The data query log is analyzed to obtain the query patterns and target query fields with query frequencies higher than a threshold in the historical query statements of the database. The query pattern refers to the set of typical characteristics and patterns presented by the query requests initiated to the database. The data modification log is analyzed to obtain the change frequency and change volume of the fields in the database; Based on the query pattern, the target query field, the change frequency, and the change amount, the index performance in the database is evaluated to obtain the index performance evaluation result.

3. The method according to claim 2, characterized in that, The process of evaluating the index performance in the database based on the query pattern, the target query field, and the field change frequency, to obtain the index performance evaluation result, includes: Based on historical query statements with a query frequency higher than the threshold in the query pattern, target query statements containing the target query field are filtered out. The target query statement is simulated in the database, and the query speedup ratio of the index is calculated based on the recorded query time when the index is enabled and the query time when the index is disabled. The space utilization rate of the index is calculated based on the total storage capacity of the data table to which the index belongs and the number of the target query fields; Based on the change frequency and the change amount, the number of write operations involving the target query field per unit time is counted, and the write operation cost of the index is calculated based on the index maintenance cost of a single write operation and the number of write operations. The query speedup ratio, the space utilization rate, and the write operation overhead are determined as the index performance evaluation results.

4. The method according to claim 3, characterized in that, The step of generating index adjustment decision information for the database based on the index performance evaluation results includes: If the query speedup ratio is less than the speedup ratio threshold, the space utilization rate is greater than the utilization rate threshold, and the write operation overhead is greater than the overhead threshold, then generate decision information to delete the index. When the query speedup ratio is greater than or equal to the speedup ratio threshold, the space utilization rate is greater than the utilization rate threshold, and the write operation overhead is greater than the overhead threshold, decision information for rebuilding the index is generated. If the query time for the target query field does not have a corresponding index and the index is disabled exceeds a preset time threshold, decision information for creating an index is generated.

5. The method according to claim 1, characterized in that, The process of adjusting the database index based on the data query log and the data modification log includes: The data query log and the data modification log are parsed to obtain the parsing results; Based on the parsing results, candidate index keys are selected from the historical query statements of the database; The query performance impact index of the candidate index key is calculated; The target index key is obtained by filtering the candidate index keys based on the query performance impact index; Based on the target index key and the index adjustment decision information, an index adjustment operation is performed on the database.

6. The method according to claim 5, characterized in that, The analysis results include: field usage frequency, field cardinality, change frequency, change volume, query time, index fragmentation parameters, and data distribution characteristics; The step of filtering candidate index keys from historical query statements in the database based on the parsing results includes: The first data field is selected from the historical query statements. The field has a usage frequency greater than a first threshold, a field size greater than a preset threshold, and a change frequency less than a second threshold. This first data field is then used as a candidate index key to be added. From the historical query statements, select second data fields whose usage frequency is less than a third threshold, and use the index keys corresponding to the second data fields that already exist in the database as candidate index keys to be deleted; wherein, the third threshold is less than the first threshold; Based on the index fragmentation parameters and the data distribution characteristics, candidate index keys to be rebuilt are selected from the historical query statements; The candidate index key to be added, the candidate index key to be deleted, and the candidate index key to be rebuilt are used as the candidate index key.

7. The method according to claim 6, characterized in that, The step of filtering the candidate index keys from the candidate index keys according to the query performance impact index includes: From the candidate index keys to be added, select the candidate index keys whose query performance impact index is greater than the index threshold, and use them as the target new index keys; From the candidate index keys to be deleted, select the candidate index keys whose query performance impact index is less than the preset elimination threshold, and use them as the target index keys to be deleted. From the candidate index keys to be rebuilt, select the candidate index keys whose query performance impact index is greater than the reconstruction threshold, and use them as the target index keys to be rebuilt. The target new index key, the target delete index key, and the target rebuild index key are used as the target index key.

8. The method according to claim 7, characterized in that, The step of performing an index adjustment operation on the database based on the target index key and the index adjustment decision information includes: When the index adjustment decision information indicates that an index should be created, an index creation adjustment operation is performed on the database based on the target newly added index key; When the index adjustment decision information indicates that an index should be deleted, the database is subjected to an index deletion adjustment operation based on the target deleted index key; When the index adjustment decision information indicates that the index should be rebuilt, the index reconstruction adjustment operation is performed on the database based on the target rebuilt index key.

9. A database index adjustment device, characterized in that, include: The log acquisition module is used to acquire data query logs and data modification logs generated by the database during operation. The performance evaluation module is used to evaluate the performance of the indexes in the database based on the data query log and the data modification log, and obtain the index performance evaluation result. The index performance evaluation result includes parameters of the impact of the indexes in the database on data query performance. The decision generation module is used to generate index adjustment decision information for the database based on the index performance evaluation results. An index adjustment module is used to adjust the index of the database based on the data query log and the data modification log when the index adjustment decision information indicates that the database should be adjusted.

10. An electronic device, characterized in that, It includes a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, when executing a program stored in memory, implements the steps of the method described in any one of claims 1-11.

11. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-11.

12. A computer program product containing instructions, wherein a computer program is stored thereon, characterized in that, When the computer program is run on a computer, it causes the computer to perform the method as described in any one of claims 1-8.

Images