CN110399395A - Speedup query method, storage medium based on precomputation - Google Patents
Speedup query method, storage medium based on precomputation Download PDFInfo
- Publication number
- CN110399395A CN110399395A CN201810346821.9A CN201810346821A CN110399395A CN 110399395 A CN110399395 A CN 110399395A CN 201810346821 A CN201810346821 A CN 201810346821A CN 110399395 A CN110399395 A CN 110399395A
- Authority
- CN
- China
- Prior art keywords
- precomputation
- sentence
- query
- query statement
- result
- 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
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention provides a kind of speedup query method based on precomputation, storage medium, and method includes: to create precomputation sentence according to commonly using query statement；Each corresponding result set of precomputation sentence is obtained, and establishes the mapping table of each precomputation language and accordingly result collection；Each precomputation sentence and the mapping table are stored to precalculated database；Receive the inquiry request of a corresponding query statement；By the various pieces for comparing sentence, judge whether a query statement can directly or indirectly obtain from a precomputation sentence, if can, the corresponding result set of the precomputation sentence is then positioned from initial data warehouse according to the mapping table, and result needed for obtaining a query statement according to the result set.Inquiry mode of the invention can substantially speed up query result, improve search efficiency；It is also equipped with good versatility and flexibility simultaneously.
Description
Technical field
The present invention relates to data management and application field, particularly relate to a kind of speedup query method based on precomputation,
Storage medium.
Background technique
In the management and application process of data, the efficient inquiry of data becomes the essential routine work of enterprise.
In the today's society of this data volume explosive increase, existing data query technique will with its system performance of the increase of data volume
It gradually decreases.In order to guarantee the constant of business datum normal queries ability, then needs to increase the investment to hardware device, thus make
It is continuously increased at relevant costs such as maintenance data.It, can if can effectively accelerate the inquiry of data on existing hardware basis
The hardware cost of enterprise is effectively reduced, reduces the waiting time of inquiry data.
Inclusion relation and the equivalence judgement of query statement always are that industry has the important issue of dispute.It is existing
Accelerate inquiry mode generally using query optimization, cache, divide the modes such as table point library, precomputation and distributed treatment.In face
When to the complex query of big data quantity, inquiry pressure is still very serious, when user repeatedly carries out real time access to database,
The system of will lead to is difficult to return to query result within reasonable time, with the increase of data volume, the system time to be treated
Also just linear to increase.
Specifically, existing acceleration inquiry mode has the disadvantage that
1, existing pre-computed query mode poor universality.If user directly inquires raw data base, then inquire
It is excessive the time required to initial data, if user inquires for precomputation report, it can not accomplish that report is saturating to analysis personnel
It is bright, i.e., the inquiry to report can not be converted into the inquiry of raw data base.
2, existing hit cache way existing defects, new inquiry identical as the query statement of precomputation can only can just order
In, and lack the function of inquiry pre-computed query sentence subset, knot needed for can not being obtained from the result set in original caching
Fruit.
3, a point library divides table mode to lack flexibility.Point library divides table need to be in creation database or creation table Shi Dingyi, can not
It is adjusted according to the actual situation, especially when data volume increases, the mode of this kind of accelerating database searching can not meet
Demand.
Publication No. CN101110074 for another example, it is entitled " data speedup query method based on file system cache "
The prior art, using historical query data as query caching data, carry out accelerating to look by saving historical query data
It askes.Specifically, turning initially to query analyzer, the main function of query analyzer is after system receives user query request
Querying condition, planning querying condition etc. are analyzed, then passes through archive buffer, analyzes in existing result data files and whether deposit
Meeting current queries demand, do not inquired from database then, and final destination file is returned into data processing
Device, data processor the processing such as are filtered to data, sort according to final query requirement, destination file is returned to upper layer
Application system.
Scheme disclosed above is cached to the historical query sentence and its result of analysis personnel, and inquiry can not for the first time
Accelerate；It is also desirable to carry out judging merging to results set and be ranked up to querying condition, process is complicated；Further
, all data are all that directly in the database, while burdening to database, search efficiency also will response for caching
It reduces；Importantly, it is to judge whether the query statement that user is passed to hits in whole or in part from several subsets,
If it is partial hit, then the part that do not hit can be calculated, analysis mode is perfect not enough, and also there are also promoted for analysis efficiency
Space.
Therefore, it is necessary to provide a kind of pre- inquiry mode for being different from the prior art, while it can solve above-mentioned mention
The pre- querying method of all the problems taken.
Summary of the invention
The technical problems to be solved by the present invention are: a kind of speedup query method based on precomputation, storage medium are provided,
Search efficiency can be significantly improved, is provided simultaneously with compared with high universalizable and flexibility.
In order to solve the above-mentioned technical problem, the technical solution adopted by the present invention are as follows:
A kind of speedup query method based on precomputation, comprising:
Precomputation sentence is created according to query statement is commonly used；
Each corresponding result set of precomputation sentence is obtained, and establishes the corresponding relationship of each precomputation language and accordingly result collection
Table；Each precomputation sentence and the mapping table are stored to precalculated database；
Receive the inquiry request of a corresponding query statement；
By comparing the various pieces of sentence, judge a query statement whether can it is direct from a precomputation sentence or
Indirect gain, if can, it is corresponding that the precomputation sentence is positioned from initial data warehouse according to the mapping table
Result set, and result needed for obtaining a query statement according to the result set.
Another technical solution provided by the invention are as follows:
A kind of computer readable storage medium, is stored thereon with computer program, realization when which is executed by processor
The step of above-mentioned speedup query method based on precomputation is included.
The beneficial effects of the present invention are: can dynamic creation precomputation sentence, store corresponding result set and both
Mapping table；After receiving query statement, first judge that can result needed for query statement from pre- according to precalculated database
Directly or indirectly obtained in the result set of computing statement, if can, according to corresponding result set obtain needed for result without into
The inquiry of row full database.Inquiry mode of the invention can effectively reduce user every time directly access raw data base and to after
Person's bring pressure, to improve search efficiency；Simultaneously, moreover it is possible to mentioning for data query ability is realized on the basis of existing hardware
Height, to save the hardware cost expenditure of enterprise；Compared to the existing inquiry mode present invention stronger, flexibility with versatility
Higher, the features such as query effect is higher.
Detailed description of the invention
Fig. 1 is the flow diagram of the speedup query method of the invention based on precomputation；
Fig. 2 is the flow diagram of the speedup query method of the embodiment of the present invention one；
Fig. 3 is the precalculation module of the embodiment of the present invention two and the interaction schematic diagram of real-time processing module；
Fig. 4 is the function implementation process signal of query statement determining device in the specific implementation of the embodiment of the present invention two
Figure.
Specific embodiment
To explain the technical content, the achieved purpose and the effect of the present invention in detail, below in conjunction with embodiment and cooperate attached
Figure is explained.
The most critical design of the present invention is: dynamic creation precomputation sentence, stores precomputation sentence and its ties with corresponding
The mapping table of fruit collection is to precalculated database；After receiving query statement, first according to precalculated database judgement inquiry
Can result needed for sentence directly or indirectly obtain from the result set of precomputation sentence, if can, without carrying out real-time query.
Fig. 1 is please referred to, the present invention provides a kind of speedup query method based on precomputation, comprising:
Precomputation sentence is created according to query statement is commonly used；
Each corresponding result set of precomputation sentence is obtained, and establishes the corresponding relationship of each precomputation language and accordingly result collection
Table；Each precomputation sentence and the mapping table are stored to precalculated database；
Receive the inquiry request of a corresponding query statement；
By comparing the various pieces of sentence, judge a query statement whether can it is direct from a precomputation sentence or
Indirect gain, if can, it is corresponding that the precomputation sentence is positioned from initial data warehouse according to the mapping table
Result set, and result needed for obtaining a query statement according to the result set.
As can be seen from the above description, the beneficial effects of the present invention are: precomputation sentence is pre-created by modeling personnel, compares
In the existing mode according to historical query record building precomputation sentence, even inquiry also can be carried out acceleration for the first time；Separately
The precalculated database of precomputation is realized in outer creation independently of initial data warehouse, if pre-computed query is hit,
Address of the result set in initial data warehouse according to the mapping table record in precalculated database, from initial data storehouse
Result needed for direct basis result set obtains in library, is not only more advantageous to data management, improves the analysis of precomputation process
Treatment effeciency can more mitigate the pressure of raw data base.The present invention can improve the inquiry energy of data on the basis of existing hardware
Power avoids enterprise from needing to be continuously increased hardware cost expenditure with the increase of inquiry data volume.
Further, further includes:
If cannot, required result is obtained from initial data warehouse according to a query statement.
Seen from the above description, reselection access raw data base carries out real-time query after pre-computed query is not hit by
Mode can effectively reduce pressure user directly accesses the database every time caused by, while can improve search efficiency again.
Further, it is described directly, specifically: a query statement is equal or described one with a precomputation sentence
The result of query statement is the subset of the corresponding result set of a precomputation sentence；
It is described indirect, specifically: by calculating the corresponding result set of a precomputation sentence, described one can be obtained
The result of query statement.
Seen from the above description, it identical with precomputation sentence can only just can determine that hit compared to the prior art, this
Invention will directly (including identical and user query sentence result can be the result set of precomputation sentence
Subset) or result needed for (such as polymerization function) acquisition is also considered as hit indirectly, such inquiry mode can significantly improve hit
Probability is more flexible, it is contemplated that the query result accuracy of calculation is higher.
Further, the various pieces by comparing sentence judge whether a query statement can be estimated from one
It calculates and is directly or indirectly obtained in sentence, specifically:
Parse a query statement, a query statement split into various pieces, the part include from and
select；
The each precomputation language for having parsed and having finished is obtained from the precalculated database；
Traversal is described to have parsed each precomputation language finished, by a query statement with parsed finish it is each
Precomputation sentence is compared one by one, result needed for a query statement is judged by comparing the corresponding value of various pieces
Whether can directly or indirectly be obtained from the corresponding result set of precomputation sentence currently traversed；
If can, stop traversing, and determine successful match.
Seen from the above description, the present invention has the function of inquiry precomputation sentence subset, is different from the prior art with straight
It connects and compares whether precomputation language and query language are completely the same to determine whether to obtain from pre-computed query as a result, past
Past to be difficult to obtain the deficiency of required result, the present invention successively parses sentence, splits into various pieces, and each layer may all include
Have: select, from, where, group by, with, having, order by, limit, by each portion for comparing sentence
Point corresponding value judges whether to obtain from its result set required as a result, it is possible to abundant and effectively utilize pre-calculated data
Library, while improving the accuracy of pre-computed query.
Further, described to position the precomputation sentence pair from initial data warehouse according to the mapping table
The result set answered, and obtain needed for a query statement according to the result set as a result, specifically:
Convert a query statement；
The corresponding result set of the precomputation sentence is positioned from initial data warehouse according to the mapping table；
A query statement after the result pooled applications conversion positioned, it is corresponding to obtain a query statement
As a result.
Seen from the above description, after query statement is judged as hit, by the way that query statement is carried out conversion processing, more preferably
With the Data Matching in initial data warehouse, be different from existing unconverted mode, can significantly increase the life in result set
Middle probability, can directly inquire corresponding precomputation sentence as a result, and directly or indirectly obtaining required result from result.
Further, by query statement determining device execute it is described according to the mapping table from initial data warehouse
The corresponding result set of the precomputation sentence is positioned, and knot needed for obtaining a query statement according to the result set
Fruit.
Seen from the above description, by query statement determining device carry out query statement whether between precomputation sentence it is corresponding
Result set in hit, and the conversion to user query sentence makes the result set utilization rate of pre-computed query sentence increase
Add.
Further, further includes:
A newly-increased precomputation sentence；
Obtain the newly-increased corresponding result set of precomputation sentence；
Store the mapping table of the newly-increased precomputation sentence, the newly-increased precomputation sentence and its result set extremely
Precalculated database.
Seen from the above description, precomputation mode of the invention is more flexible, it is contemplated that calculating sentence can dynamically carry out
Definition is different from the existing inflexible mode that can only be defined when creating database or creation table, and realizing can root
It is adjusted flexibly according to actual conditions, realizes the adjusting of database pressure.
Another technical solution provided by the invention are as follows:
A kind of computer readable storage medium, is stored thereon with computer program, realization when which is executed by processor
The step of above-mentioned speedup query method based on precomputation is included.
It corresponds to those of ordinary skill in the art will appreciate that realizing all or part of stream in above-mentioned first technical solution
Journey can instruct relevant hardware to realize by computer program, and the program can be stored in a computer can
In the storage medium of reading, the program is when being executed, it may include such as the process of above-mentioned each method.
Embodiment one
Referring to figure 2., the present embodiment provides a kind of speedup query methods based on precomputation, it is desirable to provide one is more accelerated
The query service of speed.The method of the present embodiment mainly includes two parts:
One, precomputation configuration process
S1: precomputation sentence is created according to query statement is commonly used；
Specifically, modeling personnel can create precomputation sentence at any time.In general, when creating for the first time, by foundation
Common query statement creates corresponding precomputation sentence.The querying method of the present embodiment supports dynamic creation pre-computed query
Statement function, it can increase newly at any time or delete pre-computed query sentence, to meet different demands, so that precomputation language
The configuration of sentence has flexibility.
S2: each corresponding result set of precomputation sentence is obtained, and it is corresponding with accordingly result collection to establish each precomputation sentence
Relation table；Each precomputation sentence and the mapping table are stored to precalculated database, it is corresponding to store precomputation sentence
Result set is into a particular memory region in initial data warehouse.The mapping table, storage be precomputation sentence with it is right
Corresponding relationship of the result set answered between the storage address in initial data warehouse.
User can create the query statement for needing precomputation in advance according to actual scene, and by precomputation sentence with estimated
The corresponding relationship for calculating sentence accordingly result address is all previously stored in precalculated database, is the realization of subsequent precomputation process
It lays the foundation.
Specifically, the precomputation sentence according to creation is obtained corresponding result set, the result set from raw data base
Report can be considered as, cache or divide table etc..Preferably, it will be obtained according to the actual situation when server system pressure is smaller estimated
Calculate result set corresponding to sentence；And result set is saved in initial data warehouse, preferably creation one independent storage region into
Row storage.Meanwhile also the one-to-one correspondence established between the query statement of precomputation and obtained result set storage address being closed
It is table.Result set is stored in the separate storage region in initial data warehouse, precomputation sentence and mapping table are deposited
Storage, can be during pre-computed query in precomputation result table database, and corresponding different Data Analysis Services process is straight
Connect and called from corresponding storage region, can more efficient, more specific aim, further increase search efficiency, while again can
Save resource.
In the present embodiment, modeling personnel can create pre-computed query sentence at any time, and from raw data base
Middle to obtain corresponding result set, when user query raw data base, which can convert partial query to, looks into precomputation result set
It askes, to improve the versatility of precomputation；Meanwhile the creation of precomputation sentence used in the method for the present embodiment mode has more
Flexibility when being no longer only limited in creation database or creation table, but can be flexibly adjusted according to the actual situation,
Especially when data volume increases, precalculated database can be expanded in time, with accelerating database searching, better meeting is needed
It asks.
Two, real time process
SS1: user initiates inquiry request；
SS2: server-side obtains corresponding query statement according to Client-initiated inquiry request；
SS3: server-side calls precalculated database to judge whether the query statement of user orders by query statement determining device
Middle precomputation sentence；If hit, directly inquires corresponding result set, and obtain institute from the separate storage region in above-mentioned S2
Need result；If being not hit by, by the raw data base of calling system, inquired by real-time query mode.
Query statement determining device be used for obtain user input query statement after, by user query sentence with created
Precomputation sentence carry out the comparison of each component part, judge whether the desired result of user's current queries sentence can be from
It is directly or indirectly obtained in the result set of the complete query statement of precomputation, if can be from pre-computed complete query statement
Result set in directly or indirectly obtain i.e. hit；If determining not hit in query statement determining device, this user
Inquiry request will turn into real-time query.
Specifically, the realization process of the SS3 may include following sub-step:
SS31: query statement is parsed by query statement determining device, which is split into various differences
Part；The part the part such as where, the part select, the part group by, the part from, limit, order by etc.
Deng；In general, must include the part select and the part from sentence, the component part that different query statements is included can
Can be different, concrete foundation is inquired depending on purpose.
By splitting query statement, whether the query statement for facilitating deciding on user can be directly or indirectly
It is obtained from the result set for the precomputation sentence having had been built up.
Such as:
Precomputation sentence: select a, b, c from table where d>1and e<30group by a；
After being divided into the parts such as select, it is also necessary to integrally consider these parts；
User query sentence 1:select a, b from table1where d>1and e<30group by a；
When being divided into different piece to be judged since the subsequent table of from is different from table1, so being not hit by；
User query sentence 2:select a, d from table where d>1and e<30group by a；
Since there is d in the part select in user query sentence 2, and do not have in precomputation sentence, i.e., it can not in result set
The dimension of d is found, because being not hit by.
SS32: each precomputation sentence for having parsed and having finished is obtained from precalculated database, i.e., acquired is
Precomputation sentence after parsing in the manner described above.It preferably, will be preferentially from estimated in order to further increase search efficiency
It calculates the pre-computed query sentence that obtains under all user items in database to be matched, if being not hit by, then from original number
According to the result carried out in warehouse needed for real-time query obtains.This is because the sentence of precomputation be usually for project,
It just would make sense in this way, it is contemplated that the sentence of calculation is which data possessed according to user's known items, and according to these design datas
Reasonable precomputation sentence is realized.It is anticipated that the sentence calculated is related with project, if being different project,
The precomputation sentence being related to is not with regard to very poor very big.Meanwhile separating the precomputation sentence of disparity items, purpose is also to speed up retrieval
Number.The precomputation sentence of disparity items is stored in different tables from the corresponding relationship of its result set address.
SS33: traversal is described to have parsed each precomputation language finished, and a query statement is finished with having parsed
Each precomputation sentence be compared one by one, judged needed for a query statement by comparing the corresponding value of various pieces
Result whether can directly or indirectly be obtained from the corresponding result set of precomputation sentence currently traversed；If can, stop
Traversal, and determine successful match, that is, it hits, executes SS34；If cannot, determine that it fails to match, that is, is not hit by, executes SS35；
Specifically, described directly acquire, refer to: a query statement is equal completely with a precomputation sentence, i.e., respectively
Result needed for the corresponding value of a component part is identical or the query statement is the corresponding knot of a precomputation sentence
The subset of fruit collection can be extracted directly from the result set of precomputation sentence and be obtained that is, without being calculated.
Simple example 1:
Precomputation sentence: select a, b from table where c=XXX；
User query sentence: select a from table where c=XXX；
In this way it is also assumed that being to directly hit, similar where is considered as directly change place if there is multiple conditions
Connect hit；
Complexity citing 2:
Precomputation sentence: select a, b from table1where a >=1and a≤10and c=xxxx；
User's sentence: select a, b from table1where a >=1and a≤5and c=xxxx；
Also directly hit at last in this way because in the condition of where where a >=a≤5 1and be where a >=
The subset of a≤10 1and.
The indirect gain, refers to: by calculating the corresponding result set of a precomputation sentence, can obtain institute
State the result of a query statement.It is such as secondary from the corresponding result set of a precomputation sentence by being similar to sum set function
Processing obtains the result of user query sentence.
Such as:
User query sentence is the difference of January to March daily the women customer consumption amount of money and male customers spending amount, in advance
Computing statement is the spending amount of daily male and female customer in January to May, selects the data in January to March in the latter, two
Column subtract each other it is available the former.
Precomputation sentence is corresponding are as follows: and select date, a, b from table where date>=1-1and date<
=5-31；
User query sentence are as follows: select date, a-b from table where date >=1-1and date≤
3-31；
(3-31,1-1,5-31 are to represent the date, and actual dates may be variant)
Due to query statement include or equivalence question is always the emphasis problem of industry, the judgement of query statement herein
Device can not judge completely whether user query sentence can be obtained in the result of the precomputation sentence built as a result, but
It is query statement determining device if it is judged that hitting, then user query sentence is sure from the result of pre-computed query sentence
It acquires, and guarantees the correctness of result.
SS34: according to the mapping table stored in precalculated database, by calling the storage of initial data warehouse estimated
The particular memory region for calculating corresponding result set, determines result set corresponding to the precomputation sentence of hit；Then without obtaining
In the case where the result set, directly as a result, returning to user needed for the query statement that the result Integrated query obtains user.
I.e. according to the mapping table, position of the corresponding result set in data warehouse is found out, then according to the knot found
Fruit collection obtains required result.This process greatly reduces the query time of user query sentence in the database.
SS35: the raw data base of calling system inquires the query statement of user using real-time query mode.
Above-mentioned query statement determining device greatly increases the result set utilization rate of pre-computed query sentence, and query statement is sentenced
Disconnected device may determine that whether the result of user query sentence can directly or indirectly obtain from the result set of pre-computed query sentence
It arrives；It is also equipped with the transformation function to user query sentence simultaneously.User query sentence can be inquired directly pair after meeting condition conversion
Answer pre-computed query sentence as a result, and directly or indirectly obtaining result from result.
In a specific embodiment, after determining hit, result needed for illustrating the query statement of user can pass through
It is obtained in the result set of the query statement of precomputation, then it is reasonable to be carried out by query statement of the query statement determining device to user
Conversion, then the query statement after the result pooled applications of precomputation convert again, directly returns to use for obtained result
Family is without inquiring raw data base.The reason of conversion therein is: user query sentence is inquired raw data base
Sentence, relevant inquiring operation can not be carried out directly in precomputation result set；It is pre- to user query sentence is switched to inquiry
The result set of calculating, must just be converted to user query sentence can be to the sentence that result set is inquired.
Such as:
Precomputation sentence: select a as col1, b as col2from table where c=10；
User query sentence: select a from table where c=10；
It is contemplated that calculate save result set in only have col1 and col2 this two column (a as col1 indicate by a column orders again
Entitled col1, i.e. user rename grid column name), if not converting this step of query statement, possibly can not be from result
Concentrate the sentence that obtains and originally can hit.Conversion in the example refers to the column name a for reversing reuse family to need col1as a.
When query statement determining device is judged, if user query sentence is hit, user query language will be also converted
Sentence are as follows: select col1as a from XXX.Simultaneously as subsequent where due to be it is identical, then by conversion will also
Duplicate part is omitted, while simplifying query statement.
That is, query statement can not only be converted into and the matched inquiry item of precomputation result set by conversion
Part, it is significant to increase the hit probability in precomputation result set, while it being also equipped with duplicate removal function, it can significantly simplify user query language
Sentence.
Embodiment two
The present embodiment is corresponding with embodiment one, provides a kind of acceleration inquiry system based on precomputation.The whole frame of system
Frame includes precalculation module and real-time processing module.Wherein real-time processing module needs to call precalculation module, while precomputation
Module can also be carried out individually, not influenced by real-time query module, and the availability of two modules is improved.
In the following, the step of two modules of the system to the present embodiment are respectively executed when using carries out specifically
It is bright:
Please refer to Fig. 3
In precalculation module:
S101: common query statement is concluded according to existing business scene and is divided by creation precomputation component, i.e. modeling personnel
Class establishes the query statement model of precomputation, the corresponding pre-computed query sentence of each model；And precomputation component can basis
Actual conditions obtain result set corresponding to pre-computed query sentence when system pressure is smaller, and result set is saved in S103
Precomputation result table data database in, the database be initial data warehouse separate storage region.
S102: the one-to-one correspondence table between the query statement of precomputation and obtained result table is established；
S103: the result table that all pre-computed query sentences obtain is stored in the separate storage area in initial data warehouse
In domain；
In real-time query module:
S104: user initiates inquiry request；
S105: inquiry request will use query statement determining device, which can be according to the inquiry language of user
Whether sentence inquiry has hit precomputation sentence；If hit enters S106 step, otherwise enter S107 step；
S106: hitting in query statement determining device in user query sentence, then will pass through subset inquiry component foundation
Result set is obtained in precomputation table corresponding with result set address (in the precomputation sentence and its result table name database in figure)
Address, and utilize the user query sentence after conversion from the result Integrated query in the address of acquisition (i.e. from precomputation result table
Inquired in database), without inquiring raw data base, this process greatly reduces user query sentence in the database
Query time.
S107: if user query sentence is not hit in query statement determining device, continue the inquiry of user
Sentence is obtained by real-time query.
S108: the final result is returned into user.
As shown in figure 4, in a specific embodiment, the query statement determining device with when execute the step of packet
It includes:
S201: the query statement request of user is inputted.
S202: query statement is parsed by query statement resolver, the query statement of user can divide after parsing
At a variety of different parts, such as the part select, the part from, the part where etc..
S203: from all users of acquisition in the database (i.e. precalculated database) of precomputation sentence and its result table name
Pre-computed query sentence under project, which, which has parsed, finishes.
S204: compared with the query statement of the query statement of user after parsing and precomputation is carried out one by one, respectively from
The conditions such as where, select, group by, from carry out judging whether that result required for the query statement of user whether may be used
To obtain from the result table of pre-computed query sentence, if hit, stop traversing pre-computed query sentence.
S205: if passed through, result needed for illustrating the query statement of user can pass through the query statement of precomputation
It obtains, then the query statement of user is reasonably converted in result set, and the query statement after conversion is exported.
S206: if do not passed through, query statement determining device can then export null character string, after exporting conversion if passing through
Query statement.
Embodiment three
The present embodiment provides a kind of computer readable storage mediums, are stored thereon with computer program, and the program is processed
The step of speedup query method based on precomputation described in embodiment one is included is realized when device executes；
In another specific embodiment, it is contemplated that calculate module and real-time query module and counted accordingly in calling storage medium
After calculation machine program, the step of precalculation module and real-time query module can execute in above-described embodiment two can be realized.
The specific steps process of the present embodiment refers to the description of embodiment one and embodiment two, no longer repeats herein.
In conclusion the speedup query method provided by the invention based on precomputation, storage medium, modeling personnel can move
State creates pre-computed query sentence, not only reduces the pressure that user's subsequent query directly accesses the database；But also support with
When precalculated database is expanded, with meet demand；Newly-increased query statement determining device, makes the result of pre-computed query sentence
Collection utilization rate greatly increases；Change hit cache way, realize the function of inquiry precomputation statement result subset, improves precomputation
Process obtains the probability of hit and the accuracy rate of result.Inquiry mode of the invention can substantially speed up inquiry on original hardware foundation
As a result, improving search efficiency；It is also equipped with good versatility and flexibility simultaneously.
The above description is only an embodiment of the present invention, is not intended to limit the scope of the invention, all to utilize this hair
Equivalents made by bright specification and accompanying drawing content are applied directly or indirectly in relevant technical field, similarly include
In scope of patent protection of the invention.
Claims (8)
1. a kind of speedup query method based on precomputation characterized by comprising
Precomputation sentence is created according to query statement is commonly used；
Each corresponding result set of precomputation sentence is obtained, and establishes the mapping table of each precomputation language and accordingly result collection；
Each precomputation sentence and the mapping table are stored to precalculated database；
Receive the inquiry request of a corresponding query statement；
By comparing the various pieces of sentence, judge whether a query statement can be from a precomputation sentence directly or indirectly
It obtains, if can, the corresponding result of the precomputation sentence is positioned from initial data warehouse according to the mapping table
Collection, and result needed for obtaining a query statement according to the result set.
2. as described in claim 1 based on the speedup query method of precomputation, which is characterized in that further include:
If cannot, required result is obtained from initial data warehouse according to a query statement.
3. as described in claim 1 based on the speedup query method of precomputation, which is characterized in that it is described direct, specifically: institute
State a query statement be equal with a precomputation sentence or the result of a query statement be the corresponding knot of a precomputation sentence
The subset of fruit collection；
It is described indirect, specifically: by calculating the corresponding result set of a precomputation sentence, an inquiry can be obtained
The result of sentence.
4. as described in claim 1 based on the speedup query method of precomputation, which is characterized in that described by comparing sentence
Various pieces, judge whether a query statement can directly or indirectly obtain from a precomputation sentence, specifically:
Parse a query statement, a query statement split into various pieces, the part include from and
select；
The each precomputation language for having parsed and having finished is obtained from the precalculated database；
Traversal is described to have parsed each precomputation language finished, by a query statement with parsed finish it is each estimated
It calculates sentence to be compared one by one, whether result needed for judging a query statement by comparing the corresponding value of various pieces
It can directly or indirectly be obtained from the corresponding result set of precomputation sentence currently traversed；
If can, stop traversing, and determine successful match.
5. as described in claim 1 based on the speedup query method of precomputation, which is characterized in that described according to the corresponding pass
It is that table positions the corresponding result set of the precomputation sentence from initial data warehouse, and described in obtaining according to the result set
Needed for one query statement as a result, specifically:
Convert a query statement；
The corresponding result set of the precomputation sentence is positioned from initial data warehouse according to the mapping table；
A query statement after the result pooled applications conversion positioned, obtains the corresponding knot of a query statement
Fruit.
6. as claimed in claim 5 based on the speedup query method of precomputation, which is characterized in that pass through query statement determining device
Execute it is described position the corresponding result set of the precomputation sentence from initial data warehouse according to the mapping table, and
Result needed for obtaining a query statement according to the result set.
7. as described in claim 1 based on the speedup query method of precomputation, which is characterized in that further include:
A newly-increased precomputation sentence；
Obtain the newly-increased corresponding result set of precomputation sentence；
Store the newly-increased precomputation sentence, the newly-increased precomputation sentence and its result set mapping table to estimated
Calculate database.
8. a kind of computer readable storage medium, is stored thereon with computer program, which is characterized in that the program is held by processor
The step of speedup query method based on precomputation described in the claims 1-7 any one is included is realized when row.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810346821.9A CN110399395B (en) | 2018-04-18 | 2018-04-18 | Pre-calculation-based accelerated query method and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810346821.9A CN110399395B (en) | 2018-04-18 | 2018-04-18 | Pre-calculation-based accelerated query method and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110399395A true CN110399395A (en) | 2019-11-01 |
| CN110399395B CN110399395B (en) | 2022-04-01 |
Family
ID=68319570
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810346821.9A Active CN110399395B (en) | 2018-04-18 | 2018-04-18 | Pre-calculation-based accelerated query method and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110399395B (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111311326A (en) * | 2020-02-18 | 2020-06-19 | 平安科技（深圳）有限公司 | User behavior real-time multidimensional analysis method and device and storage medium |
| CN112965991A (en) * | 2021-03-08 | 2021-06-15 | 咪咕文化科技有限公司 | Pre-calculation result generation method and device, electronic equipment and storage medium |
| CN114138798A (en) * | 2022-01-29 | 2022-03-04 | 阿里巴巴达摩院(杭州)科技有限公司 | System, method, electronic device, and medium for estimating hit probability of query result |
| WO2023035356A1 (en) * | 2021-09-10 | 2023-03-16 | 上海跬智信息技术有限公司 | Cloud analysis scenario-based hybrid query method and system, and storage medium |
| WO2023155273A1 (en) * | 2022-02-15 | 2023-08-24 | 上海跬智信息技术有限公司 | Correlation coefficient calculation method and apparatus, device, and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102479223A (en) * | 2010-11-25 | 2012-05-30 | 中国移动通信集团浙江有限公司 | Data query method and system |
| CN103559300A (en) * | 2013-11-13 | 2014-02-05 | 曙光信息产业(北京)有限公司 | Data query method and device |
| CN104699788A (en) * | 2015-03-16 | 2015-06-10 | 百度在线网络技术（北京）有限公司 | Database query method and device |
| US20180039671A1 (en) * | 2015-04-16 | 2018-02-08 | Huawei Technologies Co., Ltd. | Method and apparatus for querying data in cross-shard databases |
-
2018
- 2018-04-18 CN CN201810346821.9A patent/CN110399395B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102479223A (en) * | 2010-11-25 | 2012-05-30 | 中国移动通信集团浙江有限公司 | Data query method and system |
| CN103559300A (en) * | 2013-11-13 | 2014-02-05 | 曙光信息产业(北京)有限公司 | Data query method and device |
| CN104699788A (en) * | 2015-03-16 | 2015-06-10 | 百度在线网络技术（北京）有限公司 | Database query method and device |
| US20180039671A1 (en) * | 2015-04-16 | 2018-02-08 | Huawei Technologies Co., Ltd. | Method and apparatus for querying data in cross-shard databases |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111311326A (en) * | 2020-02-18 | 2020-06-19 | 平安科技（深圳）有限公司 | User behavior real-time multidimensional analysis method and device and storage medium |
| WO2021164253A1 (en) * | 2020-02-18 | 2021-08-26 | 平安科技（深圳）有限公司 | Method and device for real-time multidimensional analysis of user behaviors, and storage medium |
| CN112965991A (en) * | 2021-03-08 | 2021-06-15 | 咪咕文化科技有限公司 | Pre-calculation result generation method and device, electronic equipment and storage medium |
| CN112965991B (en) * | 2021-03-08 | 2023-12-08 | 咪咕文化科技有限公司 | Pre-calculation result generation method and device, electronic equipment and storage medium |
| WO2023035356A1 (en) * | 2021-09-10 | 2023-03-16 | 上海跬智信息技术有限公司 | Cloud analysis scenario-based hybrid query method and system, and storage medium |
| EP4174678A4 (en) * | 2021-09-10 | 2023-08-30 | Shanghai Kyligence Information Technology Co., Ltd | Cloud analysis scenario-based hybrid query method and system, and storage medium |
| CN114138798A (en) * | 2022-01-29 | 2022-03-04 | 阿里巴巴达摩院(杭州)科技有限公司 | System, method, electronic device, and medium for estimating hit probability of query result |
| WO2023155273A1 (en) * | 2022-02-15 | 2023-08-24 | 上海跬智信息技术有限公司 | Correlation coefficient calculation method and apparatus, device, and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110399395B (en) | 2022-04-01 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110399395A (en) | Speedup query method, storage medium based on precomputation | |
| US6505187B1 (en) | Computing multiple order-based functions in a parallel processing database system | |
| CN103064875B (en) | A kind of spatial service data distributed enquiring method | |
| US6789071B1 (en) | Method for efficient query execution using dynamic queries in database environments | |
| CN104317966B (en) | A kind of dynamic index method inquired about for electric power big data Rapid Combination | |
| US20030055813A1 (en) | Query optimization by sub-plan memoization | |
| EP1164509A2 (en) | Aggregate join index for relational databases | |
| US20050010569A1 (en) | System and method of query transformation | |
| JPH07319923A (en) | Method and equipment for processing of parallel database of multiprocessor computer system | |
| US6122644A (en) | System for halloween protection in a database system | |
| US8370326B2 (en) | System and method for parallel computation of frequency histograms on joined tables | |
| CN107169033A (en) | Relation data enquiring and optimizing method with parallel framework is changed based on data pattern | |
| CN101021874A (en) | Method and apparatus for optimizing request to poll SQL | |
| CN108052635A (en) | A kind of heterogeneous data source unifies conjunctive query method | |
| CN107291770B (en) | Mass data query method and device in distributed system | |
| US7949655B2 (en) | Joins of relations in an object relational database system | |
| CN105335479A (en) | Text data statistics realization method based on SQL | |
| Gavagsaz et al. | Load balancing in join algorithms for skewed data in MapReduce systems | |
| CN111177148A (en) | Method for automatically building and dividing tables of hydropower database | |
| JPH07160557A (en) | Data base access processing method | |
| KR101955376B1 (en) | Processing method for a relational query in distributed stream processing engine based on shared-nothing architecture, recording medium and device for performing the method | |
| US8832157B1 (en) | System, method, and computer-readable medium that facilitates efficient processing of distinct counts on several columns in a parallel processing system | |
| Su et al. | A distributed query processing strategy using decomposition, pipelining and intermediate result sharing techniques | |
| CN110347726A (en) | A kind of efficient time series data is integrated to store inquiry system and method | |
| US6421657B1 (en) | Method and system for determining the lowest cost permutation for joining relational database tables |
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 |