Database index optimization method and device, computer device, readable storage medium and program product

Abstract

The application relates to a database index optimization method and device, computer equipment, a computer readable storage medium and a computer program product. The method comprises the following steps: acquiring key SQL performance constraint data and a query statement set, wherein the query statement set comprises a key SQL statement corresponding to the key SQL performance constraint data; generating a candidate database index configuration based on the query statement set and a preset rule; if the performance data of the key SQL statement under the candidate database index configuration does not meet the key SQL performance constraint data, marking the candidate database index configuration as invalid, and returning to the step of generating the candidate database index configuration based on the query statement set and the preset rule. The method can improve the execution efficiency of overall index optimization and guarantee the performance of key business.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a database index optimization method, apparatus, computer device, computer-readable storage medium, and computer program product. Background Technology

[0002] A database index is a data structure in a database management system used to improve data query efficiency. It sorts and stores the values ​​of one or more columns (fields) in a database table, helping the system quickly locate data records that meet query conditions, thereby reducing the scope of data scanning and improving query performance. Indexes consume storage space and can impact the performance of update statements; therefore, index optimization is particularly important.

[0003] Index optimization tools have been developed in related technologies. However, some of these tools can only optimize indexes for single SQL statements (Structured Query Language), failing to achieve optimal performance under global SQL load. Other tools, while recommending globally optimal index configurations at the load level, are less efficient and may sacrifice the performance of some SQL statements. If these SQL statements originate from critical business operations, this can severely damage the business. Summary of the Invention

[0004] Therefore, it is necessary to provide a database index optimization method, apparatus, computer equipment, computer-readable storage medium, and computer program product that can guarantee the performance of critical business operations and achieve high optimization efficiency, in order to address the above-mentioned technical problems.

[0005] Firstly, this application provides a database index optimization method, including:

[0006] Obtain key SQL performance constraint data and a set of query statements, wherein the set of query statements includes key SQL statements corresponding to the key SQL performance constraint data;

[0007] Based on the set of query statements and preset rules, a candidate database index configuration is generated;

[0008] If the performance data of the key SQL statement under the candidate database index configuration does not meet the performance constraints of the key SQL statement, then the candidate database index configuration is marked as invalid, and the step of generating a candidate database index configuration based on the set of query statements and preset rules is returned.

[0009] In one embodiment, the method further includes:

[0010] If the performance data of the key SQL statement under the candidate database index configuration meets the performance constraints of the key SQL statement, then the benefit of the candidate database index configuration is calculated.

[0011] Save the benefits of the candidate database index configuration.

[0012] In one embodiment, the calculation of the benefit of the candidate database index configuration includes:

[0013] Create a virtual index configuration corresponding to the candidate database index configuration;

[0014] The database optimizer is used to obtain the benefit of each SQL statement in the set of query statements under the virtual index configuration;

[0015] The weighted sum of the gains of each SQL statement under the virtual index configuration is used to obtain the gains of the candidate database index configuration.

[0016] In one embodiment, the above-mentioned generation of candidate database index configuration based on the query statement set and preset rules includes:

[0017] Parse the SQL statements in the query statement set to obtain candidate fields;

[0018] Based on a first preset rule and a candidate index set generated according to the candidate fields, the candidate index set includes multiple candidate indexes;

[0019] Based on a preset filtering strategy, a subset of candidate indexes are selected from the candidate index set to obtain the candidate database index configuration.

[0020] In one embodiment, the above-mentioned selection of a portion of candidate indexes from the candidate index set based on a preset filtering strategy to obtain the candidate database index configuration includes:

[0021] Target candidate indices are selected from the candidate index set using a greedy algorithm or a dynamic programming algorithm, and then added to the candidate index configuration.

[0022] The filtering process terminates when a preset termination condition is triggered, and the candidate database index configuration is obtained.

[0023] In one embodiment, the method further includes:

[0024] Determine whether the target database index configuration output conditions are met;

[0025] If so, the candidate database index configuration with the highest benefit will be output as the target database index configuration;

[0026] If not, then evaluate whether the performance data of the key SQL statement under the candidate database index configuration meets the key SQL performance constraints.

[0027] Secondly, this application also provides a database index optimization apparatus, comprising:

[0028] The data acquisition module is used to acquire key SQL performance constraint data and a set of query statements, wherein the set of query statements includes key SQL statements corresponding to the key SQL performance constraint data;

[0029] The index configuration generation module is used to generate candidate database index configurations based on the set of query statements and preset rules;

[0030] The marking module is used to mark the candidate database index configuration as invalid if the performance data of the key SQL statement under the candidate database index configuration does not meet the performance constraints of the key SQL statement, and to control the index configuration generation module to re-execute the operation of generating candidate database index configurations based on the query statement set and preset rules.

[0031] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the database index optimization method described in any of the above embodiments.

[0032] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the database index optimization method described in any of the above embodiments.

[0033] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the database index optimization method described in any of the above embodiments.

[0034] The aforementioned database index optimization method, apparatus, computer equipment, computer-readable storage medium, and computer program product acquire key SQL performance constraint data and a set of query statements, the set of query statements including key SQL statements corresponding to the key SQL performance constraint data; generate candidate database index configurations based on the set of query statements and preset rules; if the performance data of the key SQL statements under the candidate database index configurations does not meet the key SQL performance constraints, the candidate database index configuration is marked as invalid, and the process of generating candidate database index configurations based on the set of query statements and preset rules is returned. This allows for the early identification and elimination of candidate database index configurations that do not meet the key SQL performance constraints, thereby reasonably pruning the index search space and improving the overall efficiency of index optimization. Simultaneously, using the key SQL performance constraint data as the basis for judging the validity of candidate database index configurations ensures that the optimized database index meets key business requirements, thus guaranteeing the performance of key business operations. Attached Figure Description

[0035] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0036] Figure 1 This is a flowchart illustrating a database index optimization method in one embodiment;

[0037] Figure 2 This is a flowchart illustrating a database index optimization method in another embodiment;

[0038] Figure 3 This is a schematic diagram of a sub-process for calculating the benefit of a candidate database index configuration if the performance data of the evaluated key SQL statement under the candidate database index configuration meets the key SQL performance constraint data in one embodiment.

[0039] Figure 4 This is a schematic diagram of a sub-process for generating candidate database index configurations based on a set of query statements and preset rules in one embodiment.

[0040] Figure 5 This is a flowchart illustrating the steps that the database index optimization method in one embodiment also includes;

[0041] Figure 6 This is a flowchart illustrating the database index optimization method in another embodiment;

[0042] Figure 7 This is a structural block diagram of a database index optimization device in one embodiment;

[0043] Figure 8 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0044] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0045] In one exemplary embodiment, such as Figure 1 As shown, a database index optimization method is provided. This embodiment uses the application of this method to a server as an example for illustration. It can be understood that this method can also be applied to a terminal, and can also be applied to a system including a terminal and a server, and can be implemented through the interaction between the terminal and the server. The terminal can be, but is not limited to, various personal computers, laptops, smartphones, tablets, IoT devices, and portable wearable devices. IoT devices can be smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. Portable wearable devices can be smartwatches, smart bracelets, head-mounted devices, etc. Head-mounted devices can be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. The server can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.

[0046] In this example, the database index optimization method includes the following steps S202 to S206. Wherein:

[0047] S202, Obtain key SQL performance constraint data and a set of query statements, the set of query statements including key SQL statements corresponding to the key SQL performance constraint data.

[0048] The critical SQL performance constraint data is defined as the maximum acceptable performance degradation percentage for a critical SQL statement. In one exemplary implementation, this critical SQL performance constraint data can be represented by the parameter ε. Typically, the maximum acceptable performance degradation percentage for a critical SQL statement is no degradation, so the critical SQL performance constraint data ε=0 can be defined.

[0049] The query statement set, also known as the SQL load, represents a collection of multiple SQL statements executed on the database, including the critical SQL statements corresponding to critical SQL performance constraint data.

[0050] Here's an example of an SQL statement: A SQL workload W includes two SQL statements, q1 and q2, where q1: SELECT a FROM t WHERE a>10, and q2: SELECT a,b FROM t WHERE b=1. If q1 is the critical SQL statement, then its critical SQL performance constraint ε=0.

[0051] S204 generates candidate database index configurations based on the set of query statements and preset rules.

[0052] For example, the server can perform syntax parsing on the SQL statements in the query statement set to obtain candidate fields, and then generate a candidate index set according to preset rules such as prefix expansion, random shuffling, and rule generation. Then, according to a certain filtering strategy, a portion of the candidate indexes are selected from the candidate index set to form a candidate database index configuration, which will be described in detail below.

[0053] S206, if the performance data of the critical SQL statement under the candidate database index configuration does not meet the performance constraints of the critical SQL statement, then mark the candidate database index configuration as invalid and return the steps of generating the candidate database index configuration based on the set of query statements and preset rules.

[0054] For example, when evaluating the performance data of a critical SQL statement under a candidate database index configuration, the server can base its evaluation on whether the critical SQL statement can effectively utilize that candidate database index configuration.

[0055] Taking the key SQL statement q1 above as an example, its initial index is (a), and the WHERE condition is a>10 (range query). The database can quickly locate all rows where "a>10" through index (a). If the candidate database index configuration C generated by S204 is C={(b),(b,a)}, and the indexes in set C are (b) and (b,a), that is, the first column of both is b, according to the prefix matching principle of indexes, that is, the query must first match the first column of the index before the index can be used. There are no filtering conditions about b in the query conditions of q1, so the use of indexes (b) or (b,a) cannot be triggered. Therefore, q1 cannot use the indexes in the candidate database index configuration C. The consequence is that q1 can only abandon the indexes in C and perform a full table scan. Compared with the original scan method using index (a), the efficiency is significantly reduced, so the performance drops sharply. At this time, the performance data of the key SQL statement under the candidate database index configuration C is ε1>0. ε1 does not meet the constraint condition of ε=0 for the performance constraint data of the key SQL of q1. Therefore, the candidate database index configuration C is marked as invalid. Understandably, subsets {(b)} and {(b,a)} of candidate database index configuration C will also be marked as invalid, since these subsets are also unusable by q1.

[0056] Candidate database index configurations marked as invalid will be discarded and will not be further selected; the database optimizer does not need to perform performance evaluation on these discarded candidate index configurations. At this point, the server has completed filtering a set of candidate database index configurations and needs to return to the step of generating candidate database index configurations for the next round of evaluation.

[0057] In the aforementioned database index optimization method, key SQL performance constraint data and a set of query statements are obtained. The query statement set includes key SQL statements corresponding to the key SQL performance constraint data. Based on the query statement set and preset rules, candidate database index configurations are generated. If the performance data of the key SQL statements under the candidate database index configuration does not meet the key SQL performance constraints, the candidate database index configuration is marked as invalid, and the step of generating candidate database index configurations based on the query statement set and preset rules is returned. This allows for the early identification and elimination of candidate database index configurations that do not meet the key SQL performance constraints, thereby reasonably pruning the index search space and improving the overall efficiency of index optimization. Simultaneously, using the key SQL performance constraint data as the basis for judging the validity of candidate database index configurations ensures that the optimized database index meets key business requirements, thus guaranteeing the performance of key business processes.

[0058] In one exemplary embodiment, such as Figure 2 As shown, the above database index optimization method further includes steps S208 to S210. Wherein:

[0059] S208. If the performance data of the critical SQL statement under the candidate database index configuration meets the critical SQL performance constraint data, then calculate the benefit of the candidate database index configuration.

[0060] For example, if the server evaluates the performance data of the key SQL statement under the candidate database index configuration and it meets the key SQL performance constraint data, such as the performance data ε1=0 of the key SQL statement under the candidate database index configuration, that is, the performance of the key SQL statement does not decrease under the candidate database index configuration, and therefore meets the constraint condition of key SQL performance constraint data ε=0, then it is necessary to further calculate the benefit of the candidate database index configuration in order to add the candidate database index configuration to the index search space as one of the candidate effective index configurations.

[0061] It should also be noted that the benefit mentioned here refers to the absolute benefit, given a candidate database index configuration and a SQL statement. The benefit of the candidate database index configuration equals the execution cost of the SQL statement under the original index minus the execution cost under the candidate database index configuration. Dividing this by the execution cost of the original index yields the relative benefit. Given a set of SQL workloads, the benefit of the candidate database index configuration equals the sum of the benefits brought to all SQL statements in the workload. This can also be understood as a weighted sum of the benefits brought to all SQL statements in the workload.

[0062] It's also important to understand that SQL execution cost is a core quantitative metric used by database optimizers to evaluate the efficiency of SQL statement execution. Essentially, it's an estimate of the system resources (such as disk I / O, CPU usage, and memory consumption) required to execute SQL. It's used to select the lowest-cost solution from multiple possible execution plans and is also a core basis for calculating index benefits and optimizing query performance. SQL execution cost is highly positively correlated with actual execution efficiency; the lower the cost, the faster the execution speed.

[0063] S210, save the benefits of candidate database index configuration.

[0064] In this example, if the performance data of the critical SQL statement under the candidate database index configuration meets the critical SQL performance constraints, the candidate database index configuration is considered valid. The benefit of the candidate database index configuration is calculated and saved so that it can be added to the index search space as one of the candidate valid index configurations. Its benefit can be used as a screening criterion in subsequent screening, thereby making it easier to select the index configuration with the greatest benefit.

[0065] In one exemplary embodiment, such as Figure 3 As shown, step S208 includes steps S302 to S306, wherein:

[0066] S302, Create a virtual index configuration corresponding to the candidate database index configuration.

[0067] A virtual index is a mechanism in a database that simulates the existence of an index but does not actually store it. It is mainly used to optimize query performance or test the effectiveness of an index and does not require the allocation of physical storage space. When evaluating the benefits of an index, creating an index directly on the database may consume a lot of I / O and CPU resources, causing SQL queries and updates to slow down, or even time out or block high-frequency write operations. To avoid these problems, a virtual index configuration corresponding to the candidate database index configuration can be created first.

[0068] S304 utilizes the database optimizer to obtain the benefit of each SQL statement in the query statement set under the virtual index configuration.

[0069] The database optimizer is a core component of the database management system kernel. It quantifies the execution cost of each execution plan (including candidate index configurations) for input SQL statements, selects the optimal execution plan with the lowest cost, and guides the database to execute the SQL, thereby maximizing query performance. Specifically, the server uses the database optimizer to evaluate the execution cost of each SQL statement under the original index and the execution cost under candidate index configurations. The difference between the two yields the absolute benefit of each SQL statement. Dividing the absolute benefit by the execution cost of the original index gives the relative benefit.

[0070] S306 calculates the weighted sum of the benefits of each SQL statement under the virtual index configuration to obtain the benefits of the candidate database index configuration.

[0071] For example, the weighted sum of the above benefits can be performed by weighting the absolute benefit of each SQL statement or by weighting the relative benefit of each SQL statement. The resulting sum of benefits represents the benefit of the candidate database index configuration in terms of global SQL load.

[0072] In this embodiment, by creating a virtual index configuration, the database optimizer is used to obtain the benefit of each SQL statement, and the benefit of each SQL statement is weighted and summed to obtain the benefit of the candidate database index configuration in the global SQL load, which facilitates the subsequent selection of the globally optimal index configuration at the load level.

[0073] In one exemplary embodiment, such as Figure 4 As shown, step S204 includes steps S402 to S406. Wherein:

[0074] S402, parse the SQL statements in the query statement set to obtain candidate fields.

[0075] For example, the server can perform syntax parsing on the SQL statements in the query statement set to obtain a set of candidate fields.

[0076] For example, taking the SQL load W mentioned above as an example, which includes two SQL statements, q1 and q2, after parsing the syntax of q1 and q2 in W, the candidate field group is (a, b).

[0077] S404, Based on the first preset rule and according to the candidate fields, a candidate index set is generated, which includes multiple candidate indexes.

[0078] In one exemplary implementation, the first preset rule may include, but is not limited to, one or more of the following: prefix expansion, random shuffling, and rule generation. Prefix expansion starts with the first field of the field group and adds fields sequentially to generate all prefix subset indexes. Random shuffling randomly arranges the fields in the field group to generate indexes with different orders, while adhering to core rules such as the rule of putting equal fields first. Rule generation strictly follows preset rules (e.g., recommending that the maximum number of indexes does not exceed a threshold) to directly generate indexes that conform to the rules. Taking the candidate field group (a, b) above as an example, using the first preset rule (prefix expansion, random shuffling, and rule generation), four candidate indexes can be obtained: (a), (b), (a, b), and (b, a). These four candidate indexes together form the candidate index set.

[0079] S406, Based on a preset filtering strategy, select a portion of the candidate indexes from the candidate index set to obtain the candidate database index configuration.

[0080] In an optional embodiment, S406 includes steps S502 to S504. Wherein:

[0081] S502 uses a greedy algorithm or dynamic programming algorithm to filter target candidate indexes from the candidate index set and adds them to the candidate index configuration.

[0082] A greedy algorithm is a step-by-step strategy for selecting the optimal solution: at each step, it chooses the option with the highest profit under the current circumstances, without looking back at previous choices, and finally assembles a solution that is better overall. For example, suppose the profits of the four candidate indices (a), (b), (a,b), and (b,a) are 100, 200, 300, and 500 respectively. In the first round, the candidate index (b,a) with the highest profit is selected and added to the candidate index configuration A, at which point A={(b,a)}. In the second round, the candidate index (a,b) with the highest profit is selected from the remaining candidate indices, at which point A={(b,a), (a,b)}, and so on.

[0083] Dynamic programming is a strategy that dynamically adjusts index configuration based on factors such as real-time data characteristics, load changes, and database operating status. It adaptively adjusts the index generation strategy according to changes in the actual environment, making the generated candidate database index configurations more suitable for the current database needs. For example, priority sorting can be dynamically adjusted based on load frequency. Specifically, different priority weights can be assigned to query statements in the SQL load, with higher weights assigned to high-frequency query statements, thus prioritizing the generation of indexes for high-frequency queries. Another example is adjusting indexes based on write pressure. If the database write pressure is high, the generation of indexes with high write overhead (such as covering indexes) is reduced, and single-column indexes with low write pressure are prioritized.

[0084] Of course, in practical applications, the greedy algorithm and the dynamic programming algorithm can also be combined. For example, continuing with the above candidate index configuration A, if the greedy algorithm is used, the first round should select candidate index (b,a) to add to candidate index configuration A. However, the dynamic programming algorithm detects that the current write pressure is extremely high when selecting (b,a), so it selects the single-column index (b). In the second round, the greedy algorithm is used to select the candidate index (a,b) with the greatest benefit from the remaining candidate indexes (a) and (a,b). After two rounds of selection, A={(b), (a,b)} is obtained.

[0085] S504, when the preset termination condition is triggered, the filtering is terminated and the candidate database index configuration is obtained.

[0086] For example, the preset termination conditions include, but are not limited to, the upper limit of the number of indexes in the index configuration, such as no more than 2, 3, or 4, to control the storage overhead of the indexes. Taking the preset termination condition of no more than 2 as an example, when the number of target candidate indexes added to the candidate index configuration reaches 2, the filtering is terminated. For example, if A={(b,a), (a,b)} meets the termination condition, the filtering is terminated and the selected database index configuration is obtained.

[0087] In this example, candidate fields are obtained by parsing SQL statements, a candidate index set is generated based on the first preset rule, and candidate database index configurations are obtained by filtering from the candidate index set based on the preset filtering strategy, which helps to improve the quality and generation efficiency of candidate database index configurations.

[0088] In one exemplary embodiment, such as Figure 5 As shown, the above method further includes steps S602 to S606. Wherein:

[0089] S602, determine whether the target database index configuration output conditions are met.

[0090] The output conditions for the target database index configuration include traversing all candidate database index configurations or reaching a preset optimization time limit. For example, the optimization time limit can be any time period such as 24 hours, a week, a month, a quarter, or half a year.

[0091] S604, if so, output the candidate database index configuration with the highest benefit as the target database index configuration.

[0092] This step involves comparing the benefits of various candidate database index configurations to determine the one with the greatest benefit. Since the candidate database index configurations whose benefits are calculated and recorded all satisfy the key SQL performance constraints, and the benefit represents the benefit of the global SQL load, the target database index configuration with the greatest benefit selected satisfies both the performance constraints of the key SQL and provides a good global benefit to the SQL load, thus achieving a balance between global optimization and local performance constraints.

