A database cost model calibration method, device and medium

Abstract

The application discloses a database cost model calibration method, device and medium, and belongs to the field of database query optimization. The method comprises the following steps: collecting SQL physical operation indexes asynchronously through business execution feedback and background lightweight exploration, mapping the indexes to actual cost values, calculating cost offset degrees, and determining cost drift when the cost offset degrees exceed a preset threshold; generating dynamic confidence intervals for each cost parameter based on historical data, extracting database operation context features, solving recommended cost values in the confidence intervals through a context tiger machine algorithm, and obtaining candidate cost values through a damping smoothing formula; obtaining a stability score by counting the number of execution plan switching times through a sliding window, attenuating a gradual control factor and recalculating the candidate cost values when the score is lower than a safety threshold, and writing an effective cost factor into an optimizer after stabilization. The scheme realizes closed-loop adaptive calibration of the cost model, effectively avoids execution plan shock, adapts to complex operation environments, and guarantees the stability of database operation.

Description

Technical Field

[0001] This application relates to the field of database query optimization technology, and in particular to a database cost model calibration method, device and medium. Background Technology

[0002] In cloud-native, multi-tenant containerized deployment scenarios, relational databases generally use cost-based optimizers to select SQL execution plans. They rely on a set of static cost factors to estimate the execution cost of operators. These cost factors are either factory defaults or manually configured globally by operations and maintenance personnel. When faced with hardware fluctuations, resource contention, storage aging, or other situations, the fixed parameters cannot match the real-time operating environment, resulting in distorted cost estimations and the continuous generation of suboptimal execution plans.

[0003] Furthermore, existing databases lack a feedback loop between the optimizer and executor, meaning that actual SQL execution time, I / O overhead, and other performance metrics cannot be fed back to the optimization module, leading to a continuous accumulation of estimation errors. Manually modifying cost parameters directly can easily trigger frequent SQL execution plan flips, severely threatening the stability of the production database and failing to meet the enterprise's requirements for stable, low-intrusion database operation. Summary of the Invention

[0004] To address the technical problems mentioned above, embodiments of this application provide a database cost model calibration method, device, and medium. The method includes: asynchronously collecting physical operation indicators during SQL execution via two paths: business execution feedback and lightweight background probing; normalizing and mapping the physical operation indicators to actual cost values ​​with the same dimensions as the optimizer's estimated cost; calculating a cost offset based on the actual cost value and the optimizer's estimated cost; determining that cost drift has occurred when the cost offset exceeds a preset drift threshold; and determining the confidence interval for each cost parameter in the optimizer based on valid historical data within a preset historical interval of the optimizer when cost drift occurs. Extract the contextual features of database operation and determine the recommended cost value within the confidence interval using a contextual slot machine algorithm. Based on the recommended cost value and a preset asymptotic control factor, determine the candidate cost value using a smoothing formula with a damping coefficient. Count the number of execution plan switches for each SQL statement within a preset sliding time window to obtain a plan stability score. When the plan stability score is lower than a preset safety threshold, decay the asymptotic control factor to a preset percentage of its original value, and recalculate the candidate cost value using the decayed asymptotic control factor until the plan stability score is not lower than the preset safety threshold. Finally, determine the candidate cost value of the final round as the effective cost factor and write it into the optimizer.

[0005] In one example, the physical operation metrics are normalized and mapped to actual cost values ​​with the same dimensions as the optimizer's estimated cost. A cost offset is calculated based on the actual cost value and the optimizer's estimated cost. When the cost offset exceeds a preset drift threshold, cost drift is determined to have occurred. Specifically, this includes: using the actual time spent on a single sequential page read as the baseline unit, converting the CPU operator computation time, sequential I / O time, and random I / O time in the physical operation metrics into actual cost scores for their respective dimensions; summing all actual cost scores to obtain the actual cost value; calculating the ratio between the actual cost value and the optimizer's estimated cost to obtain the cost offset; and pre-setting a drift threshold. When the cost offset exceeds the preset drift threshold, cost drift is determined to have occurred.

