Method, device, equipment and medium for calibrating cost factors of calling database

Abstract

The embodiments of the present application provide a method, device, electronic device and medium for calibrating the cost factor of calling a database. The method includes: obtaining a set of query statements for a database; determining the execution plan of each query statement in the query statement set respectively; executing each execution plan, determining the target cardinality of each operator in each execution plan and the actual execution time of each execution plan; determining the estimated execution time of each execution plan according to the target cardinality and initial cost factor of each operator in each execution plan; adjusting the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of all execution plans, until the difference converges, and obtaining the cost factor of each operator in each execution plan. The embodiments of the present application calibrate the initial cost factor through the difference between the actual execution time and the estimated execution time of the execution plan of each query statement, thereby improving the accuracy of the cost factor.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a calibration method, device, electronic device and computer-readable storage medium for calling a cost factor of a database. Background Art

[0002] The query optimizer is a core component of relational databases and some big data processing engines. The query optimizer can formulate an optimal execution plan for the query statement so that the executor can perform data operations according to the generated execution plan. The cost-based optimizer can determine the cost of each execution plan based on the cardinality estimation and cost model, and then select the execution plan with the lowest cost as the optimal execution plan; among them, the cost factor is one of the important parameters of the cost model, and the calibration of the cost factor is a key link for the query optimizer to generate a correct execution plan.

[0003] In the prior art, differential calculation is usually performed based on calibration query to calibrate the cost factor. In this process, manual experience is needed, the labor cost is high and human errors cannot be avoided. At the same time, manual differential calculation will cause low calibration efficiency. Summary of the invention

[0004] The embodiment of the present application provides a calibration method, device, electronic device and computer-readable storage medium for calling a cost factor of a database, which can solve the problem of low cost factor calibration efficiency in the prior art. The technical solution is as follows:

[0005] According to one aspect of an embodiment of the present application, a method for calibrating a cost factor of a calling database is provided, the method comprising:

[0006] Get a set of query statements for the database;

[0007] Determine the execution plan of each query statement in the query statement set respectively; wherein the execution plan includes at least one operator;

[0008] Execute each execution plan to determine the target cardinality of each operator in each execution plan and the actual execution time of each execution plan; the target cardinality represents the number of rows in the data set in the database called by each corresponding operator;

[0009] Determine the estimated execution time of each execution plan based on the target cardinality and initial cost factor of each operator in each execution plan;

[0010] According to the difference between the actual execution time and the estimated execution time of each execution plan, the initial cost factor of each operator in each execution plan is adjusted until the difference converges, and the cost factor of each operator in each execution plan is obtained; wherein the cost factor represents the unit cost of each corresponding operator.

[0011] Optionally, the above determination of the target cardinality of each operator in each execution plan and the actual execution time of each execution plan includes:

[0012] After executing each execution plan, the execution data of each execution plan is obtained; wherein the execution data includes the actual execution time of the execution plan;

[0013] The execution plan is parsed according to each execution data, and the target cardinality of each operator of each execution plan is determined.

[0014] Optionally, the estimated execution time for each execution plan determined above includes:

[0015] Add limiting conditions corresponding to the target cardinality to each query statement;

[0016] Execute the execution plan of each query statement according to the limiting conditions;

[0017] Get the weight of the initial cost factor of each operator during the execution of each execution plan;

[0018] According to the initial cost factor of each operator and the weight of the initial cost factor, the estimated execution time of the execution plan is obtained.

[0019] Optionally, the initial cost factor of each operator in each execution plan is adjusted according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges, and the cost factor of each operator in each execution plan is obtained, including:

[0020] Determine the loss function based on the difference between the actual execution time and the estimated execution time of all execution plans;

[0021] The initial cost factor of each operator in each execution plan is adjusted according to the loss function until the difference converges and the cost factor is obtained.

[0022] Optionally, the initial cost factor of each operator in each execution plan is adjusted according to the loss function until the difference converges to obtain the cost factor, including:

[0023] Calculate the change in the initial cost factor of each operator based on the loss function;

[0024] Adjust the initial cost factor of each operator according to the change;

[0025] Determine the loss value of the loss function based on the adjusted initial cost factor;

[0026] When the loss value is less than the preset threshold, the cost factor is obtained.

[0027] Optionally, the loss function is determined based on the difference between the actual execution time and the estimated execution time of all execution plans, including:

[0028] Determine the mean of the difference between the actual execution time and the estimated execution time for all execution plans;

[0029] Construct a loss function based on the mean.

[0030] According to another aspect of an embodiment of the present application, a calibration device for calling a cost factor of a database is provided, the device comprising:

[0031] An acquisition module is used to obtain a set of query statements for a database;

[0032] A determination module, used to respectively determine the execution plan of each query statement in the query statement set; wherein the execution plan includes at least one operator;

[0033] An execution module is used to execute each execution plan, determine the target cardinality of each operator in each execution plan and the actual execution time of each execution plan; wherein the target cardinality represents the number of rows in the data set in the database called by each corresponding operator;

[0034] The estimation module is used to determine the estimated execution time of each execution plan based on the target cardinality and initial cost factor of each operator in each execution plan;

[0035] The adjustment module is used to adjust the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges, thereby obtaining the cost factor of each operator in each execution plan; wherein the cost factor represents the unit cost of each corresponding operator.

[0036] Optionally, when determining the target cardinality of each operator in each execution plan and the actual execution time of each execution plan, the execution module is used to:

[0037] After executing each execution plan, the execution data of each execution plan is obtained; wherein the execution data includes the actual execution time of the execution plan;

[0038] The execution plan is parsed according to each execution data, and the target cardinality of each operator of each execution plan is determined.

[0039] Optionally, when determining the estimated execution time of each execution plan, the estimation module is used to:

[0040] Add limiting conditions corresponding to the target cardinality to each query statement;

[0041] Execute the execution plan of each query statement according to the limiting conditions;

[0042] Get the weight of the initial cost factor of each operator during the execution of each execution plan;

[0043] According to the initial cost factor of each operator and the weight of the initial cost factor, the estimated execution time of the execution plan is obtained.

[0044] Optionally, the adjustment module adjusts the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges to obtain the cost factor of each operator in each execution plan, and is used to:

[0045] Determine the loss function based on the difference between the actual execution time and the estimated execution time of all execution plans;

[0046] The initial cost factor of each operator in each execution plan is adjusted according to the loss function until the difference converges and the cost factor is obtained.

[0047] Optionally, when the adjustment module adjusts the initial cost factor of each operator in each execution plan according to the loss function until the difference converges and the cost factor is obtained, it is used to:

[0048] Calculate the change in the initial cost factor of each operator based on the loss function;

[0049] Adjust the initial cost factor of each operator according to the change;

[0050] Determine the loss value of the loss function based on the adjusted initial cost factor;

[0051] When the loss value is less than the preset threshold, the cost factor is obtained.

[0052] Optionally, when determining the loss function according to the difference between the actual execution time and the estimated execution time of all execution plans, the adjustment module is used to:

[0053] Determine the mean of the difference between the actual execution time and the estimated execution time for all execution plans;

[0054] Construct a loss function based on the mean.

[0055] According to another aspect of an embodiment of the present application, an electronic device is provided, which includes: a memory, a processor, and a computer program stored in the memory, and the processor executes the computer program to implement the steps of the method shown in the first aspect of the embodiment of the present application.

[0056] According to another aspect of the embodiments of the present application, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of the method shown in the first aspect of the embodiments of the present application are implemented.

[0057] According to one aspect of an embodiment of the present application, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of the method shown in the first aspect of the embodiment of the present application are implemented.

[0058] The beneficial effects of the technical solution provided by the embodiment of the present application are:

[0059] The embodiment of the present application generates a corresponding execution plan for each query statement in a query statement set; then, when each execution plan is executed for the first time, the actual execution time of each execution plan and the actual number of rows of the data set in the database called by each operator, that is, the target cardinality, are determined; then, during the second execution of each execution plan, the execution time of the execution plan is estimated based on the target cardinality and the initial cost factor to obtain the estimated execution time; and then, based on the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator is adjusted until the above difference converges to obtain the calibrated cost factor of each operator. In the embodiment of the present application, after determining the target cardinality of each operator of each execution plan, the execution time of each execution plan is estimated based on the target cardinality and the initial cost factor, thereby eliminating the influence of the error in the cardinality estimation on the cost estimation of the execution plan and improving the correlation between the estimated execution time and the initial cost factor. Compared with the prior art, in which differential calculation is performed according to a calibration query to complete the calibration of the cost factor, the present application performs regression training based on the difference between the actual execution time and the estimated execution time of each execution plan, adjusts the initial cost factor, avoids the reliance on manual experience in the prior art, and effectively improves the accuracy and efficiency of the cost factor calibration. BRIEF DESCRIPTION OF THE DRAWINGS

[0060] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in describing the embodiments of the present application are briefly introduced below.

[0061] Figure 1 A schematic diagram of an application scenario of a calibration method for a cost factor of a calling database provided in an embodiment of the present application;

[0062] Figure 2 A schematic diagram of a flow chart of a calibration method for a cost factor of a calling database provided in an embodiment of the present application;

[0063] Figure 3A schematic diagram of an execution flow of an execution plan in a calibration method for a cost factor of a calling database provided in an embodiment of the present application;

[0064] Figure 4 A schematic diagram of a process for adjusting a cost factor in a calibration method for calling a cost factor of a database provided in an embodiment of the present application;

[0065] Figure 5 A schematic diagram of a flow chart of a method for calibrating a cost factor of a calling database provided as an example in an embodiment of the present application;

[0066] Figure 6 A schematic diagram of the structure of a calibration device for calling a cost factor of a database provided in an embodiment of the present application;

[0067] Figure 7 A schematic diagram of the structure of a calibration electronic device for calling a cost factor of a database provided in an embodiment of the present application. DETAILED DESCRIPTION

[0068] The embodiments of the present application are described below in conjunction with the drawings in the present application. It should be understood that the implementation methods described below in conjunction with the drawings are exemplary descriptions for explaining the technical solutions of the embodiments of the present application and do not constitute a limitation on the technical solutions of the embodiments of the present application.

[0069] It will be understood by those skilled in the art that, unless specifically stated, the singular forms "one", "said", and "the" used herein may also include plural forms. It should be further understood that the terms "including" and "comprising" used in the embodiments of the present application refer to that the corresponding features can be implemented as the presented features, information, data, steps, operations, elements and / or components, but do not exclude the implementation as other features, information, data, steps, operations, elements, components and / or combinations thereof supported by the technical field. It should be understood that when we say that an element is "connected" or "coupled" to another element, the one element may be directly connected or coupled to the other element, or it may refer to that the one element and the other element establish a connection relationship through an intermediate element. In addition, the "connection" or "coupling" used herein may include wireless connection or wireless coupling. The term "and / or" used herein indicates at least one of the items defined by the term, for example, "A and / or B" may be implemented as "A", or as "B", or as "A and B".

[0070] In order to make the objectives, technical solutions and advantages of the present application clearer, the implementation methods of the present application will be further described in detail below in conjunction with the accompanying drawings.

[0071] A relational database system is a collection of all relations that correspond to a relational model. A relational database system establishes a relational model and uses it to process data. The relational model associates information with fields in a table to store data.

[0072] Database management systems require structures (such as tables) to be defined before storing data. With a table, each column (field) stores a different type (data type) of information. Each record in the database has its own unique key, as a row belonging to a table, and each piece of information in the row corresponds to a column in the table - all the relationships together form a relational model.

[0073] In the relational model, entities and the connections between entities are represented by relations. For example, the relations among teachers, students, courses, teaching and learning and the connections between them constitute a teaching management database. Therefore, the set of all relations corresponding to a relational model is called a relational database.

[0074] The query optimizer (or optimizer for short) is a database engine component responsible for generating effective execution plans for SQL (Structured Query Language) statements. Specifically, the query optimizer is a component of a relational database management system that performs internal optimization based on user requests, generates (or reuses) execution plans, transmits them to the storage engine to operate data, and finally returns results to the user. It is one of the core components of a relational database management system, and determines which indexes and association algorithms to use for a specific query, so that it can run efficiently.

[0075] In the prior art, when calibrating the cost factor corresponding to the optimizer, it is necessary to manually design a calibration query, and then use the above calibration query to perform differential calculation to obtain the value of the calibrated cost factor. The whole process requires a lot of manual participation; at the same time, when the database introduces a new data set, the above calibration query also needs to be updated accordingly, resulting in a long calibration time and low calibration efficiency.

[0076] The method, device, electronic device and computer-readable storage medium for calibrating the cost factor of calling a database provided in the present application are intended to solve the above technical problems in the prior art.

[0077] The following describes several exemplary embodiments to illustrate the technical solutions of the embodiments of the present application and the technical effects produced by the technical solutions of the present application. It should be noted that the following embodiments can refer to, draw on or combine with each other, and the same terms, similar features and similar implementation steps in different embodiments will not be described repeatedly.

[0078] like Figure 1As shown, the calibration method of the cost factor of calling the database of the present application can be applied to Figure 1 In the scenario shown, specifically, the server 102 can obtain a set of query statements for data from the client 101; the server 102 determines the execution plan of each query statement in the query statement set based on the query optimizer; wherein the execution plan includes at least one operator; then the server executes each execution plan and determines the target cardinality of each operator in each execution plan and the actual execution time of each execution plan; then, based on the target cardinality and initial cost factor of each operator in each execution plan, the estimated execution time of each execution plan is determined; and then, based on the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator in each execution plan is adjusted until the difference converges, and the cost factor of each operator in each execution plan is obtained. After obtaining the calibrated cost factor, the server 102 can estimate the execution cost of the query sent by the client based on the cost factor.

[0079] Figure 1 In the scenario shown, the calibration method of the cost factor of calling the database can be performed in the server, and in other scenarios, it can also be performed in the terminal.

[0080] Technicians in this technical field can understand that the "terminal" used here can be a mobile phone, a tablet computer, a PDA (Personal Digital Assistant), a MID (Mobile Internet Device), etc.; the "server" can be implemented by an independent server or a server cluster consisting of multiple servers.

[0081] The present application provides a method for calibrating the cost factor of calling a database, such as Figure 2 As shown, it can be applied to a server or a terminal for cost factor calibration, and the method includes:

[0082] S201, obtaining a query statement set for a database.

[0083] The above query statement is used to call the preset data set in the database.

[0084] In some implementations, the server or terminal used for cost factor calibration may collect query statements in real time based on user input operations, wherein the user input operations include at least one of the following:

[0085] Drag or move the interface component corresponding to the query statement to the preset range of the current interface;

[0086] Click or touch operations on the interface components corresponding to the query statement;

[0087] Input operations for query statements in preset input controls.

[0088] In other implementations, a server or terminal for cost factor calibration may obtain a query statement set sent by other clients based on a wired or wireless local area network.

[0089] The wired LAN may be Ethernet based on IEEE 802.3 (a LAN communication standard) protocol, and the wireless LAN may be Wi-Fi (a wireless communication technology) based on IEEE 802.11 (a wireless network communication standard) protocol.

[0090] S202, determining the execution plan of each query statement in the query statement set respectively; wherein the execution plan includes at least one operator.

[0091] Specifically, the server or terminal used for cost factor calibration may perform syntax and semantic analysis on each query statement to generate an execution plan for each query statement.

[0092] The execution plan may be an execution tree composed of physical operators. A physical operator generally corresponds to an operator, such as scan, join, aggregate, sort, etc. The execution plan organizes different physical operators in an execution tree in a certain order to complete the query of the corresponding query statement.

[0093] S203, executing each execution plan, determining the target cardinality of each operator in each execution plan and the actual execution time of each execution plan.

[0094] The target cardinality represents the number of rows of the data set in the database called by each corresponding operator.

