CN103729379B - Computational methods, method of adjustment and the server of SQL program - Google Patents
Computational methods, method of adjustment and the server of SQL program Download PDFInfo
- Publication number
- CN103729379B CN103729379B CN201210392343.8A CN201210392343A CN103729379B CN 103729379 B CN103729379 B CN 103729379B CN 201210392343 A CN201210392343 A CN 201210392343A CN 103729379 B CN103729379 B CN 103729379B
- Authority
- CN
- China
- Prior art keywords
- optimization
- sql programs
- programs
- sql
- original
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/2454—Optimisation of common expressions
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application is related to a kind of computational methods, method of adjustment and the server of SQL programs.This method includes：Server obtains original SQL programs to be calculated, and the original SQL programs carry identification information；According to the identification information of the original SQL programs, optimization SQL programs corresponding to the identification information is extracted in optimization database；Wherein, the optimization SQL programs are obtained after the optimization processing that the server is decomposed and recombinated to all original SQL programs in raw data base, and the optimization SQL programs include optimization logical consequence；According to the content of the optimization logical consequence of the optimization SQL programs, the optimization SQL programs are calculated, to obtain the result of calculation of the original SQL programs.The embodiment of the present application improves the efficiency calculated original SQL programs, alleviates access of the server to source data and bears.
Description
Technical field
The application is related to data query field, more particularly to a kind of computational methods of SQL SQL programs, tune
Adjusting method and server.
Background technology
With the fast development of internet, substantial amounts of data are generated.Operation to these data, is such as inquired about, and is taken
Substantial amounts of computing resource.And due to the similitude of data query operation, cause computing repeatedly for bulk information.For example, making
For in e-commerce website, to complete data manipulation, 1 ten thousand to two ten thousand SQL will be run daily
(Structured Query Language, SQL) program, operationally, the suitable of all SQL programs is realized by scheduling system
Sequence is dispatched.These SQL programs are appeared to be to solve various traffic issues, but it is substantially for source data
Similar operations, due to the similitude of calculating, cause substantial amounts of compute repeatedly.
Under prior art, the computational methods of SQL programs are：Complete meter is carried out to each SQL programs for needing to run
Calculate.The shortcoming of the computational methods of existing SQL programs is：Many SQL programs are all the similar operations to source data, therefore, right
Each SQL programs carry out complete calculating equivalent to being scanned to source data every time, in the case where source data amount is big,
This computing serious waste computing resource, and the calculating time is extended, computational efficiency is low.
The content of the invention
The purpose of the application is to provide a kind of computational methods, method of adjustment and the server of SQL programs, to realize service
The automatic original SQL programs to computing to be carried out of device, which are adjusted, to be obtained optimizing SQL programs, improves and original SQL programs are calculated
Efficiency, and mitigate server to the access burden of source data.
To achieve the above object, this application provides a kind of computational methods of SQL programs, methods described includes：
Server obtains original SQL programs to be calculated, and the original SQL programs carry identification information；
According to the identification information of the original SQL programs, corresponding excellent of the identification information is extracted in optimization database
Change SQL programs；Wherein, the optimization SQL programs are that the server is carried out to all original SQL programs in raw data base
Obtained after the optimization processing decomposed and recombinated, the optimization SQL programs include optimization logical consequence；
According to the content of the optimization logical consequence of the optimization SQL programs, the optimization SQL programs are calculated, with
Obtain the result of calculation of the original SQL programs.
Present invention also provides a kind of method of adjustment of SQL programs, methods described includes：
Deconsolidation process is carried out using grammar parser according to minimum syntactic structure word to all original SQL programs to obtain
Syntactic node is obtained, and all syntactic nodes after fractionation are assembled into one according to the original grammatical relation between each node
Syntax tree；
To carrying out matching operation between the syntax tree after the assembling of the syntactic nodes of all original SQL programs, obtain
The maximum subtree matched somebody with somebody；
The optimization logical consequence of the calculating logic of the maximum subtree of the matching is stored in the second cache table；
Replaced using the optimization logical consequence of the calculating logic preserved in the second cache table in the original SQL programs
Subprogram corresponding with the maximum subtree of the matching, obtains optimizing SQL programs.
Correspondingly, present invention also provides a kind of server, the server includes：
Acquiring unit, original SQL programs to be calculated are obtained for server, and the original SQL programs carry mark
Information；
Extraction unit, for the identification information according to the original SQL programs, the mark is extracted in optimization database
The corresponding optimization SQL programs of information；Wherein, the optimization SQL programs are the servers to all originals in raw data base
Obtained after the optimization processing that beginning SQL program is decomposed and recombinated, the optimization SQL programs include optimization logical consequence；
Computing unit, for the content of the optimization logical consequence according to the optimization SQL programs, to the optimization SQL journeys
Sequence is calculated, to obtain the result of calculation of the original SQL programs.
Correspondingly, present invention also provides a kind of server, the server includes：
Achievement unit, for using grammar parser to all original SQL programs according to minimum syntactic structure word
Carry out deconsolidation process and obtain syntactic node, and by all syntactic nodes after fractionation according to the original grammer between each node
Relation is assembled into a syntax tree；
Matching unit, for progress between the syntax tree after the syntactic node assembling to all original SQL programs
With computing, the maximum subtree matched；
Memory cell, for the optimization logical consequence of the calculating logic of the maximum subtree of the matching to be stored in into
In two cache tables；
Replacement unit, replaces described for the optimization logical consequence using the calculating logic preserved in the second cache table
Subprogram corresponding with the maximum subtree of the matching in original SQL programs, obtains optimizing SQL programs.
Therefore, the application realizes server and substantial amounts of original SQL programs is decomposed and recombinated, and obtains optimizing SQL
Program, and the optimization logical consequence of the calculating logic public to original SQL programs is stored in the second cache table, and optimization is patrolled
The content of volume result is stored in the first cache table so that the meter to optimization SQL programs is could alternatively be to original SQL calculatings
Calculate, improve the efficiency calculated original SQL programs, and alleviate access of the server to source data and bear.
Brief description of the drawings
A kind of computational methods flow chart for SQL programs that Fig. 1 provides for the embodiment of the present application；
A kind of method of adjustment flow chart for SQL programs that Fig. 2 provides for the embodiment of the present application；
The corresponding tree construction schematic diagram of original SQL programs 1 that Fig. 3 (a) provides for the embodiment of the present application；
The corresponding tree construction schematic diagram of original SQL programs 2 that Fig. 3 (b) provides for the embodiment of the present application；
The original SQL programs 1 and the maximum matched children of original SQL programs 2 that Fig. 3 (c) provides for the embodiment of the present application are shown
It is intended to；
A kind of schematic diagram for server that Fig. 4 provides for the embodiment of the present application；
A kind of schematic diagram for server that Fig. 5 provides for the embodiment of the present application.
Embodiment
Below by drawings and examples, the technical scheme to the application is described in further detail.
The computational methods and server for the SQL programs that the application is provided, by being grouped to original SQL programs and again
Group, extracts calculating logic public in multiple original SQL programs, the optimization of the public calculating logic and calculating logic is patrolled
Collect result to be stored in the second cache table, the content of the optimization logical consequence is stored in the first cache table, using public
The optimization logical consequence of calculating logic replaces the public calculating logic of the matching in original SQL programs, constructs optimization SQL journeys
Sequence, and optimization SQL programs are stored in optimization database.When server is calculated original SQL programs, server root
Corresponding optimization SQL programs are obtained in optimization database according to the identification information of original SQL programs, by optimization SQL programs
Calculating complete calculating to original SQL programs.So as to realize that the automatic original SQL programs to computing to be carried out of server are carried out
Optimization obtains optimizing SQL programs, improves the efficiency calculated original SQL programs, and mitigate access of the server to source data
Burden.
The computational methods and server of the SQL programs that the embodiment of the present application is provided are in practical application, as a kind of new
Computational methods can be applied to, by the big data processing scheme of the syntax analyzer Antlr that the increased income Database Systems supported, taking
When business device is calculated substantial amounts of SQL programs, by the decomposition and restructuring to original SQL programs, multiple SQL programs are extracted
In public calculating logic, and the optimization logical consequence of the public calculating logic and the calculating logic is stored in cache table
In, the part of the matching in original SQL programs is replaced using public optimization logical consequence, optimization SQL programs are constructed.Pass through
The calculating realized to original SQL programs is calculated to SQL programs, the efficiency calculated original SQL programs is improved, and mitigate
Access of the server to source data is born.
As shown in figure 1, a kind of computational methods flow chart of its SQL program provided for the embodiment of the present application.The embodiment
Main body is server, and which describes in detail how original SQL programs are calculated.The meter of the embodiment of the present application SQL programs
Calculation method specifically includes following steps：
Step 101, server obtains original SQL programs to be calculated, and the original SQL programs carry identification information；
Specifically, when server needs to calculate substantial amounts of original SQL programs, initial data can be accessed first
Storehouse, the database that will be calculated is extracted from raw data base.Wherein, institute is saved in raw data base in need
The original SQL programs calculated.Each SQL program has a unique identification information, in raw data base except
Preserve outside SQL programs, also saving the identification information of corresponding SQL programs.
Step 102, according to the identification information of the original SQL programs, the identification information is extracted in optimization database
Corresponding optimization SQL programs；Wherein, the optimization SQL programs are the servers to all original SQL in raw data base
Obtained after the optimization processing that program is decomposed and recombinated, the optimization SQL programs include optimization logical consequence；
Server can optimize adjustment when offline to the original SQL programs in raw data base first, and will
To optimization SQL programs and original SQL programs all according to identical identification information be stored in optimization database in.Wherein, optimize
Save optimization SQL programs and original SQL programs in database, server when offline can to optimization database in preserve
Optimization SQL programs are updated.
Server is to the optimization process of original SQL programs：
1) grammar parser is used, all original SQL programs are carried out at fractionation according to minimum syntactic structure word
Reason obtains syntactic node, and all syntactic nodes after fractionation are assembled into according to the original grammatical relation between each node
One syntax tree；
2) to carrying out matching operation between the syntax tree after the syntactic node assembling of all original SQL programs, obtain
The maximum subtree of matching；
3) the optimization logical consequence of the calculating logic of the maximum subtree of the matching is stored in the second cache table；
4) the original SQL programs are replaced using the optimization logical consequence of the calculating logic preserved in the second cache table
In subprogram corresponding with the maximum subtree of the matching, obtain optimize SQL programs.
Wherein, the matching operation refers to scan up since the leaf node of syntax tree, matches last layer identical father
The child node of node and father node, if unanimously, continuing up layer matching, when upper strata can not find the node of matching, then
Terminate with operation, the subtree that resulting matched node is constituted is exactly the maximum subtree of the matching of two original SQL programs.It is right
In the maximum subtree of the matching, grammatically with integrality, can according to this subtree calculating logic and optimization logic knot
Fruit sets up the second cache table, and the calculating logic of this subtree and optimization logical consequence is put into the second cache table, such institute
There are the original SQL programs with the corresponding subprogram of the subtree can be with public a optimization logical consequence.And for this matching
Maximum subtree calculating logic optimization logical consequence content, then can be stored in the first cache table.
Described first caching tableau format is resource file (Resource File, RCFile) form, cache table it is each
The content of row is the content of the optimization logical consequence of the calculating logic of matched children, is planted so in the corresponding grammer of SQL programs
During calculating logic including the matched children, server just can directly scan the result of calculation from the second cache table, and nothing
Again substantial amounts of source data must be calculated.The cache table of second cache table MySQL forms, wherein saving matched children pair
The corresponding SQL statement of the calculating logic answered, such as matched children.
Step 103, according to the content of the optimization logical consequence of the optimization SQL programs, the optimization SQL programs are carried out
Calculate, to obtain the result of calculation of the original SQL programs.
When calculating optimization SQL programs, computing to the corresponding calculating logic of matched children in optimization SQL programs
Optimization logical consequence when, can directly access the content of the optimization logical consequence in the first cache table.
According to the content of optimization logical consequence, according to the computational methods of normal SQL programs, you can complete to optimization SQL
The calculating of program, so as to complete the calculating to original SQL programs.
The embodiment of the present application obtains original SQL programs to be calculated by server, and the original SQL programs carry mark
Know information；According to the identification information of the original SQL programs, optimization corresponding to the identification information is extracted in optimization database
SQL programs；Wherein, the optimization SQL programs are that the server is divided all original SQL programs in raw data base
Obtained after solution and the optimization processing of restructuring, the optimization SQL programs include optimization logical consequence；According to the optimization SQL journeys
The content of the optimization logical consequence of sequence, is calculated the optimization SQL programs, to obtain the calculating of the original SQL programs
As a result.The efficiency calculated original SQL programs is which thereby enhanced, and alleviates access of the server to source data and is born.
In a preferred embodiment, as shown in figure 1, after step 101, in addition to：
Step 104, according to the identification information of the SQL programs, the mark can not be got in the optimization database
When knowing the corresponding optimization SQL programs of information, the original SQL programs are calculated.
Specifically, if server can not find the corresponding excellent of the original SQL programs to be calculated in optimization database
Change SQL programs, then the program can be calculated according to normal computational methods.
In another preferred embodiment, as shown in figure 1, before step 102, in addition to：
Step 104, processing is optimized to all original SQL programs in raw data base and obtains the optimization SQL journeys
Sequence, and the optimization SQL programs are stored in the optimization database.
Specifically, in off-line case, can be to the institute in raw data base before calculating original SQL programs
There are original SQL programs to optimize processing, if original SQL programs can be optimized, can obtain optimizing SQL programs, and
It is stored in optimization database, otherwise can not obtains optimizing SQL programs.
Above embodiment described the method that server is calculated original SQL programs, the following examples are introduced pair
Original SQL programs optimize the method that adjustment obtains optimizing SQL programs.As shown in Fig. 2 what it was provided for the embodiment of the present application
A kind of method of adjustment flow chart of SQL programs.The executive agent of the embodiment is server, and which describes in detail server pair
The optimization processing that original SQL programs are decomposed and recombinated obtains optimizing the process of SQL programs.The embodiment of the present application is specifically included
Following steps：
Step 201, all original SQL programs are torn open according to minimum syntactic structure word using grammar parser
Office reason obtains syntactic node, and by all syntactic nodes after fractionation according to the original grammatical relation group between each node
Fill as a syntax tree；
Using grammar parser, such as Antlr can be decomposed to each SQL programs, decompose the granularity of minimum, then
Assembled according to the grammatical relation between each granularity, obtain a syntax tree.
For example, the source data table in database includes table r_seller, table s_child_user etc..Wherein table r_seller
Include row pt, row user_id, row nick etc., and table s_child_user includes pt row, row LOGIN_ID etc..
For SQL programs 1：
And SQL programs 2：
Deconsolidation process is carried out according to minimum granularity to SQL programs 1 using grammar parser, and assembling is obtained such as Fig. 3 (a)
Shown syntax tree, deconsolidation process is carried out using grammar parser to SQL programs 2 according to minimum granularity, and assemble obtain as
Syntax tree shown in Fig. 3 (b).Due to the use of grammar parser parsing SQL programs being prior art, it will not be repeated here.
After the tree construction for obtaining original SQL programs, the tree construction according to the identification information of SQL programs can be stored in tree
In structural database.Of course, tree construction can not also be preserved.
Step 202, to carrying out matching fortune between the syntax tree after the syntactic node assembling of all original SQL programs
Calculate, the maximum subtree matched；
As shown in Fig. 3 (a) and Fig. 3 (b), matched since identical leaf node, i.e., from r_seller, pt ,=, '
20111015 ' start, and Upward match, node from, *, where are also identical, then review upwards, and node select is also identical, then
Identical node is just can not find upwards, therefore, maximum of the syntax tree shown in syntax tree and Fig. 3 (b) shown in Fig. 3 (a)
With subtree such as Fig. 3 (c) Suo Shi.Wherein, the alias a in Fig. 3 (a) are that the alias b in table a alias, Fig. 3 (b) are table c
Alias.
Step 203, the optimization logical consequence of the calculating logic of the maximum subtree of the matching is stored in into second to delay
Deposit in table；
The optimization logical consequence of the calculating logic of the maximum subtree of matching is：
tmp_excal_table_20120218_9039e154661ed8fdefc368c2692a6f24a
Specifically, the optimization logical consequence can be stored in the second cache table.And for the optimization logical consequence institute
Corresponding content is then stored in the first cache table.
Step 204, replace described original using the optimization logical consequence of the calculating logic preserved in the second cache table
Subprogram corresponding with the maximum subtree of the matching in SQL programs, obtains optimizing SQL programs.
Replaced and matched in original SQL programs with described using the optimization logical consequence preserved in second cache table
The corresponding subprogram of maximum subtree, obtain optimize SQL programs concrete operations be：
Using the optimization logical consequence described in being preserved in second cache table replace in the syntax tree with it is described excellent
Change the maximum subtree corresponding to logical consequence and optimize branch accordingly, the optimization branch be in the original SQL programs with institute
State the tree structure of the corresponding subprogram of maximum subtree of matching；Scanned from the optimization branch to the root of the syntax tree,
The tree structure of the optimization SQL programs is obtained, so as to obtain the optimization SQL programs.
Specifically, server can read the tree construction of the SQL programs 1 shown in Fig. 3 (a) from tree construction database,
Can be to carrying out being split to minimum particle size to original SQL programs 1.The subtree that can optimize is replaced, calculating logic is replaced with,
Such as select*from r_seller where pt=' 20111015 ' are replaced with：
tmp_exca l_table_20120218_9039e154661ed8fdefc368c2692a6f24a。
Then the optimization branch of original SQL programs 1 is scanned up, if all behaviour to optimizing branch in tree construction
Make, be converted to,
Operation to tmp_excal_table_20120218_9039e154661ed8fdefc368c2692a6f2 4a, by
This obtains optimizing SQL programs, as follows：
Server is obtained after optimization SQL programs, can be by optimization SQL programs and original SQL programs all according to same mark
Information is stored in optimization database.
Thus, the embodiment of the present application, optimizes processing to original SQL programs by above-mentioned steps, has obtained optimization SQL
Program, when server needs to calculate original SQL programs, can directly obtain optimization SQL journeys in optimization database
Sequence, reduces workload, improves operating efficiency.
Correspondingly, the embodiment of the present application additionally provides a kind of server.As shown in figure 4, it is provided for the embodiment of the present application
A kind of server schematic diagram.As shown in figure 4, book server is included with lower unit：
Acquiring unit 401, original SQL programs to be calculated are obtained for server, and the original SQL programs carry mark
Know information；
Extraction unit 402, for the identification information according to the original SQL programs, extracts described in optimization database
Optimization SQL programs corresponding to identification information；Wherein, the optimization SQL programs are the servers to the institute in raw data base
Have what is obtained after the optimization processing that original SQL programs are decomposed and recombinated, the optimization SQL programs include optimization logic knot
Really；
Computing unit 403, for the content of the optimization logical consequence according to the optimization SQL programs, to the optimization SQL
Program is calculated, to obtain the result of calculation of the original SQL programs.
Preferably, computing unit 403 is additionally operable to：According to the identification information of the SQL programs, in the optimization database
When can not get optimization SQL programs corresponding to the identification information, the original SQL programs are calculated.
Preferably, the embodiment of the present application also includes：Optimize unit 404, for believing according to the mark of the original SQL programs
Breath, when can not get optimization SQL programs corresponding to the identification information in the optimization database, to the original SQL
Program is calculated.
Optimization unit 404 is specifically included：Subelement 405 is split, for using grammar parser, to all described original
SQL programs carry out deconsolidation process according to minimum syntactic structure word and obtain syntactic node, and by all grammer sections after fractionation
Point is assembled into a syntax tree according to the original grammatical relation between each node；Coupling subelement 406, for all original
Matching operation is carried out between the syntax tree after the syntactic node assembling of SQL programs, the maximum subtree matched；Preserve son
Unit 407, for the optimization logical consequence of the calculating logic of the maximum subtree of the matching to be stored in into the second cache table
In；Subelement 408 is replaced, replaces described for the optimization logical consequence using the calculating logic preserved in the second cache table
Subprogram corresponding with the maximum subtree of the matching in original SQL programs, obtains optimizing SQL programs.
And replace subelement 408 specifically for：Use the optimization logical consequence described in being preserved in second cache table
Replace in the syntax tree and corresponding to optimize branch, the optimization branch with the maximum subtree corresponding to the optimization logical consequence
It is the tree structure of subprogram corresponding with the maximum subtree of the matching in the original SQL programs；From the optimization branch
Scanned to the root of the syntax tree, the tree structure of the optimization SQL programs is obtained, so as to obtain the optimization SQL programs.
Therefore, the embodiment of the present application obtains the original SQL programs to be calculated by server；According to original SQL journeys
The identification information of sequence, extracts optimization SQL programs corresponding to identification information in optimization database；Wherein, optimization SQL programs are clothes
Obtained after the optimization processing that business device is decomposed and recombinated to all original SQL programs in raw data base；According to described
Optimize the content of the optimization logical consequence of SQL programs, the optimization SQL programs are calculated, to obtain the original SQL journeys
The result of calculation of sequence.The efficiency calculated original SQL programs is which thereby enhanced, and alleviates access of the server to source data
Burden.
Correspondingly, present invention also provides a kind of server, as shown in figure 5, its one kind for being provided for the embodiment of the present application
The schematic diagram of server.The server includes：
Achievement unit 501, for using grammar parser to all original SQL programs according to minimum syntactic structure
Word carries out deconsolidation process and obtains syntactic node, and by all syntactic nodes after fractionation according to the original language between each node
Method relation is assembled into a syntax tree；
Matching unit 502, for being carried out between the syntax tree after the syntactic node assembling to all original SQL programs
Matching operation, the maximum subtree matched；
Memory cell 503, for the optimization logical consequence of the calculating logic of the maximum subtree of the matching to be preserved
In the second cache table；
Replacement unit 504, for being replaced using the optimization logical consequence of the calculating logic preserved in the second cache table
Subprogram corresponding with the maximum subtree of the matching in the original SQL programs, obtains optimizing SQL programs.
Replacement unit 504 specifically for：Replaced using the optimization logical consequence described in being preserved in second cache table
Corresponding with the maximum subtree corresponding to the optimization logical consequence in the syntax tree to optimize branch, the optimization branch is institute
State the tree structure of subprogram corresponding with the maximum subtree of the matching in original SQL programs；From the optimization branch to institute
The root scanning of syntax tree is stated, the tree structure of the optimization SQL programs is obtained, so as to obtain the optimization SQL programs.
Thus, the embodiment of the present application realizes server automatic to wanting by the way that original SQL programs are decomposed and recombinated
The original SQL programs of progress computing, which are adjusted, to be obtained optimizing SQL programs, so as to be counted in server to original SQL programs
The efficiency calculated original SQL programs can be improved, and mitigate server to source data with calculation optimization SQL programs during calculation
Access burden.
Professional should further appreciate that, each example described with reference to the embodiments described herein
Unit and algorithm steps, can be realized with electronic hardware, computer software or the combination of the two, hard in order to clearly demonstrate
The interchangeability of part and software, generally describes the composition and step of each example according to function in the above description.
These functions are performed with hardware or software mode actually, depending on the application-specific and design constraint of technical scheme.
Professional and technical personnel can realize described function to each specific application using distinct methods, but this realize
It is not considered that beyond scope of the present application.
The method that is described with reference to the embodiments described herein can use hardware, computing device the step of algorithm
Software module, or the two combination are implemented.Software module can be placed in random access memory (RAM), internal memory, read-only storage
(ROM), electrically programmable ROM, electrically erasable ROM, register, hard disk, moveable magnetic disc, CD-ROM or technical field
In any other form of storage medium well known to interior.
Above-described embodiment, purpose, technical scheme and beneficial effect to the application have been carried out further
Describe in detail, should be understood that the embodiment that the foregoing is only the application, be not used to limit the application
Protection domain, all any modification, equivalent substitution and improvements within spirit herein and principle, done etc. all should include
Within the protection domain of the application.
Claims (10)
1. a kind of computational methods of SQL SQL programs, it is characterised in that methods described includes：
Server obtains original SQL programs to be calculated, and the original SQL programs carry identification information；
According to the identification information of the original SQL programs, optimization SQL corresponding to the identification information is extracted in optimization database
Program；Wherein, the optimization SQL programs are that the server is decomposed to all original SQL programs in raw data base
With obtained after the optimization processing of restructuring, the optimization SQL programs include optimization logical consequence；
According to the content of the optimization logical consequence of the optimization SQL programs, the optimization SQL programs are calculated, to obtain
The result of calculation of the original SQL programs；
The optimization processing that the server is decomposed and recombinated to all original SQL programs in raw data base is specially：
Using grammar parser, deconsolidation process acquisition is carried out according to minimum syntactic structure word to all original SQL programs
Syntactic node, and all syntactic nodes after fractionation are assembled into a language according to the original grammatical relation between each node
Method tree；
To carrying out matching operation between the syntax tree after the syntactic node assembling of all original SQL programs, matched
Maximum subtree；
The optimization logical consequence of the calculating logic of the maximum subtree of the matching is stored in the second cache table；
Using the optimization logical consequence of the calculating logic preserved in the second cache table replace in the original SQL programs with institute
The corresponding subprogram of maximum subtree of matching is stated, obtains optimizing SQL programs.
2. the computational methods of SQL programs according to claim 1, it is characterised in that the acquisition original SQL to be calculated
After program, in addition to：According to the identification information of the original SQL programs, institute can not be got in the optimization database
When stating optimization SQL programs corresponding to identification information, the original SQL programs are calculated.
3. the computational methods of SQL programs according to claim 1, it is characterised in that methods described also includes：
The content of the optimization logical consequence is deposited in the first cache table.
4. the computational methods of SQL programs according to claim 1, it is characterised in that described to be protected using in the second cache table
The optimization logical consequence for the calculating logic deposited is replaced corresponding with the maximum subtree of the matching in the original SQL programs
Subprogram, obtaining optimization SQL programs is specially：
Replaced in the syntax tree and patrolled with the optimization using the optimization logical consequence described in being preserved in second cache table
Maximum subtree corresponding to volume result optimizes branch accordingly, the optimization branch be in the original SQL programs with described
The tree structure for the corresponding subprogram of maximum subtree matched somebody with somebody；
Scanned from the optimization branch to the root of the syntax tree, obtain the tree structure of the optimization SQL programs, so that
To the optimization SQL programs.
5. the computational methods of SQL programs according to claim 1, it is characterised in that described to use second cache table
With the maximum subtree corresponding to the optimization logical consequence in the described optimization logical consequence replacement syntax tree of middle preservation
Also include before corresponding optimization branch：
Deconsolidation process is carried out according to minimum syntactic structure using grammar parser original SQL programs described to each, and
All syntactic nodes after fractionation are assembled into a syntax tree according to the grammatical relation between each granularity.
6. a kind of method of adjustment of SQL SQL programs, it is characterised in that methods described includes：
Deconsolidation process is carried out according to minimum syntactic structure word to all original SQL programs using grammar parser and obtains grammer section
Point, and all syntactic nodes after fractionation are assembled into a syntax tree according to the original grammatical relation between each node；
To carrying out matching operation between the syntax tree after the syntactic node assembling of all original SQL programs, matched
Maximum subtree；
The optimization logical consequence of the calculating logic of the maximum subtree of the matching is stored in the second cache table；
Using the optimization logical consequence of the calculating logic preserved in the second cache table replace in the original SQL programs with institute
The corresponding subprogram of maximum subtree of matching is stated, obtains optimizing SQL programs.
7. the method for adjustment of SQL SQL programs according to claim 6, it is characterised in that described to use
The optimization logical consequence of the calculating logic preserved in second cache table is replaced in the original SQL programs and matched with described
The corresponding subprogram of maximum subtree, obtaining optimization SQL programs is specially：
Replaced in the syntax tree and patrolled with the optimization using the optimization logical consequence described in being preserved in second cache table
Maximum subtree corresponding to volume result optimizes branch accordingly, the optimization branch be in the original SQL programs with described
The tree structure for the corresponding subprogram of maximum subtree matched somebody with somebody；
Scanned from the optimization branch to the root of the syntax tree, obtain the tree structure of the optimization SQL programs, so that
To the optimization SQL programs.
8. the method for adjustment of SQL SQL programs according to claim 6, it is characterised in that described to use
With the optimization logical consequence institute in the described optimization logical consequence replacement syntax tree preserved in second cache table
Corresponding maximum subtree also includes before optimizing branch accordingly：
Deconsolidation process is carried out according to minimum syntactic structure using grammar parser original SQL programs described to each, and
All syntactic nodes after fractionation are assembled into a syntax tree according to the grammatical relation between each granularity.
9. a kind of server, it is characterised in that the server includes：
Acquiring unit, original SQL programs to be calculated are obtained for server, and the original SQL programs carry identification information；
Extraction unit, for the identification information according to the original SQL programs, the identification information is extracted in optimization database
Corresponding optimization SQL programs；Wherein, the optimization SQL programs are the servers to all original SQL in raw data base
Obtained after the optimization processing that program is decomposed and recombinated, the optimization SQL programs include optimization logical consequence；
Computing unit, for the content of the optimization logical consequence according to the optimization SQL programs, enters to the optimization SQL programs
Row is calculated, to obtain the result of calculation of the original SQL programs；
The optimization processing that the server is decomposed and recombinated to all original SQL programs in raw data base is specially：
Using grammar parser, deconsolidation process acquisition is carried out according to minimum syntactic structure word to all original SQL programs
Syntactic node, and all syntactic nodes after fractionation are assembled into a language according to the original grammatical relation between each node
Method tree；
To carrying out matching operation between the syntax tree after the syntactic node assembling of all original SQL programs, matched
Maximum subtree；
The optimization logical consequence of the calculating logic of the maximum subtree of the matching is stored in the second cache table；
Using the optimization logical consequence of the calculating logic preserved in the second cache table replace in the original SQL programs with institute
The corresponding subprogram of maximum subtree of matching is stated, obtains optimizing SQL programs.
10. a kind of server, it is characterised in that the server includes：
Achievement unit, for being split using grammar parser to all original SQL programs according to minimum syntactic structure word
Processing obtains syntactic node, and all syntactic nodes after fractionation are assembled according to the original grammatical relation between each node
For a syntax tree；
Matching unit, for carrying out matching fortune between the syntax tree after the syntactic node assembling to all original SQL programs
Calculate, the maximum subtree matched；
Memory cell, for the optimization logical consequence of the calculating logic of the maximum subtree of the matching to be stored in into the second cache table
In；
Replacement unit, replaces described original for the optimization logical consequence using the calculating logic preserved in the second cache table
Subprogram corresponding with the maximum subtree of the matching in SQL programs, obtains optimizing SQL programs.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210392343.8A CN103729379B (en) | 2012-10-16 | 2012-10-16 | Computational methods, method of adjustment and the server of SQL program |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210392343.8A CN103729379B (en) | 2012-10-16 | 2012-10-16 | Computational methods, method of adjustment and the server of SQL program |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN103729379A CN103729379A (en) | 2014-04-16 |
| CN103729379B true CN103729379B (en) | 2017-08-25 |
Family
ID=50453457
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201210392343.8A Active CN103729379B (en) | 2012-10-16 | 2012-10-16 | Computational methods, method of adjustment and the server of SQL program |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103729379B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108388646A (en) * | 2018-02-28 | 2018-08-10 | 弘成科技发展有限公司 | A kind of method that can ensure SQL integralities and dynamic and change |
| CN111563101B (en) * | 2020-07-11 | 2020-12-29 | 阿里云计算有限公司 | Execution plan optimization method, device, equipment and storage medium |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101436192A (en) * | 2007-11-16 | 2009-05-20 | 国际商业机器公司 | Method and apparatus for optimizing inquiry aiming at vertical storage type database |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7844608B2 (en) * | 2006-12-15 | 2010-11-30 | Yahoo! Inc. | Clustered query support for a database query engine |
| US8560523B2 (en) * | 2008-06-26 | 2013-10-15 | Microsoft Corporation | View matching of materialized XML views |
| CN102541884B (en) * | 2010-12-10 | 2014-07-02 | 中国移动通信集团贵州有限公司 | Method and device for database optimization |
| CN102609493B (en) * | 2012-01-20 | 2014-07-02 | 东华大学 | Connection sequence inquiry optimizing method based on column-storage model |
-
2012
- 2012-10-16 CN CN201210392343.8A patent/CN103729379B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101436192A (en) * | 2007-11-16 | 2009-05-20 | 国际商业机器公司 | Method and apparatus for optimizing inquiry aiming at vertical storage type database |
Non-Patent Citations (3)
| Title |
|---|
| SQL程序结构相似匹配及自动评估方法研究;刘玲;《万方学位论文全文数据库》;20100827;全文 * |
| 基于依赖树的XML数据查询优化与处理方法研究;李缨;《煤炭技术》;20110228;第30卷(第2期);全文 * |
| 数据库查询优化技术研究及其应用;刘亚欣;《中国优秀硕士论文全文数据库（电子期刊）》;20070215(第2期);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN103729379A (en) | 2014-04-16 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US7834781B2 (en) | Method of constructing an approximated dynamic Huffman table for use in data compression | |
| CN103136445B (en) | A kind of conversion method of openEHR information into relation database | |
| CN107038207A (en) | A kind of data query method, data processing method and device | |
| CN101217507A (en) | Method, device and system for providing and replacing data in web pages | |
| CN103581123B (en) | The processing method and processing device of network data, the dissemination method of network data and system | |
| US20140280172A1 (en) | System and method for distributed categorization | |
| US10924551B2 (en) | IRC-Infoid data standardization for use in a plurality of mobile applications | |
| CN100426300C (en) | Method and system for managing journal | |
| CN103150298A (en) | Customizable complicated form generation method for river basin water diversion business based on Web | |
| CN103488732A (en) | Generation method and device of static pages | |
| CN108536745A (en) | Tables of data extracting method, terminal, equipment and storage medium based on Shell | |
| CN108984155A (en) | Flow chart of data processing setting method and device | |
| CN103793481A (en) | Microblog word cloud generating method based on user interest mining and accessing supporting system | |
| CN108536433A (en) | A kind of J2EE Development Frameworks and the development approach based on the J2EE Development Frameworks | |
| CN103729379B (en) | Computational methods, method of adjustment and the server of SQL program | |
| KR101166763B1 (en) | Method for integration of database using data mapping of xml document | |
| CN107832443A (en) | Method, device and medium for processing JSON document data | |
| Majed et al. | Name lookup in named data networking: A review | |
| CN104317935B (en) | A kind of method and system of XML billing files Mass production html page | |
| CN101452456A (en) | Database query system and method thereof | |
| CN104346378A (en) | Method, device and system for realizing processing of complex data | |
| CN109739504A (en) | A method of the H5 business handling page is automatically generated according to backstage configuration | |
| Shang et al. | GvdsSQL: Heterogeneous Database Unified Access Technology for Wide-Area Environments | |
| CN107786661A (en) | Information synchronization method | |
| CN103064859A (en) | Fast extensive makeup language (XML) analytic method based on variable memory pre-application and HASH |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |