CN113377811A - Database query method and system - Google Patents
Database query method and system Download PDFInfo
- Publication number
- CN113377811A CN113377811A CN202010159338.7A CN202010159338A CN113377811A CN 113377811 A CN113377811 A CN 113377811A CN 202010159338 A CN202010159338 A CN 202010159338A CN 113377811 A CN113377811 A CN 113377811A
- Authority
- CN
- China
- Prior art keywords
- query
- point information
- database table
- change point
- data
- 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.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24552—Database cache management
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2365—Ensuring data consistency and integrity
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Computer Security & Cryptography (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the invention discloses a method and a system for a database. The method comprises the following steps: searching the query cache according to the query statement to obtain the identification information of each database table and the corresponding change point information contained in the query statement; searching a specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table; if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from the specific storage unit, acquiring query result data from the query cache; otherwise, acquiring the query result data from the database, acquiring the corresponding change point information from the specific storage unit, updating the query result data and the corresponding change point information into the query cache, and storing the identification information of the database table and the corresponding change point information into the specific storage unit when the data in the database table changes. The method ensures strong consistency of the cache data.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a database query method and a database query system.
Background
A common problem in the field of database technology is how to shorten the response time to database queries that require processing of large amounts of data. Such database queries are executed based on fewer input conditions, which generally results in outputting a large number of query results, or the database queries need to be performed by associating more database tables, which requires a longer time for the data association process. Therefore, the database query mechanism under big data is of interest to the industry.
In the prior art, one method often adopted by a database query mechanism under big data is as follows: when the database query is executed, the query result is updated to the query cache, and the subsequent query operation is actually realized aiming at the query cache. FIGS. 1a-1b show a comparison of the prior art where a query cache is not used and where a query cache is used. As shown in the figure, for the query statement SQL, in the example where the query cache is not used, the query statement SQL obtains data from the database 101 through operations such as statement analysis 102, optimization 103, and execution 104, while in the example where the query cache is used, the query statement SQL preferentially obtains data from the query cache 105 through operations such as statement analysis 102, optimization 103, and execution 104, and only when the query cache 105 does not contain data satisfying the condition, the data is obtained from the database 101, and the obtained data is updated to the query cache 105.
However, the method for implementing query using the query cache must ensure that the data stored in the query cache is the latest data, so as to ensure that the current state of the database is correctly reflected based on the query result provided by the query cache, and if the data in the database changes and the data in the query cache becomes outdated, the correct query result cannot be returned. Therefore, there is a need to provide a policy to keep the data in the query cache up to date.
Various query caching update strategies are known in the art. For example, in the query caching of MySQL, a policy is adopted that when data in a database is updated, the query caching is invalidated as a whole, but this method results in an increase in cost of update operations, and if there are many write operations, the caching effect is poor.
Disclosure of Invention
The invention aims to provide a method and a system for database query, which are used for overcoming the problems in the prior art.
To achieve the object, according to a first aspect of the present invention, an embodiment of the present invention provides a method for database query, the method including:
searching a query cache according to the received query statement to obtain the identification information of each database table and the corresponding change point information contained in the query statement;
retrieving the specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table;
if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from the specific storage unit, acquiring query result data from the query cache; otherwise, acquiring query result data from the database, acquiring corresponding change point information from the specific storage unit, and updating the query result data and the corresponding change point information to the query cache, wherein when the data in the database table changes, the identification information of the database table and the corresponding change point information are stored in the specific storage unit.
In some embodiments, a trigger is set for each database table, and when data in the database table changes, execution of the trigger is triggered, and the trigger is used for storing identification information of the database table and corresponding change point information into a specific storage unit.
In some embodiments, the step of storing the identification information of the database table and the corresponding change point information to the specific storage unit and the operation of changing the data in the database table are performed in the same transaction.
In some embodiments, the specific storage unit is a database table, and thus, the step of storing the identification information and the corresponding change point information of the database table into the database table and the operation of changing the data in the database table are executed in the same database transaction.
In some embodiments, the change point information is a self-incrementing value, which is self-incremented each time data in the database table changes.
In some embodiments, the change point information includes point in time information, and the change point information is updated according to the point in time of the operation each time data in the database table changes.
In some embodiments, the method further comprises:
setting an identifier for whether a caching mechanism is enabled;
and when the identification indicates that the cache mechanism is enabled, the query operation is realized by using the query step for the received query statement, and when the identification indicates that the cache mechanism is not enabled, the query result data is acquired from the database for any received query statement.
In some embodiments, a statement level flag is set to indicate whether caching mechanisms are enabled on a particular query statement.
In some embodiments, a global flag is set to indicate whether caching mechanisms are enabled on query statements within a global scope.
In a second aspect, an embodiment of the present invention provides a system for querying a database, including:
the updating device is used for storing the identification information of the database table and the corresponding change point information into a specific storage unit when the data in the database table changes;
the query device is used for retrieving the query cache according to the received query statement so as to obtain the identification information of each database table and the corresponding change point information contained in the query cache; searching a specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table; if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from a specific storage unit, acquiring query result data from the query cache; otherwise, acquiring query result data from the database, acquiring corresponding change point information from the specific storage unit, and updating the query result data and the corresponding change point information into the query cache.
In some embodiments, a trigger is set for each database table, and when data in the database table changes, execution of the trigger is triggered, and the trigger is used for storing identification information and latest change point information of the database table into a specific storage unit.
In some embodiments, the step of storing the identification information of the database table and the corresponding change point information to the specific storage unit and the operation of changing the data in the database table are performed in the same transaction.
In some embodiments, the change point information is a self-incrementing numerical value, the change point information being self-incremented each time a change occurs to the data in the database table.
In some embodiments, the change point information includes time point information, and the change point information is updated according to the operation time point each time the data in the database table changes.
In some embodiments, the system further comprises:
identification means for setting an identification indicating whether the caching mechanism is enabled;
when the mark indicates to start the cache mechanism, the query device uses the query cache to realize query on the received query statement, and when the mark indicates not to start the cache mechanism, the query device acquires query result data from a database on any received query statement.
In some embodiments, the flag is a statement level flag to indicate whether a caching mechanism is enabled on a particular query statement.
In some embodiments, the flag is a global flag to indicate whether a caching mechanism is enabled on query statements within a global scope.
In a third aspect, an embodiment of the present invention provides an electronic device, including a memory and a processor, where the memory further stores computer instructions executable by the processor, and the computer instructions, when executed, implement any one of the above methods.
In a fourth aspect, an embodiment of the present invention provides a computer-readable medium storing computer instructions executable by a terminal device, where the computer instructions, when executed, implement the method described in any one of the above.
According to the data query method provided by the embodiment of the invention, the consistency judgment in the database table is realized through the comparison of the change point information, and whether to read data from the query cache is determined according to the judgment result, so that the strong consistency of the cache data is ensured, and the data volume of the update operation of the change point information executed each time and the query operation executed each time is relatively limited, so that the data cache pressure in unit time is dispersed, and the overall performance of the system is relatively stable.
Drawings
The above and other objects, features and advantages of the present invention will become more apparent by describing embodiments of the present invention with reference to the following drawings, in which:
FIGS. 1a-1b show a comparison of the prior art where a query cache is not used and where a query cache is used.
FIG. 2 is an application scenario diagram of an exemplary query operation;
FIGS. 3a and 3b are flow diagrams of a method of database querying in accordance with one embodiment of the present invention;
FIG. 4 is a schematic diagram illustrating a method of querying the database of FIGS. 3a and 3 b;
FIG. 5 is a schematic diagram of a particular memory location and data stored within a query cache; (ii) a
FIG. 6 is a flow chart of a query operation in a method of database querying in accordance with another embodiment of the present invention;
FIG. 7 is a block diagram of a system for database querying in an embodiment of the present invention;
fig. 8 is a block diagram of an electronic device for implementing a method of database querying in an embodiment of the invention.
Detailed Description
The present invention will be described below based on examples, but the present invention is not limited to only these examples. In the following detailed description of the present invention, certain specific details are set forth. It will be apparent to one skilled in the art that the present invention may be practiced without these specific details. Well-known methods, procedures, and procedures have not been described in detail so as not to obscure the present invention. The figures are not necessarily drawn to scale.
FIG. 2 is an application scenario diagram of an exemplary query operation. System 20 may include a plurality of terminal devices 201 and a server 203 coupled via a network 202.
Each terminal device 201 may be constructed based on various models of processors currently on the market and includes various devices such as a memory and an input-output device. The terminal device 201 is installed with various operating systems such as WINDOWS and various application softwareTMAn operating system, a UN IX operating system, a Linux operating system, an RTOS operating system, application software such as a browser, video playback software, communication software, various research and development tools, and the like.
Each terminal device 201 may communicate with the network 202 in various ways. Network 202 may be based on a combination of one or more of a variety of communication technologies implemented by exchanging signals, including but not limited to wired technologies employing electrically and/or optically conductive cables, and wireless technologies employing infrared, radio frequency, and/or other forms. In different application scenarios, the network 202 may be the internet, a wide area network, or a local area network, such as a private network of a company. The network 202 may also be a wired network or a wireless network.
It should be understood that the above application scenario is only an application scenario of a query operation in a normal case, but is not limited thereto, for example, the initiating query operation and the executing query operation may be from the same application of the same server. In other words, the embodiment of the present invention may be applied to the scenario shown in fig. 1, and may also be applied to other scenarios.
In the following, before describing embodiments of the present invention in detail based on the drawings, some aspects of the embodiments of the present invention will be described first.
The term "query" is used as a generic term that includes information retrieval such as transactional queries, statement levels, and the like. The "query cache" is a cache unit provided for storing query result data. The query cache has a response speed superior to that of the database due to the reasons of small data volume, high read-write speed and the like, and can be located on various types of caches (caches), various types of memories (memories) and even hard disks (harddisks), which is not limited by the invention.
"change point information" is a custom term used to indicate changes to data in a database, and when data in a database table changes, the change point information also changes, for example, when a piece of data is deleted from the database, the current change point information is different from the change point information before the previous undeleted data. The change point information may be established on a per database table basis or on an entire database basis. The "specific storage unit" is a cache unit that stores change point information for each database table. The particular storage unit and the query cache are data storage units that may be from the same or different hardware and/or software systems, and the invention is not limited in this respect. In the invention, the query cache also stores change point information, and the change point information corresponds to a database table when the query result data is acquired.
Fig. 3a and 3b are flow diagrams of a method of database querying, in accordance with one embodiment of the present invention. The method comprises updating operation and query operation of the change point information, wherein the updating operation of the change point information is used for updating the change point information in the query cache according to the change condition of the database, and the query step is used for receiving query statements and acquiring query result data from the database table or the query cache according to the query statements.
The updating operation is step S302, that is, the identification information and the change point information of the database table are updated into a specific storage unit, and since the updating operation is caused by a change of data in the database table, step S301, that is, the data in the database table is changed by at least one of an addition (insert), a deletion (delete), and an update (update), is also shown in the figure. Of course, changes to the structure of a database table can also result in changes to the data within the database table. However, the change of the structure of the database table affects the normal execution of the business process, so the structure of the database table is not modified in the system running state.
The identification information of the database table is the unique identification of the database table, and can be represented by the name of the database and the name of the database table, or can be represented by only the name of the database table. The change point information can be represented in various ways. For example, the change point information may be represented as a self-increment value, which is self-incremented each time an add, update, or delete operation is performed on data in a database table. For another example, the change point information may also be represented by an operation time point of addition, update or deletion, and if a piece of data is deleted from the database tables operates at 42 minutes and 13 seconds at 18 of 11, 25 and 2019, the change point information of operates is modified as follows: 20191125184213.
the update operation is before or after each execution of an add, update or delete operation. And updating the change point information into the specific storage unit so as to ensure that the latest change point information stored in the specific storage unit in real time is the latest change point information of each database table.
The inquiry operation includes steps S311 to S315.
In step S311, the query cache is retrieved according to the received query statement to obtain the identification information of each database table and the corresponding change point information contained therein.
When retrieving a query cache from a received query statement, there are two possibilities: the first possibility is that corresponding data is stored in the query cache, and the data can be directly read out, and identification information of each database table and corresponding change point information contained in the query statement are obtained from the data; the second possibility is that the query buffer does not store the corresponding data, and then the process goes to step S316. The second may not be a scenario of interest for the present invention and is therefore not shown in the figure.
In step S312, the specific storage unit is retrieved according to the identification information of each database table to obtain the change point information corresponding to each database table.
Also, there are two possibilities to retrieve a particular storage location based on the identification information of each database table: if the first possibility is that corresponding data is retrieved from a specific storage unit, the data can be read out, so that change point information corresponding to each database table is obtained; the second possibility is that the corresponding data is not retrieved in the specific storage unit, and when the data is read in the query cache, only an error occurs in the system, an error report message can be given, and the execution of the query step is terminated. The second scenario may not be a concern of the present invention and, therefore, is not described in detail in this step.
In step S313, the change point information of each database table obtained from the query cache is compared with the corresponding change point information obtained from the specific storage unit one by one.
In step S314, whether all match. And if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from the specific storage unit, executing the step S315, otherwise executing the step S316.
In step S315, query result data is obtained from the query cache.
In step S316, query result data is obtained from the database table, corresponding change point information is obtained from the specific storage unit, and the query result data and the corresponding change point information are updated to the query cache.
In steps S313 to S316, the change point information of each database table obtained from the query cache is compared with the change point information correspondingly obtained from the specific storage unit, and if the change point information of each database table is consistent, it indicates that the query result data retrieved according to the received query statement in the query cache is the latest data, but if the change point information of one database table is inconsistent, it indicates that the query result data retrieved according to the received query statement in the query cache is not the latest data, so the query result data must be retrieved from the database, the corresponding change point information is obtained from the specific storage unit, and the query result data and the corresponding change point information are updated into the query cache.
The above embodiments are further described below in conjunction with fig. 4 and 5. Referring to FIG. 4, an update operation updates change point information in a particular storage location 10 for each change of data in a database table 401. Initially, the query statement SQL is subjected to operations such as statement analysis 402, optimization 403 and execution 404 to obtain data from the database 101, and store the data and change point information obtained from the specific storage unit 10 in the query cache 30, and then each time the query statement SQL is executed, change point information of the database table 401 is obtained from the specific storage unit 10 and then compared with the change point information obtained from the query cache 30, if the change point information is the same, the data is obtained from the query cache, and if the change point information is different, the data is obtained from the database 401, and the change point information in the specific storage unit 10 and the data obtained from the database 401 are updated in the query cache 30.
FIG. 5 is a schematic diagram of a particular memory location and data stored by a query cache. Referring to fig. 5, the specific storage unit 10 is configured to store correspondence data of identification information of a database table and table update point information, and the query cache 30 is configured to store correspondence data of a query statement, change point information, and query result data. The particular storage unit 10 may be a storage unit in various forms such as a file, memory, database table, and the like. The query cache 30 is a cache unit. For example. When data in the database table with identification information 11 changes, the change may be caused by operations such as adding, deleting, updating, changing the structure of the database table, and the like, and the change point information 21 is modified to the change point information 50 for the change. Then, a query statement 31 is received, and since the query statement 31 relates to a query to the database table with identification information of 11, therefore, the change point information 50 is retrieved from the specific storage unit 10, and compared with the change point information 41 retrieved from the query cache 30 (the change point information 41 includes change point information corresponding to each database table related to the query statement 31), if the change point information corresponding to the identification information 11 stored in the change point information 41 is 21, i.e., the change point information between the two is not consistent, it means that after the last query operation of the query statement 31, the data in the database table corresponding to the identification information 11 has changed, and at this time, the query operation will retrieve new query result data from the database table, and updates the new query result data and change point information 50 into the cache of the query statement 31.
In summary, the embodiment described above utilizes change point information for comparison, and implements consistency determination in the database table, thereby ensuring strong consistency of the cache data, and utilizes the update step to maintain lightweight change point information, and utilizes the query step to maintain query result data according to the query statement.
It should be noted that the update operation and the query operation may or may not occur simultaneously. When the update operation and the query operation do not occur simultaneously, the update operation does not affect the query operation. However, if the update operation and the inquiry operation occur simultaneously, there may occur a case where the inquiry operation has read out the change point information from the specific storage unit while the update operation is in the update state, which results in the inquiry operation not recognizing that the current inquiry result data has failed. However, this has little effect on the system and may not require processing. There is also a case where an operation of adding, deleting, or modifying data in the database table and an operation of storing identification information and corresponding change point information in a specific storage unit are performed in two different transactions, so that when one of the transactions fails, the other transaction succeeds, and thus the change point information stored in the specific storage unit does not reflect the latest state of the database table. One possible solution to this is to place the operations that cause changes to the data in the database tables and the operations that store the identification information and corresponding change point information to a particular storage location in the same transaction. Multiple operations contained by a transaction either succeed or fail together. Therefore, the change point information stored in the specific storage unit can reflect the latest state of the corresponding database table through the transaction.
When the specific storage unit is a database table, the solution is as follows: the operation of causing the data change of the database table and the operation of storing the identification information and the corresponding change point information into a specific storage unit are carried out in the same database transaction. Specifically, assuming that the database table is orders, the database table storing change point information is versions, and the versions store a record of the change point information related to the database table orders, deleting a data from the database table orders and modifying the record corresponding to the orders in the versions are executed in the same database transaction, when the deletion operation on the orders fails, the record corresponding to the orders in the versions cannot be modified, and when the deletion operation on the orders succeeds, the record corresponding to the orders in the versions cannot be modified.
When the specific storage unit is a memory unit, the existing solutions of various coding languages and overall architectures also provide corresponding methods to perform operations such as adding, deleting or modifying data in a database table and operations of storing identification information and corresponding change point information into the specific storage unit in the same transaction. For example, both local transactions in Java (JDBC transactions) and distributed transactions (Java transactions APIs) can ensure execution of the transactions. And will not be described in detail herein.
In some embodiments, the update operation is triggered by a trigger. For example, a trigger is set for each database table, and when the database table is updated, deleted, added, or the like, the execution of the trigger is triggered, and the trigger stores the identification information and the latest change point information of the database table in a specific storage unit. Previous database table orders and versions are still exemplified. Assuming that triorders are set triggers, when data is added to a database table orders, triorders are triggered, modifying the corresponding records in the versions. Similarly, when the deleting and updating operations occur, the trigger is also triggered, and the corresponding change point information is also updated.
Fig. 6 is a flowchart of a query operation in a method of database query according to another embodiment of the present invention. The update operation of this embodiment is the same as the update operation shown in fig. 3 a.
The inquiry operation of the present embodiment includes the following steps.
In step S611, an identification of whether the caching mechanism is enabled is set.
In step S612, a query statement is received.
In step s613, it is determined whether the caching mechanism is enabled for the query statement. If the caching mechanism is already enabled for the query statement, step S614 is performed, otherwise step S619 is performed.
In step S614, the query cache is retrieved according to the received query statement to obtain the identification information of each database table and the corresponding change point information contained therein.
In step S615, the specific storage unit is retrieved according to the identification information of each database table to obtain the change point information corresponding to each database table.
In step S616, the change point information of each database table obtained from the query cache is compared with the corresponding change point information obtained from the specific storage unit one by one.
In step S617, whether all match. If so, step S618 is performed, otherwise, step S619 is performed.
In step S618, query result data is obtained from the query cache
In step S619, query result data is obtained from the database, and corresponding change point information is obtained from the specific storage unit, and the query result data and the corresponding change point information are updated into the query cache.
Steps S614-S619 are the same as the corresponding steps in fig. 3b, and are not described here again.
In this embodiment, an identifier for whether a cache mechanism is enabled is set for the query statement, and each time a query statement is received, a corresponding identifier is obtained through retrieval of the query statement and is determined, if the identifier indicates that the cache mechanism is enabled, the query statement is processed by using a query cache, and if the identifier indicates that the cache mechanism is not enabled, query result data corresponding to the query statement is directly obtained from the database table. Similarly, when data in the database table is updated, deleted or modified, the identifier corresponding to the database table may be obtained by first retrieving, if the identifier indicates that the caching mechanism is not enabled, the change point information does not need to be modified, and if the identifier indicates that the caching mechanism is enabled, the change point information needs to be modified. And the mark is set aiming at the statement level, so that whether a cache mechanism is used or not can be flexibly adjusted according to the condition of each query statement. For example, some query statements with frequently changed data need to frequently update the data in the database to the query cache, and the use of the cache mechanism cannot improve the query efficiency, so the use of the cache mechanism may be stopped.
Preferably, the identifier corresponding to the query statement indicating whether the caching mechanism is enabled or not may be stored in the query cache 30, that is, each query statement corresponds to one identifier, so that whether the caching mechanism is enabled or not may be determined according to the identifiers. However, it is preferable that change point information of each database table stored in a specific storage unit is recorded regardless of whether the caching mechanism is enabled, because if change point information of each database table is not recorded, strong consistency of query caching cannot be achieved even if an identifier indicating that the caching mechanism is enabled is set for a certain query statement.
In the above embodiment, the identification of whether the caching mechanism is enabled is set for the statement level query statement. In another embodiment, an indication of whether the global caching mechanism is enabled may also be set. The global flag may be set as a global variable of the application. And after the application is started, acquiring the global variable and judging, wherein if the identifier indicates that the cache mechanism is started, all the query statements use the cache mechanism, and otherwise, all the query statements do not use the cache mechanism. Of course, both may be used in combination.
Fig. 7 is a block diagram of a system for implementing queries based on database caches according to an embodiment of the present invention. This system corresponds to the previous method and will therefore be performed in a relatively short way in the following description.
The system 70 comprises an updating means 701 and a querying means 702. The updating device 701 is configured to store the identification information of the database table and the corresponding change point information into a specific storage unit when data in the database table changes.
Specifically, the operation of causing changes to occur to data within a database table includes: adding, deleting, modifying data or changing database structures. When one of these operations occurs, the identification information of the database table and the corresponding change point information are stored into a particular storage location.
The query device 702 is configured to retrieve the query cache according to the received query statement to obtain the identification information of each database table and the corresponding change point information included in the query cache; searching a specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table; if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from the specific storage unit, acquiring query result data from the query cache; otherwise, acquiring the query result data from the database, acquiring the corresponding change point information from the specific storage unit, and updating the query result data and the corresponding change point information into the query cache.
In summary, the system provided in this embodiment utilizes the change point information for comparison, realizes the consistency determination in the database table, and determines whether to read data from the query cache according to the determination result, thereby ensuring the strong consistency of the cache data. Furthermore, the updating device is used for maintaining the lightweight change point information, and the query device is used for maintaining the query result data according to the query statement, so that the data required to be maintained by the updating device and the query device each time is relatively limited, the data cache pressure in unit time is dispersed, and the overall performance of the system is relatively stable.
In some embodiments, a trigger is set for each database table, and execution of the trigger is triggered when data in the database table changes, the trigger being used to store identification information and latest change point information of the database table into a particular storage location.
In some embodiments, the step of storing the identification information of the database table and the corresponding change point information to a particular storage location and the operation of causing the data within the database table to change are performed in the same transaction. If the particular unit of storage is also a database table, the above operations may be performed in a database transaction.
In some embodiments, the change point information is a self-incrementing value, which is self-incremented each time a change is made to the data in the database table.
In some embodiments, the change point information includes time point information, and the time point of the operation is updated into the change point information each time the data in the database table changes.
In some embodiments, the system further comprises: the identification device is used for setting the identification whether the caching mechanism is started or not; when the mark indicates that the cache mechanism is started, the query device uses the query cache to realize query on the received query statement, and when the mark indicates that the cache mechanism is not started, the query device acquires query result data from the database on any received query statement. Optionally, the flag is a statement level flag to indicate whether a caching mechanism is enabled on a particular query statement. Optionally, the flag is a global flag to indicate whether all query statements in the global scope enable the caching mechanism.
In accordance with the above embodiments, as shown in fig. 8, the present invention further provides an electronic device 80, which includes a memory 802 and a processor 801, and in addition, an input/output device 803 and other hardware 804 in some cases. The Memory 802 is, for example, a Random-Access Memory (RAM), and may also be a non-volatile Memory (non-volatile Memory), such as at least 1 disk Memory. The input/output device 803 is, for example, a display, a keyboard, a mouse, a network controller, or the like. The processor 801 may be constructed based on various models of processors currently on the market. The processor 801, the memory 802, the input/output device 803, and the other hardware 804 are connected to each other via a bus, which may be an ISA (Industry Standard Architecture) bus, a PCI (peripheral component Interconnect) bus, an EISA (Extended Industry Standard Architecture) bus, or the like. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one line is shown in FIG. 8, but it is not intended that there be only one bus or one type of bus.
The memory 802 is used for storing programs. In particular, the program may comprise program code comprising computer instructions. The memory may include both memory and non-volatile storage and provides computer instructions and data to the processor 801. The processor 801 reads a corresponding computer program from the memory 802 into the memory and then runs the computer program, and forms the above interaction method applied to the integrated development environment on a logic level, and is specifically configured to perform the following operations: searching a query cache according to the received query statement to obtain the identification information of each database table and the corresponding change point information contained in the query statement; retrieving the specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table; if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from the specific storage unit, acquiring query result data from the query cache; otherwise, acquiring query result data from the database, acquiring corresponding change point information from the specific storage unit, and updating the query result data and the corresponding change point information to the query cache, wherein when the data in the database table changes, the identification information of the database table and the corresponding change point information are stored in the specific storage unit.
As will be appreciated by one skilled in the art, the present invention may be embodied as systems, methods and computer program products. Accordingly, the present invention may be embodied in the form of entirely hardware, entirely software (including firmware, resident software, micro-code), or in a combination of software and hardware. Furthermore, in some embodiments, the invention may also be embodied as a computer program product in one or more computer-readable media having computer-readable program code embodied in the medium.
Any combination of one or more computer-readable media may be employed. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium is, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer-readable storage medium include: an electrical connection for the particular wire or wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical memory, a magnetic memory, or any suitable combination of the foregoing. In this context, a computer-readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with a processing unit, apparatus, or device.
A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a chopper. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any other suitable combination. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction system, apparatus, or device.
Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., and any suitable combination of the foregoing.
Computer program code for carrying out embodiments of the present invention may be written in one or more programming languages or combinations. The programming language includes an object-oriented programming language such as JAVA, C + +, and may also include a conventional procedural programming language such as C. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet service provider).
The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention, and various modifications and changes may be made by those skilled in the art. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (19)
1. A method of database querying, the method comprising:
searching a query cache according to the received query statement to obtain the identification information of each database table and the corresponding change point information contained in the query statement;
retrieving the specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table;
if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from the specific storage unit, acquiring query result data from the query cache; otherwise, acquiring query result data from the database, acquiring corresponding change point information from the specific storage unit, and updating the query result data and the corresponding change point information to the query cache, wherein when the data in the database table changes, the identification information of the database table and the corresponding change point information are stored in the specific storage unit.
2. The method of claim 1, wherein a trigger is set for each database table, and when data in the database table changes, execution of the trigger is triggered, and the trigger is used for storing identification information of the database table and corresponding change point information into a specific storage unit.
3. The method of claim 1, wherein the storing of the identification information of the database table and the corresponding change point information to the particular storage location and the changing of the data in the database table are performed in the same transaction.
4. The method of claim 3, wherein the particular storage location is a database table, such that the step of storing the identification information and corresponding change point information of the database table in the database table and the operation of causing the change in the data in the database table are performed in the same database transaction.
5. The method of claim 1, wherein the change point information is a self-incrementing numerical value, the change point information being self-incremented each time data in the database table changes.
6. The method of claim 1, wherein the change point information includes time point information that is updated according to an operating time point each time data within a database table changes.
7. The method of claim 1, further comprising:
setting an identifier for whether a caching mechanism is enabled;
and when the identification indicates that the cache mechanism is enabled, the query operation is realized by using the query step for the received query statement, and when the identification indicates that the cache mechanism is not enabled, the query result data is acquired from the database for any received query statement.
8. The method of claim 7, wherein the statement level flag is set to indicate whether a caching mechanism is enabled on a particular query statement.
9. The method of claim 7, wherein a global flag is set to indicate whether a caching mechanism is enabled on query statements within a global scope.
10. A system for database querying, comprising:
the updating device is used for storing the identification information of the database table and the corresponding change point information into a specific storage unit when the data in the database table changes;
the query device is used for retrieving the query cache according to the received query statement so as to obtain the identification information of each database table and the corresponding change point information contained in the query cache; searching a specific storage unit according to the identification information of each database table to obtain change point information corresponding to each database table; if the change point information of each database table acquired from the query cache is consistent with the corresponding change point information acquired from a specific storage unit, acquiring query result data from the query cache; otherwise, acquiring query result data from the database, acquiring corresponding change point information from the specific storage unit, and updating the query result data and the corresponding change point information into the query cache.
11. The system of claim 10, wherein a trigger is provided for each database table, the execution of the trigger being triggered when data in a database table changes, the trigger for storing identification information of the database table and latest change point information in a particular storage location.
12. The system of claim 10, wherein the storing of the identification information of the database table and the corresponding change point information to the particular storage location and the changing of the data in the database table are performed in the same transaction.
13. The system of claim 10, wherein the change point information is a self-incrementing numerical value, the change point information being self-incremented each time a change occurs to data in the database table.
14. The system of claim 10, wherein the change point information is time point information, and the change point information is updated according to the operation time point each time data in the database table changes.
15. The system of claim 10, further comprising:
identification means for setting an identification indicating whether the caching mechanism is enabled;
when the mark indicates to start the cache mechanism, the query device uses the query cache to realize query on the received query statement, and when the mark indicates not to start the cache mechanism, the query device acquires query result data from a database on any received query statement.
16. The system of claim 15, wherein the flag is a statement level flag to indicate whether a caching mechanism is enabled on a particular query statement.
17. The system of claim 15, wherein the flag is a global flag to indicate whether a caching mechanism is enabled on query statements within a global scope.
18. An electronic device comprising a memory and a processor, the memory further storing computer instructions executable by the processor, the computer instructions, when executed, implementing the method of any of claims 1 to 9.
19. A computer readable medium storing computer instructions executable by an electronic device, the computer instructions, when executed, implementing the method of any of claims 1 to 9.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010159338.7A CN113377811A (en) | 2020-03-09 | 2020-03-09 | Database query method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010159338.7A CN113377811A (en) | 2020-03-09 | 2020-03-09 | Database query method and system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113377811A true CN113377811A (en) | 2021-09-10 |
Family
ID=77569463
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010159338.7A Pending CN113377811A (en) | 2020-03-09 | 2020-03-09 | Database query method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113377811A (en) |
-
2020
- 2020-03-09 CN CN202010159338.7A patent/CN113377811A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9817581B2 (en) | Maintaining versions of data in solid state memory | |
| CN108459826B (en) | Method and device for processing IO (input/output) request | |
| WO2019140828A1 (en) | Electronic apparatus, method for querying logs in distributed system, and storage medium | |
| CN110046133B (en) | Metadata management method, device and system for storage file system | |
| CN109471851B (en) | Data processing method, device, server and storage medium | |
| US8832022B2 (en) | Transaction processing device, transaction processing method and transaction processing program | |
| US20120203797A1 (en) | Enhanced control to users to populate a cache in a database system | |
| US9710283B2 (en) | System and method for pre-storing small data files into a page-cache and performing reading and writing to the page cache during booting | |
| US20190129982A1 (en) | Just-in-time multi-indexed tables in a shared log | |
| US20120221538A1 (en) | Optimistic, version number based concurrency control for index structures with atomic, non-versioned pointer updates | |
| US20140258675A1 (en) | Memory controller and memory system | |
| CN111090663A (en) | Transaction concurrency control method, device, terminal equipment and medium | |
| CN109299352B (en) | Method and device for updating website data in search engine and search engine | |
| CN112416710A (en) | User operation recording method and device, electronic equipment and storage medium | |
| CN111190899B (en) | Buried data processing method, buried data processing device, server and storage medium | |
| CN114416798A (en) | Cache management method and device based on data dependency relationship and consistency guarantee | |
| US20120054154A1 (en) | User data management system and method | |
| US8676867B2 (en) | File system location verification using a sentinel | |
| CN113377811A (en) | Database query method and system | |
| CN114116720A (en) | Online database management method, device, equipment and storage medium | |
| CN114443722A (en) | Cache management method and device, storage medium and electronic equipment | |
| CN110909029A (en) | Method and medium for realizing cache based on Nosql | |
| CN113064943A (en) | Data acquisition method and device, electronic equipment and storage medium | |
| CN110413644B (en) | Data caching method, electronic device and computer readable storage medium | |
| CN115794842B (en) | Data processing method, device, electronic equipment and medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| REG | Reference to a national code |
Ref country code: HK Ref legal event code: DE Ref document number: 40058129 Country of ref document: HK |