[0095] Specifically, a server or terminal for cost factor calibration may execute each execution plan based on the optimizer, and obtain the actual execution time of the execution plan and the target cardinality of each operator in the execution plan after the execution.

[0096] In an embodiment of the present application, a database management system is taken as an example for specific description. The system runs on a server or terminal for cost factor calibration. The system may include a query optimizer component. When the query statement is "select * from t where a + 1 < 3", the query statement indicates the fields in the query table t that satisfy the condition a + 1 < 3, and the query statement includes a selection operator and a scan operator. The query optimizer component can execute the plan based on the explain analyze instruction, and obtain the actual execution time T of the plan and the number of rows processed by each operator. In the initial cost estimation model of the query optimization component, the cardinality of the selection operator and the scan operator are estimated to be 10 rows, and after actual execution, the target cardinality of the selection operator can be 12 rows and the target cardinality of the scan operator can be 15 rows.

[0097] S204, determining the estimated execution time of each execution plan according to the target cardinality and initial cost factor of each operator in each execution plan.

[0098] Specifically, the server or terminal used for cost factor calibration can re-execute each execution plan based on the optimizer, and estimate the estimated execution time of each execution plan according to the corresponding target cardinality and initial cost factor during the execution of each execution plan.

[0099] In the embodiment of the present application, a database management system is used as an example for specific description. The system runs on a server or terminal for cost factor calibration. The system may include a query optimizer component. When the query statement is "select * from t where a + 1 < 3", the query statement indicates the fields in the query table t that meet the condition a + 1 < 3, and the query statement includes a selection operator and a scan operator. The query optimizer component can execute the execution plan according to the explain analyze command and obtain the cost estimation data of the execution plan. Among them, the query optimizer component can perform cost estimation according to the initial cost factor and the target cardinality of each operator to obtain the estimated execution time t1 of the execution plan.

[0100] S205, adjusting the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges, thereby obtaining the cost factor of each operator in each execution plan.

[0101] The cost factor represents the unit cost of each corresponding operator.

[0102] Specifically, the server or terminal used for cost factor calibration can use the actual execution time and the estimated execution time of all execution plans as a training set, perform regression training based on the training set, and obtain the calibrated cost factor of each operator in each execution plan after regression training; wherein the process of regression training includes:

[0103] According to the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator in each execution plan is adjusted until the sum of the above differences is minimized, that is, the differences converge, and the calibrated cost factor of each operator in each execution plan is obtained.

[0104] The embodiment of the present application generates a corresponding execution plan for each query statement in a query statement set; then, when each execution plan is executed for the first time, the actual execution time of each execution plan and the actual number of rows of the data set in the database called by each operator, that is, the target cardinality, are determined; then, during the second execution of each execution plan, the execution time of the execution plan is estimated based on the target cardinality and the initial cost factor to obtain the estimated execution time; and then, based on the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator is adjusted until the above difference converges to obtain the calibrated cost factor of each operator. In the embodiment of the present application, after determining the target cardinality of each operator of each execution plan, the execution time of each execution plan is estimated based on the target cardinality and the initial cost factor, thereby eliminating the influence of the error in the cardinality estimation on the cost estimation of the execution plan and improving the correlation between the estimated execution time and the initial cost factor. Compared with the prior art, in which differential calculation is performed according to a calibration query to complete the calibration of the cost factor, the present application performs regression training based on the difference between the actual execution time and the estimated execution time of each execution plan, adjusts the initial cost factor, avoids the reliance on manual experience in the prior art, and effectively improves the accuracy and efficiency of the cost factor calibration.

[0105] A possible implementation method is provided in the embodiment of the present application, such as Figure 3 As shown, the target cardinality of each operator in each execution plan and the actual execution time of each execution plan in the above step S203 include:

[0106] After executing each execution plan, the execution data of each execution plan is obtained; wherein the execution data includes the actual execution time of the execution plan; the execution plan is parsed according to each execution data to determine the target cardinality of each operator of each execution plan.

[0107] Specifically, a server or terminal used for cost factor calibration can execute each execution plan according to the explain analyze instruction, and after executing each execution plan, the execution data of the execution plan can be obtained; wherein the execution data includes the total time consumed to run the execution plan, that is, the actual execution time, the actual number of processed rows and row width corresponding to each operator in the execution plan, the estimated execution time of the execution plan, and the estimated number of processed rows and row width corresponding to each operator. The above-mentioned estimated execution time is determined based on the estimated number of processed rows and row width, and the estimated number of processed rows and row width are determined based on the initial cost factor and the estimated cardinality.

[0108] In an embodiment of the present application, a possible implementation method is provided. In the above step S204, the estimated execution time of each execution plan is determined, including:

[0109] (1) Add limiting conditions corresponding to the target cardinality to each query statement.

[0110] Specifically, a server or terminal used for cost factor calibration may use a hint instruction to add a restriction condition corresponding to the target cardinality to each query statement.

[0111] (2) Execute the execution plan of each query statement according to the limited conditions; obtain the weight of the initial cost factor of each operator during the execution of each execution plan.

[0112] The weight of the initial cost factor may be obtained according to the estimated number of processing rows and the estimated processing row width of the corresponding operator.

[0113] The weight of the initial cost factor indicates different calculation methods based on different operators. In some embodiments, the weight of the initial cost factor may be the product of the estimated number of processing rows and the estimated processing row width of the corresponding operator; in other embodiments, the weight of the initial cost factor may be the product of the logarithm of the estimated processing row width of the corresponding operator and the estimated number of processing rows.

[0114] Specifically, the server or terminal used for cost factor calibration uses the explain analyze instruction to run the query statement after adding the limiting condition, so as to estimate the execution cost of the execution plan based on the limiting condition during the execution of the corresponding execution plan.

[0115] (3) Based on the initial cost factor of each operator and the weight of the initial cost factor, the estimated execution time of the execution plan is obtained.

[0116] Specifically, the product of the initial cost factor and the corresponding weight of each operator in the execution plan may be determined, and then the sum of the products corresponding to all operators in the execution plan is taken as the estimated execution time of the execution plan.

[0117] In the embodiment of the present application, a database management system is used as an example for specific description. The system runs on a server or terminal for cost factor calibration. The system may include a query optimizer component. When the query statement is "select*fromtwherea+1<3", the query statement indicates the fields in the query table t that meet the condition a+1<3, and the query statement includes a selection operator and a scan operator. The query optimizer component can execute the plan based on the explain analyze instruction and obtain the actual execution time T of the plan and the number of rows processed by each operator. The explain analyze instruction can also be output in the initial cost estimation model of the query optimization component, and the cardinality of the selection operator and the scan operator is estimated to be 10 rows, and the target cardinality of the selection operator obtained by actual execution is 12 rows, and the target cardinality of the scan operator is 15 rows. Then, the hint instruction can be used to add the corresponding target cardinality restriction condition to the query statement: select / *+cardinality(Scan,15),cardinality(Selection,12)* / *fromtwherea+1<3.

[0118] Next, you can run the query statement with the restriction added based on the explain analyze command to get the weights corresponding to each initial cost factor. For example, the initial cost factor of the selection operator is x1, and the initial cost factor of the scan operator is x2. According to the execution result of the explain analyze command, the weight of x1 is a1, and the weight of x2 is a2; then the estimated execution time of the execution plan corresponding to the query statement is t1 = x1*a1+x2*a2.

[0119] A possible implementation method is provided in the embodiment of the present application, such as Figure 4 As shown, in the above step S205, according to the difference between the actual execution time and the estimated execution time of each execution plan, the initial cost factor of each operator in each execution plan is adjusted until the difference converges, and the cost factor of each operator in each execution plan is obtained, including:

[0120] (1) Determine the loss function based on the difference between the actual execution time and the estimated execution time of all execution plans.

[0121] A possible implementation method is provided in an embodiment of the present application. The loss function is determined based on the difference between the actual execution time and the estimated execution time of all execution plans, including:

[0122] Determine the mean of the difference between the actual execution time and the estimated execution time corresponding to all execution plans; construct a loss function based on the mean.

[0123] In the embodiment of the present application, the initial cost factor and the initial cost factor corresponding to each execution plan can be used as a vector for subsequent calculation; for example, when the execution plan of the query statement includes a selection operator, a scan operator, and an aggregation operator, the initial cost factors corresponding to the above operators include: selection cost factor x1, scan cost factor x2, memory cost factor x3, CPU cost factor x4, where x i The weight is a i , i∈[1,4], i is an integer.

[0124] The execution time corresponding to the above statement is T1. The difference g between the estimated execution time and the actual execution time of the execution plan can be obtained based on the following formula:

[0125]

[0126] In practical applications, the weights of the same initial cost factors in all execution plans can be accumulated, and the loss function L can be set as:

[0127]

[0128] Where j is the number of initial cost factors, n is the number of query statements, and b j It is the cumulative value of the weights of the same initial cost factor in all execution plans, where j and n are positive integers.

[0129] (2) Adjust the initial cost factor of each operator in each execution plan according to the loss function until the difference converges and the cost factor is obtained.

[0130] In an embodiment of the present application, a possible implementation method is provided, wherein the initial cost factor of each operator in each execution plan is adjusted according to the loss function until the difference converges to obtain the cost factor, including:

[0131] a. Calculate the change in the initial cost factor of each operator based on the loss function.

[0132] Specifically, the partial derivatives of each initial cost factor can be calculated based on the loss function to determine the change Δx of each initial cost factor. j ,in,

[0133] b. Adjust the initial cost factor of each operator according to the change.

[0134] The above changes can be added to the corresponding initial cost factor to obtain the adjusted initial cost factor x′ j =x j +Δx j .

[0135] c. Determine the loss value of the loss function based on the adjusted initial cost factor.

[0136] The adjusted initial cost factor can be brought into the loss function to calculate the loss value of the loss function.

[0137] d. When the loss value is less than the preset threshold, the cost factor is obtained.

[0138] Repeat the calculations from steps a to c above until the loss value is less than a preset threshold, stop adjusting, and obtain the cost factor of the completed calibration.

[0139] In order to better understand the calibration method of the cost factor of the above-mentioned call database, the following Figure 5 An example of a method for calibrating a cost factor of calling a database of the present application is described in detail. The method can be run on a database management system, which includes a query optimizer. The database management system can be PostgreSQL (an object-relational database management system) or MySQL (a relational database management system). There is no specific limitation in the embodiments of the present application. The method includes the following steps:

[0140] S501, obtaining a query statement set for a database.

[0141] S502: The query optimizer determines the execution plan of each query statement in the query statement set.

[0142] The execution plan includes at least one operator.

[0143] S503, the query optimizer executes each execution plan according to the explain analyze instruction, and obtains the execution data of each execution plan; wherein the execution data includes the actual execution time of the execution plan; and parses the execution plan according to each execution data to determine the target cardinality of each operator of each execution plan.

[0144] S504, using a hint instruction to add a limiting condition corresponding to the target cardinality to each query statement.

[0145] S505, the query optimizer executes the execution plan of each query statement after adding the limiting condition according to the explain analyze instruction; and obtains the weight of the initial cost factor of each operator in the execution process of each execution plan.

[0146] S506: Obtain an estimated execution time of the execution plan according to the initial cost factor of each operator and the weight of the initial cost factor.

[0147] S507, determining the average of the differences between the actual execution time and the estimated execution time corresponding to all execution plans; and constructing a loss function based on the average.

[0148] S508, adjusting the initial cost factor of each operator in each execution plan according to the loss function until the difference converges to obtain the cost factor.

[0149] The embodiment of the present application generates a corresponding execution plan for each query statement in a query statement set; then, when each execution plan is executed for the first time, the actual execution time of each execution plan and the actual number of rows of the data set in the database called by each operator, that is, the target cardinality, are determined; then, during the second execution of each execution plan, the execution time of the execution plan is estimated based on the target cardinality and the initial cost factor to obtain the estimated execution time; and then, according to the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator is adjusted until the above difference converges to obtain the calibrated cost factor of each operator. In the embodiment of the present application, after determining the target cardinality of each operator of each execution plan, the execution time of each execution plan is estimated based on the target cardinality and the initial cost factor, thereby eliminating the influence of the error in the cardinality estimation on the cost estimation of the execution plan and improving the correlation between the estimated execution time and the initial cost factor. Compared with the prior art, in which differential calculation is performed according to a calibration query to complete the calibration of the cost factor, the present application performs regression training based on the difference between the actual execution time and the estimated execution time of each execution plan, adjusts the initial cost factor, avoids the reliance on manual experience in the prior art, and effectively improves the accuracy and efficiency of the cost factor calibration.

[0150] The embodiment of the present application provides a calibration device for calling a cost factor of a database, such as Figure 6 As shown, the calibration device 60 for calling the cost factor of the database may include: an acquisition module 601, a determination module 602, an execution module 603, an estimation module 604 and an adjustment module 605;

[0151] Wherein, the acquisition module 601 is used to acquire a set of query statements for a database;

[0152] A determination module 602 is used to determine the execution plan of each query statement in the query statement set; wherein the execution plan includes at least one operator;

[0153] An execution module 603 is used to execute each execution plan, determine the target cardinality of each operator in each execution plan and the actual execution time of each execution plan; wherein the target cardinality represents the number of rows in the data set in the database called by each corresponding operator;

[0154] An estimation module 604, used to determine an estimated execution time of each execution plan according to the target cardinality and initial cost factor of each operator in each execution plan;

[0155] The adjustment module 605 is used to adjust the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges, thereby obtaining the cost factor of each operator in each execution plan; wherein the cost factor represents the unit cost of each corresponding operator.

[0156] In an embodiment of the present application, a possible implementation is provided. When determining the target cardinality of each operator in each execution plan and the actual execution time of each execution plan, the execution module 603 is used to:

[0157] After executing each execution plan, the execution data of each execution plan is obtained; wherein the execution data includes the actual execution time of the execution plan;

[0158] The execution plan is parsed according to each execution data, and the target cardinality of each operator of each execution plan is determined.

[0159] In an embodiment of the present application, a possible implementation is provided. When determining the estimated execution time of each execution plan, the estimation module 604 is used to:

[0160] Add limiting conditions corresponding to the target cardinality to each query statement;

[0161] Execute the execution plan of each query statement according to the limiting conditions;

[0162] Get the weight of the initial cost factor of each operator during the execution of each execution plan;

[0163] According to the initial cost factor of each operator and the weight of the initial cost factor, the estimated execution time of the execution plan is obtained.

[0164] In an embodiment of the present application, a possible implementation method is provided. The adjustment module 605 adjusts the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges and the cost factor of each operator in each execution plan is obtained, which is used to:

[0165] Determine the loss function based on the difference between the actual execution time and the estimated execution time of all execution plans;

[0166] The initial cost factor of each operator in each execution plan is adjusted according to the loss function until the difference converges and the cost factor is obtained.

[0167] A possible implementation method is provided in an embodiment of the present application. When the adjustment module 605 adjusts the initial cost factor of each operator in each execution plan according to the loss function until the difference converges and the cost factor is obtained, it is used to:

[0168] Calculate the change in the initial cost factor of each operator based on the loss function;

[0169] Adjust the initial cost factor of each operator according to the change;

[0170] Determine the loss value of the loss function based on the adjusted initial cost factor;

[0171] When the loss value is less than the preset threshold, the cost factor is obtained.

[0172] In an embodiment of the present application, a possible implementation method is provided. When determining the loss function according to the difference between the actual execution time and the estimated execution time of all execution plans, the adjustment module 605 is used to:

[0173] Determine the mean of the difference between the actual execution time and the estimated execution time for all execution plans;

[0174] Construct a loss function based on the mean.

[0175] The device of the embodiments of the present application can execute the method provided by the embodiments of the present application, and the implementation principles are similar. The actions performed by each module in the device of each embodiment of the present application correspond to the steps in the method of each embodiment of the present application. For the detailed functional description of each module of the device, please refer to the description in the corresponding method shown in the previous text, which will not be repeated here.

[0176] The embodiment of the present application generates a corresponding execution plan for each query statement in a query statement set; then, when each execution plan is executed for the first time, the actual execution time of each execution plan and the actual number of rows of the data set in the database called by each operator, that is, the target cardinality, are determined; then, during the second execution of each execution plan, the execution time of the execution plan is estimated based on the target cardinality and the initial cost factor to obtain the estimated execution time; and then, based on the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator is adjusted until the above difference converges to obtain the calibrated cost factor of each operator. In the embodiment of the present application, after determining the target cardinality of each operator of each execution plan, the execution time of each execution plan is estimated based on the target cardinality and the initial cost factor, thereby eliminating the influence of the error in the cardinality estimation on the cost estimation of the execution plan and improving the correlation between the estimated execution time and the initial cost factor. Compared with the prior art, in which differential calculation is performed according to a calibration query to complete the calibration of the cost factor, the present application performs regression training based on the difference between the actual execution time and the estimated execution time of each execution plan, adjusts the initial cost factor, avoids the reliance on manual experience in the prior art, and effectively improves the accuracy and efficiency of the cost factor calibration.

[0177] In an embodiment of the present application, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory, and the processor executes the above-mentioned computer program to implement the steps of a method for calibrating the cost factor of calling a database. Compared with the related art, the following can be achieved: the embodiment of the present application generates a corresponding execution plan for each query statement in a query statement set; then, when each execution plan is executed for the first time, the actual execution time of each execution plan and the actual number of rows of each operator calling a data set in the database, that is, the target cardinality, are determined; then, during the second execution of each execution plan, the execution time of the execution plan is estimated based on the target cardinality and the initial cost factor to obtain an estimated execution time; and then, according to the difference between the actual execution time and the estimated execution time of all execution plans, the initial cost factor of each operator is adjusted until the above-mentioned difference converges to obtain the calibrated cost factor of each operator. In the embodiment of the present application, after determining the target cardinality of each operator of each execution plan, the execution time of each execution plan is estimated based on the target cardinality and the initial cost factor, thereby eliminating the influence of the error in the cardinality estimation on the cost estimation of the execution plan and improving the correlation between the estimated execution time and the initial cost factor. Compared with the prior art, in which differential calculation is performed according to a calibration query to complete the calibration of the cost factor, the present application performs regression training based on the difference between the actual execution time and the estimated execution time of each execution plan, adjusts the initial cost factor, avoids the reliance on manual experience in the prior art, and effectively improves the accuracy and efficiency of the cost factor calibration.

[0178] In an alternative embodiment, an electronic device is provided, such as Figure 7 As shown, Figure 7 The electronic device 700 shown includes: a processor 701 and a memory 703. The processor 701 and the memory 703 are connected, such as through a bus 702. Optionally, the electronic device 700 may also include a transceiver 704, which may be used for data interaction between the electronic device and other electronic devices, such as data transmission and / or data reception. It should be noted that in actual applications, the transceiver 704 is not limited to one, and the structure of the electronic device 700 does not constitute a limitation on the embodiments of the present application.

[0179] Processor 701 may be a CPU (Central Processing Unit), a general purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array) or other programmable logic devices, transistor logic devices, hardware components or any combination thereof. It may implement or execute various exemplary logic blocks, modules and circuits described in conjunction with the disclosure of this application. Processor 701 may also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0180] The bus 702 may include a path to transmit information between the above components. The bus 702 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus. The bus 702 may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 7 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.

[0181] The memory 703 can be a ROM (Read Only Memory) or other types of static storage devices that can store static information and instructions, a RAM (Random Access Memory) or other types of dynamic storage devices that can store information and instructions, or an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory) or other optical disk storage, optical disk storage (including compressed optical disk, laser disk, optical disk, digital versatile disk, Blu-ray disk, etc.), magnetic disk storage medium, other magnetic storage devices, or any other medium that can be used to carry or store computer programs and can be read by a computer, without limitation herein.

[0182] The memory 703 is used to store the computer program for executing the embodiment of the present application, and the execution is controlled by the processor 701. The processor 701 is used to execute the computer program stored in the memory 703 to implement the steps shown in the above method embodiment.

[0183] The electronic devices include, but are not limited to, mobile terminals such as mobile phones, notebook computers, PADs, etc. and fixed terminals such as digital TVs, desktop computers, etc.

[0184] An embodiment of the present application provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps and corresponding contents of the aforementioned method embodiment can be implemented.

[0185] The embodiment of the present application provides a computer program product or a computer program, which includes computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, so that when the computer device executes the computer instructions, the following conditions are achieved:

[0186] Get a set of query statements for the database;

[0187] Determine the execution plan of each query statement in the query statement set respectively; wherein the execution plan includes at least one operator;

[0188] Execute each execution plan to determine the target cardinality of each operator in each execution plan and the actual execution time of each execution plan; the target cardinality represents the number of rows in the data set in the database called by each corresponding operator;

[0189] Determine the estimated execution time of each execution plan based on the target cardinality and initial cost factor of each operator in each execution plan;

[0190] According to the difference between the actual execution time and the estimated execution time of each execution plan, the initial cost factor of each operator in each execution plan is adjusted until the difference converges, and the cost factor of each operator in each execution plan is obtained; wherein the cost factor represents the unit cost of each corresponding operator.

