Query cost evaluation method, system, device and medium for complex element adaptive update

Abstract

This invention discloses a method, system, device, and medium for adaptive updating of query costs for complex elements. The method includes: acquiring execution plan information, database system cost model estimation indicators, and operating environment information for the query to be evaluated; constructing a range key; selecting a cost model parameter vector based on the range key, and converting the execution plan information and database system cost model estimation indicators into a cost factor value vector; obtaining the predicted query time through combination operations; calculating the prediction error based on the actual query time and the predicted query time, and attributing the error to form a parameter set to be updated; performing local adaptive updates only on the parameter components corresponding to the parameter set to be updated when the gating decision passes; writing relevant samples into a sample library, and writing the updated cost model parameter vector into the parameter library. This invention can improve the accuracy and stability of query cost evaluation under complex and changing environments, providing a reliable basis for query optimization.

Description

Technical Field

[0001] This invention relates to the technical field of database query optimization and cost evaluation, and in particular to a method, system, device, and medium for evaluating query costs of adaptive updates of complex elements. Background Technology

[0002] In database systems, query optimizers typically select the optimal execution plan based on cost estimates of candidate execution plans. The accuracy of the query cost assessment directly impacts the rationality of the execution plan selection and the final query performance. Especially in complex query scenarios, queries often involve multi-table joins, aggregations, sorting, subqueries, and distributed data exchange. Their execution time is influenced not only by the execution plan structure itself but also by a multitude of complex factors, including database system cost model estimation metrics, system concurrency, cache hit rates, resource pool allocation, and engine version differences. Therefore, accurately assessing query execution time under continuously changing complex conditions has become a key issue in database system performance optimization.

[0003] In existing technologies, one type of method mainly relies on the static cost model built into the database system, which estimates the query cost by weighting the resource consumption of random reads, sequential reads, and writes. This type of method has the advantages of simple implementation and low computational overhead, but its parameters are usually fixed or only manually adjusted over a long period of time, making it difficult to reflect the dynamic changes in resource costs under different operating environments. When the concurrency level, cache status, resource pool allocation strategy, or deployment version of the database system changes, the static cost parameters often cannot adapt in time, leading to increased prediction deviations in query time and thus affecting the optimizer's selection of execution plans. Another type of method uses machine learning to predict query execution time, building a predictive model by extracting execution plan features, resource estimation features, or runtime log features to improve the accuracy of cost estimation in complex scenarios. While these methods can compensate for the lack of adaptability of traditional static cost models to some extent, they still have the following drawbacks: First, some methods use a unified global model, making it difficult to distinguish the differences in the impact of the same complex element on execution time under different operating environments; second, some methods rely on periodic offline retraining for their update mechanism, resulting in high update costs and difficulty in responding to changes in system state in a timely manner; third, although some black-box models can output prediction results, they lack effective attribution of the sources of prediction errors, making it difficult to determine which factors' parameters should be adjusted; fourth, existing methods usually focus on single-point prediction output, lacking uncertainty representations that match the current operating environment, and cannot provide a reliable basis for subsequent update decisions. Summary of the Invention

[0004] In view of the aforementioned existing problems, the present invention is proposed.

[0005] Therefore, this invention provides a query cost evaluation method, system, device, and medium for adaptive updating of complex elements, which solves the problems in existing query cost evaluation schemes where static cost parameters are difficult to adapt to dynamic operating environments, existing machine learning prediction methods lack error attribution and controlled update mechanisms, and it is difficult to balance prediction accuracy, update stability, and result interpretability.

[0006] To solve the above-mentioned technical problems, the present invention provides the following technical solution: In a first aspect, the present invention provides a query cost evaluation method for adaptive updating of complex features, including: Discretize and bucket the runtime environment information to construct range keys for parameter selection and updates; Select the cost model parameter vector corresponding to the range key from the preset parameter library, and combine the cost model parameter vector with the cost factor value vector to obtain the predicted query time; After the query is executed, the prediction error is calculated based on the actual query time and the predicted query time. Error attribution is performed on the obtained complex element factors. The parameters corresponding to the complex element factors whose error attribution results meet the preset conditions are selected to form a set of parameters to be updated. Execute the gating decision logic. In response to the gating decision being passed, perform local adaptive update only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key to obtain the updated cost model parameter vector. The data from this query is written into the sample database and aggregated using the range key. In response to an update, the updated cost model parameter vector is written into the parameter database using the range key as the index.

[0007] As a preferred embodiment of the query cost evaluation method for adaptive updating of complex elements described in this invention, the step of discretizing and binning the operating environment information to construct a range key for parameter selection and updating includes: Divide the concurrency count in the runtime environment information into intervals to obtain the concurrency interval range key; Divide the cache hit rate in the runtime environment information into intervals to obtain the cold and hot interval range keys; The range key is formed by combining the concurrent interval range key, the hot and cold interval range key, and at least one of the engine version identifier and resource pool identifier in the operating environment information.

