CN107704511B - A kind of SQL optimization method and equipment - Google Patents
A kind of SQL optimization method and equipment Download PDFInfo
- Publication number
- CN107704511B CN107704511B CN201710772704.4A CN201710772704A CN107704511B CN 107704511 B CN107704511 B CN 107704511B CN 201710772704 A CN201710772704 A CN 201710772704A CN 107704511 B CN107704511 B CN 107704511B
- Authority
- CN
- China
- Prior art keywords
- optimized
- query block
- sql statement
- referenced column
- optimization
- 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; 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
-
- 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
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)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The purpose of the application is to provide a kind of SQL optimization method and equipment, and the application, which passes through, determines at least one of SQL statement query block to be optimized；Analysis extraction is carried out to each query block to be optimized respectively, obtains the corresponding filter condition of each query block to be optimized；Each filter condition is based respectively on to optimize the corresponding query block to be optimized, query block after obtaining each optimization in the SQL statement, realize the filtering based on filter condition to corresponding query block to be optimized, reduce the subsequent calculation amount to query block, due to having carried out filtering optimization to the query block in SQL statement, and then the SQL statement after being optimized also is corresponded to, the optimization to SQL statement is also achieved, and then improve the performance of SQL execution.
Description
Technical field
This application involves computer field more particularly to a kind of SQL optimization method and equipment.
Background technique
The overall performance of Database Systems depends greatly on the cost and efficiency of the query processing of database.?
For in the inquiry of database, often using query block (Query Block).Query block, including be embedded in structuring and look into
It askes the anonymous subquery inside sentence (Structured Query Language, SQL), name subquery (with as), regard
Scheme (view), tables of data etc. (such as interim table) etc., is the common component part of SQL application.For example, with as Common usage
It is to be put into one group of public query block inside name subquery (with as), takes a name, subsequent SQL can be repeated
It using it, avoids computing repeatedly, to reach optimization purpose.
In order to general, the filter condition inside query block is usually fewer or does not have, and can generally add when in use
Corresponding filter condition.But in terms of compiling, the process executed, in the invocation point of compiled query block, subquery (with is named
As), interim table and the view of materialization are often compiled, or even execution terminates, and the information of invocation point not can be used directly
Optimization is filtered inside query block.
To name subquery (with as), Database Systems are when realizing, if usually name subquery (with as)
Only one invocation point, then its containing (inline): the code insertion that a name subquery (with as) is equivalent in realization arrives
Then its invocation point deletes name subquery (with as).An anonymous subquery is reformed into after containing (inline).Such as
Fruit has multiple invocation points, has the following two kinds strategy: it is (one), containing (inline) in all references point, containing (inline) it
Current optimization can be applied afterwards.The disadvantage is that the SQL of query block can be computed repeatedly repeatedly.When not containing (inline), then
Query block is individually performed, and the form of the interim table of result or certain caching (cache) is stored, knot is directly extracted when needing
Fruit, rather than repeat, to improve computational efficiency.
Thus, it can be known that being currently primarily directed to the filter condition between single SQL operation, there is no in SQL statement
Query block between filtering optimization method, therefore, how to handle between the query block in SQL filtering optimization become industry
The project mainly studied.
Summary of the invention
The purpose of the application is to provide a kind of SQL optimization method and equipment, to solve in the prior art to SQL statement
The problem of filtering between interior query block optimizes.
According to the one aspect of the application, a kind of SQL optimization method is provided, wherein the described method includes:
Determine at least one of SQL statement query block to be optimized；
Analysis extraction is carried out to each query block to be optimized respectively, obtains each query block pair to be optimized
The filter condition answered；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtain described
The query block after each optimization in SQL statement corresponds to the SQL statement after being optimized.
Further, in the above method, at least one of described determining SQL statement query block to be optimized, comprising:
The SQL statement of acquisition is analyzed by morphology, syntax and semantics analysis, obtains including the SQL statement
In all query blocks and its invocation point, the referenced column of the invocation point and its corresponding referenced column effective filtering expression
The intermediate representation information of formula；
It traverses and identifies the intermediate representation information, obtain at least one of described SQL statement query block to be optimized,
Wherein, the query block to be optimized includes the referenced column and its corresponding referenced column of its invocation point, the invocation point
Effective filter expression.
Further, described that analysis extraction is carried out to each query block to be optimized respectively in the above method, it obtains
The corresponding filter condition of each query block to be optimized, comprising:
Each of at least one query block to be optimized query block to be optimized is divided respectively
Analysis is extracted, and obtains referenced column described at least one of each described query block to be optimized in its corresponding all reference
Effective filter expression of point；
In each query block to be optimized, respectively by each referenced column in its corresponding all reference
Effective filter expression of point passes through or operation connection, obtains the corresponding merging filter expression of each referenced column, and will
The corresponding merging filter expression of each referenced column obtains each query block pair to be optimized by connecting with operation
The filter condition answered.
Further, in the above method, effective filter expression of the referenced column meets following condition:
Variable of one referenced column as effective filter expression；And
Traverse the father node of effective filter expression, until root node, there is only with operation.
Further, described to be based respectively on each filter condition to corresponding described to be optimized in the above method
Query block optimizes, the query block after obtaining each optimization in the SQL statement, corresponds to the SQL statement after being optimized,
Include:
To be shifted under each filter condition in the corresponding query block to be optimized respectively, to it is corresponding it is described to
The query block of optimization optimizes, the inquiry after obtaining the corresponding optimization of each of described SQL statement query block to be optimized
Block；
Based on the query block after the corresponding optimization of each of SQL statement query block to be optimized, correspondence obtains excellent
SQL statement after change.
Further, described that analysis extraction is carried out to each query block to be optimized respectively in the above method, it obtains
While each query block to be optimized corresponding filter condition, further includes:
To the SQL statement and its at least one of query block to be optimized be compiled；
It is described to be based respectively on each filter condition the corresponding query block to be optimized is optimized, obtain institute
Query block after stating each optimization in SQL statement, it is corresponding optimized after SQL statement after, further includes:
SQL statement after the optimization is recompiled and executed.
Further, in the above method, the query block to be optimized includes following at least any one:
Subquery query block, view query block and tables of data query block.
According to the another aspect of the application, a kind of SQL optimization equipment is additionally provided, wherein the equipment includes:
Determining device, for determining at least one of SQL statement query block to be optimized；
Extraction element, for carrying out analysis extraction to each query block to be optimized respectively, obtain it is each it is described to
The corresponding filter condition of the query block of optimization；
Optimize device, it is excellent to the corresponding query block progress to be optimized for being based respectively on each filter condition
Change, the query block after obtaining each optimization in the SQL statement, corresponds to the SQL statement after being optimized.
Further, in above equipment, the determining device is used for:
The SQL statement of acquisition is analyzed by morphology, syntax and semantics analysis, obtains including the SQL statement
In all query blocks and its invocation point, the referenced column of the invocation point and its corresponding referenced column effective filtering expression
The intermediate representation information of formula；
It traverses and identifies the intermediate representation information, obtain at least one of described SQL statement query block to be optimized,
Wherein, the query block to be optimized includes the referenced column and its corresponding referenced column of its invocation point, the invocation point
Effective filter expression.
Further, in above equipment, the extraction element is used for:
Each of at least one query block to be optimized query block to be optimized is divided respectively
Analysis is extracted, and obtains referenced column described at least one of each described query block to be optimized in its corresponding all reference
Effective filter expression of point；
In each query block to be optimized, respectively by each referenced column in its corresponding all reference
Effective filter expression of point passes through or operation connection, obtains the corresponding merging filter expression of each referenced column, and will
The corresponding merging filter expression of each referenced column obtains each query block pair to be optimized by connecting with operation
The filter condition answered.
Further, in above equipment, effective filter expression of the referenced column meets following condition:
Variable of one referenced column as effective filter expression；And
Traverse the father node of effective filter expression, until root node, there is only with operation.
Further, in above equipment, the optimization device is used for:
To be shifted under each filter condition in the corresponding query block to be optimized respectively, to it is corresponding it is described to
The query block of optimization optimizes, the inquiry after obtaining the corresponding optimization of each of described SQL statement query block to be optimized
Block；
Based on the query block after the corresponding optimization of each of SQL statement query block to be optimized, correspondence obtains excellent
SQL statement after change.
Further, in above equipment, the extraction element is also used to:
To the SQL statement and its at least one of query block to be optimized be compiled；
The optimization device is also used to:
SQL statement after the optimization is recompiled and executed.
Further, in above equipment, the query block to be optimized includes following at least any one:
Subquery query block, view query block and tables of data query block.
According to the another aspect of the application, a kind of computer readable storage medium is additionally provided, computer is stored thereon with
Executable instruction, wherein the computer executable instructions make processor when being executed by processor:
Determine at least one of SQL statement query block to be optimized；
Analysis extraction is carried out to each query block to be optimized respectively, obtains each query block pair to be optimized
The filter condition answered；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtain described
The query block after each optimization in SQL statement corresponds to the SQL statement after being optimized.
According to the another aspect of the application, a kind of computer equipment is additionally provided, wherein include:
Processor；And
It is arranged to the memory of storage computer executable instructions, the executable instruction makes the place when executed
Manage device:
Determine at least one of SQL statement query block to be optimized；
Analysis extraction is carried out to each query block to be optimized respectively, obtains each query block pair to be optimized
The filter condition answered；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtain described
The query block after each optimization in SQL statement corresponds to the SQL statement after being optimized.
Compared with prior art, the application, which passes through, determines at least one of SQL statement query block to be optimized；It is right respectively
Each query block to be optimized carries out analysis extraction, obtains the corresponding filter condition of each query block to be optimized；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtain in the SQL statement
Each optimization after query block, the filtering based on filter condition to corresponding query block to be optimized is realized, after reducing
The continuous calculation amount to query block, due to having carried out filtering optimization to the query block in SQL statement, and then also correspondence is optimized
SQL statement afterwards also achieves the optimization to SQL statement, and then improves the performance of SQL execution.
Detailed description of the invention
By reading a detailed description of non-restrictive embodiments in the light of the attached drawings below, the application's is other
Feature, objects and advantages will become more apparent upon:
Fig. 1 shows a kind of flow diagram of SQL optimization method according to the application one aspect；
Fig. 2 shows the structural schematic diagrams for optimizing equipment according to a kind of SQL of the application one aspect.
The same or similar appended drawing reference represents the same or similar component in attached drawing.
Specific embodiment
The application is described in further detail with reference to the accompanying drawing.
In a typical configuration of this application, terminal, the equipment of service network and trusted party include one or more
Processor (CPU), input/output interface, network interface and memory.
Memory may include the non-volatile memory in computer-readable medium, random access memory (RAM) and/or
The forms such as Nonvolatile memory, such as read-only memory (ROM) or flash memory (flash RAM).Memory is computer-readable medium
Example.
Computer-readable medium includes permanent and non-permanent, removable and non-removable media can be by any method
Or technology come realize information store.Information can be computer readable instructions, data structure, the module of program or other data.
The example of the storage medium of computer includes, but are not limited to phase change memory (PRAM), static random access memory (SRAM), moves
State random access memory (DRAM), other kinds of random access memory (RAM), read-only memory (ROM), electric erasable
Programmable read only memory (EEPROM), flash memory or other memory techniques, read-only disc read only memory (CD-ROM) (CD-ROM),
Digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape disk storage or other magnetic storage devices or
Any other non-transmission medium, can be used for storage can be accessed by a computing device information.As defined in this article, computer
Readable medium does not include non-temporary computer readable media (transitory media), such as the data-signal and carrier wave of modulation.
Fig. 1 shows a kind of flow diagram of SQL optimization method according to the application one aspect, is applied in database
During carrying out query filter optimization to SQL statement, the method comprising the steps of S11, step S12 and step S13, specific steps
Are as follows:
The step S11 determines at least one of SQL statement query block to be optimized；In order to determine the needs in SQL
The query block optimized, therefore before being filtered optimization to the query block in SQL, it is necessary first to from all in SQL
The query block to be optimized for needing to optimize is determined in query block；Later, the step S12, respectively to it is each it is described to
The query block of optimization carries out analysis extraction, obtains the corresponding filter condition of each query block to be optimized, realizes to each
The analysis of filter condition in query block to be optimized is extracted；Finally, the step S13, is based respectively on each filtering rod
Part optimizes the corresponding query block to be optimized, the query block after obtaining each optimization in the SQL statement, real
Show the filtering based on filter condition to corresponding query block to be optimized, reduces the subsequent calculation amount to query block, due to
Filtering optimization has been carried out to the query block in SQL statement, so also it is corresponding optimized after SQL statement, also achieve pair
The optimization of SQL statement, and then improve the performance of SQL execution.
Here, the query block to be optimized in all embodiments of the application may include following at least any one: son
Inquire query block, view query block and tables of data query block.Wherein, which may include anonymous subquery block and name
Subquery block (with as) etc., the tables of data query block may include the interim table etc. in database.Certainly, this field skill
Other any described query blocks to be optimized that art personnel will be understood that other are existing or be likely to occur from now on are such as applicable to
The application should also be included in the application protection scope, and be incorporated herein by reference.
In order to filter out the query block for needing to be filtered optimization from SQL statement, the step S11 determines SQL statement
At least one of query block to be optimized, comprising:
The SQL statement of acquisition is analyzed by morphology, syntax and semantics analysis, obtains including the SQL statement
In all query blocks and its invocation point, the referenced column of the invocation point and the referenced column effective filter expression centre
Indicate information；It wherein, not only include the reference of all query blocks and the query block in SQL statement in the intermediate representation information
The effective expression formula of point, the referenced column of the invocation point and the referenced column, for example, have query block 1 and query block 2 in SQL statement, it should
Query block 1 is corresponding with invocation point 1 and invocation point 2, and effective filter expression of the corresponding referenced column 1 of invocation point 1 and referenced column 1 draws
2 effective filter expression, effective mistake of the corresponding referenced column 3 of invocation point 2 and referenced column 3 are split with the corresponding referenced column 2 of point 1 and reference
Expression formula is filtered, query block 2 is corresponding with invocation point 3, effective filter expression of the corresponding referenced column 4 of the invocation point 3 and referenced column 4,
Effective filter expression of the corresponding referenced column 5 of invocation point 3 and referenced column 5；The intermediate representation information further includes various syntactic units
Essential information, such as between the corresponding column of the table, the table that include in SQL statement, query block and table and column, table and query block it
Between and various corresponding relationships between query block and column etc., which is recorded in the data structure of the inside of database
In, realize the analysis to the SQL statement of acquisition.
Then, the step S11 traverses and identifies the intermediate representation information, i.e., to the various numbers of the intermediate representation information
Identification judgement is carried out according to structure, identification, which is judged to whether there is in all query blocks in the SQL in the intermediate representation information, to be needed
It is filtered the query block of optimization, determines at least one of described SQL statement query block to be optimized, is realized pair
The identification and determination of the query block for needing to be filtered optimization in SQL statement, wherein the query block to be optimized includes it
Effective filter expression of invocation point, the referenced column of the invocation point and its corresponding referenced column, further, it is described to
The invocation point of the query block of optimization can be one, be also possible to multiple, and the referenced column of the invocation point can be one, can also
To be multiple.
Each of the embodiment of the present application query block to be optimized, finds its invocation point first, is then based on each reference
Point (may have alias) finds effective filtering of the referenced column and the referenced column of all references outside the query block to be optimized
Expression formula, and the corresponding referenced column of each invocation point and its effective filter expression are recorded as a set, for example, to be optimized
A query block the referenced column of invocation point 1 and its collection of effective filter expression be combined into<col 1, expression 1>,<
Col 2, expression 2>... ...<col N, expression N>}, wherein<col 1, expression 1>indicate to draw
Being classified as effective filter expression of col1 and referenced column col1 cited in point 1 is expression1；<col 2,
Effective filter expression that col2 and referenced column col2 are classified as cited in expression 2 > expression invocation point 1 is
expression2。
In one embodiment of the application, the step S12 carries out analysis extraction to each query block to be optimized respectively,
Obtain the corresponding filter condition of each query block to be optimized, comprising:
Each of at least one query block to be optimized query block to be optimized is divided respectively
Analysis is extracted, and obtains referenced column described at least one of each described query block to be optimized in its corresponding all reference
Effective filter expression of point；
Such as 1, to the name subquery to be optimized in following SQL statement: with a as carries out analysis extraction, the life
Name subquery: there are two invocation point, first invocation point is {<f, f=1>} by a in a as, second invocation point be<
F, f=2 > }, to the name subquery: with a as is analyzed as follows:
By to above-mentioned name subquery to be optimized: the analysis of with a as, available name to be optimized
Inquiry: there are two invocation points by with a as, are respectively as follows: effective filtering expression of invocation point 1 and its referenced column f and referenced column f
Formula is that effective filter expression of f=1, invocation point 2 and its referenced column f and referenced column f are f=2, is realized to name to be optimized
Subquery: the analysis of with a as, to obtain the query block to be optimized: name subquery: one in a as is drawn
With the effective filter expression for being listed in its corresponding all invocation point.
In another example 2, analysis extraction is carried out to the name subquery with abc as to be optimized in following SQL statement, it should
Name subquery: abc in abc as there are two invocation point, first invocation point be a1<c1, c1=1>,<c2, c2
=3>}, second invocation point is a2 {<c1, c1=2>,<c2, c2=4>}, wherein if invocation point uses the alias of table,
Can be corresponded to Hui Yuanbiao when analysis, example in the following example in a1, a2 can correspond to former table abc；To the name subquery: with
Abc as's is analyzed as follows:
By to above-mentioned name subquery to be optimized: the analysis of with abc as, the available name to be optimized
Subquery: there are two invocation points by with abc as, are respectively as follows: effective mistake of invocation point 1 and its referenced column c1 and referenced column c1
Filter expression formula is that effective filter expression of a1.c1=1, a2.c1=2, invocation point 2 and its referenced column c2 and referenced column c2 are
A1.c2=3, a2.c2=4 are realized to name subquery to be optimized: the analysis of with abc as, to be optimized to obtain this
Query block: name subquery: the effective filtering of two referenced columns in abc as in its corresponding all invocation point
Expression formula.
In order at least one referenced column obtained in each query block to be optimized in its corresponding all references point
Effective filter expression needs to carry out Effective judgement to the corresponding filter expression of each referenced column, to obtain each reference
Effective filter expression of column, then have effective filter expression of the referenced column that need to meet following condition, just ensures that described
The filter expression of referenced column is effective filter expression:
Variable of one referenced column as effective filter expression, i.e., have in the effective expression formula and
Only one reference is classified as variable, other are constant；And
Traverse the father node of effective filter expression, until root node, there is only with operation, i.e., have described in the traversal
The father node of filter expression is imitated until root node, only with operation (AND), cannot had or operation (OR), logic judgment operation
(CASE) branch operations such as.
Then, the step S12 continues in each query block to be optimized, and each referenced column exists respectively
Effective filter expression of its corresponding all invocation point passes through or operation connection, and it is corresponding to obtain each referenced column
Merge filter expression, and the corresponding merging filter expression of each referenced column is obtained each by connecting with operation
The corresponding filter condition of the query block to be optimized.For example, since all invocation points have filtering to the same column, in turn
Effective filter expression just for query block is merged to effective filter expression, wherein the specific steps are first each
In query block to be optimized, each referenced column is carried out or transported in effective filter expression of its corresponding all references point respectively
Connection (OR) is calculated, for example, in above-mentioned such as 2, in name subquery to be optimized: in abc as, referenced column c1 being existed
(a1 is connected with effective filter expression of a2) with OR its all references point, obtains the corresponding merging filtering expression of referenced column c1
Formula: (c1=1OR c1=2), by referenced column c2 its all references point (a1 is connected with effective filter expression of a2) with OR,
Obtain the corresponding merging filter expression of referenced column c2: (c2=3OR c2=4)；Later, each referenced column is corresponding
Merge filter expression by connecting with operation (AND), obtains the name subquery to be optimized: with abc as couple
The filter condition answered: { (c1=1OR c1=2) AND (c2=3OR c2=4) } realizes the name subquery to be optimized:
The analysis of filter condition in abc as is extracted；The query block to be optimized to each of SQL passes through above-mentioned steps S12
Analysis and extraction, may be implemented the analysis to the filter condition in each query block to be optimized extraction.
In another example in above-mentioned such as 1, to the name subquery to be optimized in SQL statement: described in a as is carried out
After the analysis of step S12 is extracted, obtain the name subquery to be optimized: the filter condition of with a as is { f=1OR f
=2 }, realize to name subquery to be optimized: the analysis and extraction of the filter condition in a as.
In one embodiment of the application, in the premise that all query blocks to be optimized in SQL statement are carried out with optimization
It is the semantic correctness guaranteed in SQL statement, such as to following SQL statement:
with abc as(
select*from table T)
select...
from abc a1,abc a2
Where a1.c1=1 and a2.c2=2
As it can be seen that a1 and a2 are respectively filtered c1 and c2, there is no limit right by referenced column c2 for invocation point a1
For invocation point a2, there is no limit therefore the filter condition that effective filter expression of table abc obtains must be referenced column c1
The superset (i.e. union) of all references point, therefore filter condition (c1=1 or c2=2) cannot be back into abc by we
As, otherwise result is not right.
In one embodiment of the application, after obtaining the corresponding filter condition of each query block to be optimized, the step
S13 is based respectively on each filter condition and optimizes to the corresponding query block to be optimized, obtains the SQL statement
In each optimization after query block, it is corresponding optimized after SQL statement, comprising:
To be shifted under each filter condition in the corresponding query block to be optimized respectively, to it is corresponding it is described to
The query block of optimization optimizes, the inquiry after obtaining the corresponding optimization of each of described SQL statement query block to be optimized
Block；Such as having 2 query blocks 1 and query block 2 to be optimized in SQL statement, corresponding filter condition is 1 He of filter condition
Filter condition 2 will then shift the query block 1 to be optimized under filter condition 1, be filtered optimization to the query block 1, and incited somebody to action
It shifts the query block 2 to be optimized under filter condition 2 onto, optimization is filtered to the query block 2.For example, simple by filter condition
Under be pushed into inherently to optimize in corresponding query block to be optimized, for example, electing 1,000,000 without filter condition originally
Record participates in subsequent operation, after having filter condition, may only select 100,000 records, subsequent calculation amount can subtract significantly
It is few, the optimization of the filtering to the query block in SQL statement is realized, is calculated and input and output IO to be reduced from source；Then,
The step S13 is obtained based on the query block after the corresponding optimization of each of SQL statement query block to be optimized, correspondence
SQL statement after optimization realizes and achievees the purpose that optimize SQL statement by the filtering optimization of query block, thus
Improve the performance of SQL statement execution.
For example, the code for obtaining the filter condition of the query block after the optimization in SQL statement is that " select*from is (to excellent
The query block of change) where filter condition ", the SQL below by taking with...as (query block to be optimized) as an example, after being optimized
Sentence are as follows:
with...as(
select*
From (query block to be optimized)
Where filter condition)
In one embodiment of the application, the step S12 carries out analysis extraction to each query block to be optimized respectively,
While obtaining each query block to be optimized corresponding filter condition, further includes:
To the SQL statement and its at least one of query block to be optimized be compiled；I.e. in SQL statement
When query block to be optimized is filtered the generation of condition, just to the SQL statement and its at least one of it is to be optimized
Query block be compiled, that is, SQL statement and query block to be optimized therein are analyzed and are extracted.Due to
Query block in SQL statement is changed, so, also the SQL optimized after query block is compiled again, then had
The step S13 is based respectively on each filter condition and optimizes to the corresponding query block to be optimized, obtains institute
Query block after stating each optimization in SQL statement, it is corresponding optimized after SQL statement after, further includes: to the optimization
SQL statement afterwards is recompiled and is executed, and realizes compiling and execution to the SQL statement after optimization.
Fig. 2 shows the structural schematic diagrams for optimizing equipment according to a kind of SQL of the application one aspect, are applied in database
During carrying out query filter optimization to SQL statement, which includes determining device 11, extraction element 12 and optimization device
13, specifically:
The determining device 11 is used for: determining at least one of SQL statement query block to be optimized；In order to determine SQL
In the query block that optimizes of needs, therefore before being filtered optimization to the query block in SQL, it is necessary first to from SQL
All query blocks in determine to need the query block to be optimized that optimizes；Later, the extraction element 12 is used for: point
It is other that analysis extraction is carried out to each query block to be optimized, obtain the corresponding filtering rod of each query block to be optimized
Part realizes that the analysis to the filter condition in each query block to be optimized is extracted；Finally, the optimization device 13 is used for: point
The corresponding query block to be optimized is not optimized based on each filter condition, is obtained in the SQL statement
Query block after each optimization realizes the filtering based on filter condition to corresponding query block to be optimized, reduces subsequent
To the calculation amount of query block, due to having carried out filtering optimization to the query block in SQL statement, and then also having corresponded to after being optimized
SQL statement, also achieve the optimization to SQL statement, so improve SQL execution performance.
Here, the query block to be optimized in all embodiments of the application may include following at least any one: son
Inquire query block, view query block and tables of data query block.Wherein, which may include anonymous subquery block and name
Subquery block (with as) etc., the tables of data query block may include the interim table etc. in database.Certainly, this field skill
Other any described query blocks to be optimized that art personnel will be understood that other are existing or be likely to occur from now on are such as applicable to
The application should also be included in the application protection scope, and be incorporated herein by reference.
In order to filter out the query block for needing to be filtered optimization from SQL statement, the determining device 11 is used for:
The SQL statement of acquisition is analyzed by morphology, syntax and semantics analysis, obtains including the SQL statement
In all query blocks and its invocation point, the referenced column of the invocation point and the referenced column effective filter expression centre
Indicate information；It wherein, not only include the reference of all query blocks and the query block in SQL statement in the intermediate representation information
The effective expression formula of point, the referenced column of the invocation point and the referenced column, for example, have query block 1 and query block 2 in SQL statement, it should
Query block 1 is corresponding with invocation point 1 and invocation point 2, and effective filter expression of the corresponding referenced column 1 of invocation point 1 and referenced column 1 draws
2 effective filter expression, effective mistake of the corresponding referenced column 3 of invocation point 2 and referenced column 3 are split with the corresponding referenced column 2 of point 1 and reference
Expression formula is filtered, query block 2 is corresponding with invocation point 3, effective filter expression of the corresponding referenced column 4 of the invocation point 3 and referenced column 4,
Effective filter expression of the corresponding referenced column 5 of invocation point 3 and referenced column 5；The intermediate representation information further includes various syntactic units
Essential information, such as between the corresponding column of the table, the table that include in SQL statement, query block and table and column, table and query block it
Between and various corresponding relationships between query block and column etc., which is recorded in the data structure of the inside of database
In, realize the analysis to the SQL statement of acquisition.
Then, the determining device 11 is for traversing and identifying the intermediate representation information, i.e., to the intermediate representation information
Various data structures carry out identification judgement, identification judge be in all query blocks in the SQL in the intermediate representation information
It is no to there is the query block for needing to be filtered optimization, determine at least one of described SQL statement query block to be optimized, it is real
The identification and determination to the query block for needing to be filtered optimization in SQL statement are showed, wherein the query block to be optimized
Effective filter expression including its invocation point, the referenced column of the invocation point and its corresponding referenced column, further,
The invocation point of the query block to be optimized can be one, be also possible to multiple, and the referenced column of the invocation point can be one
It is a, it is also possible to multiple.
Each of the embodiment of the present application query block to be optimized, finds its invocation point first, is then based on each reference
Point (may have alias) finds effective filtering of the referenced column and the referenced column of all references outside the query block to be optimized
Expression formula, and the corresponding referenced column of each invocation point and its effective filter expression are recorded as a set, for example, to be optimized
A query block the referenced column of invocation point 1 and its collection of effective filter expression be combined into<col 1, expression 1>,<
Col 2, expression 2>... ...<col N, expression N>}, wherein<col 1, expression 1>indicate to draw
Being classified as effective filter expression of col1 and referenced column col1 cited in point 1 is expression1；<col 2,
Effective filter expression that col2 and referenced column col2 are classified as cited in expression 2 > expression invocation point 1 is
expression2。
In one embodiment of the application, the extraction element 12 is used for:
Each of at least one query block to be optimized query block to be optimized is divided respectively
Analysis is extracted, and obtains referenced column described at least one of each described query block to be optimized in its corresponding all reference
Effective filter expression of point；
Such as 1, to the name subquery to be optimized in following SQL statement: with a as carries out analysis extraction, the life
Name subquery: there are two invocation point, first invocation point is {<f, f=1>} by a in a as, second invocation point be<
F, f=2 > }, to the name subquery: with a as is analyzed as follows:
By to above-mentioned name subquery to be optimized: the analysis of with a as, available name to be optimized
Inquiry: there are two invocation points by with a as, are respectively as follows: effective filtering expression of invocation point 1 and its referenced column f and referenced column f
Formula is that effective filter expression of f=1, invocation point 2 and its referenced column f and referenced column f are f=2, is realized to name to be optimized
Subquery: the analysis of with a as, to obtain the query block to be optimized: name subquery: one in a as is drawn
With the effective filter expression for being listed in its corresponding all invocation point.
In another example 2, analysis extraction is carried out to the name subquery with abc as to be optimized in following SQL statement, it should
Name subquery: abc in abc as there are two invocation point, first invocation point be a1<c1, c1=1>,<c2, c2
=3>}, second invocation point is a2 {<c1, c1=2>,<c2, c2=4>}, wherein if invocation point uses the alias of table,
Can be corresponded to Hui Yuanbiao when analysis, example in the following example in a1, a2 can correspond to former table abc；To the name subquery: with
Abc as's is analyzed as follows:
By to above-mentioned name subquery to be optimized: the analysis of with abc as, the available name to be optimized
Subquery: there are two invocation points by with abc as, are respectively as follows: effective mistake of invocation point 1 and its referenced column c1 and referenced column c1
Filter expression formula is that effective filter expression of a1.c1=1, a2.c1=2, invocation point 2 and its referenced column c2 and referenced column c2 are
A1.c2=3, a2.c2=4 are realized to name subquery to be optimized: the analysis of with abc as, to be optimized to obtain this
Query block: name subquery: the effective filtering of two referenced columns in abc as in its corresponding all invocation point
Expression formula.
In order at least one referenced column obtained in each query block to be optimized in its corresponding all references point
Effective filter expression needs to carry out Effective judgement to the corresponding filter expression of each referenced column, to obtain each reference
Effective filter expression of column, then have effective filter expression of the referenced column that need to meet following condition, just ensures that described
The filter expression of referenced column is effective filter expression:
Variable of one referenced column as effective filter expression, i.e., have in the effective expression formula and
Only one reference is classified as variable, other are constant；And
Traverse the father node of effective filter expression, until root node, there is only with operation, i.e., have described in the traversal
The father node of filter expression is imitated until root node, only with operation (AND), cannot had or operation (OR), logic judgment operation
(CASE) branch operations such as.
Then, the extraction element 12 continues in each query block to be optimized, respectively will be each described
Referenced column passes through in effective filter expression of its corresponding all invocation point or operation connects, and obtains each reference
Arrange corresponding merging filter expression, and by the corresponding merging filter expression of each referenced column by connecting with operation,
Obtain the corresponding filter condition of each query block to be optimized.For example, since all invocation points have the same column
Filtering, and then effective filter expression just for query block is merged to effective filter expression, wherein the specific steps are
First in each query block to be optimized, effective filtering to each referenced column in its corresponding all references point is expressed respectively
Formula carries out or operation connection (OR), for example, in above-mentioned such as 2, in name subquery to be optimized:, will in abc as
In its all references point, (a1 is connected with effective filter expression of a2) with OR referenced column c1, obtains the corresponding merging of referenced column c1
Filter expression: (c1=1OR c1=2) uses referenced column c2 in effective filter expression of its all references point (a1 and a2)
OR connection obtains the corresponding merging filter expression of referenced column c2: (c2=3OR c2=4)；Later, by each referenced column
Corresponding merging filter expression obtains the name subquery to be optimized: with abc by connecting with operation (AND)
The corresponding filter condition of as: { (c1=1OR c1=2) AND (c2=3OR c2=4) } realizes that name to be optimized is looked into
Ask: the analysis of the filter condition in abc as is extracted；The query block to be optimized to each of SQL passes through said extracted
The extraction of the analysis to the filter condition in each query block to be optimized may be implemented in the analysis and extraction of device 12.
In another example in above-mentioned such as 1, to the name subquery to be optimized in SQL statement: described in a as is carried out
After the analysis of extraction element 12 is extracted, obtain the name subquery to be optimized: the filter condition of with a as is { f=
1OR f=2 }, it realizes to name subquery to be optimized: the analysis and extraction of the filter condition in a as.
In one embodiment of the application, in the premise that all query blocks to be optimized in SQL statement are carried out with optimization
It is the semantic correctness guaranteed in SQL statement, such as to following SQL statement:
with abc as(
select*from table T)
select...
from abc a1,abc a2
Where a1.c1=1 and a2.c2=2
As it can be seen that a1 and a2 are respectively filtered c1 and c2, there is no limit right by referenced column c2 for invocation point a1
For invocation point a2, there is no limit therefore the filter condition that effective filter expression of table abc obtains must be referenced column c1
The superset (i.e. union) of all references point, therefore filter condition (c1=1 or c2=2) cannot be back into abc by we
As, otherwise result is not right.
In one embodiment of the application, after obtaining the corresponding filter condition of each query block to be optimized, the optimization
Device 13 is used for:
To be shifted under each filter condition in the corresponding query block to be optimized respectively, to it is corresponding it is described to
The query block of optimization optimizes, the inquiry after obtaining the corresponding optimization of each of described SQL statement query block to be optimized
Block；Such as having 2 query blocks 1 and query block 2 to be optimized in SQL statement, corresponding filter condition is 1 He of filter condition
Filter condition 2 will then shift the query block 1 to be optimized under filter condition 1, be filtered optimization to the query block 1, and incited somebody to action
It shifts the query block 2 to be optimized under filter condition 2 onto, optimization is filtered to the query block 2.For example, simple by filter condition
Under be pushed into inherently to optimize in corresponding query block to be optimized, for example, electing 1,000,000 without filter condition originally
Record participates in subsequent operation, after having filter condition, may only select 100,000 records, subsequent calculation amount can subtract significantly
It is few, the optimization of the filtering to the query block in SQL statement is realized, is calculated and input and output IO to be reduced from source；Then,
The optimization device 13 is corresponding based on the query block after the corresponding optimization of each of SQL statement query block to be optimized
SQL statement after being optimized realizes and achievees the purpose that optimize SQL statement by the filtering optimization of query block,
To improve the performance of SQL statement execution.
For example, the code for obtaining the filter condition of the query block after the optimization in SQL statement is that " select*from is (to excellent
The query block of change) where filter condition ", the SQL below by taking with...as (query block to be optimized) as an example, after being optimized
Sentence are as follows:
with...as(
select*
From (query block to be optimized)
Where filter condition)
In one embodiment of the application, the extraction element 12 is also used to:
To the SQL statement and its at least one of query block to be optimized be compiled；I.e. in SQL statement
When query block to be optimized is filtered the generation of condition, just to the SQL statement and its at least one of it is to be optimized
Query block be compiled, that is, SQL statement and query block to be optimized therein are analyzed and are extracted.Due to
Query block in SQL statement is changed, so, also the SQL optimized after query block is compiled again, then had
The optimization device 13 is used for: the SQL statement after the optimization being recompiled and executed, is realized to the SQL language after optimization
The compiling and execution of sentence.
In addition, additionally providing a kind of computer readable storage medium according to the another aspect of the application, it is stored thereon with meter
Calculation machine executable instruction, wherein the computer executable instructions make processor when being executed by processor:
Determine at least one of SQL statement query block to be optimized；
Analysis extraction is carried out to each query block to be optimized respectively, obtains each query block pair to be optimized
The filter condition answered；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtain described
The query block after each optimization in SQL statement corresponds to the SQL statement after being optimized.
According to the another aspect of the application, a kind of computer equipment is additionally provided, wherein include:
Processor；And
It is arranged to the memory of storage computer executable instructions, the executable instruction makes the place when executed
Manage device:
Determine at least one of SQL statement query block to be optimized；
Analysis extraction is carried out to each query block to be optimized respectively, obtains each query block pair to be optimized
The filter condition answered；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtain described
The query block after each optimization in SQL statement corresponds to the SQL statement after being optimized.
In conclusion the application, which passes through, determines at least one of SQL statement query block to be optimized；Respectively to each institute
It states query block to be optimized and carries out analysis extraction, obtain the corresponding filter condition of each query block to be optimized；Base respectively
The corresponding query block to be optimized is optimized in each filter condition, obtains each of described SQL statement
Query block after optimization realizes the filtering based on filter condition to corresponding query block to be optimized, reduces subsequent to looking into
The calculation amount for asking block, due to having carried out filtering optimization to the query block in SQL statement, and then also having corresponded to after being optimized
SQL statement also achieves the optimization to SQL statement, and then improves the performance of SQL execution.
It should be noted that the application can be carried out in the assembly of software and/or software and hardware, for example, can adopt
With specific integrated circuit (ASIC), general purpose computer or any other realized similar to hardware device.In one embodiment
In, the software program of the application can be executed to implement the above steps or functions by processor.Similarly, the application
Software program (including relevant data structure) can be stored in computer readable recording medium, for example, RAM memory,
Magnetic or optical driver or floppy disc and similar devices.In addition, hardware can be used to realize in some steps or function of the application, example
Such as, as the circuit cooperated with processor thereby executing each step or function.
In addition, a part of the application can be applied to computer program product, such as computer program instructions, when its quilt
When computer executes, by the operation of the computer, it can call or provide according to the present processes and/or technical solution.
And the program instruction of the present processes is called, it is possibly stored in fixed or moveable recording medium, and/or pass through
Broadcast or the data flow in other signal-bearing mediums and transmitted, and/or be stored according to described program instruction operation
In the working storage of computer equipment.Here, including a device according to one embodiment of the application, which includes using
Memory in storage computer program instructions and processor for executing program instructions, wherein when the computer program refers to
When enabling by processor execution, method and/or skill of the device operation based on aforementioned multiple embodiments according to the application are triggered
Art scheme.
It is obvious to a person skilled in the art that the application is not limited to the details of above-mentioned exemplary embodiment, Er Qie
In the case where without departing substantially from spirit herein or essential characteristic, the application can be realized in other specific forms.Therefore, no matter
From the point of view of which point, the present embodiments are to be considered as illustrative and not restrictive, and scope of the present application is by appended power
Benefit requires rather than above description limits, it is intended that all by what is fallen within the meaning and scope of the equivalent elements of the claims
Variation is included in the application.Any reference signs in the claims should not be construed as limiting the involved claims.This
Outside, it is clear that one word of " comprising " does not exclude other units or steps, and odd number is not excluded for plural number.That states in device claim is multiple
Unit or device can also be implemented through software or hardware by a unit or device.The first, the second equal words are used to table
Show title, and does not indicate any particular order.
Claims (12)
1. a kind of SQL optimization method, wherein the described method includes:
Determine at least one of SQL statement query block to be optimized, comprising: by morphology, syntax and semantics analysis to acquisition
SQL statement analyzed, obtain including all query blocks and its invocation point, the invocation point in the SQL statement
The intermediate representation information of effective filter expression of referenced column and its corresponding referenced column；It traverses and identifies the middle table
Show information, obtain at least one of described SQL statement query block to be optimized, wherein the query block to be optimized includes
Effective filter expression of its invocation point, the referenced column of the invocation point and its corresponding referenced column；
Analysis extraction is carried out to each query block to be optimized respectively, it is corresponding to obtain each query block to be optimized
Filter condition, comprising: respectively to each of at least one query block to be optimized query block to be optimized
Analysis extraction is carried out, obtains referenced column described at least one of each described query block to be optimized in its corresponding all institute
State effective filter expression of invocation point；In each query block to be optimized, respectively by each referenced column at it
Effective filter expression of corresponding all invocation points passes through or operation connection, obtains the corresponding conjunction of each referenced column
And filter expression, and the corresponding merging filter expression of each referenced column is obtained into each institute by connecting with operation
State the corresponding filter condition of query block to be optimized；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains the SQL language
The query block after each optimization in sentence, corresponds to the SQL statement after being optimized.
2. according to the method described in claim 1, wherein, effective filter expression of the referenced column meets following condition:
Variable of one referenced column as effective filter expression；And
Traverse the father node of effective filter expression, until root node, there is only with operation.
3. according to the method described in claim 1, wherein, it is described be based respectively on each filter condition to it is corresponding it is described to
The query block of optimization optimizes, the query block after obtaining each optimization in the SQL statement, corresponds to after being optimized
SQL statement, comprising:
It will be shifted under each filter condition in the corresponding query block to be optimized respectively, to corresponding described to be optimized
Query block optimize, the query block after obtaining the corresponding optimization of each of described SQL statement query block to be optimized；
Based on the query block after the corresponding optimization of each of SQL statement query block to be optimized, correspond to after obtaining optimization
SQL statement.
It is described analysis is carried out to each query block to be optimized respectively to mention 4. according to the method described in claim 1, wherein
It takes, while obtaining each query block to be optimized corresponding filter condition, further includes:
To the SQL statement and its at least one of query block to be optimized be compiled；
It is described to be based respectively on each filter condition the corresponding query block to be optimized is optimized, it obtains described
The query block after each optimization in SQL statement, it is corresponding optimized after SQL statement after, further includes:
SQL statement after the optimization is recompiled and executed.
5. method according to claim 1 to 4, wherein the query block to be optimized include it is following at least
Any one:
Subquery query block, view query block and tables of data query block.
6. a kind of SQL optimizes equipment, wherein the equipment includes:
Determining device, for determining at least one of SQL statement query block to be optimized, comprising: pass through morphology, grammer and language
The SQL statement of acquisition is analyzed in justice analysis, obtain include all query blocks in the SQL statement and its invocation point,
The intermediate representation information of effective filter expression of the referenced column of the invocation point and its corresponding referenced column；It traverses and knows
The not described intermediate representation information, obtains at least one of described SQL statement query block to be optimized, wherein described to be optimized
Query block include its invocation point, the referenced column of the invocation point and its corresponding referenced column effective filter expression；
Extraction element obtains each described to be optimized for carrying out analysis extraction to each query block to be optimized respectively
The corresponding filter condition of query block, comprising: respectively to described in each of at least one described described query block to be optimized
Query block to be optimized carries out analysis extraction, obtains referenced column described at least one of each described query block to be optimized and exists
Effective filter expression of its corresponding all invocation point；It, respectively will be each in each query block to be optimized
The referenced column passes through in effective filter expression of its corresponding all invocation point or operation connects, and obtains each described
The corresponding merging filter expression of referenced column, and by the corresponding merging filter expression of each referenced column by connecting with operation
It connects, obtains the corresponding filter condition of each query block to be optimized；
Optimize device, the corresponding query block to be optimized optimized for being based respectively on each filter condition,
Query block after obtaining each optimization in the SQL statement corresponds to the SQL statement after being optimized.
7. equipment according to claim 6, wherein effective filter expression of the referenced column meets following condition:
Variable of one referenced column as effective filter expression；And
Traverse the father node of effective filter expression, until root node, there is only with operation.
8. equipment according to claim 6, wherein the optimization device is used for:
It will be shifted under each filter condition in the corresponding query block to be optimized respectively, to corresponding described to be optimized
Query block optimize, the query block after obtaining the corresponding optimization of each of described SQL statement query block to be optimized；
Based on the query block after the corresponding optimization of each of SQL statement query block to be optimized, correspond to after obtaining optimization
SQL statement.
9. equipment according to claim 6, wherein the extraction element is also used to:
To the SQL statement and its at least one of query block to be optimized be compiled；
The optimization device is also used to:
SQL statement after the optimization is recompiled and executed.
10. equipment according to any one of claims 6 to 9, wherein the query block to be optimized include it is following at least
Any one:
Subquery query block, view query block and tables of data query block.
11. a kind of computer readable storage medium, is stored thereon with computer executable instructions, wherein the computer is executable
Instruction makes the processor when being executed by processor:
Determine at least one of SQL statement query block to be optimized, comprising: by morphology, syntax and semantics analysis to acquisition
SQL statement analyzed, obtain including all query blocks and its invocation point, the invocation point in the SQL statement
The intermediate representation information of effective filter expression of referenced column and its corresponding referenced column；It traverses and identifies the middle table
Show information, obtain at least one of described SQL statement query block to be optimized, wherein the query block to be optimized includes
Effective filter expression of its invocation point, the referenced column of the invocation point and its corresponding referenced column；
Analysis extraction is carried out to each query block to be optimized respectively, it is corresponding to obtain each query block to be optimized
Filter condition, comprising: respectively to each of at least one query block to be optimized query block to be optimized
Analysis extraction is carried out, obtains referenced column described at least one of each described query block to be optimized in its corresponding all institute
State effective filter expression of invocation point；In each query block to be optimized, respectively by each referenced column at it
Effective filter expression of corresponding all invocation points passes through or operation connection, obtains the corresponding conjunction of each referenced column
And filter expression, and the corresponding merging filter expression of each referenced column is obtained into each institute by connecting with operation
State the corresponding filter condition of query block to be optimized；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains the SQL language
The query block after each optimization in sentence, corresponds to the SQL statement after being optimized.
12. a kind of computer equipment, wherein include:
Processor；And
It is arranged to the memory of storage computer executable instructions, the executable instruction makes the processing when executed
Device:
Determine at least one of SQL statement query block to be optimized, comprising: by morphology, syntax and semantics analysis to acquisition
SQL statement analyzed, obtain including all query blocks and its invocation point, the invocation point in the SQL statement
The intermediate representation information of effective filter expression of referenced column and its corresponding referenced column；It traverses and identifies the middle table
Show information, obtain at least one of described SQL statement query block to be optimized, wherein the query block to be optimized includes
Effective filter expression of its invocation point, the referenced column of the invocation point and its corresponding referenced column；
Analysis extraction is carried out to each query block to be optimized respectively, it is corresponding to obtain each query block to be optimized
Filter condition, comprising: respectively to each of at least one query block to be optimized query block to be optimized
Analysis extraction is carried out, obtains referenced column described at least one of each described query block to be optimized in its corresponding all institute
State effective filter expression of invocation point；In each query block to be optimized, respectively by each referenced column at it
Effective filter expression of corresponding all invocation points passes through or operation connection, obtains the corresponding conjunction of each referenced column
And filter expression, and the corresponding merging filter expression of each referenced column is obtained into each institute by connecting with operation
State the corresponding filter condition of query block to be optimized；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains the SQL language
The query block after each optimization in sentence, corresponds to the SQL statement after being optimized.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710772704.4A CN107704511B (en) | 2017-08-31 | 2017-08-31 | A kind of SQL optimization method and equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710772704.4A CN107704511B (en) | 2017-08-31 | 2017-08-31 | A kind of SQL optimization method and equipment |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN107704511A CN107704511A (en) | 2018-02-16 |
| CN107704511B true CN107704511B (en) | 2019-09-06 |
Family
ID=61171462
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710772704.4A Active CN107704511B (en) | 2017-08-31 | 2017-08-31 | A kind of SQL optimization method and equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107704511B (en) |
Families Citing this family (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108804554B (en) * | 2018-05-22 | 2021-03-05 | 上海达梦数据库有限公司 | Database query method, database query device, server and storage medium |
| CN109871338B (en) * | 2019-03-15 | 2021-01-29 | 北京思特奇信息技术股份有限公司 | Data storage method and device and computer equipment |
| CN110597850A (en) * | 2019-08-20 | 2019-12-20 | 北京东方国信科技股份有限公司 | Constant optimization method for improving database query response speed |
| CN110688393B (en) * | 2019-09-29 | 2021-01-29 | 星环信息科技(上海)股份有限公司 | Query statement optimization method and device, computer equipment and storage medium |
| CN110968593B (en) * | 2019-12-10 | 2023-10-03 | 上海达梦数据库有限公司 | Database SQL statement optimization method, device, equipment and storage medium |
| CN112286964A (en) * | 2020-11-19 | 2021-01-29 | 上海达梦数据库有限公司 | SQL statement optimization method, device, equipment and storage medium |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102902778A (en) * | 2012-09-28 | 2013-01-30 | 用友软件股份有限公司 | Query sentence optimization device and query sentence optimization method |
| CN106611044A (en) * | 2016-12-02 | 2017-05-03 | 星环信息科技（上海）有限公司 | SQL optimization method and device |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7529728B2 (en) * | 2003-09-23 | 2009-05-05 | Salesforce.Com, Inc. | Query optimization in a multi-tenant database system |
| US8676785B2 (en) * | 2006-04-06 | 2014-03-18 | Teradata Us, Inc. | Translator of statistical language programs into SQL |
-
2017
- 2017-08-31 CN CN201710772704.4A patent/CN107704511B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102902778A (en) * | 2012-09-28 | 2013-01-30 | 用友软件股份有限公司 | Query sentence optimization device and query sentence optimization method |
| CN106611044A (en) * | 2016-12-02 | 2017-05-03 | 星环信息科技（上海）有限公司 | SQL optimization method and device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107704511A (en) | 2018-02-16 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107704511B (en) | A kind of SQL optimization method and equipment | |
| US8700605B1 (en) | Estimating rows returned by recursive queries using fanout | |
| CN109241101B (en) | Database query optimization method and device and computer equipment | |
| US20180081938A1 (en) | System and method for querying data sources | |
| CN106611044B (en) | SQL optimization method and equipment | |
| US20140025684A1 (en) | Indexing and searching a data collection | |
| US9367619B2 (en) | Large scale real-time multistaged analytic system using data contracts | |
| CN104598652B (en) | A kind of data base query method and device | |
| CN109656946B (en) | Multi-table association query method, device and equipment | |
| CN108073401B (en) | Method and device for executing business logic and electronic equipment | |
| CN105550220B (en) | A kind of method and device of the access of heterogeneous system | |
| US20130275954A1 (en) | Inter-procedural unreachable code elimination with use graph | |
| CN111078728B (en) | Cross-database query method and device in database archiving mode | |
| CN107239568B (en) | Distributed index implementation method and device | |
| CN111125090B (en) | Data access method and device | |
| CN107463618B (en) | Index creating method and device | |
| CN112541001A (en) | Data query method, device, storage medium and equipment | |
| US20190303460A1 (en) | Transaction-based pseudo-script generation for scheduling and implementing database schema changes | |
| CN107622070B (en) | Database management method and device | |
| CN107315806B (en) | Embedded storage method and device based on file system | |
| CN105320763A (en) | Method and apparatus for importing contents of XML file into database | |
| CN108062329B (en) | Data import method and device | |
| CN102799645A (en) | Security search device and method | |
| CN114510605A (en) | Data storage method and device, electronic equipment and storage medium | |
| CN112307272A (en) | Method and device for determining relation information between objects, computing equipment and storage medium |
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 | ||
| CP01 | Change in the name or title of a patent holder | ||
| CP01 | Change in the name or title of a patent holder |
Address after: 200233 11-12 / F, building B, 88 Hongcao Road, Xuhui District, Shanghai Patentee after: Star link information technology (Shanghai) Co.,Ltd. Address before: 200233 11-12 / F, building B, 88 Hongcao Road, Xuhui District, Shanghai Patentee before: TRANSWARP TECHNOLOGY (SHANGHAI) Co.,Ltd. |