WO2017140085A1 - 一种sql执行计划的确定方法及装置 - Google Patents
一种sql执行计划的确定方法及装置 Download PDFInfo
- Publication number
- WO2017140085A1 WO2017140085A1 PCT/CN2016/090222 CN2016090222W WO2017140085A1 WO 2017140085 A1 WO2017140085 A1 WO 2017140085A1 CN 2016090222 W CN2016090222 W CN 2016090222W WO 2017140085 A1 WO2017140085 A1 WO 2017140085A1
- Authority
- WO
- WIPO (PCT)
- Prior art keywords
- plan
- iteration
- tree
- plan tree
- iteration parameter
- Prior art date
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/235—Update request formulation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2246—Trees, e.g. B+trees
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
- G06F16/24544—Join order optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
- G06F16/24545—Selectivity estimation or determination
Definitions
- the present invention relates to the field of computer technologies, and in particular, to a method and an apparatus for determining a SQL execution plan.
- SQL Structured Query Language
- a SQL statement indicates the result that the user wants to obtain, for example, querying all customers living in Beijing, but the SQL statement does not tell the server how to execute, so when the server receives a Query command
- a Parse Tree is formed.
- the server searches for relevant statistical information in the database, and combines Parse Tree to determine the preferred SQL statement.
- SQL execution plan can be obtained in the form of a Plan Tree.
- Each node on a Plan Tree consists of several basic operations, such as traversing multiple tables, performing a nested connection or a hash connection. Basic operation, finally, the server executes the SQL execution plan according to the obtained Plan Tree, and the output result is the result represented by the SQL statement.
- the execution efficiency of the SQL statement depends to some extent on whether an efficient SQL execution plan can be determined.
- the statistical information in the database is usually a feature value obtained based on the sampled value. For example, the number of tuples in a relational table (RelTuples), the number of unique non-null values in a field in a relational table (Distinct value) and other eigenvalues, the eigenvalues obtained by these samples usually have some error, and have hysteresis in time. Therefore, the SQL execution plan determined by the server based on the above statistical information is usually not the optimal execution plan.
- Embodiments of the present invention provide a method and apparatus for determining a SQL execution plan, which can optimize the determination of the SQL execution plan and improve the execution efficiency of the SQL execution plan.
- a first aspect of the embodiments of the present invention provides a method for determining a structured query language SQL execution plan, where the SQL execution plan corresponds to at least one relationship table, and in the Nth iteration, the method includes: obtaining the N-1th iteration a first iteration parameter generated after executing the first plan tree on the at least one relationship table, wherein N is a natural number greater than 1; establishing a second plan tree according to the first iteration parameter; when the second plan tree and the first When the difference of the plan tree is not greater than the first threshold, the first plan tree or the second plan tree is determined as the SQL execution plan.
- the first threshold is 0, and correspondingly, when the difference between the second plan tree and the first plan tree is not greater than the first threshold, Determining the first plan tree or the second plan tree as the SQL execution plan, comprising: when the second plan tree is the same as the first plan tree, determining the first plan tree or the second plan tree as The SQL execution plan.
- the embodiment of the present invention obtains an optimized SQL execution plan by means of iterative execution.
- it is necessary to compare the plan tree established in the previous iteration process (collectively referred to as the first plan tree in the embodiment of the present invention) and the plan tree established in the current iterative process (in the embodiment of the present invention It is a second plan tree), and the second plan tree is established based on the updated iteration parameter (collectively referred to as the first iteration parameter in the embodiment of the present invention), that is, each time
- the second plan tree established in the iterative process is established based on the first iteration parameter updated after executing the first plan tree.
- the final plan tree is not dependent on the statistics in the database. Estimate the iteration parameters, but rely on the iteration parameters recorded during each actual execution of the plan tree in the iterative process. According to the establishment of the plan tree, thereby obtaining a more accurate SQL execution plan of the SQL statement, and improving the execution efficiency of the SQL execution plan.
- the first iteration parameter includes any one or more relationship tables corresponding to the SQL execution plan. The number of tuples.
- the first execution parameter is when the SQL execution plan corresponds to the at least two relationship tables The number of tuples in the result set obtained by the connection operation of any one or more of the at least two relation tables is further included.
- the method further includes: recording the SQL execution plan to determine the status.
- the method further includes: executing the second plan tree; and recording a second iteration parameter generated when the second plan tree is executed.
- the beneficial effect obtained by the embodiment of the present invention is that, since the updated first iteration parameter is obtained when the second plan tree is actually executed, the second plan tree established by using the updated first iteration parameter is more accurate.
- the second iteration parameter includes a tuple number of any one or more relationship tables corresponding to the SQL execution plan .
- the second iteration parameter when the SQL execution plan corresponds to the at least two relationship tables, the second iteration parameter further includes the at least The number of tuples in the result set obtained by any one or more of the two relational tables after the connection operation.
- the method further includes: when the second plan tree and the When the difference of the first plan tree is greater than the first threshold, updating the first iteration parameter according to the second iteration parameter; using the second plan tree in the Nth iteration as the first plan tree in the N+1th iteration .
- the method before the updating the first iteration parameter according to the second iteration parameter, the method further includes: when the second When the iteration parameter is the same as the first iteration parameter, a second plan tree different from the first plan tree is established according to the first iteration parameter.
- a tenth possible implementation manner of the first aspect after the second plan tree that is different from the first plan tree is established according to the first iteration parameter And further comprising: recording an execution time of executing the second plan tree; when the N is greater than the second threshold, using the second plan tree with the shortest execution time among all N iterations as the SQL execution plan.
- the second plan tree with the shortest execution time in the all N iterations is used as the SQL execution plan After that, it also includes: recording the SQL execution plan to determine the status.
- the method before the updating the first iteration parameter according to the second iteration parameter, the method further includes: performing the recording The execution time of the second plan tree; when the N is greater than the third threshold, the second plan tree with the shortest execution time among all N iterations is taken as the SQL execution plan.
- the second plan tree with the shortest execution time in the all N iterations is executed as the SQL After the plan, it also includes: recording the SQL execution plan to determine the status.
- the beneficial effect obtained by the embodiment of the present invention is that for those SQL execution plans that have mutated during the iteration process, it is possible to never iterate out the same plan tree as the last established plan tree, so in order to avoid unrestricted
- the infinite loop can be considered to be executed during the N iterations that have elapsed after the number of iterations N is greater than the threshold.
- the second plan tree with the shortest time is the optimal plan tree, and then the second plan tree is used as the SQL execution plan.
- the updating the first iteration parameter according to the second iteration parameter includes: The second iteration parameter and the first iteration parameter are taken as a union, and the result of the union is taken as the first iteration parameter.
- the method further includes: when the initial iteration is performed, according to the foregoing
- the stored estimated iteration parameter establishes an initial plan tree; the initial plan tree is executed to obtain an initial iteration parameter; and the estimated iteration parameter is updated according to the initial iteration parameter to obtain the first iteration parameter.
- the estimating iteration parameter includes a tuple of any one or more relationship tables corresponding to the SQL execution plan number.
- the initial iteration parameter includes any one or more corresponding to the SQL execution plan The number of tuples in the relational table.
- the initial execution parameter when the SQL execution plan corresponds to the at least two relational tables The number of tuples in the result set obtained by the connection operation of any one or more of the at least two relation tables is further included.
- the estimating the iteration parameter is updated according to the initial iteration parameter, to obtain the first
- the iterative parameter includes: combining the initial iteration parameter and the estimated iteration parameter, and using the result of the union as the first iteration parameter.
- the method before the initial iteration is performed, the method further includes: querying the SQL execution plan Whether the state is the determined state; when the state of the SQL execution plan is the determined state, the SQL execution plan is executed.
- the beneficial effects obtained by the embodiment of the present invention are that, after the server has stored the optimized SQL execution plan, when the execution is required to be performed again, the determination of the SQL execution plan does not need to be repeated, thereby improving the execution efficiency of the server.
- the SQL execution plan further includes a filtering condition, when the SQL
- the first iteration parameter includes the number of tuples in the one or more relationship tables corresponding to the SQL execution plan that meet the filtering condition.
- the first iteration parameter further includes: the SQL The number of unique non-null values in a field in any relation table corresponding to the execution plan, and/or the set of columns (MCV) in the relationship table that the number of occurrences exceeds the second threshold.
- the beneficial effect obtained by the embodiment of the present invention is that the second plan tree obtained in the embodiment of the present invention does not depend on the estimated iteration parameter in the database, but is based on the updated first iteration parameter in the iterative process. Established, and then when the second plan tree is the same as the first plan tree, the second plan tree is used as the SQL execution plan, which can accurately determine the SQL execution plan and improve the execution efficiency of the SQL execution plan.
- a second aspect of an embodiment of the present invention provides a determining apparatus for a structured query language SQL execution plan, the SQL execution plan corresponding to at least one relation table, the device comprising a memory and a processor coupled to the memory, at the Nth And the first iteration parameter generated after the execution of the first plan tree in the N-1th iteration, wherein N is a natural number greater than 1;
- An iterative parameter establishes a second plan tree; when the difference between the second plan tree and the first plan tree is not greater than a first threshold, determining the first plan tree or the second plan tree as the SQL execution plan.
- the first threshold is 0, and correspondingly, the processor is configured to use the second plan tree and the first plan When the tree is the same, the first plan tree or the second plan tree is determined as the SQL execution plan.
- the first iteration parameter includes any one or more relationship tables corresponding to the SQL execution plan The number of tuples.
- the first iteration parameter when the SQL execution plan corresponds to the at least two relationship tables, the first iteration parameter The number of tuples in the result set obtained by the connection operation of any one or more of the at least two relation tables is further included.
- the processor is also used to: record the SQL execution plan to determine the status.
- the processor is further configured to: execute the second plan tree; record a second iteration parameter generated when the second plan tree is executed.
- the second iteration parameter includes a tuple number of any one or more relationship tables corresponding to the SQL execution plan .
- the second execution parameter further includes the at least two relational tables when the SQL execution plan corresponds to the at least two relationship tables The number of tuples in the result set obtained by any one or more of the two relational tables after the connection operation.
- the processor is further configured to: when the second plan tree and the first plan tree When the difference is greater than the first threshold, the first iteration parameter is updated according to the second iteration parameter; the second plan tree in the Nth iteration is taken as the first plan tree in the N+1th iteration.
- the processor before updating the first iteration parameter according to the second iteration parameter, is further configured to: when the second iteration parameter is the same as the first iteration parameter, according to the first iteration parameter A second plan tree is created that is different from the first plan tree.
- the processor is further configured to: record execution time of executing the second plan tree; when the N is greater than the second threshold, use the second plan tree with the shortest execution time among all N iterations as the SQL execution plan.
- the processor is further configured to: record the SQL execution plan to determine the status.
- the processor is further configured to: before the updating the first iteration parameter according to the second iteration parameter Recording the execution time of executing the second plan tree; when the N is greater than the third threshold, the second plan tree having the shortest execution time among all N iterations is taken as the SQL execution plan.
- the second plan tree with the shortest execution time in the all N iterations is executed as the SQL After the plan, the processor is also used to: record the SQL execution plan to determine the status.
- the processor is specifically configured to: the second iteration parameter and the first The iterative parameters take the union, and the result of the union is taken as the first iteration parameter.
- the processor is further configured to: when the initial iteration is performed, Establishing an initial planning tree according to the pre-stored estimated iteration parameter; executing the initial planning tree to obtain an initial iteration parameter; updating the initial iteration parameter according to the initial iteration parameter The iteration parameters are estimated to obtain the first iteration parameter.
- the estimating iteration parameter includes a tuple of any one or more relationship tables corresponding to the SQL execution plan number.
- the initial iteration parameter includes any one or more corresponding to the SQL execution plan The number of tuples in the relational table.
- the initial iteration parameter when the SQL execution plan corresponds to the at least two relational tables, the initial iteration parameter The number of tuples in the result set obtained by the connection operation of any one or more of the at least two relation tables is further included.
- the processor is specifically configured to: the initial iteration parameter and the estimation iteration
- the parameter takes the union and uses the result of the union as the first iteration parameter.
- the processor before the initial iteration is executed, is further configured to: query the Whether the state of the SQL execution plan is the determined state; when the state of the SQL execution plan is the determined state, the SQL execution plan is executed.
- the SQL execution plan further includes a filtering condition, when the SQL
- the first iteration parameter includes the number of tuples in the one or more relationship tables corresponding to the SQL execution plan that meet the filtering condition.
- the first iteration parameter further includes: the SQL The number of unique non-null values in a field in any relation table corresponding to the execution plan, and/or the set of columns (MCV) in the relationship table that the number of occurrences exceeds the second threshold.
- a third aspect of the embodiments of the present invention provides a determining apparatus for a structured query language SQL execution plan, where the SQL execution plan corresponds to at least one relationship table, and the apparatus includes an obtaining unit, an establishing unit, and a determining unit, at the Nth time
- the acquiring unit is configured to acquire a first iteration parameter generated after executing the first plan tree on the at least one relationship table in the N-1th iteration, where N is a natural number greater than 1;
- Establishing a second plan tree according to the first iteration parameter; the determining unit is configured to: when the difference between the second plan tree and the first plan tree is not greater than a first threshold, the first plan tree or the second plan The tree is determined to execute the plan for the SQL.
- the first threshold is 0, and correspondingly, the determining unit is specifically configured to: when the second plan tree is the same as the first plan tree, The first plan tree or the second plan tree is determined as the SQL execution plan.
- the first iteration parameter includes any one or more relationship tables corresponding to the SQL execution plan. The number of tuples.
- the first iteration parameter when the SQL execution plan corresponds to the at least two relationship tables, the first iteration parameter The number of tuples in the result set obtained by the connection operation of any one or more of the at least two relation tables is further included.
- the device further includes a recording unit for recording the SQL execution plan to determine the status.
- the apparatus further includes an execution unit for executing the second plan tree; the record unit is further configured to record a second iteration parameter generated when the second plan tree is executed.
- the second iteration parameter includes the number of tuples of any one or more relationship tables corresponding to the SQL execution plan .
- the second iteration parameter when the SQL execution plan corresponds to the at least two relationship tables, the second iteration parameter further includes the at least The number of tuples in the result set obtained by any one or more of the two relational tables after the connection operation.
- the apparatus further includes an update unit, configured to: when the second plan tree and the first When the difference of the plan tree is greater than the first threshold, the first iteration parameter is updated according to the second iteration parameter; the second plan tree in the Nth iteration is used as the first plan tree in the N+1th iteration.
- the establishing unit before the updating the first iteration parameter according to the second iteration parameter, is further configured to: When the second iteration parameter is the same as the first iteration parameter, a second plan tree different from the first plan tree is established according to the first iteration parameter.
- the recording unit is further configured to record an execution time of executing the second plan tree; the determining unit is further configured to: when the N is greater than the second threshold, use the second plan tree with the shortest execution time among all N iterations as the SQL execution plan.
- the recording unit is further configured to: record the SQL execution plan to determine a state.
- the recording unit is further configured to: before the first iteration parameter is updated according to the second iteration parameter : recording the execution time of executing the second plan tree;
- the determining unit is further configured to use, as the SQL execution plan, the second plan tree with the shortest execution time among all N iterations when the N is greater than the third threshold.
- the second plan tree with the shortest execution time in the all N iterations is executed as the SQL After the plan, the recording unit is further used to: record the SQL execution plan to determine the status.
- the updating unit is specifically configured to: the second iteration parameter and the first The iterative parameters take the union, and the result of the union is taken as the first iteration parameter.
- the establishing unit is further configured to perform, when the initial iteration is performed, The pre-stored estimated iteration parameter establishes an initial plan tree; the execution unit is further configured to execute the initial plan tree to obtain an initial iteration parameter; the update unit is further configured to update the estimated iteration parameter according to the initial iteration parameter to obtain the first iteration parameter .
- the estimating iteration parameter includes a tuple of any one or more relationship tables corresponding to the SQL execution plan number.
- the initial iteration parameter includes any one or more corresponding to the SQL execution plan The number of tuples in the relational table.
- the initial execution parameter when the SQL execution plan corresponds to the at least two relational tables The number of tuples in the result set obtained by the connection operation of any one or more of the at least two relation tables is further included.
- the updating unit is specifically configured to: the initial iteration parameter and the estimation iteration The parameter is taken as a union, and the result of the union is taken as the first Iteration parameters.
- the apparatus before the initial iteration, further includes a query unit, where Querying whether the state of the SQL execution plan is the determined state; the determining unit is further configured to execute the SQL execution plan when the state of the SQL execution plan is the determined state.
- the SQL execution plan further includes a filtering condition, when the SQL
- the first iteration parameter includes the number of tuples in the one or more relationship tables corresponding to the SQL execution plan that meet the filtering condition.
- the first iteration parameter further includes: the SQL The number of unique non-null values in a field in any relation table corresponding to the execution plan, and/or the set of columns (MCV) in the relationship table that the number of occurrences exceeds the second threshold.
- a device for determining a Structured Query Language (SQL) execution plan comprising: a processor, a memory, a bus, and a communication interface; the memory is configured to store a computer execution instruction, the processor and the processor The memory is connected by the bus, and when the determining device is in operation, the processor executes the computer-executed instructions stored in the memory to cause the determining device to perform the first aspect of the first aspect of the first aspect of the present invention
- SQL Structured Query Language
- a computer storage medium for storing computer software instructions for determining a SQL execution plan described in the fourth aspect, comprising determining means for executing the SQL execution plan of the fourth aspect Designed program.
- the name of the determining device of the SQL execution plan is not limited to the device itself, and in actual implementation, these devices may appear under other names. As long as the functions of the respective devices are similar to the present invention, they fall within the scope of the claims of the present invention and their equivalents. within.
- FIG. 1 is a schematic flowchart of a method for determining a SQL execution plan according to an embodiment of the present invention
- FIG. 2 is a schematic flowchart of a method for determining another SQL execution plan according to an embodiment of the present invention
- FIG. 3 is a schematic flowchart of a method for determining another SQL execution plan according to an embodiment of the present invention
- FIG. 4 is a schematic structural diagram of an apparatus for determining an execution plan of an SQL according to an embodiment of the present invention
- FIG. 5 is a schematic structural diagram of another apparatus for determining an execution plan of an SQL according to an embodiment of the present disclosure
- FIG. 6 is a schematic structural diagram of another apparatus for determining a SQL execution plan according to an embodiment of the present invention.
- FIG. 7 is a schematic structural diagram of another apparatus for determining a SQL execution plan according to an embodiment of the present disclosure.
- FIG. 8 is a schematic structural diagram of a computer device according to an embodiment of the present invention.
- first and second are used for descriptive purposes only, and are not to be construed as indicating or implying a relative importance or implicitly indicating the number of technical features indicated. Thus, features defining “first” and “second” may include one explicitly or implicitly. Or more of this feature. In the description of the present invention, “a plurality” means two or more unless otherwise stated.
- a method for determining a SQL execution plan provided by an embodiment of the present invention may be specifically applied to a SQL execution plan determination stage.
- the core principle of the solution is to use an iterative method to establish an iterative parameter generated each time the plan tree is actually executed. Plan the tree for the optimal SQL execution plan.
- the plan tree is directly established according to the estimated iteration parameter pre-stored in the database (ie, the feature value obtained by using the sampling or estimation method), and The plan tree is used as the SQL execution plan, but the estimated iteration parameters may not be accurate, which leads to the inaccurate SQL execution plan.
- the iterative method may be used to cyclically establish and execute a plan tree, and the iteration parameters generated in the process of actually executing the plan tree are recorded to update.
- each iteration process it is necessary to compare the plan tree established in the previous iteration process (collectively referred to as the first plan tree in the embodiment of the present invention) and the plan tree established in the current iterative process (in the embodiment of the present invention It is a second plan tree), and the second plan tree is established based on the updated iteration parameter (collectively referred to as the first iteration parameter in the embodiment of the present invention), that is, each time
- the second plan tree established in the iterative process is established based on the first iteration parameter updated after executing the first plan tree.
- the final plan tree is not dependent on the statistics in the database. Estimating the iterative parameters, but based on the iterative parameters recorded during each actual execution of the plan tree in the iterative process, the plan tree is established, thereby obtaining a more accurate SQL execution plan and improving the execution efficiency of the SQL execution plan.
- the plan tree established at the N-1th iteration is the second plan tree, where N is a natural number greater than 1.
- the SQL execution plan corresponds to at least one relationship table in the database, and it is possible to set the SQL execution plan to correspond to M relationship tables, where M is a natural number greater than 1.
- the first iteration parameter may specifically include at least one of the first feature value and/or the second feature value, that is, the first feature value and the second feature value.
- the first feature value is used to indicate the number of tuples of any one or more relation tables corresponding to the SQL execution plan
- the second feature value is used to indicate that the SQL execution plan corresponds to the at least two relationship tables, the at least two relationships
- N (i) is used to represent the first feature value
- N (i, j, k) is used to represent the second feature value.
- N (i) is the number of tuples of the i-th relational table
- N (i, j, k) is a connection operation in the order of the i-th relational table, the j-th relational table, and the k-th relational table ( That is, the number of tuples in the result set after join.
- the server executing the SQL execution plan may further include determining a filtering condition of the SQL execution plan.
- the filtering condition is that all the boys in the relationship table are greater than 180 cm in height, and then, the first characteristic value may be further used. Indicates the number of tuples that match the filter condition in the i-th relationship table.
- the second iteration parameter involved in the embodiment of the present invention specifically includes the first feature value and/or the second feature value, except that the first iteration parameter refers to an iteration updated at the N-1th iteration.
- the parameter of the second iteration parameter refers to the iteration parameter generated when the second plan tree is executed at the Nth iteration. The manner of updating the first iteration parameter will be specifically described in the embodiment of the present invention.
- the feature value may further include: a number of unique non-null values in one field in the relation table (ie, Distinct value), and/or a set of columns in the relationship table that exceed the second threshold (ie, MCV, most common Value).
- the variation may be represented by a Variation in the embodiment of the present invention. Specifically, if the second plan tree established at the Nth iteration is the first established with the N-1th iteration, The plan tree is different, but the second iteration parameter generated when the second plan tree is executed is the same as the first iteration parameter generated when the first plan tree is executed, and the current SQL execution plan needs to be mutated to obtain further Better SQL execution plan.
- the embodiment of the present invention provides a method for determining a SQL execution plan. Since the second to last iterations are the same in the iterative process, the Nth iteration process is taken as an example. , the method includes:
- the Nth iteration Since the plan tree established during each iteration and the first iteration parameter generated at the end of the iterative process can be saved, in the Nth iteration, the N-1th iteration can be acquired.
- the second plan tree can be established according to the first iteration parameter obtained when the first iteration is completed.
- step 103 the second plan tree established in step 102 is executed, and the second iteration parameter generated when the second plan tree is executed is recorded.
- the second iteration parameter also includes N (i) and / or N (i, j, k) .
- step 104 compares whether the second plan tree established at the Nth iteration is the same as the first plan tree established at the N-1th iteration.
- step 104 further includes comparing whether the second plan tree established at the Nth iteration and the first plan tree established at the N-1th iteration are not greater than a threshold, and the threshold may be preset or trained.
- the fixed value can also be a dynamic value that is constantly updated in the system and is not limited.
- the execution steps of the second plan tree and the first plan tree can be compared Whether the difference step is not more than one step, or whether the difference step of the execution steps of the second plan tree and the first plan tree is not more than 99%, etc., is not limited.
- the subsequent steps involved in comparing whether the first plan tree and the second plan tree are the same or similar expressions are consistent with step 104, and are not described again.
- the threshold is not 0.
- the resulting SQL execution plan is a sub-optimal execution plan, but it should be understood that the sub-optimal execution plan determination process saves computing time and resources compared to the optimal execution plan determination process, in some implementations. For example, in an application scenario where computing time and resource requirements are high, a sub-optimal execution plan is adopted.
- the second iteration parameter is used to update the first iteration parameter.
- the first eigenvalue and/or the second eigenvalue that are different from the first iteration parameter may be added to the first iteration parameter, that is, the first iteration parameter and the second iteration parameter are taken together.
- the set obtains the updated first iteration parameter, so that in the N+1 iterations, the second iteration parameter is re-established using the updated first iteration parameter, since the updated first iteration parameter is the actual execution
- the second plan tree generated in the iterative process is used as the first plan tree in the next iteration process until the second plan tree is established in a certain iteration process and the first plan tree established in the last iteration process. The same.
- the number of iterations can be set to be incremented by one in order to repeatedly perform the above steps 101-106.
- the second plan tree established at the Nth iteration is the same as the first plan tree established at the N-1th iteration, the second plan tree is used as the SQL execution plan.
- step 107 when the second plan tree established at the Nth iteration is the same as the first plan tree established at the N-1th iteration, it is indicated that after the N iterations, the first iteration parameter is All the first eigenvalues and the second eigenvalues are already included. Therefore, when the second plan tree established according to the first iteration parameter has been optimized, the second plan tree can be used as the SQL execution plan.
- the correspondence between the second plan tree and the SQL statement related to executing the second plan tree may be saved, and when the SQL statement is received again, the server may directly obtain the first from the corresponding storage unit.
- step 107 further includes, if the second plan tree established at the Nth iteration and the first plan tree established at the N-1th iteration are not greater than a certain threshold, The second plan tree or the first plan tree is used as the SQL execution plan. It should be understood that in this embodiment, the first plan tree and the second plan tree are both sub-optimal plan trees, and one of them may be selected as the SQL execution plan.
- step 103, step 105, step 106 are optional steps.
- the execution time of the second plan tree may be recorded.
- the number of iterations exceeds a predetermined threshold, that is, the iterative algorithm does not converge within the threshold time, all the iteration cycles are selected.
- the second plan tree with the shortest execution time described above is used as the SQL execution plan.
- the state of the SQL execution plan is set to a determined state in the server, that is, the optimized SQL execution plan has been determined.
- the process of determining the SQL execution plan does not need to be repeated, which improves the execution efficiency of the server.
- the embodiment of the present invention provides a method for determining a SQL execution plan, as shown in FIG. 2, including:
- the SQL statement carries the identifier of the relational table corresponding to the SQL execution plan.
- the received SQL statement is: select count(*)from dba, that is, the row of the dba relational table is searched from the relational table identified as dba. number.
- the server may repeatedly execute the same SQL statement for a period of time, when the SQL execution plan related to the SQL statement is generated for the first time, the correspondence between the SQL execution plan and the SQL statement may be saved, so that When received again When a SQL statement is obtained, it can be queried whether a SQL execution plan corresponding to the SQL statement is stored, that is, whether the state of the SQL execution plan is a determined state.
- a variable Optimization can be set to indicate whether a SQL execution plan corresponding to the received SQL statement is stored.
- optimization ⁇ 1 that is, the state of the SQL execution plan is an indeterminate state, it means that the SQL execution plan corresponding to the received SQL statement is not stored, that is, the SQL statement is executed for the first time. Perform steps 202-213 below.
- an initial plan tree is established according to the pre-stored estimated iteration parameters.
- step 202 After the initialization operation in step 202 is completed, the first iteration process is started, at which time an initial plan tree can be established based on the estimated iteration parameters stored in the statistical information.
- the server may execute the initial plan tree established in step 203, and record the initial iteration parameters generated when the initial plan tree is executed, ie, N (i) and/or N (i) , j, k) , so that the estimated iteration parameters in step 203 can be updated using the initial iteration parameters generated when the initial plan tree is executed.
- the estimated iteration parameter for establishing the initial plan tree in step 203 specifically includes: N (t1) and N (t2) , and N (t2, t1) , and in step 204, the initial iteration parameter generated when the initial plan tree is executed. It may include N (t1) , N (t2) , and N (t1, t2) . In this case, different iteration parameters in the initial iteration parameter and the estimated iteration parameters may be updated into the estimation iteration parameter, that is, the updated estimation.
- the iterative parameters are: N (t1) , N (t2) , N (t1, t2), and N (t2, t1) , then the updated estimated iteration parameters are taken as the first iteration parameter above, so as to be in the subsequent N-1
- the second iteration tree is used in the iterative process to establish a second plan tree.
- the subsequent second to last iteration process can be referred to the following steps 205-213.
- the Nth iteration process is taken as an example for exemplary description.
- the first plan tree established at the N-1th iteration can be obtained during the Nth iteration.
- the second plan tree is established according to the updated first iteration parameter at the N-1th iteration.
- the first iteration parameter generated when the first plan tree is executed is different from the second iteration parameter generated when the second plan tree is executed, the first iteration parameter is updated by using the second iteration parameter.
- the first eigenvalue and/or the second eigenvalue that are different from the first iteration parameter may be added to the first iteration parameter, and the updated first iteration parameter, that is, the first iteration parameter, is obtained.
- the second iteration parameter is taken as a union, and the union result is taken as the updated first iteration parameter.
- the second plan tree is established according to the updated first iteration parameter at the N-1th iteration.
- the second plan tree is established according to the updated first iteration parameter (ie, the first iteration parameter obtained in step 204 above) at the first iteration.
- a second plan tree different from the first plan tree is established according to the updated first iteration parameter at the N-1th iteration.
- the second plan tree may be established according to the updated first iteration parameter at the N-1th iteration, but unlike the step 207, the second plan tree is established.
- the first plan tree established at the N-1th iteration is different.
- the server may establish a second plan tree according to the first iteration parameter, and the second plan tree at this time is the plan tree that the server considers to be optimal under the limitation of the first iteration parameter, and is not necessarily the actual optimal plan tree.
- the server may be prohibited from generating a second plan tree (ie, the same second plan tree as the first plan tree) that the server considers to be optimal, and generating another second plan tree different from the first plan tree. .
- step 209 the second plan tree established in step 207 or 208 is executed, and the second iteration parameter generated when the second plan tree is executed is recorded.
- the second iteration parameter also includes N (i) and / or N (i, j, k) .
- the first plan tree established in the first iteration that is, the first plan tree established in the first iteration is the initial plan tree established in step 203.
- step 212 If the second plan tree established during the Nth iteration is established with the N-1th iteration If the first plan tree is different, compare whether the first iteration parameter generated when the first plan tree is executed is the same as the second iteration parameter generated when the second plan tree is executed, that is, whether the SQL execution plan needs to be mutated, and proceed to step 206. The second plan tree established until the Nth iteration is the same as the first plan tree established at the N-1th iteration, and proceeds to step 212.
- the second plan tree established at the Nth iteration is the same as the first plan tree established at the N-1th iteration, the second plan tree is used as the SQL execution plan.
- step 214 when the second plan tree established at the Nth iteration is the same as the first plan tree established at the N-1th iteration, it is indicated that after the N iterations, the first iteration parameter is All of the first feature value and the second feature value are already included. Therefore, when the second plan tree established according to the first iteration parameter has been optimized, the second plan tree can be used as the SQL execution plan.
- the server can directly obtain the SQL execution plan from the corresponding storage unit.
- steps 301-303 can also be added, as an example, as shown in FIG. 3:
- the second plan tree with the shortest execution time in all N iterations is taken as the SQL execution plan.
- the execution time taken to execute the second plan tree may be recorded.
- step 302 is performed to determine whether the current number of iterations N is greater than a threshold and whether the SQL execution plan needs to be mutated.
- step 303 is executed, that is, the second execution time recorded in step 301 is the shortest.
- the plan tree as the SQL execution plan in step 201.
- the second plan tree with the shortest execution time is the optimal plan tree, and the second plan tree is used as the SQL execution plan.
- step 302 if it is determined in step 302 that the current number of iterations N is less than the threshold, and/or, the SQL execution plan does not need to be mutated, at this time, iteratively proceeds according to the above steps 210-212 until the number of iterations N is greater than the threshold. And the SQL execution plan does not need to be mutated.
- the execution time of the second plan tree is executed at each iteration, for example, the execution time of executing the second plan tree in the Nth iteration is T1, and the execution may be performed during the N-1th iteration.
- the execution time of the second plan tree is T2 and the size of T1. If T1 is less than T2, the T1 with a shorter execution time and the second plan tree corresponding to the shorter time T1 are saved, so that the saved second plan tree That is, the second plan tree with the shortest execution time in the N iterations process, without recording the execution time of the second plan tree at each iteration, and the corresponding second plan tree to save storage resources.
- An embodiment of the present invention provides a method for determining a SQL execution plan.
- the first iteration parameter includes a first feature value and/or a second feature value
- the first feature value is used to indicate the number of tuples of the i-th relationship table
- the second feature value is used to indicate that at least two relationship tables are connected after the operation
- the obtained result sets the number of tuples; then, the second iteration parameter is used to establish a second plan tree; when the second plan tree is the same as the first plan tree, the second plan tree is used as the SQL execution plan.
- the second plan tree finally obtained in the solution is not dependent on the estimated iteration parameters in the database, but is based on the updated first iteration parameter in the iterative process, and then the second plan tree and the first When the plan tree is the same, the second plan tree is executed as SQL
- the plan can more accurately determine the SQL execution plan and improve the execution efficiency of the SQL execution plan.
- FIG. 4 is a schematic structural diagram of an apparatus for determining a SQL execution plan according to an embodiment of the present invention.
- the apparatus for determining a SQL execution plan according to an embodiment of the present invention may be used to implement the implementations of the present invention shown in FIG. 1 to FIG.
- FIG. 4 For the convenience of the description, only the parts related to the embodiment of the present invention are shown for the convenience of the description. The specific technical solutions are not described in detail with reference to the embodiments of the present invention shown in FIG.
- the determining device of the SQL execution plan includes: an obtaining unit 01, an establishing unit 02, and a determining unit 03.
- the device is used in the Nth iteration,
- the obtaining unit 01 is configured to acquire a first iteration parameter generated after executing the first plan tree on the at least one relationship table in the N-1th iteration, where N is a natural number greater than 1.
- the establishing unit 02 is configured to establish a second plan tree according to the first iteration parameter
- a determining unit 03 configured to determine the first plan tree or the second plan tree as the SQL execution plan when a difference between the second plan tree and the first plan tree is not greater than a first threshold .
- the first threshold is 0, correspondingly, when the difference between the second plan tree and the first plan tree is not greater than a first threshold, the first plan tree or the Determining, by the second plan tree, the execution plan of the SQL, comprising: determining, when the second plan tree is the same as the first plan tree, the first plan tree or the second plan tree as the SQL Implementation plan.
- the first iteration parameter includes a tuple number of any one or more relationship tables corresponding to the SQL execution plan.
- the first iteration parameter when the SQL execution plan corresponds to at least two relationship tables, the first iteration parameter further includes a result obtained by the connection operation of any one or more of the at least two relationship tables The number of tuples in the collection.
- the device further includes:
- An execution unit 04 configured to execute the second plan tree
- a recording unit 05 configured to record a second iteration generated when the second plan tree is executed parameter.
- the recording unit 05 is further configured to record the SQL execution plan to determine a state.
- the second iteration parameter includes a tuple number of any one or more relation tables corresponding to the SQL execution plan.
- the second iteration parameter when the SQL execution plan corresponds to at least two relationship tables, the second iteration parameter further includes a result obtained by the connection operation of any one or more of the at least two relationship tables The number of tuples in the collection.
- the device further includes:
- the updating unit 06 is configured to: when the difference between the second plan tree and the first plan tree is greater than a first threshold, update the first iteration parameter according to the second iteration parameter; and the Nth iteration
- the second plan tree in the middle is the first plan tree in the N+1th iteration.
- the establishing unit 02 is configured to: when the second iteration parameter is the same as the first iteration parameter, establish a second plan tree that is different from the first plan tree according to the first iteration parameter.
- the recording unit 05 is further configured to record an execution time when the second plan tree is executed.
- the determining unit 03 is further configured to use, as the SQL execution plan, the second plan tree whose execution time is the shortest among all N iterations when the number of iterations N is greater than the second threshold.
- the determining unit 03 is further configured to use, as the SQL execution plan, the second plan tree whose execution time is the shortest among all N iterations when the number of iterations N is greater than the third threshold.
- the recording unit 05 is further configured to record the SQL execution plan to determine a state.
- the establishing unit 02 is further configured to establish an initial planning tree according to the pre-stored estimated iteration parameter when the initial iteration is performed;
- the execution unit 04 is further configured to execute the initial plan tree, and obtain the execution of the The initial iteration parameters that are generated when the tree is initially planned;
- the updating unit 05 is further configured to update the estimated iteration parameter by using the initial iteration parameter to obtain the first iteration parameter.
- the apparatus further includes a query unit 08, where
- the query unit 07 is configured to query whether the state of the SQL execution plan is the determined state
- the determining unit 03 is further configured to execute the SQL execution plan when the state of the SQL execution plan is the determined state.
- the updating unit 06 is specifically configured to combine the second iteration parameter and the first iteration parameter, and use the result of the union as the first iteration parameter.
- the determining means of the SQL execution plan shown in FIGS. 4-7 can be implemented in the manner of the computer device (or system) in FIG.
- FIG. 8 is a schematic diagram of a computer device 100 according to an embodiment of the present invention.
- the computer device 100 comprises at least one processor 11, a communication bus 12, a memory 13 and at least one communication interface 14.
- the memory 13 is used to store computer execution instructions
- the processor 11 is connected to the memory 13 via the bus 12, and when the determining means of the SQL execution plan is executed, the processor 11 executes the computer execution instructions stored in the memory 13 to cause the SQL execution plan determining means
- the method of determining the SQL execution plan of any of FIGS. 1 to 3 is performed.
- the specific functions of the foregoing obtaining unit 01, the establishing unit 02, the determining unit 03, the executing unit 04, the recording unit 05, the updating unit 06, and the query unit 07 can all be called by the processor 11 in the computer device 100 to call the computer in the memory 13. Instruction implementation.
- the processor 11 can be a general purpose central processing unit (CPU), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits for controlling the execution of the program of the present invention.
- CPU central processing unit
- ASIC application-specific integrated circuit
- Communication bus 12 can include a path for communicating information between the components described above.
- the communication interface 14 uses a device such as any transceiver for use with other devices or communication networks Network communication, such as Ethernet, Radio Access Network (RAN), Wireless Local Area Networks (WLAN), etc.
- Network communication such as Ethernet, Radio Access Network (RAN), Wireless Local Area Networks (WLAN), etc.
- the memory 13 can be a read-only memory (ROM) or other type of static storage device that can store static information and instructions, a random access memory (RAM) or other type of information and instructions that can be stored.
- the dynamic storage device may also be an Electrically Erasable Programmable Read-Only Memory (EEPROM), a Compact Disc Read-Only Memory (CD-ROM) or other optical disc storage, or a disc storage device ( Including compressed optical discs, laser discs, optical discs, digital versatile discs, Blu-ray discs, etc.), magnetic disk storage media or other magnetic storage devices, or can be used to carry or store desired program code in the form of instructions or data structures and can be stored by a computer Any other media taken, but not limited to this.
- the memory 13 can be independently present and connected to the processor via a bus.
- the memory 13 can also be integrated with the processor.
- the memory 13 is used to store application code for executing the solution of the present invention, and is controlled by the processor 11.
- the processor 11 is configured to execute application code stored in the memory 13.
- processor 11 may include one or more CPUs, such as CPU0 and CPU1 in FIG.
- computer device 100 can include multiple processors, such as processor 11 and processor 18 in FIG. Each of these processors can be a single-CPU processor or a multi-core processor.
- a processor herein may refer to one or more devices, circuits, and/or processing cores for processing data, such as computer program instructions.
- computer device 100 may also include output device 15 and input device 16.
- the output device 15 is in communication with the processor 11 and can display information in a variety of ways.
- the output device 15 may be a liquid crystal display (LCD), a light emitting diode (LED) display device, a cathode ray tube (CRT) display device, or a projector. Wait.
- the input device 16 communicates with the processor 11, The user's input can be accepted in a variety of ways.
- input device 16 can be a mouse, keyboard, touch screen device, or sensing device, and the like.
- the computer device 100 described above may be a general purpose computer device or a special purpose computer device.
- the computer device 100 can be a desktop computer, a portable computer, a network server, a personal digital assistant (PDA), a mobile phone, a tablet computer, a wireless terminal device, a communication device, an embedded device, or have FIG. A device of similar structure.
- PDA personal digital assistant
- Embodiments of the invention do not limit the type of computer device 100.
- An embodiment of the present invention provides a method for determining a SQL execution plan.
- the first iteration parameter updated after the N-1th iteration is obtained.
- the first The iterative parameter includes a first feature value and/or a second feature value
- the first feature value is used to indicate the number of tuples of the i-th relationship table
- the second feature value is used to indicate that at least two relationship tables are obtained after the connection operation.
- the number of tuples in the result set; then, the second iteration parameter is used to establish a second plan tree; when the second plan tree is the same as the first plan tree, the second plan tree is used as the SQL execution plan.
- the second plan tree finally obtained in the solution is not dependent on the estimated iteration parameters in the database, but is based on the updated first iteration parameter in the iterative process, and then the second plan tree and the first When the plan tree is the same, the second plan tree is used as the SQL execution plan, which can accurately determine the SQL execution plan and improve the execution efficiency of the SQL execution plan.
- the disclosed system, apparatus, and method may be implemented in other manners.
- the device embodiments described above are merely illustrative.
- the division of the modules or units is only a logical function division.
- there may be another division manner for example, multiple singles.
- Meta or components may be combined or integrated into another system, or some features may be omitted or not executed.
- the mutual coupling or direct coupling or communication connection shown or discussed may be an indirect coupling or communication connection through some interface, device or unit, and may be in an electrical, mechanical or other form.
- the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, may be located in one place, or may be distributed to multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of the embodiment.
- each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.
- the above integrated unit can be implemented in the form of hardware or in the form of a software functional unit.
- the integrated unit if implemented in the form of a software functional unit and sold or used as a standalone product, may be stored in a computer readable storage medium.
- the technical solution of the present invention which is essential or contributes to the prior art, or all or part of the technical solution, may be embodied in the form of a software product stored in a storage medium.
- a number of instructions are included to cause a computer device (which may be a personal computer, server, or network device, etc.) or a processor to perform all or part of the steps of the methods described in various embodiments of the present invention.
- the foregoing storage medium includes: a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, and the like. .
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Operations Research (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Description
Claims (43)
- 一种结构化查询语言SQL执行计划的确定方法,所述SQL执行计划对应至少一个关系表,其特征在于,在第N次迭代中,包括:获取在第N-1次迭代中对所述至少一个关系表执行第一计划树后产生的第一迭代参数,其中N为大于1的自然数;根据所述第一迭代参数建立第二计划树;当所述第二计划树与所述第一计划树的差异不大于第一阈值时,将所述第一计划树或所述第二计划树确定为所述SQL执行计划。
- 根据权利要求1所述的方法,其特征在于,所述第一阈值为0,对应的,所述当所述第二计划树与所述第一计划树的差异不大于第一阈值时,将所述第一计划树或所述第二计划树确定为所述SQL执行计划,包括:当所述第二计划树与所述第一计划树相同时,将所述第一计划树或所述第二计划树确定为所述SQL执行计划。
- 根据权利要求1或2所述的方法,其特征在于,所述第一迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求1-3任一项所述的方法,其特征在于,所述SQL执行计划对应至少两个关系表时,所述第一迭代参数还包括所述至少两个关系表中任意一个或多个至少两个关系表经过连接操作后得到的结果集合中的元组数。
- 根据权利要求1-4任一项所述的方法,其特征在于,在所述将所述第一计划树或所述第二计划树确定为所述SQL执行计划之后,还包括:记录所述SQL执行计划为确定状态。
- 根据权利要求1-5任一项所述的方法,其特征在于,在所述根据所述第一迭代参数建立第二计划树之后,还包括:执行所述第二计划树;记录执行所述第二计划树时产生的第二迭代参数。
- 根据权利要求6所述的方法,其特征在于,所述第二迭代参 数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求6或7所述的方法,其特征在于,所述SQL执行计划对应至少两个关系表时,所述第二迭代参数还包括所述至少两个关系表中任意一个或多个至少两个关系表经过连接操作后得到的结果集合中的元组数。
- 根据权利要求6-8任一项所述的方法,其特征在于,所述方法还包括:当所述第二计划树与所述第一计划树的差异大于第一阈值时,根据所述第二迭代参数更新所述第一迭代参数;将所述第N次迭代中的第二计划树作为第N+1次迭代中的第一计划树。
- 根据权利要求9所述的方法,其特征在于,在所述根据所述第二迭代参数更新所述第一迭代参数之前,还包括:当所述第二迭代参数与所述第一迭代参数相同时,根据所述第一迭代参数建立与所述第一计划树不相同的第二计划树。
- 根据权利要求10所述的方法,其特征在于,在所述根据所述第一迭代参数建立与所述第一计划树不相同的第二计划树之后,还包括:记录执行所述第二计划树的执行时间;当所述N大于第二阈值时,将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划。
- 根据权利要求11所述的方法,其特征在于,在所述将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划之后,还包括:记录所述SQL执行计划为确定状态。
- 根据权利要求9所述的方法,其特征在于,在所述根据所述第二迭代参数更新所述第一迭代参数之前,还包括:记录执行所述第二计划树的执行时间;当所述N大于第三阈值时,将全部N次迭代中所述执行时间最短 的第二计划树作为所述SQL执行计划。
- 根据权利要求13所述的方法,其特征在于,在所述将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划之后,还包括:记录所述SQL执行计划为确定状态。
- 根据权利要求8-14任一项所述的方法,其特征在于,所述根据所述第二迭代参数更新所述第一迭代参数,包括:将所述第二迭代参数和所述第一迭代参数取并集,将所述并集的结果作为所述第一迭代参数。
- 根据权利要求1-15中任一项所述的方法,其特征在于,所述方法还包括:在初始迭代执行时,根据预先存储的估算迭代参数建立初始计划树;执行所述初始计划树得到初始迭代参数;根据所述初始迭代参数更新所述估算迭代参数,得到所述第一迭代参数。
- 根据权利要求16所述的方法,其特征在于,所述估算迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求16或17所述的方法,其特征在于,所述初始迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求16-18任一项所述的方法,其特征在于,所述SQL执行计划对应至少两个关系表时,所述初始迭代参数还包括所述至少两个关系表中任意一个或多个至少两个关系表经过连接操作后得到的结果集合中的元组数。
- 根据权利要求16-19任一项所述的方法,其特征在于,所述根据所述初始迭代参数更新所述估算迭代参数,得到所述第一迭代参数,包括:将所述初始迭代参数和所述估算迭代参数取并集,将所述并集的 结果作为所述第一迭代参数。
- 根据权利要求16-20任一项所述的方法,其特征在于,在所述初始迭代执行之前,还包括:查询所述SQL执行计划的状态是否为所述确定状态;当所述SQL执行计划的状态为所述确定状态时,执行所述SQL执行计划。
- 一种结构化查询语言SQL执行计划的确定装置,所述SQL执行计划对应至少一个关系表,其特征在于,所述装置包括:存储器和耦合于存储器的处理器,在第N次迭代中:所述处理器用于获取在第N-1次迭代中对所述至少一个关系表执行第一计划树后产生的第一迭代参数,其中N为大于1的自然数;根据所述第一迭代参数建立第二计划树;当所述第二计划树与所述第一计划树的差异不大于第一阈值时,将所述第一计划树或所述第二计划树确定为所述SQL执行计划。
- 根据权利要求22所述的装置,其特征在于,所述第一阈值为0,对应的,所述当所述第二计划树与所述第一计划树的差异不大于第一阈值时,将所述第一计划树或所述第二计划树确定为所述SQL执行计划,包括,当所述第二计划树与所述第一计划树相同时,将所述第一计划树或所述第二计划树确定为所述SQL执行计划。
- 根据权利要求22或23所述的装置,其特征在于,所述第一迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求22-24任一项所述的装置,其特征在于,所述SQL执行计划对应至少两个关系表时,所述第一迭代参数还包括所述至少两个关系表中任意一个或多个至少两个关系表经过连接操作后得到的结果集合中的元组数。
- 根据权利要求22-25任一项所述的装置,其特征在于,在所述将所述第一计划树或所述第二计划树确定为所述SQL执行计划之后,所述处理器还用于:记录所述SQL执行计划为确定状态。
- 根据权利要求22-26任一项所述的装置,其特征在于,在所述根据所述第一迭代参数建立第二计划树之后,所述处理器还用于:执行所述第二计划树;记录执行所述第二计划树时产生的第二迭代参数。
- 根据权利要求27所述的装置,其特征在于,所述第二迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求27或28所述的装置,其特征在于,所述SQL执行计划对应至少两个关系表时,所述第二迭代参数还包括所述至少两个关系表中任意一个或多个至少两个关系表经过连接操作后得到的结果集合中的元组数。
- 根据权利要求27-29任一项所述的装置,其特征在于,所述处理器还用于:当所述第二计划树与所述第一计划树的差异大于第一阈值时,根据所述第二迭代参数更新所述第一迭代参数;将所述第N次迭代中的第二计划树作为第N+1次迭代中的第一计划树。
- 根据权利要求30所述的装置,其特征在于,在所述根据所述第二迭代参数更新所述第一迭代参数之前,所述处理器还用于:当所述第二迭代参数与所述第一迭代参数相同时,根据所述第一迭代参数建立与所述第一计划树不相同的第二计划树。
- 根据权利要求31所述的装置,其特征在于,在所述根据所述第一迭代参数建立与所述第一计划树不相同的第二计划树之后,所述处理器还用于:记录执行所述第二计划树的执行时间;当所述N大于第二阈值时,将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划。
- 根据权利要求32所述的装置,其特征在于,在所述将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划之 后,所述处理器还用于:记录所述SQL执行计划为确定状态。
- 根据权利要求30所述的装置,其特征在于,在所述根据所述第二迭代参数更新所述第一迭代参数之前,所述处理器还用于:记录执行所述第二计划树的执行时间;当所述N大于第三阈值时,将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划。
- 根据权利要求34所述的装置,其特征在于,在所述将全部N次迭代中所述执行时间最短的第二计划树作为所述SQL执行计划之后,所述处理器还用于:记录所述SQL执行计划为确定状态。
- 根据权利要求29-35任一项所述的装置,其特征在于,所述处理器具体用于:将所述第二迭代参数和所述第一迭代参数取并集,将所述并集的结果作为所述第一迭代参数。
- 根据权利要求22-36中任一项所述的装置,其特征在于,所述处理器还用于:在初始迭代执行时,根据预先存储的估算迭代参数建立初始计划树;执行所述初始计划树得到初始迭代参数;根据所述初始迭代参数更新所述估算迭代参数,得到所述第一迭代参数。
- 根据权利要求37所述的装置,其特征在于,所述估算迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求37或38所述的装置,其特征在于,所述初始迭代参数包括所述SQL执行计划对应的任意一个或多个关系表的元组数。
- 根据权利要求37-39任一项所述的装置,其特征在于,所述SQL执行计划对应至少两个关系表时,所述初始迭代参数还包括所述 至少两个关系表中任意一个或多个至少两个关系表经过连接操作后得到的结果集合中的元组数。
- 根据权利要求37-40任一项所述的装置,其特征在于,所述处理器具体用于:将所述初始迭代参数和所述估算迭代参数取并集,将所述并集的结果作为所述第一迭代参数。
- 根据权利要求37-41任一项所述的装置,其特征在于,在所述初始迭代执行之前,所述处理器还用于:查询所述SQL执行计划的状态是否为所述确定状态;当所述SQL执行计划的状态为所述确定状态时,执行所述SQL执行计划。
- 一种结构化查询语言SQL执行计划的确定装置,其特征在于,包括:处理器、存储器、总线和通信接口;所述存储器用于存储计算机执行指令,所述处理器与所述存储器通过所述总线连接,当所述确定装置运行时,所述处理器执行所述存储器存储的所述计算机执行指令,以使所述确定装置执行如权利要求1-21中任意一项所述SQL执行计划的确定方法。
Priority Applications (4)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
JP2017518519A JP6415708B2 (ja) | 2016-02-19 | 2016-07-15 | Sql実行計画を決定するための方法および装置 |
EP16840285.7A EP3232339B1 (en) | 2016-02-19 | 2016-07-15 | Method and device for determining sql execution plan |
RU2017113685A RU2674886C2 (ru) | 2016-02-19 | 2016-07-15 | Способ и устройство для определения плана исполнения sql |
US15/495,569 US10901976B2 (en) | 2016-02-19 | 2017-04-24 | Method and apparatus for determining SQL execution plan |
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN201610095091.0 | 2016-02-19 | ||
CN201610095091.0A CN107102995B (zh) | 2016-02-19 | 2016-02-19 | 一种sql执行计划的确定方法及装置 |
Related Child Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US15/495,569 Continuation US10901976B2 (en) | 2016-02-19 | 2017-04-24 | Method and apparatus for determining SQL execution plan |
Publications (1)
Publication Number | Publication Date |
---|---|
WO2017140085A1 true WO2017140085A1 (zh) | 2017-08-24 |
Family
ID=59625589
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
PCT/CN2016/090222 WO2017140085A1 (zh) | 2016-02-19 | 2016-07-15 | 一种sql执行计划的确定方法及装置 |
Country Status (6)
Country | Link |
---|---|
US (1) | US10901976B2 (zh) |
EP (1) | EP3232339B1 (zh) |
JP (1) | JP6415708B2 (zh) |
CN (1) | CN107102995B (zh) |
RU (1) | RU2674886C2 (zh) |
WO (1) | WO2017140085A1 (zh) |
Families Citing this family (5)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US10558668B2 (en) | 2016-07-01 | 2020-02-11 | International Business Machines Corporation | Result set output criteria |
CN108197187B (zh) * | 2017-12-26 | 2020-06-16 | 金蝶软件(中国)有限公司 | 查询语句的优化方法、装置、存储介质和计算机设备 |
CN108733789B (zh) * | 2018-05-11 | 2021-11-19 | 北京奥星贝斯科技有限公司 | 数据库操作指令的执行计划演进方法、装置以及设备 |
CN109876445B (zh) * | 2019-01-11 | 2022-08-09 | 珠海金山网络游戏科技有限公司 | 一种基于行为树的高解耦引导方法及系统 |
CN113849520B (zh) * | 2021-09-30 | 2024-05-28 | 平安科技(深圳)有限公司 | 异常sql的智能识别方法、装置、电子设备及存储介质 |
Citations (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20060212429A1 (en) * | 2005-03-17 | 2006-09-21 | Microsoft Corporation | Answering top-K selection queries in a relational engine |
CN101576880A (zh) * | 2008-05-06 | 2009-11-11 | 山东省标准化研究院 | 基于极值优化的数据库查询优化方法 |
CN103793467A (zh) * | 2013-09-10 | 2014-05-14 | 浙江鸿程计算机系统有限公司 | 一种基于超图和动态规划的大数据实时查询优化方法 |
Family Cites Families (54)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
JPH05334368A (ja) * | 1992-06-02 | 1993-12-17 | Hitachi Ltd | データベース問合せ処理方法 |
US5671403A (en) * | 1994-12-30 | 1997-09-23 | International Business Machines Corporation | Iterative dynamic programming system for query optimization with bounded complexity |
US5608904A (en) | 1995-02-13 | 1997-03-04 | Hewlett-Packard Company | Method and apparatus for processing and optimizing queries having joins between structured data and text data |
US6487547B1 (en) * | 1999-01-29 | 2002-11-26 | Oracle Corporation | Database appliance comprising hardware and software bundle configured for specific database applications |
JP2001142898A (ja) * | 1999-11-16 | 2001-05-25 | Hitachi Ltd | 問合せ処理の実行可否判定方法 |
US7398221B1 (en) * | 2001-03-30 | 2008-07-08 | Rapt, Inc. | Method and apparatus for component plan analysis under uncertainty |
US7107262B2 (en) * | 2003-02-20 | 2006-09-12 | International Business Machines Corporation | Incremental data query performance feedback model |
JP2005018430A (ja) * | 2003-06-26 | 2005-01-20 | Ntt Data Corp | データベース管理システム及び問い合わせ最適化方法 |
US8825629B2 (en) * | 2003-09-06 | 2014-09-02 | Oracle International Corporation | Method for index tuning of a SQL statement, and index merging for a multi-statement SQL workload, using a cost-based relational query optimizer |
US7353219B2 (en) * | 2004-05-28 | 2008-04-01 | International Business Machines Corporation | Determining validity ranges of query plans based on suboptimality |
US7831592B2 (en) * | 2004-10-29 | 2010-11-09 | International Business Machines Corporation | System and method for updating database statistics according to query feedback |
US8161038B2 (en) * | 2004-10-29 | 2012-04-17 | International Business Machines Corporation | Maintain optimal query performance by presenting differences between access plans |
US7610264B2 (en) * | 2005-02-28 | 2009-10-27 | International Business Machines Corporation | Method and system for providing a learning optimizer for federated database systems |
RU2409848C2 (ru) * | 2005-10-28 | 2011-01-20 | Медиарайф Местль Унд Райф Коммуникационс-Унд Информационстехнологиен Оег | Способ управления системой реляционной базы данных |
US7877381B2 (en) * | 2006-03-24 | 2011-01-25 | International Business Machines Corporation | Progressive refinement of a federated query plan during query execution |
JP2007293723A (ja) * | 2006-04-26 | 2007-11-08 | Hitachi Information Systems Ltd | データベース管理システム及び管理方法 |
US7877373B2 (en) * | 2006-06-30 | 2011-01-25 | Oracle International Corporation | Executing alternative plans for a SQL statement |
US7739269B2 (en) * | 2007-01-19 | 2010-06-15 | Microsoft Corporation | Incremental repair of query plans |
US20080201295A1 (en) * | 2007-02-21 | 2008-08-21 | Mylavarapu Praveena | Caching plans with using data values |
DE602007002474D1 (de) * | 2007-04-27 | 2009-10-29 | Software Ag | Verfahren und Datenbanksystem zur Durchführung einer XML-Datenbankabfrage |
US7941425B2 (en) * | 2007-07-25 | 2011-05-10 | Teradata Us, Inc. | Techniques for scoring and comparing query execution plans |
US9189522B2 (en) * | 2007-10-17 | 2015-11-17 | Oracle International Corporation | SQL execution plan baselines |
US10983998B2 (en) * | 2008-06-25 | 2021-04-20 | Microsoft Technology Licensing, Llc | Query execution plans by compilation-time execution |
US8775413B2 (en) * | 2008-06-30 | 2014-07-08 | Teradata Us, Inc. | Parallel, in-line, query capture database for real-time logging, monitoring and optimizer feedback |
US7974213B2 (en) * | 2008-11-21 | 2011-07-05 | At&T Intellectual Property I, L.P. | Methods and apparatus to select composite link cost-out thresholds |
US8311863B1 (en) * | 2009-02-24 | 2012-11-13 | Accenture Global Services Limited | Utility high performance capability assessment |
US8185519B2 (en) * | 2009-03-14 | 2012-05-22 | Microsoft Corporation | Techniques for exact cardinality query optimization |
US8380699B2 (en) * | 2009-09-04 | 2013-02-19 | Hewlett-Packard Development Company, L.P. | System and method for optimizing queries |
CN102053961A (zh) * | 2009-10-27 | 2011-05-11 | 中兴通讯股份有限公司 | Sql语句的检验方法、装置及提高数据库可靠性的系统 |
US20110161310A1 (en) * | 2009-12-30 | 2011-06-30 | Wei Tang | Database query plan analysis and difference processing |
CN102262636B (zh) * | 2010-05-25 | 2012-10-10 | 中国移动通信集团浙江有限公司 | 生成数据库分区执行计划的方法及装置 |
US8898146B2 (en) * | 2010-09-22 | 2014-11-25 | Hewlett-Packard Development Company, L.P. | System and method for comparing database query plans |
WO2013049715A1 (en) * | 2011-09-29 | 2013-04-04 | Cirro, Inc. | Federated query engine for federation of data queries across structure and unstructured data |
US8417689B1 (en) * | 2011-11-21 | 2013-04-09 | Emc Corporation | Programming model for transparent parallelization of combinatorial optimization |
US9002813B2 (en) * | 2011-12-22 | 2015-04-07 | Sap Se | Execution plan preparation in application server |
US8924373B2 (en) * | 2012-08-09 | 2014-12-30 | International Business Machines Corporation | Query plans with parameter markers in place of object identifiers |
US9720966B2 (en) * | 2012-12-20 | 2017-08-01 | Teradata Us, Inc. | Cardinality estimation for optimization of recursive or iterative database queries by databases |
US9146960B2 (en) * | 2012-12-20 | 2015-09-29 | Teradata Us, Inc. | Adaptive optimization of iterative or recursive query execution by database systems |
CN103092970A (zh) * | 2013-01-24 | 2013-05-08 | 华为技术有限公司 | 一种数据库操作方法及设备 |
US9576026B2 (en) * | 2013-03-13 | 2017-02-21 | Futurewei Technologies, Inc. | System and method for distributed SQL join processing in shared-nothing relational database clusters using self directed data streams |
US10268724B2 (en) * | 2013-03-15 | 2019-04-23 | Teradata Us, Inc. | Techniques for improving the performance of complex queries |
US20160253366A1 (en) * | 2013-10-15 | 2016-09-01 | Hewlett Packard Enterprise Development Lp | Analyzing a parallel data stream using a sliding frequent pattern tree |
US9996601B2 (en) * | 2013-11-14 | 2018-06-12 | Empire Technology Development Llc | Data synchronization |
CN103761080B (zh) * | 2013-12-25 | 2017-02-15 | 中国农业大学 | 一种基于SQL的MapReduce作业生成方法及系统 |
CN103984726B (zh) * | 2014-05-16 | 2017-03-29 | 上海新炬网络信息技术有限公司 | 一种数据库执行计划的局部修正方法 |
CN105243068A (zh) * | 2014-07-09 | 2016-01-13 | 华为技术有限公司 | 数据库系统的查询方法、服务器和能耗测试系统 |
US9864740B2 (en) * | 2015-02-05 | 2018-01-09 | Ciena Corporation | Methods and systems for creating and applying a template driven element adapter |
US20160246842A1 (en) * | 2015-02-25 | 2016-08-25 | Futurewei Technologies, Inc. | Query optimization adaptive to system memory load for parallel database systems |
US10115116B2 (en) * | 2015-03-02 | 2018-10-30 | Microsoft Technology Licensing, Llc | Optimizing efficiency and cost of crowd-sourced polling |
US10180978B2 (en) * | 2015-03-19 | 2019-01-15 | Sap Se | Interface providing decision support in complex problem environment |
US10585887B2 (en) * | 2015-03-30 | 2020-03-10 | Oracle International Corporation | Multi-system query execution plan |
US9916353B2 (en) * | 2015-04-01 | 2018-03-13 | International Business Machines Corporation | Generating multiple query access plans for multiple computing environments |
US10108664B2 (en) * | 2015-04-01 | 2018-10-23 | International Business Machines Corporation | Generating multiple query access plans for multiple computing environments |
US10216793B2 (en) * | 2015-11-03 | 2019-02-26 | Sap Se | Optimization of continuous queries in hybrid database and stream processing systems |
-
2016
- 2016-02-19 CN CN201610095091.0A patent/CN107102995B/zh active Active
- 2016-07-15 WO PCT/CN2016/090222 patent/WO2017140085A1/zh active Application Filing
- 2016-07-15 JP JP2017518519A patent/JP6415708B2/ja active Active
- 2016-07-15 RU RU2017113685A patent/RU2674886C2/ru active
- 2016-07-15 EP EP16840285.7A patent/EP3232339B1/en active Active
-
2017
- 2017-04-24 US US15/495,569 patent/US10901976B2/en active Active
Patent Citations (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20060212429A1 (en) * | 2005-03-17 | 2006-09-21 | Microsoft Corporation | Answering top-K selection queries in a relational engine |
CN101576880A (zh) * | 2008-05-06 | 2009-11-11 | 山东省标准化研究院 | 基于极值优化的数据库查询优化方法 |
CN103793467A (zh) * | 2013-09-10 | 2014-05-14 | 浙江鸿程计算机系统有限公司 | 一种基于超图和动态规划的大数据实时查询优化方法 |
Also Published As
Publication number | Publication date |
---|---|
EP3232339A1 (en) | 2017-10-18 |
EP3232339A4 (en) | 2018-03-07 |
JP6415708B2 (ja) | 2018-10-31 |
EP3232339B1 (en) | 2019-02-27 |
CN107102995B (zh) | 2020-02-21 |
CN107102995A (zh) | 2017-08-29 |
JP2018509666A (ja) | 2018-04-05 |
RU2017113685A (ru) | 2018-10-23 |
US20170242884A1 (en) | 2017-08-24 |
RU2017113685A3 (zh) | 2018-10-23 |
RU2674886C2 (ru) | 2018-12-13 |
US10901976B2 (en) | 2021-01-26 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US11550769B2 (en) | Data processing method, apparatus, and system | |
WO2017140085A1 (zh) | 一种sql执行计划的确定方法及装置 | |
US10133778B2 (en) | Query optimization using join cardinality | |
US10534775B2 (en) | Cardinality estimation for database query planning | |
US10585887B2 (en) | Multi-system query execution plan | |
WO2019085087A1 (zh) | 接口测试及测试数据生成方法、装置、终端和存储介质 | |
EP3173944B1 (en) | Database access method and apparatus and database system | |
WO2016134580A1 (zh) | 一种数据查询方法及装置 | |
US10599652B2 (en) | Database query time estimator | |
US11366808B2 (en) | Query processing method, data source registration method, and query engine | |
US11132366B2 (en) | Transforming directed acyclic graph shaped sub plans to enable late materialization | |
US10783143B2 (en) | Computing columnar information during join enumeration | |
US20170371922A1 (en) | Database Management for Mobile Devices | |
Liang et al. | Mid-model design used in model transition and data migration between relational databases and nosql databases | |
US9734177B2 (en) | Index merge ordering | |
CN111897824A (zh) | 数据操作方法、装置、设备和存储介质 | |
WO2023231615A1 (zh) | 一种基于数据湖的物化列创建方法以及数据查询方法 | |
US10789249B2 (en) | Optimal offset pushdown for multipart sorting | |
CN109753533A (zh) | 一种多源关系型数据库客户端开发方法及装置 | |
JP6034240B2 (ja) | 分析方法、分析装置および分析プログラム | |
CN108073584B (zh) | 一种数据处理方法及服务器 | |
CN114416784B (zh) | 处理数据库查询语句的方法、装置及原生分布式数据库 | |
US11803545B1 (en) | Runtime statistics feedback for query plan cost estimation | |
CN112732704B (zh) | 一种数据处理方法、装置及存储介质 | |
CN115563148A (zh) | 数据库查询方法和装置 |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
REEP | Request for entry into the european phase |
Ref document number: 2016840285 Country of ref document: EP |
|
WWE | Wipo information: entry into national phase |
Ref document number: 2016840285 Country of ref document: EP |
|
ENP | Entry into the national phase |
Ref document number: 2017518519 Country of ref document: JP Kind code of ref document: A |
|
ENP | Entry into the national phase |
Ref document number: 2017113685 Country of ref document: RU Kind code of ref document: A |
|
121 | Ep: the epo has been informed by wipo that ep was designated in this application |
Ref document number: 16840285 Country of ref document: EP Kind code of ref document: A1 |
|
NENP | Non-entry into the national phase |
Ref country code: DE |