JP5186270B2 - Database cache system - Google Patents

Description

  The present invention relates to a database cache system, and more particularly to a cache system applicable to an insert-only database.

  As a database access method, there is a method called insert only. In this method, an update operation to a database table is not performed by actually updating (delete) or deleting (delete) an existing record, but is always performed by inserting a new record. In this method, each record of the database table includes, together with a key identifier that is data identification information, information indicating an operation content called a CRUD value, and information indicating a time when the operation is performed. (Time stamp) is stored. Since a new record is inserted every time a change operation is performed, a plurality of records having the same key identifier and different time stamp values (hereinafter referred to as time stamp information) exist as many as the number of change histories. Become. Here, the CRUD value “CRUD” is an acronym for “Create”, “Update”, and “Delete”.

  This insert-only method was adopted because of the design requirement to reduce the probability of lock contention, in addition to the need to satisfy the auditing requirement of saving all past change history. There are many cases. For example, taking an application related to finance as an example, online processing is generally performed using the latest data, such as displaying the current account balance and other information, and performing transfer processing. On the other hand, in batch processing, interest rate calculation is performed on the balance at a certain point in the past, and a monthly report is created. That is, since the time stamp information which is the starting point in the online processing and the batch processing is different, even when these processings are executed simultaneously and data having the same key identifier is accessed, different records are accessed. As a result, lock contention is less likely to occur.

  By the way, as a general means for speeding up processing such as access to a database, there is a method using a cache memory (for example, see Patent Document 1). That is, the data fetched from the database is cached in the server. For queries that are repeatedly issued, it is checked whether the target data can be used in the cache by, for example, drawing a hash table based on the parameters.

JP 2006-511876 A

The insert-only method has the following problems.
First, in accessing the database, time stamp information must always be specified and the CRUD value must be checked. For this reason, an operation such as using a subquery is required, and the query processing becomes complicated and heavy.
Second, even when only a part of the data is changed, it is not possible to selectively fetch and write only the target column in the database table. Therefore, it is necessary to fetch all column contents once.
Furthermore, even if you want to fetch one record of the latest data from the database, you need to specify the time stamp information, so the caller generates the current time stamp information (currentTimeStamp) I have to give it.

  Depending on the contents of the application, these overheads can cause a decrease in the performance of the entire system. In particular, when business logic is repeatedly executed, such as in batch processing, this overhead cannot be ignored. Even for tables that are rarely updated, it is problematic in terms of application performance to repeat expensive database access for each transaction.

  For such a problem, it is conceivable to increase the speed by using a cache memory for normal database access. As described above, in an insert-only application, a record is specified by a combination of a key identifier and time stamp information. Therefore, a query is applied by this combination, and data is stored in the hash table using this combination as a search key. It will be cached. As the time stamp information, different data is passed each time read for query creation. For this reason, the cache is not hit in the conventional search method performed simply based on the hash table, and is not an effective means for redundant reading. For example, when trying to read the latest record with a key identifier, whether or not the cached data is the latest record among the records with the key identifier even if the data with the key identifier is cached Cannot be judged.

  An object of the present invention is to solve these problems and to realize a cache system that is effective even in an insert-only system application.

  In order to achieve the above object, the present invention is configured as the following system. This system includes a holding unit that caches data read from the database and a holding unit that adds a data record to which the same identification information is added every time data is created, updated, or deleted. A management unit that manages data cached in the unit. The holding unit includes a table for registering identification information of data, and a map for storing an entry indicating a record having the identification information in the database, individually associated with the identification information registered in the table. The management unit accepts a query including identification information and information specifying a record, searches the holding unit, reads the data if the corresponding data is cached, and reads the data if the corresponding data is not cached. Is retrieved from the database, and this data is cached in association with the entry corresponding to the identification information and record specified by the query in the map of the holding unit.

