Query result set caching method for database

Abstract

The invention provides a query result set caching method for a database, which can be applied to the technical field of databases and data processing. The method comprises the following steps that: a data node caches a query result set, and directly returns a corresponding cache result set after judging a transaction state when receiving the same query command; the data node records the transaction state of the relation table through the state table, and updates the result set cache by judging the change of the transaction state; and when the data nodes relate to data operation in a modification table, modification table structure making and transaction operation, the number of result set caches needs to be controlled and the result set caches need to be reset through parameters corresponding to corresponding object records in a modification state table, and the result set caches are cleaned regularly. The invention further provides a query result set caching device for the database, electronic equipment and a storage medium.

Description

Technical Field

[0001] The present invention relates to the technical field of database and data processing, and more specifically to a method and device for caching a query result set of a database, an electronic device and a storage medium. Background Art

[0002] Query is one of the most commonly used and important transactions in a database. In a large-scale distributed database system, how to improve query efficiency and improve user query experience has always been a hot topic in this field. When a distributed database system executes a query SQL (Structured Query Language) statement (or command) sent by a user, the CN (Coordinator Node) node selects different query methods based on the number of tables involved in the query and the query complexity. Currently, query methods can be divided into three categories: directly push down the query SQL statement sent by the user to the DN (Data Node) for execution; the CN node parses and optimizes the user's SQL statement and converts it into a query plan, then serializes the query plan and sends the serialized string to the DN, which executes the corresponding execution plan after deserialization; the CN node only sends the query table statement to the DN node, and other operations (aggregation, connection, window function, etc.) are executed by the CN after receiving the data sent from the DN. However, when the above three query methods involve massive data or the query operation is very complex, the DN will consume a lot of time to execute the query SQL statement, which greatly reduces the query efficiency and affects the user experience. Therefore, it is necessary to optimize the existing query technology solutions to improve query efficiency and reduce time consumption. Summary of the invention

[0003] In view of the above problems, the present invention provides a method and device for caching a query result set of a database, an electronic device, and a storage medium.

[0004] According to a first aspect of the present invention, a method for caching a query result set for a database is provided, the method comprising:

[0005] Construct cache tables and status tables on data nodes, and use data nodes to execute the query plan initiated by the coordination node to obtain the query result set;

[0006] Use the cache table to cache the query result set and query plan, and use the status table to record the transaction status information of the cache table;

[0007] The status table is used to record the transaction status information generated by the data node when performing table modification operations and table structure modification operations;

[0008] The number of cached query plans is controlled by setting the parameters of the data node, and the cache table and status table are reset according to the user-predefined syntax.

[0009] According to an embodiment of the present invention, constructing a cache table and a status table at a data node includes:

[0010] During database initialization, a node-level hash cache table and a node-level hash status table are constructed on each data node;

[0011] The key value of the hash cache table includes the hash value of the structured query statement or the serialized query plan, and the value of the hash cache table includes the query result set and the transaction status information of the associated table of the query result set;

[0012] The key value of the hash state table includes an identifier of the associated table of the query result set, and the value of the hash state table includes transaction status information of the associated table of the query result set.

[0013] According to an embodiment of the present invention, the query result set obtained by executing the query plan initiated by the coordination node using the data node includes:

[0014] Using the data node to receive the target query plan initiated by the coordination node, and judging the query plan cached in the cache table based on the target query plan;

[0015] If the result of the judgment is that the target query plan is not cached in the cache table, the target query plan is executed using the data node, and the cache table and the status table are updated;

[0016] If the result of the determination is that the target query plan has been cached in the cache table, a query operation is performed on the cache table and a comparison operation is performed on the status table, and based on the comparison result, the cache table and the status table are updated.

[0017] According to an embodiment of the present invention, when the judgment result is that the target query plan is not cached in the cache table, using the data node to execute the target query plan and updating the cache table and the status table includes:

[0018] Use the data node to execute the target query plan, obtain the target query result set, and insert the target query plan and the target query result set into the cache table;

[0019] Perform an identifier query on the state table, and if the identifier query result is that the identifier of the associated table of the target query result set exists in the state table, use the cache table to record the transaction state information of the associated table of the target query result set;

[0020] An identifier query is performed on the state table. If the identifier query result is that the identifier of the associated table of the target query result set does not exist in the state table, the identifier and transaction status information of the associated table of the target query result set are inserted into the state table, and the transaction status information of the associated table of the target query result set is recorded in the cache table.

[0021] According to an embodiment of the present invention, when the judgment result is that the target query plan has been cached in the cache table, performing a query operation on the cache table and performing a comparison operation on the state table, and updating the cache table and the state table based on the comparison result includes:

[0022] Query the cache table to obtain a target query result set corresponding to the target query plan, and obtain transaction status information of the associated table of the target query result set;

[0023] Compare the transaction status information in the cache table with the transaction status information in the status table to obtain a comparison result;

[0024] When the comparison result shows that the transaction status information in the cache table is the same as the transaction status information in the status table, the target query result set is sent to the coordination node using the data node.

[0025] According to an embodiment of the present invention, when the judgment result is that the target query plan has been cached in the cache table, performing a query operation on the cache table and performing a comparison operation on the state table, and updating the cache table and the state table based on the comparison result further includes:

[0026] When the comparison result shows that the transaction status information in the cache table is different from the transaction status information in the status table, the target query plan is executed using the data node, the obtained target query result set is sent to the coordination node, and the cache table and the status table are updated.

[0027] According to an embodiment of the present invention, the transaction status information generated by using the status table to record the table modification operation and the table structure modification operation performed by the data node includes:

[0028] When the data node performs a table modification operation on the target source table associated with the query result set, the transaction status information of the target source table is queried from the status table to obtain the query result, wherein the table modification operation includes copy, insert, delete, update, update insert, merge and truncate;

[0029] When the query result shows that the transaction status information of the target source table exists in the status table, the transaction status information of the target source table is updated using the status table.

[0030] According to an embodiment of the present invention, the transaction status information generated by using the status table to record the table modification operation and the table structure modification operation performed by the data node also includes:

[0031] When the data node performs a table structure modification operation on the target source table associated with the query result set, the transaction status information of the target source table is recorded in the modification status table;

[0032] When the table structure modification operation is a rollback operation or an abort operation, the transaction status information of the target source table is deleted using the status table;

[0033] When the table structure modification operation is a flashback operation, the status table is reset and the transaction status information of all target source tables in the status table is deleted.

[0034] According to an embodiment of the present invention, the above-mentioned controlling the number of cached query plans by setting parameters of data nodes and resetting the cache table and the state table according to a user predefined syntax includes:

[0035] The number of query plans cached in the cache table is controlled by setting the parameters of the data node, and query plans with a hit rate lower than the preset threshold are regularly cleared;

[0036] By setting the cache function parameters, the data node can be controlled to open or close the query result set cache function;

[0037] Reset the cache table and status table according to the user-predefined syntax, and use the data node to set the cache limit of the query result set.

[0038] According to an embodiment of the present invention, the cache limit of the query result set set by using the data node includes:

[0039] When the query plan involves database system tables, database system views, query result sets associated with temporary tables, and query result sets associated with database built-in functions, the data node turns off the query result set cache function;

[0040] In the case where the query plan is a query plan for the exchange of intermediate query result sets between data nodes, the data node turns off the query result set caching function.

[0041] According to a second aspect of the present invention, there is provided a query result set caching device for a database, the device comprising:

[0042] The table construction and execution module is used to construct the cache table and the status table on the data node, and use the data node to execute the query plan initiated by the coordination node to obtain the query result set;

[0043] A table cache and recording module is used to cache query result sets and query plans using a cache table, and to record transaction status information of the cache table using a status table;

[0044] A modification operation recording module is used to use a status table to record transaction status information generated by a data node performing table modification operations and table structure modification operations;

[0045] The parameter setting and resetting module is used to control the number of cached query plans by setting the parameters of the data nodes, and reset the cache table and the status table according to the user predefined syntax.

[0046] A third aspect of the present invention provides an electronic device, comprising: one or more processors; a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the above method.

[0047] The fourth aspect of the present invention further provides a computer-readable storage medium on which a computer program or instruction is stored, and the steps of the above method are implemented when the above computer program or instruction is executed by a processor.

[0048] The query result set caching method for a database provided by the present invention can not only greatly improve the efficiency of database queries, but also ensure the consistency of query results by setting a cache table and a state table on a data node for caching query result sets. At the same time, in the case of modifications involving massive query plans, the query result set caching function on the data node can be turned on or off by setting the data node, thereby ensuring the execution performance of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] The above contents and other objects, features and advantages of the present invention will become more apparent through the following description of the embodiments of the present invention with reference to the accompanying drawings, in which:

[0050] Figure 1 This is an application scenario diagram of a query result set caching method for a database according to an embodiment of the present invention;

[0051] Figure 2 is a flow chart of a method for caching a query result set for a database according to an embodiment of the present invention;

[0052] Figure 3 is a structural block diagram of a query result set caching device for a database according to an embodiment of the present invention;

[0053] Figure 4 It is a block diagram of an electronic device suitable for implementing a query result set caching method for a database according to an embodiment of the present invention. DETAILED DESCRIPTION

[0054] Below, embodiments of the present invention will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present invention. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of embodiments of the present invention. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of concepts of the present invention.

[0055] The terms used herein are only for describing specific embodiments and are not intended to limit the present invention. The terms "comprise", "include", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0056] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0057] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0058] Commonly used distributed databases (such as Gbase8c database) adopt the shared nothing distributed database architecture, storing data in different DN nodes (distributed tables) according to hash value shards or storing full data (replicated tables) on each DN node. According to the instance roles in the system, they can be divided into three types of instances: Coordinator, GTM and DataNode. GTM is responsible for providing global transaction numbers and snapshot information. When executing SQL commands sent by users on the Server side, the CN node is responsible for parsing the SQL statements sent by users and performing processing and parsing. The actual query, deletion, update and insertion operations involving the table are executed on the DN node. The DN node stores the actual data, receives the execution plan or SQL statement sent by CN, executes and returns the result.

[0059] However, when it comes to massive data queries or query operations with high complexity, the query efficiency of existing distributed databases will be greatly reduced.

[0060] In response to the above technical problems, the present invention provides a query result set caching method for a database, which caches the query result set in the DN. After the DN receives the query request for the first time and executes the corresponding query, before sending the query result set to the CN, the result set and the corresponding query statement or plan fragment are cached. In this way, after the DN node receives the same statement or query fragment, if all tables involved in the query have not been modified, the cached result set is directly sent to the CN node. In this way, the DN node does not need to execute the query command and directly returns the result, which can greatly improve the query efficiency of the database.

[0061] Figure 1 It is an application scenario diagram of a query result set caching method for a database according to an embodiment of the present invention.

[0062] like Figure 1 As shown, the application scenario 100 according to this embodiment may include the field of database and data processing technology. The network 104 is used to provide a medium for a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0063] The user can use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 through the network 104 to receive or send messages, etc. Various communication client applications can be installed on the first terminal device 101, the second terminal device 102, and the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only for example).

[0064] The first terminal device 101, the second terminal device 102, and the third terminal device 103 may be various electronic devices having display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0065] The server 105 may be a server that provides various services, such as a background management server (only as an example) that provides support for websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103. The background management server may analyze and process the received data such as user requests, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal device.

[0066] It should be noted that the query result set caching method for a database provided in the embodiment of the present invention can generally be executed by the server 105. Accordingly, the query result set caching device for a database provided in the embodiment of the present invention can generally be set in the server 105. The query result set caching method for a database provided in the embodiment of the present invention can also be executed by a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105. Accordingly, the query result set caching device for a database provided in the embodiment of the present invention can also be set in a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105.

[0067] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0068] At the same time, it should be noted that in the embodiments of the present invention, certain software, components, models and other existing solutions in the industry may be mentioned, which should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present invention, but it does not mean that the applicant has or will necessarily use the solution.

[0069] Figure 2 The present invention is a flowchart of a method for caching a query result set for a database according to an embodiment of the present invention.

[0070] like Figure 2 As shown, the above-mentioned query result set caching method for a database includes operations S210 to S240.

[0071] In operation S210, a cache table and a state table are constructed at the data node, and the query plan initiated by the coordination node is executed using the data node to obtain a query result set.

[0072] During the initialization of the distributed database, two tables are constructed on the data node to perform different functions respectively, thereby realizing the result set cache function of the data node.

[0073] The above query plan includes an SQL query statement (or command) and a serialized query plan, and both the above SQL query statement and the serialized query plan are string structures.

[0074] In operation S220, the query result set and the query plan are cached using the cache table, and the transaction status information of the cache table is recorded using the status table.

[0075] In operation S230, a state table is used to record transaction state information generated by a data node performing a table modification operation and a table structure modification operation.

[0076] In operation S240 , the number of cached query plans is controlled by setting parameters of the data node, and the cache table and the state table are reset according to a user predefined syntax.

[0077] The above operation S240 is used to solve the problem that if there are many table modification or table structure modification commands involved in the query process, the query result set caching function on the data node can be turned off by setting relevant parameters of the data node or user predefined syntax, so as not to reduce the execution performance of the distributed database.

[0078] The query result set caching method for a database provided by the present invention can not only greatly improve the efficiency of database queries, but also ensure the consistency of query results by setting a cache table and a state table on a data node for caching query result sets. At the same time, in the case of modifications involving massive query plans, the query result set caching function on the data node can be turned on or off by setting the data node, thereby ensuring the execution performance of the database.

[0079] According to an embodiment of the present invention, the above-mentioned construction of a cache table and a status table at a data node includes: during the database initialization process, constructing a node-level hash cache table and a node-level hash status table at each data node; wherein the key value of the hash cache table includes a hash value of a structured query statement or a serialized query plan, and the value of the hash cache table includes a query result set and transaction status information of an associated table of the query result set; wherein the key value of the hash status table includes an identifier of an associated table of the query result set, and the value of the hash status table includes transaction status information of an associated table of the query result set.

[0080] The above hash table construction process involved in the present invention is further described in detail below in conjunction with specific embodiments.

[0081] In the database, CN nodes select different query methods according to the number of tables involved in the query and the query complexity. However, for DN nodes, they receive two structures, namely SQL statements and serialized query plans, both of which are string structures. Therefore, when the database is initialized, two node-level hash table structures are constructed at each DN node. The key of one hash cache table is a string (the MD5 value of the SQL statement or serialized query plan received by the DN node), and the value includes the query result set and the transaction status of all tables involved in the query. The key of the other hash status table is the table id value, and the value in the value is the transaction status corresponding to the table.

[0082] According to an embodiment of the present invention, the above-mentioned use of data nodes to execute the query plan initiated by the coordination node to obtain the query result set includes: using the data node to receive the target query plan initiated by the coordination node, and judging the query plan cached in the cache table based on the target query plan; when the judgment result is that the target query plan is not cached in the cache table, using the data node to execute the target query plan, and updating the cache table and the status table; when the judgment result is that the target query plan has been cached in the cache table, performing a query operation on the cache table and performing a comparison operation on the status table, and based on the comparison result, updating the cache table and the status table.

[0083] Two hash tables are created when the node is initialized. At the beginning, the hash tables of all DN nodes are empty. After the DN node receives the query command sent from the CN node, it first determines whether there is a result set cache of the command in the cache table.

[0084] According to an embodiment of the present invention, when the judgment result is that the target query plan is not cached in the cache table, the target query plan is executed using the data node, and the cache table and the status table are updated, including: executing the target query plan using the data node to obtain the target query result set, and inserting the target query plan and the target query result set into the cache table; performing an identifier query on the status table, and when the identifier query result is that the identifier of the associated table of the target query result set exists in the status table, recording the transaction status information of the associated table of the target query result set using the cache table; performing an identifier query on the status table, and when the identifier query result is that the identifier of the associated table of the target query result set does not exist in the status table, inserting the identifier and transaction status information of the associated table of the target query result set into the status table, and recording the transaction status information of the associated table of the target query result set using the cache table.

[0085] The following is a further detailed description of the situation where the target query plan is not cached in the cache table through a specific embodiment.

[0086] If the result set of the command does not exist in the hash cache table, the query is executed according to the normal process, and then the query command and the corresponding result set are inserted into the cache table (to prevent the cached result set from occupying a large amount of memory, query commands with result sets exceeding 8KB are not cached), and the transaction status of all tables involved in the query is recorded. To record the transaction status of the table, you need to search the status table according to the table id, and the transaction status corresponding to the table id in the status table is recorded in the value of the cache table. If the transaction status of a table id does not exist in the status table, the table id and the corresponding transaction status (the status is the initialization status) are inserted into the status table and recorded in the value of the cache table.

[0087] According to an embodiment of the present invention, when the judgment result is that the target query plan has been cached in the cache table, a query operation is performed on the cache table and a comparison operation is performed on the status table, and based on the comparison result, the cache table and the status table are updated, including: querying the cache table to obtain a target query result set corresponding to the target query plan, and obtaining the transaction status information of the associated table of the target query result set; comparing the transaction status information in the cache table with the transaction status information in the status table to obtain a comparison result; when the comparison result is that the transaction status information in the cache table is the same as the transaction status information in the status table, using the data node to send the target query result set to the coordination node.

[0088] According to an embodiment of the present invention, when the judgment result is that the target query plan has been cached in the cache table, the query operation is performed on the cache table and the comparison operation is performed on the status table, and based on the comparison result, the cache table and the status table are updated, which also includes: when the comparison result is that the transaction status information in the cache table is different from the transaction status information in the status table, the target query plan is executed using the data node, the obtained target query result set is sent to the coordination node, and the cache table and the status table are updated.

[0089] The following further describes in detail the situation where the query plan has been cached in the cache table through a specific embodiment.

[0090] If the result set of the command exists in the hash cache table, it is necessary to compare the transaction status of each table in the cache table value with the transaction status of the table recorded in the status table. By comparing the transaction status of the table recorded in different hash cache tables, it is determined whether the tables involved have been modified since the result set corresponding to the query command was last recorded. If it has not been modified, there is no need to execute the corresponding query process again, and the cached result set is returned directly. If a table has been modified, it is necessary to execute the query according to the normal process, update the result set in the cache table, and update the transaction status in the cache table according to the status table.

[0091] According to an embodiment of the present invention, the above-mentioned use of a status table to record transaction status information generated by table modification operations and table structure modification operations performed by data nodes includes: when a data node performs a table modification operation on a target source table associated with a query result set, querying the status table for transaction status information on the target source table to obtain a query result, wherein the table modification operation includes copying, inserting, deleting, updating, updating and inserting, merging, and truncating; when the query result is that the transaction status information of the target source table exists in the status table, using the status table to update the transaction status information of the target source table.

[0092] The table modification operation involved in the present invention is further described in detail below through specific embodiments.

[0093] To ensure the correctness of the query results, when the DN node involves table modification operations (including copy, insert, delete, update, upsert, merge into, truncate and other operations), the corresponding transaction status needs to be updated in the status table. If the transaction status of the table exists in the status table, the transaction status of the table needs to be updated. If the transaction status of the table does not exist in the status table, no processing is required, because the initialization transaction status of the table will be inserted into the status table when the DN executes the query command. If the transaction status of the table cannot be found in the status table during the modification operation, it can be proved that the current DN node has not received the query operation involving the table, so no processing is required.

[0094] According to an embodiment of the present invention, the above-mentioned use of the status table to record the transaction status information generated by the data node performing table modification operations and table structure modification operations also includes: when the data node performs a table structure modification operation on the target source table associated with the query result set, the transaction status information of the target source table is modified in the status table; when the table structure modification operation is a rollback operation or an abort operation, the transaction status information of the target source table is deleted using the status table; when the table structure modification operation is a flashback operation, the status table is reset and the transaction status information of all target source tables in the status table is deleted.

[0095] The above table structure modification operation is further described in detail below through specific embodiments.

[0096] When the database executes some other statements, some special processing is required. When modifying the table structure, the corresponding transaction status needs to be modified in the status table. The table deletion operation directly deletes the corresponding record in the status table. When rollback and abort operations are involved, the transaction records of all tables involved in the transaction block need to be deleted from the status table. When the database performs a flashback operation, the status table is reset and all records in the status table are deleted.

[0097] According to an embodiment of the present invention, the above-mentioned controlling the number of cached query plans by setting parameters of data nodes and resetting the cache table and status table according to user predefined syntax includes: controlling the number of query plans cached in the cache table by setting parameters of data nodes, and regularly clearing query plans whose hit rates are lower than a preset threshold; controlling the data node to turn on or off the query result set caching function by setting cache function parameters; resetting the cache table and status table according to user predefined syntax, and using data nodes to set cache limits for query result sets.

[0098] According to an embodiment of the present invention, the above-mentioned use of data nodes to set cache restrictions for query result sets includes: when the query plan involves database system tables, database system views, query result sets associated with temporary tables, and query result sets associated with database built-in functions, the data node turns off the query result set cache function; when the query plan is a query plan for the interaction of intermediate query result sets between data nodes, the data node turns off the query result set cache function.

[0099] The following is a further detailed description of the query result set cache management process involved in the present invention through specific embodiments.

[0100] To prevent the records in the cache table from increasing infinitely and causing excessive memory usage, set the parameters of the DN node to control the number of query commands cached in the cache table, and regularly delete query commands with low hit rates in the cache table, and add user-defined syntax to reset the cache table and status table. In addition, DN adds restrictions to the result set cache function: system tables, system views, and temporary table-related queries do not cache result sets, built-in function-related queries do not cache result sets, and query plans involving the interaction of intermediate result sets between DN nodes (manifested by the query plan received by the DN node containing a stream node) do not cache result sets. Set additional parameters to control the closing and opening of the result set cache function. When caching SQL query statements on the DN node, you also need to add the table IDs of all tables involved in this query after the statement before caching to prevent confusion between query result sets of tables with the same name under different databases.

[0101] The present invention designs a method for caching query result sets in DN nodes. After the client sends a database query request, the CN node receives the query request and processes the request, and then pushes down the SQL statement or the serialized execution plan fragment to the DN to execute the corresponding query. The present invention designs a method for caching query result sets in DN nodes. After each DN receives the query request for the first time, it executes the corresponding query, and caches the result set and the corresponding query statement or plan fragment before sending the query result set to the CN. After the DN node receives the same statement or query fragment, if all tables involved in the query have not been modified, the cached result set is directly sent to the CN node, otherwise the DN node re-executes the corresponding query and updates the cached result set. This method is suitable for the situation where there are more query operations and fewer modification operations, and can improve the database query efficiency.

[0102] Based on the above-mentioned query result set caching method for a database, the present invention also provides a query result set caching device for a database. Figure 3 The device is described in detail.

[0103] Figure 3 It is a structural block diagram of a query result set caching device for a database according to an embodiment of the present invention.

[0104] like Figure 3 As shown, the query result set caching device 300 for a database in this embodiment includes a table construction and execution module 310 , a table cache and recording module 320 , a modification operation recording module 330 , and a parameter setting and resetting module 340 .

[0105] The table construction and execution module 310 is used to construct a cache table and a status table in the data node, and use the data node to execute the query plan initiated by the coordination node to obtain a query result set; the table construction and execution module 310 can be used to execute the operation S210 described above, which will not be repeated here.

[0106] The table cache and recording module 320 is used to cache the query result set and query plan using the cache table, and record the transaction status information of the cache table using the status table; the table cache and recording module 320 can be used to perform the operation S220 described above, which will not be repeated here.

[0107] The modification operation recording module 330 is used to use the status table to record the transaction status information generated by the data node performing table modification operations and table structure modification operations; the modification operation recording module 330 can be used to execute the operation S230 described above, which will not be repeated here.

[0108] The parameter setting and resetting module 340 is used to control the number of cached query plans by setting the parameters of the data nodes, and reset the cache table and the status table according to the user predefined syntax; the parameter setting and resetting module 340 can be used to execute the operation S240 described above, which will not be repeated here.

[0109] According to an embodiment of the present invention, any multiple modules of the table construction and execution module 310, the table cache and recording module 320, the modification operation recording module 330, and the parameter setting and resetting module 340 can be combined into one module for implementation, or any one of the modules can be split into multiple modules. Alternatively, at least part of the functions of one or more of these modules can be combined with at least part of the functions of other modules and implemented in one module. According to an embodiment of the present invention, at least one of the table construction and execution module 310, the table cache and recording module 320, the modification operation recording module 330, and the parameter setting and resetting module 340 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or implemented in any one of the three implementation methods of software, hardware, and firmware, or in any appropriate combination of any of them. Alternatively, at least one of the table construction and execution module 310, the table cache and recording module 320, the modification operation recording module 330, and the parameter setting and resetting module 340 may be at least partially implemented as a computer program module, which may perform corresponding functions when executed.

[0110] Figure 4 It is a block diagram of an electronic device suitable for implementing a query result set caching method for a database according to an embodiment of the present invention.

[0111] like Figure 4 As shown, the electronic device 400 according to an embodiment of the present invention includes a processor 401, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 402 or a program loaded from a storage part 408 to a random access memory (RAM) 403. The processor 401 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 401 may also include an onboard memory for caching purposes. The processor 401 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present invention.

[0112] In RAM 403, various programs and data required for the operation of electronic device 400 are stored. Processor 401, ROM 402 and RAM 403 are connected to each other via bus 404. Processor 401 performs various operations of the method flow according to the embodiment of the present invention by executing the programs in ROM 402 and / or RAM 403. It should be noted that the program can also be stored in one or more memories other than ROM 402 and RAM 403. Processor 401 can also perform various operations of the method flow according to the embodiment of the present invention by executing the programs stored in the one or more memories.

[0113] According to an embodiment of the present invention, the electronic device 400 may further include an input / output (I / O) interface 405, which is also connected to the bus 404. The electronic device 400 may further include one or more of the following components connected to the input / output (I / O) interface 405: an input portion 406 including a keyboard, a mouse, etc.; an output portion 407 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 408 including a hard disk, etc.; and a communication portion 409 including a network interface card such as a LAN card, a modem, etc. The communication portion 409 performs communication processing via a network such as the Internet. A drive 410 is also connected to the input / output (I / O) interface 405 as needed. A removable medium 411, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 410 as needed, so that a computer program read therefrom is installed into the storage portion 408 as needed.

[0114] The present invention also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiment; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present invention is implemented.

[0115] According to an embodiment of the present invention, the computer-readable storage medium may be a non-volatile computer-readable storage medium, for example, it may include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, an apparatus or a device. For example, according to an embodiment of the present invention, the computer-readable storage medium may include the ROM 402 and / or RAM 403 described above and / or one or more memories other than ROM 402 and RAM 403.

[0116] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0117] It will be appreciated by those skilled in the art that the features described in the various embodiments of the present invention may be combined and / or combined in various ways, even if such combinations or combinations are not explicitly described in the present invention. In particular, without departing from the spirit and teachings of the present invention, the features described in the various embodiments of the present invention may be combined and / or combined in various ways. All of these combinations and / or combinations fall within the scope of the present invention.

[0118] The embodiments of the present invention are described above. However, these embodiments are only for the purpose of illustration, and are not intended to limit the scope of the present invention. Although each embodiment is described above, it does not mean that the measures in each embodiment cannot be used in combination advantageously. Without departing from the scope of the present invention, those skilled in the art may make various substitutions and modifications, which should all fall within the scope of the present invention.

Claims

1. A method for caching a query result set of a database, characterized in that: The method comprises: Constructing a cache table and a state table at the data node, and using the data node to execute the query plan initiated by the coordination node to obtain a query result set; Utilizing the cache table to cache the query result set and the query plan, and utilizing the status table to record transaction status information of the cache table; Using the status table to record transaction status information generated by the data node performing table modification operations and table structure modification operations; The number of cached query plans is controlled by setting parameters of the data node, and the cache table and the state table are reset according to a user-predefined syntax.

2. The method according to claim 1, characterized in that Constructing the cache table and status table on the data node includes: During the database initialization process, a node-level hash cache table and a node-level hash status table are constructed at each of the data nodes; The key value of the hash cache table includes a hash value of a structured query statement or a serialized query plan, and the value of the hash cache table includes the query result set and transaction status information of an associated table of the query result set; The key value of the hash state table includes an identifier of the associated table of the query result set, and the value of the hash state table includes transaction status information of the associated table of the query result set.

3. The method according to claim 1, characterized in that The query plan initiated by the coordination node is executed using the data node to obtain a query result set including: Utilizing the data node to receive the target query plan initiated by the coordination node, and judging the query plan cached in the cache table based on the target query plan; If the result of the judgment is that the target query plan is not cached in the cache table, executing the target query plan using the data node, and updating the cache table and the status table; If the result of the determination is that the target query plan has been cached in the cache table, a query operation is performed on the cache table and a comparison operation is performed on the status table, and based on the comparison result, the cache table and the status table are updated.

4. The method according to claim 3, characterized in that If the result of the determination is that the target query plan is not cached in the cache table, executing the target query plan using the data node and updating the cache table and the status table include: Executing the target query plan using the data node to obtain a target query result set, and inserting the target query plan and the target query result set into the cache table; Performing an identifier query on the state table, and if the identifier query result is that the state table contains an identifier of the associated table of the target query result set, using the cache table to record transaction state information of the associated table of the target query result set; An identifier query is performed on the status table. If the identifier query result is that the identifier of the associated table of the target query result set does not exist in the status table, the identifier and transaction status information of the associated table of the target query result set are inserted into the status table, and the cache table is used to record the transaction status information of the associated table of the target query result set.

5. The method according to claim 3, characterized in that: In the case where the result of the judgment is that the target query plan has been cached in the cache table, performing a query operation on the cache table and performing a comparison operation on the state table, and updating the cache table and the state table based on the comparison result includes: Querying the cache table to obtain a target query result set corresponding to the target query plan, and acquiring transaction status information of a related table of the target query result set; Compare the transaction status information in the cache table with the transaction status information in the status table to obtain the comparison result; When the comparison result is that the transaction status information in the cache table is the same as the transaction status information in the status table, the target query result set is sent to the coordination node by using the data node.

6. The method according to claim 5, characterized in that Also includes: When the comparison result is that the transaction status information in the cache table is different from the transaction status information in the status table, the target query plan is executed using the data node, the obtained target query result set is sent to the coordination node, and the cache table and the status table are updated.

7. The method according to claim 1, characterized in that Using the status table to record the transaction status information generated by the data node performing table modification operations and table structure modification operations includes: When the data node performs a table modification operation on a target source table associated with the query result set, querying the state table for transaction state information on the target source table to obtain a query result, wherein the table modification operation includes copy, insert, delete, update, update insert, merge, and truncate; If the query result shows that the transaction status information of the target source table exists in the status table, the transaction status information of the target source table is updated by using the status table.

8. The method according to claim 7, characterized in that Also includes: When the data node performs a table structure modification operation on a target source table associated with the query result set, modifying transaction status information of the target source table recorded in the status table; In a case where the table structure modification operation is a rollback operation or an abort operation, deleting the transaction state information of the target source table by using the state table; In the case where the table structure modification operation is a flashback operation, the state table is reset and the transaction state information of all target source tables in the state table is deleted.

9. The method according to claim 1, characterized in that: Controlling the number of cached query plans by setting the parameters of the data node, and resetting the cache table and the state table according to a user predefined syntax includes: The number of query plans cached in the cache table is controlled by setting parameters of the data node, and query plans whose hit rates are lower than a preset threshold are regularly cleared; Control the data node to turn on or off the query result set cache function by setting cache function parameters; The cache table and the state table are reset according to the user predefined syntax, and the cache limit of the query result set is set using the data node.

10. The method according to claim 9, characterized in that The cache limit of the query result set set using the data node includes: In the case where the query plan involves a database system table, a database system view, a query result set associated with a temporary table, and a query result set associated with a database built-in function, the data node turns off a query result set cache function; In the case where the query plan is a query plan for the interaction of intermediate query result sets between the data nodes, the data nodes disable the query result set cache function.

Images