[0006] In one example, when cost drift occurs, the confidence interval for each cost parameter in the optimizer is determined based on the valid historical data within the preset historical interval of the optimizer. Specifically, when cost drift occurs, for each cost parameter in the optimizer, the corresponding valid historical data within the preset historical interval is retrieved; based on the average value, data dispersion, and valid data size of the historical data corresponding to each cost parameter, and combined with the preset confidence coefficient, the lower and upper bounds of the confidence interval for each cost parameter are calculated respectively, thus obtaining the confidence interval for each cost parameter in the optimizer.

[0007] In one example, the context features of the database operation are extracted, and the recommendation cost value is determined within the confidence interval using a contextual slot machine algorithm. Specifically, this includes: extracting context features of the database operation state; the context features include the number of concurrent active connections and the buffer hit rate; using the context features and the confidence interval boundaries of the corresponding cost parameters as input, and matching them within the value range of the confidence interval using the contextual slot machine algorithm to obtain the recommendation cost value corresponding to each cost parameter.

[0008] In one example, the candidate generation value is determined by a smoothing formula with damping coefficient based on the recommended generation value and the preset progressive control factor. Specifically, this includes: obtaining the original generation value of the corresponding cost parameter for which the optimizer is currently in effect; and, according to the weight ratio corresponding to the preset progressive control factor, using a strategy of retaining the original generation value with a high proportion and incorporating the recommended generation value with a low proportion, and obtaining the candidate generation value through weighted fusion calculation.

[0009] In one example, the number of execution plan switches for each SQL statement within a preset sliding time window is counted to obtain a plan stability score. Specifically, this includes: generating a unique query fingerprint for each SQL statement in the database to establish an execution plan tracking queue for each SQL statement; tracking the execution plan type for each SQL statement within the preset sliding time window and counting the number of execution plan switches; the execution plan switches include switching between index scans and sequential scans, and switching between different join operation methods; and obtaining the corresponding plan stability score based on the number of execution plan switches within the sliding time window and the total number of executions of the plan within the sliding time window.

[0010] In one example, the method further includes: when the plan stability score is not lower than a preset safety threshold, directly determining the candidate value as an effective candidate factor and writing it into the optimizer.

[0011] In one example, physical performance metrics during SQL execution are asynchronously collected through two paths: business execution feedback and lightweight background probing. Specifically, this includes: embedding an execution completion hook function at the end of the database executor; whenever SQL execution is completed, the hook function asynchronously triggers the collection process to obtain the actual sequential I / O time, random I / O time, CPU operator computation time, and buffer hit rate performance metrics corresponding to the SQL execution; and issuing micro-probing statements through a pre-set lightweight probing thread to probe the database's CPU response speed and storage layer random I / O latency, respectively.

[0012] On the other hand, embodiments of this application provide a database cost model calibration method apparatus, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform any of the above-mentioned database cost model calibration methods.

[0013] On the other hand, embodiments of this application provide a non-volatile computer storage medium for a database cost model calibration method, which stores computer-executable instructions capable of executing any of the above-mentioned database cost model calibration methods.

[0014] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects: This application asynchronously collects real-world performance metrics through a dual-path approach of business execution feedback and lightweight proactive probing, establishing a data loop between the optimizer and executor. It automatically detects hardware performance fluctuations and environmental changes, continuously calibrating underlying cost factors. This solves the problems of poor adaptability and estimation distortion in traditional static cost models, eliminating reliance on manual parameter tuning. This application introduces confidence intervals as a safety boundary for parameter adjustment, combined with a damped progressive smooth update mechanism, and incorporates query fingerprint-based plan stability tracking and oscillation protection logic. This strictly constrains the magnitude of parameter adjustments, preventing execution plan rollovers and performance avalanches caused by parameter mutations, thus improving database operational stability. This application achieves controllable calibration based on the native cost model and contextual slot machine algorithm, combining optimization effectiveness with operational determinism, adapting to the stable operation requirements of enterprise-level databases in complex scenarios such as cloud-native and multi-tenant environments. Attached Figure Description

[0015] To more clearly illustrate the technical solution of this application, some embodiments of this application will be described in detail below with reference to the accompanying drawings, in which: Figure 1 A schematic flowchart illustrating a database cost model calibration method provided in this application embodiment; Figure 2 This is a schematic diagram of the structure of a database cost model calibration method device provided in an embodiment of this application. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0017] Some embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0018] Figure 1 This is a flowchart illustrating a database cost model calibration method provided in an embodiment of this application. This method can be applied to different business domains. Certain input parameters or intermediate results in this process can be manually adjusted to help improve accuracy.

[0019] The analysis method involved in the embodiments of this application can be implemented by a terminal device or a server, and this application does not impose any special limitations on it. For ease of understanding and description, the following embodiments are all described in detail using a server as an example.

[0020] Based on this Figure 1The process may include the following steps: S101: Asynchronously collects physical operation metrics during SQL execution through two paths: business execution feedback and lightweight backend probing.

[0021] In some embodiments of this application, this step adopts a dual-driven data acquisition mode of business execution feedback and lightweight proactive probing. The two types of collected data are uniformly aggregated into the indicator aggregation module to complete data fusion. The entire process is executed asynchronously without synchronous blocking, ensuring low overhead. The business execution feedback acquisition is implemented by embedding an execution end hook function at the end of the database kernel executor. At the same time, the buffer pool manager and storage manager are connected. Whenever a business SQL is executed, the hook function asynchronously triggers the data acquisition process. The collected indicators include the actual time consumption of sequential I / O, the actual time consumption of random I / O, the CPU operator calculation time, the buffer hit rate, and the parallel thread running overhead. All data is aggregated in the background without occupying the main SQL execution process and will not cause synchronous blocking to the normal execution of business SQL.

[0022] Lightweight background probing is implemented through a persistent background probing thread. This thread runs only within the system's allowed load range and periodically issues standard micro-probing statements. Specifically, it uses metadata table query statements to probe basic CPU response speed and non-contiguous block read statements to probe random I / O latency. Proactive probing is used to build a profile of the underlying hardware performance, making up for the lack of coverage of pure business data.

[0023] The data acquisition process is controlled by load control commands issued by the load-aware adaptive window module, which manages the start and stop of the overall acquisition task and the sampling frequency in real time. The data acquisition intensity is dynamically adjusted according to the real-time load status of the database system, so as to control the system overhead on the acquisition side while ensuring the validity of the data.

[0024] S102: Normalize and map the physical operation index to an actual cost value with the same dimension as the cost estimated by the optimizer, and calculate the cost offset based on the actual cost value and the cost estimated by the optimizer. When the cost offset exceeds a preset drift threshold, it is determined that cost drift has occurred.

[0025] In some embodiments of this application, since the optimizer estimates the cost in a dimensionless abstract unit, while the collected physical metrics are in the form of time and count, the units are first unified. Specifically, the actual time taken for a single sequential page read is used as the baseline value. The collected CPU operator calculation time, sequential I / O time, and random I / O time are converted into actual cost scores for the corresponding dimensions. Then, all actual cost scores are summed to obtain the normalized actual cost value. This achieves the alignment of the physical operating metrics with the optimizer's estimated cost in terms of dimensions.

[0026] After unifying the dimensions, the ratio of the normalized actual cost value to the estimated cost value output by the optimizer is calculated to obtain the corresponding cost offset D, as shown in the formula: , where A is the normalized actual cost and E is the optimizer's estimated cost.

[0027] Meanwhile, a drift threshold is preset. When the cost offset is greater than the preset drift threshold or less than the reciprocal of the preset drift threshold, the current cost model is determined to be distorted, cost drift occurs, and a corresponding cost drift event is generated to trigger the subsequent parameter calibration process. If the cost offset is within the preset threshold range, the current cost model is determined to be accurate, and no parameter calibration is required. Only continuous collection of running data and monitoring of deviation status are needed.

[0028] S103: When cost drift occurs, the confidence interval of each cost parameter in the optimizer is determined based on the valid historical data of the optimizer's preset historical interval.

[0029] In some embodiments of this application, the traditional single-point cost value maintenance method is abandoned. Instead, a probability distribution and dynamic confidence interval are maintained for each core cost parameter as a hard safety boundary for parameter adjustment. The core cost parameters cover the underlying cost factors of the optimizer, such as sequential page access cost, random page access cost, and CPU tuple processing cost.

