Query optimization method, storage medium and computer equipment

Abstract

The invention relates to a database technology, in particular to a query optimization method, a storage medium and computer equipment. The query optimization method comprises the steps that a population is initialized and generated based on a genetic algorithm, and individuals in the population comprise randomly-generated query execution plans; executing at least one mutation operation to generate a new population until the new population meets a preset termination condition; wherein the further step of each mutation operation can comprise the following steps: generating a mutation individual based on a differential evolution algorithm at a preset probability; and inserting the variation individuals into the population to obtain a new population. According to the method, the differential evolution algorithm and the genetic algorithm are fused for query optimization, the problem that a traditional genetic algorithm is prone to falling into a local optimal solution is avoided, the global search capability is improved, the execution effect of query optimization is improved, and then the performance of a database is improved.

Description

Technical Field

[0001] The present invention relates to database technology, and in particular to a query optimization method, a storage medium and a computer device. Background Art

[0002] In the field of database management systems, query optimization is an important research direction, whose goal is to find the most efficient execution plan for a given database query. Traditional query optimization methods rely on cost-based models and heuristic rules, but these methods may not cover all possible query solutions, especially when faced with complex queries and large databases. To overcome these limitations, genetic algorithms (GAs) have been introduced into database query optimization. GA is a heuristic search method based on natural selection and genetic mechanisms, which simulates the biological evolution process to find the optimal solution.

[0003] In the context of database query optimization, GA usually includes steps such as initializing the population, selection, crossover, and mutation. The initial population consists of a series of randomly generated query execution plans, each of which is an individual. By evaluating the performance of each plan, such as execution time or resource consumption, the plan with better performance is selected for crossover and mutation operations to generate a new population. This process is repeated in multiple generations until the termination condition is met. In addition, GA discovers optimal or near-optimal query execution plans by simulating the process of natural selection and genetic mechanisms. Mutation is one of the key steps of genetic algorithms, which introduces randomness to explore the search space, helps the algorithm jump out of the local optimal solution, and increases the diversity of the population.

[0004] The role of mutation operators in genetic algorithms is to randomly change certain parts (operations / nodes) of certain individuals to create new individuals, which helps the algorithm explore new possibilities and prevents all individuals from becoming the same and falling into local optimal solutions. In the context of database query optimization, mutation can be changing the order of an operation in the query execution plan, selecting a different index, or changing the connection strategy.

[0005] However, the mutation operation used by existing genetic algorithms in database query optimization, although helpful in introducing diversity, the mutation operator in existing genetic algorithms relies on randomness and may be inefficient when exploring large or complex search spaces. It is not enough to ensure systematic exploration of the search space, especially in complex queries and large data sets, which may cause the algorithm to fall into a local optimal solution and affect the speed and quality of the optimization process. Therefore, although existing genetic algorithms provide a flexible and powerful framework for database query optimization, there is still room for improvement in execution efficiency and optimization quality. Summary of the invention

[0006] In view of the above problems, the present invention proposes a query optimization method, a storage medium and a computer device that overcome the above problems or at least partially solve the above problems.

[0007] One object of the present invention is to enhance the global search capability of query optimization to improve the execution effect of query optimization.

[0008] A further object of the present invention is to improve the effectiveness and executability of query optimization results.

[0009] In particular, the present invention provides a query optimization method, which comprises:

[0010] Initializing and generating a population based on a genetic algorithm, wherein individuals in the population include randomly generated query execution plans;

[0011] Perform at least one mutation operation to generate a new population until the new population meets the preset termination condition;

[0012] Each mutation operation further includes:

[0013] Generate mutant individuals based on differential evolution algorithm with preset probability;

[0014] Insert the mutant individuals into the population to obtain a new population.

[0015] Optionally, generating mutant individuals based on the differential evolution algorithm includes:

[0016] Perform differential vector operations according to the mutation strategy of the differential evolution algorithm to generate mutation vectors;

[0017] Determine whether the mutation vector meets the preset availability conditions;

[0018] If not, the mutation vector is subjected to boundary processing;

[0019] The mutation vector after boundary processing is serialized into unique values ​​to obtain the mutation individual.

[0020] Optionally, performing a difference vector operation according to a mutation strategy of a differential evolution algorithm to generate a mutation vector includes:

[0021] Three different individuals are randomly selected from the population;

[0022] The difference between any two of the three different individuals is calculated, and the product of the difference and the scaling factor is added to another one of the three different individuals to generate a mutation vector.

[0023] Optionally, insert the mutant individuals into the population to obtain a new population including:

[0024] Calculate the fitness value of the mutated individual;

[0025] According to the ranking of the fitness values ​​of each individual in the population, the mutant individual is inserted into the population, and the individual with the smallest fitness value in the ranking is removed to form a new population; and

[0026] The scaling factor can be adaptively adjusted according to the fitness value of the mutated individuals.

[0027] Optionally, performing boundary processing on the mutation vector includes:

[0028] Generates a sequence of unique values ​​based on the number of elements in the mutation vector;

[0029] Create a mapping dictionary, which is used to store the mapping relationship between each element in the mutation vector and the unique value in the unique value sequence;

[0030] Construct a mapping relationship between each element in the mutation vector and the unique value in the unique value sequence;

[0031] According to the mapping relationship, each element in the mutation vector is replaced with the unique value mapped to it.

[0032] Optionally, performing unique value serialization processing on the mutation vector after boundary processing includes:

[0033] The repeated elements in the mutation vector after boundary processing are corrected until all elements in the mutation vector are unique values ​​without duplication.

[0034] Optionally, generating a mutant individual based on a differential evolution algorithm with a preset probability includes:

[0035] Obtain the number of executions of the mutation operation and determine whether the number of executions reaches a preset threshold;

[0036] If so, the mutant individuals are generated based on the differential evolution algorithm;

[0037] If not, a new individual formed by hybridization of two individuals randomly selected from the population is mutated based on a genetic algorithm to generate a mutant individual.

[0038] Optionally, the preset termination conditions include:

[0039] The number of iterations of the new population reaches a predetermined iteration threshold.

[0040] According to another aspect of the present invention, a machine-readable storage medium is provided, on which a machine-executable program is stored. When the machine-executable program is executed by a processor, any one of the above-mentioned query optimization methods is implemented.

[0041] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements any of the above query optimization methods when executing the machine executable program.

[0042] The query optimization method of the present invention performs at least one mutation operation to generate a new population based on a genetic algorithm to generate a query execution plan whose individuals are randomly generated, until the new population meets a preset termination condition, thereby realizing the optimal solution for the query scheme based on the genetic algorithm. In addition, the query optimization method of the present invention further includes generating mutant individuals based on the differential evolution algorithm with a preset probability and inserting the mutant individuals into the population to obtain a new population through each mutation operation, thereby realizing the integration of the differential evolution algorithm into the genetic algorithm to improve the existing genetic algorithm. The query optimization method of the present invention integrates the differential evolution algorithm and the genetic algorithm, and uses the differential evolution algorithm to perform mutation operations, thereby introducing richer diversity by utilizing the difference information in the population, so as to more effectively explore potential query execution plans, thereby enhancing the global search capability of the query optimizer, and further improving the execution effect of query optimization.

[0043] Furthermore, the query optimization method of the present invention refines the operational process of generating mutant individuals based on the differential evolution algorithm, by setting a step of judging whether the mutant vector meets the preset usability conditions after the step of performing the difference vector operation according to the mutation strategy of the differential evolution algorithm, and performing boundary processing and unique value serialization processing on the mutant vector when the mutant vector does not meet the preset usability conditions, so that there are no values ​​within a predetermined range or unreasonable values ​​such as complex numbers or decimals in the mutant individuals, thereby ensuring the availability of the mutant individuals, thereby improving the effectiveness and executability of the query optimization results, and further improving the accuracy of the query optimization.

[0044] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0046] Figure 1 is a schematic diagram of a query optimization method according to an embodiment of the present invention;

[0047] Figure 2is a schematic diagram of generating mutant individuals based on a differential evolution algorithm in a query optimization method according to an embodiment of the present invention;

[0048] Figure 3 is a flow chart of a query optimization method according to an embodiment of the present invention;

[0049] Figure 4 is a flow chart of generating mutant individuals based on a differential evolution algorithm in a query optimization method according to an embodiment of the present invention;

[0050] Figure 5 is a schematic diagram of a machine-readable storage medium according to an embodiment of the present invention; and

[0051] Figure 6 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0052] The exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided in order to enable a more thorough understanding of the present disclosure and to enable the scope of the present invention to be fully communicated to those skilled in the art.

[0053] Figure 1 FIG. 1 is a schematic diagram of a query optimization method according to an embodiment of the present invention. Figure 1 As shown, the query optimization method may generally include:

[0054] Step S102, based on the genetic algorithm initialization, a population is generated, wherein the individuals in the population include randomly generated query execution plans. It should be noted that, in the context of database query optimization, the genetic algorithm generally includes steps such as initializing the population, selection, crossover, and mutation. The initial population consists of a series of randomly generated query execution plans, each of which is an individual.

[0055] Step S104, performing at least one mutation operation to generate a new population, until the new population meets a preset termination condition. In a specific embodiment, the termination condition in this step may include that a sufficiently good solution has been generated in the new population. In another specific embodiment, the termination condition in this step may also include that the number of iterations of the new population reaches a predetermined iteration number threshold.

[0056] It should be noted that genetic algorithms usually include steps such as population initialization, selection, crossover, and mutation. The mutation process in traditional genetic algorithms includes the following steps:

[0057] 1. Select individuals: randomly select one or more individuals from the current population for mutation;

[0058] 2. Determine the mutation point: In the query execution plan of the selected individual, randomly select one or more points (such as specific operations or nodes) as mutation points;

[0059] 3. Apply mutation operation: Apply mutation operation to the selected mutation point, where the mutation operation includes replacement, addition, deletion or reordering operation;

[0060] 4. Evaluation and retention: Evaluate the performance of the mutated individuals. If the performance improves or to maintain diversity, the mutated individuals can be retained. Otherwise, the mutated individuals are discarded.

[0061] In a specific example, in the context of database query optimization, an array such as [1,2,3,4,5,6,7,8,9] can represent the specific order of operations in a query execution plan, and each number may represent the order in which a specific table is added to the query. For example: number 1 represents the first table scanned or added to the query; number 2 represents the second table added, which may be joined with the first table; and so on, until number 9, which represents the last table added to the query. In a multi-table join query, the order in which the tables are added has a significant impact on query performance.

[0062] An optimization process may involve changing the order of these operations to find a more efficient execution path, which can reduce the required memory, speed up data retrieval, or reduce the overall computational complexity. Genetic algorithms improve the specific order of operations in the query execution plan through a continuous iterative process, including operations such as selection, crossover, and mutation, to generate multiple different arrays, and evaluate the performance of each generated query execution plan, thereby selecting the best plan for actual query execution.

[0063] That is, in the genetic algorithm, this array can be regarded as an individual chromosome, and each number (gene) represents a specific decision in the query plan. The genetic algorithm evolves a better query execution plan by simulating the process of natural selection (including crossover and mutation). Specifically, the traditional genetic algorithm can include the following steps to perform the mutation operation:

[0064] 1. Initialize an individual group: [1,2,3,4,5,6,7,8,9], this sequence represents an individual or a solution;

[0065] 2. Randomly select two positions to exchange, for example, randomly select position 5 and position 7 (that is, number 5 and number 7), exchange their positions to get a new sequence [1,2,3,4,7,6,5,8,9];

[0066] 3. Exchange the positions of two genes (i.e. the numbers in the sequence) to obtain the offspring sequence, for example, the offspring sequence is [1,2,6,4,5,3,7,8,9];

[0067] 4. Continue to perform steps 2 and 3, and execute num_gene (number of genes) - 1 times.

[0068] Therefore, a gene in the parent sequence will be randomly changed through the mutation operation, and a new offspring sequence will be obtained. The query execution plan represented by the offspring sequence may be evaluated as a more efficient query execution plan due to the reduction of the cost of certain connection operations. The genetic algorithm will repeat this mutation process, selecting the best performing individual as the next generation each time, until a sufficiently good solution is found, or other stopping conditions are met, such as reaching a predetermined number of iterations. In database query optimization, this means finding a query execution plan that is both fast and efficient.

[0069] However, traditional genetic algorithms rely on randomness when randomly selecting one or more individuals from the current population for mutation and randomly selecting one or more operations / nodes as mutation points among the selected individuals. They are inefficient when exploring large or complex search spaces and are not sufficient to systematically lead to the global optimal solution, thus easily falling into the local optimum.

[0070] To this end, the embodiment of the present invention particularly proposes an improved query optimization method, which combines the differential evolution algorithm and the genetic algorithm, in order to overcome the shortcomings of the traditional genetic algorithm in terms of global search ability, thereby improving the execution effect of query optimization. In the embodiment, the execution method of the mutation operation in the above step S104 is as follows: Figure 2 As shown, each mutation operation may further include the following steps:

[0071] Step S202, generating mutant individuals based on the differential evolution algorithm with a preset probability. It should be noted that the differential evolution algorithm is known for its efficient global search capabilities, especially in continuous optimization problems. The mutation operation of the differential evolution algorithm generates new candidate solutions by considering the differences in the population, thereby providing an effective global exploration mechanism.