[0093] S606, If not, evaluate whether the performance data of the critical SQL statement under the candidate database index configuration meets the critical SQL performance constraints.

[0094] This example demonstrates the target database index configuration that yields the greatest benefit, satisfying both the performance constraints of critical SQL queries and providing a good overall benefit to the SQL load, thus achieving a balance between global optimization and local performance constraints. If the output conditions are not met, candidate database index configurations can be continuously optimized.

[0095] In one specific embodiment, such as Figure 6 As shown, a database index optimization method is provided, including the following steps:

[0096] Step 1: Receive SQL load information and key SQL performance constraints;

[0097] Step 2: Generate a candidate index set;

[0098] Step 3: Select candidate indexes to form an effective index configuration C;

[0099] Step 4: Determine whether the index configuration has been traversed completely or the stopping condition has been met;

[0100] Step 5: If yes, output the index configuration that yields the greatest benefit;

[0101] Step 6: If not, evaluate the performance of key SQL queries under index configuration C;

[0102] Step 7: Determine whether index configuration C meets the critical SQL performance constraints of the critical SQL.

[0103] Step 8: If yes, record the benefit of index configuration C to SQL load and return to step 3;

[0104] Step 9: If not, mark index configuration C and its subsets as invalid and return to step 3.

[0105] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0106] Based on the same inventive concept, this application also provides a database index optimization apparatus for implementing the database index optimization method described above. The solution provided by this apparatus is similar to the implementation scheme described in the above method; therefore, the specific limitations in one or more database index optimization apparatus embodiments provided below can be found in the limitations of the database index optimization method described above, and will not be repeated here.

[0107] In one exemplary embodiment, such as Figure 7 As shown, a database index optimization device 700 is provided, including: a data acquisition module 701, an index configuration generation module 702, and a marking module 703, wherein:

[0108] The data acquisition module 701 is used to acquire key SQL performance constraint data and a set of query statements, the set of query statements including key SQL statements corresponding to the key SQL performance constraint data;

[0109] The index configuration generation module 702 is used to generate candidate database index configurations based on the set of query statements and preset rules;

[0110] The marking module 703 is used to mark the candidate database index configuration as invalid if the performance data of the evaluation key SQL statement under the candidate database index configuration does not meet the key SQL performance constraint data, and to control the index configuration generation module to re-execute the operation of generating candidate database index configurations based on the query statement set and preset rules.

[0111] In one exemplary embodiment, the device further includes:

[0112] The revenue calculation module is used to calculate the revenue of the candidate database index configuration if the performance data of the evaluation key SQL statement under the candidate database index configuration meets the key SQL performance constraints.

[0113] The save module is used to save the benefits of candidate database index configurations.

[0114] In an exemplary embodiment, the revenue calculation module is further configured to create a virtual index configuration corresponding to the candidate database index configuration; use the database optimizer to obtain the revenue of each SQL statement in the query statement set under the virtual index configuration; and perform a weighted summation of the revenue of each SQL statement under the virtual index configuration to obtain the revenue of the candidate database index configuration.

[0115] In an exemplary embodiment, the index configuration generation module 702 is further configured to parse the SQL statements in the query statement set to obtain candidate fields; generate a candidate index set based on a first preset rule and according to the candidate fields, the candidate index set including multiple candidate indexes; and filter some candidate indexes from the candidate index set based on a preset filtering strategy to obtain candidate database index configurations.

[0116] In an exemplary embodiment, the index configuration generation module 702 is further configured to use a greedy algorithm or a dynamic programming algorithm to filter target candidate indexes from the candidate index set and add them to the candidate index configuration; when a preset termination condition is triggered, the filtering is terminated to obtain the candidate database index configuration.

[0117] In one exemplary embodiment, the device further includes:

[0118] The judgment module is used to determine whether the target database index configuration output conditions are met;

[0119] The index configuration output module is used to output the candidate database index configuration with the highest benefit as the target database index configuration when the target database index configuration output conditions are met.