[0030] Upon detecting a cost drift event, for each cost parameter in the optimizer, valid historical running sample data within a preset historical interval is retrieved. Based on the average value, data dispersion, and valid sample size of the historical data corresponding to each cost parameter, and combined with a pre-set confidence coefficient, the lower and upper bounds of the confidence interval for each cost parameter are calculated, forming a complete confidence interval for the parameter value. The specific formula is as follows: , Where Li is the lower bound, Ui is the upper bound, k is the confidence coefficient, and n is the effective sample (data) size. The average value of historical data. This represents the degree of data dispersion.

[0031] The confidence interval has a dynamic adjustment characteristic. When the number of effective samples is smaller and the data dispersion is higher, the confidence interval of the corresponding cost parameter is automatically widened, strictly limiting the adjustment step size of a single calibration and avoiding blind adjustment of parameters when there are insufficient samples. When the number of effective samples is larger and the data dispersion is more convergent, the confidence interval of the corresponding cost parameter is automatically narrowed, improving the accuracy of parameter calibration. All subsequent update operations of cost factors are strictly limited to the confidence interval of the corresponding cost parameter and must not exceed the safety boundary.

[0032] S104: Extract the contextual features of the database operation and determine the recommendation cost within the confidence interval using the contextual slot machine algorithm.

[0033] In some embodiments of this application, the context features of the current running state of the database are first extracted, specifically including two core running features: the number of current concurrent active connections and the buffer hit rate. These features can intuitively reflect the real-time load status and cache running efficiency of the database, so that parameter calibration can be adapted to the current actual running scenario.

[0034] Furthermore, the extracted context features and the confidence interval boundaries of the corresponding cost parameters are used as input. The context slot machine algorithm completes the balance decision between exploration and utilization within the range of the confidence interval. Exploration is used to try new parameter values ​​to explore better calibration effects, while utilization is used to continue using verified high-quality parameter values ​​to ensure operational stability. The algorithm combines the current operating context to match suitable parameter values ​​and finally outputs the recommended cost value corresponding to each cost parameter.

[0035] All recommended cost values ​​obtained through the contextual slot machine algorithm are strictly constrained within the confidence interval of the corresponding cost parameters, ensuring that the parameter values ​​are always within the safety boundary. Controllable intelligent calibration is achieved by relying on the native cost model of the database, balancing the intelligence of calibration with the determinism of operation.

[0036] S105: Based on the recommended generation value and the preset progressive control factor, the candidate generation value is determined by a smoothing formula with a damping coefficient.

[0037] In some embodiments of this application, the original cost value of the corresponding cost parameter currently in effect by the optimizer is first obtained. Then, according to the weight ratio corresponding to the preset progressive control factor, a strategy of retaining the original cost value with a high proportion and incorporating the recommended cost value with a low proportion is adopted, and candidate cost values ​​are obtained through weighted fusion calculation. The progressive control factor can be set to a range of 0.05 to 0.2. Through the constraint of this damping coefficient, the cost parameter will not be directly overwritten and replaced by the recommended cost value, but will be slightly and progressively adjusted based on the original value to achieve smooth evolution and avoid jumps in parameter values. The specific candidate cost value formula is as follows: Where α is the asymptotic control factor. For original generation value, This is to recommend value.

[0038] This damped smooth update method can prevent large single parameter adjustments from impacting the overall cost assessment logic of the database, making the cost factor update a gradual fine-tuning process. While gradually correcting estimation deviations, it maintains the continuity of the parameter system to the greatest extent and reduces the impact of parameter changes on the business SQL execution plan.

[0039] S106: Within a preset sliding time window, count the number of execution plan switching times for each SQL statement to obtain a plan stability score.

[0040] In some embodiments of this application, a unique query fingerprint is first generated for each SQL statement in the database, and a corresponding execution plan tracking queue is established for each SQL statement. The query fingerprint enables accurate identification of the SQL statement, ensuring that the execution plan of the same SQL statement is continuously tracked and avoiding confusion and statistics of execution data from different SQL statements.

[0041] Within a preset sliding time window, the execution plan type corresponding to each SQL statement is continuously tracked, and the number of execution plan switches is counted. Execution plan switches include switching between index scan and sequential scan, switching between different join operation methods, such as switching between hash join and nested loop join, and other plan changes that can significantly affect execution performance. When the plan types of two adjacent executions are inconsistent, it is counted as a plan switch.

[0042] Finally, the corresponding plan stability score is calculated based on the proportion of the number of execution plan switching times within the sliding time window to the total number of plan execution times within the window. The more execution plan switching times there are, the lower the plan stability score, which indicates that the execution plan has a higher risk of oscillation. This score can be in the form of a percentage or a ten-point scale, providing a basis for judgment for subsequent oscillation protection verification.

[0043] S107: When the plan stability score is lower than the preset safety threshold, the progressive control factor is decayed to a preset percentage of the original value, and the candidate generation value is recalculated using the decayed progressive control factor until the plan stability score is not lower than the preset safety threshold. The candidate generation value of the final round is then determined as the effective cost factor and written into the optimizer.

[0044] In some embodiments of this application, a safety threshold for plan stability is preset. This threshold can be flexibly configured according to the stability requirements of the business system. After obtaining the plan stability score, it is first compared with the preset safety threshold. When the plan stability score is not lower than the preset safety threshold, it is determined that the current execution plan is stable and there is no risk of oscillation. The candidate cost value is directly determined as the effective cost factor and written into the dynamic cost model of the optimizer.

[0045] When the plan stability score is lower than the preset safety threshold, it is determined that there is a risk of plan oscillation. At this time, the progressive control factor is decayed to a preset percentage of the original value, such as 10% of the original value. By reducing the value of the progressive control factor, the parameter adjustment range is reduced. Then, the candidate cost value is recalculated using the decayed progressive control factor. After the recalculation is completed, the corresponding plan stability score is checked again. Through multiple rounds of iterative decay and recalculation, the parameter adjustment range is gradually reduced until the plan stability score rises back to no less than the preset safety threshold. Finally, the candidate cost value calculated in the last round is determined as the effective cost factor and written into the optimizer.

[0046] Once effective, the cost factor will be used for cost estimation and execution plan generation for all subsequent SQL queries. The new SQL execution results will then be collected again and enter the calibration process, thus forming a complete execution feedback calibration closed loop and achieving continuous adaptive calibration of the database cost model.

[0047] It should be noted that, although the embodiments in this application are based on... Figure 1 Steps S101 to S107 will be described sequentially, but this does not mean that steps S101 and S107 must be performed in a strict order. The reason this embodiment follows this order is... Figure 1 The order in which steps S101 to S107 are described is provided to facilitate understanding of the technical solutions of the embodiments of this application by those skilled in the art. In other words, in the embodiments of this application, the order of steps S101 to S107 can be appropriately adjusted according to actual needs.

[0048] Figure 2 A schematic diagram of a database cost model calibration method device provided in this application embodiment includes: At least one processor; and, A memory that is communicatively connected to at least one processor; wherein, A database cost model calibration method is described in which the memory stores instructions that can be executed by at least one processor, such that the at least one processor is able to perform any of the above-mentioned tasks.

[0049] Some embodiments of this application provide a database cost model calibration method using a non-volatile computer storage medium storing computer-executable instructions that can execute any of the above-described database cost model calibration methods.

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

[0051] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0052] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0053] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0054] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0055] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0056] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0057] Memory may include non-persistent storage in computer-readable media, random access memory (RAM), and non-volatile memory such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0058] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0059] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0060] The above are merely embodiments of this application and are not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the technical principles of this application should fall within the protection scope of this application.

Claims

1. A database cost model calibration method, characterized in that, The method includes: Physical performance metrics during SQL execution are collected asynchronously through two approaches: business execution feedback and lightweight backend probing. The physical operation index is normalized and mapped to an actual cost value with the same dimension as the optimizer's estimated cost. The cost offset is calculated based on the actual cost value and the optimizer's estimated cost. When the cost offset exceeds a preset drift threshold, it is determined that a cost drift has occurred. When cost drift occurs, the confidence interval of each cost parameter in the optimizer is determined based on the valid historical data of the optimizer's preset historical interval. Extract the contextual features of the database operation, and determine the recommendation cost within the confidence interval using the contextual slot machine algorithm; Based on the recommended replacement value and the preset progressive control factor, the candidate replacement value is determined by a smoothing formula with a damping coefficient. Within a preset sliding time window, the number of execution plan switching times for each SQL statement is counted to obtain a plan stability score; When the plan stability score is lower than the preset safety threshold, the progressive control factor is decayed to a preset percentage of its original value, and the candidate generation value is recalculated using the decayed progressive control factor until the plan stability score is not lower than the preset safety threshold. The candidate generation value of the final round is then determined as the effective cost factor and written into the optimizer.

2. The method according to claim 1, characterized in that, The process of normalizing and mapping the physical operation indicators to actual cost values ​​with the same dimensions as the optimizer's estimated cost, and calculating the cost offset based on the actual cost value and the optimizer's estimated cost, and determining that cost drift has occurred when the cost offset exceeds a preset drift threshold, specifically includes: Using the actual time taken for a single sequential page read as the benchmark unit, the CPU operator computation time, sequential I / O time, and random I / O time in the physical operation metrics are converted into actual cost scores for the corresponding dimensions. The actual cost value is obtained by summing all the actual cost scores. The cost offset is obtained by calculating the ratio between the actual cost and the cost estimated by the optimizer. A drift threshold is preset. When the cost offset is greater than the preset drift threshold, it is determined that a cost drift has occurred.

3. The method according to claim 1, characterized in that, When cost drift occurs, the confidence interval for each cost parameter in the optimizer is determined based on valid historical data within a preset historical interval. This specifically includes: When cost drift occurs, for each cost parameter in the optimizer, retrieve the corresponding valid historical data within the preset historical interval; Based on the average value of the historical data corresponding to each cost parameter, the degree of data dispersion, and the effective data size, and combined with the pre-set confidence coefficient, the lower and upper bounds of the confidence interval corresponding to each cost parameter are calculated to obtain the confidence interval of each cost parameter in the optimizer.

4. The method according to claim 1, characterized in that, The extraction of contextual features from the database operation and the determination of the recommendation value within the confidence interval using a contextual slot machine algorithm specifically include: Extract context features of the database in operation; the context features include the number of concurrent active connections and the buffer hit rate; The context features and the confidence interval boundaries of the corresponding cost parameters are used as inputs. The context slot machine algorithm is used to match the values ​​within the confidence interval to obtain the recommendation cost value corresponding to each cost parameter.

5. The method according to claim 1, characterized in that, The step of determining candidate generation values ​​using a smoothing formula with damping coefficients based on the recommended generation value and a preset asymptotic control factor specifically includes: Get the original cost value of the corresponding cost parameter for which the optimizer is currently in effect; Based on the weight ratios corresponding to the preset progressive control factors, a strategy is adopted to retain the original generation value with a high proportion and incorporate the recommended generation value with a low proportion, and the candidate generation value is obtained through weighted fusion calculation.

6. The method according to claim 1, characterized in that, The step of counting the number of execution plan switches for each SQL statement within a preset sliding time window to obtain a plan stability score specifically includes: Generate a unique query fingerprint for each SQL statement in the database to establish an execution plan tracing queue for each SQL statement; Within a preset sliding time window, the execution plan type corresponding to each SQL statement is tracked, and the number of execution plan switches is counted; the execution plan switches include switching between index scan and sequential scan, and switching between different join operation methods; The corresponding plan stability score is obtained based on the number of plan switching times within the sliding time window and the total number of plan executions within the sliding time window.

7. The method according to claim 1, characterized in that, The method further includes: When the plan stability score is not lower than the preset safety threshold, the candidate cost value is directly determined as an effective candidate factor and written into the optimizer.

8. The method according to claim 1, characterized in that, The method asynchronously collects physical performance metrics during SQL execution through two paths: business execution feedback and lightweight backend probing. Specifically, this includes: An execution completion hook function is implanted at the end of the database executor. Whenever the SQL execution is completed, the hook function asynchronously triggers the collection process to obtain the actual sequential I / O time, random I / O time, CPU operator calculation time, and buffer hit rate of the SQL execution. By using a pre-set lightweight probe thread, mini probing statements are sent to probe the CPU response speed of the database and the random I / O latency of the storage layer.

9. A database cost model calibration method and device, characterized in that, include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed, enables the at least one processor to perform a database cost model calibration method according to any one of claims 1-8.

10. A storage medium for a database cost model calibration method, storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of executing the database cost model calibration method according to any one of claims 1-8.

Images