[0008] As a preferred embodiment of the query cost evaluation method for adaptive updating of complex elements described in this invention, the step of obtaining the predicted query time includes: Read the cost model parameter vector corresponding to the range key from the preset parameter library. The cost model parameter vector includes constant term parameters and parameters corresponding to each complex element factor. The execution plan-related information and database system cost model estimation indicators are converted into a cost factor value vector, which includes a constant term and values ​​corresponding to each complex element factor. The predicted query time is obtained by performing a dot product operation between the cost model parameter vector and the cost factor value vector. Maintain a set of historical prediction error absolute values ​​in the historical samples corresponding to the range key; Perform quantile statistics on the set of absolute values ​​of historical prediction errors to obtain the half-width of the confidence interval; A confidence interval for the predicted query time is generated based on the predicted query time and the half-width of the confidence interval.

[0009] The beneficial effect of this preferred embodiment is that by discretizing the operating environment information into range keys and selecting cost model parameter vectors under different range keys, the query cost evaluation can be partitioned for different operating environments; at the same time, by maintaining the set of absolute values ​​of historical prediction errors and obtaining the half-width of the confidence interval, uncertainty measurement basis can be provided for subsequent gating updates.

[0010] As a preferred embodiment of the query cost evaluation method for adaptive updating of complex elements described in this invention, the step of forming the parameter set to be updated includes: Get the actual query time after the query is executed; The prediction error is calculated based on the actual query time and the predicted query time. Based on the cost model parameter vector and cost factor value vector corresponding to the range key, calculate the attribution weight of each complex element factor; Based on the attribution weights of each complex factor and the prediction error, the error allocation value of each complex factor is obtained. Based on the error allocation value, corresponding parameters are selected from each complex element factor according to preset conditions to form a parameter set to be updated.

[0011] The beneficial effect of this preferred embodiment is that by distributing the prediction error to the parameter components corresponding to each complex element factor, the factors that contribute significantly to the current prediction deviation can be identified, thereby improving the pertinence of parameter updates.

[0012] As a preferred embodiment of the query cost evaluation method for adaptive updating of complex elements according to the present invention, wherein: the execution of the gating decision logic, in response to the gating decision being passed, performs local adaptive updating only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, including: Maintain a historical sample set under the range key in the sample library; The sample size condition is determined based on whether the sample size of the historical sample set reaches a preset minimum sample size threshold. The error persistence condition is determined based on whether the sliding statistic of the absolute value of the prediction error under the range key reaches the preset error threshold. The confidence level is determined based on whether the half-width of the confidence interval corresponding to the predicted query time reaches the preset upper limit threshold. If all the preset conditions in the above determination conditions are met, in the cost model parameter vector corresponding to the range key, only the parameter components corresponding to the parameter set to be updated are locally adaptively updated. If any of the above-mentioned conditions are not met, the cost model parameter vector corresponding to the range key remains unchanged.

[0013] The advantages of this preferred embodiment are that by setting a gating decision mechanism, direct parameter updates can be avoided when the sample size is insufficient or the error fluctuation is not continuous; at the same time, local adaptive updates are performed only on the parameter components corresponding to the parameter set to be updated, which is beneficial to balance update sensitivity and parameter stability.

[0014] As a preferred embodiment of the query cost evaluation method for adaptive updating of complex elements according to the present invention, wherein: the execution of the gating decision logic, in response to the gating decision being passed, performs local adaptive updating only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, further includes: The cost model parameter vector corresponding to the range key is divided into a sub-vector to be updated and a sub-vector that remains unchanged according to the parameter component index of the parameter set to be updated. Select the nearest sample under the range key to form the training window; Based on the cost factor value components corresponding to the invariant subvector, a fixed component is deducted from the actual query time to obtain the target value for updating. Based on the cost factor subvector and the target value in the training window, a regression solution with a regularization term is performed on the subvector to be updated to obtain the updated subvector. Write the updated subvector back to the corresponding component of the original cost model parameter vector to obtain the updated cost model parameter vector; Boundary constraint processing is performed on the updated cost model parameter vector to ensure that the parameter values ​​meet the preset physical meaning constraints.

[0015] The advantages of this preferred embodiment are that by updating only the parameter components corresponding to the subvector to be updated, while keeping the other parameter components unchanged, local drift can be corrected while limiting the range of model perturbation; by introducing regularization terms and boundary constraint processing, the stability and interpretability of the update results can be further improved.

[0016] As a preferred embodiment of the query cost evaluation method for adaptive updating of complex elements described in this invention, the step of writing the data of this query into the sample database and aggregating it using the range key, and in response to generating an update, writing the updated cost model parameter vector into the parameter database using the range key as the index, includes: Write the range key, predicted query time, actual query time, prediction error, and cost factor value vector corresponding to this query into the sample database as sample records; Using range keys as aggregation indexes, samples in the sample database are retrieved, statistically analyzed, and managed in a unified manner. In response to the generation of parameter updates, the updated cost model parameter vector along with the corresponding range key is written into the parameter library; The sample library is used to support prediction error statistics, confidence interval calculation and gating determination, and the parameter library is used to support parameter selection and prediction calculation under the same range key in subsequent queries.

[0017] Secondly, this invention provides a query cost evaluation system for adaptive updating of complex features, including: The range key construction module is used to discretize and bucket the runtime environment information to construct range keys for parameter selection and updating. The prediction and evaluation module is used to select the cost model parameter vector corresponding to the range key from the preset parameter library, and to perform a combination operation on the cost model parameter vector and the cost factor value vector to obtain the predicted query time. The error attribution module is used to calculate the prediction error based on the actual query time and the predicted query time after the query is executed, and to perform error attribution on the obtained complex element factors. It selects the parameters corresponding to the complex element factors whose error attribution results meet the preset conditions to form a parameter set to be updated. The gating update module is used to execute the gating decision logic. In response to the gating decision being passed, it performs local adaptive update only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, so as to obtain the updated cost model parameter vector. The storage management module is used to write the data queried this time into the sample database and aggregate it with the range key. In response to an update, the updated cost model parameter vector is written into the parameter database with the range key as the index.

[0018] Thirdly, the present invention provides an electronic device, including a memory and a processor; the memory is used to store computer-executable instructions, and the processor, when executing the computer-executable instructions, implements the steps of a query cost evaluation method for adaptive updating of complex features.

[0019] Fourthly, the present invention provides a computer-readable storage medium storing computer-executable instructions that, when executed by a processor, implement steps of a query cost evaluation method for adaptive updating of complex features.

[0020] Compared with existing technologies, the beneficial effects of this invention are as follows: By discretizing and binning the operating environment information and constructing range keys, this invention enables parameter selection, sample collection, and parameter updates under different operating environments to revolve around the same range key; by converting execution plan-related information and database system cost model estimation indicators into cost factor value vectors and combining them with the cost model parameter vectors corresponding to the range keys, structured prediction of query execution time is achieved; by attributing prediction errors after query execution and forming a set of parameters to be updated accordingly, the targeting of parameter updates can be improved; by setting gating judgments and performing local adaptive updates only on the parameter components corresponding to the set of parameters to be updated when conditions are met, it is beneficial to balance update sensitivity and model stability; by constructing sample and parameter libraries and collecting and indexing them using range keys, a closed-loop processing flow of prediction, verification, attribution, update, and writeback can be formed, thereby improving the accuracy, stability, and continuous adaptive capability of cost evaluation in complex query scenarios. Attached Figure Description

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

[0022] Figure 1 This is a schematic diagram of the overall process logic of a query cost evaluation method for adaptive updating of complex elements provided in one embodiment of the present invention.

[0023] Figure 2 This is a technical framework diagram of a query cost evaluation method for adaptive updating of complex elements provided in one embodiment of the present invention.

[0024] Figure 3 This is a schematic diagram of the gating judgment logic of a query cost evaluation method for adaptive updating of complex elements provided in an embodiment of the present invention. Detailed Implementation

[0025] To make the above-mentioned objects, features, and advantages of the present invention more apparent and understandable, specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of them. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the protection scope of the present invention.

[0026] Example 1, referring to Figure 1 As one embodiment of the present invention, a query cost evaluation method for adaptive updating of complex features is provided, such as... Figure 1 The specific steps shown are as follows: S100: Discretize and bucket the runtime environment information to construct a range key for parameter selection and updating; S200: Select the cost model parameter vector corresponding to the range key from the preset parameter library, and perform a combined operation on the cost model parameter vector and the cost factor value vector to obtain the predicted query time; S300: After the query is executed, the prediction error is calculated based on the actual query time and the predicted query time. Error attribution is performed on the obtained complex element factors. The parameters corresponding to the complex element factors whose error attribution results meet the preset conditions are selected to form a set of parameters to be updated. S400: Execute the gating decision logic. In response to the gating decision being passed, in the cost model parameter vector corresponding to the range key, only the parameter components corresponding to the parameter set to be updated are locally adaptively updated to obtain the updated cost model parameter vector. S500: Write the data queried this time into the sample database and aggregate it with the range key. In response to the update, write the updated cost model parameter vector into the parameter database with the range key as the index.

[0027] It should be noted that this invention discretizes and bins the runtime environment information and constructs range keys, enabling parameter selection, sample collection, and parameter updates under different runtime environments to revolve around the same range key. By converting execution plan-related information and database system cost model estimation indicators into cost factor value vectors and combining them with the cost model parameter vectors corresponding to the range keys, structured prediction of query execution time is achieved. By attributing prediction errors after query execution and forming a set of parameters to be updated accordingly, the targeting of parameter updates can be improved. By setting gating criteria and performing local adaptive updates only on the parameter components corresponding to the set of parameters to be updated when conditions are met, it is beneficial to balance update sensitivity and model stability. By constructing sample and parameter libraries and collecting and indexing them using range keys, a closed-loop processing flow of prediction, verification, attribution, update, and writeback can be formed, thereby improving the accuracy, stability, and continuous adaptive capability of cost evaluation in complex query scenarios.

[0028] Example 2, refer to Figure 2 and Figure 3 Based on the previous embodiment, this embodiment provides a specific implementation method for the query cost evaluation method of adaptive updating of complex elements, and explains the technical means of this method.

[0029] In step S100 of this invention, the execution plan information, database system cost model estimation indicators, and operating environment information corresponding to the query to be evaluated are obtained as complex factor elements to be analyzed, such as... Figure 2 The following are included: Obtain execution plan information, which is used to characterize the physical execution path of the query. It includes one or more of the following: operator type, operator parent-child relationship, estimated number of output rows for each operator, estimated row width for each operator, access object identifier, etc. The above information can be obtained through the execution plan output interface provided by the database system. Obtain the cost model estimation index of the database system. The cost model estimation index is used to characterize the resource consumption estimate of the execution plan. It includes one or more of the following: random read page number estimate, sequential read page number estimate, write page number estimate, etc. Obtain runtime environment information, which includes one or more of the following: engine version identifier, concurrency, cache hit rate, resource pool identifier, etc.

[0030] In step S100 of the present invention, the operating environment information is discretized and bucketed to construct a range key for parameter selection and updating, including: Divide the concurrency count in the runtime environment information into intervals to obtain the concurrency interval range key; Divide the cache hit rate in the runtime environment information into intervals to obtain the cold and hot interval range keys; The range key is formed by combining the concurrent interval range key, the hot / cold interval range key, and at least one of the engine version identifier and resource pool identifier in the runtime environment information.

[0031] Specifically, range key It consists of two or more of the following: "engine version identifier, concurrency range, data hot / cold range, and resource pool identifier". For example, in this embodiment, the range key can be obtained as follows: : in, For concurrent interval range keys; in, The hot / cold range key is used as the range key. Combining the concurrent range key with the hot / cold range key yields the range key. .

[0032] It should be noted that step S100 can map the dynamically changing operating environment into a finite and distinguishable state space, so that subsequent parameter selection, sample collection and update operations are carried out around the same range key, realizing the isolation of cost evaluation parameters under different operating environments, avoiding mutual interference of the global model in multiple environments, and improving the evaluation system's adaptability to differences in operating environments.

[0033] In step S200 of the present invention, a cost model parameter vector corresponding to the range key is selected from a preset parameter library, and the cost model parameter vector is combined with the cost factor value vector to obtain the predicted query time, including: Read the cost model parameter vector corresponding to the range key from the preset parameter library. The cost model parameter vector includes constant term parameters and parameters corresponding to each complex element factor. The execution plan-related information and the database system cost model estimation indicators are converted into cost factor value vectors. The cost factor value vectors include constant terms and values ​​corresponding to each complex element factor. The predicted query time is obtained by performing a dot product operation between the cost model parameter vector and the cost factor value vector. Maintain a set of absolute values ​​of historical prediction errors in the historical samples corresponding to the range keys; Perform quantile statistics on the set of absolute values ​​of historical prediction errors to obtain the half-width of the confidence interval; Generate a confidence interval for the predicted query time based on the predicted query time and the half-width of the confidence interval.

[0034] Specifically, the cost model parameter vector It consists of random read page cost parameters, sequential read page cost parameters, and write page cost parameters, etc. in, Indicates range key The corresponding cost model parameter vector, Indicates range key The constant term parameter below, Indicates the first Cost parameters of complex factor elements This represents the number of dimensions of complex factor elements, allowing for on-demand expansion. For example, in this embodiment, the cost model parameter vector... .

[0035] Specifically, execution plan-related information and database system cost model estimation indicators are used as complex element factors to form the cost factor value vector. This includes the values ​​corresponding to various complex factor elements, such as the estimated number of pages read randomly, the estimated number of pages read sequentially, and the estimated number of pages written. In distributed execution scenarios, it may also include the network exchange byte volume derived from the execution plan tree and the processing scale derived from the execution plan tree. For example, in this embodiment, the cost factor value vector expression is: in, The number of random page reads estimated by the cost model. The sequential page read count estimated by the cost model. The number of pages written is estimated by the cost model. The network swap byte count is estimated from the swap class operators in the execution plan tree. The number of processing rows estimated from the execution plan tree. Queue waiting time estimates obtained from system monitoring.