[0191] The terms "first", "second", "third", "fourth", "1", "2", etc. (if any) in the specification and claims of this application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than that shown or described in the drawings.

[0192] It should be understood that, although each operation step is indicated by arrows in the flowchart of the embodiment of the present application, the implementation order of these steps is not limited to the order indicated by the arrows. Unless clearly stated herein, in some implementation scenarios of the embodiment of the present application, the implementation steps in each flowchart can be performed in other orders according to demand. In addition, some or all of the steps in each flowchart may include multiple sub-steps or multiple stages based on actual implementation scenarios. Some or all of these sub-steps or stages may be executed at the same time, and each sub-step or stage in these sub-steps or stages may also be executed at different times respectively. In different scenarios of execution time, the execution order of these sub-steps or stages may be flexibly configured according to demand, and the embodiment of the present application does not limit this.

[0193] The above is only an optional implementation method for some implementation scenarios of the present application. It should be pointed out that for ordinary technicians in this technical field, without departing from the technical concept of the solution of the present application, other similar implementation methods based on the technical ideas of the present application are also within the protection scope of the embodiments of the present application.

Claims

1. A method for calibrating a cost factor of a call database, characterized in that: include: Obtaining a query statement set for the database; Determining respectively the execution plan of each query statement in the query statement set; wherein the execution plan includes at least one operator; Execute each of the execution plans to determine the target cardinality of each operator in each of the execution plans and the actual execution time of each of the execution plans; wherein the target cardinality represents the number of rows of the data set in the database called by each of the corresponding operators; Determining an estimated execution time of each execution plan according to the target cardinality and initial cost factor of each operator in each execution plan; According to the difference between the actual execution time and the estimated execution time of each execution plan, the initial cost factor of each operator in each execution plan is adjusted until the difference converges, thereby obtaining the cost factor of each operator in each execution plan; wherein the cost factor represents the unit cost of each corresponding operator; The step of determining the estimated execution time of each execution plan specifically includes: Adding limiting conditions corresponding to the target cardinality to each of the query statements; Execute the execution plan of each query statement according to the limiting conditions; Obtaining the weight of the initial cost factor of each operator during the execution of each of the execution plans; The estimated execution time of the execution plan is obtained according to the initial cost factors of the operators and the weights of the initial cost factors.

2. The method according to claim 1, characterized in that Determining the target cardinality of each operator in each execution plan and the actual execution time of each execution plan includes: After executing each of the execution plans, acquiring execution data of each of the execution plans; wherein the execution data includes the actual execution time of the execution plan; The execution plan is parsed according to each of the execution data to determine the target cardinality of each operator of each of the execution plans.

3. The method according to claim 1, characterized in that: The step of adjusting the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan until the difference converges to obtain the cost factor of each operator in each execution plan includes: Determine the loss function based on the difference between the actual execution time and the estimated execution time of all execution plans; The initial cost factor of each operator in each of the execution plans is adjusted according to the loss function until the difference converges to obtain the cost factor.

4. The method according to claim 3, characterized in that The step of adjusting the initial cost factor of each operator in each execution plan according to the loss function until the difference converges to obtain the cost factor includes: Calculate the change of the initial cost factor of each operator according to the loss function; Adjusting the initial cost factor of each operator according to the change amount; Determine the loss value of the loss function based on the adjusted initial cost factor; When the loss value is less than a preset threshold, the cost factor is obtained.

5. The method according to claim 3, characterized in that: The loss function is determined based on the difference between the actual execution time and the estimated execution time of all execution plans, including: Determine the mean of the difference between the actual execution time and the estimated execution time for all execution plans; The loss function is constructed based on the mean.

6. A calibration device for calling a cost factor of a database, characterized in that: include: An acquisition module, used to acquire a set of query statements for the database; A determination module, used to respectively determine an execution plan for each query statement in the query statement set; wherein the execution plan includes at least one operator; An execution module, used to execute each of the execution plans, determine the target cardinality of each operator in each of the execution plans and the actual execution time of each of the execution plans; wherein the target cardinality represents the number of rows of the data set in the database called by each corresponding operator; An estimation module, used to determine the estimated execution time of each execution plan according to the target cardinality and initial cost factor of each operator in each execution plan; An adjustment module, used to adjust the initial cost factor of each operator in each execution plan according to the difference between the actual execution time and the estimated execution time of each execution plan, until the difference converges, to obtain the cost factor of each operator in each execution plan; wherein the cost factor represents the unit cost of each corresponding operator; Wherein, the estimation module is specifically used for: Adding limiting conditions corresponding to the target cardinality to each of the query statements; Execute the execution plan of each query statement according to the limiting conditions; Obtaining the weight of the initial cost factor of each operator during the execution of each of the execution plans; The estimated execution time of the execution plan is obtained according to the initial cost factors of the operators and the weights of the initial cost factors.

7. The device according to claim 6, characterized in that When determining the target cardinality of each operator in each execution plan and the actual execution time of each execution plan, the execution module is used to: After executing each of the execution plans, acquiring execution data of each of the execution plans; wherein the execution data includes the actual execution time of the execution plan; The execution plan is parsed according to each of the execution data to determine a target cardinality of each operator of each of the execution plans.

8. An electronic device comprising a memory, a processor and a computer program stored in the memory, characterized in that: The processor executes the computer program to implement the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images