CN106897343B - Searching method, storing method and device for execution plan - Google Patents
Searching method, storing method and device for execution plan Download PDFInfo
- Publication number
- CN106897343B CN106897343B CN201610576924.5A CN201610576924A CN106897343B CN 106897343 B CN106897343 B CN 106897343B CN 201610576924 A CN201610576924 A CN 201610576924A CN 106897343 B CN106897343 B CN 106897343B
- Authority
- CN
- China
- Prior art keywords
- statement
- parameterized
- execution plan
- literal quantity
- literal
- 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
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/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/242—Query formulation
- G06F16/2433—Query languages
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)
- Devices For Executing Special Programs (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the application discloses a search method, a storage method and a device of an execution plan, wherein the search method of the execution plan comprises the steps of receiving an SQ L statement, generating a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, searching an execution plan and constraint conditions which are stored in advance and correspond to the parameterized SQ L statement, judging whether parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions or not if the parameters meet the constraint conditions, and determining the searched execution plan as the execution plan to be executed.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a method and an apparatus for searching and storing an execution plan.
Background
Generally, a database system needs to make an execution process of how to achieve the purpose of an access task according to an SQ L statement corresponding to the access task, wherein the execution process is called an "execution plan" (execution plan), and the "execution plan" is a program example which can be executed to return a result desired by a user.
In the prior art, the process of generating the execution plan corresponding to the SQ L statement roughly comprises the steps of firstly, performing lexical/syntactic analysis on an SQ L statement, then performing semantic analysis on an SQ L statement, determining an optimal execution plan based on an optimization rule, and finally generating codes corresponding to the optimal execution plan, wherein the process of generating the execution plan takes a certain time, which affects the performance of a database to a certain extent.
However, the step of semantically analyzing the SQ L statement generally consumes a certain amount of time, which may cause poor performance of the database system.
Disclosure of Invention
An embodiment of the present application provides a search method, a storage method, and an apparatus for an execution plan, so as to solve the problems in the prior art.
In order to solve the above technical problem, a search method, a storage method, and an apparatus for an execution plan provided in an embodiment of the present application are implemented as follows:
a lookup method of an execution plan, comprising:
receiving a SQ L statement;
generating a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement comprises parameters corresponding to the literal quantity of the SQ L statement;
searching a prestored execution plan and constraint conditions corresponding to the parameterized SQ L statement;
and if the query is found, judging whether the parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions, and if so, determining the queried execution plan as the execution plan to be executed.
A method of storing an execution plan, comprising:
receiving a SQ L statement;
generating an execution plan corresponding to the SQ L statement;
generating a parameterized SQ L statement and a constraint condition corresponding to the SQ L statement according to the SQ L statement;
mapping the execution plan with the parameterized SQ L statement and mapping and storing the execution plan with the constraints.
A lookup apparatus to execute a plan, comprising:
a receiving unit that receives an SQ L statement;
the generating unit generates a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement comprises parameters corresponding to the literal quantity of the SQ L statement;
the searching unit is used for searching an execution plan and a constraint condition which are stored in advance and correspond to the parameterized SQ L statement;
and the judging unit is used for judging whether the parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions or not when the execution plan corresponding to the parameterized SQ L statement and the constraint conditions are found, and if so, determining the inquired execution plan as the execution plan to be executed.
A storage device to execute a plan, comprising:
a receiving unit that receives an SQ L statement;
a first generation unit that generates an execution plan corresponding to the SQ L statement;
a second generation unit which generates a parameterized SQ L statement and a constraint condition corresponding to the SQ L statement from the SQ L statement;
and a storage unit which maps the execution plan with the parameterized SQ L statement and maps and stores the execution plan with the constraint condition.
The embodiment of the application adopts at least one technical scheme which can achieve the following beneficial effects:
after receiving the SQ L statement, generating a parameterized SQ L statement corresponding to the SQ L statement, so as to find whether the prestored execution plan and constraint condition corresponding to the parameterized SQ L statement exist, and after finding the execution plan and constraint condition, judging whether the inquired execution plan is usable (i.e., whether the execution plan is to be executed) according to the constraint condition.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below, it is obvious that the drawings in the following description are only some embodiments described in the present application, and for those skilled in the art, other drawings can be obtained according to the drawings without any creative effort.
FIG. 1 is a block diagram of an exemplary system of the present application;
FIG. 2 is a flowchart of a method for storing an execution plan according to an embodiment of the present application;
FIG. 3 is a flowchart of a method for searching an execution plan according to an embodiment of the present application;
FIG. 4 is a diagram of an exemplary application scenario in accordance with the present application;
FIG. 5 is a block diagram of a lookup apparatus for executing a plan according to an embodiment of the present application;
fig. 6 is a block diagram of a storage device for executing a plan according to an embodiment of the present application.
Detailed Description
In order to make those skilled in the art better understand the technical solutions in the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.
As is well known, access operations to a database, which may include additions, deletions, reads, modifications to data, etc., may generally be accomplished via the SQ L statement, since the SQ L statement is a descriptive language that specifies what a user is to do without specifying how to do it, a database system may need to formulate an "execution plan" corresponding to the SQ L statement based on the SQ L statement of the user.
In some systems with strict latency requirements (e.g., "On-L Transaction Processing", O L TP), the time consumed for executing the execution plan is relatively short, and the time consumed for generating the execution plan is not negligible.
As mentioned above, in view of the problems existing in the prior art that a relatively large time delay is caused by lexical/syntactic analysis and semantic analysis of an SQ L statement during the process of searching for an execution plan, which results in poor performance of a database system, a commonly used scheme for improving the performance of a database is called "plan cache".
FIG. 1 is a block diagram of AN exemplary system of the present application, wherein, as shown in FIG. 1, the system may include a user terminal 10, a database server 20, and a network 30 for communication between the user terminal 10 and the database server 20, the database server 20 being connected to a database 40. the server 20 may include, but is not limited to, one or more processors or processing units 21, AN I/O interface 22, a memory 23, a bus, and a network adapter 24. the server 20 may communicate with one or more external devices 25 (e.g., a keyboard, a mouse) and a display 26. the network may be, for example, a local area network (L), AN AN network (WAN), and/or a public network, wherein a common storage space may be created in the memory 23 as a plan cache 230 for storing execution plans. in the system, the user terminal 10 sends a request carrying AN SQ L to the database server 20, the database server 20 may look up from the plan cache 230 for execution plans corresponding to the SQ L according to the SQ L statements, may generate execution plans corresponding to the execution plans, and may be stored in a non-volatile storage medium, or may be stored in a non-volatile storage medium, where execution plans may be stored in a non-volatile storage medium, such as a non-volatile storage medium, where execution frequency may be less-volatile, or may be less-volatile, such as may be less-volatile, and may be used, such as is less-a non-volatile, and may be used, such as is, and may be used, or may be used, such as a non-volatile.
Fig. 2 is a flowchart of a storage method of an execution plan according to an embodiment of the present application, where an execution subject of the method may be a database server, and in the flowchart of the method, how to store a generated execution plan in a "plan cache" will be described, where the storage method of the execution plan may include the following steps:
s101, receiving a SQ L statement.
And S102, generating an execution plan corresponding to the SQ L statement.
In the embodiment of the application, based on a pre-formed plan cache, after receiving an SQ L statement for accessing a database, a database server searches whether an execution plan corresponding to the current SQ L statement exists in the plan cache (a specific query process will be described below), if a matched and available execution plan is found, the searched execution plan is executed to implement access to the database, and if a matched and available execution plan is not found, the database server needs to generate a corresponding execution plan according to the current SQ L statement.
The process of how to generate an execution plan (i.e., a "hard parsing" process) broadly includes the following steps:
1) of course, the parsing step of the SQ L sentence also includes identifying the attributes of each word in an SQ L sentence, e.g., the word is a keyword, a word-level quantity, a variable, etc.
2) The SQ L sentence is subjected to semantic analysis, which is mainly used to determine whether a non-existing object is referenced in the SQ L sentence or whether a relevant constraint condition is violated.
3) The optimizer can comprise logic optimization and physical optimization, wherein the logic optimization is to apply various different algorithms and strategies to convert SQ L sentences to generate a plurality of equivalent SQ L sentences, and the physical optimization is to generate respective execution plans according to SQ L sentences obtained by the logic optimization, then calculate a Cost (Cost) for each execution plan, and finally select the execution plan with the minimum Cost (Cost) as the optimal execution plan.
4) And generating a code corresponding to the optimal execution plan according to the selected optimal execution plan.
In summary, for each SQ L statement, if the execution plan corresponding to the SQ L statement cannot be found from the plan cache (plan cache), an execution plan corresponding to the SQ L statement can be generated through the hard parsing process.
And S103, generating a parameterized SQ L statement and a constraint condition corresponding to the SQ L statement according to the SQ L statement.
In the embodiment of the present application, the process of generating the parameterized SQ L statement corresponding to the SQ L statement may specifically include:
a) in computer science, the literal quantity (literal) is a representation (notation) for expressing a fixed value in a code.
For example, assume that a SQ L statement is:
select c1,c2,c3from t1where c1＝1and c2like‘senior％’order by 3limit1,20；
through lexical analysis, the part of speech (such as variables, keywords, word size, etc.) of each word in the SQ L sentence can be determined, and the word size contained in the SQ L sentence is (underlined characters):
select c1,c2,c3from t1where c1＝1and c2like‘senior％’order by3limit1,20；
b) and replacing the literal quantity contained in the SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to the SQ L statement, wherein the parameter contains a sequence number corresponding to the literal quantity.
Continuing with the above example, the resulting parameterized SQ L statement is:
select c1,c2,c3from t1where c1＝@1and c2like@2order by@3limit@4,@5；
for example, the literal quantity corresponding to @1 is '1' after 'c 1', and the literal quantity '1' is the first literal quantity in the SQ L sentence.
In actual use, SQ L statements corresponding to the same parameterized SQ L statement may include a variety of.
select c1,c2,c3from t1where c1＝@1and c2like@2order by@3limit@4,@5；
The SQ L statements corresponding thereto may include:
①select c1,c2,c3from t1where c1＝1and c2like‘senior％’ order by3limit 1,20；
②select c1,c2,c3from t1where c1＝1and c2like‘senior％’ order by2limit 1,20；
among them, the above-mentioned SQ L statements ① and ② differ in the amount of words located after "order by".
In view of the above, in order to implement a scheme for querying an execution plan corresponding to the SQ L statement from the SQ L statement, it is necessary to generate a constraint corresponding to the parameterized SQ L statement while determining the parameterized SQ L statement.
In this embodiment of the application, the process of generating the constraint condition may specifically include:
c) the SQ L statement is semantically parsed to determine the literal volume associated with the constraint contained in the SQ L statement.
Wherein, the step c may specifically include but is not limited to one of the following steps:
the method comprises the steps of firstly, carrying out semantic analysis on the SQ L statement, and determining the word size contained in the SQ L statement and located behind a specific character string as the word size related to a constraint condition, wherein the specific character string comprises an order by or a group by.
For example, for the SQ L statement:
select c1,c2,c3from t1where c1＝1and c2like‘senior％’order by 3limit1,20；
the literal quantity "3" following a particular string "order by" can be determined by semantic parsing as the literal quantity associated with the constraint and the literal quantity associated with the constraint can be determined, where "3" corresponds to the 3 rd parameter in the SQ L statement described above.
And secondly, performing semantic analysis on the SQ L statement, and determining the literal quantity belonging to the precision figures contained in the SQ L statement as the literal quantity related to the constraint conditions.
For example, for two SQ L statements, "select 1.000001" and "select 1.000002" which are different and share the same execution plan, the literal amount of precision numbers "1.000001" and "1.000002" can be determined as the literal amount associated with the constraint.
And thirdly, performing semantic analysis on the SQ L statement, and determining the literal quantity contained in the truth/false condition in the SQ L statement as the literal quantity related to the constraint condition.
For example, if a SQ L statement is:
select*from t1where 1＝0and c1＝2；
where "1 ═ 0" is a constant false condition, the literal quantities "1" and "0" contained in the constant false condition can be determined as literal quantities associated with the constraint condition.
For another example, if the SQ L statement is:
select*from t1where 1＝1and c1＝2；
where "1 ═ 1" is the condition of identity, the literal quantities "1" and "1" contained in the condition of identity can be determined as the literal quantities associated with the constraint conditions.
d) And generating the constraint condition corresponding to the parameterized SQ L statement according to the determined literal quantity related to the constraint condition.
Continuing with the ascending example, for the SQ L statement:
select c1,c2,c3from t1where c1＝1and c2like‘senior％’order by 3limit1,20；
the resulting constraint is "literal amount corresponding to parameter 3 in the parameterized SQ L statement equals 3".
For another example, for the SQ L statement:
select*from t1where 1＝0and c1＝2；
the resulting constraint is "the literal quantity corresponding to parameter 1 in the parameterized SQ L statement does not equal the literal quantity corresponding to parameter 2".
It should be noted that the literal quantity corresponding to the constraint condition included in the SQ L statement may be one or more, and similarly, for an SQ L statement, one or more constraint conditions may be generated, and the present application is not limited thereto.
And S104, mapping the execution plan with the parameterized SQ L statement and mapping and storing the execution plan with the constraint condition.
In this embodiment of the application, the execution plan stored in the plan cache (plan cache) may be, for example, as shown in table 1 below:
table 1:
thus, a plan cache including a mapping relationship among the parameterized SQ L statement, the execution plan, and the constraint condition may be formed.
Fig. 3 is a flowchart of a method for searching an execution plan according to an embodiment of the present application, and fig. 4 is an exemplary application scenario diagram of the present application, and as shown in fig. 3 and fig. 4, the method for searching an execution plan may include the following steps:
s201, receiving a SQ L statement.
And S202, generating a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement comprises parameters corresponding to the literal quantity of the SQ L statement.
In this embodiment, the step S202 may specifically include performing lexical analysis on the SQ L statement to determine a literal quantity included in the SQ L statement, and replacing the literal quantity included in the SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to the SQ L statement, where the parameter includes a sequence number corresponding to the literal quantity.
As shown in FIG. 4, for example, assume that an SQ L statement is:
select c1,c2,c3from t1where c1＝1and c2like‘senior％’order by 3limit1,20；
the parameterized SQ L statements generated corresponding thereto are:
select c1,c2,c3from t1where c1＝@1and c2like@2order by@3limit@4,@5；
in the embodiment of the present application, after generating a parameterized SQ L statement corresponding to the SQ L statement, the method further includes the following steps:
a parameter array is generated that contains literal quantities corresponding to parameters in the parameterized SQ L statement.
For example, for the SQ L statement:
select c1,c2,c3from t1where c1＝1and c2like‘senior％’order by 3limit1,20；
the parameter array generated is:
{1，‘senior％’，3，1，20}。
s203, searching a prestored execution plan and constraint conditions corresponding to the parameterized SQ L statement, and if the execution plan and the constraint conditions are found, entering the step S204.
On the premise of generating the parameter array, the step S203 determines whether the literal quantity corresponding to the constraint condition in the parameter array satisfies the constraint condition according to the parameter array and the constraint condition.
As shown in fig. 4, for example, for the parameterized SQ L statement:
select c1,c2,c3from t1where c1＝@1and c2like@2order by@3limit@4,@5；
if the plan cache has the execution plan corresponding to the plan cache: plan 1and Plan2 (as in table 1 above), where the constraints corresponding to Plan1 are: "the literal amount for the 3 rd parameter is equal to 3", the constraint corresponding to Plan2 is: "the literal amount for the 3 rd parameter is equal to 3".
S204, judging whether the parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions, and if so, entering the step S205.
Since the parameterized SQ L statements "select c1, c2, c3from t1where c1 @1 adc 2like @2order by @3limit @4 @5 @", the corresponding parameter arrays are:
{1，‘senior％’，3，1，20}。
it can be seen that the parameter corresponding to the constraint is @3, and the literal quantity corresponding to the parameter @3 can be determined to be 3 through the parameter array, and it is apparent that the literal quantity "3" corresponding to @3 satisfies the constraint corresponding to Plan1, but does not satisfy the constraint corresponding to Plan 2.
S205: and determining the inquired execution plan as an execution plan to be executed.
In the above example, the queried execution Plan1 is determined as the statement SQ L, "select c1, c2, c3from t1where c1 is 1and c2like 'sensor%' order by 3limit 1,20," corresponding execution Plan to be executed, "and the Plan is executed according to the literal quantity in the parameter array, and the query result is obtained and fed back to the user terminal.
It should be noted that all execution subjects of the steps of the methods provided in the above embodiments may be the same apparatus, or different apparatuses may be used as execution subjects of the methods. For example, the execution subject of step S101 and step S102 may be device 1, and the execution subject of step S103 may be device 2; for another example, the execution subject of step S101 may be device 1, and the execution subjects of step S102 and step S103 may be device 2; and so on.
After receiving the SQ L statement, generating a parameterized SQ L statement corresponding to the SQ L statement, searching whether the prestored execution plan and constraint condition exist, and after finding the execution plan and constraint condition, judging whether the inquired execution plan is usable (i.e., whether the execution plan is to be executed) according to the constraint condition, in the process, the inquiry of the execution plan is based on the parameterized SQ L statement, and whether the inquired execution plan is usable according to the constraint condition corresponding to the execution plan, so that the process of inquiring the execution plan or judging whether the inquired execution plan is usable does not need to be realized by analyzing the SQ L statement, that is, the embodiment of the application avoids the delay of the database system for improving the performance of the database system by analyzing the SQ L statement in the process of searching the execution plan, thereby reducing the performance of the database system.
It should be noted that, in some systems with strict latency requirements (e.g., "On-L ine transactioning processing (O L TP)"), although the time consumed by the step of performing semantic parsing On the SQ L statement in the process of searching the plan cache is short, for the above systems, the latency caused by the step of semantic parsing is not negligible, and particularly, if the database system is under a large pressure, the latency is even more negligible.
Fig. 5 is a block diagram of a lookup apparatus for executing a plan according to an embodiment of the present application. Based on the above search method for the execution plan, the search apparatus 100 for the execution plan provided in this embodiment of the present application may exist in the above database server in a software, hardware, or a combination of software and hardware, and the apparatus 100 may include:
a receiving unit 101 that receives an SQ L statement;
the generating unit 102 is used for generating a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement comprises parameters corresponding to the literal quantity of the SQ L statement;
a search unit 103 for searching an execution plan and a constraint condition which are stored in advance and correspond to the parameterized SQ L statement;
the judging unit 104 is configured to judge whether a parameter corresponding to the constraint condition in the parameterized SQ L statement satisfies the constraint condition when the execution plan and the constraint condition corresponding to the parameterized SQ L statement are found, and determine the inquired execution plan as the execution plan to be executed if the parameter corresponding to the constraint condition in the parameterized SQ L statement satisfies the constraint condition.
In an optional embodiment of the present application, the apparatus 100 further includes:
an array generation unit that generates a parameter array including a literal amount corresponding to a parameter in the parameterized SQ L sentence;
the judging unit 104 judges whether the literal quantity corresponding to the constraint condition in the parameter array satisfies the constraint condition according to the parameter array and the constraint condition.
In an optional embodiment of the present application, the generating unit 102 includes:
a literal quantity determination unit which performs lexical analysis on the SQ L sentence and determines the literal quantity contained in the SQ L sentence;
and the parameter replacing unit is used for replacing the literal quantity contained in the SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to the SQ L statement, wherein the parameter contains a sequence number corresponding to the literal quantity.
Fig. 6 is a block diagram of a storage device for executing a plan according to an embodiment of the present application. Based on the storage method of the execution plan, the storage apparatus 200 of the execution plan provided in the embodiment of the present application may exist in the database server in a software, hardware, or a combination of software and hardware, and the storage apparatus 200 of the execution plan may include:
a receiving unit 201 that receives an SQ L statement;
a first generation unit 202 that generates an execution plan corresponding to the SQ L statement;
a second generation unit 203 for generating a parameterized SQ L statement and a constraint condition corresponding to the SQ L statement from the SQ L statement;
the storage unit 204 maps the execution plan with the parameterized SQ L statement and maps and stores the execution plan with the constraint condition.
In an alternative embodiment of the present application, the first generation unit 202 generates the execution plan corresponding to the SQ L statement when the execution plan corresponding to the SQ L statement is not found.
In an optional embodiment of the present application, the second generating unit 203 includes:
a literal quantity determination unit which performs lexical analysis on the SQ L sentence and determines the literal quantity contained in the SQ L sentence;
a parameter replacing unit, which replaces the literal quantity contained in the SQ L statement with a parameter and generates a parameterized SQ L statement corresponding to the SQ L statement, wherein the parameter contains a serial number corresponding to the literal quantity;
a constraint literal quantity determination unit for performing semantic analysis on the SQ L statement and determining the literal quantity related to the constraint condition contained in the SQ L statement;
and a constraint condition generating unit for generating a constraint condition corresponding to the parameterized SQ L sentence according to the determined literal quantity related to the constraint condition.
In an optional embodiment of the present application, the constraint word size determining unit performs semantic parsing on the SQ L statement, and determines a word size included in the SQ L statement after a specific character string as a word size related to a constraint condition, where the specific character string includes an "order by" or a "group by", or,
the constraint word size determining unit performs semantic analysis on the SQ L sentence, determines the word size belonging to the precision figure included in the SQ L sentence as the word size related to the constraint condition, or,
the constraint literal quantity determining unit carries out semantic analysis on the SQ L statement and determines the literal quantity contained in the truth/false condition in the SQ L statement as the literal quantity related to the constraint condition.
After receiving the SQ L statement, the storage device 100 and the lookup device 200 can search whether a prestored execution plan and a parameterized SQ L statement and constraint conditions corresponding to the parameterized SQ L statement exist by generating a parameterized SQ L statement corresponding to the SQ L statement, and after finding the execution plan and the constraint conditions, determine whether the queried execution plan is usable (i.e., whether the queried execution plan is an execution plan to be executed) according to the constraint conditions.
For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.
As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
It should also be noted that 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 an … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The application may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The application may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.
The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the system embodiment, since it is substantially similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.
The above description is only an example of the present application and is not intended to limit the present application. Various modifications and changes may occur to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.
Claims (12)
1. A method for performing a plan lookup, comprising:
receiving a SQ L statement;
generating a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement comprises parameters corresponding to the literal quantity of the SQ L statement;
searching a prestored execution plan and constraint conditions corresponding to the parameterized SQ L statement, and determining the execution plan and the constraint conditions corresponding to the parameterized SQ L statement, wherein the execution plan and the constraint conditions corresponding to the parameterized SQ L statement comprise the steps of performing lexical analysis on a sample SQ L statement to determine a literal quantity contained in the sample SQ L statement, replacing the literal quantity contained in the sample SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to a sample SQ L statement, wherein the parameter comprises a serial number corresponding to the literal quantity, performing semantic analysis on the sample SQ L statement to determine a literal quantity contained in the sample SQ L statement and related to the constraint conditions, and generating the constraint conditions corresponding to the parameterized SQ L statement according to the determined literal quantity related to the constraint conditions;
and if the query is found, judging whether the parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions, and if so, determining the queried execution plan as the execution plan to be executed.
2. The method of claim 1, wherein after generating a parameterized SQ L statement corresponding to the SQ L statement, prior to finding pre-stored execution plan and constraint conditions corresponding to the parameterized SQ L statement, the method further comprises:
generating a parameter array comprising literal quantities corresponding to parameters in the parameterized SQ L statement;
judging whether the parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions, specifically including:
and judging whether the literal quantity corresponding to the constraint condition in the parameter array meets the constraint condition or not according to the parameter array and the constraint condition.
3. The method as claimed in claim 1, wherein generating a parameterized SQ L statement corresponding to the SQ L statement from the SQ L statement comprises:
performing lexical analysis on the SQ L sentence, and determining the literal quantity contained in the SQ L sentence;
and replacing the literal quantity contained in the SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to the SQ L statement, wherein the parameter contains a sequence number corresponding to the literal quantity.
4. A method of storing an execution plan, comprising:
receiving a SQ L statement;
generating an execution plan corresponding to the SQ L statement;
generating a parameterized SQ L0 statement and a constraint condition corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement and the constraint condition are generated by performing lexical analysis on the SQ L statement to determine a literal quantity contained in the SQ L statement, replacing the literal quantity contained in the SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to the SQ L statement, wherein the parameter contains a sequence number corresponding to the literal quantity, performing semantic analysis on the SQ L statement to determine a literal quantity related to the constraint condition contained in the SQ L statement, and generating the constraint condition corresponding to the parameterized SQ L statement according to the determined literal quantity related to the constraint condition;
mapping the execution plan with the parameterized SQ L statement and mapping and storing the execution plan with the constraints.
5. The method according to claim 4, wherein generating an execution plan corresponding to the SQ L statement comprises:
and when the execution plan corresponding to the SQ L statement is not searched, generating the execution plan corresponding to the SQ L statement.
6. The method according to claim 4, wherein performing semantic parsing on the SQ L statement to determine the literal quantity related to the constraint condition contained in the SQ L statement comprises:
carrying out semantic analysis on the SQ L statement, and determining the literal quantity contained in the SQ L statement and positioned after a specific character string as the literal quantity related to a constraint condition, wherein the specific character string comprises 'order by' or 'group by',
performing semantic analysis on the SQ L sentence, and determining the literal quantity belonging to the precision figures contained in the SQ L sentence as the literal quantity related to the constraint condition, or,
and performing semantic analysis on the SQ L statement, and determining the literal quantity contained in the truth/false condition in the SQ L statement as the literal quantity related to the constraint condition.
7. A lookup apparatus for performing a plan, comprising:
a receiving unit that receives an SQ L statement;
the generating unit generates a parameterized SQ L statement corresponding to the SQ L statement according to the SQ L statement, wherein the parameterized SQ L statement comprises parameters corresponding to the literal quantity of the SQ L statement;
the searching unit is used for searching a prestored execution plan and constraint conditions corresponding to the parameterized SQ L statement and determining the execution plan and constraint conditions corresponding to the parameterized SQ L statement, and comprises the steps of performing lexical analysis on a sample SQ L statement and determining the literal quantity contained in the sample SQ L statement, replacing the literal quantity contained in the sample SQ L statement with a parameter to obtain the parameterized SQ L statement corresponding to the sample SQ L statement, wherein the parameter contains a serial number corresponding to the literal quantity, performing semantic analysis on the sample SQ L statement to determine the literal quantity contained in the sample SQ L statement and related to the constraint conditions, and generating the constraint conditions corresponding to the parameterized SQ L statement according to the determined literal quantity and related to the constraint conditions;
and the judging unit is used for judging whether the parameters corresponding to the constraint conditions in the parameterized SQ L statement meet the constraint conditions or not when the execution plan corresponding to the parameterized SQ L statement and the constraint conditions are found, and if so, determining the inquired execution plan as the execution plan to be executed.
8. The apparatus of claim 7, further comprising:
an array generation unit that generates a parameter array including a literal amount corresponding to a parameter in the parameterized SQ L sentence;
the judging unit judges whether the literal quantity corresponding to the constraint condition in the parameter array meets the constraint condition according to the parameter array and the constraint condition.
9. The apparatus of claim 7, wherein the generating unit comprises:
a literal quantity determination unit which performs lexical analysis on the SQ L sentence and determines the literal quantity contained in the SQ L sentence;
and the parameter replacing unit is used for replacing the literal quantity contained in the SQ L statement with a parameter to obtain a parameterized SQ L statement corresponding to the SQ L statement, wherein the parameter contains a sequence number corresponding to the literal quantity.
10. A storage device that executes a plan, comprising:
a receiving unit that receives an SQ L statement;
a first generation unit that generates an execution plan corresponding to the SQ L statement;
the second generation unit is used for generating a parameterized SQ L statement and a constraint condition corresponding to the SQ L statement according to the SQ L statement and comprises a literal quantity determination unit, a parameter replacement unit, a constrained literal quantity determination unit, a constraint condition generation unit and a constraint condition generation unit, wherein the literal quantity determination unit is used for performing lexical analysis on the SQ L statement and determining the literal quantity contained in the SQ L statement;
and a storage unit which maps the execution plan with the parameterized SQ L statement and maps and stores the execution plan with the constraint condition.
11. The apparatus according to claim 10, wherein the first generation unit generates the execution plan corresponding to the SQ L statement when the execution plan corresponding to the SQ L statement is not found.
12. The apparatus according to claim 10, wherein the constrained font size determining unit performs semantic parsing on the SQ L sentence, determines a font size included in the SQ L sentence after a specific character string as a font size related to the constraint condition, wherein the specific character string includes an order by or a group by, or,
the constraint word size determining unit performs semantic analysis on the SQ L sentence, determines the word size belonging to the precision figure included in the SQ L sentence as the word size related to the constraint condition, or,
the constraint literal quantity determining unit carries out semantic analysis on the SQ L statement and determines the literal quantity contained in the truth/false condition in the SQ L statement as the literal quantity related to the constraint condition.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610576924.5A CN106897343B (en) | 2016-07-20 | 2016-07-20 | Searching method, storing method and device for execution plan |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610576924.5A CN106897343B (en) | 2016-07-20 | 2016-07-20 | Searching method, storing method and device for execution plan |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106897343A CN106897343A (en) | 2017-06-27 |
| CN106897343B true CN106897343B (en) | 2020-08-07 |
Family
ID=59190962
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610576924.5A Active CN106897343B (en) | 2016-07-20 | 2016-07-20 | Searching method, storing method and device for execution plan |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106897343B (en) |
Families Citing this family (16)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107451203B (en) * | 2017-07-07 | 2020-09-01 | 阿里巴巴集团控股有限公司 | Database access method and device |
| CN107508909A (en) * | 2017-09-15 | 2017-12-22 | 无锡南理工科技发展有限公司 | The processing method of multithread real-time management is wireless middleware |
| CN110069522A (en) * | 2017-11-10 | 2019-07-30 | 阿里巴巴集团控股有限公司 | Data query method and apparatus |
| CN108804554B (en) * | 2018-05-22 | 2021-03-05 | 上海达梦数据库有限公司 | Database query method, database query device, server and storage medium |
| CN108763536B (en) * | 2018-05-31 | 2020-04-14 | 阿里巴巴集团控股有限公司 | Database access method and device |
| CN109063170B (en) * | 2018-08-17 | 2021-07-30 | 百度在线网络技术（北京）有限公司 | Intelligent query method, device, terminal and computer readable storage medium |
| CN109241101B (en) * | 2018-08-31 | 2020-06-30 | 阿里巴巴集团控股有限公司 | Database query optimization method and device and computer equipment |
| CN111221840B (en) * | 2018-11-23 | 2023-05-30 | 阿里云计算有限公司 | Data processing method and device, data caching method, storage medium and system |
| CN109684351B (en) * | 2018-12-18 | 2020-11-06 | 上海达梦数据库有限公司 | Execution plan viewing method, device, server and storage medium |
| CN112445817A (en) * | 2019-08-27 | 2021-03-05 | 阿里巴巴集团控股有限公司 | Query instruction execution method and device, electronic equipment and computer readable medium |
| CN111078670B (en) * | 2019-12-19 | 2023-06-20 | 上海达梦数据库有限公司 | Database execution plan clearing method, device, equipment and storage medium |
| CN111506603B (en) * | 2020-04-23 | 2024-03-26 | 上海达梦数据库有限公司 | Data processing method, device, equipment and storage medium |
| CN111639096B (en) * | 2020-05-29 | 2024-03-08 | 北京奇艺世纪科技有限公司 | SQL sentence execution method and device, electronic equipment and storage medium |
| CN111797112B (en) * | 2020-06-05 | 2022-04-01 | 武汉大学 | PostgreSQL preparation statement execution optimization method |
| CN114168620B (en) * | 2022-02-11 | 2022-05-17 | 北京奥星贝斯科技有限公司 | Execution plan processing method and device |
| CN114756578A (en) * | 2022-04-07 | 2022-07-15 | 京东科技信息技术有限公司 | Method and device for determining SQL (structured query language) execution plan |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7933894B2 (en) * | 2007-06-15 | 2011-04-26 | Microsoft Corporation | Parameter-sensitive plans for structural scenarios |
| CN102609451A (en) * | 2012-01-11 | 2012-07-25 | 华中科技大学 | SQL (structured query language) query plan generation method oriented to streaming data processing |
| CN103678589A (en) * | 2013-12-12 | 2014-03-26 | 用友软件股份有限公司 | Database kernel query optimization method based on equivalence class |
Family Cites Families (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8346761B2 (en) * | 2004-08-05 | 2013-01-01 | International Business Machines Corporation | Method and system for data mining for automatic query optimization |
| CN102110110A (en) * | 2009-12-28 | 2011-06-29 | 中国移动通信集团公司 | Method and device for data access based on SOA (Service-Oriented Architecture) |
| TWI507897B (en) * | 2010-05-14 | 2015-11-11 | Alibaba Group Holding Ltd | Search methods and devices |
| CN102426612A (en) * | 2012-01-13 | 2012-04-25 | 广州从兴电子开发有限公司 | Conditional object query method and system |
| CN105224690B (en) * | 2015-10-30 | 2019-06-18 | 上海达梦数据库有限公司 | Generate and select the method and system of the executive plan of the corresponding sentence containing ginseng |
-
2016
- 2016-07-20 CN CN201610576924.5A patent/CN106897343B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7933894B2 (en) * | 2007-06-15 | 2011-04-26 | Microsoft Corporation | Parameter-sensitive plans for structural scenarios |
| CN102609451A (en) * | 2012-01-11 | 2012-07-25 | 华中科技大学 | SQL (structured query language) query plan generation method oriented to streaming data processing |
| CN103678589A (en) * | 2013-12-12 | 2014-03-26 | 用友软件股份有限公司 | Database kernel query optimization method based on equivalence class |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106897343A (en) | 2017-06-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106897343B (en) | Searching method, storing method and device for execution plan | |
| CN107402987B (en) | Full-text retrieval method and distributed NewSQL database system | |
| AU2017208219B2 (en) | Generating a domain ontology using word embeddings | |
| US9959311B2 (en) | Natural language interface to databases | |
| CN104657439B (en) | Structured query statement generation system and method for precise retrieval of natural language | |
| US9448995B2 (en) | Method and device for performing natural language searches | |
| CN104657440B (en) | Structured query statement generation system and method | |
| US20160275148A1 (en) | Database query method and device | |
| US10042921B2 (en) | Robust and readily domain-adaptable natural language interface to databases | |
| US9652472B2 (en) | Service requirement analysis system, method and non-transitory computer readable storage medium | |
| US10303689B2 (en) | Answering natural language table queries through semantic table representation | |
| CN110795455A (en) | Dependency relationship analysis method, electronic device, computer device and readable storage medium | |
| CN106682147A (en) | Mass data based query method and device | |
| WO2019169858A1 (en) | Searching engine technology based data analysis method and system | |
| KR102299744B1 (en) | Method for obtaining data model in knowledge graph, apparatus, device and medium | |
| US20130254193A1 (en) | Distance in Contextual Network Graph | |
| CN113779062A (en) | SQL statement generation method and device, storage medium and electronic equipment | |
| CN114579104A (en) | Data analysis scene generation method, device, equipment and storage medium | |
| US20180189380A1 (en) | Job search engine | |
| CN111159381A (en) | Data searching method and device | |
| JP2016192202A (en) | Collation processing system, method, and program | |
| CN108766507A (en) | A kind of clinical quality index calculating method based on CQL Yu standard information model openEHR | |
| CN114676678A (en) | Structured query language data parsing method and device and electronic equipment | |
| JP2019200582A (en) | Search device, search method, and search program | |
| CN114896269A (en) | Structured query statement detection method and device, electronic equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20200921 Address after: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman, British Islands Patentee after: Innovative advanced technology Co.,Ltd. Address before: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman, British Islands Patentee before: Advanced innovation technology Co.,Ltd. Effective date of registration: 20200921 Address after: Cayman Enterprise Centre, 27 Hospital Road, George Town, Grand Cayman, British Islands Patentee after: Advanced innovation technology Co.,Ltd. Address before: A four-storey 847 mailbox in Grand Cayman Capital Building, British Cayman Islands Patentee before: Alibaba Group Holding Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20210128 Address after: 801-10, Section B, 8th floor, 556 Xixi Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee after: Ant financial (Hangzhou) Network Technology Co.,Ltd. Address before: Ky1-9008 business centre, 27 Hospital Road, Georgetown, grand caiman, UK Patentee before: Innovative advanced technology Co.,Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20210902 Address after: 100020 unit 02, 901, floor 9, unit 1, building 1, No.1, East Third Ring Middle Road, Chaoyang District, Beijing Patentee after: Beijing Aoxing Beisi Technology Co.,Ltd. Address before: 801-10, Section B, 8th floor, 556 Xixi Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee before: Ant financial (Hangzhou) Network Technology Co.,Ltd. |
|
| TR01 | Transfer of patent right |