CN107704511A - A kind of SQL optimization methods and equipment - Google Patents
A kind of SQL optimization methods and equipment Download PDFInfo
- Publication number
- CN107704511A CN107704511A CN201710772704.4A CN201710772704A CN107704511A CN 107704511 A CN107704511 A CN 107704511A CN 201710772704 A CN201710772704 A CN 201710772704A CN 107704511 A CN107704511 A CN 107704511A
- Authority
- CN
- China
- Prior art keywords
- optimized
- query block
- sql statement
- optimization
- query
- 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.)
- Granted
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 methods and equipment, and the application is by determining at least one query block to be optimized in SQL statement；Analysis extraction is carried out to each query block to be optimized respectively, obtains filter condition corresponding to each query block to be optimized；Each filter condition is based respectively on to optimize the corresponding query block to be optimized, obtain the query block after each optimization in the SQL statement, realize based on filtering of the filter condition to corresponding query block to be optimized, reduce subsequently to the amount of calculation of 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
The application is related to computer realm, more particularly to a kind of SQL optimization methods and equipment.
Background technology
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
Ask 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., be the conventional part of SQL applications.For example, with as Common usages
It is that one group of public query block is put into inside name subquery (with as), takes a name, SQL below can be repeated
Using it, avoid computing repeatedly, so as to reach optimization purpose.
In order to general, the filter condition inside query block is generally fewer or does not have, and can typically add when in use
Corresponding filter condition.But in terms of compiling, the process performed, in the invocation point of compiled query block, name subquery (with
As), interim table and the view of materialization are often compiled, or even perform and terminate, and the information of invocation point can not be applied directly to
Filtering optimization is carried out inside query block.
To name subquery (with as), Database Systems are when realizing, if generally name subquery (with as)
An only invocation point, then its containing (inline):Arrived in realization equivalent to the code insertion of name subquery (with as)
Its invocation point, then delete 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：(1) it is, containing (inline) in all references point, containing (inline) it
Afterwards can be with using current optimization.Its shortcoming is that the SQL of query block can be computed repeatedly repeatedly.When not containing (inline), then
Individually perform query block, and by result with interim table or certain caching (cache) form deposit, it is necessary to when directly extract knot
Fruit, rather than repeat, to improve computational efficiency.
Thus, it can be known that the filter condition between single SQL operations is currently primarily directed to, not in SQL statement
Query block between filtering optimization method, therefore, how to handle between the query block in SQL filtering optimization turn into industry
The problem mainly studied.
The content of the invention
The purpose of the application is to provide a kind of SQL optimization methods and equipment, to solve in the prior art to SQL statement
The problem of filtering between interior query block optimizes.
According to the one side of the application, there is provided a kind of SQL optimization methods, wherein, methods described includes：
Determine at least one query block to be optimized in SQL statement；
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, obtains described
The query block after each optimization in SQL statement, correspond to the SQL statement after being optimized.
Further, in the above method, at least one query block to be optimized determined in SQL statement, including：
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 effective filter table of the corresponding referenced column reach
The intermediate representation information of formula；
Travel through and identify the intermediate representation information, obtain at least one query block to be optimized in the SQL statement,
Wherein, the query block to be optimized includes its invocation point, the referenced column of the invocation point and its corresponding referenced column
Effective filter expression.
Further, it is described that analysis extraction is carried out to each query block to be optimized respectively in the above method, obtain
Filter condition corresponding to each query block to be optimized, including：
Each query block to be optimized at least one query block to be optimized is divided respectively
Analysis extraction, at least one referenced column in each query block to be optimized is obtained in all references corresponding to it
Effective filter expression of point；
In each query block to be optimized, respectively by each referenced column in all references corresponding to it
Effective filter expression of point by or computing connect, obtain merging filter expression corresponding to each referenced column, and will
Merge filter expression corresponding to each referenced column by being connected with computing, obtain each query block pair to be optimized
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 the effectively filter expression；And
The father node of the effectively filter expression is traveled through, until root node, is only existed and computing.
Further, it is described to be based respectively on each filter condition to corresponding described to be optimized in the above method
Query block optimizes, and obtains the query block after each optimization in the SQL statement, corresponds to the SQL statement after being optimized,
Including：
In the query block to be optimized corresponding to being shifted onto respectively under each filter condition, described treated to corresponding
The query block of optimization optimizes, and obtains the inquiry after optimizing corresponding to each query block to be optimized in the SQL statement
Block；
Based on the query block after optimization corresponding to each query block to be optimized in the SQL statement, correspondingly obtain excellent
SQL statement after change.
Further, it is described that analysis extraction is carried out to each query block to be optimized respectively in the above method, obtain
Corresponding to each query block to be optimized while filter condition, in addition to：
The SQL statement and at least one query block to be optimized therein are compiled；
It is described to be based respectively on each filter condition the corresponding query block to be optimized is optimized, obtain institute
The query block after each optimization in SQL statement is stated, after corresponding to the SQL statement after being optimized, in addition to：
SQL statement after the optimization is recompiled and performed.
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 optimizations equipment is additionally provided, wherein, the equipment includes：
Determining device, for determining at least one query block to be optimized in SQL statement；
Extraction element, for carrying out analysis extraction to each query block to be optimized respectively, obtain each described treat
Filter condition corresponding to 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, obtain the query block after each optimization in the SQL statement, correspond to the SQL statement after being optimized.
Further, in the said 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 effective filter table of the corresponding referenced column reach
The intermediate representation information of formula；
Travel through and identify the intermediate representation information, obtain at least one query block to be optimized in the SQL statement,
Wherein, the query block to be optimized includes its invocation point, the referenced column of the invocation point and its corresponding referenced column
Effective filter expression.
Further, in the said equipment, the extraction element is used for：
Each query block to be optimized at least one query block to be optimized is divided respectively
Analysis extraction, at least one referenced column in each query block to be optimized is obtained in all references corresponding to it
Effective filter expression of point；
In each query block to be optimized, respectively by each referenced column in all references corresponding to it
Effective filter expression of point by or computing connect, obtain merging filter expression corresponding to each referenced column, and will
Merge filter expression corresponding to each referenced column by being connected with computing, obtain each query block pair to be optimized
The filter condition answered.
Further, in the said equipment, effective filter expression of the referenced column meets following condition：
Variable of one referenced column as the effectively filter expression；And
The father node of the effectively filter expression is traveled through, until root node, is only existed and computing.
Further, in the said equipment, the optimization device is used for：
In the query block to be optimized corresponding to being shifted onto respectively under each filter condition, described treated to corresponding
The query block of optimization optimizes, and obtains the inquiry after optimizing corresponding to each query block to be optimized in the SQL statement
Block；
Based on the query block after optimization corresponding to each query block to be optimized in the SQL statement, correspondingly obtain excellent
SQL statement after change.
Further, in the said equipment, the extraction element is additionally operable to：
The SQL statement and at least one query block to be optimized therein are compiled；
The optimization device is additionally operable to：
SQL statement after the optimization is recompiled and performed.
Further, in the said 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 recording medium is additionally provided, is stored thereon with computer
Executable instruction, wherein, the computer executable instructions cause the processor when being executed by processor：
Determine at least one query block to be optimized in SQL statement；
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, obtains described
The query block after each optimization in SQL statement, correspond to the SQL statement after being optimized.
According to the another aspect of the application, a kind of computer equipment is additionally provided, wherein, including：
Processor；And
It is arranged to store the memory of computer executable instructions, the executable instruction makes the place when executed
Manage device：
Determine at least one query block to be optimized in SQL statement；
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, obtains described
The query block after each optimization in SQL statement, correspond to the SQL statement after being optimized.
Compared with prior art, the application is by determining at least one query block to be optimized in SQL statement；It is right respectively
Each query block to be optimized carries out analysis extraction, obtains filter condition corresponding to each query block to be optimized；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains in the SQL statement
Each optimization after query block, realize based on filtering of the filter condition to corresponding query block to be optimized, after reducing
The continuous amount of calculation to query block, due to having carried out filtering optimization to the query block in SQL statement, and then also correspond to and optimized
SQL statement afterwards, the optimization to SQL statement is also achieved, and then improve the performance of SQL execution.
Brief description of the drawings
By reading the detailed description made to non-limiting example made with reference to the following drawings, the application's is other
Feature, objects and advantages will become more apparent upon：
Fig. 1 shows a kind of schematic flow sheet of SQL optimization methods according to the application one side；
Fig. 2 shows to optimize the structural representation of equipment according to a kind of SQL of the application one side.
Same or analogous reference represents same or analogous part in accompanying drawing.
Embodiment
The application is described in further detail below in conjunction with the accompanying drawings.
In one typical configuration of the application, terminal, the equipment of service network and trusted party include one or more
Processor (CPU), input/output interface, network interface and internal memory.
Internal memory may include computer-readable medium in volatile memory, random access memory (RAM) and/or
The forms such as Nonvolatile memory, such as read-only storage (ROM) or flash memory (flash RAM).Internal 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 instruction, data structure, the module of program or other data.
The example of the storage medium of computer includes, but are not limited to phase transition internal memory (PRAM), static RAM (SRAM), moved
State random access memory (DRAM), other kinds of random access memory (RAM), read-only storage (ROM), electric erasable
Programmable read only memory (EEPROM), fast flash memory bank or other memory techniques, read-only optical disc read-only storage (CD-ROM),
Digital versatile disc (DVD) or other optical storages, magnetic cassette tape, magnetic disk storage or other magnetic storage apparatus or
Any other non-transmission medium, the information that can be accessed by a computing device available for storage.Defined according to herein, computer
Computer-readable recording 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 schematic flow sheet of SQL optimization methods according to the application one side, applied in database
During query filter optimization being carried out to SQL statement, the method comprising the steps of S11, step S12 and step S13, specific steps
For：
The step S11, determine at least one query block to be optimized in SQL statement；In order to determine the needs in SQL
The query block optimized, therefore before the query block in SQL carries out filtering optimization, it is necessary first to from all in SQL
Determine to need the query block to be optimized optimized in query block；Afterwards, the step S12, described treated to each respectively
The query block of optimization carries out analysis extraction, obtain each query block to be optimized corresponding to filter condition, realize to each
The analysis extraction of filter condition in query block to be optimized；Finally, the step S13, it is based respectively on each filtering rod
Part optimizes to the corresponding query block to be optimized, obtains the query block after each optimization in the SQL statement, real
Show based on filtering of the filter condition to corresponding query block to be optimized, reduced subsequently to the amount of calculation of query block, due to
Filtering optimization has been carried out to the query block in SQL statement, and then has also corresponded to the SQL statement after being optimized, has been also achieved 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 can include following at least any one：Son
Inquire about query block, view query block and tables of data query block.Wherein, the subquery block can include anonymous subquery block and name
Subquery block (with as) etc., the tables of data query block can include interim table in database etc..Certainly, this area 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
It the application, should also be included in the application protection domain, and be incorporated herein by reference herein.
Need to carry out the query block for filtering optimization to filter out from SQL statement, the step S11 determines SQL statement
In at least one query block to be optimized, including：
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 invocation point referenced column and the referenced column effective filter expression centre
Represent information；Wherein, the reference of all query blocks and the query block in SQL statement is not only included 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, should
Query block 1 is corresponding with effective filter expression of invocation point 1 and invocation point 2, the corresponding referenced column 1 of invocation point 1 and referenced column 1, draws
Effective mistake of 2 effective filter expression, the corresponding referenced column 3 of invocation point 2 and referenced column 3 is 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 also includes various syntactic units
Essential information, such as SQL statement include table, corresponding to table between row, query block and table and row, table and query block it
Between and various corresponding relations between query block and row etc., data structure of the intermediate representation information record in the inside of database
In, realize the analysis to the SQL statement of acquisition.
Then, the step S11 is traveled through and is identified the intermediate representation information, i.e. the various numbers to the intermediate representation information
Judgement is identified 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
Filter the query block of optimization, determine at least one query block to be optimized in the SQL statement, realize pair
Needs in SQL statement filter the identification and determination of the query block of optimization, 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, it is further, described to treat
The invocation point of the query block of optimization can be one or multiple, and the referenced column of the invocation point can be one, also may be used
To be multiple.
Each query block to be optimized in the embodiment of the present application, finds its invocation point, is then based on each reference first
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 referenced column corresponding to 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>Expression is drawn
It is expression1 with the effective filter expression for being classified as col1 and referenced column col1 cited in point 1；<col 2,
expression 2>Represent invocation point 1 cited in be classified as col2 and referenced column col2 effective filter expression is
expression2。
In the embodiment of the application one, the step S12 carries out analysis extraction to each query block to be optimized respectively,
Filter condition corresponding to each query block to be optimized is obtained, including：
Each query block to be optimized at least one query block to be optimized is divided respectively
Analysis extraction, at least one referenced column in each query block to be optimized is obtained in all references corresponding to it
Effective filter expression of point；
Such as 1, to the name subquery to be optimized in following SQL statement：With a as carry out analysis extraction, the life
Name subquery：A in a as has two invocation points, first invocation point for<F, f=1>, second invocation point for<
F, f=2>, to the name subquery：With a as' is analyzed as follows：
By to above-mentioned name subquery to be optimized：With a as analysis, name to be optimized can be obtained
Inquiry：There are two invocation points in a as, be respectively：Invocation point 1 and its referenced column f and referenced column f effective filter table reach
Formula is that f=1, invocation point 2 and its referenced column f and referenced column f effective filter expression are f=2, is realized to name to be optimized
Subquery：With a as analysis, 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 all invocation points corresponding to it.
In another example 2, analysis extraction is carried out to the name subquery with abc as to be optimized in following SQL statement, should
Name subquery：Abc in abc as has two invocation points, first invocation point be a1<C1, c1=1>,<c2,c2
=3>, second invocation point be a2<C1, c1=2>,<C2, c2=4>, wherein, if invocation point uses the alias of table,
Can be corresponded to Hui Yuanbiao during analysis, example in the following example in a1, a2 can correspond to former table abc；To the name subquery：with
Abc as' is analyzed as follows：
By to above-mentioned name subquery to be optimized：With abc as analysis, the name to be optimized can be obtained
Subquery：There are two invocation points in abc as, be respectively：Effective mistake of invocation point 1 and its referenced column c1 and referenced column c1
Filter expression formula is that a1.c1=1, a2.c1=2, invocation point 2 and its referenced column c2 and referenced column c2 effective filter expression are
A1.c2=3, a2.c2=4, realize to name subquery to be optimized：With abc as analysis, it is to be optimized to obtain this
Query block：Name subquery：The effective filtering of two referenced columns in abc as in all invocation points corresponding to it
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 to filter expression corresponding to each referenced column, it is necessary to carry out Effective judgement, to obtain each reference
Effective filter expression of row, the then effective filter expression for having the referenced column need to meet following condition, just ensure that described
The filter expression of referenced column is effective filter expression：
One referenced column as it is described effectively filter expression variable, i.e., have in the effective expression formula and
Only one reference is classified as variable, and other are constant；And
The father node of the effectively filter expression is traveled through, until root node, only exists and computing, i.e., is having described in traversal
The father node of filter expression is imitated until root node, only with computing (AND), it is impossible to have or computing (OR), logic judgment computing
(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 all invocation points corresponding to it by or computing connect, obtain corresponding to each referenced column
Merge filter expression, and filter expression will be merged corresponding to each referenced column by being connected with computing, obtain each
Filter condition corresponding to the query block to be optimized.For example, because all invocation points have filtering to same row, and then
Effective filter expression just for query block is merged to effective filter expression, wherein concretely comprising the following steps 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 with abc as, referenced column c1 is existed
Effective filter expression of its all references point (a1 and a2) is connected with OR, is obtained merging filter table corresponding to referenced column c1 and is reached
Formula：(c1=1OR c1=2), referenced column c2 is connected in effective filter expression of its all references point (a1 and a2) with OR,
Obtain merging filter expression corresponding to referenced column c2：(c2=3OR c2=4)；Afterwards, by corresponding to each referenced column
Merge filter expression by being connected with computing (AND), obtain 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 extraction of filter condition in abc as；Above-mentioned steps S12 is passed through to each query block to be optimized in SQL
Analysis and extraction, it is possible to achieve to the filter condition in each query block to be optimized analysis extraction.
In another example in above-mentioned such as 1, to the name subquery to be optimized in SQL statement：Described in a as are carried out
After step S12 analysis extraction, the name subquery to be optimized is obtained：With a as filter condition is { f=1OR f
=2 }, realize to name subquery to be optimized：The analysis and extraction of filter condition in a as.
In the embodiment of the application one, all query blocks to be optimized in SQL statement carry out the premise of optimization
It is to ensure the semantic correctness 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
It can be seen that a1 and a2 filter to c1 and c2 respectively, for invocation point a1 referenced column c2 be do not have it is conditional, it is right
For invocation point a2, referenced column c1 is that do not have conditional, therefore the filter condition that table abc effective filter expression obtains must be
The superset (i.e. union) of all references point, therefore filter condition (c1=1 or c2=2) can not be back into abc by we
As, otherwise result is not right.
In the embodiment of the application one, after filter condition corresponding to each query block to be optimized is obtained, the step
S13 is based respectively on each filter condition and the corresponding query block to be optimized is optimized, and obtains the SQL statement
In each optimization after query block, it is corresponding optimized after SQL statement, including：
In the query block to be optimized corresponding to being shifted onto respectively under each filter condition, described treated to corresponding
The query block of optimization optimizes, and obtains the inquiry after optimizing corresponding to each query block to be optimized in the SQL statement
Block；Such as having 2 query blocks 1 and query block 2 to be optimized in SQL statement, filter condition corresponding to difference is the He of filter condition 1
It filter condition 2, then will shift the query block 1 to be optimized under filter condition 1 onto, filtering optimization carried out to the query block 1, and incited somebody to action
Shift the query block 2 to be optimized under filter condition 2 onto, filtering optimization is carried out to the query block 2.It is for example, simple by filter condition
Under be pushed into corresponding to inherently optimize in query block to be optimized, such as, originally without filter condition, elect 1,000,000
Record participates in computing below, after having filter condition, may only select 100,000 records, amount of calculation below can subtract significantly
It is few, the filtering optimization to the query block in SQL statement is realized, so as to reduce calculating and input and output IO from source；Then,
The step S13 is corresponding to obtain based on the query block after optimization corresponding to each query block to be optimized in the SQL statement
SQL statement after optimization, the filtering optimization by query block is realized to reach the purpose optimized to SQL statement, so as to
Improve the performance of SQL statement execution.
For example, the code of the filter condition of the query block after the optimization in SQL statement is obtained as " select*from (is treated excellent
The query block of change) where filter conditions ", below by taking with...as (query block to be optimized) as an example, the SQL after being optimized
Sentence is:
with...as(
select*
From (query block to be optimized)
Where filter conditions)
In the embodiment of the application one, the step S12 carries out analysis extraction to each query block to be optimized respectively,
While obtaining filter condition corresponding to each query block to be optimized, in addition to：
The SQL statement and at least one query block to be optimized therein are compiled；I.e. in SQL statement
When query block to be optimized carries out the generation of filter condition, just at least one to be optimized of the SQL statement and its centering
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, to be also compiled, then have again to optimizing the SQL after query block
The step S13 is based respectively on each filter condition and the corresponding query block to be optimized is optimized, and obtains institute
The query block after each optimization in SQL statement is stated, after corresponding to the SQL statement after being optimized, in addition to：To the optimization
SQL statement afterwards is recompiled and performed, and realizes compiling and execution to the SQL statement after optimization.
Fig. 2 shows to optimize the structural representation of equipment according to a kind of SQL of the application one side, applied in database
During carrying out query filter optimization to SQL statement, the equipment includes determining device 11, extraction element 12 and optimization device
13, be specially：
The determining device 11 is used for：Determine at least one query block to be optimized in SQL statement；In order to determine SQL
In the query block that optimizes of needs, therefore before the query block in SQL carries out filtering optimization, it is necessary first to from SQL
All query blocks in determine to need the query block to be optimized that optimizes；Afterwards, 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 filtering rod corresponding to each query block to be optimized
Part, realize the analysis extraction to the filter condition in each query block to be optimized；Finally, the optimization device 13 is used for：Point
The corresponding query block to be optimized is not optimized based on each filter condition, obtained in the SQL statement
Query block after each optimization, is realized based on filtering of the filter condition to corresponding query block to be optimized, is reduced follow-up
To the amount of calculation 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 can include following at least any one：Son
Inquire about query block, view query block and tables of data query block.Wherein, the subquery block can include anonymous subquery block and name
Subquery block (with as) etc., the tables of data query block can include interim table in database etc..Certainly, this area 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
It the application, should also be included in the application protection domain, and be incorporated herein by reference herein.
Need to carry out the query block for filtering optimization to filter out 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 invocation point referenced column and the referenced column effective filter expression centre
Represent information；Wherein, the reference of all query blocks and the query block in SQL statement is not only included 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, should
Query block 1 is corresponding with effective filter expression of invocation point 1 and invocation point 2, the corresponding referenced column 1 of invocation point 1 and referenced column 1, draws
Effective mistake of 2 effective filter expression, the corresponding referenced column 3 of invocation point 2 and referenced column 3 is 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 also includes various syntactic units
Essential information, such as SQL statement include table, corresponding to table between row, query block and table and row, table and query block it
Between and various corresponding relations between query block and row etc., data structure of the intermediate representation information record in the inside of database
In, realize the analysis to the SQL statement of acquisition.
Then, the determining device 11 is used to travel through and identify the intermediate representation information, i.e., to the intermediate representation information
Various data structures be identified judgement, identification judge be in all query blocks in the SQL in the intermediate representation information
The no query block in the presence of needs filter optimization, determines at least one query block to be optimized in the SQL statement, real
Show the identification and determination of the query block that the needs in SQL statement filter with optimization, wherein, the query block to be optimized
Include effective filter expression of 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 or multiple, and the referenced column of the invocation point can be one
It is individual or multiple.
Each query block to be optimized in the embodiment of the present application, finds its invocation point, is then based on each reference first
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 referenced column corresponding to 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>Expression is drawn
It is expression1 with the effective filter expression for being classified as col1 and referenced column col1 cited in point 1；<col 2,
expression 2>Represent invocation point 1 cited in be classified as col2 and referenced column col2 effective filter expression is
expression2。
In the embodiment of the application one, the extraction element 12 is used for：
Each query block to be optimized at least one query block to be optimized is divided respectively
Analysis extraction, at least one referenced column in each query block to be optimized is obtained in all references corresponding to it
Effective filter expression of point；
Such as 1, to the name subquery to be optimized in following SQL statement：With a as carry out analysis extraction, the life
Name subquery：A in a as has two invocation points, first invocation point for<F, f=1>, second invocation point for<
F, f=2>, to the name subquery：With a as' is analyzed as follows：
By to above-mentioned name subquery to be optimized：With a as analysis, name to be optimized can be obtained
Inquiry：There are two invocation points in a as, be respectively：Invocation point 1 and its referenced column f and referenced column f effective filter table reach
Formula is that f=1, invocation point 2 and its referenced column f and referenced column f effective filter expression are f=2, is realized to name to be optimized
Subquery：With a as analysis, 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 all invocation points corresponding to it.
In another example 2, analysis extraction is carried out to the name subquery with abc as to be optimized in following SQL statement, should
Name subquery：Abc in abc as has two invocation points, first invocation point be a1<C1, c1=1>,<c2,c2
=3>, second invocation point be a2<C1, c1=2>,<C2, c2=4>, wherein, if invocation point uses the alias of table,
Can be corresponded to Hui Yuanbiao during analysis, example in the following example in a1, a2 can correspond to former table abc；To the name subquery：with
Abc as' is analyzed as follows：
By to above-mentioned name subquery to be optimized：With abc as analysis, the name to be optimized can be obtained
Subquery：There are two invocation points in abc as, be respectively：Effective mistake of invocation point 1 and its referenced column c1 and referenced column c1
Filter expression formula is that a1.c1=1, a2.c1=2, invocation point 2 and its referenced column c2 and referenced column c2 effective filter expression are
A1.c2=3, a2.c2=4, realize to name subquery to be optimized：With abc as analysis, it is to be optimized to obtain this
Query block：Name subquery：The effective filtering of two referenced columns in abc as in all invocation points corresponding to it
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 to filter expression corresponding to each referenced column, it is necessary to carry out Effective judgement, to obtain each reference
Effective filter expression of row, the then effective filter expression for having the referenced column need to meet following condition, just ensure that described
The filter expression of referenced column is effective filter expression：
One referenced column as it is described effectively filter expression variable, i.e., have in the effective expression formula and
Only one reference is classified as variable, and other are constant；And
The father node of the effectively filter expression is traveled through, until root node, only exists and computing, i.e., is having described in traversal
The father node of filter expression is imitated until root node, only with computing (AND), it is impossible to have or computing (OR), logic judgment computing
(CASE) branch operations such as.
Then, the extraction element 12 continues on in each query block to be optimized, respectively will be each described
Effective filter expression of referenced column all invocation points corresponding to it by or computing connect, obtain each reference
Merge filter expression corresponding to row, and filter expression will be merged corresponding to each referenced column by being connected with computing,
Obtain filter condition corresponding to each query block to be optimized.For example, because all invocation points have to same row
Filtering, and then effective filter expression just for query block is merged to effective filter expression, wherein concretely comprising the following steps
First in each query block to be optimized, effective filter table of each referenced column in its corresponding all references point is reached respectively
Formula carries out or computing connection (OR), for example, in above-mentioned such as 2, in name subquery to be optimized：, will in abc as
Referenced column c1 is connected in effective filter expression of its all references point (a1 and a2) with OR, obtains merging corresponding to referenced column c1
Filter expression：(c1=1OR c1=2), referenced column c2 is used in effective filter expression of its all references point (a1 and a2)
OR connections, obtain merging filter expression corresponding to referenced column c2：(c2=3OR c2=4)；Afterwards, will each referenced column
Corresponding merging filter expression obtains the name subquery to be optimized by being connected with computing (AND)：with abc
Filter condition corresponding to as：{ (c1=1OR c1=2) AND (c2=3OR c2=4) }, realize that name to be optimized is looked into
Ask：The analysis extraction of filter condition in abc as；Said extracted is passed through to each query block to be optimized in SQL
The analysis and extraction of device 12, it is possible to achieve the analysis extraction to the filter condition in each query block to be optimized.
In another example in above-mentioned such as 1, to the name subquery to be optimized in SQL statement：Described in a as are carried out
After the analysis extraction of extraction element 12, the name subquery to be optimized is obtained：With a as filter condition is { f=
1OR f=2 }, realize to name subquery to be optimized：The analysis and extraction of filter condition in a as.
In the embodiment of the application one, all query blocks to be optimized in SQL statement carry out the premise of optimization
It is to ensure the semantic correctness 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
It can be seen that a1 and a2 filter to c1 and c2 respectively, for invocation point a1 referenced column c2 be do not have it is conditional, it is right
For invocation point a2, referenced column c1 is that do not have conditional, therefore the filter condition that table abc effective filter expression obtains must be
The superset (i.e. union) of all references point, therefore filter condition (c1=1 or c2=2) can not be back into abc by we
As, otherwise result is not right.
In the embodiment of the application one, after filter condition corresponding to each query block to be optimized is obtained, the optimization
Device 13 is used for：
In the query block to be optimized corresponding to being shifted onto respectively under each filter condition, described treated to corresponding
The query block of optimization optimizes, and obtains the inquiry after optimizing corresponding to each query block to be optimized in the SQL statement
Block；Such as having 2 query blocks 1 and query block 2 to be optimized in SQL statement, filter condition corresponding to difference is the He of filter condition 1
It filter condition 2, then will shift the query block 1 to be optimized under filter condition 1 onto, filtering optimization carried out to the query block 1, and incited somebody to action
Shift the query block 2 to be optimized under filter condition 2 onto, filtering optimization is carried out to the query block 2.It is for example, simple by filter condition
Under be pushed into corresponding to inherently optimize in query block to be optimized, such as, originally without filter condition, elect 1,000,000
Record participates in computing below, after having filter condition, may only select 100,000 records, amount of calculation below can subtract significantly
It is few, the filtering optimization to the query block in SQL statement is realized, so as to reduce calculating and input and output IO from source；Then,
The optimization device 13 is corresponding based on the query block after optimization corresponding to each query block to be optimized in the SQL statement
SQL statement after being optimized, the filtering optimization by query block is realized to reach the purpose optimized to SQL statement,
So as to improve the performance of SQL statement execution.
For example, the code of the filter condition of the query block after the optimization in SQL statement is obtained as " select*from (is treated excellent
The query block of change) where filter conditions ", below by taking with...as (query block to be optimized) as an example, the SQL after being optimized
Sentence is:
with...as(
select*
From (query block to be optimized)
Where filter conditions)
In the embodiment of the application one, the extraction element 12 is additionally operable to：
The SQL statement and at least one query block to be optimized therein are compiled；I.e. in SQL statement
When query block to be optimized carries out the generation of filter condition, just at least one to be optimized of the SQL statement and its centering
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, to be also compiled, then have again to optimizing the SQL after query block
The optimization device 13 is used for：SQL statement after the optimization is recompiled and performed, is realized to the SQL languages after optimization
The compiling and execution of sentence.
In addition, according to the another aspect of the application, a kind of computer-readable recording medium is additionally provided, is stored thereon with meter
Calculation machine executable instruction, wherein, the computer executable instructions cause the processor when being executed by processor：
Determine at least one query block to be optimized in SQL statement；
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, obtains described
The query block after each optimization in SQL statement, correspond to the SQL statement after being optimized.
According to the another aspect of the application, a kind of computer equipment is additionally provided, wherein, including：
Processor；And
It is arranged to store the memory of computer executable instructions, the executable instruction makes the place when executed
Manage device：
Determine at least one query block to be optimized in SQL statement；
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, obtains described
The query block after each optimization in SQL statement, correspond to the SQL statement after being optimized.
In summary, the application is by determining at least one query block to be optimized in SQL statement；Respectively to each institute
State query block to be optimized and carry out analysis extraction, obtain filter condition corresponding to each query block to be optimized；Base respectively
The corresponding query block to be optimized is optimized in each filter condition, obtained each in the SQL statement
Query block after optimization, realize based on filtering of the filter condition to corresponding query block to be optimized, reduce subsequently to looking into
The amount of calculation of block is ask, 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, the optimization to SQL statement is also achieved, and then improve 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 application 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 realize steps described above or function by computing device.Similarly, the application
Software program (including related data structure) can be stored in computer readable recording medium storing program for performing, for example, RAM memory,
Magnetically or optically driver or floppy disc and similar devices.In addition, some steps or function of the application can employ hardware to realize, example
Such as, coordinate as with processor so as to perform the circuit of each step or function.
In addition, the part of the application can be applied to computer program product, such as computer program instructions, when its quilt
When computer performs, by the operation of the computer, it can call or provide according to the present processes and/or technical scheme.
And the programmed 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 medias and be 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, the device includes using
Memory in storage computer program instructions and processor for execute program instructions, wherein, when the computer program refers to
When order is by the computing device, method and/or skill of the plant running based on foregoing 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 one exemplary embodiment, Er Qie
In the case of 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, embodiment all should be regarded as exemplary, and be nonrestrictive, scope of the present application is by appended power
Profit requires rather than described above limits, it is intended that all in the implication and scope of the equivalency of claim by falling
Change is included in the application.Any reference in claim should not be considered as to the involved claim of limitation.This
Outside, it is clear that the word of " comprising " one is not excluded for other units or step, and odd number is not excluded for plural number.That is stated in device claim is multiple
Unit or device can also be realized by a unit or device by software or hardware.The first, the second grade word is used for table
Show title, and be not offered as any specific order.
Claims (16)
1. a kind of SQL optimization methods, wherein, methods described includes：
Determine at least one query block to be optimized in SQL statement；
Analysis extraction is carried out to each query block to be optimized respectively, obtained corresponding to each query block to be optimized
Filter condition；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains the SQL languages
The query block after each optimization in sentence, corresponds to the SQL statement after being optimized.
2. the method according to claim 11, wherein, at least one query block to be optimized determined in SQL statement,
Including：
The SQL statement of acquisition is analyzed by morphology, syntax and semantics analysis, obtains including in the SQL statement
Effective filter expression of all query blocks and its invocation point, the referenced column of the invocation point and its corresponding referenced column
Intermediate representation information；
Travel through and identify the intermediate representation information, obtain at least one query block to be optimized in the SQL statement, its
In, the query block to be optimized includes its invocation point, the referenced column of the invocation point and its having for the corresponding referenced column
Imitate filter expression.
3. according to the method for claim 2, wherein, it is described analysis is carried out to each query block to be optimized respectively to carry
Take, obtain filter condition corresponding to each query block to be optimized, including：
Analysis is carried out to each query block to be optimized at least one query block to be optimized respectively to carry
Take, obtain at least one referenced column in each query block to be optimized in all invocation points corresponding to it
Effective filter expression；
In each query block to be optimized, respectively by each referenced column in all invocation points corresponding to it
Effective filter expression by or computing connect, obtain merging filter expression corresponding to each referenced column, and will be each
Merge filter expression corresponding to the referenced column by being connected with computing, obtain corresponding to each query block to be optimized
Filter condition.
4. according to the method for claim 2, wherein, effective filter expression of the referenced column meets following condition：
Variable of one referenced column as the effectively filter expression；And
The father node of the effectively filter expression is traveled through, until root node, is only existed and computing.
5. according to the method for claim 1, wherein, it is described to be based respectively on each filter condition and described treated to corresponding
The query block of optimization optimizes, and obtains the query block after each optimization in the SQL statement, corresponds to after being optimized
SQL statement, including：
In the query block to be optimized corresponding to being shifted onto respectively under each filter condition, to corresponding described to be optimized
Query block optimize, obtain the query block after optimizing corresponding to each query block to be optimized in the SQL statement；
Based on the query block after optimization corresponding to each query block to be optimized in the SQL statement, correspond to after obtaining optimization
SQL statement.
6. according to the method for claim 1, wherein, it is described analysis is carried out to each query block to be optimized respectively to carry
Take, while obtaining filter condition corresponding to each query block to be optimized, in addition to：
The SQL statement and at least one query block to be optimized therein are compiled；
It is described to be based respectively on each filter condition the corresponding query block to be optimized is optimized, obtain described
The query block after each optimization in SQL statement, after corresponding to the SQL statement after being optimized, in addition to：
SQL statement after the optimization is recompiled and performed.
7. method according to any one of claim 1 to 6, 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.
8. a kind of SQL optimizes equipment, wherein, the equipment includes：
Determining device, for determining at least one query block to be optimized in SQL statement；
Extraction element, for carrying out analysis extraction to each query block to be optimized respectively, obtain each described to be optimized
Query block corresponding to filter condition；
Optimize device, the corresponding query block to be optimized optimized for being based respectively on each filter condition,
The query block after each optimization in the SQL statement is obtained, corresponds to the SQL statement after being optimized.
9. equipment according to claim 8, wherein, the determining device is used for：
The SQL statement of acquisition is analyzed by morphology, syntax and semantics analysis, obtains including in the SQL statement
Effective filter expression of all query blocks and its invocation point, the referenced column of the invocation point and its corresponding referenced column
Intermediate representation information；
Travel through and identify the intermediate representation information, obtain at least one query block to be optimized in the SQL statement, its
In, the query block to be optimized includes its invocation point, the referenced column of the invocation point and its having for the corresponding referenced column
Imitate filter expression.
10. equipment according to claim 9, wherein, the extraction element is used for：
Analysis is carried out to each query block to be optimized at least one query block to be optimized respectively to carry
Take, obtain at least one referenced column in each query block to be optimized in all invocation points corresponding to it
Effective filter expression；
In each query block to be optimized, respectively by each referenced column in all invocation points corresponding to it
Effective filter expression by or computing connect, obtain merging filter expression corresponding to each referenced column, and will be each
Merge filter expression corresponding to the referenced column by being connected with computing, obtain corresponding to each query block to be optimized
Filter condition.
11. equipment according to claim 9, wherein, effective filter expression of the referenced column meets following condition：
Variable of one referenced column as the effectively filter expression；And
The father node of the effectively filter expression is traveled through, until root node, is only existed and computing.
12. equipment according to claim 8, wherein, the optimization device is used for：
In the query block to be optimized corresponding to being shifted onto respectively under each filter condition, to corresponding described to be optimized
Query block optimize, obtain the query block after optimizing corresponding to each query block to be optimized in the SQL statement；
Based on the query block after optimization corresponding to each query block to be optimized in the SQL statement, correspond to after obtaining optimization
SQL statement.
13. equipment according to claim 8, wherein, the extraction element is additionally operable to：
The SQL statement and at least one query block to be optimized therein are compiled；
The optimization device is additionally operable to：
SQL statement after the optimization is recompiled and performed.
14. the equipment according to any one of claim 8 to 13, wherein, the query block to be optimized is included below extremely
It is any one of few：
Subquery query block, view query block and tables of data query block.
15. a kind of computer-readable recording medium, is stored thereon with computer executable instructions, wherein, the computer can perform
Instruction causes the processor when being executed by processor：
Determine at least one query block to be optimized in SQL statement；
Analysis extraction is carried out to each query block to be optimized respectively, obtained corresponding to each query block to be optimized
Filter condition；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains the SQL languages
The query block after each optimization in sentence, corresponds to the SQL statement after being optimized.
16. a kind of computer equipment, wherein, including：
Processor；And
It is arranged to store the memory of computer executable instructions, the executable instruction makes the processing when executed
Device：
Determine at least one query block to be optimized in SQL statement；
Analysis extraction is carried out to each query block to be optimized respectively, obtained corresponding to each query block to be optimized
Filter condition；
It is based respectively on each filter condition to optimize the corresponding query block to be optimized, obtains the SQL languages
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 true CN107704511A (en) | 2018-02-16 |
| CN107704511B 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) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108804554A (en) * | 2018-05-22 | 2018-11-13 | 上海达梦数据库有限公司 | A kind of data base query method, device, server and storage medium |
| CN109871338A (en) * | 2019-03-15 | 2019-06-11 | 北京思特奇信息技术股份有限公司 | A kind of date storage method, device and computer equipment |
| CN110597850A (en) * | 2019-08-20 | 2019-12-20 | 北京东方国信科技股份有限公司 | Constant optimization method for improving database query response speed |
| CN110688393A (en) * | 2019-09-29 | 2020-01-14 | 星环信息科技（上海）有限公司 | Query statement optimization method and device, computer equipment and storage medium |
| CN110968593A (en) * | 2019-12-10 | 2020-04-07 | 上海达梦数据库有限公司 | 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 (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20070239669A1 (en) * | 2006-04-06 | 2007-10-11 | Carlos Ordonez | Translator of statistical language programs into sql |
| US20090276395A1 (en) * | 2003-09-23 | 2009-11-05 | Salesforce.Com, Inc. | Query optimization in a multi-tenant database system |
| 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 |
-
2017
- 2017-08-31 CN CN201710772704.4A patent/CN107704511B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090276395A1 (en) * | 2003-09-23 | 2009-11-05 | Salesforce.Com, Inc. | Query optimization in a multi-tenant database system |
| US20070239669A1 (en) * | 2006-04-06 | 2007-10-11 | Carlos Ordonez | Translator of statistical language programs into sql |
| 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 |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108804554A (en) * | 2018-05-22 | 2018-11-13 | 上海达梦数据库有限公司 | A kind of data base query method, device, server and storage medium |
| CN108804554B (en) * | 2018-05-22 | 2021-03-05 | 上海达梦数据库有限公司 | Database query method, database query device, server and storage medium |
| CN109871338A (en) * | 2019-03-15 | 2019-06-11 | 北京思特奇信息技术股份有限公司 | A kind of date storage method, device and computer equipment |
| CN110597850A (en) * | 2019-08-20 | 2019-12-20 | 北京东方国信科技股份有限公司 | Constant optimization method for improving database query response speed |
| CN110688393A (en) * | 2019-09-29 | 2020-01-14 | 星环信息科技（上海）有限公司 | Query statement optimization method and device, computer equipment and storage medium |
| CN110968593A (en) * | 2019-12-10 | 2020-04-07 | 上海达梦数据库有限公司 | Database SQL statement optimization method, device, 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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107704511B (en) | 2019-09-06 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107704511A (en) | A kind of SQL optimization methods and equipment | |
| US9892117B2 (en) | Optimizing relational database queries with multi-table predicate expressions | |
| CN113032362B (en) | Data blood edge analysis method, device, electronic equipment and storage medium | |
| US8700605B1 (en) | Estimating rows returned by recursive queries using fanout | |
| CN104765731B (en) | Database inquiry optimization method and apparatus | |
| CN102270225B (en) | Data change daily record method for supervising and data change daily record supervising device | |
| US20100280991A1 (en) | Method and system for versioning data warehouses | |
| CN106611037A (en) | Method and device for distributed diagram calculation | |
| CN106611044A (en) | SQL optimization method and device | |
| US9406018B2 (en) | Systems and methods for semantic data integration | |
| US10067962B2 (en) | Expression based synopsis of database tables | |
| CN107609011A (en) | The maintaining method and device of a kind of data-base recording | |
| US20130275954A1 (en) | Inter-procedural unreachable code elimination with use graph | |
| CN114297204A (en) | Data storage and retrieval method and device for heterogeneous data source | |
| CN111078728B (en) | Cross-database query method and device in database archiving mode | |
| CN104750743A (en) | System and method for ticking and rechecking transaction files | |
| CN107220265A (en) | A kind of database statement compiling and execution method and device | |
| CN110110034A (en) | A kind of RDF data management method, device and storage medium based on figure | |
| US20170249346A1 (en) | Global filter factor estimation | |
| CN105786478A (en) | Data processing method and device | |
| CN108062329B (en) | Data import method and device | |
| CN102799645A (en) | Security search device and method | |
| CN111723104A (en) | Method, device and system for syntax analysis in data processing system | |
| Fejza et al. | Efficient Enumeration of Recursive Plans in Transformation-based Query Optimizers | |
| CN110020069B (en) | Method and device for calculating access amount |
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. |