CN103377292B - Database result set caching method and device - Google Patents
Database result set caching method and device Download PDFInfo
- Publication number
- CN103377292B CN103377292B CN201310275023.9A CN201310275023A CN103377292B CN 103377292 B CN103377292 B CN 103377292B CN 201310275023 A CN201310275023 A CN 201310275023A CN 103377292 B CN103377292 B CN 103377292B
- Authority
- CN
- China
- Prior art keywords
- executive plan
- dml
- result
- database engine
- implementing 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.)
- Active
Links
Abstract
An embodiment of the invention provides a database result set caching method and device. The database result set caching method includes the steps: receiving a DML (data manipulation language) statement for performing DML operation on an original data table by a database engine; awakening an execution plan set in a sleep state by the database engine if judging that a first final execution result and a middle execution result obtained by previously executing the execution plan for the original data table are stored in a cache region after performing the DML operation by the database engine, re-executing the execution plan based on the middle execution result and the original data table after the DML operation is performed to obtain a second final execution result corresponding to the original data table after the DML operation is performed, and storing the second final execution result in the cache region. Therefore, when the original data table is changed, the whole execution result does not need to be re-executed, and query time is shortened.
Description
Technical field
The present embodiments relate to the communication technology, more particularly, to a kind of database result set cache method and apparatus.
Background technology
At present, in database actual application environment, for the inquiry request of quick response user, result set is often used to delay
Deposit technology, when the principle of result set cache is to receive identical inquiry request, current using Query Result response before
Inquiry request, rather than directly initial data is processed.It is very big that result set cache technology makes the query performance of database have
Lifting, less physics can be had to read and write and the faster response time.Result set cache be mainly used in data table memory connect,
The result of the time-consuming operation such as gathering or sequence, quickly to obtain result.
In prior art, it is according to SQL（Structured Query Language, referred to as：SQL）Look into
Ask sentence doing result set cache, if a SQL query statement is started with select, database server will be
It is buffered in buffer area with the corresponding Query Result of hash key assignments of this SQL text, each buffer area and a SQL text
Hash key assignments corresponds to, and before application result set cache, database server will not do any process to SQL text, that is,
Say, if two SQL query statement only differ a character, this two SQL query statement are also by the Different Results collection using caching.
However, in practical application, being updated sometimes inevitable, if original tables of data to original tables of data
There occurs renewal, the result set caching before can be led to unavailable, when receiving identical inquiry request, need to expend central authorities
Processor（Central Processing Unit, referred to as：CPU）Re-execute whole query script, to generate new result
Collection.
Content of the invention
The embodiment of the present invention provides a kind of database result set cache method and apparatus, to avoid occurring in raw data table
During change, need to re-execute whole executive plan, save query time.
In a first aspect, the embodiment of the present invention provides a kind of database result set cache method, including：
Database engine receiving data manipulation language DML statement, described DML statement is used for making described database engine to former
Beginning tables of data carries out DML operation；
After described database engine execution described DML operation, if judge to know be stored with buffer area before to described former
The first final implementing result and middle implementing result that beginning tables of data executes an executive plan and obtains,
Then described database engine wakes up and is arranged to dormant described executive plan, and executes knot in the middle of described
Execute through the raw data table of DML operation on the basis of described executive plan really and again, to obtain operating with through DML
The corresponding second final implementing result of raw data table and be stored in described buffer area.
In the first possible implementation of first aspect, described database engine wakes up and is arranged to sleep state
Described executive plan, including：
Described database engine wakes up immediately and is arranged to dormant described executive plan；Or,
When meeting default trigger condition, described database engine wakes up and is arranged to dormant and described executes meter
Draw.
In conjunction with the first possible implementation of first aspect or first aspect, in the possible implementation of second
In, before described database engine receiving data manipulation language DML statement, also include：
Described executive plan is executed to described raw data table, obtains the described first final implementing result and described centre
Implementing result, the working condition of described executive plan is set to sleep state, and is stored in described buffer area.
According to the possible implementation of the second of first aspect, in the third possible implementation, described execution
Plan includes SQL SQL query statement.
Second aspect, the embodiment of the present invention provides a kind of database engine, including：
Receiver module, for receiving data manipulation language DML statement, described DML statement is used for making described database engine
DML operation is carried out to raw data table；
Processing module, for executing after described DML operation, if judge to know be stored with buffer area before to described original
The first final implementing result and middle implementing result that tables of data executes an executive plan and obtains, then wake up and be arranged to sleep
The described executive plan of dormancy state, and in described middle implementing result and on the basis of the raw data table of DML operation
Execute described executive plan again, with the corresponding second final implementing result of raw data table that obtains with operate through DML simultaneously
It is stored in described buffer area.
In the first possible implementation of second aspect, described processing module specifically for：
Wake up immediately and be arranged to dormant described executive plan；Or,
When meeting default trigger condition, wake up and be arranged to dormant described executive plan.
In conjunction with the first possible implementation of second aspect or second aspect, in the possible implementation of second
In, described processing module is additionally operable to：
Described executive plan is executed to described raw data table, obtains the described first final implementing result and described centre
Implementing result, the working condition of described executive plan is set to sleep state, and is stored in described buffer area.
According to the possible implementation of the second of second aspect, in the third possible implementation, described execution
Plan includes SQL SQL query statement.
Database result set cache method and apparatus provided in an embodiment of the present invention, receives DML language by database engine
Sentence, carries out DML operation to raw data table；After described database engine executes described DML operation, if judging to know in buffer area
The the first final implementing result before being stored with, an executive plan being executed to described raw data table and obtaining and middle execution
As a result, then described database engine wake-up is arranged to dormant described executive plan, and in described middle implementing result
And through DML operation raw data table on the basis of execute described executive plan again, with obtain with through DML operation
The corresponding second final implementing result of raw data table is simultaneously stored in described buffer area.Thus changing in raw data table
When, without re-executing whole executive plan, but can execute again on the basis of middle implementing result and described execute meter
Draw, save query time.
Brief description
In order to be illustrated more clearly that the embodiment of the present invention or technical scheme of the prior art, below will be to embodiment or existing
Have technology description in required use accompanying drawing be briefly described it should be apparent that, drawings in the following description are these
Some bright embodiments, for those of ordinary skill in the art, without having to pay creative labor, acceptable
Other accompanying drawings are obtained according to these accompanying drawings.
Fig. 1 is the flow chart of database result set cache embodiment of the method one of the present invention；
Fig. 2 is the flow chart of database result set cache embodiment of the method two of the present invention；
Fig. 3 is the schematic diagram of the executive plan of client in database result set cache method of the present invention；
Fig. 4 is the structural representation of database engine embodiment one of the present invention.
Specific embodiment
Purpose, technical scheme and advantage for making the embodiment of the present invention are clearer, below in conjunction with the embodiment of the present invention
In accompanying drawing, the technical scheme in the embodiment of the present invention is clearly and completely described it is clear that described embodiment is
The a part of embodiment of the present invention, rather than whole embodiments.Based on the embodiment in the present invention, those of ordinary skill in the art
The every other embodiment being obtained under the premise of not making creative work, broadly falls into the scope of protection of the invention.
Fig. 1 is the flow chart of database result set cache embodiment of the method one of the present invention.As shown in figure 1, the present embodiment carries
For method can include：
S101, database engine receiving data manipulation language（Data Manipulation Language, referred to as：DML）
Sentence, described DML statement is used for making described database engine carry out DML operation to raw data table.
DML statement specifically can be used for raw data table is updated, insert or delete operation, and the present embodiment is with to former
Beginning tables of data illustrates as a example inserting a new record.Database engine, can be according to DML statement when receiving DML statement
Update is carried out to raw data table.
S102, described database engine execution described DML operation, if judge to know be stored with buffer area before to described
The first final implementing result and middle implementing result that raw data table executes an executive plan and obtains, then described database
Engine wakes up and is arranged to dormant described executive plan, and in described middle implementing result and through DML operation
Described executive plan is executed again on the basis of raw data table, corresponding with the raw data table that obtains with operate through DML
Second final implementing result is simultaneously stored in described buffer area.
If executing to described raw data table it is understood that judging to know in buffer area less than before storing
The the first final implementing result planned and obtain and middle implementing result, can not execute operation.
In actual application, it is permissible that described database engine wake-up is arranged to dormant described executive plan
Including two ways：First kind of way wakes up immediately for described database engine and is arranged to dormant and described executes meter
Draw；The second way is when meeting default trigger condition, and described database engine wakes up and is arranged to dormant institute
State executive plan.The present embodiment is not limited to this.
The database result set cache method that the present embodiment provides, receives DML statement by database engine, to original number
Carry out DML operation according to table；After described database engine execution described DML operation, if before judging to know and being stored with buffer area
The the first final implementing result one executive plan being executed to described raw data table and obtaining and middle implementing result, then described
Database engine wakes up and is arranged to dormant described executive plan, and in described middle implementing result and through DML
Described executive plan is executed again, to obtain and the raw data table through DML operation on the basis of the raw data table of operation
Corresponding second final implementing result is simultaneously stored in described buffer area.Thus when raw data table changes, can not
With re-executing whole executive plan, but execute described executive plan again on the basis of middle implementing result, save
Query time.
Fig. 2 is the flow chart of database result set cache embodiment of the method two of the present invention.As shown in Fig. 2 in above-mentioned enforcement
On the basis of example, before S101, the method that the present embodiment provides can also include：
S201, described executive plan is executed to described raw data table, obtain the described first final implementing result and institute
State middle implementing result, the working condition of described executive plan is set to sleep state, and be stored in described buffer area.
Specifically, described executive plan can include described SQL query statement.In the present embodiment, described executive plan
Two kinds of working conditions, running status and sleep state can be included, database engine, can be by after having executed described executive plan
The working condition of described executive plan is set to sleep state, rather than directly terminates described executive plan, simultaneously because slow
Deposit and in area, store middle implementing result and the first final implementing result, therefore, if raw data table changes, database draws
Hold up and be set to described dormant described executive plan before can waking up, i.e. make described executive plan be in running status,
And execute described executive plan again on the basis of the raw data table after described middle implementing result and change, to obtain
With change after the corresponding second final implementing result of raw data table and be stored in described buffer area.
The database result set cache method that the present embodiment provides, by when first time executing described executive plan, obtaining
Obtain the described first final implementing result and described middle implementing result, the working condition of described executive plan is set to sleep
State, and if be stored in described buffer area so that follow-up raw data table changes, described database engine can wake up
Described executive plan, and execute described execution again on the basis of the raw data table after described middle implementing result and change
Plan, and the obtain second final implementing result is stored in described buffer area, initiate identical again in user and execute meter
When drawing, can direct acquisition final result from buffer area, and without re-executing whole executive plan, when saving inquiry
Between.
With a specific embodiment, database result set cache method is described in detail below.
Fig. 3 is the schematic diagram of the executive plan of client in database result set cache method of the present invention.As shown in figure 3,
In this example, it is assumed that executive plan is to index scanning（customer）Table and full table scan（sales）Table carries out Hash
Connect（Hash Join）.Skilled person will appreciate that, Hash Join includes build hash and two steps of hash join
Suddenly, wherein, build hash is the operation carrying out for customer table, and hash join is to customer table and sales
The operation that table is carried out.
The result of build hash is the middle implementing result described in the present embodiment, and the result of hash join is
Described in the present embodiment first final implementing result, the result set in Fig. 3 is first described in the present embodiment and finally holds
Row result or the second final implementing result.
Assume that customer end A initiates SQL query statement for the first time, database engine, after receiving SQL query statement, is analyzed
SQL statement text, checks in buffer area and final executes knot with the presence or absence of corresponding with the hash key assignments of this SQL statement text first
Really, if existing, database engine takes out this first final implementing result from buffer area and returns to customer end A；If not depositing
, then database engine execution executive plan corresponding with described SQL query statement, obtain the middle execution of described executive plan
Result and the first final implementing result, and the working condition of described executive plan is set to sleep state, and it is stored in described
In buffer area, return the first final implementing result to customer end A.
If customer end B inserts a record in sales table, after database engine has executed update, can judge
Whether there is the described first final implementing result and described middle implementing result in buffer area, if existing, waking up and being set
For dormant described executive plan, this is recorded and is pushed into the node of Hash Join and does hash join operation, without
Re-start build hash that is to say, that because customer table does not change, i.e. the result of build hash does not have
Change, and due to sales table has been carried out with update, therefore it may only be necessary to execute again to customer table and sales table
Hash join operates, to obtain with the corresponding second final implementing result of sales table inserting a record and to be stored in institute
State in buffer area.
If customer end A has initiated identical SQL query statement again, database engine can directly obtain from buffer area
The described second final implementing result is taken to return to customer end A.
The method that the present embodiment provides, by, after raw data table changes, database engine judges in buffer area
With the presence or absence of the corresponding first final implementing result and middle implementing result, if existing, in described middle implementing result
On the basis of execute described executive plan again, to obtain the second final implementing result, initiate identical SQL in client second
During query statement, directly can obtain the described second final implementing result from buffer area and return to client, save inquiry
Time.
Fig. 4 is the structural representation of database engine embodiment one of the present invention.As shown in figure 4, the number that the present embodiment provides
Can include according to storehouse engine：
Receiver module 11, for receiving data manipulation language DML statement, described DML statement is used for making described database draw
Hold up and DML operation is carried out to raw data table；
Processing module 12, for executing after described DML operation, if judge to know be stored with buffer area before to described former
The first final implementing result and middle implementing result that beginning tables of data executes an executive plan and obtains, then wake up and be arranged to
Dormant described executive plan, and the basis of the raw data table in described middle implementing result and through DML operation
On execute described executive plan again, with the corresponding second final implementing result of raw data table obtaining with operate through DML
And be stored in described buffer area.
Wherein, described processing module 12 specifically can be used for waking up immediately and is arranged to dormant and described executes meter
Draw；Or, when meeting default trigger condition, waking up and be arranged to dormant described executive plan.
Described processing module 12 can be also used for executing described executive plan to described raw data table, obtains described first
Final implementing result and described middle implementing result, the working condition of described executive plan are set to sleep state, and deposit
Storage is in described buffer area.Described executive plan includes SQL SQL query statement.
The present embodiment provide database engine, can be used for execute said method embodiment technical scheme, its principle and
Technique effect is similar to, and here is omitted.
One of ordinary skill in the art will appreciate that：The all or part of step realizing above-mentioned each method embodiment can be led to
Cross the related hardware of programmed instruction to complete.Aforesaid program can be stored in a computer read/write memory medium.This journey
Sequence upon execution, executes the step including above-mentioned each method embodiment；And aforesaid storage medium includes：ROM, RAM, magnetic disc or
Person's CD etc. is various can be with the medium of store program codes.
Finally it should be noted that：Various embodiments above only in order to technical scheme to be described, is not intended to limit；To the greatest extent
Pipe has been described in detail to the present invention with reference to foregoing embodiments, it will be understood by those within the art that：Its according to
So the technical scheme described in foregoing embodiments can be modified, or wherein some or all of technical characteristic is entered
Row equivalent；And these modifications or replacement, do not make the essence of appropriate technical solution depart from various embodiments of the present invention technology
The scope of scheme.
Claims (8)
1. a kind of database result set cache method is it is characterised in that include：
Database engine receiving data manipulation language DML statement, described DML statement is used for making described database engine to original number
Carry out DML operation according to table；
After described database engine execution described DML operation, if judge to know be stored with buffer area before to described original number
Execute an executive plan according to table and the first final implementing result of obtaining and middle implementing result, then described database engine is called out
Wake up and be arranged to dormant described executive plan, and the original number in described middle implementing result and through DML operation
According to executing described executive plan on the basis of table again, with obtain with the raw data table corresponding second operating through DML
Whole implementing result is simultaneously stored in described buffer area；
Described executive plan is to carry out setting up Hash to index scan table and full table scan table and Hash connects, and wherein, described builds
Vertical Hash is for the described index operation that carries out of scan table, and it is to described index scan table and described complete that described Hash connects
The operation that table scan table is carried out, and described middle implementing result is the described result setting up Hash, described first final executes knot
Fruit is the result that described Hash connects.
2. method according to claim 1 is arranged to dormant it is characterised in that described database engine wakes up
Described executive plan, including：
Described database engine wakes up immediately and is arranged to dormant described executive plan；Or,
When meeting default trigger condition, described database engine wakes up and is arranged to dormant described executive plan.
3. method according to claim 1 and 2 is it is characterised in that described database engine receiving data manipulation language DML
Before sentence, also include：
Described executive plan is executed to described raw data table, obtains the described first final implementing result and described middle execution
As a result, the working condition of described executive plan is set to sleep state, and is stored in described buffer area.
4. method according to claim 3 is it is characterised in that described executive plan includes SQL SQL looks into
Ask sentence.
5. a kind of database engine is it is characterised in that include：
Receiver module, for receiving data manipulation language DML statement, described DML statement is used for making described database engine to former
Beginning tables of data carries out DML operation；
Processing module, for executing after described DML operation, if judge to know be stored with buffer area before to described original number
The the first final implementing result executing an executive plan according to table and obtaining and middle implementing result, then wake up and be arranged to sleep
The described executive plan of state, and described middle implementing result and through DML operation raw data table on the basis of again
The described executive plan of secondary execution, with the corresponding second final implementing result of raw data table that obtains with operate through DML and deposit
Storage is in described buffer area；
Described executive plan is to carry out setting up Hash to index scan table and full table scan table and Hash connects, and wherein, described builds
Vertical Hash is for the described index operation that carries out of scan table, and it is to described index scan table and described complete that described Hash connects
The operation that table scan table is carried out, and described middle implementing result is the described result setting up Hash, described first final executes knot
Fruit is the result that described Hash connects.
6. database engine according to claim 5 it is characterised in that described processing module specifically for：
Wake up immediately and be arranged to dormant described executive plan；Or,
When meeting default trigger condition, wake up and be arranged to dormant described executive plan.
7. the database engine according to claim 5 or 6 is it is characterised in that described processing module is additionally operable to：
Described executive plan is executed to described raw data table, obtains the described first final implementing result and described middle execution
As a result, the working condition of described executive plan is set to sleep state, and is stored in described buffer area.
8. database engine according to claim 7 is it is characterised in that described executive plan includes SQL
SQL query statement.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310275023.9A CN103377292B (en) | 2013-07-02 | 2013-07-02 | Database result set caching method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310275023.9A CN103377292B (en) | 2013-07-02 | 2013-07-02 | Database result set caching method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN103377292A CN103377292A (en) | 2013-10-30 |
| CN103377292B true CN103377292B (en) | 2017-02-15 |
Family
ID=49462400
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201310275023.9A Active CN103377292B (en) | 2013-07-02 | 2013-07-02 | Database result set caching method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103377292B (en) |
Families Citing this family (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106446153A (en) * | 2016-09-21 | 2017-02-22 | 广州特道信息科技有限公司 | Distributed newSQL database system and method |
| CN108536692B (en) * | 2017-03-01 | 2022-03-11 | 华为技术有限公司 | Execution plan generation method and device and database server |
| CN108038215A (en) * | 2017-12-22 | 2018-05-15 | 上海达梦数据库有限公司 | Data processing method and system |
| CN111597214A (en) * | 2019-02-21 | 2020-08-28 | 北京京东尚科信息技术有限公司 | Method, apparatus, computer system and medium for data query |
| CN111324607B (en) * | 2020-02-04 | 2021-09-14 | 中科驭数（北京）科技有限公司 | SQL statement multiplexing method and device |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102279880A (en) * | 2011-07-28 | 2011-12-14 | 深圳市五巨科技有限公司 | Method and system for updating cache in real time |
| CN102789475A (en) * | 2012-05-08 | 2012-11-21 | 华为技术有限公司 | Data synchronization method and device for result set cache at server end of database |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7797298B2 (en) * | 2006-02-28 | 2010-09-14 | Microsoft Corporation | Serving cached query results based on a query portion |
-
2013
- 2013-07-02 CN CN201310275023.9A patent/CN103377292B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102279880A (en) * | 2011-07-28 | 2011-12-14 | 深圳市五巨科技有限公司 | Method and system for updating cache in real time |
| CN102789475A (en) * | 2012-05-08 | 2012-11-21 | 华为技术有限公司 | Data synchronization method and device for result set cache at server end of database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN103377292A (en) | 2013-10-30 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107391653B (en) | Distributed NewSQL database system and picture data storage method | |
| CN103377292B (en) | Database result set caching method and device | |
| CN104778270A (en) | Storage method for multiple files | |
| CN103646073A (en) | Condition query optimizing method based on HBase table | |
| JP6795259B2 (en) | Methods and equipment for optimizing database transactions | |
| CN109885642B (en) | Hierarchical storage method and device for full-text retrieval | |
| CN116108057B (en) | Distributed database access method, device, equipment and storage medium | |
| US8965879B2 (en) | Unique join data caching method | |
| CN104050182A (en) | Configurable rule for monitoring data of in-memory database | |
| US9292405B2 (en) | HANA based multiple scenario simulation enabling automated decision making for complex business processes | |
| KR101424568B1 (en) | Client and database server for resumable transaction and method thereof | |
| CN110955719B (en) | Data access processing equipment, system and method | |
| US9390131B1 (en) | Executing queries subject to different consistency requirements | |
| CN113157411B (en) | Celery-based reliable configurable task system and device | |
| KR101666440B1 (en) | Data processing method in In-memory Database System based on Circle-Queue | |
| WO2016078388A1 (en) | Data aging method and apparatus | |
| CN106649584B (en) | Index processing method and device in master-slave database system | |
| CN106503027B (en) | Database operation method and device | |
| CN102375888A (en) | Method for deleting big files in distributed file system efficiently | |
| WO2020147334A1 (en) | Method and system for data query based on ignite cache architecture | |
| CN103544302A (en) | Partition maintenance method and device of database | |
| CN113377791A (en) | Data processing method, system and computing equipment | |
| CN102682087B (en) | Method, device and system for managing cache result set | |
| CN107239474B (en) | Data recording method and device | |
| CN103678173A (en) | Cache invalidation method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20220207 Address after: 550025 Huawei cloud data center, jiaoxinggong Road, Qianzhong Avenue, Gui'an New District, Guiyang City, Guizhou Province Patentee after: Huawei Cloud Computing Technology Co.,Ltd. Address before: 518129 Bantian HUAWEI headquarters office building, Longgang District, Guangdong, Shenzhen Patentee before: HUAWEI TECHNOLOGIES Co.,Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20221205 Address after: 518129 Huawei Headquarters Office Building 101, Wankecheng Community, Bantian Street, Longgang District, Shenzhen, Guangdong Patentee after: Shenzhen Huawei Cloud Computing Technology Co.,Ltd. Address before: 550025 Huawei cloud data center, jiaoxinggong Road, Qianzhong Avenue, Gui'an New District, Guiyang City, Guizhou Province Patentee before: Huawei Cloud Computing Technology Co.,Ltd. |