Here, the map is stored in the database and is generated when data having predetermined identification information that is not registered in the table is accessed for the first time.
In the map entry, time information given to the record when the record is created is recorded. Then, the management unit accepts a query that describes a condition for uniquely determining this time information as information for specifying a record, and indicates an entry having time information specified according to this condition as the record specified by this query. Entry.
More specifically, this time information is a time stamp indicating the time when an operation is performed on the data that caused the record to be created.
When data having identification information specified by the query does not exist in the database, information indicating that is stored in the holding unit in association with the identification information registered in the table.
When identification information is specified step by step by executing a query, an auxiliary entry is provided between the table and the map, and the highest-level identification information described in the query is registered in the table, and the lower level obtained by executing the query The identification information is associated with the identification information in the table and registered in the auxiliary entry, and the map is associated with the lowest-level identification information registered in the auxiliary entry.
When a record of data having predetermined identification information is added in the database, the identification information registered in the table and the map associated with the identification information are deleted in the holding unit, and the data associated with the map entry When is cached, the cached data is also deleted.
Alternatively, when a data record having predetermined identification information is added in the database, an entry associated with this record is added to the map associated with the identification information.

The present invention is also realized as the following data structure. This data structure consists of a table for registering identification information in the database to which data records to which the same identification information is assigned each time data is created, updated, or deleted, and the identification information registered in the table. And a map that stores entries indicating records with identification information in the database, and the data obtained from the database based on a given query corresponds to the identification information and records specified by the query in the map It is cached in association with the entry.
In this map, entries are arranged in time series and stored based on time information given to the records when the database records are created.
When data having identification information registered in the table does not exist in the database, information indicating that no data exists is held instead of the map.

  The present invention is also realized as the following method. This method is a database caching method that adds a record of data to which the same identification information is assigned every time data is created, updated, or deleted, and includes a query including identification information and information specifying the record. Accepting and searching for the identification information specified by the query with respect to the table for registering the identification information of the data, and if the identification information specified by the query does not exist in the table, register this identification information in the table, A step of generating a map storing an entry indicating a record having the identification information in the database in association with the identification information, and when the identification information specified by the query exists in the table, the map is associated with the identification information. If the data associated with the map entry is cached, Reads data, the data associated with the entry and a step of caching in association with data read from the database if not cached in the entry.

  Furthermore, the present invention can also be realized as a program that controls a computer to realize each function in the above system or a program that causes a computer to execute processing corresponding to each step in the above method. This program can be provided by being stored and distributed in a magnetic disk, an optical disk, a semiconductor memory, or other recording medium, or distributed via a network.

  According to the present invention configured as described above, it is possible to realize a cache system that is effective even in an insert-only application in a database.

Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings.
<System configuration>
FIG. 1 is a diagram illustrating a functional configuration example of a database system to which the present embodiment is applied.
As shown in FIG. 1, the database system 100 according to the present embodiment includes a database 110 and an access unit 120, and also includes a data holding unit 130, a cache management unit 140, and a map creation unit for realizing a cache system. 150. As another configuration example, each component 130, 140, 150 of the cache system may be realized not as a part of the database system but as a part of an application system that accesses the database. Furthermore, it can be realized as an independent layer between the application system and the database system. In these cases, the access unit 120 indicates a part for accessing data in the application system.

FIG. 2 is a diagram illustrating a hardware configuration example of a computer constituting the database system 100.
The computer 10 shown in FIG. 2 includes a CPU (Central Processing Unit) 10a that is a calculation means, a main memory 10c that is a storage means, and a magnetic disk device (HDD: Hard Disk Drive) 10g. In addition, a network interface card 10f for connecting to an external device via a network, a video card 10d and a display device 10j for performing display output, and an audio mechanism 10h for performing audio output are provided. Furthermore, an input device 10i such as a keyboard or a mouse is provided.

  As shown in FIG. 2, the main memory 10c and the video card 10d are connected to the CPU 10a via the system controller 10b. The network interface card 10f, the magnetic disk device 10g, the sound mechanism 10h, and the input device 10i are connected to the system controller 10b via the I / O controller 10e. Each component is connected by various buses such as a system bus and an input / output bus. For example, the CPU 10a and the main memory 10c are connected by a system bus or a memory bus. Between the CPU 10a and the magnetic disk device 10g, the network interface card 10f, the video card 10d, the audio mechanism 10h, the input device 10i, etc., PCI (Peripheral Components Interconnect), PCI Express, serial ATA (AT Attachment) , USB (Universal Serial Bus), AGP (Accelerated Graphics Port) and other input / output buses.

  Note that FIG. 2 merely exemplifies a hardware configuration of a computer suitable for application of the present embodiment, and it is needless to say that each actual server is not limited to the illustrated configuration. For example, instead of providing the video card 10d, only the video memory may be mounted and the CPU 10a may process the image data. Further, the audio mechanism 10h may be provided as a function of a chip set that constitutes the system controller 10b and the I / O controller 10e without being an independent configuration. In addition to the magnetic disk device 10g, an auxiliary storage device may be provided with a drive using various optical disks and flexible disks as media. A liquid crystal display is mainly used as the display device 10j, but any other type of display such as a CRT display or a plasma display may be used.

  In the database system 100 shown in FIG. 1, the functions of the access unit 120, the cache management unit 140, and the map creation unit 150 are executed by the CPU 10 a executing the program loaded in the main memory 10 c in the computer shown in FIG. 2, for example. It is realized by doing. The database 110 is realized by, for example, the magnetic disk device 10g, and the data holding unit 130 of the cache system is realized by providing a cache area in the main memory 10c, for example.

  In FIG. 1, a database 110 stores data to be searched. In this embodiment, access to the database 110 is performed by an insert-only method. In this method, a new record is additionally created in the database 110 every time a data update operation is performed. In the created record, a key identifier for identifying data and time stamp information indicating the time when the operation causing the creation of the record is performed are recorded. Therefore, the database 110 includes a plurality of records having different time stamp information even for the same key identifier as many as the number of change histories. That is, the time stamp information can be used as identification information for records having the same key identifier. In each record, a CRUD value representing the operation content is recorded. In reading data, since the state of the database 110 is not changed, a new record is not generated (inserted). Therefore, any one of “create”, “update”, and “delete” is recorded in the column (column) of the CRUD value.

FIG. 3 is a diagram illustrating a configuration example of the database 110.
In the database 110 illustrated in FIG. 3, there are three records with different time stamp information for the key identifier “10032752046925”. The CRUD values of these records are “C”, “U”, and “D” in order from the oldest time stamp information, where “C” is created (Create), “U” is updated (Update), “ “D” represents deletion. That is, the example of FIG. 3 shows that the data with the key identifier “10032752046925” has been created, updated once, and then deleted.

  The access unit 120 receives a query described in SQL or the like, accesses the database 110, and executes processing such as record creation, reading, update, and deletion. The access unit 120 instructs the cache management unit 140 to operate the cache when accessing the database 110.

  The data holding unit 130 holds cache data. In the present embodiment, cache data is held by a data structure that is a combination of a hash table for key identifiers and a data structure called a map that represents the entire change history for each key identifier. Details of this data structure will be described later.

  The cache management unit 140 manages the cache data held in the data holding unit 130. Then, in response to an instruction from the access unit 120, an operation related to data caching is performed. Specifically, the record corresponding to the query of the read request by the access unit 120 is searched, and a response is returned according to the search result. If the corresponding record is not cached, the read record is cached. If the key identifier corresponding to the query does not exist in the hash table of the data holding unit 130, the key identifier is registered in the hash table and the map creating unit 150 creates a map related to the key identifier.

  The map creation unit 150 creates a map of the record with the specified key identifier under the control of the cache management unit 140. The map is a record of record change history for each key identifier in time series. The data structure of the cache including this map will be described below.

<Cache data structure>
FIG. 4 is a diagram showing an example of the data structure of the cache in this embodiment. FIG. 5 is a diagram showing an example of the data structure of the map entry in FIG.
As shown in FIG. 4, the cache of this embodiment has a data structure in which a hash table 410 and a map 420 are combined. In FIG. 4, a key identifier that has been read at least once and a map 420 associated with the key identifier are registered in the hash table 410. In the hash table 410 of FIG. 4, a portion indicated in gray is a portion where the key identifier is registered.

  When a key identifier is registered in the hash table 410, a map 420 of this key identifier is created. In this map 420, map entries in which time stamp information (timeStamp), CRUD value (crudVal), and data object (entryObj) are recorded are arranged in descending order (that is, time series) of the time stamp information. The data object is a pointer to the contents of the cached record corresponding to the map entry, and is initially “null”. For example, the time stamp information “TS1”, the CRUD value “U (update)”, and the data object “null” are recorded in the map entry 421 shown in FIG.

  The map 420 is created when data having a predetermined key identifier that is stored in the database 110 and is not registered in the hash table 410 is accessed by the access unit 120 for the first time. At this time, “null” is recorded in the data object field of each map entry. Thereafter, when a record having this key identifier and having predetermined time stamp information is read, the key identifier and the time stamp information are read from the database 110 to the data object field of the corresponding map entry, and the data holding unit A pointer to the contents of the cached record is recorded in 130. In the example shown in FIG. 4, in the map entry 422, the contents of the corresponding record are recorded in the data object.

  If there is no data having the key identifier specified in the query, the cache management unit 140 cannot create a map of the key identifier. In this case, the cache management unit 140 registers the key identifier in the hash table 410 and stores in the data storage unit 130 data indicating that there is no data having the key identifier in place of the map 420. Let In the example shown in FIG. 4, the data 423 indicates that there is no data having the corresponding key identifier.

  The creation of the map is executed by the cache management unit 140 issuing a dedicated query for fetching only the time stamp information and the CRUD value from each record having the target key identifier. The specific contents of the map creation query differ depending on the original data fetch contents (query). For example, “for the record (s) having the key identifier bound in the main query, the latest record before the specified time stamp information is selected in the subquery, and its CRUD value is“ D (delete) Consider a case where a data fetch such as “return non-return” is performed. The query (SQL sentence) when such access is performed is as shown in FIG. 6, for example. And the code | cord | chord which produces | generates a data structure from the query for exclusive use for creating the map corresponding to this and a query result becomes like FIG.

  By the way, when an access other than reading is performed on the data in which the key identifier and the map 420 exist in the hash table 410, a new record is generated (inserted) in the database 110 and stored in the database 110. The recorded record and the contents of the map 420 do not match. In this case, the cache management unit 140 once releases the cache for the key identifier. That is, the key identifier of the hash table 410 and the map 420 associated with the key identifier are deleted, and the record held in association with the map entry of the map 420 is also deleted. Then, when the same key identifier is next accessed, registration to the hash table 410 and creation of a map are performed again.

  Further, instead of releasing the cache when a new record is generated in the database 110, the map 420 can be updated to cope with it. A record for the database 110 is generated with the latest time stamp information. Therefore, the cache management unit 140 creates a new map entry, sets time stamp information, a CRUD value, and a data object, and then adds this new map entry to the top of the corresponding map 420 in the data holding unit 130. To do.

Here, for checking whether the database 110 has been updated, the conventional technique can be applied as it is. However, by using the structure of managing the cache by the map 420 in this embodiment, the following simple process is possible. It can also be done by the method. That is, it is a method of issuing a lightweight query that returns the number of records having a given key identifier in the database 110 and checking whether the cached data is usable. If the return value from the query matches the number of entries in the current map 420, it can be determined that the database 110 has not been updated since the map 420 was created for this key identifier. Therefore, the cached data can be used as it is. On the other hand, if the return value is greater than the number of entries in the map 420, it can be determined that the database 110 has changed since the map 420 was created. Therefore, the above processing (cache release or map 420 update) is performed. Note that a lightweight query that returns the number of records is, for example, a procedure for creating a map 420 as shown in FIG.
“Select count (*) from TABLE where KEY_IDFR =?”
become that way.

  If the data structure shown in FIG. 4 is used, with respect to data in which the key identifier is registered in the hash table 410, information including time stamp information regarding all records having the key identifier in the database 110 is recorded in the map 420. The Therefore, when any record is to be acquired based on the time stamp information given to the query, which record corresponds to which entry on the map 420 and whether or not the content of the record is cached. Can be judged. Therefore, if the corresponding record is cached, the record is read and returned. If the corresponding record is not cached, the access unit 120 moves to the database 110 search, and then the search result is acquired and data is retained. The cache may be cached in the unit 130. Further, by associating information indicating that no data exists in the hash table 410 with a predetermined key identifier that has already been searched but is known to have no data in the database 110, the database 110 is actually Without searching, it can be recognized that the desired data does not exist.

<Operation of the cache system>
Next, the operation of the cache system configured by the data holding unit 130, the cache management unit 140, and the map creation unit 150 will be described.
FIG. 8 is a flowchart showing the operation flow of the cache system.
When data is read by a query in which a key identifier and time stamp information are specified, the access unit 120 causes the cache management unit 140 to perform a cache search for the corresponding data (step 801). Based on an instruction from the access unit 120, the cache management unit 140 first checks whether the key identifier specified in the query is registered in the hash table 410.

  When the key identifier specified by the query is registered in the hash table 410 (Yes in Step 802), the cache management unit 140 next searches the map 420 associated with the key identifier (Step 803). . If a record matching the condition based on the time stamp information specified by the query is cached (Yes in step 804), the cache management unit 140 reads the record and returns it to the access unit 120 (step 805).

  On the other hand, as a result of searching the map 420, if the record that matches the condition based on the time stamp information specified by the query is not cached (No in step 804), that is, if the data object is “null”, the cache management The unit 140 returns the result to the access unit 120. Then, the search result of the database 110 by the access unit 120 is acquired (step 806), and the record read from the database 110 is cached in association with the map entry of the map 420 (step 807).

  On the other hand, when the key identifier specified by the query is not registered in the hash table 410 (No in step 802), the cache management unit 140 indicates that the record matching the query is not cached. And the key identifier is registered in the hash table 410 (step 808). The cache management unit 140 issues a dedicated query for creating the map 420 (step 809).

  If data having the key identifier specified by the query exists in the database 110 (Yes in step 810), information such as time stamp information of the record having the key identifier is obtained. In this case, the cache management unit 140 creates the map 420 based on the obtained information (step 811). Next, the cache management unit 140 acquires the search result of the database 110 by the access unit 120 (step 812), and caches the record read from the database 110 in association with the map entry of the map 420 (step 813). .

  On the other hand, when the data having the key identifier specified by the query does not exist in the database 110 (No in step 810), the map 420 for the key identifier cannot be created. In this case, the cache management unit 140 causes the data holding unit 130 to hold information indicating that no data exists in association with the key identifier registered in the hash table 410 in step 808 (step 814).

<Other examples of cache data structure>
The data structure of the cache in the cache system according to the present embodiment is composed of a combination of a hash table 410 for registering key identifiers and a map 420 for managing records having the same key identifier in time series. Here, the configuration itself of the hash table 410 and the map 420 can be changed according to the contents of the data fetch.

  Consider the query (SQL statement) shown in FIG. In this query, fetching with a key identifier and time stamp information is the same as in the example of FIG. 6, but a column (IDFR2) not specified in the main query is bound in the subquery. This is because the subquery returns a set of records having the latest time stamp among the records having the specified key identifier among the records having the specified key identifier, and only those whose CRUD value is not “D (delete)”. Finally return.

FIG. 10 is a diagram illustrating an example of a data structure of a cache corresponding to such a query.
In the data structure shown in FIG. 10, auxiliary entries (Auxiliary entries) 430 are provided between the hash table 410 and the map 420, and key identifiers specified step by step as the query is executed are The auxiliary entries 430 are registered hierarchically. Then, the map 420 is registered in association with the lowest layer key identifier registered in the auxiliary entry 430.

  FIG. 11 shows a code for generating a data structure from a dedicated query for creating a map in the data structure shown in FIG. 10 and a query result. When the query of FIG. 11 is executed, a map 420 arranged in time stamp order for each value of IDFR2 is created from the entry of the hash table 410 via the list of auxiliary entries 430 in which IDFR2 is arranged in ascending order. Is done. When a query in which a predetermined key identifier and time stamp information are specified is accepted for the cache in this state, for example, a set of entries surrounded by a one-dot chain line in FIG. 10 is specified. Then, if necessary, the database 110 is accessed to obtain a record of the data object (entryObj) corresponding to the query, and then a set of objects excluding the CRUD value “D (deleted)” is returned.

  As described above, the data structure suitable for the case where the key identifier is specified step by step as the query is executed has been described. Depending on the contents of the query, auxiliary entries 430 can be provided in several stages.

  As mentioned above, although this embodiment was described, the technical scope of this invention is not limited to the range as described in the said embodiment. For example, in the above embodiment, since the query is issued by designating the key identifier and the time stamp information, the time stamp information is used to associate each map entry with the record in the map. On the other hand, the map may be created by appropriately using other information that can uniquely identify the record corresponding to the query. In addition, it is clear from the description of the scope of the claims that various modifications or improvements added to the above embodiment are also included in the technical scope of the present invention.

It is a figure which shows the function structural example of the database system to which this embodiment is applied. It is a figure which shows the hardware structural example of the computer which comprises a database system. It is a figure which shows the structural example of the database of this embodiment. It is a figure which shows the example of the data structure of the cache in this embodiment. It is a figure which shows the example of the data structure of the map entry in FIG. It is a figure which shows the example of the query for data fetch. It is a figure which shows the example of the code | cord | chord which produces | generates a data structure from the query for exclusive use for creating the map of this embodiment corresponding to the query of FIG. 6, and a query result. It is a flowchart which shows the flow of operation | movement of the cache system of this embodiment. It is a figure which shows the other example of the query for a data fetch. It is a figure which shows the example of the data structure of the cache of this embodiment corresponding to the query of FIG. It is a figure which shows the example of the code | cord | chord which produces | generates a data structure from the query for exclusive use for creating the map in the data structure shown in FIG. 10, and a query result.

Explanation of symbols

DESCRIPTION OF SYMBOLS 100 ... Database system, 110 ... Database, 120 ... Access part, 130 ... Data holding part, 140 ... Cache management part, 150 ... Map creation part, 410 ... Hash table, 420 ... Map, 421, 422 ... Map entry

Claims (10)

In a database that adds a record of the data with the same identification information every time data is created, updated, or deleted,
A holding unit that caches data read from the database;
A management unit that manages data cached in the holding unit;
The holding part is
A table for registering the identification information of the data;
A map for storing an entry indicating a record having the identification information in the database, individually associated with the identification information registered in the table,
The management unit
Accepting a query including information specifying the identification information and a record, searching the holding unit,
If the corresponding data is cached, read the data,
If the corresponding data is not cached, the data is acquired from the database, and the data is cached in association with the entry corresponding to the identification information and record specified by the query in the map of the holding unit. system. The system according to claim 1, wherein the map is generated when the data is stored in the database and has the predetermined identification information that is not registered in the table for the first time. In the entry of the map, time information given to the record when the record is created is recorded,
The management unit accepts a query that describes a condition for uniquely determining the time information as information for designating the record, and indicates an entry having time information specified according to the condition as a record designated by the query The system according to claim 1, wherein the system is an entry. The system according to claim 3, wherein the time information is a time stamp indicating a time when an operation is performed on the data that causes the record to be created. The data indicating that when the data having the identification information specified by the query does not exist in the database, the information indicating that is stored in the holding unit in association with the identification information registered in the table. The system according to 1. When the identification information is specified in stages by executing a query, an auxiliary entry is provided between the table and the map, and the highest-level identification information described in the query is registered in the table. The lower-level identification information obtained by executing is registered in the auxiliary entry in association with the identification information in the table, and the map is associated with the lowest-level identification information registered in the auxiliary entry. The system described in. When a record of data having predetermined identification information is added in the database, the holding unit deletes the identification information registered in the table and the map associated with the identification information, and the map 2. The system of claim 1, wherein when data associated with the entry is cached, the cached data is also deleted. The system according to claim 1, wherein when a record of data having predetermined identification information is added in the database, an entry associated with the record is added to the map associated with the identification information. . A database caching method for adding a record of the data with the same identification information every time data is created, updated, or deleted,
Receiving a query including the identification information and information specifying a record, and searching the identification information specified by the query against a table for registering the identification information of data;
When the identification information specified by the query does not exist in the table, the identification information is registered in the table, and a map storing an entry indicating a record having the identification information in the database is associated with the identification information. Generating step;
When the identification information specified by the query exists in the table, if the data associated with the entry of the map associated with the identification information is cached, the data is read and associated with the entry Caching the retrieved data from the database in association with the entry if the data is not cached. To the computer that controls the database system that adds a record of the data to which the same identification information is assigned every time data is created, updated, or deleted,
A process of accepting a query including information specifying the identification information and a record, and searching the identification information specified by the query against a table that registers the identification information of data;
If the identification information specified by the query does not exist in the table, the identification information is registered in the table, and a map that stores an entry indicating a record having the identification information in the database in association with the identification information is generated. Processing to
When the identification information specified by the query exists in the table, if the data associated with the entry of the map associated with the identification information is cached, the data is read and associated with the entry A program for executing a process of caching the data read from the database in association with the entry if the read data is not cached.

Images