CN111639096B - SQL sentence execution method and device, electronic equipment and storage medium - Google Patents
SQL sentence execution method and device, electronic equipment and storage medium Download PDFInfo
- Publication number
- CN111639096B CN111639096B CN202010476128.0A CN202010476128A CN111639096B CN 111639096 B CN111639096 B CN 111639096B CN 202010476128 A CN202010476128 A CN 202010476128A CN 111639096 B CN111639096 B CN 111639096B
- Authority
- CN
- China
- Prior art keywords
- execution plan
- evaluation score
- execution
- sql statement
- association table
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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 OR CALCULATING; 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/2455—Query execution
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Operations Research (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the invention provides a method and a device for executing Structured Query Language (SQL) sentences, electronic equipment and a storage medium, wherein the method comprises the following steps: selecting an execution plan with the lowest evaluation score in the execution plan association table as a first selected execution plan aiming at the SQL sentence to be executed; the execution plan in the execution plan association table represents a manner of acquiring data indicated by the SQL statement from the database; the SQL statement is executed using the first selected execution plan. By applying the technical scheme provided by the embodiment of the invention, the corresponding relation between the execution plan and the evaluation score can be stored in the execution plan association table, when the SQL sentence is acquired, the execution plan with the lowest evaluation score is selected to execute the SQL sentence, the lower the evaluation score is, the less processing resources are consumed by the execution plan, and the probability of generating a poor execution plan is reduced.
Description
Technical Field
The present invention relates to the field of computer technologies, and in particular, to a method and apparatus for executing an SQL (Structured Query Language) statement, an electronic device, and a storage medium.
Background
Before being executed, the query component in the database engine generates a plurality of execution plans according to the SQL statement, and selects one execution plan from the execution plans to execute the SQL statement, but when the selected execution plan is worse, the execution duration of the SQL statement is longer, and more computing resources and I/O (Input/Output) resources are consumed.
Aiming at the problems, in the prior art, a CBO (Cost-Based Optimization) mode is adopted to select an execution plan, when the more computing resources, I/O resources and time are consumed by the execution plan, the higher the Cost is, aiming at an SQL sentence to be executed, the distribution condition of data related to the SQL sentence in a database is recorded in statistical information, and an execution plan with lower Cost is selected based on the statistical information, and the SQL sentence is executed by adopting the execution plan. For example, the purpose of the SQL statement is to screen girls with scores of more than 90 from the set of data, the statistics information records that the percentage of the total number of the women with scores of more than 90 is 5%, the percentage of the total number of the women with sexes is 50%, and two execution plans exist, wherein the first is to screen out the people with scores of more than 90 first, and then screen out the people with sexes from the people with scores of more than 90; the second is to screen out the female sex first, then screen out the person with score greater than 90 minutes from the female sex, obviously the first execution plan needs less data than the second, because the person with score greater than 90 minutes is screened out first, 95% of people have been eliminated, the data processing amount is obviously reduced, so the first execution plan should be adopted to execute SQL sentence based on the statistical information, and the cost is the lowest.
The statistical information is refreshed once every preset time, the latest data distribution condition is recorded, and the latest corresponding relation between all execution plans and costs is generated based on the latest data distribution condition, wherein the accuracy of the statistical information is 100%, but before the statistical information is not refreshed next time, the accuracy of the statistical information is continuously reduced along with the updating of the data, the probability of selecting the optimal execution plan is lower, the accuracy of the statistical information is lower, and the probability of using the worse execution plan is higher.
Disclosure of Invention
The embodiment of the invention aims to provide an execution method, an execution device, electronic equipment and a storage medium of SQL sentences, which are used for solving the problem of high probability of using a worse execution plan when the SQL sentences are executed. The specific technical scheme is as follows:
in a first aspect, an embodiment of the present invention provides a method for executing an SQL statement, including:
selecting an execution plan with the lowest evaluation score in an execution plan association table as a first selected execution plan, wherein evaluation scores of a plurality of execution plans for the SQL statement are stored in the execution plan association table, the evaluation score of one execution plan represents the condition of processing resources consumed when the execution plan is used for executing the SQL statement last time, and the higher the evaluation score is, the more the processing resources are consumed by the execution plan; the execution plan in the execution plan association table represents a mode of acquiring data indicated by the SQL statement from a database;
And executing the SQL sentence by adopting the first selected execution plan.
Further, after said executing said SQL statement using said first selected execution plan, said method further comprises:
calculating a new evaluation score of the first selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the first selected execution plan at this time;
and updating the evaluation score of the first selected execution plan in the execution plan association table to be the new evaluation score.
Further, before selecting the execution plan with the lowest evaluation score in the execution plan association table as the selected execution plan for the SQL sentence to be executed, the method further includes:
generating an execution plan for executing the SQL statement as a candidate execution plan aiming at the SQL statement to be executed;
judging whether the evaluation score of the execution plan to be selected can be found in an execution plan association table;
when the evaluation score of the execution plan to be selected is found, executing the SQL statement aiming at the execution to be executed, and selecting the execution plan with the lowest evaluation score in the execution plan association table;
when the evaluation score of the execution plan to be selected is not found, executing the SQL sentence by adopting the execution plan to be selected;
Calculating to obtain an evaluation score of the execution plan to be selected based on the condition of processing resources consumed when the SQL statement is executed by adopting the execution plan to be selected at this time;
and adding the evaluation score of the execution plan to be selected into the execution plan association table.
Further, before selecting the execution plan with the lowest evaluation score in the execution plan association table as the selected execution plan for the SQL sentence to be executed, the method further includes:
aiming at SQL sentences to be executed, acquiring the accuracy of statistical information of data related to the SQL sentences in a database;
judging whether the accuracy of the statistical information is smaller than a first preset accuracy threshold value or not;
if the evaluation score is smaller than the evaluation score, executing the SQL statement to be executed, and selecting an execution plan with the lowest evaluation score from an execution plan association table;
if not, selecting an execution plan with the lowest execution cost from the execution plans for the SQL sentence based on the execution cost of the execution plans for the SQL sentence calculated in advance as a second selected execution plan;
executing the SQL statement by adopting the second selected execution plan;
Calculating an evaluation score of the second selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the second selected execution plan at this time;
and adding the evaluation score of the second selected execution plan to the execution plan association table.
Further, the statistical information is refreshed by adopting the following steps:
judging whether the time length between the current time and the time of refreshing the statistical information last time reaches a preset time length or not;
if the preset time length is reached, judging whether the accuracy of the statistical information is smaller than a second preset accuracy threshold, wherein the second preset accuracy threshold is not larger than the first preset accuracy threshold;
and if the accuracy is smaller than the second preset accuracy threshold, refreshing the statistical information.
Further, the method further comprises:
and if the preset duration is reached and the accuracy of the statistical information is smaller than the second preset accuracy threshold, clearing the evaluation score stored in the execution plan association table.
Further, the evaluation score of an execution plan is calculated based on the number of data lines of the data processed during the execution of the execution plan.
In a second aspect, an embodiment of the present invention provides an execution apparatus for an SQL statement, including:
a first selecting unit, configured to select, as a first selected execution plan, an execution plan with a lowest evaluation score in an execution plan association table, where evaluation scores of a plurality of execution plans for the SQL statement are stored in the execution plan association table, where the evaluation score of one execution plan indicates a situation of processing resources consumed when the execution plan was last used to execute the SQL statement, and a higher evaluation score indicates that the processing resources consumed by the execution plan are greater; the execution plan in the execution plan association table represents a mode of acquiring data indicated by the SQL statement from a database;
and the first execution unit is used for executing the SQL sentence by adopting the first selected execution plan.
Further, the device further comprises:
the computing unit is used for computing a new evaluation score of the first selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the first selected execution plan at this time;
and the updating unit is used for updating the evaluation score of the first selected execution plan in the execution plan association table to the new evaluation score.
Further, the device further comprises:
the generating unit is used for selecting the execution plan with the lowest evaluation score in the execution plan association table for the SQL sentence to be executed by the first selecting unit, and generating the execution plan for executing the SQL sentence as the execution plan to be selected for the SQL sentence to be executed before the SQL sentence to be executed is used as the first selected execution plan;
the first judging unit is used for judging whether the evaluation score of the execution plan to be selected can be found in the execution plan association table; triggering the first selection unit when the evaluation score of the execution plan to be selected is found; triggering a second execution unit when the evaluation score of the execution plan to be selected is not found;
the second execution unit is used for executing the SQL sentence by adopting the execution plan to be selected; calculating to obtain an evaluation score of the execution plan to be selected based on the condition of processing resources consumed when the SQL statement is executed by adopting the execution plan to be selected at this time; and adding the evaluation score of the execution plan to be selected into the execution plan association table.
Further, the device further comprises:
the first selection unit is used for selecting an execution plan with the lowest evaluation score in the execution plan association table for the SQL sentence to be executed, and acquiring the accuracy of statistical information of data related to the SQL sentence in a database for the SQL sentence to be executed before the execution plan is used as the selected execution plan;
The second judging unit is used for judging whether the accuracy of the statistical information is smaller than a first preset accuracy threshold value or not; triggering the first selection unit if the accuracy of the statistical information is smaller than a first preset accuracy threshold; triggering a second selection unit if the accuracy of the statistical information is not less than a first preset accuracy threshold;
the second selecting unit is configured to select, as a second selected execution plan, an execution plan with the lowest execution cost from the plurality of execution plans for the SQL statement, based on the execution costs of the plurality of execution plans for the SQL statement calculated in advance; executing the SQL statement by adopting the second selected execution plan; calculating an evaluation score of the second selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the second selected execution plan at this time; and adding the evaluation score of the second selected execution plan to the execution plan association table.
Further, the device further comprises:
the third judging unit is used for judging whether the duration of the current time and the time of the last refreshing of the statistical information reaches the preset duration or not; if the preset time length is reached, judging whether the accuracy of the statistical information is smaller than a second preset accuracy threshold value, wherein the second preset accuracy threshold value is not larger than the first preset accuracy threshold value;
And the refreshing unit is used for refreshing the statistical information if the accuracy of the statistical information is smaller than the second preset accuracy threshold value.
Further, the device further comprises:
and the emptying unit is used for emptying the evaluation scores stored in the execution plan association table if the preset duration is reached and the accuracy of the statistical information is smaller than the second preset accuracy threshold.
Further, the evaluation score of an execution plan is calculated based on the number of data lines of the data processed during the execution of the execution plan.
In a third aspect, an embodiment of the present invention provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, where the processor, the communication interface, and the memory complete communication with each other through the communication bus;
a memory for storing a computer program;
and the processor is used for realizing the steps of the execution method of any SQL statement when executing the program stored in the memory.
In a fourth aspect, embodiments of the present invention provide a computer-readable storage medium having stored therein a computer program which, when executed by a processor, implements the steps of the execution method of any of the SQL statements.
In a fifth aspect, embodiments of the present invention further provide a computer program product comprising instructions that, when executed on a computer, cause the computer to perform the method of executing an SQL statement as described in any of the above.
According to the execution method, the device, the electronic equipment and the storage medium of the SQL sentence, aiming at the SQL sentence to be executed, an execution plan with the lowest evaluation score in an execution plan association table is selected as a first selected execution plan, wherein evaluation scores of a plurality of execution plans aiming at the SQL sentence are stored in the execution plan association table, the evaluation score of one execution plan represents the condition of processing resources consumed when the execution plan is used for executing the SQL sentence last time, and the higher the evaluation score is, the more the processing resources consumed by the execution plan are represented; executing the SQL statement by adopting the first selected execution plan; the execution plan in the execution plan association table represents the manner in which the data indicated by the SQL statement is retrieved from the database.
By applying the technical scheme provided by the embodiment of the invention, the corresponding relation between the execution plan and the evaluation score can be stored in the execution plan association table, when the SQL sentence is acquired, the execution plan with the lowest evaluation score is selected for executing the SQL sentence, the lower the evaluation score is, the less processing resources are consumed by the execution plan, and the probability of generating a poor execution plan is reduced.
Of course, it is not necessary for any one product or method of practicing the invention to achieve all of the advantages set forth above at the same time.
Drawings
In order to more clearly illustrate the embodiments of the invention or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a flowchart of a method for executing an SQL statement according to an embodiment of the present invention;
FIG. 2 is a flowchart of another method for executing SQL statements according to an embodiment of the invention;
FIG. 3 is a flowchart of another method for executing SQL statements according to an embodiment of the invention;
FIG. 4 is a flowchart of another method for executing SQL statements according to an embodiment of the invention;
FIG. 5a is a schematic diagram of an SQL statement execution device according to an embodiment of the present invention;
FIG. 5b is a schematic diagram illustrating an execution apparatus of another SQL statement according to an embodiment of the invention;
FIG. 5c is a schematic diagram illustrating an execution apparatus of another SQL statement according to an embodiment of the invention;
FIG. 5d is a schematic diagram illustrating an execution apparatus of another SQL statement according to an embodiment of the invention;
fig. 6 is a schematic structural diagram of an electronic device according to an embodiment of the present invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present invention, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
The embodiment of the invention provides a method for executing an SQL sentence, and FIG. 1 is a flow chart of the method for executing the SQL sentence provided by the embodiment of the invention, as shown in FIG. 1, and comprises the following steps:
and 11, selecting an execution plan with the lowest evaluation score in the execution plan association table as a first selected execution plan according to the SQL sentence to be executed.
Wherein, the evaluation scores of a plurality of execution plans aiming at the SQL sentence are stored in the execution plan association table, the evaluation score of one execution plan represents the condition of the processing resource consumed when the execution plan is used for executing the SQL sentence last time, and the higher the evaluation score is, the more the processing resource consumed by the execution plan is; the execution plan in the execution plan association table represents the manner in which the data indicated by the SQL statement is retrieved from the database.
And step 12, executing the SQL statement by adopting the first selected execution plan.
By adopting the execution method of the SQL sentence shown in the figure 1, the corresponding relation between the execution plan and the evaluation score can be stored in the execution plan association table, when the SQL sentence is acquired, the execution plan with the lowest evaluation score is selected for executing the SQL sentence, the lower evaluation score indicates that the less processing resources are consumed by the execution plan, and the probability of generating a worse execution plan is reduced.
In one embodiment of the present application, fig. 2 is a flowchart of another execution method of an SQL statement provided in the embodiment of the present invention, as shown in fig. 2, on the basis of fig. 1, the method may include the following steps:
and step 13, calculating new evaluation scores of the first selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by the first selected execution plan.
And step 14, updating the evaluation score of the first selected execution plan in the execution plan association table to be a new evaluation score.
In this embodiment of the present application, since the data in the database may be updated, after the SQL statement is executed by using the first selected execution plan, the evaluation score of the first selected execution plan in the execution plan association table may be updated, so as to improve the accuracy of the evaluation score recorded in the execution plan association table.
In one embodiment of the present application, the evaluation score of an execution plan is calculated based on the number of data lines of the processed data in the process of being executed by the execution plan, where the processed data may include scanned data and read data, and the calculation may be simply adding the number of lines of the scanned data and the read data, or weighting and then adding the number of lines of the scanned data and the read data.
Taking the example of weighting and then summing the number of lines of scanned data and read data, evaluating the score=the number of scanning lines×the weight of the number of scanning lines+the number of reading lines×the weight of the number of reading lines, assuming that one SQL statement is to find out women with a score of 95 or more from a list of 100 students, 50 women with a score of 95 or more, and 10 students with a score of 95 or more, for one execution plan of the SQL statement, the execution plan specifically comprises the steps of firstly scanning 100 lines of student data, finding out 50 lines of student data with a gender of female, then scanning the 50 lines of student data with a gender of female, reading the 50 lines of student data with a score of 95 or more from the 50 lines of student data, assuming that 5 women with a score of 95 or more have a score of 5, presetting the weight of the number of scanning lines to be 0.6, and the weight of the number of reading lines to be 0.4, evaluating the score= (100+50) ×0.6+5×0.4=92.
In one embodiment of the present application, fig. 3 is a flowchart of another execution method of an SQL statement provided in the embodiment of the present invention, as shown in fig. 3, may include the following steps:
step 31, for the SQL sentence to be executed, generating an execution plan for executing the SQL sentence as a candidate execution plan.
After the relational database obtains the SQL statement, a greedy algorithm can be adopted to generate an execution plan for executing the SQL statement as a candidate execution plan.
Step 32, judging whether the evaluation score of the execution plan to be selected can be found in the execution plan association table; when the evaluation score of the execution plan to be selected is found, step 33 is executed; when the evaluation score of the execution plan to be selected is not found, step 35 is executed.
As shown in table 1, the evaluation scores of a plurality of execution plans for the SQL statement are stored in the execution plan association table, the evaluation score indicates the condition of the processing resource consumed when the execution plan was last used for executing the SQL statement, the higher the evaluation score is, the more the processing resource consumed by the execution plan is, assuming that the execution plan to be selected is the execution plan 3, the evaluation score of the execution plan 3 can be found in the execution plan association table, and step 33 is executed; assuming that the execution plan to be selected is the execution plan 4, the evaluation score of the execution plan 5 cannot be found in the execution plan association table, and step 35 is executed.
| Execution plan | Evaluation score |
| Execution plan 1 | 10 |
| Execution plan 2 | 245 |
| Execution plan 3 | 12 |
Table 1: execution plan association table
Step 33, selecting the execution plan with the lowest evaluation score in the execution plan association table as the first selected execution plan for the SQL sentence to be executed.
In the execution plan association table, the execution plan having the lowest evaluation score is selected, and as shown in table 1, the evaluation score of the execution plan 1 is 3, and the execution plan 1 is selected because the evaluation score is the lowest in the execution plan association table.
Step 34, executing the SQL statement using the first selected execution plan.
The evaluation score may be calculated based on the number of data lines of the data processed in the process of executing the first selected execution plan, the number of scanning lines and the number of reading lines are recorded when the SQL statement is executed by adopting the first selected execution plan this time, and a new evaluation score of the first selected execution plan may be calculated based on the weighted calculation of the number of scanning lines and the number of reading lines, where the new evaluation score=the number of scanning lines×the weight of the number of scanning lines+the number of reading lines×the weight of the number of reading lines. For example, the weight of the scan line number is preset to be 0.6, the weight of the read line number is 0.4, the scan line number is 150 lines and the read line number is 5 lines when the SQL statement is executed by using the first selected execution plan, and the new evaluation score of the first selected execution plan=150×0.6+5×0.4=92 points.
In one embodiment, after step 34, a new evaluation score of the first selected execution plan may be calculated based on the processing resources consumed when the SQL statement is executed using the first selected execution plan this time, and further, the evaluation score of the first selected execution plan in the execution plan association table may be updated to the new evaluation score.
Assuming that the first selected execution plan is execution plan 3, the evaluation score 12 in execution plan 3 is changed to the new evaluation score 92 calculated as described above, as shown in table 2.
| Execution plan | Evaluation score |
| Execution plan 1 | 10 |
| Execution plan 2 | 245 |
| Execution plan 3 | 92 |
Table 2: updated execution plan association table
And 35, executing the SQL statement by adopting the alternative execution plan.
The execution plan 4 is assumed to be the execution plan 4, and the execution plan 4 cannot be found in the execution plan association table, so that the SQL statement is executed by using the execution plan 4 to be selected. Since the evaluation scores stored in the execution plan association table do not include the evaluation scores of the execution plan 4, the evaluation scores of the execution plan 4 may be lower than the evaluation scores already in the execution plan association table, that is, the processing resources consumed for executing the SQL statement with the execution plan 4 may be less, so the SQL statement is executed with the execution plan 4. In this way, the data in the execution plan association table can be enriched, and the lowest evaluation score in the execution plan association table can be reduced as much as possible.
Step 36, calculating to obtain the evaluation score of the execution plan to be selected based on the condition of the processing resources consumed when the SQL statement is executed by the execution plan to be selected.
In this step, how to calculate the evaluation score of the execution plan to be selected may be performed by calculating the new evaluation score of the first execution plan selected in the above-mentioned step 25, which is not described in detail herein by way of example.
And step 37, adding the evaluation score of the execution plan to be selected to the execution plan association table.
The evaluation score of the execution plan to be selected is added to the execution plan association table, and the execution plan 4 and its evaluation score are added to the execution plan association table, assuming that the evaluation score of the execution plan 4 is 8 points, as shown in table 3.
Table 3: added execution plan association table
In one embodiment of the present application, fig. 4 is a flowchart of another execution method of an SQL statement provided in the embodiment of the present invention, as shown in fig. 4, may include the following steps:
step 41, for the SQL statement to be executed, obtaining accuracy of statistical information of data related to the SQL statement in the database.
The accuracy of the statistical information can be recorded in the statistical information, the accuracy is more than or equal to 0 and less than or equal to 100 percent, after the execution of the SQL statement is finished each time, the CPU (Central Processing Unit ) can additionally dispatch the accuracy of asynchronously refreshing the statistical information by a thread, and the accuracy of the statistical information of the data related to the SQL statement in the database is acquired aiming at the SQL statement to be executed.
Step 42, judging whether the accuracy of the statistical information is smaller than a first preset accuracy threshold; if less, go to step 43; if not, step 45 is performed.
The first preset accuracy threshold value can reflect whether the statistical information is reliable or not, the statistical information lower than the first preset accuracy threshold value is not reliable any more, the accuracy of the obtained statistical information is compared with 80% on the assumption that the first preset accuracy threshold value is set to 80%, and whether the accuracy of the statistical information is smaller than 80% or not is judged; if less, go to step 43; if not, step 45 is performed.
Step 43, selecting the execution plan with the lowest evaluation score in the execution plan association table as the first selected execution plan for the SQL sentence to be executed.
This step may be the same as step 33 described above, and reference may be made specifically to the description of step 33 above.
Step 44, execute the SQL statement using the first selected execution plan.
Step 45, selecting an execution plan with the lowest execution cost from the execution plans for the SQL sentence based on the execution cost of the execution plans for the SQL sentence calculated in advance as a second selected execution plan;
for a plurality of execution plans of the SQL sentence, calculating the execution cost of the execution plans in advance, wherein the more calculation resources, I/O resources and time consumed by the execution plans, the higher the cost, and the execution plan with the lowest execution cost is selected as the second selected execution plan.
Step 46, executing the SQL statement using the second selected execution plan.
Step 47, calculating an evaluation score of the second selected execution plan based on the processing resources consumed when executing the SQL statement using the second selected execution plan.
In this step, how to calculate the evaluation score of the second selected execution plan may be performed by using the calculation method of calculating the new evaluation score of the first selected execution plan in the above step, which will not be described in detail herein by way of example.
And step 48, adding the evaluation score of the second selected execution plan to the execution plan association table.
If the evaluation score of the second selected execution plan exists in the execution plan association table, updating the evaluation score of the second selected execution plan in the execution plan association table to be a new evaluation score obtained by calculation at the time;
and if the evaluation score of the second selected execution plan does not exist in the execution plan association table, adding the evaluation score of the second selected execution plan into the execution plan association table.
In one embodiment, after step 44, a new evaluation score of the first selected execution plan may be calculated based on the processing resources consumed when the SQL statement is executed using the first selected execution plan this time, and further, the evaluation score of the first selected execution plan in the execution plan association table may be updated to the new evaluation score.
In one embodiment of the present application, the following steps may be employed to refresh the statistics:
judging whether the time length between the current time and the time of last refreshing of the statistical information reaches a preset time length or not; if the preset time length is reached, judging whether the accuracy of the statistical information is smaller than a second preset accuracy threshold value, wherein the second preset accuracy threshold value is not larger than the first preset accuracy threshold value; and if the accuracy is smaller than a second preset accuracy threshold value, refreshing the statistical information.
With the continuous updating of the data in the database, the accuracy of the statistical information is continuously reduced, in order to better reflect the distribution situation of the data in the database, the statistical information needs to be refreshed periodically, the last refreshed time is recorded in the statistical information, whether the time length between the current time and the last refreshed time reaches the preset time length is judged, for example, the current time is 2020, 1 month and 17 days, the last refreshed time is 2020, and the preset time length is 1 day, so that the preset time length is reached, whether the accuracy of the statistical information is smaller than a second preset accuracy threshold value is judged, wherein the second preset accuracy threshold value is not larger than the first preset accuracy threshold value, and if the second preset accuracy threshold value is smaller, the statistical information is refreshed. Since a large amount of computing resources and I/O resources are consumed for refreshing the statistical information, setting the second preset accuracy threshold value not larger than the first preset accuracy threshold value can avoid refreshing the statistical information under the condition that the accuracy of the statistical information meets the requirement, thereby reducing the consumption of the computing resources and the I/O resources.
In one embodiment of the present application, the execution method of the SQL statement may further include:
and if the preset time length is reached and is smaller than a second preset accuracy threshold value, clearing the evaluation score stored in the execution plan association table.
And if the time length between the current time and the time of refreshing the statistical information last time reaches the preset time length and is smaller than a second preset threshold value, clearing the evaluation score stored in the execution plan association table, and adding the generated new evaluation score of the execution plan into the execution plan association table after refreshing the statistical information.
Correspondingly, based on the same inventive concept, corresponding to the above method for executing the SQL statement provided in the embodiment of the present application, the embodiment of the present invention further provides an apparatus for executing the SQL statement, and fig. 5a is a schematic structural diagram of the apparatus for executing the SQL statement provided in the embodiment of the present invention, as shown in fig. 5a, where the apparatus may include:
a first selecting unit 51, configured to select, as a first selected execution plan, an execution plan with a lowest evaluation score in an execution plan association table, where evaluation scores of a plurality of execution plans for the SQL statement are stored in the execution plan association table, the evaluation score of one execution plan indicating a case of processing resources consumed when the execution plan was last used to execute the SQL statement, the higher the evaluation score indicates the more processing resources consumed by the execution plan; the execution plan in the execution plan association table represents a manner of acquiring data indicated by the SQL statement from the database;
The first execution unit 52 is configured to execute the SQL statement using the first selected execution plan.
In one embodiment of the present application, further, as shown in fig. 5b, the apparatus may further include:
a calculating unit 53, configured to calculate a new evaluation score of the first selected execution plan based on the condition of processing resources consumed when the SQL statement is executed using the first selected execution plan at this time;
and an updating unit 54 for updating the evaluation score of the first selected execution plan in the execution plan association table to a new evaluation score.
In one embodiment of the present application, further, as shown in fig. 5c, the apparatus may further include:
a generating unit 55, configured to select, for the SQL statement to be executed, an execution plan with the lowest evaluation score in the execution plan association table, as a first selected execution plan, and generate, for the SQL statement to be executed, an execution plan for executing the SQL statement, as a candidate execution plan;
a first judging unit 56 for judging whether the evaluation score of the execution plan to be selected can be found in the execution plan association table; when the evaluation score of the execution plan to be selected is found, the first selection unit 51 is triggered; triggering the second execution unit 57 when the evaluation score of the execution plan to be selected is not found;
A second execution unit 57 for executing the SQL statement using the execution plan to be selected; calculating to obtain an evaluation score of the execution plan to be selected based on the condition of processing resources consumed when the SQL statement is executed by the execution plan to be selected at the time; and adding the evaluation score of the execution plan to be selected into the execution plan association table.
In one embodiment of the present application, further, as shown in fig. 5d, the apparatus may further include:
an obtaining unit 58, configured to, in the first selecting unit 51, select, for the SQL statement to be executed, an execution plan with the lowest evaluation score in the execution plan association table, and obtain, for the SQL statement to be executed, accuracy of statistical information of data related to the SQL statement in the database, before the execution plan is selected as the selected execution plan;
a second judging unit 59, configured to judge whether the accuracy of the statistical information is less than a first preset accuracy threshold; triggering the first selection unit 51 if the accuracy of the statistical information is less than a first preset accuracy threshold; triggering the second selection unit 510 if the accuracy of the statistical information is not less than the first preset accuracy threshold;
a second selecting unit 510, configured to select, as a second selected execution plan, an execution plan with the lowest execution cost from the plurality of execution plans for the SQL statement, based on the execution costs of the plurality of execution plans for the SQL statement calculated in advance; executing the SQL statement by adopting a second selected execution plan; calculating an evaluation score of the second selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by the second selected execution plan; and adding the evaluation score of the second selected execution plan to the execution plan association table.
Further, in an embodiment of the present application, the apparatus may further include:
the third judging unit is used for judging whether the time length between the current time and the time of the last time of refreshing the statistical information reaches the preset time length or not; if the preset time length is reached, judging whether the accuracy of the statistical information is smaller than a second preset accuracy threshold value, wherein the second preset accuracy threshold value is not larger than the first preset accuracy threshold value;
and the refreshing unit is used for refreshing the statistical information if the accuracy of the statistical information is smaller than a second preset accuracy threshold value.
In one embodiment of the present application, the apparatus may further include:
and the emptying unit is used for emptying the evaluation scores stored in the execution plan association table if the preset duration is reached and the accuracy of the statistical information is smaller than a second preset accuracy threshold.
Further, an evaluation score of an execution plan is calculated based on the number of data lines of the data processed during execution of the execution plan.
Correspondingly, based on the same inventive concept, corresponding to the execution method of the above SQL statement provided in the embodiment of the present application, the embodiment of the present invention further provides an electronic device, as shown in fig. 6, including a processor 61, a communication interface 62, a memory 63, and a communication bus 64, where the processor 61, the communication interface 62, and the memory 63 complete communication with each other through the communication bus 64,
A memory 63 for storing a computer program;
the processor 61 is configured to execute the program stored in the memory 63, and implement the following steps:
selecting an execution plan with the lowest evaluation score in an execution plan association table as a first selected execution plan aiming at an SQL sentence to be executed, wherein evaluation scores of a plurality of execution plans aiming at the SQL sentence are stored in the execution plan association table, the evaluation score of one execution plan represents the condition of processing resources consumed when the execution plan is used for executing the SQL sentence last time, and the higher the evaluation score is, the more the processing resources consumed by the execution plan are; the execution plan in the execution plan association table represents a manner of acquiring data indicated by the SQL statement from the database;
the SQL statement is executed using the first selected execution plan.
According to the electronic equipment provided by the embodiment of the invention, the corresponding relation between the execution plans and the evaluation scores can be stored in the execution plan association table, when the SQL sentence is acquired, the execution plan with the lowest evaluation score is selected for executing the SQL sentence, the lower evaluation score is used for indicating that the less processing resources are consumed by the execution plan, and the probability of generating a poor execution plan is reduced.
The communication bus mentioned above for the electronic devices may be a peripheral component interconnect standard (Peripheral Component Interconnect, PCI) bus or an extended industry standard architecture (Extended Industry Standard Architecture, EISA) bus, etc. The communication bus may be classified as an address bus, a data bus, a control bus, or the like. For ease of illustration, the figures are shown with only one bold line, but not with only one bus or one type of bus.
The communication interface is used for communication between the electronic device and other devices.
The Memory may include random access Memory (Random Access Memory, RAM) or may include Non-Volatile Memory (NVM), such as at least one disk Memory. Optionally, the memory may also be at least one memory device located remotely from the aforementioned processor.
The processor may be a general-purpose processor, including a central processing unit (Central Processing Unit, CPU), a network processor (Network Processor, NP), etc.; but also digital signal processors (Digital Signal Processing, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field programmable gate arrays (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.
Correspondingly, based on the same inventive concept, corresponding to the execution method of the SQL statement provided in the embodiment of the present application, in a further embodiment of the present invention, a computer readable storage medium is further provided, where a computer program is stored, and when the computer program is executed by a processor, the steps of the execution method of any one of the SQL statement described above are implemented.
Correspondingly, based on the same inventive concept, corresponding to the execution method of the SQL statement provided in the embodiment of the present application, in a further embodiment provided in the present invention, a computer program product containing instructions is further provided, which when run on a computer, causes the computer to execute the execution method of any of the SQL statements in the embodiment.
In the above embodiments, it may be implemented in whole or in part by software, hardware, firmware, or any combination thereof. When implemented in software, may be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When loaded and executed on a computer, produces a flow or function in accordance with embodiments of the present invention, in whole or in part. The computer may be a general purpose computer, a special purpose computer, a computer network, or other programmable apparatus. The computer instructions may be stored in or transmitted from one computer-readable storage medium to another, for example, by wired (e.g., coaxial cable, optical fiber, digital Subscriber Line (DSL)), or wireless (e.g., infrared, wireless, microwave, etc.). The computer readable storage medium may be any available medium that can be accessed by a computer or a data storage device such as a server, data center, etc. that contains an integration of one or more available media. The usable medium may be a magnetic medium (e.g., floppy Disk, hard Disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid State Disk (SSD)), etc.
It is noted that relational terms such as first and second, and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
In this specification, each embodiment is described in a related manner, and identical and similar parts of each embodiment are all referred to each other, and each embodiment mainly describes differences from other embodiments. In particular, for the apparatus, electronic device, computer readable storage medium, and computer program product embodiments, the description is relatively simple, as relevant to the method embodiments being referred to in the section of the description of the method embodiments.
The foregoing description is only of the preferred embodiments of the present invention and is not intended to limit the scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.
Claims (9)
1. A method of executing a structured query language SQL statement, comprising:
selecting an execution plan with the lowest evaluation score in an execution plan association table as a first selected execution plan, wherein evaluation scores of a plurality of execution plans for the SQL statement are stored in the execution plan association table, the evaluation score of one execution plan represents the condition of processing resources consumed when the execution plan is used for executing the SQL statement last time, and the higher the evaluation score is, the more the processing resources are consumed by the execution plan; the execution plan in the execution plan association table represents a mode of acquiring data indicated by the SQL statement from a database;
executing the SQL statement by adopting the first selected execution plan;
before selecting the execution plan with the lowest evaluation score in the execution plan association table as the first selected execution plan for the SQL sentence to be executed, the method further comprises:
Aiming at SQL sentences to be executed, acquiring the accuracy of statistical information of data related to the SQL sentences in a database;
judging whether the accuracy of the statistical information is smaller than a first preset accuracy threshold value or not;
if the evaluation score is smaller than the evaluation score, executing the SQL statement to be executed, and selecting an execution plan with the lowest evaluation score from an execution plan association table;
if not, selecting an execution plan with the lowest execution cost from the execution plans for the SQL sentence based on the execution cost of the execution plans for the SQL sentence calculated in advance as a second selected execution plan;
executing the SQL statement by adopting the second selected execution plan;
calculating an evaluation score of the second selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the second selected execution plan at this time;
and adding the evaluation score of the second selected execution plan to the execution plan association table.
2. The method of claim 1, wherein after said executing said SQL statement with said first selected execution plan, said method further comprises:
Calculating a new evaluation score of the first selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the first selected execution plan at this time;
and updating the evaluation score of the first selected execution plan in the execution plan association table to be the new evaluation score.
3. The method of claim 1, wherein prior to selecting the execution plan with the lowest evaluation score in the execution plan association table as the first selected execution plan for the SQL statement to be executed, the method further comprises:
generating an execution plan for executing the SQL statement as a candidate execution plan aiming at the SQL statement to be executed;
judging whether the evaluation score of the execution plan to be selected can be found in an execution plan association table;
when the evaluation score of the execution plan to be selected is found, executing the SQL statement aiming at the execution to be executed, and selecting the execution plan with the lowest evaluation score in the execution plan association table;
when the evaluation score of the execution plan to be selected is not found, executing the SQL sentence by adopting the execution plan to be selected;
calculating to obtain an evaluation score of the execution plan to be selected based on the condition of processing resources consumed when the SQL statement is executed by adopting the execution plan to be selected at this time;
And adding the evaluation score of the execution plan to be selected into the execution plan association table.
4. The method of claim 1, wherein the statistical information is refreshed by:
judging whether the time length between the current time and the time of refreshing the statistical information last time reaches a preset time length or not;
if the preset time length is reached, judging whether the accuracy of the statistical information is smaller than a second preset accuracy threshold, wherein the second preset accuracy threshold is not larger than the first preset accuracy threshold;
and if the accuracy is smaller than the second preset accuracy threshold, refreshing the statistical information.
5. The method as recited in claim 4, further comprising:
and if the preset duration is reached and the accuracy of the statistical information is smaller than the second preset accuracy threshold, clearing the evaluation score stored in the execution plan association table.
6. The method of claim 1, wherein the evaluation score for an execution plan is calculated based on the number of data lines of the data processed during execution of the execution plan.
7. An execution device for structured query language SQL statements, comprising:
A selecting unit, configured to select, as a first selected execution plan, an execution plan with a lowest evaluation score in an execution plan association table, where evaluation scores of a plurality of execution plans for the SQL statement are stored in the execution plan association table, where the evaluation score of one execution plan indicates a situation of processing resources consumed when the execution plan was last used to execute the SQL statement, and a higher evaluation score indicates that the processing resources consumed by the execution plan are greater; the execution plan in the execution plan association table represents a mode of acquiring data indicated by the SQL statement from a database;
the execution unit is used for executing the SQL sentence by adopting the first selected execution plan;
the apparatus further comprises:
the system comprises a first selection unit, an acquisition unit and a second selection unit, wherein the first selection unit is used for selecting an execution plan with the lowest evaluation score in an execution plan association table as a first selected execution plan, and acquiring the accuracy of statistical information of data related to the SQL statement in a database aiming at the SQL statement to be executed;
the second judging unit is used for judging whether the accuracy of the statistical information is smaller than a first preset accuracy threshold value or not; if the evaluation score is smaller than the evaluation score, triggering the first selection unit to execute the SQL statement to be executed, and selecting an execution plan with the lowest evaluation score from an execution plan association table; if not, triggering a second selection unit;
The triggering second selecting unit is used for selecting an execution plan with the lowest execution cost from a plurality of execution plans for the SQL sentence based on the execution cost of the plurality of execution plans for the SQL sentence calculated in advance as a second selected execution plan; executing the SQL statement by adopting the second selected execution plan; calculating an evaluation score of the second selected execution plan based on the condition of processing resources consumed when the SQL statement is executed by adopting the second selected execution plan at this time; and adding the evaluation score of the second selected execution plan to the execution plan association table.
8. The electronic equipment is characterized by comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory are communicated with each other through the communication bus;
a memory for storing a computer program;
a processor for carrying out the method steps of any one of claims 1-6 when executing a program stored on a memory.
9. A computer-readable storage medium, characterized in that the computer-readable storage medium has stored therein a computer program which, when executed by a processor, implements the method steps of any of claims 1-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010476128.0A CN111639096B (en) | 2020-05-29 | 2020-05-29 | SQL sentence execution method and device, electronic equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010476128.0A CN111639096B (en) | 2020-05-29 | 2020-05-29 | SQL sentence execution method and device, electronic equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111639096A CN111639096A (en) | 2020-09-08 |
| CN111639096B true CN111639096B (en) | 2024-03-08 |
Family
ID=72331643
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010476128.0A Active CN111639096B (en) | 2020-05-29 | 2020-05-29 | SQL sentence execution method and device, electronic equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111639096B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118277397A (en) * | 2022-12-29 | 2024-07-02 | 华为技术有限公司 | Method, device and computing device for determining execution plan |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102436494A (en) * | 2011-11-11 | 2012-05-02 | 中国工商银行股份有限公司 | Device and method for optimizing execution plan and based on practice testing |
| CN106897343A (en) * | 2016-07-20 | 2017-06-27 | 阿里巴巴集团控股有限公司 | The lookup method of executive plan, storage method and device |
| CN110069522A (en) * | 2017-11-10 | 2019-07-30 | 阿里巴巴集团控股有限公司 | Data query method and apparatus |
| CN110196863A (en) * | 2018-05-04 | 2019-09-03 | 腾讯科技（深圳）有限公司 | Data processing method, calculates equipment and storage medium at device |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| GB2378534B (en) * | 2001-08-03 | 2003-08-20 | Oracle Corp | SQL execution analysis |
| US8903801B2 (en) * | 2007-09-14 | 2014-12-02 | Oracle International Corporation | Fully automated SQL tuning |
-
2020
- 2020-05-29 CN CN202010476128.0A patent/CN111639096B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102436494A (en) * | 2011-11-11 | 2012-05-02 | 中国工商银行股份有限公司 | Device and method for optimizing execution plan and based on practice testing |
| CN106897343A (en) * | 2016-07-20 | 2017-06-27 | 阿里巴巴集团控股有限公司 | The lookup method of executive plan, storage method and device |
| CN110069522A (en) * | 2017-11-10 | 2019-07-30 | 阿里巴巴集团控股有限公司 | Data query method and apparatus |
| CN110196863A (en) * | 2018-05-04 | 2019-09-03 | 腾讯科技（深圳）有限公司 | Data processing method, calculates equipment and storage medium at device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111639096A (en) | 2020-09-08 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN104239351B (en) | A kind of training method and device of the machine learning model of user behavior | |
| WO2017118328A1 (en) | Push information rough selection sorting method, device and computer storage medium | |
| CN113064930B (en) | Cold and hot data identification method and device of data warehouse and electronic equipment | |
| CN113641708B (en) | Rule engine optimization method, data matching method and device, storage medium and terminal | |
| CN110363580B (en) | Information recommendation method and device, computer equipment and storage medium | |
| CN112528703B (en) | Method and device for identifying table structure and electronic equipment | |
| CN118132566B (en) | Database index optimization method | |
| CN111858267B (en) | Early warning method, early warning device, electronic equipment and storage medium | |
| CN111651264A (en) | Method, device and computer equipment for obtaining physical machine resource allocation model | |
| CN116610547A (en) | Server performance evaluation method, device, computer equipment and storage medium | |
| CN111488736B (en) | Self-learning word segmentation method, device, computer equipment and storage medium | |
| CN111639096B (en) | SQL sentence execution method and device, electronic equipment and storage medium | |
| CN111563115B (en) | Statistical method and device for data distribution information in distributed database | |
| CN105608096A (en) | Time series data processing method and system | |
| CN112989183B (en) | Product information recommendation method and device based on life cycle and related equipment | |
| CN111353052B (en) | Multimedia object recommendation method and device, electronic equipment and storage medium | |
| CN113536106A (en) | Method for determining information content to be recommended | |
| US10671644B1 (en) | Adaptive column set composition | |
| CN116091111A (en) | Product demand prediction method and device, electronic equipment and storage medium | |
| US20200410586A1 (en) | Adjusting Method and Adjusting Device, Server and Storage Medium for Scorecard Model | |
| CN113962403B (en) | Operation and maintenance processing method, device and electronic equipment | |
| CN115222123A (en) | Customer quantity prediction method, apparatus, device, medium, and program product | |
| CN113297254A (en) | Conceptualization query method and device | |
| CN113761134B (en) | User portrait construction method, device, computer equipment and storage medium | |
| CN118152439B (en) | Method and device for caching flight data |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |