KR101780041B1 - Method and apparatus for improving throughput of database - Google Patents

Abstract

Disclosed are a method for increasing processing speed of a database and an apparatus thereof. The database apparatus, when receiving a query for a time series database, identifies whether a cache for the query exists, identifies a first area of the time series database in which the query is to be executed and a second area of the time series database for the cache, and obtains a final result for the query by using a result stored in the cache when the second area is placed in the first area.

Description

[0001] The present invention relates to a method and an apparatus for improving processing speed of a database,

The present invention relates to a method and apparatus for improving the processing speed of a database using a cache.

In order to manage a database, a query is used. Generally, an INSERT query, a DELETE query, an UPDATE query, and a SELECT query exist in a SQL (Structured Query Language). Insertion, deletion, and update queries all cause a substantial change in the data stored in the database, whereas the select query performs specific data retrieval or statistical computation, and does not cause any substantial change in the data stored in the database.

In a conventional DBMS (Database Management System), when a query is generated, all the data in the corresponding range of the table specified in the query is read, and the final result of the query is calculated. If the data in the database is updated, inserted, or deleted, it is necessary to read all the data in the corresponding range of the database and query the same as the previous one. In other words, even if some data is changed, the entire data is read again every time, and the same query is repeated, which causes the same load to be incurred all over the system.

Patent Application No. 10-2012-0051558

SUMMARY OF THE INVENTION The present invention provides a method and system for improving the processing speed of a database by performing a query by reading only a part of changed data without having to read all the data in the corresponding range of the database every time the query is repeatedly executed I have to.

According to an aspect of the present invention, there is provided a method for improving a processing speed of a database, the method comprising: receiving a query for a time series database; Determining whether a cache for the query exists; Determining a first area of the time series database to which the query is to be performed and a second area of the time series database for the cache; And if the second region is located within the first region, using the results stored in the cache to obtain a final result for the query.

According to an aspect of the present invention, there is provided an apparatus for processing a query, the apparatus comprising: a query receiver for receiving a query for a time series database; A cache search unit for determining whether a cache for the query exists; And a query execution unit for obtaining a final result of the query using a result stored in the cache if a second area of the time series database for the cache is located in a first area of the time series database to be queried .

According to the present invention, when a query is repeatedly performed, it is possible to greatly reduce the query execution time by utilizing previously performed query results without having to read all the data in the time series database every time and perform the query.

In a time series database in which data is sequentially accumulated, periodic execution of a specific query can be used suitably for a large database such as big data because the time taken to obtain the result can be kept constant regardless of the total amount of data.

FIG. 1 is a diagram illustrating an example of a schematic structure for improving a database processing speed according to the present invention.
2 is a diagram showing a configuration of an embodiment of a query processing apparatus according to the present invention,
3 illustrates a configuration of an embodiment of a cache according to the present invention,
4 shows an example of each area of a time series database for a query and a cache,
5 is a diagram illustrating an example of a process of obtaining a final result of a query using a result of intermediate calculation of a cache according to the present invention.
6 is a flowchart illustrating a method of improving the processing speed of a database according to an embodiment of the present invention,
FIG. 7 is a flowchart illustrating a cache search method according to an embodiment of the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, a database processing speed enhancement method and apparatus according to the present invention will be described in detail with reference to the accompanying drawings.

FIG. 1 is a diagram showing an example of a schematic structure for improving the database processing speed according to the present invention.

Referring to FIG. 1, upon receiving a query from the outside, the query processing apparatus 100 reads data from a range specified by a query from the time series database 110, performs a query, and outputs a final result.

The time series database 110 is a database for storing and analyzing data generated in a time series in real time. Generally, the time series database 110 stores log-likelihood data, so that it is almost read-only that data need not be changed.

The time series database 110 stores data in the order of the past to the present time based on the generation time of the data. The time series database may assign a data identifier (RID) to the data stored in the table in the form of increasing or decreasing sequentially with time. For example, the data identifier may be generated on a time basis, such as year, month, day, hour, minute, nanosecond, and so on. In this case, data for a specific time period can be easily grasped only by the data identifier.

When the data identifier is a sequential value, the time series database 110 can manage the table by keeping the identifier of the data generated the longest ago in the table header and the identifier of the most recently generated data. In this embodiment, as an example of data, there is a record stored in a table.

When the query processing apparatus 100 executes the query, the query execution result (the final result or the intermediate calculation result, etc.) is stored in the cache together with the information about the data area of the time series database 110 in which the query is performed. In the case where the data identifiers are sequentially incrementing values, the query processing device 100 stores the table identifier, the data identifier (BeginRID) at the start time point of the data area and the data identifier (EndRID) at the last time point as information about the data area . At this time, the data newly added after the cache storage can be easily grasped since it has an identifier of a value sequentially increased from the last data ID (EndRID).

When the same query as the previously performed query is issued, the query processing apparatus 100 does not read all the data of the corresponding area of the time series database 110 designated by the query, but rather reads the changed part (i.e., ) To perform the query only for that part, and the existing part uses the results stored in the cache. If there is no changed part after the execution of the previous query, the query processing apparatus 100 may use the results stored in the cache without directly reading the data of the time series database 110 at all.

When the cache according to the present embodiment is available, the query is not a query (for example, insert, delete, update query, etc.) causing actual data change, (E.g., a select query) occurs.

Some types of select queries that can be used in this embodiment are as follows.

<Type 1>

SELECT search target expression
FROM Name of table to be searched
WHERE search condition expression

A type 1 query is a query that executes a search condition on the table to be searched and returns values corresponding to the search target expression. In this case, statistics using search target data are not performed. The expression to be searched can be either a column of the table itself or an expression containing the columns.

Since the type 1 query does not perform the statistical operation, the query processing device 100 can store only the final result of the query execution in the cache. Therefore, when the query processing apparatus 100 performs the query again, only the data newly added after the storage time of the cache is read in the time series database 110, and the result is output as the final result of the query together with the data stored in the cache.

<Type 2>

An expression that contains a SELECT set function
FROM Name of table to be searched
WHERE search condition

The select query of type 2 retrieves the data satisfying the search condition and returns the result of performing the set function on the result set. The aggregate function includes a count that counts the number of result sets, a sum that computes the sum of the result sets, and so on. Even if there are several data in the result set that satisfies the search condition, one result is generated.

Upon receiving the query of type 2, the query processing apparatus 100 calculates a result of performing the aggregate function by reading only the newly added data after the storage time of the cache, and using the result and the result of the aggregate function stored in the cache , And produces the final result for the query. For example, if the query is to calculate the sum of data stored in the table, the query processing device 100 performs a sum operation on the data newly added to the table after the storage time of the cache, adds the sum to the total stored in the cache The final result of the query is computed.

The GROUP BY clause and the set function can be used to group the search result sets into specific conditions and perform statistical computation for each group. An example of this is the following type 3.

<Type 3>

SELECT base expression, aggregate function
FROM destination table
WHERE search condition
GROUP BY criteria expression
ORDER BY sequence expression
HAVING conditional clause containing aggregate sum result

In type 3, the SELECT clause contains an aggregate function that computes a statistical result and a base expression for which you want to search. The FROM clause specifies the table to be searched, and the WHERE clause sets the search conditions. The GROUP BY clause sets an expression to be used as an aggregation criterion, and the ORDER BY clause specifies the display order of search results including a statistic value. And a HAVING clause to restrain the result set as a result of the aggregate function.

When the query processing apparatus 100 receives the query of type 3, it performs grouping on newly added data after the storage time of the cache, performs operation on the group, and then uses the operation result of each group stored in the cache To produce the final result of the query.

2 is a diagram showing a configuration of an embodiment of a query processing apparatus according to the present invention.

2, the query processing apparatus 100 includes a query receiving unit 200, a cache searching unit 210, a query executing unit 220, at least one cache 230, 232 and 234, and a cache storing unit 240 .

The query receiving unit 200 receives the query. The query receiving unit 200 transmits the query to the query execution unit 220 when the received query is a query that causes a substantial change in data such as an insert, delete, or update query, As shown in FIG. The query receiving unit 200 transmits the query to the cache search unit 210 when the query is a query such as a select query that does not cause a substantial change in data but requests a query or a statistical operation.

The cache search unit 210 determines whether a cache for the query received from the query receiving unit 200 exists. The cache search unit 210 searches the cache in which the same query is stored to determine whether there is a cache for the query. In this case, when comparing the queries themselves, . Therefore, the cache search unit 210 preferably searches the cache based on the hash value for the query. The search process of the cache for the query is shown in FIG.

If there is no cache for the query, the cache search unit 210 transmits the query to the query execution unit 220 so that the query can be performed by the conventional method.

If there is a cache for the query, the cache search unit 210 grasps the first area of the time series database to be subjected to the current query and the second area of the time series database for the cache. If the second area is located in the first area and the data of the second area is not changed (for example, deleted or updated) after the cache is stored, the cache search unit 210 transmits the result stored in the cache to the query execution unit 220 .

Whether or not the data belonging to the second area is included in the first area is determined by comparing the data of the oldest data in the first area (BeginRID_1) and the latest data identifier (EnDRID_1), and can easily grasp the identifier (BeginRID_2) of the oldest data in the second area and the identifier (EndRID_2) of the latest data in the second area. For example, if the data identifiers sequentially increase according to the data generation time, the second area belongs to the first area if (BeginRID_1 <= BeginRID_2) & (EndRID_1> = EndRID_2).

Whether or not the data belonging to the second area has been changed may be determined based on whether the identifier of the data in which deletion or update has occurred is located within the interval of the data identifier of the second area It can be easily grasped. For example, when the deleted data identifier (RID_del) is located between the identifier intervals of the second area, that is, when BeginRID_2 <= RID_del <= EndRID_2, the data of the second area is changed.

The query execution unit 220 obtains the final result of the query from the cache search unit 210 using the result stored in the cache. For example, if the first area of the current query and the second area of the cache are the same, then the query execution unit 220 does not need to perform a query on the time series database, Output. As another example, when the second area is a part of the first area as shown in FIG. 4, the query execution unit 220 performs a query on the difference area between the first area and the second area, Use the result to get the final result for the current query. A specific method for obtaining the final result of the query in the case where the second region is a part of the first region will be described again in FIG. 4 and FIG.

The cache storage unit 240 stores the execution result of the query performed by the query execution unit 220 in the cache. In the above example, if the first region and the second region coincide with each other, and the final result of the query outputs the results stored in the cache, there is no need to store them again because the same cache exists.

According to the embodiment, the cache storage unit 240 may store the cache only when it is more efficient to use the cache, rather than always storing the query execution result. The final result for the query and the condition for storing the intermediate result may differ.

For example, the storage of intermediate results is stored when the cost of being used to restore the cache is less than the cost of performing the actual query from scratch. For example, when the result of a set function for each group is calculated as shown in <Type 3> among the types of queries shown in FIG. 1, it can be stored as an intermediate result.

The final result is stored when the execution time of the query is longer than a predetermined time. Queries that take too little time should be done directly, so it is better not to store them in the cache. As another example, when the final result of a query exceeds a preset memory size, it is preferable not to store it in order to prevent a system load due to memory use.

3 is a diagram showing a configuration of an embodiment of a cache according to the present invention.

Referring to FIG. 3, the cache 230 can be divided into a final result cache 300 storing a final result of query execution, and an operation result cache 350 storing an intermediate operation result of query execution.

The cache 230 includes a query field 305, an LRU list field 310, a hash list field 315, a first region information field 320, a management information field 325, a final result field 330, An area information field 355, and an intermediate calculation result field 360. [ The cache of this embodiment is an example for helping understanding of the present invention, and the structure of the cache is not necessarily limited to this, and can be variously modified according to the embodiment.

The query field 305 contains the query used to produce the final result or intermediate result stored in the current cache.

The LRU list field 310 includes a cache list sorted in order of recent use in order to store and manage a plurality of caches in a certain storage space in a Least-Recently-Used (LRU) manner. For example, when a new cache is created in a state where a maximum of 100 caches can be stored in the storage space, a cache that has not been used for the longest time is deleted by referring to the LRU list among 100 existing caches, Lt; / RTI &gt;

The hash list field 315 includes at least one cache list in which the hash value is the same but the query is different. For example, the first cache is for a first query, the second cache is for a second query, but the hash values for the first query and the second query may be the same. When the query processing device finds a cache having the same hash value, it can easily find another cache having the same hash value by referring to the hash list field 315 of the cache.

The first and second area information fields 320 and 355 indicate data area information of a time series database referenced in order to obtain a final result or an intermediate calculation result in the query field 305. For example, the area information field may include a table identifier, a data identifier at the beginning of the data area (BeginRID), and a data identifier at the end (EndRID).

The management information field 325 may include various information according to the embodiment such as the user information requesting the query, the number of times the corresponding cache is referenced, and the reference date. The cache can be managed by the LRU method using the reference count stored in the management information field 325 and the reference date.

The final result field 330 contains the final result of performing the query.

The intermediate result field 360 stores the result of the operation performed to obtain the final result of the query. For example, if the query is the same as in FIG. 1 (type 3), the intermediate result field stores the result of the group operation for each group in the form of a list.

4 is a diagram showing an example of each area of a time series database for a query and a cache.

Referring to FIG. 4, the first area of the time series database 400 where the query is to be performed and the second area of the time series database of the cache may be different from each other. In other words, since the final result or the intermediate result stored in the cache is the result of performing the query on the second area of the time series database (for example, data between the data identifiers RID1 to RID2) Can not be used as a result of the execution of.

If there is no change in the data for the second area after cache storage, the results stored in the cache can be used for the second area. Therefore, the query processing apparatus does not read the entire first region of the time series database (for example, the data between the data identifiers RID1 to RID3) again to perform the query, but instead performs the query by comparing the first region and the second region with the difference region , Data between the data identifiers RID2 to RID3), and the query execution speed can be improved by using the query execution result stored in the cache as it is for the second area.

Although the present embodiment shows a case where data is newly added after cache storage, the present invention can be applied as it is when a query further including past data before the second region for the cache is performed. Alternatively, the present invention can be applied to a case where a query further including past data and newly added data of the second area is performed.

5 is a diagram illustrating an example of a process of obtaining a final result of a query using the intermediate calculation result of the cache according to the present invention.

4 and 5, when the second area stored in the cache is a part of the first area in which the query is to be performed, the query processing apparatus analyzes the query, classifies the same operation method in the query, 500, 502, 504, and 506).

The query processing apparatus not only performs an operation on the entire first region but also restores the results (500, 502, 504) of the operation corresponding to the second region into the value 520 stored in the cache, (530) and obtains the intermediate calculation result (506) for the difference region. The query processor then uses the intermediate results 500, 502, 504, and 506 to obtain the final result 510.

For example, each intermediate result 500, 502, 504, and 506 may be the result of an aggregate operation for each group in the query of FIG.

FIG. 6 is a flowchart showing a flow of an embodiment of a method for improving the processing speed of a database according to the present invention.

Referring to FIG. 6, the query processing apparatus receives a query (S600). If the query is about insertion, deletion, or update, the query processing device performs the query in a conventional manner.

If the query is related to a query, a statistical operation, or the like, the query processing apparatus searches whether there is a cache for the query (S610). If there is no cache for the query (S620), the query processing device performs the query in the conventional manner (S660) and stores the query execution result in the cache (S670).

Time series databases continue to accumulate over time or erase existing data. Therefore, even if there is a cache for the query, the first area of the time series database to which the query is to be performed and the second area of the time series database for the cache may be different from each other.

If the first region and the second region coincide with each other and the data for the second region has not been changed up to the present time since the cache is stored (S630), the query processing apparatus proceeds to Step S630 to return the final result stored in the cache as the final result for the current query (S640). If the second area is a part of the first area and the data for the second area is not changed until the present time after the cache is stored (S630), the query processing device stores the intermediate calculation result stored in the cache, The final result is calculated using the intermediate calculation result of the query performed on the difference area of the difference region (S650). If the data for the second area has been changed since the cache is stored, the query processing device performs the query in the same manner as the conventional method.

FIG. 7 is a flowchart illustrating a cache search method according to an embodiment of the present invention.

Referring to FIG. 7, the query processing apparatus obtains a hash value for a query (S700). The query processing apparatus searches the cache using the hash value (S710). The query processing device compares the query to be performed and the query stored in the query field (305 in FIG. 3) of the cache with each other (S720). If the query is not the same, the query processing device uses the hash list of the cache (315 in FIG. 3) to determine whether another cache with the same hash value exists. If there is another cache, the query processing apparatus again performs a process of determining whether or not the other cache is a cache for a query to be performed.

The present invention can also be embodied as computer-readable codes on a computer-readable recording medium. A computer-readable recording medium includes all kinds of recording apparatuses in which data that can be read by a computer system is stored. Examples of the computer-readable recording medium include ROM, RAM, CD-ROM, magnetic tape, floppy disk, optical data storage, and the like. The computer-readable recording medium may also be distributed over a networked computer system so that computer readable code can be stored and executed in a distributed manner.

The present invention has been described with reference to the preferred embodiments. It will be understood by those skilled in the art that various changes in form and details may be made therein without departing from the spirit and scope of the invention as defined by the appended claims. Therefore, the disclosed embodiments should be considered in an illustrative rather than a restrictive sense. The scope of the present invention is defined by the appended claims rather than by the foregoing description, and all differences within the scope of equivalents thereof should be construed as being included in the present invention.

Claims (13)

A method for improving a processing speed of a database by a query processing device,
The processing speed enhancement method includes:
Receiving a query for a time series database;
Determining whether a cache for the query exists;
Determining a first region of the time series database in which the query is to be performed and a second region of the time series database used to obtain a query execution result stored in the cache; And
If the second region is located in the first region, reading the data of the time series database for the region excluding the second region in the first region, and comparing the result obtained by performing the query with the query execution result stored in the cache And obtaining a final result of the query using the query result. 2. The method of claim 1, wherein obtaining a final result for the query comprises:
Determining whether data changes in the second area between a generation time of the cache and a reception time of the query; And
And outputting a result stored in the cache as a final result for the query if there is no data change in the second region and the first region and the second region coincide with each other, How to improve. 2. The method of claim 1, wherein obtaining a final result for the query comprises:
Performing the query on a difference region between the first region and the second region when the second region is a portion of the first region; And
And outputting a result of performing the query on the difference region and a final result calculated using a result stored in the cache. The method of claim 3,
Wherein the cache comprises an operation result that is a result of an operation occurring in performing the query on the second region,
Wherein obtaining the final result for the query comprises:
Calculating an operation result for the difference area; And
And calculating the final result of the query for the first region using the computation result for the second region stored in the cache and the computation result for the difference region, How to improve. The method according to claim 1,
And if the cache for the query does not exist, storing a cache including a result of performing a query on the first area. The method of claim 1, wherein the step of determining whether the cache exists includes:
Obtaining a hash value for the query;
Retrieving a cache including a hash value equal to the hash value; And
Determining whether the query is the same as the query stored in the retrieved cache,
Wherein the cache includes at least one cache list in which the hash value is the same but the query is different,
Wherein the step of searching for the cache searches at least one cache having the same hash value using the cache list. A query receiver for receiving a query for a time series database;
A cache search unit for determining whether a cache for the query exists; And
When the second region of the time series database used for obtaining the query execution result stored in the cache is located in the first region of the time series database in which the query is to be performed, And a query execution unit for obtaining the final result of the query by using the result obtained by performing the query by reading the data of the database and the query execution result stored in the cache. 8. The apparatus of claim 7,
If there is no data change in the second region between the generation time of the cache and the reception of the query and if the first region and the second region coincide with each other, a result stored in the cache is calculated as a final result for the query And the query processing unit. 8. The apparatus of claim 7,
And a final result is calculated using the result of the query for the difference region between the first region and the second region and the result stored in the cache when the second region is a part of the first region To the query processing unit. 8. The method of claim 7,
A query field that stores the query;
A hash list field storing a list of at least one cache having a hash value for the query;
An area information field indicating an area where the query is performed; And
And a result field for storing a final result or an intermediate result of the query. 11. The apparatus of claim 10,
A query stored in a query field of a cache including a hash value equal to a hash value of a query received by the query receiving unit is received by the query receiving unit Whether the query is the same as the received query. 8. The apparatus of claim 7,
And generating and storing a cache including a result of performing a query for the first area if the cache for the query does not exist. A computer-readable recording medium storing a program for performing the method according to any one of claims 1 to 6.

Images