[0072] Step S204, insert the mutant individuals into the population to obtain a new population. That is, when performing the mutation operation, the mutation operation of the differential evolution algorithm can be introduced to assist the mutation operator of the traditional genetic algorithm to enhance the global search capability and make the population more diverse.

[0073] The query optimization method of the embodiment of the present invention realizes the optimal solution of the query scheme based on the genetic algorithm by performing at least one mutation operation to generate a new population after initializing the query execution plan with randomly generated individuals based on the genetic algorithm until the new population meets the preset termination condition. In addition, the query optimization method of the embodiment of the present invention further includes generating mutant individuals based on the differential evolution algorithm with a preset probability through each mutation operation, and inserting the mutant individuals into the population to obtain a new population, thereby realizing the integration of the differential evolution algorithm into the genetic algorithm to improve the existing genetic algorithm. The query optimization method of the embodiment of the present invention integrates the differential evolution algorithm and the genetic algorithm, uses the differential evolution algorithm for mutation operations, enhances the global search capability of the query optimizer, thereby more effectively exploring potential query execution plans, and improves the robustness of the query optimizer under different query loads and data distributions, thereby maintaining a stable optimization effect in a wider range of application scenarios, thereby improving the execution effect of query optimization.

[0074] At the same time, the query optimization method of the embodiment of the present invention improves the quality of the query execution plan found through the global search characteristics of differential evolution, thereby reducing the execution cost and improving the system performance. In addition, the mutation operator in the traditional genetic algorithm may lead to the loss of population diversity, limiting the exploration space of the optimization process, while the query optimization method of the embodiment of the present invention adopts the mutation operation of the differential evolution algorithm, which can use the difference information in the population to introduce richer diversity to avoid premature convergence, thereby increasing the diversity of the solution, further improving the quality of the query execution plan found, and further improving the execution effect of the query optimization.

[0075] In some embodiments, after the above step S102, the query optimization method of the embodiment of the present invention may further include the following steps: calculating the fitness values ​​of the individuals in the population and ranking them. In addition, the above step S204 may specifically include the following steps: calculating the fitness values ​​of the variant individuals; inserting the variant individuals into the population according to the ranking of the fitness values ​​of each individual in the population, and removing the individual with the smallest fitness value in the ranking to form a new population. That is to say, after generating the variant individuals, the fitness values ​​of the newly generated variant individuals are calculated, and they are inserted into the population according to the ranking of the fitness values, and the individuals ranked last in the population are removed.

[0076] The query optimization method of the embodiment of the present invention obtains a new population by inserting the mutant individuals generated based on the differential evolution algorithm into the population according to the fitness value ranking, thereby improving the quality of the query execution plan within the population and further improving the execution effect of the query optimization.

[0077] In some embodiments, the above step S202 may specifically include the following steps: obtaining the number of executions of the mutation operation, and determining whether the number of executions reaches a preset threshold; if so, generating a mutant individual based on a differential evolution algorithm; if not, mutating a new individual formed by hybridization of two individuals randomly selected from a population based on a genetic algorithm to generate a mutant individual. That is to say, when performing a mutation operation, the mutation strategy in the differential evolution algorithm is adopted with a certain probability. This means that the mutation strategy in the differential evolution algorithm is not adopted every time, but is randomly decided whether to adopt it according to a set probability. For example, if the preset probability is set to 20%, then the mutation strategy in the differential evolution algorithm will be adopted approximately once every 5 times. Therefore, the query optimization method of the embodiment of the present invention introduces randomness to avoid the algorithm from falling into a local optimal solution, thereby increasing the possibility of finding a global optimal solution.

[0078] In some embodiments, the above step S202 can be specifically performed as the following steps: performing a difference vector operation according to the mutation strategy of the differential evolution algorithm to generate a mutation vector; determining whether the mutation vector meets the preset usable condition; if not, performing boundary processing on the mutation vector; performing unique value serialization processing on the mutation vector after boundary processing to obtain a mutant individual. It should be noted that the preset usable condition can include that the elements in the mutation vector are all non-repeating integers.

[0079] The query optimization method of the embodiment of the present invention refines the operation process of generating mutant individuals based on the differential evolution algorithm, and sets a step of judging whether the mutant vector meets the preset availability condition after the step of performing the difference vector operation according to the mutation strategy of the differential evolution algorithm, and performs boundary processing and unique value serialization processing on the mutant vector when the mutant vector does not meet the preset availability condition, so that there are no values ​​within a predetermined range or unreasonable values ​​such as complex numbers or decimals in the mutant individuals, thereby ensuring the availability of the mutant individuals, thereby improving the effectiveness and executability of the query optimization results, and further improving the accuracy of the query optimization.

[0080] In some embodiments, the above-mentioned step of performing a difference vector operation according to the mutation strategy of the differential evolution algorithm to generate a mutation vector may include the following steps: randomly selecting three different individuals in the population; calculating the difference between any two of the three different individuals, and adding the product of the difference and the scaling factor to another one of the three different individuals to generate a mutation vector.

[0081] Specifically, the mutation strategy of the differential evolution algorithm is shown in the following formula:

[0082] V i,G+1 =X r1,G +F*(X r2,G -X r3,G );

[0083] Among them, X r1 , X r2 and X r3 are three different individuals randomly selected from the current population, F represents the scaling factor, V i,G+1 represents the obtained mutation vector, i represents the individual number in the population, and G is the evolutionary generation.

[0084] In this solution, F can be set to a fixed value or can be adaptively adjusted according to the improvement of the fitness value. In a specific embodiment, the scaling factor can be adaptively adjusted according to the fitness value of the variant individual. The specific adjustment method is: the fitness value of the variant individual is ranked with the fitness value of the individuals in the population to determine the current search stage; a larger F is used in the early stage of the search to enhance the global search capability; and the F value is reduced in the later stage of the search to carefully search the current area to accurately locate the optimal solution. In other words, the value of F can depend on the specific problem and the current search stage to achieve adaptive adjustment.

[0085] In some embodiments, the above-mentioned step of performing boundary processing on the mutation vector may include the following steps: generating a unique value sequence according to the number of elements in the mutation vector; creating a mapping dictionary, the mapping dictionary being used to store the mapping relationship between each element in the mutation vector and the unique value in the unique value sequence; constructing a mapping relationship between each element in the mutation vector and the unique value in the unique value sequence; and according to the mapping relationship, replacing each element in the mutation vector with the unique value mapped thereto.

[0086] In a specific embodiment, taking the newly generated mutation vector V=[2.5, 4.5, 4.5, 3.5, 3.5, 7.5, 6.5, 6.5, 5.5] as an example, the above step of performing boundary processing on the mutation vector can be specifically performed as follows:

[0087] 1. Generate a sequence of unique values:

[0088] First, create an integer sequence from lower_bound to upper_bound (including both endpoints). Lower_bound and upper_bound represent the upper and lower bounds of the individual, respectively. Second, randomly shuffle this sequence to introduce randomness and ensure that the mapping result is not deterministic. The specific example is as follows:

[0089] 1) Generate an integer sequence from lower_bound to upper_bound (including both endpoints), where lower_bound and upper_bound represent the upper and lower bounds of the individuals in the mutation vector V. For example, sorted_unique_vals = [1, 2, 3, 4, 5, 6, 7, 8, 9], where lower_bound = 1; upper_bound = 9.

[0090] 2) Randomly shuffle the generated integer sequence: random(sorted_unique_vals). For example, the shuffled result is [8,7,6,5,4,3,2,9,1].

[0091] 2. Create a mapping dictionary:

[0092] The function initializes an empty dictionary map = {} to store the mapping relationship from the current value in V to the unique value sequence sorted_unique_vals.

[0093] 3. Build mapping relationship:

[0094] 1) The function constructs a mapping relationship through two nested loops. The outer loop traverses each element in V, and the inner loop traverses the shuffled unique value sequence.

[0095] 2) In the inner loop, the function checks whether each unique value has been mapped. If not, the current value in V is mapped to this unique value, and the inner loop is jumped out to continue mapping the next element in V. The specific example is as follows:

[0096] Traverse each element in V and find a unique mapping value for each element: for example, traverse a sorted and non-repeated list of values, such as [8,7,6,5,4,3,2,9,1]; check if unique_val has been mapped; if not, map val to unique_val, for example, map 2.5 to 8, 4.5 to 7, 3.5 to 6, 5.5 to 5, etc.; once the mapping is completed, jump out of the current loop. For example, the mapping relationship is established as follows (only a few mappings are shown): 2.5 is mapped to 8, 4.5 is mapped to 7, 3.5 is mapped to 6, and 5.5 is mapped to 5.

[0097] 4. Apply mapping to vector:

[0098] 1) Once the mapping relationship is established, the function traverses V and checks whether each element in V exists in the mapping dictionary map.

[0099] 2) If it exists, replace the element with the value in the mapping dictionary; if it does not exist (theoretically, it will not happen, because all values ​​in V should find corresponding unique values ​​in the mapping dictionary), directly use the value of the element. The specific steps are as follows:

[0100] Apply the mapping relationship to V and replace each value with its mapped unique value: traverse all elements of the mutation vector V; check whether the current element is in the mapping dictionary map_to_unique; if it is in the mapping dictionary, replace the current element with the corresponding value in the mapping dictionary; if it is not in the mapping dictionary, keep the current element unchanged (theoretically, this situation will not happen). For example, the final mapping vector Vm is as follows: Vm = [8, 7, 7, 6, 6, 3, 2, 2, 5].

[0101] In the query optimization method of the embodiment of the invention, values ​​that do not exist in the predetermined range or unreasonable values ​​such as complex numbers or decimals in the mutation vector can be replaced by integers within the predetermined range, ensuring the availability of the mutation individuals, thereby improving the effectiveness and executability of the query optimization results, and further improving the accuracy of the query optimization.

[0102] In some embodiments, the above-mentioned step of performing unique value serialization processing on the mutation vector after boundary processing may include the following steps: correcting the repeated elements in the mutation vector after boundary processing until all elements in the mutation vector are non-repeating unique values. For example, after boundary processing of the newly generated mutation vector, a new mapping vector Vm=[8,7,7,6,6,3,2,2,5] is obtained. It can be seen that there are still repeated values ​​in Vm, so the repeated values ​​need to be corrected. Since the goal of the present invention is to maintain as many characteristics of the original mutation vector V as possible, some compromises need to be made when correcting repeated values. For example, the first 7 that appears in Vm is retained, and the second 7 is replaced by a unique value that does not appear in Vm, such as 9 (selected according to the value in sorted_unique_vals). Repeat this process until all values ​​are unique. For example, the corrected result is Vm=[8,7,9,6,5,3,2,4,1].

[0103] In the query optimization method of the embodiment of the invention, the modified mutation vector contains unique integers, and each element is in the range of 1 to 9, while retaining the characteristics of the original mutation vector V as much as possible. In addition, the mapping process involves a certain degree of randomness and heuristic decision-making to ensure that the mapped result meets all requirements.

[0104] Therefore, the query optimization method of the embodiment of the present invention achieves the following technical effects:

[0105] 1. Improved global search capability: Traditional genetic algorithms are prone to fall into local optimal solutions when searching in large-scale or multi-dimensional parameter spaces. The query optimization method of the embodiment of the present invention proposes to use the mutation operation in the differential evolution algorithm to enhance the global search capability of the query optimizer, thereby more effectively exploring potential query execution plans.

[0106] 2. Improve the quality of solutions: In order to meet the efficiency requirements of database query execution, this solution aims to improve the quality of the query execution plan found through the global search characteristics of differential evolution, thereby reducing the execution cost and improving system performance.

[0107] 3. Increase the diversity of solutions: The mutation operator in the traditional genetic algorithm may lead to the loss of population diversity, limiting the exploration space of the optimization process. The query optimization method of the embodiment of the present invention adopts the mutation operation of the differential evolution algorithm, utilizes the difference information in the population to introduce richer diversity, and avoids premature convergence.

[0108] 4. Accelerated convergence speed: In a database system that requires fast response, the convergence speed of the query optimizer is crucial. The query optimization method of the embodiment of the present invention accelerates the convergence of the optimization process and reduces the time for determining the query plan by introducing the mutation operation in the differential evolution algorithm.

[0109] 5. An adaptive adjustment mechanism is constructed: Traditional genetic algorithms lack an effective mechanism to dynamically adjust the degree of variation, while the scaling factor F of the differential evolution algorithm in the query optimization method of the embodiment of the present invention can be dynamically adjusted according to the query execution situation, thereby realizing adaptive optimization.

[0110] 6. Enhanced robustness: The query optimization method of the embodiment of the present invention improves the robustness of the query optimizer under different query loads and data distributions through the mutation operation enhanced by the differential evolution algorithm, thereby maintaining a stable optimization effect in a wider range of application scenarios.

[0111] In summary, the query optimization method of the embodiment of the present invention combines the framework of the genetic algorithm and the mutation mechanism of the differential evolution algorithm to form an effective global exploration mechanism, which overcomes the shortcomings of the genetic algorithm in local search efficiency, and at the same time utilizes the advantages of the differential evolution algorithm in global search, thereby maintaining the robustness of the query optimization process while improving the convergence speed and the quality of the solution.

[0112] Figure 3 FIG. 1 is a flowchart of a query optimization method according to an embodiment of the present invention. Figure 3 As shown, the process of the query optimization method of the embodiment of the present invention may include the following steps:

[0113] Step S302, randomly initializing a population. Specifically, this step can be specifically performed as follows: generating a population based on a genetic algorithm initialization, wherein the individuals in the population include randomly generated query execution plans.

[0114] Step S304, calculating the fitness values ​​of individuals in the population and ranking them;

[0115] Step S306, determine whether the evolution generation is reached, if yes, end this process, if no, execute step S308;

[0116] Step S308, randomly select two individuals A and B, and crossbreed individuals A and B to generate a new individual C;

[0117] Step S310, determine whether to select the original mutation strategy, if yes, execute step S312, if no, execute step S314;

[0118] Step S312, using a genetic algorithm to mutate the new individual C, and then proceeding to step S316;

[0119] Step S314, using the differential evolution algorithm to generate new mutant individuals, and then proceeding to step S316;

[0120] Step S316, calculate the fitness value of the newly generated mutant individual, and insert it into the population according to the fitness value ranking, remove the individual with the lowest ranking in the population, and return to step S306.

[0121] The query optimization method of the embodiment of the present invention realizes the search for the optimal solution of the query scheme based on the genetic algorithm by performing at least one mutation operation to generate a new population after initializing the population of query execution plans whose individuals are randomly generated based on the genetic algorithm until the new population meets the preset termination condition. In addition, the query optimization method of the embodiment of the present invention further includes generating mutant individuals based on the differential evolution algorithm with a preset probability and inserting the mutant individuals into the population to obtain a new population through each mutation operation, thereby realizing the integration of the differential evolution algorithm into the genetic algorithm to improve the existing genetic algorithm. The query optimization method of the embodiment of the present invention integrates the differential evolution algorithm and the genetic algorithm, and adopts the differential evolution algorithm to perform mutation operations, thereby introducing richer diversity by utilizing the difference information in the population, so as to more effectively explore potential query execution plans, thereby enhancing the global search capability of the query optimizer, and further improving the execution effect of query optimization.

[0122] Figure 4 FIG. 1 is a flowchart of generating variant individuals based on a differential evolution algorithm in a query optimization method according to an embodiment of the present invention. Figure 4As shown, the process of generating mutant individuals based on the differential evolution algorithm in the query optimization method of the embodiment of the present invention may include the following steps:

[0123] Step S402, randomly select three different individuals from the population.

[0124] Step S404, performing a difference vector operation to generate a new mutant individual. Specifically, this step can be specifically performed as follows: calculating the difference between any two of the three different individuals, and adding the product of the difference and the scaling factor to another of the three different individuals to generate a mutation vector.

[0125] Step S406, determine whether the obtained variant individual meets the conditions, if so, end this process, if not, enter step S408.

[0126] Step S408: performing boundary constraint processing on the variant individuals.

[0127] Step S410: unique value serialization is performed on the variant individuals. This process ends here.

[0128] The query optimization method of the embodiment of the present invention refines the operation process of generating mutant individuals based on the differential evolution algorithm, and sets a step of judging whether the mutant vector meets the preset availability condition after the step of performing the difference vector operation according to the mutation strategy of the differential evolution algorithm, and performs boundary processing and unique value serialization processing on the mutant vector when the mutant vector does not meet the preset availability condition, so that there are no values ​​within a predetermined range or unreasonable values ​​such as complex numbers or decimals in the mutant individuals, thereby ensuring the availability of the mutant individuals, thereby improving the effectiveness and executability of the query optimization results, and further improving the accuracy of the query optimization.

[0129] This embodiment also provides a machine-readable storage medium and a computer device. Figure 5 is a schematic diagram of a machine-readable storage medium 10 according to an embodiment of the present invention, Figure 6 is a schematic diagram of a computer device 20 according to one embodiment of the present invention.

[0130] The machine-readable storage medium 10 stores a machine-executable program 11 , and when the machine-executable program 11 is executed by a processor, the query optimization method of any of the above embodiments can be implemented.

[0131] The computer device 20 may include a memory 220, a processor 210, and a machine executable program 11 stored in the memory 220 and running on the processor 210, and the processor 210 implements the query optimization method of any of the above embodiments when executing the machine executable program 11.

[0132] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0133] For the description of this embodiment, the machine-readable storage medium 10 can be any device that can contain, store, communicate, propagate or transmit a program for use with an instruction execution system, device or equipment or in conjunction with these instruction execution systems, devices or equipment. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection portion with one or more wirings (electronic device), a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and editable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disk read-only memory (CDROM). In addition, the machine-readable storage medium 10 can even be paper or other suitable media on which the program can be printed, because the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting or processing in other suitable ways as necessary, and then stored in a computer memory.

[0134] It should be understood that each part of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0135] The computer device 20 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 20 may be a cloud computing node. The computer device 20 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 20 may be implemented in a distributed cloud computing environment where remote processing devices linked through a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0136] The computer device 20 may include a processor 210 adapted to execute stored instructions, and a memory 220 providing temporary storage space for the operation of the instructions during operation. The processor 210 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 220 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0137] The processor 210 may be connected to an I / O interface (input / output interface) suitable for connecting the computer device 20 to one or more I / O devices (input / output devices) through a system interconnect (e.g., PCI, PCI-Express, etc.). The I / O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touch pad or a touch screen, etc. The I / O devices may be built-in components of the computer device 20, or may be devices externally connected to the computing device.

[0138] The processor 210 may also be linked to a display interface suitable for connecting the computer device 20 to a display device through a system interconnect. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, a television, or a projector, etc., which are externally connected to the computer device 20. In addition, a network interface controller (NIC) may be suitable for connecting the computer device 20 to a network through a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet small computer system interface, etc.) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. A remote device may be connected to the computing device through a network.

[0139] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0140] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A query optimization method, comprising: Initializing and generating a population based on a genetic algorithm, wherein individuals in the population include randomly generated query execution plans; Perform at least one mutation operation to generate a new population until the new population meets a preset termination condition; wherein Each mutation operation further includes: Generate mutant individuals based on differential evolution algorithm with preset probability; The mutant individuals are inserted into the population to obtain the new population.

2. The query optimization method according to claim 1, wherein: The generation of mutant individuals based on the differential evolution algorithm includes: Perform differential vector operations according to the mutation strategy of the differential evolution algorithm to generate mutation vectors; Determining whether the mutation vector meets a preset availability condition; If not, performing boundary processing on the variation vector; The mutation vector after boundary processing is subjected to unique value serialization processing to obtain the mutation individual.

3. The query optimization method according to claim 2, wherein: According to the mutation strategy of the differential evolution algorithm, the difference vector operation is performed to generate the mutation vector including: Randomly select three different individuals from the population; The difference between any two of the three different individuals is calculated, and the product of the difference and the scaling factor is added to another one of the three different individuals to generate a mutation vector.

4. The query optimization method according to claim 3, wherein: Inserting the variant individual into the population to obtain the new population comprises: Calculating the fitness value of the mutated individual; According to the ranking of the fitness values ​​of the individuals in the population, the mutant individuals are inserted into the population, and the individuals with the smallest fitness value in the ranking are removed to form the new population; and The scaling factor can be adaptively adjusted according to the fitness value of the variant individual.

5. The query optimization method according to claim 2, wherein: The boundary processing of the mutation vector includes: generating a unique value sequence according to the number of elements in the mutation vector; Creating a mapping dictionary, the mapping dictionary is used to store a mapping relationship between each element in the mutation vector and a unique value in the unique value sequence; Constructing a mapping relationship between each element in the mutation vector and a unique value in the unique value sequence; According to the mapping relationship, each element in the mutation vector is replaced with a unique value mapped thereto.

6. The query optimization method according to claim 2, wherein: The unique value serialization processing of the mutation vector after boundary processing includes: The repeated elements in the mutation vector after the boundary processing are corrected until all elements in the mutation vector are unique values ​​without repetition.

7. The query optimization method according to claim 1, wherein: The generation of mutant individuals based on the differential evolution algorithm with preset probabilities includes: Obtaining the number of executions of the mutation operation, and determining whether the number of executions reaches a preset threshold; If yes, generating the mutant individual based on the differential evolution algorithm; If not, a new individual formed by hybridization of two individuals randomly selected from the population is mutated based on a genetic algorithm to generate the mutated individual.

8. The query optimization method according to claim 1, wherein: The preset termination conditions include: The number of iterations of the new population reaches a predetermined iteration number threshold.

9. A machine-readable storage medium having a machine-executable program stored thereon, wherein the machine-executable program, when executed by a processor, implements the query optimization method according to any one of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the query optimization method according to any one of claims 1 to 8 when executing the machine executable program.

Images