[0036] Specifically, in this embodiment, the expression for predicting the query time is: .

[0037] In some embodiments, it can be based on a range key The statistical results of historical prediction errors are used to generate the half-width confidence interval for the predicted query time. Specifically, in the range key... Maintain the set of absolute values ​​of historical prediction errors And take its quantile as the half-width of the confidence interval, its expression is: in, Indicates range key The confidence interval half-width below The statistical function representing quantiles. To preset the quantile level, This represents the set of absolute values ​​of historical prediction errors. This represents the confidence interval for predicting the query time. For example, in this embodiment, The value is 0.9.

[0038] It should be noted that step S200 selects the corresponding cost model parameter vector based on the range key and performs a combined operation with the cost factor value vector obtained by converting the execution plan and cost model estimation index, which can output a structured prediction query time. At the same time, by maintaining the set of absolute values ​​of historical prediction errors and calculating the half-width of the confidence interval, an uncertainty measure is provided for the prediction results, which not only improves the accuracy of the prediction, but also provides a reliable statistical basis for subsequent gating updates.

[0039] In step S300 of the present invention, after the query is executed, the prediction error is calculated based on the actual query time and the predicted query time, and error attribution is performed on the obtained complex element factors. Parameters corresponding to complex element factors whose error attribution results meet preset conditions are selected to form a parameter set to be updated, including: Get the actual query time after the query is executed; The prediction error is calculated based on the actual query time and the predicted query time. Calculate the attribution weights of each complex element factor based on the cost model parameter vector and cost factor value vector corresponding to the range key. Based on the attribution weights and prediction errors of each complex factor, the error allocation values ​​of each complex factor are obtained. Based on the error allocation value, corresponding parameters are selected from each complex element factor according to preset conditions to form a parameter set to be updated.

[0040] Specifically, obtain the actual query time from the database system. The prediction error is obtained by subtracting the prediction query time output in step S200. Error attribution first calculates the attribution weight for each complex factor, expressed as: in, Indicates the first Attribution weights of complex factor elements Indicates range key Next Cost parameters of complex factor elements Indicates the first Each complex factor corresponds to a cost value. To prevent the use of a preset value where the denominator is zero. For example, in this embodiment... The value is 0.1.

[0041] Specifically, the error attribution is calculated based on the attribution weight of each complex factor to obtain the error allocation value for each complex factor. The expression is as follows: in, Indicates the first Error allocation values ​​for each complex element factor. Indicates the first Attribution weights of complex factor elements This indicates the prediction error.

[0042] Specifically, based on the error allocation values ​​of each complex element factor, parameters to be updated are selected from the parameters corresponding to each complex element factor according to preset conditions, forming a set of parameters to be updated. The preset conditions include one or more of the following: setting a threshold. ,filter Corresponding to complex factors; screening The largest front One factor. For example, in this embodiment, The value is 1. The value is 3.

[0043] It should be noted that after the query is executed, step S300 calculates the prediction error based on the actual query time and the predicted query time, and distributes the error to each complex element factor through attribution weights, which can accurately locate the key factors that cause prediction deviations; by screening factors that meet preset conditions, a set of parameters to be updated is formed, avoiding blind full updates and improving the targeting and efficiency of parameter adjustment.

[0044] In step S400 of the present invention, gating decision logic is executed. In response to passing the gating decision, local adaptive updates are performed only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key. Figure 3 The following are included: Maintain a historical sample set under the range key in the sample library; The sample size condition is determined based on whether the sample size of the historical sample set reaches the preset minimum sample size threshold. The error persistence condition is determined based on whether the sliding statistic of the absolute value of the prediction error under the range key reaches the preset error threshold. The confidence level is determined based on whether the half-width of the confidence interval corresponding to the predicted query time reaches the preset upper limit threshold. If all the preset conditions in the above judgment conditions are met, local adaptive updates are performed only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key. If any of the above-mentioned conditions are not met, the cost model parameter vector corresponding to the range key remains unchanged.

[0045] Specifically, gating decisions must include at least one or more of the following criteria: ① Sample size condition: Maintain range keys in the sample database The following historical sample set ,when When the sample size condition is met, among which This is a preset minimum sample size threshold. For example, in this embodiment, The value is 3; ② Error persistence condition: Maintain range keys in the sample library absolute value of prediction error Sliding statistics It is updated using an exponential moving average method, and its expression is: in, For the preset smoothing coefficient, For the first The sliding statistics obtained from the next update For the first The prediction error for this sample. When When the error persistence condition is met, where This is a preset threshold. For example, in this embodiment, The value is 0.7. The value is 1.

[0046] ③ Confidence condition: When the confidence half-width obtained in step S200 When the confidence condition is met, where This is a preset upper limit. For example, in this embodiment, The value is 1.

[0047] In step S400 of the present invention, gating decision logic is executed. In response to passing the gating decision, local adaptive updates are performed only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key. Figure 3 The diagram also includes: The cost model parameter vector corresponding to the range key is divided into a sub-vector to be updated and a sub-vector that remains unchanged according to the index of the parameter component corresponding to the parameter set to be updated. Select the nearest sample under the range key to form the training window; Based on the cost factor value components corresponding to the invariant subvector, a fixed component is deducted from the actual query time to obtain the target value used for updating. Based on the cost factor subvector and the target value in the training window, perform a regression solution with a regularization term on the subvector to be updated to obtain the updated subvector; Write the updated subvector back to the corresponding component of the original cost model parameter vector to obtain the updated cost model parameter vector; Boundary constraint processing is performed on the updated cost model parameter vector to ensure that the parameter values ​​meet the preset physical meaning constraints.

[0048] Specifically, let the set of parameter component indices corresponding to the parameter set to be updated be... range key The corresponding cost model parameter vector By index set Divide into sub-vectors to be updated with invariant subvectors In the range key Select the nearest The training window consists of 10 samples. ,in For the first The cost factor value vector corresponding to each sample For the first The actual query time corresponding to each sample. Because... Keeping it constant, first calculate the target value after deducting the fixed component. Its expression is: in, express Zhongyu The corresponding component subvectors. Then, the updated subvectors are solved using ridge regression. Its optimization objective is: in, express Zhongyu The corresponding sub-vectors, The parameters of the subvector to be updated before the update. A preset regularization coefficient is used to limit the magnitude of parameter updates and suppress the influence of noise. For example, in this embodiment, The value is 0.1.

[0049] Furthermore, the updated solution is obtained by solving the above optimization problem. and write it back The corresponding components are used to form the updated cost model parameter vector. Preferably, to ensure the physical meaning of the cost parameters, boundary constraint processing can be performed on the updated parameter components, such as setting parameter components less than zero to zero and truncating parameter components exceeding a preset upper limit to the upper limit.

[0050] It should be noted that step S400 introduces gating decision logic, which can avoid triggering updates when data is insufficient or error fluctuations are not representative, thus ensuring the robustness of parameter updates. After gating is passed, only the parameter components corresponding to the parameter set to be updated are locally adaptively updated, which can quickly respond to changes in system state and maintain the stability of non-drifted parameters, achieving a balance between sensitivity and stability of the evaluation model.

[0051] In step S500 of the present invention, the range key, predicted query time, actual query time, prediction error, and cost factor value vector used for calculation are written into the sample database and aggregated by the range key; in response to an update, the updated cost model parameter vector is written into the parameter database using the range key as the index, including: Write the range key, predicted query time, actual query time, prediction error, and cost factor value vector corresponding to this query into the sample database as sample records; Using range keys as aggregation indexes, samples in the sample database are retrieved, statistically analyzed, and managed in a unified manner. In response to the generation of parameter updates, the updated cost model parameter vector along with the corresponding range key is written into the parameter library; The sample library is used to support prediction error statistics, confidence interval calculation and gating determination, while the parameter library is used to support parameter selection and prediction calculation under the same range key in subsequent queries.

[0052] Specifically, the sample library storage includes at least one or more of the following fields: range key Predicted query time Actual query time Prediction error Cost factor value vector Historical sample set Sliding statistics etc.; and with range keys As a aggregation index, it enables samples under the same range key to be retrieved and statistically analyzed uniformly, supporting the confidence interval calculation in step S200 and the gating determination and parameter update in step S400. The parameter library storage includes at least one or more of the following fields: range key Cost model parameter vector wait.

[0053] It should be noted that step S500 writes the data from each query into the sample database and aggregates it with a range key, providing unified data support for error statistics, confidence interval calculation, and gating determination; at the same time, it writes the updated cost model parameter vector into the parameter database with a range key as the index, realizing a closed-loop process, enabling the query cost evaluation system to have continuous adaptive evolution capabilities, and significantly improving the long-term accuracy and reliability of cost evaluation in complex dynamic environments.

[0054] Example 3: This example provides a query cost evaluation system for adaptive updates of complex features, including: The range key construction module is used to discretize and bucket the runtime environment information to construct range keys for parameter selection and updating. The prediction and evaluation module is used to select the cost model parameter vector corresponding to the range key from the preset parameter library, and to perform a combined operation on the cost model parameter vector and the cost factor value vector to obtain the predicted query time. The error attribution module is used to calculate the prediction error based on the actual query time and the predicted query time after the query is executed, and to perform error attribution on the obtained complex element factors. It selects the parameters corresponding to the complex element factors whose error attribution results meet the preset conditions to form a parameter set to be updated. The gating update module is used to execute the gating decision logic. In response to the gating decision being passed, it performs local adaptive update only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, and obtains the updated cost model parameter vector. The storage management module is used to write the data queried this time into the sample database and aggregate it with the range key. In response to an update, the updated cost model parameter vector is written into the parameter database with the range key as the index.

[0055] It should be noted that the technical solution of the query cost evaluation system for adaptive updating of complex elements is based on the same concept as the technical solution of the query cost evaluation method for adaptive updating of complex elements described above. For details not described in detail in the technical solution of the query cost evaluation system for adaptive updating of complex elements in this embodiment, please refer to the description of the technical solution of the query cost evaluation method for adaptive updating of complex elements described above.

[0056] The above-mentioned unit modules can be embedded in the processor of the electronic device in hardware form or independent of it, or they can be stored in the memory of the electronic device in software form, so that the processor can call and execute the corresponding operations of the above modules.

[0057] This embodiment also provides an electronic device, which includes a processor, a memory, a communication interface, a display screen, and an input device connected via a system bus. The processor provides computing and control capabilities. The memory includes a non-volatile storage medium and internal memory. The non-volatile storage medium stores an operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, carrier networks, NFC (Near Field Communication), or other technologies. When the computer program is executed by the processor, it implements a query cost evaluation method for adaptive updates of complex elements. The display screen can be a liquid crystal display (LCD) or an e-ink display. The input device can be a touch layer covering the display screen, buttons, a trackball, or a touchpad mounted on the device's casing, or an external keyboard, touchpad, or mouse.

[0058] This embodiment also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method proposed in the above embodiments.

[0059] The storage medium proposed in this embodiment belongs to the same inventive concept as the method proposed in the above embodiments. Technical details not described in detail in this embodiment can be found in the above embodiments, and this embodiment has the same beneficial effects as the above embodiments.

[0060] Based on the above description of the implementation methods, those skilled in the art can clearly understand that the present invention can be implemented using software and necessary general-purpose hardware, and of course, it can also be implemented using hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as a computer floppy disk, read-only memory, random access memory, flash memory, hard disk, or optical disk, and includes several instructions to cause an electronic device (which may be a personal computer, server, or network device, etc.) to execute the method of the embodiments of the present invention.

[0061] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the present invention.

Claims

1. A query cost evaluation method for adaptive updating of complex features, characterized in that, include: Discretize and bucket the runtime environment information to construct range keys for parameter selection and updates; Select the cost model parameter vector corresponding to the range key from the preset parameter library, and combine the cost model parameter vector with the cost factor value vector to obtain the predicted query time; After the query is executed, the prediction error is calculated based on the actual query time and the predicted query time. Error attribution is performed on the obtained complex element factors. The parameters corresponding to the complex element factors whose error attribution results meet the preset conditions are selected to form a set of parameters to be updated. Execute the gating decision logic. In response to the gating decision being passed, perform local adaptive update only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key to obtain the updated cost model parameter vector. The data from this query is written into the sample database and aggregated using the range key. In response to an update, the updated cost model parameter vector is written into the parameter database using the range key as the index.

2. The query cost evaluation method for adaptive updating of complex elements as described in claim 1, characterized in that, The discretization and bucketing of the operating environment information to construct a range key for parameter selection and updating includes: Divide the concurrency count in the runtime environment information into intervals to obtain the concurrency interval range key; Divide the cache hit rate in the runtime environment information into intervals to obtain the cold and hot interval range keys; The range key is formed by combining the concurrent interval range key, the hot and cold interval range key, and at least one of the engine version identifier and resource pool identifier in the operating environment information.

3. The query cost evaluation method for adaptive updating of complex elements as described in claim 2, characterized in that, The predicted query time includes: Read the cost model parameter vector corresponding to the range key from the preset parameter library. The cost model parameter vector includes constant term parameters and parameters corresponding to each complex element factor. The execution plan-related information and database system cost model estimation indicators are converted into a cost factor value vector, which includes a constant term and values ​​corresponding to each complex element factor. The predicted query time is obtained by performing a dot product operation between the cost model parameter vector and the cost factor value vector. Maintain a set of historical prediction error absolute values ​​in the historical samples corresponding to the range key; Perform quantile statistics on the set of absolute values ​​of historical prediction errors to obtain the half-width of the confidence interval; A confidence interval for the predicted query time is generated based on the predicted query time and the half-width of the confidence interval.

4. The query cost evaluation method for adaptive updating of complex elements as described in claim 3, characterized in that, The process of forming the parameter set to be updated includes: Get the actual query time after the query is executed; The prediction error is calculated based on the actual query time and the predicted query time. Based on the cost model parameter vector and cost factor value vector corresponding to the range key, calculate the attribution weight of each complex element factor; Based on the attribution weights of each complex factor and the prediction error, the error allocation value of each complex factor is obtained. Based on the error allocation value, corresponding parameters are selected from each complex element factor according to preset conditions to form a parameter set to be updated.

5. The query cost evaluation method for adaptive updating of complex elements as described in claim 4, characterized in that, The execution gating decision logic, in response to a successful gating decision, performs local adaptive updates only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, including: Maintain a historical sample set under the range key in the sample library; The sample size condition is determined based on whether the sample size of the historical sample set reaches a preset minimum sample size threshold. The error persistence condition is determined based on whether the sliding statistic of the absolute value of the prediction error under the range key reaches the preset error threshold. The confidence level is determined based on whether the half-width of the confidence interval corresponding to the predicted query time reaches the preset upper limit threshold. If all the preset conditions in the above determination conditions are met, in the cost model parameter vector corresponding to the range key, only the parameter components corresponding to the parameter set to be updated are locally adaptively updated. If any of the above-mentioned conditions are not met, the cost model parameter vector corresponding to the range key remains unchanged.

6. The query cost evaluation method for adaptive updating of complex features as described in claim 5, characterized in that, The execution of the gating decision logic, in response to passing the gating decision, involves performing a local adaptive update only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, and further includes: The cost model parameter vector corresponding to the range key is divided into a sub-vector to be updated and a sub-vector that remains unchanged according to the parameter component index of the parameter set to be updated. Select the nearest sample under the range key to form the training window; Based on the cost factor value components corresponding to the invariant subvector, a fixed component is deducted from the actual query time to obtain the target value for updating. Based on the cost factor subvector and the target value in the training window, a regression solution with a regularization term is performed on the subvector to be updated to obtain the updated subvector. Write the updated subvector back to the corresponding component of the original cost model parameter vector to obtain the updated cost model parameter vector; Boundary constraint processing is performed on the updated cost model parameter vector to ensure that the parameter values ​​meet the preset physical meaning constraints.

7. The query cost evaluation method for adaptive updating of complex features as described in claim 6, characterized in that, The step of writing the data queried in this instance into the sample database and aggregating it using the range key, and in response to an update, writing the updated cost model parameter vector into the parameter database using the range key as the index, includes: Write the range key, predicted query time, actual query time, prediction error, and cost factor value vector corresponding to this query into the sample database as sample records. Using range keys as aggregation indexes, samples in the sample database are retrieved, statistically analyzed, and managed in a unified manner. In response to the generation of parameter updates, the updated cost model parameter vector along with the corresponding range key is written into the parameter library; The sample library is used to support prediction error statistics, confidence interval calculation and gating determination, and the parameter library is used to support parameter selection and prediction calculation under the same range key in subsequent queries.

8. A query cost evaluation system for adaptive updating of complex features, employing the query cost evaluation method for adaptive updating of complex features as described in any one of claims 1 to 7, characterized in that, include: The range key construction module is used to discretize and bucket the runtime environment information to construct range keys for parameter selection and updating. The prediction and evaluation module is used to select the cost model parameter vector corresponding to the range key from the preset parameter library, and to perform a combination operation on the cost model parameter vector and the cost factor value vector to obtain the predicted query time. The error attribution module is used to calculate the prediction error based on the actual query time and the predicted query time after the query is executed, and to perform error attribution on the obtained complex element factors. It selects the parameters corresponding to the complex element factors whose error attribution results meet the preset conditions to form a parameter set to be updated. The gating update module is used to execute the gating decision logic. In response to the gating decision being passed, it performs local adaptive update only on the parameter components corresponding to the parameter set to be updated in the cost model parameter vector corresponding to the range key, so as to obtain the updated cost model parameter vector. The storage management module is used to write the data queried this time into the sample database and aggregate it with the range key. In response to an update, the updated cost model parameter vector is written into the parameter database with the range key as the index.

9. An electronic device comprising a memory and a processor, characterized in that: The memory is used to store computer-executable instructions, and when the processor executes the computer-executable instructions, it implements the steps of the query cost evaluation method for adaptive updating of complex elements as described in any one of claims 1 to 7.

10. A computer-readable storage medium having computer-executable instructions stored thereon, characterized in that: When the computer-executable instructions are executed by the processor, they implement the steps of the query cost evaluation method for adaptive updating of complex elements as described in any one of claims 1 to 7.

Images