[0120] The evaluation module is used to evaluate whether the performance data of key SQL statements under candidate database index configurations meets the key SQL performance constraints when the output conditions of the target database index configuration are not met.

[0121] Each module in the aforementioned database index optimization device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0122] In one exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 8 As shown, this computer device includes a processor, memory, input / output (I / O) interfaces, and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media. The database stores critical SQL performance constraints and sets of query statements. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When executed by the processor, the computer program implements a database index optimization method.

[0123] Those skilled in the art will understand that Figure 8 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0124] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the database index optimization method described in any of the above embodiments.

[0125] In one exemplary embodiment, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps of the database index optimization method described in any of the above embodiments.

[0126] In one exemplary embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps of the database index optimization method described in any of the above embodiments.

[0127] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0128] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0129] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0130] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A database index optimization method, characterized in that, The method includes: Obtain key SQL performance constraint data and a set of query statements. The set of query statements includes key SQL statements corresponding to the key SQL performance constraint data. The key SQL performance constraint data is denoted as ε=0, which means that the maximum acceptable performance degradation of the key SQL statement is no degradation. Based on the set of query statements and preset rules, a candidate database index configuration is generated; If the performance data of the key SQL statement under the candidate database index configuration does not meet the performance constraints of the key SQL, then the candidate database index configuration is marked as invalid, and other invalid subsets derived from the candidate database index configuration are also marked as invalid. Then, the step of generating candidate database index configuration based on the set of query statements and preset rules is returned. If the performance data of the key SQL statement under the candidate database index configuration meets the performance constraints of the key SQL statement, then the benefit of the candidate database index configuration is calculated. Save the benefits of the candidate database index configuration.

2. The method according to claim 1, characterized in that, The calculation of the benefits of the candidate database index configuration includes: Create a virtual index configuration corresponding to the candidate database index configuration; The database optimizer is used to obtain the benefit of each SQL statement in the set of query statements under the virtual index configuration; The weighted sum of the gains of each SQL statement under the virtual index configuration is used to obtain the gains of the candidate database index configuration.

3. The method according to claim 1, characterized in that, The step of generating candidate database index configurations based on the set of query statements and preset rules includes: Parse the SQL statements in the query statement set to obtain candidate fields; Based on a first preset rule and a candidate index set generated according to the candidate fields, the candidate index set includes multiple candidate indexes; Based on a preset filtering strategy, a subset of candidate indexes are selected from the candidate index set to obtain the candidate database index configuration.

4. The method according to claim 3, characterized in that, The step of selecting a subset of candidate indexes from the candidate index set based on a preset filtering strategy to obtain the candidate database index configuration includes: Target candidate indices are selected from the candidate index set using a greedy algorithm or a dynamic programming algorithm, and then added to the candidate index configuration. The filtering process terminates when a preset termination condition is triggered, and the candidate database index configuration is obtained.

5. The method according to any one of claims 1 to 4, characterized in that, The method further includes: Determine whether the target database index configuration output conditions are met; If so, the candidate database index configuration with the highest benefit will be output as the target database index configuration; If not, then evaluate whether the performance data of the key SQL statement under the candidate database index configuration meets the key SQL performance constraints.

6. A database index optimization device, characterized in that, The device includes: The data acquisition module is used to acquire key SQL performance constraint data and a set of query statements. The set of query statements includes key SQL statements corresponding to the key SQL performance constraint data. The key SQL performance constraint data is denoted as ε=0, which means that the maximum acceptable performance degradation of the key SQL statement is no degradation. The index configuration generation module is used to generate candidate database index configurations based on the set of query statements and preset rules; The marking module is used to mark the candidate database index configuration as invalid if the performance data of the key SQL statement under the candidate database index configuration does not meet the performance constraints of the key SQL statement, and to mark other invalid subsets derived from the candidate database index configuration as invalid. Then, it controls the index configuration generation module to re-execute the operation of generating candidate database index configurations based on the query statement set and preset rules. The revenue calculation module is used to calculate the revenue of the candidate database index configuration if the performance data of the key SQL statement under the candidate database index configuration meets the performance constraint data of the key SQL. A storage module is used to store the benefits of the candidate database index configuration.

7. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the method of any one of claims 1 to 5.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 5.

9. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 5.

Images